Re: [sqlite] New to SQLite and I have a question

[Jay A. Kreibich]

On Sat, Sep 11, 2010 at 06:27:16PM -0700, Bob Keeland scratched on the wall:
 
> The Definitive Guide to SQLite by Mike Owens

  Good, but older.  Doesn't cover some of the newer features, including
  many of the _v2() interfaces.  
  
  Covers SQL and basic database theory.  Although this spends a little
  time on non-C APIs, VB is not one of the languages that is covered.

  Known for having a sub-standard index.

> Using SQLite by Jay A. Kreibich 

  Very new.  Also covers SQL, as well as database design theory, but I
  must admit that it only covers the C APIs.  Very large reference
  section.

  As you might guess, this is a personal favorite.

> The SQL Guide to SQLite by Rick F. van der Lans 

  This only covers the SQL language used by SQLite, it does not cover
  the library or APIs.

  I have not had a chance to look at this yet, but my other van der Lans
  books are quite good, and I expect this to be a very good book as well.


   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New to SQLite and I have a question

[Sam Carleton]

Bob,

If you need to learn the basics of SQL, I would highly recommend the book SQL
For 
Smarties.
It covers all the basics and a lot more.  I also have his book Trees and
Hierarchies in SQL for
Smarties,
but this assumes you already know the basics.

Once you understand SQL, SQLite is REALLY easy to use, all I have ever used
is the docs on the web site and it has been outstanding!  Mind you, I am
using the C interface.

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

Re: [sqlite] New to SQLite and I have a question

[Bob Keeland]

While I greatly appreciate the help I've gotten on SQLite in general, I still 
wonder about the last part of my questions below. Can anyone recommend a good 
book for learning to use SQLite? What about these that I found on Amazon.com
 
The Definitive Guide to SQLite by Mike Owens
Using SQLite by Jay A. Kreibich 
The SQL Guide to SQLite by Rick F. van der Lans 
 
I only know a little about SQL in general and even less about SQLite, and I 
could probably use help. I learned how to program in Visual Studio.NET 2003 
from a book 
Sam's Teach Yourself Microsoft Visual Basic.NET 2003. I'm now using Visual 
Basic 2010 Express (and have ordered a book on it).
 
Bob Keeland


--- On Sat, 9/11/10, Olaf Schmidt  wrote:


From: Olaf Schmidt 
Subject: Re: [sqlite] New to SQLite and I have a question
To: sqlite-users@sqlite.org
Date: Saturday, September 11, 2010, 6:00 PM



"Bob Keeland"  schrieb

> I am new to using SQLite but think that it may be good
> for a project that I'm working on. I do my programming
> in Visual Basic and don't know any C\C++.
> Is there any problem with connecting with SQLite from
> Visual Basic?
No.
Though the links Simon gave you, are for wrappers which
"connect" you to the (VB).NET world.
If it is "classic VB" you're using (up to VB-Version 5/6) -
then you can also take a look at the COM-Wrapper-
section in the SQLite-wiki:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>...
>
> My main question - Does this sound like SQLite would
> be appropriate?
Yes, from what you wrote, there should be no problems -
does not seem like a "heavy scenario" - and could even
fit into memory completely (at least on Desktop-Systems),
and SQLite supports "InMemory-Mode".

As to expanding your solution to Handheld-Devices ...
SQLite is working fine there (although many Devs
prefer working at the lower "C-language-level" then) -
but as far as I know, Robert Simpsons .NET-wrapper
(http://sqlite.phxsoftware.com) should work there
as well... as long as you're targetting devices, which
support the .NET-mobile framework (then you
could work further with your language-binding as
long as it is the VB.NET-basic-dialect and not
VB-Classic).

A broader approach, to bring your "search-services"
to these devices would be a "Web-hosted one",
since most of the newer Smartphones come with
a decent Browser (and often with permanent Internet-
connection nowadays).

> I've been using Access as my database up to now...
As said, if VB.NET, then http://sqlite.phxsoftware.com
is a good recommendation (working over ADO.NET)...
and in case we're talking about VB-Classic and
existing experience with "normal (COM-) ADO" - then
my wrapper at: http://www.thecommon.net/2.html
is probably the one with the greatest resemblance
to ADO-behaviour - not much to learn anew.

But if your GUI (for the Desktop-Version) is not too
complex, I'd develop a "unified solution" (for both,
Desktop and HandHeld) as a WebApp ...


