Re: [sqlite] Table within a table??

[mark m]

Well, you pretty much guessed right on just about everything going through
my head.  I am brand new to database programming and have been trying to
learn
just enough to accomplish what I want to do within my own app.

The app I'm working on has evolved over the years into something that could
now
benefit from something more sophisticated than text file data storage.  Your
explanations
were EXTREMELY helpful.  If anything it gives me the confidence that I'm
going down
the right road.  If not for the great help from everyone in this thread I
might have abandoned
this effort altogether.  My developer instincts were telling me that I was
missing
something important or asking the wrong questions.

Thanks again for all the help.

P.S. your developer vs. database perspective should be a sticky or FAQ for
other newbies on this mailing list.


Mark

On Sat, Oct 31, 2009 at 9:32 PM, Jay A. Kreibich  wrote:

>
> On Sat, Oct 31, 2009 at 01:13:31AM -0400, mark m scratched on the wall:
> > O.K.  I think I am starting to get the idea.  It is just so foreign for
> me
> > to organize things this way. A master work history table for all cases
> > almost seems confusing.  It will just take a bit of adjustment for me
> > to "trust" the database way of doing things.  Text files organized in
> > the way I described has always made it easy for me to figure things
> > out when there was a problem.
>
>   This is not uncommon.  If I had to guess, I'd say you're an experienced
>  applications developer, but are somewhat new to databases.  You're
>  applying your instincts and experience in designing and laying out
>  runtime-data structures and classes to your database design, but find
>  yourself stumbling over some of the minor conceptual differences in
>  this new environment.
>
>  The good news is that this is normal, and nearly everyone makes these
>  mistakes.  The great news is that I've found much of that experience
>  can be utilized and successfully applied to database design as soon
>  as you wrap your head around a few minor differences.
>
>  The biggest stumbling block-- and the one you've hit head-on here--
>  is to think of tables as *instances* of compound data structures.  They
>  look a lot like arrays or dynamic lists, so this is an easy mistake.
>
>  In your case, you need a bunch of records associated with some other
>  table row, so the instinct is to create a brand new table to fit that
>  need.  You'll end up with a bunch of tables with the exact same
>  type-signature (column/type/name pattern), but that's how instances
>  of data structures or classes work-- this is exactly what you'd do
>  if that table was some fancy C++ class that offered a dynamic list
>  of elements.  It also provides for a clear and direct route to get
>  from a main data record to a collection of related sub-members.
>
>  Unfortunately, in the database world, that's the wrong concept and,
>  as others have pointed out, this will lead to no end of problems.
>
>  Here is the first big rule, and it alone will get you pretty far:
>
>Tables are data structures and classes.
>
>  If you would define a data structure or class to fit some specific
>  and well defined purpose, that's a table.  The .sql file that
>  defines your tables should map to the .h file in your head.
>
>  Don't think of tables themselves as multi-element data structures,
>  think of them as the data structures definitions.  It just happens that
>  in the database environment, every instance of a particular data
>  structure (i.e. a row) happens to be managed by a global instance pool.
>
>  In extension of that, the database world has no "contains a."   All
>  your compound structures are built from "references a."  And the
>  references that bind everything together are not pointers, but ID
>  values.
>
>  The other big stumbling block is that most of these references are
>  "backwards".  If you were building this as an application, you'd have
>  your main records, and each one of those would have a pointer off to
>  some dynamic list or array that held the log records.  In the database
>  world, the main record simply exists with some type of unique
>  identifier.  Rather than the main record pointing to the associated log
>  records, the log records point back and the main record they're
>  associated with.
>
>  This tends to make application developers uncomfortable.  If you had
>  a main record and wanted a list of all the log records associated
>  with it, you'd want to be able to de-reference some pointer or other
>  association directly to some container item, like an array or list of
>  log records.  This is generally what leads to the desire to build
>  multiple tables of the same type, because tables look a lot like
>  instances of a container item.  You're thinking of those tables as
>  *instances* of multi-element data structures, rather than a global
>  collection of all sub-instances of that type.
>
>  That's pretty natural.  After all, it 

Re: [sqlite] Table within a table??

[Jay A. Kreibich]

On Sat, Oct 31, 2009 at 01:13:31AM -0400, mark m scratched on the wall:
> O.K.  I think I am starting to get the idea.  It is just so foreign for me
> to organize things this way. A master work history table for all cases
> almost seems confusing.  It will just take a bit of adjustment for me
> to "trust" the database way of doing things.  Text files organized in
> the way I described has always made it easy for me to figure things
> out when there was a problem.

  This is not uncommon.  If I had to guess, I'd say you're an experienced
  applications developer, but are somewhat new to databases.  You're
  applying your instincts and experience in designing and laying out
  runtime-data structures and classes to your database design, but find
  yourself stumbling over some of the minor conceptual differences in
  this new environment.

  The good news is that this is normal, and nearly everyone makes these
  mistakes.  The great news is that I've found much of that experience
  can be utilized and successfully applied to database design as soon
  as you wrap your head around a few minor differences.

  The biggest stumbling block-- and the one you've hit head-on here--
  is to think of tables as *instances* of compound data structures.  They
  look a lot like arrays or dynamic lists, so this is an easy mistake.

  In your case, you need a bunch of records associated with some other
  table row, so the instinct is to create a brand new table to fit that
  need.  You'll end up with a bunch of tables with the exact same
  type-signature (column/type/name pattern), but that's how instances
  of data structures or classes work-- this is exactly what you'd do
  if that table was some fancy C++ class that offered a dynamic list
  of elements.  It also provides for a clear and direct route to get
  from a main data record to a collection of related sub-members.

  Unfortunately, in the database world, that's the wrong concept and,
  as others have pointed out, this will lead to no end of problems.
 
  Here is the first big rule, and it alone will get you pretty far:

Tables are data structures and classes.
  
  If you would define a data structure or class to fit some specific
  and well defined purpose, that's a table.  The .sql file that
  defines your tables should map to the .h file in your head.  
  
  Don't think of tables themselves as multi-element data structures,
  think of them as the data structures definitions.  It just happens that
  in the database environment, every instance of a particular data
  structure (i.e. a row) happens to be managed by a global instance pool.
  
  In extension of that, the database world has no "contains a."   All
  your compound structures are built from "references a."  And the
  references that bind everything together are not pointers, but ID
  values.

  The other big stumbling block is that most of these references are
  "backwards".  If you were building this as an application, you'd have
  your main records, and each one of those would have a pointer off to
  some dynamic list or array that held the log records.  In the database
  world, the main record simply exists with some type of unique
  identifier.  Rather than the main record pointing to the associated log
  records, the log records point back and the main record they're
  associated with.

  This tends to make application developers uncomfortable.  If you had
  a main record and wanted a list of all the log records associated
  with it, you'd want to be able to de-reference some pointer or other
  association directly to some container item, like an array or list of
  log records.  This is generally what leads to the desire to build
  multiple tables of the same type, because tables look a lot like
  instances of a container item.  You're thinking of those tables as
  *instances* of multi-element data structures, rather than a global
  collection of all sub-instances of that type.

  That's pretty natural.  After all, it would be an odd way to build an
  application.  While many environments have global lists of objects
  (e.g. static class variables that hold a list of every instance of
  that particular class), you rarely use those lists for building
  general associations.  In specific, if you were manipulating record X
  and wanted a list of all the associated log records for X, you would
  never consider putting a "I belong to data X" field into the log
  record.  This would require scanning the global list of log records
  to get from a main record to all the associated log records.  Not
  only is that somewhat indirect, it sounds very inefficient.

  But this is exactly what you do in the database world.  The main
  difference being that this is exactly the kind of thing that
  databases are very very good at-- both in terms of optimized searches
  for specific records in a large collection (if properly indexed) as
  well as powerful set manipulations that allow very complex
  manip

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

All I know is that SQLite chooses the wrong order when I give it my query at 
this form:

 

SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

 

Where:

A = 50,000 rows.

B = 2 rows.

C = 10,000 rows

D = 250,000 rows

 

 

(B + C + D) have two columns that are present in A, of which only one is 
indexed. Could that be the culprit?

 

Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
something wrong in this case.

 

 

 

> From: paiva...@gmail.com
> Date: Fri, 30 Oct 2009 12:16:17 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> Of course SQLite wasn't changed much in this part since November 2008
> but the citation you gave is either wrong or the key words in it are
> "something like" in phrase "SQlite does something like this". Because
> SQLite is smart enough to choose smaller table as an outer one and
> bigger table as an inner one. Although it can choose other way round
> if you have index on Id in smaller table and don't have index on Id in
> bigger table. And in this case there's no performance hit in such
> decision, only benefit.
> 
> Pavel
> 
> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
>  wrote:
> >
> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> >
> >
> >
> > SQLite uses only nested loops to implement joins. Given a query like the 
> > following:
> >
> > SELECT ...
> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> >
> >
> >
> > SQlite does something like this:
> >
> >
> >
> > for each row in OuterTable
> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> > end for each
> >
> >
> >
> > I assume SQLite has not improved on JOIN precedence since then, which means 
> > that if OuterTable is HUGE, there will be an huge performance hit!
> >
> >
> >
> > "Some database engines like SQL Server decide which table is the outer 
> > table and which table is the inner table"
> >
> >
> > How do I simulate that behavior in SQLite? A misformed SQL statement from 
> > the user results in unacceptable lockups...
> >
> > _
> > Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> > http://windows.microsoft.com/shop
> > ___
> > 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

  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data loss after vacuum

[Dair Grant]

chen jia wrote:

> Before I ran vacuum, I ran pragma integrity_check; and got
> sqlite> pragma integrity_check;
> ok
> 
> After I ran vacuum, I ran pragma integrity_check; again and got
> sqlite> pragma integrity_check;
> *** in database main ***
> On tree page 15 cell 36: Child page depth differs
> On tree page 15 cell 37: Child page depth differs
> On tree page 20 cell 1: Child page depth differs

This sounds like an issue I ran into:

  

I sent some test cases to DRH to examine and he indicated the bug was
present in 3.6.10 but was fixed in 3.6.11.


-dair
___
d...@refnum.com  http://www.refnum.com/


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

Re: [sqlite] Data loss after vacuum

[Simon Slavin]

On 31 Oct 2009, at 9:39pm, chen jia wrote:

> I have not updated sqlite from version 3.6.10 to version 3.6.12 yet.

3.6.10 was January 2009.  9 months ago.  Perhaps there's an easy way  
to get a later version.

> But, I have tried .dump by following the instructions on
> .
>
> I created a new database by using
> $ echo '.dump' | sqlite3 hq.db | gzip -c > hq.dump.gz

