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/42999 Hello all, I am looking for a way to completely turn off the creation of journal files. Any help is much appreciated. Thanks. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Stephen Abbamonte 2008-12-01T15:46:21 http://comments.gmane.org/gmane.comp.db.sqlite.general/42997 Hello! Is it possible? Best regards, Alexey. _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Alexey Pechnikov 2008-12-01T14:56:26 http://comments.gmane.org/gmane.comp.db.sqlite.general/42992 To the best of my findings, it seems to me that one needs to write a mini SQL parser in order to read the 'sql' field from sqlite_master, in order to discover the fields in an Sqlite table. Is this really a necessary design? Would it not be better if sqlite3_table_column_metadata had a mode that could enumerate the columns in a table? Or am I missing something obvious? _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Ben Harper 2008-12-01T13:02:31 http://comments.gmane.org/gmane.comp.db.sqlite.general/42991 Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word.   Is there anyway to get partial matches for beginning or middle parts of a word?   e.g. *sion - to match version or *si* to match version   Thanks Rael _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Rael Bauer 2008-12-01T11:48:01 http://comments.gmane.org/gmane.comp.db.sqlite.general/42989 Hi all, I've trouble optimizing for an N:M mapping table. The schema of the table is this: CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL); I want to retrieve a list of n filtered on the presence of certain values of m, e.g. give me all n for which there is an m = 3 and m = 5, but no m = 7. A naive query would look like this: SELECT a.n FROM map a, map b, map c WHERE a.n = b.n AND a.n = c.n AND a.m = 3 AND b.m = 5 AND c.id not in (select id from map where c.m = 7); This can be slow, even for the more simple case with only positive selection: SELECT a.n FROM map a, map b WHERE a.n = b.n AND a.m = 3 AND b.m = 5; And this variation does not make it a lot faster: SELECT n FROM map WHERE m = 3 INTERSECT SELECT n FROM map where m = 5; There are about a million entries in the table map and want to increase to about 10 million. The current indexes are CREATE INDEX map_n ON map(n); CREATE INDEX map_m ON map(n,m); Is there a cleverer way of doing these queries? The fraction of n's t Jos van den Oever 2008-12-01T09:26:54 http://comments.gmane.org/gmane.comp.db.sqlite.general/42987 Hello, The simple query is like this: SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?; Account number 13100 is not present in the table bilance1 when pYear=2005 and it should be like this and therefore dbs is also not present. Other years account number 13100 is present and query works OK. When the query is run with a parameter pYear='2005' it returns nothing - the resultset is empty or nothing? (I am doing it from python25) Even if I change the query to : SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100' and pYear='2005'; it still returns NOTHING, not 0. The same happens also on MS SQLServer 2005 so I think it should be like that according to sql standards. If there is no account number 13100 in the table then the result is NOTHING, not NULL or 0. Strictly speaking I think also the value of 0 is not correct in this case but I would like to have it. It seams that coalesce can handle NULL not NOTHING. My question is: Is there an SQL way to handle above query t aivars 2008-12-01T08:26:30 http://comments.gmane.org/gmane.comp.db.sqlite.general/42981 Anybody that is way better than me please help. For the life of me I can not get this to work. select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 union all select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 union all select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 union all select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 union all select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; sticky1 2008-11-30T20:41:32 http://comments.gmane.org/gmane.comp.db.sqlite.general/42971 I have never posed in a group before forgive me if I am out of line with my question. I have a table called table1 With three fields: Field1, Field2, Field3 Here is my query but I get errors. Any help work be great select field1, field2 from ( select * from ee_table1 where field2 = 'blue' limit 1 and field1 < (select min(field1) from (select * from ee_table1 where field2 = 'white' limit 4 and field1 < (select field1 from ee_table1 where field2 = 'red' limit 1 ) order by field1 desc) a ) order by field1 desc ) previous_blue union all select field1,field2 from ( select * from ee_table1 where field2 = 'white' limit 4 and field1 < (select field1 from ee_table1 where field2 = 'red' limit 1) order by field1 desc ) previous_5_white union all (select field1, field2 from ee_table1 where field2 = 'red' limit 1) union all select field1,field2 from ( select * from ee_table1 where field2 = 'white' limit 4 Webmaster 2008-11-29T23:52:59 http://comments.gmane.org/gmane.comp.db.sqlite.general/42964 Hi, We have database table, and it is being written, sometimes 30 times a second. Some of the data succeeded, but some aren't. How should we handle this sort of lock issue ? thanks Richard K _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Richard Kim 2008-11-28T22:16:17 http://comments.gmane.org/gmane.comp.db.sqlite.general/42960 This seems basic, so please excuse me if it is a common question. I have a table defined like this: CREATE TABLE map ( runID INT, type TEXT, itemID int, att0 NUMERIC, att1 NUMERIC, PRIMARY KEY ( runID, type, itemID ) ); Here are the first four rows: 1|c|0|0.0290534291416|-0.580828785896 1|c|1|-0.0737001597881|1.31409645081 1|c|2|-0.957751333714|0.651466667652 1|c|3|-0.229464128613|0.659206032753 Adding values to att0 (one of the numeric columns) gives this odd result: sqlite> select itemid, att0, (att0 + 0.95) dx from map; 0|0.0290534291416| 1|-0.0737001597881| 2|-0.957751333714|-0.00775133371400005 3|-0.229464128613| 4|-1.05415046215|-0.10415046215 ... 7|1.09137058258|2.04137058258 I don't understand why some values of att0 cannot be added to 0.95 and don't see a pattern (which is why I included row 7. Could someone explain please? I am using sqlite3 3.5.9 on ubuntu to query the database. I am using python 2.5 to create and enter the numbers. Tom tom_slee 2008-11-28T16:25:10 http://comments.gmane.org/gmane.comp.db.sqlite.general/42936 Hi, I have a left join problem with SQLite. this Select SELECT ProjectId, InputId, DataValue, Formula FROM ProjectData WHERE (ProjectId = < at >Id) contains almost 15 000 rows. this Table ExcelMapValue contains almost 5 000 rows. and contains these columns InputId, sheetCode, rowIndex, ColumnIndex When I run this, it takes at least 2 minutes or it says out of memory Select * From ExcelMapValue OUTER JOIN (SELECT ProjectId, InputId, DataValue, Formula FROM ProjectData WHERE (ProjectId = < at >Id)) derivedtbl_1 On ExcelMapValue.InputId = derivedtbl_1.InputId Anyone can help me ? I don't understand why it takes forever to run. In Sql server it runs in less than a second. Tommy Anderson 2008-11-27T21:38:37 http://comments.gmane.org/gmane.comp.db.sqlite.general/42927 Morning, I have been playing around with SQLite to use as an alternative to one of our proprietary file formats used to read large amounts of data. Our proprietary format performs very badly i.e. takes a long time to load some data; as expected SQLite is lighting quick in comparison - great! One considerable stumbling block is the footprint (size) of the database file on disk. It turns out that SQLite is roughly 7x larger than our proprietary format - this is prohibitive. The data is pretty simple really, 2 tables Table 1 BIGINT (index), VARCHAR(30), VARCHAR(10) Table 2 BIGINT (index), FLOAT For a particular data set Table1 has 1165 rows and Table 2 has 323 rows, however typically Table 2 becomes bigger for larger models. The size on disk of this file is 11.8 Mb (compared to 1.7 Mb for our proprietary format). I have noticed that if I drop the indexes the size drops dramatically - however the query performance suffers to an unacceptable level. For a larger model the DB footprint is Simon Bulman 2008-11-27T08:12:02 http://comments.gmane.org/gmane.comp.db.sqlite.general/42925 Hi, I would like to insert into sometable values ( someid , text ) ; but 'text' contains a few commas, and then it would look like (...) values ( someid , text , text2 , text3 ) ; Is there a syntax to do that properly, maybe like C string constants ("text,\"text\",text\n")? Thanks, Maurício _______________________________________________ sqlite-users mailing list sqlite-users< at >sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Mauricio 2008-11-27T13:07:06 http://comments.gmane.org/gmane.comp.db.sqlite.general/42922 i have query that don't work in sqlite but in mysql work and make good result. sqlite : table definition CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product varchar(60),  Location varchar(50), "begin" datetime); CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product varchar(60), Location varchar(50),"end" datetime); CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50), Product varchar(60), Location varchar(50), Begin datetime, End datetime, Difference integer, PRIMARY KEY (Code,Level,Category,Product,Location,Begin,End)); query : insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin, akhir1.End, strftime("%s",akhir1.End)-strftime("%s",awal1.Begin) as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1.End >= awal1.Begin group by awal1.Beg Rachmat Febfauza 2008-11-27T04:08:06 http://comments.gmane.org/gmane.comp.db.sqlite.general/42920 Good afternoon list, Is there any way to open a SQLite databases out of an in memory buffer rather than an operating system file (via a VFS)? The runtime environment of one of our platforms does not have local storage that can be accessed via a normal operating system VFS but via an interface that accesses very slow secure storage. This interface only supports passing files in and out via memory buffers and is hideously slow. On our other platforms we load our database into an completely memory resident database (:memory:) from an database file on read only storage which we attach to during start up and then detach once we've finished copying our data into memory. This approach has worked quite well so far, as it avoids SQLite attempting to access storage (which is read only) except during set-up. So we would quite like to keep using this approach if possible but are unsure about how to solve this new problem with working with the buffers coming from or going into secure storage. One solution would se Brown, Daniel 2008-11-27T01:12:09 http://comments.gmane.org/gmane.comp.db.sqlite.general/42918 In a test harness, I'm using a sqlite3_prepare/bind/step/finalize sequence to add rows to a table, and then add them again, intentionally violating the unique index constraints. It appears that sqlite3_step reports the violation (sqlite3_errorMessage), and then the details (the offending column numbers) are only reported when I check the error in the sqlite3_finalize() routine ... ::: Error ::: sq3_step: Error 19 returned from sqlite3_step. constraint failed ::: Error ::: sq3_finish: Error 19 returned from sqlite3_finalize. columns sname, fname, dob are not unique This is not a problem, per se, but in an ideal world, one might handle the error one time, and receive all the relevant information at one go. Is there a reason to defer reporting the details until the transaction is completed? Just wondering ... Cheers, Rob Sciuk _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listi Rob Sciuk 2008-11-26T23:12:17 http://comments.gmane.org/gmane.comp.db.sqlite.general/42910 Hi all, The following session crashes SQLite3.exe 3.6.6.2 on Windows XP: sqlite> create table a (b); sqlite> select max(b) as q from a where q = 1; SQLite prevents me from directly using max(b) in the 'where' clause, but I think it gets fooled by the alias. Thanks, Dan _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Daniel Zingaro 2008-11-26T20:18:42 http://comments.gmane.org/gmane.comp.db.sqlite.general/42909 Good Morning list, Is there any functionality built into SQLite to generate CRC values for tables? We would like to be able to verify that the contents of the table we just updated matches the intended contents. Currently using our old database solution we generate a CRC value for each table and compare them. Is there similar functionality in SQLite? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Brown, Daniel 2008-11-26T19:37:19 http://comments.gmane.org/gmane.comp.db.sqlite.general/42906 Hi all, I recently realized that sqlite extension DLL loading could use a little help from LoadLibraryEx(path, NULL, LOAD_WITH_ALTERED_SEARCH_PATH), unless extension DLLs were always put in the system directory. The only drawback of this function is that doesn't support Windows CE and Windows 9x. Regards, /Mike/ _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users barabbas-Re5JQEeQqe8AvxtiuMwx3w< at >public.gmane.org 2008-11-26T18:26:51 http://comments.gmane.org/gmane.comp.db.sqlite.general/42905 SQLite version 3.6.6.2 is now available for download on the SQLite website http://www.sqlite.org/download.html Version 3.6.6.2 fixes a bug that could potentially lead to database corruption. The bug was introduced in SQLite version 3.6.6. The bug was discovered by an assert() failure during stress testing and has not been seen in the wild. Database corruption resulting from this bug is still only a theoretical possibility. Despite focused efforts, we have so far been unable to generate a test case that actually causes database corruption. Nevertheless, the nature of the bug suggests that such a test should be possible, even if it is difficult to devise. The fact we have have been unable in creating a test case suggests the bug is unlikely to be causing problems in the real world. Even so, we are issuing this branch release out of an abundance of caution and recommending that all users of SQLite version 3.6.6 and 3.6.6.1 upgrade to version 3.6.6.2. D. Richard Hipp drh D.Richard 2008-11-26T18:15:38 http://comments.gmane.org/gmane.comp.db.sqlite.general/42897 Hi, according to documentation, "An application is allows to prepare multiple SQL statements in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding prepared statements." I am using two prepared statements in my code (that does INSERT), following the pattern prepare(stmt1) prepare(stmt2) while (xx) { bind(stmt1) step(stmt1) reset(stmt1) bind(stmt2) step(stmt2) reset(stmt2) } The first iteration works, but int the second step() fails with code 1. I have also double-checked that I give the correct statement objects etc. Commenting out one of them works. I running 3.6.6.1 on WinXP. I am thinking of trying to add both INSERT's inside one statement insted, hopefully that will work better.. -Oyvind _______________________________________________ sqlite-users mailing list sqlite-users-CzDROfG0BjIdnm+yROfE0A< at >public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Oyvind Idland 2008-11-26T16:14:21 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.sqlite.general