Re: [sqlite] Process memory space exhausted in 3.7.0

[Victor Morales-Duarte]

As it turns out, I can reproduce the failure using a single huge insert.
The code that I'm including below compiles under bcc32 from
Embarcadero's C++ Builder 2007 and cl from vs2005. Since it's more
likely that people have MS compilers available, the compilation line
having this source file and the sqlite3 3.7.0 amalgamation files in the
same folder is:

cl -EHsc -Fefail.exe main.cpp sqlite3.c

You can then invoke fail.exe with a single command line argument of
8000 like this:

Fail.exe 8000

The source for the executable is listed below. If you're wondering about
why the numbers being inserted are more complicated than need be, it's
because I just wanted the table and indices to look as much as possible
like the actual data that our application stores in sqlite because I had
not realized that the failure could be reproduced with simply inserting.
Beware that there is no handling of incorrect command line arguments.

If you monitor this executable run with perfmon and look at its virtual
bytes, you'll see them hit 2GB and then the next time the insert
statement is stepped, it fails with an I/O disc error. I understand that
this may not be the intended use case of sqlite, but I don't remember
reading anything anywhere that forces you limit the size of insert
operations, so a priori, there should not be any reason why simply
inserting a lot of rows in a single transaction should make the db fail.
If you break up the insert into chunks
_and_close_the_connection_between_chunks_ then the error does not occur.
This is even more bothersome because there is also no documentation
saying that there would be a limit on the number of operations that can
be performed on a single connection.

Should I open a bug for this?

Thanks a lot!

Victor


start main.cpp-
#include 
#include 
#include 
#include 
#include 
#include 
#include 

#include "sqlite3.h"

