RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

I apologise. Just discoverd that it is a Berkely db file. I am really sorry
to waste your time on this. Many thanks, though, for your assistance.

Mark

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 6:11 p.m.
> To: [EMAIL PROTECTED]
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote:
> > Sorry, but how do I check the version of  DBD::SQLite I have 
> > installed? Perl is not my thing. When I look in the Perl code I can 
> > see DBIx::SimplePerl commands. I ran the scipt below but it 
> did not show any installed modules.
> 
> Use cpan. As you can see below, I have DBD::SQLite version 
> 1.14 installed. That uses SQLite 3.4.2 (you can check the 
> change log on CPAN).
> 
> $ cpan
> CPAN: File::HomeDir loaded ok (v0.58)
> 
> cpan shell -- CPAN exploration and modules installation 
> (v1.9205) ReadLine support enabled
> 
> cpan[1]> i DBD::SQLite
> CPAN: Storable loaded ok (v2.15)
> Going to read /Users/punkish/.cpan/Metadata
>   Database was generated on Tue, 04 Dec 2007 23:37:19 GMT 
> Strange distribution name [DBD::SQLite] Module id = DBD::SQLite
> CPAN_USERID  MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>)
> CPAN_VERSION 1.14
> CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz
> UPLOAD_DATE  2007-09-19
> MANPAGE  DBD::SQLite - Self Contained RDBMS in a DBI Driver
> INST_FILE
> /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm
> INST_VERSION 1.14
> 
> 
>  cpan[2]>
> 
> 
> >
> > "#!/usr/bin/perl
> >
> > use CPAN;
> >
> > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");
> >
> > foreach $a (@ARGV) {
> >   foreach $mod (CPAN::Shell->expand("Module", $a)){
> > printf("%-20s %10s %10s %s\n",
> >   $mod->id,
> >   $mod->inst_version eq "undef" || !defined($mod->inst_version)
> > ? "-" : $mod->inst_version,
> >   $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
> > ? "-" : $mod->cpan_version,
> >   $mod->uptodate ? "" : "*"
> > );
> >   }
> > }"
> >
> >
> >
> > Thanks
> >
> >
> >
> > > -Original Message-
> > > From: P Kishor [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 5:44 p.m.
> > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > > > I have also tried v 3.5.3 now and still cannot open the 
> database. 
> > > > I guess I need the right version of sqlite3 so that I 
> can dump to 
> > > > sql and then I can rebuild in a newer version. But how do I
> > > find our what
> > > > version of sqlite I need?
> > > >
> > > >
> > > > > -Original Message-
> > > > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > > > To: sqlite-users@sqlite.org
> > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > > > >
> > > > > I have a sqlite db file. I have tried to open it with 
> sqlite v 
> > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give
> > > me the same
> > > > > error "File Is Encrypted Or Is Not a Database". How can I
> > > determine
> > > > > what version of sqlite will open this db? The perl
> > > application can
> > > > > open the database using the perl module it has. But I 
> cant get 
> > > > > command line access to the db. Any ideas?
> > > > >
> > >
> > >
> > > If you can open the database with Perl then it is not encrypted. 
> > > Check the version of DBD::SQLite you have installed. Its 
> docs will 
> > > tell what version of SQLite it has compiled in.
> > >
> > > Then, are you sure you are trying to open it with the right 
> > > versioned SQLite? Try
> > >
> > > $ which sqlite3
> > >
> > > to find out if you are inadvertently picking up an old SQLite 
> > > program installed somewhere in your path.
> > >
> > > --
> >
> > > ---
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > --
> > > ---
> > >
> >
> >
> 
> 
> 
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies 
> http://www.nelson.wisc.edu/ Open Source Geospatial Foundation 
> (OSGeo) http://www.osgeo.org/ Summer 2007 S Policy Fellow, 
> The National Academies http://www.nas.edu/
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

The db I am trying to open is attached. Thanks

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 6:11 p.m.
> To: [EMAIL PROTECTED]
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote:
> > Sorry, but how do I check the version of  DBD::SQLite I have 
> > installed? Perl is not my thing. When I look in the Perl code I can 
> > see DBIx::SimplePerl commands. I ran the scipt below but it 
> did not show any installed modules.
> 
> Use cpan. As you can see below, I have DBD::SQLite version 
> 1.14 installed. That uses SQLite 3.4.2 (you can check the 
> change log on CPAN).
> 
> $ cpan
> CPAN: File::HomeDir loaded ok (v0.58)
> 
> cpan shell -- CPAN exploration and modules installation 
> (v1.9205) ReadLine support enabled
> 
> cpan[1]> i DBD::SQLite
> CPAN: Storable loaded ok (v2.15)
> Going to read /Users/punkish/.cpan/Metadata
>   Database was generated on Tue, 04 Dec 2007 23:37:19 GMT 
> Strange distribution name [DBD::SQLite] Module id = DBD::SQLite
> CPAN_USERID  MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>)
> CPAN_VERSION 1.14
> CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz
> UPLOAD_DATE  2007-09-19
> MANPAGE  DBD::SQLite - Self Contained RDBMS in a DBI Driver
> INST_FILE
> /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm
> INST_VERSION 1.14
> 
> 
>  cpan[2]>
> 
> 
> >
> > "#!/usr/bin/perl
> >
> > use CPAN;
> >
> > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");
> >
> > foreach $a (@ARGV) {
> >   foreach $mod (CPAN::Shell->expand("Module", $a)){
> > printf("%-20s %10s %10s %s\n",
> >   $mod->id,
> >   $mod->inst_version eq "undef" || !defined($mod->inst_version)
> > ? "-" : $mod->inst_version,
> >   $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
> > ? "-" : $mod->cpan_version,
> >   $mod->uptodate ? "" : "*"
> > );
> >   }
> > }"
> >
> >
> >
> > Thanks
> >
> >
> >
> > > -Original Message-
> > > From: P Kishor [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 5:44 p.m.
> > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > > > I have also tried v 3.5.3 now and still cannot open the 
> database. 
> > > > I guess I need the right version of sqlite3 so that I 
> can dump to 
> > > > sql and then I can rebuild in a newer version. But how do I
> > > find our what
> > > > version of sqlite I need?
> > > >
> > > >
> > > > > -Original Message-
> > > > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > > > To: sqlite-users@sqlite.org
> > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > > > >
> > > > > I have a sqlite db file. I have tried to open it with 
> sqlite v 
> > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give
> > > me the same
> > > > > error "File Is Encrypted Or Is Not a Database". How can I
> > > determine
> > > > > what version of sqlite will open this db? The perl
> > > application can
> > > > > open the database using the perl module it has. But I 
> cant get 
> > > > > command line access to the db. Any ideas?
> > > > >
> > >
> > >
> > > If you can open the database with Perl then it is not encrypted. 
> > > Check the version of DBD::SQLite you have installed. Its 
> docs will 
> > > tell what version of SQLite it has compiled in.
> > >
> > > Then, are you sure you are trying to open it with the right 
> > > versioned SQLite? Try
> > >
> > > $ which sqlite3
> > >
> > > to find out if you are inadvertently picking up an old SQLite 
> > > program installed somewhere in your path.
> > >
> > > --
> >
> > > ---
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > --
> > > ---
> > >
> >
> >
> 
> 
> 
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies 
> http://www.nelson.wisc.edu/ Open Source Geospatial Foundation 
> (OSGeo) http://www.osgeo.org/ Summer 2007 S Policy Fellow, 
> The National Academies http://www.nas.edu/
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

 Sorry ... how do I get sqlite v3.4.2? 

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 6:11 p.m.
> To: [EMAIL PROTECTED]
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote:
> > Sorry, but how do I check the version of  DBD::SQLite I have 
> > installed? Perl is not my thing. When I look in the Perl code I can 
> > see DBIx::SimplePerl commands. I ran the scipt below but it 
> did not show any installed modules.
> 
> Use cpan. As you can see below, I have DBD::SQLite version 
> 1.14 installed. That uses SQLite 3.4.2 (you can check the 
> change log on CPAN).
> 
> $ cpan
> CPAN: File::HomeDir loaded ok (v0.58)
> 
> cpan shell -- CPAN exploration and modules installation 
> (v1.9205) ReadLine support enabled
> 
> cpan[1]> i DBD::SQLite
> CPAN: Storable loaded ok (v2.15)
> Going to read /Users/punkish/.cpan/Metadata
>   Database was generated on Tue, 04 Dec 2007 23:37:19 GMT 
> Strange distribution name [DBD::SQLite] Module id = DBD::SQLite
> CPAN_USERID  MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>)
> CPAN_VERSION 1.14
> CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz
> UPLOAD_DATE  2007-09-19
> MANPAGE  DBD::SQLite - Self Contained RDBMS in a DBI Driver
> INST_FILE
> /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm
> INST_VERSION 1.14
> 
> 
>  cpan[2]>
> 
> 
> >
> > "#!/usr/bin/perl
> >
> > use CPAN;
> >
> > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");
> >
> > foreach $a (@ARGV) {
> >   foreach $mod (CPAN::Shell->expand("Module", $a)){
> > printf("%-20s %10s %10s %s\n",
> >   $mod->id,
> >   $mod->inst_version eq "undef" || !defined($mod->inst_version)
> > ? "-" : $mod->inst_version,
> >   $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
> > ? "-" : $mod->cpan_version,
> >   $mod->uptodate ? "" : "*"
> > );
> >   }
> > }"
> >
> >
> >
> > Thanks
> >
> >
> >
> > > -Original Message-
> > > From: P Kishor [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 5:44 p.m.
> > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > > > I have also tried v 3.5.3 now and still cannot open the 
> database. 
> > > > I guess I need the right version of sqlite3 so that I 
> can dump to 
> > > > sql and then I can rebuild in a newer version. But how do I
> > > find our what
> > > > version of sqlite I need?
> > > >
> > > >
> > > > > -Original Message-
> > > > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > > > To: sqlite-users@sqlite.org
> > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > > > >
> > > > > I have a sqlite db file. I have tried to open it with 
> sqlite v 
> > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give
> > > me the same
> > > > > error "File Is Encrypted Or Is Not a Database". How can I
> > > determine
> > > > > what version of sqlite will open this db? The perl
> > > application can
> > > > > open the database using the perl module it has. But I 
> cant get 
> > > > > command line access to the db. Any ideas?
> > > > >
> > >
> > >
> > > If you can open the database with Perl then it is not encrypted. 
> > > Check the version of DBD::SQLite you have installed. Its 
> docs will 
> > > tell what version of SQLite it has compiled in.
> > >
> > > Then, are you sure you are trying to open it with the right 
> > > versioned SQLite? Try
> > >
> > > $ which sqlite3
> > >
> > > to find out if you are inadvertently picking up an old SQLite 
> > > program installed somewhere in your path.
> > >
> > > --
> >
> > > ---
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > --
> > > ---
> > >
> >
> >
> 
> 
> 
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies 
> http://www.nelson.wisc.edu/ Open Source Geospatial Foundation 
> (OSGeo) http://www.osgeo.org/ Summer 2007 S Policy Fellow, 
> The National Academies http://www.nas.edu/
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

 Aahh, ok - I have the same version. Many thanks. I will try that version of
sqlite then. Fingers crossed. :)

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 6:11 p.m.
> To: [EMAIL PROTECTED]
> Cc: sqlite-users@sqlite.org
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote:
> > Sorry, but how do I check the version of  DBD::SQLite I have 
> > installed? Perl is not my thing. When I look in the Perl code I can 
> > see DBIx::SimplePerl commands. I ran the scipt below but it 
> did not show any installed modules.
> 
> Use cpan. As you can see below, I have DBD::SQLite version 
> 1.14 installed. That uses SQLite 3.4.2 (you can check the 
> change log on CPAN).
> 
> $ cpan
> CPAN: File::HomeDir loaded ok (v0.58)
> 
> cpan shell -- CPAN exploration and modules installation 
> (v1.9205) ReadLine support enabled
> 
> cpan[1]> i DBD::SQLite
> CPAN: Storable loaded ok (v2.15)
> Going to read /Users/punkish/.cpan/Metadata
>   Database was generated on Tue, 04 Dec 2007 23:37:19 GMT 
> Strange distribution name [DBD::SQLite] Module id = DBD::SQLite
> CPAN_USERID  MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>)
> CPAN_VERSION 1.14
> CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz
> UPLOAD_DATE  2007-09-19
> MANPAGE  DBD::SQLite - Self Contained RDBMS in a DBI Driver
> INST_FILE
> /usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm
> INST_VERSION 1.14
> 
> 
>  cpan[2]>
> 
> 
> >
> > "#!/usr/bin/perl
> >
> > use CPAN;
> >
> > printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");
> >
> > foreach $a (@ARGV) {
> >   foreach $mod (CPAN::Shell->expand("Module", $a)){
> > printf("%-20s %10s %10s %s\n",
> >   $mod->id,
> >   $mod->inst_version eq "undef" || !defined($mod->inst_version)
> > ? "-" : $mod->inst_version,
> >   $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
> > ? "-" : $mod->cpan_version,
> >   $mod->uptodate ? "" : "*"
> > );
> >   }
> > }"
> >
> >
> >
> > Thanks
> >
> >
> >
> > > -Original Message-
> > > From: P Kishor [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 5:44 p.m.
> > > To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> > > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > > > I have also tried v 3.5.3 now and still cannot open the 
> database. 
> > > > I guess I need the right version of sqlite3 so that I 
> can dump to 
> > > > sql and then I can rebuild in a newer version. But how do I
> > > find our what
> > > > version of sqlite I need?
> > > >
> > > >
> > > > > -Original Message-
> > > > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > > > To: sqlite-users@sqlite.org
> > > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > > > >
> > > > > I have a sqlite db file. I have tried to open it with 
> sqlite v 
> > > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give
> > > me the same
> > > > > error "File Is Encrypted Or Is Not a Database". How can I
> > > determine
> > > > > what version of sqlite will open this db? The perl
> > > application can
> > > > > open the database using the perl module it has. But I 
> cant get 
> > > > > command line access to the db. Any ideas?
> > > > >
> > >
> > >
> > > If you can open the database with Perl then it is not encrypted. 
> > > Check the version of DBD::SQLite you have installed. Its 
> docs will 
> > > tell what version of SQLite it has compiled in.
> > >
> > > Then, are you sure you are trying to open it with the right 
> > > versioned SQLite? Try
> > >
> > > $ which sqlite3
> > >
> > > to find out if you are inadvertently picking up an old SQLite 
> > > program installed somewhere in your path.
> > >
> > > --
> >
> > > ---
> > > To unsubscribe, send email to [EMAIL PROTECTED]
> > > --
> > > ---
> > >
> >
> >
> 
> 
> 
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies 
> http://www.nelson.wisc.edu/ Open Source Geospatial Foundation 
> (OSGeo) http://www.osgeo.org/ Summer 2007 S Policy Fellow, 
> The National Academies http://www.nas.edu/
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] File Is Encrypted Or Is Not a Database

