[sqlite] Documentation of SQLite

[Patrik Nilsson]

Dear All,

Where can I find the downloadable HTML documentation? Not so long ago I 
could download it on the download page.


Best regards,
Patrik

--
PGP-key fingerprint: 1B30 7F61 AF9E 538A FCD6  2BE7 CED7 B0E4 3BF9 8D6C

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

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

[Patrik Nilsson]

>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY

"The sqlite3_reset() function is called to reset a prepared statement
object back to its initial state, ready to be re-executed. Any SQL
statement variables that had values bound to them using the
sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings()
to reset the bindings."

To close a prepared statement you need to use finalize.

"The sqlite3_finalize() function is called to delete a prepared
statement. If the most recent evaluation of the statement encountered no
errors or if the statement is never been evaluated, then
sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of
statement S failed, then sqlite3_finalize(S) returns the appropriate
error code or extended error code."

You have one prepared statement open and then try to start a
transaction. This gives you a busy error.

On 07/17/2013 06:56 PM, Dušan Paulovič wrote:
> If you remove a busy check, does it output any statements?
> Do you have any custom functions/operations running so they could block
> sqlite in creating new statement?
> 
> 
> 2013/7/17 Loren Keagle 
> 
>> Hi everyone,
>>
>> I have an interesting locking problem that I'm wondering if someone can
>> help with some insight.
>>
>> I have a master database with some metadata, and several sub-databases to
>> store logging events. I have one reader object and one writer object that
>> attach to the sub-databases and encapsulate the read/write operations
>> respectively.
>>
>> I've found a very unexpected locking behavior with the attached databases
>> and exclusive transactions. One of my unit tests does the following:
>>
>> Begin EXCLUSIVE TRANSACTION;
>> insert several rows of data;
>> Commit transaction;
>>
>> Prepare query statement;
>> Iterate through one or more rows;
>> Reset statement;
>>
>> Attempt to begin transaction; <--- SQLITE_BUSY
>> Would like to write more here, but can't unless I close/open the
>> connection;
>>
>> I can't seem to figure out any reason why I can't create a new exclusive
>> transaction here, and I feel it must have to do with the fact that I have
>> attached to sub-databases (possibly the same sub-database) with my
>> reader/writer objects. This is single threaded and only database connection
>> (with attach/detach logic).
>> I have verified that all statements prepared by the connection are
>> properly reset - this is handled by my C++ wrappers, and any errors will
>> throw an exception. I even iterated through all of the current statements
>> with the following code immediately before my transaction failure, with no
>> results:
>>
>> sqlite3_stmt *stmt = NULL;
>> while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL)
>> {
>> if (sqlite3_stmt_busy(stmt))
>> {
>> const char* sql = sqlite3_sql(stmt);
>> std::cout << sql << "\r\n";
>> }
>> }
>>
>> Can anyone think of a reason why attached databases would prevent entering
>> a second transaction? BTW, it doesn't seem to work with immediate
>> transactions either. If I remove the query, everything works fine.
>>
>> Thanks!
>>
>>
>> 
>> This email, including any attachments and files transmitted with it, are
>> for the sole use of the intended recipient(s) to whom this email is
>> addressed, and may contain confidential and/or privileged information. Any
>> unauthorized review, use, disclosure or distribution is prohibited. If you
>> are not the intended recipient, please be advised that you have received
>> this email in error, and please contact the sender by reply email and
>> destroy all copies (including all electronic and hard copies) of the
>> original message. 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
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

I believe the performance of my current statements is better and clearer.

>>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>>> then issue an update statement. I think "insert or update" will
>>> increase performance.

Although I'm not happy with it.

/Patrik

On 06/27/2013 03:08 PM, Hick Gunter wrote:
> Use parameters and bind...
> 
> REPLACE INTO table_test
> SELECT :key, a, b, :val FROM table_test WHERE id=:key
> UNION ALL
> SELECT :key, NULL, NULL, :val
> LIMIT 1;
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 15:01
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] request for feature: insert or update
> 
> Thank you for your suggestion!
> 
> I would like to have all in one statement, then "insert or update" is 
> perfect. This is least error prone. If you split the statement into several, 
> you will end up in more statements to test and more bugs. (You write "3" on 
> two places.)
> 
> Working with blobs, I expect this procedure to waste more time.
> 
>> REPLACE INTO table_test
>>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>>    UNION ALL
>>SELECT 1, NULL, NULL, 3
>>LIMIT 1;
> 
> /Patrik
> 
> On 06/27/2013 02:46 PM, Richard Hipp wrote:
>> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
>> <nipatriknils...@gmail.com>wrote:
>>
>>> Hi All!
>>>
>>> A feature I'm missing is a syntax like with "insert or update".
>>>
>>> You define a table as:
>>> "create table table_test (id as integer primary key, a as integer, b
>>> as integer, c as integer)"
>>>
>>> Then you know that the "id" is unique and you only can have one row
>>> with that integer.
>>>
>>> Then you can give the following statements:
>>> insert or update into table_test (id,c) values (1,3)
>>>
>>
>> REPLACE INTO table_test
>>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>>UNION ALL
>>SELECT 1, NULL, NULL, 3
>>LIMIT 1;
>>
>>
>>> insert or update into table_test (id,b) values (1,2) insert or update
>>> into table_test (id,a) values (1,1) insert or update into table_test
>>> (id,a) values (5,13)
>>>
>>> This result is the following set:
>>> 1|1|2|3
>>> 5|13||
>>>
>>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>>> then issue an update statement. I think "insert or update" will
>>> increase performance.
>>>
>>> If the "insert or update" can't perform its operation, it can issue a
>>> SQLITE_AMBIGUOUS error.
>>>
>>> Best regards,
>>> Patrik
>>>
>>> --
>>> ASCII ribbon campaign ( )
>>>  against HTML e-mail   X
>>>  www.asciiribbon.org  / \
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

Thank you for your suggestion!

I would like to have all in one statement, then "insert or update" is
perfect. This is least error prone. If you split the statement into
several, you will end up in more statements to test and more bugs. (You
write "3" on two places.)

Working with blobs, I expect this procedure to waste more time.

> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;

/Patrik

