Re: [sqlite] Update if exists or Insert if exists

[Jim Dodgen]

"insert or replace" should do it

On Fri, Jan 9, 2009 at 6:21 PM, chiefmccrossan wrote:

>
> Hi,
>
> I have a table called Test with one column called Name - CREATE TABLE
> "Test"
> ("Name" VARCHAR)
>
> I would like to insert a value into test if the name column is empty but if
> the column is not empty then I would like to update/overwrite the current
> value in the Name column.
>
> Can anyone please help me?
>
> Thanks
> Adam
> --
> View this message in context:
> http://www.nabble.com/Update-if-exists-or-Insert-if-exists-tp21384054p21384054.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
>



-- 
Jim Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update if exists or Insert if exists

[chiefmccrossan]

Hi,

I have a table called Test with one column called Name - CREATE TABLE "Test"
("Name" VARCHAR)

I would like to insert a value into test if the name column is empty but if
the column is not empty then I would like to update/overwrite the current
value in the Name column.

Can anyone please help me?

Thanks
Adam
-- 
View this message in context: 
http://www.nabble.com/Update-if-exists-or-Insert-if-exists-tp21384054p21384054.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] What does "PRAGMA integrity_check" actually do?

[Richard Klein]

Roger Binns wrote:
>> Writing to the beginning of the file (the first 40 bytes
>> or so) corrupts the database so badly that SQLite can't
>> even execute the pragma.
> 
> The header contains a signature for the file and important meta
> information which make the file useless if they are wrong.  See
> http://www.sqlite.org/fileformat.html#tocentry_71
> 
Great document!  I wasn't aware that it existed.  Thanks!

> That said, if you are using a system that random corrupts files then
> your problems are far larger than SQLite.  How do you deal with other
> data being corrupted, or even ensure that the programs you run aren't
> messed with?
>
We're confident of our file system, because we've never had
any problems with it.  However, we have seen some nasty data
corruption problems with our current database system, which
we hope to eliminate by converting to SQLite.

- Richard Klein


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

Re: [sqlite] What does "PRAGMA integrity_check" actually do?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Richard Klein wrote:
> Writing to the beginning of the file (the first 40 bytes
> or so) corrupts the database so badly that SQLite can't
> even execute the pragma.

The header contains a signature for the file and important meta
information which make the file useless if they are wrong.  See
http://www.sqlite.org/fileformat.html#tocentry_71

> Writing to the middle of the file -- even a large swath
> of garbage -- does undetected by the pragma.

See the documentation for some idea of what the pragma does:
http://www.sqlite.org/pragma.html#debug

SQLite does not checksum its values.  The integrity check pragma is
checking the structural integrity of the file.  If you write garbage
over free pages, or change values in ways that don't compromise the
structural integrity then you won't see any problems.

There has been some debate over how far the integrity checking should
go.  Some people want a quick verification while others are happy for
spotting a byte out of place even if it would take several minutes.

That said, if you are using a system that random corrupts files then
your problems are far larger than SQLite.  How do you deal with other
data being corrupted, or even ensure that the programs you run aren't
messed with?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkln+98ACgkQmOOfHg372QRDBACfcS9YjywhW21tQ8fzTZ7CQT0b
eJMAn1uCGmm7WwqGfFyaz0RcaYofR1uv
=19QF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] erratum

[Richard Klein]

"does undetected" => "goes undetected"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does "PRAGMA integrity_check" actually do?

[Richard Klein]

I wrote a test program to deliberately trash a database
file, and then see if "PRAGMA integrity_check" could
detect the corruption.

The only thing I found that works is writing beyond the
end of the file.

Writing to the beginning of the file (the first 40 bytes
or so) corrupts the database so badly that SQLite can't
even execute the pragma.

Writing to the middle of the file -- even a large swath
of garbage -- does undetected by the pragma.

I was hoping that the integrity checking pragma would be
more robust than this.  Am I missing something?

Thanks,
- Richard Klein

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

Re: [sqlite] Hi, a question from Colombia

[Ribeiro, Glauber]

Carlos,

If you don't mind, I'll answer through the list, because there are
people there who know much more than I do.

Here's your question:


> Here I'm looking for subtract two datefields from diferentes tables if

> the field1 is not null, if that field is null the value is 'without 
> close' and this case conditional
>
> CASE
>WHEN field1 IS NULL
> THEN 'SIN CERRAR'
> ELSE (STRFTIME('%J',field1) - STRFTIME('%J',field2))
> END AS DIAS_CERRAR,


How are you storing your dates? SQLite doesn't have a true date format,
so you need to store it either as a string or a number (which could be a
Julian date, a Unix-style number of seconds since a reference date, or
something else even).

Assuming you are storing your dates as strings like this: 2009-01-09,
the code above should give the difference, in days, between the 2 dates.
Is it not working for you?




> and this
>
> convert(STRFTIME('%J',field1) - STRFTIME('%J',field2))


I'm not sure what this is; convert() is not a standard function in
SQLite. Is this from hsql? Or is it a user-defined function?


glauber



 

-Original Message-
From: Carlos Suarez [mailto:carlos.sua...@rolsoft.com] 
Sent: Friday, January 09, 2009 1:14 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Hi, a question from Colombia

Hi, my name is Carlos Suarez from Colombia, I have a few problems while 
I migrated from hsql to sqlite and I want to know if you can help me 
with some of this stuff by this way of mail or have I  to quote in 
somewhere?.

thanksfully

Carlos Suarez



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

Re: [sqlite] Why the difference in these 2 SQLite files?

[RB Smissaert]

OK, any DB write to this file on the desktop (not the mobile device)
will make the query go fast. A completely trivial update will do it, so
it looks somehow the db write on the desktop does makes it go fast.
I can't check now if the same applies when doing this db write on the first
PC and hopefully it does.
Maybe I need some app to compare the 2 db files to see what is going on
here. Baffling me.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:54
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Why the difference in these 2 SQLite files?

Have now also looked at the full EXPLAIN and although understand that
it looks indentical to me for both databases:

Slow
-
addropcode  p1  p2  p3

