Re: [sqlite] memory usage after VACUUM

[Pavel Ivanov]

> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?

Yes, execute "pragma cache_size = 100" for example, or put other
number of your liking into there.

If closing and re-opening of the database makes extra memory usage to
go away then it was used by database cache. Default cache size is 2000
pages. Command shown above sets it to 100 pages. I don't know what
size of cache is best for you, just beware that usually with smaller
cache SQLite needs more frequently to go to disk and thus queries are
slower. But cache works only if you repeatedly access the same
tables/indexes over the same connection. And I don't know how much
slower queries are without cache on iOS.


Pavel

On Wed, Mar 9, 2011 at 10:23 AM, Nick Hodapp  wrote:
> I'm using sqlite in an iOS app, via the popular FMDB wrapper.
>
> My profiling tool is showing me that the app is using 2.5 MB of memory
> before a VACUUM, and nearly 6MB after.  The tool shows that the extra memory
> was allocated by sqlite3MemMalloc().  If I close and re-open the database
> then the extra memory usage goes away, returning to 2.5 MB.
>
> Is there any sqlite function I can call, or some other technique, to reduce
> the memory allocated and hung-onto by sqlite, particularly during a VACUUM?
>
> It's possible but unlikely that the FMDB wrapper is affecting things.  I
> haven't removed it from the equation to test, however.
>
> Nick Hodap
> ___
> 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-dev] Amalgamation code doesn't supoort limit on update / delete

[Pavel Ivanov]

http://lmgtfy.com/?q=SQLITE_ENABLE_UPDATE_DELETE_LIMIT+site%3Asqlite.org=1

If you look for description of SQLITE_ENABLE_UPDATE_DELETE_LIMIT on
the resulting page you'll see that it's not a bug or oversight, it's a
documented feature.


Pavel

On Sun, Mar 13, 2011 at 2:46 PM, Steven Hartland
 wrote:
> After spending many hours banging my head trying to figure out why
> sqlite-jdbc was erroring on a delete with a limit even when compiled
> with SQLITE_ENABLE_UPDATE_DELETE_LIMIT I finally tracked it down
> to the fact that the Amalgamation download is simply missing half
> the code to support it :(
>
> Compiling from standard source, in this case from FreeBSD ports
> results in a sqlite3.c which has much more code that references
> the options to support update / delete limits than the standard
> Amalgamation version.
>
> I can't find anything that says this is a know limitation so can
> only assume its an oversight / bug, is this the case?
>
>    Regards
>    Steve
>
>
> 
> This e.mail is private and confidential between Multiplay (UK) Ltd. and the 
> person or entity to whom it is addressed. In the event of misdirection, the 
> recipient is prohibited from using, copying, printing or otherwise 
> disseminating it or any information contained in it.
>
> In the event of misdirection, illegible or incomplete transmission please 
> telephone +44 845 868 1337
> or return the E.mail to postmas...@multiplay.co.uk.
>
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] X most recent entries

[Pavel Ivanov]

Try this one:

select * from (select * from multiturnTable order by rowid desc limit 5000)
where (player1 = ? or player2 = ?)
and (complete=0 or p1SubmitScore=0 or p2SubmitScore=0)


Pavel


On Mon, Mar 14, 2011 at 1:58 PM, Ian Hardingham  wrote:
> Ah, sorry about this - my query is this one:
>
> SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM
> multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM
> multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0
> OR p2SubmitScore=0)
>
> And I only want to consider the last 5000 for any SELECTs from
> multiturnTable.
>
> Thanks,
> Ian
>
> On 14/03/2011 17:54, Adam DeVita wrote:
>> select id from table order by id desc limit 5000
>>
>>
>> Adam
>>
>> On Mon, Mar 14, 2011 at 1:52 PM, Ian Hardingham > > wrote:
>>
>>     Hey guys.
>>
>>     I have a table with an autoincrement primary ID, and as part of a
>>     select
>>     I would like to only take the 5000 "largest"/most recent ids.  Is
>>     there
>>     a quick way of doing this without having to get the max first?
>>
>>     Thanks,
>>     Ian
>>     ___
>>     sqlite-users mailing list
>>     sqlite-users@sqlite.org 
>>     http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>
>> --
>> VerifEye Technologies Inc.
>> 905-948-0015x245
>> 151 Whitehall Dr, Unit 2
>> Markham ON, L3R 9T1
>> Canada
>>
>
> ___
> 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] Only select most recent results takes longer

[Pavel Ivanov]

> My tests show that, counter-intuitively, the second query takes between
> 3 and 5 times as long as the first query.
>
> This seems weird to me - any ideas why this would be?

What does EXPLAIN QUERY PLAN for both queries say? And what SQLite
version do you use?

Also do you understand that '?' (exactly like that in quotes) is not a
parameter you can bind? It's a string consisting of one character.


Pavel


On Tue, Mar 15, 2011 at 7:37 AM, Ian Hardingham  wrote:
> Hey guys.
>
> Due to some help I had yesterday, I was advised to change this query:
>
> SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM
> multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM
> multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0
> OR p2SubmitScore=0)
>
> To this one:
>
> SELECT * FROM (SELECT * FROM multiturnTable ORDER BY id DESC LIMIT 5000)
> WHERE (player1 = '?' or player2 = '?') AND (complete=0 or
> p1SubmitScore=0 or p2SubmitScore=0)
>
> (Information about multiturnTable is at the end of this email).
>
> My tests show that, counter-intuitively, the second query takes between
> 3 and 5 times as long as the first query.
>
> This seems weird to me - any ideas why this would be?
>
> Thanks,
> Ian
>
> Further info:
>
> multiturnTable has 70,000 rows, currently has no indices, and is created
> like this:
>
> CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
> NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE ...
> complete INTEGER ... p1SubmitScore INTEGER, p2SubmitScore INTEGER ...
> ___
> 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.so dynamic library-linux

[Pavel Ivanov]

What's wrong with compiling SQLite library by yourself with all
parameters you need?
Why do you need cross-compiler for that? What's wrong with compiler on
your linux box?

Pavel

On Fri, Mar 18, 2011 at 10:54 AM, Udon Shaun  wrote:
> Hi Peeps.
> I've noticed that the SQLite site no longer posts the linux x32 so library 
> (only
> the windows ones) which is a bit of a pain because I compile only in windows.
>
> Does anyone have a linux x32 build (sqlite 3.7.5)  with the following compiler
> options? Or can create one for me?
>
> SQLITE_ENABLE_COLUMN_METADATA
> SQLITE_ENABLE_FTS3_PARENTHESIS
> SQLITE_ENABLE_RTREE
> SQLITE_ENABLE_STAT2
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> SQLITE_DEFAULT_PAGE_SIZE 4096
> SQLITE_DEFAULT_FOREIGN_KEYS 1
>
>  I'd also appreciate a link to a decent windows->linux cross compiler "How To"
> (I'm using MingW under Codeblocks). Haven't found one yet and been searching 
> for
> ages. They all seem to be either for linux->windows or for cygwin.
>
> Thanks in advance.
>
>
>
> ___
> 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.so dynamic library-linux

[Pavel Ivanov]

Oh, and BTW, reply to the whole list, not to me only, please.

Pavel

On Fri, Mar 18, 2011 at 9:41 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> I still don't understand what you do. If you don't have Linux, you
> don't compile on Linux and you don't test on Linux then why do you
> need library for Linux? If some third-party company compiles and tests
> your application on Linux then why they can't compile SQLite on Linux
> too? It's not that hard.
>
> Pavel
>
> On Fri, Mar 18, 2011 at 9:36 PM, Udon Shaun <udon_sh...@yahoo.com> wrote:
>> What linux box? I don't have a linux box and know nothing about linux.
>> I supply a SQLite libarary (freeware) for LabVIEW. It supports win x32, x64
>> (which I do compile myself) and I used to just download the linux one. A 3rd
>> party tests the tool since they have a)LabVIEW for linux and b) linux. Now
>> it is not supplied on the SQLite site, I cannot release the next version of
>> the tool without either cross-compiling on windows (the only OS I know) or
>> dropping linux support. I'd rather not so the latter.
>> Shaun
>> 
>> From: Pavel Ivanov <paiva...@gmail.com>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Cc: Udon Shaun <udon_sh...@yahoo.com>
>> Sent: Fri, March 18, 2011 2:59:13 PM
>> Subject: Re: [sqlite] SQLite.so dynamic library-linux
>>
>> What's wrong with compiling SQLite library by yourself with all
>> parameters you need?
>> Why do you need cross-compiler for that? What's wrong with compiler on
>> your linux box?
>>
>> Pavel
>>
>> On Fri, Mar 18, 2011 at 10:54 AM, Udon Shaun <udon_sh...@yahoo.com> wrote:
>>> Hi Peeps.
>>> I've noticed that the SQLite site no longer posts the linux x32 so library
>>> (only
>>> the windows ones) which is a bit of a pain because I compile only in
>>> windows.
>>>
>>> Does anyone have a linux x32 build (sqlite 3.7.5)  with the following
>>> compiler
>>> options? Or can create one for me?
>>>
>>> SQLITE_ENABLE_COLUMN_METADATA
>>> SQLITE_ENABLE_FTS3_PARENTHESIS
>>> SQLITE_ENABLE_RTREE
>>> SQLITE_ENABLE_STAT2
>>> SQLITE_ENABLE_UPDATE_DELETE_LIMIT
>>> SQLITE_DEFAULT_PAGE_SIZE 4096
>>> SQLITE_DEFAULT_FOREIGN_KEYS 1
>>>
>>>  I'd also appreciate a link to a decent windows->linux cross compiler "How
>>> To"
>>> (I'm using MingW under Codeblocks). Haven't found one yet and been
>>> searching for
>>> ages. They all seem to be either for linux->windows or for cygwin.
>>>
>>> Thanks in advance.
>>>
>>>
>>>
>>> ___
>>> 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] storing big numbers into NUMERIC, DECIMAL columns

[Pavel Ivanov]

> Is there way how to store numeric values, which are out of REAL range ?

SQLite has no way of storing numbers other than REAL or INTEGER. If
you want the exact number to be stored your only option is to store it
as TEXT (and don't work with it as a number on SQL level).


Pavel

On Tue, Mar 22, 2011 at 2:51 AM, LacaK  wrote:
> Hi,
> I have table like this:
> CREATE TABLE tab1 (
>  a INTEGER,
>  c DECIMAL(30,7),
>  ...
> );
>
> When I am trying insert values like:
> INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456);
> INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456');
>
> values for c column are always rounded or cast to :
> 1.23456789012346e+19
>
> If I understand correctly column c has NUMERIC affinity, but when storing
> values, they are stored using REAL storage class.
> But why, when conversion from TEXT is NOT lossless ? (AFAIU first 15
> significant digits are not preserved)
> Is there way how to store numeric values, which are out of REAL range ?
>
> TIA
> -Laco.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table if not exists and insert seed data

[Pavel Ivanov]

You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If
it succeeds then you populate table with data (remember to do that in
the same transaction where you created the table). If CREATE TABLE
fails then you don't insert your data.


Pavel

On Mon, Mar 21, 2011 at 6:04 PM, Erich93063  wrote:
> I am trying to create a SQLite database if it doesn't exist, which I
> know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I
> need to initially populate the database with seed data if it doesn't
> exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create
> the table if it doesn't exist, but if I follow that up with insert
> statements, those would ALWAYS get ran. I only want to enter the seed
> data if the database does not exist. ???
>
> THANKS
> ___
> 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] storing big numbers into NUMERIC, DECIMAL columns

[Pavel Ivanov]

> Yes, but I can not affect column type ... FreePascal SQLite3 connector must
> be able to work with any user database.

If your goal is to work with any user database created outside of your
FreePascal connector then chances are that user will use the same
database outside of your FreePascal connector too. And if use declared
column as NUMERIC then he probably will expect numbers to be inserted
into that column (especially if the data type inside FreePascal is
also some sort of number). User also will probably execute some
queries that do some arithmetic operations on values in that column.
And they will be really surprised to see that not all the data is
numbers there.

So you better insert everything as numbers, let SQLite transform
everything to REAL and lose precision. And tell your users that it's
limitation of SQLite and you cannot do anything with it, only suggest
to insert big numbers as text.


Pavel

On Wed, Mar 23, 2011 at 9:06 AM, LacaK  wrote:
>> And...no conversion is performed if you declare the field as text and
>> insert as text.
>
> Yes, but I can not affect column type ... FreePascal SQLite3 connector must
> be able to work with any user database.
>
> And when user defines column like NUMERIC or DECIMAL ... so with NUMERIC
> column affinity,
> then it does not help when I write/bind numbers like TEXT
> (sqlite3_bind_text) '123456789123456789.123456789'
> because SQLite ALWAYS convert such "text number" to native floating-point or
> integer number, which leads to lost of precision.
>
> So probably before bind I will test if number of significant digits > 15
> then I will use sqlite3_bind_blob else sqlite3_bind_double
>
> -Laco.
>
>
> ___
> 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] sqlite3_step() error and constraints

[Pavel Ivanov]

> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
> at the bottom.

I believe it was changed with recent versions of SQLite. Is call to
sqlite3_extended_result_codes
(http://www.sqlite.org/c3ref/extended_result_codes.html) not needed
anymore?


Pavel


On Thu, Mar 31, 2011 at 2:08 PM, Igor Tandetnik  wrote:
> On 3/31/2011 2:02 PM, Julien Laffaye wrote:
>> When sqlite3_step() fail because a UNIQUE constraint is not satisfied,
>> it returns SQLITE_ERROR.
>> I see that there is an error code named SQLITE_CONSTRAINT. Why
>> sqlite3_step() does not return this one?
>
> For reasons described in the documentation:
> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section
> at the bottom.
> --
> 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] updating records in table A from joined records in table B

[Pavel Ivanov]

> ... that is to say, update table_a.value from table_b.value, but only
> on rows where table_a.key = table_b.key

update table_a set value =
(select table_b.value from table_b where table_b.key = table_a.key)


Pavel


On Fri, Apr 1, 2011 at 8:42 PM, Robert Poor  wrote:
> I'd like to be able to update specific records in table A from joined
> records in table B. So for example:
>
> CREATE TABLE "table_a" ("key" integer, "value" float);
> CREATE TABLE "table_b" ("key" integer, "value" float);
> INSERT INTO "table_a" ("key", "value") VALUES (1,   1.0), (2,   2.0),(3,   
> 3.0);
> INSERT INTO "table_b" ("key", "value") VALUES (1, 101.0), (2, 102.0),(4, 
> 104.0);
>
> In an imaginary version of SQLite ;) this might be written as:
>
> # UPDATE table_a
> #   JOIN table_b
> #    SET table_a.value = table_b.value
> #  WHERE table_a.key1 = table_b.key
>
> resulting in table_a:
>
> key | value
> 1   | 101
> 2   | 102
> 3   |   3
>
> ... that is to say, update table_a.value from table_b.value, but only
> on rows where table_a.key = table_b.key
>
> I've pored over the UPDATE syntax, but I don't see a way to do this.
> What's the idiom in SQLite?
> ___
> 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] Build instructions for Winodws with unicode support

[Pavel Ivanov]

> I can create the dll, but it does not work with BLOB data type. It works
> with other data types. The dll that I downloaded from the sqlite.org website
> works with BLOB data type.
>
> Any help would be appreciated.

Any pointers on what doesn't work for you and how it works instead of
intended behavior would be appreciated too.


Pavel


On Mon, Apr 4, 2011 at 11:32 AM, tiwaris  wrote:
>
> Hello,
>
> I need build instructions for Windows with unicode support.
>
> I am using sqlite3.h and sqlite3.c from the amalgamation source downloaded
> from http://sqlite.org/sqlite-amalgamation-3070500.zip.
>
> I am using the following CFLAGS when compiling sqlite
>
> SQLITE_ENABLE_COLUMN_METADATA
> SQLITE_ENABLE_FTS3
> SQLITE_THREADSAFE=1
> SQLITE_ENABLE_ICU
>
> I am linking sqlite with icuuc.lib and icuin.lib unicode libraries to create
> a sqlite dll.
>
> I can create the dll, but it does not work with BLOB data type. It works
> with other data types. The dll that I downloaded from the sqlite.org website
> works with BLOB data type.
>
> Any help would be appreciated.
>
> Thanks.
>
>
>
>
>
>
> --
> View this message in context: 
> http://old.nabble.com/Build-instructions-for-Winodws-with-unicode-support-tp31315626p31315626.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Build instructions for Winodws with unicode support

[Pavel Ivanov]

t;);
>                        System.err.println(e);
>                        e.printStackTrace();
>                }
>
>                //Print the buffers
>                System.out.print("Write buffer = ");
>                for (int i = 0; i < writeBuffer.length; i++) {
>                        System.out.print(writeBuffer[i]);
>                }
>                System.out.println();
>                System.out.print("Read  buffer = ");
>                for (int i = 0; i < readBuffer.length; i++) {
>                        System.out.print(readBuffer[i]);
>                }
>                System.out.println();
>
>                //Check the md5sum
>                try {
>                        java.security.MessageDigest digest =
> java.security.MessageDigest.getInstance("MD5");
>                        byte[] md5sum = null;
>                        java.math.BigInteger bigInt = null;
>
>                        //Write buffer
>                        digest.reset();
>                        digest.update(writeBuffer);
>                        md5sum = digest.digest();
>                        bigInt = new java.math.BigInteger(1, md5sum);
>                        System.out.println("MD5 checksum of write buffer = " +
> bigInt.toString(16));
>
>                        //Read buffer
>                        digest.reset();
>                        digest.update(readBuffer);
>                        md5sum = digest.digest();
>                        bigInt = new java.math.BigInteger(1, md5sum);
>                        System.out.println("MD5 checksum of read  buffer = " +
> bigInt.toString(16));
>                } catch (Exception e) {
>                        System.err.println("MD5 checksum not available");
>                        return;
>                }
>        }
>
>
> }
>
> The exact error message is as follows.
>
> The buffer could not be written to the database.
> java.sql.SQLException: TEST.model may not be NULL
> java.sql.SQLException: TEST.model may not be NULL
>        at org.sqlite.DB.throwex(DB.java:427)
>        at org.sqlite.DB.executeBatch(DB.java:352)
>        at org.sqlite.PrepStmt.executeBatch(PrepStmt.java:104)
>        at org.sqlite.MainDriver.(MainDriver.java:71)
>        at org.sqlite.MainDriver.main(MainDriver.java:14)
>
>
> Basically, the column with name "model" has data type BLOB, and null is
> being written to that column. When I build my application on linux,
> everything works just fine. But, on windows, it works fine the downloaded
> sqlite dll but not with my own dll.
>
>
>
> Pavel Ivanov-2 wrote:
>>
>>> I can create the dll, but it does not work with BLOB data type. It works
>>> with other data types. The dll that I downloaded from the sqlite.org
>>> website
>>> works with BLOB data type.
>>>
>>> Any help would be appreciated.
>>
>> Any pointers on what doesn't work for you and how it works instead of
>> intended behavior would be appreciated too.
>>
>>
>> Pavel
>>
> --
> View this message in context: 
> http://old.nabble.com/Build-instructions-for-Winodws-with-unicode-support-tp31315626p31317012.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request for an example code use async IO

[Pavel Ivanov]

> Can someone be so kind as to provide a short example of initializing 
> asynchronous module, opening DB for read/write, create a table, and write 
> some data to it?

There's nothing special in opening db, creating table or writing data
into it while using async module. You should use the same API in the
same way you use it without async module. All you need to do is to
initialize async module and call sqlite3async_run in the dedicated
thread. I believe everything is well explained in here
http://www.sqlite.org/asyncvfs.html (pay special attention to section
2.0) and in sqlite3async.h header. Do you find something specifically
hard to understand?


Pavel


On Mon, Apr 4, 2011 at 9:47 PM, Ricky Huang  wrote:
> Hi,
>
> I am planning on incorporating the sqlite3async module into my project to 
> improve code responsiveness during disk writes.  I've Googled all over for 
> example code on using the module but can't find any.
>
> Can someone be so kind as to provide a short example of initializing 
> asynchronous module, opening DB for read/write, create a table, and write 
> some data to it?
>
>
> Thanks in advance.
> ___
> 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] Request for an example code use async IO

[Pavel Ivanov]

To avoid races you should do this:

Main thread:
 - sqlite3async_initialize()
 - Starts child thread
 - sqlite3_open_v2()
 - sqlite3_exec(), sqlite3_prepare_v2()/sqlite3_step(), etc
 - sqlite3_close()
 - Halts and joins child thread
 - sqlite3async_shutdown()
 - exits

Child thread:
 - sqlite3async_run()


Pavel


On Tue, Apr 5, 2011 at 1:45 PM, Ricky Huang <rhhu...@soe.ucsd.edu> wrote:
> On Apr 4, 2011, at 7:10 PM, Pavel Ivanov wrote:
>
>>> Can someone be so kind as to provide a short example of initializing 
>>> asynchronous module, opening DB for read/write, create a table, and write 
>>> some data to it?
>>
>> […] All you need to do is to initialize async module and call 
>> sqlite3async_run in the dedicated thread. […]
>
> Thanks for getting back to me Pavel.
>
> So let me see if I understand what you said:
>
> Main thread:
>  - Starts child thread
>  - sqlite3_open_v2()
>  - sqlite3_exec(), sqlite3_prepare_v2()/sqlite3_step(), etc
>  - sqlite3_close()
>  - Halts and joins child thread
>
> Child thread:
>  - sqlite3async_initialize()
>  - sqlite3async_run()
>  - sqlite3async_shutdown()
>
>
> is that correct?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No journal vs WAL journal

[Pavel Ivanov]

> Furthermore, if I turn off auto checkpointing, the WAL file grows to
> more than 5 GB without transactions, but only to about 922 MB with a
> transaction. Are the commit markers really taking that much space?

WAL-journal is not some kind of change log with commit markers. It's a
sequence of changed pages with commit markers (which AFAIK can take as
much as 1Kb in size). So let's say your database has 1Kb pages, each
transaction adds a row of 50 bytes. For each transaction SQLite must
write all changed pages into WAL-journal. It would be at least one
page per transaction. So the size of WAL-journal would be like 20-40
times bigger than amount of data changed. If you combine for example
each 40 transactions into one then you'll get size of WAL-journal
probably only twice or triple the total size of changed data. With all
transactions combined into single one the size of WAL-journal will be
almost equal to size of all changed pages written once...

