[sqlite] Best page size and cache size in high memory environment
Hi All, My application uses temporary tables that can become quite big (300MB), so it makes sense to me to change the page size and cache size with pragma statements. The docs say The default suggested cache size is 2000 pages and The normal configuration for SQLite running on workstations is for atomic write to be disabled, for the maximum page size to be set to 65536, for SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the maximum default page size to be set to 8192. So I set my page size to 8192. That means the suggested cache size is still only 16MB. If I increase my cache size to 20 000 pages I see a lot more that 10x increase in memory use and also very slow connection close, presumably due to deallocation of page memory. Should I increase page size instead? What do you guys do when it is OK for SQLite to chew as much memory as you have? Also, does it make sense to change the page size only for the temp database? pragma temp.page_size=65536 ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: Binding Arrays
Fantastic! I've been wanting this for a long time. Since which version do we have sqlite3_intarray_x? On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp d...@sqlite.org wrote: Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature Request: Binding Arrays
Since version 3.6.21, circa 2009-12-07. Note however that this capability is not built in. It is an extension that you need to compile and link separately. OK... Herewith my vote to make it standard then, like SQLITE_ENABLE_COLUMN_METADATA was enabled for the precompiled binary at some point. I prefer to stick to the precompiled binaries. Besides, it would make the sqlite3_intarray functions more visible in the documentation, etc. I'm sure I'm not the only one that didn't know about this very useful functionality. It's about time the binary got slightly bigger ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
Tim, Simon Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too outlandish if MySQL does it my way. Simon says: The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen. I would venture to say perhaps the standard wasn't too clear on this, or at the very least the fact that MySQL does it differently means there is a bit of a smudge on this part. Darren says: declaring NUMERIC types is saying you don't care about the behavior. I do care about behaviour, so I'll change my management system to exclude NUMERIC as an option since I have no use for it then! I cannot expect my clients to know little quirks to this level of detail. I agree with what Darren says about the option of having 2 operators, / and div, that's what MySQL does and it is also a feature of Pascal and other languages. Please don't get me wrong. I haven't used MySQL for new projects in years, so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous in the SQL world, I wouldn't even have raised the issue. If I am correct in taking away from this discussion don't use NUMERIC column definitions if you want to do any calculations [with divisions] on them, then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
I should have asked you for (1,2,20) as well and we could see whether it outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC means FLOAT. Of course it does! All the others too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in type conversion prior to comparison
Actually, to be more accurate, the internal storage may be far from a float (as in IEEE double) but a divide on an integer-looking value will certainly be done with floating point math. On Mon, May 13, 2013 at 6:13 PM, Paul van Helden p...@planetgis.co.zawrote: I should have asked you for (1,2,20) as well and we could see whether it outputs '10' or '10.0'. But yes, it would appear that in Oracle, NUMERIC means FLOAT. Of course it does! All the others too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
On Sun, May 12, 2013 at 1:54 PM, Michael Black mdblac...@yahoo.com wrote: PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you would not have known to turn it on (default would have to be OFF for backwards compatibility). I will use it on every connection I make in future to avoid future pain. (I have a SQLite management system where my clients can create their own tables and enter their own SQL for custom reports) On Sun, May 12, 2013 at 2:35 PM, Simon Slavin slav...@bigfraud.org wrote: I think it's endemic to computers, the same as the difficulty with counting in units of 0.1 until you get 1. On Sun, May 12, 2013 at 2:59 PM, Yan Seiner y...@seiner.com wrote: If you want floats, you have to specify floats. If you want integers, you have to specify integers. The compiler has no way to know which you want. Just get in the habit of always adding a .0 if you want float constants. I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no way to specify a float when you insert into a NUMERIC. 1.0 turns into an integer. Then you do a division on all rows with an SQL select and you get mixed results because some rows have floats and some rows have integers. In C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts like Pascal. This is not my main issue. Consistency throughout a table, is. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
What do you mean, select precision? The double value you pass to sqlite3_bind_double() will be used as is. Are you saying you want to round it first? Then go ahead and do that - I'm not sure what that has to do with SQLite. -- It is an issue with SQLite because the values in NUMBER(10,2) have no effect. Too often I see small values with 15 digits in a table because a double was passed as-is. It is not just about space, it is also about presentation. In engineering we are taught that the number of digits should also tell you the accuracy of the sample, so for example a latitude/longitude obtained from a handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just junk. Since the database does not do this for you, when the programmer knows the accuracy of the sample, he shouldn't be lazy and instead do Round(Longitude*100)/100 before binding. Of course, when the data is presented it should be properly rounded with zeros added at the end or even zeros replacing digits to the left of the decimal (to indicate precision), but my point is you shouldn't store junk digits in the first place. I love it that you don't have to specify TEXT and NUMBER lengths, but would have preferred that SQLite didn't ignore them when specified and that in a NUMBER(p,s) column, the double is stored as an integer internally if p=18. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to select a precision?
A delared type of NUMBER(10,2) has NUMERIC affinity, which means that SQLite will attempt to store (string) values as integers first and floats second before giving up and storing strings. This has nothing to do with my reply and I understand how it works. You do realize that there are decimal numbers that have infinite binary expansions? I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know they are the same in SQLite). You are also talking presentation (as in formatting) of numeric values as opposed to representation (as in storing/retrieving). The former is best handled in the user interface while the latter is the subject of database engines. My point was about not storing binary junk - the part of a number that has no meaning because the accuracy of the inputs is limited. When you have a generic db manager that can show any table or if you are looking at the results of your own SQL statement, it helps to reduce clutter on the screen. The data also compresses better. Fatihful reproduction of formatting would be possible using TEXT affinity and calling sqlite3_bind_text. Performing arithmetic with these numbers would however be tricky, slow and would still not guarantee that calculated values would conform to the desired formatting. Of course, but in most cases we don't need to go this far. My main point is about rounding before binding; my secondary point that scale in a column definition can be desirable to avoid it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRUNCATE TABLE alias for DELETE FROM
Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
It would be possible to implement TRUNCATE TABLE on top of that, but this would be only syntactic sugar. ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if the solution is that simple and won't break anything. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unofficial poll
I am using this feature a lot. My applications log all changes to the database, SQL and parameters. So I have an attached log.db with a field for the SQL and then 32 typeless columns for the parameters. Works like a charm! On Sun, Sep 23, 2012 at 12:37 PM, Baruch Burstein bmburst...@gmail.comwrote: I am curious about the usefulness of sqlite's unique type handling, and so would like to know if anyone has ever actually found any practical use for it/used it in some project? I am referring to the typeless handling, e.g. storing strings in integer columns etc., not to the non-truncating system e.g. storing any size number or any length string (which is obviously very useful in many cases). Has anyone ever actually taken advantage of this feature? In what case? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select rows where a column is not unique
On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M. jmtils...@st-claire.orgwrote: Guys, I'm sure this is a pretty lame question, but my thinking hat is malfunctioning this morning. How can I select all rows from a table where a specific column is NOT UNIQUE? Table has three columns (charge_code, mnemonic, description). SELECT * FROM tablename WHERE charge_code IN (SELECT charge_code FROM tablename GROUP BY description HAVING Count(*)1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
Hi, Is this correct? Should update triggers not only fire for actual changes? I have a large table with a column which contains all NULL values except for 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, except it fires for every row. Thanks, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
You are right, sorry, just checked. sqlite3_changes returns number of records hit, not changed. Have been using sqlite for 2 years now and was always under the impression this was for actual changes. But which is better behaviour, reporting row hits versus real changes? Especially when it comes to triggers? On Tue, Jul 3, 2012 at 2:19 PM, Yuriy Kaminskiy yum...@gmail.com wrote: Paul van Helden wrote: Is this correct? Should update triggers not only fire for actual changes? I have a large table with a column which contains all NULL values except for 4. I expected an UPDATE table SET column=NULL to only fire 4 triggers, except it fires for every row. I'm pretty sure that sqlite3_changes() in this case also returns *all* rows, not only 4 really changed. If you want triggers to only fire for really changed rows (and sqlite3_changes() to return only those 4 rows), you should add WHERE clause: UPDATE table SET column=NULL WHERE column IS NOT NULL; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
The statement UPDATE table SET column=NULL updates every row in the table. The fact that some rows may already have a NULL in that column is not important. Well, it is important to me, the word change means before != after :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 2:43 PM, Paul van Helden p...@planetgis.co.zawrote: The statement UPDATE table SET column=NULL updates every row in the table. The fact that some rows may already have a NULL in that column is not important. Well, it is important to me, the word change means before != after :-) Just checked MySQL: UPDATE testtable SET testrow=NULL; Affected rows: 40 UPDATE testtable SET testrow=NULL; Affected rows: 0 That is what I'm familiar with. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 2:45 PM, Black, Michael (IS) michael.bla...@ngc.comwrote: What's better is that it tells you what you asked for...not what you think you asked for...which it does. I asked for changes :-) You've already been shown the correct solution...a WHERE clause... I've done that even before posting here, just thought it odd. You want sqlite to do a complete record compare, including following update triggers, on EVERY record it looks at to see if something happened to change??? Just the fields in the SET clause. Yuck... As compare to the WHERE clause which does exactly what you want and runs oodles faster (in all likelihood). I always keep an eye on the affected rows to see what my statements have done (whether I used a WHERE or not). Even with a WHERE, I would prefer seeing actual changes :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
On Tue, Jul 3, 2012 at 3:03 PM, Black, Michael (IS) michael.bla...@ngc.comwrote: And Oracle says the opposite: Yet they all give the same answer when done with update testtable set testrow=null where testrow not null; You keep hammering this one, it is obvious, I understand, THANKS! What if the SET and WHERE contain many columns? Now I have to add a WHERE columnmynewval for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert complex where clause AND col1?1 AND col2?2 AND col3?3. (passing a null parameter to the above won't even work!) Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL create table testtable(testrow number); Table created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL insert into testtable values(NULL); 1 row created. SQL update testtable set testrow=null; 3 rows updated. SQL update testtable set testrow=null; 3 rows updated. No surprises there. Oracle has never managed to impress me. SQL update testtable set testrow=null where testrow is not null; 0 rows updated. So rather than holding your breath for Oracle to change I'd recommend you do it the portable way. I'm not waiting for anything. My last question was simple: which is better? Since MySQL does it the correct way perhaps we can just think about this for sqlite4? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0
Then why do you keep hammering on the idea that SQLite is somehow incorrect or wrong? You've explained what you're trying to do. We've explained there is a better way to do that, that also happens to provide the correct answer on all platforms, AND likely runs faster-- especially if any of those columns has an index on them. I gave a simple example. I work with generic cases. My application doesn't have all static SQL. A lot is from the user or built dynamically. What if the SET and WHERE contain many columns? Then you're asking for a more complex operation. Your SQL gets a bit more complex as well. Now I have to add a WHERE columnmynewval for every column in SET to get the actual changes, something like UPDATE testtable SET col1=?1, col2=?2, col3=? WHERE insert complex where clause AND col1?1 AND col2?2 AND col3?3. (passing a null parameter to the above won't even work!) Well, no, it won't, because you're using the wrong operator. Use WHERE col1 IS NOT ?1 AND... and it all works fine. OK thanks, so I should always use IS NOT where I always used . Oh well (talk about yuck!) No surprises there. Oracle has never managed to impress me. I know what you mean. That MySQL database they make is difficult to take seriously. Very funny. They didn't make it, they own it now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Lynton, What is the value of msg.num_bytes_in? Is it fsize? And what do you get when you SELECT Length(raw_stream_in) FROM test ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 2:46 PM, Lynton Grice lynton.gr...@logosworld.comwrote: char* from SQLite? You say that SELECT treats message as TEXT which is fine, but then how can I get the FULL payload back into a char* so that I can write it to a file? SELECT doesn't treat the BLOB as text, the command line client (and apparently .output) does. My proof of concept goal is to now get that full binary stream back 100% and write it to a file Read the results from the SELECT with a C program. sqlite3_column_bytes I am getting this currently with your sample file when I do a full LOOP back test ;-( I must be doing the SELECT wrong somehow? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 3:15 PM, Lynton Grice lynton.gr...@logosworld.comwrote: Thanks, issue solved with the following: len = sqlite3_column_bytes(stmt,2); memcpy(msg-raw_stream_in, sqlite3_column_text(stmt, 2), len); sqlite3_column_blob is a better function to use. sqlite3_column_text will add a zero character and if your database uses UTF16 encoding will give you an interesting result. Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique index versus unique constraint
Automatic indexes have no SQL entry in the SQLite_Master table, so you can use that to see if you can drop it. eg: select Name from SQLite_Master where type = 'index' and SQL is null will give you the name of all the automatic indexes, which can't be dropped. Thanks Tom, It actually makes sense to never drop any automatic index (!). My question should have been how to reliably determine whether an index is automatic or not. Unless someone can come up with a compelling reason not to test for sqlite_autoindex_ in pragma index_list, I'd prefer to stick to that. Now I'm wondering if in future a situation could arise where the SQL will be provided for automatic indexes... Eg. to aid in exporting DDL. Yes, we definitely need more extensive schema introspection facilities in SQLite. A simple autoindex field in pragma index_list would be a good start :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unique index versus unique constraint
Hi All, When I create a table and specify a unique constraint, a unique index is automatically created. This index cannot be dropped, so the only way to get rid of the uniqueness is to recreate the table without the constraint. No problem. When I create a table without the unique constraint, I can add the unique requirement later by creating a unique index (if it works!). Apart from behaving the same for INSERTS, this is not exactly the same because I can simply drop the index to remove the unique requirement. So my problem is, I need to know how the unique requirement was created in the first place in order to get rid of it in the appropriate manner. One solution would be to parse the SQL field in sqlite_master to look for the constraint. (I'd prefer not!). I also don't really want to attempt a DROP INDEX and then fall back to recreating the table since I am generating scripts. Another solution that ocurred to me is to check for sqlite_autoindex_ in the name field of pragma index_list(tablename). Can I simply assume that unique indexes named sqlite_autoindex_* cannot be dropped? Perhaps if pragma table_info(tablename) had a unique column like it has a notnull column, but only for unique constraints on single fields. Or if pragma index_list had a constraint (or cantdrop or something) column that would indicate that the index cannot be dropped. Am I missing something? How do you solve this problem? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users