Unzip the .gz file into a text file and look through it.  See if you  
can see anything strange about it, especially in table firmsret11.  I  
will bet that it looks okay.

> $ zcat hq.dump.gz | sqlite3 hq3.db
>
> Then, I did pragma integrity_check and vacuum on the new database.
> Before I ran vacuum, the pragma integrity check returns OK. After I
> ran vacuum, I still got errors when I ran pragma integrity_check.
> Also, the number of rows of table firmsret11 reduces to 2.

I think you have come up against one of the known bugs in 3.6.10.  But  
I don't know enough about SQLite to be sure.  However, I'd recommend  
you update just in case the bug I'm thinking of, or a later bug is  
causing your problem.  And anyone trying to cure your bug will  
definitely want to start from the current version of SQLite, not one  
nine months old.

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

Re: [sqlite] How to make LIKE searches case-sensitive

[Ted Rolle]

On Sat, 31 Oct 2009 18:43:46 +0100
Sylvain Pointeau  wrote:

> http://www.sqlite.org/pragma.html#pragma_case_sensitive_like

Thanks, Sylvain!  I'm more impressed with SQLite every day.

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

Re: [sqlite] Data loss after vacuum

[chen jia]

> Although the first integrity_check says that there are no errors, it
> may be wrong.  I am not able to definitely tell what is wrong, so I am
> just guessing.  Your earlier post said you were running version
> 3.6.10.  Please try to update to the latest version of SQLite
> available for your platform, but at least version 3.6.12.
>
> You may wish to rescue as much data as possible out of your database
> file.  One way to do this is to open it in the command-line tool and
> use the '.dump' command to create a text file with the SQL commands in
> to recreate the database.  You can then create a new blank database
> file and use the '.read' command to run those SQL commands to make a
> new, uncorrupt database which /should/ have all your data in.  Please
> see the page
>
> 
>
> for examples of how to write output to a text file but one example
> would be
>
> echo '.dump' | sqlite3 hq.db > hq.txt
>
> You should be able to use VACUUM on the new database file without any
> problems.
>
> Simon.
>
> PS: Please allow replies to go to the mailing list as normal.  That
> way if one person gives you bad advice another person can see it and
> correct it.

Thanks again, Simon,

I am using ubuntu 9.04. I installed sqlite3 by using apt-get install
sqlite3 and got sqlite3 version 3.6.10.

I have not updated sqlite from version 3.6.10 to version 3.6.12 yet.
But, I have tried .dump by following the instructions on
.

I created a new database by using
$ echo '.dump' | sqlite3 hq.db | gzip -c > hq.dump.gz
$ zcat hq.dump.gz | sqlite3 hq3.db

Then, I did pragma integrity_check and vacuum on the new database.
Before I ran vacuum, the pragma integrity check returns OK. After I
ran vacuum, I still got errors when I ran pragma integrity_check.
Also, the number of rows of table firmsret11 reduces to 2.

$ sqlite3 hq3.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
sqlite> select count(*) from firmsret11;
2
sqlite> pragma integrity_check;
*** in database main ***
On tree page 19 cell 20: Child page depth differs
On tree page 19 cell 21: Child page depth differs
On tree page 3301422 cell 103: Child page depth differs
On tree page 16011 cell 0: 2nd reference to page 15965
On tree page 16011 cell 1: 2nd reference to page 15966
On tree page 16011 cell 2: 2nd reference to page 15967
On tree page 16011 cell 3: 2nd reference to page 15968
On tree page 16011 cell 4: 2nd reference to page 15969
On tree page 16011 cell 5: 2nd reference to page 15970
On tree page 16011 cell 6: 2nd reference to page 15971
On tree page 16011 cell 7: 2nd reference to page 15972
On tree page 16011 cell 8: 2nd reference to page 15973
On tree page 16011 cell 9: 2nd reference to page 15974
On tree page 16011 cell 10: 2nd reference to page 15975
On tree page 16011 cell 11: 2nd reference to page 15976
On tree page 16011 cell 12: 2nd reference to page 15977
On tree page 16011 cell 13: 2nd reference to page 15978
On tree page 16011 cell 14: 2nd reference to page 15979
On tree page 16011 cell 15: 2nd reference to page 15980
On tree page 16011 cell 16: 2nd reference to page 15981
On tree page 16011 cell 17: 2nd reference to page 15982
On tree page 16011 cell 18: 2nd reference to page 15983
On tree page 16011 cell 19: 2nd reference to page 15984
On tree page 16011 cell 20: 2nd reference to page 15985
On tree page 16011 cell 21: 2nd reference to page 15986
On tree page 16011 cell 22: 2nd reference to page 15987
On tree page 16011 cell 23: 2nd reference to page 15988
On tree page 16011 cell 24: 2nd reference to page 15989
On tree page 16011 cell 25: 2nd reference to page 15990
On tree page 16011 cell 26: 2nd reference to page 15991
On tree page 16011 cell 27: 2nd reference to page 15992
On tree page 16011 cell 28: 2nd reference to page 15993
On tree page 16011 cell 29: 2nd reference to page 15994
On tree page 16011 cell 30: 2nd reference to page 15995
On tree page 16011 cell 31: 2nd reference to page 15996
On tree page 16011 cell 32: 2nd reference to page 15997
On tree page 16011 cell 33: 2nd reference to page 15998
On tree page 16011 cell 34: 2nd reference to page 15999
On tree page 16011 cell 35: 2nd reference to page 16000
On tree page 16011 cell 36: 2nd reference to page 16001
On tree page 16011 cell 37: 2nd reference to page 16002
On tree page 16011 cell 38: 2nd reference to page 16003
On tree page 16011 cell 39: 2nd reference to page 16004
On tree page 16011 cell 40: 2nd reference to page 16005
On tree page 16011 cell 41: 2nd reference to page 16006
On tree page 16011 cell 42: 2nd reference to page 16007
On tree page 16011 cell 43: 2nd reference to page 16008
On tree page 16011 cell 44: 2nd reference to page 16009
On tree page 16011 cell 45: 2nd reference to page 16010
On tree page 16011 cell 46: 2nd reference to page 16012
On tree page 16011 cell 47: 2nd reference to page 16013
On tree page 16011 cel