I hope you understand now why you see the difference in WAL-journal size.


Pavel


On Tue, Apr 5, 2011 at 10:01 PM, Nikolaus Rath  wrote:
> Hello,
>
> I'm experimenting with creation of a 156 MB database from scratch. I've
> set synchronous = NORMAL and locking_mode = exlusive.
>
> With journal_mode = off and without wrapping the INSERTs into a
> transaction, creating the db takes 54 seconds.
>
> With journal_mode = WAL (and still no transaction), it takes an
> impressive 900 seconds.
>
> With journal_mode = WAL and the INSERTs packed into a transaction, the
> total time is 60 seconds.
>
> I am slightly surprised by this. I was expecting that the difference
> between transaction/no transaction with WAL is just the writing of an
> additional few bytes of commit marker into the WAL file with every
> insert. But apparently something much more time consuming is happening
> as well.
>
> Furthermore, if I turn off auto checkpointing, the WAL file grows to
> more than 5 GB without transactions, but only to about 922 MB with a
> transaction. Are the commit markers really taking that much space?
>
> Can someone shed some light on what factor I am missing here?
>
>
> Thanks,
>
>   -Nikolaus
>
> --
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No journal vs WAL journal

[Pavel Ivanov]

> Two ?  One for the page holding the table, another for the page holding the 
> primary key ?

If table has INTEGER PRIMARY KEY then it is the same page I believe.
:) I.e. table storage is in fact an index on rowid (or its alias).


Pavel


On Wed, Apr 6, 2011 at 10:50 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 6 Apr 2011, at 3:15pm, Pavel Ivanov wrote:
>
>> For each transaction SQLite must
>> write all changed pages into WAL-journal. It would be at least one
>> page per transaction.
>
> Two ?  One for the page holding the table, another for the page holding the 
> primary key ?
>
> 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] Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84

[Pavel Ivanov]

> any idea ?

Your application have race conditions and corrupts memory.


Pavel


On Wed, Apr 6, 2011 at 2:36 PM, Vander Clock Stephane
 wrote:
> hello,
>
> in heavy multithread environnmeent we receive (one time a month, so not
> very often), this error :
> Access violation at address 6090B662 in module 'sqlite3.dll'. Read of
> address DE8D6B84
>
> any idea ?
> ___
> 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] GROUP BY Problem

[Pavel Ivanov]

Probably this could work:

SELECT c1,c2,sum(t2.c3) / count(distinct t3.rowid),count(distinct t3.rowid)
FROM t1 LEFT JOIN t2 on t2.key2=t1.key1
LEFT JOIN t3 on t3.key3=t1.key1
GROUP BY t1.key1

And btw, you didn't say where your c1 and c2 come from but if they are
from t1 and t1 has several rows with the same key1 but different c1
and c2 then you will get random data as a result of the query.


Pavel


On Thu, Apr 7, 2011 at 8:31 PM, Pete  wrote:
> I am trying to use GROUP BY to summarise information from a main table and
> two sub tables, e.g.:
>
>
> SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1
> LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1
>
>
> The result is that the count column returns the count of (the number of t2
> entries * the number of t3 entries), and the sum column returns (the t2 sum
> value * the count of entries in t3).
>
>
> For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count
> of t3.c4 is 5, the sum column returns 5000 and the count column returns 15.
>  If either of t2 or t3 has no qualifying entries, the calculation for the
> other table is correct.
>
>
> I guess GROUP BY isn't designed to deal with this type of situation.  Can
> anyone suggest a way to do this?
>
>
> Thanks,
>
> Pete
> ___
> 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] Trouble inserting null value from txt file

[Pavel Ivanov]

> There is a way to insert null values from a file without specifying it
> in?

It's impossible when you use standard sqlite3 command line utility. In
your particular case you have 2 options: either write your own
application that will recognize some kind of value as null and insert
it instead of plain text, or you can use temporary table like this:

CREATE TABLE tmp (name text);

Then import data (without first column) into it, then execute the
following statement:

insert into t (name) select name from tmp;


Pavel


On Fri, Apr 8, 2011 at 1:28 PM, craf  wrote:
> Hi.
>
> I'm new to SQLite and I am looking for a way to insert null data from
> txt file.
>
> My database is:
>
> CREATE TABLE t (id integer primary key autoincrement,
>                name text);
>
> The data in the file data.txt are:
>
> null,"Peter"
> null,"Xavier"
>
>
> The problem occurs when I import the data:
>
> sqlite>.separator ','
> sqlite>.import data.txt t
>
> The error that throws me is:
>
> "Error: datatype mismatch"
>
> It is due to null, but I tried to put as:
>
> "null","Peter"
> 'null','Peter'
> '','Peter'
>  ,'Peter'
>
> But none of these options recognizes me.
>
> There is a way to insert null values from a file without specifying it
> in?
>
> Sorry for my English!
>
> Regards
>
> Cristian
>
> ___
> 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 is the page-cache filled?

[Pavel Ivanov]

> * Which version of the source should I use? The amalgamized source
> isn't really useful

Why it's not useful for you if it contains all the functionality SQLite have?

> * Where are pages read and written? I found the page-cache, but not
> the functions that read/write those pages to disk.

You are looking for the VFS layer. Look for functions unixWrite,
unixRead, winWrite, winRead.


Pavel


On Fri, Apr 8, 2011 at 3:11 PM, Clemens Eisserer  wrote:
> Hi,
>
> I would like to create a version of sqlite, which includes a few
> changes to the way pages are loaded and written to disk.
>
> * Which version of the source should I use? The amalgamized source
> isn't really useful, however for all other source-archives its stated
> its not recommended using.
>
> * Where are pages read and written? I found the page-cache, but not
> the functions that read/write those pages to disk. A small hint would
> be really helpful :)
>
> Thanks a lot, Clemens
> ___
> 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] Problem setting journal_mode=WAL

[Pavel Ivanov]

> If the 'COMMIT' reports that no transaction is active, why is the
> JOURNAL_MODE pragma still complaining about it being within a transaction?
> Am I missing something here?

Probably you have some not finalized SELECT statements still active.
To check that you can call sqlite3_close - it will fail in such case
too.


Pavel


On Tue, Apr 12, 2011 at 9:17 AM, Jaco Breitenbach
 wrote:
> Dear experts,
>
> My application makes use of several data files attached to the SQLite
> database.  Data files are attached on demand as they become necessary.  I'm
> experimenting with the WAL journalling mode as a speed/performance
> optimisation.  Unfortunately I'm having some difficulty setting the journal
> mode to WAL on the attached databases.
>
> Here is some diagnostic output:
> Setting database 'DC2_DB_20110412' journal mode to WAL.
> sqlite3_exec('0x1f8f57a8','COMMIT',NULL,NULL,'cannot commit - no transaction
> is active') = SQLITE_ERROR
> sqlite3_exec('0x1f8f57a8','PRAGMA
> DC2_DB_20110412.JOURNAL_MODE=WAL',NULL,NULL,'cannot change into wal mode
> from within a transaction') = SQLITE_ERROR
>
> The two sqlite3_exec() statements are executed directly after each other.
> If the 'COMMIT' reports that no transaction is active, why is the
> JOURNAL_MODE pragma still complaining about it being within a transaction?
> Am I missing something here?
>
> I'm working with SQLite 3.7.3.
>
> Best regards,
> Jaco
> ___
> 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] Corruption on many Windows XP hosts

[Pavel Ivanov]

> The fact that one engineer installed a site, began operating the app,
> then saw it become corrupt minutes later rules out power loss or hard
> resets in at least that case. An operating system level problem should
> have been noticed by now given it's Windows XP... And the file is
> locally held too.

I don't know how Windows's disk cache works but theoretically when you
set synchronous to OFF (0) OS can write database pages to disk in any
order it likes, disregarding the order SQLite requires. So while one
process writes those pages, another process can read those pages and
because of random writing order second process can read inconsistent
data, meaning it sees corrupted database.


Pavel


On Wed, Apr 13, 2011 at 7:14 AM, James Green  wrote:
> Yep we've read through that.
>
> Several of the possibilities are difficult to rule out of course. They
> just seem highly unlikely (!) given the rate of corruption across our
> sites.
>
> The fact that one engineer installed a site, began operating the app,
> then saw it become corrupt minutes later rules out power loss or hard
> resets in at least that case. An operating system level problem should
> have been noticed by now given it's Windows XP... And the file is
> locally held too.
>
> Is it possible that we're seeing something nasty occurring as a result
> of anti-virus software perhaps? I might expect a higher rate of
> corruption if so.
>
> The only avenue apparently open to us is to use WAL mode. Would WAL +
> sync=normal provide a much better resistance to corruption? We
> (probably) don't care about losing the last set of SQL statements.
>
> sync=full does not work well for our app (no transactions). Far too slow.
>
> James
>
> On 13 April 2011 11:47, Richard Hipp  wrote:
>> On Tue, Apr 12, 2011 at 5:15 AM, James Green wrote:
>>
>>>  My question is does anyone have pointers
>>> to help us isolate the problems we are seeing.
>>>
>>
>> http://www.sqlite.org/howtocorrupt.html
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-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] Corruption on many Windows XP hosts

[Pavel Ivanov]

> Pavel, please tell me you are wrong.  Surely windows maintains disk cache
> coherency even in the absence of explicit FlushFileBuffers() calls?

I'm sorry, I've got this idea from some post on this list. And only
after I wrote the email I've realized how scary it could be if it
worked this way.


Pavel


On Wed, Apr 13, 2011 at 10:25 AM, Richard Hipp <d...@sqlite.org> wrote:
> On Wed, Apr 13, 2011 at 9:53 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > The fact that one engineer installed a site, began operating the app,
>> > then saw it become corrupt minutes later rules out power loss or hard
>> > resets in at least that case. An operating system level problem should
>> > have been noticed by now given it's Windows XP... And the file is
>> > locally held too.
>>
>> I don't know how Windows's disk cache works but theoretically when you
>> set synchronous to OFF (0) OS can write database pages to disk in any
>> order it likes, disregarding the order SQLite requires. So while one
>> process writes those pages, another process can read those pages and
>> because of random writing order second process can read inconsistent
>> data, meaning it sees corrupted database.
>>
>
> Pavel, please tell me you are wrong.  Surely windows maintains disk cache
> coherency even in the absence of explicit FlushFileBuffers() calls?
>
> Can any windows experts comment on this?
>
>
>
>>
>>
>> Pavel
>>
>>
>> On Wed, Apr 13, 2011 at 7:14 AM, James Green <james.mk.gr...@gmail.com>
>> wrote:
>> > Yep we've read through that.
>> >
>> > Several of the possibilities are difficult to rule out of course. They
>> > just seem highly unlikely (!) given the rate of corruption across our
>> > sites.
>> >
>> > The fact that one engineer installed a site, began operating the app,
>> > then saw it become corrupt minutes later rules out power loss or hard
>> > resets in at least that case. An operating system level problem should
>> > have been noticed by now given it's Windows XP... And the file is
>> > locally held too.
>> >
>> > Is it possible that we're seeing something nasty occurring as a result
>> > of anti-virus software perhaps? I might expect a higher rate of
>> > corruption if so.
>> >
>> > The only avenue apparently open to us is to use WAL mode. Would WAL +
>> > sync=normal provide a much better resistance to corruption? We
>> > (probably) don't care about losing the last set of SQL statements.
>> >
>> > sync=full does not work well for our app (no transactions). Far too slow.
>> >
>> > James
>> >
>> > On 13 April 2011 11:47, Richard Hipp <d...@sqlite.org> wrote:
>> >> On Tue, Apr 12, 2011 at 5:15 AM, James Green <james.mk.gr...@gmail.com
>> >wrote:
>> >>
>> >>>  My question is does anyone have pointers
>> >>> to help us isolate the problems we are seeing.
>> >>>
>> >>
>> >> http://www.sqlite.org/howtocorrupt.html
>> >>
>> >> --
>> >> D. Richard Hipp
>> >> d...@sqlite.org
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-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
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Balance between keeping DBs open vs repeated open/close

[Pavel Ivanov]

> Keep in mind that sqlite has a limit of 32 open databases.

32 _attached_ databases, not opened. With different sqlite3* handlers
you can open as many databases as you want (and your memory permits).


Pavel


On Thu, Apr 7, 2011 at 6:05 AM, Eduardo  wrote:
> At 18:11 06/04/2011, you wrote:
>>Thanks Stephan and Simon - I kind of figured it was one of those "how
>>long is a ball of string" questions, but I just wanted to check there
>>weren't any particular gotchas to watch out for.
>>
>>Regarding the separate files, it seems the best way to go. Each
>>individual table could get pretty large (possibly several gb), and the
>>one table per DB approach means that should any one file get damaged in
>>some way, only that one set of data would be lost.
>
> Keep in mind that sqlite has a limit of 32 open databases.
>
>>--
>>Paul Roberts
>
> ___
> 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] 'integer'

[Pavel Ivanov]

Stefan,

> SQLite should'nt be called a "SQL database" (as e.g. touted on its homepage).
> Instead it should clearly declare itself as an "In-memory SQL
> Datastore" or a "Data container with SQL capabilities".

This is quite serious allegations. Making them you should explain what
they are based on.
SQLite is in no way "in-memory", it stores all data to file. And
please explain how "data container" differs from "database". In some
sense all databases are "data containers" because they contain data.


Pavel


On Sun, Apr 17, 2011 at 8:12 AM, Stefan Keller  wrote:
> Michael and Jay are right about the subtleties on how SQlite
> interprets what is a data type, a primary key and a database schema
> and it's ACID implementation in general.
>
> To me, the main reason - and remedy - of this FAQ is that SQlite
> should'nt be called a "SQL database" (as e.g. touted on its homepage).
> Instead it should clearly declare itself as an "In-memory SQL
> Datastore" or a "Data container with SQL capabilities".
>
> Yours, S.
>
>
> 2011/4/17 Black, Michael (IS) :
>> Seems to behave OK for me on 3.7.5 on Windows.  What version are you using 
>> on what OS with what compile flags?
>>
>> You also "said" it didn't work but you didnt' actually what what you did.
>>
>> Like this...
>>
>>
>>
>> SQLite version 3.7.5
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>>   ...>         OtherColumn INTEGER);
>> sqlite>
>> sqlite> INSERT INTO Tg (TgConfigId) VALUES (1);
>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 1;
>> 1|
>> sqlite>
>> sqlite> SELECT * FROM Tg;
>> 1|
>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('1');
>> Error: PRIMARY KEY must be unique
>> sqlite> INSERT INTO Tg (TgConfigId) VALUES ('2');
>> sqlite> SELECT * FROM Tg;
>> 1|
>> 2|
>> sqlite> SELECT * FROM Tg WHERE TgConfigId = 2;
>> 2|
>>
>>
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>> behalf of Tobias Vesterlund [tobias.vesterl...@ericsson.com]
>> Sent: Saturday, April 16, 2011 12:40 PM
>> To: sqlite-users@sqlite.org
>> Subject: EXT :[sqlite] 'integer'
>>
>> Hi,
>>
>> I ran into something I don't understand, maybe someone here can shed some 
>> light on it for me.
>>
>> I have a table named Tg which is created (with tcl) by:
>>
>> CREATE TABLE Tg (TgConfigId INTEGER PRIMARY KEY AUTOINCREMENT,
>>        OtherColumn INTEGER);
>>
>> If I do:
>> INSERT INTO Tg (TgConfigId) VALUES (1);
>>
>> The following select works:
>> SELECT * FROM Tg WHERE TgConfigId = 1;
>>
>> But if I insert '1' instead I have to select on '1', 1 no longer works. That 
>> makes some sense, but not entirely, as what I inserted isn't an integer any 
>> longer but a string. Why would I be allowed to insert values with '' in a 
>> column that is specified to be an integer?
>>
>> Regards,
>> Tobias
>>
>> ___
>> 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] What happens if you insert more than your RAM size into an in memory database?

[Pavel Ivanov]

You won't be able to insert. The statement will fail.

Pavel


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).
>
> regards,
> Adam
> ___
> 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] Performance issues with sqlite3 sqlite3_step function

[Pavel Ivanov]

> Depending on the database size, it sometimes takes minutes to get a result.
> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
> seconds.
> 
> I use SQLITE Version 3.6.22.

Maybe SQLite Manager uses newer version of SQLite? Try to execute in
it "SELECT sqlite_version()".


Pavel


On Mon, Apr 18, 2011 at 9:45 AM, Mathias Spoerr  wrote:
> Hello,
>
>
>
> I have problems with a specific query when using the "sqlite3_step"
> function:
>
>
>
>    SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface
>
>    INNER JOIN interfaces ON
> devInterface.interfaces_int_id=interfaces.intf_id
>
>    INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id
>
>    INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id
>
>
>
> I only posted the parts of the query which I identified to cause the
> problem.
>
>
>
> For querying the database, I use the following code:
>
> http://www.dreamincode.net/forums/topic/122300-sqlite-in-c/
>
>
>
> and when debugging it, it hangs at
>
>
>
>          while(true)
>
>          {
>
>     ---> result = sqlite3_step(statement);
>
>
>
>                if(result == SQLITE_ROW)
>
>                {
>
>                     std::vector values;
>
>                     ...
>
>                }
>
>                ...
>
>          }
>
>
>
> Depending on the database size, it sometimes takes minutes to get a result.
> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
> seconds.
>
> In case the query takes several minutes, the "neighbor" and "nlink" tables
> have more than 150k entries.
>
>
>
>
>
> For buliding the database I used the Forward engineer feature of the MySQL
> Workbench tool, and modified the syntax to work for sqlite3:
>
>
>
> CREATE TABLE IF NOT EXISTS device(dev_id INTEGER PRIMARY KEY AUTOINCREMENT,
> type INT, hwtype INT, dataSource INT, hostname TEXT, sw_version TEXT,
> stpBridgeID TEXT, stpProtocol TEXT);
>
>
>
> CREATE TABLE IF NOT EXISTS interfaces(intf_id INTEGER PRIMARY KEY
> AUTOINCREMENT, intfName TEXT, intfType TEXT, phl INT, macAddress TEXT,
> ipAddress TEXT, subnetMask TEXT, duplex TEXT, speed TEXT, status TEXT,
> description TEXT, l2l3 TEXT, errLvl INT, loadLvl INT, channel_intf_id INT,
> vpc_id INT, CONSTRAINT fk_interfaces_interfaces1 FOREIGN KEY
> (channel_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE);
>
>
>
> CREATE TABLE IF NOT EXISTS devInterface (interfaces_int_id INT,
> device_dev_id INT, cdp_cdp_id INT, PRIMARY KEY (interfaces_int_id,
> device_dev_id, cdp_cdp_id), CONSTRAINT fk_dev_interface_interfaces1 FOREIGN
> KEY (interfaces_int_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE, CONSTRAINT fk_dev_interface_device1 FOREIGN KEY
> (device_dev_id) REFERENCES device (dev_id) ON DELETE CASCADE ON UPDATE
> CASCADE, CONSTRAINT fk_devInterface_cdp1 FOREIGN KEY (cdp_cdp_id) REFERENCES
> cdp (cdp_id) ON DELETE CASCADE ON UPDATE CASCADE);
>
>
>
> CREATE TABLE IF NOT EXISTS neighbor (neighbor_id INTEGER PRIMARY KEY
> AUTOINCREMENT, l2_addr TEXT NULL , l3_addr TEXT NULL);
>
>
>
> CREATE TABLE IF NOT EXISTS nlink (neighbor_neighbor_id INT,
> interfaces_intf_id INT, PRIMARY KEY (neighbor_neighbor_id,
> interfaces_intf_id), CONSTRAINT fk_table1_neighbor1  FOREIGN KEY
> (neighbor_neighbor_id ) REFERENCES neighbor (neighbor_id) ON DELETE CASCADE
> ON UPDATE CASCADE, CONSTRAINT fk_table1_interfaces1 FOREIGN KEY
> (interfaces_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE);
>
>
>
> I use SQLITE Version 3.6.22.
>
>
>
>
>
> Thanks,
>
> mspoerr
>
>
>
>
>
> ___
> 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] Performance issues with sqlite3 sqlite3_step function

[Pavel Ivanov]

> I checked
> the SQLite release notes and I could not find changes for sqlite3_step.
> Should I upgrade anyway?

Did you notice in release notes something like "optimizer
improvements"? That's what impacts the sqlite3_step performance.


Pavel


On Mon, Apr 18, 2011 at 9:54 AM, Spoerr Mathias <math...@spoerr.org> wrote:
> Hello Pavel,
>
> thank you for your answer. SQLite Manager uses version 3.7.4, but I checked
> the SQLite release notes and I could not find changes for sqlite3_step.
> Should I upgrade anyway?
>
> Thanks,
> Mathias
>
>
>
> On Mon 18/04/11 15:49 , "Pavel Ivanov" paiva...@gmail.com sent:
>
>> Depending on the database size, it sometimes takes minutes to get a
>> result.
>> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
>> seconds.
>> 
>> I use SQLITE Version 3.6.22.
>
> Maybe SQLite Manager uses newer version of SQLite? Try to execute in
> it "SELECT sqlite_version()".
>
>
> Pavel
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

[Pavel Ivanov]

OK, sorry I didn't tell you all possible word combinations to look
for. It looks like release notes use "query planner" instead of
"optimizer".


Pavel


On Mon, Apr 18, 2011 at 10:09 AM, Spoerr Mathias <math...@spoerr.org> wrote:
> the last Optimizer enhancement was with 3.6.22
> http://www.sqlite.org/changes.html
>
> Thanks,
> Mathias
>
>
>
> On Mon 18/04/11 16:05 , "Pavel Ivanov" paiva...@gmail.com sent:
>
>> I checked
>> the SQLite release notes and I could not find changes for sqlite3_step.
>> Should I upgrade anyway?
>
> Did you notice in release notes something like "optimizer
> improvements"? That's what impacts the sqlite3_step performance.
>
>
> Pavel
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building sqlite for windows in a proper way

[Pavel Ivanov]

> Does anyone one know how to build sqlite to get the same binary as on
> download page ?

Did you try to remove all those defines that you add at build time and
leave only default values set inside sqlite3.c file?


Pavel


On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak  wrote:
> Hello
>
> My problem is no matter how i build sqlite - my binary is much slower than
> the precompiled one on sqlite download page (about 3 - 6 times depending on
> the query).
>
> I am using sqlite3.h and sqlite3.c from the amalgamation source:
>
> http://www.sqlite.org/sqlite-amalgamation-3070602.zip
>
> I have added the following flags when compiling sqlite:
>
> gcc
> -s -O4 -I. -fomit-frame-pointer
> -DNDEBUG
> -DSQLITE_OS_WIN=1
> -DSQLITE_HAVE_READLINE=0
> -DSQLITE_THREADSAFE=1
> -DSQLITE_TEMP_STORE=2
> -DSQLITE_ENABLE_RTREE
> -DSQLITE_ENABLE_FTS3
> -DSQLITE_OMIT_COMPILEOPTION_DIAGS
> -DSQLITE_ENABLE_COLUMN_METADATA
> -DNO_TCL
>
> I built it both with MINGW and with MSVS 2010.
>
> Does anyone one know how to build sqlite to get the same binary as on
> download page ?
>
> Any help would be appreciated.
>
> Thanks.
>
> Jakub
> ___
> 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] Is Lock Table statement spported?

[Pavel Ivanov]

Whenever you have doubt like that just go to
http://www.sqlite.org/lang.html and see if the statement you are
trying to execute exists there and what its correct syntax is.


Pavel


On Wed, Apr 20, 2011 at 10:40 AM,   wrote:
> Is LOCK TABLE statement supported by by SQLite? I am getting syntax error 
> when I execute this "LOCK TABLE tablename IN EXCLUSIVE MODE NOWAIT". Just 
> want to make sure it's not supported rather than my syntax is incorrect.
>
>
> ___
> 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] just fyi: iPhone and iPad 3G's tracking their owners' movements and saving to sqlite db

