Re: [sqlite] Transaction inside transaction

[Igor Korot]

Dan,

On Sun, Oct 21, 2012 at 9:40 PM, Dan Kennedy  wrote:
> On 10/22/2012 11:34 AM, Igor Korot wrote:
>>
>> Hi, ALL,
>> Is it possible to have transaction inside transaction? Will it be
>> handled correctly?
>>
>> What I mean is: crate transaction 1 by issuing "BEGIN", create
>> transaction 2 by issuing "BEGIN".
>> Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
>> If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
>> If "COMMIT" is issued, continue transaction 1.
>
>
> See here:
>
>   http://www.sqlite.org/lang_savepoint.html
>
> They don't work exactly as you describe above, but might be
> close enough.

Looks like this is what I need.
However I am not sure why you said "They don't work exactly as described..."

Thank you.

>
> ___
> 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] Transaction inside transaction

[Dan Kennedy]

On 10/22/2012 11:34 AM, Igor Korot wrote:

Hi, ALL,
Is it possible to have transaction inside transaction? Will it be
handled correctly?

What I mean is: crate transaction 1 by issuing "BEGIN", create
transaction 2 by issuing "BEGIN".
Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
If "COMMIT" is issued, continue transaction 1.


See here:

  http://www.sqlite.org/lang_savepoint.html

They don't work exactly as you describe above, but might be
close enough.

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

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Dan Kennedy]

On 10/21/2012 03:48 PM, Imanuel wrote:

is there any way to have a fast SQLite
3.7.14.1 via DLL or at least to tell why it's that slow?


I don't think it's the dll itself that is the problem, just
something about the way it is used in Delphi. And I don't
actually know anything about Delphi..

Is the wrapper or the application issuing any PRAGMA statements
to SQLite? Does the wrapper intercept calls to the VFS interface?

Dan.





Imanuel


Am 18.10.2012 17:49, schrieb Imanuel:

No, I can't - 26s vs 15s (old vs new).

But when I run the test in my Delphi test application, 3.7.14.1 takes
285 seconds (tested again right now).
All the time, CPU usage is 25% (on a quad core).


This is my test code:

sqlite3_open('test.db', handle);
t0:=now();
sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
Namen(name)'), nil, nil, nil);
showmessage(floattostr((now()-t0)*86400));
sqlite3_close(handle);


The DLL is referenced in this unit:
https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas

I simply have replaced the dll without changing the linking source code
to test with 3.7.14.1 - I hope that's ok.

Imanuel




Am 18.10.2012 16:49, schrieb Dan Kennedy:

On 10/18/2012 03:32 PM, Imanuel wrote:

Ok, here it is (45mb):
http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html


On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new
version. Are you able to reproduce the performance regression with
these two?

   http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip
   http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip

Dan.







Imanuel


Am 18.10.2012 00:37, schrieb Imanuel:

No, the performance stays the same.
I have also tried using a big cache_size, but that didn't change
anything, too.

Yes, I can share the database - it is currently uploading, I will mail
the link tomorrow.

Imanuel


Am 17.10.2012 22:08, schrieb Dan Kennedy:

On 10/18/2012 01:32 AM, Imanuel wrote:

Hello

I tested this on an SSD with a database with one single table with
5,553,534 entries:
CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang
TEXT,
name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll
INTEGER
DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)

When running this command:
CREATE INDEX idx_namen_name ON Namen(name)

Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36
seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
Indexing the column "geonameid" makes 24 vs. 312 seconds.
Neither of the both columns are presorted.

If you set "PRAGMA temp_store = memory" in 3.7.14.1 is the
performance the same as in 3.6.22?

Are you able to share the database?

___
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



___
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



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

[sqlite] Transaction inside transaction

[Igor Korot]

Hi, ALL,
Is it possible to have transaction inside transaction? Will it be
handled correctly?

What I mean is: crate transaction 1 by issuing "BEGIN", create
transaction 2 by issuing "BEGIN".
Close transaction 2 by issuing either "COMMIT" or "ROLLBACK".
If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1.
If "COMMIT" is issued, continue transaction 1.

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

Re: [sqlite] A question about prepared statements

[Igor Tandetnik]

