Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
Brzozowski, Christoph wrote: Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) That version was released in April 2010 and is no longer officially supported. in a multi user scenario, where multiple users on different machines access the same database, which lies on a network share, or in a single user scenario, where the database lies locally but is accessed simultaneously by multiple processes on the same machine. Sharing a SQLite database file using a network share on Windows can be problematic, please refer to the following link for further information: https://www.sqlite.org/faq.html#q5 When we switched the database to an encrypted one, by adding a password to the connection string passed to the ADO.NET Sqlite provider, the synchronization mechanisms ceased to work. The CryptoAPI-based encryption included with System.Data.SQLite is a legacy feature, has known issues, and is officially unsupported. It is being retained only for the purpose of backward compatibility with legacy applications that make use of it. That all being said, you might wish to try using the latest released version of System.Data.SQLite. Alternatively, you might want to look into the commercial SEE extension, which does work with System.Data.SQLite and is fully supported for use with it. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite handler in trigger
Hi, I would like to check sqlite handler usability while using trigger: I have register one function to sqlite by sqlite3_create_function().Now I am creating trigger on update selecting that function for callaback. In above scenario it is mandatory that sqlite3 handler used while create function setting trigger should use for update. I have case where one process is updating data with his private handler but trigger on update is created by other process by his private handler. Cheers - Techi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
Thank you very much for the responses. That was helpful. With best regards, Christoph Brzozowski Siemens AG Industry Sector Industry Automation Division Industrial Automation Systems Process Automation I IA AS PA CIS RD 5 Karl-Legien-Str. 190 53117 Bonn, Germany Tel: +49 228 64805-215 mailto:christoph.brzozow...@siemens.com Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme; Managing Board: Joe Kaeser, Chairman, President and Chief Executive Officer; Roland Busch, Brigitte Ederer, Klaus Helmrich, Barbara Kux, Hermann Requardt, Siegfried Russwurm, Peter Y. Solmssen, Michael Suess; Registered offices: Berlin and Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 12300, Munich, HRB 6684; WEEE-Reg.-No. DE 23691322 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Donnerstag, 5. September 2013 08:08 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency Brzozowski, Christoph wrote: Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) That version was released in April 2010 and is no longer officially supported. in a multi user scenario, where multiple users on different machines access the same database, which lies on a network share, or in a single user scenario, where the database lies locally but is accessed simultaneously by multiple processes on the same machine. Sharing a SQLite database file using a network share on Windows can be problematic, please refer to the following link for further information: https://www.sqlite.org/faq.html#q5 When we switched the database to an encrypted one, by adding a password to the connection string passed to the ADO.NET Sqlite provider, the synchronization mechanisms ceased to work. The CryptoAPI-based encryption included with System.Data.SQLite is a legacy feature, has known issues, and is officially unsupported. It is being retained only for the purpose of backward compatibility with legacy applications that make use of it. That all being said, you might wish to try using the latest released version of System.Data.SQLite. Alternatively, you might want to look into the commercial SEE extension, which does work with System.Data.SQLite and is fully supported for use with it. -- Joe Mistachkin ___ 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] Collating sequences. Was: Query problems
There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. If you give a collating sequence to a column in a table definition, then that collating sequence becomes the default for that column: CREATE TABLE t1(pqr TEXT COLLATE xyzzy); In the table above, the pqr column has a default collating sequence of xyzzy. Any time you do a comparison against that column the default collating sequence is used. So, for example, if you say: SELECT * FROM t1 WHERE pqr=?1; Then the xyzzy collating sequence is used to compare the input string bound to ?1 against the value of the column pqr. It is *not* necessary to explicitly add a COLLATE clause to the comparison: SELECT * FROM t1 WHERE pq4=?1 COLLATE xyzzy; Though not necessary, adding the COLLATE clause is harmless in this case. If you create an index on a column, that index automatically uses the default collating sequence, unless you specify an alternative. So, for example, if you say: CREATE INDEX t1pqr ON t1(pqr); Then the xyzzy collating sequence is used for the index as well. It is *not* necessary to explicitly add a COLLATE clause to the index: CREATE INDEX t1pqr ON t1(pqr COLLATE xyzzy); On the other hand, doing so is a harmless no-op. Creating an index with a collating sequence that differs from the default does not change the default collating sequence of the column. Hence, if you say: CREATE TABLE t2(mno TEXT); CREATE INDEX t2mno1 ON t2(mno COLLATE xyzzy); SELECT * FROM t2 WHERE mno=?2; The comparison in the query uses the default collating sequence for the column mno and hence cannot make use of the index. But if you query this way: SELECT * FROM t2 WHERE mno=?3 COLLATE xyzzy; Then the collating sequence of the index matches the collating sequence of the query and the index can be used. -- 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] about Khmer unicode with sqlite
Dear Sir or Madam How do integrate Khmer unicode into sqlite? When I try to use Khmer unicode with sqlite, the characters does not display. All I see, they display in English. Thanks, Samuel San ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] commit fails when journal_mode = memory
Hello, The next problem occurs since switching to sqlite 3.8; it was never observed in 3.7.17 or earlier. I have an application in which I insert a lot of data in a transaction, started by executing begin transaction, after having executed PRAGMA synchronous = 0 and PRAGMA journal_mode = MEMORY. After that the inserts take place. When I finally try to execute the commit statement, I get an SQLITE_BUSY status. This error does not happen when I remove the journal_mode statement. The latest version tested is 3.8.0.2. I build the library with the next defines: THREADSAFE=2 SQLITE_ENABLE_FTS4 SQLITE_ENABLE_FTS3_PARENTHESIS SQLITE_ENABLE_RTREE SQLITE_ENABLE_COLUMN_METADATA SQLITE_ENABLE_STAT3 SQLITE_CORE The application is built with the same defines, except SQLITE_CORE which is then ommitted. Frank De prins [cid:image002.jpg@01CE45B7.374DA450] Advisory Software for Real Estate Facility Management Sneeuwbeslaan 20 b3 ∙ 2610 Antwerp ∙ Belgium t. +32 (0) 3 829 04 95 frank.depr...@mcs.fmmailto:serge.vanginde...@mcs.fm ∙ www.mcs.fmhttp://www.mcs.fm http://www.mcs.fm/legal/email-disclaimer/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance question related to multiple processes using sqlite
Hello, We use Qt with sqldriver Sqlite-4 in our application. (Windows XP 32 bit, Visual studio 2005) We are facing a performance issue in the following scenario. There are two processes A and B. A uses sqlite DB1 and keeps populating data. Process B uses sqlite DB2 for writing and occasionally opens DB1 for reading. What we notice is when A is writing into DB1, B hangs for a couple of seconds even though it is not performing any DB operations on DB1/DB2. On opening Windows event viewer, we see that process B is making a lot of registry calls and is accessing disk a lot during the time that process A is writing to DB1 and process B really is not doing any DB operation at that time. For now, we have not been able to identify if this is happenning inside Qt sql module or inside sqlite. But would any one know if there is any kind of synchronization that is done between different processes that connect to different sqlite databases that might slow down one process when the other process is performing DB operation on its sqlite DB? Thanks in advance! This message and any attachments are solely for the use of intended recipients. The information contained herein may include trade secrets, protected health or personal information, privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using such information is strictly prohibited and may be unlawful. If you are not an intended recipient, you are hereby notified that you received this email in error, and that any review, dissemination, distribution or copying of this email and any attachment is strictly prohibited. If you have received this email in error, please contact the sender and delete the message and any attachment from your system. Thank you for your cooperation ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about Khmer unicode with sqlite
On Thu, Sep 5, 2013 at 6:16 AM, Sarith San khmeres...@yahoo.com wrote: Dear Sir or Madam How do integrate Khmer unicode into sqlite? When I try to use Khmer unicode with sqlite, the characters does not display. All I see, they display in English. SQLite is a C-library. It does not display anything. You must be confusing SQLite with an interactive shell program of some kind that lets you interact with an SQLite database using typed-in commands. The display is an operation of the shell program, not of SQLite itself. If Khmer unicode is not being displayed correctly, then that is a fault in the shell program, not of SQLite. So what shell program are you using? D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance question related to multiple processes using sqlite
On Wed, Sep 4, 2013 at 3:51 PM, Varadan, Yamini (SCR US) (EXT) yamini.varadan@siemens.com wrote: But would any one know if there is any kind of synchronization that is done between different processes that connect to different sqlite databases that might slow down one process when the other process is performing DB operation on its sqlite DB? No. Two processes connected to different databases operating completely independently of one another as far as SQLite is concerned. Even if two processes are talking to the same SQLite database, if one process is not actively using SQLite and does not have a transaction open, then there is no interaction with the other process. And even if there is interaction, that interaction is limited to file locks and/or use of a small amount of shared memory in WAL mode. It is never the case that one process will block or signal another process. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation update request
Hi, Simon, von Simon Slavin On 4 Sep 2013, at 3:05pm, Markus Schaber m.scha...@codesys.com wrote: Afaics, this applies to partial indices for similar reasons. I did not even know partial indices was implemented. Thank you. It's new in 3.8.0 :-) Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite handler in trigger
On 9/5/2013 5:39 AM, techi eth wrote: I have case where one process is updating data with his private handler but trigger on update is created by other process by his private handler. I'm not sure I understand this sentence. When you run CREATE TRIGGER statement, the trigger you've just created becomes part of the database schema, visible to all connections (unless you do CREATE TEMP TRIGGER; is this what you are talking about?) On the other hand, sqlite3_create_function() registers a custom function just for this connection. If you create a trigger that calls this function, then those connections where the function is not registered will fail to even prepare an UPDATE statement - SQLite will issue an unknown function error. In general, there is no mechanism built into SQLite that would allow one process to be automatically notified that another process made a change to the database. If that's what you want, you would have to implement that in your application - you can't somehow trick SQLite into doing it for you. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about Khmer unicode with sqlite
It may also be the system which is actually doing the display to the terminal. In my case, I was displaying a UTF-8 document on Linux, using Konsole. I had set LC_ALL to en_US.UTF-8. The file had the UTF-8 sequence 0xe2 0x80 0x93, which is U+2013, or an en dash. But I was seeing an latin small letter a with a circumflex. The reason was that Konsole normally displays characters based on the old DEC VT100 character map. To see the proper character, I had to run the Linux command: echo -e \e%G which sets Konsole from ISO8859-1 character set to UTF-8 character set. I don't know what system, program, etc the OP is using, but it may need to be customedto cause the display to render the proper glyphs. On Thu, Sep 5, 2013 at 7:08 AM, Richard Hipp d...@sqlite.org wrote: On Thu, Sep 5, 2013 at 6:16 AM, Sarith San khmeres...@yahoo.com wrote: Dear Sir or Madam How do integrate Khmer unicode into sqlite? When I try to use Khmer unicode with sqlite, the characters does not display. All I see, they display in English. SQLite is a C-library. It does not display anything. You must be confusing SQLite with an interactive shell program of some kind that lets you interact with an SQLite database using typed-in commands. The display is an operation of the shell program, not of SQLite itself. If Khmer unicode is not being displayed correctly, then that is a fault in the shell program, not of SQLite. So what shell program are you using? D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- As of next week, passwords will be entered in Morse code. Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x text collate c2); select x from t1 union select x from t2 order by x; Which collation is used by UNION to deduplicate? Which collation is used by ORDER BY to sort? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik i...@tandetnik.org wrote: On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x text collate c2); select x from t1 union select x from t2 order by x; Which collation is used by UNION to deduplicate? Which collation is used by ORDER BY to sort? The left-most. http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Collating sequences. Was: Query problems On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik i...@tandetnik.org wrote: On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x text collate c2); select x from t1 union select x from t2 order by x; Which collation is used by UNION to deduplicate? Which collation is used by ORDER BY to sort? The left-most. http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
On Thu, Sep 5, 2013 at 9:40 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' The left-most SELECT statement in the query where the column has an explicit collating sequence. In other words: the first SELECT statement of the query that has an explicitly defined collating sequence. -- 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] UPDATE question
I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. Currently I'm doing this by selecting the Pkey values of the entries with sequence =3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence = seq_to_insert AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. Currently I'm doing this by selecting the Pkey values of the entries with sequence =3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
Oops.. sorry.. I missed the last paragraph. If you're essentially single threaded.. I can do it in two updates... UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence = seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence 0 AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. Currently I'm doing this by selecting the Pkey values of the entries with sequence =3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On Sep 4, 2013, at 4:21 PM, Yuzem naujnit...@gmail.com wrote: I want to construct genres icons and each icon must display 4 movies. Assuming this is IMDB… what about a scalar subquery? For example, assuming a slightly different schema from yours: selectgenre.code as genre, ( select group_concat( title ) from ( selectmovie.title as title from movie join movie_genre onmovie_genre.movie_id = movie.id where movie_genre.genre_id = genre.id order by movie.title limit 4 ) ) as sample from genre where genre.code = 'Western' order by genre.code Western|26 Men (1957),A Man Called Shenandoah (1965),ABC Weekend Specials (1977) {The Winged Colt (#1.5)},Action in the Afternoon (1953) CPU Time: user 0.083246 sys 0.000443 This is for: select count( * ) from genre; 30 select count( * ) from movie; 2545331 select count( * ) from movie_genre; 1545196 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On 5 Sep 2013, at 7:56pm, Yuzem naujnit...@gmail.com wrote: I did some testing and found some strange results. Please do an ANALYZE and try the same things again. Also, do you have any indexes on those tables (apart from the primary keys, of course) ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
I did an ANALYZE but I'm getting the same results. I do have indexes: CREATE TABLE movies ( movies UNIQUE, name, icon_modified ); CREATE TABLE genres ( genres, movies, UNIQUE(genres,movies) ); people has an index on people (people UNIQUE) and tasks is a view: CREATE VIEW tasks AS SELECT 'creators' tasks,movies,people,'' details FROM creators UNION ALL SELECT 'directors' tasks,movies,people,'' details FROM directors UNION ALL SELECT 'writers' tasks,movies,people,'' details FROM writers UNION ALL SELECT 'actors'tasks,movies,people,characters details FROM actors; The tables creators, directors, etc, have similar indexes as genres: CREATE TABLE directors ( directors, people, UNIQUE(directors,people) ); -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71033.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
I did some testing and found some strange results. SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 2.475s SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 0.035s SELECT tasks FROM tasks LEFT JOIN people ON tasks.people = people.people GROUP BY tasks ORDER BY tasks time: 0.164s SELECT tasks FROM tasks JOIN people ON tasks.people = people.people GROUP BY tasks ORDER BY tasks time: 0.163s The strange thing is that tasks and people are much larger tables than genres and movies: SELECT count() from genres; 3998 SELECT count() from movies; 1529 SELECT count() from tasks; 24964 SELECT count() from people; 19626 -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71031.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
On 5 Sep 2013, at 7:20pm, Peter Haworth p...@lcsql.com wrote: That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. An interesting point. If all the updates are done inside a COMMIT, then the conflict may not be recognised because by the time the first change is written back to the table, the conflicting entry has already been renumbered. Nope, apparently SQLite doesn't support deferring conflict resolution until the COMMIT [1]. Maybe that should be added to these two pages: http://www.sqlite.org/omitted.html http://www.sqlite.org/conflict.html Simon. [1] Except for FOREIGN KEYS. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On Sep 5, 2013, at 8:56 PM, Yuzem naujnit...@gmail.com wrote: SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 2.475s SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP BY genres ORDER BY genres; time: 0.035s Yeah… the inner join vs out join will produce a significantly different access plan. For example, using a different schema: (1) inner join 0|0|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1 0|1|0|SEARCH TABLE genre USING INTEGER PRIMARY KEY (rowid=?) (2) outer join 0|0|0|SCAN TABLE genre USING COVERING INDEX sqlite_autoindex_genre_1 0|1|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1 In this case, the first plan is much better in terms of throughput as movie_genre is much larger table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
Ok, wonderful, now it is working correctly but how do I select multiple columns from table movies? Should I add another sub query? Example: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname FROM movies JOIN genres AS movie_genres ONmovies.movies = movie_genres.movies WHERE movie_genres.genres = genres.genres ORDER BY movies.name LIMIT 6 ) ), ( SELECT substr(group_concat(movies,' '),1,60) FROM ( SELECTname FROM movies JOIN genres AS movie_genres ONmovies.movies = movie_genres.movies WHERE movie_genres.genres = genres.genres ORDER BY movies.name LIMIT 6 ) ) FROM genres GROUP BY genres ORDER BY genres.genres; If I add another sub query it takes almost twice the time than using only one sub query. It is still faster than before. Petite Abeille-2 wrote Yeah… the inner join vs out join will produce a significantly different access plan. Yes but if that's the case don't you think that the difference in time is a bit much? And why there is almost no difference between JOIN and LEFT JOIN when using tasks and people which are larger tables? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71038.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
Thanks but 4 rows is not what I am looking for. I found a solution, concatenation: SELECT genres.genres, ( SELECT group_concat(movie,' ') FROM ( SELECTmovies.movies||','||name movie FROM movies JOIN genres AS movie_genres ONmovies.movies = movie_genres.movies WHERE movie_genres.genres = genres.genres ORDER BY movies.name LIMIT 3 ) ) FROM genres GROUP BY genres ORDER BY genres.genres; Any clue on why LEFT JOIN is so slow when used with genres but not with larger tables? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71040.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On Sep 5, 2013, at 10:28 PM, Yuzem naujnit...@gmail.com wrote: Ok, wonderful, now it is working correctly but how do I select multiple columns from table movies? Should I add another sub query? Nope. You have now changed the problem definition, so scalars will not be a good fit. Blindly copy paste them will not get you anywhere. The key to success in your case is to access the movie_genre table only once, as selectively as possible. So, if, for a given genre you want 4 movies, you could try something along these lines: selectgenre.code, movie.title from ( selectmovie_genre.movie_id, movie_genre.genre_id from movie_genre where movie_genre.genre_id = 30 order by movie_genre.id limit 4 ) asmovie_genre join movie onmovie.id = movie_genre.movie_id join genre ongenre.id = movie_genre.genre_id Which gives you 4 rows: Western|26 Men (1957) Western|A Man Called Shenandoah (1965) Western|ABC Weekend Specials (1977) {The Winged Colt (#1.5)} Western|Action in the Afternoon (1953) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
Petite Abeille-2 wrote Sure. But your conclusion is a most likely a red herring. The crux of the matter is that inner and outer join have a wildly different semantic. And therefore execution plan. That's all. Seems all very reasonable from an optimizer point of view. But I have no conclusion, I acknowledge that inner and outer join are different but I don't know why LEFT JOIN works very fast on larger tables and very slow on smaller tables at least in those cases. I would like to have a conclusion on that matter. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71042.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On Sep 5, 2013, at 11:27 PM, Yuzem naujnit...@gmail.com wrote: Any clue on why LEFT JOIN is so slow when used with genres but not with larger tables? Sure. But your conclusion is a most likely a red herring. The crux of the matter is that inner and outer join have a wildly different semantic. And therefore execution plan. That's all. Seems all very reasonable from an optimizer point of view. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL insert performance on Windows Mobile 6.5
Hi, I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5 running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar with the NSIcom CrE-ME 4.12 JVM. Performance of the insert is very low. I am getting about 1000 records added a minute. Any ideas the on how to improve the performance would be appreciated. Code overview: 1. Query an iSeries database with the JTOpen package to pull 56,000 records. This is fairly quick, less than 10 seconds to complete. 2. For each record returned, create an insert batch statement. 3. When 1000 records are added to the batch, execute the batch update and commit the transaction (autocommit is disabled) 4. Two pragma statements are also used when the connection is created. a. PRAGMA journal_mode=OFF; b. PRAGMA synchronous = OFF; The key sections of code: /* * PopulateItems.java * * Inserts items from the iSeries ERP into the PDA database item file * */ package com.probuild.pda; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; /** * */ public class PopulateItems { private Connection iSeriesConn; private Connection sqliteConn; public final int DB_COMMIT_COUNT = 5000; /** * Creates a new instance of PopulateItems */ public PopulateItems() { // Establish connections to iSeries and SQLite DBs try { Class.forName( com.ibm.as400.access.AS400JDBCDriver ); String url =jdbc:as400://as400.dxlbr.com;S10036FD;naming=sql;errors=full;; iSeriesConn = DriverManager.getConnection( url, SATCP, SATCP ); Class.forName( org.sqlite.JDBC ); sqliteConn = DriverManager.getConnection(jdbc:sqlite:\\pda.db); Statement statement = sqliteConn.createStatement(); // turn journaling off String sql = PRAGMA journal_mode=OFF;; statement.execute(sql); // turn journaling off sql = PRAGMA synchronous = OFF;; statement.execute(sql); statement.close(); sqliteConn.setAutoCommit(false); } catch (Exception e) { System.out.println ( e.getMessage() ); } } public void closeConnections ( ) { try { iSeriesConn.close(); sqliteConn.close(); } catch ( Exception e ) { System.out.println ( e.getMessage() ); } } public boolean buildItemTable (String locCode) { Statement iStmt = null; PreparedStatement pStmt = null; ResultSet iResult = null; boolean success = false; System.out.println( Building Item Table... ); try { pStmt = sqliteConn.prepareStatement( INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ); // SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept Code, Mfg Item# // Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N), RISBCD (P or C) String sql = SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT, RIMNF#, + RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD + FROM DRMS.DRITEMFL + WHERE RILOC# = + locCode + AND RIRLC# = 0 AND RISKU# 5 + ORDER BY RISKU#; iStmt = iSeriesConn.createStatement(); iResult = iStmt.executeQuery( sql ); System.out.println( iSeries select form dritemfl complete: ); int count = 0; while ( iResult.next ( ) ) { int sku = iResult.getInt( RISKU# ); long upc = iResult.getLong( RIUPC# ); //String sku = iResult.getString( RISKU# ); //String upc = iResult.getString( RIUPC# ); String alt = iResult.getString( RIAITM ); String desc = iResult.getString( RIDESC ); String uom = iResult.getString( RILSTU ); String dept = iResult.getString( RIDEPT ); String mnf = iResult.getString( RIMNF# ); String vendorItem = iResult.getString( RIVITM ); int minQ = iResult.getInt( RIMINQ ); int maxQ = iResult.getInt( RIMAXQ ); String netItem = iResult.getString( RINETI ); String subItemCode = iResult.getString( RISBCD ); pStmt.setInt( 1, sku ); pStmt.setLong( 2, upc ); pStmt.setString(3, alt.trim() ); pStmt.setString( 4, desc.replace( '\'', ' ').trim() ); pStmt.setString( 5, uom ); pStmt.setString( 6, dept ); pStmt.setString( 7, mnf.replace( '\'', '
Re: [sqlite] SQL insert performance on Windows Mobile 6.5
On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony tony.bull...@probuild.comwrote: Hi, I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5 running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar with the NSIcom CrE-ME 4.12 JVM. Performance of the insert is very low. I am getting about 1000 records added a minute. Any ideas the on how to improve the performance would be appreciated. On a workstation, with PRAGMA synchronous=OFF, you should be getting about 300,000 rows per *second*. Performance on your mobile computer will be less, of course, but it shouldn't be 18,000 times less. Do you have any profiling tools available to see what it taking so long? Code overview: 1. Query an iSeries database with the JTOpen package to pull 56,000 records. This is fairly quick, less than 10 seconds to complete. 2. For each record returned, create an insert batch statement. 3. When 1000 records are added to the batch, execute the batch update and commit the transaction (autocommit is disabled) 4. Two pragma statements are also used when the connection is created. a. PRAGMA journal_mode=OFF; b. PRAGMA synchronous = OFF; The key sections of code: /* * PopulateItems.java * * Inserts items from the iSeries ERP into the PDA database item file * */ package com.probuild.pda; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; /** * */ public class PopulateItems { private Connection iSeriesConn; private Connection sqliteConn; public final int DB_COMMIT_COUNT = 5000; /** * Creates a new instance of PopulateItems */ public PopulateItems() { // Establish connections to iSeries and SQLite DBs try { Class.forName( com.ibm.as400.access.AS400JDBCDriver ); String url =jdbc:as400://as400.dxlbr.com ;S10036FD;naming=sql;errors=full;; iSeriesConn = DriverManager.getConnection( url, SATCP, SATCP ); Class.forName( org.sqlite.JDBC ); sqliteConn = DriverManager.getConnection(jdbc:sqlite:\\pda.db); Statement statement = sqliteConn.createStatement(); // turn journaling off String sql = PRAGMA journal_mode=OFF;; statement.execute(sql); // turn journaling off sql = PRAGMA synchronous = OFF;; statement.execute(sql); statement.close(); sqliteConn.setAutoCommit(false); } catch (Exception e) { System.out.println ( e.getMessage() ); } } public void closeConnections ( ) { try { iSeriesConn.close(); sqliteConn.close(); } catch ( Exception e ) { System.out.println ( e.getMessage() ); } } public boolean buildItemTable (String locCode) { Statement iStmt = null; PreparedStatement pStmt = null; ResultSet iResult = null; boolean success = false; System.out.println( Building Item Table... ); try { pStmt = sqliteConn.prepareStatement( INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ); // SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept Code, Mfg Item# // Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N), RISBCD (P or C) String sql = SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT, RIMNF#, + RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD + FROM DRMS.DRITEMFL + WHERE RILOC# = + locCode + AND RIRLC# = 0 AND RISKU# 5 + ORDER BY RISKU#; iStmt = iSeriesConn.createStatement(); iResult = iStmt.executeQuery( sql ); System.out.println( iSeries select form dritemfl complete: ); int count = 0; while ( iResult.next ( ) ) { int sku = iResult.getInt( RISKU# ); long upc = iResult.getLong( RIUPC# ); //String sku = iResult.getString( RISKU# ); //String upc = iResult.getString( RIUPC# ); String alt = iResult.getString( RIAITM ); String desc = iResult.getString( RIDESC ); String uom = iResult.getString( RILSTU ); String dept = iResult.getString( RIDEPT ); String mnf = iResult.getString( RIMNF# ); String vendorItem = iResult.getString( RIVITM ); int minQ = iResult.getInt( RIMINQ ); int maxQ = iResult.getInt( RIMAXQ );
Re: [sqlite] SQL insert performance on Windows Mobile 6.5
Richard, Thanks for the reply. I will look into the profiling tools. The only diagnostic I have is the CPU utilization of the java process which is very high (90%) for the database update. -Tony -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 4:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL insert performance on Windows Mobile 6.5 On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony tony.bull...@probuild.comwrote: Hi, I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5 running on a MC9190-G mobile computer. I am using sqlitejdbc-v056.jar with the NSIcom CrE-ME 4.12 JVM. Performance of the insert is very low. I am getting about 1000 records added a minute. Any ideas the on how to improve the performance would be appreciated. On a workstation, with PRAGMA synchronous=OFF, you should be getting about 300,000 rows per *second*. Performance on your mobile computer will be less, of course, but it shouldn't be 18,000 times less. Do you have any profiling tools available to see what it taking so long? Code overview: 1. Query an iSeries database with the JTOpen package to pull 56,000 records. This is fairly quick, less than 10 seconds to complete. 2. For each record returned, create an insert batch statement. 3. When 1000 records are added to the batch, execute the batch update and commit the transaction (autocommit is disabled) 4. Two pragma statements are also used when the connection is created. a. PRAGMA journal_mode=OFF; b. PRAGMA synchronous = OFF; The key sections of code: /* * PopulateItems.java * * Inserts items from the iSeries ERP into the PDA database item file * */ package com.probuild.pda; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; /** * */ public class PopulateItems { private Connection iSeriesConn; private Connection sqliteConn; public final int DB_COMMIT_COUNT = 5000; /** * Creates a new instance of PopulateItems */ public PopulateItems() { // Establish connections to iSeries and SQLite DBs try { Class.forName( com.ibm.as400.access.AS400JDBCDriver ); String url =jdbc:as400://as400.dxlbr.com ;S10036FD;naming=sql;errors=full;; iSeriesConn = DriverManager.getConnection( url, SATCP, SATCP ); Class.forName( org.sqlite.JDBC ); sqliteConn = DriverManager.getConnection(jdbc:sqlite:\\pda.db); Statement statement = sqliteConn.createStatement(); // turn journaling off String sql = PRAGMA journal_mode=OFF;; statement.execute(sql); // turn journaling off sql = PRAGMA synchronous = OFF;; statement.execute(sql); statement.close(); sqliteConn.setAutoCommit(false); } catch (Exception e) { System.out.println ( e.getMessage() ); } } public void closeConnections ( ) { try { iSeriesConn.close(); sqliteConn.close(); } catch ( Exception e ) { System.out.println ( e.getMessage() ); } } public boolean buildItemTable (String locCode) { Statement iStmt = null; PreparedStatement pStmt = null; ResultSet iResult = null; boolean success = false; System.out.println( Building Item Table... ); try { pStmt = sqliteConn.prepareStatement( INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ); // SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept Code, Mfg Item# // Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N), RISBCD (P or C) String sql = SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT, RIMNF#, + RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD + FROM DRMS.DRITEMFL + WHERE RILOC# = + locCode + AND RIRLC# = 0 AND RISKU# 5 + ORDER BY RISKU#; iStmt = iSeriesConn.createStatement(); iResult = iStmt.executeQuery( sql ); System.out.println( iSeries select form dritemfl complete: ); int count = 0; while ( iResult.next ( ) ) { int sku = iResult.getInt( RISKU# ); long upc = iResult.getLong( RIUPC# ); //String sku = iResult.getString( RISKU# ); //String upc =
Re: [sqlite] Please help me optimize this LEFT JOIN query.
Thanks Petite Abeille, I translated your code to this: SELECT genres.genres, ( SELECT substr(group_concat(name,' '),1,60) FROM ( SELECTname FROM movies JOIN genres ONmovies.movies = genres.movies ORDER BY movies.name LIMIT 6 ) ) AS sample FROM genres GROUP BY genres ORDER BY genres.genres; time: 0.028s It is incredibly fast but it gives me the wrong result: Action|American Graffiti American Graffiti American Graffiti Mulhol Adventure|American Graffiti American Graffiti American Graffiti Mulhol Animation|American Graffiti American Graffiti American Graffiti Mulhol Biography|American Graffiti American Graffiti American Graffiti Mulhol Etc... Am I doing something wrong? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71035.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help me optimize this LEFT JOIN query.
On Sep 5, 2013, at 9:45 PM, Yuzem naujnit...@gmail.com wrote: It is incredibly fast but it gives me the wrong result: Yeah… you forgot the 'where' clause in the subquery… you need to restrict it on the genre ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
On Thu, 5 Sep 2013 19:53:15 +0100 Simon Slavin slav...@bigfraud.org wrote: On 5 Sep 2013, at 7:20pm, Peter Haworth p...@lcsql.com wrote: That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. An interesting point. If all the updates are done inside a COMMIT, then the conflict may not be recognised because by the time the first change is written back to the table, the conflicting entry has already been renumbered. I was puzzled by this thread, so ran my own little test. To my dismay, UPDATE is not atomic in SQLite. $ rm -f db; sqlite3 -echo db sql create table t ( PKey INTEGER PRIMARY KEY , Name TEXT , Sequence INTEGER , unique (Name, Sequence) ); insert into t (Name, Sequence) values ('Blue', 1); insert into t (Name, Sequence) values ('Blue', 2); insert into t (Name, Sequence) values ('Blue', 3); insert into t (Name, Sequence) values ('Blue', 4); insert into t (Name, Sequence) values ('Blue', 5); -- insert a new 3 BEGIN TRANSACTION ; UPDATE t SET Sequence = Sequence + 1 WHERE Sequence = 3 AND Name = 'Blue'; SQL error near line 17: columns Name, Sequence are not unique That's perfectly good SQL. SQLite is simply not executing the update atomically. Anyone tempted to protest may be forgetting atomic means more than all or nothing. It also means the DBMS may execute the transaction however it sees fit, at the cost of ensuring that constraints remain in effect upon commit but *only* upon commit. That's why this works: sqlite create table a (a int, b int check( a + b = 3)); sqlite insert into a values (1, 2); sqlite select * from a; a b -- -- 1 2 sqlite update a set a = b, b = a; sqlite select * from a; a b -- -- 2 1 Constraints hold for the whole database at all times whenever the user can see the data. Any update is valid if it can logically be applied and leave the database in a state consistent with its declared constraints. Here's how Marc L. Allen's query should work (different DBMS): $ bsqldb /tmp/sql PKey Name Sequence --- -- --- 1 Blue 1 2 Blue 2 3 Blue 4 4 Blue 5 5 Blue 6 6 Blue 3 6 rows affected Note that the final insert is assigned the next auto-generated PKey (6), and the old 3 is now 4, etc. Granted, it's not easy. The update processing cannot simply find each row in turn and update it (as appears to be the case now). One alternative would be to make a copy of the whole set, update it, and then apply it en masse. Another probably slower way would be to keep trying: if a row can't be updated, find one that can and continue until they're all done or you're painted into a corner and have to rollback. Doubtless there are better ways. No one ever said a DBMS was a simple creature! Simon mentioned http://www.sqlite.org/conflict.html This has nothing to do with deferred constraint resolution. Deferred constraint resolution involves more than one table being updated in a transaction, deferring constraint enforcement until the commit. See for example this bit of DB2 documentation, http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0633.htm. I also looked at http://www.sqlite.org/lang_conflict.html, which says, REPLACE When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. The order in which the rows are (internally) processed is arbitrary, and the results of using or replace here are predictably unpredictable: BEGIN TRANSACTION ; UPDATE or replace t SET Sequence = Sequence + 1 WHERE Sequence = 3 AND Name = 'Blue'; insert into t (Name, Sequence) values ('Blue', 3); COMMIT; select * from t; PKeyNameSequence -- -- -- 1 Blue1 2 Blue2 3 Blue4 5 Blue6 6 Blue3 but what's a Blue 5 among friends? :-/ As regards the documentation, the only fair thing to do for now would be to add UPDATE to the omitted page, explaining the row-by-rowism. I would also suggest flagging the UPDATE page itself, because the behavior is very much at variance with the standard. --jkl
Re: [sqlite] Sqlite handler in trigger
I am not sure if my problem I have stated clearly, found below detail explanation!!! Process 1: Handler1 = OpenConn(); Sqlite_Createfunc(Handler1, my_notifier_function()..); CREATE TRIGGER Event_test1 AFTER Update ON test BEGIN SELECT my_notifier_function(); END; Proecss2: Handler2 = OpenConn(); Update test SET value; In this scenario when proecss2 will do update then trigger logic will get executed my_notifier_function() function may get called. If above will not happen then please suggest what is best I can get from sqlite3 to handle this kind of scenario. May be any other possible operation? Thanks.. On Thu, Sep 5, 2013 at 6:55 PM, Igor Tandetnik i...@tandetnik.org wrote: On 9/5/2013 5:39 AM, techi eth wrote: I have case where one process is updating data with his private handler but trigger on update is created by other process by his private handler. I'm not sure I understand this sentence. When you run CREATE TRIGGER statement, the trigger you've just created becomes part of the database schema, visible to all connections (unless you do CREATE TEMP TRIGGER; is this what you are talking about?) On the other hand, sqlite3_create_function() registers a custom function just for this connection. If you create a trigger that calls this function, then those connections where the function is not registered will fail to even prepare an UPDATE statement - SQLite will issue an unknown function error. In general, there is no mechanism built into SQLite that would allow one process to be automatically notified that another process made a change to the database. If that's what you want, you would have to implement that in your application - you can't somehow trick SQLite into doing it for you. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Sqlite handler in trigger
On 9/6/2013 12:51 AM, techi eth wrote: I am not sure if my problem I have stated clearly, found below detail explanation!!! Process 1: Handler1 = OpenConn(); Sqlite_Createfunc(Handler1, my_notifier_function()..); CREATE TRIGGER Event_test1 AFTER Update ON test BEGIN SELECT my_notifier_function(); END; Proecss2: Handler2 = OpenConn(); Update test SET value; That last statement will fail with unknown function 'my_notifier_function' error. In this scenario when proecss2 will do update... In this scenario process2 will be unable to do update. If above will not happen then please suggest what is best I can get from sqlite3 to handle this kind of scenario. Once again: there is no mechanism built into SQLite that would allow one process to be automatically notified that another process made a change to the database. Which part of this sentence did you find unclear the first time round? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users