[Pavel Ivanov]

It's not related to the list but still...

> Technically, the data referred to is as follows.  An iPhone logs details of 
> which phone base stations it connects to, and the 'status' data obtained from 
> the base station when it was connected.  The location (long & lat) of the 
> base station is part of the base station's status string.  That is all.

I believe it's not location of the base station, it's location of the
phone itself based on GPS information. That's why these news are so
shocking.


Pavel


On Wed, Apr 20, 2011 at 7:17 PM, Simon Slavin  wrote:
>
> On 21 Apr 2011, at 12:03am, Donald Griggs wrote:
>
>> The video mentions that the file "consolidated.db" is an ordinary sqlite
>> file, containing latitude, longitude, and timestamps.   The data they
>> examined went back several months.
>>
>> http://radar.oreilly.com/2011/04/apple-location-tracking.html
>>
>> There's no allegation the data is *transmitted* to Apple or anyone else, but
>> this is intriguing nonetheless.
>
> SQLite is not really related to this, other than the data is held in an 
> SQLite database file, as is almost all database-suitable data (rows and 
> columns) in an iPhone.
>
> Technically, the data referred to is as follows.  An iPhone logs details of 
> which phone base stations it connects to, and the 'status' data obtained from 
> the base station when it was connected.  The location (long & lat) of the 
> base station is part of the base station's status string.  That is all.  Many 
> phones for many manufacturers do this: it's a vital part of the information 
> used when looking at poor signal strengths and dropped calls.
>
> I am not at all surprised that this data is collected, since it's invaluable 
> when diagnosing problems with the phone functions.  What surprises me is that 
> entries in that database are not deleted after, say, a month.
>
> 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] date field with default current date

[Pavel Ivanov]

> Does not work on python with sqlite3 module

What does "SELECT sqlite_version()" gives you in python with sqlite3 module?


Pavel


On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro  wrote:
> Hi.
>
> 2011/4/21 Black, Michael (IS) 
>
>> create table t (d default CURRENT_DATE,i number);
>> insert into t (i) values(1);
>> select * from t;
>> 2011-04-21|1
>>
>>
>>
>> Use CURRENT_TIME if you want hours/minutes too.
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
>> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
>> Sent: Thursday, April 21, 2011 5:37 AM
>> To: General Discussion of SQLite Database
>> Subject: EXT :[sqlite] date field with default current date
>>
>> Hi.
>> I'm working with python and sqlite3 and i ask how to create a table with a
>> date
>> field wih defaults current date.
>> Thanks.
>> --
>> Fabio Spadaro
>> www.fabiospadaro.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
>>
>
> Does not work on python with sqlite3 module
>
> --
> Fabio Spadaro
> www.fabiospadaro.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] date field with default current date

[Pavel Ivanov]

>> What does "SELECT sqlite_version()" gives you in python with sqlite3
>> module?
> [(u'3.5.9',)]

Well, CURRENT_DATE should work then, it was added in 3.1.0. Could you
show us an exact statement you are trying to execute and the exact
text of error you get?


Pavel


On Thu, Apr 21, 2011 at 1:43 PM, Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> Hi.
>
> 2011/4/21 Pavel Ivanov <paiva...@gmail.com>
>
>> > Does not work on python with sqlite3 module
>>
>> What does "SELECT sqlite_version()" gives you in python with sqlite3
>> module?
>>
>>
>> Pavel
>>
>>
>> On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro <fabiolinos...@gmail.com>
>> wrote:
>> > Hi.
>> >
>> > 2011/4/21 Black, Michael (IS) <michael.bla...@ngc.com>
>> >
>> >> create table t (d default CURRENT_DATE,i number);
>> >> insert into t (i) values(1);
>> >> select * from t;
>> >> 2011-04-21|1
>> >>
>> >>
>> >>
>> >> Use CURRENT_TIME if you want hours/minutes too.
>> >>
>> >>
>> >>
>> >> Michael D. Black
>> >>
>> >> Senior Scientist
>> >>
>> >> NG Information Systems
>> >>
>> >> Advanced Analytics Directorate
>> >>
>> >>
>> >>
>> >> 
>> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> >> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
>> >> Sent: Thursday, April 21, 2011 5:37 AM
>> >> To: General Discussion of SQLite Database
>> >> Subject: EXT :[sqlite] date field with default current date
>> >>
>> >> Hi.
>> >> I'm working with python and sqlite3 and i ask how to create a table with
>> a
>> >> date
>> >> field wih defaults current date.
>> >> Thanks.
>> >> --
>> >> Fabio Spadaro
>> >> www.fabiospadaro.com<http://www.fabiospadaro.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
>> >>
>> >
>> > Does not work on python with sqlite3 module
>> >
>> > --
>> > Fabio Spadaro
>> > www.fabiospadaro.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
>>
>
> [(u'3.5.9',)]
>
>
> --
> Fabio Spadaro
> www.fabiospadaro.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] date field with default current date

[Pavel Ivanov]

> In my system is set up with 11:35:07 while I am in the data 9:37:30
> Why?

http://www.sqlite.org/lang_createtable.html
"If the default value of a column is CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP, then the value used in the new row is a text
representation of the current UTC date and/or time."

It won't be your local time.


Pavel


On Fri, Apr 22, 2011 at 5:46 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> Hi.
>
> 2011/4/22 Fabio Spadaro <fabiolinos...@gmail.com>
>
>> Hi
>>
>>
>> 2011/4/22 Fabio Spadaro <fabiolinos...@gmail.com>
>>
>>> Hi.
>>>
>>>
>>> 2011/4/22 Pavel Ivanov <paiva...@gmail.com>
>>>
>>>> >> What does "SELECT sqlite_version()" gives you in python with sqlite3
>>>> >> module?
>>>> > [(u'3.5.9',)]
>>>>
>>>> Well, CURRENT_DATE should work then, it was added in 3.1.0. Could you
>>>> show us an exact statement you are trying to execute and the exact
>>>> text of error you get?
>>>>
>>>>
>>>> Pavel
>>>>
>>>>
>>>> On Thu, Apr 21, 2011 at 1:43 PM, Fabio Spadaro <fabiolinos...@gmail.com>
>>>> wrote:
>>>> > Hi.
>>>> >
>>>> > 2011/4/21 Pavel Ivanov <paiva...@gmail.com>
>>>> >
>>>> >> > Does not work on python with sqlite3 module
>>>> >>
>>>> >> What does "SELECT sqlite_version()" gives you in python with sqlite3
>>>> >> module?
>>>> >>
>>>> >>
>>>> >> Pavel
>>>> >>
>>>> >>
>>>> >> On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro <
>>>> fabiolinos...@gmail.com>
>>>> >> wrote:
>>>> >> > Hi.
>>>> >> >
>>>> >> > 2011/4/21 Black, Michael (IS) <michael.bla...@ngc.com>
>>>> >> >
>>>> >> >> create table t (d default CURRENT_DATE,i number);
>>>> >> >> insert into t (i) values(1);
>>>> >> >> select * from t;
>>>> >> >> 2011-04-21|1
>>>> >> >>
>>>> >> >>
>>>> >> >>
>>>> >> >> Use CURRENT_TIME if you want hours/minutes too.
>>>> >> >>
>>>> >> >>
>>>> >> >>
>>>> >> >> Michael D. Black
>>>> >> >>
>>>> >> >> Senior Scientist
>>>> >> >>
>>>> >> >> NG Information Systems
>>>> >> >>
>>>> >> >> Advanced Analytics Directorate
>>>> >> >>
>>>> >> >>
>>>> >> >>
>>>> >> >> 
>>>> >> >> From: sqlite-users-boun...@sqlite.org [
>>>> sqlite-users-boun...@sqlite.org]
>>>> >> on
>>>> >> >> behalf of Fabio Spadaro [fabiolinos...@gmail.com]
>>>> >> >> Sent: Thursday, April 21, 2011 5:37 AM
>>>> >> >> To: General Discussion of SQLite Database
>>>> >> >> Subject: EXT :[sqlite] date field with default current date
>>>> >> >>
>>>> >> >> Hi.
>>>> >> >> I'm working with python and sqlite3 and i ask how to create a table
>>>> with
>>>> >> a
>>>> >> >> date
>>>> >> >> field wih defaults current date.
>>>> >> >> Thanks.
>>>> >> >> --
>>>> >> >> Fabio Spadaro
>>>> >> >> www.fabiospadaro.com<http://www.fabiospadaro.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] query to find mixed values

[Pavel Ivanov]

> REQ3 is a problem because you have mixed signs in INV and only one record in 
> REP
>
> How would I craft a query to return REQ3?

Could be something like this:

select INV.REQ,
count(case when INV.AMT > 0 then 1 else null end) positive,
count(case when INV.AMT < 0 then 1 else null end) negative,
count(distinct REP.rowid) rep_amount
from INV, REP
where INV.REQ = REP.REQ
group by INV.REQ
having positive > 0 and negative > 0 and rep_amount = 1;


Pavel


On Tue, Apr 26, 2011 at 12:23 PM, David Bicking  wrote:
> I have two tables:
>
> CREATE TABLE INV
> ( REQ int,
>  INV char,
>  AMT float );
>
> CREATE TABLE REP
> ( REQ int,
>  ACCT char
>  AMT float );
>
> I need to flag any combinations where for a given REQ value, one table has a 
> list of numbers of mixed signs, and the other table has one and only one 
> value.
>
> So
> REQ|INV|AMT
> 1|i1|1
> 1|i2|2
> 2|i3|3
> 2|i4|-6
> 3|i5|5
> 3|i6|-4
>
> REQ|ACCT|AMT
> 1|a1|3
> 2|a2|-2
> 2|a3|-1
> 3|a4|1
>
> REQ1 is okay because the values are the same sign.
> REQ2 is okay because there are more than one record in each table
> REQ3 is a problem because you have mixed signs in INV and only one record in 
> REP
>
> How would I craft a query to return REQ3?
>
> At this point, I haven't a clue, so any help would be appreciated.
>
> Thanks,
> David
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vacuum and rowids

[Pavel Ivanov]

> After more poking, it appears that rowids might not be changed by a vacuum if 
> I have an index on the table. Is this true? If so, is it something I can rely 
> on going forward?

No, it's not true. The only way to keep your rowids intact is to
declare an INTEGER PRIMARY KEY alias for it. And you better never
reference "rowid" name in your application or your database schema.


Pavel


On Thu, Apr 28, 2011 at 3:36 PM, Dave Hayden  wrote:
> When the VACUUM feature was added I took a look at using it to keep database 
> file sizes down, but discovered that it changed rowids and messed up my 
> references between tables (or what I gather the database people call "foreign 
> keys"). I'm playing around with this again and it looks like rowids aren't 
> affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild 
> the existing tables if I don't have to.
>
> After more poking, it appears that rowids might not be changed by a vacuum if 
> I have an index on the table. Is this true? If so, is it something I can rely 
> on going forward?
>
> Thanks!
> -Dave
>
> ___
> 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] vacuum and rowids

[Pavel Ivanov]

>> which suggests that referring to rowids is fine.
>
> It does not suggest referring to ROWIDs is fine, it only says that it
> can be done.  I think Pavel's point is that referencing ROWIDs is bad
> practice, so that is why he says you shouldn't do it.

Yes, that's right. You can refer to rowid, but it's a bad practice.
Especially if you refer to it in foreign keys. As you have seen just
vacuum the database and suddenly you see some bugs, incorrect foreign
key references and you don't understand how they were able to make
their way into the database.

You are right that by just adding a column ROWID INTEGER PRIMARY KEY
you can fix things without changing much of the code but it would be a
big confusion for any developer who will look at your code either
after you or as an additional developer on the project. So in a short
term if you want a quick hack that would be fairly decent solution.
But in a long term I would suggest to add column ID INTEGER PRIMARY
KEY and change all references to rowid towards id.


Pavel


On Thu, Apr 28, 2011 at 9:31 PM, Rich Rattanni  wrote:
>> "You can access the ROWID of an SQLite table using one the special column 
>> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
>> to use one of those special names, then the use of that name will refer to 
>> the declared column not to the internal ROWID."
>>
>> which suggests that referring to rowids is fine.
>
> It does not suggest referring to ROWIDs is fine, it only says that it
> can be done.  I think Pavel's point is that referencing ROWIDs is bad
> practice, so that is why he says you shouldn't do it.
>
> --
> Rich
> ___
> 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] Compile Error

[Pavel Ivanov]

I believe any compilation options that require changes in SQL parser
require compiling from original sources as well. They cannot be used
with amalgamation file which has already generated SQL parser's code.


Pavel


On Tue, May 3, 2011 at 3:05 PM, jeff archer  wrote:
> I am attempting to compile SQLite on windows with SQLITE_OMIT_TRIGGER defined
> and I get the following errors.
>
> Sure enought the code in yy_...() is not exclosed in ifndef 
> SQLITE_OMIT_TRIGGER
>
> 1> Creating library ..\..\Bin\x64\Debug\SqlUtils.lib and object
> ..\..\Bin\x64\Debug\SqlUtils.exp
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3DeleteTriggerStep referenced in function yy_destructor
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3DropTrigger
> referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerSelectStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerDeleteStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerInsertStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol
> sqlite3TriggerUpdateStep referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3BeginTrigger
> referenced in function yy_reduce
> 1>sqlite3.obj : error LNK2019: unresolved external symbol sqlite3FinishTrigger
> referenced in function yy_reduce
> 1>..\..\Bin\x64\Debug\SqlUtils.dll : fatal error LNK1120: 8 unresolved 
> externals
>  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] INNER JOIN Optimization

[Pavel Ivanov]

> The problem is that I would like to avoid splitting the query into two parts. 
> I would expect SQLite to do the same thing for me automatically (at least in 
> the second scenario), but it does not seem to happen... Why is that?

In short, because SQLite cannot read your mind.

To understand the answer compare speeds of executing one query (with
one TABLE_A) and creating an in-memory database, creating a table in
it and using that table in one query (with the same TABLE_A). I bet
the first option (straightforward query without in-memory database)
will be much faster. So SQLite selects the fastest way to execute your
query. It cannot predict what the future queries will be to understand
how to execute the whole set of queries faster. You can do that and
you should split your query in two parts.


Pavel


On Wed, May 4, 2011 at 10:13 AM, petmal Malik  wrote:
>
> Hello.
>
> I have two tables to join. TABLE_A (contains column 'a') and TABLE_BC 
> (contains columns 'b' and 'c'). There is a condition on TABLE_BC. The two 
> tables are joined by 'rowid'.
> Something like:
>
> SELECT a, b, c FROM main.TABLE_A INNER JOIN main.TABLE_BC WHERE (b > 10.0 AND 
> c < 10.0) ON main.TABLE_A.rowid = main.TABLE_BC.rowid ORDER BY a;
>
> Alternatively:
>
> SELECT a, b, c FROM main.TABLE_A AS s1 INNER JOIN (SELECT rowid, b, c FROM 
> main.TABLE_BC WHERE (b > 10.0 AND c < 10.0)) AS s2 ON s1.rowid = s2.rowid 
> ORDER BY a;
>
> I need to do this a couple of time with different TABLE_A, but TABLE_BC does 
> not change... I could therefore speed things up by creating a temporary 
> in-memory database (mem) for the constant part of the query.
>
> CREATE TABLE mem.cache AS SELECT rowid, b, c FROM main.TABLE_BC WHERE (b > 
> 10.0 AND c < 10.0);
>
> followed by (many):
>
> SELECT a, b, c FROM main.TABLE_A INNER JOIN mem.cache ON main.TABLE_A.rowid = 
> mem.cache.rowid ORDER BY a;
>
> I get the same result set from all the queries above, but the last option is 
> by far the fastest one.
> The problem is that I would like to avoid splitting the query into two parts. 
> I would expect SQLite to do the same thing for me automatically (at least in 
> the second scenario), but it does not seem to happen... Why is that?
>
> Thanks.
>
>
> ___
> 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] Addition: Re: SQLite locking question

[Pavel Ivanov]

>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

Another suggestion could be to turn off shared cache mode.


Pavel


On Fri, May 6, 2011 at 7:15 AM, Dan Kennedy  wrote:
> On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
>> Hi Dan,
>>
 I have to correct myself a little... the hanging sqlite3_open_v2() does
 not happe while the prepare of the first query but during the first
 sqlite3_step() of the first query...
>>>
>>> Each shared-cache has a mutex associated with it. When
>>> sqlite3_step is called it grabs the mutexes for all shared-caches
>>> it might use. The mutexes are not released until the call
>>> to sqlite3_step() returns.
>>>
>>> So if you have one query that spends a lot of time in sqlite3_step()
>>> you are going to block your other threads. Unfortunately, that is
>>> the nature of shared-cache mode.
>>
>> Thanks for the info. But is this even true when enabling read
>> uncommitted isolation mode using:
>>
>> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
>> 0,)
>>
>> ??
>>
>> I suspect it's the master table lock that is hold there, right?
>
> You are correct that your query will hold a shared-lock on the
> master table at the shared cache level, but it's the mutex that
> is causing you problems. Shared-cache locks are held for the
> duration of a transaction to ensure transactions are correctly
> isolated. In this case the master table is locked to make sure
> that the table your query is accessing is not dropped by another
> thread while you are using it.
>
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.
>
>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.
>
> Dan.
>
> ___
> 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] Query question

[Pavel Ivanov]

> sqlite> select * from a where a=10 group by b;
> 10|1
> 10|2
> sqlite> select * from a where a=11 group by b;
> 11|2
> 11|3
>
> How can I do count equivalent of such a query to find out how many
> distinct values of b there are for a given a? (That is get an answer of
> 2 in the above)

select count(distinct b) from a where a = 11;


Pavel


On Fri, May 6, 2011 at 10:03 AM, Matthew Jones  wrote:
> A simple query question that I really ought to know the answer to but don't:
>
> Given a table with multiple columns in it how do I count how many
> entries there are with column A matching some value where column B is
> distinct?
>
> sqlite> create table a (a, b);
> sqlite> insert into a values (10, 1);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 3);
> sqlite> select * from a where a=10 group by b;
> 10|1
> 10|2
> sqlite> select * from a where a=11 group by b;
> 11|2
> 11|3
>
> How can I do count equivalent of such a query to find out how many
> distinct values of b there are for a given a? (That is get an answer of
> 2 in the above)
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> Long Down Avenue
> Stoke Gifford
> Bristol.  BS34 8QZ
> Tel:   +44 (0) 117 312 7490
> Email:    matthew.jo...@hp.com
>
> Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks,
> RG12 1HN. Registered No: 690597 England
>
> The contents of this message and any attachments to it are confidential
> and may be legally privileged. If you have received this message in
> error, you should delete it from your system immediately and advise the
> sender.
>
> To any recipient of this message within HP, unless otherwise stated you
> should consider this message and attachments as "HP CONFIDENTIAL".
> ___
> 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] Addition: Re: SQLite locking question

[Pavel Ivanov]

> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?

Yes, you are involved in a "magical" thinking. All that you said would
be true if any change that SQLite does in the database cache was
atomic. I.e. let's say SQLite needs to add a row, zing, and the row is
in there and all necessary cache pages are changed without other
thread ever noticing any inconsistent state during the process. Also
let's say SQLite needs to find some row in the table, zing, and it
already knows where that row is and other thread didn't change
anything during the search... All this cannot happen, thus mutex is
held.

BTW, if you just read data then locking cannot be an issue for you.
Turn off shared cache and you will be okay.


Pavel


On Fri, May 6, 2011 at 3:56 PM, Ole Reinhardt
 wrote:
> Hi Pavel,
>
>> >> Any other chance to speed this up (apart from the obvious "optimize the
>> >> query, do not use distinct on large tables)=
>> >
>> > Without seeing the query or database schema? Not really... Depending
>> > on the exact query an index on "xyz" might help.
>>
>> Another suggestion could be to turn off shared cache mode.
>
> Oh ok :)
>
> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?
>
> Bye,
>
> Ole
>
> --
>
> Thermotemp GmbH, Embedded-IT
>
> Embedded Hard-/ Software and Open Source Development,
> Integration and Consulting
>
> http://www.embedded-it.de
>
> Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen -
> tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97
>
> Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
> Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
> Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
> Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question: Memory-Based Databases