0   Goto0   34  
1   MemLoad 0   0   
2   If  0   31  
3   MemInt  1   0   
4   MemInt  0   1   
5   Integer 1   0   
6   MustBeInt   0   0   
7   MemStore2   0   
8   IfMemZero   2   31  
9   IfMemPos2   13  
10  Pop 1   0   
11  MemInt  -1  3   
12  Goto0   14  
13  MemStore3   1   
14  Integer 0   0   
15  OpenRead1   15123   keyinfo(1,BINARY)
16  SetNumColumns   1   2   
17  Integer 823 0   
18  IsNull  -1  30  
19  MakeRecord  1   0   d
20  MemStore4   0   
21  MoveGe  1   30  
22  MemLoad 4   0   
23  IdxGE   1   30  +
24  Column  1   0   
25  MemInt  1   1   
26  Pop 1   0   
27  MemIncr -1  2   
28  IfMemZero   2   30  
29  Next1   22  
30  Close   1   0   
31  MemLoad 1   0   
32  Callback1   0   
33  Halt0   0   
34  Transaction 0   0   
35  VerifyCookie0   397 
36  Goto0   1   
37  Noop0   0   


Fast
-
addropcode  p1  p2

0   Goto0   34
1   MemLoad 0   0
2   If  0   31
3   MemInt  1   0
4   MemInt  0   1
5   Integer 1   0
6   MustBeInt   0   0
7   MemStore2   0
8   IfMemZero   2   31
9   IfMemPos2   13
10  Pop 1   0
11  MemInt  -1  3
12  Goto0   14
13  MemStore3   1
14  Integer 0   0
15  OpenRead1   15123
16  SetNumColumns   1   2
17  Integer 831 0
18  IsNull  -1  30
19  MakeRecord  1   0
20  MemStore4   0
21  MoveGe  1   30
22  MemLoad 4   0
23  IdxGE   1   30
24  Column  1   0
25  MemInt  1   1
26  Pop 1   0
27  MemIncr -1  2
28  IfMemZero   2   30
29  Next1   22
30  Close   1   0
31  MemLoad 1   0
32  Callback1   0
33  Halt0   0
34  Transaction 0   0
35  VerifyCookie0   400
36  Goto0   1
37  Noop0   0

So, what possibly could explain this? There can be no cache effect as I can
reverse it from fast to slow and vice versa by copying one or the other
database.


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:00
To: sqlite-users@sqlite.org
Subject: [sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an 

Re: [sqlite] how to replace the sentence limit

[Igor Tandetnik]

Carlos Suarez 
wrote:
> Thanks Igor, I haven't note about the diagram, now I want to ask you
> help for this:
>
> CASE
> WHEN field1 IS NULL
> THEN 'SIN CERRAR'
>  ELSE (STRFTIME('%J',field1) - STRFTIME('%J',field2))
> END AS DIAS_CERRAR,

Yes, please feel free to ask my help for this.

Igor Tandetnik 



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

Re: [sqlite] 600ms for simple query: How to optimize it?

[D. Richard Hipp]

On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote:
>
> SELECT t.topic, t.length
> FROM printgroup AS pg1
> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup
> LEFT JOIN topics AS t ON t.topicID = pg2.topicID
> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID
> WHERE ti.topic_textID = ''
> ORDER BY pg2.topicID ASC;
>

You seem very fond of using LEFT JOINs in places where they do not  
make good sense.  What is it that you think a LEFT JOIN does?  How is  
a LEFT JOIN different than an ordinary inner JOIN?  I ask because I  
suspect that your answer will reveal misconceptions about LEFT JOINs  
which, when rectified, will cause most of your performance issues to  
go away.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] how to replace the sentence limit

[Carlos Suarez]

Thanks Igor, I haven't note about the diagram, now I want to ask you 
help for this:

CASE
WHEN field1 IS NULL
THEN 'SIN CERRAR'
 ELSE (STRFTIME('%J',field1) - STRFTIME('%J',field2))
END AS DIAS_CERRAR,


Igor Tandetnik escribió:
> Carlos Suarez 
> wrote:
>   
>> Hello, my name is Carlos I need to replace de common sentence in
>> hsqldb --LIMIT min rows - max rows with something for delimit a query
>> in sqlite because this is not supported
>> 
>
> select * from mytable
> limit 10 offset 5;
>
> -- or
>
> select * from mytable
> limit 5, 10;
>
> For more details, see http://sqlite.org/lang_select.html
>
> 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] Hi, a question from Colombia

[Ribeiro, Glauber]

Hi, Carlos,

People here are helpful and friendly, and we will try to help you in our
abundant free time, but try to include as much information as possible
with your question, so we can give you meaningful answers.

For example, don't just ask "why is my SQL so slow?", but tell us
specifically what SQL you are running, number or records in your table,
kind of computer, anything that might help us figure it out.

glauber

-Original Message-
From: Carlos Suarez [mailto:carlos.sua...@rolsoft.com] 
Sent: Friday, January 09, 2009 1:14 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Hi, a question from Colombia

Hi, my name is Carlos Suarez from Colombia, I have a few problems while 
I migrated from hsql to sqlite and I want to know if you can help me 
with some of this stuff by this way of mail or have I  to quote in 
somewhere?.

thanksfully

Carlos Suarez



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

Re: [sqlite] 600ms for simple query: How to optimize it?

[Kees Nuyt]

On Fri, 09 Jan 2009 21:16:03 +0100, Lukas Haase
 wrote in General Discussion of SQLite
Database :

>Hello Richard!
>
>Thank you very much!! It works! :-)
>
>
>Indeed. 0-10 milliseconds instead of 500-800 :-)
>
>But may you tell me why this works and where you have this information? 
>I know the O-notation but I do not know /why/ this boosts down to log(n)...

Use EXPLAIN SELECT .
to see the virtual machine instructions
and EXPLAIN SELECT QUERY PLAN .
to see which index is used.

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

Each JOIN is implemented as nested loops. The virtual
machine code can tell a lot about what part of the database
has to be scanned.