[P Kishor]

On Dec 4, 2007 11:02 PM, Mark Easton <[EMAIL PROTECTED]> wrote:
> Sorry, but how do I check the version of  DBD::SQLite I have installed? Perl
> is not my thing. When I look in the Perl code I can see DBIx::SimplePerl
> commands. I ran the scipt below but it did not show any installed modules.

Use cpan. As you can see below, I have DBD::SQLite version 1.14
installed. That uses SQLite 3.4.2 (you can check the change log on
CPAN).

$ cpan
CPAN: File::HomeDir loaded ok (v0.58)

cpan shell -- CPAN exploration and modules installation (v1.9205)
ReadLine support enabled

cpan[1]> i DBD::SQLite
CPAN: Storable loaded ok (v2.15)
Going to read /Users/punkish/.cpan/Metadata
  Database was generated on Tue, 04 Dec 2007 23:37:19 GMT
Strange distribution name [DBD::SQLite]
Module id = DBD::SQLite
CPAN_USERID  MSERGEANT (MSERGEANT <[EMAIL PROTECTED]>)
CPAN_VERSION 1.14
CPAN_FILEM/MS/MSERGEANT/DBD-SQLite-1.14.tar.gz
UPLOAD_DATE  2007-09-19
MANPAGE  DBD::SQLite - Self Contained RDBMS in a DBI Driver
INST_FILE
/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/DBD/SQLite.pm
INST_VERSION 1.14


 cpan[2]>


