Re: [sqlite] Bug due to left join strength reduction optimization?

[Danny]

This has been fixed by revision d840e. Thanks for the quick response, drh!

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

Re: [sqlite] Bug due to left join strength reduction optimization?

[Danny]

R Smith  writes:
> As a matter of interest - what happens when the aliasing is taken out of
> the loop and the query changes to:
>
> CREATE TABLE tab (id INT);
> INSERT INTO tab VALUES (1);
> SELECT 1
>FROM tab LEFT JOIN tab AS tab2 ON 0
>WHERE (tab2.id IS NOT NULL) = 0
> ;
>
> I don't have that broken version currently, so can't test on my side, but I'm 
> assuming your example is minimal and it works if anything is changed, which 
> means it's likely the fault of the logic that checks the aliased value 
> (unless the above query still fails, in which case my assumption is wrong and 
> the above is a better test case).

In fact, that returns the incorrect empty result as well; I suppose I
didn't manage to minimize all the way. Good catch!

Replacing the end of the WHERE clause with "is 0" still returns one row,
as in Keith's example, while "= 0", "= false", and "is false" all give
no rows.

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

[sqlite] Bug due to left join strength reduction optimization?

[Danny]

The result of the query described below changed (became incorrect, I
believe) with the addition of the left join strength reduction
optimization in revision dd568, and remains that way in trunk (2c876, at
the time of writing).

Consider the following statements:

```
CREATE TABLE tab (id INT);
INSERT INTO tab VALUES (1);
SELECT tab2.id IS NOT NULL AS c
  FROM tab LEFT JOIN tab AS tab2 ON 0
  WHERE c = 0;
```

As of revision a8dfe (parent of dd568), the SELECT outputs one row with
one column containing 0, as I would expect. At dd568 (and at trunk),
however, it outputs no rows.

This looks similar in spirit to an existing, fixed bug [1], but the
output for the test case there has gone back to its pre-LJSRO value at
some point since dd568, while this one has not.

Thanks,
Danny

[1] https://www.sqlite.org/src/tktview/1e39b966ae9ee7394334
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

[Danny Milosavljevic]

Hi,

I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and
then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3).

Expected result: Orders result by column "a", in ascending order.
Observed result: Orders in some strange order.

I also tried sqlite3_bind_int64, didn't change the result.

Should this use case work?

To reproduce:

OK case (prints 2 and then 5):

#include 
#include 
#include 

int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", ) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
 "INSERT INTO t(a) VALUES (5);"
 "INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) != 
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1", -1, , 0) 
!= SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}

Not OK case (prints 5 and then 2):

#include 
#include 
#include 

int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", ) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
 "INSERT INTO t(a) VALUES (5);"
 "INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) != 
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY ?", -1, , 0) 
!= SQLITE_OK ||
sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}

Also OK but not that useful:

#include 
#include 
#include 