Olaf



___
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] New to SQLite and I have a question

[Bob Keeland]

WOW! I asked for help and it seems that I got a library. Thanks for the 
information. This will obviously take me some time to study, but it looks like 
I may be heading in the right direction. Now I just have to come up with the 
information that will go into the database (whether it would be SQLite, Access, 
or whatever). The data for the database will much more difficult to come up 
with than writing the code for the program. Thanks a lot for the help.
 
Bob Keeland

--- On Sat, 9/11/10, Simon Slavin  wrote:


From: Simon Slavin 
Subject: Re: [sqlite] New to SQLite and I have a question
To: "General Discussion of SQLite Database" 
Date: Saturday, September 11, 2010, 5:00 PM



On 11 Sep 2010, at 9:27pm, Bob Keeland wrote:

> I am new to using SQLite but think that it may be good for a project that I'm 
> working on. I do my programming in Visual Basic and don't know any C\C++. Is 
> there any problem with connecting with SQLite from Visual Basic?

You'll need to pick a way of accessing it.  A quick Google gives me

http://www.kirupa.com/net/sqllite_vb_pg1.htm

http://sqlite.phxsoftware.com/

http://sqlite.phxsoftware.com/forums/t/1033.aspx

Your description of the project doesn't suggest any reason why SQLite would be 
worse than any of the alternatives.

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



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

Re: [sqlite] Issues .import(ing) a .csv file

[Simon Slavin]

On 12 Sep 2010, at 1:02am, Rich Shepard wrote:

>   I cannot see what's wrong with a line in a .csv file. SQLite tells me it
> expected 14 columns of data, but found 15. No matter how many times I count
> the columns (exported in .csv from an OpenOffice.org Calc spreadsheet with
> 14 columns), that's all I find.

Any commas in it ?  Any single or double quotes ?

Try importing it into a table with 15 columns and see where what it does.

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

Re: [sqlite] Issues .import(ing) a .csv file

[Melton Low]

On Sat, Sep 11, 2010 at 6:02 PM, Rich Shepard wrote:

>   I cannot see what's wrong with a line in a .csv file. SQLite tells me it
> expected 14 columns of data, but found 15. No matter how many times I count
> the columns (exported in .csv from an OpenOffice.org Calc spreadsheet with
> 14 columns), that's all I find. Nothing appears wrong with the following
> line, either. And there's no extra space or char visible in emacs after the
> final field's closing ".
>
>   What should I look for that I haven't tried yet to find?
>
> Rich
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

When you saved the spreadsheet as a csv file, you probably took the default
delimiter which happens to be a comma.  You should check for a cell with
data that has a comma in it. The embedded comma would be treated as a column
separator which would result in the extra column.

If you have an embedded comma, you have a couple of choices.  You can fix
the data and remove the comma.  Alternatively, you can tell OpenOffice to
use a different delimiter.

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

[sqlite] Issues .import(ing) a .csv file

[Rich Shepard]

   I cannot see what's wrong with a line in a .csv file. SQLite tells me it
expected 14 columns of data, but found 15. No matter how many times I count
the columns (exported in .csv from an OpenOffice.org Calc spreadsheet with
14 columns), that's all I find. Nothing appears wrong with the following
line, either. And there's no extra space or char visible in emacs after the
final field's closing ".

   What should I look for that I haven't tried yet to find?

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

[sqlite] Using the sqlite3_unlock_notify() API on Windows

[Sam Carleton]

I just read over the "Using the sqlite3_unlock_notify() API" and it
looks like exactly what I need.  The only catch is that currently I am
running everything on Windows.

Does anyone have a port of the supporting functions discussed on the
page for Windows?

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

Re: [sqlite] New to SQLite and I have a question

[Olaf Schmidt]

"Bob Keeland"  schrieb

> I am new to using SQLite but think that it may be good
> for a project that I'm working on. I do my programming
> in Visual Basic and don't know any C\C++.
> Is there any problem with connecting with SQLite from
> Visual Basic?
No.
Though the links Simon gave you, are for wrappers which
"connect" you to the (VB).NET world.
If it is "classic VB" you're using (up to VB-Version 5/6) -
then you can also take a look at the COM-Wrapper-
section in the SQLite-wiki:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>...
>
> My main question - Does this sound like SQLite would
> be appropriate?
Yes, from what you wrote, there should be no problems -
does not seem like a "heavy scenario" - and could even
fit into memory completely (at least on Desktop-Systems),
and SQLite supports "InMemory-Mode".

