Re: [sqlite] Common Multi-treaded Problem
On Fri, 13 May 2011 17:14:32 -0700 (PDT) John Deal bassd...@yahoo.com wrote: Again thanks for the information and I apologize for taking up so much list bandwidth. I hope others can benefit. Watching this thread was useful to me and I'd like to use this reply to *thank* Pavel and the other guys for explaining these things in-depth! /off-topic Mihai -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Multi-treaded Problem
If you don't mind, John, for bullying in the discussion... On Thu, 12 May 2011 17:58:40 -0400 Pavel Ivanov paiva...@gmail.com wrote: There's no dependency between different prepared statements, but there is dependency between transactions as they use the same database. And transaction cannot be finished (implicitly or explicitly) until all statements in this transaction are reset or finalized. Pavel, could you please specify what do you mean by statements in this transaction? Statements that were prepared (sqlite3_prepare) or initiated (sqlite3_step) during the transaction? Also, is this something that one should immediately deduce, necessary behavior based on the model of SQLite (or perhaps RDBMS, ACID), or is it something that one normally learns by heart, being, for the end-user, just the way SQLite works? Thanks, Mihai -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to reuse a prepared statement
On Sat, 23 Apr 2011 09:39:40 -0400 Sam Carleton scarle...@miltonstreet.com wrote: Can someone then explain the purpose of sqlite3_clear_bindings()? If I understand things correctly, you call sqlite3_reset() to reuse a prepaired statement, why do you call sqlite3_clear_bindings()? Is it because sqlite3_reset() does not actually clear the binding values, just resets the preparied statement? I have a feeling this is the case, so... Yes, take for example a SELECT * FROM table WHERE name='John' statement, for each step it gets one 'John' row. At step n, it will give you the n-th 'John' row, but if you reset it, you'll get the first 'John' row again, though it's still 'John' that was bound to the statement, it won't change (to the default NULL) unless you call clear bindings. I understand things correctly, you call sqlite3_reset() to reuse a prepaired statement, why do you call sqlite3_clear_bindings()? You don't have to. Personally, I've never once found a use for it. I now realize that I used it several times for no good reason. :) However, I think it's safer to use it, it's easier to debug a problem involving an unwelcome NULL than an arbitrary incorrect entry, in case something goes wrong. -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automating the build of a sqlite database
On Sat, 23 Apr 2011 12:17:54 -0400 Tom Holden holden_fam...@sympatico.ca wrote: Perhaps by adding .quit to your schema.sql I tried this and it does not work, no error but it enters interactive mode once more. Also, the necessity to add sqlite shell commands to the file would pervert the pure SQL schema if it worked. But quit can (should?) be appended to each batch command as an argument for sqlite3.exe: sqlite3.exe -init schema.sql default.db3 .quit On Unices I use: sqlite3.exe default.db3 schema.sql as exemplified by DRH (IIRC), but I guess there's no way to do something similar on Windows cmd? -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automating the build of a sqlite database
On Sat, 23 Apr 2011 19:06:02 +0200 Roger Andersson r...@telia.com wrote: On Unices I use: sqlite3.exe default.db3 schema.sql as exemplified by DRH (IIRC), but I guess there's no way to do something similar on Windows cmd? Have you tried exactly the same on Windows cmd? I'm sorry for the typo, my executable is sqlite3, not sqlite3.exe. To answer your question: no, I haven't had tried that on plain cmd at the time I wrote the message. Being intrigued by your question, I discovered it actually works... (I normally use MSYS when I need something done in command-line on Windows) -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] date field with default current date
On Thu, 21 Apr 2011 15:17:00 +0200
Fabio Spadaro fabiolinos...@gmail.com wrote:
Does not work on python with sqlite3 module
Try using the date and time functions, 'date' or 'datetime' in your case:
INSERT INTO table(..., date) VALUES(..., datetime('now'));
http://www.sqlite.org/lang_datefunc.html
--
Mihai Militaru mihai.milit...@xmpp.ro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Match statements per query
On Tue, 19 Apr 2011 14:18:05 -0600 Dave White dwh...@companioncorp.com wrote: For example, this works: SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND T01_fts.words MATCH 'SBNTKN*' These do not: SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 OR T01_fts.words MATCH 'SBNTKN*' SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND (T01_fts.words MATCH 'SBNTKN*' OR T01_fts.words MATCH 'CTLTKN*' ) I think it is GLOB '*' what you look for (or LIKE '%' for case-insensitive match): SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 OR T01_fts.words GLOB 'SBNTKN*'; SELECT T01_fts.docid FROM T01_fts JOIN T01 ON T01_fts.docid = T01.rowid WHERE T01.hasPic = 0 AND (T01_fts.words GLOB 'SBNTKN*' OR T01_fts.words GLOB 'CTLTKN*'); And an entirely separate question: Is there currently a way, or will there soon be a way to clone prepared statements? I'd do it like this: sqlite3_stmt *stmt2 = NULL; sqlite3_prepare_v2(sqlite3_db_handle(stmt1), sqlite3_sql(stmt1), -1, stmt2, NULL); Check what the respective functions do here: http://www.sqlite.org/c3ref/funclist.html Basically: - the first argument function returns the database of the first statement (you may pass a different open database handle directly, in order to clone the first statement over it); - the second argument function returns the sql text of the first statement; - the third argument is the size of the text to parse, negative to get it up to the first NULL - normally the end; - the fourth is a pointer to your new unallocated statement; I think copying the bindings is possible using sqlite3_bind_parameter_* and something else I can't figure out right now. -- Mihai Militaru mihai.milit...@xmpp.ro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Capitalisation
On Fri, 25 Mar 2011 10:35:37 +0100 J Trahair j.trah...@foreversoftware.co.uk wrote: If the SELECT statement is SELECT * FROM Customers WHERE CustomerCode = 'tra001' that is, with tra001 in lower case, SQLite *does not find the record*. SELECT * FROM Customers WHERE CustomerCode LIKE 'tra001' would do. There are exceptions, for non-ASCII characters in unicode - http://www.sqlite.org/lang_expr.html#like - however, the like function can be redefined to match your needs: http://www.sqlite.org/pragma.html#pragma_case_sensitive_like SQL Server Express and MySQL - and even Access - do not have this disadvantage. My opinion is that matching the exact string you want is not a disadvantage, 'tra001' and 'TRA001' are different things, why would the equal sign match them?... -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about database design
On Wed, 2 Feb 2011 18:59:48 -0600 Nicolas Williams nicolas.willi...@oracle.com wrote: Any idea why pg does ok on these queries without the extra index - Maybe they're created by default? SQLIte doesn't create any indexes automatically on primary key fields or anything else, correct? No, it doesn't. Use EXPLAIN QUERY PLAN to see what SQLite3 is doing. Hmm SqLite does create persistent indices on UNIQUE - and consequently PRIMARY - keys, doesn't it? -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for data entry
Thanks for the suggestion, Tom, your post was very educational. At least in my case, you're on the spot, I knew about views but I didn't use them in my project so far, although a short analysis tells me that they would be extremely useful for it - for example I should create one called files for tables filenames (unique path names) and fcontents (unique checksum) in a left outer join by the content index (int64). Guess what I'm doing next :). Anyway, I see the great advantage and also how easily forms can be created with such views. Regards, Mihai On Fri, 15 Oct 2010 17:45:53 +1100 BareFeetWare list@tandb.com.au wrote: You don't need to create special tables for data entry. You can create views instead, coupled with instead of triggers. This is one of the most undersold features of SQL and SQLite in general, I think. When you design a database, you should properly structure and normalize it so that, for instance, there is no redundancy in data entry, a column that has an occasional entry is moved to its own table (rather than populating the main table with nulls), a multiple choice column contains an integer that links to a related table of values and so on. Dates should probably be entered as a julian day real (rather than text) and money should probably be entered as an integer (in cents) rather than a real, to avoid float calculation errors. All that works well from a data integrity point of view. It's tight, uses minimal space and avoids redundancy and inaccuracies. But if you give such a properly normalized database in its raw form to a use for data entry (or even to browse data), it's horrible. They could go crazy trying to cross reference tables, mentally convert dates and money, switch to a separate table for occasional column info etc. The answer is to create a view. This is pretty common place. In a view, you can join tables, show just some rows, show calculated totals, convert date reals to human readable text, integer amounts to currency etc. But what few database designers seem to exploit is the fact that views can also be used to accept user input and convert that into meaningful entry into the underlying related tables. And, to answer the original poster's query, a view can -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)
On Tue, 19 Oct 2010 10:54:13 +1100 BareFeetWare list@tandb.com.au wrote: -- alternatively you could do this, which will update the existing row, if exists, or insert a new one if it doesn't: update users set name = 'Joe C', type = 4, where id = 1; insert or ignore into users (id, type, name) values (1, 4, 'Joe C'); What about using a trigger which automatically updates right before the insertion? I'm using this trigger: CREATE TRIGGER users_update_existing BEFORE INSERT ON users BEGIN UPDATE users SET type=NEW.type, id=NEW.id WHERE name=NEW.name; END ...then in my code I use only: INSERT OR IGNORE INTO users(name,type,id) VALUES(?,?,?); It works very well so far, I'm just curious if you can forsee any drawback in this approach. -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gui for data entry
On Wed, 13 Oct 2010 18:15:54 +0300 Mihai Militaru mihai.milit...@gmx.com wrote: That is what I am using, but unless I am missing something, it doesn't let me create a form for data entry. Please pardon my distraction, Graham, I didn't read your post carefully. My recommendation was general-purpose. But I got this idea: what if you create such forms yourself, using the flexibility given by SQL? I don't know whether it would satisfy all your requirements, but at least for duplication of data you can easily use temporary tool tables with triggers, eg. you create a table using the required fields (both named purposefully), and then triggers attached to it can update different things on different target tables. You export (or write manually) this easy setup to an SQL file and import it every time you work, editing its content any time you need more features. The manager appears to support user defined functions in a language it doesn't specify and I don't recognize it (but I assume it's Javascript) so the possibilities seem to be unlimited. -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gui for data entry
I use the Mozilla add-on, it's very handy and it gets updated. https://addons.mozilla.org/en-US/firefox/addon/5817/ It currently uses SQLite version 3.6.22. -- Mihai Militaru mihai.milit...@gmx.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