>
> "#!/usr/bin/perl
>
> use CPAN;
>
> printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");
>
> foreach $a (@ARGV) {
>   foreach $mod (CPAN::Shell->expand("Module", $a)){
> printf("%-20s %10s %10s %s\n",
>   $mod->id,
>   $mod->inst_version eq "undef" || !defined($mod->inst_version)
> ? "-" : $mod->inst_version,
>   $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
> ? "-" : $mod->cpan_version,
>   $mod->uptodate ? "" : "*"
> );
>   }
> }"
>
>
>
> Thanks
>
>
>
> > -Original Message-
> > From: P Kishor [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, 5 December 2007 5:44 p.m.
> > To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> > Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> >
> > On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > > I have also tried v 3.5.3 now and still cannot open the database. I
> > > guess I need the right version of sqlite3 so that I can dump to sql
> > > and then I can rebuild in a newer version. But how do I
> > find our what
> > > version of sqlite I need?
> > >
> > >
> > > > -Original Message-
> > > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > > To: sqlite-users@sqlite.org
> > > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > > >
> > > > I have a sqlite db file. I have tried to open it with sqlite v
> > > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give
> > me the same
> > > > error "File Is Encrypted Or Is Not a Database". How can I
> > determine
> > > > what version of sqlite will open this db? The perl
> > application can
> > > > open the database using the perl module it has. But I cant get
> > > > command line access to the db. Any ideas?
> > > >
> >
> >
> > If you can open the database with Perl then it is not
> > encrypted. Check the version of DBD::SQLite you have
> > installed. Its docs will tell what version of SQLite it has
> > compiled in.
> >
> > Then, are you sure you are trying to open it with the right
> > versioned SQLite? Try
> >
> > $ which sqlite3
> >
> > to find out if you are inadvertently picking up an old SQLite
> > program installed somewhere in your path.
> >
> > --
>
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
> >
>
>



-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

I am sure on the versions of sqlite I have been using. When yoy run them it
always shows the version of sqlite you are running. This is very very
frustrating. I have a sqlite db which the Perl app is opening fine using
DBIx::SimplePerl, yet U cannnot open the db from command line. It seems more
than a little strange that I cannot determine the version of a sqlite db.  

> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 5:44 p.m.
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > I have also tried v 3.5.3 now and still cannot open the database. I 
> > guess I need the right version of sqlite3 so that I can dump to sql 
> > and then I can rebuild in a newer version. But how do I 
> find our what 
> > version of sqlite I need?
> >
> >
> > > -Original Message-
> > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > To: sqlite-users@sqlite.org
> > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > I have a sqlite db file. I have tried to open it with sqlite v 
> > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give 
> me the same 
> > > error "File Is Encrypted Or Is Not a Database". How can I 
> determine 
> > > what version of sqlite will open this db? The perl 
> application can 
> > > open the database using the perl module it has. But I cant get 
> > > command line access to the db. Any ideas?
> > >
> 
> 
> If you can open the database with Perl then it is not 
> encrypted. Check the version of DBD::SQLite you have 
> installed. Its docs will tell what version of SQLite it has 
> compiled in.
> 
> Then, are you sure you are trying to open it with the right 
> versioned SQLite? Try
> 
> $ which sqlite3
> 
> to find out if you are inadvertently picking up an old SQLite 
> program installed somewhere in your path.
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

Sorry, but how do I check the version of  DBD::SQLite I have installed? Perl
is not my thing. When I look in the Perl code I can see DBIx::SimplePerl
commands. I ran the scipt below but it did not show any installed modules.

"#!/usr/bin/perl

use CPAN;

printf("%-20s %10s %10s\n", "Module", "Installed", "CPAN");

foreach $a (@ARGV) {
  foreach $mod (CPAN::Shell->expand("Module", $a)){
printf("%-20s %10s %10s %s\n",
  $mod->id,
  $mod->inst_version eq "undef" || !defined($mod->inst_version)
? "-" : $mod->inst_version,
  $mod->cpan_version eq "undef" || !defined($mod->cpan_version)
? "-" : $mod->cpan_version,
  $mod->uptodate ? "" : "*"
);
  }
}"



Thanks


> -Original Message-
> From: P Kishor [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 5:44 p.m.
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] File Is Encrypted Or Is Not a Database
> 
> On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> > I have also tried v 3.5.3 now and still cannot open the database. I 
> > guess I need the right version of sqlite3 so that I can dump to sql 
> > and then I can rebuild in a newer version. But how do I 
> find our what 
> > version of sqlite I need?
> >
> >
> > > -Original Message-
> > > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > > To: sqlite-users@sqlite.org
> > > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> > >
> > > I have a sqlite db file. I have tried to open it with sqlite v 
> > > 3.4.1, 2.8.17 and 3.3.5 and each of these versions give 
> me the same 
> > > error "File Is Encrypted Or Is Not a Database". How can I 
> determine 
> > > what version of sqlite will open this db? The perl 
> application can 
> > > open the database using the perl module it has. But I cant get 
> > > command line access to the db. Any ideas?
> > >
> 
> 
> If you can open the database with Perl then it is not 
> encrypted. Check the version of DBD::SQLite you have 
> installed. Its docs will tell what version of SQLite it has 
> compiled in.
> 
> Then, are you sure you are trying to open it with the right 
> versioned SQLite? Try
> 
> $ which sqlite3
> 
> to find out if you are inadvertently picking up an old SQLite 
> program installed somewhere in your path.
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite locking mechanism with threads

[Ken]

99.9% certain this is not a sqlite issue. But a script language issue.
   
  It depends on how the script language implements threads. 
   
  Try writing this in C and using posix threads.
   
  You'll also need to have multiple cpu's to really take advantage of threading 
as well.

yahalome <[EMAIL PROTECTED]> wrote:
  I work with sqlite 3.5.3. 
I experimented with threads and I get a strange behavior. it looks 
like one thread takes over sqlite and does not leave it until it is 
finished even though I tell it to wait (using random after). This is 
the script I use: 
package require sqlite3 
package require Thread 

sqlite3 conn test 
catch {conn eval "drop table a"} 
conn eval "create table a (b varchar(10))" 
set t1 [thread::create] 
set t2 [thread::create] 
set b { 
package require sqlite3 
sqlite3 conn test 
conn timeout 10 
for {set i 0} {$i<10} {incr i} { 
puts $i-[thread::id] 
conn eval "insert into a values ($i-[thread::id])" 
£ wait for randomal time so other thread might take over sqlite 
after [expr {int(rand()*100)}] 

} 
thread::exit 
} 

thread:::send -async $t1 $b 
thread:::send -async $t2 $b 
thread::wait 

the result is: 
0-65539 
0-81924 
1-65539 
2-65539 
3-65539 
4-65539 
5-65539 
6-65539 
7-65539 
8-65539 
9-65539 
1-81924 
2-81924 
3-81924 
4-81924 
5-81924 
6-81924 
7-81924 
8-81924 
9-81924 

why in the start both threads get access? 
why after it only thread 1 access it and thread 2 starts only when 
thread 1 finishes. the behavior changes if I increate the after but it 
looks like sqlite does not retry for a long time after it meets a 
lock. 





Yahalom Emet

XPO Team

Xor Financial Solutions





Email: [EMAIL PROTECTED]

www.XorTechnologies.com 

Re: [sqlite] File Is Encrypted Or Is Not a Database

[P Kishor]

On 12/4/07, Mark Easton <[EMAIL PROTECTED]> wrote:
> I have also tried v 3.5.3 now and still cannot open the database. I guess I
> need the right version of sqlite3 so that I can dump to sql and then I can
> rebuild in a newer version. But how do I find our what version of sqlite I
> need?
>
>
> > -Original Message-
> > From: Mark Easton [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, 5 December 2007 3:58 p.m.
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] File Is Encrypted Or Is Not a Database
> >
> > I have a sqlite db file. I have tried to open it with sqlite
> > v 3.4.1, 2.8.17 and 3.3.5 and each of these versions give me
> > the same error "File Is Encrypted Or Is Not a Database". How
> > can I determine what version of sqlite will open this db? The
> > perl application can open the database using the perl module
> > it has. But I cant get command line access to the db. Any ideas?
> >


If you can open the database with Perl then it is not encrypted. Check
the version of DBD::SQLite you have installed. Its docs will tell what
version of SQLite it has compiled in.

Then, are you sure you are trying to open it with the right versioned
SQLite? Try

$ which sqlite3

to find out if you are inadvertently picking up an old SQLite program
installed somewhere in your path.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

I have also tried v 3.5.3 now and still cannot open the database. I guess I
need the right version of sqlite3 so that I can dump to sql and then I can
rebuild in a newer version. But how do I find our what version of sqlite I
need?


> -Original Message-
> From: Mark Easton [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, 5 December 2007 3:58 p.m.
> To: sqlite-users@sqlite.org
> Subject: [sqlite] File Is Encrypted Or Is Not a Database
> 
> I have a sqlite db file. I have tried to open it with sqlite 
> v 3.4.1, 2.8.17 and 3.3.5 and each of these versions give me 
> the same error "File Is Encrypted Or Is Not a Database". How 
> can I determine what version of sqlite will open this db? The 
> perl application can open the database using the perl module 
> it has. But I cant get command line access to the db. Any ideas?
> 
> Thanks
> Mark
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite locking mechanism with threads

[yahalome]

I work with sqlite 3.5.3. 
I experimented with threads and I get a strange behavior. it looks 
like one thread takes over sqlite and does not leave it until it is 
finished even though I tell it to wait (using random after). This is 
the script I use: 
package require sqlite3 
package require Thread 

sqlite3 conn test 
catch {conn eval "drop  table a"} 
conn eval "create table a (b varchar(10))" 
set t1 [thread::create] 
set t2 [thread::create] 
set b { 
package require sqlite3 
sqlite3 conn test 
conn timeout 10 
for {set i 0} {$i<10} {incr i} { 
puts $i-[thread::id] 
conn eval "insert into a values ($i-[thread::id])" 
£ wait for randomal time so other thread might take over sqlite 
after [expr {int(rand()*100)}] 

} 
thread::exit 
} 

thread:::send -async $t1 $b 
thread:::send -async $t2 $b 
thread::wait 

the result is: 
0-65539 
0-81924 
1-65539 
2-65539 
3-65539 
4-65539 
5-65539 
6-65539 
7-65539 
8-65539 
9-65539 
1-81924 
2-81924 
3-81924 
4-81924 
5-81924 
6-81924 
7-81924 
8-81924 
9-81924 

why in the start both threads get access? 
why after it only thread 1 access it and thread 2 starts only when 
thread 1 finishes. the behavior changes if I increate the after but it 
looks like sqlite does not retry for a long time after it meets a 
lock. 

 

 

Yahalom Emet

XPO Team

Xor Financial Solutions

 

 

Email:   [EMAIL PROTECTED]

www.XorTechnologies.com  

 

[sqlite] File Is Encrypted Or Is Not a Database

[Mark Easton]

I have a sqlite db file. I have tried to open it with sqlite v 3.4.1, 2.8.17
and 3.3.5 and each of these versions give me the same error "File Is
Encrypted Or Is Not a Database". How can I determine what version of sqlite
will open this db? The perl application can open the database using the perl
module it has. But I cant get command line access to the db. Any ideas?

Thanks
Mark

Re: [sqlite] SQLite is in Android

[P Kishor]

On 12/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Richard Klein <[EMAIL PROTECTED]> wrote:
> > Surely I'm not the first person to notice that
> > SQLite is part of Google's open-source Android
> > platform for mobile phones.
> >
>
> I've had Android listed at http://www.sqlite.org/famous.html
> since the day it was announced.
>

I was curious... isn't Apple's use of SQLite a lot more extensive than
listed on your page? I believe they use it for file metadata (used in
Spotlight searches), for Time Machine, and the fact that they provide
the option for storing data in SQLite format as part of their Core
Data framework. It is really almost built into the operating system in
a manner of speaking.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] search time in FTS3 tables sometimes very long

[Scott Hess]

2007/12/4 Scott Hess <[EMAIL PROTECTED]>:
> This seems a little excessive, though.  I do see that there's an
> O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
> docListUnion()).  I can reasonably make that O(logN), which might help
> a great deal, if you're hitting it.  Not really sure how to tell if
> you're hitting it, but I'll experiment at my end and see whether I can
> improve things there.