[Pavel Ivanov]

> Is this true, or is the memory usage pretty much similar?

Until you reach limit set by 'pragma cache_size' memory usage would be
the same for in-memory database and on-disk database. When the size of
your database grows beyond 'pragma cache_size' in-memory database
starts to consume more memory than on-disk one, because it has nowhere
to push pages out of the cache.


Pavel


On Tue, May 10, 2011 at 9:18 AM, Tito Ciuro  wrote:
> Hello,
>
> I have been using memory-based databases (opened via :memory:) and 
> performance is great. However, one of the things I assumed with memory-based 
> databases was that memory usage would be higher than the temporary or 
> persistent databases stored on disk. Is this true, or is the memory usage 
> pretty much similar? I've perform a few tests with fairly small data sets and 
> I don't see a difference. Any ideas?
>
> Thanks,
>
> -- Tito
> ___
> 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] Three questions

[Pavel Ivanov]

>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>
> I get SQLITE_BUSY "database is locked" at this point, as I would expect.

MySQL may behave differently but that's discussion not for this list.


Pavel


On Wed, May 11, 2011 at 11:41 AM, Igor Tandetnik  wrote:
> On 5/11/2011 11:24 AM, Dagdamor wrote:
>> Hello. First of all, sorry for posting three completely different questions 
>> into the same message ;)
>>
>> Question #1. I was working with transactions in InnoDB (MySQL) and noticed a 
>> weird behavior. Consider we have a table:
>>
>> CREATE TABLE test (id INT NOT NULL PRIMARY KEY);
>>
>> and two transactions (from two different connections) are trying to insert a 
>> record at once:
>>
>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>
> I get SQLITE_BUSY "database is locked" at this point, as I would expect.
> --
> 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] Three questions

[Pavel Ivanov]

> Therefore, the second insert fails on every database system i ever 
> encountered.

Apparently you didn't encounter Oracle. In such situation Oracle
freezes transaction B until transaction A is committed or rollbacked.
After that it knows what to return to transaction B - error or success
correspondingly.


Pavel


On Wed, May 11, 2011 at 1:18 PM, Martin Engelschalk
 wrote:
> Hello,
>
> This question does not arise with SQLite, because parallel transaction
> are not supported, as Igor and Pavel pointed out.
>
> However, consider this: If you have a unique constraint on a table like
> in your example, when should the database enforce it?
>
> To use your example and add a second colum
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this 
> also works like you expected.
> 00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 
> 'bar') is now committed. However, Transaction A was first!
> 00:06 Transaction A: COMMIT // This cannot work. What error message would you 
> expect?
>
> Now, consider large transactions with many Operations.
> Therefore, the second insert fails on every database system i ever 
> encountered.
>
> Martin
>
>
> Am 11.05.2011 17:24, schrieb Dagdamor:
>> and two transactions (from two different connections) are trying to insert a 
>> record at once:
>>
>> 00:01 Transaction A: BEGIN
>> 00:02 Transaction B: BEGIN
>> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
>> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
>> key' error! why???
>> 00:05 Transaction A: ROLLBACK // works okay, table remains empty
>> 00:06 Transaction B: ??? // has nothing to do because was unable to insert a 
>> record into an empty table!
>>
>> To put it simple, transaction A tried to insert a record but soon aborted 
>> itself via ROLLBACK. If I understand transactions principle correctly, a 
>> rolled-back transaction should act like it never happened in the first 
>> place, and other threads should not see its traces. But for some reason 
>> another transaction noticed that and refused to insert values into table. 
>> The question is: is that a correct behavior, and I should keep this in mind, 
>> or SQLite would handle this scenario different way? :/
> ___
> 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 I dynamically select a table ?

[Pavel Ivanov]

> I can't trully construct sql statement piece by piece with SQL
> db as I did with Oracle. Just wanted to confirm.

Why do you need to construct SQL specifically with db's tools? Why
can't you do that in your host language?
Oracle needs dynamic SQL feature because it will work much faster than
the same made in the application which will have to do a lot of
network round-trips while gathering pieces together. But in SQLite
there is no such thing as network round trip and so dynamic SQL won't
work any faster than the same logic in your application. In fact it
would work even slower because SQLite would have to implement
something general with lots of different features and you can
implement something simple and optimized to your particular use case.


Pavel


On Wed, May 11, 2011 at 10:03 PM, John  wrote:
> Yes, I could. But considering that I'm applying tons of logic and not just
> selected this would be a real mess. Not even sure I could pull it.
> Normalization was something I lacked with regard to previous post. But in
> this case, I don't think it has anything to do with it. It's just alack of
> dynamic sql. I can't trully construct sql statement piece by piece with SQL
> db as I did with Oracle. Just wanted to confirm.
>
> On Wed, May 11, 2011 at 9:58 PM, Nico Williams wrote:
>
>> On Wed, May 11, 2011 at 8:47 PM, John  wrote:
>> > That would work if I needed to select a single column from a table. But
>> if I
>> > need to select multiple values (c1, c2), then it wouldn't work. Can't
>> have
>> > subquery with more than one column selected, in general, I think.
>>
>> You can do one case for each result column.  It gets wordy, fast.
>>
>> Normalization helps...  :)
>>
>> Nico
>> --
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> ~John
> ___
> 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] Multi-threading Common Problem

[Pavel Ivanov]

> "After a BEGIN EXCLUSIVE, no other database connection except for 
> read_uncommitted connections will be able to read the database and no other 
> connection without exception will be able to write the database until the 
> transaction is complete."
>
> This tells me that reads outside of a transaction would be permitted while an 
> exclusive transaction is taking place.

This works only when shared cache mode is turned on and only within
the same process. Nothing uncommitted can be read between processes or
between different connections in the same process when shared cache is
turned off.


Pavel


On Thu, May 12, 2011 at 4:26 PM, John Deal  wrote:
> Good question.  Very possible my understanding is not complete.
>
> I have basically read and write transactions, each potentially with several 
> accesses to the DB.  I want to ensure that if a write transaction is 
> happening, no read transactions are in progress since it would be possible to 
> have obtain incomplete data (mixture of some reads being valid but other no 
> longer valid because the write transaction changed them).  In other words, a 
> read "transaction" (I do not use a transaction for the reads) consists of 
> multiple pieces of data that makeup a set that I want to ensure is valid as a 
> set.
>
> It is my understanding that a transaction (which I do use for the write 
> transaction which is also a set) locks the DB for writes but not reads. If a 
> transaction does lock the DB for exclusive access then you are correct, I do 
> not need the OS mutexes.  Maybe I do not understand the following:
>
> "After a BEGIN EXCLUSIVE, no other database connection except for 
> read_uncommitted connections will be able to read the database and no other 
> connection without exception will be able to write the database until the 
> transaction is complete."
>
> This tells me that reads outside of a transaction would be permitted while an 
> exclusive transaction is taking place.
>
> If a write transaction is not taking place, I want to allow multiple reads 
> which the OS rwlock allows.
>
> Any enlightenment would be welcomed.
>
> Thanks.
>
> --- On Thu, 5/12/11, Roger Binns  wrote:
>
>> From: Roger Binns 
>> Subject: Re: [sqlite] Multi-threading Common Problem
>> To: sqlite-users@sqlite.org
>> Date: Thursday, May 12, 2011, 4:01 PM
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 05/12/2011 09:38 AM, John Deal wrote:
>> > I have been working for weeks on this and I feel there
>> must be something simple I am overlooking.
>>
>> Why are you discarding SQLite's builtin and tested mutexes
>> and then
>> effectively reimplementing your own to get the same
>> effect?
>>
>> Or bigger picture question what is it you are trying to
>> achieve in the first
>> place?
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.11 (GNU/Linux)
>>
>> iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c
>> ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6
>> =jehy
>> -END PGP SIGNATURE-
>> ___
>> 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] Common Multi-treaded Problem

[Pavel Ivanov]

> Interesting is the impression I had with prepared statements was the reset 
> was only necessary if you wanted to reuse that statement.  Since each each DB 
> connection is in its own instance of a class (with it own set of prepared 
> statements) I would not think there would be any dependency on different 
> physical prepared statements on different threads.  I would expect this with 
> incomplete transactions.

There's no dependency between different prepared statements, but there
is dependency between transactions as they use the same database. And
transaction cannot be finished (implicitly or explicitly) until all
statements in this transaction are reset or finalized.


Pavel


On Thu, May 12, 2011 at 4:01 PM, John Deal  wrote:
> Hello Igor,
>
> That very well maybe it.  I am not at home so can't test for sure but I reset 
> the prepared statements right before I use them so they are left hanging if 
> another thread came in.
>
> Interesting is the impression I had with prepared statements was the reset 
> was only necessary if you wanted to reuse that statement.  Since each each DB 
> connection is in its own instance of a class (with it own set of prepared 
> statements) I would not think there would be any dependency on different 
> physical prepared statements on different threads.  I would expect this with 
> incomplete transactions.
>
> Anyway, thanks for the insight.
>
> John
>
> --- On Thu, 5/12/11, Igor Tandetnik  wrote:
>
>> From: Igor Tandetnik 
>> Subject: Re: [sqlite] Common Multi-treaded Problem
>> To: sqlite-users@sqlite.org
>> Date: Thursday, May 12, 2011, 12:35 PM
>> On 5/12/2011 12:31 PM, John Deal
>> wrote:
>> > When I allow multiple readers with each thread using a
>> different DB
>> > connection (open with the same flags) and each thread
>> having
>> > exclusive use of its DB connection (no sharing of
>> connections) and if
>> > more than one thread is reading the DB at the same
>> time, the DB
>> > becomes locked for writing even when all the reads are
>> finished.
>>
>> My first inclination would be to look for places where you
>> leak active
>> statement handles, by failing to reset or finalize
>> statements. The read
>> operation is not really finished until the statement is
>> reset/finalized.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common Multi-treaded Problem

[Pavel Ivanov]

> Humm.  Resetting each prepared statement right after use seemed to work.  So 
> in review, a select prepared statement will lock the DB from other threads 
> (or is it DB connections?) but not the current thread (or is it DB 
> connection).

Yes, you are right. Transactions and database locks are
per-connection. So if you work with only one connection (even sharing
it between different threads) and not resetting your statements it
means that all your application works in a one huge transaction,
committing everything at the end (I guess if you hard kill your
application in the middle you'll see that nothing was committed to the
database). And if you work with several different connections (no
matter in different threads or in a single thread) they will block
each other, i.e. if you execute writing transaction in one connection
you won't be able to write in a second connection and sometimes you
won't even able to read in a second connection.

And answering your question from another email: you can step through
any number of prepared statements simultaneously as long as they are
all created from the same connection. They won't block each other from
executing. You just can't call sqlite3_step() on one connection
simultaneously in several threads - they will be serialized. Other
than that you are free to step through any number of select statements
and execute updates in parallel. But there's one rule of thumb to
remember: never change data that should be returned in some active
select statement. You can get very surprising behavior in this case.


Pavel


On Thu, May 12, 2011 at 8:33 PM, John Deal <bassd...@yahoo.com> wrote:
> Hello Pavel,
>
> Humm.  Resetting each prepared statement right after use seemed to work.  So 
> in review, a select prepared statement will lock the DB from other threads 
> (or is it DB connections?) but not the current thread (or is it DB 
> connection).
>
> Thanks for the help!
>
> John
>
> --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> From: Pavel Ivanov <paiva...@gmail.com>
>> Subject: Re: [sqlite] Common Multi-treaded Problem
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Date: Thursday, May 12, 2011, 5:58 PM
>> > Interesting is the impression I
>> had with prepared statements was the reset was only
>> necessary if you wanted to reuse that statement.  Since
>> each each DB connection is in its own instance of a class
>> (with it own set of prepared statements) I would not think
>> there would be any dependency on different physical prepared
>> statements on different threads.  I would expect this with
>> incomplete transactions.
>>
>> There's no dependency between different prepared
>> statements, but there
>> is dependency between transactions as they use the same
>> database. And
>> transaction cannot be finished (implicitly or explicitly)
>> until all
>> statements in this transaction are reset or finalized.
>>
>>
>> Pavel
>>
>>
>> On Thu, May 12, 2011 at 4:01 PM, John Deal <bassd...@yahoo.com>
>> wrote:
>> > Hello Igor,
>> >
>> > That very well maybe it.  I am not at home so can't
>> test for sure but I reset the prepared statements right
>> before I use them so they are left hanging if another thread
>> came in.
>> >
>> > Interesting is the impression I had with prepared
>> statements was the reset was only necessary if you wanted to
>> reuse that statement.  Since each each DB connection is in
>> its own instance of a class (with it own set of prepared
>> statements) I would not think there would be any dependency
>> on different physical prepared statements on different
>> threads.  I would expect this with incomplete
>> transactions.
>> >
>> > Anyway, thanks for the insight.
>> >
>> > John
>> >
>> > --- On Thu, 5/12/11, Igor Tandetnik <itandet...@mvps.org>
>> wrote:
>> >
>> >> From: Igor Tandetnik <itandet...@mvps.org>
>> >> Subject: Re: [sqlite] Common Multi-treaded
>> Problem
>> >> To: sqlite-users@sqlite.org
>> >> Date: Thursday, May 12, 2011, 12:35 PM
>> >> On 5/12/2011 12:31 PM, John Deal
>> >> wrote:
>> >> > When I allow multiple readers with each
>> thread using a
>> >> different DB
>> >> > connection (open with the same flags) and
>> each thread
>> >> having
>> >> > exclusive use of its DB connection (no
>> sharing of
>> >> connections) and if
>> >> > more t

Re: [sqlite] Common Multi-treaded Problem

[Pavel Ivanov]

> Pavel, could you please specify what do you mean by "statements in this 
> transaction"? Statements
> that were prepared (sqlite3_prepare) or initiated (sqlite3_step) during the 
> transaction?

Statements that were initiated during the transaction.

> Also, is this something that one should immediately deduce, necessary 
> behavior based on the model
> of SQLite (or perhaps RDBMS, ACID), or is it something that one normally 
> learns by heart, being,
> for the end-user, just the way SQLite works?

Basic model for any transaction-based DBMS that support any
transaction isolation level stricter than "read uncommitted":
obtaining data for select statement cannot cross transaction boundary,
otherwise data would be inconsistent. But different DBMS can treat
this rule differently. SQLite fails to execute COMMIT if some
statements are active, Oracle cancels execution of all active
statements while executing COMMIT I believe. So it's some specifics
which should be learned by heart if one wants to work with DBMS
seriously.


Pavel


On Thu, May 12, 2011 at 9:53 PM, Mihai Militaru <mihai.milit...@xmpp.ro> wrote:
> If you don't mind, John, for bullying in the discussion...
>
> On Thu, 12 May 2011 17:58:40 -0400
> Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> There's no dependency between different prepared statements, but there
>> is dependency between transactions as they use the same database. And
>> transaction cannot be finished (implicitly or explicitly) until all
>> statements in this transaction are reset or finalized.
>
> Pavel, could you please specify what do you mean by "statements in this 
> transaction"? Statements
> that were prepared (sqlite3_prepare) or initiated (sqlite3_step) during the 
> transaction?
>
> Also, is this something that one should immediately deduce, necessary 
> behavior based on the model
> of SQLite (or perhaps RDBMS, ACID), or is it something that one normally 
> learns by heart, being,
> for the end-user, just the way SQLite works?
>
> Thanks,
> Mihai
>
> --
> Mihai Militaru <mihai.milit...@xmpp.ro>
> ___
> 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] IN clause in search query to search a single field containing comma delimited values

[Pavel Ivanov]

> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";
>
> but I'm guessing LIKE isn't as efficient, and the query is more
> awkward.  Any advise would be appreciated.  Thanks!

This LIKE is the only way to get information from your table. But you
can do it more efficiently by changing your database schema. I'd
suggest to have additional table to store all those categories
separately, one category per row (with columns like id_from_table,
category). Then you'll be able to have a simple and efficient query on
it.


Pavel


On Fri, May 13, 2011 at 3:15 PM, Trevor Borgmeier  wrote:
> I have a database where a field's value is a comma separated list
> indicating the basic categories the item belongs to.
>
> so if the "categories" field has a value of "1,8,15"
>
> I want to do a query like this:
>
> SELECT categories FROM myTable WHERE "8" IN (categories);
>
> but it only finds records where "8" is the only category...
>
> Is there anyway for it to evaluate the contents fo the categories field
> first rather than compare it as a whole?
>
> The describe query works in MySQL, but the port doesn't... So far the
> hack is to do something like this...
>
> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%";
>
> but I'm guessing LIKE isn't as efficient, and the query is more
> awkward.  Any advise would be appreciated.  Thanks!
>
> -Trevor
> ___
> 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] Common Multi-treaded Problem

[Pavel Ivanov]

> If on thread #1 using connection #1 does a step on a prepared select 
> statement and then is blocked before the sqlite3_column() statements and 
> tailing reset statement and thread #2 using connection #2 tries to do a step 
> of a different select prepared statement on the same DB, will the second 
> select step return a database lock error?

No, two select statements never block each other.


Pavel


On Fri, May 13, 2011 at 8:14 PM, John Deal <bassd...@yahoo.com> wrote:
> Hello Pavel,
>
> Many thanks on the extensive information.  I think I understand what you are 
> saying.  I do have a couple of questions.
>
> If on thread #1 using connection #1 does a step on a prepared select 
> statement and then is blocked before the sqlite3_column() statements and 
> tailing reset statement and thread #2 using connection #2 tries to do a step 
> of a different select prepared statement on the same DB, will the second 
> select step return a database lock error?  It just seems with what I 
> understand this is a race condition.  Of course with multi-core, thread #1 
> does not even have to block for this to happen.
>
> If the above is true, then it seems the solution is to use a single DB 
> connection and let the selects serialize.  I choose the separate 
> connection/separate thread model since that was implemented before the 
> separate thread/common connection model assuming the implementation of the 
> former was more established than the later.
>
> Again thanks for the information and I apologize for taking up so much list 
> bandwidth.  I hope others can benefit.
>
> John
>
> --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> From: Pavel Ivanov <paiva...@gmail.com>
>> Subject: Re: [sqlite] Common Multi-treaded Problem
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Date: Thursday, May 12, 2011, 9:53 PM
>> > Humm.  Resetting each prepared
>> statement right after use seemed to work.  So in review, a
>> select prepared statement will lock the DB from other
>> threads (or is it DB connections?) but not the current
>> thread (or is it DB connection).
>>
>> Yes, you are right. Transactions and database locks are
>> per-connection. So if you work with only one connection
>> (even sharing
>> it between different threads) and not resetting your
>> statements it
>> means that all your application works in a one huge
>> transaction,
>> committing everything at the end (I guess if you hard kill
>> your
>> application in the middle you'll see that nothing was
>> committed to the
>> database). And if you work with several different
>> connections (no
>> matter in different threads or in a single thread) they
>> will block
>> each other, i.e. if you execute writing transaction in one
>> connection
>> you won't be able to write in a second connection and
>> sometimes you
>> won't even able to read in a second connection.
>>
>> And answering your question from another email: you can
>> step through
>> any number of prepared statements simultaneously as long as
>> they are
>> all created from the same connection. They won't block each
>> other from
>> executing. You just can't call sqlite3_step() on one
>> connection
>> simultaneously in several threads - they will be
>> serialized. Other
>> than that you are free to step through any number of select
>> statements
>> and execute updates in parallel. But there's one rule of
>> thumb to
>> remember: never change data that should be returned in some
>> active
>> select statement. You can get very surprising behavior in
>> this case.
>>
>>
>> Pavel
>>
>>
>> On Thu, May 12, 2011 at 8:33 PM, John Deal <bassd...@yahoo.com>
>> wrote:
>> > Hello Pavel,
>> >
>> > Humm.  Resetting each prepared statement right after
>> use seemed to work.  So in review, a select prepared
>> statement will lock the DB from other threads (or is it DB
>> connections?) but not the current thread (or is it DB
>> connection).
>> >
>> > Thanks for the help!
>> >
>> > John
>> >
>> > --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com>
>> wrote:
>> >
>> >> From: Pavel Ivanov <paiva...@gmail.com>
>> >> Subject: Re: [sqlite] Common Multi-treaded
>> Problem
>> >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> >> Date: Thursday, May 12, 2011, 5:58 PM
>> >> 

Re: [sqlite] threading and grand central dispatch (OS X/iOS)

[Pavel Ivanov]

> I'm still not 100% sure if there's a problem sharing connections across 
> threads with SQLITE_OPEN_NOMUTEX as long as I guarantee that they aren't 
> concurrent. I suspect there aren't, but I'm not 100% sure. Any case where 
> sqlite3 would be less than happy that pthread_self wasn't always the same?

As long as you guarantee that the same connection is not used
concurrently in different threads SQLite will be happy with any mix of
threads. But apparently your "not 100% sure" is actually "it's not
true" in this case (as your tests showed).


Pavel


On Sun, May 15, 2011 at 4:51 PM, Steven Parkes  wrote:
> Ah, crud. I kinda forgot I had a WAL checkpoint thread running. I thought I 
> had disabled that a long time ago. And it was sharing a connection on a 
> different thread.
>
> Fixing that, things seem stable under GCD with SQLITE_OPEN_NOMUTEX.
>
> I'm still not 100% sure if there's a problem sharing connections across 
> threads with SQLITE_OPEN_NOMUTEX as long as I guarantee that they aren't 
> concurrent. I suspect there aren't, but I'm not 100% sure. Any case where 
> sqlite3 would be less than happy that pthread_self wasn't always the same?
>
> Note that this isn't me redoing what sqlite3 is doing: GCD provides a higher 
> level abstraction that guarantees (modulo my stupidity) non-concurrency. It 
> should be enough for SQLITE_OPEN_NOMUTEX while being (at least slightly) more 
> concurrent than SQLITE_OPEN_FULLMUTEX.
>
> ___
> 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] Query efficiency

[Pavel Ivanov]

> That is, is leaving it to the
> query optimiser to figure out that I only need the sub select once the
> best thing to do?