int main() {
sqlite3* db;
sqlite3_stmt* stmt;
printf("%s\n", sqlite3_libversion());
if (sqlite3_open(":memory:", ) != SQLITE_OK ||
sqlite3_exec(db, "CREATE TABLE t(a int);"
 "INSERT INTO t(a) VALUES (5);"
 "INSERT INTO t(a) VALUES (2);", NULL, 0, NULL) != 
SQLITE_OK ||
sqlite3_prepare_v2(db, "SELECT a FROM t ORDER BY 1, ?", -1, , 
0) != SQLITE_OK ||
sqlite3_bind_int(stmt, 1, 1) != SQLITE_OK ||
sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
if (sqlite3_step(stmt) != SQLITE_ROW)
abort();
printf("%d\n", sqlite3_column_int(stmt, 0));
return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3 fixes for 3.19.3

[Danny Couture]

I’d like to submit 2 compilation fixes

https://github.com/dcou/sqlite/commit/be48df67c63d8db221c2ae3ac3b49b93760460e7.patch



https://github.com/dcou/sqlite/commit/3f8d8d9b743e247bba15dd2b82b5dc26ac915a44.patch



and one regression fix for 3.19.3



https://github.com/dcou/sqlite/commit/c93d35b54213049c86be76b8d0e74948fecfbf4b.patch


Thanks

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

[sqlite] Potential corruption on VACUUM crash when SQLITE_OMIT_AUTOVACUUM is defined

[Danny Couture]

I found a bug in latest (3.7.14.1) with a very specific #define that can causes 
a database corruption after truncation because of missing backup pages.

If you specify this define:
#define SQLITE_OMIT_AUTOVACUUM

And then execute a VACUUM operation that shrinks the database, due to the 
#ifndef at sqlite3.c:42830

The code that would otherwise proceed to backup pages before truncation will 
not be executed.

Even the comment is wrong, it CAN and WILL happen for a normal vacuum too, not 
just in auto vacuum.

/* If this transaction has made the database smaller, then all pages
  ** being discarded by the truncation must be written to the journal
  ** file. This can only happen in auto-vacuum mode.
  **
  ** Before reading the pages with page numbers larger than the
  ** current value of Pager.dbSize, set dbSize back to the value
  ** that it took at the start of the transaction. Otherwise, the
  ** calls to sqlite3PagerGet() return zeroed pages instead of
  ** reading data from the database file.
  */

So here is my fix... (just removed the #ifndef)

/* If this transaction has made the database smaller, then all pages
  ** being discarded by the truncation must be written to the journal
  ** file. This can happen in auto-vacuum mode and during a normal
  ** vacuum operation.
  **
  ** Before reading the pages with page numbers larger than the
  ** current value of Pager.dbSize, set dbSize back to the value
  ** that it took at the start of the transaction. Otherwise, the
  ** calls to sqlite3PagerGet() return zeroed pages instead of
  ** reading data from the database file.
  */
  if( pPager->dbSizedbOrigSize
   && pPager->journalMode!=PAGER_JOURNALMODE_OFF
  ){
Pgno i;   /* Iterator variable */
const Pgno iSkip = PAGER_MJ_PGNO(pPager); /* Pending lock page */
const Pgno dbSize = pPager->dbSize;   /* Database image size */
pPager->dbSize = pPager->dbOrigSize;
for( i=dbSize+1; i<=pPager->dbOrigSize; i++ ){
  if( !sqlite3BitvecTest(pPager->pInJournal, i) && i!=iSkip ){
PgHdr *pPage; /* Page to journal */
rc = sqlite3PagerGet(pPager, i, );
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
rc = sqlite3PagerWrite(pPage);
sqlite3PagerUnref(pPage);
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
  }
}
pPager->dbSize = dbSize;
  }


You can test it with a really simple application that I included in 
attachment...

You just have to put a breakpoint at sqlite3.c:56747 and step over it and then 
restart the app right there.

The next integrity_check will fail completely :)

Thanks

Danny Couture
Technical Architect
Ubisoft Montreal


#include "stdafx.h"

//DON'T FORGET TO COMPILE SQLITE WITH #define SQLITE_OMIT_AUTOVACUUM
#include "sqlite3.h"

int callback(void *, int argc, char ** argv, char ** x)
{
for (int i = 0; i < argc; ++i)
printf("%s, ", argv[i]);

printf("\n");

return 0;
}

int _tmain(int argc, _TCHAR* argv[])
{
sqlite3 * db;
sqlite3_open("test.db", );

char * errorMsg;

sqlite3_exec(db, "PRAGMA integrity_check", callback, 0, );

sqlite3_exec(db, "CREATE TABLE test (Key INT, Test DOUBLE, Text 
VARCHAR(1024))", callback, 0, );

sqlite3_exec(db, "BEGIN", callback, 0, );

//add some stuff
char query[1024];
for (int i = 0; i < 10; ++i)
{
sprintf_s(query, "INSERT INTO test VALUES(%d, %d.5, \"%d\")", i, i, i);
sqlite3_exec(db, query, callback, 0, );
}

sqlite3_exec(db, "COMMIT", callback, 0, );

//remove some of the stuff so the vacuum shrinks the DB
sqlite3_exec(db, "DELETE FROM test WHERE Key > 5000", callback, 0, 
);

//add a breakpoint at sqlite3.c:58390 and restart the application right 
there.
//the next integrity_check will fail completely...
sqlite3_exec(db, "VACUUM", callback, 0, );

return 0;
}

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

Re: [sqlite] How about a proper forum rather than an e-mail list

[Danny Staten]

I for one would love a forum, and disagree about it being the same as an 
email list.  I would love to not see 40+ emails in my inbox every day from 
this mailing list, but I do find the available resource handy to have when I 
need it.  A forum would allow us to be more active because we can quickly 
and easily ask questions and follow the conversation rather than having to 
scan through a myriad of other emails, or try and follow the reply chain 
when a flood comes in.  It would also allow us to keep our inboxes a lot 
cleaner and clutter free which would be very very nice.


-Original Message- 
From: Frank Missel

Sent: Tuesday, October 18, 2011 6:35 AM
To: 'Teg' ; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] How about a proper forum rather than an e-mail list

Hi Teg,


I love forums and consider them far superior to email if only because it's
easier to follow a topic with less quoting needed. The downside is that
someone  has  to  manage the forum. I've managed a forum for the past  10
years  and there's a daily spam cleanup process and constant attacks and
required upgrades.  You have to set the tone and be pretty ruthless about
flaming too.

I'd like to see a forum. I just wouldn't want to manage it.


Okay, but if the posting is by members only would it not be the same as the
e-mail-lists.
I don't see a lot of spam in the e-mail-list, so either it also monitored by
someone or the fact that it can only be accessed by members makes for a well
behaved list which would be the same for the forum.

/Frank

___
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] Hidding records from the application