int main( int argc, char* argv[] )
{
   // boost::lexical_cast where art thou?
   int nRecords = 400;
   if ( argc > 1 )
   {
  std::stringstream sstr;
  sstr << argv[1];
  sstr >> nRecords;
   }

   sqlite3* connection = NULL;
   sqlite3_stmt* statement = NULL;
   
   // Open db
   std::cout << "Openning db." << std::endl;
   int rc = sqlite3_open( "./test.db",  );
   if ( rc )
   {
  std::string errorMessage( sqlite3_errmsg( connection ) );
  std::runtime_error ex( errorMessage );
  sqlite3_close( connection );
  connection = NULL;
  std::cerr << errorMessage;
  throw ex;
   }
   else
   {
  int theTimeout = 5000;
  sqlite3_exec( connection, 
"PRAGMA page_size = 4096; PRAGMA foreign_keys = 1;
PRAGMA cache_size = 2; PRAGMA journal_mode=WAL;", 
NULL, NULL, NULL );
  sqlite3_busy_timeout( connection, theTimeout );
   }

   // Schema
   std::cout << "Creating schema." << std::endl;
   sqlite3_exec( connection, "BEGIN IMMEDIATE TRANSACTION;;", NULL,
NULL, NULL );
   sqlite3_exec( connection, "CREATE TABLE IF NOT EXISTS TEST_DATA( ID
INTEGER PRIMARY KEY AUTOINCREMENT, GROUPID INTEGER, USERID INTEGER,
CONTEXTID INTEGER, TSTAMP INTEGER, SOURCE STRING, TYPE STRING, SERVERID
INTEGER, NSIG INTEGER, NNOI INTEGER, LON FLOAT, LAT FLOAT, CONF FLOAT
);", NULL, NULL, NULL );
   sqlite3_exec( connection, "CREATE INDEX IF NOT EXISTS
IDX_TEST_DATA_IDON TEST_DATA( ID );",
NULL, NULL, NULL );
   sqlite3_exec( connection, "CREATE INDEX IF NOT EXISTS
IDX_TEST_DATA_USERID_GROUPID_TSTAMP ON TEST_DATA( USERID, GROUPID,
TSTAMP);", NULL, NULL, NULL );
   sqlite3_exec( connection, "CREATE INDEX IF NOT EXISTS
IDX_TEST_DATA_GROUPID_TSTAMPON TEST_DATA( GROUPID, TSTAMP );",
NULL, NULL, NULL );
   sqlite3_exec( connection, "CREATE INDEX IF NOT EXISTS
IDX_TEST_DATA_CONTEXTID_TSTAMP  ON TEST_DATA( CONTEXTID, TSTAMP );",
NULL, NULL, NULL );
   sqlite3_exec( connection, "CREATE INDEX IF NOT EXISTS
IDX_TEST_DATA_LAT_LON   ON TEST_DATA( LAT, LON );",
NULL, NULL, NULL );
   sqlite3_exec( connection, "COMMIT TRANSACTION;",  NULL, NULL,
NULL );
   
   std::string insertRecordStatementStr( "INSERT INTO TEST_DATA (
GROUPID, USERID, CONTEXTID, TSTAMP, SOURCE, TYPE, SERVERID, NSIG, NNOI,
LON, LAT, CONF ) VALUES ( NULL, ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, NULL,
NULL, NULL );" );

   std::cout << "Preparing statement db." << std::endl;
   rc = sqlite3_prepare_v2( connection,
insertRecordStatementStr.c_str(), -1, , 0 );
   if ( rc != SQLITE_OK )
   {
  std::string errorMessage( sqlite3_errmsg( connection ) );
  std::runtime_error ex( errorMessage );
  sqlite3_finalize( statement );
  sqlite3_close( connection );
  statement = NULL;
  connection = NULL;
  std::cerr << errorMessage;
  throw ex;
   }

   std::map< int, std::pair< int, int > > userId2ContextIdCount;
   const char* sourceStr = "test_failure";
   const char* types[] = { "type_01_1234567890_1234567890", 
   

[sqlite] SQLite bug report: Bad truncation in column mode with non ASCII characters

[François Bonzon]

In ".mode column", the function used to compute the length of the string to
print does not account for multi-byte characters, resulting in a shorter
string, and all remaining fields in the line shifted to the left.

Example:

sqlite> select * from test limit 3;
namename2   name3   zipcode cityaddress
--  --  --  --  --  --
Feuerwehrt  des Landkr  Soltau-Fal  29664   Walsrode
Leitstelle  Rettungsds   des Kreis  59555   Lippstadt   Geiststr.
Feuerwehr   der Freiwi  Schönböc  23556   Lübeck Bernsteind

Two first lines are OK. On the third line, the two ö cause 23556 to be
shifted two chars left, then the ü causes an additional one char shift for
Bernsteind.

I noticed this bug while using version 3.6.22, and looking at the release
notes, I don't read about a fix for this issue in more recent versions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

[Adrian Aichner]

Here is the little patch verbatim.

Regards,

Adrian

diff -u c:\Hacking\sqlite3\version3.html.~1~
c:\Hacking\sqlite3\version3.html
--- c:\Hacking\sqlite3\version3.html.~1~ 2010-08-08 17:54:51.12500 +0200
+++ c:\Hacking\sqlite3\version3.html 2010-08-08 17:54:51.140625000 +0200
@@ -235,8 +235,8 @@
 feature is retained in version 3.0, though in a slightly modified form.
 Each table column will store any type of data, though columns have an
 affinity for the format of data defined by their declared datatype.
-When data is inserted into a column, that column will make at attempt
-to convert the data format into the columns declared type.   All SQL
+When data is inserted into a column, that column will make an attempt
+to convert the data format into the column's declared type.   All SQL
 database engines do this.  The difference is that SQLite 3.0 will
 still store the data even if a format conversion is not possible.
 
@@ -297,9 +297,9 @@

 
 When creating new user-defined SQL functions and collating sequences,
-each function or collating sequence can specify it if works with
+each function or collating sequence can specify if it works with
 UTF-8, UTF-16be, or UTF-16le.  Separate implementations can be registered
-for each encoding.   If an SQL function or collating sequences is required
+for each encoding.   If an SQL function or collating sequence is required
 but a version for the current text encoding is not available, then
 the text is automatically converted.  As before, this conversion takes
 computation time, so programmers are advised to pick a single


On Sun, Aug 8, 2010 at 6:00 PM, Adrian Aichner <
adrian.aich...@googlemail.com> wrote:

> Greetings!
>
> I hope you'll find this little patch useful.
>
> I'm not sure whether this webpage is auto-generated.
>
> In that case the patch should still be useful to spot the typos.
>
> Thanks for this great SQL database engine!
>
> --
> Adrian Aichner
>  mailto:adrian.aich...@gmail.com
>
>


-- 
Adrian Aichner
 mailto:adrian.aich...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incremental row number associated to the current Query?

[Igor Tandetnik]

Mike Henshaw  wrote:
> 1. The application is from a third party with no access to the source code 
> but the SQL query can be updated since the SQL query
> is used in a custom HTML template which can also be updated. 
> 2. The calculations to the RowNum would then have to be in the C/CPP 
> application which would nullify the use of customizable HTML
> template. 

What kind of template language is being used? Many of them have a built-in way 
to number rows, or implement alternate coloring (the motivation for your second 
example, I suspect).

There is no built-in mechanism in SQLite for this kind of row numbering. 
Implementing it in SQL query is very inefficient, and is outright impossible if 
the resultset needs to contain duplicate rows.
-- 
Igor Tandetnik


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

Re: [sqlite] Incremental row number associated to the current Query?

[Mike Henshaw]

>> Is there a way to create an incremental row or show a row number that is 
>> linked to the current select query that can be used in
>> calculations? 
> 
>> Basically a row counter for the current query that can be used in 
>> calculations.

>Your application makes a series of sqlite3_step calls, one for each row. Can't 
>it just increment a counter on each step?

>> 2. The application is written in C/CPP so no counters or variables from 
>> C/CPP can be used.

>I don't see how the second statement follows from the first. In fact, it 
>appears that just the opposite should be the case.

1. The application is from a third party with no access to the source code but 
the SQL query can be updated since the SQL query is used in a custom HTML 
template which can also be updated.
2. The calculations to the RowNum would then have to be in the C/CPP 
application which would nullify the use of customizable HTML template.

Have a dynamite day.

Michael Henshaw
AdCom Technologies Inc.
1119 Superior Ave.
Sheboygan, WI 53081
(920) 694-0039

http://www.adcomtechnologies.com/

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Monday, August 09, 2010 5:25 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Incremental row number associated to the current Query?

Mike Henshaw  wrote:
> Is there a way to create an incremental row or show a row number that is 
> linked to the current select query that can be used in
> calculations? 
> 
> Basically a row counter for the current query that can be used in 
> calculations.

Your application makes a series of sqlite3_step calls, one for each row. Can't 
it just increment a counter on each step?

> 2. The application is written in C/CPP so no counters or variables from C/CPP 
> can be used.

I don't see how the second statement follows from the first. In fact, it 
appears that just the opposite should be the case.
-- 
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] Incremental row number associated to the current Query?

[Igor Tandetnik]

Mike Henshaw  wrote:
> Is there a way to create an incremental row or show a row number that is 
> linked to the current select query that can be used in
> calculations? 
> 
> Basically a row counter for the current query that can be used in 
> calculations.

Your application makes a series of sqlite3_step calls, one for each row. Can't 
it just increment a counter on each step?

> 2. The application is written in C/CPP so no counters or variables from C/CPP 
> can be used.

I don't see how the second statement follows from the first. In fact, it 
appears that just the opposite should be the case.
-- 
Igor Tandetnik


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

[sqlite] Incremental row number associated to the current Query?

[Mike Henshaw]

Is there a way to create an incremental row or show a row number that is linked 
to the current select query that can be used in calculations?

Basically a row counter for the current query that can be used in calculations.

Notes:
1. The query can be change at any time.
2. The application is written in C/CPP so no counters or variables from C/CPP 
can be used.
3. The device has limited flash memory so a scripting language such as Perl, 
PHP, or Ruby cannot be used.
4. The table primary key are not always in consecutive order (useless records 
that have no historical significant are removed).

Query:
SELECT
SomeSortOfRowCount AS RowNum,
COALESCE(Locations.Name, Equipment.Area, 'Unkown Location') AS 
LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
Equipment.SerialNum AS SerialNo,
REPLACE(COALESCE(Equipment.Area || ' ' || EquipmentUses.Name, 
Equipment.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment
LEFT OUTER JOIN Employee ON Employee.EmployeeId = Equipment.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = Equipment.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
Equipment.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = 
Equipment.SerialNum
WHERE Equipment.SerialNum <> ""
GROUP BY Equipment.SerialNum
ORDER BY LocalName


Output:
RowNum | LocalName| Employee| SerialNo   | Item  | 
DateAdded
---
1  | Bob's Work Bench | (Bob Smith) | #S444SSABA | Hammer Drill  | 
2010-10-10
2  | Tom's Work Bench | (Tom Smith) | #BAA890293 | Desk Phone| 
2010-10-10
3  | Front Desk   |   | #203WE3902 | Local Workstation | 
2010-10-10

OR

Query:
SELECT
(SomeSortOfRowCount % 2) + 1 AS RowNum,
COALESCE(Locations.Name, Equipment.Area, 'Unkown Location') AS 
LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
Equipment.SerialNum AS SerialNo,
REPLACE(COALESCE(Equipment.Area || ' ' || EquipmentUses.Name, 
Equipment.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment
LEFT OUTER JOIN Employee ON Employee.EmployeeId = Equipment.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = Equipment.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
Equipment.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = 
Equipment.SerialNum
WHERE Equipment.SerialNum <> ""
GROUP BY Equipment.SerialNum
ORDER BY LocalName

Output:
RowNum | LocalName| Employee| SerialNo   | Item  | 
DateAdded
---
1  | Bob's Work Bench | (Bob Smith) | #S444SSABA | Hammer Drill  | 
2010-10-10
2  | Tom's Work Bench | (Tom Smith) | #BAA890293 | Desk Phone| 
2010-10-10
1  | Front Desk   |   | #203WE3902 | Local Workstation | 
2010-10-10


I have tried:
SELECT
(SELECT COUNT(*) FROM Equipment tb1 WHERE tb1. EquipmentId <= 
tb2.EquipmentId AND tb1. SerialNum <> "") AS RowNum,
COALESCE(Locations.Name, tb2.Area, 'Unkown Location') AS LocalName,
COALESCE('(' || Employee.FirstName || ' ' || Employee.LastName || ')', 
'') AS Employee,
tb2.SerialNum AS SerialNo,
REPLACE(COALESCE(tb2.Area || ' ' || EquipmentUses.Name, 
tb2.EquipmentTypeName), 'SVR', 'Server') AS Item,
strftime('%m-%d-%Y',MIN(EquipmentHistory.DateTimeOccured)) AS DateAdded
FROM Equipment tb2
LEFT OUTER JOIN Employee ON Employee.EmployeeId = tb2.EmployeeId
LEFT OUTER JOIN Locations ON Locations.LocationId = tb2.LocationId OR 
Locations.LocationId = Employee.LocationId
LEFT OUTER JOIN EquipmentUses ON EquipmentUses.EquipmentUsesId = 
tb2.EquipmentUsesId
LEFT OUTER JOIN EquipmentHistory ON EquipmentHistory.SerialNum = tb2.SerialNum
WHERE tb2.SerialNum <> ""
GROUP BY tb2.SerialNum
ORDER BY LocalName

But:
1. The row numbers / counter is not in the proper order (ORDER BY LocalName).
2. Other queries that use a row number / counter will COUNT(*) using GROUP BY 
ROW_X which then RowNum will count every instance where the ROW is not NULL.

The only way I have success was to create a temp table:
1. Drop the table (table_query_temp) if it exits.
2. Create a Table (table_query_temp) with an incremental Primary Key.
3. Run the query as a insert (insert into table_query_temp (...) select ) 
while leaving the Primary Key as null for inserts.
4. Query the new table (select * from table_query_temp).
5. Drop the table 

Re: [sqlite] commit transaction/savepoints on program crash

[Simon Slavin]

[New text /below/ any text you want to quote, please.]

On 9 Aug 2010, at 9:09pm, Josh wrote:

> Thanks for the answers, but I guess I should have made my question more 
> clear. I knew that you can commit every sql statement individually, then 
> the question would be, how can I roll them back?
> 
> In other words I'd like something like savepoint and rollback to 
> savepoint, while not loosing the transactions if there is a power failure. 
> I know I can have either one of these, but can I have both!?

The difficulty is that there's no way to ask the database which savepoints it 
was in the middle of when it crashed, and how far through each savepoint it 
was.  For instance, suppose you were working on a SAVEPOINT concerning 20 rows 
when you lost power.  With the system you describe, when power is restored you 
need to know you were on row 16 of SAVEPOINT A, so you can write the other 4 
rows, then RELEASE it.  But you can't find that information useful in any way, 
because the system crashed before the other 4 rows made it to disk, so you've 
lost all the data about them.  So your only real option is to ROLLBACK the 
whole savepoint.  So you might as well not have the partial savepoint 
information there in the first place.

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

Re: [sqlite] commit transaction/savepoints on program crash

[Josh]

Thanks for the answers, but I guess I should have made my question more 
clear. I knew that you can commit every sql statement individually, then 
the question would be, how can I roll them back?

In other words I'd like something like savepoint and rollback to 
savepoint, while not loosing the transactions if there is a power failure. 
I know I can have either one of these, but can I have both!?

Josh

>> I'm new to the list and had a question. I know the default behavior for
>> savepoints (or any transactions) is that if they have not been committed, if
>> the program crashes, they are lost. Is there any way to have them committed 
>> by
>> default? Basically I *only* want the transaction rolled back in case of an
>> explicit rollback statement, not due to program crash/power failure, etc. 
>> Does
>> anyone know of a way of doing this? Thanks!
>
> Sure.  Don't define any transactions.  Just issue every command 
> separately.  That way SQLite automatically makes a little transaction 
> for each command, and once the command is finished it will automatically 
> COMMIT it.
>
> 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] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

Igor Tandetnik  writes:

[...]
> 
> Why would you want to fail statements that end up not violating any
> constraints?

The idea is to prevent that somebody can change the table staff that is not
already in the table. Therefore I create the first user by not switching on
foreign keys.

My idea was that the constraint is violated because the PK isn't found (I
assumed the INSERT/UPDATE will take place after the check).

But if I take http://en.wikipedia.org/wiki/Foreign_key for granted than I
misunderstood the Foreign Key concept because there are 2 different(!) tables
required.

Oliver

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Igor Tandetnik]

Oliver Peters  wrote:
> Please believe me I really try hard to understand - but what I don't 
> understand
> is the fact that you can insert in this case:
> 
> 
> PRAGMA foreign_keys = ON;
> 
> /*
> **success INSERT = yes
> */
> 
> CREATE TABLE a(
>idINTEGER PRIMARY KEY
> AUTOINCREMENT,
>id_staff_editor   INTEGER NOT NULL,
>FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
> );
> 
> INSERT INTO a(id_staff_editor) VALUES(1);
> 
> 
> The table is empty, there is no record and as far as I understand I'm doing 
> the
> following
> 
> Insert into the field id_staff_editor of table a the value 1 if the value 1 is
> already present in the field id of table a (but in the moment there is no 
> record
> in table a)

The actual implementation is more like: insert the record, then check 
constraints - if they fail, roll back the insertion and raise an error.

> To my mind the concept of Foreign Keys means that you check before 
> INSERT/UPDATE

Why would you want to fail statements that end up not violating any constraints?
-- 
Igor Tandetnik


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

Re: [sqlite] commit transaction/savepoints on program crash

[Simon Slavin]

On 9 Aug 2010, at 5:05pm, Josh wrote:

> I'm new to the list and had a question. I know the default behavior for 
> savepoints (or any transactions) is that if they have not been committed, if 
> the program crashes, they are lost. Is there any way to have them committed 
> by 
> default? Basically I *only* want the transaction rolled back in case of an 
> explicit rollback statement, not due to program crash/power failure, etc. 
> Does 
> anyone know of a way of doing this? Thanks!

Sure.  Don't define any transactions.  Just issue every command separately.  
That way SQLite automatically makes a little transaction for each command, and 
once the command is finished it will automatically COMMIT it.

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

Re: [sqlite] commit transaction/savepoints on program crash

[Pavel Ivanov]

> Is there any way to have them committed by
> default? Basically I *only* want the transaction rolled back in case of an
> explicit rollback statement, not due to program crash/power failure, etc. Does
> anyone know of a way of doing this?

You can avoid transaction begin/commit statements, so that every
statement is in its own transaction and automatically committed at the
end of execution. Otherwise if program crashes there's no way to know
what non-corrupted state should database be in while transaction is
not fully committed yet.


Pavel

On Mon, Aug 9, 2010 at 12:05 PM, Josh  wrote:
> Hello all,
>
> I'm new to the list and had a question. I know the default behavior for
> savepoints (or any transactions) is that if they have not been committed, if
> the program crashes, they are lost. Is there any way to have them committed by
> default? Basically I *only* want the transaction rolled back in case of an
> explicit rollback statement, not due to program crash/power failure, etc. Does
> anyone know of a way of doing this? Thanks!
>
> Josh
> ___
> 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] commit transaction/savepoints on program crash

[Josh]

Hello all,

I'm new to the list and had a question. I know the default behavior for 
savepoints (or any transactions) is that if they have not been committed, if 
the program crashes, they are lost. Is there any way to have them committed by 
default? Basically I *only* want the transaction rolled back in case of an 
explicit rollback statement, not due to program crash/power failure, etc. Does 
anyone know of a way of doing this? Thanks!

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

Dan Kennedy  writes:

> 
> > why returns the "INSERT INTO a" not an error while the "INSERT INTO  
> > b" does? How
> > corresponds this behaviour to the concept of FOREIGN KEYS?
> 
> > CREATE TABLE a(
> >idINTEGER PRIMARY  
> > KEY
> > AUTOINCREMENT,
> >id_staff_editor   INTEGER NOT NULL,
> >FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
> > );
> >
> > INSERT INTO a(id_staff_editor) VALUES(1);
> >
> > CREATE TABLE b(
> >idINTEGER PRIMARY  
> > KEY
> > AUTOINCREMENT,
> >id_staff_editor   INTEGER NOT NULL,
> >FOREIGN KEY(id_staff_editor)  REFERENCES b(id)
> > );
> >
> > INSERT INTO b(id_staff_editor) VALUES(2);
> 
> The key statement is in the second paragraph here:
> 
>http://www.sqlite.org/foreignkeys.html#fk_deferred
> 
> "If a statement modifies the contents of the database so
> that an immediate foreign key constraint is in violation
> at the conclusion the statement, an exception is thrown
> and the effects of the statement are reverted."
> 
> At the conclusion of your two insert statements, table "a"
> contains (1, 1) and table "b" contains (1, 2). Since the
> contents of table "b" violate the FK constraint, an
> exception is thrown.
> 
> Dan.
> 

Please believe me I really try hard to understand - but what I don't understand
is the fact that you can insert in this case:


PRAGMA foreign_keys = ON;

/*
**success INSERT = yes
*/

CREATE TABLE a(
idINTEGER PRIMARY KEY
AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
);

INSERT INTO a(id_staff_editor) VALUES(1);


The table is empty, there is no record and as far as I understand I'm doing the
following

Insert into the field id_staff_editor of table a the value 1 if the value 1 is
already present in the field id of table a (but in the moment there is no record
in table a)

To my mind the concept of Foreign Keys means that you check before INSERT/UPDATE

Oliver

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Martin.Engelschalk]

Am 09.08.2010 16:57, schrieb Oliver Peters:
> [...]
>
> To my mind the simplified question is:
>
> why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? 
> How
> corresponds this behaviour to the concept of FOREIGN KEYS?
>
> I wrote the code into file.sql (encoding=utf8, if this matters) and started
>   sqlite3 -bail test.db3<  file.sql
>
>
> PRAGMA foreign_keys = ON;
>
> /*
> **success INSERT = yes
> */
>
> CREATE TABLE a(
>  idINTEGER PRIMARY KEY
> AUTOINCREMENT,
>  id_staff_editor   INTEGER NOT NULL,
>  FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
> );
>
> INSERT INTO a(id_staff_editor) VALUES(1);
This insert succeeds, because the relusting record has the value 1 in 
it's field id. Id is autoincrement, therefore the first record will habe 
id == 1, which satisfies your foreign key, because you insert 1 in the 
column id_staff_editor as well.
> /*
> **success INSERT = no
> */
>
> CREATE TABLE b(
>  idINTEGER PRIMARY KEY
> AUTOINCREMENT,
>  id_staff_editor   INTEGER NOT NULL,
>  FOREIGN KEY(id_staff_editor)  REFERENCES b(id)
> );
>
> INSERT INTO b(id_staff_editor) VALUES(2);
>
This insert FAILS, because the relusting record has the value 1 in it's 
field id. Id is autoincrement, therefore the first record will habe id 
== 1, which DOES NOT satisfiy your foreign key, because you insert *2* 
in the column id_staff_editor .

> Oliver
>
>
> ___
> 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] foreign key the true error - sqlite 3.7.0.1