>I have other queries which worry me. But that trick did not help in 
>these cases :-(
>
[...]
>
>Thank you again and best regards,
>Luke

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hi, a question from Colombia

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Carlos Suarez wrote:
> Hi, my name is Carlos Suarez from Colombia, I have a few problems while 
> I migrated from hsql to sqlite and I want to know if you can help me 
> with some of this stuff by this way of mail or have I  to quote in 
> somewhere?.

Follow this:

 http://www.catb.org/~esr/faqs/smart-questions.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAklnu+oACgkQmOOfHg372QReegCgtkujle3SF/8jpAATRTMk4FnU
pCwAnizzPz0Aj6HhSwhBvn4bWUSfPAZ/
=81Nc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to replace the sentence limit

[Igor Tandetnik]

Carlos Suarez 
wrote:
> Hello, my name is Carlos I need to replace de common sentence in
> hsqldb --LIMIT min rows - max rows with something for delimit a query
> in sqlite because this is not supported

select * from mytable
limit 10 offset 5;

-- or

select * from mytable
limit 5, 10;

For more details, see http://sqlite.org/lang_select.html

Igor Tandetnik 



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

Re: [sqlite] Why the difference in these 2 SQLite files?

[RB Smissaert]

Have now also looked at the full EXPLAIN and although understand that
it looks indentical to me for both databases:

Slow
-
addropcode  p1  p2  p3

0   Goto0   34  
1   MemLoad 0   0   
2   If  0   31  
3   MemInt  1   0   
4   MemInt  0   1   
5   Integer 1   0   
6   MustBeInt   0   0   
7   MemStore2   0   
8   IfMemZero   2   31  
9   IfMemPos2   13  
10  Pop 1   0   
11  MemInt  -1  3   
12  Goto0   14  
13  MemStore3   1   
14  Integer 0   0   
15  OpenRead1   15123   keyinfo(1,BINARY)
16  SetNumColumns   1   2   
17  Integer 823 0   
18  IsNull  -1  30  
19  MakeRecord  1   0   d
20  MemStore4   0   
21  MoveGe  1   30  
22  MemLoad 4   0   
23  IdxGE   1   30  +
24  Column  1   0   
25  MemInt  1   1   
26  Pop 1   0   
27  MemIncr -1  2   
28  IfMemZero   2   30  
29  Next1   22  
30  Close   1   0   
31  MemLoad 1   0   
32  Callback1   0   
33  Halt0   0   
34  Transaction 0   0   
35  VerifyCookie0   397 
36  Goto0   1   
37  Noop0   0   


Fast
-
addropcode  p1  p2

0   Goto0   34
1   MemLoad 0   0
2   If  0   31
3   MemInt  1   0
4   MemInt  0   1
5   Integer 1   0
6   MustBeInt   0   0
7   MemStore2   0
8   IfMemZero   2   31
9   IfMemPos2   13
10  Pop 1   0
11  MemInt  -1  3
12  Goto0   14
13  MemStore3   1
14  Integer 0   0
15  OpenRead1   15123
16  SetNumColumns   1   2
17  Integer 831 0
18  IsNull  -1  30
19  MakeRecord  1   0
20  MemStore4   0
21  MoveGe  1   30
22  MemLoad 4   0
23  IdxGE   1   30
24  Column  1   0
25  MemInt  1   1
26  Pop 1   0
27  MemIncr -1  2
28  IfMemZero   2   30
29  Next1   22
30  Close   1   0
31  MemLoad 1   0
32  Callback1   0
33  Halt0   0
34  Transaction 0   0
35  VerifyCookie0   400
36  Goto0   1
37  Noop0   0

So, what possibly could explain this? There can be no cache effect as I can
reverse it from fast to slow and vice versa by copying one or the other
database.


RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert
Sent: 09 January 2009 20:00
To: sqlite-users@sqlite.org
Subject: [sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an index, again in a small table, completely unrelated to the above
query and I see the same speed gain. I have compared the 2 different
database files, so stats, all the different pragma's etc. and I can't see
the difference. I have also compared the SQLite query plans and they are the
same (using the index) for both files. So what possibly could explain the
difference in speed?

I know it is a bit of a long-winded question, but maybe somebody has some
idea what is going on here and if so, very grateful for that as I can't see
it.


RBS





___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] Memory loss with sqlite3_step()

[Jay A. Kreibich]

On Fri, Jan 09, 2009 at 07:42:27PM +, John Delaney scratched on the wall:
> 
> Hi,
>  
> I am a new user so please make allowances accordingly.
>  
> Using SQLite3 3.5.9, I am seeing a consistent rise in memory with each 
> call to sqlite3_step().
 
  
> This is basic functionality so I am doing something wrong. 

  I'd assume it is the normal cache behavior.  By default, SQLite
  database pages are 1K and the max page cache is 2000, using a total
  of about 3MB of RAM (each page as a ~0.5K overhead).

  You could try a lot more queries and see if things level out and/or
  lower the cache limit via PRAGMA.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to replace the sentence limit

[Carlos Suarez]

Hello, my name is Carlos I need to replace de common sentence in hsqldb
--LIMIT min rows - max rows with something for delimit a query in sqlite 
because this is not supported

thanks,

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

Re: [sqlite] 600ms for simple query: How to optimize it?

[Lukas Haase]

Hello Richard!

Thank you very much!! It works! :-)

D. Richard Hipp schrieb:
> On Jan 7, 2009, at 6:11 PM, Lukas Haase wrote:
> 
>> Hello,
>>
>> Can somebody tell me why this (simple) query take so much time? This
>> query does nothing more than querying a table and JOINing two other
>> tables together.
>>
>> SELECT
>>  ti1.topicID AS topicID,
>>  ti2.topic_textID AS parent,
>>  n.level,
>>  n.level_order
>> FROM navigation AS n
>> LEFT JOIN topic_ids AS ti1 ON ti1.topicID = n.topicID
>> LEFT JOIN topic_ids AS ti2 ON ti2.topicID = n.parent_topicID
>> WHERE ti1.topic_textID = 'X';
> 
> SQLite should be running this query in O(NlogN).
> 
> If you change the first LEFT JOIN to a plain old JOIN (which should  
> give equivalent results by virtue of the WHERE clause restricting  
> ti1.topic_textID to not be NULL) then it should run in O(logN) - much  
> faster.  Try it and let me know.

Indeed. 0-10 milliseconds instead of 500-800 :-)

But may you tell me why this works and where you have this information? 
I know the O-notation but I do not know /why/ this boosts down to log(n)...