Григорий Григоренко  wrote:
> Let's say app does the following (via the same connection):
> 1. BEGIN TRANSACTION;
> 2. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not 
> finalized or reset;
> 3. execute stmt B (DELETE FROM foo WHERE rowid=1), B is prepared, executed 
> and finalized;
> 4. access column data via stmt A
> 5. COMMIT

http://www.sqlite.org/c3ref/column_blob.html

If the SQL statement does not currently point to a valid row... the result is 
undefined.
-- 
Igor Tandetnik

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

Re: [sqlite] How to correctly use Truncate Table

[Simon Slavin]

On 22 Oct 2012, at 2:00am, Guillaume Saumure  wrote:

> For the moment I use "DELETE FROM table_name" as a workaround but what's 
> wrong with Truncate instruction ?

It's not standard original SQL, it was introduced by one particular 
manufacturer who wanted things done a particular way and has been adopted as a 
standard since (2008, only three years ago).  It is handled differently by 
different manufacturers so it helps to be an expert on one particular version 
of SQL if you want to know exactly what it does.

SQLite correctly handles the standard SQL form "DELETE FROM table_name" to do 
exactly what you want in an efficient way.

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

[sqlite] How to correctly use Truncate Table

[Guillaume Saumure]

Hello everyone,

I would like to suppress all row inside an existing table before 
inserting new element. To to this I would like to use "TRUNCATE TABLE 
table_name" as suggested here http://www.w3schools.com/sql/sql_drop.asp.

And this is the error I get when I try to do this :

near "TRUNCATE": syntax error

For the moment I use "DELETE FROM table_name" as a workaround but what's 
wrong with Truncate instruction ?


Thanks beforehand.

Guillaume




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

Re: [sqlite] CREATE TABLE parsing error

[Richard Hipp]

On Sun, Oct 21, 2012 at 12:21 PM, Sean Doull-Connolly wrote:

> SQLite version 3.7.11
> Fedora 17
> Linux 3.3.4-5.fc17.i686.PAE
>
> The CREATE TABLE text file below is missing a comma after the first
> FOREIGN KEY
> clause, and this should draw an error.
> No error is reported, and the table is created with all three FOREIGN KEY
> constraints.
>

This is not something we are at liberty to change.

The complete text of CREATE TABLE statements is stored in the sqlite_master
table and reparsed every time the database is opened, so that the new
database connection can know the schema.  There are hundreds of thousands,
perhaps millions, of individual applications in circulation that use
SQLite.  Some percentage of those might have missing commas in their
FOREIGN KEY statements.  If we change SQLite to enforce the comma, those
applications would break.


>
> $ sqlite3 test.db 
>  test.sql =
>
> CREATE TABLE  path2file (
> idINTEGER NOT NULL PRIMARY KEY,
> f_id  INTEGER,
> p_id  INTEGER,
> z_id  INTEGER,
> mode  CHAR(4),
> date_stmp CHAR(10),
> time_stmp CHAR(10),
> FOREIGN KEY (f_id) REFERENCES file (id)
> FOREIGN KEY (p_id) REFERENCES path (id),
> FOREIGN KEY (z_id) REFERENCES backup_zip (id)
> );
>
>  ===
> ___
> 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] CREATE TABLE parsing error

[Sean Doull-Connolly]

SQLite version 3.7.11 
Fedora 17
Linux 3.3.4-5.fc17.i686.PAE

The CREATE TABLE text file below is missing a comma after the first FOREIGN KEY 
clause, and this should draw an error.
No error is reported, and the table is created with all three FOREIGN KEY 
constraints.

$ sqlite3 test.db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A question about prepared statements

[Григорий Григоренко]

Fri, 19 Oct 2012 13:16:31 -0700 от Pavel Ivanov :
>>> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?', 
>>> [  'alice' ]);
>
>>
>
>> 1. If I drop record for "alice" from db and then access column data in 
>> prepared stmt will it work OK?
>
>
>
> If the select statement wasn't reset or finalized, you won't be able to 
> delete a record from the database (or, in WAL journaling mode, you would be 
> able to, but the reader would still see original data).
>
>
Well, if he uses the same connection used to prepare the statement,
>
then he will be able to delete record even in normal journaling mode.
>
What happens in this case is undefined. It might be that column data
>
will be still accessbile, it might be you get some garbage, it might
>
be an access violation.
Using same connection means using same transaction, right?