AFAIK, SQLite's optimizer is not that smart to collapse two identical
sub-queries and reuse once generated result.

> Is the select I'm doing where both a_id1 & 2 are "in" the exact same
> select the most efficient way to do this?

I'd say that the following query will work faster in this particular
case (with this set of tables and indexes):

select ar.*
from a_relation ar, ab ab1, ab ab2
where ar.a_id1 = ab1.a_id
and ab1.b_id = 1
and ar.a_id2 = ab2.a_id
and ab2.b_id = 1;

But this query could be not transformable to your real case. Also
performance in real schema could be different.


Pavel


On Tue, May 17, 2011 at 5:29 AM, Matthew Jones  wrote:
> O.k. So this is a very cut down example but it illustrates the question:
>
> sqlite> create table a (a_id int primary key);
> sqlite> create table b (b_id int primary key);
> sqlite> create table ab (a_id int, b_id int, primary key(a_id, b_id));
> sqlite> create table a_relation (a_id1 int, a_id2, primary key(a_id1,
> a_id2));
> sqlite> select * from a_relation where
>    ...> a_id1 in (select a_id from ab where b_id = 1) and
>    ...> a_id2 in (select a_id from ab where b_id = 1);
>
> Is the select I'm doing where both a_id1 & 2 are "in" the exact same
> select the most efficient way to do this? That is, is leaving it to the
> query optimiser to figure out that I only need the sub select once the
> best thing to do?
>
> (The actual tables in question are a little more complicated and I have
> versions to cope with but this effectively what I'm doing in C++ [so I'm
> preparing and binding etc.]. The actual sub select have a group by a_id
> to cope with multiple entries with different versions.)
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> ___
> 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] SQL Statement Help(selecting days).

[Pavel Ivanov]

> I need help to build a statement in order to select all days free from
> events in a specific time range.

This kind of task should be implemented in your application. SQL
wasn't intended for and can't solve such tasks.


Pavel


On Wed, May 18, 2011 at 4:06 PM, Danilo Cicerone  wrote:
> Hi to all,
> I need help to build a statement in order to select all days free from
> events in a specific time range.
> E.g.:
>
> - Range(-MM-DD) from 2011-01-01 to 2011-02-01.
> - Event 1 from 2011-01-02 to 2011-01-08
> - Event 2 from 2011-01-06 to 2011-01-12
> - Event 3 from 2011-01-18 to 2011-01-21
>
> Resulting Days Free:
> - from 2011-01-01 to 2011-01-01
> - from 2011-01-13 to 2011-01-17
> - from 2011-01-22 to 2011-02-01
>
> Thanks for your help in advance.
> Danilo
> ___
> 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] Query efficiency

[Pavel Ivanov]

> sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab
> where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1
> from ab where ab.a_id2 = 1 and ab.b_id = 1) ;

With your schema this can be transformed the same way:

select aa.*
from aa, ab ab1, ab ab2
where aa.a_id1 = ab1.a_id1
and ab1.a_id2 = 1
and ab1.b_id = 1
and aa.a_id2 = ab2.a_id1
and ab2.a_id2 = 1
and ab2.b_id = 1;


Pavel


On Thu, May 19, 2011 at 1:33 PM, Matthew Jones <matthew.jo...@hp.com> wrote:
> First of all I couldn't for the life of we work out why that new query
> would work but I'm sure that's just a limit of my knowledge. I then
> realised that the database definition I had used was really very
> different from what I was trying to do so I've had another go and then
> tried to use the query with the new schema. Unfortunately, I have failed
> miserably so here's the schema I'm working with now.
>
> Note that table b isn't used by I've created it to show that table ab is
> a list of references of a records to b records and b records contain an a_id
>
> sqlite> create table a (a_id int primary key);
> sqlite> create table b (a_id int, b_id int, primary key(a_id, b_id));
> sqlite> insert into a values(1);
> sqlite> insert into a values(2);
> sqlite> create table aa (a_id1 int, a_id2 int, primary key(a_id1, a_id2));
> sqlite> insert into aa values (1, 1);
> sqlite> insert into aa values (1, 2);
> sqlite> insert into aa values (2, 1);
> sqlite> insert into aa values (3, 1);
> sqlite> insert into aa values (1, 3);
> sqlite> select * from aa;
> 1|1
> 1|2
> 2|1
> 3|1
> 1|3
> sqlite> create table ab (a_id1 int, a_id2 int, b_id int, primary
> key(a_id1, a_id2, b_id));
> sqlite> insert into ab values(1, 1, 1);
> sqlite> insert into ab values(2, 1, 1);
> sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab
> where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1
> from ab where ab.a_id2 = 1 and ab.b_id = 1) ;
> 1|1
> 1|2
> 2|1
> sqlite> select aa.* from aa ar, ab ab1, ab ab2;
> Error: no such table: aa
> sqlite> select aa.* from aa as ar, ab ab1, ab ab2;
> Error: no such table: aa
> sqlite>
>
> At this point I got confused but realised I had asked about a very
> different schema and obviously must be doing something wrong as this
> failed before adding any sort of where clause.
>
>
>> From: Pavel Ivanov<paiva...@gmail.com>
>> Subject: Re: [sqlite] Query efficiency
>>
>>> >  That is, is leaving it to the
>>> >  query optimiser to figure out that I only need the sub select once the
>>> >  best thing to do?
>> AFAIK, SQLite's optimizer is not that smart to collapse two identical
>> sub-queries and reuse once generated result.
>>
>>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>>> >  select the most efficient way to do this?
>> I'd say that the following query will work faster in this particular
>> case (with this set of tables and indexes):
>>
>> select ar.*
>> from a_relation ar, ab ab1, ab ab2
>> where ar.a_id1 = ab1.a_id
>> and ab1.b_id = 1
>> and ar.a_id2 = ab2.a_id
>> and ab2.b_id = 1;
>>
>> But this query could be not transformable to your real case. Also
>> performance in real schema could be different.
>>
>>
>> Pavel
>>
>>
>> On Tue, May 17, 2011 at 5:29 AM, Matthew Jones<matthew.jo...@hp.com>  wrote:
>>> >  O.k. So this is a very cut down example but it illustrates the question:
>>> >
>>> >  sqlite>  create table a (a_id int primary key);
>>> >  sqlite>  create table b (b_id int primary key);
>>> >  sqlite>  create table ab (a_id int, b_id int, primary key(a_id, b_id));
>>> >  sqlite>  create table a_relation (a_id1 int, a_id2, primary key(a_id1,
>>> >  a_id2));
>>> >  sqlite>  select * from a_relation where
>>> >  ? ?...>  a_id1 in (select a_id from ab where b_id = 1) and
>>> >  ? ?...>  a_id2 in (select a_id from ab where b_id = 1);
>>> >
>>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>>> >  select the most efficient way to do this? That is, is leaving it to the
>>> >  query optimiser to figure out that I only need the sub select once the
>>> >  best thing to do?
>>> >
>>> >  (The actual tables in question are a little more complicated and I have
>>> >  versions to cope with but this effectively what I'm doing in C++ [so I'm
>>> >  preparing and binding etc.]. The actual sub select have a group by a_id
>>> >  to cope with multiple entries with different versions.)
>>> >
>>> >  Thanks
>>> >
>>> >  --
>>> >  Matthew Jones
>>> >  Hewlett-Packard Ltd
>>> >
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Pavel Ivanov]

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

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


Pavel


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

Re: [sqlite] Multi-threading Common Problem

[Pavel Ivanov]

> I have all writes in transactions.  If I deactivate my pthread_rwlock() that 
> enforce the above, several writes fail with a "database locked" error (I 
> assume it is returning SQLITE_BUSY).
>
> So how do I implement the equivalent of a pthread_rwlock() using SQLite 
> mechinisms?

When SQLITE_BUSY in a reader transaction is returned just wait a
little bit and try again. Also you can benefit from
sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html).

Another question is why do you want to get read of pthread_rwlock if
it works for you?


Pavel


On Tue, May 24, 2011 at 7:43 AM, John Deal  wrote:
> Hello Roger,
>
> Sorry to be so brain-dead but I am still confused.  I have multiple threads, 
> each with their own DB connection.  I want to allow multiple readers 
> accessing the DB at the same time since nothing is changing.  However, if a 
> writes is to take place, I want all readers to finish their reads and give 
> the writer exclusive access.  Once the writer is done, the readers can come 
> back in.
>
> I have all writes in transactions.  If I deactivate my pthread_rwlock() that 
> enforce the above, several writes fail with a "database locked" error (I 
> assume it is returning SQLITE_BUSY).  With my pthread_rwlock(), I have 
> multiple threads reading the DB and my writes get the exclusive access they 
> need.  Now I could loop on the write until it gets in but that seems very 
> wasteful.
>
> So how do I implement the equivalent of a pthread_rwlock() using SQLite 
> mechinisms?
>
> Thanks,
>
> John
>
> --- On Tue, 5/24/11, Roger Binns  wrote:
>
>> From: Roger Binns 
>> Subject: Re: [sqlite] Multi-threading Common Problem
>> To: "General Discussion of SQLite Database" 
>> Date: Tuesday, May 24, 2011, 3:10 AM
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 05/23/2011 09:12 PM, John Deal wrote:
>> > I guess I am lost on how to obtain a many reader or
>> one writer mutex in SQLite.
>>
>> You are confusing locks on the database and locks in the
>> library on a
>> sqlite3 pointer.  The latter is what the mutex alloc
>> function you reference
>> is about and there there is no reader/writer
>> mechanism.  Access has to be
>> serialized.
>>
>> > You are correct in the locking article referenced I
>> want a mutex that can have the lock states of shared,
>> pending, and exclusive.
>>
>> Those are locks on the database which you get through
>> regular operations and
>> transactions.
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.11 (GNU/Linux)
>>
>> iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk
>> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp
>> =rXCY
>> -END PGP SIGNATURE-
>> ___
>> 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] Multi-threading Common Problem

[Pavel Ivanov]

> I don't want per-say to remove my pthread_rwlock() but the main point of this 
> discussion is I should not have to use pthread_rwlock().

I'd say pthread_rwlock and SQLite-specific mechanisms work completely
differently and you should choose depending on what you want to do. As
you saw to use SQLite's mechanisms you should write additional code
waiting when write lock is released. As you said it's not effective
and prone to starvation. But it works across process boundaries when
pthread_rwlock works only inside your process although it does all
waiting very effectively on a kernel level.
So if you will ever want to connect to your database with sqlite3
command line tool for example while your application is running, and
you will do some manipulations with the database, then your
pthread_rwlock won't work and you will still get SQLITE_BUSY. And now
you decide what mechanism is better for you.


Pavel


On Tue, May 24, 2011 at 10:11 AM, John Deal <bassd...@yahoo.com> wrote:
> Hello Pavel,
>
> I don't want per-say to remove my pthread_rwlock() but the main point of this 
> discussion is I should not have to use pthread_rwlock().  Others have 
> mentioned I should be using SQLite-specific mechanisms to achieve the same 
> results.  I am just trying to understand how to do that.  Pthread_rwlock() 
> works fine.
>
> Thanks,
>
> John
>
> --- On Tue, 5/24/11, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> From: Pavel Ivanov <paiva...@gmail.com>
>> Subject: Re: [sqlite] Multi-threading Common Problem
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Date: Tuesday, May 24, 2011, 9:51 AM
>> > I have all writes in
>> transactions.  If I deactivate my pthread_rwlock() that
>> enforce the above, several writes fail with a "database
>> locked" error (I assume it is returning SQLITE_BUSY).
>> >
>> > So how do I implement the equivalent of a
>> pthread_rwlock() using SQLite mechinisms?
>>
>> When SQLITE_BUSY in a reader transaction is returned just
>> wait a
>> little bit and try again. Also you can benefit from
>> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html).
>>
>> Another question is why do you want to get read of
>> pthread_rwlock if
>> it works for you?
>>
>>
>> Pavel
>>
>>
>> On Tue, May 24, 2011 at 7:43 AM, John Deal <bassd...@yahoo.com>
>> wrote:
>> > Hello Roger,
>> >
>> > Sorry to be so brain-dead but I am still confused.  I
>> have multiple threads, each with their own DB connection.
>>  I want to allow multiple readers accessing the DB at the
>> same time since nothing is changing.  However, if a writes
>> is to take place, I want all readers to finish their reads
>> and give the writer exclusive access.  Once the writer is
>> done, the readers can come back in.
>> >
>> > I have all writes in transactions.  If I deactivate
>> my pthread_rwlock() that enforce the above, several writes
>> fail with a "database locked" error (I assume it is
>> returning SQLITE_BUSY).  With my pthread_rwlock(), I have
>> multiple threads reading the DB and my writes get the
>> exclusive access they need.  Now I could loop on the write
>> until it gets in but that seems very wasteful.
>> >
>> > So how do I implement the equivalent of a
>> pthread_rwlock() using SQLite mechinisms?
>> >
>> > Thanks,
>> >
>> > John
>> >
>> > --- On Tue, 5/24/11, Roger Binns <rog...@rogerbinns.com>
>> wrote:
>> >
>> >> From: Roger Binns <rog...@rogerbinns.com>
>> >> Subject: Re: [sqlite] Multi-threading Common
>> Problem
>> >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> >> Date: Tuesday, May 24, 2011, 3:10 AM
>> >> -BEGIN PGP SIGNED MESSAGE-
>> >> Hash: SHA1
>> >>
>> >> On 05/23/2011 09:12 PM, John Deal wrote:
>> >> > I guess I am lost on how to obtain a many
>> reader or
>> >> one writer mutex in SQLite.
>> >>
>> >> You are confusing locks on the database and locks
>> in the
>> >> library on a
>> >> sqlite3 pointer.  The latter is what the mutex
>> alloc
>> >> function you reference
>> >> is about and there there is no reader/writer
>> >> mechanism.  Access has to be
>> >> serialized.
>> >>
>> >> > You are correct in the locking article
>> referenced I
>> >> want a mutex t

Re: [sqlite] Using sqlite.net with Linq to create database

[Pavel Ivanov]

> If I try to query database existence using Linq's "DatabaseExists" I get
> a NotImplemented exception in the sqlite ChangeDatabase function - which
> doesn't make sense to me.

SQLite doesn't have a notion of server containing several databases.
That's why it makes perfect sense that SQLite doesn't implement
DatabaseExists or ChangeDatabase methods. If you want to check
database existence just check if file exists in the file system. If
you want to use new database just open new connection to it. Also if
you want to create a new database just open connection to it, it will
be created automatically.

Sorry I can't say what syntax for all those things it is in Linq.


Pavel


On Tue, May 24, 2011 at 10:28 AM, Ruth Ivimey-Cook  wrote:
> Folks,
>
> Has anyone successfully used system.data.linq and system.data.sqlite
> together with the linq CreateDatabase function?
>
> When I try, I get an exception "syntax error near "DATABASE"".
>
> If I try to query database existence using Linq's "DatabaseExists" I get
> a NotImplemented exception in the sqlite ChangeDatabase function - which
> doesn't make sense to me.
>
> Regards,
> Ruth
>
> --
> Software Manager&  Engineer
> Tel: 01223 414180
> Blog: http://www.ivimey.org/blog
> LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/
>
>
> ___
> 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] Using sqlite.net with Linq to create database

[Pavel Ivanov]

> Surely, the fact that SQLite doesn't permit one file having several
> databases should just mean that "CREATE DATABASE ;" is a really
> simple operation, not that it throws an exception and/or fails.

There's no such command in SQLite. So if Linq can't live without
issuing this command then it can't be used with SQLite.


Pavel


On Tue, May 24, 2011 at 11:05 AM, Ruth Ivimey-Cook <r...@ivimey.org> wrote:
> Pavel Ivanov wrote:
>>> If I try to query database existence using Linq's "DatabaseExists" I get
>>> a NotImplemented exception in the sqlite ChangeDatabase function - which
>>> doesn't make sense to me.
>> SQLite doesn't have a notion of server containing several databases.
>> That's why it makes perfect sense that SQLite doesn't implement
>> DatabaseExists or ChangeDatabase methods. If you want to check
>> database existence just check if file exists in the file system. If
>> you want to use new database just open new connection to it. Also if
>> you want to create a new database just open connection to it, it will
>> be created automatically.
> I wanted to distinguish between a file being present and that file
> actually being parsable as an sqlite database.
>
> I also wanted Linq to create the tables required in the file... i.e.
> moving from "no file" to s3db with tables".
>
> In Linq  the CreateDatabase call both creates the database (i.e. in
> sqlite terms, make an s3db file) and populates that database with
> tables, according to the metadata and class information provided in the
> code.
>
> Surely, the fact that SQLite doesn't permit one file having several
> databases should just mean that "CREATE DATABASE ;" is a really
> simple operation, not that it throws an exception and/or fails.
>
> If the s3db file format includes a database name (distinct from the name
> of the file) then that name can be used for  - otherwise, the 
> should be ignored IMO.
>
> Regards
> Ruth
>
> --
> Software Manager&  Engineer
> Tel: 01223 414180
> Blog: http://www.ivimey.org/blog
> LinkedIn: http://uk.linkedin.com/in/ruthivimeycook/
>
>
> ___
> 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] Incorrect left join behavior?

[Pavel Ivanov]

> I can use two left joins.  While writing the first left join, I discovered
> that it is behaving like an inner join.
>
> select *
> from test a
>   left join test b on a.component = b.component
> where a.machine = 'machine1'
>   and b.machine = 'machine2';

By the WHERE condition you limit results of your left join only to
those that have b.machine equal to 'machine2' and exclude other 2 rows
where b.machine is null. To get the result you need you have to
rewrite the query in one of the following ways:

select *
from test a left join test b
on a.component = b.component
and b.machine = 'machine2'
where a.machine = 'machine1';

or

select *
from test a left join test b
on a.component = b.component
where a.machine = 'machine1'
and (b.machine is null or b.machine = 'machine2');


I think the first syntax is preferable.


Pavel


On Wed, May 25, 2011 at 9:09 AM, Michael Stephenson
 wrote:
> Hi, I have a table as below.  Note that machine1 has 3 components (1-3),
> while machine2 has only 1 components (1).
>
>
>
>
> Machine
>
> Component
>
> Version
>
>
> machine1
>
> component1
>
> 1
>
>
> machine1
>
> component2
>
> 1
>
>
> machine1
>
> component3
>
> 1
>
>
> machine2
>
> component1
>
> 1
>
>
>
> create table test(Machine, Component, Version);
>
> insert into test values('machine1', 'component1', 1);
>
> insert into test values('machine1', 'component2', 1);
>
> insert into test values('machine1', 'component3', 1);
>
> insert into test values('machine2', 'component1', 1);
>
>
>
> I need to see what component versions are different on the machines.  What I
> really need is a "self outer join", but since SQLite doesn't do outer joins,
> I can use two left joins.  While writing the first left join, I discovered
> that it is behaving like an inner join.
>
>
>
> select *
>
> from test a
>
>   left join test b on a.component = b.component
>
> where a.machine = 'machine1'
>
>   and b.machine = 'machine2';
>
>
>
>
>
> The expected behavior would be to return 3 rows: one row showing the
> component versions for machine1 and machine2, and two rows showing the
> component version for machine1 with null for machine2.
>
>
>
> The actual behavior is that of an inner join:  I get only one row that shows
> the component versions for both machines, and the other two components,
> which don't exist for machine2, do not appear in the results at all.
>
>
>
> I'm aware that SQLite only does left joins properly using SQL92 syntax,
> which I am using.  I'm also aware that I'm using a where clause, and the
> docs state that join constraints in a where clause cause a left join to
> behave as an inner join.  However, my where clause does not constrain the
> join expression, just the initial rows involved available to be joined.
>
>
>
> I'm able to work around this by:
>
>
>
> select *
>
> from (select * from test where machine = 'machine1') a
>
>   (select * from test where machine = 'machine2') b
>
> on a.component = b.component;
>
>
>
> But, I just wanted to point this behavior out.  Perhaps I've missed
> something.  It looks like any time there is a where clause, a left join is
> going to behave as an inner join.
>
>
>
> Thanks,
>
>
>
> ~Mike
>
>
>
>
>
> ___
> 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 in Xcode

[Pavel Ivanov]

> 2.)     I moved SQLite3.c and SQLite3.h into my source folder and added them 
> to the project.  I made no changes to the code nor did I do anything special 
> when I added them to my project (i.e. I did not set any special compile 
> flags-I simply added the two files to the project).
> 3.)     I compiled and received 1200+ errors.

Try to change settings for sqlite3.c so that it is compiled as C not
as C++ (I don't know exactly where are those settings in XCode, I
believe you should know that).


Pavel


On Thu, May 26, 2011 at 10:49 AM, john darnell
 wrote:
> Hello everyone.
>
> I am still trying to get SQLite to work on my Mac.  I want to make sure I am 
> doing what I should be doing.  Here are the steps I have taken:
>
> 1.)      I downloaded and uncompressed sqlite-autoconf-3070602.tar.gz from 
> the SQLite download page.
> 2.)     I moved SQLite3.c and SQLite3.h into my source folder and added them 
> to the project.  I made no changes to the code nor did I do anything special 
> when I added them to my project (i.e. I did not set any special compile 
> flags-I simply added the two files to the project).
> 3.)     I compiled and received 1200+ errors.
>
> Since then I have tried using the sqlite.dylib file that comes with OSX but 
> in doing so, some important SQLite functions (such as the prepare function) 
> were not found during the link process.
>
> My most recent attempt has been to follow the instructions that come with the 
> tarball I downloaded to create my own SQLite library.  When I run 
> ./configure, the procedure errors out, saying that it could not find an 
> acceptable C compiler in the $Path.
>
> Anyone have any new possibilities to try?
>
> BTW, (and I apologize for not sharing this sooner-it didn't occur to me that 
> it might be relevant until recently)  I am using SQLite3 in concert with the 
> InDesign SDK to produce an InDesign plugin (which we freely distribute to our 
> customers).  There is a possibility that the SDK has something going on that 
> causes the massive explosion of errors.
>
> And finally, the very first time I used SQLite3 on my Windows box, upon 
> compilation, it generated a whopping 200 errors, and continued to do so until 
> I turned off "using precompiled headers."  I cannot see a way to do this in 
> Xcode.  Perhaps that is my problem?
>
> R,
> John A.M. Darnell
> Senior Programmer
> Walsworth Publishing Company
> Brookfield, MO
> John may also be reached at 
> johnamdarn...@gmail.com
>
> Trivia question Trivia question:  In The Lord of the Rings,Leglolas was a 
> prince among the Silvan Elves.  What was the name of his father the King?  
> For extra credit, what was his surname?
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in Xcode