I have other queries which worry me. But that trick did not help in 
these cases :-(

Especially I have problems with a self-join. In a table I have defined 
groups of elements ("printgroup"):

CREATE TABLE printgroup(
topicID INTEGER,
printgroup INTEGER,
PRIMARY KEY(topicID, printgroup)
);

I think these indices are not necessary because both fields are primary 
keys anyway.
CREATE INDEX topicID ON printgroup(topicID);
CREATE INDEX pprintgroup ON printgroup(printgroup);

When I know one element of a group (given by topicID) I want to find all 
other elements in the same group:

SELECT t.topic, t.length
FROM printgroup AS pg1
LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup
LEFT JOIN topics AS t ON t.topicID = pg2.topicID
LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID
WHERE ti.topic_textID = ''
ORDER BY pg2.topicID ASC;

The table "topics" just contains the actual data for each topicID 
(t.topic with length t.length).

This query takes a few seconds (und to minutes) with "sqlite3.exe" and 
even much longer in my application (sqlite with CppSQlite3): Up to 15 
minutes!

Mimicking your magic above I tried to leave out the "LEFT" in the 
self-joins but it did not change anything :-(

And unfortunately, the optimization FAQ [1] is very incomplete, at least 
at the interesting points (indices) :-(


Thank you again and best regards,
Luke


[1] http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

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

Re: [sqlite] Collation not used

[Martin Engelschalk]

Igor Tandetnik wrote:
> Martin Engelschalk
>  wrote:
>   
>> The problem appeared when my users eliminated leading blanks from the
>> data.
>> 
>
> I'm not sure I understand. What does this have to do with collating 
> numbers? Are you saying you were storing numbers as strings, with 
> leading blanks, and had a collation function to actually compare them as 
> numbers? Why?
>   
My collation does not compare the data as numbers but interpreted them 
in a different way. Comes from a bad data design :-)
>   
>> Is it a good idea that the actual type of the data influences whether
>> it is compared with the collaction function or not?
>> 
>
> Well, collation functions as they exist today take char* or wchar_t* as 
> a parameter, and no type information. How would you pass a number to it? 
> Maybe it would have been wise to design them to accept sqlite3_value* 
> pointers as parameters, the way custom functions do. But that wasn't 
> done, so it is what it is.
>   
The way the data is passed to the function is not a problem for me, but 
the fact that the function is not called if all the data is numerical.
> If for whatever reason you do want to compare numbers as strings, you 
> can write
>
> order by Cast(NumberColumn as string) collate MyCollation
>   
This is a very good idea! It might just give me back my weekend!
> 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] KEY keyword

[Igor Tandetnik]

ed  wrote:
> however, I am still not clear if the conflict resolution clause will
> be used
> on a table with no UNIQUE or PRIMARY KEY columns?

It may if there are other constraints, such as CHECK or NOT NULL. For 
more details, see http://sqlite.org/lang_conflict.html

Igor Tandetnik 



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

Re: [sqlite] Bool Values

[Griggs, Donald]

A design constraint of SQLITE was to avoid adding a bunch of bool
present in other DB's.

(Bad joke, couldn't resist) 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Emil Obermayr
Sent: Friday, January 09, 2009 2:58 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Bool Values

Is there a way to use bool-values to make migration from other DB
easier?

e.g.

select * from address where local = true

like defining true as a contant that represents a numeric 1?
___
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] Collation not used

[Igor Tandetnik]

Martin Engelschalk
 wrote:
> The problem appeared when my users eliminated leading blanks from the
> data.

I'm not sure I understand. What does this have to do with collating 
numbers? Are you saying you were storing numbers as strings, with 
leading blanks, and had a collation function to actually compare them as 
numbers? Why?

> Is it a good idea that the actual type of the data influences whether
> it is compared with the collaction function or not?

Well, collation functions as they exist today take char* or wchar_t* as 
a parameter, and no type information. How would you pass a number to it? 
Maybe it would have been wise to design them to accept sqlite3_value* 
pointers as parameters, the way custom functions do. But that wasn't 
done, so it is what it is.

If for whatever reason you do want to compare numbers as strings, you 
can write

order by Cast(NumberColumn as string) collate MyCollation

Igor Tandetnik 



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

[sqlite] Why the difference in these 2 SQLite files?

[RB Smissaert]

Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.

Now the thing I don't understand.
I run the following query (on the mobile device):

SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)

There is an index on field PATIENT_ID.

Takes query takes quite long, say about 1 second.

Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an index, again in a small table, completely unrelated to the above
query and I see the same speed gain. I have compared the 2 different
database files, so stats, all the different pragma's etc. and I can't see
the difference. I have also compared the SQLite query plans and they are the
same (using the index) for both files. So what possibly could explain the
difference in speed?

I know it is a bit of a long-winded question, but maybe somebody has some
idea what is going on here and if so, very grateful for that as I can't see
it.


RBS





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

[sqlite] Bool Values

[Emil Obermayr]

Is there a way to use bool-values to make migration from other DB easier?

e.g.

select * from address where local = true

like defining true as a contant that represents a numeric 1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] KEY keyword

[ed]

Igor,thank you for the explanation

however, I am still not clear if the conflict resolution clause will be used
on a table with no UNIQUE or PRIMARY KEY columns?
I would assume not but i couldn't find the answer in the documentation.

thanks,
ed


On Fri, Jan 9, 2009 at 11:17 AM, Igor Tandetnik  wrote:

> ed  wrote:
> > I have a sqlite 3.3.4 app using a db with the following schema:
> >
> > CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR);
> >
> > Is the KEY keyword utilized?
>
> The way this statement is parsed, column 'n' has the type of 'INTEGER
> KEY'. Remember, SQLite allows almost any odd sequence of identifiers as
> a type name (except that it can't contain certain keywords that
> introduce column-constraint clause, e.g. PRIMARY). You could just as
> well write
>
> CREATE TABLE my_data(n I LOVE SQLITE, ...);
>
> (try it - it does work).
>
> > Will it act the same as a primary key ?
>
> No.
>
> > Ultimately, i'm trying to determine if the KEY will enforce a unique
> > constraint
>
> No. But PRIMARY KEY or UNIQUE will.
>
> > such that the following insert's conflict clause would
> > even be necessary.
>
> Conflict resolution clause is never necessary. If none is specified, the
> default of ABORT is used.
> --
> With best wishes,
>Igor Tandetnik
>
> With sufficient thrust, pigs fly just fine. However, this is not
> necessarily a good idea. It is hard to be sure where they are going to
> land, and it could be dangerous sitting under them as they fly
> overhead. -- RFC 1925
>
>
>
> ___
> 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] KEY keyword

[Kees Nuyt]

On Fri, 9 Jan 2009 10:05:49 -0800, ed 
wrote in General Discussion of SQLite Database
:

>Hello,
>I have a sqlite 3.3.4 app using a db with the following schema:
>
>CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR);
>
>Is the KEY keyword utilized?

According to the syntax diagrams
http://www.sqlite.org/lang_createtable.html it isn't.

>Will it act the same as a primary key ?

If you need column n to be a primary key, define my_data as:
 
CREATE TABLE my_data(
n INTEGER PRIMARY KEY, 
s INTEGER, 
p INTEGER, 
od VARCHAR
); 
-- this is the preferred, and mosst efficient form.
-- http://www.sqlite.org/lang_createtable.html 
-- tells why

or 

CREATE TABLE my_data(
n INTEGER UNIQUE, 
s INTEGER, 
p INTEGER, 
od VARCHAR
);
-- this will work, but it will be a little bit slower.