Let's say app does the following (via the same connection):
1. BEGIN TRANSACTION;
2. prepare and step stmt A (SELECT * FROM foo WHERE rowid=1), A is not 
finalized or reset;
3. execute stmt B (DELETE FROM foo WHERE rowid=1), B is prepared, executed and 
finalized;
4. access column data via stmt A
5. COMMIT

A record with rowid=1 is actually deleted from db on step 5, right?

Does accessing record data via stmt A (step 4)  "break any rules"? 



>
>
>> 2. Can I drop client table while having such prepared stmt not closed?
>
>
I think table client cannot be dropped altogether until all statements
>
using it are reset/finalized.
>
>
>
Pavel
>
>
>
On Fri, Oct 19, 2012 at 12:49 PM, Igor Tandetnik  wrote:
>
> Григорий Григоренко  wrote:
>
>> I am using prepared stmts to cache selected record for later use. I have a 
>> wrapper class, that has methods like AsString(name),
>
>> AsFloat(name) etc to extract data for a column name.
>
>>
>
>> I prepare, bind and then do Sqlite3_step() to get record. Later I use 
>> sqlite3_column_XXX() to access data.
>
>>
>
>> My concern is - do open selective prepared stmts depend on something in db 
>> or lock something in db?
>
>
>
> Yes, until you call sqlite3_reset or sqlite3_finalize. For as long as the 
> statement is "active" (meaning, sqlite3_reset or sqlite3_finalize has not 
> been called after the most recent sqlite3_step call), it holds a read 
> transaction open.
>
>
>
>> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?', [ 
>>  'alice' ]);
>
>>
>
>> 1. If I drop record for "alice" from db and then access column data in 
>> prepared stmt will it work OK?
>
>
>
> If the select statement wasn't reset or finalized, you won't be able to 
> delete a record from the database (or, in WAL journaling mode, you would be 
> able to, but the reader would still see original data).
>
>
>
>> 2. Can I drop client table while having such prepared stmt not closed?
>
>
>
> What do you mean by "closed" here?
>
>
>
>> 3. If I have a lot of such stmts (100 or more) will it somehow affect 
>> performance of SQLite or waste lots of memory?
>
>
>
> No, not really. There is a reasonably small data structure associated with a 
> prepared statement, on the order of a few hundred bytes perhaps.
>
>
>
>> Are there some kind of cursors for each selecting prepared stmt?
>
>
>
> In a sense. After a call to sqlite3_step and before a call of sqlite3_reset 
> or sqlite3_finalize, you might think of a statement handle as a form of a 
> cursor. Each subsequent sqlite3_step call advances this cursor forward by one 
> row.
>
>
>
>> 4. If there is a cursor, maybe there is a way to disconnect a cursor from 
>> stmt keeping the single record still available?
>
>
>
> No, short of making a copy of every column's value.
>
> --
>
> Igor Tandetnik
>
>
>
> ___
>
> 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] A question about prepared statements

[Григорий Григоренко]

Fri, 19 Oct 2012 15:49:07 -0400 от "Igor Tandetnik" :
>Григорий Григоренко  wrote:
>
> I am using prepared stmts to cache selected record for later use. I have a 
> wrapper class, that has methods like AsString(name),
>
> AsFloat(name) etc to extract data for a column name. 
>
> 
>
> I prepare, bind and then do Sqlite3_step() to get record. Later I use 
> sqlite3_column_XXX() to access data.
>
> 
>
> My concern is - do open selective prepared stmts depend on something in db or 
> lock something in db?
>
>
Yes, until you call sqlite3_reset or sqlite3_finalize. For as long as the 
statement is "active" (meaning, sqlite3_reset or sqlite3_finalize has not been 
called after the most recent sqlite3_step call), it holds a read transaction 
open.
OK.



>
>
> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?', [  
> 'alice' ]);
>
> 
>
> 1. If I drop record for "alice" from db and then access column data in 
> prepared stmt will it work OK?
>
>
If the select statement wasn't reset or finalized, you won't be able to delete 
a record from the database (or, in WAL journaling mode, you would be able to, 
but the reader would still see original data).
What kind of error is returned in this case?

Actually, I should (and would) find out this via simple test app )


>
>
> 2. Can I drop client table while having such prepared stmt not closed?
>
>
What do you mean by "closed" here?
Finalized.