With the attached patch, the time to match against 't*' with the rfc
dataset goes from 1m16s to 5s.

It passes the tests, but I'll not guarantee that this is what I'll
check in.  I want to think on it.  But let me know if this doesn't
help.

-scott
Index: ext/fts3/fts3.c
===
RCS file: /sqlite/sqlite/ext/fts3/fts3.c,v
retrieving revision 1.12
diff -u -r1.12 fts3.c
--- ext/fts3/fts3.c 24 Nov 2007 00:41:52 -  1.12
+++ ext/fts3/fts3.c 5 Dec 2007 01:03:32 -
@@ -,6 +,21 @@
   return rc;
 }
 
+/* Call docListUnion() to merge *left and *right into *out, destroying
+** *left and *right.  Handles left or right in the same location as
+** out (in-place merge).
+*/
+static void docListUnionWithDestroy(DataBuffer *left, DataBuffer *right,
+DataBuffer *out) {
+  DataBuffer result;
+  dataBufferInit(, left->nData+right->nData);
+  docListUnion(left->pData, left->nData, right->pData, right->nData,
+   );
+  dataBufferDestroy(left);
+  dataBufferDestroy(right);
+  *out = result;
+}
+
 /* Scan pReader for pTerm/nTerm, and merge the term's doclist over
 ** *out (any doclists with duplicate docids overwrite those in *out).
 ** Internal function for loadSegmentLeaf().
@@ -5562,6 +5577,15 @@
 static int loadSegmentLeavesInt(fulltext_vtab *v, LeavesReader *pReader,
 const char *pTerm, int nTerm, int isPrefix,
 DataBuffer *out){
+  /* To keep merging O(logN), keep a list of merged buffers.  After
+  ** each doclist is added, buffers are merged for the number of 0
+  ** low-order bits in nDoclists.  So this many doclists are merged at
+  ** each point: 0, 1, 0, 2, 0, 1, 0, 4, 0, 1, 0, 2, ... (like a
+  ** tree).
+  */
+  DataBuffer *pBuffers = NULL;
+  int nBuffers = 0, nMaxBuffers = 0, nDoclists = 0;
+
   assert( nTerm>0 );
 
   /* Process while the prefix matches. */
@@ -5575,24 +5599,54 @@
 int c = leafReaderTermCmp(>leafReader, pTerm, nTerm, isPrefix);
 if( c==0 ){
   const char *pData = leavesReaderData(pReader);
-  int nData = leavesReaderDataBytes(pReader);
-  if( out->nData==0 ){
-dataBufferReplace(out, pData, nData);
-  }else{
-DataBuffer result;
-dataBufferInit(, out->nData+nData);
-docListUnion(out->pData, out->nData, pData, nData, );
-dataBufferDestroy(out);
-*out = result;
-/* TODO(shess) Rather than destroy out, we could retain it for
-** later reuse.
-*/
+  int n, nData = leavesReaderDataBytes(pReader);
+  if( nBuffers==nMaxBuffers ){
+++nMaxBuffers;
+pBuffers = sqlite3_realloc(pBuffers, nMaxBuffers*sizeof(*pBuffers));
+  }
+  dataBufferInit(&(pBuffers[nBuffers]), nData);
+  dataBufferReplace(&(pBuffers[nBuffers]), pData, nData);
+  nBuffers++;
+  assert(nBuffers<=nMaxBuffers);
+
+  ++nDoclists;
+  for( n=nDoclists; (n%2)==0; n>>=1) {
+assert(n);  /* Can't happen if nDoclists!=0. */
+docListUnionWithDestroy(&(pBuffers[nBuffers-2]),
+&(pBuffers[nBuffers-1]),
+&(pBuffers[nBuffers-2]));
+nBuffers--;
   }
 }
 if( c>0 ) break;  /* Past any possible matches. */
 
 rc = leavesReaderStep(v, pReader);
-if( rc!=SQLITE_OK ) return rc;
+if( rc!=SQLITE_OK ){
+  while( nBuffers>0 ){
+nBuffers--;
+dataBufferDestroy(&(pBuffers[nBuffers]));
+  }
+  sqlite3_free(pBuffers);
+  return rc;
+}
+  }
+  while( nBuffers>1 ){
+docListUnionWithDestroy(&(pBuffers[nBuffers-2]),
+&(pBuffers[nBuffers-1]),
+&(pBuffers[nBuffers-2]));
+nBuffers--;
+  }
+  if( nBuffers>0 ){
+assert(1==nBuffers);
+if( out->nData==0 ){
+  dataBufferDestroy(out);
+  *out = pBuffers[0];
+}else{
+  docListUnionWithDestroy(out, &(pBuffers[0]), out);
+}
+sqlite3_free(pBuffers);
+  } else {
+assert(NULL==pBuffers);
   }
   return SQLITE_OK;
 }
-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite is in Android

[drh]

Richard Klein <[EMAIL PROTECTED]> wrote:
> Surely I'm not the first person to notice that
> SQLite is part of Google's open-source Android
> platform for mobile phones.
> 

I've had Android listed at http://www.sqlite.org/famous.html
since the day it was announced.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite is in Android

[Richard Klein]

Surely I'm not the first person to notice that
SQLite is part of Google's open-source Android
platform for mobile phones.

This is a *huge* win for SQLite ...

- Richard Klein

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] search time in FTS3 tables sometimes very long

[Scott Hess]

2007/12/4 Ingo Godau-Gellert <[EMAIL PROTECTED]>:
> What is really strange is that FTS3 search phrases like
> SELECT referenzcode FROM volltext where volltext match ('installation
> manual') are performed really fast within some milliseconds, independent
> to the search phrase.
> But in general I allow the user to enter a search word in a dedicated
> form field (Windows computers), the search starts after entering of each
> additional character.
>
> That means:
> Entering the word "installation" the search starts after entering the
> character "i". Then, after entering the second character "n" the search
> field is "in" and starts again. Then the user enters "s" and the search
> is interrupted and starts again with search word "ins".
>
> To be able to find not only table entries containing "i", "in", "ins",
> "inst", ... there is automatically added the character "*" - in fact the
> search phrase is "i*", "in*", "ins"*, "inst*", ...
>
> Now it's very interesting that a search phrase containing at least 4
> characters causes a search time of max. some seconds.

In general, the more specific the term is, the faster the search will
be.  Very unlikely terms will be faster than common terms.

If you do a search for 'installation', then fts can dig directly down
to the information for 'installation' and serve it up.  But if you
search for 'i*', it's as if you're searching for all terms starting
with 'i', so fts has to find all those terms and merge their
information together.  This is always going to be slower than a query
for a specific term within that range of terms.

The specific reasons why you're seeing slow performance, and the
specific things you see slow performance on, are very dependent on the
data in your system.  In general, more is slower than less.  So in
cases where you have a large number of results, it will generally be
slower.

> The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries.
> But if the search f.e. is "F1*" the search takes around 1128,5 seconds
> to find 77652 entries.

This seems a little excessive, though.  I do see that there's an
O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to
docListUnion()).  I can reasonably make that O(logN), which might help
a great deal, if you're hitting it.  Not really sure how to tell if
you're hitting it, but I'll experiment at my end and see whether I can
improve things there.

