http://blog.gmane.org/gmane.comp.db.sqlite.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.sqlite.general/74657 <pre> My desire is to have a Fossil code-sprint on July 3. Come and bring your laptop and be prepared to hack. I'll have contributor license agreements&lt;http://www.fossil-scm.org/fossil/doc/trunk/www/copyright-release.html&gt;on hand for you to sign if you have not done so already, I will be handing out passwords for push privileges on the core Fossil repository, and I will providing guidance and training to anybody who is interested in how to get started in hacking Fossil. Ideas for things to implement at the sprint include: * WYSIWYG wiki editing * Web-based content file editing * User preferences stored in a cookie * Automatically adjust side-by-side font sizes to fit the screen * Full text search * Better labeling of attachments * Fix the private branch mechanism to make it easy to publish a private branch * Automated self-test The above list will likely grow. So if you are planning to be in the Munich area on July 3 please stop by Contact Stephen Beal for information on the venue. If this code sprint w</pre> Richard Hipp 2012-05-25T19:45:01 http://comments.gmane.org/gmane.comp.db.sqlite.general/74655 <pre> How to reproduce: obtain any SQLite database with 2 or more tables in it (I used "CREATE TABLE a (x INTEGER PRIMARY KEY); CREATE TABLE b (y INTEGER PRIMARY KEY)"). Compile and run the following C# code: SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder(); connBuilder.DataSource = &lt;Path to the database&gt;; SQLiteConnection conn = new SQLiteConnection(connBuilder.ConnectionString); DataSet dataSet = new DataSet(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(&lt; at &gt;"SELECT * FROM a; SELECT * FROM b;", conn); dataAdapter.FillSchema(dataSet, SchemaType.Source); The last line mysteriously throws NullReferenceException — apparently, something went wrong deep inside System.SQLite.Data assembly, but I don't have its source code at the hands. I would appreciate if someone pointed me at a _nice_ workaround other than "Don't use FillSchema()". _______________________________________________ sqlite-users mailing list sqlite-users&lt; at &gt;sqlite.org http://sqlite.org:8080/cgi-bin/mailman/list</pre> z Kemtauros 2012-05-25T19:21:48 http://comments.gmane.org/gmane.comp.db.sqlite.general/74653 <pre> http://swatim.sw.ohost.de/gertycamp3.html </pre> Frank Chang 2012-05-25T18:56:24 http://comments.gmane.org/gmane.comp.db.sqlite.general/74652 <pre>I am running the following query - CREATE TABLE XYZ AS SELECT ID, MIN (DATE) FROM SOURCE-TABLE WHERE CRITERIA GROUP BY ID ; SOURCE-TABLE has 600 million rows, 2 million meet the WHERE criteria, and get grouped to 100,000 distinct IDs. There are no indexes as there is too much variety in the WHERE clauses and index creation on this table takes many hours. Temp_store=memory. When executed - the source table is read very slowly, and runtime is 1/2 hour. I split this SQL into 2 steps - the first just extracting - the second just grouping. The extract read the table extremely fast and finished in 3 minutes. The GROUP BY ran in a few seconds. So a 10X improvement - which is pretty incredible speed. Why such a large disparity? Why wouldn't the single-step SQL read fast - write the accepted values to TEMP - then sort and group - same as the 2-step? Any way to influence the SQlite SQL processor to chose a more optimal single-step approach? Thanks ! </pre> Udi Karni 2012-05-25T18:38:54 http://comments.gmane.org/gmane.comp.db.sqlite.general/74648 <pre>We were just having a discussion on a different thread on the relative merits of static versus dynamic linking. Notice that if the application where statically linked (if that were possible in C#) then all the grief that Peter is having would have never come up. The application would just work, and everybody would be happy. On Fri, May 25, 2012 at 10:56 AM, Peter Walburn &lt; Peter.Walburn-pm10qGyYuvuzR313v1King&lt; at &gt;public.gmane.org&gt; wrote: </pre> Richard Hipp 2012-05-25T15:56:41 http://comments.gmane.org/gmane.comp.db.sqlite.general/74644 <pre>Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: "The message's content type wa</pre> Peter Walburn 2012-05-25T14:57:37 http://comments.gmane.org/gmane.comp.db.sqlite.general/74643 <pre>Hi, I have an application written in C# .Net 4.0 Framework. I use SQLite within the application. I have recently updated from an older version of SQLite to the latest version as I have moved to .Net Framework 4.0 and I received error messages about Mixed Mode Frameworks. Anyway, I do the development on an 64-Bit Windows 7 operating system. I use Installshield 2010 Express to create an installation for the application. The application works ok on the Windows 7 PC, but when installed on a different PC (or on a Virtual Client PC using VMWare), I always receive a message such as: Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007e). I have tried to post this email about 5 times and it is always returned saying: "The message's content type wa</pre> Peter Walburn 2012-05-25T14:56:11 http://comments.gmane.org/gmane.comp.db.sqlite.general/74635 <pre>Now I am writing datas to sqlite3 DB in memory(":memory:") in a thread . And concurrently I want to read the datas of the same DB in memory too. but While i used the same object of sqlite3 to write and read, i had got the error:SQLITE_MISUSE. If i use the different objects of sqlite3 to write and read concurrently,these objects get the different DB in memory.This is not I want to do.I want to get datas of the same database. Moreover, I don't plan to write and read the database in disks too.Becase I thick it's more faster to operate in memory. I open the database in the way of "m_sqliteDB.open(":memory:");" now. How to write and read the same sqlite3 DB in memory concurrently in two thread? That might be? thank you. </pre> 吴 毅 2012-05-25T08:51:27 http://comments.gmane.org/gmane.comp.db.sqlite.general/74609 <pre>Hi, I have converted a legacy program that has used a Microsoft Jet (Access) database. The program is written in Visual C++. It is an client-server MFC application that used OLE-DB interfaces to directly connect to the database. I have managed to write a drop-in replacement for all the OLE-DB data accessors, and now they all are sqlite3 interfaces. OK, this is the introduction. Now, to the problem itself. The program has a global object called g_session. It used to be an ATL CSession-derived class that instantiated an OLE-DB connection at the program startup and closed the connection at the program end. I have converted the class, so it contains a sqlite3* member that gets opened using sqlite3_open_v2 at the program startup and closed using sqlite3_close at the end. As you can imagine, this global g_session object is used everywhere in the program for all the selects, updates, inserts and deletes. The program is a multi-user program that runs across the network in Windows environment. The users constantl</pre> Andrew Cherednik 2012-05-25T00:55:51 http://comments.gmane.org/gmane.comp.db.sqlite.general/74596 <pre>Hello! I have a program that does some math in an SQL query. There are hundreds of thousands rows (some device measurements) in an SQLite table, and using this query, the application breaks these measurements into groups of, for example, 10000 records, and calculates the average for each group. Then it returns the average value for each of these groups. The query looks like this: SELECT strftime('%s',Min(Stamp)) AS DateTimeStamp, AVG(P) AS MeasuredValue, ((100 * (strftime('%s', [Stamp]) - 1334580095)) / (1336504574 - 1334580095)) AS SubIntervalNumber FROM LogValues WHERE ((DeviceID=1) AND (Stamp &gt;= datetime(1334580095, 'unixepoch')) AND (Stamp &lt;= datetime(1336504574, 'unixepoch'))) GROUP BY ((100 * (strftime('%s', [Stamp]) - 1334580095)) / (1336504574 - 1334580095)) ORDER BY MIN(Stamp) The numbers in this request are substituted by my application with some values. I don't know if i can optimize this request more (if anyone could help me to do so, i'd really appreciate).. This SQL query can b</pre> Nick 2012-05-24T19:59:27 http://comments.gmane.org/gmane.comp.db.sqlite.general/74584 <pre>Is this Select statement valid? In Oracle, it wouldn't be because what is the aggregate of A. Is this behavior defined anywhere? create table T (A,B); insert into T (A,B) values (1,3); insert into T (A,B) values (2,3); select A,B from T group by B; </pre> Mike King 2012-05-24T17:22:18 http://comments.gmane.org/gmane.comp.db.sqlite.general/74578 <pre>How can I construct a update query to calculate and set a record field "latency" with the difference between "timestamps" by "deviceid"? Appears sqlite doesn't support lag and lead. ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the main ones of interest are 'timestamp', 'latency' and 'DeviceID'. 2012-05-01 13:12:11.103 Null 14356 2012-05-01 13:12:11.103 Null 14372 2012-05-01 13:12:11.103 Null 4356 2012-05-01 13:12:07.103 Null 14356 .... 2012-05-01 13:12:11.221 Null 14356 The data is collected inconsistently... many times out of sequence I want to update the latency field for each record with the latest previous timestamp for the same DeviceID. Run this update query every time new data is appended to the table. Periodically recalc the entire table if an out of sequence file is processed or at minimum recalc from the earliest out of sequence time to present. I can do this fairly easily in excel but am struggling in sql/sql</pre> IQuant 2012-05-24T15:46:31 http://comments.gmane.org/gmane.comp.db.sqlite.general/74577 <pre>Dear all, On the support page http://www.sqlite.org/howtocompile.html, it says: "The use of the amalgamation is recommended for all applications." Is this a general recommendation, to use the amalgamated source file as the preferred way of including SQLite functionality in one's application, rather than using a separately compiled library? Or should I read this as a recommendation just in case I need to compile SQLite from source, and need to decide between using the amalgamation or using the individual source files? Kind regards, Sidney </pre> Sidney Cadot 2012-05-24T15:43:31 http://comments.gmane.org/gmane.comp.db.sqlite.general/74575 <pre>Hello, SQLite version 3.7.9 2011-11-01 00:52:41 Valgrind reports some memory leaks : ==9709== HEAP SUMMARY: ==9709== in use at exit: 94,248 bytes in 42 blocks ==9709== total heap usage: 1,338 allocs, 1,296 frees, 1,066,595 bytes allocated ==9709== ==9709== 80 bytes in 1 blocks are possibly lost in loss record 7 of 28 ==9709== at 0x4C2779D: malloc (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so) ==9709== by 0x437230: sqlite3MemMalloc (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x437C47: mallocWithAlarm (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x437CE2: sqlite3Malloc (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x437D30: sqlite3_malloc (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x43E1BC: findInodeInfo (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x4405CC: fillInUnixFile (in /home/huji/undev/vhs/Sondes/unix/sondes.exe) ==9709== by 0x440EA5: unixOpen (in /home/huji/undev/vhs/Sondes/unix</pre> Alfred Sawaya 2012-05-23T13:22:09 http://comments.gmane.org/gmane.comp.db.sqlite.general/74566 <pre>I've downloaded and compiled icu.c according to the instructions in the included README (though I had to add -fPIC to the compiler options). Now, when searching a table, I'm not getting the kind of diacritic-insensitive behavior I was expecting: sqlite&gt; .load lib/libSQLiteICU.so sqlite&gt; select * from owner where firstname like '%dré%'; id firstname last emai phon netid ---- ------------- ---- ---- ---- ------------- 2 André-Marie Ampère ampere-hcDgGtZH8xNBDgjK7y7TUQ&lt; at &gt;public.gmane.org 555-2222 ampere sqlite&gt; select * from owner where firstname like '%dre%'; sqlite&gt; I expected both statements to return the same result. Am I overlooking something or do I misunderstand the capabilities of ICU's "unicode-aware LIKE operator"? </pre> Courtney Grimland 2012-05-23T18:11:40 http://comments.gmane.org/gmane.comp.db.sqlite.general/74563 <pre>Hi, I was always under the impression that prepared statements can only be used from one thread at a time, so if 2 threads need to perform the same query independently, you need to have a prepared statement for each thread. Now I came across the following which seems to contradict this: " http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized SQLITE_CONFIG_SERIALIZED There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time. If SQLite is compiled with the SQLITE_THREADSAFE=0 compile-time option then it is not </pre> Sander Jansen 2012-05-23T17:51:34 http://comments.gmane.org/gmane.comp.db.sqlite.general/74558 <pre>The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also very different. SQLITE_LOCKED implies that the contention is on the same connection whereas SQLITE_BUSY implies that the contention is from another connection and can be handled via a busy handler. The error message reported from sqlite3_errmsg is very deceiving though... SQLITE_BUSY -&gt; "database is locked." SQLITE_LOCKED -&gt; "database table is locked." For days now, I kept receiving a "database is locked." error in my logs thinking the contention was due to a single connection only to just now realize the error code was indeed SQLITE_BUSY. Can we instead change the error message to read: SQLITE_BUSY -&gt; "database is busy." SQLITE_LOCKED -&gt; "database table is locked" -Shaun Shaun Seckman Firaxis Games Programmer </pre> Shaun Seckman (Firaxis 2012-05-23T16:39:20 http://comments.gmane.org/gmane.comp.db.sqlite.general/74551 <pre> When trying to analyze a 3.5 GB database file (which passes PRAGMA integrity_check) using the Windows analyzer provided in the download page, it fails immediately with an "Empty or malformed database: db.sqlite" error message. It works on some smaller databases I've tested. My guess is that this is caused by a 32-bit signed integer overflow on line 42 of tool/spaceanal.tcl </pre> Demper Delic 2012-05-23T10:06:54 http://comments.gmane.org/gmane.comp.db.sqlite.general/74550 <pre>Hello List! I see unexpected behavior using sqlite3's .read command. The basic problem is illustrated by the following test file: C:\&gt;type test2.sql -- two errors, sqlite3 exits select count(*) from no_table_a; select count(*) from no_table_b; C:\&gt; Here's the result of running sqlite3 and .read-ing the test file: C:\&gt;sqlite3.exe SQLite version 3.7.12 2012-05-14 01:41:23 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite&gt; .read test2.sql Error: near line 2: no such table: no_table_a Error: near line 3: no such table: no_table_b C:\&gt; Notice that sqlite3 has exited rather than returning to the "sqlite&gt;" prompt. As far as I can tell this only happens when the file processed by .read contains precisely two errors. I am running SQLite version 3.7.12 2012-05-14 01:41:23 on 64-bit windows 7. My copy of sqlite3.exe came from sqlite-shell-win32-x86-3071200.zip that I downloaded on 5/18/12. Best regards. K. Frank P.S. Here are </pre> K. Frank 2012-05-22T22:27:00 http://comments.gmane.org/gmane.comp.db.sqlite.general/74549 <pre>Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I need help around sqlite?????, my question: How question in sqlite the sentence like the sentence in Posthe tgreSQL: data -&gt; date type -&gt; example -&gt; 22/05/2012 -&gt; diferent -&gt; "22/05/2012" question/sentence: select *from tcontsif01 where data&gt;='01/01/2012' and data&lt;='01/05/2012' in sqlite ?????? not type data, only TEXT -&gt; in sqlite 22/05/2012 like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by negative, negative -&gt; select *from tcontsif01 where data&gt;='01/01/2012' and data&lt;='01/05/2012' order by data -&gt; negative -&gt; order by ASCII in sqlite please, one light, please solution, please example, please help me I understand datetime, understand strtime please help me, please a example routine sql for sqlite3 with functions, Marcelo Paiva Stante Santa Catarina - Brasil - here is not tropical here is subtropical here is cold, but here is Brasil ... please help me </pre> Marcelo Paiva 2012-05-22T18:25:15 http://comments.gmane.org/gmane.comp.db.sqlite.general/74548 <pre>Hi all, I need to disable the Sqlite3 locking system for a Windows app that runs on a Mac using the Wine/Crossover API, this due to the fact Wine/Crossover have problem to manage the locking status on a network shared drive. My app already has a built-in locking system so it could runs also without the Sqlite3 internal lock system. So, is there a way to disable the Sqlite locking system (windows) ? As I know this is already available in Sqlite3 unix using the unix-nolock parameter in lib compilation. Thanks in advance Best Regards, Marco </pre> Marco Turco 2012-05-23T11:11:26 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general