Re: [sqlite] Different kind of constraint

[Stephen Chrzanowski]

That is perfectly valid, and would resolve a bunch of other headaches.  I
think I'll go that route.  Thanks.

On Sun, Feb 11, 2018 at 8:47 AM, J Decker  wrote:

> 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 
> 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 
> > 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
> > > >sqlite-users@mailinglists.sqlite.org
> > > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > >
> > >
> > > 

Re: [sqlite] Different kind of constraint

[J Decker]

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 
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 
> 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
> > >sqlite-users@mailinglists.sqlite.org
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users 

Re: [sqlite] Different kind of constraint

[Stephen Chrzanowski]

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  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
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different kind of constraint

[Keith Medcalf]

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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different kind of constraint

[Dan Kennedy]

On 02/11/2018 12:34 PM, Stephen Chrzanowski wrote:

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);


How about:

  CREATE UNIQUE INDEX i1 ON MagazineContent(MagazineID) WHERE 
FileType='Cover';


Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users