Re: [sqlite] Data loss after vacuum

[Simon Slavin]

On 31 Oct 2009, at 7:03pm, chen jia wrote:

> Before I ran vacuum, I ran pragma integrity_check; and got
> sqlite> pragma integrity_check;
> ok
>
> After I ran vacuum, I ran pragma integrity_check; again and got
> sqlite> pragma integrity_check;
> *** in database main ***
> On tree page 15 cell 36: Child page depth differs
> On tree page 15 cell 37: Child page depth differs
> On tree page 20 cell 1: Child page depth differs
> On tree page 3301422 cell 103: Child page depth differs
> On tree page 13623 cell 0: 2nd reference to page 13592
> On tree page 13623 cell 1: 2nd reference to page 13593

Although the first integrity_check says that there are no errors, it  
may be wrong.  I am not able to definitely tell what is wrong, so I am  
just guessing.  Your earlier post said you were running version  
3.6.10.  Please try to update to the latest version of SQLite  
available for your platform, but at least version 3.6.12.

You may wish to rescue as much data as possible out of your database  
file.  One way to do this is to open it in the command-line tool and  
use the '.dump' command to create a text file with the SQL commands in  
to recreate the database.  You can then create a new blank database  
file and use the '.read' command to run those SQL commands to make a  
new, uncorrupt database which /should/ have all your data in.  Please  
see the page



for examples of how to write output to a text file but one example  
would be

echo '.dump' | sqlite3 hq.db > hq.txt

You should be able to use VACUUM on the new database file without any  
problems.

Simon.

PS: Please allow replies to go to the mailing list as normal.  That  
way if one person gives you bad advice another person can see it and  
correct it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data loss after vacuum

[chen jia]

> Please enter this line in the same sqlite3 tool you are using for
> vacuum:

> PRAGMA integrity_check;

> It appears that you have a copy of the database from before the
> 'vacuum' command.  If you can, take another copy and run the PRAGMA
> command both before and after the 'vacuum'.  For more details see

> 

> Simon.

Thanks, Simon,

Before I ran vacuum, I ran pragma integrity_check; and got
sqlite> pragma integrity_check;
ok

