Re: [sqlite] Timezone/DST question

On 8 Mar 2011, at 13:47, Enrico Thierbach wrote: > I would like to state in which place a time is meant to be meaningful, and > have sqlite come up > with the proper timezone and DST correction depending on place and time. > Sorry for not making that clear in the beginning. > > Is there a way

Re: [sqlite] Storing data with SQLite

On 9 Mar 2011, at 13:05, Igor Tandetnik wrote: > liran ritkop wrote: >> Ok, I'll do it that way, it's a smart idea. >> The question is, if i use files and not blob data, when i want to update the >> data (this time the file, and not the blob field) it can lead to some kind >>

Re: [sqlite] memory usage after VACUUM

On 9 Mar 2011, at 15:23, Nick Hodapp wrote: > I'm using sqlite in an iOS app, via the popular FMDB wrapper. > > My profiling tool is showing me that the app is using 2.5 MB of memory > before a VACUUM, and nearly 6MB after. The tool shows that the extra memory > was allocated by

Re: [sqlite] duplicate a prepared statement?

On 9 Mar 2011, at 21:29, Nick Hodapp wrote: > Is it possible to duplicate a prepared statement? Yes, if the original statement used sqlite3_prepare_v2: DBrc = sqlite3_prepare_v2(db,sqlite3_sql(original),-1,,NULL); > I have a need to run the same query multiple times with different >

Re: [sqlite] How to get Unicode Value of any Character in string/text

On 22 Mar 2011, at 07:25, ashish yadav wrote: > Hi , > > To be more specific that : > 1. Database is UTF-8. > 2. Programming Language is C or C++. > > Database may contain Chines / Japanese character of strings. > > So , if there is any way /APIs to know Unicode of Character ? iconv() Best

Re: [sqlite] Create table if not exists and insert seed data

On 21 Mar 2011, at 22:04, Erich93063 wrote: > I am trying to create a SQLite database if it doesn't exist, which I > know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I > need to initially populate the database with seed data if it doesn't > exist. If I use CREATE TABLE IF NOT

[sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

Hi all, I'm noticing a new failure with SQLite 3.7.3 as compared to the previous version I was using, 3.6.23.1. Error message: -- Assertion failed: (memIsValid([i])), function sqlite3VdbeExec, file .../sqlite3.c, line 64507. Information from the time of error:

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

On 25 Nov 2010, at 14:06, Dan Kennedy wrote: > On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: >> Hi all, >> >> I'm noticing a new failure with SQLite 3.7.3 as compared to the previous >> version I was using, 3.6.23.1. > > Are you able to share the d

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

On 26 Nov 2010, at 10:28, Philip Graham Willoughby wrote: > On 25 Nov 2010, at 14:06, Dan Kennedy wrote: > >> On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: >>> Hi all, >>> >>> I'm noticing a new failure with SQLite 3.7.3 as compared to the prev

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

On 2 Dec 2010, at 20:43, Sylvain Pointeau wrote: > Hi, > > I am on macosx sqlite 3.7.3 > > $ /usr/local/bin/sqlite3 test.db3 > SQLite version 3.7.3 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .read ./testSchema.sql > sqlite> .read ./TestValues.sql >

Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)

On 4 Dec 2010, at 10:42, Dan Kennedy wrote: > On 12/04/2010 12:54 AM, Philip Graham Willoughby wrote: >> On 2 Dec 2010, at 20:43, Sylvain Pointeau wrote: >> >>> Hi, >>> >>> I am on macosx sqlite 3.7.3 >>> >>> $ /usr/local/bi

Re: [sqlite] need help on escaping the ']' character in the identifier

On 7 Dec 2010, at 08:49, 곽현미 wrote: > Anyone please give me an advise, > > when i try the query below, > > CREATE TABLE "Test]" (no INTEGER), > > sqlite gives me the 'unrecognized token: "]" ' result. > > Is there a way to escape the ']' character in the identifier? Use the right quotes,

Re: [sqlite] SQLite server