[Dan Kennedy]

> why returns the "INSERT INTO a" not an error while the "INSERT INTO  
> b" does? How
> corresponds this behaviour to the concept of FOREIGN KEYS?

> CREATE TABLE a(
>idINTEGER PRIMARY  
> KEY
> AUTOINCREMENT,
>id_staff_editor   INTEGER NOT NULL,
>FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
> );
>
> INSERT INTO a(id_staff_editor) VALUES(1);
>
> CREATE TABLE b(
>idINTEGER PRIMARY  
> KEY
> AUTOINCREMENT,
>id_staff_editor   INTEGER NOT NULL,
>FOREIGN KEY(id_staff_editor)  REFERENCES b(id)
> );
>
> INSERT INTO b(id_staff_editor) VALUES(2);


The key statement is in the second paragraph here:

   http://www.sqlite.org/foreignkeys.html#fk_deferred

"If a statement modifies the contents of the database so
that an immediate foreign key constraint is in violation
at the conclusion the statement, an exception is thrown
and the effects of the statement are reverted."

At the conclusion of your two insert statements, table "a"
contains (1, 1) and table "b" contains (1, 2). Since the
contents of table "b" violate the FK constraint, an
exception is thrown.

Dan.


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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

[...]

To my mind the simplified question is:

why returns the "INSERT INTO a" not an error while the "INSERT INTO b" does? How
corresponds this behaviour to the concept of FOREIGN KEYS?