As to expanding your solution to Handheld-Devices ...
SQLite is working fine there (although many Devs
prefer working at the lower "C-language-level" then) -
but as far as I know, Robert Simpsons .NET-wrapper
(http://sqlite.phxsoftware.com) should work there
as well... as long as you're targetting devices, which
support the .NET-mobile framework (then you
could work further with your language-binding as
long as it is the VB.NET-basic-dialect and not
VB-Classic).

A broader approach, to bring your "search-services"
to these devices would be a "Web-hosted one",
since most of the newer Smartphones come with
a decent Browser (and often with permanent Internet-
connection nowadays).

> I've been using Access as my database up to now...
As said, if VB.NET, then http://sqlite.phxsoftware.com
is a good recommendation (working over ADO.NET)...
and in case we're talking about VB-Classic and
existing experience with "normal (COM-) ADO" - then
my wrapper at: http://www.thecommon.net/2.html
is probably the one with the greatest resemblance
to ADO-behaviour - not much to learn anew.

But if your GUI (for the Desktop-Version) is not too
complex, I'd develop a "unified solution" (for both,
Desktop and HandHeld) as a WebApp ...


Olaf



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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Max Vlasov]

On Sun, Sep 12, 2010 at 2:15 AM, Stephen Oberholtzer <
oliverkloz...@gmail.com> wrote:

> > Stephen, are you telling that is' smaller in any situation? When I
> mentioned
> > the trigger in case of fast reading of rowid/id, I thought that in this
> case
> > there can be a separated table with sing field id (rowid) that should
> change
> > its contents synchronously to the main table that contains all data. I
> > suppose in this case the two variants (index vs trigger) is on par in
> terms
> > of the size or am I wrong?
> >
> > Max
>
> Underneath the surface, an index is just a mini-table that contains
> the indexed columns, plus the rowid, and is stored in sort order.
> An index will always contain the indexed columns, plus the rowid.
> Since their is no way to have MORE columns in an index than in the
> table itself, there is no way for an index to be  bigger than its
> table.
>
>
Thanks for your detailed description, my initial post already contained the
reference to the fact that the index is smaller than the table itself,
that's why I already used this method. I just tried to ask you whether you
see any reason for a different table containing interested fields and linked
to the main table with the trigger to be bigger than the index created on
the same fields. Or (if sizes are similar) the trigger has some other
disadvantages...

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Stephen Oberholtzer]

On Sat, Sep 11, 2010 at 2:24 PM, Max Vlasov  wrote:
> On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer <
> oliverkloz...@gmail.com> wrote:
>
>> 2. They contain only a subset of the columns in the main table, so
>> they are smaller -- so reading through the entire index is faster than
>> reading through the entire table.
>>
>>
> Stephen, are you telling that is' smaller in any situation? When I mentioned
> the trigger in case of fast reading of rowid/id, I thought that in this case
> there can be a separated table with sing field id (rowid) that should change
> its contents synchronously to the main table that contains all data. I
> suppose in this case the two variants (index vs trigger) is on par in terms
> of the size or am I wrong?
>
> Max

Underneath the surface, an index is just a mini-table that contains
the indexed columns, plus the rowid, and is stored in sort order.
An index will always contain the indexed columns, plus the rowid.
Since their is no way to have MORE columns in an index than in the
table itself, there is no way for an index to be  bigger than its
table. The worst case is when the index has every single column in the
table, in which case the index is exactly the same size as the table
(because it contains the exact same data, just in a different order).
In fact, the table itself is basically an index with 'rowid' as the
first column.

 When you generate an insert/update/delete statement, SQLite
automatically generates code to maintain the index (updates are
handled by deleting + reinserting):

CREATE TABLE Foo (value integer primary key, insertdate text not null,
name text not null);
CREATE INDEX date_IX on Foo (insertdate);

sqlite> explain insert into Foo (insertdate, name) values ('20100911', 'Steve');
addr  opcode         p1    p2    p3    p4             p5  comment
  -        -  --  -