> It's clear to me that the search time in some cases takes longer as in
> other cases. Especially I would expect that the search takes as longer
> as the amount of found entries is bigger.
>
> But is there anyone who could explain me, why "F1*" takes 1128,5 seconds
> search time? Or "F3*" takes 202,8s? What is the reason for such a long
> duration?

If there are a lot of hits distributed across a few terms, that might
be much faster than fewer hits distributed across a large number of
terms.

> In my opinion a short search phrase with "*" should be very fast.

:-).  Prefix searches will likely always be slower than specific
searches, because they're essentially implemented as a set of specific
searches OR'ed together.  Better than that, of course, but still
strictly slower than a single specific search.

> Is there any possibility to solve this behaviour? Today I tried every
> possible search phrase combination with 2 characters only, noticed the
> search time and decided to use FTS search only in case the search will
> likely take less than 30 seconds. As soon as the search phrase will take
> longer than 30 seconds I use the standard SQLITE3 search algorithm.
> That's a workaround for today, but I consider someone being here who
> could improve the algorithm behaviour of FTS3?

SQLite has a function sqlite3_interrupt() which you can use to
interrupt statements.  Since fts3 is implemented in terms of SQLite,
you _should_ be able to call sqlite3_interrupt() to cause it to stop
executing.  If your code is asynchronous, then you could start the
query in the background, and call sqlite3_interrupt() after awhile to
stop execution.

There are a couple different things I've considered for fts to
implement to help out with this case.  One thing would be a "limit"
feature, so you could say something like:

   SELECT rowid FROM t WHERE t MATCH 'a* limit:10';

Why this is helpful is that it tells fts that it only needs to
generate 10 hits, which it might be able to optimize to be very fast.

Another thing I've considered is to add some sort of prefix-specific
index to the system.  I'm not even clear what this would mean, so I'm
not going to describe it :-).  But it would probably be something in
the CREATE statement which indicated that you expected to do prefix
queries and wanted fts to keep additional information to make those
fast.  OR, it might be a completely distinct table which only keeps an
index to make prefix-searching fast.

One reason you might find full-table-scans to sometimes be faster than
fts is if fts ends up essentially looking 

Re: [sqlite] fts table insert performance

[Scott Hess]

Sorry for the delayed response.  Was waiting for time to dig into this a little.

Nov 18, 2007 2:05 AM Wang Yun <[EMAIL PROTECTED]>:
> I insert rfc txt files into a full text search table, 4119 txt files are
> 188MB totally. After insert, database file is 443MB.
> Logic is below, it's not the real code.

I've attached a tcl script I used in experimenting with this.  I'm
doing this on a Redhat 9 box (kernel 2.4.22 with some additional
patches), running on a local disk both for input and the database.
The version of rfc I'm using has 4756 files with 252M of data.  I
compiled tclsqlite3 with flags:

-O6 -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DSQLITE_CORE=1
-DSQLITE_ENABLE_FTS1=1 -DSQLITE_ENABLE_BROKEN_FTS1=1
-DSQLITE_ENABLE_FTS2=1 -DSQLITE_ENABLE_BROKEN_FTS2=1
-DSQLITE_ENABLE_FTS3=1 -DHAVE_DLOPEN=1

[Listing all this detail because various things can cause various
differences.  The most-relevant parameters are probably -O6
-DNDEBUG=1.]

> This cost 154 seconds, I use fts2 and my PC is Intel 2.33GHz, 2 CPUs.
> If I don't use fts, just insert into normal table, will cost 11 seconds.

Your results are a bit worse than I'd expect.  I get 6.2s for the
simple-table case, and about 45s for the fts3 case, not quite your 14x
slow-down.  fts2 and fts3 should be nearly identical for this kind of
test.

Basic table, one transaction:
2.190u 1.060s 0:06.20 52.4% 0+0k 0+0io 393pf+0w
-rw-r--r--  1 shess eng 239505408 Dec  4 11:15 dbs/baseline.db

Basic table, per-insert transaction:
3.280u 1.890s 0:34.19 15.1% 0+0k 0+0io 393pf+0w
-rw-r--r--  1 shess eng 239505408 Dec  4 11:16 dbs/baseline.db

fts3, one transaction:
40.820u 1.550s 0:44.44 95.3%0+0k 0+0io 414pf+0w
-rw-r--r--  1 shess eng 307900416 Dec  4 11:17 dbs/baseline.db

fts3, per-insert transaction:
61.300u 3.330s 1:43.04 62.7%0+0k 0+0io 414pf+0w
-rw-r--r--  1 shess eng 348348416 Dec  4 11:21 dbs/baseline.db

I'm not seeing as much bloat as you describe - perhaps I'm using a
bigger page size.

> I don't know when sqlite will update the full text index, after each insert?

fts2/3 update the index after each transaction, and also before each
insert where an explicit rowid is less than the maximum rowid seen in
the current transaction, and also before any query run against the
table.  In this case, it should only be updating at the end of the
transaction, and as needed when the in-memory table fills up.

> How can I improve the performance?

The optimal case for fts2/3 is to do many inserts per transaction,
letting the table select rowid/docid.  In that case it will collect
the new data in memory and flush it to disk less frequently.  For a
test of this size, it might also help to have a bigger page cache so
that SQLite doesn't have to flush the journal file to disk.  You could
define kPendingThreshold in the fts3.c (or fts2.c) source code to be
larger (this doesn't seem to help me at all, though).

Without going in and profiling things, though, my guess is that the
time in this test is dominated by tokenization.  I'm basing this on
the high CPU utilization, and the lack of impact from tweaking
kPendingThreshold and pragma cache_size.  Possibly there's also some
cost from segment merges, though those should generally be fairly I/O
dominated.

-scott

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ORDER BY Performance on 30,000 records

[Ofir Neuman]

Thanks, now it takes only few ms  : )

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, December 02, 2007 7:52 PM
Subject: Re: [sqlite] ORDER BY Performance on 30,000 records




On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote:


Hi All,

I have some performance problem when adding ORDER BY to my query,  hope 
you

can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.


Drop the index on ParentID and replace it with this:

   CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID);

Then queries of the form

   SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate;

will be very fast.



Current number of records in table: 40,000

My query is very simple:
SELECT ID FROM Table1 WHERE ParentID = '{---}'  ORDER BY
ModifiedDate

According to the data in my table this query should return 30,000 
records.


While using ORDER BY it takes 3-4 SEC to retrieve the query result, 
without

the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.


D. Richard Hipp
[EMAIL PROTECTED]




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

[Joe Wilson]

--- Dan <[EMAIL PROTECTED]> wrote:

> The "b" in the ORDER BY does not match "x1.b" because it is
> not a simple identifier (according to matchOrderbyToColumn()).
> It does not match either "" or " as ".
> 
> After failing to find a match for "b" in the leftmost SELECT,
> SQLite searches the next leftmost and matches "b" to "b"
> (column 2).
> 
> That's how it is at the moment, anyhow.
> 
> >
> >   http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html
> >
> 
> Cheers. I'm starting to realise why this little corner of sqlite
> is the way it is...

I believe that there are 2 different issues with the current implementation:

1. The result set column names of a compound SELECT should drop all 
   table qualifiers, as they've lost all meaning once in a UNION.

   i.e., instead of:

 sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2;
 x1.b|a
 value|value

   you should see:

 b|a
 value|value

   as other databases do:

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b;
 +--+--+
 | b| a|
 +--+--+
 |2 |1 |
 |9 |0 |
 +--+--+

 mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b;
 ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause'

2. The compound SELECT's ORDER BY statement elements should only be matched
   against the leftmost SELECT. If there is no match in the leftmost
   SELECT, then an error should result - even if a match could potentially
   be found in non-leftmost SELECTs.

Or do you disagree?




  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Allocating Memory For A Pool Of Database Connections

[Ken]

David,

You have some pointer issues going on!

Maybe something like this


struct db_cache_type
{
 sqlite3 *db;   /* database file pointer */
 char *key;   /* key identifies the database that was opened */
 int mode;   /* database open mode */
};
struct db_cache_type **db_cache_list; /* list of database connections

 */
int db_cache_count = 5;   /* number of databases in pool */

/* allocate memory for array */
db_cache_list = (struct db_cache_type *)malloc(db_cache_count * sizeof(struct
 db_cache_type *));

/* aloocate each element pointer */
for (i= 0; i < db_cache_count; i ++)
  / DO NOT type cast malloc /
  db_cache_list[i] = malloc(sizeof(struct db_cache_type));



 sqlite3_open_v2( db_cach_list->key, _cache_list[i]->db, flags, NULL); 