I wrote the code into file.sql (encoding=utf8, if this matters) and started
 sqlite3 -bail test.db3 < file.sql


PRAGMA foreign_keys = ON;

/*
**success INSERT = yes
*/

CREATE TABLE a(
idINTEGER PRIMARY KEY
AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
);

INSERT INTO a(id_staff_editor) VALUES(1);

/*
**success INSERT = no
*/

CREATE TABLE b(
idINTEGER PRIMARY KEY
AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES b(id)
);

INSERT INTO b(id_staff_editor) VALUES(2);


Oliver


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

Re: [sqlite] Mistake in documentation and question

[Richard Hipp]

On Mon, Aug 9, 2010 at 8:38 AM, Ioannis Epaminonda wrote:

>
> In page http://www.sqlite.org/datatype3.html
> under section 3.2 Affinity Of Comparison Operands 2nd bullet point should
> read:
> An expression  "CAST(expr AS type)"  instead of "CAST(expr TO
> type)"
>
> In addition a quick question.
> Is there a way to perform a division of two columns (real type) and force
> the result to be presented/rounded in 2 decimal places ?
>

round(a/b, 2)


>
> Thanks
> --
> View this message in context:
> http://old.nabble.com/Mistake-in-documentation-and-question-tp29387429p29387429.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] foreign key the true error - sqlite 3.7.0.1