After I ran vacuum, I ran pragma integrity_check; again and got
sqlite> pragma integrity_check;
*** in database main ***
On tree page 15 cell 36: Child page depth differs
On tree page 15 cell 37: Child page depth differs
On tree page 20 cell 1: Child page depth differs
On tree page 3301422 cell 103: Child page depth differs
On tree page 13623 cell 0: 2nd reference to page 13592
On tree page 13623 cell 1: 2nd reference to page 13593
On tree page 13623 cell 2: 2nd reference to page 13594
On tree page 13623 cell 3: 2nd reference to page 13595
On tree page 13623 cell 4: 2nd reference to page 13596
On tree page 13623 cell 5: 2nd reference to page 13597
On tree page 13623 cell 6: 2nd reference to page 13598
On tree page 13623 cell 7: 2nd reference to page 13599
On tree page 13623 cell 8: 2nd reference to page 13600
On tree page 13623 cell 9: 2nd reference to page 13601
On tree page 13623 cell 10: 2nd reference to page 13602
On tree page 13623 cell 11: 2nd reference to page 13603
On tree page 13623 cell 12: 2nd reference to page 13604
On tree page 13623 cell 13: 2nd reference to page 13605
On tree page 13623 cell 14: 2nd reference to page 13606
On tree page 13623 cell 15: 2nd reference to page 13607
On tree page 13623 cell 16: 2nd reference to page 13608
On tree page 13623 cell 17: 2nd reference to page 13609
On tree page 13623 cell 18: 2nd reference to page 13610
On tree page 13623 cell 19: 2nd reference to page 13611
On tree page 13623 cell 20: 2nd reference to page 13612
On tree page 13623 cell 21: 2nd reference to page 13613
On tree page 13623 cell 22: 2nd reference to page 13614
On tree page 13623 cell 23: 2nd reference to page 13615
On tree page 13623 cell 24: 2nd reference to page 13616
On tree page 13623 cell 25: 2nd reference to page 13617
On tree page 13623 cell 26: 2nd reference to page 13618
On tree page 13623 cell 27: 2nd reference to page 13619
On tree page 13623 cell 28: 2nd reference to page 13620
On tree page 13623 cell 29: 2nd reference to page 13621
On tree page 13623 cell 30: 2nd reference to page 13622
On tree page 13623 cell 31: 2nd reference to page 13624
On tree page 13623 cell 32: 2nd reference to page 13625
On tree page 13623 cell 33: 2nd reference to page 13626
On tree page 13623 cell 34: 2nd reference to page 13627
On tree page 13623 cell 35: 2nd reference to page 13628
On tree page 13623 cell 36: 2nd reference to page 13629
On tree page 13623 cell 37: 2nd reference to page 13630
On tree page 13623 cell 38: 2nd reference to page 13631
On tree page 13623 cell 39: 2nd reference to page 13632
On tree page 13623 cell 40: 2nd reference to page 13633
On tree page 13623 cell 41: 2nd reference to page 13634
On tree page 13623 cell 42: 2nd reference to page 13635
On tree page 13623 cell 43: 2nd reference to page 13636
On tree page 13623 cell 44: 2nd reference to page 13637
On tree page 13623 cell 45: 2nd reference to page 13638
On tree page 13623 cell 46: 2nd reference to page 13639
On tree page 13623 cell 47: 2nd reference to page 13640
On tree page 13623 cell 48: 2nd reference to page 13641
On tree page 13623 cell 49: 2nd reference to page 13642
On tree page 13623 cell 50: 2nd reference to page 13643
On tree page 13623 cell 51: 2nd reference to page 13644
On tree page 13623 cell 52: 2nd reference to page 13645
On tree page 13623 cell 53: 2nd reference to page 13646
On tree page 13623 cell 54: 2nd reference to page 13647
On tree page 13623 cell 55: 2nd reference to page 13648
On tree page 13623 cell 56: 2nd reference to page 13649
On tree page 13623 cell 57: 2nd reference to page 13650
On tree page 13623 cell 58: 2nd reference to page 13651
On tree page 13623 cell 59: 2nd reference to page 13652
On tree page 13623 cell 60: 2nd reference to page 13653
On tree page 13623 cell 61: 2nd reference to page 13654
On tree page 13623 cell 62: 2nd reference to page 13655
On tree page 13623 cell 63: 2nd reference to page 13656
On tree page 13623 cell 64: 2nd reference to page 13657
On tree page 13623 cell 65: 2nd reference to page 13658
On tree page 13623 cell 66: 2nd reference to page 13659
On tree page 13623 cell 67: 2nd reference to page 13660
On tree page 13623 cell 68: 2nd reference to page 13661
On tree page 13623 cell 69: 2nd reference to page 13662
On tree page 13623 cell 70: 2nd reference to page 13663
On tree page 13623 cell 71: 2nd reference to page 13664
On tree page 13623 cell 72: 2nd reference to page 13665
On tree page 13623 cell 73: 2nd reference to page 13666
On tree page 13623 cell 74: 2nd reference to page 13667
On tree page

Re: [sqlite] A constraint bug?

[Jeremy Hinegardner]