On 06/27/2013 02:46 PM, Richard Hipp wrote:
> On Thu, Jun 27, 2013 at 6:05 AM, Patrik Nilsson
> <nipatriknils...@gmail.com>wrote:
> 
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3)
>>
> 
> REPLACE INTO table_test
>SELECT 1, a, b, 3 FROM table_test WHERE id=1
>UNION ALL
>SELECT 1, NULL, NULL, 3
>LIMIT 1;
> 
> 
>> insert or update into table_test (id,b) values (1,2)
>> insert or update into table_test (id,a) values (1,1)
>> insert or update into table_test (id,a) values (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will increase
>> performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

The SQLite syntax "replace into" does almost what I want.

It also erases the other values of the same line, which I don't want.

On 06/27/2013 01:38 PM, Simon Slavin wrote:
> 
> On 27 Jun 2013, at 11:05am, Patrik Nilsson <nipatriknils...@gmail.com> wrote:
> 
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3)
>> insert or update into table_test (id,b) values (1,2)
>> insert or update into table_test (id,a) values (1,1)
>> insert or update into table_test (id,a) values (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
> 
> I understand your desire for the combination 'INSERT OR UPDATE' but I think 
> there is too much danger of SQL not understanding whether the UPDATE should 
> be affecting a single row or multiple rows or one new row.  The analysis 
> stage for the instruction would have to figure out whether you were correctly 
> specifying the whole of the primary key of the row.  Given that there may be 
> no primary row specified in the table definition, or that the primary key may 
> be a compound key, and that you may be using different COLLATions, this 
> analysis would involve quite a few instructions to make sure it got 
> everything perfectly right, which means it would be slow.
> 
> For further discussion on this search for the conventional term for what you 
> asked for: UPSERT, and for the endless discussion of exactly what the MERGE 
> does in discussion fora about other implementations of SQL.  You will see 
> that even experienced SQL writers have a hard time explaining clearly what 
> MERGE does under different circumstances.
> 
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and
>> then issue an update statement. I think "insert or update" will increase
>> performance.
> 
> I think that’s the best way to clearly explain to SQLite what you want done.  
> I agree that it is two commands long, but it takes advantage of existing 
> SQLite statements and it’s impossible for someone reading your code to 
> misunderstand what you want to happen.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> .
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

On 06/27/2013 02:16 PM, Hick Gunter wrote:
>> -Ursprüngliche Nachricht- Von: Simon Slavin
>> [mailto:slav...@bigfraud.org] Betreff: Re: [sqlite] request for
>> feature: insert or update
>> 
>> 
>> On 27 Jun 2013, at 11:05am, Patrik Nilsson
>> <nipatriknils...@gmail.com> wrote:
>> 
>> 
>>> Now I'm doing: "insert or ignore into table_test (id) values (1)"
>>> and then issue an update statement. I think "insert or update"
>>> will increase performance.
>> 
>> I think that's the best way to clearly explain to SQLite what you
>> want done.  I agree that it is two commands long, but it takes
>> advantage of existing SQLite statements and >it's impossible for
>> someone reading your code to misunderstand what you want to
>> happen.
>> 
>> Simon.
> 
> The sequence "insert or ignore" followed by "update" is IMHO
> indicative of file-oriented, procedural thinking, where you have to
> locate the record first and update the desired fields second (or fail
> to find a record and create a new one). Implementing this 1:1 in SQL
> (which is set-oriented and rule-based) causes the standard path of
> execution (record is already present) to always perform the exception
> case insert.
> 
> That is why I proposed doing the standard path of execution (the
> update) first, and then checking for the exception condition (no rows
> processed) before doing the exception processing (insert with the
> given values).
> 

It can be implemented in this way, but writing the same statement two
times means it is error prone. I want to write the statement once and
verify it.

Otherwise you can end up with bugs everywhere.

> While the first method will reliably run with implicit (autocommit)
> or explicit transactions, the second requires either an explicit
> transaction or a loop. Consider:
> 
> 
> BEGIN; UPDATE; if (no rows) then INSERT; COMMIT;
> 
> --> the first thread to enter then transaction will do the INSERT -->
> all others do only the UPDATE
> 
> repeat { UPDATE; If (no rows) then INSERT; } until (no error);
> 
> --> If two threads happen to attempt the INSERT, one will fail -->
> The failing thread must retry the UPDATE
> 
> 
> 
> 
> 
> --
>
> 
Gunter Hick
> Software Engineer Scientific Games International GmbH Klitschgasse 2
> – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100
> 0 E-Mail: h...@scigames.at
> 
> This e-mail is confidential and may well also be legally privileged.
> If you have received it in error, you are on notice as to its status
> and accordingly please notify us immediately by reply e-mail and then
> delete this message from your system. Please do not copy it or use it
> for any purposes, or disclose its contents to any person as to do so
> could be a breach of confidence. Thank you for your cooperation. 
> ___ sqlite-users mailing
> list sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

You are right it won't fail if you define the table with autoincrement:

"create table table_test (id as integer primary key AUTOINCREMENT, a as
integer, b as integer, c as integer)"

/Patrik

On 06/27/2013 01:45 PM, Hick Gunter wrote:
> Insert into table_test (a,b) values (1,2); inserts a new row with a default 
> id field (see SQLite documentation) and does not fail.
> 
> Update table_test set a=1, b=2; modifies all rows of the table and does not 
> fail either.
> 
> Why should "insert or update" fail???
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:55
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] request for feature: insert or update
> 
>>
>> Which circumstances are you thinking of that would cause the proposed
> "insert or update" to fail (other than those that would cause the update to 
> fail too)?
> 
> It is a fail condition when the statement is ambiguous.
> 
> create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
> 
> insert or update into table_test (id,c) values (1,3) (This is OK)
> 
> insert or update into table_test (a,b) values (1,2) (It is not OK since you 
> don't have a unique value to relate to.)
> 
> 
> On 06/27/2013 12:32 PM, Hick Gunter wrote:
>> You can check the number of rows modified by an UPDATE statement using
>> the sqlite3_changes() interface. (Using pragma count_changes is
>> deprecated!)
>>
>> Within a transaction, when you issue an
>>
>> UPDATE table_test SET = WHERE id = ;
>>
>> and sqlite3_changes() returns 0 then you need to
>>
>> INSERT INTO table_test (id,) VALUES (,);
>>
>> otherwise you are good to go.
>>
>> Which circumstances are you thinking of that would cause the proposed 
>> "insert or update" to fail (other than those that would cause the update to 
>> fail too)?
>>
>> -Ursprüngliche Nachricht-
>> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
>> Gesendet: Donnerstag, 27. Juni 2013 12:05
>> An: General Discussion of SQLite Database
>> Betreff: [sqlite] request for feature: insert or update
>>
>> Hi All!
>>
>> A feature I'm missing is a syntax like with "insert or update".
>>
>> You define a table as:
>> "create table table_test (id as integer primary key, a as integer, b as 
>> integer, c as integer)"
>>
>> Then you know that the "id" is unique and you only can have one row with 
>> that integer.
>>
>> Then you can give the following statements:
>> insert or update into table_test (id,c) values (1,3) insert or update
>> into table_test (id,b) values (1,2) insert or update into table_test
>> (id,a) values (1,1) insert or update into table_test (id,a) values
>> (5,13)
>>
>> This result is the following set:
>> 1|1|2|3
>> 5|13||
>>
>> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
>> issue an update statement. I think "insert or update" will increase 
>> performance.
>>
>> If the "insert or update" can't perform its operation, it can issue a 
>> SQLITE_AMBIGUOUS error.
>>
>> Best regards,
>> Patrik
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> --
>> 
>>  Gunter Hick
>> Software Engineer
>> Scientific Games International GmbH
>> Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien
>> Tel: +43 1 80100 0
>> E-Mail: h...@scigames.at
>>
>> This e-mail is confidential and may well also be legally privileged. If you 
>> have received it in error, you are on notice as to its status and 
>> accordingly please notify us immediately by reply e-mail and then delete 
>> this message from your system. Please do not copy it or use it for any 
>> purposes, or disclose its contents to any person as to do so could be a 
>> breach of confidence. Thank you for your cooperation.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> --
> ASCII ri

Re: [sqlite] request for feature: insert or update

[Patrik Nilsson]

>
> Which circumstances are you thinking of that would cause the proposed
"insert or update" to fail (other than those that would cause the update
to fail too)?

It is a fail condition when the statement is ambiguous.

create table table_test (id as integer primary key, a as integer, b as
integer, c as integer)"

insert or update into table_test (id,c) values (1,3)
(This is OK)

insert or update into table_test (a,b) values (1,2)
(It is not OK since you don't have a unique value to relate to.)


On 06/27/2013 12:32 PM, Hick Gunter wrote:
> You can check the number of rows modified by an UPDATE statement using the 
> sqlite3_changes() interface. (Using pragma count_changes is deprecated!)
> 
> Within a transaction, when you issue an
> 
> UPDATE table_test SET = WHERE id = ;
> 
> and sqlite3_changes() returns 0 then you need to
> 
> INSERT INTO table_test (id,) VALUES (,);
> 
> otherwise you are good to go.
> 
> Which circumstances are you thinking of that would cause the proposed "insert 
> or update" to fail (other than those that would cause the update to fail too)?
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Donnerstag, 27. Juni 2013 12:05
> An: General Discussion of SQLite Database
> Betreff: [sqlite] request for feature: insert or update
> 
> Hi All!
> 
> A feature I'm missing is a syntax like with "insert or update".
> 
> You define a table as:
> "create table table_test (id as integer primary key, a as integer, b as 
> integer, c as integer)"
> 
> Then you know that the "id" is unique and you only can have one row with that 
> integer.
> 
> Then you can give the following statements:
> insert or update into table_test (id,c) values (1,3) insert or update into 
> table_test (id,b) values (1,2) insert or update into table_test (id,a) values 
> (1,1) insert or update into table_test (id,a) values (5,13)
> 
> This result is the following set:
> 1|1|2|3
> 5|13||
> 
> Now I'm doing: "insert or ignore into table_test (id) values (1)" and then 
> issue an update statement. I think "insert or update" will increase 
> performance.
> 
> If the "insert or update" can't perform its operation, it can issue a 
> SQLITE_AMBIGUOUS error.
> 
> Best regards,
> Patrik
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] request for feature: insert or update

[Patrik Nilsson]

Hi All!

A feature I'm missing is a syntax like with "insert or update".

You define a table as:
"create table table_test (id as integer primary key, a as integer, b as
integer, c as integer)"

Then you know that the "id" is unique and you only can have one row with
that integer.

Then you can give the following statements:
insert or update into table_test (id,c) values (1,3)
insert or update into table_test (id,b) values (1,2)
insert or update into table_test (id,a) values (1,1)
insert or update into table_test (id,a) values (5,13)

