[sqlite] (no subject)

[Jayram Singh]

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

[sqlite] (no subject)

[Lars Frederiksen]

Hi SQLite-users,

I have in the console prg (cmd) created a small database in SQLite with only
one table (just in order to check out SQLite). The Table is defined like
this:

CREATE TABLE IF NOT EXISTS "Gloser"(
GRAESK VARCHAR(50) NULL,
DANSK VARCHAR(50) NULL,
LEKTION VARCHAR(10) NULL,
NOTE TEXT NULL
);

As a programming language I use Delphi 10.2 (GUI Object Pascal) and their
FireDAC components for handling SQLite.
When I (in my Delphi program) try to write to the database like this:

fdqFlashCard.Append;
fdqFlashCard.FieldByName('GRAESK').AsString:= 'græsk';
fdqFlashCard.FieldByName('DANSK').AsString:= 'dansk';
fdqFlashCard.FieldByName('LEKTION').AsString:= 'lektion';
fdqFlashCard.Post;

Then I get this error:

[FireDAC][Phys][SQLite]ERROR: Attempt to write a readonly database

I have been searching without a result. Anybody with a helping hint?

BR Lars 

 

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

Re: [sqlite] (no subject)

[J Decker]

On Mon, Jul 30, 2018 at 7:09 PM J Decker  wrote:

>
>
> On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:
>
>>
>> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
>> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
>> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>>
>> >Is there something about the combination of options I've used?
>>
>> Do you get different results when using different options?  (of course,
>> if you turn off foreign key enforcement then you will not get errors when
>> you violate declared foreign key constraints), so other than that obviously?
>>
>> It seems to be complaining about a foreign-key reference not existing.
>> The only option which would affect that is having foreign-keys turned on.
>> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
>> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
>> enforced -- the default state is ON rather than OFF)
>>
>> I removed all ( added pragma foreign_keys=on when opening the connection
> ), and it worked... readded options one at a time until it failed.
> OMIT_UTF16 causes it to fail.
>
> "SQLITE_OMIT_UTF16"
>
I think I lied there (and really sorry this was under (no subject))

>
>
>
>> To understand what foreign-key constraint you are violating one would
>> need to see the database schema -- however my initial guess would be that
>> that a option4_name is being checked when you provided an name_id -- that
>> you have your constraints crossed (or collation for text fields set
>> incorrectly, perhaps)
>>
>> > ... select name_id from
>> >option4_name
>> >where name like 'System Settings'
>>
>> Why are you using "like" instead of "==" ... there is no wildcard in the
>> string ...
>>
>
> Uhmm good question?  I think it can also load names by wildcard.
>
>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> ___
>> 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] (no subject)

[J Decker]

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:

>
> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>
> >Is there something about the combination of options I've used?
>
> Do you get different results when using different options?  (of course, if
> you turn off foreign key enforcement then you will not get errors when you
> violate declared foreign key constraints), so other than that obviously?
>
> It seems to be complaining about a foreign-key reference not existing.
> The only option which would affect that is having foreign-keys turned on.
> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
> enforced -- the default state is ON rather than OFF)
>
> I removed all ( added pragma foreign_keys=on when opening the connection
), and it worked... readded options one at a time until it failed.
OMIT_UTF16 causes it to fail.

"SQLITE_OMIT_UTF16"



> To understand what foreign-key constraint you are violating one would need
> to see the database schema -- however my initial guess would be that that a
> option4_name is being checked when you provided an name_id -- that you have
> your constraints crossed (or collation for text fields set incorrectly,
> perhaps)
>
> > ... select name_id from
> >option4_name
> >where name like 'System Settings'
>
> Why are you using "like" instead of "==" ... there is no wildcard in the
> string ...
>

Uhmm good question?  I think it can also load names by wildcard.


>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] (no subject)

[Keith Medcalf]

>"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
>"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
>"SQLITE_DEFAULT_FOREIGN_KEYS=1"

>Is there something about the combination of options I've used?

Do you get different results when using different options?  (of course, if you 
turn off foreign key enforcement then you will not get errors when you violate 
declared foreign key constraints), so other than that obviously?

It seems to be complaining about a foreign-key reference not existing.  The 
only option which would affect that is having foreign-keys turned on.  
(SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute 
PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys 
enforced -- the default state is ON rather than OFF)  

To understand what foreign-key constraint you are violating one would need to 
see the database schema -- however my initial guess would be that that a 
option4_name is being checked when you provided an name_id -- that you have 
your constraints crossed (or collation for text fields set incorrectly, 
perhaps)

> ... select name_id from
>option4_name
>where name like 'System Settings'

Why are you using "like" instead of "==" ... there is no wildcard in the string 
...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

[sqlite] (no subject)

[J Decker]

I have these options ... version 3.23.0.

"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0","SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
"SQLITE_DEFAULT_FOREIGN_KEYS=1"

This is the end of operations...  It's starting to get an option...

13:31:12.541|88A08724~n...@sack.cc(91500):Getting option
{DEFAULT}[System Settings]Enable System Mapping=0

It selects from option4_name the name_id that = "system settings"

13:31:12.543|88A08724~n...@sack.cc(91500):Do
Command[01EA9BCE24EC:*/../option.db]: select name_id from option4_name
where name like 'System Settings'

it returns a UUID of that name

13:31:12.544|88A08724~n...@sack.cc
(85945):cc47f95a-e79a-11e7-872c-2e6fc90d301f

this checks to see if the option already exists...

13:31:12.546|88A08724~n...@sack.cc(91500):Do
Command[01EA9BCE24EC:*/../option.db]: select option_id from option4_map
where parent_option_id='69c9627f-e285-11e7-8728-2c6fc90d3020' and
name_id='cc47f95a-e79a-11e7-872c-2e6fc90d301f'

And it does not.

13:31:12.547|88A08724~n...@sack.cc(85661):no data

This opens a new connection to the database ( * B6E7EC instead of * CE24EC
); and starts a transation for the inserts.

13:31:23.409|88A08724~n...@sack.cc(84179):Do
Command[01EA9DB6E7EC:*/../option.db]: BEGIN TRANSACTION

This does an insert of the above queried node with the UUID of the name
returned from option4_name

13:31:23.410|88A08724~n...@sack.cc(92559):Do
Command[01EA9DB6E7EC:*/../option.db]: Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('7f597a7b-9437-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')

And the operation fails.

13:31:23.418|88A08724~n...@sack.cc(92567):Error inserting option:
Result of prepare failed? foreign key mismatch - "option4_map" referencing
"option4_name" at char 191[] in [Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('7f597a7b-9437-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')]


Is there something about the combination of options I've used?

There will only be 1 thread ever in sqlite library code at a time. (the
number after the logging timestamp is the thread ID   88A08724 )  So
not only is it one thread, but is the same thread always.

there shouldn't be any outstanding operations correction there aren't
any other outstanding operations on the database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[dmp]

On 7/12/18, danap wrote:
>> I use a dump
>> in my interface which I used with diff to compare changes in my
>> personal expense database. This was to insure changes introduced in work
>> on the interface were not screwing things up. Very helpful to insure
>> your not introducing bugs.

D. Richard Hipp wrote:
> I am glad that has been working for you.  But there is a caveat:  The
> ".dump" format can (and does) change slightly from one release of
> SQLite to the next.  So you are welcomed to continue using ".dump"
> this way, but just be careful that you do not compare the .dump output
> from two different versions of SQLite.

Well, sorry Mr. Hipp, but the dump was from my own SQLite GUI. So what
I was doing is insuring some coding changes to the GUI's dump had not
changed anything, by comparing to prior dumps of data with the same
tool. So I was really doing exactly what you caution about to debug
between versions.

Idea being, dump ASCII data, compare over time with some kind of diff
tool to see changes. So to user's question, be sure to use same version
of the dump tool as you indicate.

danap.

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

[sqlite] (no subject)

[Roy Piter]

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

Re: [sqlite] (no subject)

[R Smith]

On 2018/06/19 9:55 PM, Stephen Chrzanowski wrote:

*sigh* I wish I had this kind of homework back in the day..


Well, I say you could just do the homework of those who do post homework 
questions here That way it technically IS your homework, and as 
such, your wish would have come true.



Cheers,
Your friendly neighbourhood Genie.

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

Re: [sqlite] (no subject)

[Stephen Chrzanowski]

*sigh* I wish I had this kind of homework back in the day..

On Tue, Jun 19, 2018 at 3:22 PM, Simon Slavin  wrote:

> On 19 Jun 2018, at 8:15pm, Vicente minguez gabarda 
> wrote:
>
> > hello I need a simple example of relationship between table,
> > I would like to be able to visualize the relationship code between tables
> > and then reuse it.
>
> 
>
> One day, just for a laugh, we should actually do someone's homework for
> them.
>
> 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] (no subject)