[Danny]

In my mainframe days, using IDMS/SQL, I limited user access to table data, down 
to the column level, based upon logged on userid.  This was accomplished via 
database procedures.  I'm new to SQLite, so don't know if it has any similar 
capabilities.

>
>From: Igor Tandetnik 
>To: sqlite-users@sqlite.org
>Sent: Friday, July 15, 2011 9:24 PM
>Subject: Re: [sqlite] Hidding records from the application
>
>On 7/15/2011 9:19 PM, san long wrote:
>> Dear all,
>> I have an idea related to the safety of the records in a table: if it is
>> possible to hide some records in a table so the upper user application could
>> not see them?
>> For example:
>> table food has content:
>> 1, "food A"
>> 2, "food B"
>> I want to hide the record whose rowid is 2, so:
>> sqlite>  SELECT  * from food;
>> --
>> 1, "food A"
>> ---
>
>How is SQLite supposed to know which application is allowed to see these 
>rows and which one isn't? Presumably, *someone* must be able to see 
>them, or else you can just delete them and be done with it. What exactly 
>makes an application "upper user application" (as opposed to  "lower 
>system application", I guess)?
>-- 
>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] current version support wal mode?

[Danny]

Paul

Like the old saying goes ... "When all else fails, read the manual."

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

Have a nice day.

Danny


>
>From: Paul Linehan <lineh...@tcd.ie>
>To: sqlite-users@sqlite.org
>Sent: Saturday, July 2, 2011 7:51 PM
>Subject: Re: [sqlite] current version support wal mode?
>
>2011/7/2 Kees Nuyt <k.n...@zonnet.nl>:
>
>> Just feed it the SQL statement:
>
>>        PRAGMA jounal_mode=WAL;
>
>
>I'm not being nasty here, but that is *_not_* an SQL statement.
>
>
>It is a C directive (or, at least, that's what I think it is!).
>
>
>Rgs,
>
>
>
>Paul...
>
>
>-- 
>
>lineh...@tcd.ie
>
>Mob: 00 353 86 864 5772
>___
>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] Sqlite database integrity check fails with disk I/O error (10)

[Danny]

If I had the same problem ... I would:

1.  Refresh the surface of the hard drive using spinrite (grc.com), which is OS 
and file-structure independent;

2.  Then, I'd run the Linux equivalent to the Windows chkdsk command to resolve 
any file-structure issues;

In all but the worst hard drive issues, this would in all likelihood take care 
of any read errors.

--- On Mon, 6/20/11, Raja Kondu  wrote:

From: Raja Kondu 
Subject: [sqlite] Sqlite database integrity check fails with disk I/O error (10)
To: sqlite-users@sqlite.org
Date: Monday, June 20, 2011, 11:25 AM