>Ultimately, i'm trying to determine if the KEY will enforce 
>a unique constraint, such that the following insert's 
>conflict clause would even be necessary.
>
>INSERT OR REPLACE INTO elevator_data 
>(id, schedule, panel, output_data) VALUES 

That depends on what you need.

Assuming the "n INTEGER PRIMARY KEY" definition above,

INSERT INTO elevator_data 
(id, schedule, panel, output_data) VALUES ..;
will throw an error if you try to insert a duplicate key.

INSERT OR REPLACE INTO elevator_data 
(id, schedule, panel, output_data) VALUES ..;
will delete the original row and insert the new one.
None of the original columns values in the affected row will
survive.

>thanks,
>ed
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation not used

[Martin Engelschalk]

Hello Igor,

in the beginning this was an attempt to circumvent the missing DESC 
Indices prior to Version 3.3.0. However, it all grew and now i do all 
kinds of things using collations. I admit that i could achieve most of 
them in a different way, but i would have to change my application.
The problem appeared when my users eliminated leading blanks from the data.

Is it a good idea that the actual type of the data influences whether it 
is compared with the collaction function or not? Imangine a table with a 
field of normally mixed numerical an not-numerical data. If i want to 
impose my own ordering, i can do this only as long as i am sure that not 
all rows have a numerical field value.

But I really do not want to criticise. Now I know how the system works, 
I can program a solution.

Thanks,
Martin

Igor Tandetnik wrote:
> Martin.Engelschalk
>  wrote:
>   
>> yes, you are right, Thank you.
>> Is there a reason for this?
>> 
>
> Why would you want a collation function for numbers? What are you trying 
> to achieve?
>
> 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] Memory loss with sqlite3_step()

[John Delaney]

Hi,
 
I am a new user so please make allowances accordingly.
 
Using SQLite3 3.5.9, I am seeing a consistent rise in memory with each call to 
sqlite3_step().
 
My code pattern to store a 14830 byte buffer is:
 