[Black, Michael (IS)]

I found the (apparent) problem.
 
This works and throws the constraint violation:
 
PRAGMA foreign_keys=OFF;
CREATE TABLE staff_01(
id INTEGER PRIMARY KEY AUTOINCREMENT,
id_staff_editor INTEGER NOT NULL,
code CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor) REFERENCES staff(id)
);
INSERT INTO "staff_01" VALUES(1,1,'CB');
CREATE TABLE staff_02(
id INTEGER PRIMARY KEY AUTOINCREMENT,
id_staff_editor INTEGER NOT NULL,
code CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor) REFERENCES staff_01(id)
);
pragma foreign_keys=ON;
pragma foreign_keys;
INSERT INTO "staff_02" VALUES(1,1000,'CB');
INSERT INTO "staff_02" VALUES(2,1,'CB');
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1
sqlite> INSERT INTO "staff_02" VALUES(1,1000,'CB');
Error: foreign key constraint failed
sqlite> INSERT INTO "staff_02" VALUES(2,1,'CB');
sqlite>

But put it inside a transaction (like from .dump or the examples that Oliver 
gave) and it doesn't retain the change in the foreign_keys setting.  Is this 
the desired behavior?  This would mean you couldn't import data and force the 
constraint inside a transaction.  You have to set the foreign_keys value BEFORE 
the BEGIN.  That kind of makes sense.

SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> begin transaction;
sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
0

  
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies
Sent: Mon 8/9/2010 9:13 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] foreign key the true error - sqlite 3.7.0.1



On 9 August 2010 14:56, Black, Michael (IS)  wrote:
> From http://www.sqlite.org/foreignkeys.html
>
>
>
> Although I believe I compiled my shell WITHOUT these defined but I still 
> don't get enforcement of the foreign key constraint.
>
> It appears that foreign keys are fully constrained by default.  Is there any 
> way in the shell to find out if it's enabled?
>

Shell compiled with SQLITE_OMIT_FOREIGN_KEYS defined

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> pragma foreign_keys;
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
sqlite> pragma foreign_keys=off;
sqlite> pragma foreign_keys;
sqlite>

Shell compiled without SQLITE_OMIT_FOREIGN_KEYS defined
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
sqlite> pragma foreign_keys=off;
sqlite> pragma foreign_keys;
0

.
.
.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>

Regards,
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] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

I use the standard windows binary from http://www.sqlite.org/sqlite-3_7_0_1.zip
and if I do the recommended test I get

>sqlite3
SQLite version 3.7.0.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1
sqlite> pragma foreign_keys=OFF;
sqlite> pragma foreign_keys;
0
sqlite>

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Simon Davies]

On 9 August 2010 14:56, Black, Michael (IS)  wrote:
> From http://www.sqlite.org/foreignkeys.html
>
>
>
> Although I believe I compiled my shell WITHOUT these defined but I still 
> don't get enforcement of the foreign key constraint.
>
> It appears that foreign keys are fully constrained by default.  Is there any 
> way in the shell to find out if it's enabled?
>

Shell compiled with SQLITE_OMIT_FOREIGN_KEYS defined

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> pragma foreign_keys;
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
sqlite> pragma foreign_keys=off;
sqlite> pragma foreign_keys;
sqlite>

Shell compiled without SQLITE_OMIT_FOREIGN_KEYS defined
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
sqlite> pragma foreign_keys=off;
sqlite> pragma foreign_keys;
0

.
.
.
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Black, Michael (IS)]

>From http://www.sqlite.org/foreignkeys.html

 

Although I believe I compiled my shell WITHOUT these defined but I still don't 
get enforcement of the foreign key constraint.

It appears that foreign keys are fully constrained by default.  Is there any 
way in the shell to find out if it's enabled?

 


2. Enabling Foreign Key Support 


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

Assuming the library is compiled with foreign key constraints enabled, it must 
still be enabled by the application at runtime, using the PRAGMA foreign_keys 

  command. For example: 
sqlite> PRAGMA foreign_keys = ON;
 
 
If put a trigger in it works (of course)
PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE staff_01(

id INTEGER PRIMARY KEY AUTOINCREMENT,

id_staff_editor INTEGER NOT NULL,

code CHAR(2) NOT NULL,

FOREIGN KEY(id_staff_editor) REFERENCES staff(id)

);

INSERT INTO "staff_01" VALUES(1,1,'CB');

CREATE TABLE staff_02(

id INTEGER PRIMARY KEY AUTOINCREMENT,

id_staff_editor INTEGER NOT NULL,

code CHAR(2) NOT NULL,

FOREIGN KEY(id_staff_editor) REFERENCES staff_01(id)

);

pragma foreign_keys=ON;

CREATE TRIGGER trig1 before insert on staff_02

for each row begin

select raise(abort,'staff_02 insert violates foreign key')

where (select id from staff_01 where id=NEW.id_staff_editor) IS NULL;