== SImpler method, dont use ** on the struct =


struct db_cache_type
{
 sqlite3 *db;   /* database file pointer */
 char *key;   /* key identifies the database that was opened */
 int mode;   /* database open mode */
};
 struct db_cache_type *db_cache_list; /* list of database connections */

int db_cache_count = 5;   /* number of databases in pool */

/* allocate memory for array */
db_cache_list = malloc(db_cache_count * sizeof(struct
 db_cache_type));

memset(db_cach_list, 0
   , sizeof( db_cache_count * sizeof(struct db_cache_type) );

Use is also simpler:
 sqlite3_open_v2( db_cach_list[i].key, _cache_list[i].db, flags, NULL); 

 
 

David Gelt <[EMAIL PROTECTED]> wrote: Hi there,

I am having a minor issue when trying to malloc() memory for a list of database 
connections. I need to keep open a large number of database files and run 
queries against them. The application is running on Linux and is a 
synchronuous, single threaded application. 

In order to keep all these database connections open, I defined an array 
(simple list) but when I am trying to allocate memory for sqlite3 type I get an 
error complaining about sizeof(sqlite3). I can't keep defining variables for 
each database open because there might be too many and I don't know in advance 
the database name, just its structure.

Here is what I am doing:

struct db_cache_type
{
 sqlite3 *db;   /* database file pointer */
 char *key;   /* key identifies the database that was opened */
 int mode;   /* database open mode */
};

struct db_cache_type **db_cache_list; /* list of database connections */
int db_cache_count;   /* number of databases in pool */

/* allocate memory for array */
db_cache_list = (struct db_cache_type **)malloc(1 * sizeof(struct db_cache_type 
*));

/* __ ERROR ON NEXT LINE at sizeof() 
___*/
db_cache_list[i] = (struct sqlite3 *)malloc(db_cache_count * sizeof(struct 
sqlite3));

db_cache_list[i]->db = db;

Any help on how to create an array of sqlite3 database connections would be 
greatly appreciated. 

Thanks in advance.

David


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

Re: [sqlite] Allocating Memory For A Pool Of Database Connections

[drh]

David Gelt <[EMAIL PROTECTED]> wrote:
> 
> The application is ... a synchronuous, single threaded application. 

Yes!  Way to go!

> 
> In order to keep all these database connections open, I defined 
> an array (simple list) but when I am trying to allocate memory 
> for sqlite3 type I get an error complaining about sizeof(sqlite3).

You'll be storing a pointer to the connection, so you want to
use sizeof(sqlite3*);

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Allocating Memory For A Pool Of Database Connections

[David Gelt]

Hi there,

I am having a minor issue when trying to malloc() memory for a list of database 
connections. I need to keep open a large number of database files and run 
queries against them. The application is running on Linux and is a 
synchronuous, single threaded application. 

In order to keep all these database connections open, I defined an array 
(simple list) but when I am trying to allocate memory for sqlite3 type I get an 
error complaining about sizeof(sqlite3). I can't keep defining variables for 
each database open because there might be too many and I don't know in advance 
the database name, just its structure.

Here is what I am doing:

struct db_cache_type
{
 sqlite3 *db;   /* database file pointer */
 char *key;   /* key identifies the database that was opened */
 int mode;   /* database open mode */
};

struct db_cache_type **db_cache_list; /* list of database connections */
int db_cache_count;   /* number of databases in pool */

/* allocate memory for array */
db_cache_list = (struct db_cache_type **)malloc(1 * sizeof(struct db_cache_type 
*));

/* __ ERROR ON NEXT LINE at sizeof() 
___*/
db_cache_list[i] = (struct sqlite3 *)malloc(db_cache_count * sizeof(struct 
sqlite3));

db_cache_list[i]->db = db;

Any help on how to create an array of sqlite3 database connections would be 
greatly appreciated. 

Thanks in advance.

David


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

Re: [sqlite] Cache resizing problem.

[Dan]

On Dec 4, 2007, at 11:47 PM, Dennis Cote wrote:


Dan wrote:



This is failing because the internal representation of the database
schema used by the first connection has not yet been updated to
include the changes made in step 4 by the second connection.

After the sqlite3_prepare() in step 6 fails with the "no such table"
error, SQLite realises that it may be using an old schema version and
discards it. The new schema will be loaded fresh from the database
next time a call is made to sqlite3_prepare().

So the easiest fix is just to retry the sqlite3_prepare().





Doesn't sqlite do this automatically now if the OP were to switch  
to the new sqlite3_prepare_v2 API instead of using the  
sqlite3_prepare API?


No. sqlite3_prepare_v2() behaves the same way in this case.

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting values by column name

[Dennis Cote]

Jon Drnek wrote:

Is there a way that I can find out the number of a given column name in the
query?  

 

Instead of 

 


ts.key = sqlite3_column_int(preparedGet,0);

 


I'd like to do something like:

 


ts.key = sqlite3_column_int(preparedGet,get_column_id(preparedGet,"id"));

 


Is there a way to do that?

 
  

Jon,

I don't believe there is any API function to do that for columns (but 
you can do it for bound parameters).


You would have to build your own map from the name to the index number 
after preparing the statement. You can use the sqlite3_column_count 
function to get the number of columns, and then loop calling 
sqlite3_column_name to get al the names once. If you save those names 
along with the associated index, you can then use that table (or an STL 
map if you are using C++) to translate from a column name back to its 
column index for use with the other sqlite3_column_* API functions.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

[Dan]

On Dec 4, 2007, at 10:35 PM, Joe Wilson wrote:


--- Dan <[EMAIL PROTECTED]> wrote:

i.e., if we have:

   CREATE TABLE x1(a, b, c);
   CREATE TABLE x2(a, b, c);

then the following pairs of statements are equivalent:

...


   SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
   SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;


Don't you mean ORDER BY 1?


I'm talking about sqlite cvs, as the code is implemented right
now (see matchOrderbyToColumn() in select.c). So 2 is correct,
as the test you did shows.

The "b" in the ORDER BY does not match "x1.b" because it is
not a simple identifier (according to matchOrderbyToColumn()).
It does not match either "" or " as ".

After failing to find a match for "b" in the leftmost SELECT,
SQLite searches the next leftmost and matches "b" to "b"
(column 2).

That's how it is at the moment, anyhow.



  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



Cheers. I'm starting to realise why this little corner of sqlite
is the way it is...

Dan.




   
__ 
__

Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http:// 
mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ



-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Cache resizing problem.

[Dennis Cote]

Dan wrote:



This is failing because the internal representation of the database
schema used by the first connection has not yet been updated to
include the changes made in step 4 by the second connection.

After the sqlite3_prepare() in step 6 fails with the "no such table"
error, SQLite realises that it may be using an old schema version and
discards it. The new schema will be loaded fresh from the database
next time a call is made to sqlite3_prepare().

So the easiest fix is just to retry the sqlite3_prepare().


Dan,

Doesn't sqlite do this automatically now if the OP were to switch to the 
new sqlite3_prepare_v2 API instead of using the sqlite3_prepare API?


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite:Deletion in Joins method

[Dennis Cote]

Sreedhar.a wrote:

CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE
TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE
TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);

CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER
CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id
INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON
DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES
BGM(BgmId)ON DELETE CASCADE );
 
I created a trigger as follows .


Here I am checking after deleting that record in Music Table I will check
wheather that Artist_id is present now in that table MUSIC i.e, (SELECT
Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that
Artist_Id is Null then I will delete it in the ARTIST table.

But this is not happening with the below trigger.
Do I need to add more constraints in the below trigger.
Please help to solve this.

"CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
SELECT CASE
WHEN 
	(SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id)
IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id'  
END;

END;"


  
You need to use a conditional delete in your trigger. You can't do that 
using a select with a case statement. You could try something like this 
(untested):


CREATE TRIGGER fkdc_MUSIC
AFTER DELETE ON MUSIC
FOR EACH ROW
BEGIN
 delete from Artist 
   where ArtistId = old.ArtistId
   and not exist (select id from music where ArtistId = old.ArtistId); 
 delete from Album

   where AlbumId = old.AlbumId
   and not exist (select id from music where AlbumId = old.AlbumId); 
 delete from BGM

   where BgmId = old.BgmId
   and not exist (select id from music where BgmId = old.BgmId); 
END;"


A couple of other things to note:

You should probably change your table definitions to key the phrase 
"INTEGER PRIMARY KEY" together so that sqlite can use its btree key 
optimization. Instead of this:


CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);

use this:

CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text);

Also, if you want to speed up the searches in the deletes trigger above, 
at the expense of slowing down all the insert and delete operations into 
the tables, you could add indexes on the individual Id columns in the 
music table.


create index MusicArtistId on Muisc(ArtistId);
create index MusicAlbumId on Muisc(AlbumId);
create index MusicBgmId on Muisc(BgmId);

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite does not support multi-row inserts?

[Samuel R. Neff]