[Simon Slavin]

On 19 Jun 2018, at 8:15pm, Vicente minguez gabarda  wrote:

> hello I need a simple example of relationship between table,
> I would like to be able to visualize the relationship code between tables
> and then reuse it.



One day, just for a laugh, we should actually do someone's homework for them.

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

[sqlite] (no subject)

[Vicente minguez gabarda]

hello I need a simple example of relationship between table,
I would like to be able to visualize the relationship code between tables
and then reuse it.
thank you very much
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Darrol Thompson]

Sent from Yahoo Mail on Android
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Jim Dodgen]

Thanks I needed a Laugh,

try clicking on one of the links at the bottom of your emails and
unsubscribe.

*Jim Dodgen*







On Fri, Oct 6, 2017 at 7:41 PM, cindy estepp 
wrote:

> stop emailing me i do not know you
> ___
> 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] (no subject)

[cindy estepp]

stop emailing me i do not know you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Ron]

This seems to be exactly what I'm looking for. Thanks!

--Ron

Op ma 17 jul. 2017 om 20:23 schreef Jens Alfke 

>
> > On Jul 17, 2017, at 11:08 AM, Ron  wrote:
> >
> > I am looking into the possibility of putting the changes in SQLite
> database
> > into kafka, similar to this:
> >
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> <
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> >
>
> I suggest looking at the source code of the Sessions extension, which
> “provide[s] a mechanism for recording changes to some or all of the rowid
> tables in an SQLite database, and packaging those changes into a
> "changeset" or "patchset" file that can later be used to apply the same set
> of changes to another database with the same schema and compatible starting
> data.”
>
> It looks as though the changesets produced by the extension aren’t
> intended to be readable, but its implementation must be doing something
> similar to what you want to do, i.e. discovering all the changes to tables
> made since some prior time. So you should be able to modify the extension
> to take those changes and send them to Kafka instead of writing them into a
> blob.
>
> https://www.sqlite.org/sessionintro.html
>
> —Jens
> ___
> 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] (no subject)

[Jens Alfke]

> On Jul 17, 2017, at 11:08 AM, Ron  wrote:
> 
> I am looking into the possibility of putting the changes in SQLite database
> into kafka, similar to this:
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
>  
> 

I suggest looking at the source code of the Sessions extension, which 
“provide[s] a mechanism for recording changes to some or all of the rowid 
tables in an SQLite database, and packaging those changes into a "changeset" or 
"patchset" file that can later be used to apply the same set of changes to 
another database with the same schema and compatible starting data.”

It looks as though the changesets produced by the extension aren’t intended to 
be readable, but its implementation must be doing something similar to what you 
want to do, i.e. discovering all the changes to tables made since some prior 
time. So you should be able to modify the extension to take those changes and 
send them to Kafka instead of writing them into a blob.

https://www.sqlite.org/sessionintro.html

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

Re: [sqlite] (no subject)

[Jay Kreibich]

I’d look at creating a virtual table that can be setup to “shadow” any existing 
table.  Basically pass-through any read or write operations, possibly shunting 
off writes.   This has the advantage of not requiring any kind of patching or 
modifications to the core library.  Sounds a lot simpler too.

 -j





On Jul 17, 2017, at 1:08 PM, Ron  wrote:

> Hi,
> 
> Before I dive into the SQLite internals, I'd like to ask the mailing list
> first.
> 
> I am looking into the possibility of putting the changes in SQLite database
> into kafka, similar to this:
> https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
> .
> 
> Would it be hard to get this info out of SQLite?
> PostgreSQL calls it 'logical decoding'. I may not need the full feature,
> but I would need all changed records.
> 
> Thanks for any insights.
> Ron Arts
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



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

[sqlite] (no subject)

[Ron]

Hi,

Before I dive into the SQLite internals, I'd like to ask the mailing list
first.

I am looking into the possibility of putting the changes in SQLite database
into kafka, similar to this:
https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/
.

Would it be hard to get this info out of SQLite?
PostgreSQL calls it 'logical decoding'. I may not need the full feature,
but I would need all changed records.

Thanks for any insights.
Ron Arts
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[عبدالعزيز الرويلي]

[sqlite] (no subject)

[Dan Kennedy]

On 10/03/2015 03:19 AM, Andrew Cunningham wrote:
>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every
> operation
> on any table that is the parent or child in an FK constraint) it does
> extra seeks to see if it really does. i.e. each time you insert
> into the
> parent table, it seeks within the child table to see if the insert
> fixes
> a foreign key violation. And if it does, the counter is decremented.
>
> So we know there is overhead, but I'm quite surprised that it is 1000x
> slower.
>
> How large is the database? So large that these extra seeks could be
> performing real IO (i.e. having to go all the way to the disk for
> every
> seek, not just finding the data in the OS cache)?
>
>
> OK, that kinds of makes sense in explaining where the problem comes 
> from. I really do see an exponential growth in execution time 
> depending on how many INSERTS are made after the first FK violation 
> occurs.
>
> I have a very simple example that , for example, takes 0.2s with no FK 
> violations and 25s ( about  100x slower) when a single FK violation is 
> forced before I start doing the "mass creation".
>
> This is a small database, creating 500  parent objects, each with 50 
> child objects.
> If I change (double) the number of objects to 1000 the time changes to 
> 0.4s and 100s. It looks like an N^2 problem. So you can see that very 
> quickly my factor of 1000x is very easy to reach..
>
>
> When FK checking is in DEFERRED mode, the implication is that no FK 
> checking is made until the COMMIT.
>
> That's very useful, as often it is impossible to make the 
> modifications done in the correct order to satisfy all the 
> FK=IMMEDIATE constraints, even though the end result is a 
> "referentially correct" database.
>
> So what you are saying above makes DEFERRED mode a lot less useful.
>
> DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests.
>
>
>


Hi,

Ok. My first guess is that you are missing the "suggested index" on the 
child table. Without this, if there is a foreign key violation in the 
db, each insert on the parent table will cause a linear scan of the 
entire child table. With the index, it's just a simple seek.

https://www.sqlite.org/foreignkeys.html#fk_indexes

If you're unsure, open your database using the command line tool and 
dump the schema using the ".schema" command. Post that here and someone 
should be able to tell you if you're missing an index or not.

Dan.

[sqlite] (no subject)

[R.Smith]

On 2015-10-03 01:51 PM, Dan Kennedy wrote:
>
> Ok. My first guess is that you are missing the "suggested index" on 
> the child table. Without this, if there is a foreign key violation in 
> the db, each insert on the parent table will cause a linear scan of 
> the entire child table. With the index, it's just a simple seek. //...

This is quite probably the issue. It is extremely unlikely that anyone 
could have a reason for not having an index on a relation column, but it 
happens.

On this note, some other engines (such as MySQL) forces an Index to 
exist on a foreign key reference, or rather, it won't form a foreign key 
relation on a non-indexed column. I realize this is perhaps late in the 
life-cycle for SQLite3 to consider, but maybe SQLite4 could have similar 
enforcement?

[sqlite] (no subject)

[Andrew Cunningham]

>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every operation
> on any table that is the parent or child in an FK constraint) it does
> extra seeks to see if it really does. i.e. each time you insert into the
> parent table, it seeks within the child table to see if the insert fixes
> a foreign key violation. And if it does, the counter is decremented.
>
> So we know there is overhead, but I'm quite surprised that it is 1000x
> slower.
>
> How large is the database? So large that these extra seeks could be
> performing real IO (i.e. having to go all the way to the disk for every
> seek, not just finding the data in the OS cache)?


OK, that kinds of makes sense in explaining where the problem comes from. I
really do see an exponential growth in execution time depending on how many
INSERTS are made after the first FK violation occurs.

I have a very simple example that , for example, takes 0.2s with no FK
violations and 25s ( about  100x slower) when a single FK violation is
forced before I start doing the "mass creation".

This is a small database, creating 500  parent objects, each with 50 child
objects.
If I change (double) the number of objects to 1000 the time changes to 0.4s
and 100s. It looks like an N^2 problem. So you can see that very quickly my
factor of 1000x is very easy to reach..


When FK checking is in DEFERRED mode, the implication is that no FK
checking is made until the COMMIT.

That's very useful, as often it is impossible to make the modifications
done in the correct order to satisfy all the FK=IMMEDIATE constraints, even
though the end result is a "referentially correct" database.

So what you are saying above makes DEFERRED mode a lot less useful.

DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests.

[sqlite] (no subject)

[Ismail Keskin]

___
?smail Keskin
ismail.keskine at gmail.com
cep : 0532 576 8488

[sqlite] (no subject)

[djamel slim]

