Re: [sqlite] Namespacing sqlite3

2013-09-24 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/09/13 16:37, Neville Dastur wrote:
> That's a very interesting concept.

I've been doing it since 2004, and support all versions of Python from 2.3
onwards including 3.x.

> Are you using http://www.cython.org/ or something else to "create" the
> library wrapper

Nope.  I write good old fashioned C code against the CPython API.
https://code.google.com/p/apsw/source/browse/src

This where sqlite3.c gets #included followed by the other files a few
lines later.

https://code.google.com/p/apsw/source/browse/src/apsw.c#46

Cython didn't exist when I started, but SWIG did.  However it required a
lot of scaffolding and wasn't worth it.  My resulting code performs better
than Python's somewhat standard sqlite3 module, and has far better
testing, as well as complete coverage of the SQLite API.

  http://apidoc.apsw.googlecode.com/hg/pysqlite.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJCZOcACgkQmOOfHg372QRaYwCgqoR4C9DmXg27SCbOXg2a4GNe
vyUAnRYFf8Uej21hrJTkhwXJWddkvYWi
=bKyX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Namespacing sqlite3

2013-09-24 Thread Neville Dastur 
That's a very interesting concept.

Are you using http://www.cython.org/ or something else to "create" the library 
wrapper

Neville

On 24 Sep 2013, at 22:39, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 24/09/13 12:56, Neville Dastur wrote:
>> Searching around on Google it seems that namespacing in c / obj-c is
>> not possible for the sqlite3 library.
> 
> There is another approach that I use.  I produce a Python C extension.
> The final shared library with my extension contains only one exported
> symbol as expected by Python, with all my symbols and SQLite private inside.
> 
> What I do is make everything one compilation unit.  SQLite is already
> setup for this.  For example in single.c:
> 
>   #define SQLITE_API static
>   #define SQLITE_EXTERN static
>   #include "sqlite3.c"
> 
>   #include my other C files
> 
> All my methods are declared static too.  This approach has the advantage
> that it works really well and has no expectations or dependencies on the
> rest of the system.  It even works if another copy of SQLite is loaded
> into the process (eg CoreData on Mac likes to do that).  As another bonus
> it is also a bit faster too as the compiler ends up inlining SQLite code
> into your methods that call it.
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.12 (GNU/Linux)
> 
> iEYEARECAAYFAlJCBoIACgkQmOOfHg372QRRsQCePzIDcnfYiXf3c/RHyqhnlsdz
> pZcAoNrIRsMoScmoR3c10py9mynosLmm
> =KTqr
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Namespacing sqlite3

2013-09-24 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/09/13 12:56, Neville Dastur wrote:
> Searching around on Google it seems that namespacing in c / obj-c is
> not possible for the sqlite3 library.

There is another approach that I use.  I produce a Python C extension.
The final shared library with my extension contains only one exported
symbol as expected by Python, with all my symbols and SQLite private inside.

What I do is make everything one compilation unit.  SQLite is already
setup for this.  For example in single.c:

   #define SQLITE_API static
   #define SQLITE_EXTERN static
   #include "sqlite3.c"

   #include my other C files

All my methods are declared static too.  This approach has the advantage
that it works really well and has no expectations or dependencies on the
rest of the system.  It even works if another copy of SQLite is loaded
into the process (eg CoreData on Mac likes to do that).  As another bonus
it is also a bit faster too as the compiler ends up inlining SQLite code
into your methods that call it.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlJCBoIACgkQmOOfHg372QRRsQCePzIDcnfYiXf3c/RHyqhnlsdz
pZcAoNrIRsMoScmoR3c10py9mynosLmm
=KTqr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] best practice on sqlite database schema refactoring

2013-09-24 Thread Bao Niu 
>From time to time, with a database already loaded with hundreds records, I
need to change a column name, or move a field from one table to another
table(sometimes even another database). I use python to code with my
database. With python if you want to refactor your code there is a lot of
tools to do it automatically, which help change/move function names while
preserving dependencies for you.
Is there an automatic tool for sqlite refactoring? Is doing it manually the
only option? What is the best practice?
Many thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Keith Medcalf 