[Pavel Ivanov]

> Here are two errors out of the 1200+ errors generated (I tried to include as 
> much information as possible to help identify the problem.  Please forgive if 
> I am overdoing it...):
>
>        Compiling /.../sqlite3.c
>        Error:invalid conversion from "void *" to "char *"
>        This error was marked at line 13726.

IIRC, this error is the most famous difference between C and C++. And
if this error appears then you are compiling it as C++, not as C.


Pavel


On Thu, May 26, 2011 at 12:35 PM, john darnell
 wrote:
> Mr. Slavin, here's the information you requested:
>
> Please note that I am working in Xcode 3.1.3.  That may or may not be 
> part/all of the problem.
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org]
>> On Behalf Of Simon Slavin
>> Sent: Thursday, May 26, 2011 10:01 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite in Xcode
>>
>> > 1.)      I downloaded and uncompressed sqlite-autoconf-3070602.tar.gz from 
>> > the
>> SQLite download page.
>>
>> The top one, with just .c and .h is adequate.  You shouldn't end up using 
>> any of
>> the scripts or makefiles for building.
>
> I can confirm that I only moved/added sqlite3.c and sqlite3.h into the source 
> folder and into the project.
>
>>
>> > 2.)     I moved SQLite3.c and SQLite3.h into my source folder and added 
>> > them to
>> the project.
>>
>> > I made no changes to the code nor did I do anything special when I added 
>> > them
>> to my project (i.e. I did not set any special compile flags-I simply added 
>> the two
>> files to the project).
>>
>> Make sure sqlite3.c is 'ticked' next to 'Target Membership' for your product 
>> file.
>
> I am not someone who uses Xcode frequently--I usually develop on Windows and 
> port t the Mac, but if by "Target Membership" you mean the last column in the 
> pane that lists all files when the project name is selected in the Groups & 
> Files pane, headed by an icon that looks like a target, the sqlite3.c file is 
> 'ticked.'
>
>>
>> Also make sure sqlite3.c is listed as 'C source' and sqlite3.h is listed as 
>> 'C header'
>> under 'Identity and Type'.
>
> I clicked the sqlite3.c filename in the Groups & Files panel, clicked the 
> information icon (the blue button with the white 'i' at the top of the 
> window).  Under the General tab, under the File Type dropdown, the file is 
> listed as 'sourcecode.c.c', and the sqlite3.h file is listed as 
> 'sourcecode.c.h.'  If you have in mind some other location, please let me 
> know.
>
>>
>> > 3.)     I compiled and received 1200+ errors.
>>
>> Try the above, and if those don't go away, post the first one or two (note 
>> all
>> 1200+) here.
>>
>
> Here are two errors out of the 1200+ errors generated (I tried to include as 
> much information as possible to help identify the problem.  Please forgive if 
> I am overdoing it...):
>
>        Compiling /.../sqlite3.c
>        Error:invalid conversion from "void *" to "char *"
>        This error was marked at line 13726.
>
>        Error:forward declaration of 'struct SrcList_item'
>        This error was marked at line 11047.
>
> There were six warnings as well, most of which were warnings about division 
> by zero.
>
> R,
> John
>
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Pavel Ivanov]

>  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not
> update the same row in a table more than once?

You can update it as many times as you need.

> What are the exact limitations on what I can do during a Transaction?

Do not update a table if there is some select statement currently
active and using it. Active means you made at least one call to
sqlite3_step and last call to sqlite3_step returned SQLITE_ROW.


Pavel


On Wed, Jun 1, 2011 at 10:00 AM, Ian Hardingham  wrote:
> Hey guys, thanks for all the help so far today.
>
>  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not
> update the same row in a table more than once?  What are the exact
> limitations on what I can do during a Transaction?
>
> Thanks,
> Ian
> ___
> 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] TRANSACTIONs

[Pavel Ivanov]

> Actually, you do know what SQLite does without knowing the internals. It
> claims to be serializable and ACID
> (http://www.sqlite.org/transactional.html), therefore it's fine.

"Serializable" there means that once transaction is started statements
won't see any data committed in other transactions. But it doesn't
impose any restrictions on how statements should behave within the
same transaction. And in SQLite it's pretty unpredictable and so even
SQLite's documentation prohibits updating some table while select
statement on it is active (I can find a link on sqlite.org for you
when I have some spare time).


Pavel


On Wed, Jun 1, 2011 at 2:41 PM, Robert Myers <rob.my...@ziften.com> wrote:
> On 6/1/2011 1:23 PM, Simon Slavin wrote:
>> On 1 Jun 2011, at 7:12pm, Jan Hudec wrote:
>>
>>> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote:
>>>>>  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not
>>>>> update the same row in a table more than once?
>>>> You can update it as many times as you need.
>>>>
>>>>>  What are the exact limitations on what I can do during a Transaction?
>>>> Do not update a table if there is some select statement currently
>>> Actually insert, update and delete are OK. Drop and alter table are
>>> a problem.
>> Pavel is right.  He left out some details to make things simple.
>>
>> Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve 
>> three rows, then make a change that would mean you should have retrieved 
>> eleven rows, not ten.  You can't predict what SQLite will do without knowing 
>> the internal workings of SQLite, right ?  So don't do that.  The same is 
>> true even if the only thing you change is values to be returned.  Does 
>> SQLite copy the all values when you execute the SELECT, or row-by-row as you 
>> step through the results ?  Again, you don't know unless you know the 
>> internal workings of SQLite.  So don't do that.
>>
>> Simon.
>> ___
>
> Actually, you do know what SQLite does without knowing the internals. It
> claims to be serializable and ACID
> (http://www.sqlite.org/transactional.html), therefore it's fine.
> ___
> 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] TRANSACTIONs

[Pavel Ivanov]

> What about the I of ACID? The select should have an implicit transaction
> around it.

No, it shouldn't. It can be a part of some other transaction. I in
ACID means Isolation of transactions, not Isolation of select
statements. Otherwise there would be no way for select statement to
read uncommitted data in the same transaction.


Pavel


On Wed, Jun 1, 2011 at 2:57 PM, Robert Myers <rob.my...@ziften.com> wrote:
> On 6/1/2011 1:47 PM, Pavel Ivanov wrote:
>>> Actually, you do know what SQLite does without knowing the internals. It
>>> claims to be serializable and ACID
>>> (http://www.sqlite.org/transactional.html), therefore it's fine.
>> "Serializable" there means that once transaction is started statements
>> won't see any data committed in other transactions. But it doesn't
>> impose any restrictions on how statements should behave within the
>> same transaction. And in SQLite it's pretty unpredictable and so even
>> SQLite's documentation prohibits updating some table while select
>> statement on it is active (I can find a link on sqlite.org for you
>> when I have some spare time).
> What about the I of ACID? The select should have an implicit transaction
> around 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] Trigger help needed

[Pavel Ivanov]

> CREATE TRIGGER ts_update after update on ip_timestamps when

This means that your trigger fires after each UPDATE statement. But

> I'm adding/updating records with statements like:
>
> INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 );

you do INSERT statements, so your trigger is never executed. Don't be
confused: REPLACE means that if needed the old row is deleted and new
is inserted. Row is never updated and UPDATE triggers are never
executed (I'm not sure about DELETE and INSERT triggers though).


Pavel


On Wed, Jun 1, 2011 at 3:31 PM, Jim Mellander  wrote:
> Hopefully someone can help me with this
>
> I have a table with IP addresses and timestamps - I want to update the
> table when the new timestamp is later than the old one
>
>
> $ sqlite3 test.db
> SQLite version 3.7.4
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .schema
> CREATE TABLE ip_timestamps (ip text unique, timestamp date);
> CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC);
> CREATE TRIGGER ts_update after update on ip_timestamps when
> NEW.timestamp < OLD.timestamp BEGIN update ip_timestamps set timestamp
> = OLD.timestamp; END;
>
>
> I'm adding/updating records with statements like:
>
> INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 );
>
> The goal is to keep the latest timestamp in the database (the older
> timestamp could occur later in the input than the newer timestamp),
> but the trigger doesn't seem to be working - I assume the trigger is
> flawed.  Any suggestions?
>
>
> Thanks in advance.
> ___
> 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 reports

[Pavel Ivanov]

> I did try sending a mail over the dev-list, but got no reply. Could
> someone tell me how it should be done?

You should send it here with the exact steps of reproducing the bug.

Hopefully your tool is not code analyzer because "potential bugs"
without any real life steps to catch it won't be paid attention to.


Pavel


On Thu, Jun 2, 2011 at 9:40 AM, Ronald Burgman
 wrote:
> Hi everybody,
>
> I'm currently using a tool to find bugs in the SQLite source code. I've
> found some interesting things and want to submit some bug reports so the
> developers can also have a look at it.
>
> However I'm new to SQLite so I'm not sure where and how to submit these
> reports. I did try sending a mail over the dev-list, but got no reply. Could
> someone tell me how it should be done?
>
> Cheers,
> Ronald
> ___
> 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] HELP : how to use datetime('column', 'localtime') as a part of sql string

[Pavel Ivanov]

> but is returning the value stored in the database when executed
> as a part of a java program. Can  anyone help me if I am missing
> something here ?

Looks like your java program runs with a different locale than SQLite
Manager. I'm not sure why it thinks that you are in a UTC timezone.
Maybe there's something in starting scripts or in initialization code
of this program?


Pavel


On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu
 wrote:
> Hi
>
> I am preparing a string sql query as follows
>
>     String jobQuery = "SELECT j.jobId, c.channelName ,
> datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus,
> CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN
> 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS
>       jobQuality, j.jobCompleteDate ";
>         jobQuery += "FROM Job j ";
>         jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId ";
>
> this query gives me jobCreateDate in my localtime when run in sqlite
> manager, but is returning the value stored in the database when executed
> as a part of a java program. Can  anyone help me if I am missing
> something here ?
>
> Sridhar
> ___
> 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] HELP : how to use datetime('column', 'localtime') as a part of sql string

[Pavel Ivanov]

> I have verified my locale and default time zone in the same program.The
> values are correct and it is not UTC timezone. Any help appreciated.

How did you do that? What OS are you running it on?
Can you extract the code accessing SQLite database from your program
and create a mini-program that does nothing else except executes this
query? Run this mini-program, see what it returns. Then from the same
shell prompt run sqlite3 command line utility and execute this query
there, see if results are different. If they are show us full text of
java mini-program, we will try to find the problem.


Pavel


On Thu, Jun 2, 2011 at 10:57 AM, Sridhar Polavarapu
<sridhar.polavar...@idc-rimage.com> wrote:
> I have verified my locale and default time zone in the same program.The
> values are correct and it is not UTC timezone. Any help appreciated.
>
> Sridhar
>
> On 02-06-2011 19:34, Sridhar Polavarapu wrote:
>>
>> The returned value is displayed to the user. We are not storing back the
>> returned column.
>>
>> Sridhar
>>
>> On 02-06-2011 19:28, Pavel Ivanov wrote:
>>>>
>>>> but is returning the value stored in the database when executed
>>>> as a part of a java program. Can  anyone help me if I am missing
>>>> something here ?
>>>
>>> Looks like your java program runs with a different locale than SQLite
>>> Manager. I'm not sure why it thinks that you are in a UTC timezone.
>>> Maybe there's something in starting scripts or in initialization code
>>> of this program?
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu
>>> <sridhar.polavar...@idc-rimage.com>  wrote:
>>>>
>>>> Hi
>>>>
>>>> I am preparing a string sql query as follows
>>>>
>>>>     String jobQuery = "SELECT j.jobId, c.channelName ,
>>>> datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus,
>>>> CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN
>>>> 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS
>>>>       jobQuality, j.jobCompleteDate ";
>>>>         jobQuery += "FROM Job j ";
>>>>         jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId ";
>>>>
>>>> this query gives me jobCreateDate in my localtime when run in sqlite
>>>> manager, but is returning the value stored in the database when executed
>>>> as a part of a java program. Can  anyone help me if I am missing
>>>> something here ?
>>>>
>>>> Sridhar
>>>> ___
>>>> 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] HELP : how to use datetime('column', 'localtime') as a part of sql string

[Pavel Ivanov]

AutomatedTests>cd
> \Users\sridhar\Desktop
>
> C:\Users\sridhar\Desktop>sqlite3.exe C:\Signal\Publisher\publisher.db
> SQLite version 3.7.6.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT j.jobId, c.channelName , datetime(j.jobCreateDate,
> 'localtime'), j.jobStatus, j.jobQuality, j.jobCompleteDate FROM Job j,
> Channel c where c
> elId = j.jobChannelId ORDER BY jobId DESC;
> 762|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:53|COMPLETE|md|2011-06-02 13:26:14
> 761|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:52|COMPLETE|md|2011-06-02 13:26:07
> 760|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:52|COMPLETE|md|2011-06-02 13:25:59
> 759|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:51|COMPLETE|md|2011-06-02 13:25:41
> 758|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:50|COMPLETE|md|2011-06-02 13:25:36
> 757|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:50|COMPLETE|md|2011-06-02 13:25:30
> 756|automatedTest_13067437059065180049472173|2011-06-02
> 18:54:49|COMPLETE|md|2011-06-02 13:25:23
> 755|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:09|COMPLETE|md|2011-06-02 13:17:30
> 754|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:08|COMPLETE|md|2011-06-02 13:17:25
> 753|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:07|COMPLETE|md|2011-06-02 13:17:12
> 752|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:07|COMPLETE|md|2011-06-02 13:16:51
> 751|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:06|COMPLETE|md|2011-06-02 13:16:48
> 750|automatedTest_130675855965820033590511079|2011-06-02
> 18:46:05|COMPLETE|md|2011-06-02 13:19:17
> 749|automatedTest_1307018964144181250169479584|2011-06-02
> 18:19:30|COMPLETE|hi|2011-06-02 12:57:58
> 748|NaveenTest|2011-06-02 18:19:29|COMPLETE|hi|2011-06-02 12:56:59
> 747|automatedTest_1307018192020180478065994419|2011-06-02
> 18:06:39|COMPLETE|hi|2011-06-02 12:56:30
> 746|NaveenTest|2011-06-02 18:06:37|COMPLETE|hi|2011-06-02 12:55:33
> 745|automatedTest_130675855965720033590323324|2011-06-02
> 17:22:21|COMPLETE|md|2011-06-02 11:52:54
> 744|automatedTest_1306482594629320685646725968||COMPLETE|md|2011-06-02
> 10:18:47
> 743|automatedTest_130675534731016821286104758|2011-06-02 20:45:25|INIT|md|
> 742|automatedTest_130675534731016821286104758|2011-06-02 20:38:15|INIT|md|
> 741|automatedTest_130675534731016821286104758|2011-06-02
> 15:04:06|COMPLETE|md|2011-06-02 09:34:46
> 740|automatedTest_130675855965820033590602240||COMPLETE|md|2011-06-02
> 09:33:59
> 739|automatedTest_130675855965820033590602240|2011-06-02 20:26:11|INIT|md|
> 738|automatedTest_130675855965820033590602240|2011-06-02 14:55:39|INIT|md|
> 737|NaveenTest|2011-06-02 19:24:34|INIT|md|
> 736|NaveenTest|2011-06-02 13:54:02|COMPLETE|md|2011-06-02 08:25:51
> 735|SridharTest|2011-06-02 18:53:24|INIT|md|
> 734|SridharTest|2011-06-02 13:21:44|COMPLETE|md|2011-06-02 07:56:07
> 733|automatedTest_1306481519407319610451279968|2011-06-02 18:41:25|INIT|md|
> 732|automatedTest_1306481519407319610451279968|2011-06-02
> 13:10:45|COMPLETE|md|2011-06-02 07:45:47
> 731|automatedTest_130675534731016821286048311|2011-06-02
> 12:55:25|COMPLETE|md|2011-06-02 07:28:14
> 730|automatedTest_130675534731016821286048311|2011-06-02 18:24:59|INIT|md|
> 729|automatedTest_13067429149993217266307076|2011-06-02
> 12:42:25|COMPLETE|md|2011-06-02 07:16:24
> 728|SridharTest|2011-06-02 12:31:59|COMPLETE|md|2011-06-02 07:02:25
> 727|automatedTest_1306496245310334336328084528|2011-06-02
> 12:24:48|COMPLETE|md|2011-06-02 06:55:29
> 726|automatedTest_130648151940731961056560|2011-06-01
> 12:30:33|COMPLETE|md|2011-06-01 07:01:04
> 725|automatedTest_130651279595727994816196128|2011-05-31
> 19:26:47|COMPLETE|md|2011-05-31 13:57:44
> 724|automatedTest_130651279595727994816196128|2011-05-31
> 19:26:47|COMPLETE|md|2011-05-31 13:57:24
> 723|automatedTest_130651279595727994816196128|2011-05-31
> 19:26:46|COMPLETE|md|2011-05-31 13:57:20
> 722|automatedTest_130651279595727994816196128|2011-06-02
> 17:52:02|COMPLETE|md|2011-05-31 13:57:15
>
> On 02-06-2011 20:33, Pavel Ivanov wrote:
>>>
>>> I have verified my locale and default time zone in the same program.The
>>> values are correct and it is not UTC timezone. Any help appreciated.
>>
>> How did you do that? What OS are you running it on?
>> Can you extract the code accessing SQLite database from your program
>> and create a mini-program that does nothing else except executes this
>> query? Run this mini-program, see what it returns. Then from the same
>> shell prompt run sqlite3 c

Re: [sqlite] Slow rollback - possible causes?

[Pavel Ivanov]

> DB file in WAL mode, checkpointing done every 5 seconds by separate
> thread in program

Depending on the mode of checkpointing you use it can fail if there
are some other reading or writing transactions in progress. And at the
time you observe very long rollback actual checkpointing happens
because no other transactions are active. Did you monitor the size of
WAL file?


Pavel


On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler  wrote:
> Hello!
>
> Does anyone know of a reason why we might be seeing SQLite transaction
> rollbacks that take between 60 and 240 seconds?  (One particularly odd
> occurrence was almost 20 minutes long!)  This doesn't seem to happen
> often, but when it does it's painful.  During the rollback, the disk
> is definitely seeing a large amount of IO activity.
>
> The transactions being rolled back don't appear to be specific to any
> one table (some of the tables have ~200k rows, one table has ~17M
> rows), similarly we've seen transactions rolled back for different
> UPDATE and INSERT operations.  (Overall, the workload is for a
> high-ish traffic web application.  Lots of reads, far fewer writes).
>
> DB file in WAL mode, checkpointing done every 5 seconds by separate
> thread in program
> SQLite version: 3.7.2
> DB filesize: approximately 15GB
> Transaction size: sometimes a few KB, up to ~2MB
> OS: Ubuntu Linux 10.04
>
> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
> going to it (we'll be separating more of the workload out soon).
>
> Other pragmas that may or may not be relevant:
> count_changes = OFF
> synchronous = OFF
> temp_store = MEMORY
> wal_autocheckpoint = 0
> cache_size = 300
>
> Any thoughts or ideas?
>
> -Eric
> ___
> 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] Slow rollback - possible causes?

[Pavel Ivanov]

> (Actually, that was another general question we had, should that WAL
> file ever shrink during use?  Why would it grow to that size at all?)

It shrinks, when the full checkpoint is completed successfully. Until
then it grows.


Pavel


On Tue, Jun 7, 2011 at 10:03 PM, Eric Sigler <esig...@expensify.com> wrote:
> We haven't watched the WAL continuously, but we have noticed that the
> WAL file grows slowly in size over time between application restarts
> (around every 2 weeks).  Currently, the WAL file for one of our DBs is
> around 40MB, we've seen it grow up to 130MB or so.  I'll try to catch
> the WAL size and see if it changes dramatically.
>
> (Actually, that was another general question we had, should that WAL
> file ever shrink during use?  Why would it grow to that size at all?)
>
> -Eric
>
> On Tue, Jun 7, 2011 at 6:44 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>>> thread in program
>>
>> Depending on the mode of checkpointing you use it can fail if there
>> are some other reading or writing transactions in progress. And at the
>> time you observe very long rollback actual checkpointing happens
>> because no other transactions are active. Did you monitor the size of
>> WAL file?
>>
>>
>> Pavel
>>
>>
>> On Tue, Jun 7, 2011 at 9:02 PM, Eric Sigler <esig...@expensify.com> wrote:
>>> Hello!
>>>
>>> Does anyone know of a reason why we might be seeing SQLite transaction
>>> rollbacks that take between 60 and 240 seconds?  (One particularly odd
>>> occurrence was almost 20 minutes long!)  This doesn't seem to happen
>>> often, but when it does it's painful.  During the rollback, the disk
>>> is definitely seeing a large amount of IO activity.
>>>
>>> The transactions being rolled back don't appear to be specific to any
>>> one table (some of the tables have ~200k rows, one table has ~17M
>>> rows), similarly we've seen transactions rolled back for different
>>> UPDATE and INSERT operations.  (Overall, the workload is for a
>>> high-ish traffic web application.  Lots of reads, far fewer writes).
>>>
>>> DB file in WAL mode, checkpointing done every 5 seconds by separate
>>> thread in program
>>> SQLite version: 3.7.2
>>> DB filesize: approximately 15GB
>>> Transaction size: sometimes a few KB, up to ~2MB
>>> OS: Ubuntu Linux 10.04
>>>
>>> Hardware-wise, the SQLite instance is running in a VM with 4GB of RAM,
>>> 2 virtual CPUs (early 2010 Xeons), IO for this VM runs on a single
>>> 750GB SATA disk (Barracuda ES.2) with minimal to moderate other IO
>>> going to it (we'll be separating more of the workload out soon).
>>>
>>> Other pragmas that may or may not be relevant:
>>> count_changes = OFF
>>> synchronous = OFF
>>> temp_store = MEMORY
>>> wal_autocheckpoint = 0
>>> cache_size = 300
>>>
>>> Any thoughts or ideas?
>>>
>>> -Eric
>>> ___
>>> 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] Slow rollback - possible causes?