This result is the following set:
1|1|2|3
5|13||

Now I'm doing: "insert or ignore into table_test (id) values (1)" and
then issue an update statement. I think "insert or update" will increase
performance.

If the "insert or update" can't perform its operation, it can issue a
SQLITE_AMBIGUOUS error.

Best regards,
Patrik

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MONTH function

[Patrik Nilsson]

You can write:

"select cast(strftime('%m',datetime('now')) as integer)=6"
1


On 06/23/2013 09:45 PM, Lucas wrote:
> Hello,
> 
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
> 
> This works perfect under MySQL or MSSQL.
> 
> Do you plan to introduce MONTH and YEAR functions?.
> 
> 
> Also, the function strftime('%m', fecha) does not solve the issue:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
> 
> 
> Please, any idea of how to solve it.
> 
> 
> Thank you. Best regards,
> 
> Lucas de Beltran
> Caritas España
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MONTH function

[Patrik Nilsson]

"select strftime('%m',datetime('now'))='06'"
1

"select strftime('%m',datetime('now'))=6"
0

It is considered as a string, not a number.


On 06/23/2013 09:45 PM, Lucas wrote:
> Hello,
> 
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
> 
> This works perfect under MySQL or MSSQL.
> 
> Do you plan to introduce MONTH and YEAR functions?.
> 
> 
> Also, the function strftime('%m', fecha) does not solve the issue:
> 
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
> 
> 
> Please, any idea of how to solve it.
> 
> 
> Thank you. Best regards,
> 
> Lucas de Beltran
> Caritas España
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deleting with offset when grouping

[Patrik Nilsson]

Thank you very much. It works.

Vielen Dank,
Patrik

On 05/22/2013 12:22 PM, Hick Gunter wrote:
> 
> DELETE FROM history WHERE 2 < (SELECT count() FROM history n WHERE 
> n.id=history.id and n.lastactivity > history.lastactivity);
> 
> -Ursprüngliche Nachricht-
> Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com]
> Gesendet: Mittwoch, 22. Mai 2013 10:48
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Deleting with offset when grouping
> 
> Hi All,
> 
> I can't figure out how to delete with offset using groups.
> 
> Imagine I have a table
> create table history(id integer, lastactivity datetime default
> (datetime('now')))
> 
> and I insert values as
> 
> insert into history (id) values (1)
> 
> A resulting table might look
> 
> 1|2013-05-22 07:50:05
> 1|2013-05-22 07:50:08
> 1|2013-05-22 07:50:10
> 1|2013-05-22 07:50:12
> 2|2013-05-22 07:50:16
> 1|2013-05-22 07:50:18
> 2|2013-05-22 07:50:20
> 2|2013-05-22 07:50:21
> 2|2013-05-22 07:50:22
> 1|2013-05-22 07:50:24
> 3|2013-05-22 07:50:34
> 1|2013-05-22 07:50:36
> 3|2013-05-22 07:50:37
> 3|2013-05-22 07:50:39
> 1|2013-05-22 07:50:41
> 6|2013-05-22 07:58:24
> 
> Now I would like to delete the oldest rows if a group of id has more than 
> three, but I would like to keep the youngest ones in each id group.
> I can delete with offset for the overall table. Is this possible for a group?
> 
> The lastactivity column is unique.
> 
> Cheers,
> Patrik
> 
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deleting with offset when grouping

[Patrik Nilsson]

Hi All,

I can't figure out how to delete with offset using groups.

Imagine I have a table
create table history(id integer, lastactivity datetime default
(datetime('now')))

and I insert values as

insert into history (id) values (1)

A resulting table might look

1|2013-05-22 07:50:05
1|2013-05-22 07:50:08
1|2013-05-22 07:50:10
1|2013-05-22 07:50:12
2|2013-05-22 07:50:16
1|2013-05-22 07:50:18
2|2013-05-22 07:50:20
2|2013-05-22 07:50:21
2|2013-05-22 07:50:22
1|2013-05-22 07:50:24
3|2013-05-22 07:50:34
1|2013-05-22 07:50:36
3|2013-05-22 07:50:37
3|2013-05-22 07:50:39
1|2013-05-22 07:50:41
6|2013-05-22 07:58:24

Now I would like to delete the oldest rows if a group of id has more
than three, but I would like to keep the youngest ones in each id group.
I can delete with offset for the overall table. Is this possible for a
group?

The lastactivity column is unique.

Cheers,
Patrik

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inner vs. outer join inconsistency

[Patrik Nilsson]

Could it be some of the features that SQLite doesn't support?

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

/Patrik

On 03/03/2013 08:48 PM, Tom Matrix wrote:
> Hello,
> 
> I’ve encountered a problem, which is hardly reproducable on arbitrary
> databases, therefore I attached one.
> The problem is that inner join omits the result on large datasets. But
> again, ’large’ does not necessarly refer simply the amount of data; I
> couldn’t reproduce it on different (arbitrary) databases with larger
> datasets, but I could on another database with the same structure.
> 
> To be more specific:
> 
> The following query reports 18900080 rows (after some computation time):
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> 
> However, the following one reports 0 rows immediately:
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> JOIN labels la2 ON il2.labelid = la2.id
> 
> This is not what I expected. So I checked, that for example
> 
> SELECT il2.labelid
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> JOIN entryintervals ei2 ON en2.id = ei2.entryid
> JOIN interval2label il2 ON ei2.id = il2.entry_intervalid
> LEFT JOIN labels la2ON il2.labelid = la2.id
> LIMIT 1
> 
> returns the id 47. The empty set, returned by the query containing
> inner joins only, implies that no row exists with id 47 in the
> ’labels’ table. This is, however, not true:
> 
> SELECT * FROM labels WHERE id = 47
> 
> returns the appropriate row...
> 
> I’m using the latest (SQLite version 3.7.15.2 2013-01-09 11:53:05)
> version on Windows.
> Previously, I used SQLite version 3.7.13 2012-06-11 02:05:22 with
> Cygwin, which gave similar result, except that it was enough to have
> less joins:
> 
> SELECT COUNT(*)
> FROM entryintervals ei1
> JOIN interval2label il1 ON ei1.id = il1.entry_intervalid
> JOIN labels la1 ON il1.labelid = la1.id
> JOIN labelgroups lg1ON la1.groupid = lg1.id
> JOIN entries en1ON ei1.entryid = en1.id
> JOIN modules mo1ON en1.moduleid = mo1.id
> JOIN measurements me1   ON en1.measurementid = me1.id
> JOIN entries en2ON en1.measurementid = en2.measurementid
> JOIN modules mo2ON en2.moduleid = mo2.id
> [LEFT] JOIN entryintervals ei2 ON en2.id = ei2.entryid
> 
> Have you ever experienced such behavior?
> Please take a look at it, as it might be a rare but serious low-level
> problem.
> 
> Thanks,
> Tamás
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

-- 
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] type definitions or aliases

[Patrik Nilsson]

On 02/19/2013 05:00 PM, Stephen Chrzanowski wrote:
> My best suggestion would be to modify the code so when you compile the
> string to make the select/delete/update, it uses the table name.  For
> testing purposes, you could have a combo box, or text field, or a text file
> that has the contents to point to the table you need.

I hoped not to rewrite most of the statements...:( Too many to debug!

> Either that, copy your subject table to a memory table with the backup API,
> and run against that.  In one of my applications, I offer the user the
> choice to run the "database" from memory or from the drive directly.  When
> the application is run, it first reads the HDD file first, drops it to
> memory, works off memory, and when the application closes, it drops the
> data back to the drive.

Generally no. I have databases bigger than my RAM. And when I go public
with my application... I can't.

> (I'm sick - if that last sentence doesn't make sense, blame my kids and the
> meds! ;) )

(Everything you wrote made sense...)

It seems to me that the easiest way is to create a table "version" and
put some information into it.

> On Tue, Feb 19, 2013 at 10:37 AM, Patrik Nilsson
> <nipatriknils...@gmail.com>wrote:
> 
>> In my case I read the restriction of the triggers: I use database.table
>> and I insert with default values.
>>
>> I can't use triggers for this.
>>
>> /Patrik
>>
>> On 02/19/2013 04:27 PM, Dave McKee wrote:
>>>> Is it possible to use triggers for this?
>>>
>>> Yes.
>>>
>>> "Triggers may be created on views, as well as ordinary tables, by
>>> specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
>>> INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
>>> not an error to execute an INSERT, DELETE or UPDATE statement on the
>> view,
>>> respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
>>> causes the associated triggers to fire. The real tables underlying the
>> view
>>> are not modified (except possibly explicitly, by a trigger program)."
>>>
>>> http://www.sqlite.org/lang_createtrigger.html
>>> ___
>>> 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] type definitions or aliases

