Just a thought; a thumbnail/cover image seems like somehting that is often gotten before lots of other data, and maybe storing that alone in a table of covers would keep that index shorter for the entry pages... And then you could enforce unique parent id on the cover and not on the content.
On Sun, Feb 11, 2018 at 2:36 AM, Stephen Chrzanowski <pontia...@gmail.com> wrote: > Keith> Fixed text pushed by code, no user interaction to define what goes > in this field will exist. The FileType handle will be used for different > file type descriptors that I'll later be grouping together (IE: JPG, PNG, > BMP all become "Images", while MP3 or WAV would be AUDIO). I can set the > field to be insensitive with no repercussions, or I change the code to > force upper or lower case when setting this field. When I dump the random > files in, the software is going to insert the type of files based on > extension, but when I SELECT, I'll be ordering/filtering based on FileType > then FileName. > > Dan> I'll try that on a scratch database. I've been fighting with string > types and how this wrapper handles things. I almost considered switching > to integers for this, and have a type set or a bunch of constants in code > to determine the different types, however, it came down to how string > conversions (again) and how it stored "Cover" as some type of weird string, > but everything I used to look at that string came back looking absolutely > normal. I've managed to find a work around for it, and possibly an > adjustment in how I use this wrapper. I've done plenty of inserts with > strings, I guess not as many with doing string compare. Makes me wonder > what code I have written that inserts strings isn't what I expect. > > I still might rework this part of code/DB, I don't know yet. Only been at > it for 5 hours. But bed time now. > > Thanks for the input. > > On Sun, Feb 11, 2018 at 2:49 AM, Keith Medcalf <kmedc...@dessus.com> > wrote: > > > > > Is FileType case sensitive? > > > > > > --- > > The fact that there's a Highway to Hell but only a Stairway to Heaven > says > > a lot about anticipated traffic volume. > > > > > > >-----Original Message----- > > >From: sqlite-users [mailto:sqlite-users- > > >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski > > >Sent: Saturday, 10 February, 2018 22:35 > > >To: General Discussion of SQLite Database > > >Subject: [sqlite] Different kind of constraint > > > > > >I've been mucking with this for the last half hour or so. > > > > > >I have a table which is kind of in a unique situation. > > > > > >In the app I was talking about earlier, I have a table that will > > >contain > > >BLOB that has FK data referencing a magazine table. This blob-tables > > >responsibility is going to hold the actual contents of the files I'm > > >going > > >to give it, so, floppy disk images, or, pictures, or what have you. > > >Any > > >file can be attached to this one particular magazine, BUT, I only > > >want a > > >single COVER image stored in this table for that one magazine, and > > >I'd like > > >SQLite to handle ensuring that this happens. > > > > > >In reference to the magazine table itself, instead of putting a BLOB > > >field > > >on the actual Magazine table, or creating a table JUST for cover > > >images, > > >I'd like to have a unique constraint on the table in that I can have > > >the > > >same MagazineID and FileType fields, EXCEPT, I can only have one > > >MagazineID > > >with one FileType='Cover' entry. > > > > > >In other words, I can never have more than a single > > >MagazineID=1,FileType='Cover' entry, but, I can have as many > > >MagazineID=1,FileType='Image' and as many MagazineID=1,FileType='D64' > > >rows > > >with their relevant blob info. > > > > > >What I'm doing in code right now is deleting > > >MagazineID=1,FileType='Cover' > > >before doing an insert on the table, then update the blob field with > > >the > > >image, but, if I can set this up to be an Insert Or Replace function, > > >that'd be better on me. > > > > > >The schema for the table in question is pretty simple: > > > > > >CREATE TABLE [_MagazineContent]( > > > [MagazineID] INTEGER NOT NULL REFERENCES [Magazines]([MagazineID]) > > >ON > > >DELETE CASCADE, > > > [Description] CHAR, > > > [FileType] CHAR NOT NULL, > > > [Content] BLOB); > > >_______________________________________________ > > >sqlite-users mailing list > > >firstname.lastname@example.org > > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > email@example.com > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > firstname.lastname@example.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list email@example.com http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users