Hello,
I would like know the difference between SQLite and SQLite Manager,
and how many bytes i can register in SQLite and SQLite Manager.
Thanks.

[sqlite] (no subject)

[Simon Slavin]

On 11 Mar 2015, at 2:18pm, djamel slim  wrote:

> I would like know the difference between SQLite and SQLite Manager,

SQLite is a tool for programmers to use.  It allows for easy and flexible 
storage and retrieval of data.  If you're just someone who uses programs, and 
not a programmer, you'll never use SQLite.

SQLite Manager is one of the many programs which uses SQLite.  It was not 
written by, and is not supported by, the team that wrote SQLite.

> and how many bytes i can register in SQLite and SQLite Manager.

I can't think of any feature of SQLite which 'registers' bytes.  SQLite can 
store bytes and there are limits to the number of bytes it can store, but they 
are so high you're unlikely to ever run into them.  If you want to know the 
details you can probably find what you want here:



If not, post again with a clearer question.

Simon.

[sqlite] (no subject)

[Adam Devita]

from
http://sourceforge.net/projects/sqlitemanager/

"SQLiteManager is a multilingual web based tool to manage SQLite
database. The programming language used is: PHP4, but work fine with
PHP5. Work just as well on a platform Linux as on Windows or MAC."

from
http://www.sqlite.org/
"SQLite is a software library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine."

One is a db engine, the other is a user interface that uses it.



On Wed, Mar 11, 2015 at 10:18 AM, djamel slim  
wrote:
> Hello,
> I would like know the difference between SQLite and SQLite Manager,
> and how many bytes i can register in SQLite and SQLite Manager.
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] (no subject)

[rhey Yahoo]

Please dnt spam my email

Re: [sqlite] (no subject)

[Simon Slavin]

On 13 Oct 2014, at 12:06pm, Rohit Kaushal  wrote:

> please unregister me

Only you can stop forest fires.  See the link at the bottom of every post to 
this list.

Simon.

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

[sqlite] (no subject)

[Rohit Kaushal]

please unregister me
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Uros Reljic]

http://recycling-it.com/wp-content/themes/health.google.php
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[David Bicking]

On Tue, 1/28/14, Igor Tandetnik <i...@tandetnik.org> wrote:

 Subject: Re: [sqlite] (no subject)
 To: sqlite-users@sqlite.org
 Date: Tuesday, January 28, 2014, 2:41 PM
 
 On 1/28/2014 2:26 PM,
 David Bicking wrote:
 > I have two tables:
 >
 > ARB
 >   KEY (PRIMARY KEY)
 >   ASSIGN (NOT NECESSARILY
 UNIQUE)
 >
 > DMC
 >   KEY (NOT UNIQUE)
 >   ASSIGN (NOT UNIQUE)
 >   VALUE
 >
 > I need to report all the records from ARB,
 and sum up the values if the keys match OR if the keys
 don't match, then sum up the values where the ASSIGN
 matches, but only if the ASSIGN is unique in ARB.
 >
 > SELECT ARB.KEY
 > , ARB.ASSIGN
 > ,
 COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY =
 ARB.KEY)
 >                   
     , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN =
 ARB.ASSIGN AND ...), 0)
 > FROM ARB;
 >
 > I can't think of
 >what to put after that AND
 
 >Something like this perhaps:
 
 >and 1 = (select count(*) from
 >ARB t2 where t2.ASSIGN=ARB.ASSIGN)
 
> Igor Tandetnik

Thanks.

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 1/28/2014 2:26 PM, David Bicking wrote:

I have two tables:

ARB
  KEY (PRIMARY KEY)
  ASSIGN (NOT NECESSARILY UNIQUE)

DMC
  KEY (NOT UNIQUE)
  ASSIGN (NOT UNIQUE)
  VALUE

I need to report all the records from ARB, and sum up the values if the keys 
match OR if the keys don't match, then sum up the values where the ASSIGN 
matches, but only if the ASSIGN is unique in ARB.

SELECT ARB.KEY
, ARB.ASSIGN
, COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY)
   , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = 
ARB.ASSIGN AND ...), 0)
FROM ARB;

I can't think of what to put after that AND


Something like this perhaps:

and 1 = (select count(*) from ARB t2 where t2.ASSIGN=ARB.ASSIGN)

--
Igor Tandetnik

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

[sqlite] (no subject)

[David Bicking]

I have two tables:

ARB 
 KEY (PRIMARY KEY)
 ASSIGN (NOT NECESSARILY UNIQUE)

DMC
 KEY (NOT UNIQUE)
 ASSIGN (NOT UNIQUE)
 VALUE

I need to report all the records from ARB, and sum up the values if the keys 
match OR if the keys don't match, then sum up the values where the ASSIGN 
matches, but only if the ASSIGN is unique in ARB.

SELECT ARB.KEY
, ARB.ASSIGN
, COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY)
  , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = 
ARB.ASSIGN AND ...), 0)
FROM ARB;

I can't think of what to put after that AND

I don't think it matters, but I simplified things up there. DMC is actually a 
view of a table with KEY/ASSIGN and another with KEY/VALUE.

Any help is appreciated.

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Paul Sanderson]

That worked thanks Igor.


On 16 May 2013 15:33, Paul Sanderson  wrote:

> Rob yes thats correct
>
> Igor - thanks I'll give that a go
>
>
>
> On 16 May 2013 14:51, Rob Richardson  wrote:
>
>> First idea: include a subject line.
>>
>> I'm not 100% clear on your message.  You said:
>> " For those entries in table1 where there is a null in t2..."
>> I'm guessing you wanted to say:
>> " For those entries in table1 where there is a null in t1..."
>>
>> Is that right?
>>
>> RobR
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Paul Sanderson]

Rob yes thats correct

Igor - thanks I'll give that a go



On 16 May 2013 14:51, Rob Richardson  wrote:

> First idea: include a subject line.
>
> I'm not 100% clear on your message.  You said:
> " For those entries in table1 where there is a null in t2..."
> I'm guessing you wanted to say:
> " For those entries in table1 where there is a null in t1..."
>
> Is that right?
>
> RobR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Rob Richardson]

First idea: include a subject line.

I'm not 100% clear on your message.  You said:
" For those entries in table1 where there is a null in t2..."
I'm guessing you wanted to say:
" For those entries in table1 where there is a null in t1..."

Is that right?

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 5/16/2013 9:29 AM, Paul Sanderson wrote:

I have two tables of the form

create table1 (id1 int, t1 text)
create table2 (id2 int unique, t2 text)

For those entries in table1 where there is a null in t2 I want to copy the
corresponding entry from table2


update table1 set t1=(select t2 from table2 where id2=id1)
where t1 is null;

--
Igor Tandetnik

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

[sqlite] (no subject)

[Paul Sanderson]

I have two tables of the form

create table1 (id1 int, t1 text)
create table2 (id2 int unique, t2 text)

the data in table 1 is such that some values for t1 are NULL
ID1 can contain duplicates

ID2 is unique and for every instance of ID1 in table1 there will be a
corresponding ID2 entry in table2
t2 in table 2 is always populated with a string

For those entries in table1 where there is a null in t2 I want to copy the
corresponding entry from table2


any ideas?

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

[sqlite] (no subject)

[michael walmsley]

Hi all

Im getting the following exception when I try to open a connection to
a SQLite db:

System.TypeLoadException: Requested Windows Runtime type
'Sqlite.Sqlite3' is not registered. --->
System.Runtime.InteropServices.COMException: Class not registered
(Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
Result StackTrace:  
at System.StubHelpers.StubHelpers.GetWinRTFactoryObject(IntPtr pCPCMD)
   at Sqlite.Sqlite3.sqlite3_open_v2(String filename, Database& db,
Int32 flags, String zVfs)
   at MSI.Framework.DataAccess.SQLite.SQLite3.Open(String filename,
Database& db, Int32 flags, IntPtr zVfs)
   at MSI.Framework.DataAccess.SQLite.SQLiteConnection..ctor(String
databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks)
   at PhoneTests.UnitTest1.CreateATable()

My situation is:

im using sqlite-net-wp8
and sqllite as downloaded from nuget
this produced a .net wrapper called SQLite.cs and SQLiteAsync.cs

and im calling:
using (var conn = new SQLiteConnection(@"E:\TEMP\TEST.DB",
SQLiteOpenFlags.ReadWrite))

Is this enough information or not?

-- 
Kind Regards, Michael Grant Walmsley
E: mgw...@gmail.com
P: 0412886788
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Paul Sanderson]

This makes a huge difference in speed thanks


>>Also, you can write the query a bit more compactly:

>>select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in
>>(select md5 from rtable where isgraphic = 1 and vsc = 0);


