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

Re: [sqlite] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Did a bit more digging, and the code I wrote is doing what its supposed to
do.  There is (what I'm calling) a bug with the string handling in the
wrapper (That I didn't write) I'm using that doesn't know how to deal with
"strings" type, but it does work fine with ansistrings, and I think the
translation between strings and ansistring seems to be messing up the
actual bind.

What I had before was this:
db.ExecSQL('delete from sqlite_sequence where
name=?',[ansistring('Magazines')]);

However, when I changed the line to this:
db.ExecSQL('delete from sqlite_sequence where name=''Magazines''');

The code did what I needed it to do.

On Sat, Feb 10, 2018 at 8:19 PM, Simon Slavin  wrote:

> On 11 Feb 2018, at 1:12am, Stephen Chrzanowski 
> wrote:
>
> > How does one reset the sqlite_sequence values?
>
> See section 2.6.2 of
>
> 
>
> When considering how to modify that table, pay attention to the last
> paragraph of that section.
>
> Simon.
> ___
> 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] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
ago.  Its served me well in Delphi 7, but with the move to Unicode
materials, its got a few things to iron out that I've been able to work
around in code.  Like this threads sample. :]


On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin  wrote:

>
>
> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski 
> wrote:
>
> > What I had before was this:
> > db.ExecSQL('delete from sqlite_sequence where
> > name=?',[ansistring('Magazines')]);
>
> Oh dear.  Don't you just hate getting lost in multiple text
> representations ?
>
> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
> Unicode.  Anyway, glad you got it working.
>
> Simon.
> ___
> 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] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Half thought... SQLite Wrapper from Tim Anderson at itwriting.com

On Sat, Feb 10, 2018 at 9:06 PM, Stephen Chrzanowski 
wrote:

> Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
> ago.  Its served me well in Delphi 7, but with the move to Unicode
> materials, its got a few things to iron out that I've been able to work
> around in code.  Like this threads sample. :]
>
>
> On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin 
> wrote:
>
>>
>>
>> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski 
>> wrote:
>>
>> > What I had before was this:
>> > db.ExecSQL('delete from sqlite_sequence where
>> > name=?',[ansistring('Magazines')]);
>>
>> Oh dear.  Don't you just hate getting lost in multiple text
>> representations ?
>>
>> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
>> Unicode.  Anyway, glad you got it working.
>>
>> Simon.
>> ___
>> 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] sqlite 3.22.0 read-only WAL mode clarifications

[Leonard Lausen]

Hey Simon,

thanks for your clarification.

> Will any of the processes accessing the database have write permission
> ? If not, if they're all just reading the existing database, then
> there's no opportunity for corruption. Think of it as

All of the processes (on "main" and remote host) have read and write
access, though I am only reading data in the processes accessing the db
on a network filesystem. So it may be safer to open the database with
SQLITE_OPEN_READONLY on the remote host.

> 1) Reading a database while it's being written to may yield corrupt results.  
> SQLite uses locking to avoid this.
> 2) Two processes writing a database at once will corrupt the database.  
> SQLite uses locking to avoid this.
> 3) Locking does not work properly across a network

Thanks for making me aware of potential corrupt results on read.

Due to the potential corruption I changed my scripts to ssh into the
main host and create a backup of the database with 'journal_mode delete'
that can then safely be used to read data even when on a network
filesystem.

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

[sqlite] Reset value of sqlite_sequence

[Stephen Chrzanowski]

How does one reset the sqlite_sequence values?

I'm trying with [ delete from sqlite_sequence where name='...' ] but my app
continuously goes and keeps adding up the number.  If I go into a 3rd party
tool and delete the table, the next time my app goes and populates data,
the counter starts at 1.  Is there a flush or something I need to run?

I essentially want to truncate the table, get rid of all data, reset the
insert counter.

This is a development action only while I'm preparing some data for the
database, and not to be used as a production activity.

I suppose what I could do is just forcibly set the ID at insert, but, was
rather curious to why this delete wasn't working on the sqlite_sequence
table.

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

Re: [sqlite] Reset value of sqlite_sequence

[Simon Slavin]

On 11 Feb 2018, at 1:12am, Stephen Chrzanowski  wrote:

> How does one reset the sqlite_sequence values?

See section 2.6.2 of



When considering how to modify that table, pay attention to the last paragraph 
of that section.

Simon.
___
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] Reset value of sqlite_sequence

[Simon Slavin]

On 11 Feb 2018, at 1:39am, Stephen Chrzanowski  wrote:

> What I had before was this:
> db.ExecSQL('delete from sqlite_sequence where
> name=?',[ansistring('Magazines')]);

Oh dear.  Don't you just hate getting lost in multiple text representations ?

C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects Unicode.  
Anyway, glad you got it working.

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

[sqlite] Different kind of constraint

[Stephen Chrzanowski]

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] sqlite 3.22.0 read-only WAL mode clarifications

[Simon Slavin]

On 10 Feb 2018, at 7:07am, Leonard Lausen  wrote:

> thanks for your clarification.

You're welcome.  A couple of things you wrote make me think I phrased things 
poorly so I'm just taking this opportunity to illustrate what I wrote.

>> Will any of the processes accessing the database have write permission
>> ? If not, if they're all just reading the existing database, then
>> there's no opportunity for corruption. Think of it as
> 
> All of the processes (on "main" and remote host) have read and write
> access, though I am only reading data in the processes accessing the db
> on a network filesystem. So it may be safer to open the database with
> SQLITE_OPEN_READONLY on the remote host.

SQLITE_OPEN_READONLY on the remote processes won't change anything important 
given that the remote processes aren't making changes anyway.  What's important 
is that nothing is making changes to the database while it's being read.  Even 
if the remote processes are only reading, not writing, they can still get bad 
data if a local process is making changes to the database at the same time.

>> 1) Reading a database while it's being written to may yield corrupt results. 
>> SQLite uses locking to avoid this.
>> 2) Two processes writing a database at once will corrupt the database.  
>> SQLite uses locking to avoid this.
>> 3) Locking does not work properly across a network
> 
> Thanks for making me aware of potential corrupt results on read.
> 
> Due to the potential corruption I changed my scripts to ssh into the
> main host and create a backup of the database with 'journal_mode delete'
> that can then safely be used to read data even when on a network
> filesystem.

The journal mode of the database doesn't matter.  It can still provide 
corrupted data if locking isn't working properly.

Here's an example of what can happen if locking isn't working properly.  This 
is contrived and probably fake (in real life things might not work like this), 
but it demonstrates a problem in a simple way.

Table "people":
Anna, 28
Bertie, 40
Edna, 42
Fred, 88
Gillian, 90
An index on table "people" of (age)

Process 1 (accessing the file locally, read-write):
UPDATE people SET age="38" WHERE name="Fred"
Process 2 (accessing the file remotely, read-only):
SELECT name,age FROM people ORDER BY age

Suppose the two happen with timing such that the UPDATE is written to the file 
when the SELECT is between "Bertie" and "Edna"  The "Fred" row will not be 
returned by the SELECT !

Now carry this through to how in the file pointers are used to point to rows, 
and you can see similar problems even if you are just inserting a new row or 
deleting an old one.  Rather than getting incorrect data, you can get a result 
indicating that the file is corrupt.  But you might not, you might just get 
incorrect data as in the above example.

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