On Sat, Oct 31, 2009 at 08:31:01AM -0400, D. Richard Hipp wrote:
> 
> On Oct 30, 2009, at 10:25 PM, Mick wrote:
> 
> > This is more FYI than needing it (as I have already worked around  
> > it), but I
> > have discovered that an IGNORE constraint on an insert, when one of  
> > the
> > fields in the constraint is NULL, will insert a duplicate record  
> > into the
> > database.
> 
> This is not a bug.  See, for example, the explanation on 
> http://www.sqlite.org/cvstrac/tktview?tn=3463
> 
> >
> > i.e.
> >
> > CREATE TABLE mytable (
> > ID1 INTEGER NOT NULL,
> > ID2 INTEGER NOT NULL,
> > SomeText VARCHAR(100) COLLATE NOCASE,
> > PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
> >
> > INSERT INTO mytable VALUES (1, 1, NULL);
> > INSERT INTO mytable VALUES (1, 1, NULL);

As an aside, Postgres 8.4 does not allow the insert to happen in the first
place.  http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html

"The primary key constraint specifies that a column or columns of a table 
can
 contain only unique (non-duplicate), nonnull values."

  [jer...@[local]] 12:59:21> create table mytable( id1 integer not null, 
   id2 integer not null, 
  sometext varchar(100),  
primary key(id1, id2, sometext));
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" 
for table "mytable" CREATE TABLE
  Time: 492.766 ms
  [jer...@[local]] 13:00:04> insert into mytable values(1,1,NULL);
  ERROR:  null value in column "sometext" violates not-null constraint

Change the PRIMARY KEY to a UNIQUE contraint and sqlite and postgres
agree on the behavior.  On the same postgresql page:

"For the purpose of a unique constraint, null values are not considered 
 equal."

  [jer...@[local]] 13:03:32> create table mytable( id1 integer not null, 
   id2 integer not null, 
  sometext varchar(100),
   unique(id1, id2, sometext));
  NOTICE:  CREATE TABLE / UNIQUE will create implicit index "mytable_id1_key" 
for table "mytable"
  CREATE TABLE
  Time: 121.473 ms
  [jer...@[local]] 13:03:49> insert into mytable values(1,1,NULL);
  INSERT 0 1
  Time: 18.476 ms
  [jer...@[local]] 13:03:54> insert into mytable values(1,1,NULL);
  INSERT 0 1
  Time: 0.539 ms
  [jer...@[local]] 13:03:56> select * from mytable;
   id1 | id2 | sometext 
  -+-+--
 1 |   1 | NULL
 1 |   1 | NULL
  (2 rows)

  Time: 14.775 ms

enjoy,

-jeremy

-- 

 Jeremy Hinegardner  jer...@hinegardner.org 

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

Re: [sqlite] Data loss after vacuum

[chen jia]

> Please enter this line in the same sqlite3 tool you are using for
> vacuum:

> PRAGMA integrity_check;

> It appears that you have a copy of the database from before the
> 'vacuum' command.  If you can, take another copy and run the PRAGMA
> command both before and after the 'vacuum'.  For more details see

> 

> Simon.

Thanks, Simon,

Before I ran vacuum, I ran pragma integrity_check; and got
sqlite> pragma integrity_check;
ok

After I ran vacuum, I ran pragma integrity_check; again and got
sqlite> pragma integrity_check;
*** in database main ***
On tree page 15 cell 36: Child page depth differs
On tree page 15 cell 37: Child page depth differs
On tree page 20 cell 1: Child page depth differs
On tree page 3301422 cell 103: Child page depth differs
On tree page 13623 cell 0: 2nd reference to page 13592
On tree page 13623 cell 1: 2nd reference to page 13593
On tree page 13623 cell 2: 2nd reference to page 13594
On tree page 13623 cell 3: 2nd reference to page 13595
On tree page 13623 cell 4: 2nd reference to page 13596
On tree page 13623 cell 5: 2nd reference to page 13597
On tree page 13623 cell 6: 2nd reference to page 13598
On tree page 13623 cell 7: 2nd reference to page 13599
On tree page 13623 cell 8: 2nd reference to page 13600
On tree page 13623 cell 9: 2nd reference to page 13601
On tree page 13623 cell 10: 2nd reference to page 13602
On tree page 13623 cell 11: 2nd reference to page 13603
On tree page 13623 cell 12: 2nd reference to page 13604
On tree page 13623 cell 13: 2nd reference to page 13605
On tree page 13623 cell 14: 2nd reference to page 13606
On tree page 13623 cell 15: 2nd reference to page 13607
On tree page 13623 cell 16: 2nd reference to page 13608
On tree page 13623 cell 17: 2nd reference to page 13609
On tree page 13623 cell 18: 2nd reference to page 13610
On tree page 13623 cell 19: 2nd reference to page 13611
On tree page 13623 cell 20: 2nd reference to page 13612
On tree page 13623 cell 21: 2nd reference to page 13613
On tree page 13623 cell 22: 2nd reference to page 13614
On tree page 13623 cell 23: 2nd reference to page 13615
On tree page 13623 cell 24: 2nd reference to page 13616
On tree page 13623 cell 25: 2nd reference to page 13617
On tree page 13623 cell 26: 2nd reference to page 13618
On tree page 13623 cell 27: 2nd reference to page 13619
On tree page 13623 cell 28: 2nd reference to page 13620
On tree page 13623 cell 29: 2nd reference to page 13621
On tree page 13623 cell 30: 2nd reference to page 13622
On tree page 13623 cell 31: 2nd reference to page 13624
On tree page 13623 cell 32: 2nd reference to page 13625
On tree page 13623 cell 33: 2nd reference to page 13626
On tree page 13623 cell 34: 2nd reference to page 13627
On tree page 13623 cell 35: 2nd reference to page 13628
On tree page 13623 cell 36: 2nd reference to page 13629
On tree page 13623 cell 37: 2nd reference to page 13630
On tree page 13623 cell 38: 2nd reference to page 13631
On tree page 13623 cell 39: 2nd reference to page 13632
On tree page 13623 cell 40: 2nd reference to page 13633
On tree page 13623 cell 41: 2nd reference to page 13634
On tree page 13623 cell 42: 2nd reference to page 13635
On tree page 13623 cell 43: 2nd reference to page 13636
On tree page 13623 cell 44: 2nd reference to page 13637
On tree page 13623 cell 45: 2nd reference to page 13638
On tree page 13623 cell 46: 2nd reference to page 13639
On tree page 13623 cell 47: 2nd reference to page 13640
On tree page 13623 cell 48: 2nd reference to page 13641
On tree page 13623 cell 49: 2nd reference to page 13642
On tree page 13623 cell 50: 2nd reference to page 13643
On tree page 13623 cell 51: 2nd reference to page 13644
On tree page 13623 cell 52: 2nd reference to page 13645
On tree page 13623 cell 53: 2nd reference to page 13646
On tree page 13623 cell 54: 2nd reference to page 13647
On tree page 13623 cell 55: 2nd reference to page 13648
On tree page 13623 cell 56: 2nd reference to page 13649
On tree page 13623 cell 57: 2nd reference to page 13650
On tree page 13623 cell 58: 2nd reference to page 13651
On tree page 13623 cell 59: 2nd reference to page 13652
On tree page 13623 cell 60: 2nd reference to page 13653
On tree page 13623 cell 61: 2nd reference to page 13654
On tree page 13623 cell 62: 2nd reference to page 13655
On tree page 13623 cell 63: 2nd reference to page 13656
On tree page 13623 cell 64: 2nd reference to page 13657
On tree page 13623 cell 65: 2nd reference to page 13658
On tree page 13623 cell 66: 2nd reference to page 13659
On tree page 13623 cell 67: 2nd reference to page 13660
On tree page 13623 cell 68: 2nd reference to page 13661
On tree page 13623 cell 69: 2nd reference to page 13662
On tree page 13623 cell 70: 2nd reference to page 13663
On tree page 13623 cell 71: 2nd reference to page 13664
On tree page 13623 cell 72: 2nd reference to page 13665
On tree page 13623 cell 73: 2nd reference to page 13666
On tree page 13623 cell 74: 2nd reference to page 13667
On tree page

Re: [sqlite] How to make LIKE searches case-sensitive

[Sylvain Pointeau]

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


On Sat, Oct 31, 2009 at 4:27 PM, P Kishor  wrote:

> On Sat, Oct 31, 2009 at 10:21 AM, Ted Rolle  wrote:
> > I'd like to make LIKE '%Blah%/ only find 'Blah', and not 'blah'.
>
>
> http://www.sqlite.org/lang_expr.html#glob
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> 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: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

On Saturday 31 October 2009 18:55:58 Pavel Ivanov wrote:
> > And so text '1' in view_test is not equal to text '1' in view_test2. Are 
> > you sure
> > that current datatypes realisation is right?
> 
> Are you sure that when you compare column in view to something it is
> not optimized to get value from table and compare it? In this case
> your results are explainable.

I'm sure there are the bugs. I'm not sure why. The SQLite shell is part of
SQLite project and so the bugs must be reported here. 

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

Re: [sqlite] has used anyone sqlite for production web sites ??

[Alexey Pechnikov]

Hello!

Add SQLite database to your current site and write to it too. You
will see how this work on you environment.

About your site load. 60/80 Concurrent users with common query 
time about 50 ms produce 1200/1600 requests per second.
"10 concurrent writers" with "4/7  Insert or Update per request"
can produce about 1 kB data by transaction and so 200kB data per 
second (20 tps as above). By 86400 second per day and 365 day per 
year your site produce ~6 TB database!
This is not a small project :-)