On 31 January 2013 19:54, Igor Tandetnik  wrote:

> On 1/31/2013 2:33 PM, Paul Sanderson wrote:
>
>> My query is
>>
>> select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not
>> exists (select md5 fr
>> om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0);
>>
>> explain query plan and explain have been run on the table with the results
>> below. Any ideas where and how I can improve performance?
>>
>
> An index on rtable(md5) should help. If you do have one, and it doesn't
> get picked, try changing "isgraphic = 1" to "+isgraphic = 1" in the inner
> select (the unary plus operator makes the index on isgraphic inapplicable,
> hopefully steering the query planner towards a more helpful index).
>
> Also, you can write the query a bit more compactly:
>
> select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in
> (select md5 from rtable where isgraphic = 1 and vsc = 0);
>
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Simon Slavin]

On 31 Jan 2013, at 8:58pm, Simon Slavin  wrote:

> Depending on how chunky the values are in each column, a good index for this 
> would be an index on (md5,isgraphic,vsc).

Sorry, that should be one index on (isgraphic,vsc), I think.

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

Re: [sqlite] (no subject)

[Simon Slavin]

On 31 Jan 2013, at 8:48pm, Paul Sanderson  wrote:

> Thanks all
> 
> All columns in the query are indexed.

That does not do you much good.  Each SELECT can use only one index at a time.  
So if you have one index per column the query uses an index on, say, isgraphic, 
then it will have to search every value in vsc itself.

The way to create indexes is to figure out one index that's useful for each 
SELECT.  Here's the command you want to speed up:

> My query is
> 
> select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not
> exists (select md5 fr
> om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0);

Depending on how chunky the values are in each column, a good index for this 
would be an index on (md5,isgraphic,vsc).  Try creating this index and see if 
it helps.

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 1/31/2013 2:33 PM, Paul Sanderson wrote:

My query is

select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not
exists (select md5 fr
om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0);

explain query plan and explain have been run on the table with the results
below. Any ideas where and how I can improve performance?


An index on rtable(md5) should help. If you do have one, and it doesn't 
get picked, try changing "isgraphic = 1" to "+isgraphic = 1" in the 
inner select (the unary plus operator makes the index on isgraphic 
inapplicable, hopefully steering the query planner towards a more 
helpful index).


Also, you can write the query a bit more compactly:

select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in
(select md5 from rtable where isgraphic = 1 and vsc = 0);

--
Igor Tandetnik

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

Re: [sqlite] (no subject)

[Simon Slavin]

On 31 Jan 2013, at 7:33pm, Paul Sanderson  wrote:

> explain query plan and explain have been run on the table with the results
> below. Any ideas where and how I can improve performance?

Can you show us the indexes you've created on rtable ?

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

Re: [sqlite] (no subject)

[Rui Maciel]

On 09/05/2012 06:36 PM, Arbol One wrote:

That would be the C++ Standard Template Library
http://en.wikipedia.org/wiki/Standard_Template_Library

I am learning the library and as a student I always have questions.


The STL isn't necessarily a project, as it is defined in the C++ 
standard and therefore is a part of the C++ programming language.


If you are looking for public forums where the STL is discussed, you 
could give Usenet's comp.lang.c++ a try.  If you don't have a usenet 
client around, here's a google news link:


https://groups.google.com/forum/?fromgroups=#!forum/comp.lang.c++


Hope this helps,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Igor Tandetnik]

Arbol One  wrote:
> Does anyone know if there is a mailing list for the STL project?

Which of these do you mean by STL?

http://en.wikipedia.org/wiki/Standard_Template_Library
http://en.wikipedia.org/wiki/State_logic
http://en.wikipedia.org/wiki/Subtitle_(captioning)
http://en.wikipedia.org/wiki/STL_(file_format)
http://en.wikipedia.org/wiki/Statement_List

Also, how is your question related to SQLite?
-- 
Igor Tandetnik

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

[sqlite] (no subject)

[Arbol One]

Does anyone know if there is a mailing list for the STL project?

 

This e-mail is for the sole use of the intended recipient and may contain
confidential or privileged information. Unauthorized use of its contents is
prohibited. If you have received this e-mail in error, please notify sender
immediately via return e-mail and then delete the original e-mail.

 

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

Re: [sqlite] (no subject)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/07/12 17:54, Jay A. Kreibich wrote:
> Except that adds about 10x the workload for the moderators.  I'd rather
> have the SQLite team working on code, even if it means suffering the
> occasional spam message.

Who says the SQLite team have to be the moderators?

With Google Groups there is a web page with all pending messages shown and
the actions available.  Anyone with access to the page can moderate the
messages.

Heck I'd be happy to do some of the moderation.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlAHkaEACgkQmOOfHg372QSFXwCg1boCVH5leBJa1jMaxIFMeg/J
ByAAoM+JPb2pyZwAsDvsmgS46BJ1Erfx
=WFbu
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Jay A. Kreibich]

On Wed, Jul 18, 2012 at 02:10:52PM -0700, Roger Binns scratched on the wall:
> On 18/07/12 13:00, Richard Hipp wrote:
> > We have taken to requiring moderator approval before accepting new
> > members onto this mailing list.

> A better approach as used elsewhere (eg Google & Yahoo groups) is that the
> first ~10 messages from someone are moderated.  (The moderator can put
> that person on the approved/banned list early.)

  Except that adds about 10x the workload for the moderators.  I'd
  rather have the SQLite team working on code, even if it means
  suffering the occasional spam message.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 18/07/12 13:00, Richard Hipp wrote:
> We have taken to requiring moderator approval before accepting new
> members onto this mailing list.

A better approach as used elsewhere (eg Google & Yahoo groups) is that the
first ~10 messages from someone are moderated.  (The moderator can put
that person on the approved/banned list early.)

Note that this applies even if someone joins, doesn't post for a year and
then sends something.  It could be legit or their account could have been
hacked.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlAHJlMACgkQmOOfHg372QSQdACg21+RvVpBGFHMSTfQdMc6tOnu
oMQAnjI1Ca/0RFjgMfIVqrtQTyRWp5h+
=d35G
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Richard Hipp]

For the removal of doubt:

We have taken to requiring moderator approval before accepting new members
onto this mailing list.  And even so spam like that shown below sometime
leaks through.  There is an arms race on, with spammers getting ever more
sophisticated in their attacks.  Sorry for the noise.

"dragonslayer2k" has now been banished from the mailing list, of course.
Not that it matters, since dragonslayer2k was probably a throw-away account
anyhow

On Wed, Jul 18, 2012 at 3:40 PM, Danny  wrote:

>
> http://www.searvhlands.com/blog/wp-content/plugins/zkuaufaainv/shop.php?cup209.png
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[James Brison]

http://en.shijingsteel.com/pptlr.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Bageesh.M.Bose]

Any one please tell me How to use triggers in sqlite using C...
-- 
With Regards,
*Bageesh.M.Bose*
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Pavel Ivanov]

On Tue, Jun 12, 2012 at 8:32 AM, Igor Tandetnik  wrote:
> Nicholas Thompson  wrote:
>> Is it possible to safely use multiple threads in a process with each
>> thread making
>> its own connection to the same database
>>
>> rc = sqlite3_open("file::memory:?cache=shared", );
>
> Every time you connect to a :memory: database, a new in-memory database is 
> created. There ain't no such thing as two connections to the same memory 
> database.

Igor,

This comment will soon become out-of-date. See
http://www.sqlite.org/draft/releaselog/3_7_13.html.


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

Re: [sqlite] (no subject)

[Igor Tandetnik]

Nicholas Thompson  wrote:
> Is it possible to safely use multiple threads in a process with each
> thread making
> its own connection to the same database
> 
> rc = sqlite3_open("file::memory:?cache=shared", );

Every time you connect to a :memory: database, a new in-memory database is 
created. There ain't no such thing as two connections to the same memory 
database.
-- 
Igor Tandetnik

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

Re: [sqlite] (no subject)

[Dan Kennedy]

On 06/12/2012 05:41 PM, Nicholas Thompson wrote:

Hi, I am new to SQLITE

Would be using Microsoft VS 2008&  2010

Is it possible to safely use multiple threads in a process with each
thread making
its own connection to the same database

rc = sqlite3_open("file::memory:?cache=shared",);


Should be safe, yes. Assuming you are using the library in either
"multi-thread" or "serialized" (the default) mode.

  http://www.sqlite.org/threadsafe.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Nicholas Thompson]

Hi, I am new to SQLITE

Would be using Microsoft VS 2008 & 2010

Is it possible to safely use multiple threads in a process with each
thread making
its own connection to the same database

rc = sqlite3_open("file::memory:?cache=shared", );

ie. does sqlite implement an alternative to f_lock especially for
:memory: databases.

