Re: [sqlite] checking if a table exists

[Brian Curley]

This is for a bash function, but you can adapt it for perl as you need to.

PRAGMA table_info() will poll the schema for you, and you can grep for your
table name:

function sq3_info () {

   #  Debug/verbose mode...
   if [ "${1}" == "-v" ]; then
  set -x
   fi

   not_sqlite="Error: file is encrypted or is not a database"  2>&1

   if [ -e ${2:-${1}} ] ;then
 #  Invalid first argument...
#  STFU
 if [ "${1}" != "-v" ] \
&& [ "$(echo ${1} |cut -b 1 )" == "-" ]; then
   printf "\nError:  %s \n" "Invalid arg1... "
   return
 #  File exists, but...it's either zero byte or not a SQLite
file...
#  GTFO
 elif [ -z "$(head -1 ${2:-${1}} |grep 'Lite format' )" ] \
|| [ "$(sqlite3 ${2:-${1}} .tab 2>/dev/null )" ==
"${not_sqlite}" ] ;then
   printf "\nError:  %s \n" "${not_sqlite}"
   return
 fi
 #  Hey, look...a real file!
#  sed is :
# a) inserting additional column...only on Line 1
# b) inserting table name values...only on Line 2+
 hdr=$(for i in $(sqlite3 ${2:-${1}} '.tables' |tr -d [:cntrl:] )
  do sqlite3 -header ${2:-${1}} "pragma table_info(${i});" \
 |sed '1 s/^/table_nm|/g;1!s/^/'${i}'|/g;'
  done
  )
 #  Print hdr content...
#  Line 1 only
 printf "%s\n" ${hdr} |awk 'NR == 1 { print };'
#  Line 2 onward...minus repeats column headers and sorted by
object and field order...
 printf "%s\n" ${hdr} |awk 'NR != 1 { print };' |sort -t'|' -k1,1
-k2,2n |grep -v "^table_nm"
   elif [ ! -e ${1} ]; then
  printf "\nError:  %s \n"  "No SQLite file found..."
   fi

   #  Turn off debug/verbose mode...IF it was us that did it...
   if [ "${1}" == "-v" ] \
  && [ ! -z "$(set |grep xtrace )" ]; then
 set +x
   fi

}



Regards.

Brian P Curley
  cell: 845 548-4377


On Fri, Sep 22, 2017 at 5:01 PM, Igor Korot  wrote:

> Hi,
>
> On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1  wrote:
> > I could use the “if not exist” syntax, and do in some places, but this
> is a SELECT. I need to verify the table exists before I attempt to pull
> data from it to prevent throwing and error.
>
> Try following query:
>
> SELECT 1 FROM sqlite_master WHERE  type = 'table' and name = ?;";
>
> And then bind the actual variable to the query.
>
> If the execution return a row the table exists.
>
> Thank you.
>
> >
> > Mike
> >
> >> On Sep 22, 2017, at 15:24, Igor Korot  wrote:
> >>
> >> Hi,
> >>
> >> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond <
> david.raym...@tomtom.com> wrote:
> >>> Not familiar with perl, but...
> >>>
> >>> -You should never need to do writable_schema = on for any normal
> operation. You can always read the schema even without that.
> >>>
> >>> -To my untrained eye it looks like you made a "name" variable, but
> never actually bound it to the statement. Usually that would involve some
> form of bind function call or providing it as an argument to the execute
> function.
> >>>
> >>> For example Python: cur.execute(,  of values to bind>)
> >>> cur.execute("select count(*) from sqlite_master where type = 'table'
> and name = ?;", (,))
> >>>
> >>>
> >>> -Original Message-
> >>> From: sqlite-users [mailto:sqlite-users-bounces@
> mailinglists.sqlite.org] On Behalf Of mikeegg1
> >>> Sent: Friday, September 22, 2017 3:36 PM
> >>> To: SQLite mailing list
> >>> Subject: [sqlite] checking if a table exists
> >>>
> >>> I’m using the statement:
> >>>
> >>> select count(*) from sqlite_master where type = 'table' and name =
> ‘$NAME’;
> >>
> >> If you write the query like this it will check for the table called
> "$NAME".
> >>
> >>>
> >>> This statement works fine in the sqlite3 shell. This statement does
> not work in my API.
> >>> Is there a PRAGMA I need to issue so I can check for table existence?
> >>>
> >>> TIA
> >>>
> >>> Mike
> >>>
> >>> PERL code to check for table existence:
> >>>
> >>> sub tableexists($$) {
> >>>my $dbh = shift;
> >>>my $name = shift;
> >>>my $tableexists = 0;
> >>>
> >>>$dbh->do("pragma writable_schema = 'on';");
> >>>
> >>>my $sql = "select count(*) from sqlite_master where type = 'table'
> and name = '$name';";
> >>>my $stmt = $dbh->prepare($sql);
> >>>$stmt->execute or die "$0: verifying table name failed:
> $DBI::errstr";
> >>>while(my @row = $stmt->fetchrow_array) {
> >>>$tableexists = $row[0];
> >>>}
> >>>$stmt->finish;
> >>>
> >>>$dbh->do("pragma writable_schema = 'off';");
> >>>
> >>>return $tableexists;
> >>> }
> >>
> >> I think SQLite support "CREATE TABLE IF NOT EXIST" syntax.
> >> So you don't have to check for the table existence and just execute
> >> such query.
> >>
> >> Thank you.
> >>
> >>>
> >>> 

Re: [sqlite] checking if a table exists

[Igor Korot]

Hi,

On Fri, Sep 22, 2017 at 4:42 PM, mikeegg1  wrote:
> I could use the “if not exist” syntax, and do in some places, but this is a 
> SELECT. I need to verify the table exists before I attempt to pull data from 
> it to prevent throwing and error.

Try following query:

SELECT 1 FROM sqlite_master WHERE  type = 'table' and name = ?;";

And then bind the actual variable to the query.

If the execution return a row the table exists.

Thank you.

>
> Mike
>
>> On Sep 22, 2017, at 15:24, Igor Korot  wrote:
>>
>> Hi,
>>
>> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond  
>> wrote:
>>> Not familiar with perl, but...
>>>
>>> -You should never need to do writable_schema = on for any normal operation. 
>>> You can always read the schema even without that.
>>>
>>> -To my untrained eye it looks like you made a "name" variable, but never 
>>> actually bound it to the statement. Usually that would involve some form of 
>>> bind function call or providing it as an argument to the execute function.
>>>
>>> For example Python: cur.execute(, >> values to bind>)
>>> cur.execute("select count(*) from sqlite_master where type = 'table' and 
>>> name = ?;", (,))
>>>
>>>
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>> Behalf Of mikeegg1
>>> Sent: Friday, September 22, 2017 3:36 PM
>>> To: SQLite mailing list
>>> Subject: [sqlite] checking if a table exists
>>>
>>> I’m using the statement:
>>>
>>> select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;
>>
>> If you write the query like this it will check for the table called "$NAME".
>>
>>>
>>> This statement works fine in the sqlite3 shell. This statement does not 
>>> work in my API.
>>> Is there a PRAGMA I need to issue so I can check for table existence?
>>>
>>> TIA
>>>
>>> Mike
>>>
>>> PERL code to check for table existence:
>>>
>>> sub tableexists($$) {
>>>my $dbh = shift;
>>>my $name = shift;
>>>my $tableexists = 0;
>>>
>>>$dbh->do("pragma writable_schema = 'on';");
>>>
>>>my $sql = "select count(*) from sqlite_master where type = 'table' and 
>>> name = '$name';";
>>>my $stmt = $dbh->prepare($sql);
>>>$stmt->execute or die "$0: verifying table name failed: $DBI::errstr";
>>>while(my @row = $stmt->fetchrow_array) {
>>>$tableexists = $row[0];
>>>}
>>>$stmt->finish;
>>>
>>>$dbh->do("pragma writable_schema = 'off';");
>>>
>>>return $tableexists;
>>> }
>>
>> I think SQLite support "CREATE TABLE IF NOT EXIST" syntax.
>> So you don't have to check for the table existence and just execute
>> such query.
>>
>> Thank you.
>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] checking if a table exists

[mikeegg1]

I could use the “if not exist” syntax, and do in some places, but this is a 
SELECT. I need to verify the table exists before I attempt to pull data from it 
to prevent throwing and error.

Mike

> On Sep 22, 2017, at 15:24, Igor Korot  wrote:
> 
> Hi,
> 
> On Fri, Sep 22, 2017 at 4:04 PM, David Raymond  
> wrote:
>> Not familiar with perl, but...
>> 
>> -You should never need to do writable_schema = on for any normal operation. 
>> You can always read the schema even without that.
>> 
>> -To my untrained eye it looks like you made a "name" variable, but never 
>> actually bound it to the statement. Usually that would involve some form of 
>> bind function call or providing it as an argument to the execute function.
>> 
>> For example Python: cur.execute(, > values to bind>)
>> cur.execute("select count(*) from sqlite_master where type = 'table' and 
>> name = ?;", (,))
>> 
>> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>> Behalf Of mikeegg1
>> Sent: Friday, September 22, 2017 3:36 PM
>> To: SQLite mailing list
>> Subject: [sqlite] checking if a table exists
>> 
>> I’m using the statement:
>> 
>> select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;
> 
> If you write the query like this it will check for the table called "$NAME".
> 
>> 
>> This statement works fine in the sqlite3 shell. This statement does not work 
>> in my API.
>> Is there a PRAGMA I need to issue so I can check for table existence?
>> 
>> TIA
>> 
>> Mike
>> 
>> PERL code to check for table existence:
>> 
>> sub tableexists($$) {
>>my $dbh = shift;
>>my $name = shift;
>>my $tableexists = 0;
>> 
>>$dbh->do("pragma writable_schema = 'on';");
>> 
>>my $sql = "select count(*) from sqlite_master where type = 'table' and 
>> name = '$name';";
>>my $stmt = $dbh->prepare($sql);
>>$stmt->execute or die "$0: verifying table name failed: $DBI::errstr";
>>while(my @row = $stmt->fetchrow_array) {
>>$tableexists = $row[0];
>>}
>>$stmt->finish;
>> 
>>$dbh->do("pragma writable_schema = 'off';");
>> 
>>return $tableexists;
>> }
> 
> I think SQLite support "CREATE TABLE IF NOT EXIST" syntax.
> So you don't have to check for the table existence and just execute
> such query.
> 
> Thank you.
> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] checking if a table exists

[Igor Korot]

Hi,

On Fri, Sep 22, 2017 at 4:04 PM, David Raymond  wrote:
> Not familiar with perl, but...
>
> -You should never need to do writable_schema = on for any normal operation. 
> You can always read the schema even without that.
>
> -To my untrained eye it looks like you made a "name" variable, but never 
> actually bound it to the statement. Usually that would involve some form of 
> bind function call or providing it as an argument to the execute function.
>
> For example Python: cur.execute(,  values to bind>)
> cur.execute("select count(*) from sqlite_master where type = 'table' and name 
> = ?;", (,))
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of mikeegg1
> Sent: Friday, September 22, 2017 3:36 PM
> To: SQLite mailing list
> Subject: [sqlite] checking if a table exists
>
> I’m using the statement:
>
> select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;

If you write the query like this it will check for the table called "$NAME".

>
> This statement works fine in the sqlite3 shell. This statement does not work 
> in my API.
> Is there a PRAGMA I need to issue so I can check for table existence?
>
> TIA
>
> Mike
>
> PERL code to check for table existence:
>
> sub tableexists($$) {
> my $dbh = shift;
> my $name = shift;
> my $tableexists = 0;
>
> $dbh->do("pragma writable_schema = 'on';");
>
> my $sql = "select count(*) from sqlite_master where type = 'table' and 
> name = '$name';";
> my $stmt = $dbh->prepare($sql);
> $stmt->execute or die "$0: verifying table name failed: $DBI::errstr";
> while(my @row = $stmt->fetchrow_array) {
> $tableexists = $row[0];
> }
> $stmt->finish;
>
> $dbh->do("pragma writable_schema = 'off';");
>
> return $tableexists;
> }

I think SQLite support "CREATE TABLE IF NOT EXIST" syntax.
So you don't have to check for the table existence and just execute
such query.

Thank you.

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

Re: [sqlite] checking if a table exists

[David Raymond]

Not familiar with perl, but...

-You should never need to do writable_schema = on for any normal operation. You 
can always read the schema even without that.

-To my untrained eye it looks like you made a "name" variable, but never 
actually bound it to the statement. Usually that would involve some form of 
bind function call or providing it as an argument to the execute function.

For example Python: cur.execute(, )
cur.execute("select count(*) from sqlite_master where type = 'table' and name = 
?;", (,))


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of mikeegg1
Sent: Friday, September 22, 2017 3:36 PM
To: SQLite mailing list
Subject: [sqlite] checking if a table exists

I’m using the statement:

select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;

This statement works fine in the sqlite3 shell. This statement does not work in 
my API.
Is there a PRAGMA I need to issue so I can check for table existence?

TIA

Mike

PERL code to check for table existence:

sub tableexists($$) {
my $dbh = shift;
my $name = shift;
my $tableexists = 0;

$dbh->do("pragma writable_schema = 'on';");

my $sql = "select count(*) from sqlite_master where type = 'table' and name 
= '$name';";
my $stmt = $dbh->prepare($sql);
$stmt->execute or die "$0: verifying table name failed: $DBI::errstr";
while(my @row = $stmt->fetchrow_array) {
$tableexists = $row[0];
}
$stmt->finish;

$dbh->do("pragma writable_schema = 'off';");

return $tableexists;
}

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

[sqlite] checking if a table exists

[mikeegg1]

I’m using the statement:

select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;

This statement works fine in the sqlite3 shell. This statement does not work in 
my API.
Is there a PRAGMA I need to issue so I can check for table existence?

TIA

Mike

PERL code to check for table existence:

sub tableexists($$) {
my $dbh = shift;
my $name = shift;
my $tableexists = 0;

$dbh->do("pragma writable_schema = 'on';");

my $sql = "select count(*) from sqlite_master where type = 'table' and name 
= '$name';";
my $stmt = $dbh->prepare($sql);
$stmt->execute or die "$0: verifying table name failed: $DBI::errstr";
while(my @row = $stmt->fetchrow_array) {
$tableexists = $row[0];
}
$stmt->finish;

$dbh->do("pragma writable_schema = 'off';");

return $tableexists;
}

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

[sqlite] bug when saving database under dos

[janezz55 .]

Do you have the "write" rights to the directory?

yes, the file does get written (file size 0), but the error is returned
anyway.

Do you already have a file of that name in that folder ?

no, I can provide an arbitrary name and it won't work. The file does
get written (file size 0), but the error is returned.

Does SHELL.EXE have enough rights to overwrite it ?

yes. it can do anything it wants.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug when saving database under dos

[Simon Slavin]

On 22 Sep 2017, at 4:36pm, janezz55 .  wrote:

> I made an image of the problem:
> 
> https://i.stack.imgur.com/a09zN.png

Do you already have a file of that name in that folder ?

Does SHELL.EXE have enough rights to overwrite it ?

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

Re: [sqlite] bug when saving database under dos

[Igor Korot]

Hi,

On Fri, Sep 22, 2017 at 11:36 AM, janezz55 .  wrote:
> Hello! Saving the database under dos-compiled sqlite does not work. How can
> I go about debugging this (probably, just a small fix is necessary)? Maybe
> you have experience with this bug? I made an image of the problem:
>
> https://i.stack.imgur.com/a09zN.png

Do you have the "write" rights to the directory?

Thank you.

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

[sqlite] bug when saving database under dos

[janezz55 .]

Hello! Saving the database under dos-compiled sqlite does not work. How can
I go about debugging this (probably, just a small fix is necessary)? Maybe
you have experience with this bug? I made an image of the problem:

https://i.stack.imgur.com/a09zN.png
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Fri, 22 Sep 2017 10:54:21 +0100, John G
 wrote:

>I know this is an older thread, but shouldn't that reference be on the ITEM
>table ?  So ...
>
>CREATE TABLE ATTRIBUTES (
>ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE,
>   KEY   TEXT,
>VALUE TEXT,
>PRIMARY KEY (ITEM_ID,KEY)
>  ) WITHOUT ROWID;
>
>John G

Yes, you are right. I didn't pay enough attention typing that
code. My apologies for any confusion that may have caused.

-- 
Regards,
Kees Nuyt



>On 11 September 2017 at 13:11, Kees Nuyt  wrote:
>
>> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt 
>> wrote:
>>
>> > CREATE TABLE ATTRIBUTES (
>> >  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
>> >   KEY   TEXT,
>> >   VALUE TEXT,
>> >   PRIMARY KEY (ITEM_ID,KEY)
>> > ) WITHOUT ROWID;
>> > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);
>>
>> Correction:
>> In this construct, it makes no sense to create the index
>> attr_item_id, because the ITEM_ID is the first column
>> of the primary key (which is indexed implicitly).
>>
>> So, you can leave out the CREATE INDEX attr_item_id
>> statement in this case.
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running query in command window is slow

[Frank Millman]

Richard Hipp wrote:
On 9/22/17, Frank Millman  wrote:
> > I see that Python is using Sqlite3 3.14.2.
>
> What version of the command-line shell are you using?  It would be a problem 
> if the command-line shell is a later version and yet is running slower.
I was running 3.8.6, so it was actually quite old.

I have now upgraded both of them to 3.20.1

Sorry for the confusion.

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

Re: [sqlite] Running query in command window is slow

[Richard Hipp]

On 9/22/17, Frank Millman  wrote:
> I see that Python is using Sqlite3 3.14.2.
>

What version of the command-line shell are you using?  It would be a
problem if the command-line shell is a later version and yet is
running slower.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with SQL query

[John G]

I know this is an older thread, but shouldn't that reference be on the ITEM
table ?  So ...

CREATE TABLE ATTRIBUTES (
ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE,
   KEY   TEXT,
VALUE TEXT,
PRIMARY KEY (ITEM_ID,KEY)
  ) WITHOUT ROWID;

John G

On 11 September 2017 at 13:11, Kees Nuyt  wrote:

> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt 
> wrote:
>
> > CREATE TABLE ATTRIBUTES (
> >  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
> >   KEY   TEXT,
> >   VALUE TEXT,
> >   PRIMARY KEY (ITEM_ID,KEY)
> > ) WITHOUT ROWID;
> > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);
>
> Correction:
> In this construct, it makes no sense to create the index
> attr_item_id, because the ITEM_ID is the first column
> of the primary key (which is indexed implicitly).
>
> So, you can leave out the CREATE INDEX attr_item_id
> statement in this case.
>
> --
> Regards,
>
> Kees Nuyt
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running query in command window is slow

[Frank Millman]

Clemens Ladisch wrote:
> 
> > Frank Millman wrote:
> > I have a fairly complex query. If I execute it using Python, it takes
> > 1 second. If I copy and paste it into the Sqlite3 command window, it
> > takes 10 minutes.
> 
> Probably different SQLite versions.
> 
> What is the EXPLAIN QUERY PLAN output in both cases?
> 


Thanks, Clemens. You just beat me to it.

I was about to reply that I had forgotten to check that, but now that I have, I 
see that Python is using Sqlite3 3.14.2.

I won’t show the query plan unless you really want to see it. The query 
involves a JOIN to a VIEW, which then has to retrieve data from the underlying 
physical tables. Obviously some work has been done to optimise this in more 
recent versions.

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

Re: [sqlite] Running query in command window is slow

[Clemens Ladisch]

Frank Millman wrote:
> I have a fairly complex query. If I execute it using Python, it takes
> 1 second. If I copy and paste it into the Sqlite3 command window, it
> takes 10 minutes.

Probably different SQLite versions.

What is the EXPLAIN QUERY PLAN output in both cases?


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

[sqlite] Running query in command window is slow

[Frank Millman]

Hi all

I am running Python 3.6.0 and Sqlite3 3.8.6 on Windows 10.

I have a fairly complex query. If I execute it using Python, it takes 1 second. 
If I copy and paste it into the Sqlite3 command window, it takes 10 minutes.

I am not too bothered – it is the Python one that is important. Still, it seems 
odd. Can anyone suggest a reason for this?

Thanks

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