end;

INSERT INTO "staff_02" VALUES(1,1000,'CB');

INSERT INTO "staff_02" VALUES(2,1,'CB');

COMMIT;

First insert fails...2nd one works
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Oliver Peters
Sent: Mon 8/9/2010 8:38 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] foreign key the true error - sqlite 3.7.0.1



Igor Tandetnik  writes:

>
> Oliver Peters  wrote:
> > This sql code works in the two cases but AFAIK  it shouldn't in the second
> >
> > PRAGMA foreign_keys = ON;
> >
> > CREATE TABLE staff_02(
> > idINTEGER PRIMARY KEY AUTOINCREMENT,
> > id_staff_editor   INTEGER NOT NULL,
> > code  CHAR(2) NOT NULL,
> > UNIQUE(code)
> > FOREIGN KEY(id_staff_editor)  REFERENCES staff_02(id)
> > );
> >
> > INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');
>
> The first record gets inserted with an id of 1, which just happens to match
the value of id_staff_editor. See
> what this would do:
>
> INSERT INTO staff_02(id_staff_editor,code) VALUES(1000,'CB');
>

So I think I misunderstood the concept of Foreign Keys - I thought at first it
is checked if the PK exists and if it is not existing it is rejected to
INSERT/UPDATE the FK

Could you please explain why it is o.k that this works?


PRAGMA foreign_keys = ON;

CREATE TABLE a(
idINTEGER PRIMARY KEY
AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
);

INSERT INTO a(id_staff_editor) VALUES(1);
INSERT INTO a(id_staff_editor) VALUES(2);
INSERT INTO a(id_staff_editor) VALUES(3);


___
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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

Igor Tandetnik  writes:

> 
> Oliver Peters  wrote:
> > This sql code works in the two cases but AFAIK  it shouldn't in the second
> > 
> > PRAGMA foreign_keys = ON;
> > 
> > CREATE TABLE staff_02(
> > idINTEGER PRIMARY KEY AUTOINCREMENT,
> > id_staff_editor   INTEGER NOT NULL,
> > code  CHAR(2) NOT NULL,
> > UNIQUE(code)
> > FOREIGN KEY(id_staff_editor)  REFERENCES staff_02(id)
> > );
> > 
> > INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');
> 
> The first record gets inserted with an id of 1, which just happens to match
the value of id_staff_editor. See
> what this would do:
> 
> INSERT INTO staff_02(id_staff_editor,code) VALUES(1000,'CB');
> 

So I think I misunderstood the concept of Foreign Keys - I thought at first it
is checked if the PK exists and if it is not existing it is rejected to
INSERT/UPDATE the FK

Could you please explain why it is o.k that this works?


PRAGMA foreign_keys = ON;

CREATE TABLE a(
idINTEGER PRIMARY KEY
AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES a(id)
);

INSERT INTO a(id_staff_editor) VALUES(1);
INSERT INTO a(id_staff_editor) VALUES(2);
INSERT INTO a(id_staff_editor) VALUES(3);


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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Igor Tandetnik]

Oliver Peters  wrote:
> This sql code works in the two cases but AFAIK ;-) it shouldn't in the second
> 
> PRAGMA foreign_keys = ON;
> 
> CREATE TABLE staff_02(
> idINTEGER PRIMARY KEY AUTOINCREMENT,
> id_staff_editor   INTEGER NOT NULL,
> code  CHAR(2) NOT NULL,
> UNIQUE(code)
> FOREIGN KEY(id_staff_editor)  REFERENCES staff_02(id)
> );
> 
> INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');

The first record gets inserted with an id of 1, which just happens to match the 
value of id_staff_editor. See what this would do:

INSERT INTO staff_02(id_staff_editor,code) VALUES(1000,'CB');

-- 
Igor Tandetnik

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

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

and the added , after UNIQUE(code) doesn't change anything

(oh what a day ;-) )

Oliver

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

[sqlite] foreign key the true error - sqlite 3.7.0.1

[Oliver Peters]

This sql code works in the two cases but AFAIK ;-) it shouldn't in the second

Am I right?


CREATE TABLE staff_01(

idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES 
staff_01(id)
);

INSERT INTO staff_01(id_staff_editor,code) VALUES(1,'CB');


PRAGMA foreign_keys = ON;

CREATE TABLE staff_02(
idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
UNIQUE(code)
FOREIGN KEY(id_staff_editor)  REFERENCES 
staff_02(id)
);

INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');


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

Re: [sqlite] Round was Mistake in documentation and question

[Roger Andersson]

> In addition a quick question.
> Is there a way to perform a division of two columns (real 
> type) and force the result to be presented/rounded in 2 
> decimal places ?
> 
ROUND should do the trick ;-)
http://www.sqlite.org/lang_corefunc.html#round
Please note
http://www.sqlite.org/faq.html#q16

/Roger

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

Re: [sqlite] foreign key error 01

[Igor Tandetnik]

Oliver Peters  wrote:
> reproduction
> 
> 
> CREATE TABLE staff_01(
> idINTEGER PRIMARY KEY AUTOINCREMENT,
> id_staff_editor   INTEGER NOT NULL,
> code  CHAR(2) NOT NULL,
> FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
> );
> 
> INSERT INTO staff_01(id_staff_editor,code) VALUES(1,'CB');
> 
> 
> PRAGMA foreign_keys = ON;
> 
> CREATE TABLE staff_02(
> idINTEGER PRIMARY KEY AUTOINCREMENT,
> id_staff_editor   INTEGER NOT NULL,
> code  CHAR(2) NOT NULL,
> FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
> );
> 
> INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');
> 
> For the last insert I get:
> 
> "Error: near line 20: no such table: main.staff"

And indeed, there is no such table. What is your question?
-- 
Igor Tandetnik

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

Re: [sqlite] foreign key error 01

[Black, Michael (IS)]

Instead of 
REFERENCES staff(id)
Try
REFERENCES staff_01(id)
 
That spelling thing will get you every time...:-)
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Oliver Peters
Sent: Mon 8/9/2010 7:46 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] foreign key error 01



Hello



environment
---

OS : Win XP (every patch)
sqlite : 3.7.0.1



reproduction


CREATE TABLE staff_01(
idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
);

INSERT INTO staff_01(id_staff_editor,code) VALUES(1,'CB');


PRAGMA foreign_keys = ON;

CREATE TABLE staff_02(
idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
);

INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');





For the last insert I get:

"Error: near line 20: no such table: main.staff"

Greetings
Oliver


___
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] foreign key error 01

[Oliver Peters]

Oliver Peters  writes:

sorry, my FK-clause was wrong (forgot the _01 & 0_2)

my mistake

(but the next thread will show the true error)

Oliver

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

[sqlite] foreign key error 01

[Oliver Peters]

Hello



environment
---

OS : Win XP (every patch)
sqlite : 3.7.0.1



reproduction


CREATE TABLE staff_01(
idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
);

INSERT INTO staff_01(id_staff_editor,code) VALUES(1,'CB');


PRAGMA foreign_keys = ON;

CREATE TABLE staff_02(
idINTEGER PRIMARY 
KEY AUTOINCREMENT,
id_staff_editor   INTEGER NOT NULL,
code  CHAR(2) NOT NULL,
FOREIGN KEY(id_staff_editor)  REFERENCES staff(id)
);

INSERT INTO staff_02(id_staff_editor,code) VALUES(1,'CB');





For the last insert I get:

"Error: near line 20: no such table: main.staff"

Greetings
Oliver


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

[sqlite] Mistake in documentation and question

[Ioannis Epaminonda]

In page http://www.sqlite.org/datatype3.html
under section 3.2 Affinity Of Comparison Operands 2nd bullet point should
read:
An expression  "CAST(expr AS type)"  instead of "CAST(expr TO type)"

In addition a quick question.
Is there a way to perform a division of two columns (real type) and force
the result to be presented/rounded in 2 decimal places ?

Thanks
-- 
View this message in context: 
http://old.nabble.com/Mistake-in-documentation-and-question-tp29387429p29387429.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Black, Michael (IS)]

Sounds to me like Boyer-Moore is needed
http://en.wikipedia.org/wiki/Boyer%E2%80%93Moore_string_search_algorithm
 
And...I would probably pre-load the database table into 26 seperate memory 
tables to avoid any SQL interactivity at all other than the initial loading.  
Adding the SQL layer slows things down far too much.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano
Sent: Mon 8/9/2010 7:00 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 
with suffix-tree tokenizer be the fast way?



First, permit me a little rant. As a user, I dislike this kind of
incremental search feature if there's no easy way to toggle it or to
configure it and the list of items will be large enough to cause a typing
lag. The feature can become an intrusive nuisance, the opposite of what is
intended.  Browsers put this feature on the URL address bar and Google has
it on its search-input. Keystrokes entered often get swallowed up. It's
worse than typing on a 300 baud dumb terminal, for at least on those ancient
machines your characters would eventually be displayed on the green screen,
whereas with today's browsers the characters often just get eaten; I find
myself having to retype the first few characters of a URL or search term far
too often.

I agree with Radzi's suggestion. Once you have the initial set of of hits
(rowid, name)  in an array, do the rest in procedurally rather than going
back against the database with a new SQL query and a longer search string.
That will be much faster that issuing a new SQL query after every keystroke.
 I would wait until the user had typed at least two characters before
kicking off the initial search because finding every value that contains a
common letter is not helpful when the list of matches is a very long one.

Regards
Tim Romano
Swarthmore PA




On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess  wrote:

> On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts  wrote:
> > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
> >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts 
> wrote:
> >>> FTS3 only searches full terms/words by default, but I think if I built
> a custom
> >>> tokenizer that returned all the suffix trees for a name:
> >>
> >> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
> >
> > Prefix searches don't allow matching in the middle of words. For
> > example, I want  "bert"
> > to match my name, "roberts".
>
> Darn.  Sorry, was only thinking with half my brain, and that half
> connected your problem up with some past idea.  You're right, you'd
> need the tidbits to get at the interior substrings.
>
> That said, you should be able to pretty easily copy the current
> tokenizer and modify it to return multiple tokens at a single
> location.
>
> -scott
> ___
> 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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Tim Romano]

First, permit me a little rant. As a user, I dislike this kind of
incremental search feature if there's no easy way to toggle it or to
configure it and the list of items will be large enough to cause a typing
lag. The feature can become an intrusive nuisance, the opposite of what is
intended.  Browsers put this feature on the URL address bar and Google has
it on its search-input. Keystrokes entered often get swallowed up. It's
worse than typing on a 300 baud dumb terminal, for at least on those ancient
machines your characters would eventually be displayed on the green screen,
whereas with today's browsers the characters often just get eaten; I find
myself having to retype the first few characters of a URL or search term far
too often.

I agree with Radzi's suggestion. Once you have the initial set of of hits
(rowid, name)  in an array, do the rest in procedurally rather than going
back against the database with a new SQL query and a longer search string.
That will be much faster that issuing a new SQL query after every keystroke.
 I would wait until the user had typed at least two characters before
kicking off the initial search because finding every value that contains a
common letter is not helpful when the list of matches is a very long one.

Regards
Tim Romano
Swarthmore PA




On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess  wrote:

> On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts  wrote:
> > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
> >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts 
> wrote:
> >>> FTS3 only searches full terms/words by default, but I think if I built
> a custom
> >>> tokenizer that returned all the suffix trees for a name:
> >>
> >> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
> >
> > Prefix searches don't allow matching in the middle of words. For
> > example, I want  "bert"
> > to match my name, "roberts".
>
> Darn.  Sorry, was only thinking with half my brain, and that half
> connected your problem up with some past idea.  You're right, you'd
> need the tidbits to get at the interior substrings.
>
> That said, you should be able to pretty easily copy the current
> tokenizer and modify it to return multiple tokens at a single
> location.
>
> -scott
> ___
> 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] typo fixing patch for http://www.sqlite.org/version3.html

[Simon Slavin]

On 9 Aug 2010, at 12:37pm, Richard Hipp wrote:

> On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin  wrote:
> 
>> On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote:
>> 
>>> I hope you'll find this little patch useful.
>> 
>> Attachments are stripped from messages to this list.  Please describe what
>> your patch does in your message, and post the URL where interested people
>> can download it.
> 
> I was looking at the mailing list setup yesterday, and if I read am reading
> the settings correctly, attachments are only stripped if their mimetype is
> not text/plain.  As an experiment, I added a short text attachment to this
> message to see if it will go through...

I received that with attachment intact.

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

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

[Richard Hipp]

On Mon, Aug 9, 2010 at 7:23 AM, Simon Slavin  wrote:

>
> On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote:
>
> > I hope you'll find this little patch useful.
>
> Attachments are stripped from messages to this list.  Please describe what
> your patch does in your message, and post the URL where interested people
> can download it.
>

I was looking at the mailing list setup yesterday, and if I read am reading
the settings correctly, attachments are only stripped if their mimetype is
not text/plain.  As an experiment, I added a short text attachment to this
message to see if it will go through


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