Many thanks Nick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Werner Smit]

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

Re: [sqlite] (no subject)

[Richard Hipp]

On Wed, Jun 6, 2012 at 1:08 AM, vallur raghava reddy <
vallur.raghavare...@gmail.com> wrote:

> Hi,
> I have few queries regarding the SQLite. I have listed them below please go
> through them and give your answers. Thanks
> Say if I am working on a DB with 3 tables with 100 records each and each
> records with some 5 fields of data.Each field is varchar(20)
>
>  1. What will be the Stack Size?
>

SQLite has been used in Windows device drivers, with a maximum stack frame
size of 4K and a practical upper bound on the stack size of 2K.  More space
than this might be needed depending on what SQL you run.


>  2. What will be the Heap Size?
>

Thre is a tradeoff between memory and speed.  The more heap space you give
the SQLite, the faster it will run.  You can run it in less than 100K or
200K of heap in the default configuration.  By tweaking the configuration,
you can probably run it in 50K or less of heap.  Most people are more
interested in making it run faster than in minimizing the heap memory
usage, and thus give it multiple megabytes to use.

There are features in SQLite that let you control the amount of heap memory
usage.  In particular, you can configure SQLite in such as way that you
hand it a single big chunk of heap memory at start time and it will never
use any memory other than that one chunk you give it.


>  3. What is the max no. of file pointers that will be used at a point of
>  time?
>

Depends on what you are doing.  There are at least 2 per database file.
Note that all your TEMP tables are stored in a separate database file.
Also, SQLite sometimes has to manifest a view and that manifestation is
stored in a separate database file.  3 file descriptors per database file
are required in WAL mode.


>  4. What is the max no. of Mutexes that will be used at a point of time?
>

5 + N + M where N is the number of open database connections and M is the
number of open database files.


>  5. Is there a tool to calculate these?
>
> Please bare my ignorance if any and answer these questions.
>
> Thanks Again,
>
> Raghava Reddy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Eduardo Morras]

At 07:08 06/06/2012, you wrote:

Hi,
I have few queries regarding the SQLite. I have listed them below please go
through them and give your answers. Thanks
Say if I am working on a DB with 3 tables with 100 records each and each
records with some 5 fields of data.Each field is varchar(20)

  1. What will be the Stack Size?


Depends on what are you doing with the db, it vary with time.


  2. What will be the Heap Size?


Again, depends on what are you doing.


  3. What is the max no. of file pointers that will be used at a point of
  time?


If I Remember Correctly (IIRC) it's 2 per db file. But it was on 
3.2.x times. Keep in mind that sqlite can access and manage up to 32 
db files using ATTACH.



  4. What is the max no. of Mutexes that will be used at a point of time?


Don't know, why do you need it?


  5. Is there a tool to calculate these?


Compile with debug mode on and use gdb.


Please bare my ignorance if any and answer these questions.

Thanks Again,

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



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

[sqlite] (no subject)

[vallur raghava reddy]

Hi,
I have few queries regarding the SQLite. I have listed them below please go
through them and give your answers. Thanks
Say if I am working on a DB with 3 tables with 100 records each and each
records with some 5 fields of data.Each field is varchar(20)

  1. What will be the Stack Size?
  2. What will be the Heap Size?
  3. What is the max no. of file pointers that will be used at a point of
  time?
  4. What is the max no. of Mutexes that will be used at a point of time?
  5. Is there a tool to calculate these?

Please bare my ignorance if any and answer these questions.

Thanks Again,

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

Re: [sqlite] (no subject)

[Simon Slavin]

On 4 Jun 2012, at 6:39pm, Jörgen Hägglund  wrote:

> What I am trying to accomplish is an update from the source data. If the 
> entry is already there, just ignore it. Otherwise add the new data.

If any unique key (including the primary key) of the existing row and the new 
entry match, then you can avoid getting an error by using

INSERT OR IGNORE …

This means that if the INSERT would normally result in an error message about 
duplicate key values, SQLite does not do the new INSERT, but returns an 'OK' 
result code, as if the INSERT had worked.

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

Re: [sqlite] (no subject)

[Jörgen Hägglund]

Hi!
Thank You for replying Stefanos!

Unfortunately, the errors were typos on my part. Sorry about that.
As I wrote, everything works fine the first time around.
What I am trying to accomplish is an update from the source data. If the 
entry is already there, just ignore it. Otherwise add the new data.


I'm using version 3.7.12.

Theories anyone?

Regards,
/Jörgen

 skrev 1970-01-01 01:00:


You are missing parentheses here:

create table if not exists a (id integer primary key autoincrement, a 
text unique, b default current_timestamp);
create table if not exists b (id integer primary key autoincrement, a 
integer references a(id) on update cascade on delete cascade, b text, 
c text, d text, e text, f real, g text, h text, unique (a, b) on 
conflict ignore);
create table if not exists c (id integer primary key autoincrement, a 
integer, b integer references b(id) on update cascade on delete 
cascade, c integer, d text, e integer, f text, g blob, unique (a, b, 
c) on conflict ignore);



Also here you miss 'a'

insert into c (a, b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g);


Also what sqlite version are you using?

I have run this with the latest available version (3.7.12.1) under 
SQLite Manager (Firefox / Iceweasel extension).


If you need any further help, please let me know.

Cheers.

Stefanos





From: Jörgen Hägglund jor...@sirlucifer.mine.nu
To: sqlite-users@sqlite.org
Sent: Sunday, June 3, 2012 11:10 PM
Subject: [sqlite] Foreign key constraint failed

Hi all!
I have just recently discovered the strengths of foreign keys but now, 
I've been banging my head against the wall for a couple of days.

I just can't figure out what is causing the error or how to get around it.
Here's the specs (simplified) of my DB;

create table if not exists a (id integer primary key autoincrement, a 
text unique, b default current_timestamp);
create table if not exists b (id integer primary key autoincrement, a 
integer references a(id) on update cascade on delete cascade, b text, 
c text, d text, e text, f real, g text, h text, unique (a, b) on 
conflict ignore;
create table if not exists c (id integer primary key autoincrement, a 
integer, b integer references b(id) on update cascade on delete 
cascade, c integer, d text, e integer, f text, g blob, unique (a, b, 
c) on conflict ignore;


And the insert query;

insert into c (b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g);

The problem is that I get a "Foreign key constraint failed" error when 
I try to insert the same data twice. The first time it works as it 
should and the data is inserted correctly. But, the second time, I get 
the exception.


Is there anyone out there who's willing (and able) to help me? And 
hopefully explain what I'm doing wrong.


Regards,
/Jörgen

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







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

Re: [sqlite] (no subject)

[Kees Nuyt]

On Fri, 25 May 2012 14:56:24 -0400, Frank Chang
 wrote:

>
> http://swatim dot sw dot ohost dot de/gertycamp3.html

Poisonous link, don't click.

Frank, please perform a rigorous virus check on your machine(s), you may
have been infected by a bot.


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 5/24/2012 11:46 AM, IQuant wrote:

ie.  I have a table with 1,000,000 + records collecting real time
stats from many devices with many columns but the main ones of
interest are 'timestamp', 'latency' and 'DeviceID'.

2012-05-01 13:12:11.103  Null   14356
2012-05-01 13:12:11.103  Null   14372
2012-05-01 13:12:11.103  Null   4356
2012-05-01 13:12:07.103  Null   14356
.
2012-05-01  13:12:11.221 Null14356

I want to update the latency field for each record with the latest
previous timestamp for the same DeviceID.


update MyTable set latency = (
  select max(t2.timestamp) from MyTable t2
  where t2.DeviceId = MyTable.DeviceId and t2.timestamp < 
MyTable.timestamp);


--
Igor Tandetnik

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

[sqlite] (no subject)

[IQuant]

How can I construct a update query to calculate and set a record field
"latency" with the difference between "timestamps" by "deviceid"?
Appears sqlite doesn't support lag and lead.

ie.  I have a table with 1,000,000 + records collecting real time
stats from many devices with many columns but the main ones of
interest are 'timestamp', 'latency' and 'DeviceID'.

2012-05-01 13:12:11.103  Null   14356
2012-05-01 13:12:11.103  Null   14372
2012-05-01 13:12:11.103  Null   4356
2012-05-01 13:12:07.103  Null   14356

2012-05-01  13:12:11.221 Null14356

The data is collected inconsistently...  many times out of sequence

I want to update the latency field for each record with the latest
previous timestamp for the same DeviceID.

Run this update query every time new data is appended to the table.
Periodically recalc the entire table if an out of sequence file is
processed or at minimum recalc from the earliest out of sequence time
to present.

I can do this fairly easily in excel but am struggling in sql/sqlite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Levi Haskell]

http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php
5/7/2012 6:58:21 AM   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Levi Haskell]

