Thanks for pointing multimedia id, Stephan Beal. I missed it. I will ad this to my schema.
On Thu, Nov 7, 2013 at 3:07 PM, Stephan Beal <[email protected]> wrote: > 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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
