[sqlite] Schema syntax error

Divmod Axiom[1] is a Python ORM built on SQLite; one of the book keeping tables it creates in the database has a column named "indexed", which became a reserved word around SQLite 3.6.4 (?). The "obvious" fix for this problem is to simply quote the column name using "", but the problem is that it

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

On Mon, Mar 16, 2009 at 10:08 AM, P Kishor wrote: > On Mon, Mar 16, 2009 at 8:59 AM, John Machin wrote: >> On 17/03/2009 12:33 AM, P Kishor wrote: >>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor wrote: is there a way to have a

Re: [sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"

manohar s wrote: > Yes, the solution you suggested is working fine. But can't we change this > through SQLite? > > You can set the location SQLite will use for temporary files using pragma commands. See pragma_temp_store and pragma_temp_store_directory at

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

P Kishor wrote: > > compatibility. And, as 'they' say, 0 is a perfectly fine number. Why > let it go waste. > > Real people always start counting from 1. Only programmers (and the occasional hardware engineer) start counting from 0. We see it so often it starts to seem normal, but it really

Re: [sqlite] how many tables can I have in one database

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mrobi...@cs.fiu.edu Sent: Wednesday, March 18, 2009 7:26 PM To: sqlite-users@sqlite.org Subject: [sqlite] how many tables can I have in one database Dear colleagues, I

Re: [sqlite] Getting SQLITE_BUSY within atransactionandhow tohandle it

> sorry, I don't know if it is allowed to end a transaction from a > different connection than the one that obtained the lock. > I would not recommend that, sounds a bit unhealthy from the > application point of view. I'm pretty sure that a transaction is > partly connection related since

[sqlite] how many tables can I have in one database

Dear colleagues, I understand that Sqlite can handle databases with 2 teragytes of data each. I would like to know how many tables can a dabase have. Thank you Michael ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite concat issue

On 19/03/2009 3:09 AM, Patnaik, Anjela wrote: > Hi John, > > Thanks for your response! Hi Anjela, I'm presuming that your off-list reply was accidental. > Now, the second column comes back as {} when I use sqlite TCL API. The TCL > llength is 1, instead of zero, probably due to the curly

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

Hello! On Wednesday 18 March 2009 21:51:10 Roger Binns wrote: > > Tcl, Python and other langs have different unicode implementations. The > > realizations are more simple than ICU library but millions of > > applications are using these. I'm will glad to see Tcl/Python/etc. > > unicode

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: > Tcl, Python and other langs have different unicode implementations. The > realizations are more simple than ICU library but millions of applications > are > using these. I'm will glad to see Tcl/Python/etc. unicode

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mail.sqlite wrote: > - Please, let us try to bring down the discussion to the intended solution - > a simple way to define and use a "user defined" collating for 8 bit ASCII > characters! You don't need anyone's permission - go ahead and implement

Re: [sqlite] Newbie: round function

Thaks for the workaround. But according to the documentation, round(3.1416) should return zero digits to the right of the decimal point. I checked with mysql: $ mysql Server version: 5.0.51a-3ubuntu5.4 (Ubuntu) mysql> select round(3.1416); +---+ | round(3.1416) | +---+ |

Re: [sqlite] Newbie: Further reading / books recommendations

OT, but related (AGAIN) I was adding these books to my amazon wishlist (aka Ron's list of books that he would forget about if he didn't put them on the list) and noticed that these titles are all available on the amazon kindle, which is delivered wirelessly via 3G network. I don't have a

Re: [sqlite] accessing php produced db from shell

On Wed, 18 Mar 2009 10:04:59 + (GMT) Swithun Crowe wrote: > Or, maybe $db should be $base, as in your example above? > yes that was one of my silly oversights :D there were several others, but they essentially boiled down to trying to follow the tutorial and

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

Ah yesss. My apologies. Please don't bill me. Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp Sent: Wednesday, March 18, 2009 1:32 PM

Re: [sqlite] Newbie: Further reading / books recommendations

On Wed, 18 Mar 2009, Dermot wrote: > I need get up to speed on a number of aspect of databases. I do need to > grasp SQL syntax but also I also could use a more ideas on schema design > and the process of modelling. For a well-written, in-depth book on SQL I highly recommend Rick van der

[sqlite] Newbie: Further reading / books recommendations

Hi, I need get up to speed on a number of aspect of databases. I do need to grasp SQL syntax but also I also could use a more ideas on schema design and the process of modelling. I have learning MySQL. Are there books that the list might suggest? Thanx, Dp.

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

On Mar 18, 2009, at 1:22 PM, Wilson, Ron P wrote: > OT, but tangentially related... > > Is there any reason why all the dates are a month off in cvstrac > remarks? See for example, the link below in dr.h's reply. That is, > I'm assuming that the anonymous remark on february 18 and dr.h's

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

OT, but tangentially related... Is there any reason why all the dates are a month off in cvstrac remarks? See for example, the link below in dr.h's reply. That is, I'm assuming that the anonymous remark on february 18 and dr.h's followup remark 2 hours later were really posted today, i.e.

Re: [sqlite] Speed of DROP INDEX

Re: [sqlite] Speed of DROP INDEX This may not be useful to your situation, but my (not terribly informed) *guess* is that the reason it takes so long is that the index pages are spread throughout your 8-millon row database. If by chance it's feasible to either: -- Not create the index until

Re: [sqlite] Newbie question

On Wed, 18 Mar 2009 16:04:51 +, Dermot wrote: >2009/3/18 Hoover, Jeffrey : >> >> Assumming  this is only one row in tbl2 where name='Joe'... >> >> this should work: >> SELECT * FROM tbl1 >> WHERE description='someval' >> AND foreign_key_id=(select id

Re: [sqlite] Newbie: round function

Well, it doesn't exactly say that an integer is returned. round() always returns a float: sqlite> select typeof(3); typeof(3) -- integer sqlite> select typeof(round(3)); typeof(round(3)) real sqlite> You can do this: sqlite> select typeof(cast(round(3.14) as

Re: [sqlite] Speed of DROP INDEX

The journal grew to about the same size as well, so it seemed it was getting use. I'll try journal_mode off and see if that has any effect. Thanks! _Nik On Mar 18, 2009, at 9:54 AM, Jim Wilcoxson wrote: > Hmm... Maybe it is creating the journal but not really using it with > synchronous=off.

Re: [sqlite] Speed of DROP INDEX

Hmm... Maybe it is creating the journal but not really using it with synchronous=off. You might try pragma journal_mode = off. That might keep it from creating a journal, but if you already tried using synchronous=off, my guess is journal_mode=off won't run any faster. Jim On 3/18/09, Nikolas

[sqlite] Newbie: round function

Hi! Is this expected? $ sqlite3 SQLite version 3.6.10 Enter ".help"for instructions Enter SQL statements terminated with a ";" sqlite> select round(3.1416); 3.0 CPU Time: user 0.00 sys 0.00 sqlite> I expected an integer 3 as documented: round(X) round(X,Y) Round off the number X

Re: [sqlite] Newbie question

according to this http://www.sqlite.org/lang_keywords.html both the single and double quotes should have worked. (the single quotes are preferred) On Wed, Mar 18, 2009 at 9:04 AM, Dermot wrote: > 2009/3/18 Hoover, Jeffrey : > > > > Assumming this is

Re: [sqlite] Newbie question

2009/3/18 Hoover, Jeffrey : > > Assumming  this is only one row in tbl2 where name='Joe'... > > this should work: > SELECT * FROM tbl1 > WHERE description='someval' > AND foreign_key_id=(select id from tbl2 where name='Joe'); > > this is better: > select tbl1.* from tbl1, tbl2 >

Re: [sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

On Wed, Mar 18, 2009 at 10:59 AM, D. Richard Hipp wrote: > > > I still have no idea what a ".pc" file is or what it is used for.  But > apparently it is very important to some compilers. > I gather it's the latest thing for unixy package management. I guess config scripts

Re: [sqlite] Newbie question

SQLite treats double-quoted strings as column and table identifiers. Use single-quotes for literals. SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); -Clark - Original Message From: Dermot To:

Re: [sqlite] Newbie question

I think its because sqlite (and most rdbms's) expect literal strings to be enclose in ' not ". I think sybase is an except, accepting either. In SQLite you use " to enclose table/column names that contain non0standard characters or where object id is case sensitive, such as select "grant#" from

Re: [sqlite] Newbie question

Assumming this is only one row in tbl2 where name='Joe'... this should work: SELECT * FROM tbl1 WHERE description='someval' AND foreign_key_id=(select id from tbl2 where name='Joe'); this is better: select tbl1.* from tbl1, tbl2 where tbl1.description='someval' AND tbl2.name='Joe' and

Re: [sqlite] Newbie question

I'm relatively newbie too, but just curious, is it anything to do with (select id from tbl2 where name="Joe") not being guarenteed to return a scaler? Ie only a single value? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf

Re: [sqlite] Newbie question

2009/3/18 Igor Tandetnik : > Dermot wrote: >> I want to do a select query a bit like this: >> >> SELECT * FROM tbl1 WHERE description="someval" AND >> foreign_key_id=(select id from tbl2 where name="Joe"); >> >> This gives me a syntax error and my

Re: [sqlite] Newbie question

Dermot wrote: > I want to do a select query a bit like this: > > SELECT * FROM tbl1 WHERE description="someval" AND > foreign_key_id=(select id from tbl2 where name="Joe"); > > This gives me a syntax error and my other efforts are not yielding > results. What's the text

[sqlite] Newbie question

Hi, I am very green with SQL entirely so I apologise in advance for what might be a simple query. I want to do a select query a bit like this: SELECT * FROM tbl1 WHERE description="someval" AND foreign_key_id=(select id from tbl2 where name="Joe"); This gives me a syntax error and my other

[sqlite] minor config bug sqlite-amalgamation-3.6.11.tar.gz

FYI, a minor bug in sqlite-amalgamation-3.6.11.tar.gz in file sqlite3.pc.in: Version: @RELEASE@ => Version: @VERSION@ Without this change it won't link with the Redland RDF library. -gregg ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Speed of DROP INDEX

I'm not sure what you mean...? _Nik On Mar 18, 2009, at 2:23 AM, pi song wrote: > Would not that be more efficient to do it in batch? Like an entry in > transaction means a block of deletions? If there is a crash during > dropping operation then the journal can be looked up and replayed. > > Pi

Re: [sqlite] Speed of DROP INDEX

I've actually been running it with synchronous=off. Unfortunately, it doesn't seem to run any faster and still creates a journal file. _Nik On Mar 17, 2009, at 6:05 PM, Jim Wilcoxson wrote: > Drop is executed within a transaction, which means that every record > you touch has to be backed up

Re: [sqlite] httpd server ???

I have an HTTP server wj\hich embeds Sqlite as well as a custom page generation language, and compiler and a remote procedure call interface for AJAX functionality and Javascript as an embedded scripting language. It runs on Unix/Linux and conditionally compiles for Windows. It uses a

Re: [sqlite] accessing php produced db from shell

Hello p i couldn't do php -i (bash: php: command not found), may be i have to p install the cli php? Yes, the cli php is optional. p $base = new PDO("sqlite:$dbname", 0666, $err) I don't think you need the other arguments for PDO. Something like: if ($base = new PDO("sqlite:$dbname)) { if

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

Hello! > From the German example, you can't even do that (name order is different > than dictionary order). I think we are agreed that the default SQLite > implementation gets ASCII right and makes no attempt to deal > specifically with non-ASCII locales. The ICU extension gets all the >

Re: [sqlite] Speed of DROP INDEX

Would not that be more efficient to do it in batch? Like an entry in transaction means a block of deletions? If there is a crash during dropping operation then the journal can be looked up and replayed. Pi Song On Wed, Mar 18, 2009 at 12:05 PM, Jim Wilcoxson wrote: > Drop is

Re: [sqlite] httpd server ???

On Mar 17, 2009, at 10:05 PM, dcharno wrote: >> The SQLite website is implemented using a profoundly simple HTTP >> server that runs off of inetd. The complete source code is contained >> in a single file of C code that is available on-line at: >> >>

Re: [sqlite] accessing php produced db from shell

On Wed, 18 Mar 2009 08:17:56 + (GMT) Swithun Crowe wrote: > php -i | grep -i sqlite will tell you the versions that are being > used on your machine. > ok thanks! i couldn't do php -i (bash: php: command not found), may be i have to install the cli php?

Re: [sqlite] accessing php produced db from shell

Hello p i'm using sqlite3, but with php5 i had to install php5-sqlite. is the p sqlite i'm using with php5 on debian version 2? could this be the p problem? or is it something else? PHP uses SQLite2 in its normal extension. But the PDO extension uses SQLite3, so you can use that extension

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

- Please, let us try to bring down the discussion to the intended solution - a simple way to define and use a "user defined" collating for 8 bit ASCII characters! As said before, the proposal doesn't rely on locales. If a user needs a german collating sequence with sort order for phone-book,

Re: [sqlite] Getting SQLITE_BUSY within a transactionandhow tohandle it

sorry, I don't know if it is allowed to end a transaction from a different connection than the one that obtained the lock. I would not recommend that, sounds a bit unhealthy from the application point of view. I'm pretty sure that a transaction is partly connection related since sqlite will still

Re: [sqlite] Getting SQLITE_BUSY within a transactionandhow tohandle it

> I'm wondering on which statement your first thread returns busy as > well. both are waiting to get a transaction acquired? Or is the first > thread waiting for the commit? Both are waiting to start a transaction. > Maybe you can post some code or a little extraction of what you are > doing?

Re: [sqlite] Getting SQLITE_BUSY within a transaction andhow tohandle it

Dennis, I'm wondering on which statement your first thread returns busy as well. both are waiting to get a transaction acquired? Or is the first thread waiting for the commit? Maybe you can post some code or a little extraction of what you are doing? When I started with sqlite and threads I can

[sqlite] accessing php produced db from shell

i'm working through one of the php-sqlite tutorials: http://www.scriptol.com/sql/sqlite-table.php it works fine, but when i try going in with sqlite3 from the shell and try to do anything, i get SQL error: file is encrypted or is not a database i'm using sqlite3, but with php5 i had to install