Hi I am using the Sqlite database in the linux box . Here I am encountered
the disk I/O error when I perform the integrity check on the database.

The error occuted during the sqlite prepare statement only.

The prepared select statement is  : " pragma integrity_check "

retValue = sqlite3_prepare(pDb,pragma integrity_check ) ;

retvalue = 10

#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
Can some one help me why the disk I/O error occured during the
sqlite3_prepare()  statement ?

Kindly provide me your feed back.


-- 
Thanks,
Raja Kondu.
___
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] sqlitebrowser - anyone compiled a recent one?

[Danny]

Paul

I too have had a bad experience with this Firefox addon.  I found it to be 
buggy, to generate all sorts of errors, and frequently I had to shutdown the 
addon between transactions to get it to continue to work.

Months ago I switched to SQLite Expert Personal and haven't looked back.

Danny

--- On Fri, 6/10/11, Paul Linehan <lineh...@tcd.ie> wrote:

From: Paul Linehan <lineh...@tcd.ie>
Subject: Re: [sqlite] sqlitebrowser - anyone compiled a recent one?
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, June 10, 2011, 10:55 AM

2011/6/9 Christoph P.U. Kukulies <k...@kukulies.org>:


>> If you want something that works on Linux as well as Windows, try
>> the Firefox SQLite extension - it's the dog's!

> I've heard big caveats about that one - to avoid like the .., no, I
> don't want to open another can of worms :)


I don't wish to cause controversy here, but could you explain
exactly what you mean by this? What is there about this tool
that is problematic?


TIA and rgs,


Paul...


-- 

lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
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] Unlocking the database

[Danny]

John, I've had nothing but trouble with the Firefox plugin.  Download and 
install the SQLite Expert Personal 3 GUI (free) and see if that does anything 
for you, or at least gives you better diagnostics.

--- On Sat, 5/28/11, Simon Slavin  wrote:

> From: Simon Slavin 
> Subject: Re: [sqlite] Unlocking the database
> To: "General Discussion of SQLite Database" 
> Date: Saturday, May 28, 2011, 1:02 PM
> 
> On 28 May 2011, at 5:39pm, john darnell wrote:
> 
> > After the reboot, I tried opening the database in my
> program as well as SQLite Manager (the Firefox plugin).
> Neither worked, I got the SQLITE_BUSY return code from the
> SQLite call (I believe it was sqlite3_prepare_v2), and a
> long and cryptic error message from SQLite Manager.
> 
> Something is weird with that.  I'm not a SQLite dev
> but I don't think anything inside SQLite can do that. 
> Perhaps SQLite Manager does 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] How to upgrade from SQLite 3.7.4 to 3.7.6.3

[Danny]

In the meantime, I have found SQLite Expert Personal 3 to be a more stable 
solution than the firefox plugin.  I finally ditched the plugin entirely.

http://www.sqliteexpert.com/download.html


--- On Wed, 5/25/11, Simon Slavin  wrote:

> From: Simon Slavin 
> Subject: Re: [sqlite] How to upgrade from SQLite 3.7.4 to 3.7.6.3
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, May 25, 2011, 5:33 PM
> 
> On 25 May 2011, at 10:11pm, Long, Matthew wrote:
> 
> > I have firefox Sqlite manager installed, and I see the
> Sqlite version:
> > 3.7.4 installed when I start Sqlite manager. How do I
> upgrade 3.7.4 to
> > 3.7.6.3?
> 
> You can't upgrade, you have to wait for programmers to do
> it.  It is probably using whatever version of SQLite is
> built into FireFox, or a version of SQLite built into
> itself.  So the upgrade will happen when the people who
> make FireFox, or that plugin, decide to upgrade.
> 
> 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] Sqlite Files

[Danny]

Or a good GUI shell ... SQLite Expert Personal

http://www.sqliteexpert.com/download.html




--- On Sun, 5/22/11, Stephan Beal  wrote:

> From: Stephan Beal 
> Subject: Re: [sqlite] Sqlite Files
> To: "General Discussion of SQLite Database" 
> Date: Sunday, May 22, 2011, 7:42 AM
> On Sun, May 22, 2011 at 1:28 PM,
> wrote:
> 
> > Hello,
> > Can you please tell me how to open and read SQlite
> files. I have a Firefox
> > browser which uses a Read It Later add-on. I would
> like to open this file
> > and view its contents with a view of deleting some of
> it.
> >
> 
> The sqlite shell:
> 
> http://www.sqlite.org/sqlite.html
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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 send messages to list from pc?

[Danny]

TB giving any errors?  

Do they show up in the Sent folder?  

The Outbox folder? 

Are you sending in HTML instead of text? 

Have you confirmed "one more time" that you are sending it to the correct 
address?

Can't think of any other questions.


--- On Fri, 5/13/11, Don Ireland  wrote:

> From: Don Ireland 
> Subject: Re: [sqlite] Can't send messages to list from pc?
> To: "General Discussion of SQLite Database" 
> Date: Friday, May 13, 2011, 5:35 PM
> Anybody have any ideas?  This is
> really odd.
> 
> And it's annoying to have to send from my droid--especially
> when I need to copy/paste an error msg that's on my pc
> because I have to email it to myself and then send that
> message on to the list.
> 
> Don Ireland
> 
> -Original Message-
> From: Don Ireland 
> To: SQLite 
> Sent: Thu, 12 May 2011 12:22 PM
> Subject: [sqlite] Can't send messages to list from pc?
> 
> I use Thunderbird on my laptop and also have an Imap client
> on my Android.
> 
> I signed up for this list by sending a msg from
> Android.  Both email clients send from the same
> address.  
> But when I send a message from my pc, it seems to end up in
> a vacuum because it never appears on the list.
> 
> Any ideas?
> 
> TIA!
> 
> Don Ireland
> ___
> 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] SQLite as a Logger: How to mimic "rotation of logs"?

[Danny]

Why not have TWO tables?  Log_A and Log_B?

When Log_A is full, DELETE everything from Log_B and start logging to it.  When 
Lob_B is full, DELETE everything from Log_A and start logging to it again.

If you want, while logging to one, the other can be archived ...


--- On Tue, 5/10/11, Simon Slavin  wrote:

From: Simon Slavin 
Subject: Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
To: "General Discussion of SQLite Database" 
Date: Tuesday, May 10, 2011, 7:34 AM


On 10 May 2011, at 11:42am, Lynton Grice wrote:

> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use 
> to say FIX the sqlite database size to say "5 MB"?

There isn't one.  SQLite would not know which records to delete.

> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will 
> happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or 
> similar? I guess I am looking for a "round robin queue" here?

A round robin queue is fine.  Every so often, to kill off old records do

SELECT max(rowid) FROM myTable

then in your code subtract from it however many rows you want to keep, then do

DELETE FROM myTable WHERE rowid < firstToRetain

It won't work perfectly but it's simple and fast.

> While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a 
> LOGGER implementation, and the integer reaches it's limit (even though I 
> am deleting previous records), will the sqlite database assign "un-used 
> primary keys" (previously deleted) to any NEW inserts?

A SQLite integer can get /really/ big: 2^63.  There's no way you could ever 
write enough records to push it  over the limit.  Your hardware will 
disintegrate first.

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] installing sqlite

[Danny]

SQLite Manager for firefox has real problems.  I made it work for a while until 
I found a real GUI.  And does a pretty decent job.

SQLite Expro Personal is FREE.  Of course, there is also a paid version with 
some additional features.

http://www.sqliteexpert.com/

--- On Tue, 4/19/11, Kees Nuyt  wrote:

From: Kees Nuyt 
Subject: Re: [sqlite] installing sqlite
To: sqlite-users@sqlite.org
Date: Tuesday, April 19, 2011, 5:42 PM

On Tue, 19 Apr 2011 15:04:31 -0400, Carlos Contreras
 wrote:

>sqlite offer a very eficient program but I dont understand how to install it
>in my Windows NT PC. I dont know how to compile a C program and then use it
>as a shell to run the sqlite commands.
>
>Can you help me?

Download the command line tool and/or the .dll for windows from
the download page http://www.sqlite.org/download.html 