[Pavel Ivanov]

> We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.

If you want guaranteed finish of the checkpoint (and thus not growing
WAL-file) you need to issue "PRAGMA wal_checkpoint(RESTART)".


Pavel


On Tue, Jun 7, 2011 at 10:36 PM, Eric Sigler <esig...@expensify.com> wrote:
> So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA
> wal_checkpoint" to the open DB handle.
>
> -Eric
>
> On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> (Actually, that was another general question we had, should that WAL
>>> file ever shrink during use?  Why would it grow to that size at all?)
>>
>> It shrinks, when the full checkpoint is completed successfully. Until
>> then it grows.
>>
>>
>> Pavel
> ___
> 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] Cannot load SQLite.Interop.dll but file is in the folder

[Pavel Ivanov]

> Thus, could you please let me know what framework has been used to
> compile the "Precompiled Binaries For Windows" on the download page?
> Should I re-compile SQLite with the same framework than the one used for
> my application?

Sorry, I can't answer these questions. I'm not on SQLite development
team and don't know what framework it was compiled for. And I'm no
expert in .NET development, so I can't give any advice on how you
should re-compile the SQLite to be usable on .NET 4.0. But quick
googling revealed this page
http://msdn.microsoft.com/en-us/library/bb397456.aspx. Maybe digging
into it may help you or somebody more experienced with .NET on this
list will give you some advice.


Pavel


On Wed, Jun 8, 2011 at 5:03 AM, Cyrille <rssxpr...@free.fr> wrote:
> Dear Pavel,
>
> Finally, it seems that you were right: the person confirmed me that the
> software was not running with a PC with the framework 4.0. It works fine
> with another PC with framework 3.5.
>
> My application is compiled with the framework 3.0.
> Thus, could you please let me know what framework has been used to
> compile the "Precompiled Binaries For Windows" on the download page?
> Should I re-compile SQLite with the same framework than the one used for
> my application?
>
> Thanks for your support
> Cyrille
>
>
> Le 31/05/2011 15:57, Pavel Ivanov a écrit :
>> I would ask one more question: do they have the same Windows and .NET
>> Framework version as you have? AFAIK, Windows and .NET specifically
>> are very picky on all dlls versions. So if for example you have
>> SQLite.Interop.dll compiled with a newer .NET Framework and manifest
>> in it requires your version then it won't load in older .NET
>> Framework. I believe without extra steps during compilation the
>> opposite is also true - if you compile on older .NET Framework it
>> won't load in newer one.
>> But don't ask me how to make it accept different versions of Framework
>> and other libraries. I'm no expert in that, just had some chance in
>> the past to struggle with such problems.
>>
>>
>> Pavel
>>
>>
>> On Tue, May 31, 2011 at 8:54 AM, Black, Michael (IS)
>> <michael.bla...@ngc.com>  wrote:
>>> Unfortunately you're also hitting a rather lousy error message which 
>>> doesn't tell you "file not found" or "permission denied" or such..for which 
>>> I always deduct points for my students.
>>>
>>>
>>>
>>> So...try this..
>>>
>>> http://www.softpedia.com/get/Programming/Debuggers-Decompilers-Dissasemblers/Strace%20NT.shtml
>>>
>>> That will hopefully show you what's happening.
>>>
>>>
>>>
>>> I suspect that there could be something funky in the file name.
>>>
>>> You can have them try to rename it and see if that works if they just 
>>> retype the whole filename.
>>>
>>>
>>>
>>> Also...is there any path mungling going on for 8-char path names?
>>> Try putting the app in a simpler directory name structure that doesn't 
>>> exceed 8 chars for each dir name.
>>>
>>>
>>>
>>> And what happens if they execute from a command prompt?
>>>
>>>
>>>
>>> And what's the "working directory" of the menu entry?
>>>
>>>
>>>
>>>
>>>
>>> Michael D. Black
>>>
>>> Senior Scientist
>>>
>>> NG Information Systems
>>>
>>> Advanced Analytics Directorate
>>>
>>>
>>>
>>> 
>>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
>>> behalf of Cyrille [rssxpr...@free.fr]
>>> Sent: Monday, May 30, 2011 12:19 PM
>>> To: General Discussion of SQLite Database
>>> Subject: EXT :Re: [sqlite] Cannot load SQLite.Interop.dll but file is in 
>>> the folder
>>>
>>> Dear Michael,
>>>
>>> Thank you very much for your link. What is strange is that, whatever the
>>> SafeDllSearchMode is enable or not, the first location searched is the
>>> application one. ANd, as mentioned in my first message, the DLL is in
>>> the same folder as the EXE file.
>>> I do not know if there is a link but the user who experiences this
>>> problem is running my application under WIndows 7 32bit. Is there any
>>> possible link?
>>>
>>> Thanks again
>>> Cyrille
>>>
>>>
>>>
>>> Le 29/05/2011 20:33, Black, Michael (IS) a écrit :

Re: [sqlite] Sqlite DATE() function. Add a variable amount of months.

[Pavel Ivanov]

> The main issue for me here is that the +1 is inside the string '+1 month'
> and i thus cant use a subquerry of the type SELECT date('now','+(SELECT id
> FROM table) month');

You can do like this:

SELECT date('now','+' || (SELECT id FROM table) || ' month');


Pavel


On Wed, Jun 8, 2011 at 1:42 PM, datepb  wrote:
>
> Hello,
>
> This may seem a very simple issue it is giving me some very hard time.
> I need to add a variable amount of months to a date.
>
> According to the doc, this is done for adding 1 month :
> SELECT date('now','+1 month');
>
>
> And I would need something like SELECT date('now','+$i month');
>
>
>
>
> This SELECT is inside a trigger that is created say at the beginning of the
> day and then the value of $i changes during the day depending on feed in the
> different tables, so i need to do it in SQL (I cant call the piece of code
> conatining the trigger later during the day; so for exemple a bash(perl,
> whatever...) script calling the SQL code with update value of $i is not good
> either)
>
>
>
> Now for trying to do it in sql:
>
> The main issue for me here is that the +1 is inside the string '+1 month'
> and i thus cant use a subquerry of the type SELECT date('now','+(SELECT id
> FROM table) month');
>
> I cant either build a string and then do SELECT date('now',SELECT mystring
> FROM table2);
>
>
>
> The two other functions for dates in sqlite are:
>
> -strftime(format, timestring, modifier, modifier, ...)  which is equivalent
> to date() in this way:  date(...) =  strftime('%Y-%m-%d', ...)
> It works perfectly for getting results on dates like:
> SELECT strftime('%m','now') - strftime('%m','2004-01-01 02:34:56');
> would give the difference in month between the dates, but unfortunately the
> modifier to change the date in the same as in date:
> SELECT strftime('%m','now','+3 month');
> So no help from here.
>
> -julianday(timestring, modifier, modifier, ...) the number of days since
> noon in Greenwich on November 24, 4714 B.C.
> This is "great" (let's say doable) for adding a variable number of days like
> this:
> SELECT DATE( SELECT julianday('now')+id FROM table );
>
> But useless for adding months (would have to take into account 30 or 31 days
> month etc.. years).
>
>
>
> I really looked on forums, google, etc and didnt find any solution.
>
> Doc on the date function in sqlite: http://www.sqlite.org/lang_datefunc.html
>
> Have a nice day, thanks
>
> Harder question: why on earth would someone ever code a function this way
> instead of DATE('now',int, string) ??
>
>
>
>
>
>
>
> --
> View this message in context: 
> http://old.nabble.com/Sqlite-DATE%28%29-function.-Add-a-variable-amount-of-months.-tp31802785p31802785.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table info for a particular query

[Pavel Ivanov]

> Is there any way to do something along the lines of PRAGMA
> some_command(SELECT * FROM table1 JOIN table2) and have it return A, B, C,
> a, b, c as the headers?

If you insist on getting results as resultset then there's no way to
do that. But you can do the similar thing by using API like
sqlite3_column_name (http://sqlite.org/c3ref/column_name.html) or
sqlite3_column_database_name
(http://sqlite.org/c3ref/column_database_name.html) in C.


Pavel


On Wed, Jun 8, 2011 at 2:08 PM, Eric Zhao  wrote:
> Hi,
>
> I find the PRAGMA table_info(*tablename)* command very helpful.
> However, sometimes I want to extract table headers for a table constructed
> by a query which does not have the exact same headers as any one table in my
> database.
>
> For example:
>
> Table 1 headers:  A, B, and C
> Table 2 headers:  a, b, and c
>
> Query:  SELECT * FROM table1 JOIN table2;
>
> Is there any way to do something along the lines of PRAGMA
> some_command(SELECT * FROM table1 JOIN table2) and have it return A, B, C,
> a, b, c as the headers?
>
> Thanks.
> ___
> 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] Dynamically loaded sqlite (linux)

[Pavel Ivanov]

> I know that I should avoid such things, but just curious, is it something
> inside sqlite that probably makes one dynanmically linked and other
> dynamically loaded library share global data and can this be avoidable?

No, it's Linux linker who works like that. If you have several
libraries loaded into the process with the same exported symbols then
everybody using those symbols will be linked against ones located in
the library loaded first. Any symbols located in libraries loaded
after that won't be used. This is the default behavior of Linux linker
(it can be changed but AFAIK it's changed very rarely) and this
feature can produce sometimes very nasty results. E.g. if you try to
load the same library located in different directories then
initialization code will be executed in both of them, but they both
will be executed against the same global/static variables. And it
could lead to problems during initialization and definitely will lead
to problems during finalization (like double frees, segmentation
faults etc.).

So if you ever want to use dlopen() you should be really really
careful to avoid loading the same library several times (even if the
same library have different file names).


Pavel


On Thu, Jun 9, 2011 at 9:56 AM, Max Vlasov  wrote:
> On Tue, Jun 7, 2011 at 9:22 PM, Martin Gadbois  wrote:
>
>> On Tue, Jun 7, 2011 at 12:52 PM, Jay A. Kreibich  wrote:
>>
>> > On Tue, Jun 07, 2011 at 07:47:25PM +0400, Max Vlasov scratched on the
>> wall:
>> > > Hi,
>> > >
>> > > I'm trying to use sqlite with linux (Ubuntu, Pascal, Lazarus). I'm
>> still
>> > not
>> > > very familiar with linux development so I might miss something
>> essential.
>> > >
>> > > Two scenarios work ok
>> > > - statically linked latest version compiled (3.7.6.3), no options or
>> > defines
>> > > changed
>> > > - Dynamically loaded (dlopen) sqlite used from the installed package
>> > > libsqlite3 (libsqlite3.so)
>> >
>> >   That's not how dynamic libraries work (not normally, anyways).
>> >  Generally you simply tell the compiler/linker to link in the library
>> >  at build time, and allow the usage of dynamic libs.  The dynamic
>> >  link is then done on application start-up by the OS.  In Windows
>> >  terms, it is like using an .DLL by linking in the associated .lib
>> >  file.  Moving from a static library to a dynamic library requires no
>> >  code changes.
>> >
>>
>>
>> There is a way to do a _good_ shared library. I suggest reading the
>> excellent paper: http://www.akkadia.org/drepper/dsohowto.pdf
>>
>> As for the OP question, do
>> gcc -shared -Wl,-init=sqlite3_initialize -o libsqlite.so sqlite3.o
>>
>> and then link your application with
>> gcc -L. -lsqlite -o test test.c
>>
>> This assumes that libsqlite.so is in your current path: . (thus the -L.)
>>
>>
>
> Martin and Jay, thanks for the info.
>
> The hint helped be to track the problem.
>
> It appears that my problem was that I forgot to remove dynamic linking of
> libsqlite3.so library and had my own dynamic loading of another sqlite
> binary. Since the exceptions was when sqlite accessed sqlite global config
> (sqlite3GlobalConfig), I suppose they were probably magically sharing the
> global data or something like that.
>
> I know that I should avoid such things, but just curious, is it something
> inside sqlite that probably makes one dynanmically linked and other
> dynamically loaded library share global data and can this be avoidable? I
> thought that two libraries having different filenames and sonames virtually
> different for the system, but it looks like they're not.
>
> Thanks
>
> Max
> ___
> 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-dev] Index problem on Windows

[Pavel Ivanov]

> So my next question is then, given that I have some data coming in, in
> a random order, and I want an index. What can I do in order for the
> performance to be better?

Create index only after all data is populated.


BTW, this kind of questions will be answered best on a sqlite-users
list. sqlite-dev is for internal issues related to SQLite development
not to development using SQLite.


Pavel


On Thu, Jun 9, 2011 at 3:39 PM, Anders Rune Jensen  wrote:
> On Fri, Jun 10, 2011 at 12:05 AM, Michael Stephenson
>  wrote:
>> Hi Anders,
>
> Hello Michael
>
>> I tried your test app.  At first it crashed, until I increased the size of
>> your temp buffer.
>>
>> I ran it with a high-performance timer around the commits, and they
>> consistently took around 150 ms.  I saw no degradation in performance as the
>> app progressed.
>>
>> As an experiment, I added the following two lines right after the open
>> (turning on WAL mode), and the average commit was then down to around 35 ms:
>>
>>        sql = "pragma journal_mode = WAL;";
>>        sqlite3_exec(db, sql, NULL, NULL, NULL);
>>
>> Is it possible you are experiencing a hardware I/O issue?
>
> Wow, that's quite a difference with WAL.
>
> I just realized that the different between the C# and the C version is
> in the C version the numbers are inserted in order, and in the C#
> version they are inserted randomly. I tried changing the C# version to
> insert them in order and it worked fine as well.
>
> So my next question is then, given that I have some data coming in, in
> a random order, and I want an index. What can I do in order for the
> performance to be better?
>
> Thanks!
>
>> -Original Message-
>> From: sqlite-dev-boun...@sqlite.org [mailto:sqlite-dev-boun...@sqlite.org]
>> On Behalf Of Anders Rune Jensen
>> Sent: Thursday, June 09, 2011 2:48 PM
>> To: sqlite-...@sqlite.org
>> Subject: [sqlite-dev] Index problem on Windows
>>
>> Hello
>>
>> Let me first start by saying that I have been a very happy user of sqlite
>> for about 10 years now. I have used it in many different projects and have
>> had absolutely no problems with it so far.
>>
>> I have run into a problem where the time to do a commit starts taking longer
>> and longer. We are talking on the orders of 250ms for a table with ~ 20k
>> lines and a disc size of around 2-3mb. I have tracked the performance
>> problem down to something to do with indexing. It's almost as it is creating
>> the index for every commit. The commit consists of
>> 100 INSERTS. I have made a as small program as I could where I can reproduce
>> the problem and have tried running this on Linux as well.
>> Where the problem doesn't seem to occur. The problem exists with both WAL
>> and truncate journaling mode. The problem doesn't seem to exist when I use a
>> memory database instead of a file. I have tried both versionj 3.6.23.1 and
>> 3.7.6.3.
>>
>> On Windows where I'm experiencing the problem I run sqlite in a C# program.
>> I have checked the implementation of transaction support in the
>> System.Date.Sqlite wrapper and it does absolutely nothing else than simply
>> to a COMMIT. Sadly I don't have a C compiler for Windows so I can't check it
>> when not running the wrapper, but it should be the same.
>>
>> Attached is a C# program that will demonstrate the problem and a C program.
>>
>> Thanks!
>>
>> --
>> Anders Rune Jensen
>> http://www.iola.dk
>>
>> ___
>> sqlite-dev mailing list
>> sqlite-...@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>>
>
>
>
> --
> Anders Rune Jensen
> http://www.iola.dk
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in composite foreign key constraints.

[Pavel Ivanov]

IIRC, there was a bug in enforcement compound foreign keys where one
of columns is primary key and it was fixed. So if you take the latest
SQLite version I guess you won't see this problem.


Pavel


On Fri, Jun 10, 2011 at 11:12 AM, Max B  wrote:
> Hello,
>
> Please consider this (working) schema and this test data, on sqlite 3.7.4:
>
> CREATE TABLE TestTable (
>    id integer primary key,
>    name varchar,
>    source_id integer not null,
>    parent_id integer,
>    foreign key(parent_id) references TestTable(id)
> );
>
> PRAGMA foreign_keys=1;
> INSERT INTO TestTable VALUES (1, 'Parent', 1, null);
> INSERT INTO TestTable VALUES (2, 'Child', 1, 1);
> INSERT INTO TestTable VALUES (3, 'Bad', 1, 5);
>
> This schema is supposed to represent some object which can have an internal
> parent/child hierarchy, enforced using the self-referential foreign key.  The
> first two inserts should work, and the third should fail with a foreign key
> error, which it does.
>
> Next, I wanted to enforce that the source_id between the parent and child are
> also identical: that is parents and children must come from the same source.
>
> So, I tweaked it to use a composite foreign key, and created the required 
> UNIQUE
> index:
>
> CREATE TABLE TestTable (
>    id integer primary key,
>    name varchar,
>    source_id integer not null,
>    parent_id integer,
>    foreign key(source_id, parent_id) references TestTable(source_id, id)
> );
>
> CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
>
> PRAGMA foreign_keys=1;
> INSERT INTO TestTable VALUES (1, 'Parent', 1, null);
> INSERT INTO TestTable VALUES (2, 'Child', 1, 1);
> INSERT INTO TestTable VALUES (3, 'Bad', 1, 5);
>
> In this situation, the third insert succeeds, in spite of it's parent id not
> existing.  sqlite fails to enforce the constraint.
>
> If I tweak the schema *slightly*, only replacing 'integer primary key' with
> 'integer unique', I do get the expected behaviour:
>
>    CREATE TABLE TestTable (
>        id INTEGER UNIQUE,
>        name VARCHAR,
>        source_id INTEGER NOT NULL,
>        parent_id INTEGER,
>        FOREIGN KEY(source_id, parent_id) REFERENCES TestTable(source_id, id)
>    );
>
>    CREATE UNIQUE INDEX testindex ON TestTable(source_id, id);
>
> In this case, the third insert fails as expected.  However, this is not
> substantively different then the second schema, but one enforces the FK 
> properly
> and the other doesn't.  I looked at the opcodes for the second, but couldn't
> figure out the logic error.
>
> If it matters, I'm doing this testing on Ubuntu 11.04, with sqlite 3.7.4.
>
>
>
> ___
> 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-dev] Is there an option to not lock during transaction?

[Pavel Ivanov]

> Hmm... unless read_uncommited is persistant somehow; looks like if I
> just don't try to use shared cache it works... I thought I had
> disabled cache before alone and still got the same results...

read_uncommitted works only when shared_cache is on.

To answer your original question: if you disable shared cache, start
reading transaction on one connection and start writing transaction on
the other connection then you'll be able to read on the first
connection database in the state it was before writing transaction.
But if your writing transaction will grow too big then it won't be
able to proceed until reading transaction is finished.


Pavel


On Sun, Jun 12, 2011 at 4:11 PM, J Decker  wrote:
> If I disable enable_shared_cache then there is a pragme
> read_uncommitted that doesn't generate the locks; so non shared,
> read_uncommited combination works;
>
> I tried 'PRAGMA journal_mode=WAL'  which says it returns a string; I
> assume that if I treat it as a query and sqlite3_step I should get
> back this string?  sqlite3_step returns SQLITE_DONE not SQLITE_ROW so
> it looks like there is no data.  I'm currently using version 3.7.5
> amalgamation compiled with vs2010.  updated to 3.7.6.3 and still get
> no result from the pramga.
>
> Hmm... unless read_uncommited is persistant somehow; looks like if I
> just don't try to use shared cache it works... I thought I had
> disabled cache before alone and still got the same results...
>
>
>
> On Sun, Jun 12, 2011 at 5:48 AM, Florian Weimer  wrote:
>> * J. Decker:
>>
>>> I have enabled sqlite3_enable_shared_cache( 1 );
>>> I have basically two connections to the same database in the same
>>> process.  One connection is used for selects, the other is used for
>>> insert and replace.  I can end up with several threads with selects,
>>> but the writer is only used in by a single thread[at a time].
>>
>> The question seems to belong to the sqlite-users list; Reply-To set.
>>
>> Have you tried using WAL mode?
>> ___
>> sqlite-dev mailing list
>> sqlite-...@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>>
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Is there an option to not lock during transaction?

[Pavel Ivanov]

> Doesn't enabling WAL mode address the transaction size limit?

Yes, you are right. WAL mode will eliminate transaction size issue,
although there will be a WAL-file size issue. ;) But that issue is
only about disk space, nothing related to locking of reading
transactions.


Pavel


On Fri, Jun 17, 2011 at 1:18 AM, Florian Weimer <f...@deneb.enyo.de> wrote:
> * Pavel Ivanov:
>
>> To answer your original question: if you disable shared cache, start
>> reading transaction on one connection and start writing transaction on
>> the other connection then you'll be able to read on the first
>> connection database in the state it was before writing transaction.
>> But if your writing transaction will grow too big then it won't be
>> able to proceed until reading transaction is finished.
>
> Doesn't enabling WAL mode address the transaction size limit?
> ___
> 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] Opening a database on a Mac

[Pavel Ivanov]

