[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] 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] "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] 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] 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] 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] 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
[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] 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] 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
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] 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] 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] 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] [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
Re: [sqlite] Using SQLite with R*Tree and FTS3 support in iOS
4.3 MB seems really too big... you are probably building a debug version of the library. -- Marco Bambini http://www.sqlabs.com On May 16, 2011, at 5:01 PM, Tito Ciuro wrote: > Hello, > > I have a question about SQLite running on iOS. If I'm not mistaken, SQLite on > iOS is not compiled with R*Tree and FTS3. Compiling a static library of > SQLite's amalgamated version weighs at about 4.3 MB, which represents almost > 25% of the 20 MB-per-app allowed on the App Store. For many, this is a major > setback because many apps can easily reach this limit. > > My question is: since a "light" version of SQLite is already included in iOS, > would it be too complicated to build a static library with only R*Tree and > FTS3 support? The idea being of course that the app would link against iOS' > SQLite and the app's R*Tree/FTS3 library, thus reducing the app's footprint > considerably. > > Are there dependencies that would make this attempt a nightmare? Has anyone > gone through this? > > Thanks in advance, > > -- Tito > ___ > 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] vacuum and rowids
Dave please take a look at this blog post: http://www.sqlabs.com/blog/?p=51 -- Marco Bambini http://www.sqlabs.com On Apr 28, 2011, at 9:36 PM, Dave Hayden wrote: > When the VACUUM feature was added I took a look at using it to keep database > file sizes down, but discovered that it changed rowids and messed up my > references between tables (or what I gather the database people call "foreign > keys"). I'm playing around with this again and it looks like rowids aren't > affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild > the existing tables if I don't have to. > > After more poking, it appears that rowids might not be changed by a vacuum if > I have an index on the table. Is this true? If so, is it something I can rely > on going forward? > > Thanks! > -Dave > > ___ > 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] Help with a query
Thanks a lot Simon and Robert. -- Marco Bambini http://www.sqlabs.com On Mar 4, 2011, at 11:50 AM, Robert Hairgrove wrote: > On Fri, 2011-03-04 at 11:10 +0100, Marco Bambini wrote: >> Hello, >> I have a table defined as: >> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id >> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) >> >> In that table there some rows like: >> obj_id prop_keyprop_value >> 1PARENTID0 >> 1RESOURCE_ORDER 0 >> 2PARENTID0 >> 2RESOURCE_ORDER 1 >> 3PARENTID0 >> 3RESOURCE_ORDER 3 >> >> I need a query that returns all the obj_id with prop_key='PARENTID' AND >> prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. >> Any help? > > Sounds like a job for a self-join. Try this: > > SELECT T1.obj_id, T2.prop_value > FROM MKProperties T1 > INNER JOIN MKProperties T2 > ON (T1.obj_id = T2.obj_id) > WHERE T2.prop_key = 'RESOURCE_ORDER' > AND T1.prop_key = 'PARENT_ID' > AND T1.prop_value = 0 > ORDER BY T2.prop_value; > > > > ___ > 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] Help with a query
Hello, I have a table defined as: CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) In that table there some rows like: obj_id prop_keyprop_value 1 PARENTID0 1 RESOURCE_ORDER 0 2 PARENTID0 2 RESOURCE_ORDER 1 3 PARENTID0 3 RESOURCE_ORDER 3 I need a query that returns all the obj_id with prop_key='PARENTID' AND prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. Any help? -- 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] memory used by sqlite library
Michele take a look at the sqlite3_status function: http://www.sqlite.org/c3ref/status.html and http://www.sqlite.org/c3ref/c_status_malloc_count.html -- Marco Bambini http://www.sqlabs.com On Feb 25, 2011, at 2:17 PM, Michele Pradella wrote: > Do you know if is there a way to ask to the sqlite library the amount of > memory that is using? > It could be useful when I have to take a look to the memory used in my > application. So I can distinguish between memory allocated by my > application itself and allocated by sqlite library. > It's possible to set up a maximum amount of memory that the library can use? > > -- > Selea s.r.l. > > >Michele Pradella R > > >SELEA s.r.l. > > Via Aldo Moro 69 > Italy - 46019 Cicognara (MN) > Tel +39 0375 889091 > Fax +39 0375 889080 > *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> > *http://www.selea.com* > ___ > 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
Thanks Igor and thanks Martin, I need to add both the id and the other properties to an hash table (a Cocoa NSDictionary) so I needed a way to have a key, value representation that includes also the id. I solved the problem with 2 queries and some Cocoa code. I don't like complex queries and 2 simple queries is a better approach for the maintainability of the project. Thanks a lot for your advices. -- Marco Bambini http://www.sqlabs.com On Feb 2, 2011, at 8:11 PM, Igor Tandetnik wrote: > On 2/2/2011 11:16 AM, Marco Bambini wrote: >> your query returns 3 columns, but I need just two columns (key, value for >> example). > > Why? You have all the information you need, just in a slightly different > (and, arguably, easier to use) form. > >> The first row should be the label 'ID' and the id of the MKObjects followed >> by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. > > Why should it? Why exactly do you insist on this format? > > What should happen, in your proposed representation, when there is more > than row in MKObjects, each with its own set of properties? > -- > 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
Hello Igor, your query returns 3 columns, but I need just two columns (key, value for example). The first row should be the label 'ID' and the id of the MKObjects followed by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. For example MKObjects contains (1,IPHONE,PANEL,0) and MKProperties contains (1,1,NAME,About Box) and (2,1,WIDTH,200) the result of the query should should be: col1col2 'ID'1 'NAME' 'About Box' 'WIDTH', '200' -- Marco Bambini http://www.sqlabs.com On Feb 2, 2011, at 1:43 PM, Igor Tandetnik wrote: > Marco Bambini <ma...@sqlabs.net> wrote: >> Hello, I have two tables defined as: >> >> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, >> type TEXT, parent_id INTEGER DEFAULT 0); >> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id >> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, >> prop_key)); >> >> I need to create a query that returns 2 columns key, value (column names are >> not important) where the first row is the label 'ID' >> with value id from MKObjects and the other rows are the columns prop_key, >> prop_value from MKProperties where obj_id= MKObjects.id >> satisfying a WHERE condition. > > You don't really need, or want, to create a query like that. It goes against > the grain of SQL. You want this: > > select o.id, prop_key, prop_value > from MKObjects o join MKProperties p on (o.id = p.obj_id) > where type='PANEL' AND platform='IPHONE' > order by o.id; > > When formatting your report, output a section heading whenever id column > changes from previous row. > -- > 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] Query help
Hello, I have two tables defined as: CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, type TEXT, parent_id INTEGER DEFAULT 0); CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)); I need to create a query that returns 2 columns key, value (column names are not important) where the first row is the label 'ID' with value id from MKObjects and the other rows are the columns prop_key, prop_value from MKProperties where obj_id= MKObjects.id satisfying a WHERE condition. So far I am using a query like: SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id); but I am wondering if there is a better way (without using 3 select statements). 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] TEMP TRIGGER and SQLITE_OMIT_TEMPDB
I am trying to use TEMP TRIGGER inside my code but every time I use it I receive a "SQL logic error or missing database" error. I compiled sqlite with the option SQLITE_OMIT_TEMPDB. but analyzing the source code I can see for example: #ifndef SQLITE_OMIT_TRIGGER /* If there are TEMP triggers on this table, modify the sqlite_temp_master ** table. Don't do this if the table being ALTERed is itself located in ** the temp database. */ if( (zWhere=whereTempTriggers(pParse, pTab))!=0 ){ sqlite3NestedParse(pParse, "UPDATE sqlite_temp_master SET " "sql = sqlite_rename_trigger(sql, %Q), " "tbl_name = %Q " "WHERE %s;", zName, zName, zWhere); sqlite3DbFree(db, zWhere); } #endif so sqlite_temp_master is necessary in order to use TEMP TRIGGER and so I can use TEMP TRIGGER only if I recompile sqlite without the SQLITE_OMIT_TEMPDB macro. Anyone can confirm my assumption? 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] CREATE TEMP TRIGGER
Where I can find more information about TEMP TRIGGERs? In the official page: http://www.sqlite.org/lang_createtrigger.html there is no mention about the TEMP clause. 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] SQLiteConverter
SQLabs today is pleased to announce SQLiteConverter, the fastest and easiest way to convert your mySQL, PostgreSQL, Oracle (natively) and a wide range of ODBC compliant databases (like Microsoft SQL Server, Access, FoxPro and many others) to sqlite. It combines a very intuitive interface with powerful features so you can convert an existing remotely hosted database to sqlite in few steps. Thanks to a very intuitive wizard you'll be guide step by step to 5 easy operations in order to completely convert your remote databases. The entire process depends on how much data you need to convert but most of the time you'll be able to complete it in few seconds. SQLiteConverter is the ideal tool not only to complete a conversion between different databases to sqlite but it is also the ideal solution to backup your remote data to another disk based relational sql database. You always have full control over the conversion process, you can decide to convert the entire database or just select the individual tables you want to backup. More information available from: http://www.sqlabs.com/sqliteconverter.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] assert crash in wal
Try to add: -DSQLITE_THREADSAFE =1 to your compilation options. -- Marco Bambini http://www.sqlabs.com On Dec 15, 2010, at 2:34 PM, Yoni Londner wrote: > Hi, > > I wrote a little program that insert in a loop rows in to the DB, and in > another thread run wal_checkpoint. > After few minutes (6-7) I get (consistently) the following assert error: > > sqlite_test: ..//src/wal.c:1364: walMerge: Assertion `iLeft>=nLeft || > aContent[aLeft[iLeft]]>dbpage' failed. > > I compiled sqlite from fossil, with -DSQLITE_DEBUG and -DSQLITE_TEST > I pasted below the program and the stacks. > Am I doing something wrong? > > Yoni. > > (gdb) info threads > 2 Thread 26132 0x0804ed3b in pthreadMutexEnter (p=0x8103068) > at ..//src/mutex_unix.c:238 > * 1 Thread 26133 0xb7734424 in __kernel_vsyscall () > (gdb) bt > #0 0xb7734424 in __kernel_vsyscall () > #1 0xb75e2640 in raise () from /lib/i686/cmov/libc.so.6 > #2 0xb75e4018 in abort () from /lib/i686/cmov/libc.so.6 > #3 0xb75db5be in __assert_fail () from /lib/i686/cmov/libc.so.6 > #4 0x080776a8 in walMerge (aContent=0xb7729088, aLeft=0x96157b4, nLeft=2, > paRight=0xb759a898, pnRight=0xb759a89c, aTmp=0x9619cb8) at > ..//src/wal.c:1364 > #5 0x080778a0 in walMergesort (aContent=0xb7729088, aBuffer=0x9619cb8, > aList=0x9614654, pnList=0xb759a8f0) at ..//src/wal.c:1405 > #6 0x08077c63 in walIteratorInit (pWal=0x93f5c60, pp=0xb759a984) > at ..//src/wal.c:1510 > #7 0x08077d8b in walCheckpoint (pWal=0x93f5c60, sync_flags=2, nBuf=1024, > zBuf=0x93c3968 "\r") at ..//src/wal.c:1579 > #8 0x08079c0b in sqlite3WalCheckpoint (pWal=0x93f5c60, sync_flags=2, > nBuf=1024, > zBuf=0x93c3968 "\r") at ..//src/wal.c:2647 > #9 0x0805de51 in sqlite3PagerCheckpoint (pPager=0x93c2f08) at > ..//src/pager.c:6558 > #10 0x0809e68d in sqlite3BtreeCheckpoint (p=0x93c17a8) at > ..//src/btree.c:7953 > #11 0x0804b8fb in sqlite3Checkpoint (db=0x93c0af0, iDb=10) at > ..//src/main.c:1402 > #12 0x080dd80d in sqlite3VdbeExec (p=0x93c36d8) at ..//src/vdbe.c:5225 > #13 0x0806b932 in sqlite3Step (p=0x93c36d8) at ..//src/vdbeapi.c:394 > #14 0x0806bba9 in sqlite3_step (pStmt=0x93c36d8) at ..//src/vdbeapi.c:458 > #15 0x080497af in sqlite3_exec (db=0x93c0af0, zSql=0x80e3e75 "PRAGMA > wal_checkpoint", > xCallback=0, pArg=0x0, pzErrMsg=0xb759b38c) at ..//src/legacy.c:70 > #16 0x0804937b in _sql_exec (conn=0x93c0af0, query=0x80e3e75 "PRAGMA > wal_checkpoint", > fail_if_locked=0) at sqlite_large_wal.c:52 > #17 0x080494d8 in thread_do () at sqlite_large_wal.c:104 > #18 0xb75a34c0 in start_thread () from /lib/i686/cmov/libpthread.so.0 > #19 0xb769784e in clone () from /lib/i686/cmov/libc.so.6 > (gdb) thread 2 > [Switching to thread 2 (Thread 26132)]#0 0x0804ed3b in pthreadMutexEnter ( > p=0x8103068) at ..//src/mutex_unix.c:238 > 238 assert( p->nRef>0 || p->owner==0 ); > (gdb) bt > #0 0x0804ed3b in pthreadMutexEnter (p=0x8103068) at > ..//src/mutex_unix.c:238 > #1 0x0804e6db in sqlite3_mutex_enter (p=0x8103068) at ..//src/mutex.c:112 > #2 0x0804da91 in sqlite3_free (p=0x958de28) at ..//src/malloc.c:470 > #3 0x0804dbcf in sqlite3DbFree (db=0x93b4068, p=0x958de28) at > ..//src/malloc.c:503 > #4 0x0806f289 in releaseMemArray (p=0x96130d8, N=6) at > ..//src/vdbeaux.c:1018 > #5 0x0807021f in closeAllCursors (p=0x94df2b8) at ..//src/vdbeaux.c:1538 > #6 0x08070f21 in sqlite3VdbeHalt (p=0x94df2b8) at ..//src/vdbeaux.c:2042 > #7 0x080d1991 in sqlite3VdbeExec (p=0x94df2b8) at ..//src/vdbe.c:861 > #8 0x0806b932 in sqlite3Step (p=0x94df2b8) at ..//src/vdbeapi.c:394 > #9 0x0806bba9 in sqlite3_step (pStmt=0x94df2b8) at ..//src/vdbeapi.c:458 > #10 0x080497af in sqlite3_exec (db=0x93b4068, > zSql=0x80e3e24 "INSERT INTO tbl1 values('", 'a' , > "', '", 'b' , "')", xCallback=0, pArg=0x0, > pzErrMsg=0xbf9dd57c) > at ..//src/legacy.c:70 > #11 0x0804937b in _sql_exec (conn=0x93b4068, > query=0x80e3e24 "INSERT INTO tbl1 values('", 'a' times>, "', '", 'b' , "')", fail_if_locked=1) at > sqlite_large_wal.c:52 > #12 0x080493ed in sql_exec (conn=0x93b4068, > query=0x80e3e24 "INSERT INTO tbl1 values('", 'a' times>, "', '", 'b' , "')") at sqlite_large_wal.c:64 > #13 0x0804945b in do_insert (conn=0x93b4068) at sqlite_large_wal.c:85 > #14 0x08049663 in main (argc=, argv= optimized out>) > at sqlite_large_wal.c:139 > (gdb) > > > #include "sqlite3.h" > #include "stdio.h" > #include "stdlib.h" > #include "fcntl.h" > #include "errno.h" > > #define NSEC_PER_MS 10
Re: [sqlite] sqlite3_analyzer issue on MacOS X 10.6.5
I just tried it on my Mac and this new build works pretty well. Thanks a lot for your assistance. -- Marco Bambini http://www.sqlabs.com On Dec 8, 2010, at 1:54 AM, Richard Hipp wrote: > In Tue, Dec 7, 2010 at 7:15 PM, Richard Hipp <d...@sqlite.org> wrote: >> On Tue, Dec 7, 2010 at 6:22 PM, Marco Bambini <ma...@sqlabs.net> wrote: >>> Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue: >>> >>> dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib >>> Referenced from: /Users/marco/Desktop/sqlite3_analyzer >>> Reason: image not found >>> Trace/BPT trap >>> >>> Seems like a broken binary to me. >>> Any idea? >> >> I don't know how to statically link the TCL libraries on a Mac. I >> tried every combination of options I could think of and none of them >> seem to work. >> >> I think you just have to install TCL on your Mac in order to use >> sqlite3_analyzer there. Bummer. > > I finally figured out how to statically link TCL on a Mac (you have to > add "-framework CoreFoundation" to the compiler command-line) I > rebuilt using this recipe and put up a new image. Please download the > latest and try again. > > >> >> >>> -- >>> 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 >> > > > > -- > 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_analyzer issue on MacOS X 10.6.5
Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue: dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib Referenced from: /Users/marco/Desktop/sqlite3_analyzer Reason: image not found Trace/BPT trap Seems like a broken binary to me. 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
[sqlite] Date/Time query help
I have a table like: CREATE TABLE foo (id integer PRIMARY KEY AUTOINCREMENT,connection_date text); and I insert data into this table using the syntax: INSERT INTO foo (connection_date) VALUES (datetime('now','localtime')); INSERT INTO foo (connection_date) VALUES (datetime('now','localtime')); I really need to use localtime and after the two simple INSERT above my table contains rows like: id connection_date 1 2010-12-07 14:39:43 2 2010-12-07 14:39:59 I need to create a query that is able to retrieve all the id(s) from foo where connection_date is older than 5 minutes starting from now (in localtime). 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] WAL mode and backup API
Hello, if a running sqlite database is in WAL mode and a backup is performed on that db using the sqlite3_backup API, does that process is considered like a reader and can proceed concurrently with other readers and with the other writer? Thanks a lot for your answer. -- 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] SQLiteConverter
Viadana, Italy - SQLabs announced SQLiteConverter, the fastest and easier way to convert your mySQL, PostgreSQL and Oracle database to sqlite. It combines a very intuitive interface with very powerful features so you can convert an existing remotely hosted database to sqlite in few time. Thanks to a very intuitive wizard you'll be guide step by step to 5 easy operations in order to completely convert your remote databases. The entire process obviously depends on how much data you need to convert but most of the time you'll be able to complete it in few seconds. SQLiteConverter is the ideal tool not only to complete a conversion between different DBMS to sqlite but it is also the ideal solution to backup your remote data to another disk based relational sql database. You always have full control over the conversion process, you can decide to convert the entire database or just select the individual tables you want to backup. New databases data sources will be added in the future as a free upgrade! More information available at: http://www.sqlabs.com/sqliteconverter.php Minimum requirements: * MacOS X 10.4 or higher * Windows 2000/NT/XP/Vista/7 A SQLiteConverter single license is $49 USD. Company and multiplatform licenses are also available. For more information, please visit the SQLabs website. -- 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] Strange error
I have a strange error with sqlite 3.6.23.1, does anyone have an explanation? CREATE TABLE 'tblMoneyFlow' ( 'id' INTEGER DEFAULT '0' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 'RekeningNrEigenaar' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount' ('idAccountNr'), 'Valuta' VARCHAR(4), 'DatumTransactie' DATE NOT NULL DEFAULT '01-01-1970', 'direction' DEFAULT 'D', 'bedrag' FLOAT NOT NULL DEFAULT '0', 'RekeningNrBestemming' INTEGER DEFAULT '0' NOT NULL REFERENCES 'tblBankAccount' ('idAccountNr'), 'Begunstigde' VARCHAR(32), 'DatumBoeking' DATE NOT NULL DEFAULT '01-01-1970', 'TransactieCode' INTEGER NOT NULL DEFAULT '0' REFERENCES 'tblMoneyFlowAction' ('id'), 'TransactieOmschrijving' VARCHAR(25) ); INSERT INTO 'tblMoneyFlow' DEFAULT VALUES; error is "no such table: main.tblMoneyFlow" Thanks a lot. P.S. I suspect it should have something to do with the references constraints -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] SQLiteManager 3.5
Viadana, Italy - SQLabs has announced SQLiteManager 3.5, the most powerful sqlite database manager tool for MacOS X and Windows. SQLiteManager is a powerful GUI database manager for sqlite databases. It combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2, sqlite 3, in memory databases, AES 128 encrypted databases and with REAL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way, it supports you arbitrary SQL commands and much more. Version 3.5 features a new powerful table editor with foreign key support and an advanced low level sqlite 3 database analyzer plus a lot of other improvements. What's new in this version: * Added a new detailed analyzer feature * Added Foreign Key support * Added a Copy RecordSet new menu item * Added Views to the Export dialog * Brand new CREATE/ALTER table dialog * Improved the importer engine * Improved the exporter engine * Improved display of BLOB images * Improved NULL values handling * Improved handling of table names that contains special characters * Improved both the Manage and SQL panel * Fixed a Win32 Edit Table menu issue * Fixed a couple of minor Win32 related issues * Fixed a case insensitive bug in inline editing * Fixed a bug that occurs while updating a row that contains BLOB columns * Fixed some issues related to BOOLEAN values * Fixed an issue that occurs while editing values with an sqlite 2 database * Fixed the incorrect Database Seems Encrypted message * Fixed an issue related to the RSReport engine * Fixed an issue related to saved SQL commands * Updated sqlite to the latest 3.6.23.1 version * Updated DoD ListBox to version 2.3 * Updated User's Manual and RBScript Manual * Updated Language Reference * Updated SQLite3ProfessionalPlugin to version 3.9 * Updated RSReport engine to version 2010.2.1 * A lot of other small fixes and optimizations Some features include: * SQLite2 and SQLite3 support * REAL Server support * In-Memory database support * AES 128 encrypted SQLite 3 databases support * Browse tables, views, and indexes * Create new tables, views, indexes and triggers * Create notes and script * Drop tables, views, indexes, triggers * Full alter tables support * Manage tables by inserting, editing, and deleting records * Built-in inline editing * Built-in virtual machine analyzer * Built-in query optimizer * Full-text search support * Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records * Save frequently used SQL commands for later use * Convert SQLite 2 databases to SQLite 3 * Powerful reports generation with flexible report templates * Advanced import and export capabilities * Built-in language reference * Embed notes and stickies in your databases * Load native sqlite 3 extensions * Script language support for automate repetitive tasks * New record editor to easily modify/add rows * Real-time BLOB preview * SQL history * Log window * New CSV import engine that supports multi gigabity files * Improved export engine * New powerful print engine with real-time preview and PDF export * Attach external database files * Dump database files on disk * Ability to open hidden files or bundles under OSX * New table editor with full support for altering exiting tables * New improved GUI * New Chart panel to easily visualize your data (Line chart, Bar chart, Pie chart, Venn chart, Scatter, Radar, Map and QR Code) Minimum requirements: * MacOS X 10.4 or higher * Windows 2000/NT/XP/Vista/7 A SQLiteManager single license is $49 USD (a $20 discount coupon code is available for SQLiteManager 2.x registered users). Company and multiplatform licenses are also available. For more information, please visit the SQLabs website at: http://www.sqlabs.com/sqlitemanager.php -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_name
Unfortunately I cannot modify the query... it is supplied by an user. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Nov 13, 2009, at 3:10 PM, Virgilio Fornazin wrote: > SELECT > field as NAME > > does not work? > > On Fri, Nov 13, 2009 at 12:07, Marco Bambini <ma...@sqlabs.net> wrote: > >> sqlite 3.6.19 >> >> CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT); >> a >> SELECT rowid, col1, col2 >> >> returns the following column names with sqlite3_column_name: >> col1, col1, col2 >> >> Is there a way to force the first column name to be returned as rowid and >> not as its col1 alias? >> >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> ___ >> 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] sqlite3_column_name
sqlite 3.6.19 CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT); a SELECT rowid, col1, col2 returns the following column names with sqlite3_column_name: col1, col1, col2 Is there a way to force the first column name to be returned as rowid and not as its col1 alias? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Hello Yan, you could use my SQLiteManager application: http://www.sqlabs.com/sqlitemanager.php Regards, -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Sep 21, 2009, at 9:43 AM, Yan Bertrand wrote: >Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > - SQLiteManager plugin (for Mozilla Firefox), but it does not > do this by default. The website says it can but the explaination for > it > is still < to be done >. > > - SQLiteStudio does not display anything (it says < NULL > in > italic, but it is not a NULL content.) > > - SQLite2009 Pro but it does not dosplay blobs either > > - I could dump the table but it really gets tedious. > > > > Note: I tried exporting to other formats but the blobs are replaced > by a > string (either < NULL > or < NONE >). I know my blobs are not empty > because C-code can read them. > > > > Any idea? > > > > Thank you for your support and merry continued use of SQLite! > > > > Yan > > ___ > 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] Explanation
Yes, you are right (as always). Time with SQLITE_THREADSAFE=0 is about 4.33 seconds now. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:23 PM, D. Richard Hipp wrote: > > On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > >> On Fri, 28 Aug 2009, Marco Bambini might have said: >>> >>> Version 3.4.2 takes about 5.06 seconds (average value) while version >>> 3.6.17 takes about 7.28 seconds (average value). >>> Could be a slowdown in the library for the complexity added over the >>> years or does someone have another possible explanation? > > Our measurements show a substantial performance improvement over > 3.4.2. > > Perhaps you are running in the default configuration, which has been > augmented with many new mutexes since version 3.4.2 in order to make > SQLite proof against over-zealous users of threads. If you recompile > with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance > back. > > >>> >>> Thanks. >> >> Did you just relink your app or did you also migrate the data to a >> new >> sqlite3 database? I think the migration command is: >> >> echo '.dump' | sqlite3 $db | sqlite3 $dbnew >> >> Maybe the internal database structure has changed? > > The file-format is unchanged. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > 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] Explanation
Library is statically linked into the final app and the db is newly created... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:17 PM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: > >> Hello, >> >> today I made some test on a project I wrote some years ago. >> I upgraded sqlite library from version 3.4.2 to version 3.6.17. >> What I am really unable to understand is the time difference required >> to perform the same query using the exact same algorithm by the two >> libraries. >> >> SELECT * FROM table1 >> where table1 has 1 million rows and 10 columns (its an 80MB db). >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? > > Mike > ___ > 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] Explanation
Hello, today I made some test on a project I wrote some years ago. I upgraded sqlite library from version 3.4.2 to version 3.6.17. What I am really unable to understand is the time difference required to perform the same query using the exact same algorithm by the two libraries. SELECT * FROM table1 where table1 has 1 million rows and 10 columns (its an 80MB db). Version 3.4.2 takes about 5.06 seconds (average value) while version 3.6.17 takes about 7.28 seconds (average value). Could be a slowdown in the library for the complexity added over the years or does someone have another possible explanation? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
Have you tried my SQLiteManager app? http://www.sqlabs.com/sqlitemanager.php It can easily convert/import your CSV file into an sqlite3 database. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 7, 2009, at 4:57 PM, Adam DeVita wrote: > This is why I generally advocate TAB delimited files over CSV > > Restaurant , Menu Item, Price > Tom, Dick "The MAN", and Harry's Bar & Grill , Specials /new stuff! > Mikey's > Burger "Delishiousness ' , $5 > > If you only have to upload your data once, you should be able to use a > spreadsheet program to convert to TAB delimited rather than going > through > the work of writing your own parser. > > > > On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P < > ronald.wil...@tycoelectronics.com> wrote: > >>> I'm trying to take a CSV file and create a sqlite3 database for the >>> iPhone. >>> The CSV file has 33K entries and is 2 MB. The problem I am having >>> is >> that >>> only about 1/10 of the database file gets written into the sqlite3 >>> database. >> >> The .import csv method is imperfect; if you have quoted strings in >> your csv >> that have commas or newlines in them, the import will do surprising >> things. >> I had to write my own code to do imports with quoted strings. >> >> RW >> >> Ron Wilson, Engineering Project Lead >> (o) 434.455.6453, (m) 434.851.1612, www.harris.com >> >> HARRIS CORPORATION | RF Communications Division >> assuredcommunications(tm) >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada > ___ > 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] [ANN] SQLiteManager 3.0
VIADANA, Italy (June 24, 2009) - SQLabs is proud to announce today the worldwide availability of SQLiteManager 3.0, the most powerful sqlite database manager tool for MacOS X and Windows. SQLiteManager is a "next generation" GUI database manager for sqlite databases. It combines an incredible easy to use interface with blazing speed and advanced features. SQLiteManager allows you to open and work with sqlite 2, sqlite 3, in memory databases, AES 128 encrypted databases and with REAL Server databases. It allows you to create and browse tables, views, triggers and indexes. It enables you to insert, delete and updates records in a very intuitive way, it supports you arbitrary SQL commands and much more. Some features include: *SQLite2 and SQLite3 support. *REAL Server support. *In-Memory database support. *AES 128 encrypted SQLite 3 databases support. *Browse tables, views, and indexes. *Create new tables, views, indexes and triggers. *Create notes and script. *Drop tables, views, indexes, triggers. *Full alter tables support. *Manage tables by inserting, editing, and deleting records. *Built-in inline editing. *Built-in virtual machine analyzer. *Built-in query optimizer. *Full-text search support. *Show PDF, JPEG, BMP, TIFF, QuickTime from BLOB records. *Save frequently used SQL commands for later use. *Convert SQLite 2 databases to SQLite 3. *Powerful reports generation with flexible report templates. *Advanced import and export capabilities. *Built-in language reference. *Embed notes and stickies in your databases. *Load native sqlite 3 extensions. *Script language support for automate repetitive tasks. *New record editor to easily modify/add rows. *Real-time BLOB preview. *SQL history. *Log window. *New CSV import engine that supports multi gigabity files. *Improved export engine. *New powerful print engine with real-time preview and PDF export! *Attach external database files. *Dump database files on disk. *Ability to open hidden files or bundles under OSX. *New table editor with full support for altering exiting tables. *New improved GUI. *New Chart panel to easily visualize your data (Line chart, Bar chart, Pie chart, Venn chart, Scatter, Radar, Map and QR Code) Minimum requirements: - MacOS X 10.4 or higher - Windows 2000/NT/XP/Vista/7 A SQLiteManager single license is $49. Company and multiplatform licenses are also available. For more information, please visit the SQLabs website: http://www.sqlabs.com/sqlitemanager.php Contact Information Web: http://www.sqlabs.com Email: i...@sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
Hello Martin, it's a db than an user sent me... so I really don't know... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:31 AM, Martin Engelschalk wrote: > Hi Marco, > > How do you insert this data into your database? > I opened your database with an old version of SQLiteSpy, which uses an > even older version of sqlite. It showed the value as a blob. > > Martin > Marco Bambini wrote: >> I understand that the issue could be caused by the wrong datatype... >> but what is strange is that the same db and the same query worked >> fine >> with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x >> >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote: >> >> >>> Your field value is a blob, so you have to use a cast like you did >>> to >>> find the row, or use a blob literal: >>> SELECT * FROM lo_user WHERE lo_name=X'61646d696e'; >>> >>> >>> >>> Marco Bambini wrote: >>> >>>> I just posted the db on my website... there is one row and there >>>> aren't invisible characters. >>>> >>>> Please note that the following query returns the exact row: >>>> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; >>>> but I really don't have an explanation... >>>> >>>> -- >>>> Marco Bambini >>>> http://www.sqlabs.com >>>> http://www.creolabs.com/payshield/ >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: >>>> >>>> >>>> >>>>> Hi, >>>>> >>>>> attachments do not make it through the list. >>>>> There is no row with the value 'admin' in the field 'lo_name' in >>>>> your >>>>> table. Did you check that there are no blank spaces or other >>>>> invisible >>>>> characters? >>>>> >>>>> Martin >>>>> >>>>> Marco Bambini wrote: >>>>> >>>>> >>>>>> Anyone can please explain me why this query: >>>>>> SELECT * FROM lo_user WHERE lo_name='admin'; >>>>>> returns 0 rows in this db? >>>>>> >>>>>> >>>>>> >>>>>> Thanks. >>>>>> -- >>>>>> Marco Bambini >>>>>> http://www.sqlabs.com >>>>>> http://www.creolabs.com/payshield/ >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> ___ >>>>>> 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-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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
I understand that the issue could be caused by the wrong datatype... but what is strange is that the same db and the same query worked fine with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:17 AM, Martin Engelschalk wrote: > Your field value is a blob, so you have to use a cast like you did to > find the row, or use a blob literal: > SELECT * FROM lo_user WHERE lo_name=X'61646d696e'; > > > > Marco Bambini wrote: >> I just posted the db on my website... there is one row and there >> aren't invisible characters. >> >> Please note that the following query returns the exact row: >> SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; >> but I really don't have an explanation... >> >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: >> >> >>> Hi, >>> >>> attachments do not make it through the list. >>> There is no row with the value 'admin' in the field 'lo_name' in >>> your >>> table. Did you check that there are no blank spaces or other >>> invisible >>> characters? >>> >>> Martin >>> >>> Marco Bambini wrote: >>> >>>> Anyone can please explain me why this query: >>>> SELECT * FROM lo_user WHERE lo_name='admin'; >>>> returns 0 rows in this db? >>>> >>>> >>>> >>>> Thanks. >>>> -- >>>> Marco Bambini >>>> http://www.sqlabs.com >>>> http://www.creolabs.com/payshield/ >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> ___ >>>> 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-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] Why row is not found?
I just posted the db on my website... there is one row and there aren't invisible characters. Please note that the following query returns the exact row: SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; but I really don't have an explanation... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: > Hi, > > attachments do not make it through the list. > There is no row with the value 'admin' in the field 'lo_name' in your > table. Did you check that there are no blank spaces or other invisible > characters? > > Martin > > Marco Bambini wrote: >> Anyone can please explain me why this query: >> SELECT * FROM lo_user WHERE lo_name='admin'; >> returns 0 rows in this db? >> >> >> >> Thanks. >> -- >> Marco Bambini >> http://www.sqlabs.com >> http://www.creolabs.com/payshield/ >> >> >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why row is not found?
You can download the db from: http://www.sqlabs.com/download/test.sqlite It's only 4KB. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Jun 5, 2009, at 11:04 AM, Marco Bambini wrote: > Anyone can please explain me why this query: > SELECT * FROM lo_user WHERE lo_name='admin'; > returns 0 rows in this db? > > > > Thanks. > -- > Marco Bambini > http://www.sqlabs.com > http://www.creolabs.com/payshield/ > > > > > > > ___ > 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] Why row is not found?
Anyone can please explain me why this query: SELECT * FROM lo_user WHERE lo_name='admin'; returns 0 rows in this db? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] most efficient way to get 1st row
SELECT ... LIMIT 1; -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On May 19, 2009, at 5:03 PM, Sam Carleton wrote: > I am far from a SQL expert, but I am 99.9% sure there is SQL syntax > to limit > the number of results, I have not looked it up but I will in a little > while... > > I only need one result. Since I am working with the C/C++ API, I > plan to > simply call sqlite3_step() only once. > > Is there any point in using the SQL syntax to limit it to one? > Would the > syntax slow the process down because it simply isn't needed or will > it speed > it up because of all the wonderful internal things going on? > ___ > 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] SQL logic error or missing database
Thanks a lot for the explanation Igor. -- Marco Bambini On May 14, 2009, at 1:55 PM, Igor Tandetnik wrote: > "Marco Bambini" <ma...@sqlabs.net> wrote > in message news:aa7dd05f-4679-43dd-9dd3-2ba6b98af...@sqlabs.net >> I have two threads that are writing 2000 rows each to the same >> database at the same time. >> I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1. >> >> Each client executes this code (pseudo C code): >> void write (sqlite3 *db) { >> int i; >> >> for (i=1; i<=2000; i++) { >> if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN >> IMMEDIATE;", ...); >> sqlite3_exec(db, "INSERT INTO", ...); >> if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db, >> "COMMIT;", ...); >> } >> } >> and db is shared between the two clients. > > You have a race condition here: between the calls to > sqlite3_get_autocommit and sqlite3_exec, the other thread could very > well have issued a BEGIN or a COMMIT of its own. Between the time you > check the condition and the time you act on it, the condition could > have > changed. > > Besides, the documentation on sqlite3_get_autocommit has this > sentence: > If another thread changes the autocommit status of the database > connection while this routine is running, then the return value is > undefined. In other words, sqlite3_get_autocommit is explicitly not > thread-safe. > > Since you only run one INSERT per transaction anyway, why do you feel > you need explicit BEGIN and COMMIT? > >> At the end of the loop, instead of having 4000 rows I have 3976 rows >> (it's random, sometimes I have 3972 or 3974). >> sqlite3_exec doesn't returns any error during the INSERT statement, >> but I have some errors during the BEGIN IMMEDIATE, errors are all: >> SQL logic error or missing database (printed with sqlite3_errmsg). > > Your use of sqlite3_errmsg is itself very likely a race. Between the > time you detect an error and the time you retrieve error message, the > other thread could have run some statements that modify the error > message. Moreover, between the time you call sqlite3_errmsg and the > time > you actually print the string pointed to by the char* pointer the > function returns, the string may be modified or even deallocated. > > 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] SQL logic error or missing database
I have two threads that are writing 2000 rows each to the same database at the same time. I am using sqlite 3.6.13 compiled with SQLITE_THREADSAFE=1. Each client executes this code (pseudo C code): void write (sqlite3 *db) { int i; for (i=1; i<=2000; i++) { if (sqlite3_get_autocommit(db) != 0) sqlite3_exec(db, "BEGIN IMMEDIATE;", ...); sqlite3_exec(db, "INSERT INTO", ...); if (sqlite3_get_autocommit(db) == 0) sqlite3_exec(db, "COMMIT;", ...); } } and db is shared between the two clients. At the end of the loop, instead of having 4000 rows I have 3976 rows (it's random, sometimes I have 3972 or 3974). sqlite3_exec doesn't returns any error during the INSERT statement, but I have some errors during the BEGIN IMMEDIATE, errors are all: SQL logic error or missing database (printed with sqlite3_errmsg). Any explanation of the possible causes for the missed rows? Thanks. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users