0     Trace          0     0     0                    00
1     Goto           0     19    0                    00
2     OpenWrite      0     2     0     3              00
3     OpenWrite      1     3     0     keyinfo(1,BINARY)  00
4     NewRowid       0     3     0                    00
5     Null           0     4     0                    00
6     String8        0     5     0     20100911       00
7     String8        0     6     0     Steve          00
8     HaltIfNull     19    2     5     Foo.insertdate may not be NULL
00
9     HaltIfNull     19    2     6     Foo.name may not be NULL  00
10    SCopy          5     7     0                    00
11    SCopy          3     8     0                    00
12    MakeRecord     7     2     1     ab             00
13    IdxInsert      1     1     0                    10
14    MakeRecord     4     3     9     daa            00
15    Insert         0     9     3     Foo            1b
16    Close          0     0     0                    00
17    Close          1     0     0                    00
18    Halt           0     0     0                    00
19    Transaction    0     1     0                    00
20    VerifyCookie   0     4     0                    00
21    TableLock      0     2     1     Foo            00
22    Goto           0     2     0                    00

Step #12 builds the index record and step #13 performs an insert into
the index. You may note that #12 builds a record with 2 columns, when
the index definition only has 1. That's because every index implicitly
includes the rowid.  If I were to add additional indexes to Foo, there
would be additional (SCopy + MakeRecord + IdxInsert) instructions for
each one.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New to SQLite and I have a question

[Simon Slavin]

On 11 Sep 2010, at 9:27pm, Bob Keeland wrote:

> I am new to using SQLite but think that it may be good for a project that I'm 
> working on. I do my programming in Visual Basic and don't know any C\C++. Is 
> there any problem with connecting with SQLite from Visual Basic?

You'll need to pick a way of accessing it.  A quick Google gives me

http://www.kirupa.com/net/sqllite_vb_pg1.htm

http://sqlite.phxsoftware.com/

http://sqlite.phxsoftware.com/forums/t/1033.aspx

Your description of the project doesn't suggest any reason why SQLite would be 
worse than any of the alternatives.

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

[sqlite] New to SQLite and I have a question

[Bob Keeland]

Hi all,
I am new to using SQLite but think that it may be good for a project that I'm 
working on. I do my programming in Visual Basic and don't know any C\C++. Is 
there any problem with connecting with SQLite from Visual Basic?
 
Basically my program will conduct repeatitive searches on a database based on 
user input. In most cases the search will be on one variable (one column) at a 
time. The database will be relatively small with less than 3000 records and 
perhaps a couple hundred columns. For each query of the database I need to keep 
the records that match and eliminate all other records. A followon search will 
just search the records remaining from the previous search. Of course I could 
have the user select several variables and then do a more thorough search, 
but for my data that would not work in a lot of cases.  
 
With enough queries (sometimes just a few and sometimes a lot) the number of 
remaining records will approach 1. The one remaining record will contain the 
information that the user will be looking for. There will not be any adding, 
deleting, or modifying records. This will justy be a tool for finding 
information based on variables related to the wanted data. The database is 
plant characteristics data and the program will be used by plant ecologists to 
determine the species of plant in hand. There are lots of books that help users 
do this, but the books use dichotomous keys and the same thing could be done, 
in some respects easier, in a program, especially if the program could 
eventually be written for a smart phone. I've had good luck with a similar 
program on PDAs.
 
My main question - Does this sound like SQLite would be appropriate? I've been 
using Access as my database up to now. If SQLite sounds appropriate can anyone 
recommend a good book on SQLite? Any help or suggestions welcome (even if the 
answer is go away).
 
Bob Keeland
Forest Dynamics, Inc.


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

Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?

[Max Vlasov]

>
> is there a program that converts sqlite database from windows-1252 to utf-8
> ?
>

It the base is not big, I think you can dump the db to sql file with the
sqlite shell, convert this text file to UTF-8 (even with notepad) and feed
it to new db

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

[sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?

[Stef Mientki]

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

Re: [sqlite] reading directly from indices (was: returning smaller subset of columns: index vs trigger)

[Max Vlasov]

On Sat, Sep 11, 2010 at 10:26 PM, Drake Wilson  wrote:

> Quoth Max Vlasov , on 2010-09-11 22:16:14 +0400:
> > although index at least should not be worse in any situation
>
> I wouldn't be quite so sure.  Would there not be use cases in which
> different sets of columns from the same rows were selected in queries
> close together, such that reading from the main table data repeatedly
> would have better pager cache locality than reading from different
> indices based on which columns were wanted?
>
>
Good point, it's easy to be smart when there's a single select statement,
but when the statement is complex, it's a trickier task. This reminds me of
the algorithms used for OCR, for every symbol there are variants, and every
symbol would probably ended up with a single variant when dictionary entries
are applied to the word tree. Thinking similar way in this case for every
sub select optimizer would keep variants of b-tree possible (main table or
indexes) and final choice for all selects should minimize total number of
b-trees used. But it's just my speculation, the optimizer I think is a very
complex thing today so any change must be applied with care

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

Re: [sqlite] reading directly from indices (was: returning smaller subset of columns: index vs trigger)

[Drake Wilson]

Quoth Max Vlasov , on 2010-09-11 22:16:14 +0400:
> although index at least should not be worse in any situation

I wouldn't be quite so sure.  Would there not be use cases in which
different sets of columns from the same rows were selected in queries
close together, such that reading from the main table data repeatedly
would have better pager cache locality than reading from different
indices based on which columns were wanted?

Granted, that may be an uncommon case, but it seems worth considering
such cases given the (apparent) complexity cost of adding index reads
to the compiled query when an index would not otherwise be used.

> Max

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Max Vlasov]

On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer <
oliverkloz...@gmail.com> wrote:

> 2. They contain only a subset of the columns in the main table, so
> they are smaller -- so reading through the entire index is faster than
> reading through the entire table.
>
>
Stephen, are you telling that is' smaller in any situation? When I mentioned
the trigger in case of fast reading of rowid/id, I thought that in this case
there can be a separated table with sing field id (rowid) that should change
its contents synchronously to the main table that contains all data. I
suppose in this case the two variants (index vs trigger) is on par in terms
of the size or am I wrong?

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Max Vlasov]

On Sat, Sep 11, 2010 at 8:54 PM, Simon Slavin  wrote:

>
> On 11 Sep 2010, at 5:38pm, Stephen Oberholtzer wrote:
>
> > It would be an interesting attempt to try efficiently patching SQLite
> > to recognize this situation and read data directly out of the index.
>
> But I've been told many times that SQLite already does this.
>

Simon, I think this is because the index is forced when you use ORDER BY so
if the index matches it, this is the only way to do the retrieval
effectively. But when you tell you want some field(s) without order by and
they're also contains in the index, sqlite optimizer would recognize there
would be fewer reads in case of the index, but I suppose it currently
doesn't do this. I think there might be some indirect logic behind this or
maybe this thing with size difference not so obvious. For example  if the
field mentioned is TEXT and it's much more larger than all other fields
together,the benefits of using the index for retrieval are not so great,
although index at least should not be worse in any situation

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Simon Slavin]

On 11 Sep 2010, at 5:38pm, Stephen Oberholtzer wrote:

> It would be an interesting attempt to try efficiently patching SQLite
> to recognize this situation and read data directly out of the index.

But I've been told many times that SQLite already does this.  For example, 
suppose you have the table

TABLE myTable

a   INTEGER
b   INTEGER
c   INTEGER
d   INTEGER
e   INTEGER

And you have the single index on (a,b,c,d)

If you do

SELECT a,b,c,d FROM myTable WHERE a=1 AND b=2 ORDER BY c

I was told that SQLite retrieved all the data needed by that SELECT from the 
index, because it was all available there, and therefore SQLite didn't have to 
go find the data from the table at all.

This means that SQLite already implements the sort of sub-TABLE that was 
discussed earlier: a copy of just a few columns of the original table.

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Stephen Oberholtzer]

On Sat, Sep 11, 2010 at 7:52 AM, Max Vlasov  wrote:

> Cory, thanks, just wondering, is the nature of the index internals is a part
> of SQL standard or just a common sense. For example, I can imagine real
> example when the space used is not a big problem so index could contain
> other non-indexed fields just for benefits of returning them without
> necessity to query the main table. Also (unlikely really used, but
> theoretically possible) minimalistic approach to space when only rowids (or
> equivalent) is saved and every operation including comparision operation
> requires querying the main table by rowid. It seems that in the first case
> the benefits of the index for faster loading will be completely lost.
>
> Max

First, I'd like to point out that if you fiddle around with EXPLAIN,
you will find that, interestingly enough, creating an index internally
behaves a lot like if you placed insert/update/delete triggers on your
table -- but the index requires a LOT less code (in fact, a single
instruction).