http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php
5/7/2012 6:58:21 AM   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Dawit Tegene]

Hi Simon, thanks for the reply.
 I am using the C libraries as described in the link that you mentioned. I am 
not able to use a shell(or any other) [recompiled tool as the version of SQLite 
that I am using to create has the SEE (encryption) enabled. Whatever tool that 
I use I have to compile it with the licence and encryption key that is specific 
to our licence. At any rate, I have compiled a sql browser tool that uses the 
sql library with the encryption enabled, and I am having problem executing the 
SQL statemsnts (not all, but aggretate functions). Thanks for your help.> From: 
slav...@bigfraud.org
> Date: Wed, 2 May 2012 11:57:40 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] (no subject)
> 
> 
> On 2 May 2012, at 8:55am, Octet Computech <dawitteg...@hotmail.com> wrote:
> 
> > I have an application that creates
> > and saves some data in a SQLite database. I am using SQLite v 3.6.14, 
> > accessing
> > it through the native dll form a C# WPF application. For some reason that I
> > cant figure out am getting a corrupted file with some strange 
> > characteristics
> > in some rare instances.
> 
> Are you calling the SQLite C library functions as described on this page:
> 
> <http://www.sqlite.org/c3ref/funclist.html>
> 
> or are you using some other library to execute SQLite commands ?  If you're 
> using another library, which library are you using ?
> 
> > Second I am able to open the file
> > with SQLite browser application (specially compiled for my license of SEE of
> > course), but there are some Sql statements that do not work.
> 
> Please download the shell tool for your OS from
> 
> <http://www.sqlite.org/download.html>
> 
> and try accessing the database using that shell tool instead of whatever 
> browser application you're using.  Does the shell tool still suggest the same 
> corruption as your browser app ?  If there is no shell tool for your OS, you 
> can transfer a copy of the database to a standard computer.  The file format 
> is identical on all platforms.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Simon Slavin]

On 2 May 2012, at 8:55am, Octet Computech  wrote:

> I have an application that creates
> and saves some data in a SQLite database. I am using SQLite v 3.6.14, 
> accessing
> it through the native dll form a C# WPF application. For some reason that I
> cant figure out am getting a corrupted file with some strange characteristics
> in some rare instances.

Are you calling the SQLite C library functions as described on this page:



or are you using some other library to execute SQLite commands ?  If you're 
using another library, which library are you using ?

> Second I am able to open the file
> with SQLite browser application (specially compiled for my license of SEE of
> course), but there are some Sql statements that do not work.

Please download the shell tool for your OS from



and try accessing the database using that shell tool instead of whatever 
browser application you're using.  Does the shell tool still suggest the same 
corruption as your browser app ?  If there is no shell tool for your OS, you 
can transfer a copy of the database to a standard computer.  The file format is 
identical on all platforms.

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

[sqlite] (no subject)

[Octet Computech]

Here is the problem that I am having
with SQLite with encryption extension enabled, and I need help.

I have an application that creates
and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing
it through the native dll form a C# WPF application. For some reason that I
cant figure out am getting a corrupted file with some strange characteristics
in some rare instances.

First of all there some significant
rows of a table are missing and another table whose structure is intact has all
the rows empty.

Second I am able to open the file
with SQLite browser application (specially compiled for my license of SEE of
course), but there are some Sql statements that do not work. A simple select on
the partially filled table works, for example, but any select statement that
involves aggregate functions (count, average, max min, distinct etc...) do not
work.

Third, I open the file with a text
editor and I see that a significant chunk of the file towards the end is filled
with the NUL character which is unlike all the other files that are not
corrupted.

This is the way my C# application
works with the files: 1. Start transaction


 Write to table 1
 Write to table 2
 Write to table 3
 Write around 80,000 rows in table 4
 Write data to table 5 based on aggregate data from
 table 4
 Commit transaction


Table 4 has thousands of rows
missing and table 5 is empty all together. Before someone states the obvious, I
can see that there is no surprise that table 5 is empty since aggregate
functions are not really working on table 4. But the question is what can lead
to this kind of corruption where data write can stop after some thousands of
rows and aggregate functions not working.

Does anyone have any idea why this
is happening? My problem is further complicated by the fact that I am not able
to reproduce the problem for the life of me; it only happened to one of our
clients, and they are not able to tell me anything that is useful to determine
the sequence of events that lead to this file corruption.

I do try to handle all exceptions in
the application, but when this problem happens no exceptions are thrown by the
application or the underlying SQLite library.Thanks for the help. Dawit



 

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

[sqlite] (no subject)

[Darko Filipovic]

http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html;> 
http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Allen Fowler]

http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html;>
 
http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Kees Nuyt]

On Wed, 4 Jan 2012 17:21:18 +0530, Tarun 
wrote:

>Hi All,
>
>I am running program in which I have created SQLite DB file using:
>sqlite3_open_v2()
>
>configured it:
>
>PRAGMA journal_mode = WAL
>PRAGMA synchronous=OFF
>
>Then created one table into SQLiteDB by using sqlite3_exec() in which
>I passed SQL command of creating table.
>
>After configuration, SQLite file size =  1024bytes
>
>Then I inserted 500 records into table using INSERT sql command in
>sqlite3_exec()
>SQLite file size =  69632
>
>Then I did deletion of all 500 records from table using DELETE sql query in
>sqlite3_exec()
>SQLite file size =  113664
>
>I don't know why file size is increased on deletion ??
>
>Then I ran VACUUM command.
>
>SQLite file size remained  113664, it did not get reduced.
>
>But when I closed my application working on SQLite DB file, file size
>is reduced to 3072.
>
>I did not understand this behavior. I think as soon as I run VACUUM
>command, it should reduce file size at that moment.
>Please share correct behavior that should happen in this scenario shared above.
>
>
>Waiting for your response, I am stuck in my work.

Perhaps 
 http://www.sqlite.org/pragma.html#pragma_incremental_vacuum
is what you are looking for?

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] (no subject)

[Stephan Beal]

On Wed, Jan 4, 2012 at 12:51 PM, Tarun  wrote:

> Waiting for your response, I am stuck in my work.
>

The physical size of the db file is an implementation detail, not a
well-published behaviour with concrete reproducible results, and should in
no way affect your use of sqlite3 (unless of course you are on a very
space-constrained device).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Tarun]

Hi All,

I am running program in which I have created SQLite DB file using:
sqlite3_open_v2()

configured it:

PRAGMA journal_mode = WAL
PRAGMA synchronous=OFF

Then created one table into SQLiteDB by using sqlite3_exec() in which
I passed SQL command of creating table.

After configuration, SQLite file size =  1024bytes

Then I inserted 500 records into table using INSERT sql command in
sqlite3_exec()
SQLite file size =  69632

Then I did deletion of all 500 records from table using DELETE sql query in
sqlite3_exec()
SQLite file size =  113664

I don't know why file size is increased on deletion ??

Then I ran VACUUM command.

SQLite file size remained  113664, it did not get reduced.

But when I closed my application working on SQLite DB file, file size
is reduced to 3072.

I did not understand this behavior. I think as soon as I run VACUUM
command, it should reduce file size at that moment.
Please share correct behavior that should happen in this scenario shared above.


Waiting for your response, I am stuck in my work.


-- 
Thanks and Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Pratibha Kulkarni]

Thanks,
Pratibha Kulkarni
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Tim Streater]

On 31 Aug 2011 at 23:24, Igor Tandetnik  wrote: 

> On 8/31/2011 5:56 PM, Tim Streater wrote:
>> In the above, each database is newly created as shown. What I had
>> forgotten to do was to create the "test" table in the second database
>> before copying the data. What seems to happen is that, lacking a
>> "test" table in the test2 database, SQLite appears to assume that I
>> must mean the "test" table in the test1 database - it tries to copy
>> data from the table into itself and so gets the error above.
>
> Yes. This is documented behavior - see http://sqlite.org/lang_attach.html.

Thanks for that helpful link. I'm sure I've looked at it before, but didn't 
register all the details of how it works. Now I see there's a way of referring 
to the original database as main, I'll use fully qualified table names wherever 
I have attached databases.

>> Is this reasonable behaviour? I might have expected to have a "no such table"
>> error.
>
> Which part of the documentation might have led you to expect that?

No part :-)

Sorry for the lack of subject line originally BTW.

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 8/31/2011 5:56 PM, Tim Streater wrote:

In the above, each database is newly created as shown. What I had
forgotten to do was to create the "test" table in the second database
before copying the data. What seems to happen is that, lacking a
"test" table in the test2 database, SQLite appears to assume that I
must mean the "test" table in the test1 database - it tries to copy
data from the table into itself and so gets the error above.