>
>
> 3. If I have a lot of such stmts (100 or more) will it somehow affect 
> performance of SQLite or waste lots of memory?
>
>
No, not really. There is a reasonably small data structure associated with a 
prepared statement, on the order of a few hundred bytes perhaps.
>
>
> Are there some kind of cursors for each selecting prepared stmt? 
>
>
In a sense. After a call to sqlite3_step and before a call of sqlite3_reset or 
sqlite3_finalize, you might think of a statement handle as a form of a cursor. 
Each subsequent sqlite3_step call advances this cursor forward by one row.
>
>
> 4. If there is a cursor, maybe there is a way to disconnect a cursor from 
> stmt keeping the single record still available?
>
>
No, short of making a copy of every column's value.
OK.


>
-- 
>
Igor Tandetnik
>
>
___
>
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] Find first non-NULL values of several columns

[Black, Michael (IS)]

How's about you store your interpolated value during insert?

You can use a binary mask of say, 16384, to indicate the value is interpolated 
in case you need to know that.  In the original data you sent only one value 
can be interpolated at record 3.  You probably want the interpolation to be 
weighted towards the times when separated by more than one time interval. That 
should be able to done during the update too I think thought that syntax is a 
bit beyond me at the moment.

create table v(a integer primary key,b,c,d,e,f);
create trigger v_insert after insert on v
when new.a > 1 and new.b is not null and (select b from v where a=new.a-1) is 
null
begin
  update v set b=(new.b+(select b from v where v.a 
strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC;

So I would typically want to access the last non-NULL value because of
the DESC keyword.  But if I understand things correctly, a statement
like above will never work because an aggregate function reads the
data in no particular order regardless of the ORDER BY statement.

I like Igor's suggestion.  Although not quite universal, it's clever.
Ryan's suggestion should work well, except that I will need a first(a,
b) and last(a, b) function (if I want to support both ascending and
descending order) and I can leave out the ORDER BY part.  So:

SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE 
unix_time > strftime('%s', 'now', '-1 hour');

Yes, it will have run through the whole set, whereas multiple SELECT
col_x FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour') AND
col_x IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early.
But this will not be a problem for me since I want to have a modest
upper limit (1 hour) anyway.

--
Steinar Midtskogen
___
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] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

[Imanuel]

Sorry if I'm annoying you, but is there any way to have a fast SQLite
3.7.14.1 via DLL or at least to tell why it's that slow?

Imanuel


