Rebuild event database from filesystem?

[mdailey]

Hello to all and thanks for a great open source product.

I have a question about rebuilding my event database from the events stored on the filesystem.

I put my events directory on a CIFS share mounted from my NAS. This mostly works well.

However, I had a problem that every time the NAS went offline for some reason, if zmaudit.pl ran, it would delete all the events from the database. The events themselves are still on the disk -- I have about 2 GB of events sitting around and no access to them from the console.

I think I have fixed the problem by adding the "hard" option to the CIFS mount -- this should force zmaudit.pl to block rather than delete database entries when the NAS is temporarily offline. The default behavior for CIFS is "soft" in which any accesses to the filesystem will, when the filesystem is unavailable, return an error immediately, encouraging zmaudit.pl to delete the corresponding database entry.

Hopefully that will work fine. But now I have an empty database and want to rebuild my events database from the files that are still sitting there on the filesystem.

I guess I imagine a reverse of zmaudit.pl -- a script that looks for events on the disk and adds a database entry for any event that's on the FS but not in the DB.

Is it possible? I don't know if all the necessary information is there on the disk or not, but such a script would be very useful.

[Magic919]

I really can't see that there's enough data. You could run some fancy find exargs kind of command, but you'd only really get the monitor ID and the Event ID.

Here's the full table -

mysql> describe Events;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| Id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| MonitorId | int(10) unsigned | NO | PRI | 0 | |
| Name | varchar(64) | NO | | | |
| Cause | varchar(32) | NO | | | |
| StartTime | datetime | YES | MUL | NULL | |
| EndTime | datetime | YES | | NULL | |
| Width | smallint(5) unsigned | NO | | 0 | |
| Height | smallint(5) unsigned | NO | | 0 | |
| Length | decimal(10,2) | NO | | 0.00 | |
| Frames | int(10) unsigned | YES | MUL | NULL | |
| AlarmFrames | int(10) unsigned | YES | | NULL | |
| TotScore | int(10) unsigned | NO | | 0 | |
| AvgScore | smallint(5) unsigned | YES | | 0 | |
| MaxScore | smallint(5) unsigned | YES | | 0 | |
| Archived | tinyint(3) unsigned | NO | MUL | 0 | |
| Videoed | tinyint(3) unsigned | NO | | 0 | |
| Uploaded | tinyint(3) unsigned | NO | | 0 | |
| Emailed | tinyint(3) unsigned | NO | | 0 | |
| Messaged | tinyint(3) unsigned | NO | | 0 | |
| Executed | tinyint(3) unsigned | NO | | 0 | |
| Notes | text | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+

[mdailey]

Thanks Magic919 -- you think all I would have to do is populate that table? Might not be too bad. Approximate start/stop times could be extracted from the file system time stamps or even OCR'ed from the images themselves, and zma could possibly be run on each sequence to reconstruct the cause/scores (though I don't know if the buffered frames are sufficient for the background model, and haven't really found a use for those fields myself anyway). All the other fields would have to be set to default values I guess.

I guess no such thing exists then! Will try to find some time (or hire a student)...

And, obviously, I should be backing up the database regularly.