It is using the country table and aliasing it to languages ...

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of John Drescher
> Sent: Tuesday, 24 September, 2013 10:50
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug in sqlite3.exe?
> 
> On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen
> wrote:
> 
> > SQLite version 3.7.16.2 2013-04-12 11:52:43
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> .tables
> > CityCountry Languages
> > Country Country Official Languages
> > Country CapitalsCountryLanguage
> > sqlite> select count(*) from country languages;
> > count(*)
> > --
> 
> 239
> >
> 
> Although I am a bit surprised that this query succeeded since there is
> no
> languages table.
> 
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Namespacing sqlite3

2013-09-24 Thread Neville Dastur 
Okay, so it's already namespaced with a prefix which I can replace. Thank you

On 24 Sep 2013, at 21:27, Richard Hipp  wrote:

> On Tue, Sep 24, 2013 at 3:56 PM, Neville Dastur 
> wrote:
> 
>> Hi
>> 
>> I am hoping someone can help with this one. I am aware that some might
>> consider it a C issue more than sqlite3, but C doesn't handle this well and
>> so I am looking for specific sqlite3 info.
>> 
>> I am looking to create my own sqlite3 Appcelerator module. Appcelerator at
>> present already uses sqlite3 and so compiles in the iOS sqlite3 dynamic
>> library. This is not something I have control over. So if I create my own
>> module the sqlite3 external symbols cause a duplicate symbol error.
>> 
>> Searching around on Google it seems that namespacing in c / obj-c is not
>> possible for the sqlite3 library. Or at least I haven't got any of the hacky
>> workarounds to work. So I was wondering if someone was able to point me
>> in the direction of creating a list of symbols that I would need to rename
>> in order to effectively namespace the library. I would intend to parse the
>> consolidated source through perl or the such like to achieve this.
>> 
> 
> sed s/sqlite3_/nevilleDb_/g sqlite3.c >nevilleDb.c
> 
> 
>> 
>> Thanks
>> 
>> Neville
>> 
>> --
>> Surgeons Net Education: http://www.surgeons.org.uk
>> Clinical Software Solutions: http://www.clinsoftsolutions.com
>> Find our free and paid apps on the iTunes Apple store and Android Google
>> Play store
>> LinkedIn: http://www.linkedin.com/profile/view?id=49617062
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Namespacing sqlite3

2013-09-24 Thread Richard Hipp 
On Tue, Sep 24, 2013 at 3:56 PM, Neville Dastur wrote:

> Hi
>
> I am hoping someone can help with this one. I am aware that some might
> consider it a C issue more than sqlite3, but C doesn't handle this well and
> so I am looking for specific sqlite3 info.
>
> I am looking to create my own sqlite3 Appcelerator module. Appcelerator at
> present already uses sqlite3 and so compiles in the iOS sqlite3 dynamic
> library. This is not something I have control over. So if I create my own
> module the sqlite3 external symbols cause a duplicate symbol error.
>
> Searching around on Google it seems that namespacing in c / obj-c is not
> possible for the sqlite3 library. Or at least I haven't got any of the hacky
>  workarounds to work. So I was wondering if someone was able to point me
> in the direction of creating a list of symbols that I would need to rename
> in order to effectively namespace the library. I would intend to parse the
> consolidated source through perl or the such like to achieve this.
>

 sed s/sqlite3_/nevilleDb_/g sqlite3.c >nevilleDb.c


>
> Thanks
>
> Neville
>
> --
> Surgeons Net Education: http://www.surgeons.org.uk
> Clinical Software Solutions: http://www.clinsoftsolutions.com
> Find our free and paid apps on the iTunes Apple store and Android Google
> Play store
> LinkedIn: http://www.linkedin.com/profile/view?id=49617062
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Namespacing sqlite3

2013-09-24 Thread Neville Dastur 
Hi

I am hoping someone can help with this one. I am aware that some might consider 
it a C issue more than sqlite3, but C doesn't handle this well and so I am 
looking for specific sqlite3 info.

I am looking to create my own sqlite3 Appcelerator module. Appcelerator at 
present already uses sqlite3 and so compiles in the iOS sqlite3 dynamic 
library. This is not something I have control over. So if I create my own 
module the sqlite3 external symbols cause a duplicate symbol error.