MS SQL 2008 will support multi-row insert statements too.

http://richardsbraindump.blogspot.com/2007/07/what-new-in-sql-2008-katmai.ht
ml 

Sam

---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 04, 2007 10:47 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite does not support multi-row inserts?

I can't confirm whether it is, but Wikipedia claims it is:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Multirow inserts

An SQL feature (since SQL-92) is the use of row value constructors to insert
multiple rows at a
time in a single SQL statement:

 INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b,
...]), (value2a,
[value2b, ...]), ...

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and
H2.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite does not support multi-row inserts?

[Joe Wilson]

--- Sander Marechal <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:
> > --- Sander Marechal <[EMAIL PROTECTED]> wrote:
> >> I ran into a problem when using SQLite from PHP. It appears that SQLite3 
> >> does not support multi-row inserts in the form:
> >>
> >> INSERT INTO (col1, col2) VALUES (1, 2), (3, 4)
> >> 
> >> Will if be implemented in the future?
> > 
> > I doubt it.
> 
> Too bad. I think it's in SQL92 but I'm not 100% sure (The SQL92 specs 
> are a bit hard to read).

I can't confirm whether it is, but Wikipedia claims it is:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Multirow inserts

An SQL feature (since SQL-92) is the use of row value constructors to insert 
multiple rows at a
time in a single SQL statement:

 INSERT INTO table (column1, [column2, ... ]) VALUES (value1a, [value1b, ...]), 
(value2a,
[value2b, ...]), ...

This feature is supported by DB2, PostgreSQL (since version 8.2), MySQL, and H2.



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Single row insert speeds

[drh]

"Mark Riehl" <[EMAIL PROTECTED]> wrote:
>> 
> For testing, I've modified the the insert to look like this:
> char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO
> sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\",
> \"1196303669.065335988998\",
> \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0
> peak);\");COMMIT;";
> 
> Is this the correct syntax for the PRAGMA statement?  Can I issue it
> once and will it remain active as long as the connection is open?
> 

The PRAGMA syntax is correct.  Issue it once when you initially
open the connection.  The BEGIN and COMMIT are automatic and
can be omitted (for additional speed).  SQL wants values to
be quoted using single-quotes, not double-quotes.  SQLite allows
double-quotes but it is technically wrong.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Writing to Flash Memory

[Mark Riehl]

I'm developing an application to run on an ARM-based PDA-like device.
It's running Linux and I've got SQLite 3.4.1 installed.

I'm trying to optimize my insert statements (in a different discussion
thread) I've gotten some good suggestions to use the PRAGMA statement
to disable the syncronization.  On my Shuttle running Fedora 3, I see
insert times of ~0.2 ms.  However, the same test application running
on the PDA is taking ~25 ms for the same insert.

The media doesn't appear to be that slow - writing the same  insert
statement to a text file takes ~0.2ms.  Granted, there is more going
on with the SQLite insert than the file right, but, I wouldn't expect
such a discrepancy.

Does anyone have any experience using SQLite and flash?  Any
particular settings that would be helpful?

Thanks,
Mark

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

[Joe Wilson]

--- Dan <[EMAIL PROTECTED]> wrote:
> i.e., if we have:
> 
>CREATE TABLE x1(a, b, c);
>CREATE TABLE x2(a, b, c);
> 
> then the following pairs of statements are equivalent:
...
> 
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;

Don't you mean ORDER BY 1?

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; <--

I thought *only* the leftmost SELECT in the compound chain governs the
selection of the column names used by the ORDER BY.  The names of the 
subsequent compound SELECTs should be ignored. At least that's how it 
works on MySQL and other databases I've used:

given:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table x2(a INT, b INT, c INT);
  insert into x2 values(9, 0, 4);  

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
+--+--+
| b| a|
+--+--+
|9 |0 |
|2 |1 |
+--+--+

mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;
+--+--+
| b| a|
+--+--+
|2 |1 |
|9 |0 |
+--+--+

Oracle has the same behavior as MySQL, as I recall.

sqlite 3.5 produces a different result since it appears to be
getting the column name from the rightmost compound select:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  x1.b|a
  9|0
  2|1

Compare MySQL:

  create table x1(a INT, b INT, c INT);
  insert into x1 values(1, 2, 3);
  create table g2(x INT, y INT, z INT);
  insert into g2 values(9, 0, 4);

  mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  ERROR 1054 (42S22): Unknown column 'y' in 'order clause'

to sqlite:

  sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y;
  9|0
  2|1

> To my mind, the logical change to make would be to allow this:
> 
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
>SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;

This query is (also) unambiguous given the logic outlined above:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;

I thought all of this was already hashed in this thread:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html



  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Single row insert speeds

[Mark Riehl]

All - After reviewing some of my calculations, I realized I made a
mistake and that the inserts didn't improve as much as I thought.  I
used the PRAGMA synchronouse = OFF and I'm averaging about 0.21 ms for
an insert on a Core 2 Shuttle running Fedora 3.

Here is the table I've defined:
CREATE TABLE sampleTable (
 logHost varchar(64) DEFAULT NULL,
 compId smallint(5) DEFAULT NULL,
 pid int(10) DEFAULT NULL,
 version varchar(8) DEFAULT NULL,
 rptTime decimal(20,6) DEFAULT NULL,
 rptStatus tinyint(3) DEFAULT NULL,
 data text
);

Here is a typical insert:

INSERT INTO sampleTable
VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.06533598
8998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak)
;\")";

For testing, I've modified the the insert to look like this:
char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO
sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\",
\"1196303669.065335988998\",
\"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0
peak);\");COMMIT;";

Is this the correct syntax for the PRAGMA statement?  Can I issue it
once and will it remain active as long as the connection is open?

Thanks,
Mark