Look for the heading "Precompiled Binaries For Windows"
There is nothing to install, just unzip the .zip archive(s) into a
folder of your choice.

Or, if you prefer a GUI tool:
Install the Firefox web browser from 
http://www.mozilla.com/en-US/firefox/new/

and add the SQLite manager add-on from 
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

I have no idea wether all that is compatible with Windows NT.
Windows XP and later are fine.
-- 
  (  Kees Nuyt
  )
c[_]
___
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] What happens if you insert more than your RAM

[Danny]

Depends on access type.  If accessing sequentially, paging would be minimal, 
that is, you would process the "segment" that fits into memory, then page in 
another "segment" and process that, etc., etc.

However completely random hits on the database could result in heavy paging, 
unless it were possible to do the random accesses in a "sorted" manner.  For 
example, input transactions sorted by the same key that you are accessing by.

--- On Tue, 4/19/11, jeff archer  wrote:

> From: jeff archer 
> Subject: [sqlite] What happens if you insert more than your RAM
> To: "SQLite-user.org" 
> Date: Tuesday, April 19, 2011, 2:29 PM
> Wouldn't it page to disk, thrash and
> be very slow first?  
> 
> >On Mon, 18 Apr 2011 09:46:44 -0400, Pavel Ivanov 
> wrote:
> >You won't be able to insert. The statement will fail.
> >
> >On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita 
> wrote:
> >> Good day,
> >>
> >> What happens if you insert more than your RAM size
> into an in memory
> >> database?
> >> (I'm particularly interested in the Windows
> context).
> >>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615 
> ___
> 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 help for newbie

[Danny]

Thanks everyone.  That was so easy it was embarrassing!  :)

--- On Mon, 4/18/11, Simon Slavin  wrote:

> From: Simon Slavin 
> Subject: Re: [sqlite] SELECT help for newbie
> To: j...@kreibi.ch, "General Discussion of SQLite Database" 
> 
> Date: Monday, April 18, 2011, 1:06 PM
> 
> On 18 Apr 2011, at 4:52pm, Jay A. Kreibich wrote:
> 
> >  SELECT book, chapter, count(verse) AS
> total_verses 
> >    FROM scripture
> >    GROUP BY 1, 2; 
> 
> Just for clarity, since he's still learning, I might
> suggest instead
> 
>  SELECT book, chapter, count(verse) AS total_verses 
>    FROM scripture
>    GROUP BY book, chapter;
> 
> 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] SELECT help for newbie

[Danny]

Hello

I have a table with a primary key consisting of three columns:  Book, Chapter, 
Verse.

I'd like to produce a results set that contains 1 row for each chapter of each 
book, showing the total verses in that chapter.

I know enough SQL to know I can get the total number of verses in a chapter of 
a book with

SELECT COUNT(VERSE) AS TOT_VERSES WHERE BOOK = 1 AND CHAPTER = 1

... but not enough to produce the following ...

BOOK  CHAPTERTOT_VERSES
  1  1   31
  1  2   22
  1  3   99 ETC., ETC.

I would appreciate any help you care to give as I continue to learn SQL.

Thanks.

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

[sqlite] Using the static lib of sqlite3 under 64 bit ubuntu

[Danny De Keuleneire]

Settings:

Gcc 4.3.3
Added usr/lib64/libsqlite3.a

Error:

Undefined reference to 'pthread_mutex_trylock'

What I am missing?

If I use the so shared lib no problems at all.

Tx,

Danny

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

Re: [sqlite] Importing tab-separated data containing quotes

[Danny Suls]

Hi Donald,

You're right. We're using a slightly modified build of sqlite3 at the 
company I work for, and it turns out that the problem is specific to our 
version. The "official" build of sqlite3 doesn't have this issue. Of 
course I should have tested the official binary first before jumping to 
conclusions.

Thanks for your reply.

- Danny