Searching around on Google it seems that namespacing in c / obj-c is not 
possible for the sqlite3 library. Or at least I haven't got any of the hacky 
 workarounds to work. So I was wondering if someone was able to point me in the 
direction of creating a list of symbols that I would need to rename in order to 
effectively namespace the library. I would intend to parse the consolidated 
source through perl or the such like to achieve this.

Thanks

Neville

--
Surgeons Net Education: http://www.surgeons.org.uk
Clinical Software Solutions: http://www.clinsoftsolutions.com
Find our free and paid apps on the iTunes Apple store and Android Google Play 
store
LinkedIn: http://www.linkedin.com/profile/view?id=49617062








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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Richard Hipp 
On Mon, Sep 23, 2013 at 12:19 PM, Jean-Marie CUAZ  wrote:

> Hello,
>
> Thanks to SQlite developement team for this wonderfull library
>
> We have been hit today by the following :
>
> package require sqlite3
>
> sqlite3 db1 test
>
> db1 eval {CREATE TABLE T1 (A PRIMARY KEY, B, C, D UNIQUE DEFAULT '-')}
>
> db1 eval {INSERT INTO T1 VALUES (0, 1,  1, 'a') , (1, 1, 1, 'b') , (2, 1,
> 1 , 'c')}
>
> the issue is here :
>
> db1 transaction {
>
> db1 eval {INSERT OR REPLACE INTO T1 (A, B, C) VALUES  (0, 2, 0),
> (1, 2, 0) , (2, 2, 0)}
>
> }
>
> -> no exception is raised to host langage Tcl for 2 rows not inserted
> because a UNIQUE constraint is not respected
> -> partial execution : 2 rows are definitely deleted from the table
> ...(ouch !)
>
>
Works as designed.

The first REPLACE changes the 0-row from 0,1,1,a into 0,2,0,-.  The second
REPLACE replaces two rows 0,2,0,- and 1,1,1,b with a single new row
1,2,0,-.  The 0,2,0,- was replace to satisfy the UNIQUE constraint and the
1,1,1,b was replaced to satisfy the PRIMARY KEY constraint.  The third
REPLACE replaces the 0,2,0,- and 2,1,1,c rows with 2,2,0,-.  Again, two
rows are replaced with one in order to satisfy two constraints:  The UNIQUE
and the primary key.  You are left with a single row: 2,2,0,-.

What exactly where you expecting to happen?  What do you think REPLACE
should do if it has to content with two separate constraint conflicts on
two separate rows?




>
> Regards,
>
> Jean-Marie Cuaz
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] sqlite database created in PHP not readable in python

2013-09-24 Thread Richard Hipp 
On Mon, Sep 23, 2013 at 11:19 PM, Aryc  wrote:

> All;
> I'm trying to use SQlite as a transfer medium between my PHP world and my
> Python world.
> in PHP i can read and write just fine. but when i try to read the database
> from a python program i get the dreaded "file is encrypted or not a
> database" message.
> can any  body help me?
>
>
Are you using the PHP interface for SQLite2 or for SQLite3?  What are the
first 16 bytes of the generated database file?



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



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

Re: [sqlite] SQLite3 3.7.17 => using column names with character '@'.

2013-09-24 Thread Richard Hipp 
On Tue, Sep 24, 2013 at 2:02 PM, Petite Abeille wrote:

>
> On Sep 24, 2013, at 7:59 PM, Dan Kennedy  wrote:
>
> > A double quoted string is treated as a column name if possible, or a
> > string literal otherwise. It's an SQL thing.
>
> Nah. The second part is a SQLite thing. Randomly converting identifiers
> into literals is more of a misfeature.
>

It was originally intended as a feature to provide enhanced MySQL
compatibility, since MySQL used to *require* double-quotes on strings.
Yes, it is something of a misfeature now, but we cannot change it without
breaking backwards compatibility.



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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 8:16 PM, Simon Slavin  wrote:

> The first version (INSERT OR FAIL, then UPDATE) won't lead to any SQLite 
> errors if one of the rows already exists. So you can do a whole lot of both 
> lines in one transaction and the transaction will still succeed.

Is that so? If 'INSERT OR IGNORE' ignores,  say, a null constraint, as opposed 
to, say, a unique one, then you are none the wiser.

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 7:19pm, Marc L. Allen  wrote:

> INSERT OR IGNORE?

Dammit, yes.  I meant INSERT OR IGNORE, not INSERT OR FAIL.  Thanks Marc.

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 8:05 PM, Simon Slavin  wrote:

> Which is why you do an INSERT first, and allow it to fail, then do the UPDATE.

Sure. A lot of error proce procedural code to do what one SQL statement could 
do much more naturally.

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Marc L. Allen 
So it does!  And, for those of us lucky enough to be on a system that isn't 8 
years old, enjoy! ;)

And now that I see that, I will renew push for upgrades. 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 2:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 24, 2013, at 8:09 PM, Marc L. Allen  wrote:

> Not complaining, mind you.  MS SQL doesn't have it, and I've long learned to 
> deal with it.

MS SQL Server sports a MERGE statement if I'm not mistaken:

http://msdn.microsoft.com/en-us/library/bb510625.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Marc L. Allen 
INSERT OR IGNORE? 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, September 24, 2013 2:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On 24 Sep 2013, at 7:09pm, Marc L. Allen  wrote:

> Also, there are times when you do a bulk insert, so you have to structure the 
> query to not fail on records that are already present.

Yeah.  Actually I got what I posted wrong.  I should have written

Which is why you do an INSERT OR FAIL, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

The first version (INSERT OR FAIL, then UPDATE) won't lead to any SQLite errors 
if one of the rows already exists. So you can do a whole lot of both lines in 
one transaction and the transaction will still succeed.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 7:09pm, Marc L. Allen  wrote:

> Also, there are times when you do a bulk insert, so you have to structure the 
> query to not fail on records that are already present.

Yeah.  Actually I got what I posted wrong.  I should have written

Which is why you do an INSERT OR FAIL, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

The first version (INSERT OR FAIL, then UPDATE) won't lead to any SQLite errors 
if one of the rows already exists. So you can do a whole lot of both lines in 
one transaction and the transaction will still succeed.

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 8:09 PM, Marc L. Allen  wrote:

> Not complaining, mind you.  MS SQL doesn't have it, and I've long learned to 
> deal with it.

MS SQL Server sports a MERGE statement if I'm not mistaken:

http://msdn.microsoft.com/en-us/library/bb510625.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Igor Tandetnik 

On 9/24/2013 12:49 PM, John Drescher wrote:

On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote:


SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
CityCountry Languages
Country Country Official Languages
Country CapitalsCountryLanguage
sqlite> select count(*) from country languages;
count(*)
--


239




Although I am a bit surprised that this query succeeded since there is no
languages table.


"languages" is an alias here. The query is equivalent to

select count(*) from country AS languages;

AS is optional and can be omitted.
--
Igor Tandetnik

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 8:06 PM, Marc L. Allen  wrote:

> Considered harmful?  How so?  I wouldn't mind a version of ON CONFLICT UPDATE 
> fieldlist.

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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Marc L. Allen 
Yes, of course.  But, it seems a waste of time, since you're obviously already 
at the record (or at least done the initial index search) to not be able to 
simply update it.

Not complaining, mind you.  MS SQL doesn't have it, and I've long learned to 
deal with it.

Also, there are times when you do a bulk insert, so you have to structure the 
query to not fail on records that are already present.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, September 24, 2013 2:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On 24 Sep 2013, at 6:58pm, Petite Abeille  wrote:

> On Sep 24, 2013, at 7:54 PM, "Marc L. Allen"  
> wrote:
> 
>> Yep.  What most people want is an INSERT OR UPDATE.  
> 
> Yep. Which is what one usually calls 'MERGE':
> 
> http://en.wikipedia.org/wiki/Merge_(SQL)
> 
> And sadly, SQLite doesn't provide anything like that at all.

Which is why you do an INSERT first, and allow it to fail, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 7:54 PM, "Marc L. Allen"  
wrote:

> Yep.  What most people want is an INSERT OR UPDATE.  

Yep. Which is what one usually calls 'MERGE':

http://en.wikipedia.org/wiki/Merge_(SQL)