Second, on the subject of indexes:

Indexes have two properties that make them beneficial in two different ways:
1. They are sorted, so any WHERE clause that matches the first N
columns can be matched very effectively.
2. They contain only a subset of the columns in the main table, so
they are smaller -- so reading through the entire index is faster than
reading through the entire table.

Now, my tests indicate that SQLite's optimizer is *not* clever enough
to account for #2:

CREATE TABLE Foo (value integer primary key, insertdate text not null,
name text not null);
CREATE INDEX date_IX on Foo (insertdate);

sqlite> explain query plan select insertdate from foo;
orde  from   deta
  -  
0 0  TABLE foo

It would be an interesting attempt to try efficiently patching SQLite
to recognize this situation and read data directly out of the index.


-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding Shared-Cache Mode

[Dan Kennedy]

On Sep 11, 2010, at 9:19 AM, Sam Carleton wrote:

> I just read the page on Shared-Cache Mode and it left me with some  
> questions...
>
> Q1: Is my understanding correct:  Shared-Cache Mode is used within a
> process to gain table locking, as compared to the normal file locking.

Fair enough. That's not the reason it was added, but that is one use for
shared-cache mode. You will get table-level locking within a process if
you enable shared-cache mode.

Shared-cache mode was added to save memory on embedded platforms.

If you're not specifically worried about memory, and not using the
unlock-notify feature, WAL mode gives you more concurrency these days
anyhow.

   http://www.sqlite.org/unlock_notify.html
   http://www.sqlite.org/wal.html

> How to Enabling Shared-Cache Mode in the following situation:
>
> SQLite is being used in an Apache module which uses the Apache DBD
> API.  The DBD is a connection pooling API.  In other words, the DBD
> calls sqlite3_open_v2() and the module simply gets a connections from
> the DBD. Before the module code ever gets executed, the DBD creates 4
> connections to the database.
>
> Q2: Is my understanding correct:  The first time the module code gets
> a connection and calls int sqlite3_enable_shared_cache(int), the other
> three connections will NOT be in the Shared-Cache, but any future
> connections will be in the shared-cache.
>
> Q3: Further, when the module code gets the second connection and calls
> int sqlite3_enable_shared_cache(int), it will be added to the same
> shared-cache.

All that matters is whether or not shared-cache mode is enabled when
sqlite3_open[_v2]() is called (or when the database is ATTACHed, if
it is an attached db). If shared-cache mode is enabled at this point,
the connection will be able to connect to an existing shared-cache
(if one exists), or create a new cache that connections opened in
the future may share. If it is not enabled when sqlite3_open[_v2]()
is called, the connection uses a private cache.

> Q4: My thought is each and every time the module code gets a
> connection, it simply calls int sqlite3_enable_shared_cache(int) to
> make sure that connection is in the shared-pool.  Am I correct in
> assuming that the cost of calling int sqlite3_enable_shared_cache(1)
> when shared-cache is already enabled is very small?

Very small.

Dan.

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Max Vlasov]

On Sat, Sep 11, 2010 at 2:05 PM, Cory Nelson  wrote:

> On Sat, Sep 11, 2010 at 12:36 AM, Max Vlasov  wrote:
> > Hi,
> >
> > What other observations can be made about these two types of solutions
> that
> > I may face in the future?
>
> Both are commonly used in the wild.  Indexes have the added bonus over
> triggers that you'll have less of your own code to test and maintain,
> so I usually end up using them if I can.  I reserve triggers for more
> complex operations.  SQL Server even has another option -- an indexed
> view.
>
>
Cory, thanks, just wondering, is the nature of the index internals is a part
of SQL standard or just a common sense. For example, I can imagine real
example when the space used is not a big problem so index could contain
other non-indexed fields just for benefits of returning them without
necessity to query the main table. Also (unlikely really used, but
theoretically possible) minimalistic approach to space when only rowids (or
equivalent) is saved and every operation including comparision operation
requires querying the main table by rowid. It seems that in the first case
the benefits of the index for faster loading will be completely lost.

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

Re: [sqlite] ON DELETE CASCADE not working, version 3.6.23.1

[Igor Tandetnik]