[Patrik Nilsson]

In my case I read the restriction of the triggers: I use database.table
and I insert with default values.

I can't use triggers for this.

/Patrik

On 02/19/2013 04:27 PM, Dave McKee wrote:
>> Is it possible to use triggers for this?
> 
> Yes.
> 
> "Triggers may be created on views, as well as ordinary tables, by
> specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON
> INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is
> not an error to execute an INSERT, DELETE or UPDATE statement on the view,
> respectively. Instead, executing an INSERT, DELETE or UPDATE on the view
> causes the associated triggers to fire. The real tables underlying the view
> are not modified (except possibly explicitly, by a trigger program)."
> 
> http://www.sqlite.org/lang_createtrigger.html
> ___
> 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] type definitions or aliases

[Patrik Nilsson]

Quite good, but I read that "You cannot DELETE, INSERT, or UPDATE a
view". I need to do that!

Is it possible to use triggers for this?

Patrik

On 02/19/2013 04:06 PM, Stephan Beal wrote:
> On Tue, Feb 19, 2013 at 4:04 PM, Patrik Nilsson
> <nipatriknils...@gmail.com>wrote:
> 
>> (some alias command: abc is an alias of abc_v1 for this connection)
>>
>> select * from abc
>>
>> (The last command would then "select * from abc_v1".)
>>
> 
> How about:
> 
> CREATE TEMPORARY VIEW abc AS SELECT * FROM abc_v1;
> 
> e.g.
> 
> sqlite> create temporary view x as select * from sqlite_master;
> sqlite> select * from x;
> sqlite> create table t(a,b,c);
> sqlite> select * from x;
> table|t|t|2|CREATE TABLE t(a,b,c)
> 
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] type definitions or aliases

[Patrik Nilsson]

Hi,

I change database format frequently and would like to have an easy way
to distinguish the tables.

Is it possible to have a pointer (like typedef in C) that points to the
current used table? As an temporary pointer?

Example:

I have a table named abc, which I version as abc_v1, abc_v2, ... Can the
table abc automatically point to a table of my choise?

create table abc_v1(a integer,b integer)

(some alias command: abc is an alias of abc_v1 for this connection)

select * from abc

(The last command would then "select * from abc_v1".)

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

Re: [sqlite] SQLite Trace Log

[Patrik Nilsson]

Hello,

Not exactly the program language you asked for, but here it is how I
have it in C for gtk:

Call this function after opening the connection:

sqlite3_trace ( sqliteconnection, sqlite_trace, NULL );


static void sqlite_trace ( void *arg, const char *query )
{
FILE *file = fopen ( "/tmp/sqlitetrace.txt", "a" );

if ( file != 0 )
{
GString *stringquery = g_string_new ( query );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringquery );

GString *stringleft = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringleft );
GString *stringright = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringright );

GString *stringconcatenated = g_string_new ( "" );
dmemory_gstring_free_on_destruction_with_temporary_object (
stringconcatenated );

if ( stringquery->len > 127 )
{
g_string_assign ( stringleft, stringquery->str );
g_string_truncate ( stringleft, 64 );

if ( stringquery->len > 63 )
{
g_string_assign ( stringright, stringquery->str );
g_string_erase ( stringright, 0, stringquery->len - 63 );
}

g_string_assign ( stringconcatenated, stringleft->str );

if ( stringright->len > 0 )
{
g_string_append ( stringconcatenated, " [...] " );
g_string_append ( stringconcatenated, stringright->str );
}

fprintf ( file, "%s\n", stringconcatenated->str );
}
else
{
fprintf ( file, "%s\n", stringquery->str );
}

fclose ( file );
}
}

Best Regards,
Patrik

On 02/11/2013 08:17 AM, Winston Brummer wrote:
> Hi
> 
> I saw in the version history that SQLite makes use of trace listeners.
> Could anyone give me an example of how to attach a trace listener to an
> application that uses System.Data.SQLite? Specifically related to the
> compact frame work version for Windows Mobile and Pocket PC.
> 
> Any help would be greatly appreciated.
> Winston
> ___
> 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] random, infrequent disk I/O errors

[Patrik Nilsson]

Hello,

This letter is a help getting you started to finding your error.

There are many SQLITE_IOERR-errors, for example SQLITE_IOERR_NOMEM which
means out of memory.

I have noticed in my application that I sometimes get out of memory when
calling g_string_new() after modifying the whole table, i.e. adding a
new column.

After thinking about what it is I compiled the sqlite-code with
SQLITE_ENABLE_MEMORY_MANAGEMENT and called sqlite3_release_memory()
after doing much change to the database. I haven't got that error since.

Even though this change is relatively recently I have made test
stresses, but as for now it seems to work.

"The sqlite3_release_memory() interface attempts to free N bytes of heap
memory by deallocating non-essential memory allocations held by the
database library."

Line 23566 in sqlite.c v3.7.15.1:
/*
** This routine translates a standard POSIX errno code into something
** useful to the clients of the sqlite3 functions.  Specifically, it is
** intended to translate a variety of "try again" errors into SQLITE_BUSY
** and a variety of "please close the file descriptor NOW" errors into
** SQLITE_IOERR
**
** Errors during initialization of locks, or file system support for locks,
** should handle ENOLCK, ENOTSUP, EOPNOTSUPP separately.
*/

Line 1021 in sqlite.c v3.7.15.1:
#define SQLITE_IOERR_READ  (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE  (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE(SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED   (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK  (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN   (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE   (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK   (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK  (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT  (SQLITE_IOERR | (23<<8))

Best Regards,
Patrik


On 02/10/2013 05:28 PM, Greg Janée wrote:
> Hello, I'm running a web service that uses SQLite that throws a disk I/O
> exception every once in a while, meaning once every few weeks.
> 
> Details: SQLite 3.7.0.1, being called from an Apache/Django/Python
> multi-threaded application running on Solaris 10.  The database file is
> on a local filesystem, and is <200MB.  Disk space is not an issue on
> that filesystem.
> 
> Everything appears fine, and then one transaction out of the blue gets a
> disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes an
> integrity check.  These disk I/O errors don't seem to correlate with
> anything.  When one happens, there isn't a lot of activity on the system
> (individual transactions occurring once every few seconds, say, so
> threading/locking/contention wouldn't seem to be an issue), and the
> transactions immediately before and after the failed transaction
> succeeded just fine.  If there is any correlation, they all seem to
> happen in the middle of the night.  I've checked with my sysadmin, and
> he can't think of anything (backups, virus scans, etc.) that might be
> happening at that time.
> 
> I've searched for help on this topic and have come up with nothing.  It
> should be clear from the above that directory permissions are not the
> problem.
> 
> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm
> using doesn't provide access to any more information (if there is any to
> be had).
> 
> Thanks in advance,
> -Greg
> 
> ___
> 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] INSERT OR IGNORE - returning new or existing rowid

[Patrik Nilsson]

Maybe is faster to swap the commands:

1. Select. If found done.
2. Insert (not doing ignore to get an error if it fails) and then get
the rowid from sqlite3_last_insert_rowid.

Patrik

On 01/04/2013 11:18 AM, Simon Slavin wrote:
> 
> On 4 Jan 2013, at 9:55am, Krzysztof  wrote:
> 
>> When I use INSERT OR IGNORE, if insertion fail (record exists),
>> then sqlite3_last_insert_rowid does return nothing. Is exists similar
>> solution which:
>> 1. If insert success then return new rowid
>> 2. If insert fail (record exists) then return rowid of existing record
> 
> No.  You are correct about how it works.  The best solution is to do the 
> INSERT OR IGNORE as you're doing it already, then to use a SELECT to find the 
> rowid.
> 
> 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] strange behavior with integer with where clause

[Patrik Nilsson]

Since I have blobs, datetimes, and integers I managed to use the
hex-convertion on some integers as well. They are stored as text in my
program.

I changed the code for the interval integer, so it is inserted as an
integer. It seems to work. I don't get this strange kind of selection
anymore.

Thank you for the dump command.

/Patrik