And sadly, SQLite doesn't provide anything like that at all. Oh, well… 'ON 
CONFLICT clause' considered harmful. There you have it.



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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Marc L. Allen 
Considered harmful?  How so?  I wouldn't mind a version of ON CONFLICT UPDATE 
fieldlist.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 1:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 24, 2013, at 7:54 PM, "Marc L. Allen"  
wrote:

> Yep.  What most people want is an INSERT OR UPDATE.  

Yep. Which is what one usually calls 'MERGE':

http://en.wikipedia.org/wiki/Merge_(SQL)

And sadly, SQLite doesn't provide anything like that at all. Oh, well... 'ON 
CONFLICT clause' considered harmful. There you have it.



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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 6:58pm, Petite Abeille  wrote:

> On Sep 24, 2013, at 7:54 PM, "Marc L. Allen"  
> wrote:
> 
>> Yep.  What most people want is an INSERT OR UPDATE.  
> 
> Yep. Which is what one usually calls 'MERGE':
> 
> http://en.wikipedia.org/wiki/Merge_(SQL)
> 
> And sadly, SQLite doesn't provide anything like that at all.

Which is why you do an INSERT first, and allow it to fail, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

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

Re: [sqlite] SQLite3 3.7.17 => using column names with character '@'.

2013-09-24 Thread Petite Abeille 

On Sep 24, 2013, at 7:59 PM, Dan Kennedy  wrote:

> A double quoted string is treated as a column name if possible, or a
> string literal otherwise. It's an SQL thing.

Nah. The second part is a SQLite thing. Randomly converting identifiers into 
literals is more of a misfeature.

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

Re: [sqlite] Problem with method numRows() in Sqlite3

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 9:50am, pisey phon  wrote:

> And now I have an error with insert data into database with Sqlite3.
> Errro: "SQLite3::exec() [sqlite3.exec]: near "SET": syntax error".

Insert a debugging line into your program so that having make up the full 
INSERT command it shows it on the display or put it in a log file before trying 
to ->exec it.  There's probably a syntax error in the command.

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

Re: [sqlite] sqlite database created in PHP not readable in python

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 4:19am, Aryc  wrote:

> I'm trying to use SQlite as a transfer medium between my PHP world and my 
> Python world.
> in PHP i can read and write just fine. but when i try to read the database 
> from a python program i get the dreaded "file is encrypted or not a database" 
> message.
> can any  body help me?

The first thing to try is to make absolutely sure that they are both accessing 
the same file.  If you are not specifying complete a path for the database file 
in both languages, please make that change, just for testing purposes.

I think your PHP library is using a file format that is too new for your Python 
library.  In both languages please execute the commands

SELECT sqlite_version()
PRAGMA journal_mode

and tell us what you get back.  You might also try the same thing in the 
opposite direction:

delete the existing database file
use Python to create the database file, a table in it, and a row in the table
try to read the file from your PHP interface.

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

Re: [sqlite] SQLite3 3.7.17 => using column names with character '@'.

2013-09-24 Thread Dan Kennedy 

On 09/24/2013 04:52 PM, Alexander Syvak wrote:

Hello,

using input select all "project@na" from project where 1 yields 4 lines of
"project@na". In fact the column named project@na does not exist, but the
project@name does in the tested data base. What is the gist of such a
result?


A double quoted string is treated as a column name if possible, or a
string literal otherwise. It's an SQL thing. More detail here:

  http://www.sqlite.org/lang_keywords.html


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

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Marc L. Allen 
Yep.  What most people want is an INSERT OR UPDATE.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 1:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 23, 2013, at 6:19 PM, Jean-Marie CUAZ  wrote:

> -> no exception is raised to host langage Tcl for 2 rows not inserted 
> -> because a UNIQUE constraint is not respected partial execution : 2 
> -> rows are definitely deleted from the table ...(ouch !)

Yep. Looks like it does exactly what it says on the tin: 

"When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes 
pre-existing rows that are causing the constraint violation prior to inserting 
or updating the current row and the command continues executing normally. If a 
NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces 
the NULL value with the default value for that column, or if the column has no 
default value, then the ABORT algorithm is used. If a CHECK constraint 
violation occurs, the REPLACE conflict resolution algorithm always works like 
ABORT."

http://www.sqlite.org/lang_conflict.html

