On Thu, Nov 7, 2013 at 11:50 AM, dd <[email protected]> wrote: > CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY > AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, > UNIQUE(file_path)); >
Some years ago i worked on something similar to keep track of my multimedia collection and i added one layer of abstraction to this table: instead of storing just a file path, i stored a media ID and a path, with the media ID effectively defining the root path. e.g. let's say i've got 5 external hard drives, each of them with a unique label. Mine were called MM-<SIZE_IN_GB>-<SERIAL_NUM>, e.g. MM-500-00, MM-500-01, etc. (MM==MultiMedia). The file path is then relative to wherever that media (device) is mounted. In my case i stored the media id as a simple string, but arguably more correct would be to store it as an integer and then have a separate lookup table mapping id==>name. A view could then easily translate that into a more human-readable form. Was it worth the effort? In my case it was because my media was scattered around many external drives and this allowed me to quickly determine which drive. But for simple use cases it very possibly isn't worth the extra effort. Unfortunately, i have long since lost all that code (it was implemented in JS, using a SpiderMonkey binding for sqlite), so i don't have it to share with you, but the idea is simple enough that it doesn't really need a demonstration. -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