On 20 Dec 2010, at 22:18, Simon Slavin wrote: > > On 20 Dec 2010, at 9:02pm, Richard Hipp wrote: > >> (2) Create your own custom mini-SQL-server using SQLite and your own >> protocol for your applications to talk to that min-server over the network. > > In your opinion (or in the opinion of

Re: [sqlite] SQLite server

On 21 Dec 2010, at 12:16, Max Vlasov wrote: > Simon, I read both your suggestion and the Richard's good explanation about > network problems. I think that the idea still deserves to live in some form > :). I sometimes access sqlite db on a remote computer accessed with sqlite > shell executed in

Re: [sqlite] SQLite server

On 21 Dec 2010, at 14:19, Simon Slavin wrote: > > On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote: > >> Implementing an SQLite-based server does not obviously enable this in and of >> itself. If you could open a database on a remote machine using its f

Re: [sqlite] SQLite server

On 22 Dec 2010, at 13:12, Simon Slavin wrote: > > You do mention something worthwhile: if you had a server/client version of > SQLite you could get rid of all the code to do with file sharing and locking. > That's quite a lot of code, if you include all the PRAGMAs and related > programming

Re: [sqlite] Propose minor incompatible API change

On 11 Jan 2011, at 08:20, Max Vlasov wrote: > On Tue, Jan 11, 2011 at 3:54 AM, Richard Hipp wrote: > >> >> This is, technically, a compatibility break. On the other hand, there >> appear to be vast numbers of smartphone applications that currently depend >> on undefined

Re: [sqlite] Propose minor incompatible API change

On 11 Jan 2011, at 12:15, Richard Hipp wrote: > That new OS release includes the latest shared library for > SQLite. You didn't put it there, and the consequences of putting it there are not your responsibility. Nor are the consequences of someone else's app breaking because they didn't read

Re: [sqlite] Propose minor incompatible API change

On 11 Jan 2011, at 13:36, Andy Gibbs wrote: > On Tuesday, January 11, 2011 1:35 PM, Jean-Denis Muys wrote: > >> Don't encumber SQLite with workarounds and special cases >> to cater to bugs in client software. > > Isn't an accurate synopsis of the problem this: that Sqlite has *already* >

Re: [sqlite] Propose minor incompatible API change

Please don't top-post and include everything that went before. On 11 Jan 2011, at 17:10, Scott A Mintz wrote: > There's the issue of "this is what I meant" vs. "this is what I did." When > you have a couple hundred customer's, changing the code is painful but > doable. When you have a couple

Re: [sqlite] Propose minor incompatible API change

On 12 Jan 2011, at 12:49, Andy Gibbs wrote: > On Wednesday, January 12, 2011 10:08 AM, Philip Graham Willoughby wrote: > >> unfortunately 3.7.2 shipped in Ubuntu Maverick and >> 3.6.23.1 shipped in a maintenance update for Fedora >> Core 14. So lots of people alre

Re: [sqlite] huge performance decrease after deleting/creating a table !

On 12 Jan 2011, at 15:12, Vander Clock Stephane wrote: > before it's return in 100 ms now 30 secondes :( > > What i do wrong and how to correct it ? Issue the SQL command: ANALYZE This will help the query planner understand which indexes are best to use; I have seen it dramatically improve

Re: [sqlite] How to track a record if the aliased id is changed

On 13 Jan 2011, at 17:27, Nicolas Williams wrote: > On Thu, Jan 13, 2011 at 10:59:29AM +, Simon Slavin wrote: >> His problem is that he doesn't know which rows are aliased to rowid, >> so he can't provide a list of column names. So the following might be >> closer >> >> CREATE TEMP TRIGGER

Re: [sqlite] How to track a record if the aliased id is changed

On 14 Jan 2011, at 10:45, Max Vlasov wrote: > Phil, it's an interesting solution. I will probably use it, but the current > problem is that I have to resolve object context from non-oop function. It's > possible (maybe even by hard-coding pointer as one of parameters in the > trigger statement),

Re: [sqlite] How to track a record if the aliased id is changed

On 14 Jan 2011, at 11:06, Max Vlasov wrote: > I understand this, but in my case the tables are objects also. So when I > create a temporary trigger I should pass the table object context. Dead easy: make the object context required to find the table a parameter to the function, and put the

Re: [sqlite] How to track a record if the aliased id is changed

On 14 Jan 2011, at 16:53, Nicolas Williams wrote: > On Fri, Jan 14, 2011 at 10:20:22AM +0000, Philip Graham Willoughby wrote: >> You can do slightly better using a SELECT instead of an UPDATE in the >> trigger body. Install a custom function called rowid_changed() and >&

Re: [sqlite] FTS3 bug

On 15 Jan 2011, at 01:18, Jonas Bengtsson wrote: > 2) Any suggestions as to how to sanitize the user's input to avoid > this problem? This is just the one I've found, but I'm assuming there > are more. Don't try and sanitize the user's input - you will never be able to prove you have accounted