May be you speak about user sessions? User session can continue
a lot of times but total requests count can be small.

I'm using SQLite for few GB databases but with ~1000 concurrent
sessions. See some my tests and results here
http://geomapx.blogspot.com/2009/09/sqlite-3617-mobigroup2.html
Article is russian but you can read sql listings. And I did add a few
english comments.

I can add that time of the creating new SQLite db connection is about 
0.5 ms and so you can open database 2000 times per second on 
single-core processor. So the total amount of readers can be huge.

P.S. GeoIP by SQLite
http://geomapx.blogspot.com/2009/10/geoip-sqlite.html
Try to download the dataset and test it on your site.

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

Re: [sqlite] BUG: datatypes conversion logic error

[Pavel Ivanov]

> And so text '1' in view_test is not equal to text '1' in view_test2. Are you 
> sure
> that current datatypes realisation is right?

Are you sure that when you compare column in view to something it is
not optimized to get value from table and compare it? In this case
your results are explainable.

Pavel

On Sat, Oct 31, 2009 at 11:13 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> $ sqlite3
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table test(a blob);
> sqlite> insert into test values(1);
> sqlite> insert into test values('1');
> sqlite> create view view_test as select a||'' as a from test;
> sqlite> select typeof(a) from view_test;
> text
> text
> sqlite> select * from view_test where a=1;
> sqlite> create view view_test2 as select cast(a as text) as a from test;
> sqlite> select typeof(a) from view_test2;
> text
> text
> sqlite> select * from view_test2 where a=1;
> 1
> 1
>
> And so text '1' in view_test is not equal to text '1' in view_test2. Are you 
> sure
> that current datatypes realisation is right?
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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 make LIKE searches case-sensitive

[P Kishor]

On Sat, Oct 31, 2009 at 10:21 AM, Ted Rolle  wrote:
> I'd like to make LIKE '%Blah%/ only find 'Blah', and not 'blah'.


http://www.sqlite.org/lang_expr.html#glob



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to make LIKE searches case-sensitive

[Ted Rolle]

I'd like to make LIKE '%Blah%/ only find 'Blah', and not 'blah'.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

$ sqlite3
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(a blob);
sqlite> insert into test values(1);
sqlite> insert into test values('1');
sqlite> create view view_test as select a||'' as a from test;
sqlite> select typeof(a) from view_test;
text
text
sqlite> select * from view_test where a=1;
sqlite> create view view_test2 as select cast(a as text) as a from test;
sqlite> select typeof(a) from view_test2;
text
text
sqlite> select * from view_test2 where a=1;
1
1

And so text '1' in view_test is not equal to text '1' in view_test2. Are you 
sure
that current datatypes realisation is right?

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

Re: [sqlite] BUG: datatypes conversion logic error

[Pavel Ivanov]

> As you can see above 1 is always equal to '1'.

You're wrong. As you can see from your own examples when you store 1
or '1' in the field a of type "text" then comparison of field a to
either 1 or '1' is always true. And at the same time comparison of
constant 1 to constant '1' is always false. Do you feel the difference
in these test cases? Try to change something in your test case. E.g.
declare your field a as type "blob" or as type "any dummy type you
like" and you'll suddenly see that 1 is not always equal to '1'.

> Do you know some language where 1='1' or 1!='1' randomly?

I don't know of any language that does anything randomly and I know
that SQLite doesn't do these comparisons "randomly" either.
Have you ever read the document I pointed you to? You better do
because it explains all subtle differences in your test cases that you
do not happen to see now.

> The internal representation of the integer and of the double is different
> and internal conversation is needed. Most modern languages make this
> for string values too.