On Dec 3, 2007 6:45 PM, Mark Riehl <[EMAIL PROTECTED]> wrote:
> I used the PRAGMA statement and turned off the synchronous option.  It
> made a huge difference.  Single inserts were ranging from 5 - 50 ms,
> now, they're at ~.04 ms.
>
> However, I guess there is a tradeoff between the safety of the
> synchronous operation (in case power is lost) versus the insert
> speeds.
>
> Thanks for the help,
> Mark
>
>
> On Dec 3, 2007 12:59 PM,  <[EMAIL PROTECTED]> wrote:
> > "P Kishor" <[EMAIL PROTECTED]> wrote:
> > > I get 1000+ inserts a second for a random 100 byte string
> > > insert (
> >
> > I get 5+ inserts/sec on my Linux box.
> >
> > Insert speed is not the issue.  It is COMMIT speed.  At
> > each commit, SQLite waits until all data is on oxide before
> > continuing.  That will typically take at least two rotations
> > of the disk platter, or about 17 millisecond, depending on
> > your disk drive.  Waiting for data to get to oxide is
> > part of being ACID.  You can set:
> >
> >PRAGMA synchronous=OFF;
> >
> > and your COMMITs will go *much* faster because it will no
> > longer wait on the disk drive.  But if you lose
> > power in the middle of a commit, you might corrupt your
> > database file.
> >
> > Note that there is an implied BEGIN...COMMIT around every
> > INSERT statement if you do not explicitly start a transaction
> > using your own BEGIN.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Getting values by column name

[Igor Tandetnik]

Jon Drnek 
wrote:

I have a prepared statement  that looks like

char *getSql = "select * from transducer where id = ?";
rc = sqlite3_prepare_v2(transducerDb,getSql,-1,,NULL);

Is there a way that I can find out the number of a given column name
in the query?


You can enumerate all columns and generate a map of column name to 
index. See sqlite3_column_count, sqlite3_column_name[16]


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Getting values by column name

[Jon Drnek]

Hello, 

 

I have a prepared statement  that looks like

 

char *getSql = "select * from transducer where id = ?";

rc = sqlite3_prepare_v2(transducerDb,getSql,-1,,NULL);

 

Is there a way that I can find out the number of a given column name in the
query?  

 

Instead of 

 

ts.key = sqlite3_column_int(preparedGet,0);

 

I'd like to do something like:

 

ts.key = sqlite3_column_int(preparedGet,get_column_id(preparedGet,"id"));

 

Is there a way to do that?

 

Thanks, 

 

Jon

 

 

 

[sqlite] How to get older source

[Ian Frosst]

I'm trying to access the 3.4.2 (and previous) sources from the website by
changing the version numbers in the source download link to various values,
which has worked for me in the past.  If I use the following links, things
work and I can get both the long form and amalgamated sources:

http://sqlite.org/sqlite-amalgamation-3_5_3.zip
http://sqlite.org/sqlite-amalgamation-3_5_2.zip

http://www.sqlite.org/sqlite-source-3_5_3.zip
http://www.sqlite.org/sqlite-source-3_5_2.zip

However, versions 3.5.1 and back don't work (file not found.)  Note that I
don't try to use the amalgamation stuff for 3.4.2 back, just the normal
source links.

Have historical versions been removed, or is there another place I should be
looking for this source?

Thanks,
Ian

Re: [sqlite] Cache resizing problem.

[Dan]

On Dec 4, 2007, at 3:49 PM, Sabyasachi Ruj wrote:


Hi,

I am getting a problem if I am modifying cache size.
This can be reproduced by the following steps:-
We need two connections to reprodce this.

Say the database name is: "test.db"
*"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH  
TIME WE

WANT TO GET THE PROBLEM.*

1.
Create a connection to test.db. Here "test.db" should be created  
physically.


If it is existing please DELETE it.
We will call this connection as First Connection

2.
Set PRAGMA cache_size =  for this connection

3.
Create another connection to test.db. This is called the Second  
Connection.


4.
Creat a a table (say "student_master") in this second connection.

5.
Insert some data in this table through second connection.

6.
Then try to select from the same table with the first connection.


This is failing because the internal representation of the database
schema used by the first connection has not yet been updated to
include the changes made in step 4 by the second connection.

After the sqlite3_prepare() in step 6 fails with the "no such table"
error, SQLite realises that it may be using an old schema version and
discards it. The new schema will be loaded fresh from the database
next time a call is made to sqlite3_prepare().

So the easiest fix is just to retry the sqlite3_prepare().



I am getting error while preparing the SELECT statement.
And the error is no such table: "student_master".

So I think the changes we are making in the second connection are not
visible.
But I do not understand, why does it work if I am not executing  
that CACHE

resize query!


Because in the absence of the cache-resize query, the first connection
does not initialise it's internal schema until step 6. By the time it
is loaded for the first time in step 6 the "student_master" table has
already been added. Hence no problem.

Dan.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Cache resizing problem.

[Sabyasachi Ruj]

Hi,

I am getting a problem if I am modifying cache size.
This can be reproduced by the following steps:-
We need two connections to reprodce this.

Say the database name is: "test.db"
*"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH TIME WE
WANT TO GET THE PROBLEM.*

1.
Create a connection to test.db. Here "test.db" should be created physically.

If it is existing please DELETE it.
We will call this connection as First Connection

2.
Set PRAGMA cache_size =  for this connection

3.
Create another connection to test.db. This is called the Second Connection.

4.
Creat a a table (say "student_master") in this second connection.

5.
Insert some data in this table through second connection.

6.
Then try to select from the same table with the first connection.

I am getting error while preparing the SELECT statement.
And the error is no such table: "student_master".

So I think the changes we are making in the second connection are not
visible.
But I do not understand, why does it work if I am not executing that CACHE
resize query!


I am pasting the code for linux here. It can be reproduced in windows too.

There is one macro in my code:-
#define SET_CACHE_SQLITE 1

You can comment it, if you dont want the cache resize code, and then it will
work properly!

Is it my problem or a SQLite problem?

THE CODE:-


/**/
#include
#include 
#include
#include "sqlite3.h"

// Define SET_CACHE_SQLITE if you want the cache to be enabled, otherwise
comment it
#define SET_CACHE_SQLITE 1

int func_reproduce( )
{
sqlite3 *db1;// Database handle for first connection
sqlite3 *db2;// Database handle for second connection

sqlite3_stmt *  pstmt1 = NULL;
sqlite3_stmt *  pstmt2 = NULL;

int ret = 1;
void **unused;

// Open the first connection
int ret1 = sqlite3_open( "test.db",  );
if(ret1 != SQLITE_OK)
{
printf("OPEN error - db\n", sqlite3_errmsg(db1));
exit(EXIT_FAILURE);
}
printf("Opened db1\n");

#ifdef SET_CACHE_SQLITE // Modify Cache?
// Modify the cache size in first connection
char * query_cache = "PRAGMA cache_size = 5000";

//Prepare Cache query
ret = sqlite3_prepare(db1, query_cache,
(int)strlen(query_cache)*sizeof(char),
,
(const char **));

if(ret != SQLITE_OK)
{
printf("Prepare Error - CACHE: (%s)\n", sqlite3_errmsg(db1));
exit(EXIT_FAILURE);
}
printf("Prepared: CACHE\n");

//Step Cache query
ret = sqlite3_step(pstmt1);

if(ret != SQLITE_DONE)
{
printf("Step Error - CACHE: (%s)\n", sqlite3_errmsg(db1));
exit(EXIT_FAILURE);
}
printf("Stepped CACHE\n");
sqlite3_finalize(pstmt1);
printf("Finalized cache\n");
#endif //SET_CACHE_SQLITE

//Open the second connection
ret1 = sqlite3_open( "test.db",  );
if(ret1 != SQLITE_OK)
{
printf("OPEN error - db2\n", sqlite3_errmsg(db2));
exit(EXIT_FAILURE);
}
printf("Opened db2\n");


char *query_create = "CREATE TABLE IF NOT EXISTS [student_master] (
\
[student_id] INTEGER NOT NULL
PRIMARY KEY AUTOINCREMENT UNIQUE, \
[student_name] VARCHAR(50))";

//Prepare CREATE TABLE query
ret = sqlite3_prepare(db2, query_create,
(int)strlen(query_create)*sizeof(char),
,
(const char **));

if(ret != SQLITE_OK)
{
printf("Prepare Error - CREATE: (%s)\n", sqlite3_errmsg(db2));
exit(EXIT_FAILURE);
}
printf("Prepared for CREATE\n");

//Step CREATE TABLE query
ret = sqlite3_step(pstmt2);
if(ret != SQLITE_DONE)
{
if(ret == SQLITE_ERROR)
sqlite3_finalize(pstmt2);
printf("Step Error - CREATE: (%s)\n", sqlite3_errmsg(db2));
exit(EXIT_FAILURE);
}
printf("Stepped for CREATE\n");
sqlite3_finalize(pstmt2);
printf("Finalized CREATE\n");

// Insert a row with the second connection
char *query_insert = "INSERT INTO [student_master]([student_name])
values ('CrazyBoy')";


// Prepare for INSERT query
ret = sqlite3_prepare(db2, query_insert,
(int)strlen(query_insert)*sizeof(char),
,
(const char **));

if(ret != SQLITE_OK)
{
printf("Prepare Error - INSERT: (%s)\n", sqlite3_errmsg(db2));
exit(EXIT_FAILURE);
}
printf("Prepared for INSERT\n");

// Step INSERT query
ret = sqlite3_step(pstmt2);
if(ret != SQLITE_DONE)
{
if(ret == SQLITE_ERROR)
sqlite3_finalize(pstmt2);
printf("Step Error - INSERT: (%s)\n", sqlite3_errmsg(db2));
exit(EXIT_FAILURE);
}
printf("Stepped for 

Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2

[Dan]

On Dec 4, 2007, at 10:27 AM, Dr Gerard Hammond wrote:

I have reported it as a bug - ticket is http://www.sqlite.org/ 
cvstrac/tktview?tn=2822


It appears as though the /src/select.c (Line1499) changed
from:
   if( iCol<0 && mustComplete ){
to:
}else if( mustComplete ){

in version 1.336 of this file - http://www.sqlite.org/cvstrac/ 
filediff?f=sqlite/src/select.c=1.335=1.336


And this change results in this bug.


On 04/12/2007, at 4:59 AM, Joe Wilson wrote:


--- Marco Bambini <[EMAIL PROTECTED]> wrote:

Starting from version 3.4.2 I receive errors with queries like:

SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY  
a.field

or even
SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY  
a.field


error is:
ORDER BY term number 1 does not match any result column

Tables are created by:
CREATE TABLE a (field);
CREATE TABLE b (field);

Please note that the above queries worked fine with sqlite 3.2.x or
3.3.x.
Any idea?


You probably know the workarounds:

 SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;

or

 SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER  
BY x;


but it's odd that this one doesn't work as well:

 create table t1(a);
 create table t2(b);

 select t1.a from t1 union all select t2.b from t2 order by a;

 SQL error: ORDER BY term number 1 does not match any result column




At present, expressions in the ORDER BY clause attached to a compound
SELECT must be either:

  1) An integer between 1 and the number of columns returned by
 the SELECT statement (inclusive), or

  2) A simple identifier (no quotes). In this case SQLite tries to  
match

 the identifier to one of the returned columns of data by scanning
 the result-set of each of the individual SELECT statements,  
starting

 from the left. The identifier matches the column if the expression
 in the result set is either "" or " as  
"


This means you cannot specify an arbitrary sort key for a compound
statement, you can only nominate one of the returned columns to sort
on.

i.e., if we have:

  CREATE TABLE x1(a, b, c);
  CREATE TABLE x2(a, b, c);

then the following pairs of statements are equivalent:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY a;
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;

To my mind, the logical change to make would be to allow this:

  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
  SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;

Because it is consistent with this kind of statement:

  SELECT "x1.b" FROM (SELECT x1.b FROM x1);

Any opinions?

Dan.






Cheers.

--

Dr Gerard Hammond
Garvan Institute of Medical Research






-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---





-
To unsubscribe, send email to [EMAIL PROTECTED]
-