[sqlite] Outter join question of sqlite
> Does sqlite support outter syntax which similar to oracle, or sybase, > which don't need to change the FROM clause? > > Like > tableA.columnA *= tableB.columnB or tableA.columnA = tableB.columnB > ((+) > > ?? > No reply gotten, this mean sqlite only support ANSI outter join syntax? ''~`` ( o o ) +--.oooO--(_)--Oooo.--+ We are limited, not by our abilities, but by our vision. | | | http://www.carfield.com.hk| |.oooO| |( ) Oooo.| +-\ (( )+ \_)) / (_/
Re: [sqlite] temp_store assumptions
Mike, Thank you very much for your useful reply. the way to do this is to use a second db instance, open it to :MEMORY:, and copy the data (once) to this new db. then, you will have a RAM-based db, which should be fast. Yes, this is what I was hoping to do with the first method. Thanks - I'll give this a try. I am not sure I understand your GUI topology, though. dirlling down is a process of elimination, as I understand it, which means executing queries against progressively smaller result sets (temporary tables would do fine here), and should be very fast with proper indexes. once I select "vacation", I have a list of vacation photos. What is the user's next action ? do they select "sports" ? If so, is this meant to be "select photos with vacation AND sports keywords" ? if so, this is trivial and very fast. just do a join with the previous result set (saved in a temporary table). You are right - once you have the keywords it is a simple AND query. The tricky part is once you've selected "vacation", what are the keywords (the subset) that will be displayed to the user? That query is: - select all photos that have the keyword 'vacation' - return all (distinct) keywords of all those photos This query is performed often - it is what I'm trying to optimise. It gets slightly more complex as you drill down. For example, say "vacation" and "sports" are selected. The query is then: - select all photos that have the keywords 'vacation' and 'sports' - return all (distinct) keywords of all those photos and, are you sure this is a hierarchical data base ? it sounds like the typical many-to-many with split relation, which cannot be represented hirerarchically, since the data is *not* hierarchical in nature. No, the data is not inherently hierarchical at all. I'm kind of faking it by massaging it into something that looks hierarchical. I've had a very hard time communicating what I'm trying to accomplish, but I do have working code that I'm happy with. I just need to speed up my searches by an order of magnitude or two. I'm trying to do it in memory since I have not yet come up with a clever way to simplify the actual queries. Thanks again for your help! Demitri
Re: [sqlite] Outter join question of sqlite
> No reply gotten, this mean sqlite only support ANSI outter join syntax? Yes.
[sqlite] Escape charater of sqlite?
Can I escape a special character in SELECT statement? Is it using quote(X) function?
Re: [sqlite] sqlite3_get_table_printf, sqlite3_exec_printf not available anymore?
[EMAIL PROTECTED] wrote: We have been using sqlite3_get_table_printf, sqlite3_exec_printf functions in our C application to execute the queries. I find that these APIs are not available in v3.0.7 though it was available in v3.0.3. May I know why these APIs are not available in the stable sqlite 3.0.7 and may I know the alternative APIs that I can use continuing my printf like query command constructions? These functions were removed by check-in [1868] because you can easily reproduce them yourself as follows: char *z = sqlite3_mprintf(zFormat, ...); sqlite3_exec(..., z, ...); sqlite3_free(z); -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] temp_store assumptions
> > You are right - once you have the keywords it is a simple AND query. > The tricky part is once you've selected "vacation", what are the > keywords (the subset) that will be displayed to the user? That query > is: > > - select all photos that have the keyword 'vacation' > - return all (distinct) keywords of all those photos > > This query is performed often - it is what I'm trying to optimise. It > gets slightly more complex as you drill down. For example, say > "vacation" and "sports" are selected. The query is then: > > - select all photos that have the keywords 'vacation' and 'sports' > - return all (distinct) keywords of all those photos > ok, if I understand you correctly, I think I have a nice solution, which should be very fast, and neat. I am assuming the following schema: create table photo( id integer primary key, data varchar(20) ); create table keyword( id integer primary key, data varchar(20) ); create table pk( idPhoto integer, idKeyword integer); create index pk_1 on pk (idPhoto); (these indexes are crucial to fast operation) create index pk_2 on pk (idkeyword); >> >> I am assuming the following GUI: say we have a window that is split into two panes. The left hand pane contains a tree structure with keywords. The right hand pane is a grid that shows the selected photos. The semantics of the treeview as as follows: The first level of the tree contains a distinct list of all available keywords. each node is initially collapsed. when a node is selected, the right-hand pane fills with the photos that have this keyword (we are at the top level, still). The tricky part, which I did not understand from your initial description, is "what are the children of a keyword node". I am assuming that what you want is this: the children of the keyword are those keywords that exist in all photos that have the active keyword. in other words, if I select "vacation", and this gives me 10 photos, and these photos in turn have each 5 more keywords, I expect to see a list of max 50 keywords (minus any non-distinct). If this is what you are doing, it is very clever, because it assures that your user queries will always produce (some) results. If my assumptions are correct, then you have two different issues to handle : a. given a list of keywords, which photos contain all of them ? this is needed in order to populate the right hand pane, and I think that it is NOT a simple AND thing b. given a list of keywords (which is produced by traversing the tree from the root up to the selected node), which keywords should appear as children of this node? it turns out that there are very simple sql constructs that can give these results with a few statements, instead of a programmatic solution which would indeed fire a large number of small subqueries. The idea is to utilize the sql SET (asw in mathematical sets) operations, which is why SQL was created in the first place. Let us take issue (a) first: say that the user has selected 2 keywords, whose IDS your tree structure knows (it stores them as data behind each node). further, the keyword path (in ID form is :) 100/745 (100 is vacation 745 is sports etc etc) The following query populates your right hand side: select idPhoto,data from pk join photo on id=idPhoto where idKeyword=100 intersect select idPhoto,data from pk join photo on id=idPhoto where idKeyword=745 the trick is the intersect operator, which gives you the desired AND effect. The joins are done in order to avoid re-querying for the actual photo data. in a simpler,essential form, this would be : select idPhoto from pk where idKeyword=100 intersect select idPhoto from pk where idKeyword=745 you can see how this can be expanded for any number of keywords, and how to build this statement dynamically, based on the current active tree node. Having solved (a), the solution to (b) is similar, and in fact uses the same query, only as a sub-query: what you need here is to find the proper children keywords for keyword 745. so, you do this: select distinct idKeyword from pk where idPhoto in ( select idPhoto from pk where idKeyword=100 intersect select idPhoto from pk where idKeyword=745 ) notice that the subquery in this case is the same as the one that would have run for node path 100/745. This suggests that clever caching can be applied here. for example, you could have saved the query that you run for the right hand side (the results pane), and re-use it: create temp table x as select idPhoto,data from pk join photo on id=idPhoto where idKeyword=100 intersect select idPhoto,data from pk join photo on id=idPhoto where idKeyword=745 then, select * from x populates your RHS, and select distinct idKeyword from pk where idPhoto in (Select idPhoto from x) populates your tree sub-nodes and, in fact you only need to run the (b) query the first time a node is expanded, not all the time, assuming static data. so, to finalize, in order to service a GUI such as the one I
RE: [sqlite] problems using DBD::Sqlite2
Point taken, I wrote this e-mail last night when I was very tired and sick of trying to debug this. 8) Basically my script is using POE, the perl object environment, its an event driven state machine. In Detail "POE Parcels out execution time among one or more tasks, called sessions. Sessions multitask through cooperation (at least until Perl's threads become more mainstream). That is, each session returns execution to POE as quicky as possible so it can parcel out time to the next. The POE developers and contributors have written many modules and components to POE, such as POE::Components that implement non-blocking DBI calls, so you can do multiple inserts at once or an insert and an update or whatever. My program is trying to use POE::Component::EasyDBI to make 1 insert into an oracle database, and another insert into the SQLite2 database. Someone mentioned to me yesterday that more than one process can't access the SQLite2 database, that it doesn't support that kind of stuff. -Original Message- From: Scott Leighton [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 8:35 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] problems using DBD::Sqlite2 On Thursday 07 October 2004 5:11 pm, Freeman, Michael wrote: > I put in a ticket, but I thought I'd also throw this question out to you > guys. I'm having some problems with a script that is using sqlite. I'll > cut and paste from the ticket: > > > > POE::Component::EasyDBI Got STDERR from child, which should never happen > ( <- errstr= ( 'no such table: trapdlog(1) at dbdimp.c line 412' ) [1 > items] at SubProcess.pm line 866 ) at > /usr/local/lib/perl5/site_perl/5.8.5/POE/Component/EasyDBI.pm line 678. > > I keep getting these messages when I run my perl script with > DBI->trace(1); > > I created my database and table and its very simple I'm confused, what do the examples have to do with the code above? In the examples you are using DBI, above you show an error from POE. They are two completely different interfaces. The examples run fine here, so I'd say you need to provide more information regarding the real script giving you a problem. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.108-default x86_64
[sqlite] DBD::SQLite2
How come DBD::SQLite2 does not produce a 'sqlite' binary? *** Note new e-mail address -- Michael J. Freeman Netco Government Services [EMAIL PROTECTED] --
[sqlite] eVC++ 3.0 port
has anyone ported sqlite 3.0 to PocketPC using eVC++ 3.0 compiler ? I'm trying it now and am having quite a few problems. For example, functions in are not supported as well as file locking. I'm also getting millions of warnings because program is attempting to downgrade from int64 to int without typcasting, and even with typcasting that is a dangerous thing to do. If it doesn't need a 64-bit integer when why use it in the first place??? Mel Stober
Re: [sqlite] eVC++ 3.0 port
Did you try the sqlite-wince port? http://sqlite-wince.sf.net Jakub Stober, Mel wrote: has anyone ported sqlite 3.0 to PocketPC using eVC++ 3.0 compiler ? I'm trying it now and am having quite a few problems. For example, functions in are not supported as well as file locking. I'm also getting millions of warnings because program is attempting to downgrade from int64 to int without typcasting, and even with typcasting that is a dangerous thing to do. If it doesn't need a 64-bit integer when why use it in the first place??? Mel Stober -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
RE: [sqlite] eVC++ 3.0 port
Thank you very much for that link. I didn't know it existed! Mel Stober Tri-Cor Industries Inc./GATES AIT 618-632-9252 Ext 423 -Original Message- From: Jakub Adamek [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 10:53 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] eVC++ 3.0 port Did you try the sqlite-wince port? http://sqlite-wince.sf.net Jakub Stober, Mel wrote: > has anyone ported sqlite 3.0 to PocketPC using eVC++ 3.0 compiler ? I'm > trying it now and am having quite a few problems. For example, functions in > are not supported as well as file locking. > > I'm also getting millions of warnings because program is attempting to > downgrade from int64 to int without typcasting, and even with typcasting > that is a dangerous thing to do. If it doesn't need a 64-bit integer when > why use it in the first place??? > > > Mel Stober > -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
[sqlite] Help with query
Hi, This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or Shop2>'' order by Postcode asc" works as I require, but I want to expand the query to include data from another table, and have got a bit stuck The second table is called Agents. If the above query is successful, I want it to include the columns called Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents table matches either Shop1 or Shop2 in the NewsData table Can anyone help me with this? Regards, Brian Pugh
[sqlite] still having problems with DBD::SQLite2
I am still having problems with a script trying to use SQLite2. My 4
line test script works fine, but my other code keeps giving me DBI
errors saying it can't find the table. Here is what I get in the DBI
trace.
!! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
<- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
line 412 at ./logwiz.pl line 377.
no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line 377.
<- disconnect_all= '' at DBI.pm line 674
ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
! <- DESTROY(DBI::st=HASH(79dd10))= undef during global destruction
The code I'm using:
my $lite_dbh = &sqlite_connect();
# prepare the update statement
my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
= ? WHERE node = ? } )
or syslog_die($DBI::errstr);
# prepare the insert statement
my $lite_sth_insert = $lite_dbh->prepare( q{ INSERT INTO trapdlog
(epochtime, trap_category,
trap_create_time,
ip_hostname, trap_source,
description, status)
VALUES (?,?,?,?,?,?,?) } )
or syslog_die($DBI::errstr);
# prepare the select statement
my $lite_sth_select = $lite_dbh->prepare( q{ SELECT status FROM trapdlog
WHERE node = ? } )
or syslog_die($DBI::errstr);
$lite_sth_select->execute($node) or die $DBI::errstr;
my $status = $lite_sth_select->fetchrow;
$lite_sth_update->execute("$node","CLEAR") or die
$DBI::errstr;
$lite_sth_insert->execute("$epochtime","$trap_category",
"$t_date",
"$node", "$trap_source",
"$ndescription",
"DOWN") or die $DBI::errstr;
sub sqlite_connect {
# need to add code in here to check that the database exists. if it
does not we
# will create it. *thought*.
my $sqlite_dbh =
DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
or die $DBI::errstr;
return $sqlite_dbh;
}
*** Note new e-mail address
--
Michael J. Freeman
Netco Government Services
[EMAIL PROTECTED]
--
Re: [sqlite] DBD::SQLite2
At 10:32 AM -0500 10/8/04, Freeman, Michael wrote: How come DBD::SQLite2 does not produce a 'sqlite' binary? Because it's not supposed to, and doing so would be redundant. The sole purpose of DBD::SQLite[2|] is to be a plug-in module for the Perl DBI framework, so you can use SQLite via the DBI programming interface. The DBD::SQLite distribution leaves out a few files that are in the usual SQLite distro, including the shell and TCL wrappers, and provides an XS/Perl wrapper instead. If you want a sqlite binary that is used independent of Perl, then you don't use DBD::SQLite on CPAN, but rather the usual distro on sqlite.org. -- Darren Duncan
Re: [sqlite] Help with query
Without testing on my side... how's that? select a.Shop1, a.Shop2, a.Postcode, b.Code, b.Shopname, b.Address1, b.Price from NewsData a, Agents b where (a.Shop1 > '' or a.Shop2 > '') and b.Shopname > '' and (a.Shop1 = b.Shopname or a.Shop2 = b.Shopname) order by a.Postcode asc On Fri, 8 Oct 2004 18:27:59 +0100, Brian Pugh <[EMAIL PROTECTED]> wrote: > Hi, > > This query, "select distinct Shop1,Shop2,Postcode from NewsData where Shop1>'' or > Shop2>'' order by Postcode asc" > works as I require, but I want to expand the query to include data from another > table, and have got a bit stuck > > The second table is called Agents. If the above query is successful, I want it to > include the columns called > Code, Shopname, Address1 and Price, but only if the Shopname column in the Agents > table matches either > Shop1 or Shop2 in the NewsData table > > Can anyone help me with this? > > Regards, > > Brian Pugh > >
RE: [sqlite] still having problems with DBD::SQLite2
I read this guys post on the yahoo groups site,
http://groups.yahoo.com/group/sqlite/message/4528
He said this:
I've solved the "no such table: " problem.
I had a filename that was sometimes not properly NULL terminated.
Along with /path/databasename there were a few
/path/databasenameplusjunk files.
The problem is, my 4 line test script works fine, and when I try to do
the same thing in another larger script it blows up..
Help me please before I am forced to install mysql...
-Original Message-
From: Freeman, Michael
Sent: Friday, October 08, 2004 1:03 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] still having problems with DBD::SQLite2
I am still having problems with a script trying to use SQLite2. My 4
line test script works fine, but my other code keeps giving me DBI
errors saying it can't find the table. Here is what I get in the DBI
trace.
!! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
<- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
line 412 at ./logwiz.pl line 377.
no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line 377.
<- disconnect_all= '' at DBI.pm line 674
ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
! <- DESTROY(DBI::st=HASH(79dd10))= undef during global destruction
The code I'm using:
my $lite_dbh = &sqlite_connect();
# prepare the update statement
my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
= ? WHERE node = ? } )
or syslog_die($DBI::errstr);
# prepare the insert statement
my $lite_sth_insert = $lite_dbh->prepare( q{ INSERT INTO trapdlog
(epochtime, trap_category,
trap_create_time,
ip_hostname, trap_source,
description, status)
VALUES (?,?,?,?,?,?,?) } )
or syslog_die($DBI::errstr);
# prepare the select statement
my $lite_sth_select = $lite_dbh->prepare( q{ SELECT status FROM trapdlog
WHERE node = ? } )
or syslog_die($DBI::errstr);
$lite_sth_select->execute($node) or die $DBI::errstr;
my $status = $lite_sth_select->fetchrow;
$lite_sth_update->execute("$node","CLEAR") or die
$DBI::errstr;
$lite_sth_insert->execute("$epochtime","$trap_category",
"$t_date",
"$node", "$trap_source",
"$ndescription",
"DOWN") or die $DBI::errstr;
sub sqlite_connect {
# need to add code in here to check that the database exists. if it
does not we
# will create it. *thought*.
my $sqlite_dbh =
DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
or die $DBI::errstr;
return $sqlite_dbh;
}
*** Note new e-mail address
--
Michael J. Freeman
Netco Government Services
[EMAIL PROTECTED]
--
Re: [sqlite] still having problems with DBD::SQLite2
The problem you are having is that, while your sqlite_connect() code
assumes it is fine to just create a database file if it doesn't exist
(which is what SQLite does automatically), your other code always
assumes that the database file did exist before.
Your other code is trying to update or insert into a table without
first checking that the table exists. And the table won't exist if
the database didn't exist; newly created databases have no tables in
them.
You need to issue a "create table trapdlog ..." statement if the
database was newly created just now, and the table doesn't exist yet,
prior to doing any inserts or updates.
-- Darren Duncan
At 1:02 PM -0500 10/8/04, Freeman, Michael wrote:
I am still having problems with a script trying to use SQLite2. My 4
line test script works fine, but my other code keeps giving me DBI
errors saying it can't find the table. Here is what I get in the DBI
trace.
!! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
<- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
line 412 at ./logwiz.pl line 377.
no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line 377.
The code I'm using:
my $lite_dbh = &sqlite_connect();
# prepare the update statement
my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
= ? WHERE node = ? } )
sub sqlite_connect {
# need to add code in here to check that the database exists. if it
does not we
# will create it. *thought*.
my $sqlite_dbh =
DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
or die $DBI::errstr;
return $sqlite_dbh;
}
RE: [sqlite] still having problems with DBD::SQLite2
I created the database using:
dbish dbi:SQLite2:trapdlog.db
create table trapdlog (epochtime, trap_category, trap_create_time,
ip_hostname, trap_source, description, status);
create index trapd_idx on trapdlog (ip_hostname,epochtime);
I am able to connect to it with DBIsh and sqlite and it shows the table
exists. Also, I am able to do inserts into it with a test script:
#!/usr/local/bin/perl
# $Id $
use DBI;
my $dbh = DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","");
my $sql = "INSERT INTO trapdlog (epochtime, trap_category,
trap_create_time, ip_hostname, trap_source, description, status) values
('epoc','trapc','trapcxi','ip','tsrc','moocow','CLEAR')";
my $sth = $dbh->prepare($sql) or die("$DBI::errstr\n");
$sth->execute;
Also, another weird error is that if I specify the full path to the
database and I have DBI->trace(1); on, it says it can't connect to the
database. Without the full path, It just says the trapdlog doesn't
exist..
-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 3:13 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] still having problems with DBD::SQLite2
The problem you are having is that, while your sqlite_connect() code
assumes it is fine to just create a database file if it doesn't exist
(which is what SQLite does automatically), your other code always
assumes that the database file did exist before.
Your other code is trying to update or insert into a table without
first checking that the table exists. And the table won't exist if
the database didn't exist; newly created databases have no tables in
them.
You need to issue a "create table trapdlog ..." statement if the
database was newly created just now, and the table doesn't exist yet,
prior to doing any inserts or updates.
-- Darren Duncan
At 1:02 PM -0500 10/8/04, Freeman, Michael wrote:
>I am still having problems with a script trying to use SQLite2. My 4
>line test script works fine, but my other code keeps giving me DBI
>errors saying it can't find the table. Here is what I get in the DBI
>trace.
>
> !! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
>(err#0)
>
> <- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
>
>DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
>line 412 at ./logwiz.pl line 377.
>
>no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line
377.
>
>The code I'm using:
>
>my $lite_dbh = &sqlite_connect();
>
># prepare the update statement
>
>my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
>= ? WHERE node = ? } )
>
>
>sub sqlite_connect {
>
> # need to add code in here to check that the database exists. if
it
>does not we
>
> # will create it. *thought*.
>
> my $sqlite_dbh =
>DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
>
> or die $DBI::errstr;
>
> return $sqlite_dbh;
>
>}
RE: [sqlite] still having problems with DBD::SQLite2
At 3:26 PM -0500 10/8/04, Freeman, Michael wrote: Also, another weird error is that if I specify the full path to the database and I have DBI->trace(1); on, it says it can't connect to the database. Without the full path, It just says the trapdlog doesn't exist.. I was going to bring this up too, but the other problem was more glaring. When you do not give a full path, then SQLite will look in your current working directory for the database file. If you are running your program from a shell prompt, then you need to first cd into the directory that contains your database file before running your script. If you are running your program as a web application / CGI script, then your current working directory is probably whatever actual folder corresponds to the web address you invoked to run the program; unless this is where your database file is (and it shouldn't be, for security reasons), then giving an unqualified filename won't work. Your current working directory when running your script is probably different than the one your database is in, hence your problem. Using full paths gets around this because then it doesn't matter what your cwd is. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
The script and the database live and are called from in the same directory. -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 3:44 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 3:26 PM -0500 10/8/04, Freeman, Michael wrote: >Also, another weird error is that if I specify the full path to the >database and I have DBI->trace(1); on, it says it can't connect to the >database. Without the full path, It just says the trapdlog doesn't >exist.. I was going to bring this up too, but the other problem was more glaring. When you do not give a full path, then SQLite will look in your current working directory for the database file. If you are running your program from a shell prompt, then you need to first cd into the directory that contains your database file before running your script. If you are running your program as a web application / CGI script, then your current working directory is probably whatever actual folder corresponds to the web address you invoked to run the program; unless this is where your database file is (and it shouldn't be, for security reasons), then giving an unqualified filename won't work. Your current working directory when running your script is probably different than the one your database is in, hence your problem. Using full paths gets around this because then it doesn't matter what your cwd is. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 3:45 PM -0500 10/8/04, Freeman, Michael wrote: The script and the database live and are called from in the same directory. As an experiment, try putting the code from your two scripts into the same script. The combined script would first run the code for creating the database, then run the code for updating it. Both parts do an open() and a close(), and both use the unqalified file name. -- Darren Duncan
[sqlite] WinCE port
I have it working -- sortof. Compiled the source code with eVC++ 3.0 for the Win32 emulator in debug mode to make the DLL easier to debug. (doing that on an actual PocketPC device is horrible!) I created an MFC for WinCE version of shell.c to make it easier to enter text and see error messages. The problem I have is that insert SQL statement doesn't seem to work. Using the examples from here: http://bs2000.fujitsu-siemens.com/download/Goodies/SMAWPlus/SMAWPsqli/html/s qlite.1.html it doesn't like the exalamation point inside quoted strings. If I use Notepad to view the database file I can see the create table command that I sent to it. But after the inserts in the example link above the table contains no data. Mel Stober Tri-Cor Industries Inc./GATES AIT 618-632-9252 Ext 423
RE: [sqlite] still having problems with DBD::SQLite2
I am pretty sure I know whats going on now. I am using POE (Perl Object environment, I highly recommend it poe.perl.org) and what is happening is my program is basically trying to do inserts into the database at the same time, which I think is creating a deadlock. It can handle doing one insert at one time, but when I fire a lot of events at it that are kind of happening asynchronously on the server, it fails. It would be nice if the debugging and logging output made some sort of damn sense or would tell you these things.. I think I have had my head up my ass all day cuz of this. I am going to try do some stuff in my program that will "pause" all the other helper "threads" when I'm doing a sql insert. -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 3:55 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 3:45 PM -0500 10/8/04, Freeman, Michael wrote: >The script and the database live and are called from in the same >directory. As an experiment, try putting the code from your two scripts into the same script. The combined script would first run the code for creating the database, then run the code for updating it. Both parts do an open() and a close(), and both use the unqalified file name. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: I am pretty sure I know whats going on now. I am using POE (Perl Object environment, I highly recommend it poe.perl.org) and what is happening is my program is basically trying to do inserts into the database at the same time, which I think is creating a deadlock. It can handle doing one insert at one time, but when I fire a lot of events at it that are kind of happening asynchronously on the server, it fails. It would be nice if the debugging and logging output made some sort of damn sense or would tell you these things.. I think I have had my head up my ass all day cuz of this. I am going to try do some stuff in my program that will "pause" all the other helper "threads" when I'm doing a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
Re: [sqlite] WinCE port
Stober, Mel, dando pulos de alegria, escreveu : I created an MFC for WinCE version of shell.c to make it easier to enter text and see error messages. The problem I have is that insert SQL statement doesn't seem to work. Using the examples from here: http://bs2000.fujitsu-siemens.com/download/Goodies/SMAWPlus/SMAWPsqli/html/s qlite.1.html it doesn't like the exalamation point inside quoted strings. I advise you to check your code as the example is pretty common. As an aside, Paul Sokolovsky succeeded in porting TCL 8.4.6 into an iPaq 4150, using an arm-wince-gcc port. With that it achieved running the full test suite on sqlite 2.8.13 and all relevant single-threaded tests passed. I'm very grateful to him as I'm more confident now on the status of the port :) This unfortunely doesn't apply to 3.0.x, but I think it's a good sign :) Best regards, ~Nuno Lucas
[sqlite] Questions about sqlite's join translation
The swiki says that making JOINs into a where clause is more efficient, since sqlite translates the join condition into a where clause. It also says that you make queries more effiecient by minimizing the number of rows returned in the FROM clause as far to the left as possible in the join. Does the latter matter if you are translating everything into a where clause anyway? --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] still having problems with DBD::SQLite2
Well, you can always synchronize access and share the same pointer, right (in windows; using fork in unix is bad, presumably because fork() just copies all the data into the child)? It sucks if you are using sql_step, or, I imagine, precompiled queries, though. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 4:23 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: >I am pretty sure I know whats going on now. I am using POE (Perl Object >environment, I highly recommend it poe.perl.org) and what is happening >is my program is basically trying to do inserts into the database at >the same time, which I think is creating a deadlock. It can handle >doing one insert at one time, but when I fire a lot of events at it >that are kind of happening asynchronously on the server, it fails. It >would be nice if the debugging and logging output made some sort of >damn sense or would tell you these things.. I think I have had my head >up my ass all day cuz of this. I am going to try do some stuff in my >program that will "pause" all the other helper "threads" when I'm doing >a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
Re: [sqlite] Questions about sqlite's join translation
Keith Herold wrote: The swiki says that making JOINs into a where clause is more efficient, since sqlite translates the join condition into a where clause. When SQLite sees this: SELECT * FROM a JOIN b ON a.x=b.y; It translate it into the following before compiling it: SELECT * FROM a, b WHERE a.x=b.y; Neither form is more efficient that the other. Both will generate identical code. (There are subtle differences on an LEFT OUTER JOIN, but those details can be ignored when you are looking at things at a high level, as we are.) > It also says that you make queries more effiecient by minimizing the number of rows returned in the FROM clause as far to the left as possible in the join. Does the latter matter if you are translating everything into a where clause anyway? SQLite implements joins using nested loops with the outer loop formed by the first table in the join and the inner loop formed by the last table in the join. So for the example above you would have: For each row in a: For each row in b such that b.y=a.x: Return the row If you reverse the order of the tables in the FROM clause like this: SELECT * FROM b, a WHERE a.x=b.y; You should get an equivalent result on output, but SQLite will implement the query differently. Specifically it does this: For each row in b: For each row in a such that a.x=b.y: Return the row The trick is that you want to arrange the order of tables so that the "such that" clause on the inner loop is able to use an index to jump right to the appropriate row instead of having to do a full table scan. Suppose, for example, that you have an index on a(x) but not on b(y). Then if you do this: SELECT * FROM a, b WHERE a.x=b.y; For each row in a: For each row in b such that b.y=a.x: Return the row For each row in a, you have to do a full scan of table b. So the time complexity will be O(N^2). But if you reverse the order of the tables in the FROM clause, like this: SELECT * FROM b, a WHERE b.y=a.x; For each row in b: For each row in a such that a.x=b.y Return the row No the inner loop is able to use an index to jump directly to the rows in a that it needs and does not need to do a full scan of the table. The time complexity drops to O(NlogN). So the rule should be: For every table other than the first, make sure there is a term in the WHERE clause (or the ON or USING clause if that is your preference) that lets the search jump directly to the relavant rows in that table based on the results from tables to the left. Other database engines with more complex query optimizers will typically attempt to reorder the tables in the FROM clause in order to give you the best result. SQLite is more simple-minded - it codes whatever you tell it to code. Before you ask, I'll point out that it makes no different whether you say "a.x=b.y" or "b.y=a.x". They are equivalent. All of the following generate the same code: ON a.x=b.y ON b.y=a.x WHERE a.x=b.y WHERE b.y=a.x -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
