How can I keep my filesystem structure in sync with SQL?
I currently have a filesystem path that I would like to index into a SQL database. I need to access data so that I can make queries against files based on modified times or partial names or many other items.
Is there a way to somehow sync the filesystem with the database automatically, or even access the filesystem in a sql-like interface without rewriting recursively through directories?
I checked Microsoft Sync Framework 2.0 as it now supports SQL databases, however it does not support file sync to databases.
I'm sure other vendors are doing something similar, like Microsoft for the file database in Media Center, or programs like TVersity that store the file database.
source to share
You didn't mention the programming language, but this is how I would go about it, and I think this is how most media applications that support the library do it (although they might be writers in different languages and use the win32 api).
Using .net to get the raw data, I would recursively scan the directory one day and then add all the information to the database. Then I started the service using the FileSystemWatcher object to receive change notifications and handle events accordingly.
source to share
It sounds like you want Windows Search . (or Windows 7 library if you feel bleeding).
Ultimately though, something has to scan the drive in order to pull the information, be it you or a third party service.
As a third party, SQL may not be the best tool for this particular job, depending on what you want to look for. Tree structures are notoriously tricky to represent effectively in relational databases - your searches can get quite expensive!
source to share