Usually... with insight... 'replace' is rarely what one really wants.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Petite Abeille 

On Sep 23, 2013, at 6:19 PM, Jean-Marie CUAZ  wrote:

> -> no exception is raised to host langage Tcl for 2 rows not inserted because 
> a UNIQUE constraint is not respected
> -> partial execution : 2 rows are definitely deleted from the table ...(ouch 
> !)

Yep. Looks like it does exactly what it says on the tin: 

"When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes 
pre-existing rows that are causing the constraint violation prior to inserting 
or updating the current row and the command continues executing normally. If a 
NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces 
the NULL value with the default value for that column, or if the column has no 
default value, then the ABORT algorithm is used. If a CHECK constraint 
violation occurs, the REPLACE conflict resolution algorithm always works like 
ABORT."

http://www.sqlite.org/lang_conflict.html

Usually… with insight… 'replace' is rarely what one really wants.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 3.7.17 => using column names with character '@'.

2013-09-24 Thread Alexander Syvak 
Hello,

using input select all "project@na" from project where 1 yields 4 lines of
"project@na". In fact the column named project@na does not exist, but the
project@name does in the tested data base. What is the gist of such a
result?
P.S. Please, reply to my e-mail.
Regards,
Alexander.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with method numRows() in Sqlite3

2013-09-24 Thread pisey phon 
And now I have an error with insert data into database with Sqlite3.
Errro: "SQLite3::exec() [sqlite3.exec]: near "SET": syntax error".
I a a new with Sqlite3

here is my code:

open($db_file);
}
public function connecting($db){
if(!$db){
echo $db->lastErrorMsg();
}else{
echo "Opened database successfully";
}
}
public function queryData($db,$table){
$this->connecting($db);
$result = $db->query("select * from $table");
return $result;
}

public function stringEscape($string){
return SQLite3::escapeString($string);
}

public function save($db,$table,$fields=array()){

if(isset($table)&&$table!==""&_array($fields)&&!empty($fields)){
$query = $db->exec("INSERT INTO $table SET ");
$temp = array();
foreach($fields as $fieldName => $fieldValue ){
$temp[] = $fieldName . " = '" . 
$this->stringEscape($fieldValue) ."' ";
}
$query .= implode( " , " , $temp ) . " ; " ;
//$db = $db->queryExec($query);
return $this->queryData($db,$query);
} 
return false;
}

}   
$db = new SQLite('test.at');
echo "
Insert Data into Database
";
$result = $db->save($db,"people",array("ID"=>NULL, "Name"=>"SeySey",
"Sex"=>"F"));
$result->execute();
//print_r($result);
?>



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Problem-with-method-numRows-in-Sqlite3-tp71420p71440.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

2013-09-24 Thread Jean-Marie CUAZ 

Hello,

Thanks to SQlite developement team for this wonderfull library

We have been hit today by the following :

package require sqlite3

sqlite3 db1 test

db1 eval {CREATE TABLE T1 (A PRIMARY KEY, B, C, D UNIQUE DEFAULT '-')}

db1 eval {INSERT INTO T1 VALUES (0, 1,  1, 'a') , (1, 1, 1, 'b') , (2, 
1, 1 , 'c')}


the issue is here :

db1 transaction {

db1 eval {INSERT OR REPLACE INTO T1 (A, B, C) VALUES  (0, 2, 
0), (1, 2, 0) , (2, 2, 0)}


}

-> no exception is raised to host langage Tcl for 2 rows not inserted 
because a UNIQUE constraint is not respected
-> partial execution : 2 rows are definitely deleted from the table 
...(ouch !)



Regards,

Jean-Marie Cuaz

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

Re: [sqlite] Problem with method numRows() in Sqlite3

2013-09-24 Thread pisey phon 
thanks you so much for replying it really help me



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Problem-with-method-numRows-in-Sqlite3-tp71420p71439.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite database created in PHP not readable in python

2013-09-24 Thread Aryc 
All;
I'm trying to use SQlite as a transfer medium between my PHP world and my 
Python world.
in PHP i can read and write just fine. but when i try to read the database from 
a python program i get the dreaded "file is encrypted or not a database" 
message.
can any  body help me?