Griggs wrote:
>  Hi Danny,
>
> When you wrote "... and try to import it in SQLite..." 
> I'm pretty sure you were using the sqlite3 commandline utility.
>
> I ran your test using the sqlite3 utility version 3.5.5 and version
> 3.4.2 on Windows XP with your input file of:
>
> Unquoted value 1\tUnquoted value 2\r\n
> "Quoted" value 1\t"Quoted" value 2\r\n
>
>   (where \t=tab character, \r=return, and \n=newline)
>
> And in each case it imported without error and the data was as expected
> (the quotes were preserved as part of the data values).
>
> If you compiled sqlite3 yourself, I wonder if your difficulty my be an
> artifact of the libraries you linked in.
>
> Regards,
>   Donald
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Danny Suls
> Sent: Friday, July 04, 2008 5:04 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Importing tab-separated data containing quotes
>
> I'm having a problem importing tab-separated files containing quotes.
>
> For example:
>
> When I take a tab-separated text file (quote_test.txt), containing these
> lines...
> Unquoted value 1Unquoted value 2
> "Quoted" value 1"Quoted" value 2
>
> ... and try to import it in SQLite with these commands:
> create table quote_test (value1, value2); .separator \t .import
> quote_test.txt quote_test
>
> I get this error:
> quote_test.txt line 2: expected 2 columns of data but found 1
>
> 
>
>
> This email and any attachments have been scanned for known viruses using 
> multiple scanners. We believe that this email and any attachments are virus 
> free, however the recipient must take full responsibility for virus checking. 
> This email message is intended for the named recipient only. It may be 
> privileged and/or confidential. If you are not the named recipient of this 
> email please notify us immediately and do not copy it or use it for any 
> purpose, nor disclose its contents to any other person.
> ___
> 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] Compiler errors

[Danny Reinhold]

> When I try to recompile the shell.c file I get 4 errors, "unresolved 
> external" on:
> 
> Where can I find the sourcecode for these functions ?

> sqliteOsFileExists
os.h/os.c

> sqliteIsNumber
> sqliteStrICmp

> sqliteStrNiCmp

sqliteInt.h/util.c

All these files belong to a normal sqlite build. So these symbols should be
defined if you link your program correctly with the sqlite library...

  - Danny

--
Danny Reinhold 
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled

[Danny Reinhold]

Hi again,

> Actually, the -DNDEBUG=1 is not SQLite specific.  This is how
> you disable assert()s.  The SQLite library is full of assert()s
> for sanity checking.  But it is smaller and runs twice as fast
> if you leave them out.
Oh yes, you are right.
(I forgot it because I don't use assert() very often...)

I really think that I need to sleep... ;-))

BTW: I didn't find a way to control the creation of debug (with
correct setting of NDEBUG) or thread safe code in the configure.ac file.
Shall I add those options?

  - Danny



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled

[Danny Reinhold]

Hi!

> Danny,
> 
> thank you for your answer!! =)
No problem - but it wasn't correct... ;-)
(I should sleep a little ;-))

> > > TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 
The important thing is not only the option -DNDEBUG=1 but
mainly -g ofcourse... -DNDEBUG=1 is a SQLite specific directive
while -g is a compiler option for the gcc...

  - Danny

--
Danny Reinhold 
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Compiling a shared library (.so) WITH threadsafe option enabled

[Danny Reinhold]

Hi!

> I added these things myself to the Makefile...
>
> TCC = gcc -g -O2 -DTHREADSAFE=1 -DNDEBUG=1 
> LIBREADLINE = ... -lpthread
>
> Then:
>
> $ make
> $ cd .libs
> $ strip libsqlite.so.0.8.6
>
> That's all. Now the libsqlite.so file is only 260K (less than half the
last
> size)...
>
> May I ask??
>
> - Is the threadsafe really enabled by changing only those two lines in
> the Makefile?
Yes, -DTHREADSAFE=1 enables thread safety

> - Isn't the libsqlite.so too small?  Haven't I stripped too much from
> it??
Probably you compiled with debug enabled the first time. So the compiler
put a lot of debugging symbols into the output files.
With -DNDEBUG=1 you disabled the debugging mode - now the compiler
doesn't generate the debugging symbols. Thus the resulting files are much
smaller.

You can verify this:
- Compile without -DNDEBUG=1. The resulting library should have the
  old length
- Now remove the debugging symbols by using the strip command
  (strip libsqlite.so)