Wow, I have never seen so far anybody calling C and C++ "not a modern
language". Or you have already proposed this feature to C++0x
standard?


Pavel


P.S. Do you realize that as long as you make some accusations of type
"SQLite doesn't work like I want it to" without ever trying to
understand why SQLite works that way nobody will treat you seriously?

On Sat, Oct 31, 2009 at 5:29 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> On Saturday 31 October 2009 02:31:44 Simon Slavin wrote:
>>
>> On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote:
>>
>> > Now SQLite think that 1 is equal to '1' in some causes and think
>> > different in other.
>>
>> Just like every other language, once you get into it you have to learn
>> how the language works to understand what's going on.  Your problem is
>> not really with the comparison, it's with what happens to a value when
>> it is stored in a table.  Strongly typed languages usually do one of
>> two things:
>>
>> A) not allow the comparison at all (you get a syntax error from the
>> compiler)
>>
>> B) say that two values of different types never equal one-another
>
> You did forget the third way:
>
> C) convert values to same type before comparision
>
> In C the comparision some of different types is valid:
> int x =1;
> double y=2;
> if (y>x) ...
> The internal representation of the integer and of the double is different
> and internal conversation is needed. Most modern languages make this
> for string values too.
>
> In Tcl value can has string or numeric internal representation and is 
> converted
> before comparison. When values can have the same type and is equal than the
> result of equality check is positive. When values doesn't have the same type
> or have the same type but isn't equal than equality check is negative.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A constraint bug?

[D. Richard Hipp]

On Oct 30, 2009, at 10:25 PM, Mick wrote:

> This is more FYI than needing it (as I have already worked around  
> it), but I
> have discovered that an IGNORE constraint on an insert, when one of  
> the
> fields in the constraint is NULL, will insert a duplicate record  
> into the
> database.

This is not a bug.  See, for example, the explanation on 
http://www.sqlite.org/cvstrac/tktview?tn=3463

>
> i.e.
>
> CREATE TABLE mytable (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> SomeText VARCHAR(100) COLLATE NOCASE,
> PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
>
> INSERT INTO mytable VALUES (1, 1, NULL);
> INSERT INTO mytable VALUES (1, 1, NULL);
>
> Creates 2 records with the same primary key. It makes no difference  
> whether
> you use "OR IGNORE" in the insert statements, either.
>
> Cheers,
>
> Mick O'Neill
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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] A constraint bug?

[Simon Davies]

2009/10/31 Mick :
> This is more FYI than needing it (as I have already worked around it), but I
> have discovered that an IGNORE constraint on an insert, when one of the
> fields in the constraint is NULL, will insert a duplicate record into the
> database.
>
> i.e.
>
> CREATE TABLE mytable (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> SomeText VARCHAR(100) COLLATE NOCASE,
> PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
>
> INSERT INTO mytable VALUES (1, 1, NULL);
> INSERT INTO mytable VALUES (1, 1, NULL);
>
> Creates 2 records with the same primary key. It makes no difference whether
> you use "OR IGNORE" in the insert statements, either.

NULL != NULL, so the primary key is not conflicting

>
> Cheers,
>
> Mick O'Neill
>

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

[sqlite] A constraint bug?

[Mick]

This is more FYI than needing it (as I have already worked around it), but I
have discovered that an IGNORE constraint on an insert, when one of the
fields in the constraint is NULL, will insert a duplicate record into the
database. 
 
i.e.
 
CREATE TABLE mytable (
ID1 INTEGER NOT NULL,
ID2 INTEGER NOT NULL,
SomeText VARCHAR(100) COLLATE NOCASE,
PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
 
INSERT INTO mytable VALUES (1, 1, NULL);
INSERT INTO mytable VALUES (1, 1, NULL);
 
Creates 2 records with the same primary key. It makes no difference whether
you use "OR IGNORE" in the insert statements, either.
 
Cheers,
 
Mick O'Neill
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Windows Network

[Dave Dyer]

>I have heard problems with SQLite and NFS but I have no idea if a standard
>Windows shared drive uses NFS or not.  Am I o.k. to use SQLite???

It's ok for low intensity uses.  You'll get "database locked" errors
if there is too much contention for the database. 

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

Re: [sqlite] BUG: datatypes conversion logic error

[Alexey Pechnikov]

Hello!

On Saturday 31 October 2009 02:31:44 Simon Slavin wrote:
> 
> On 30 Oct 2009, at 9:47pm, Alexey Pechnikov wrote:
> 
> > Now SQLite think that 1 is equal to '1' in some causes and think
> > different in other.
> 
> Just like every other language, once you get into it you have to learn  
> how the language works to understand what's going on.  Your problem is  
> not really with the comparison, it's with what happens to a value when  
> it is stored in a table.  Strongly typed languages usually do one of  
> two things:
> 
> A) not allow the comparison at all (you get a syntax error from the  
> compiler)
> 
> B) say that two values of different types never equal one-another

You did forget the third way:

C) convert values to same type before comparision

In C the comparision some of different types is valid:
int x =1;
double y=2;
if (y>x) ...
The internal representation of the integer and of the double is different
and internal conversation is needed. Most modern languages make this 
for string values too.

In Tcl value can has string or numeric internal representation and is converted 
before comparison. When values can have the same type and is equal than the
result of equality check is positive. When values doesn't have the same type
or have the same type but isn't equal than equality check is negative.

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