On 12/23/2012 09:52 PM, Larry Brasfield wrote:
> Patrik Nilsson wrote:
>> After dumping the database I found that the line of insertion looks like
>> this
>>
>> INSERT INTO "repetition"
>> VALUES(617,X'323031322D31322D32332031393A33303A3436',X'30',X'323031322D31322D32332031393A33303A3436',0,1,1,0);
>>
>>
>> Does SQLite manage the insertion with hexadecimal characters?
> 
> Those hex digits are part of what the SQLite docs call a blob literal.
> 
> The 3rd column is greater than zero and is the ASCII code for the
> character we call zero.  I expect that your code for displaying results
> is disguising that fact.  I also expect that you will want to find
> where/why blobs are being inserted, and get the inserted types to be
> closer to what is supposed to be represented.  (number, string, etc.)
> The BLOB is really for raw data that is not to be further interpreted as
> anything else.
> 
> Cheers,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] strange behavior with integer with where clause

[Patrik Nilsson]

> What do you get with query
>   select * from repetition where cast(interval as integer)==0 and
> interval>0

"select * from repetition where cast(interval as integer)==0 and
interval>0 and id=617"

With this I get the same as the strange one. Without "id=617" it is
still selected.

/Patrik

On 12/23/2012 09:02 PM, Larry Brasfield wrote:
> Patrik Nilsson wrote:
>> When I perform a select as the following
>>
>> select * from repetition where interval>0 and id=617
>>
>> the result is unexpected
>>
>> 617|2012-12-23 19:30:46|0|2012-12-23 19:30:46|0|1|1|0
>>
>> It shows a result with "interval" zero, although I requested everything
>> above zero.
>>
>> When I select using the same statement and interval equal to zero, there
>> is no result lines.
>>
>> select * from repetition where interval=0 and id=617
>>
>> The table looks like:
>>
>> 0|id|integer|0||1
>> 1|last|datetime|1|datetime('now')|0
>> 2|interval|integer|0||0
>> 3|next|datetime|1|datetime('now')|0
>> 4|priority|integer|1|0|0
>> 5|repeat|integer|1|1|0
>> 6|valid|integer|1|0|0
>> 7|sound|integer|1|0|0
> 
> What do you get with query
>   select * from repetition where cast(interval as integer)==0 and
> interval>0
> ?
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] strange behavior with integer with where clause

[Patrik Nilsson]

Hi All!

When I perform a select as the following

select * from repetition where interval>0 and id=617

the result is unexpected

617|2012-12-23 19:30:46|0|2012-12-23 19:30:46|0|1|1|0

It shows a result with "interval" zero, although I requested everything
above zero.

When I select using the same statement and interval equal to zero, there
is no result lines.

select * from repetition where interval=0 and id=617

The table looks like:

0|id|integer|0||1
1|last|datetime|1|datetime('now')|0
2|interval|integer|0||0
3|next|datetime|1|datetime('now')|0
4|priority|integer|1|0|0
5|repeat|integer|1|1|0
6|valid|integer|1|0|0
7|sound|integer|1|0|0

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

Re: [sqlite] SQLite Version 3.7.15.1

[Patrik Nilsson]

Thank you for the release!

I can't find sqlite-shell-linux-x86-3071501.zip and sqlite-doc-3071501.zip.

On 12/19/2012 10:24 PM, D. Richard Hipp wrote:
> SQLite version 3.7.15.1, a patch release, is now available on the SQLite 
> website:
> 
>  http://www.sqlite.org/
> 
> This patch release fixes a single bug the managed to sneak into the 3.7.15 
> release from last week.  Two lines of code changed and one assert() was added:
> 
> 
> http://www.sqlite.org/src/fdiff?v1=53b991af50dab230=74d72b1613aac386#chunk1
> 
> The complete patch includes the change above, and some new test cases, and 
> the version number and configure script were updated.  A description of the 
> bug that was fixed is here:
> 
> http://www.sqlite.org/src/info/a7b7803e8d1e869
> 
> The bug causes a NULL pointer dereference given some unusual but perfectly 
> legal SQL.   The bug is not data dependent and is thus not a security 
> vulnerability (since if an attacker can inject arbitrary SQL into your 
> application, then you have already been compromised).  If you application 
> does not use the unusual SQL construct necessary to tickle this bug (and most 
> applications don't) then you are perfectly safe staying with whatever prior 
> version of SQLite you are currently using.  Nevertheless, upgrading to 
> 3.7.15.1 is recommended.
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> 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 bug concerning SQLite - no suport for Extended ASCII (filepath)

[Patrik Nilsson]

Most probably you need to convert the file path (string) into rtf8 and
then send it to SQLite.

/Patrik

On 11/23/2012 05:35 PM, Michal Walczak wrote:
> I am using SQLite Release 3.7.14.1 downloaded by Nuget with Visual Studio 
> 2012 Ultimate RTM on Windows 8 Pro.
> 
> Because I am Polish, I am using the extended ASCII charset, also my username 
> uses it. Apparently SQLite is not able to process the file name with the file 
> path which includes this characters.
> 
> 
> Error:
> "“Could not open database file: 
> C:\Users\Michał\AppData\Local\Packages\e12bc76c-4f76-4198-8ed7-a6b0d8828dd3_e7ma2tg35b58y\LocalState\people.db
>  (14)”"
> 
> 
> Temporary fix:
> I have created a local account without Extended ASCII and the project with 
> the database works.
> 
> Best Regards,
> Michał Walczak
> 
> ___
> 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] FW: how to select " char in sqlite

[Patrik Nilsson]

There is always the possibility of converting a string into hex.

http://www.sqlite.org/lang_expr.html "Literal Values"

Example:

X'53514C697465'

/Patrik

On 10/26/2012 12:08 PM, Kees Nuyt wrote:
> On Fri, 26 Oct 2012 01:25:24 +, 
> YAN HONG YE  wrote:
> 
>> char bh1[320];
>> memset(bh1,0,320);
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>> id,partnumber,substr(\'\',1,180) as 
>> img,pcs from engine where id>7;\" >> n.html");
>> system(bh1);  //here couldn't work
>>
>> error:
>> sqlite3 -html -header t9_engine.db "select id,partnumber,substr('> src="'||pi
>> c||'" height=220/>',1,180) as img,pcs from engine where id>7;" >> 
>> n.htmlError: n
>> ear "'> operable program or batch file.
>> The system cannot find the path specified.
> 
> 
> 
> 
>> char bh1[320];
>> memset(bh1,0,320);
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>> id,partnumber,substr(\'\',1,180) as 
>> img,pcs from engine where id>7;\" >> n.html");
>> strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select id,partnumber,'> src='||pic||' height=220/>' as img,pcs from engine where id>7;\" >> n.html");
>> system(bh1);  //here could work
>> the result is:
>> 8
>> AA34841687 000 INSONO-SOUS-MOTEUR--
>> img src=C:\t9\images\INSONO-SOUS-MOTEUR.jpg height=220/   
>> //here I wanna add " char between  'C:\t9\images\INSONO-SOUS-MOTEUR.jpg'  
>> 1
>> 
>>
>> and the best way is change 
>>  to  <
>>  to  >
> 
> You will never get that right. Quoting will always stay a problem.
> Forking out from C to a shell is bad practice. Forking out to a DOS
> shell is a headache. It's not SQLite related and off topic in this list.
> 
> Nevertheless, Michael Black did provide a working solution on Wed, 24
> Oct 2012 15:09:24 +, did you read it?
> 
> Please have a look at the sample C code I linked to before.
> There are more examples there.
> http://icculus.org/~chunky/stuff/sqlite3_example/
> 
> Good luck!
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] interesting deadlock.

[Patrik Nilsson]

Do you test for the backup errors, i.e. SQLITE_BUSY and SQLITE_LOCKED?

Do you test for step errors, i.e.  SQLITE_BUSY?

If you get the busy error, you can wait a while and try again or start over.

/Patrik