Mark Rainess  wrote:
> CREATE TABLE tags_product (
> id INTEGER PRIMARY KEY,
> tag_id INTEGER NOT NULL,
> product_id INTEGER NOT NULL,
> FOREIGN KEY(tag_id) REFERENCES tags(id),
> FOREIGN KEY(tag_id) REFERENCES product_product(id)

Should this be

FOREIGN KEY(product_id) REFERENCES product_product(id)

?
-- 
Igor Tandetnik

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

[sqlite] ON DELETE CASCADE not working, version 3.6.23.1

[Mark Rainess]

  Hello all,

I have PRAGMA foreign_keys = ON

With:
 pysqlite version is 2.4.1
 sqlite3 version is 3.6.23.1

And the tables:

CREATE TABLE tags_product (
 id INTEGER PRIMARY KEY,
 tag_id INTEGER NOT NULL,
 product_id INTEGER NOT NULL,
 FOREIGN KEY(tag_id) REFERENCES tags(id),
 FOREIGN KEY(tag_id) REFERENCES product_product(id) ON DELETE 
CASCADE ON UPDATE CASCADE,
 UNIQUE('tag_id', 'product_id')
);

CREATE TABLE tags (
 id INTEGER PRIMARY KEY,
 tag_name TEXT NOT NULL UNIQUE
);

CREATE TABLE product_product (
 id INTEGER PRIMARY KEY,
 [etc.]
);


I have several rows in tags_product linked to a row in parent table 
product_product.
When I delete the product_product row, I expect all of the related rows 
in tags_product to automatically be deleted.
I find that only one of the rows in tags_product gets deleted.

My application (with PRAGMA foreign_keys = ON) creates all of the rows.

I used the command line sqlite3 (with PRAGMA foreign_keys = ON) to 
delete the product_product row.

What's going on?

Mark

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

Re: [sqlite] returning smaller subset of columns: index vs trigger

[Cory Nelson]

On Sat, Sep 11, 2010 at 12:36 AM, Max Vlasov  wrote:
> Hi,
>
> sometimes it is necessary to return only part of columns (maybe even only
> id/rowid) and sure SELECT Col1, Col2 works. But as the real tests some time
> ago showed, sqlite actually reads all the data from db (sure with an
> exception if record is bigger than a page). One of the approaches to
> optimize this is to use index, created only on the fields needed. For one of
> my application containing 70,000 records with about 200 bytes each it saved
> much time when i needed to load only id on the start
>
> But recently I thought that the same result can be achieved with a separated
> table and a trigger that keeps necessary data in sync. So, I wanted to
> compare these solutions. My first pro for each.
> - With the Index I didn't had to change much in the code, just create the
> index and append ORDER BY (the trigger case will require special connection
> in the logic of the code/classes between the tables).
> - On the other hand the trigger approach looks more logical and
> self-describing (you will always understand for what was it for just by
> looking at the statement) in contrary to the index that looks more like a
> trick or a hack. And moreover this scheme will theoretically give a similar
> boost for other db engines.
>
> What other observations can be made about these two types of solutions that
> I may face in the future?

Both are commonly used in the wild.  Indexes have the added bonus over
triggers that you'll have less of your own code to test and maintain,
so I usually end up using them if I can.  I reserve triggers for more
complex operations.  SQL Server even has another option -- an indexed
view.


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

[sqlite] returning smaller subset of columns: index vs trigger

[Max Vlasov]

Hi,

sometimes it is necessary to return only part of columns (maybe even only
id/rowid) and sure SELECT Col1, Col2 works. But as the real tests some time
ago showed, sqlite actually reads all the data from db (sure with an
exception if record is bigger than a page). One of the approaches to
optimize this is to use index, created only on the fields needed. For one of
my application containing 70,000 records with about 200 bytes each it saved
much time when i needed to load only id on the start

But recently I thought that the same result can be achieved with a separated
table and a trigger that keeps necessary data in sync. So, I wanted to
compare these solutions. My first pro for each.
- With the Index I didn't had to change much in the code, just create the
index and append ORDER BY (the trigger case will require special connection
in the logic of the code/classes between the tables).
- On the other hand the trigger approach looks more logical and
self-describing (you will always understand for what was it for just by
looking at the statement) in contrary to the index that looks more like a
trick or a hack. And moreover this scheme will theoretically give a similar
boost for other db engines.

What other observations can be made about these two types of solutions that
I may face in the future?

Thanks,

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