-- 
D. Richard Hipp
d...@sqlite.org
This is a test to see if a text/plain attachment will make it
through on the sqlite-users mailing list.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] typo fixing patch for http://www.sqlite.org/version3.html

[Simon Slavin]

On 8 Aug 2010, at 5:00pm, Adrian Aichner wrote:

> I hope you'll find this little patch useful.

Attachments are stripped from messages to this list.  Please describe what your 
patch does in your message, and post the URL where interested people can 
download it.

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

[sqlite] typo fixing patch for http://www.sqlite.org/version3.html

[Adrian Aichner]

Greetings!

I hope you'll find this little patch useful.

I'm not sure whether this webpage is auto-generated.

In that case the patch should still be useful to spot the typos.

Thanks for this great SQL database engine!

-- 
Adrian Aichner
 mailto:adrian.aich...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slowdown when adding terms to query

[Edward Hawke]

  Thank you all for your responses,

Igor - using a UNION made a huge difference, thank you.

I have a quick query on from the suggestion of adding indices for 
anything that appears either side of an equals sign. I have many 
user-selectable terms that can be added to the query. Is it worth adding 
indices for all of these? I have heard having too many indices can slow 
searches down, so I want to know where to stop, or is there no 
hard-and-fast rule, just test and see?

Regards,

Ed

On 06/08/2010 13:22, Igor Tandetnik wrote:
> Edward Hawke  wrote:
>> I have a query that is working at an acceptable speed, and I need to add
>> something else to it. As soon as I add the extra terms it grinds to a halt
>> (taking well over 5 minutes to perform the query on a relatively small
>> dataset c.100,000 records).
> But because you repeat the same table three-four times in the FROM clause, 
> thus building a cross-product, you are effectively working with a dataset of 
> some 10^15 records. Anything that suppress the use of indexes by SQLite will 
> cause performance to tank.
>
>> My acceptably fast query is fairly complicated as it pulls information from
>> a number of different tables, linking them all using WHERE clauses (I've
>> read somewhere this is the fastest way to do this in SQLite). It is shown
>> below (apologies for it's length) though I have cut out the information
>> about which columns it is selecting as it makes it too long to comfortably
>> read.
>>
>> *SELECT*  *FROM* Delivery, Match, Tour,
>> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
>> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
>> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
>> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
>> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
>> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
>> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
>> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
>> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
>> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
>> AND Batsman.ID = 1234567890);
>>
>> There are often multiple IDs specified at the end of the WHERE clause, as
>> these are added depending upon selections a user has made from a GUI.
>>
>> In a very specific case I need to select the above plus one unrelated row.
>> Therefore my query changes to (for example):
>>
>> *SELECT*  *FROM* Delivery, Match, Tour,
>> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam
>> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam,
>> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID =
>> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID =
>> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID =
>> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND
>> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
>> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
>> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
>> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
>> AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND
>> Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND
>> BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND
>> Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID =
>> Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND
>> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID =
>> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID =
>> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID
>> AND Delivery.ID = 1987654321);
> Using OR pretty much kills optimization in SQLite. Run this as two separate 
> queries, or as a UNION query with two subqueries, each of which only uses AND.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] synchronous issue

[Lei, Rick (GE EntSol, SensInsp)]

Hi, Simon,

Yes, I agree that a journal file in memory will lead some risk when I
set synchronous as off. However the speed is a much critical requirement
for me.

I have tried to close and reopen the file. However the time to open a
file and prepare the first query need more time.

Maybe I need spend more time to study the database design to enhance the
performance.

Thanks!

BR
Rick

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, August 09, 2010 3:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] synchronous issue


On 9 Aug 2010, at 7:13am, Lei, Rick (GE EntSol, SensInsp) wrote:

> In order to reduce the operation time, I set synchronous as OFF and
> journal_mode as MEMORY by PRAGMA command. However I found that the
data
> may be lost after inserting a BLOB data, even when I use a commit
> action.

Yes, transactions (BEGIN ... COMMIT) are a level above disk access
level.  Doing a COMMIT does not guarantee that your disk file is
completely up-to-date and consistent.

> I think the reason is synchronous is off. How can I know that
> there is some data which are not written to the disk?

The answer depends on whether you consider having data in the journal
file rather than the database as being 'written to disk'.  However, the
lack of documentation to say otherwise suggests that with the 'PRAGMA
synchronous' setting you've chosen you can never be sure of this.  The
fact that you are keeping your journal file in memory makes this even
more serious.

> And how can I
> force sqlite to write these data on disk?

Close the database and reopen it.  There is an alternative which I would
want to check the source code before committing myself about: switch
'PRAGMA synchronous' to FULL, then immediately back to OFF again.

It may be that the tricks you're using for speed are not the best way to
speed up your operations.  You might want to take a look at how you're
using transactions, and how many indexes you really need.  Or even how
your tables are organised.

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] synchronous issue

[Simon Slavin]

On 9 Aug 2010, at 7:13am, Lei, Rick (GE EntSol, SensInsp) wrote:

> In order to reduce the operation time, I set synchronous as OFF and
> journal_mode as MEMORY by PRAGMA command. However I found that the data
> may be lost after inserting a BLOB data, even when I use a commit
> action.

Yes, transactions (BEGIN ... COMMIT) are a level above disk access level.  
Doing a COMMIT does not guarantee that your disk file is completely up-to-date 
and consistent.

> I think the reason is synchronous is off. How can I know that
> there is some data which are not written to the disk?

The answer depends on whether you consider having data in the journal file 
rather than the database as being 'written to disk'.  However, the lack of 
documentation to say otherwise suggests that with the 'PRAGMA synchronous' 
setting you've chosen you can never be sure of this.  The fact that you are 
keeping your journal file in memory makes this even more serious.

> And how can I
> force sqlite to write these data on disk?

Close the database and reopen it.  There is an alternative which I would want 
to check the source code before committing myself about: switch 'PRAGMA 
synchronous' to FULL, then immediately back to OFF again.

It may be that the tricks you're using for speed are not the best way to speed 
up your operations.  You might want to take a look at how you're using 
transactions, and how many indexes you really need.  Or even how your tables 
are organised.

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

[sqlite] synchronous issue

[Lei, Rick (GE EntSol, SensInsp)]

Hi,

 

In order to reduce the operation time, I set synchronous as OFF and
journal_mode as MEMORY by PRAGMA command. However I found that the data
may be lost after inserting a BLOB data, even when I use a commit
action. I think the reason is synchronous is off. How can I know that
there is some data which are not written to the disk? And how can I
force sqlite to write these data on disk?

 

Thanks!

 

BR
Rick

 

 

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