Re: [sqlite] opinion on possible bad effects from detachingdatabase whilst statements prepared on it.

2011-10-17 Thread O'Neill, Owen
, October 17, 2011 1:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] opinion on possible bad effects from detachingdatabase whilst statements prepared on it. On 17 Oct 2011, at 1:22pm, O'Neill, Owen wrote: > the application is distributed, so while it is running it &

[sqlite] opinion on possible bad effects from detaching database whilst statements prepared on it.

2011-10-17 Thread O'Neill, Owen
Hi Everyone, I'm trying to hunt down an awkard bug in a multi-threaded application so I was interested in people's opinions on whether the following is a likely problem scenario. - the application is distributed, so while it is running it re-syncronises it's state with the server by

Re: [sqlite] How to make this calculation in VIEW

2010-11-18 Thread O'Neill, Owen
Hi Jeff, I haven't actually tried it, but just by inspection I would guess that a view can't refer to another column within itself, so there are 2 options. Create a second view on top of the first view. (I've not tested this - note how the view name is aliased to just 'Patterns' because I'm

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

2010-09-17 Thread O'Neill, Owen
Hi Keith, Depending on your definition of 'efficient' then this might do what you want. (untested - might need to add or remove bracket since sqlite does seem fussy about having what it considers to be extra ones) INSERT OR REPLACE INTO "file_downloads" SELECT COALESCE( (SELECT

Re: [sqlite] In Memory Usage

2010-01-04 Thread O'Neill, Owen
If you were wanting to copy all of the in-memory database tables to a file, rather than just one, then the backup api might be another way to go. http://www.sqlite.org/backup.html (I haven't used it myself but the documentation lists copying from memory database instances to file databases as

Re: [sqlite] begin transaction

2009-11-23 Thread O'Neill, Owen
Hi Tom, Whilst not knowing much about the process, I have a recollection about something in the documentation that said if sqlite thought that there was a potential for deadlock the busy handler was never even called. Could that explain this ? Cheers Owen -Original Message- From:

Re: [sqlite] Fine-grainy error report needed

2009-11-16 Thread O'Neill, Owen
What I usually do is work my way through binding each value to null one at a time until it works, when it does I know I've found the column that was previously the offending one. - and when I say null I should really say "some known value that is within the constraints range on the column".

[sqlite] Feature suggestion - default busy handler

2009-11-11 Thread O'Neill, Owen
Hi Everyone, On the basis of the number of times it comes up on the mailing list, and the grounds that most 'casual' users will want Sqlite to work as well as possible 'out the box' - I'd like to suggest the that the default busy handler is changed from being none to being the 'standard' busy

Re: [sqlite] Locking bug?

2009-11-04 Thread O'Neill, Owen
I could be wrong, but even if the statement failed, and you have called finalize, I vaguely recollect reading somewhere that (if you called BEGIN) then you need to rollback. Can't find the manual / wiki link to back that up at the moment. (oh and installing a busy handler should help things -

Re: [sqlite] SQLite on PocketBook

2009-11-04 Thread O'Neill, Owen
Correct, ARM's emulate hardware floating point using software. And it's really slow too since it forces a context switch as the processor spots it's been given a FP operation to do and has to jump into the fp emulation. If you are lucky enough to have control over it & depending on the level of

Re: [sqlite] Storing 3-byte ints efficiently

2009-11-04 Thread O'Neill, Owen
I'm not the expert on this, but there is an overhead to consider in terms of the record header. http://www.sqlite.org/fileformat.html one question - the table storing the integers - was it created with one of the data values as the primary key ? If not then you'll have a rowid in each row too.

Re: [sqlite] in-memory database concept

2009-11-04 Thread O'Neill, Owen
If that's really what you want to do then I think something along the lines of keeping the DB in the C++ program and then using a named pipe or socket to do the inter-processes communication between the java and C++ programs is the way to go. - or some CORBA/SOAP like wrapper to do the remote

Re: [sqlite] has used anyone sqlite for production web sites ??

2009-10-29 Thread O'Neill, Owen
Hi Sebastian, The reading should be ok, but you'll have to be *really* careful how you handle that many concurrent writers. See the recent thread titled "shared cache mode and 'LOCKED'" which has a discussion of topics with the same problem. Cheers. Owen -Original Message- From:

Re: [sqlite] shared cache mode and 'LOCKED'

2009-10-29 Thread O'Neill, Owen
to unblock. Be >> aware, you should also close any open cursors before yielding, >> because open cursors will prevent write locks and you'll waste time >> yielding for nothing. >> >> John hope this helps (and isn't incorrect). thanks tom -Original Message- From:

Re: [sqlite] Late data typing. Am I missing something?

2009-10-28 Thread O'Neill, Owen
You can get close if you put some check constraints on the columns. I must agree with other posters that the lack of an exposed timestamp type does feel like something of a gap. Owen -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On

[sqlite] shared cache mode and 'LOCKED'

2009-10-28 Thread O'Neill, Owen
Hi Everyone, Does anyone know if this page is still up to date with respect to when you get "SQLITE_LOCKED" when operating in shared cache mode ? http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked (I'm trying to solve a two writers problem and am trying to understand the best way to solve

[sqlite] undocumented "ignore_check_constraints" pragma

2009-10-27 Thread O'Neill, Owen
Hi Everyone, I was just about to start investigating what was involved in implementing a pragma that did just this - only to find it exists already ! (ver 3.6.15) (and it does function as it says on the tin) (look at the flagPragma function "ignore_check_constraints", SQLITE_IgnoreChecks ) Any

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

2009-10-23 Thread O'Neill, Owen
: [sqlite] [Windows] Good GUI alternative to sqlite.exe? On Fri, 23 Oct 2009 11:13:05 +0100, "O'Neill, Owen" <oone...@averyberkel.com> wrote: >http://sqlitebrowser.sourceforge.net/screenshots.html Thanks for the pointer. "Version 1.2 released - 04/05/2005" "SQLite Dat

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

2009-10-23 Thread O'Neill, Owen
http://sqlitebrowser.sourceforge.net/screenshots.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault Sent: Friday, October 23, 2009 10:45 AM To: sqlite-users@sqlite.org Subject: [sqlite] [Windows] Good GUI

Re: [sqlite] Slow SELECTs in application

2009-10-21 Thread O'Neill, Owen
Really does depend on the query (sql) you are running. To investigate start by looking at the explain plan http://www.sqlite.org/lang_explain.html http://www.razorsql.com/docs/sqlite_explain_plan.html Classic "slow query" problems are table scans - where the engine has to scan the entire

Re: [sqlite] Replacing a table

2009-10-15 Thread O'Neill, Owen
No problem, Update "table" set "columnname"='newvalue'; Time to learn some sql basics and discover the 'where' clause :-) http://www.sqlite.org/lang_update.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita

Re: [sqlite] Replacing a table

2009-10-15 Thread O'Neill, Owen
Run the sql 'delete from "tablename";' if the table definition is different (different column names or data types ) then you will need to drop the table and create a new one. 'drop table "tablename";' http://www.sqlite.org/lang_createtable.html if the table is huge you might get different

Re: [sqlite] how to represent a tree in SQL

2009-10-14 Thread O'Neill, Owen
Personally I'd put the nodes and vertices/paths/edge between them in separate tables. (unless you're happy with the 1 vertex between nodes constraint) It does really get horrible if you're planning to use SQL to find relations with a distance apart of > 1 vertex. I have an oh so vague

[sqlite] best device characteristic settings when using jffs2 file system ?

2009-10-13 Thread O'Neill, Owen
Hi Everyone, I'm using sqlite on a JFFS2 file system (writing to NAND flash) so I'm wondering what the best file system characteristics to report via the xSectorSize and xDeviceCharacteristics methods are ? http://www.sqlite.org/c3ref/io_methods.html (JFFS2 is in summary a rotating log