Re: [sqlite] Query or table

On 18 Jan 2011, at 13:51, Ian Hardingham wrote: > Hey guys. I am currently doing the following to find out the "record" > between two players in my game: > > SELECT count(*) TotalGames, sum(score > 0) GamesWonByPlayer1, sum(score > < 0) GamesWonByPlayer2, sum(score = 0) Draws FROM

Re: [sqlite] Query or table

Ian, On 18 Jan 2011, at 16:40, Ian Hardingham wrote: > In general, my server is too slow. It has to run many operations a > second, and many DB operations, so the exact definition of "too slow" is > a little arbitrary. Nothing in that paragraph implies that the DB speed is a problem, or that

Re: [sqlite] WAL on a separate filesystem?

On 19 Jan 2011, at 11:53, Russell Leighton wrote: > Perhaps that could be the default and a pragma could be used to > override this default and specify the directory holding the WAL. > > This could be useful in cases that users want to put the WAL > someplace else (like an SSD). I think

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

>SELECT length(CAST ("Data" AS BLOB)) FROM "message_revision"; What about: UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB); As a one-time command to correct the table. There is also the option of using triggers to ensure future "Data" values are kept as BLOB if the INSERT code

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

Hi Sam, On 11 Feb 2011, at 05:29, Sam Carleton wrote: > I am sure it is bad form, but attached is one of the 3.6.23 DB, it is only > 12K. The mailing list software strips attachments; can you share it on dropbox (free account here: http://www.dropbox.com/referrals/NTEwMzQxNDc1OQ ) or some

Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress

On 18 Feb 2011, at 13:12, Black, Michael (IS) wrote: > I'm of the opinion that all such warnings should be permanently fixed. Such > warnings do point to potential problems. > And not by disabling the warning but by fixing the code (explicit casts for > example). It's been a while since I

Re: [sqlite] Compiler warnings in R-Tree code under Visual StudioExpress

On 18 Feb 2011, at 14:25, Jean-Denis Muys wrote: > On 18 févr. 2011, at 15:12, Philip Graham Willoughby wrote: > >> Adding casts to get rid of warnings is usually the wrong answer in my >> experience. Certainly you should never cast the return value of a function >>

Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs

On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: > I am not starting from scratch doing my own encryption; there are enough > open source libraries publicly available which are good enough for my > purposes. And all of them offer approximately no security if you use them incorrectly. For this

Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs

On 20 Feb 2011, at 10:32, Robert Hairgrove wrote: > On Sun, 2011-02-20 at 09:35 +0000, Philip Graham Willoughby wrote: >> On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: >> >>> I am not starting from scratch doing my own encryption; there are enough >>&g

Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs

On 20 Feb 2011, at 10:52, Robert Hairgrove wrote: > On Sun, 2011-02-20 at 09:35 +0000, Philip Graham Willoughby wrote: >> For this task I would use AES-256 in counter mode with an appropriate nonce >> (the counter is trivially derived from the file offset of the block to be

Re: [sqlite] owner/permissions on wal/shm and journal files

On 20 Feb 2011, at 16:18, thilo wrote: > My DB is owned by a different user (www) and I as root insert some data. ... > Are there any suggestions on how to make this workable? Don't do that. Unless you can guarantee that no malicious user has been able to alter your database it isn't safe to

Re: [sqlite] owner/permissions on wal/shm and journal files

On 21 Feb 2011, at 09:36, Simon Slavin wrote: > On 21 Feb 2011, at 9:35am, Philip Graham Willoughby wrote: > >> On 20 Feb 2011, at 16:18, thilo wrote: >> >>> My DB is owned by a different user (www) and I as root insert some data. >> ... >>>

Re: [sqlite] Asymmetric keys encryption

On 22 Feb 2011, at 15:41, Max Vlasov wrote: > The obvious solution is public-key cryptography. The question is about > different ways how it could be implemented with sqlite. The requirement for > this system is that it should operate in two modes: > - insert-only when no reading operation is

Re: [sqlite] Asymmetric keys encryption

On 22 Feb 2011, at 22:03, H. Phil Duby wrote: > On Tue, Feb 22, 2011 at 9:07 AM, Philip Graham Willoughby > <phil.willoug...@strawberrycat.com> wrote: >> >> On 22 Feb 2011, at 15:41, Max Vlasov wrote: >>> The obvious solution is public-key cryptography. The que

Re: [sqlite] complex query

On 24 Feb 2011, at 07:49, Aric Bills wrote: > people (a table of individuals who are somehow interrelated) > fields: uid (among others) > > partners (links individuals [i.e., individual spouses] to couple IDs) > fields: coupleid, personid > > children (links children to their parents' couple

Re: [sqlite] complex query

On 24 Feb 2011, at 13:40, Igor Tandetnik wrote: > Josh Marell wrote: >> I would say that couple should be a 3 column table, with coupleID, >> partner1ID, partner2ID. It looks like right now, you have just coupleID and >> partnerID, that doubles the number of rows you have.

Re: [sqlite] complex query

On 24 Feb 2011, at 14:07, Simon Slavin wrote: > > On 24 Feb 2011, at 1:06pm, Josh Marell wrote: > >> I would say that couple should be a 3 column table, with coupleID, >> partner1ID, partner2ID. It looks like right now, you have just coupleID and >> partnerID, that doubles the number of rows

Re: [sqlite] complex query

On 24 Feb 2011, at 15:20, Simon Slavin wrote: > On 24 Feb 2011, at 3:18pm, Philip Graham Willoughby wrote: > >> On 24 Feb 2011, at 14:07, Simon Slavin wrote: >> >>> A more flexible way to lay out genealogy databases is as follows: >>> >>> Person:

Re: [sqlite] adding fdopen to VFS?

On 28 Feb 2011, at 20:38, Roger Binns wrote: >> Why is it not possible to create a custom VFS for the intended purpose >> and register it for the connections that need it? > > Because that custom VFS would be an almost duplicate of the existing VFS but > with a few key places changed. > > If

Re: [sqlite] Query help

On 1 Mar 2011, at 22:01, Jeff Archer wrote: > Hi all, and thanks in advance for you help. > > And this select which I would like to modify to only return lowest X,Y value > for each ImageID. > > select Defects.DefectID > , Defects.ImageID > , Defects.AnalysisID > , Defects.X > , Defects.Y > ,

[sqlite] (How) can I test for an open transaction?

Hi all, I use SQLite in an iPhone application, and for my own convenience I have wrapped it up in an object-oriented wrapper of my own devising. I would like to close the database connection if it is safe to do so when my app is suspended, so that the -wal file can be erased saving the user

Re: [sqlite] VB.net error on adding SQLite3.dll to Project References

On 3 Mar 2011, at 09:23, J Trahair wrote: > Hi everyone. > > VB.Net 2005, I am trying to add sqlite3.dll to the Project References. When I > try to Add the dll I get an error message: > > 'A reference to {path to sqlite3.dll} could not be added. Please make sure > that the file is