Thanks
Paul Alagna 


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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Staffan Tylen 
Many who solved this, thank you all.

Staffan



On Tue, Sep 24, 2013 at 7:11 PM, Yuriy Kaminskiy  wrote:

> Staffan Tylen wrote:
> > On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin 
> wrote:
> >
> >> On 24 Sep 2013, at 5:35pm, Staffan Tylen 
> wrote:
> >>
> >>> sqlite> .tables
> >>> CityCountry Languages
> >>> Country Country Official Languages
> >>> Country CapitalsCountryLanguage
> >> Either don't use spaces in your token names (table names, column names,
> >> index names, etc.) or quote them when you use them.  Something like
> >>
> >> select count(*) from "country official languages";
> >>
> >> or
> >>
> >> select count(*) from [country official languages];
> >>
> >> will probably work.  I avoid all space in token names because they cause
> >> problems with other versions of SQL too, and I don't want to get into
> >> dangerous habits.
>
> > Well, it's not my database I'm looking at. What puzzles me is that
> Country
> > Languages works but Country Official Languages doesn't, so could there
> be a
> > parsing problem?
>
> No. `Languages` is interpreted as *alias* to table `Country`:
>
> SELECT ... FROM Country Languages
> is same as
> SELECT ... FROM [Country] AS [Languages]
>
> And
> SELECT ... FROM Country Official
> is same as
> SELECT ... FROM [Country] AS [Official]
>
> And that's why `SELECT ... FROM Country Languages` return exactly same
> result as
> `SELECT FROM Country Official`; if you would've issued just `SELECT ...
> FROM
> Country`, it would've returned same result as well.
>
> > I agree, the names should be quoted ...
> >
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Yuriy Kaminskiy 
Staffan Tylen wrote:
> On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin  wrote:
> 
>> On 24 Sep 2013, at 5:35pm, Staffan Tylen  wrote:
>>
>>> sqlite> .tables
>>> CityCountry Languages
>>> Country Country Official Languages
>>> Country CapitalsCountryLanguage
>> Either don't use spaces in your token names (table names, column names,
>> index names, etc.) or quote them when you use them.  Something like
>>
>> select count(*) from "country official languages";
>>
>> or
>>
>> select count(*) from [country official languages];
>>
>> will probably work.  I avoid all space in token names because they cause
>> problems with other versions of SQL too, and I don't want to get into
>> dangerous habits.

> Well, it's not my database I'm looking at. What puzzles me is that Country
> Languages works but Country Official Languages doesn't, so could there be a
> parsing problem?

No. `Languages` is interpreted as *alias* to table `Country`:

SELECT ... FROM Country Languages
is same as
SELECT ... FROM [Country] AS [Languages]

And
SELECT ... FROM Country Official
is same as
SELECT ... FROM [Country] AS [Official]

And that's why `SELECT ... FROM Country Languages` return exactly same result as
`SELECT FROM Country Official`; if you would've issued just `SELECT ... FROM
Country`, it would've returned same result as well.

> I agree, the names should be quoted ...
>
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Dan Kennedy 

On 09/24/2013 11:53 PM, Staffan Tylen wrote:

Well, it's not my database I'm looking at. What puzzles me is that Country
Languages works but Country Official Languages doesn't, so could there be a
parsing problem?



It's because the AS keyword is optional. These two are equivalent:

  SELECT count(*) FROM Country Languages;
  SELECT count(*) FROM Country AS Languages;

Dan.







On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin  wrote:


On 24 Sep 2013, at 5:35pm, Staffan Tylen  wrote:


sqlite> .tables
CityCountry Languages
Country Country Official Languages
Country CapitalsCountryLanguage

Either don't use spaces in your token names (table names, column names,
index names, etc.) or quote them when you use them.  Something like

select count(*) from "country official languages";

or

select count(*) from [country official languages];

will probably work.  I avoid all space in token names because they cause
problems with other versions of SQL too, and I don't want to get into
dangerous habits.

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


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


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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread John Drescher 
On Tue, Sep 24, 2013 at 12:53 PM, Staffan Tylen wrote:

> Well, it's not my database I'm looking at. What puzzles me is that Country
> Languages works but Country Official Languages doesn't, so could there be a
> parsing problem?
>
>
I think I see what is happening..

when you have a space in the from the second name is being taken as an AS
(used for an alias)

So in your

You are counting the number of countries in your Country table.

The following should work in that example:

select count(languages.*) from country languages;

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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Richard Hipp 
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote:

> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .tables
> CityCountry Languages
> Country Country Official Languages
> Country CapitalsCountryLanguage
> sqlite> select count(*) from country languages;
>

The previous is parsed the same as:

   SELECT count(*) FROM country AS languages;

Your are querying the table named "country" and giving it an alias of
"languages".




> count(*)
> --
> 239
> sqlite> select count(*) from country official languages;
> Error: near "languages": syntax error
> sqlite> select count(*) from country official;
> count(*)
> --
> 239
> sqlite> select count(*) from countrylanguage;
> count(*)
> --
> 988
> sqlite> select count(*) from languages;
> Error: no such table: languages
> sqlite>
>
> I'm aware the version of SQLite is not current so it might already have
> been fixed.
>
> Staffan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Staffan Tylen 
Well, it's not my database I'm looking at. What puzzles me is that Country
Languages works but Country Official Languages doesn't, so could there be a
parsing problem?

I agree, the names should be quoted ...


On Tue, Sep 24, 2013 at 6:50 PM, Simon Slavin  wrote:

>
> On 24 Sep 2013, at 5:35pm, Staffan Tylen  wrote:
>
> > sqlite> .tables
> > CityCountry Languages
> > Country Country Official Languages
> > Country CapitalsCountryLanguage
>
> Either don't use spaces in your token names (table names, column names,
> index names, etc.) or quote them when you use them.  Something like
>
> select count(*) from "country official languages";
>
> or
>
> select count(*) from [country official languages];
>
> will probably work.  I avoid all space in token names because they cause
> problems with other versions of SQL too, and I don't want to get into
> dangerous habits.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread John Drescher 
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote:

> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .tables
> CityCountry Languages
> Country Country Official Languages
> Country CapitalsCountryLanguage
> sqlite> select count(*) from country languages;
> count(*)
> --

239
>

Although I am a bit surprised that this query succeeded since there is no
languages table.

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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Simon Slavin 

On 24 Sep 2013, at 5:35pm, Staffan Tylen  wrote:

> sqlite> .tables
> CityCountry Languages
> Country Country Official Languages
> Country CapitalsCountryLanguage

Either don't use spaces in your token names (table names, column names, index 
names, etc.) or quote them when you use them.  Something like

select count(*) from "country official languages";

or

select count(*) from [country official languages];

will probably work.  I avoid all space in token names because they cause 
problems with other versions of SQL too, and I don't want to get into dangerous 
habits.

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

Re: [sqlite] Bug in sqlite3.exe?

2013-09-24 Thread John Drescher 
On Tue, Sep 24, 2013 at 12:35 PM, Staffan Tylen wrote:

> SQLite version 3.7.16.2 2013-04-12 11:52:43
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .tables
> CityCountry Languages
> Country Country Official Languages
> Country CapitalsCountryLanguage
> sqlite> select count(*) from country languages;
> count(*)
> --
> 239
> sqlite> select count(*) from country official languages;
> Error: near "languages": syntax error
> sqlite> select count(*) from country official;
> count(*)
> --
> 239
> sqlite> select count(*) from countrylanguage;
> count(*)
> --
> 988
> sqlite> select count(*) from languages;
> Error: no such table: languages
> sqlite>
>
> I'm aware the version of SQLite is not current so it might already have
> been fixed.
>
>
This does not look like a bug to me. You need to escape spaces.

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

[sqlite] Bug in sqlite3.exe?

2013-09-24 Thread Staffan Tylen 
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
CityCountry Languages
Country Country Official Languages
Country CapitalsCountryLanguage
sqlite> select count(*) from country languages;
count(*)
--
239
sqlite> select count(*) from country official languages;
Error: near "languages": syntax error
sqlite> select count(*) from country official;
count(*)
--
239
sqlite> select count(*) from countrylanguage;
count(*)
--
988
sqlite> select count(*) from languages;
Error: no such table: languages
sqlite>

I'm aware the version of SQLite is not current so it might already have
been fixed.

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