- Now the library should have about 260K again...

The strip command removes debugging symbols from object files,
executables and libraries while -DNDEBUG=1 causes the compiler
to not generate them (and so it's useless to strip the library after
creation)...

  - Danny

--
Danny Reinhold
Reinhold Software & Services
http://www.rsas.de



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Developers of SQLite

[Danny Reinhold]

Hi!

> Another day I saw a homepage with the developers of SQLite but I'm not
> finding again. Does anybody could send it to me?

Probably you mean this page:

http://cvs.hwaci.com/sqlite/wiki?p=PointsOfContact

I even did not find a direkt way via the wiki to it (I searched in
the archive)...

BTW: I found it in a thread about autoconf, automake and friends.
What happened to this? Is somebody actively maintaining these
build method for SQLite?

  - Danny

--
Danny Reinhold 
Reinhold Software & Services




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Where statements are they case sensitive?

[Danny Reinhold]

Hi!

> I just checked something and noticed that the WHERE statement is case
sensitive.  I have check this in SQL Server and it is not case sensitive.
>
> I am using 2.8.5 and 2.8.6.
>
> As an example in the northwind DB I have for SQLite .  There is a table
called Orders
> select * from sqlite_master where Name = 'orders'  return no rows but
> select * from sqlite_master where Name = 'Orders'  does return rows
>
> but
>
> create table orders(a) returns an error with the table already exists.
I don't know MS SQL Server and I don't know what you did there.
But I think:
Strings that are enclosed in ' are always case sensitive.
Column and table names are always case insensitive.

So it does not matter if you write:
select a, b, c from mytable;
select A, B, C from MYTABLE;
or
select a, B, c from myTable;

But it does always matter if you write:
select * from mytable where a = 'hello';
select * from mytable where a = 'Hello';
or
select * from mytable where a = 'hElLo';

If you search a table name in sqlite_master, then you do a string
comparision and that is case sensitive.
If you create a table you don't use a string literal but a table name
and that is case insensitive.

This behaviour looks very straight and correct to me and I think
it is standard SQL behaviour.

What exatly did you do to get another result with MS SQL Server?


> Should the where statement be case sensitive , By default I don't think it
should.
>
> Should I report a bug on this or was it by design??
I think it is very well designed and should not be changed.

If you want case insensitive where clauses, use something like this:
select * from sqlite_master where upper(name) = 'MYTABLE';

  - Danny

--
Danny Reinhold
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Checking the busy state

[Danny Reinhold]

Hi,

> The application is actually supposed to run on a network and all copies
will
> access the database file at some central location. I'm currently playing
> around with SQLITE_BUSY and SQLITE_LOCKED values and they work 90% of the
> time, but then sometimes they don't work for some reason and I get thrown
> out of the program.
>
> This whole networking concept is proving to be a real pain to implement,
but
> I really need it, so I'll keep banging my head against it :-)


Maybe you should consider writing a SQLite server application that runs
on the machine where you store the database file and a client library that
does not directly use the file but connects the server for queries.
Then you don't have do worry about network file system issues...

I think such solutions already exist (SQL relay or so shall be one of them).

It would be great if such a client library would be API compatible to
the SQLite library itself. Then you could turn a simple SQLite
application to a client application for your SQLite server simply
by linking against your client library instead of linking with SQLite
directly...

Just an idea...

  - Danny

--
Danny Reinhold
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Performance benchmarking

[Danny Reinhold]

> Danny Reinhold wrote:
> > DRH tested inserts with and without transactions on several
> > DBMSs. PostgreSQL and MySQL where faster _without_
> > explicit transactions.
> > That looks a bit strange to me...
> If you are referring to Test 1 and Test 2 at
http://www.sqlite.org/speed.html,
> please look again.  The transaction-less test (Test 1) only inserts 1000
> records, whereas the transaction test (Test 2) inserts 25000 records.  So
> even though the elapse time is a little more for some engines on the
second
> test, they are doing 25 times more work, so they are really quite a bit
> faster.
Ah yes, I see.
Sorry.

  - Danny

--
Danny Reinhold
Reinhold Software & Services



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]