Am 18.10.2012 17:49, schrieb Imanuel:
> No, I can't - 26s vs 15s (old vs new).
> 
> But when I run the test in my Delphi test application, 3.7.14.1 takes
> 285 seconds (tested again right now).
> All the time, CPU usage is 25% (on a quad core).
> 
> 
> This is my test code:
> 
> sqlite3_open('test.db', handle);
> t0:=now();
> sqlite3_exec(handle, PAnsiChar('CREATE INDEX idx_namen_name ON
> Namen(name)'), nil, nil, nil);
> showmessage(floattostr((now()-t0)*86400));
> sqlite3_close(handle);
> 
> 
> The DLL is referenced in this unit:
> https://raw.github.com/plashenkov/SQLite3-Delphi-FPC/09d8674805c73d1ab0fa05832750cc0f727102f0/Source/SQLite3.pas
> 
> I simply have replaced the dll without changing the linking source code
> to test with 3.7.14.1 - I hope that's ok.
> 
> Imanuel
> 
> 
> 
> 
> Am 18.10.2012 16:49, schrieb Dan Kennedy:
>> On 10/18/2012 03:32 PM, Imanuel wrote:
>>> Ok, here it is (45mb):
>>> http://www.file-upload.net/download-6707980/CREATE_INDEX_test.7z.html
>>
>> On Linux here 3.6.22 takes around 61 seconds. Against 23 for a new
>> version. Are you able to reproduce the performance regression with
>> these two?
>>
>>   http://www.sqlite.org/sqlite-shell-win32-x86-3062200.zip
>>   http://www.sqlite.org/sqlite-shell-win32-x86-307140100.zip
>>
>> Dan.
>>
>>
>>
>>
>>
>>>
>>> Imanuel
>>>
>>>
>>> Am 18.10.2012 00:37, schrieb Imanuel:
 No, the performance stays the same.
 I have also tried using a big cache_size, but that didn't change
 anything, too.

 Yes, I can share the database - it is currently uploading, I will mail
 the link tomorrow.

 Imanuel


 Am 17.10.2012 22:08, schrieb Dan Kennedy:
> On 10/18/2012 01:32 AM, Imanuel wrote:
>> Hello
>>
>> I tested this on an SSD with a database with one single table with
>> 5,553,534 entries:
>> CREATE TABLE Namen(id INTEGER PRIMARY KEY, geonameid INTEGER, lang
>> TEXT,
>> name TEXT, pref INTEGER DEFAULT 0, short INTEGER DEFAULT 0, coll
>> INTEGER
>> DEFAULT 0, historic INTEGER DEFAULT 0, sort INTEGER DEFAULT 7)
>>
>> When running this command:
>> CREATE INDEX idx_namen_name ON Namen(name)
>>
>> Version 3.6.22 (from here: http://indasoftware.com/sqlite/) takes 36
>> seconds, while 3.7.14.1 (as DLL, too) takes 279 seconds.
>> Indexing the column "geonameid" makes 24 vs. 312 seconds.
>> Neither of the both columns are presorted.
> If you set "PRAGMA temp_store = memory" in 3.7.14.1 is the
> performance the same as in 3.6.22?
>
> Are you able to share the database?
>
> ___
> 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
>>>
>>
>> ___
>> 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] Question about foreign key

[Simon Slavin]

On 21 Oct 2012, at 8:54am, Igor Korot  wrote:

> On Sun, Oct 21, 2012 at 12:05 AM, Simon Slavin  wrote:
>> 
>> 
>> The simplest thing is to use any compilation (including any you already 
>> have) and just make sure you use the PRAGMA immediately after opening the 
>> file.
> 
> I didn't change anything yet and didn't include any defines in the MSVC 
> project.
> So all I need is "sqlite3_exec( handle, "PRAGMA foreign_keys = ON",
> NULL, NULL,  );", right?

Right.  Actually you should really examine the values that is returned and 
(simplest) assert that it's SQLITE_OK, but that's true for every _exec().

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

Re: [sqlite] Question about foreign key

[Igor Korot]

Hi, Simon,

On Sun, Oct 21, 2012 at 12:05 AM, Simon Slavin  wrote:
>
> On 21 Oct 2012, at 7:35am, Igor Korot  wrote:
>
>> database. In order for that to happen properly I need the
>> foreing key constraint to be included.
>
> The simplest thing is to use any compilation (including any you already have) 
> and just make sure you use the PRAGMA immediately after opening the file.

I didn't change anything yet and didn't include any defines in the MSVC project.
So all I need is "sqlite3_exec( handle, "PRAGMA foreign_keys = ON",
NULL, NULL,  );", right?

Thank you.

>
> 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] Question about foreign key

[Simon Slavin]

On 21 Oct 2012, at 7:35am, Igor Korot  wrote:

> database. In order for that to happen properly I need the
> foreing key constraint to be included.

The simplest thing is to use any compilation (including any you already have) 
and just make sure you use the PRAGMA immediately after opening the file.

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

Re: [sqlite] Question about foreign key

[Igor Korot]

Hi, Keith,

On Sat, Oct 20, 2012 at 11:10 PM, Keith Medcalf  wrote:
> On Saturday, 20 October, 2012 at 23:42, Igor Korot  wrote:
>
>> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
>> support is disabled by default.
>
> Yes, foreign key enforcement is disabled by default.

OK.

>
>> In order to enable it I need to compile SQLITE with 2 defines undefined.
>
> Not quite.  The default setting for the enforcement of foreign key 
> constraints is controlled by the define
>
> SQLITE_DEFAULT_FOREIGN_KEYS
>
> The default value is 0, which disables foreign key constraint enforcement by 
> default.
> Recompiling with a value of 1 enables foreign key constraint enforcement by 
> default.
>
> At runtime, you can use the SQL statements
> PRAGMA FOREIGN_KEYS=1;
> to enable foreign key constraint enforcement and
> PRAGMA FOREIGN_KEYS=0;
> to disable foreign key constraint enforcement.
> PRAGMA FOREIGN_KEYS;
> will return 0 or 1 indicating whether foreign key constraints are being 
> enforced or not.

All this contradicts the document I referenced above. See part 2 of it.