On 08/24/2012 05:46 PM, Jonathan Engle wrote:
> Ran into this recently, it's happened on one machine running a beta test of 
> our software.  This is a multi-threaded application, and I've run into a 
> sequence of steps that deadlocks hard that as far as I can tell from the 
> documentation shouldn't. 
> This is using SQLite 3.7.13 with SEE.
> The source database is using WAL mode, all transactions are done as 
> IMMEDIATE, synchronous mode is set to 0, and it is encrypted.
> The destination database for the backup is not encrypted, and is default 
> (non-WAL, full synchronous) modes.
> 
> 
> There are multiple threads active:
> 
> - one performing a write
> - two performing reads
> - one closing a connection
> - one is in the middle of a backup operation
> 
> Here are the call stacks for the threads:
> 
> 
> Writing thread:
> 
> sqlite3_step
> sqlite3VdbeExec
> sqlite3VdbeHalt  
> sqlite3BtreeCommitPhaseOne  
> sqlite3PagerCommitPhaseOne  
> pagerWalFrames  
> sqlite3BackupUpdate  
> backupOnePage  
> sqlite3BtreeEnter  
> lockBtreeMutex  
> pthread_mutex_lock  
> __psynch_mutexwait  
> 
> Closing a connection thread:
> 
> sqlite3_close  
> sqlite3BtreeEnterAll  
> sqlite3BtreeEnter  
> lockBtreeMutex  
> pthread_mutex_lock  
> __psynch_mutexwait  
> 
> Reading thread:
>   
> sqlite3_step  
> sqlite3VdbeExec  
> sqlite3VdbeEnter  
> sqlite3BtreeEnter  
> lockBtreeMutex  
> pthread_mutex_lock  
> __psynch_mutexwait  
> 
> Backing up thread:
>   
> sqlite3_backup_step  
> sqlite3BtreeEnter  
> lockBtreeMutex  
> pthread_mutex_lock  
> __psynch_mutexwait  
>   
> Reading thread:
> 
> sqlite3_step  
> sqlite3VdbeExec  
> sqlite3VdbeEnter  
> sqlite3BtreeEnter  
> lockBtreeMutex  
> pthread_mutex_lock  
> __psynch_mutexwait
> 
> 
> 
> Also, the destination database for the backup is created on the stack by the 
> the thread doing the backup and is never passed out to anybody (explicitly).
> 
> What looks like is happening to me is that the writing and backing-up thread 
> are deadlocking with each other, with 'sqlite3BackupUpdate' attempting to 
> update the backup destination database.  Unfortunately, this is not something 
> I've reproduced locally, so I can't look parameters or lock states.  I'm 
> going to try, as a kind of hail-mary, putting a BEGIN IMMEDIATE transactions 
> around the backup to block writing during the database backup.
> 
> If anyone has any suggestions or ideas about what I might be doing wrong 
> here, I'd appreciate it.
> 
> 
> ___
> 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] Can't create empty database

[Patrik Nilsson]

sqlite3 test.db ""

It creates an empty file, zero bytes long.

On 06/25/2012 10:01 AM, L Anderson wrote:
> Googling on how to create a sqlite database (empty one) it appears
> I need only do 'sqlite3 test.db'.  However, when I try that I get:
> 
> ->sqlite3 test.db
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> 
> however, no database is created.  What am I doing wrong and what
> do I need to do to create an empty database 'test.db'?
> 
> Thanks,
> 
> LA
> 
> ___
> 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] select ... where count(*)

[Patrik Nilsson]

Great! This works better than mine suggestion.
/Patrik

On 06/23/2012 07:12 PM, Petite Abeille wrote:
> 
> On Jun 23, 2012, at 6:55 PM, Patrik Nilsson wrote:
> 
>> select id,count(*) from repetitionhistory where count(id)<3 group by id
> 
> select id,count(*) from repetitionhistory group by id having count(id)<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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: select ... where count(*)

[Patrik Nilsson]

select a,b from (select id as a,count(id) as b from repetitionhistory
group by id) where b<3


 Original Message 
Subject: select ... where count(*)
Date: Sat, 23 Jun 2012 18:55:22 +0200
From: Patrik Nilsson <nipatriknils...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>

Hi All,

I want to write a statement like: (gives error on "count",  misuse of
aggregate: count())

select id,count(*) from repetitionhistory where count(id)<3 group by id


This statement works when written without "where":

select id,count(*) from repetitionhistory group by id


But I'm only interested in those not being used many times.

Best regards,
Patrik





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

[sqlite] select ... where count(*)

[Patrik Nilsson]

Hi All,

I want to write a statement like: (gives error on "count",  misuse of
aggregate: count())

select id,count(*) from repetitionhistory where count(id)<3 group by id


This statement works when written without "where":

select id,count(*) from repetitionhistory group by id


But I'm only interested in those not being used many times.

Best regards,
Patrik


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

Re: [sqlite] select count

[Patrik Nilsson]

Great! You solved my problem.
/Patrik

On 06/23/2012 06:28 PM, giris wrote:
> Hi:
> 
> 
> Assuming that the column in A is (for example) named x, the query will be
> 
> select count(*), x from A group by x
> 
> q.v "GROUP BY"
> 
> HTH.
> 
> Thanks
> 
> 
> ____
>  From: Patrik Nilsson <nipatriknils...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
> Sent: Saturday, June 23, 2012 12:24 PM
> Subject: [sqlite] select count
>  
> Hi All,
> 
> I have a table "a" which contains number like:
> 
> 1
> 1
> 1
> 1
> 2
> 2
> 4
> 
> When I "select distinct * from a" I get:
> 1
> 2
> 4
> 
> How can I get the count for each number? Like this:
> 1|4
> 2|2
> 4|1
> 
> How do I write the needed select statement?
> 
> Best regards,
> Patrik
> ___
> 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] select count

[Patrik Nilsson]

Hi All,

I have a table "a" which contains number like:

1
1
1
1
2
2
4

When I "select distinct * from a" I get:
1
2
4

How can I get the count for each number? Like this:
1|4
2|2
4|1

How do I write the needed select statement?

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

Re: [sqlite] bug "PRAGMA table_info"

[Patrik Nilsson]

Thank you!

On 06/19/2012 08:25 PM, Jay A. Kreibich wrote:
> On Tue, Jun 19, 2012 at 08:16:37PM +0200, Patrik Nilsson scratched on the 
> wall:
>> Hi All,
>>
>> I use "PRAGMA table_info" to check my tables at start up and if the
>> table columns are the need of updating (adding or removal) I do that.
>>
>> After a redesign of how the databases are managed this doesn't work
>> anymore. Instead of using several database connections, I use one.
>>
>> The cleaned up the code looks better and is less error prone to select
>> the wrong database connection, but it comes with a prize:
>>
>> "PRAGMA table_info" does not take databases as table names. "test" works
>> as an argument, but "main.test" doesn't.
> 
> 
>   No, but following the standard of every other PRAGMA, this does work:
> 
> PRAGMA main.table_info( test );
> 
> 
>   See the syntax diagrams here:  http://sqlite.org/pragma.html
> 
>-j
> 

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

[sqlite] bug "PRAGMA table_info"

[Patrik Nilsson]

Hi All,

I use "PRAGMA table_info" to check my tables at start up and if the
table columns are the need of updating (adding or removal) I do that.

After a redesign of how the databases are managed this doesn't work
anymore. Instead of using several database connections, I use one.

The cleaned up the code looks better and is less error prone to select
the wrong database connection, but it comes with a prize:

"PRAGMA table_info" does not take databases as table names. "test" works
as an argument, but "main.test" doesn't.

Best Regards,
Patrik

patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(page)"
0|id|integer|0||1
1|type|integer|0||0
2|sortorder|integer|0||0
3|width|integer|0||0
4|height|integer|0||0
5|sizedata|integer|0||0
6|deleted|integer|1|0|0
7|viewwidth|integer|1|0|0
8|fontname|text|0||0
9|fontsize|integer|1|0|0
10|comment|text|0||0
11|data|blob|0||0
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(main.page)"
Error: near ".": syntax error
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "create table testa(a integer)"
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(testa)"
0|a|integer|0||0
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(main.testa)"
Error: near ".": syntax error
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "create table main.testb(a integer)"
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(testb)"
0|a|integer|0||0
patrik@debian:~$ ~/Projects/DMemory/trunk/shell/sqlite3
/home/files/test.dmemory "PRAGMA table_info(main.testb)"
Error: near ".": syntax error
patrik@debian:~$
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] progressbar

[Patrik Nilsson]

Thank you for your suggestion. I will consider it.

Patrik

On 05/05/2012 10:31 PM, Roger Binns wrote:
> On 05/05/12 12:52, Patrik Nilsson wrote:
>> I use Sqlite as a document-file. Saving is when user requests to or
>> when program quits.
> 
> Even less reason to couple the user interface to database operations.  Are
> you really sure your users want to micromanage moving of data between
> transient (RAM) and persistent (disk) storage?
> 
> I got rid of the 'Save' menu from my programs many years ago because users
> really should not have to care about that, or worry if they have saved
> their work.  I made sure there was a very robust undo mechanism as well as
> a way of seeing what things looked like at any point in the past.
> 
> These days requiring users to tell the program when to copy data from
> transient storage to persistent is very anachronistic.  As an example
> you'll notice that no mobile apps do that, and most web ones don't either.
> 
> Roger
> ___
> 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] progressbar

