[sqlite] Select statement with ORDER BY specified by column value
Hi all, Is there a way to specify an ORDER BY clause by column value? I have a table declared as: CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, prop_tag TEXT DEFAULT '*', UNIQUE(obj_id, prop_key, prop_tag)) and a sample query: SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND (prop_tag='ios' OR prop_tag='*') ORDER BY (prop_tag='ios') LIMIT 1; I would like to prioritise results based on the fact that the prop_tag column is 'ios'. Thanks. -- Marco Bambini https://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)
Dear SQLite Developers, I just noticed the following bug in SQLite version 3.28.0: create table t AS values (1), (2); .print "select with correct output ..." select * from ( select * from t limit 1 ) union all select * from t ; .print "same select leads to incorrect result when used inside view ..." create view v as select * from ( select * from t limit 1 ) union all select * from t ; select * from v; * How to reproduce: Run the attached SQL code from a shell with: sqlite3 < sqlite-bug.sql * Expected result: Both queries should yield the following output: 1 1 2 * What did go wrong: The second query yields to the output: 1 It seems that the limit clause in the compound select when used inside a view is used for the overall result set and not for the subquery. In the hope that this might help others. Thank you very much for your hard work! Cheers, Marco ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A parser for sqlite databases to implement full ALTER TABLE support
More info at: https://github.com/marcobambini/sqlite-createtable-parser -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what is server-process-edition?
So it has nothing to do with which table/row the transaction is modifying? In your example connection 2 always returns with an error on COMMIT? Seems like the improvement is only on when the error occurs and not on concurrency or am I missing something? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs > On 26 Mar 2018, at 09:41, Simon Slavin <slav...@bigfraud.org> wrote: > > On 26 Mar 2018, at 8:09am, Marco Bambini <ma...@sqlabs.net> wrote: > >> Is there a better formal description about the "transactions may not >> overlap" sentence? >> Is there any example about overlapping transactions? > > Overlapping transactions occur when a second connection does a BEGIN before > the first connection does its COMMIT. It's difficult to present it well > without colours or fixed-width fonts, but try this: > > connection 1: BEGIN CONCURRENT > connection 1: -- various database reads and updates > connection 2: BEGIN CONCURRENT > connection 2: -- various database reads and updates > connection 1: COMMIT > connection 2: COMMIT > > (Alternatively the COMMIT lines could occur in the other order. Either way, > the transactions are overlapping.) > > Using normal BEGIN variants, the thread executing the third or fourth lines > would be the one which noticed the problem. The thread would be delayed and > may eventually return SQLITE_. This new variant BEGIN CONCURRENT ensures that > the thread executing the second BEGIN is not delayed, and that instead the > error is returned when that connection executes the COMMIT. > > Simon. > ___ > 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] what is server-process-edition?
Is there a better formal description about the "transactions may not overlap" sentence? Is there any example about overlapping transactions? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs >> The begin-concurrent branch >> (https://sqlite.org/src/timeline?r=begin-concurrent=all) allows you >> to say: >> >>BEGIN CONCURRENT; >>-- various database reads and updates >>COMMIT; >> >> And to do that simultaneously in two or more database connections, and >> have them all work. Except, the concurrent transactions may not >> overlap. That is to say, content written by one may not be read or >> written by another. If the transactions do overlap, the second one to >> try to COMMIT will get an SQLITE_BUSY_SNAPSHOT error and will be >> forced to abandon its transaction and start over. >> >> The begin-concurrent branch is in production use in high-stress >> environments. We have not merged that branch to trunk (yet) because >> it currently imposes extra overhead on all applications, even >> applications that do not use BEGIN CONCURRENT. >> >> Another alternative is the newer server-process-edition branch >> (https://sqlite.org/src/timeline?n=all=server-process-edition) which >> you can read about here: >> https://sqlite.org/src/artifact/0c6bc6f55191b690 >> >> -- >> 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
[sqlite] sqlite server-process-edition and begin-concurrent
I am looking for a way to increase concurrency from within cubeSQL. I am highly interested in a couple of sqlite branches: - begin-concurrent seems interesting and updated - sqlite server-process-edition branch has been updated on August 2017 Based on documentation seems like that sqlite server-process-edition would be a better option for my case but is the branch still maintained and updated? Are the branches mutually exclusive or can be used both in the same process? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lazy virtual table creation
Philippe, with a bit of work you can use the authorize api in order to know when an access to a non existing table is performed. https://sqlite.org/c3ref/set_authorizer.html Hope this helps. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs > On 17 Mar 2018, at 15:53, Philippe Riand <p...@darwino.com> wrote: > > We are using virtual tables to provide an SQL access to our data set and this > works very well. But we have potentially "a lot” of virtual tables, with > some even yet unknown when we start the DB. We’d like to create them lazily, > on first access. > Is there a hook we can use so when an SQL statement refers to a non existing > table it asks a callback for a VT definition? It is fine if these dynamic > table requires a specific prefix similar to the "temp” one. > > Regards, > > Phil. > > > ___ > 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] Dynamically load all the sqlite functions
Hi all, I need to dynamically load all the functions inside a sqlite shared library. What I am trying to do is to execute the same code just swapping a pointer from libsqlite1 and libsqlite2 (two different version of the sqlite library). Normally I should dlopen the library, load all functions with dlsym and once done call dlclose. I noticed that all function prototypes are already defined inside sqlite3ext.h but I wondering if there is a function I could use to all the symbols from the lib without manually process all the function names. Any clue? Thanks. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Testing sqlite db to see if exists & ready
Seems like a good reason to introduce a way to query the existence of a particular pragma command, something like: PRAGMA exists('user_version'); or PRAGMA exists='user_version'; -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 6 Apr 2017, at 13:42, Richard Hipp <d...@sqlite.org> wrote: > > On Thu, 06 Apr 2017 12:11 +0100, Tim Streater <t...@clothears.org.uk> wrote: >> >> I keep reading that the continued >> existence of any particular PRAGMA is completely un-guaranteed. >> > > We say that. But in practice, if we were to remove a pragma it would > break thousands, perhaps millions, of applications, so they are all > there for the long haul. Especially "PRAGMA user_version" you can > count on being there. > -- > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A CTE to count the records (rows) for each table
Excerpts from Richard Hipp's message of 2017-03-13 14:47:49 -0400: > On 3/13/17, Marco Silva <marco.prado...@gmail.com> wrote: > > Hi, > > > > Does anyone knows a Common Table Expression (CTE) to be used with the > > sqlite_master table so we can count for each table how many rows it > > has. > > That is not possible. Each table (potentially) has a different > structure, and so table names cannot be variables in a query - they > must be specified when the SQL is parsed. > > But you could do this with an extension such as > https://www.sqlite.org/src/artifact/f971962e92ebb8b0 that implements > an SQL function that submits new SQL text to the SQLite parser. For > example: > > SELECT name, eval(printf('SELECT count(*) FROM "%w"',name)) > FROM sqlite_master >WHERE type='table' AND coalesce(rootpage,0)>0; > Worked pretty well, with the extension you mentioned. Thanks, Dr. Hipp -- Marco Arthur @ (M)arco Creatives ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A CTE to count the records (rows) for each table
Hi, Does anyone knows a Common Table Expression (CTE) to be used with the sqlite_master table so we can count for each table how many rows it has. -- Marco Arthur @ (M)arco Creatives ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Responsive" website revamp at www.sqlite.org
You could just filter by screen size instead of user agent. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 06 Sep 2016, at 14:26, Richard Hipp <d...@sqlite.org> wrote: > > On 9/6/16, Nelson, Erik - 2 <erik.l.nel...@bankofamerica.com> wrote: >> >> What percentage of sqlite.org hits are mobile browsers? > > How do I tell? > > The website saw 2748 distinct UserAgent strings within just the past > 24 hours (a holiday in the USA, FWIW). How do I tell which of those > are bots, mobile devices, and/or desktops? > > -- > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count connections
Hi, How do we know how many connections a database has ? Is it possible to query it using SQL ? Is there a simple command from the sqlite3 shell client ? -- Marco Arthur @ (M)arco Creatives
[sqlite] CREATE TABLE parser
> On 02 Mar 2016, at 13:32, Luca Ferrari wrote: > > On Wed, Mar 2, 2016 at 1:10 PM, Marco Bambini wrote: >> I developed the parser myself. > > Great job but...what is the aim? > Why one should use this instead of, let's say, Perl SQL::Parser > <http://search.cpan.org/~rehsack/SQL-Statement-1.407/lib/SQL/Parser.pm>? Probably because it can be easily embedded in C and is it also way faster and a lot requires less memory. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > > Luca > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE TABLE parser
I developed the parser myself. Here you go a link to the GitHub page: https://github.com/marcobambini/sqlite-createtable-parser -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 02 Mar 2016, at 11:50, Clemens Ladisch wrote: > > Marco Bambini wrote: >> I developed a CREATE TABLE parser in C > > Did you duplicate the SQLite SQL parser? > Or is your parser supposed to have differences? > >> I would like to stress test it before releasing it as open source on GitHub. > > Without the source, finding errors would not be as easy. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CREATE TABLE parser
Hi all, I developed a CREATE TABLE parser in C that is able to extract every details about an sqlite table (table and columns constraints, foreign keys, flags and so on). So far it seems to work pretty well but I would like to stress test it before releasing it as open source on GitHub. Anyone can send me or help me find out some CREATE TABLE sql statements to add to my internal tests? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] non-integer rowid/document id
Excerpts from jeremy's message of 2015-12-14 11:09:48 -0700: > Hi all, > > I'm working with FTS5 and I'd like to guarantee that a particular document is > indexed only once. I have what I consider to be a document id, but it is not > an > integer value its a hexadecimal string, think GUID/md5/sha1. Since the fts5 > rowid column is an integer, it appears I'll need to create a mapping from my > document id to an fts5 rowid. Don't need a mapping use your key and ensure your key is unique. > > I'm thinking the best method for me to resolve this would to just use an > external content table with triggers to update FTS5 table. Pretty much exactly > like https://sqlite.org/fts5.html#section_4_4_2 and use triggers to keep the > FTS5 table in sync with the external content table. Using that solution (the best I think) just combine: -- The external table CREATE TABLE tbl (key TEXT UNIQUE, content, id INTEGER PRIMARY KEY); -- The virtual table CREATE VIRTUAL TABLE fts USING fts5(key, content, content=tbl, content_rowid=id); > > If I'm going to have to use an external table to create a rowid for the > fts5 table, I might as well use use the external content table. > > Any additional thoughts on this from the more knowledgeable? > > enjoy, > > -jeremy > > --? > > Jeremy Hinegarnder > Copious Free Time, LLC -- Marco Arthur @ (M)arco Creatives
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Solved. At the end I have added the parameter -DSQLITE_THREADSAFE=0 to exclude the multithread code from the library Thank you. Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, October 22, 2015 8:51 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 Marco Turco wrote: > could you please give me the full Bcc32 string you are using ? That would not be useful for you; I'm using an incompatible calling convention. Just replace "-tW" with "-tWM". Regards, Clemens ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Hi, could you please give me the full Bcc32 string you are using ? thank you Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Thursday, October 22, 2015 4:22 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 Marco Turco wrote: > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW > -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF > -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 > -Ik:\BCC70\Include;k:\XHARBOUR\Include > -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c My Borland C++ 5.5.1 (the old, free version) needs -tWM for a multithreaded program; you're using -tW. Regards, Clemens ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
The problem is when I link the generated library. I receive the following error related to the first two warnings so I'm unable to generate the executable file. Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero Technologies, Inc. K:\RENTMNG\Obj\mod306f.c: k:\BCC70\Bin\iLink32.Exe -Gn -aa -Tpe -s @K:\RENTMNG\RENTMNG.bcl Turbo Incremental Link 6.70 Copyright (c) 1997-2014 Embarcadero Technologies, Inc. Error: Unresolved external '__endthreadex' referenced from K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3 Error: Unresolved external '__beginthreadex' referenced from K:\ACTIVEXP\SOURCE\SQLITE.SEE\SQLITE3.LIB|sqlite3 Error: Unable to perform link Marco -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith Sent: Thursday, October 22, 2015 12:12 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00 On 2015-10-22 11:01 AM, Marco Turco wrote: > Hi all, > > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > > > See below. > > > > k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW > -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF > -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 > -Ik:\BCC70\Include;k:\XHARBOUR\Include > -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c > > > > Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero > Technologies, Inc. > > K:\sqlite_see_2013\sqlite3.c: > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function > '_endthreadex' with no prototype in function sqlite3ThreadProc > > Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function > '_beginthreadex' with no prototype in function sqlite3ThreadCreate > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is > never used in function winUnfetch > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect > assignment in function vdbeSorterCompareInt > > Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect > assignment in function vdbeSorterSetupMerge > > Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is > never used in function sqlite3CodecGetKey > > k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 > @K:\sqlite_see_2013\sqlite_see_2013.bcl , > K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst These are just warnings... They are not important and it should still compile. If it fails to compile you should get a message like this: "Error : Failed to compile K:\sqlite_see_2013\sqlite3.c 185433: Some description of what failed" The warnings doesn't mean it did not compile. What makes you think the file failed to compile, is the actual compiled file missing or such? > > > > TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc. > > +K:\sqlite_see_2013\Obj\sqlite3.Obj > > > > Any help ? Thank you in advance > > > > Marco > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00
Hi all, I'm trying to generate the sqlite3 library but there is no way with Embercadero C++ 7.00. I always receive some warnings and the first two related to the _endthreadex' and '_beginthreadex' cannot permit to link me the library. See below. k:\BCC70\Bin\Bcc32.Exe -DHB_NO_DEFAULT_API_MACROS -M -c -O2 -e -tW -DHB_NO_DEFAULT_STACK_MACROS -DHB_OS_WIN_USED -DHB_FM_STATISTICS_OFF -D__HARBOUR__ -DSQLITE_HAS_CODEC=1 -Ik:\BCC70\Include;k:\XHARBOUR\Include -nK:\sqlite_see_2013\Obj K:\sqlite_see_2013\sqlite3.c Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero Technologies, Inc. K:\sqlite_see_2013\sqlite3.c: Warning W8065 K:\sqlite_see_2013\sqlite3.c 23909: Call to function '_endthreadex' with no prototype in function sqlite3ThreadProc Warning W8065 K:\sqlite_see_2013\sqlite3.c 23936: Call to function '_beginthreadex' with no prototype in function sqlite3ThreadCreate Warning W8057 K:\sqlite_see_2013\sqlite3.c 38624: Parameter 'iOff' is never used in function winUnfetch Warning W8060 K:\sqlite_see_2013\sqlite3.c 80952: Possibly incorrect assignment in function vdbeSorterCompareInt Warning W8060 K:\sqlite_see_2013\sqlite3.c 82583: Possibly incorrect assignment in function vdbeSorterSetupMerge Warning W8057 K:\sqlite_see_2013\sqlite3.c 185433: Parameter 'iDb' is never used in function sqlite3CodecGetKey k:\BCC70\Bin\TLib.Exe K:\sqlite_see_2013\sqlite_see_2013.Lib /P512 @K:\sqlite_see_2013\sqlite_see_2013.bcl , K:\sqlite_see_2013\Debug\sqlite_see_2013.Lst TLIB 6.4 Copyright (c) 1987-2014 Embarcadero Technologies, Inc. +K:\sqlite_see_2013\Obj\sqlite3.Obj Any help ? Thank you in advance Marco
[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?
Excerpts from Daniel Polski's message of 2015-09-24 09:50:36 -0300: > > Hello again, > I think I can simplify the actual question: > > Can I use a SELECT returning multiple rows as input to some INSERT > logic, and in that logic INSERT into multiple tables? That was exactly what I showed you using only one table. (INSERT always insert in one Table, only exception is for views (INSTEAD OF) triggers). > > Like if I have a table t1 containing id 1, 2, 3. > > For each id in t1, do > INSERT INTO t2 (...) > INSERT INTO t3 (...) --But this depends on the last_insert_rowid() from > the insert into t2 above > > The problem for me is that I don't know how to fetch the > last_insert_rowid() needed for the t3 table. (If I didn't have to split > up the insert I could simply run something like INSERT INTO t2 SELECT id > FROM t1; ) You can't use last_insert_rowid because it only stores a single value. In order to have the list of values you have somehow to create a variable for that. One way is to use temporary tables, but I think this is not possible in a trigger (to have a CREATE statement). The other way is what I suggested you: create a new trigger for t2 itself to check on the values inserted. > > Best Regards, > Daniel -- Marco Arthur @ (M)arco Creatives
[sqlite] Trigger, fetch matching rows and process/insert each row into multiple tables?
Excerpts from Daniel Polski's message of 2015-09-24 07:38:24 -0300: > Hello! > > --Table t1 contains some "base" data > CREATE TABLE t1 ( > idINTEGER PRIMARY KEY, > name VARCHAR(10), > value INT NOT NULL > ); > > INSERT INTO t1 VALUES(1, 't1 1', 0); > INSERT INTO t1 VALUES(2, 't1 2', 0); > > --Table t2 can contain extra data for a row in table t1 > CREATE TABLE t2( > id INTEGER PRIMARY KEY, > t1_idINT NOT NULL, > dataINT NOT NULL, > CONSTRAINT 'FK_t2_001' FOREIGN KEY( t1_id ) REFERENCES t1 ( id ) > ); > INSERT INTO t2 VALUES(1, 1, 10); > INSERT INTO t2 VALUES(2, 1, 20); > INSERT INTO t2 VALUES(3, 2, 30); > > CREATE TABLE t3 ( > idINTEGER PRIMARY KEY, > name VARCHAR(10) > ); > > CREATE TABLE t4( > t2_idINT NOT NULL, > t3_idINT NOT NULL, > CONSTRAINT 'FK_t4_001' FOREIGN KEY( t2_id ) REFERENCES t2 ( id ), > CONSTRAINT 'FK_t4_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id ) > ); > > > > -- Here is where my real question starts. Can I fetch a list of rows in > a trigger > -- and update different tables from that data? Im looking for some kind > -- of looping over the rows found in t2, where I can insert data to t3 and > -- t4 for each row found in t2. > > CREATE TRIGGER trigger_1 AFTER UPDATE OF value ON t1 > WHEN new.value = 1 > AND EXISTS (SELECT id FROM t2 WHERE t1_id = new.id) > AND (some more rules) > ... > -- For each row in t2 with a t1_id referencing the t1 id, > -- 1: insert a row into t3 > -- 2: insert a row into t4 binding the newly created row in t3 to the > corresponding row in t2 may try this to insert into t3. CREATE TRIGGER trigger_on_table_1 AFTER UPDATE OF value ON t1 FOR EACH ROW WHEN NEW.VALUE = 1 AND EXISTS (SELECT id FROM te WHERE ti_id = NEW.id) BEGIN -- SELECT THE ROWS FROM t2 to insert values INSERT INTO t3 VALUES(id, name) SELECT t2.t1_id, 'dummy' FROM t2 WHERE t2.t1_id == NEW.id; END; Then you may wish to set a trigger to t4 itself to handle the newly created t3 rows. -- Marco Arthur @ (M)arco Creatives
[sqlite] System.Data.SQLite encryption
Anyone know which encryption algorithm is used in System.Data.SQLite? Seems like it doesn?t use the standard SEE sqlite extension. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] sqlite3session
Is the sqlite3session module still available and supported? If not is there a replacement/workaround for it? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] Epoch time with timestamp in ms
Hello I tried to convert an epoch time with a timestamp in milliseconds like 1393732179010 to a correct date but it seems like that the function: SELECT datetime(1393732179010, 'unixepoch', 'localtime'); is not able to detect ms resolution and incorrectly reports a negative date. How can I tell sqlite to interpret timestamp in ms? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-16 1:36 GMT+02:00 Jean-Christophe Deschamps : > While I can agree with some of the most obvious "don't be dumb" remarks, > there are many things that can't be let unchallenged. And I don't > appreciate the overall tone: "WE at MozillaBigBalls are all clever enough > to use SQLite smartly but you mere mortals are not, so don't even think to > use that fragile piece of softawre." > There's never been that kind of tone, the article has been written to point new Mozilla codebase contributors at possible pitfalls we already hit in the past, and actually help them making informed decisions. It's not intended for global consumption. Sure it's public, cause there's no reason to "hide" technical document that might be useful to contributors, just to complicate their access. Can you find any page that states Mozilla doesn't like SQLite or wants anyone to abandon it? It is used everywhere, from mercurial repositories to Firefox OS... > In it's introduction, the blurb talks about SQLite hidden complexity. Then > it avdises Mozilla pluggin developers to avoid SQLite at almost any rate, > due to "performance reasons", and recommends compressed JSON/logs instead. > It actually briefly explains in which cases a JSON log can work better and at the end it also states what to do and avoid if still SQLite is found to be the best store (and it clearly happens!). It tries to make people think before doing. > Yet this guy(s) advocate that *-every-* pluggin devs should independently > roll his own storage layer It states to evaluate alternatives. Existing alternatives like OS.File writeAtomic and JSON. Those are already used with success in both the products and add-ons, when it makes sense. Sure the developer mus think to durability, backups, coherence, but would it not be the same if he'd use SQLite? You must find the right compromise between durability and performance, you still have to handle corruption cases. Thinking about that stuff is part of normal planning for any feature, independently from the chosen store. > Then another question remains: instead of putting the onus on SQLite being > huge (footprint) and slow (CPU, the 22s "example"), why don't the author(s) > of the blurb question the real root causes of the evil they condemn and > openly recognize that the problem lies entirely elsewhere, perhaps in > Mozilla core code design itself? > Because it's clear the article is about SQLite use in the Mozilla codebase. It's clear there are issues in the Mozilla codebase itself (I'd be surprised of the opposite!). > After all, it's Mozilla devs themselves who designed pluggin APIs and let > "spurious main-thread SQL statements" be possible. If they were sooo > clever, they would never had allowed that and they also would have wrapped > SQLite interface in a strictly limited set of rules enforced by a safe API. > That, they won't tell you. > And they'd have a 100% bug free product with millions lines of code... No, that's unrealistic. The Mozilla codebase comes from the 90s Netscape codebase, at that time the most common thing was a single-threaded and single-process browser, able to show text, some images and tables. The reality evolved A LOT and the code had to evolve to cope with it. At the time mozStorage (the SQLite wrapper) was written, there was still that kind of vision, and it was written as a main-thread synchronous API. Sure, now we all know it was wrong, but at that time it was the right-ish thing to do. The API grew a purely asynchronous alternative, but when you have hundreds millions of users and thousands of add-ons using an API, you can't just say "sorry, we now break you all". You must play fairly. Sure, the Firefox add-ons are the most powerful add-ons around, they can do anything, and while this might be (as you point out) a downside, cause they can perform poorly, it is also their major selling point. There are still a lots of things you can do with a Firefox add-on that you can't do in any other browser add-on. Also, this has nothing to do with the fact SQLite can still be the wrong choice for certain data store needs (as it's the best for others), that is still the main purpose of that article. > Also, if Mozilla devs were sooo much more clever than average Joe and sooo > caring about performance, they certainly would have fixed the hundreds of > memory leaks that plague FF users > This is going off topic, I'd be happy to digress about this but it's not the right place. If you have suggestions or bugs, the codebase is open and the bug tracker is public, you can reach every single developer through mail or IRC. You have all the tools to make the difference. Marco
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-15 18:05 GMT+02:00 Jean Chevalier : > What I'm thinking now is to what extent the developer who put up the > Mozilla wiki page was entitled to put up opinions and statements either > expressed or implied about a third-party product on behalf of the Mozilla > Foundation (though is that the same Foundation that pays the Consortium > member fee?). The mozilla wiki contains informations useful to contributors to the mozilla codebase and more generally "mozillians", it's not intended to be a global resource of information like wikipedia, nor any kind of official documentation on how to use libraries. It's targeted to code written against the Mozilla codebase and it's not even in the official MDN. The document you pointed at was created some time ago, cause we had a problem with developers taking "the easy way" too often, when they needed a store they just used SQLite (or better mozStorage, our wrapper) because the API was nice and already available, without doing any kind of analysis of their needs. The title was (likely) chosen explicitly negative to make people read the article and clarify the point before going deep into details. And it helped, now people ask what's the best store for their kind of workflow, and clearly SQLite is still a used option. Please don't attach any kind of negative bias to a wiki article, it's just a technical article about possible pitfalls our developers will hit (and have hit) if they don't think what they are doing. Nothing more than that. SQLite is used extensively in every single Mozilla product, so what? . The contents of the page came out from actual bugs and misuses we hit in years of use and experience with it and were discussed with attention. Most also have workarounds or suggested fixes. Honestly I just think you are giving too much importance to a technical document with a clear target and very well expressed points, rather than to the fact SQLite is happily used everywhere. It should just be used properly, not randomly.
[sqlite] sqlitediff
Luuk, that's really really funny thanks. Starting from sqlite 3.8.10 sqlitediff.exe has been added to the official sqlite distribution. I downloaded both the Amalgamation version and the Alternative Source Code Formats from the official download page but I wasn't able to find the diff code, nor a link to download the executable from http://www.sqlite.org/sqldiff.html <http://www.sqlite.org/sqldiff.html>. Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs > On 24 May 2015, at 08:59, Luuk wrote: > > On 24-5-2015 08:39, Marco Bambini wrote: >> Where can I download sqlitediff and its source code? >> > > http://lmgtfy.com/?q=sqlitediff=1 > > or, more correct: > http://lmgtfy.com/?q=sqlitediff+source > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlitediff
Where can I download sqlitediff and its source code? Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
[sqlite] Query help
I have a table EnginePreference: CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, UNIQUE(engine,databasename,key)) and a table Groups: CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE COLLATE NOCASE) I need to select from EnginePreferences replacing groupid with groupname and I can do that with: SELECT engine,groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); What I really need is ALSO to replace groupname with * if groupid is 0. Please note that groupid 0 is never written into the Groups table so my original query would return NULL as groupname with groupid is 0 instead of the required "*". Any idea? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs
Re: [sqlite] WITHOUT ROWID option
Thanks a lot Richard, I really appreciate. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 16:31, Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> What is the best way to know if a table has been created with the WITHOUT >> ROWID option? >> > > > (1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see > if it returns an error. This might fail on a table like "CREATE TABLE > xyz(rowid TEXT PRIMARY KEY)WITHOUT ROWID". > > (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master > WHERE tbl_name='table'". If the PRAGMA mentions an > "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you > have a WITHOUT ROWID table. This approach is more complex, but never > fails, afaik. > > >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://twitter.com/sqlabs >> http://instagram.com/sqlabs >> >> >> >> ___ >> 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] WITHOUT ROWID option
So, is there an official recommended way? or that check should require a manual sql parsing? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 15:51, Igor Tandetnik <i...@tandetnik.org> wrote: > On 5/7/2014 9:40 AM, RSmith wrote: >> SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE >> type='table' AND tbl_name='YourTableName' >> >> Returns 1 for tables made without rowid, 0 for the rest. > > CREATE TABLE t(x text default 'WITHOUT ROWID'); > > -- > Igor Tandetnik > > ___ > 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] WITHOUT ROWID option
What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_analyzer source code
If I remember correctly, sqlite3_analyzer is a python script... I am not able to find its source code since in the download page there is a binary app only. Any help? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3session_changeset API oddity
I am using sqlite3_changeset API to add undo/redo capabilities to an app. I record sqlite operations and I store all of them in a sqlite3_session object. When I need to UNDO an operation I simply do something like: rc = sqlite3session_changeset(session, , ); rc = sqlite3changeset_invert(pnChangeset, ppChangeset, , ); rc = sqlite3changeset_apply (self.db, pnInvertedChangeset, ppInvertedChangeset, NULL, fConflict, NULL); The problem is that I would like to reuse the same session object to perform the REDO operation but after executing the UNDO code above, when I call: rc = sqlite3session_changeset(session, , ); pnChangeset is always 0. Is this a bug or something not correctly documented? Please let me know. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Apply a changeset
Documentation is not very clear about this point… in order to apply a change set starting from a session should just I write something like: int rc= sqlite3session_changeset(session, , ); rc = sqlite3changeset_apply (db, pnChangeset, ppChangeset, NULL, NULL, NULL); Is that all? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 03 Jan 2014, at 15:54, Richard Hipp <d...@sqlite.org> wrote: > The standard pre-built amalgamation won't work with sessions. You have to > pull source code from the "sessions" branch: > http://www.sqlite.org/src/timeline?r=sessions > > > On Fri, Jan 3, 2014 at 9:50 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> Hello, >> I am using the sqlite3_session module amalgamated into the main sqlite3.c >> file (3.8.2). >> I defined both: >> >> #define SQLITE_ENABLE_SESSION 1 >> #define SQLITE_ENABLE_PREUPDATE_HOOK1 >> >> but compiler is not able to find all the sqlite3_preupdate* functions. >> I am missing something? >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://twitter.com/sqlabs >> http://instagram.com/sqlabs >> >> >> >> ___ >> 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
[sqlite] sqlite3_preupdate functions
Hello, I am using the sqlite3_session module amalgamated into the main sqlite3.c file (3.8.2). I defined both: #define SQLITE_ENABLE_SESSION 1 #define SQLITE_ENABLE_PREUPDATE_HOOK1 but compiler is not able to find all the sqlite3_preupdate* functions. I am missing something? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] List of registered tokenizers
What is the correct way to get a list of all registered sqlite tokenizers? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 search for terms inside a word
I have a virtual FTS4 table and I would like to search for some terms inside a word, is that possible? For example if a column contains "mysuperword mytestword" I would like to find it using the subword: "super". So with LIKE it worked but it is really slow: SELECT * FROM myTable WHERE myColumn LIKE '%super%'; While with the MATCH operator it does not find anything: SELECT * FROM myTable WHERE myColumn MATCH '*super*'; Any help? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW:
http://www.horsecenter.com.br/i4jsow.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Change a field type
Hi, I have a table with one field defined as smallint(3) and I would like to change the field type now in char(3) (without lost the value inside). How can I make the change ? Do I need to create a new table with the new fields where import the current data or is there a better method ? Thanks in advance Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite and Mac App Store sandbox
Seems like new sqlite based applications will have a lot of troubles to be accepted in the Mac App Store thanks to strictly sandbox policies. The main issue is that sqlite uses temporary files (next to the database file) to store wal, shm or journal information. Apple simply does not grant read/write privileges on non user specifically selected files… so there is no way for sqlite to create temporary information. A solution could be to just set journal_mode to MEMORY but database got corrupted in case of crash or power off and does not seem a very good solution. Another solution could be to have the ability to specify a directory for all these files… but there could be a lot of side effect. Please note that I also tried to set the com.apple.security.temporary-exception.files.home-relative-path.read-write to all user's home folder but Apple rejects all these settings. Anyone have another solution? Any help? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UTF-8
Sometimes instead of a query like: "INSERT INTO foo (col1) VALUES ('Boxhagener Straße');" I receive the utf-8 encoded counterpart: "INSERT INTO foo (col1) VALUES ('Boxhagener Str\u00c3\u009fe');" Is there a way to automatically decode this latest query to UTF-8 just using sqlite API? Please note that since I have access to the sqlite amalgamation source code I could also expose an internal function. Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 05:18 PM, Dan Kennedy wrote: Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. You are right. I have been re-reading our mail thread and this is exactly what happens. I have build a SQLite version from the latest version in the archive (3.7.16) and this version creates a correct backup. Thanks for the fix. -- Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 01/02/2013 11:03 AM, Marco ten Thije wrote: On 01/02/2013 10:04 AM, Dan Kennedy wrote: What series of commands did you pass to 3.7.5 to create this file? This file was copied from an existing product which uses QT 4.7 libraries to populate the database. I cannot reproduce the exact commands which resulted in this file, because it is populated dynamically by the software. I found out that QT 4.7 is using the 3.6.19 SQLite library. The databases were created using the 3.7.5 version, so you were right about this. The database is created with a 3.7.5 version and populated with 3.6.19 I did some more testing and found out that the backup database which SQLite 3.7.5 reports to be corrupted, is reported OK by SQLite 3.6.19, although this backup was created by the 3.7.5 SQLite version. I created a ASCII dump (using the .dump command) of the backup database using 3.6.19 and this dump is identical (except for the 'PRAGMA foreign_keys=OFF') to a dump of the original database. So it looks like SQLITE 3.7.5 and 3.7.15.1 (and probably all versions in between) have a problem reading the backup file, while 3.6.19 can read it. I have stored a copy of the original database on http://www.cbbio.nl/sqlite/energy.sqlite for further analysing Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
54 is never used Page 55 is never used Page 58 is never used Page 65 is never used Page 66 is never used Page 68 is never used Page 73 is never used Page 82 is never used Page 89 is never used Page 91 is never used Page 93 is never used Page 98 is never used Page 99 is never used -- Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
On 12/21/2012 05:18 PM, Dan Kennedy wrote: Thanks. I think it's this: http://www.sqlite.org/src/info/0cfd98ee20 Dan. Thanks. I have looked into ticket, but we also see this problem when the backup is written and read by the same SQLite version (3.7.5). I have recreated a backup using the 3.7.5 version. These are the hexdumps of both the original file and the backup created with SQLite 3.7.5: The original file: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 The backup created with SQLite 3.7.5: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 030 0100 040 050 0100 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 -- Regards, Marco ten Thije ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
What is the size of the two database files? The size of both files is 160768 bytes. Also, can we have the first 6 lines of each hex dump (i.e. enough to see the first 100 bytes)? The first bytes of the original database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 f1d0 7600 020 9700 2100 0300 0100 030 0100 040 050 0200 060 2d00 1de2 000d 0004 00e3 7202 2002 070 7301 e300 080 The first bytes of the backup database: 000 5153 694c 6574 6620 726f 616d 2074 0033 010 0004 0101 4000 2020 0100 7600 020 9700 2100 0100 0100 030 0100 040 050 0100 060 2d00 27e2 000d 0004 00e3 7202 2002 070 7301 e300 080 I can send the binary database, but the mailinglist strips the attachments. Best regards, Marco ten Thije // ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
It returns 'ok': ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma integrity_check; ok sqlite> So, the original database looks ok. I have compared the two databases (original and backup) and they only differ in a few bytes in the header. The sizes match. This is a diff between the hexdumps of both databases: diff energy.hex backup.hex 2,3c2,3 < 010 0004 0101 4000 2020 f1d0 7600 < 020 9700 2100 0300 0100 --- > 010 0004 0101 4000 2020 0100 7600 > 020 9700 2100 0100 0100 6,7c6,7 < 050 0200 < 060 2d00 1de2 000d 0004 00e3 7202 2002 --- > 050 0100 > 060 2d00 27e2 000d 0004 00e3 7202 2002 On 12/21/2012 02:34 PM, Eric Minbiole wrote: Can you please try running "pragma integrity_check;" on the original database? That will give an indication of whether the original database has any data corruption. On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije< marco.tenth...@usetechnology.nl> wrote: Hello, We are facing a problem with the '.backup' command using the SQLite command line interface. The resulting backup-database seems to be corrupt. We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can also be reproduced on the latest 3.7.15.1 version on Intel. I have attached our database to this email. These are the steps to reproduce it: ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from EnergyLogData; 3306 sqlite> .backup backup.sqlite sqlite> .exit The above shows the number of records in one of the tables and creates a backup to a new 'backup.sqlite' database file. But this new file seems to be corrupt: ./sqlite3 backup.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from EnergyLogData; Error: database disk image is malformed sqlite> .exit I have look into the Bug database and found ticket 3858, but that one should already be fixed and we are not writing to the database during the backup. Is this a bug or some other problem? -- Regards, Marco ten Thije --**--** *Use system engineering bv* Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Mail Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: +31 53 5741456 F: +31 53 5741458 E: marco.tenthije@usetechnology.** nl<marco.tenth...@usetechnology.nl> <mailto:marco.tenthije@** usetechnology.nl<marco.tenth...@usetechnology.nl>>I: www.usetechnology.nl<http://www.usetechnology.nl/> Chamber of Commerce nr: Veluwe en Twente 528 98210 VAT nr.: NL 8506.54.713B01 --**--** /This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Use system engineering bv shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Use system engineering bv does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. / ___ 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 -- Met een vriendelijke groet, Marco ten Thije *Use system engineering bv* Bezoekadres: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Postadres: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: 053 5741456 F: 053 5741458 E: marco.tenth...@usetechnology.nl <mailto:marco.tenth...@usetechnology.nl> I: www.usetechnology.nl <http://www.usetechnology.nl/> KvK nr: Veluwe en Twente 528 98210 BTW nr.: NL 8506.54.713B01 /Dit bericht (inclusief de eventuele bijlagen) is vertrouwelijk. Wanneer u dit bericht ten onrechte heeft ontvangen,
[sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command
Hello, We are facing a problem with the '.backup' command using the SQLite command line interface. The resulting backup-database seems to be corrupt. We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can also be reproduced on the latest 3.7.15.1 version on Intel. I have attached our database to this email. These are the steps to reproduce it: ./sqlite3 energy.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from EnergyLogData; 3306 sqlite> .backup backup.sqlite sqlite> .exit The above shows the number of records in one of the tables and creates a backup to a new 'backup.sqlite' database file. But this new file seems to be corrupt: ./sqlite3 backup.sqlite SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select count(*) from EnergyLogData; Error: database disk image is malformed sqlite> .exit I have look into the Bug database and found ticket 3858, but that one should already be fixed and we are not writing to the database during the backup. Is this a bug or some other problem? -- Regards, Marco ten Thije *Use system engineering bv* Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands Mail Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands T: +31 53 5741456 F: +31 53 5741458 E: marco.tenth...@usetechnology.nl <mailto:marco.tenth...@usetechnology.nl> I: www.usetechnology.nl <http://www.usetechnology.nl/> Chamber of Commerce nr: Veluwe en Twente 528 98210 VAT nr.: NL 8506.54.713B01 /This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorised use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Use system engineering bv shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Use system engineering bv does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. / ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: SQLiteManager 4 - Working with sqlite databases will never be the same!
Hello, we are extremely proud to announce a major release for our SQLiteManager application (available for both MacOS X and Windows). SQLiteManager is a powerful database management system for sqlite databases, it combines an easy to use interface with blazing speed and advanced features. SQLiteManager allows you to work with a wide range of sqlite 3 databases (like plain databases, in memory databases, AES 128/256/RC4 encrypted databases and also with cubeSQL server databases). Some features includes: - New flagship interface - Powerful inline editor - Built-in Lua scripting language - Powerful table editor with realtime sql generator - Support for all official encryption algorithms (AES 128/256 and RC4) - Built-in virtual machine disassembler - Built-in chart generator - Flexible import/export engine - … and much more! More information available from: http://www.sqlabs.com/sqlitemanager/ A SQLiteManager single license is $49.00 (upgrades from $29.00). Company and multiplatform licenses are also available. For more information, please visit http://sqlabs.com/store/ Sincerely yours, SQLabs Team http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Write to a View
>From the official documentation: You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use an INSTEAD OF trigger on the view to accomplish the same thing. Can someone clarifies this point? Seems like it is possible to write into a view but I do not understand what if the correct way to perform the operation. Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteDoctor
Announcing SQLiteDoctor, the only way to recover your corrupted sqlite databases. Details available from: http://www.sqlabs.com/sqlitedoctor.php -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Columns involved in a WHERE clause
Can I parse the output of the EXPLAIN my_query statement in order to have an indirect access to columns involved in the WHERE clause? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs On Sep 21, 2012, at 4:10 PM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> Hello, >> is there a way to extract column names involved in a WHERE clause of a >> query without manually parse the select statement? >> >> For example from a query like: >> SELECT * FROM myTable WHERE col1=… AND col2=…; >> I would need to extract both col1 and col2. >> >> There are no APIs in SQLite to do that. No. I think you have to parse > the SQL yourself. > > > >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> >> >> >> >> >> >> ___ >> 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] Columns involved in a WHERE clause
Can I parse the output of the EXPLAIN my_query statement in order to have an indirect access to columns involved in the WHERE clause? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs On Sep 21, 2012, at 4:10 PM, Richard Hipp <d...@sqlite.org> wrote: > On Fri, Sep 21, 2012 at 9:56 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> Hello, >> is there a way to extract column names involved in a WHERE clause of a >> query without manually parse the select statement? >> >> For example from a query like: >> SELECT * FROM myTable WHERE col1=… AND col2=…; >> I would need to extract both col1 and col2. >> >> There are no APIs in SQLite to do that. No. I think you have to parse > the SQL yourself. > > > >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> >> >> >> >> >> >> ___ >> 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
[sqlite] Columns involved in a WHERE clause
Hello, is there a way to extract column names involved in a WHERE clause of a query without manually parse the select statement? For example from a query like: SELECT * FROM myTable WHERE col1=… AND col2=…; I would need to extract both col1 and col2. Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Send me your corrupted databases
Hello, can you please send me your corrupted database files? (or put somewhere where I can download them). I am finishing a new sqlite utility and I would like to test it with a lot of different databases. Thanks a lot! -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] app-defined functions, statements and sqlite3_value
Ciao, I am a True Beginner with SQLite and I am writing a binding to it for a programming language. While wrapping the functions accepting "sqlite3_value" arguments, I am not sure if I understand from where "sqlite3_bind_value()" (and "sqlite3_result_value()") should take their arguments. Is it actually possible and safe to execute a statement from the implementation of an app-defined SQL function? So SQL function arguments can be bound to statement parameters, and results from a statement can be used as return values of app-defined SQL functions? Can I think of instances of "sqlite3_value" as shared data structures whose life is correctly handled by SQLite with some mechanism like reference counting? If the answers are "yes": * Is there some simple and "known" example of such mechanism I can put in my test suite and documentation to show how it is done? * If the nested statement execution fails for some reason: is there some convention about how the app-defined SQL function should signal the error to its caller? If the answers are "no": are "sqlite3_bind_value()" and "sqlite3_result_value()" there only for SQL function implementations embedded in the source code of SQLite? So is it better if I do not expose them at the foreign language level? HTH -- Marco Maggi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite4 locking and concurrency
Hello, I am wondering if someone is able to provide some details (or directions) about locking and concurrency in sqlite 4. I guess there should be improvements over sqlite 3 but I am looking for information. Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between 3.7.11 and 3.7.13
I am sorry, but it was sent twice for a mistake. -- Marco Bambini http://www.sqlabs.com On Jun 21, 2012, at 2:41 PM, Marco Bambini wrote: > Consider the following example: > > CREATE TABLE t1(x); > CREATE TABLE t2(y); > SELECT max((SELECT avg(x) FROM t2)) FROM t1; > > With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error > "Misuse of aggregate: avg()" is returned. > Any thought? > -- > Marco Bambini > http://www.sqlabs.com > http://twitter.com/sqlabs > > > > > > > > ___ > 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] Difference between 3.7.11 and 3.7.13
Consider the following example: CREATE TABLE t1(x); CREATE TABLE t2(y); SELECT max((SELECT avg(x) FROM t2)) FROM t1; With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse of aggregate: avg()" is returned. Any thought? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Difference between 3.7.11 and 3.7.13
Consider the following example: CREATE TABLE t1(x); CREATE TABLE t2(y); SELECT max((SELECT avg(x) FROM t2)) FROM t1; With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse of aggregate: avg()" is returned. Any thought? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite sessions: handling rollbacks
Can you post some code or more details? Sessions has been specifically developed to handle rollbacks so your issue sounds really strange to me. -- Marco Bambini http://www.sqlabs.com On Jun 13, 2012, at 8:57 PM, Charles Samuels wrote: > > I'm using sqlite's sessions module, and I'm noticing that it doesn't appear > to > handle rollbacks. > > Specifically, if I'm recording a database, and then I rollback the database, > the session module appears to still record the changes made by that rollback. > > Why does this apparently significant flaw exists this module? Is there an > easy > to way solve it? > > Charles > ___ > 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] The "sessions" branch
I am sorry Dr. Hipp, I am probably doing something wrong, but I just retried and no sessions code is in-line in the final sqlite3.c file. I downloaded: http://www.sqlite.org/src/zip/SQLite-d07b7b67d1b3bf65.zip?uuid=d07b7b67d1b3bf65cfe8d96d45a7f1d387bea7ce then cd SQLite-d07b7b67d1b3bf65 ./configure; make sqlite3.c preupdate_hook code is in-line but no sessions related code. -- Marco Bambini http://www.sqlabs.com On Jun 4, 2012, at 4:38 PM, Richard Hipp wrote: > On Mon, Jun 4, 2012 at 10:34 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> I just tried to download: >> http://www.sqlite.org/src/info/d07b7b67d1 >> from the session branch. >> >> Then: >> ./configure; make sqlite3.c >> but no session module is included in the amalgamation sqlite3.c file. >> > > All of the sessions code is in-line in the core. Enable it using > -DSQLITE_ENABLE_PREUPDATE_HOOK -DSQLITE_ENABLE_SESSION. > > >> >> From the executed cp command: >> cp -f ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c >> ./src/backup.c ./src/bitvec.c ./src/btmutex.c ./src/btree.c ./src/btree.h >> ./src/btreeInt.h ./src/build.c ./src/callback.c ./src/complete.c >> ./src/ctime.c ./src/date.c ./src/delete.c ./src/expr.c ./src/fault.c >> ./src/fkey.c ./src/func.c ./src/global.c ./src/hash.c ./src/hash.h >> ./src/hwtime.h ./src/insert.c ./src/journal.c ./src/legacy.c >> ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem0.c ./src/mem1.c >> ./src/mem2.c ./src/mem3.c ./src/mem5.c ./src/memjournal.c ./src/mutex.c >> ./src/mutex.h ./src/mutex_noop.c ./src/mutex_os2.c ./src/mutex_unix.c >> ./src/mutex_w32.c ./src/notify.c ./src/os.c ./src/os.h ./src/os_common.h >> ./src/os_os2.c ./src/os_unix.c ./src/os_win.c ./src/pager.c ./src/pager.h >> ./src/parse.y ./src/pcache.c ./src/pcache.h ./src/pcache1.c ./src/pragma.c >> ./src/prepare.c ./src/printf.c ./src/random.c ./src/resolve.c >> ./src/rowset.c ./src/select.c ./src/status.c ./src/shell.c ./src/ >> sqlite.h.in ./src/sqlite3ext. >> h ./src/sqliteInt.h ./src/sqliteLimit.h ./src/table.c ./src/tclsqlite.c >> ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c >> ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c >> ./src/vdbeblob.c ./src/vdbemem.c ./src/vdbesort.c ./src/vdbetrace.c >> ./src/vdbeInt.h ./src/vtab.c ./src/wal.c ./src/wal.h ./src/walker.c >> ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c >> ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h >> ./ext/fts1/fts1_tokenizer1.c ./ext/fts2/fts2.c ./ext/fts2/fts2.h >> ./ext/fts2/fts2_hash.c ./ext/fts2/fts2_hash.h ./ext/fts2/fts2_icu.c >> ./ext/fts2/fts2_porter.c ./ext/fts2/fts2_tokenizer.h >> ./ext/fts2/fts2_tokenizer.c ./ext/fts2/fts2_tokenizer1.c ./ext/fts3/fts3.c >> ./ext/fts3/fts3.h ./ext/fts3/fts3Int.h ./ext/fts3/fts3_aux.c >> ./ext/fts3/fts3_expr.c ./ext/fts3/fts3_hash.c ./ext/fts3/fts3_hash.h >> ./ext/fts3/fts3_icu.c ./ext/fts3/fts3_porter.c ./ext/fts3/fts3_snippet.c >> ./ext/fts3/fts3_tokeni >> zer.h ./ext/fts3/fts3_tokenizer.c ./ext/fts3/fts3_tokenizer1.c >> ./ext/fts3/fts3_write.c ./ext/icu/sqliteicu.h ./ext/icu/icu.c >> ./ext/rtree/rtree.h ./ext/rtree/rtree.c keywordhash.h opcodes.c opcodes.h >> parse.c parse.h config.h sqlite3.h tsrc >> >> no ./ext/session/ folder is included. >> Any help? >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> On May 31, 2012, at 9:27 PM, Richard Hipp wrote: >> >>> On Thu, May 31, 2012 at 2:55 PM, Charles Samuels <char...@cariden.com >>> wrote: >>> >>> If you are on the sessions branch, then you have the changes just do: >>> >>> ./configure; make sqlite3.c >>> >>> Then compile your application with -DSQLITE_ENABLE_SESSION to get the >>> functionality. Documentation on the session extensions is thin but can >> be >>> seen here: http://www.sqlite.org/sessions/session.html >> ___ >> 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] The "sessions" branch
I just tried to download: http://www.sqlite.org/src/info/d07b7b67d1 from the session branch. Then: ./configure; make sqlite3.c but no session module is included in the amalgamation sqlite3.c file. >From the executed cp command: cp -f ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/backup.c ./src/bitvec.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/btreeInt.h ./src/build.c ./src/callback.c ./src/complete.c ./src/ctime.c ./src/date.c ./src/delete.c ./src/expr.c ./src/fault.c ./src/fkey.c ./src/func.c ./src/global.c ./src/hash.c ./src/hash.h ./src/hwtime.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem0.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mem5.c ./src/memjournal.c ./src/mutex.c ./src/mutex.h ./src/mutex_noop.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/notify.c ./src/os.c ./src/os.h ./src/os_common.h ./src/os_os2.c ./src/os_unix.c ./src/os_win.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pcache.c ./src/pcache.h ./src/pcache1.c ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/resolve.c ./src/rowset.c ./src/select.c ./src/status.c ./src/shell.c ./src/sqlite.h.in ./src/sqlite3ext. h ./src/sqliteInt.h ./src/sqliteLimit.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbemem.c ./src/vdbesort.c ./src/vdbetrace.c ./src/vdbeInt.h ./src/vtab.c ./src/wal.c ./src/wal.h ./src/walker.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c ./ext/fts2/fts2.c ./ext/fts2/fts2.h ./ext/fts2/fts2_hash.c ./ext/fts2/fts2_hash.h ./ext/fts2/fts2_icu.c ./ext/fts2/fts2_porter.c ./ext/fts2/fts2_tokenizer.h ./ext/fts2/fts2_tokenizer.c ./ext/fts2/fts2_tokenizer1.c ./ext/fts3/fts3.c ./ext/fts3/fts3.h ./ext/fts3/fts3Int.h ./ext/fts3/fts3_aux.c ./ext/fts3/fts3_expr.c ./ext/fts3/fts3_hash.c ./ext/fts3/fts3_hash.h ./ext/fts3/fts3_icu.c ./ext/fts3/fts3_porter.c ./ext/fts3/fts3_snippet.c ./ext/fts3/fts3_tokeni zer.h ./ext/fts3/fts3_tokenizer.c ./ext/fts3/fts3_tokenizer1.c ./ext/fts3/fts3_write.c ./ext/icu/sqliteicu.h ./ext/icu/icu.c ./ext/rtree/rtree.h ./ext/rtree/rtree.c keywordhash.h opcodes.c opcodes.h parse.c parse.h config.h sqlite3.h tsrc no ./ext/session/ folder is included. Any help? -- Marco Bambini http://www.sqlabs.com On May 31, 2012, at 9:27 PM, Richard Hipp wrote: > On Thu, May 31, 2012 at 2:55 PM, Charles Samuels <char...@cariden.com>wrote: > > If you are on the sessions branch, then you have the changes just do: > > ./configure; make sqlite3.c > > Then compile your application with -DSQLITE_ENABLE_SESSION to get the > functionality. Documentation on the session extensions is thin but can be > seen here: http://www.sqlite.org/sessions/session.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disable lock control in Sqlite (windows)
Hi all, I need to disable the Sqlite3 locking system for a Windows app that runs on a Mac using the Wine/Crossover API, this due to the fact Wine/Crossover have problem to manage the locking status on a network shared drive. My app already has a built-in locking system so it could runs also without the Sqlite3 internal lock system. So, is there a way to disable the Sqlite locking system (windows) ? As I know this is already available in Sqlite3 unix using the unix-nolock parameter in lib compilation. Thanks in advance Best Regards, Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Escape table and column names
Hello, I would like to receive a definitive answer about what is the recommended way to escape table and column names. SQLite supports single quotes, double quotes and square brackets (for mySQL compatibility) … but I haven't yet found the official or recommended way. Anyone have an answer? What about if table name or column name contains that escape character? Do I need to escape it in another way? Please let me know. Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Serialized access faster than multithreaded?
Hi there, I've been using sqlite for a few years now and it worked fine in my experience. Now that I have some time, I'm stress testing it to see how much I can get from it; what I want to check is its performance in a multithreading environment. To my surprise, it seems that serialized access to the database (which is the default in the source code for Windows) is ~20% faster than multithreading (no matter how the shared cache option is set). My test program creates 4 threads that do some work on a single table of a ~200MB WAL-based database. First two threads do only SELECTs; third thread does UPDATEs; fourth thread does INSERTs; the SQL commands are not wrapped into explicit transactions. Serialized test (SQLITE_THREADSAFE=1): The main thread opens the database, sets the cache and busy timeout to huge values and passes the db descriptor to the threads. Multithreaded test (SQLITE_THREADSAFE=2): The main thread creates the other threads and wait; each thread opens its own database connection and sets the cache and timeout (using a shared cache required some changes to handle the LOCKED return codes, but didn't give better results). Is this the expected behavior or am I missing something? Thanks in advance. Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table already exists error code
When I try to create a table that already exists inside a database I obtain the error message: "table xyz already exists" and the error code is 1. Is there a way to receive a more detailed error message that can inform me that the object is already present inside the database? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Detailed error code
When I try to create a table that already exists inside a database I obtain the error message: "table xyz already exists" and the error code is 1. Is there a way to obtain a more specific error code that can programmatically inform me that the object is already inside the db? Seems like also Extended Result Codes do not cover this case. Please help. Thanks let me know. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Visual Studio 2008 Express and sqlite3.c
Please note that if I use the same compiler to compile sqlite3.c as a single c file than everything is compiled without errors. Errors occurs only when sqlite3.c is part of a c++ project. I also manually set sqlite3.c to be compiled as C file instead of Default without any luck. Seems like something confuse the Visual C++ compiler. Any idea? -- Marco Bambini http://www.sqlabs.com On Mar 30, 2012, at 1:08 PM, Nick Shaw wrote: > -Original Message- >> I am trying to compile the latest sqlite 3.7.11 amalgamation C file within a >> Visual Studio 2008 Express C++ project. >> sqlite3.c is correctly set as to be compiled as C code but I am unable to >> find out a way to fix some compilation errors: >> >> Compiling... >> sqlite3.c >> ..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':' >> before '!' >> ..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';' >> ..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}' >> ..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct >> 'LikeOp' >> ..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have >> struct/union type >> ..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!' >> ..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have >> struct/union type >> ..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!' >> ..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of this >> type as an expression >>..\Sources\sqlite3.c(8133) : see declaration of 'ExprList' >> ..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have >> struct/union type >> ..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few >> arguments for call >> ..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!' >> ..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have >> struct/union type >> ..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few >> arguments for call >> ..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!' > > Looks like something's not right with the definition of the LikeOp struct in > your copy of the file. I've got the exact same amalgamation in my VS2008 > project and it builds fine. Can you check what you have for the LikeOp > structure definition? Mine looks like this, and starts on line 107829: > > struct LikeOp { > Token eOperator; /* "like" or "glob" or "regexp" */ > int not; /* True if the NOT keyword is present */ > }; > > > Thanks, > Nick. > -- > ___ > 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] Visual Studio 2008 Express and sqlite3.c
I am trying to compile the latest sqlite 3.7.11 amalgamation C file within a Visual Studio 2008 Express C++ project. sqlite3.c is correctly set as to be compiled as C code but I am unable to find out a way to fix some compilation errors: Compiling... sqlite3.c ..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':' before '!' ..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';' ..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}' ..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct 'LikeOp' ..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have struct/union type ..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!' ..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have struct/union type ..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!' ..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of this type as an expression ..\Sources\sqlite3.c(8133) : see declaration of 'ExprList' ..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have struct/union type ..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few arguments for call ..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!' ..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have struct/union type ..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few arguments for call ..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!' Can someone is able to give me an explanation and a way to fix them? Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger or sql to preserve order
Hello, I have a table defined like: foo (id INTEGER, pid INTEGER, oid INTEGER) id is an unique identification number pid is parent_id oid is order_id and with some data like: id pid oid 74 1 1 42 1 2 28 0 1 1 1 3 17 0 2 16 2 1 9 2 3 3 2 4 10 2 2 within the same pid (parent_id) I need a way to automatically keep oid (order_id) sorted and oid is very volatile, it will change frequently, users will add or remove items and reorder the items. So in this example if an user add a new item with pid 1 at position 2 then the old positions 2 and 3 must be incremented by 1. Trigger should take care of automatically reordering oid within the same pid in case of INSERT, UPDATE and DELETE. Please note that if the trigger is too complicated then I could just execute a smart sql statement every time table foo changes. Anyone have a smart solution? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Alter table: number size
Hi all, I need to alter a field from smallint(1) to smallint(2), is there to do this using ALTER TABLE ? Thanks in advance Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite extensions repository
Hello, I am wondering if there is there a place that collect all available sqlite extensions. Please let me know. Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite extensions repository
Hello, I am wondering if there is there a place that collect all available sqlite extensions. Please let me know. Thanks. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explanation
sqlite 3.7.8 on MacOS X This query works fine: SELECT rowid, * FROM 'customer' WHERE rowid < 100; While this query does not return anything: SELECT rowid, * FROM 'customer' WHERE 'rowid' < 100; Do you have an explanation? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ANN: SQLiteSync
Hello, SQLabs is proud to announce SQLiteSync: a very powerful and easy to use application to compare, merge and keep in sync two sqlite databases. http://www.sqlabs.com/sqlitesync.php Thanks to its underline technology it is able to visualize the differences between millions of rows in few milliseconds. SQLiteSync smart visualization technology is able to detect BLOB columns and very large TEXT columns and display all of them in a preview panel. SQLiteSync is able to apply differences in data, tables, indexes, trigger and views. Synchronization code can be applied directly to the databases or an optional SQL text script can be generated. SQLiteSync is available for both MacOS X and Windows. More information available from: http://www.sqlabs.com/sqlitesync.php Sincerely. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA index_info explanation
Thanks a lot Tom, don't know why this email was delivered even today, it was sent yesterday and Dr. Hipp already replied me. Anyway… thanks a lot for your detailed explanation, I really appreciate. -- Marco Bambini http://www.sqlabs.com On Jan 11, 2012, at 4:06 PM, BareFeetWare wrote: > Hi Marco, > > In reply to: > >> From the PRAGMA index_info documentation: >> This pragma returns one row each column in the named index. The first column >> of the result is the rank of the column within the index. The second column >> of the result is the rank of the column within the table. The third column >> of output is the name of the column being indexed. >> >> I am particularly interested in the rank fields … what does rank really >> means here? >> Is there a lower/upper bound to this rank value? > > If you have an index that is for multiple columns, the "rank of the column > within the index" (which is labeled in the output as "seqno") tells you the > order of those columns in the index. For example: > > create table Person > ( ID integer primary key not null > , "First Name" text collate nocase > , "Last Name" text collate nocase > , "Email" text collate nocase > ) > ; > create index "Person index by First, Last" > on "Person" ("First Name", "Last Name") > ; > create index "Person index by Email, First" > on "Person" ("Email", "First Name") > ; > > Then: > > pragma index_info ('Person index by First, Last') > > gives: > > seqno cid name > 0 1 First Name > 1 2 Last Name > > which means that this indexes columns "First Name" and "Last Name" in that > order (ie 0, 1). > > And > > pragma index_info ('Person index by Email, First') > > gives: > > seqno cid name > 0 3 Email > 1 1 First Name > > which means that this indexes columns "Email" and "First Name" in that order > (ie 0, 1). > > Tom > > Tom Brodhurst-Hill > BareFeetWare > > -- > iPhone/iPad/iPod and Mac software development, specialising in databases > develo...@barefeetware.com > -- > Twitter: http://twitter.com/barefeetware/ > Facebook: http://www.facebook.com/BareFeetWare > > ___ > 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] PRAGMA index_info explanation
>From the PRAGMA index_info documentation: This pragma returns one row each column in the named index. The first column of the result is the rank of the column within the index. The second column of the result is the rank of the column within the table. The third column of output is the name of the column being indexed. I am particularly interested in the rank fields … what does rank really means here? Is there a lower/upper bound to this rank value? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA index_info explanation
>From the PRAGMA index_info documentation: This pragma returns one row each column in the named index. The first column of the result is the rank of the column within the index. The second column of the result is the rank of the column within the table. The third column of output is the name of the column being indexed. I am particularly interested in the rank fields … what does rank really means here? Is there a lower/upper bound to this rank value? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql server management studio like tool for SQLite
Take a look at my SQLiteManager app: http://www.sqlabs.com/sqlitemanager.php -- Marco Bambini http://www.sqlabs.com On Nov 6, 2011, at 7:04 PM, Pete wrote: > Opinions on the best one for OS X? > Pete > > > > > > >> Message: 6 >> Date: Sat, 05 Nov 2011 15:46:36 -0500 >> From: John Horn <pagemeis...@sbcglobal.net> >> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >> Subject: Re: [sqlite] sql server management studio like tool for >> SQLite >> Message-ID: <4eb5a0ac.8050...@sbcglobal.net> >> Content-Type: text/plain; charset=ISO-8859-1; format=flowed >> >> Kit, I've tried many of the tools listed @ >> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools. My hands-down vote >> is for SQLiteExpert Professional @ >> http://sqliteexpert.com/<http://sqliteexpert.com/>. In my opinion >> spending $59 for the Pro version is a **no-brainer** for many reasons. >> >> 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] System.Data.SQLite Exception with wrong password
On 08/10/2011 01:24, Joe Mistachkin wrote: Marco Cosentino wrote: The ErrorCode is set to "NotADatabase". Wouldn't it more correct if this code is set to something like SQLiteErrorCode.Auth or the Exception is more specialized? The exceptions thrown by System.Data.SQLite reflect the underlying error code returned from the native core SQLite library. In the case you mention, the exception is technically correct even though it may seem counterintuitive at first glance. -- Joe Mistachkin Hi Joe, thank you for the reply. I understand the point. But the encryption subsystem should be smart enough to prevent passing an encrypted stream to the native SQLite library (I am assuming that the encryption subsystem operates between the SQLite core library and the OS). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Exception with wrong password
Hi, I'm using a password protected database. When I type the wrong password in my app the resulting Exception is a SQLiteException with this message: "File opened is not a database file". The ErrorCode is set to "NotADatabase". Wouldn't it more correct if this code is set to something like SQLiteErrorCode.Auth or the Exception is more specialized? Thank you Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQLite error message
https://discussions.apple.com/message/15712311#15712311 -- Marco Bambini http://www.sqlabs.com On Sep 5, 2011, at 10:46 PM, Lani Gonzales wrote: > Dear Technical Support: > > Please help me remove this error message: > > The procedure entry point sqlite3_wal_checkpoint could not be located in the > dynamic link library SQlite3.dll > > Please kindly send instructions on how to resolve this problem. > > Thank you, > > Lani Gonzales > ___ > 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] Need Help! -- SQlite database on server
Please take a look at cubeSQL: http://www.sqlabs.com/cubesql.php -- Marco Bambini http://www.sqlabs.com On Aug 4, 2011, at 7:15 PM, Vinoth raj wrote: > Dear SQLite users, > > I have been using SQlite database since three years. Suddenly I have a > requirement for client/server support for my project. > So, the requirement is to save sqlite database on a server from a C++ > application. > I explored the SQLite API with no success. Even numeours queries on the > google did not yield any result. > It would be a great help if you can shed some light on my problem. Is it > possible at all to save SQLite database on a server? > > Eagerly looking forward to your valuable advice. > > Thanks and Regards, > Vinoth > New Delhi, India > ___ > 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] Comments
Why this valid statement: CREATE TABLE USER( id text, -- the id of the user nametext-- the name of the user ); gives me a syntax error with sqlite 3.7.6.3? Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] year, month & day problem
Works.. Im really sorry for my stupid question, thanks for support and your time, i think that i need an holyday.. Marco Il giorno domenica 17 luglio 2011, Black, Michael (IS) <michael.bla...@ngc.com> ha scritto: > Column numbers are zero-based, not one-based. > > From http://www.sqlite.org/c3ref/column_blob.html > > "The leftmost column of the result set has the index 0. " > > > > That's at least part of your problem. > > > > So should be: > > k.AAArticoli=sqlite3_column_int(statmentS, 0); > k.MMArticoli=sqlite3_column_int(statmentS, 1); > k.GGArticoli=sqlite3_column_int(statmentS, 2); > > And can't you run the command-line sqlite3 to see what's in your table? > > > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > ____ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of marco bianchini [informa...@gmail.com] > Sent: Sunday, July 17, 2011 7:05 AM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] year, month & day problem > > if can help, thats real code : > > -- to read > > -(void) readUpdateStatus{ > const char *sql="select > AAArticoli,MMArticoli,GGArticoli,AAFoto,MMFoto,GGFoto,AAClienti,MMClienti,GGClienti,AAOrdini,MMOrdini,GGOrdini,AA,MM,GG > from settings"; > > > sqlite3_stmt *statmentS; > if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK) > { > if (sqlite3_step(statmentS)==SQLITE_ROW) { > UserSettings *k = [UserSettings sharedUserSettings]; > k.AAArticoli=sqlite3_column_int(statmentS, 1); > k.MMArticoli=sqlite3_column_int(statmentS, 2); > k.GGArticoli=sqlite3_column_int(statmentS, 3); > > k.AAFoto=sqlite3_column_int(statmentS, 4); > k.MMFoto=sqlite3_column_int(statmentS, 5); > k.GGFoto=sqlite3_column_int(statmentS, 6); > > k.AAClienti=sqlite3_column_int(statmentS, 7); > k.MMClienti=sqlite3_column_int(statmentS, 8); > k.GGClienti=sqlite3_column_int(statmentS, 9); > > k.AAOrdini=sqlite3_column_int(statmentS, 10); > k.MMOrdini=sqlite3_column_int(statmentS, 11); > k.GGOrdini=sqlite3_column_int(statmentS, 12); > > k.AA=sqlite3_column_int(statmentS, 13); > k.MM=sqlite3_column_int(statmentS, 14); > k.GG=sqlite3_column_int(statmentS, 15); > > } > sqlite3_finalize(statmentS); > } > return; > } > > > -- to write > > -(int) ioSQL:(NSString*)sql{ > sqlite3_stmt *statment; > if (sqlite3_prepare_v2(database,[sql UTF8String],-1,, > NULL)==SQLITE_OK) { > if (sqlite3_step(statment)==SQLITE_DONE) { > sqlite3_finalize(statment); > return 1; > } > } > return 0; > } > > -(int) setUpdated{ > return [self ioSQL:@"update settings set AA=strftime('%Y', 'now'), > MM=strftime('%m', 'now'), GG=strftime('%d', 'now')"]; > } > > -(int) setArticoliUpdated{ > return [self ioSQL:@"update settings set AAArticoli=strftime(\"%Y\", > \"now\"), MMArticoli=strftime(\"%m\", \"now\"), GGArticoli=strftime(\"%d\", > \"now\")"]; > } > -(int) setFotoUpdated{ > return [self ioSQL:@"update settings set AAFoto=strftime(\"%Y\", > \"now\"), MMFoto=strftime(\"%m\", \"now\"), GGFoto=strftime(\"%d\", > \"now\")"]; > } > -(int) setClientiUpdated{ > return [self ioSQL:@"update settings set AAClienti=strftime(\"%Y\", > \"now\"), MMClienti=strftime(\"%m\", \"now\"), GGClienti=strftime(\"%d\", > \"now\")"]; > } > -(int) setOrdiniUpdated{ > return [self ioSQL:@"update settings set AAOrdini=strftime(\"%Y\", > \"now\"), MMOrdini=strftime(\"%m\", \"now\"), GGOrdini=strftime(\"%d\", > \"now\")"]; > } > > > > > > > > 2011/7/17 marco bianchini <informa...@gmail.com> > >> you'r right, i made a mistake doing copy and paste writing original mail, >> the real select query contains more fields and i can ensure that 1 based >> indexes are correct and respected into the real code, real update query is >> hardcoded, no params (0 based): 2 days checking, im sure.. >> >> at least, do u know a way to load the copied writable database running into >> the XCode simulator, so i can understand if my problem is writing or >> retriving data (in this way 50% my troubles are solved)? >> >> Using Mac and windows against ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] year, month & day problem
if can help, thats real code : -- to read -(void) readUpdateStatus{ const char *sql="select AAArticoli,MMArticoli,GGArticoli,AAFoto,MMFoto,GGFoto,AAClienti,MMClienti,GGClienti,AAOrdini,MMOrdini,GGOrdini,AA,MM,GG from settings"; sqlite3_stmt *statmentS; if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK) { if (sqlite3_step(statmentS)==SQLITE_ROW) { UserSettings *k = [UserSettings sharedUserSettings]; k.AAArticoli=sqlite3_column_int(statmentS, 1); k.MMArticoli=sqlite3_column_int(statmentS, 2); k.GGArticoli=sqlite3_column_int(statmentS, 3); k.AAFoto=sqlite3_column_int(statmentS, 4); k.MMFoto=sqlite3_column_int(statmentS, 5); k.GGFoto=sqlite3_column_int(statmentS, 6); k.AAClienti=sqlite3_column_int(statmentS, 7); k.MMClienti=sqlite3_column_int(statmentS, 8); k.GGClienti=sqlite3_column_int(statmentS, 9); k.AAOrdini=sqlite3_column_int(statmentS, 10); k.MMOrdini=sqlite3_column_int(statmentS, 11); k.GGOrdini=sqlite3_column_int(statmentS, 12); k.AA=sqlite3_column_int(statmentS, 13); k.MM=sqlite3_column_int(statmentS, 14); k.GG=sqlite3_column_int(statmentS, 15); } sqlite3_finalize(statmentS); } return; } -- to write -(int) ioSQL:(NSString*)sql{ sqlite3_stmt *statment; if (sqlite3_prepare_v2(database,[sql UTF8String],-1,, NULL)==SQLITE_OK) { if (sqlite3_step(statment)==SQLITE_DONE) { sqlite3_finalize(statment); return 1; } } return 0; } -(int) setUpdated{ return [self ioSQL:@"update settings set AA=strftime('%Y', 'now'), MM=strftime('%m', 'now'), GG=strftime('%d', 'now')"]; } -(int) setArticoliUpdated{ return [self ioSQL:@"update settings set AAArticoli=strftime(\"%Y\", \"now\"), MMArticoli=strftime(\"%m\", \"now\"), GGArticoli=strftime(\"%d\", \"now\")"]; } -(int) setFotoUpdated{ return [self ioSQL:@"update settings set AAFoto=strftime(\"%Y\", \"now\"), MMFoto=strftime(\"%m\", \"now\"), GGFoto=strftime(\"%d\", \"now\")"]; } -(int) setClientiUpdated{ return [self ioSQL:@"update settings set AAClienti=strftime(\"%Y\", \"now\"), MMClienti=strftime(\"%m\", \"now\"), GGClienti=strftime(\"%d\", \"now\")"]; } -(int) setOrdiniUpdated{ return [self ioSQL:@"update settings set AAOrdini=strftime(\"%Y\", \"now\"), MMOrdini=strftime(\"%m\", \"now\"), GGOrdini=strftime(\"%d\", \"now\")"]; } 2011/7/17 marco bianchini <informa...@gmail.com> > you'r right, i made a mistake doing copy and paste writing original mail, > the real select query contains more fields and i can ensure that 1 based > indexes are correct and respected into the real code, real update query is > hardcoded, no params (0 based): 2 days checking, im sure.. > > at least, do u know a way to load the copied writable database running into > the XCode simulator, so i can understand if my problem is writing or > retriving data (in this way 50% my troubles are solved)? > > Using Mac and windows against the same database to simulate the same query > everithing works perfectly.. im really getting crazy. > i know i can close my eyes and invert that commands, but i care my software > and i really like to know what im doing wrong.. > > thx for reply, marco > > > 2011/7/17 John Deal <bassd...@yahoo.com> > >> Hello, >> >> I am new and have received much information from this list so I hope I am >> not wasting bandwidth. I don't know if it is my misunderstanding or typos >> but should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12 >> and 13? If this is the case, according to the docs on sqlite3_column_int() >> "...if the column index is out of range, the result is undefined." >> >> I hope I did not misunderstand the issue. >> >> --- On Sun, 7/17/11, marco bianchini <informa...@gmail.com> wrote: >> >> > From: marco bianchini <informa...@gmail.com> >> > Subject: [sqlite] year, month & day problem >> > To: sqlite-users@sqlite.org >> > Date: Sunday, July 17, 2011, 6:05 AM >> > Hi all, >> > call me stupid but after some days of try and a lot of >> > Googleing, im still >> > wondering how to solve my problem: >> > i need to execute a query that updates 3 integer fields >> > (AA, MM, GG) of a >> > table, containing respectively today year, today month and >> > tod
Re: [sqlite] year, month & day problem
you'r right, i made a mistake doing copy and paste writing original mail, the real select query contains more fields and i can ensure that 1 based indexes are correct and respected into the real code, real update query is hardcoded, no params (0 based): 2 days checking, im sure.. at least, do u know a way to load the copied writable database running into the XCode simulator, so i can understand if my problem is writing or retriving data (in this way 50% my troubles are solved)? Using Mac and windows against the same database to simulate the same query everithing works perfectly.. im really getting crazy. i know i can close my eyes and invert that commands, but i care my software and i really like to know what im doing wrong.. thx for reply, marco 2011/7/17 John Deal <bassd...@yahoo.com> > Hello, > > I am new and have received much information from this list so I hope I am > not wasting bandwidth. I don't know if it is my misunderstanding or typos > but should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12 > and 13? If this is the case, according to the docs on sqlite3_column_int() > "...if the column index is out of range, the result is undefined." > > I hope I did not misunderstand the issue. > > --- On Sun, 7/17/11, marco bianchini <informa...@gmail.com> wrote: > > > From: marco bianchini <informa...@gmail.com> > > Subject: [sqlite] year, month & day problem > > To: sqlite-users@sqlite.org > > Date: Sunday, July 17, 2011, 6:05 AM > > Hi all, > > call me stupid but after some days of try and a lot of > > Googleing, im still > > wondering how to solve my problem: > > i need to execute a query that updates 3 integer fields > > (AA, MM, GG) of a > > table, containing respectively today year, today month and > > today day: > > > > update settings set AA=strftime('%Y', 'now'), > > MM=strftime('%m', 'now'), > > GG=strftime('%d', 'now') > > > > looks working well, but later, when i read that values: > > > > const char *sql="select AA,MM,GG from > > settings"; > > sqlite3_stmt *statmentS; > > if (sqlite3_prepare_v2(database, sql > > ,-1,, NULL)==SQLITE_OK) > > { > > if > > (sqlite3_step(statmentS)==SQLITE_ROW) { > > UserSettings *k = > > [UserSettings sharedUserSettings]; > > > > k.AA=sqlite3_column_int(statmentS, 1); > > > > k.MM=sqlite3_column_int(statmentS, 12); > > > > k.GG=sqlite3_column_int(statmentS, 13); > > ... > > > > > > i obtain correct values, but in inverse order: > > > > AA (year) contains the day number > > MM (month) is correct > > GG (day) contains the year > > > > using SQLIte Manager addons for Firefox, this query: > > select strftime('%Y', 'now'), strftime("%m", "now"), > > strftime("%d", "now") > > from settings > > returns correct values, running or loading value into XCode > > simulator looks > > not working and i dont know why. :'( > > > > > > does anyone can suggest me what to fix or check? > > thx in advance, marco > > ___ > > 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
[sqlite] year, month & day problem
Hi all, call me stupid but after some days of try and a lot of Googleing, im still wondering how to solve my problem: i need to execute a query that updates 3 integer fields (AA, MM, GG) of a table, containing respectively today year, today month and today day: update settings set AA=strftime('%Y', 'now'), MM=strftime('%m', 'now'), GG=strftime('%d', 'now') looks working well, but later, when i read that values: const char *sql="select AA,MM,GG from settings"; sqlite3_stmt *statmentS; if (sqlite3_prepare_v2(database, sql ,-1,, NULL)==SQLITE_OK) { if (sqlite3_step(statmentS)==SQLITE_ROW) { UserSettings *k = [UserSettings sharedUserSettings]; k.AA=sqlite3_column_int(statmentS, 1); k.MM=sqlite3_column_int(statmentS, 12); k.GG=sqlite3_column_int(statmentS, 13); ... i obtain correct values, but in inverse order: AA (year) contains the day number MM (month) is correct GG (day) contains the year using SQLIte Manager addons for Firefox, this query: select strftime('%Y', 'now'), strftime("%m", "now"), strftime("%d", "now") from settings returns correct values, running or loading value into XCode simulator looks not working and i dont know why. :'( does anyone can suggest me what to fix or check? thx in advance, marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help
Thanks a lot Igor, it's perfect now. -- Marco Bambini http://www.sqlabs.com On Jun 12, 2011, at 4:46 PM, Igor Tandetnik wrote: > Marco Bambini <ma...@sqlabs.net> wrote: >> I have a table Clients defined as (simplified version): >> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity >> TEXT, ping_timeout INTEGR); >> >> ping_timeout was a global property so in order to get a list of all clients >> timedout I used a query like (C code): >> snprintf(sql, sizeof(sql), "select id from Clients where last_activity < >> datetime('now', '-%d.00 seconds', 'localtime');", >> settings.ping_timeout); >> >> things are recently changed in my app and ping_timeout is now a client >> property set inside the Clients table (and no longer a >> global property), so I would like to perform the query using just the >> ping_timeout column in the Clients table instead of the >> settings.ping_timeout global property. > > select id from Clients where last_activity < datetime('now', -ping_timeout || > ' seconds', 'localtime'); > -- or > select id from Clients where >(julianday('now', 'localtime') - julianday(last_activity)) * 86400 > > ping_timeout; > > -- > Igor Tandetnik > > ___ > 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] Query help
No I am sorry but I need to query the ping_timeout column from inside the same query. -- Marco Bambini http://www.sqlabs.com On Jun 12, 2011, at 2:02 PM, Roger Andersson wrote: > On 06/12/11 01:52 PM, Marco Bambini wrote: >> things are recently changed in my app and ping_timeout is now a client >> property set inside the Clients table (and no longer a global property), so >> I would like to perform the query: >> snprintf(sql, sizeof(sql), "select id from Clients where last_activity< >> datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout); >> using just the ping_timeout column in the Clients table instead of the >> settings.ping_timeout global property. >> >> Any idea? > snprintf(sql, sizeof(sql), "select id from Clients where last_activity < > datetime('now', '-%d.00 seconds', 'localtime');", ping_timeout); > > ___ > 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] Query help
Hello guys, I have a table Clients defined as (simplified version): CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity TEXT, ping_timeout INTEGR); each time a client performs an operation the last_activity column is updated with: UPDATE Clients SET last_activity=datetime('now','localtime') WHERE id=N; ping_timeout was a global property so in order to get a list of all clients timedout I used a query like (C code): snprintf(sql, sizeof(sql), "select id from Clients where last_activity < datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout); things are recently changed in my app and ping_timeout is now a client property set inside the Clients table (and no longer a global property), so I would like to perform the query: snprintf(sql, sizeof(sql), "select id from Clients where last_activity < datetime('now', '-%d.00 seconds', 'localtime');", settings.ping_timeout); using just the ping_timeout column in the Clients table instead of the settings.ping_timeout global property. Any idea? -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to know the offset of a rowid inside a table?
Thanks Martin and Richard, solution was so simple that I think to need a time break today. Should I add an ORDER BY rowid clause at the end of the SELECT statement or its implicit by the WHERE clause? Thanks a lot. -- Marco Bambini http://www.sqlabs.com On Jun 8, 2011, at 4:27 PM, Richard Hipp wrote: > On Wed, Jun 8, 2011 at 10:18 AM, Marco Bambini <ma...@sqlabs.net> wrote: > >> I have a table foo with N rows and I want to know the offset of the row >> with rowid X inside that table. >> What query/strategy should I perform? >> >> I assume that a brute force algorithm should be >> 1. SELECT * FROM foo ORDER BY rowid; >> 2. loop inside the recordset until X is found incrementing a counter by 1 >> but I am quite sure that should be a more elegant way. >> >> > SELECT count(*) FROM foo WHERE rowid<=X > > The above gives an answer in linear time. It is theoretically possible to > add a little extra metadata to the btree nodes in order to compute the > offset logarithmic time. But I deliberately decided not to included that > metadata when I designed the SQLite btree file format since keeping that > metadata current slows down write performance. > > > >> Thanks a lot. >> -- >> Marco Bambini >> http://www.sqlabs.com >> >> >> >> >> >> >> >> >> ___ >> 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
[sqlite] How to know the offset of a rowid inside a table?
I have a table foo with N rows and I want to know the offset of the row with rowid X inside that table. What query/strategy should I perform? I assume that a brute force algorithm should be 1. SELECT * FROM foo ORDER BY rowid; 2. loop inside the recordset until X is found incrementing a counter by 1 but I am quite sure that should be a more elegant way. Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proper way to escape table name and column name
What is the official way to escape table name that contains a space and column name that contain a spaces? Thanks a lot. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SEE encryption lib and odbc
Hi all, We are successfully using the SEE module in Sqlite. All runs well but we need to provide a ODBC connection to a limited number of users. Question: is there a Sqlite odbc driver SEE compatible ? Thanks. Regards, Marco Turco IT Business Devl Manager Software XP LLP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] cubeSQL
Viadana, Italy - SQLabs announced cubeSQL a fully featured and high performance relational database management system built on top of the sqlite database engine. It is the ideal database server for both developers who want to convert a single user database solution to a multiuser project and for companies looking for an affordable, easy to use and easy to maintain database management system. cubeSQL runs on Windows, Mac, Linux and it can be embedded into any iOS and Cocoa application. cubeSQL is incredibly fast, has a small footprint, is highly reliable and it offers some unique features. It can be easily accessed with any JSON client, with PHP, with the native C SDK and with an highly optimized REAL Studio plugin. Some features includes: - Multi-core and multiprocessor aware. - Strong AES encryption (128, 192 and 256 bit). - Supports unlimited connections. - Full ACID (Atomic, Consistent, Isolated, Durable) compliant. - Platform independent storage engine. - Full support of triggers and transactions. - Journal engine for crash recovery. - Supports databases of 2 terabytes. - Supports sqlite 3 databases. - Automatic logging. - Automatic compression. - Multiversion concurrency control (MVCC). - Plugins for extending the SQL language and the custom commands supported by the server. - Restore and backup support. - Mac OS X, Windows and Linux support. - Native 32bit and 64bit supports. ... and much more Minimum requirements: * MacOS X 10.5 or higher * Windows NT/XP/Vista/7/Server or higher * Linux kernel 2.6.2 or higher Pricing and Availability: cubeSQL Developer Edition is completely free of charge and enables developers to create an application based on cubeSQL without paying any fees until they are ready to deploy their application. Commercial license starts at $299 USD. For more information, please visit the SQLabs website: http://www.sqlabs.com -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users