Yes. This is documented behavior - see http://sqlite.org/lang_attach.html.


Is this reasonable behaviour? I might have expected to have a "no such table" 
error.


Which part of the documentation might have led you to expect that?
--
Igor Tandetnik

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

Re: [sqlite] (no subject)

[Jay A. Kreibich]

On Wed, Aug 31, 2011 at 10:56:00PM +0100, Tim Streater scratched on the wall:

> In the above, each database is newly created as shown. What I had
> forgotten to do was to create the "test" table in the second
> database before copying the data. What seems to happen is that,
> lacking a "test" table in the test2 database, SQLite appears to
> assume that I must mean the "test" table in the test1 database -
> it tries to copy data from the table into itself and so gets the
> error above.
> 
> Is this reasonable behaviour? I might have expected to have a
> "no such table" error.

  Reasonable or not, it is the defined behavior:

http://sqlite.org/lang_attach.html

Tables in an attached database can be referred to using the syntax
database-name.table-name. If the name of the table is unique across
all attached databases and the main and temp databases, then the
database-name prefix is not required. If two or more tables in
different databases have the same name and the database-name prefix
is not used on a table reference, then the table chosen is the one
in the database that was least recently attached.

  In other words, SQLite will generally search the temp database, the
  main database, and then all attached databases in index order.  This
  brings up some odd edge cases, as the temp database is searched
  before the main database, even though the main database has a lower
  index, but it generally works as expected.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Tim Streater]

Today when trying to copy from one database to another, I had the following 
error (simplified example below):

  Second-Mini% sqlite3 test1
  sqlite> create table test (absid integer primary key, otherfield integer);
  sqlite> insert into test (absid,otherfield) values (null, 10);
  sqlite> insert into test (absid,otherfield) values (null, 20);
  sqlite> select * from test;
  absid | otherfield
  --+---
  1 | 10
  2 | 20
  sqlite> ^D

  Second-Mini% sqlite3 test2
  sqlite> attach database test1 as src;
  sqlite> insert into test select * from src.test;
  Error: PRIMARY KEY must be unique
  sqlite> 

In the above, each database is newly created as shown. What I had forgotten to 
do was to create the "test" table in the second database before copying the 
data. What seems to happen is that, lacking a "test" table in the test2 
database, SQLite appears to assume that I must mean the "test" table in the 
test1 database - it tries to copy data from the table into itself and so gets 
the error above.

Is this reasonable behaviour? I might have expected to have a "no such table" 
error.

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

[sqlite] (no subject)

[john darnell]

I am attempting to open an SQLite database on the Mac (OSX Snow Leopard) and am 
getting an error.  This is the code I am using:


   char  DBEnginePath[1000];

   strcpy(DBEnginePath, "Macintosh HD:Applications:Adobe InDesign 
CS5:Plug-Ins:WPC_ID:IndexData.db");
   fprintf(stderr, "%s\n", DBEnginePath);   
 //  Sends correct path to stderr for verification.
   Result = sqlite3_open_v2(DBEnginePath, _ptr, SQLITE_OPEN_READONLY, NULL); 
 //  Errors out here.

   const char *msg = sqlite3_errmsg(db_ptr);
   fprintf(stderr, "Here's the SQLite error message: %s\n", msg);   
 // Sent to stderr: "Unable to open database file."


I have verified that the file exists on the path described above.  What am I 
doing wrong?

TIA!

R,
John A.M. Darnell
Senior Programmer
Walsworth Publishing Company
Brookfield, MO
John may also be reached at 
johnamdarn...@gmail.com

Trivia question Trivia question:  In The Lord of the Rings,Leglolas was a 
prince among the Silvan Elves.  What was the name of his father the King?  For 
extra credit, what was his surname?



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

Re: [sqlite] (no subject)

[Jean-Christophe Deschamps]

>Ah, there is a way to write it so that you can have at most one of
>those constraints where only one makes sense, and not require a
>specific order of constraints, but it'd require listing all the
>possible orderings, which would be impractical.
>
>So if one wanted to enforce that there's at most one of such
>constraints then the best pace to do it in in sqlite3AddDefaultValue()
>and friends, rather than in the grammar.  But really, is it worth it?

No, certainly not. SQLite parser is fine like it is, no reason to bloat 
it with much fat just to flag ill constructs, particularily in this cas 
where there's no harm done.

Thanks Nico for looking and for your detailed explanation.

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

Re: [sqlite] (no subject)

[Nico Williams]

On Thu, May 19, 2011 at 4:28 PM, Nico Williams  wrote:
> However, I'm not sure how to write this such that there can be only
> one of those constraints of which there should be just one but without
> then imposing ordering on those constraints.  IMO there's no need to
> fix this.

Ah, there is a way to write it so that you can have at most one of
those constraints where only one makes sense, and not require a
specific order of constraints, but it'd require listing all the
possible orderings, which would be impractical.

So if one wanted to enforce that there's at most one of such
constraints then the best pace to do it in in sqlite3AddDefaultValue()
and friends, rather than in the grammar.  But really, is it worth it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Nico Williams]

On Thu, May 19, 2011 at 4:22 PM, Pavel Ivanov  wrote:
>> Is there a rationale for allowing such statements or is that an effect
>> of the 'Lite' nature?  (Note: I'm not complaining, just asking.)
>
> I believe that's an effect of the "typeless" design. As SQLite doesn't
> have strict type names for columns it accepts pretty much anything for
> that. So in your case it thinks that type of first column is "CHAR
> COLLATE NOCASE" and for second "INTEGER DEFAULT 1". What do those type
> names mean is up to you. :)

Not really, it seems to be an effect of the way the column constraint
grammar rules are written:

carg ::= CONSTRAINT nm ccons.
carg ::= ccons.
ccons ::= DEFAULT term(X).{sqlite3AddDefaultValue(pParse,);}
ccons ::= DEFAULT LP expr(X) RP.  {sqlite3AddDefaultValue(pParse,);}
ccons ::= DEFAULT PLUS term(X).   {sqlite3AddDefaultValue(pParse,);}
ccons ::= DEFAULT MINUS(A) term(X).  {
...
}
ccons ::= DEFAULT id(X).  {
...
}
...
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).{sqlite3AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
 {sqlite3AddPrimaryKey(pParse,0,R,I,Z);}
ccons ::= UNIQUE onconf(R).  {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0,0,0);}
ccons ::= CHECK LP expr(X) RP.   {sqlite3AddCheckConstraint(pParse,X.pExpr);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
 {sqlite3CreateForeignKey(pParse,0,,TA,R);}
ccons ::= defer_subclause(D).{sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C).{sqlite3AddCollateType(pParse, );}


However, I'm not sure how to write this such that there can be only
one of those constraints of which there should be just one but without
then imposing ordering on those constraints.  IMO there's no need to
fix this.

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

Re: [sqlite] (no subject)

[Pavel Ivanov]

> Is there a rationale for allowing such statements or is that an effect
> of the 'Lite' nature?  (Note: I'm not complaining, just asking.)

I believe that's an effect of the "typeless" design. As SQLite doesn't
have strict type names for columns it accepts pretty much anything for
that. So in your case it thinks that type of first column is "CHAR
COLLATE NOCASE" and for second "INTEGER DEFAULT 1". What do those type
names mean is up to you. :)


Pavel


On Thu, May 19, 2011 at 4:40 PM, Jean-Christophe Deschamps
 wrote:
> Anoher (silly) question about what SQLite considers valid input, again
> out of mere curiosity.
>
> A statement like:
>
> CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT
> 1 DEFAULT 2);
>
> doesn't cause any error: SQLite applies only the last constraint of
> each type, namely COLLATE BINARY and DEFAULT 2 in this case.
>
> Is there a rationale for allowing such statements or is that an effect
> of the 'Lite' nature?  (Note: I'm not complaining, just asking.)
>
> Is it OK to ignore all but the last constraint of each type when
> parsing that statement, just like SQLite currently does?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Jean-Christophe Deschamps]

Anoher (silly) question about what SQLite considers valid input, again 
out of mere curiosity.

A statement like:

CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT 
1 DEFAULT 2);

doesn't cause any error: SQLite applies only the last constraint of 
each type, namely COLLATE BINARY and DEFAULT 2 in this case.