[Patrik Nilsson]

On 05/05/2012 08:25 PM, Roger Binns wrote:

> Something else you can consider is changing how your program works so that
> the user interface isn't slaved to database operations.  You can let the
> UI queue up work to be done, and then have a background worker thread
> actually do the work in the queue.  Save the queue in a separate database
> so that work can be resumed even if the application crashes or is
> terminated.  (BTW this is how many mobile apps operate because db
> operations can take quite a while and they also have to be synced to a
> server over uncertain network connections.)

I use Sqlite as a document-file. Saving is when user requests to or when
program quits.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] progressbar

[Patrik Nilsson]

It will not relate to the number of bytes/pages to move.

An example:

If I "explain vacuum"...
0|Trace|0|0|0||00|
1|Vacuum|0|0|0||00|
2|Halt|0|0|0||00|

I count these rows to two (excluding halt). When running a lengthy
operation the best resolution I can get is 50 percent.

Unfortunately, this will not be informative to the user.

Patrik

On 05/05/2012 07:45 PM, Simon Slavin wrote:
> 
> On 5 May 2012, at 6:18pm, Patrik Nilsson <nipatriknils...@gmail.com> wrote:
> 
>> Is it possible to get the total number of virtual machine instructions a
>> "begin... commit"-statement will need? I.e. progress of saving data to
>> the database.
> 
> Using 'EXPLAIN ...' for each of your instructions, and adding up the number 
> of rows in each answer might give you a useful number.
> 
> 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

[sqlite] progressbar

[Patrik Nilsson]

Hi All,

Is it possible to get the total number of virtual machine instructions
an operation is requiring?

When backing up you have "sqlite3_backup_remaining" to do this job. By
invoking the callback to every N is equal to 1 and saving the first
value you have a progressbar with percentage.

I want to use "sqlite3_progress_handler" during "vacuum" to inform the
user of the relative progress, i.e. sending vacuum to a 3 gigabyte
database takes a while. Possible?

Is it possible to get the total number of virtual machine instructions a
"begin... commit"-statement will need? I.e. progress of saving data to
the database.

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

Re: [sqlite] Permissions

[Patrik Nilsson]

Google gives:

http://docs.oseems.com/application/apache/change-user-and-group


If for some reason you need to run Apache as different user and group,
the trick is to just change the User and Group directive in Apache
configuration file. The configuration file is normally in
/etc/apache2/httpd.conf, though that depends on the system you're using.
The following example will make Apache run as the user nobody and the
group nobody;

User nobody
Group nobody




On 04/22/2012 01:19 PM, Steinar Midtskogen wrote:
> Patrik Nilsson <nipatriknils...@gmail.com> writes:
> 
>> You can try setting your user as member of group apache.
> 
> That's already done, but the trouble is that when the shm and wal
> files are created by and therefore owned by "apache", then "user"
> can't change that file unless it's group writeable.  Having apache run
> with umask 002 should fix this, but I wonder if there is another
> workaround (and I haven't figured yet out how to configure apache to
> do this, anyway).
> 
> -Steinar
> 
>>
>> On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
>>> This might be slightly off topic, but perhaps a common problem for
>>> many sqlite users.
>>>
>>> I have a database (wal mode) that apache (the web server) needs to
>>> access, readonly.  Since it needs to be able to lock it for reading,
>>> apache needs write access.  So the database has these permissions:
>>>
>>> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
>>>
>>> The directory is also group writeable.
>>>
>>> The trouble is that when apache is accessing the database, the
>>> database file owner can't access it, not even for reading.  The result
>>> is "unable to open database file".  I believe that the cause is that
>>> apache creates these files:
>>>
>>> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
>>> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal
>>>
>>> which other users have no write access to.  So access to the database
>>> is locked until sqlite remove these files.
>>>
>>> Is there a way to work around this, other than to set umask 002 for
>>> apache?
>>>
>> ___
>> 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] Permissions

[Patrik Nilsson]

You can try setting your user as member of group apache.

On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
> This might be slightly off topic, but perhaps a common problem for
> many sqlite users.
> 
> I have a database (wal mode) that apache (the web server) needs to
> access, readonly.  Since it needs to be able to lock it for reading,
> apache needs write access.  So the database has these permissions:
> 
> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
> 
> The directory is also group writeable.
> 
> The trouble is that when apache is accessing the database, the
> database file owner can't access it, not even for reading.  The result
> is "unable to open database file".  I believe that the cause is that
> apache creates these files:
> 
> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal
> 
> which other users have no write access to.  So access to the database
> is locked until sqlite remove these files.
> 
> Is there a way to work around this, other than to set umask 002 for
> apache?
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Patrik Nilsson]

You can use:

create table t ( id integer primary key autoincrement, created_on
DATETIME DEFAULT CURRENT_TIMESTAMP )

Patrik

On 04/16/2012 06:27 PM, Mr. Puneet Kishor wrote:
> Given
> 
>   CREATE TABLE t (
>   id INTEGER NOT NULL,
>   created_on DATETIME DEFAULT CURRENT_TIMESTAMP
>   PRIMARY KEY (id, created_on)
>   );
> 
> how can I make just the 'id' column auto-increment?
> 
> 
> --
> Puneet Kishor
> ___
> 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] Primary Key uniqueness

[Patrik Nilsson]

On 03/29/2012 06:27 PM, Joe Bennett wrote:
> Hi,
> 
> I have three sqlite3 datbases that currently have no primary key (actually
> done with two and stuck on the third). I am converting them to 'new' tables
> with a primary key like so:
> 
> create table if not exists new_table
>  (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1,
> Column_27, Column_47))
> 
> Now, when I insert the data from old_table into new_table, I get the error
> message 'columns Column_1,Column_27,Column_47 are not unique' using this
> syntax:
> 
> insert into new_table (Column_1,Column_2,Column_3..,Column_47) select *
> from old_table

You can use:
insert or ignore into new_table...
insert or replace into new_table...

and later find a match with "select" which data didn't get into the new
table, if they are important.

/Patrik

> 
> That's OK, shame on me for not checking that prior to the insert... Now, I
> am using this syntax to find the dupes and remove them:
> 
> select rowid, Column_1,Column_27,Column_47,count(*)
> from old_table
> group by Column_1,Column_27,Column_47
> having count(*) > 1
> 
> 
> No rows meet this criteria... So, I'm looking for a better way to find the
> non unique data in the old_table so I can clear this error and insert it
> into the new table... Any ideas?
> 
> 
> 
> 
> -Joe
> ___
> 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] Bug hunting in SQLite

[Patrik Nilsson]

Thank you for all your support.

Thank you, Roger. You pointed out the most likely error: I didn't set
the sqlite-task to exclusively use the connection when reading from the
database, only when writing. I used a share lock when reading.

Thank you, Teg. You made me confident with having large databases. For a
moment I believed that I couldn't have databases of some gigabyte.

Patrik

On 02/27/2012 12:14 AM, Patrik Nilsson wrote:
> Thank you, Roger, for your piece of advice. I will consider it, but it
> will be a great deal to rework.
> 
> Patrik
> 
> On 02/26/2012 11:41 PM, Roger Binns wrote:
>> On 26/02/12 12:40, Patrik Nilsson wrote:
>>> Yes. My program starts two thread, the main one and a worker. There
>>> are mutexes so only one at a time can the sqlite interface.
>>
>> In previous postings to the mailing list when people do things like this,
>> I believe that it turning out to be a threading bug in their program is
>> 100%.  The onus will be on you to prove that you do not have a threading bug.
>>
>> Some examples.  Unless you call sqlite3_db_mutex you cannot safely get the
>> error string.  Unless you keep sqlite3_stmt per thread you can have memory
>> changed underneath you.  Unless you use SQLITE_TRANSIENT, the memory that
>> gets used may not be what you intended (your symptoms correlate with this
>> BTW).
>>
>> Even if you write perfect thread safe and correct code in 999 places in
>> your code, getting the thousandth one slightly wrong is enough to cause
>> problems.
>>
>> By far the safest thing to do is to either only do SQLite activity in one
>> thread, or to give each thread its own sqlite3 connection.
>>
>> Roger
>> ___
>> 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] Bug hunting in SQLite