[quote]
In order to use foreign key constraints in SQLite, the library must be
compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER
defined.
[/quote]

The constant you mentioned is not present in this part of online documentation.

>
>> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
>> added .c and .h file to my project and inspected them.
>
>> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
>> see the #define of this constant anywhere in the .c file.
>
> By default nothing is omitted.  However, if you *do* choose to omit parts of 
> the engine, the effect will be as described:
>
> If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then 
> SQLite behaves as it did prior to version 3.6.19 - foreign key definitions 
> are parsed and may be queried using PRAGMA foreign_key_list, but foreign key 
> constraints are not enforced. The PRAGMA foreign_keys command is a no-op in 
> this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key 
> definitions cannot even be parsed (attempting to specify a foreign key 
> definition is a syntax error).

Well let me clarify a bit.
I am trying to develop a software which will communicate with SQLite
database. In order for that to happen properly I need the
foreing key constraint to be included.
And now I need to look for a third constant that was not in a picture. ;-)

>
>> Is foreign key documentation outdated?
>
> No, it is correct.  See http://www.sqlite.org/compile.html for the options 
> you can define at compile time, the defaults, and the effect.

Well it does not mention the constant you were talking about so at the
very least it's incorrect/incomplete.

>
>> Also one minor question: do I need both .h files or just one will suffice?
>
> sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are 
> not part of the engine itself -- that is, loadable modules.  sqlite3.h is the 
> header file which must be included by c sources which are part of the engine, 
> such as the sqlite3.c amalgamation itself, or extension modules that will be 
> built-in.

OK, so sqlite3.h should be enough to work with basic query. That's
what I thought but needed confirmation for.

Thank you.

>
>> Thank you.
>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> 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] Question about foreign key

[Keith Medcalf]

On Saturday, 20 October, 2012 at 23:42, Igor Korot  wrote:

> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
> support is disabled by default.

Yes, foreign key enforcement is disabled by default.

> In order to enable it I need to compile SQLITE with 2 defines undefined.

Not quite.  The default setting for the enforcement of foreign key constraints 
is controlled by the define

SQLITE_DEFAULT_FOREIGN_KEYS

The default value is 0, which disables foreign key constraint enforcement by 
default.  
Recompiling with a value of 1 enables foreign key constraint enforcement by 
default.

At runtime, you can use the SQL statements
PRAGMA FOREIGN_KEYS=1;
to enable foreign key constraint enforcement and
PRAGMA FOREIGN_KEYS=0;
to disable foreign key constraint enforcement.  
PRAGMA FOREIGN_KEYS;
will return 0 or 1 indicating whether foreign key constraints are being 
enforced or not.

> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
> added .c and .h file to my project and inspected them.
 
> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
> see the #define of this constant anywhere in the .c file.

By default nothing is omitted.  However, if you *do* choose to omit parts of 
the engine, the effect will be as described:

If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then 
SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are 
parsed and may be queried using PRAGMA foreign_key_list, but foreign key 
constraints are not enforced. The PRAGMA foreign_keys command is a no-op in 
this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key 
definitions cannot even be parsed (attempting to specify a foreign key 
definition is a syntax error).  

> Is foreign key documentation outdated?

No, it is correct.  See http://www.sqlite.org/compile.html for the options you 
can define at compile time, the defaults, and the effect.

> Also one minor question: do I need both .h files or just one will suffice?

sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are 
not part of the engine itself -- that is, loadable modules.  sqlite3.h is the 
header file which must be included by c sources which are part of the engine, 
such as the sqlite3.c amalgamation itself, or extension modules that will be 
built-in.
 
> Thank you.


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Question about foreign key

[Pavel Ivanov]

On Sat, Oct 20, 2012 at 10:41 PM, Igor Korot  wrote:
> Hi, ALL,
> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
> support is disabled by default.
> In order to enable it I need to compile SQLITE with 2 defines undefined.

Which also undefined by default. Any SQLITE_OMIT_* define is undefined
by default.

> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
> added .c and .h file to my project
> and inspected them.
>
> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
> see the #define of this constant
> anywhere in the .c file.
>
> Is foreign key documentation outdated?

Nope, everything is right.

> Also one minor question: do I need both .h files or just one will suffice?

Would be nice to know what names those both .h files have. But
generally speaking sqlite3.h should be enough.


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