A. mem = sqlite3_memory_used();
sqlite3_prepare_v2(...
B. mem = sqlite3_memory_used();
sqlite3_bind_blob(..,..,..SQLITE_STATIC);
C. mem = sqlite3_memory_used();
sqlite3_step(..
D. mem = sqlite3_memory_used();
sqlite3_finalize(
E. mem = sqlite3_memory_used();
 
This sequence of calls is repeated on the same connection and ignoring the 
first run, the memory stat returned at each stage indicates a loss of 17460 
bytes. The values for the second and third runs are:
 
A. 32588  50048
B  34533  51993
C. 34533  51993
D. 67059  84519
E. 50048  67508
 
Calling sqlite3_bind_blob() with SQLITE_TRANSIENT causes as extra malloc but 
the end result is the same - i.e. a memory loss of 17460 bytes.  
 
A. 32588  50048
B. 34533  51993
C. 49363  66823
D. 81889  99349
E. 50048  67508
 
  
However. if I comment out the call to sqlite3_step() then I do not see any 
memory loss either with SQLITE_STATIC or SQLITE_TRANSIENT.
 
This is basic functionality so I am doing something wrong. The docs thoough do 
not throw any light on the matter.
 
Any help or guidance is greatly appreciated.
 
Regards,
 
John D.
 
_
What can you do with the new Windows Live? Find out
http://www.microsoft.com/windows/windowslive/default.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a Datatable [first steps of newbie]

[Roosevelt Anderson]

What does your connection string look like?

On Fri, Jan 9, 2009 at 2:36 PM, Alessio Forconi  wrote:
> Thanks for your suggestion, I do not think that is the problem because
> the connection is the one used with success in other parts of the
> program, you show the code that I wrote following your suggestions:
>
> string sqlString = "SELECT * FROM Proclamatori";
> conn = new SQLiteConnection(connString);
> try
> {
> conn.Open();
> SQLiteDataAdapter apt = new SQLiteDataAdapter(sqlString, conn);
> DataTable dtProclamatori = new DataTable();
> apt.Fill(dtProclamatori);
> }
> catch (SQLiteException ex)
> {
>  MessageBox.Show(ex.Message);
> }
> finally
> {
> conn.Close();
>  }
>
>
> I know that there are groups for microsoft c # but that does not seem
> like a software applications that are not microsoft: (
>
> Roosevelt Anderson ha scritto:
>> It should be "Data Source=database.db" not "DataSource=database.db". I
>> didn't realize this because of the line break in the email. This will
>> create a new, empty database named database.db in the directory where
>> your application is running.
>>
>> On Fri, Jan 9, 2009 at 7:32 AM, Alessio Forconi  wrote:
>>
>>> Thanks...
>>>
>>> I get this error
>>>
>>> "Data Source cannot be empty. Use :memory: to open an in-memory database"
>>>
>>> but the connection is sattamante the same as that used in other parts of
>>> the program
>>>
>>> Roosevelt Anderson ha scritto:
>>>
 If you trying to extract data from a sqlite database to a DataTable
 here is how you do it using the .Net data provider:

SQLiteConnection conn = new SQLiteConnection("Data
 Source=database.db");
 conn.Open();
   SQLiteDataAdapter apt = new SQLiteDataAdapter("select * from 
 tbl", conn);
 DataTable dt = new DataTable();
 apt.Fill(dt);
   conn.Close();




 On Thu, Jan 8, 2009 at 11:28 AM, Alessio Forconi  
 wrote:


> Hello everyone,
>
> I am making the first steps with the programming and I want to help
> create a DataTable from a database sqlite with C #.
>
> Can you give me an example of how do I create it without using a
> dataset?
>
> Thank you very much for your 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
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a Datatable [first steps of newbie]

[Alessio Forconi]

Thanks for your suggestion, I do not think that is the problem because 
the connection is the one used with success in other parts of the 
program, you show the code that I wrote following your suggestions:

string sqlString = "SELECT * FROM Proclamatori";
conn = new SQLiteConnection(connString);
try
{
 conn.Open();
 SQLiteDataAdapter apt = new SQLiteDataAdapter(sqlString, conn);
 DataTable dtProclamatori = new DataTable();
 apt.Fill(dtProclamatori);
}
catch (SQLiteException ex)
{
  MessageBox.Show(ex.Message);
}
finally
{
 conn.Close();
 }


I know that there are groups for microsoft c # but that does not seem 
like a software applications that are not microsoft: (

Roosevelt Anderson ha scritto:
> It should be "Data Source=database.db" not "DataSource=database.db". I
> didn't realize this because of the line break in the email. This will
> create a new, empty database named database.db in the directory where
> your application is running.
>
> On Fri, Jan 9, 2009 at 7:32 AM, Alessio Forconi  wrote:
>   
>> Thanks...
>>
>> I get this error
>>
>> "Data Source cannot be empty. Use :memory: to open an in-memory database"
>>
>> but the connection is sattamante the same as that used in other parts of
>> the program
>>
>> Roosevelt Anderson ha scritto:
>> 
>>> If you trying to extract data from a sqlite database to a DataTable
>>> here is how you do it using the .Net data provider:
>>>
>>>SQLiteConnection conn = new SQLiteConnection("Data
>>> Source=database.db");
>>> conn.Open();
>>>   SQLiteDataAdapter apt = new SQLiteDataAdapter("select * from 
>>> tbl", conn);
>>> DataTable dt = new DataTable();
>>> apt.Fill(dt);
>>>   conn.Close();
>>>
>>>
>>>
>>>
>>> On Thu, Jan 8, 2009 at 11:28 AM, Alessio Forconi  wrote:
>>>
>>>   
 Hello everyone,

 I am making the first steps with the programming and I want to help
 create a DataTable from a database sqlite with C #.

 Can you give me an example of how do I create it without using a
 dataset?

 Thank you very much for your 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
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collation not used

[Igor Tandetnik]

Martin.Engelschalk
 wrote:
> yes, you are right, Thank you.
> Is there a reason for this?

Why would you want a collation function for numbers? What are you trying 
to achieve?

Igor Tandetnik



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

Re: [sqlite] KEY keyword

[Igor Tandetnik]

ed  wrote:
> I have a sqlite 3.3.4 app using a db with the following schema:
>
> CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR);
>
> Is the KEY keyword utilized?

The way this statement is parsed, column 'n' has the type of 'INTEGER 
KEY'. Remember, SQLite allows almost any odd sequence of identifiers as 
a type name (except that it can't contain certain keywords that 
introduce column-constraint clause, e.g. PRIMARY). You could just as 
well write

CREATE TABLE my_data(n I LOVE SQLITE, ...);

(try it - it does work).

> Will it act the same as a primary key ?

No.

> Ultimately, i'm trying to determine if the KEY will enforce a unique
> constraint

No. But PRIMARY KEY or UNIQUE will.

> such that the following insert's conflict clause would
> even be necessary.

Conflict resolution clause is never necessary. If none is specified, the 
default of ABORT is used.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925



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

[sqlite] Hi, a question from Colombia

[Carlos Suarez]

Hi, my name is Carlos Suarez from Colombia, I have a few problems while 
I migrated from hsql to sqlite and I want to know if you can help me 
with some of this stuff by this way of mail or have I  to quote in 
somewhere?.

thanksfully

Carlos Suarez


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

Re: [sqlite] update PHP

[Emil Obermayr]

Am Donnerstag, 8. Januar 2009 schrieb Kees Nuyt:

> Note: With Apache, the easiest location for sqlite3.dll is
> the .../apache/bin directory. php_pdo will find it there.
> Perhaps the same goes for lighttpd.

It works with sqlite3.dll in die PHP-directory.

Thanks for that information. Everything works nicely now.

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

[sqlite] KEY keyword

[ed]

Hello,
I have a sqlite 3.3.4 app using a db with the following schema:

CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR);

Is the KEY keyword utilized?
Will it act the same as a primary key ?


Ultimately, i'm trying to determine if the KEY will enforce a unique
constraint, such that the following insert's conflict clause would even be
necessary.

INSERT OR REPLACE INTO elevator_data (id, schedule, panel,
output_data) VALUES 


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

Re: [sqlite] How to turn off Indexing in SQLite?

[Ken]

 
> 
> I am using SQLite and C interface.
> 
>  
> 
>  How to turn off Indexing in SQLite?


create the SQL so the index can not be used 
add an " OR ( 1 =  0)  to the end of the where clause.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a Datatable [first steps of newbie]

[Roosevelt Anderson]

It should be "Data Source=database.db" not "DataSource=database.db". I
didn't realize this because of the line break in the email. This will
create a new, empty database named database.db in the directory where
your application is running.

On Fri, Jan 9, 2009 at 7:32 AM, Alessio Forconi  wrote:
> Thanks...
>
> I get this error
>
> "Data Source cannot be empty. Use :memory: to open an in-memory database"
>
> but the connection is sattamante the same as that used in other parts of
> the program
>
> Roosevelt Anderson ha scritto:
>> If you trying to extract data from a sqlite database to a DataTable
>> here is how you do it using the .Net data provider:
>>
>>SQLiteConnection conn = new SQLiteConnection("Data
>> Source=database.db");
>> conn.Open();
>>   SQLiteDataAdapter apt = new SQLiteDataAdapter("select * from tbl", 
>> conn);
>> DataTable dt = new DataTable();
>> apt.Fill(dt);
>>   conn.Close();
>>
>>
>>
>>
>> On Thu, Jan 8, 2009 at 11:28 AM, Alessio Forconi  wrote:
>>
>>> Hello everyone,
>>>
>>> I am making the first steps with the programming and I want to help
>>> create a DataTable from a database sqlite with C #.
>>>
>>> Can you give me an example of how do I create it without using a
>>> dataset?
>>>
>>> Thank you very much for your 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] Retrieving NANs

[Sander Jansen]

On Thu, Jan 8, 2009 at 7:03 PM, Gerry Snyder  wrote:
> Sander Jansen wrote:
>> I need to store NaNs in my database and able to retrieve them as well.
>>
>> Since sqlite will give me back 0.0 when I call sqlite3_column_double
>> and a result contains a NAN,
>> I was wondering what the best way is to retrieve a NAN from the database.
>>
>> Here's what I currently do in peudo code:
>>
>> if (sqlite3_column_type(column)==SQLITE_FLOAT)
>>value = sqlite3_column_double(column);
>> else
>>value = NAN;
>>
>> Now, the doc says that
>>
>> "The value returned by sqlite3_column_type() is only meaningful if no
>> type conversions have occurred as described below."
>>
>> Am I correctly assuming the "no type conversions have occurred"  means
>> "no type conversions on that (row,column) of the result set"? I mean
>> next time I call sqlite3_column_type() on the same column but on the
>> next row of the result set, it will still give the correct answer?
>>
>> Thanks,
>>
>> Sander
>>
> That sounds correct, yes.
>
> One suggestion--if the column will always have either a float or NAN,
> why not just not insert anything if you have NAN, and then test for NULL?
>

I see your point, but the query is already a prepared statement. So
having a double as input, I can either check for a NAN and do a
bind_null in that case, or just don't check it at all and do a
bind_double. Even in your suggested case, I would need to check
whether or not the double is a NaN or not. So I take it the easy way
and always do a bind_double.

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

Re: [sqlite] Collation not used

[Martin.Engelschalk]

Hello Dan,

yes, you are right, Thank you.
Is there a reason for this?

martin

Dan schrieb:
> On Jan 9, 2009, at 9:43 PM, Martin.Engelschalk wrote:
>
>   
>> Hello list,
>>
>> I definied a collation and used it in the order by - clauses of  
>> queries.
>> In one query, sqlite calls the collation function, and in the other
>> query, it does not (i checked by inserting a printf inside the  
>> collation
>> function).
>> The queries differ only in the order by - clause.
>>
>> This query Works OK:
>>
>> select d1._recno,
>>   d1._source_id,
>>   d1._source_position,
>>   d1._source_len,
>>   d1._source_lfd,
>>   d1._list_id,
>>   d1._list_lfd,
>>   d1._country,
>>   coalesce(d2._skip,d1._skip) _skip ,
>>   coalesce(d1._skip,d2._skip) _state_num ,
>>   d2.f_mail2Group f_mail2Group,
>>   d2.f_mail2Code f_mail2Code,
>>   d2.f_mail2Stat f_mail2Stat,
>>   d1.f_FirstName f_FirstName,
>>   d1.f_LastName f_LastName,
>>   d1.f_StreetLine f_StreetLine,
>>   d1.f_Zip f_Zip,
>>   d1.f_CityName f_CityName,
>>   d2.f_Bewertung f_Bewertung,
>>   d2.f_Dublettengruppennummer f_Dublettengruppennummer,
>>   d2.f_Dublettentyp f_Dublettentyp
>>  from data1 d1 left outer join data2 d2 on d2._recno = d1._recno
>> order by 11 collate DQS_NUM_ASC, 13 collate DQS_NUM_ASC, 12 collate
>> DQS_NUM_ASC
>>
>> If I change the oder by - clause to the following, the collation
>> function ist not called, and the result list is sorted in the standard
>> order.
>>
>> order by 20 collate DQS_NUM_ASC, 21 collate DQS_NUM_ASC, 19 collate
>> DQS_NUM_ASC
>>
>> All fields of the table "data2 d2" are defined as type "integer"
>> My version is 3.2.5.
>>
>> Does anyone have an idea? Might it be a good idea to upgrade to the
>> newest version? I want do do this only if really necessary.
>> 
>
> The collation function is only called for sorting (or comparing) text
> values. Is it possible that the second set of columns are entirely
> populated with numbers, blobs and nulls?
>
>
> ___
> 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] Collation not used

[Dan]

On Jan 9, 2009, at 9:43 PM, Martin.Engelschalk wrote:

> Hello list,
>
> I definied a collation and used it in the order by - clauses of  
> queries.
> In one query, sqlite calls the collation function, and in the other
> query, it does not (i checked by inserting a printf inside the  
> collation
> function).
> The queries differ only in the order by - clause.
>
> This query Works OK:
>
> select d1._recno,
>   d1._source_id,
>   d1._source_position,
>   d1._source_len,
>   d1._source_lfd,
>   d1._list_id,
>   d1._list_lfd,
>   d1._country,
>   coalesce(d2._skip,d1._skip) _skip ,
>   coalesce(d1._skip,d2._skip) _state_num ,
>   d2.f_mail2Group f_mail2Group,
>   d2.f_mail2Code f_mail2Code,
>   d2.f_mail2Stat f_mail2Stat,
>   d1.f_FirstName f_FirstName,
>   d1.f_LastName f_LastName,
>   d1.f_StreetLine f_StreetLine,
>   d1.f_Zip f_Zip,
>   d1.f_CityName f_CityName,
>   d2.f_Bewertung f_Bewertung,
>   d2.f_Dublettengruppennummer f_Dublettengruppennummer,
>   d2.f_Dublettentyp f_Dublettentyp
>  from data1 d1 left outer join data2 d2 on d2._recno = d1._recno
> order by 11 collate DQS_NUM_ASC, 13 collate DQS_NUM_ASC, 12 collate
> DQS_NUM_ASC
>
> If I change the oder by - clause to the following, the collation
> function ist not called, and the result list is sorted in the standard
> order.
>
> order by 20 collate DQS_NUM_ASC, 21 collate DQS_NUM_ASC, 19 collate
> DQS_NUM_ASC
>
> All fields of the table "data2 d2" are defined as type "integer"
> My version is 3.2.5.
>
> Does anyone have an idea? Might it be a good idea to upgrade to the
> newest version? I want do do this only if really necessary.

The collation function is only called for sorting (or comparing) text
values. Is it possible that the second set of columns are entirely
populated with numbers, blobs and nulls?


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

[sqlite] Collation not used

[Martin.Engelschalk]

Hello list,

I definied a collation and used it in the order by - clauses of queries. 
In one query, sqlite calls the collation function, and in the other 
query, it does not (i checked by inserting a printf inside the collation 
function).
The queries differ only in the order by - clause.

This query Works OK:

select d1._recno,
   d1._source_id,
   d1._source_position,
   d1._source_len,
   d1._source_lfd,
   d1._list_id,
   d1._list_lfd,
   d1._country,
   coalesce(d2._skip,d1._skip) _skip ,
   coalesce(d1._skip,d2._skip) _state_num ,
   d2.f_mail2Group f_mail2Group,
   d2.f_mail2Code f_mail2Code,
   d2.f_mail2Stat f_mail2Stat,
   d1.f_FirstName f_FirstName,
   d1.f_LastName f_LastName,
   d1.f_StreetLine f_StreetLine,
   d1.f_Zip f_Zip,
   d1.f_CityName f_CityName,
   d2.f_Bewertung f_Bewertung,
   d2.f_Dublettengruppennummer f_Dublettengruppennummer,
   d2.f_Dublettentyp f_Dublettentyp
  from data1 d1 left outer join data2 d2 on d2._recno = d1._recno
order by 11 collate DQS_NUM_ASC, 13 collate DQS_NUM_ASC, 12 collate 
DQS_NUM_ASC

If I change the oder by - clause to the following, the collation 
function ist not called, and the result list is sorted in the standard 
order.

order by 20 collate DQS_NUM_ASC, 21 collate DQS_NUM_ASC, 19 collate 
DQS_NUM_ASC

All fields of the table "data2 d2" are defined as type "integer"
My version is 3.2.5.

Does anyone have an idea? Might it be a good idea to upgrade to the 
newest version? I want do do this only if really necessary.

Thanks,
Martin


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

Re: [sqlite] 50 inserts takeing 8 seconds

[Jay A. Kreibich]

On Thu, Jan 08, 2009 at 03:45:26PM +0530, Pramoda M. A scratched on the wall:
> Hi all.
> 
> My desktop system with taking 8 seconds to insert 100 rows with only one
> column...

  BEGIN
  
  INSERT...
  INSERT...
  INSERT...
  INSERT...
  INSERT...
  ...

  COMMIT


On Thu, Jan 08, 2009 at 02:30:25PM +0530, Pramoda M. A scratched on the wall:
> Hi All,
>
> I am using SQLite and C interface.  
>
> How to turn off Indexing in SQLite?

  DROP INDEX



   -j
 
-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to turn off Indexing in SQLite?

[Igor Tandetnik]

"Pramoda M. A" 
wrote in message
news:f7846b8f3c78c049b6a1dff861f6c16f03115...@kcinblrexb01.kpit.com
> How to turn off Indexing in SQLite?

Don't create any indexes on any of your tables.

Igor Tandetnik 



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

Re: [sqlite] How to create a Datatable [first steps of newbie]

[Alessio Forconi]

Thanks...

I get this error

"Data Source cannot be empty. Use :memory: to open an in-memory database"

but the connection is sattamante the same as that used in other parts of 
the program

Roosevelt Anderson ha scritto:
> If you trying to extract data from a sqlite database to a DataTable
> here is how you do it using the .Net data provider:
>
>SQLiteConnection conn = new SQLiteConnection("Data
> Source=database.db");
> conn.Open();
>   SQLiteDataAdapter apt = new SQLiteDataAdapter("select * from tbl", 
> conn);
> DataTable dt = new DataTable();
> apt.Fill(dt);
>   conn.Close();
>
>
>
>
> On Thu, Jan 8, 2009 at 11:28 AM, Alessio Forconi  wrote:
>   
>> Hello everyone,
>>
>> I am making the first steps with the programming and I want to help
>> create a DataTable from a database sqlite with C #.
>>
>> Can you give me an example of how do I create it without using a
>> dataset?
>>
>> Thank you very much for your 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] How to turn off Indexing in SQLite?

[Pramoda M. A]

Hi All,

 

I am using SQLite and C interface.

 

 How to turn off Indexing in SQLite?

 

 

 

 

 

With Regards,

Pramoda.M.A

KPIT Cummins Infosystems Limited | Bengaluru

 

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

Re: [sqlite] Transfer data between databases

[Chris Wedgwood]

On Fri, Jan 09, 2009 at 09:18:09AM +0100, Pierre Chatelier wrote:

> I have two separate SQLite database files, but containing the same
> kind of tables. Is there a quick way to copy rows from one table of
> a file to the same table of the other file ?

ATTACH DATABASE 'fromdb.sqlite' AS fromdb;
[...]
INSERT INTO fromdb.tablename SELECT * FROM src;


(attach & prefix 'src' as needed)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 50 inserts takeing 8 seconds

[Pramoda M. A]

Hi all.

 

My desktop system with taking 8 seconds to insert 100 rows with only one
column...

 

I am inserting using Ffor loop (C interface)

 

Can anybody help me?

 

 

 

With Regards,

Pramoda.M.A

KPIT Cummins Infosystems Limited | Bengaluru

 

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

[sqlite] .dump and transactions

[Thomas Jarosch]

Hello together,

I run a small script every night via cron to backup a database
using the ".dump" statement. SQlite version is 3.6.6.2 on Linux.

Normally this script works fine and from time to time
I get a backup file that looks like this:
---
BEGIN TRANSACTION;
END TRANSACTION;
---

Consider you have a database with a simple table.
Acccess the database using the command line "sqlite3" tool
and do
-
BEGIN TRANSACTION;
INSERT INTO simple_table (xyz) ...
-

Now open another, concurring instance of the sqlite3 command line tool
and issue a ".dump" command. It will then produce the empty
transaction output mentioned first.

Shouldn't the command line tool wait for a locking timeout
or atleast return BUSY when it can't dump the database?

Is there a better way to backup the database?

Thanks in advance,
Thomas

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

Re: [sqlite] Transfer data between databases

[Martin.Engelschalk]

Hi Pierre,

you can open both database files at the same time and using the same 
connection: Look at the attach - Command:
http://www.sqlite.org/lang_attach.html
You can then use both databases in the same statement (insert into 
MyTable (... columns ...) select ... columns ... from 
MyOtherDatabase.MyTable where ...)

Martin

Pierre Chatelier schrieb:
> Hello,
>
> This may be a question with a very short answer...
> I have two separate SQLite database files, but containing the same  
> kind of tables. Is there a quick way to copy rows from one table of a  
> file to the same table of the other file ?
> I suppose "no", and I will have to perform SELECT on one side and  
> INSERT on the other. But since one of my column is a blob type, it is  
> a little more pain than a simple string copy, because for performance  
> I should have to handle the blob with the read/write functions. Right ?
>
> Anyway, are there tricks to know to make such a row transfert simple  
> and efficient ?
>
> Regards,
>
> Pierre Chatelier
> ___
> 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] Transfer data between databases

[Simon Davies]

2009/1/9 Pierre Chatelier :
> Hello,
>
> This may be a question with a very short answer...
> I have two separate SQLite database files, but containing the same
> kind of tables. Is there a quick way to copy rows from one table of a
> file to the same table of the other file ?
> I suppose "no", and I will have to perform SELECT on one side and
> INSERT on the other. But since one of my column is a blob type, it is
> a little more pain than a simple string copy, because for performance
> I should have to handle the blob with the read/write functions. Right ?
>
> Anyway, are there tricks to know to make such a row transfert simple
> and efficient ?
>

Something like

 attach 'tst2.db' as db2;
 insert into db2.tbl1 select * from main.tbl1;

> Regards,
>
> Pierre Chatelier

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

[sqlite] Transfer data between databases

[Pierre Chatelier]

Hello,

This may be a question with a very short answer...
I have two separate SQLite database files, but containing the same  
kind of tables. Is there a quick way to copy rows from one table of a  
file to the same table of the other file ?
I suppose "no", and I will have to perform SELECT on one side and  
INSERT on the other. But since one of my column is a blob type, it is  
a little more pain than a simple string copy, because for performance  
I should have to handle the blob with the read/write functions. Right ?

Anyway, are there tricks to know to make such a row transfert simple  
and efficient ?

Regards,

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