[Patrik Nilsson]

Thank you, Roger, for your piece of advice. I will consider it, but it
will be a great deal to rework.

Patrik

On 02/26/2012 11:41 PM, Roger Binns wrote:
> On 26/02/12 12:40, Patrik Nilsson wrote:
>> Yes. My program starts two thread, the main one and a worker. There
>> are mutexes so only one at a time can the sqlite interface.
> 
> In previous postings to the mailing list when people do things like this,
> I believe that it turning out to be a threading bug in their program is
> 100%.  The onus will be on you to prove that you do not have a threading bug.
> 
> Some examples.  Unless you call sqlite3_db_mutex you cannot safely get the
> error string.  Unless you keep sqlite3_stmt per thread you can have memory
> changed underneath you.  Unless you use SQLITE_TRANSIENT, the memory that
> gets used may not be what you intended (your symptoms correlate with this
> BTW).
> 
> Even if you write perfect thread safe and correct code in 999 places in
> your code, getting the thousandth one slightly wrong is enough to cause
> problems.
> 
> By far the safest thing to do is to either only do SQLite activity in one
> thread, or to give each thread its own sqlite3 connection.
> 
> Roger
> ___
> 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] Bug hunting in SQLite

[Patrik Nilsson]

It says "ok"! I manage to get the same error corruption again.

I removed the defines "SQLITE_ENABLE_COLUMN_METADATA" and
"SQLITE_ENABLE_MEMORY_MANAGEMENT".

/PN

On 02/26/2012 10:19 PM, Simon Slavin wrote:
> 
> On 26 Feb 2012, at 8:40pm, Patrik Nilsson <nipatriknils...@gmail.com> wrote:
> 
>> Yes. My program starts two thread, the main one and a worker. There are
>> mutexes so only one at a time can the sqlite interface.
> 
> Hmm.  Once you have done the thing that apparently corrupts your database, 
> quit your app and use the sqlite3 command-line tool to look at your database. 
>  Run
> 
> PRAGMA integrity_check; 
> 
> and also have it display the field which you think is corrupt and check that 
> the corrupt value really is stored in the database rather than just being 
> returned by your SELECT command.
> 
> 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] Bug hunting in SQLite

[Patrik Nilsson]

I'll try valgrind and I've enabled sqlite3_trace. /PN

On 02/26/2012 09:11 PM, Roger Binns wrote:
> On 26/02/12 11:29, Patrik Nilsson wrote:
>> How do I set up a log
> 
> A log of what?  This will get you queries:
> 
>  http://www.sqlite.org/c3ref/profile.html
> 
> This will get you disk operations:
> 
>  http://www.sqlite.org/compile.html#enable_iotrace
> 
>> I have made a verify function for the whole table of the data-blobs.
> 
> Yet more reason for this to be implemented:
> 
>   http://www.sqlite.org/src/tktview?name=72b01a982a
> 
> One thing you should do is run your program under valgrind.  It is quite
> likely that it is other code messing with SQLite's memory.  I like to set
> the --freelist-vol flag to a huge number to ensure freed memory is never
> reused - this is especially good at catching problems in long running
> programs.
> 
> Roger
> ___
> 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] Bug hunting in SQLite

[Patrik Nilsson]

I only load "data" from a particular row when I need to display it. The
"data" that got corrupted isn't read for some time and even if I close
down the program and restarts the computer the same corruption is there
on the same row. It is saved into "data", although I don't modify it in
any way.

Patrik

On 02/26/2012 09:12 PM, Pavel Ivanov wrote:
> My guess is you read blob data incorrectly. You get pointer to the data
> from SQLite, but actually read the data when memory has been already reused
> for other purposes.
> 
> Pavel
> 
> 
> On Sunday, February 26, 2012, Patrik Nilsson <nipatriknils...@gmail.com>
> wrote:
>> Dear All,
>>
>> I have a table called page (defined below) and have noticed that the
>> data-variable (blob) sometimes get corrupted. How do I set up a log to
>> debug what is happening. What I want is a log file saved with my file,
>> so I can backtrace the error.
>>
>> This is also to be able to send in a good bug report to the developers
>> of SQLite.
>>
>> I don't know the exact steps to reproduce the error, but the following
>> might give someone a clue:
>>
>> 1) Add rows (one by one) with data.
>> 2) Update sortorder for the whole table with begin/commit
>> 3) After these and maybe some other steps (i.e. updating a row's data
>> with new data), I discover that a random row of the table has a
>> corrupted data-blob.
>>
>> I have made a verify function for the whole table of the data-blobs. The
>> data-blob can contain images varying in size from a few hundred bytes to
>> some (about 5) megabytes of images. (Currently only png-images.)
>>
>> Best regards,
>> Patrik
>>
>>
>> create table if not exists page ( id integer primary key autoincrement,
>> type integer, sortorder integer, width integer, height integer, size
>> integer, deleted integer default 0, zoom integer default 0, modified
>> datetime default (datetime('now','localtime')), comment text, data blob )
>>
>> ___
>> 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] Bug hunting in SQLite

[Patrik Nilsson]

On 02/26/2012 09:13 PM, Simon Slavin wrote:
> 
> On 26 Feb 2012, at 7:29pm, Patrik Nilsson <nipatriknils...@gmail.com> wrote:
> 
>> I have a table called page (defined below) and have noticed that the
>> data-variable (blob) sometimes get corrupted. How do I set up a log to
>> debug what is happening. What I want is a log file saved with my file,
>> so I can backtrace the error.
> 
> There are various callbacks you can use to make SQLite call your own logging 
> routines, but I don't think anything will help with this sort of thing.
> 
>> This is also to be able to send in a good bug report to the developers
>> of SQLite.
>>
>> I don't know the exact steps to reproduce the error, but the following
>> might give someone a clue:
>>
>> 1) Add rows (one by one) with data.
>> 2) Update sortorder for the whole table with begin/commit
> 
> Can you explain that ?  Tables don't have a sortorder.  You would normally 
> put begin/commit around commands changing the data in a table, e.g. the 
> INSERT commands in your step (1).

sortorder is defined as an integer in the table and is the way I use to
know which order the rows is to be displayed.

create table if not exists page ( id integer primary key autoincrement,
type integer, sortorder integer, width integer, height integer, size
integer, deleted integer default 0, zoom integer default 0, modified
datetime default (datetime('now','localtime')), comment text, data blob )

> 
>> 3) After these and maybe some other steps (i.e. updating a row's data
>> with new data), I discover that a random row of the table has a
>> corrupted data-blob.
> 
> Are you using any PRAGMAs ?

The only pragma I use is "PRAGMA table_info(table)" and it is when I
analyze if the table is correct or an update is needed.

Also the defines I use when compiling are:
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_MEMORY_MANAGEMENT

The last define is so I can use "sqlite3_release_memory()".

> Is the database file on the hard disk of the computer doing the operations, 
> or are you accessing it across a network ?

It is a local file using Debian Linux. I compile the source code
directly into my executable.

> Are you doing any kind of multi-thread/multi-process/multi-user thing ?

Yes. My program starts two thread, the main one and a worker. There are
mutexes so only one at a time can the sqlite interface.

> 
> There are a whole bunch of things that will corrupt your file so I'll point 
> you at the page, but answering the above questions will help you faster.
> 
> <http://www.sqlite.org/howtocorrupt.html>
> 
> 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

[sqlite] Bug hunting in SQLite

[Patrik Nilsson]

Dear All,

I have a table called page (defined below) and have noticed that the
data-variable (blob) sometimes get corrupted. How do I set up a log to
debug what is happening. What I want is a log file saved with my file,
so I can backtrace the error.

This is also to be able to send in a good bug report to the developers
of SQLite.

I don't know the exact steps to reproduce the error, but the following
might give someone a clue:

1) Add rows (one by one) with data.
2) Update sortorder for the whole table with begin/commit
3) After these and maybe some other steps (i.e. updating a row's data
with new data), I discover that a random row of the table has a
corrupted data-blob.

I have made a verify function for the whole table of the data-blobs. The
data-blob can contain images varying in size from a few hundred bytes to
some (about 5) megabytes of images. (Currently only png-images.)

Best regards,
Patrik


create table if not exists page ( id integer primary key autoincrement,
type integer, sortorder integer, width integer, height integer, size
integer, deleted integer default 0, zoom integer default 0, modified
datetime default (datetime('now','localtime')), comment text, data blob )

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