>   strcpy(DBEnginePath, "Macintosh HD:Applications:Adobe InDesign 
> CS5:Plug-Ins:WPC_ID:IndexData.db");

Try to change path here to "/Applications/Adobe InDesign
CS5/Plug-Ins/WPC_ID/IndexData.db".


Pavel


On Fri, Jun 17, 2011 at 3:08 PM, john darnell
 wrote:
> Sorry to send this twice, but I realized that my first transmission did not 
> include a subject line.
>
> _
> From: john darnell
> Sent: Friday, June 17, 2011 1:56 PM
> To: 'General Discussion of SQLite Database'
> Subject:
>
>
> I am attempting to open an SQLite database on the Mac (OSX Snow Leopard) and 
> am getting an error.  This is the code I am using:
>
>
>   char      DBEnginePath[1000];
>
>   strcpy(DBEnginePath, "Macintosh HD:Applications:Adobe InDesign 
> CS5:Plug-Ins:WPC_ID:IndexData.db");
>   fprintf(stderr, "%s\n", DBEnginePath);                                      
>   //  Sends correct path to stderr for verification.
>   Result = sqlite3_open_v2(DBEnginePath, _ptr, SQLITE_OPEN_READONLY, 
> NULL);  //  Errors out here.
>
>   const char *msg = sqlite3_errmsg(db_ptr);
>   fprintf(stderr, "Here's the SQLite error message: %s\n", msg);              
>   // Sent to stderr: "Unable to open database file."
>
>
> I have verified that the file exists on the path described above.  What am I 
> doing wrong?
>
> TIA!
>
> R,
> John A.M. Darnell
> Senior Programmer
> Walsworth Publishing Company
> Brookfield, MO
> John may also be reached at 
> johnamdarn...@gmail.com
>
> Trivia question Trivia question:  In The Lord of the Rings,Leglolas was a 
> prince among the Silvan Elves.  What was the name of his father the King?  
> For extra credit, what was his surname?
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
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)

[Pavel Ivanov]

> Can some one help me why the disk I/O error occured during the
> sqlite3_prepare()  statement ?

Because while preparing the statement SQLite must read schema from the
database. And although in case of integrity check it's not really
necessary I think it's still the general rule and is done before it's
even known what statement is being prepared just so that resolving of
all identifiers could be done during the parsing.


Pavel


On Mon, Jun 20, 2011 at 11:25 AM, Raja Kondu  wrote:
> 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] openmpi parallel problem

[Pavel Ivanov]

Do you check result code of connection closing? Is it successful?
If it's not successful then some statements are still active and
reading transaction is still open. That could be the reason of error
in write process.


Pavel


On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
 wrote:
> i use openmpi fo parallelization, an c++ as the interface to sqlite.
> in my program one process only writes into the database, and the other
> processes read at the beginning, and then never do anything with it. But
> even when i close the database-connection of the non-writing processes,
> my write process says, that the database is locked. And this lock never
> gets released.
> I don't understand, why there is a lock on the database, when no other
> connection exists. Is there a way, that i can see, which connection has
> the lock?
> i tried this in a testprogramm, and there it worked, but not in the
> programm i want to use.
>
> thanks for any help
> ___
> 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] openmpi parallel problem

[Pavel Ivanov]

> there are no statements open, as far as i can see.

Your eyes can miss something. Does sqlite3_close return SQLITE_OK or
something else?


Pavel


On Wed, Jun 22, 2011 at 12:36 PM, Thorsten Kersting
<thorsten.kerst...@itp1.uni-stuttgart.de> wrote:
> there are no statements open, as far as i can see.
>
> On 06/22/2011 05:29 PM, Pavel Ivanov wrote:
>> Do you check result code of connection closing? Is it successful?
>> If it's not successful then some statements are still active and
>> reading transaction is still open. That could be the reason of error
>> in write process.
>>
>>
>> Pavel
>>
>>
>> On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
>> <thorsten.kerst...@itp1.uni-stuttgart.de>  wrote:
>>> i use openmpi fo parallelization, an c++ as the interface to sqlite.
>>> in my program one process only writes into the database, and the other
>>> processes read at the beginning, and then never do anything with it. But
>>> even when i close the database-connection of the non-writing processes,
>>> my write process says, that the database is locked. And this lock never
>>> gets released.
>>> I don't understand, why there is a lock on the database, when no other
>>> connection exists. Is there a way, that i can see, which connection has
>>> the lock?
>>> i tried this in a testprogramm, and there it worked, but not in the
>>> programm i want to use.
>>>
>>> thanks for any help
>>> ___
>>> 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] [BUG?] unexpected effect of view nesting on type conversion

[Pavel Ivanov]

> Just thought I should add that the problem seems to be gone with the
> just released version 3.7.7, but it's not mentioned in the release notes.

It's not in release notes but it's in the timeline. See
http://www.sqlite.org/src/info/91e2e8ba6f and
http://www.sqlite.org/src/info/0b3174e0b1.


Pavel


On Fri, Jun 24, 2011 at 7:15 AM, Mark Brand  wrote:
>
 I've run into a phenomenon I don't understand where view nesting affects
 types.
 Seen in sqlite 3.7.6.3
>>> sqlite-3.6.23 does NOT show this behavior.
>> After further reflection, I am going to go out on a limb and suggest
>> that the behavior described in the OP is a regression. The behavior
>> started somewhere between versions 3.6.23 and 3.7.6.3. It can have
>> serious consequences in the form of calculation errors.
>
>
> Just thought I should add that the problem seems to be gone with the
> just released version 3.7.7, but it's not mentioned in the release notes.
>
> regards,
>
> Mark
> ___
> 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] help needed to optimize a query

[Pavel Ivanov]

> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor, if I add a where clause selecting only one kind of data then it
> reduces to less than 5 seconds which is acceptable.

What kind of selects did you do here?

Generally in my opinion if you want to get all data as one table with
one file per row you shouldn't do that using just SELECT statement.
You should get raw data with SELECT and then transform it into the
necessary table format in your application.


Pavel


On Tue, Jun 28, 2011 at 10:33 AM, Lazarus 101  wrote:
> Hi guys, i'm working on an Android app and using sqlite to store some data
> and i need some help with a query.
>
> I have the following table structure:
>
> FILES
> file_id INTEGER NOT NULL,
> name TEXT
>
> DATA
> file_id INTEGER REFERENCES files(resource_id) ON DELETE CASCADE,
> data_type TEXT,
> value TEXT
>
> data_type can be one of:
> (title, description, date_taken, latitude,longitude, duration,
> album,track_nr,genre, artist, year)
>
> as you've probably guessed this is used to store information about some
> media files.
>
> Now, I want to select all music details in the following format
> file_id | name | duration | genre | artist | description | album | track_nr
> | year
>
> it's possible that a music from files does not have any data associated with
> it, in this case i want the name to be in the result set
>
> This has to run as fast as possible. A left join between these tables is too
> slow, for 10.000 entries it takes around 15 seconds just to navigate through
> the cursor, if I add a where clause selecting only one kind of data then it
> reduces to less than 5 seconds which is acceptable.
>
> Thanks a lot for any input.
> ___
> 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] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

>> Putting the 'ORDER BY' clause in view won't work?
>
> It will work just fine, in that the results you see will appear in the ORDER 
> you asked for.

I believe that's not always true and is not required by SQL standard.
Most probably 'select * from view_name' will return rows in the order
written in the view. But 'select * from view_name where some_column =
some_value' can already return rows in completely different order. And
'select * from table_name, view_name where some_condition' will almost
certainly ignore any ORDER BY in the view.

So ORDER BY in the view doesn't guarantee you anything.


Pavel


On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin  wrote:
>
> On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>
>> 2011/7/1 Simon Slavin 
>>
>>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>>>
 Isn't it possible to use a view for that?
>>>
>>> You can use a VIEW if you want, but VIEWs don't sort the table either.  A
>>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
>>> SQLite executes the SELECT.
>>
>> Putting the 'ORDER BY' clause in view won't work?
>
> It will work just fine, in that the results you see will appear in the ORDER 
> you asked for.
>
> However, it has no influence on how data is stored.  In fact no table data is 
> stored for a VIEW at all.  The thing stored is the parameters given when you 
> created the VIEW.  Every time you refer to a VIEW in a SQL statement SQL goes 
> back and looks at the VIEW specification again.
>
> 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] sqlite3_create_function and threads

[Pavel Ivanov]

> To put it another way, if I call sqlite3_create_function to install a
> custom function, is that function now available to all threads using
> SQLite or is it available only to the thread that made the
> sqlite3_create_function call?

Yes, it's available to all threads using the same connection.

> And does the same rule apply to the application-defined pointer that can
> optionally be supplied in the sqlite3_create_function call via parameter
> 5???

Yes. What's wrong with that?


Pavel


On Fri, Jul 1, 2011 at 10:53 AM, Technology Lighthouse
 wrote:
>  From the docs it's unclear to me whether the use of
> sqlite3_create_function is thread-specific or not.
>
> To put it another way, if I call sqlite3_create_function to install a
> custom function, is that function now available to all threads using
> SQLite or is it available only to the thread that made the
> sqlite3_create_function call?
>
> And does the same rule apply to the application-defined pointer that can
> optionally be supplied in the sqlite3_create_function call via parameter
> 5???
>
> --
> Paul Roberts
> ___
> 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] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> What I want to do is...make sure that when I say BETWEEN I really mean eg
> BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
> not rowid.

So, can you add "ORDER BY pos" to your queries?


Pavel


On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
<mgbg25...@blueyonder.co.uk> wrote:
> Thank you all for your responses.
> I had to go out after posting and have just come back.
> My concern is with...
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> and
> SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>
> t_x and t_y are dimension tables.
> that hold the x and y margins of a spreadsheet.
> The margins will have an implied order shown by pos
> which will differ from the order in which rows are added (represented by
> rowid).
>
> What I want to do is...make sure that when I say BETWEEN I really mean eg
> BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
> not rowid. I hope that helps explain why pos exists and is not rowid i.e. I
> want to be able to "insert" and "delete" records "!in between" the existing
> ones or at least make it look like that even if the records are physically
> appended to the tables.
> Hope this clarifies things and look forward to your thoughts.
>
>
> On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> >> Putting the 'ORDER BY' clause in view won't work?
>> >
>> > It will work just fine, in that the results you see will appear in the
>> ORDER you asked for.
>>
>> I believe that's not always true and is not required by SQL standard.
>> Most probably 'select * from view_name' will return rows in the order
>> written in the view. But 'select * from view_name where some_column =
>> some_value' can already return rows in completely different order. And
>> 'select * from table_name, view_name where some_condition' will almost
>> certainly ignore any ORDER BY in the view.
>>
>> So ORDER BY in the view doesn't guarantee you anything.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin <slav...@bigfraud.org>
>> wrote:
>> >
>> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>> >
>> >> 2011/7/1 Simon Slavin <slav...@bigfraud.org>
>> >>
>> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>> >>>
>> >>>> Isn't it possible to use a view for that?
>> >>>
>> >>> You can use a VIEW if you want, but VIEWs don't sort the table either.
>>  A
>> >>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
>> >>> SQLite executes the SELECT.
>> >>
>> >> Putting the 'ORDER BY' clause in view won't work?
>> >
>> > It will work just fine, in that the results you see will appear in the
>> ORDER you asked for.
>> >
>> > However, it has no influence on how data is stored.  In fact no table
>> data is stored for a VIEW at all.  The thing stored is the parameters given
>> when you created the VIEW.  Every time you refer to a VIEW in a SQL
>> statement SQL goes back and looks at the VIEW specification again.
>> >
>> > 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-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] sqlite3_create_function and threads

[Pavel Ivanov]

>  > Yes. What's wrong with that?
>
> Nothing at all - I just needed to know whether that was the case so I
> could design certain sections of my code accordingly.

Three question marks of yours suggested me that you think it's awfully wrong.

Note that although your function and application pointer will be
shared by all threads SQLite (or in some cases you) will guarantee
that this function won't be called simultaneously from different
threads for this connection. So you need to introduce some additional
thread-safety only if you use the same function and same application
pointer for different simultaneous connections.


Pavel


On Fri, Jul 1, 2011 at 11:11 AM, Technology Lighthouse
 wrote:
>  > Yes. What's wrong with that?
>
> Nothing at all - I just needed to know whether that was the case so I
> could design certain sections of my code accordingly.
>
> Thanks for the help!
> --
> Paul Roberts
> ___
> 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] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of how you insert it.

I don't understand what you are talking about here. You should write
it like this:

SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;


Pavel


On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
<mgbg25...@blueyonder.co.uk> wrote:
> Thx for your suggestion...
> Yes "BY ORDER BY pos" has to be in there somewhere.
> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of how you insert it.
> I'll have a play.
>
>
>
> On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > What I want to do is...make sure that when I say BETWEEN I really mean eg
>> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> and
>> > not rowid.
>>
>> So, can you add "ORDER BY pos" to your queries?
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>> <mgbg25...@blueyonder.co.uk> wrote:
>> > Thank you all for your responses.
>> > I had to go out after posting and have just come back.
>> > My concern is with...
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > and
>> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>> >
>> > t_x and t_y are dimension tables.
>> > that hold the x and y margins of a spreadsheet.
>> > The margins will have an implied order shown by pos
>> > which will differ from the order in which rows are added (represented by
>> > rowid).
>> >
>> > What I want to do is...make sure that when I say BETWEEN I really mean eg
>> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> and
>> > not rowid. I hope that helps explain why pos exists and is not rowid i.e.
>> I
>> > want to be able to "insert" and "delete" records "!in between" the
>> existing
>> > ones or at least make it look like that even if the records are
>> physically
>> > appended to the tables.
>> > Hope this clarifies things and look forward to your thoughts.
>> >
>> >
>> > On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> wrote:
>> >
>> >> >> Putting the 'ORDER BY' clause in view won't work?
>> >> >
>> >> > It will work just fine, in that the results you see will appear in the
>> >> ORDER you asked for.
>> >>
>> >> I believe that's not always true and is not required by SQL standard.
>> >> Most probably 'select * from view_name' will return rows in the order
>> >> written in the view. But 'select * from view_name where some_column =
>> >> some_value' can already return rows in completely different order. And
>> >> 'select * from table_name, view_name where some_condition' will almost
>> >> certainly ignore any ORDER BY in the view.
>> >>
>> >> So ORDER BY in the view doesn't guarantee you anything.
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin <slav...@bigfraud.org>
>> >> wrote:
>> >> >
>> >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>> >> >
>> >> >> 2011/7/1 Simon Slavin <slav...@bigfraud.org>
>> >> >>
>> >> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>> >> >>>
>> >> >>>> Isn't it possible to use a view for that?
>> >> >>>
>> >> >>> You can use a VIEW if you want, but VIEWs don't sort the table
>> either.
>> >>  A
>> >> >>> VIEW is just a way of saving a SELECT query.  When you consult the
>> VIEW
>> >> >>> SQLite executes the SELECT.
>> >> >>
>> >> >> Putting the 'ORDER BY' clause in view won't work?
>> >> >
>> >> > It will work just fine, in that the results you see will appear in the
>> >> ORDER you asked for.
>> >> >
>> >> > However, it has no influence on how data is stored.  In fact no table
>&

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> I'll certainly try
>>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
> results just sorted by pos.

Maybe I've missed something in this conversation? Please clarify how
"results sorted by pos" will be different from "x1 and x2 to be
ordered before BETWEEN sees". And tell us more clearly what results
you want to see from your query. We certainly see that you want to get
value of pos from all rows where value of txt lies between 'x1' and
'x2'. Now do you want those results to be order by value of pos (add
ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
in a random order (do not add ORDER BY at all)? If you believe that
result of a query differs depending on what order SQLite processes
rows in then you are wrong.


Pavel


On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
<mgbg25...@blueyonder.co.uk> wrote:
> I'll certainly try
>>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
> results just sorted by pos.
>
> I've just done this.
>    const char* sqlSelect =    "SELECT d FROM t_d "
>                            "WHERE xpos in "
>                            "(SELECT pos FROM (SELECT * from t_x ORDER BY
> pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>                            //bit for t_y omitted.
> in the hope that (SELECT * from t_x ORDER BY pos) presents it's results to
> SELECT BETWEEN in pos order.
> I am concerned about having to specify both xpos and pos and am not sure how
> these two get reconciled.
> I am getting results but want to add more data to the tables to see whats
> going on.
>
> Thank you for your assistance though.
>
> On 1 July 2011 17:07, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > It strikes me that
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > needs to be operating on the results returned by
>> > SELECT * FROM t_x BY ORDER BY pos
>> > ie another level of query is required but I'm not sure of how you insert
>> it.
>>
>> I don't understand what you are talking about here. You should write
>> it like this:
>>
>> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
>> <mgbg25...@blueyonder.co.uk> wrote:
>> > Thx for your suggestion...
>> > Yes "BY ORDER BY pos" has to be in there somewhere.
>> > It strikes me that
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > needs to be operating on the results returned by
>> > SELECT * FROM t_x BY ORDER BY pos
>> > ie another level of query is required but I'm not sure of how you insert
>> it.
>> > I'll have a play.
>> >
>> >
>> >
>> > On 1 July 2011 16:12, Pavel Ivanov <paiva...@gmail.com> wrote:
>> >
>> >> > What I want to do is...make sure that when I say BETWEEN I really mean
>> eg
>> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> >> and
>> >> > not rowid.
>> >>
>> >> So, can you add "ORDER BY pos" to your queries?
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>> >> <mgbg25...@blueyonder.co.uk> wrote:
>> >> > Thank you all for your responses.
>> >> > I had to go out after posting and have just come back.
>> >> > My concern is with...
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > and
>> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>> >> >
>> >> > t_x and t_y are dimension tables.
>> >> > that hold the x and y margins of a spreadsheet.
>> >> > The margins will have an implied order shown by pos
>> >> > which will differ from the order in which rows are added (represented
>> by
>> >> > rowid).
>> >> >
>> >> > What I want to do is...make sure that when I say BETWEEN I really mean
>> eg
>> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> >> and
>> >> > not rowid. I hope that helps explain why pos exists and is not rowid
>> i.e.
>> >> I
>> >> > want to be able to "insert" and "delet

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> Its not a very good example because the two are adjacent and 'x1' and 'x2'
> sound like they're adjacent too.

They are not adjacent - 'x1123456' and a lot of other strings starting
with 'x1' are between them.

> I'm only interested in the results of BETWEEN when you're looking at x1 and
> x2 from the pos order perspective

Then David's query is a way to go.


Pavel


On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
<mgbg25...@blueyonder.co.uk> wrote:
> Pavel, David
> Thanks for bearing with me...
>> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt
> = 'x1' and the row where txt = 'x2'
> Yes that's right.
> Its not a very good example because the two are adjacent and 'x1' and 'x2'
> sound like they're adjacent too.
> I'm only interested in the results of BETWEEN when you're looking at x1 and
> x2 from the pos order perspective
> ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid
> perspective/order.
> I'll write a better description of what I'm trying to do and come back.
>
> On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > I'll certainly try
>> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
>> the
>> > results just sorted by pos.
>>
>> Maybe I've missed something in this conversation? Please clarify how
>> "results sorted by pos" will be different from "x1 and x2 to be
>> ordered before BETWEEN sees". And tell us more clearly what results
>> you want to see from your query. We certainly see that you want to get
>> value of pos from all rows where value of txt lies between 'x1' and
>> 'x2'. Now do you want those results to be order by value of pos (add
>> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
>> in a random order (do not add ORDER BY at all)? If you believe that
>> result of a query differs depending on what order SQLite processes
>> rows in then you are wrong.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>> <mgbg25...@blueyonder.co.uk> wrote:
>> > I'll certainly try
>> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
>> the
>> > results just sorted by pos.
>> >
>> > I've just done this.
>> >    const char* sqlSelect =    "SELECT d FROM t_d "
>> >                            "WHERE xpos in "
>> >                            "(SELECT pos FROM (SELECT * from t_x ORDER BY
>> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>> >                            //bit for t_y omitted.
>> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's results
>> to
>> > SELECT BETWEEN in pos order.
>> > I am concerned about having to specify both xpos and pos and am not sure
>> how
>> > these two get reconciled.
>> > I am getting results but want to add more data to the tables to see whats
>> > going on.
>> >
>> > Thank you for your assistance though.
>> >
>> > On 1 July 2011 17:07, Pavel Ivanov <paiva...@gmail.com> wrote:
>> >
>> >> > It strikes me that
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > needs to be operating on the results returned by
>> >> > SELECT * FROM t_x BY ORDER BY pos
>> >> > ie another level of query is required but I'm not sure of how you
>> insert
>> >> it.
>> >>
>> >> I don't understand what you are talking about here. You should write
>> >> it like this:
>> >>
>> >> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
>> >> <mgbg25...@blueyonder.co.uk> wrote:
>> >> > Thx for your suggestion...
>> >> > Yes "BY ORDER BY pos" has to be in there somewhere.
>> >> > It strikes me that
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > needs to be operating on the results returned by
>> >> > SELECT * FROM t_x BY ORDER BY pos
>> >> > ie another level of query is required but I'm not sure of how you
>> insert
>> >> it.
>> >> > I'll have a play.
>&

Re: [sqlite] Java and accented characters

[Pavel Ivanov]

> Any idea? Please, anyone can help me?

How did you put your data into database? I guess you did that with
command line utility. And as your console wasn't set to have UTF-8
charset then contents of pais now is not in UTF-8. But jdbc driver
tries to treat it as UTF-8 and so you don't see what you want to see.
To check this guess execute this query:

select nom_pais, hex(nom_pais) from pais;

You can execute it in command line utility and see if hex contents has
correct UTF-8 encoding as it should be.


Pavel


On Sun, Jul 3, 2011 at 2:40 PM, Fabiana Nascimento  wrote:
> Hello,
>
> I'm having problem with accented characters when getting text data from
> sqlite3 with Java (sqlite jdbc).  When i query in command line, the result
> came correct with accentuation. For example:
>
> *sqlite> select nom_pais from pais ;
> Itália
> Rússia
> *
> But, when I query from java app, it's work wrong, as:
>
> *nome do país: It li
> nome do país: R£ssi
> *
> I tried set properties on connection:
>
> *Properties prop = new Properties();
> prop.put("charSet", "UTF-8");
> this.conn = DriverManager.getConnection("jdbc:sqlite:" + file, prop);
> *
> And set system properties before get result:
> *
> System.setProperty("file.encoding", "UTF-8");*
>
> But nothing doesn't work...
> Any idea? Please, anyone can help me?
>
> best regards
>
> Fabiana
> ___
> 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] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Pavel Ivanov]

> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();

It shouldn't be ever possible to change the database from within a
function called from SELECT statement. SELECT query doesn't ever
changes any data.


Pavel


On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper  wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my function I try to drop an r-tree virtual table, but I get the error 
> SQLITE_LOCKED.
>
> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();
>
> Thanks,
> Ben
> ___
> 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