Is there a rationale for allowing such statements or is that an effect 
of the 'Lite' nature?  (Note: I'm not complaining, just asking.)

Is it OK to ignore all but the last constraint of each type when 
parsing that statement, just like SQLite currently does?

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

[sqlite] (no subject)

[John Deal]

Hello All,

I have been using SQLite for a couple of years but have never posted to this 
list before.  I am sure my problem is common and am looking for ideas to solve 
it.

I have used SQLite extensively single-threaded with no problems (other than my 
own!).  I am currently working on another project adding SQLite functionality 
to a multi-threaded environment.  Here is my situation.

Ubuntu 10.04 64-bit.

Have used the SQLite3 library and compiled from source directly into the 
application.  Same results.

Multi-threaded compile flag configuration kept as the default and forced with 
sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported.  Open is 
via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE 
flags.  I have tried this with and without shared cache.

When I use the above configuration multi-threaded but using only one DB 
connection in which only one thread is accessing the DB at a time, it works 
fine for both read and write.  I use OS read/write mutex in which all threads 
must obtain a write lock to get at the DB effectively forcing only one thread 
accessing the DB at one time.

When I allow multiple readers with each thread using a different DB connection 
(open with the same flags) and each thread having exclusive use of its DB 
connection (no sharing of connections) and if more than one thread is reading 
the DB at the same time, the DB becomes locked for writing even when all the 
reads are finished.  The DB is locked, not the OS mutex.  There are no DB 
writes.  How can the DB be locked for writes in this situation?  I test this 
with the sqlite3 program and opening the database while the application is 
running and try to do an insert.

I have been working for weeks on this and I feel there must be something simple 
I am overlooking.  Thanks for any help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Patkó Last namesándor]

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

[sqlite] (no subject)

[Patko Sándor]

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

Re: [sqlite] (no subject)

[Simon Slavin]

On 21 Oct 2010, at 2:42pm, ""   wrote:

> please remove my name jbh...@bluefrog.com from the mailing list.

Please click on the link included at the end of every message on this list.

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

[sqlite] (no subject)

please remove my name jbh...@bluefrog.com from the mailing list.
Thank you.

79

AE5IL John Houston
www.rebuildinglostchurches.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[cmartin]

On Sat, 22 May 2010, Prakash Reddy Bande wrote:
> Me too, all attempts to unsubscribe have failed.

On Sat, 22 May 2010, pcvetsko...@yahoo.com wrote:
> please unuscribed pcvetko...@yahoo.com

Did you try this:

http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Enter the email address you subscribed with, click 'Unsubscribe or edit 
options', follow the directions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Prakash Reddy Bande]

Me too, all attempts to unsubscribe have failed. I have put a filter, but still 
my .pst is becoming big.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Pece Cvetkoski [pcvetko...@yahoo.com]
Sent: Saturday, May 22, 2010 10:49 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] (no subject)

please unuscribed pcvetko...@yahoo.com, you insoult my mail, and i want to 
unuscribe for your contacts, thanx



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

[sqlite] (no subject)

[Pece Cvetkoski]

please unuscribed pcvetko...@yahoo.com, you insoult my mail, and i want to 
unuscribe for your contacts, thanx


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

Re: [sqlite] (no subject)

[David Bicking]

Indeed, stupid typo in my sql.  Sorry for the noise and the lack of a subject 
on the subject line in my first email.

David

--- On Tue, 10/27/09, Pavel Ivanov <paiva...@gmail.com> wrote:

> From: Pavel Ivanov <paiva...@gmail.com>
> Subject: Re: [sqlite] (no subject)
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Date: Tuesday, October 27, 2009, 3:26 PM
> Something screwed up in your
> application:
> 
> sqlite> create table assets (Code Text, Acct1 Text,
> Acct2 Text);
> sqlite> insert into assets values ('C0', 'name1',
> 'name2');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite> insert into assets select 'C0', 'name1', 'name3'
> where not
> exists (select 1 from assets where Code='C0' and
> acct1='name1');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite>
> 
> 
> Pavel
> 
> On Tue, Oct 27, 2009 at 2:44 PM, David Bicking <dbic...@yahoo.com>
> wrote:
> > I have a table ASSETS with fields (Code Text, Acct1
> Text, Acct2 Text).
> > (There are other fields, and the primary key is a
> combination of 5 columns)
> >
> > For one code ('C0') I want to implement a rule that if
> I attempt to insert a combination of 'C0'/Acct1/Acct2 it
> will be ignored if the first two keys are already in the
> table. (for non-'C0' codes, this rule doesn't apply.)
> >
> > select * from assets;
> > Code   Acct1     Acct2
> > 'C0'   'name1'   'name2'
> >
> >
> > insert into assets values('C0', 'name1', 'name3');
>  -- ignore
> > insert into assets values('C0', 'name3', 'name4');
>  -- succeed
> > insert into assets values('C0', 'name1', 'name2');
>  -- ignore
> > insert into assets values('C1', 'name1', 'name2');
>  -- succeed
> >
> > I tried:
> >
> > insert into assets select 'C0', 'name1', 'name3' where
> not exists (select 1 from assets where Code='C0' and
> acct1='name1');
> >
> > but it went ahead and inserted the row anyway. My hope
> was that since the where clause was false, the select would
> return nothing and thus nothing would be inserted.
> >
> > Is there a way I can craft the insert to do this, or
> do I need to set up a trigger to ignore the insert? If the
> latter, how should the trigger read?
> >
> > Thanks,
> > David
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Pavel Ivanov]

Something screwed up in your application:

sqlite> create table assets (Code Text, Acct1 Text, Acct2 Text);
sqlite> insert into assets values ('C0', 'name1', 'name2');
sqlite> select * from assets;
C0|name1|name2
sqlite> insert into assets select 'C0', 'name1', 'name3' where not
exists (select 1 from assets where Code='C0' and acct1='name1');
sqlite> select * from assets;
C0|name1|name2
sqlite>


Pavel

On Tue, Oct 27, 2009 at 2:44 PM, David Bicking  wrote:
> I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
> (There are other fields, and the primary key is a combination of 5 columns)
>
> For one code ('C0') I want to implement a rule that if I attempt to insert a 
> combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are 
> already in the table. (for non-'C0' codes, this rule doesn't apply.)
>
> select * from assets;
> Code   Acct1     Acct2
> 'C0'   'name1'   'name2'
>
>
> insert into assets values('C0', 'name1', 'name3');  -- ignore
> insert into assets values('C0', 'name3', 'name4');  -- succeed
> insert into assets values('C0', 'name1', 'name2');  -- ignore
> insert into assets values('C1', 'name1', 'name2');  -- succeed
>
> I tried:
>
> insert into assets select 'C0', 'name1', 'name3' where not exists (select 1 
> from assets where Code='C0' and acct1='name1');
>
> but it went ahead and inserted the row anyway. My hope was that since the 
> where clause was false, the select would return nothing and thus nothing 
> would be inserted.
>
> Is there a way I can craft the insert to do this, or do I need to set up a 
> trigger to ignore the insert? If the latter, how should the trigger read?
>
> Thanks,
> David
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[David Bicking]

I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
(There are other fields, and the primary key is a combination of 5 columns)

For one code ('C0') I want to implement a rule that if I attempt to insert a 
combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are 
already in the table. (for non-'C0' codes, this rule doesn't apply.)

select * from assets;
Code   Acct1 Acct2
'C0'   'name1'   'name2'


insert into assets values('C0', 'name1', 'name3');  -- ignore
insert into assets values('C0', 'name3', 'name4');  -- succeed
insert into assets values('C0', 'name1', 'name2');  -- ignore
insert into assets values('C1', 'name1', 'name2');  -- succeed
 
I tried:

insert into assets select 'C0', 'name1', 'name3' where not exists (select 1 
from assets where Code='C0' and acct1='name1');

but it went ahead and inserted the row anyway. My hope was that since the where 
clause was false, the select would return nothing and thus nothing would be 
inserted.

Is there a way I can craft the insert to do this, or do I need to set up a 
trigger to ignore the insert? If the latter, how should the trigger read?

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Thompson, Nick (GE EntSol, Intelligent Platforms)]

Hi, (SQLite v3.6.17, on ARM with Linux 2.6.18)

I'm currently investigating the applicability of SQLite in an embedded
application. Part of this would be to store configuration data, for
which SQLite seems like a natural fit.

I also have a requirement for exchange of realtime data between
processes. The data is only stored in RAM (probably on a tmpfs
filesystem in Linux, so it can be shared) and, while atomic commits are
valuable, it is not important to be robust in the face of reboots.

Testing shows that I can commit writes at about 400 transactions per
second and read tables at about 1400 queries per second. I have tried
changing PRAGMA settings for journal_mode to OFF, synchronous OFF and
temp_store to 2. These seem to have a small impact (to about 410
commits/second).

PRAGMA journal_mode; gives no output though which makes me suspicious as
to whether it is doing anything.

The performance so far is perhaps adequate, but since it takes up almost
100% CPU to do it, it will of course tail off when I start to do
anything with the data.

Are there any other configuration options (per DB) that I should be
considering, or other tips that might be relevant?

Thanks,
Nick.


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