Re: [sqlite] Once again about random values appearance
James K. Lowden wrote: On Fri, 14 Feb 2014 08:32:02 +0400 Max Vlasov max.vla...@gmail.com wrote: From: Max Vlasov max.vla...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Reply-To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: Fri, 14 Feb 2014 08:32:02 +0400 Subject: Re: [sqlite] Once again about random values appearance On Fri, Feb 14, 2014 at 6:35 AM, James K. Lowden jklow...@schemamania.orgwrote: select id, (select id from TestTable where id = abs(random() % 100)) as rndid from TestTable where id=rndid On Thu, 13 Feb 2014 07:26:55 -0500 Richard Hipp d...@sqlite.org wrote: It is undefined behavior, subject to change depending the specific version of SQLite, compile-time options, optimization settings, and the whim of the query planner. It should be defined. In the above query, random() should be evaluated once. In the SQL model the user provides inputs once, and the system evaluates them once. Once for the query or once for the row? Once for the query. As a user you have no control how the system evaluates your query. The evaluation may change over time with different implementations, but the semantics of the query do not. Not long ago on this list we discussed SELECT *, datetime('now') from T; and the behavior was that the datetime function was called per-row, resulting in different times on different rows. It was changed, the rationale IIRC to be compatible with the SQL standard and other DBMSs. Like datetime, random() is a nondeterministic function taking constant (but void) input. It should be evaluated once, as provided. Where it appears in your query should not matter. That would make it consistent with how other functions work, and with the SQL standard. Good in theory, bad in practice. CURRENT_TIME = CURRENT_TIME was /almost always/ true before change (when it happened to be false, it was /surprising/ and /counter-intuitive/ exception), and just become /always/ true after change (no surprising change in behavior!). RANDOM() = RANDOM() is /always false/ now, it will become /always true/ after your suggested change. It have much higher potential to break existing code, e.g. SELECT * FROM t ORDER BY RANDOM() LIMIT 1 will be completely broken (well, sure, it is not best way to select random row(s) from table - it is overly expensive and unportable - but still much more likely to be used - and relied on - in existing code). Besides, datetime function behavior is specified by SQL standard - and RANDOM() is not. Then again, I, too, don't quite like how OP's queries - such as SELECT r r FROM (SELECT RANDOM() AS r FROM t) behavior depends on whether query flattening was used or not (even though one can write it off as documented undefined behavior). Or, that `SELECT RANDOM() AS r FROM t ORDER BY r` gives quite unexpected result. I just don't think that same approach as with datetime function would be good in this case. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Once again about random values appearance
On 17 Feb 2014, at 7:59am, Max Vlasov max.vla...@gmail.com wrote: So the nanosec example modified Select v-v from ( Select nanosec() as v from TestTable ) ...shows non-zero values for the current (3.8.3) and for older (3.6.10) version. Erm ... that worries me a little. I don't know how SQLite handles sub-selects internally. But the conventional way of doing them is a JOIN to a VIEW. Which means that v-v should always equal zero. By the way, when figuring out how to optimize this still it's worth noting that a parameterless function is rare in SQL. It's rarely worth optimizing unnamed expressions because you rarely get the same unnamed expression (including parameters) used twice. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Explain Query Plan with virtual tables involved
Would it be possible to have plans for virtual tables more in line with plans for regular tables? With virtual tables, the plan is always a SCAN TABLE, while for regular tables it's either a SCAN TABLE for full scans, and a SEARCH TABLE for an indexed plan. Because I don't yet use idxStr but only idxNum in sqlite3_index_info, my plans look like: SCAN TABLE folder_member VIRTUAL TABLE INDEX 16: SCAN TABLE folder_member VIRTUAL TABLE INDEX -1: (16 is the by-folder non unique index, -1 is the full-scan index). Even if I used idxStr, it will still be a SCAN TABLE, there's no way to tell SQLite that a specific index is the full scan index of the vtable, to have the plan use SCAN TABLE only for this one, and SEARCH TABLE for the other cursors/indexes, and this despite the fact that I use an astronomical estimatedCost (std::numeric_limitsint::max()). Could we add either a convention on idxNum to indicate this is a full scan, or a new field in sqlite3_index_info to explicitly indicate it? Also, is SQLite itself building idxStr such that it looks like nkey (guid=? AND folder=?), i.e. I need to build such a string myself, to have my vtable-using plans resemble the native SQLite table plans? Finally, all my indexes are basically COVERING indexes, i.e. there's no separate lookup of the row, given the rowid, so how could I similarly communicate this fact to SQLite, such that it prints it like for its native plans? My hope is that in the future, all plans look the same, except for an additional VIRTUAL keyword between USING and INDEX, and possibly the idxNum perhaps. Any chance this might happen? If not, and it's not a question of dev time, why would the above goal not be considered? Thanks, --DD C:\Users\DDeviennesqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table folder_member (guid blob, folder blob, folder_type text); sqlite create unique index nkey on folder_member(guid, folder); sqlite create index by_guid on folder_member(guid); sqlite create index by_folder_type on folder_member(folder_type); sqlite create index by_folder on folder_member(folder); sqlite explain query plan select * from folder_member where guid='ab' and folder='bc'; 0|0|0|SEARCH TABLE folder_member USING INDEX nkey (guid=? AND folder=?) sqlite explain query plan select * from folder_member where folder_type='foo'; 0|0|0|SEARCH TABLE folder_member USING INDEX by_folder_type (folder_type=?) sqlite explain query plan select * from folder_member; 0|0|0|SCAN TABLE folder_member sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Text column: never used vs. set to empty string
If I have a text column defined as it might be as MYCOL TEXT (that is with no default value), is there a way to distinguish in some row or other between a column into which no data has ever been entered, and a column that might have been set to a string, but later set to the empty string? (or even only ever set to the empty string, perhaps). Thanks, -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On Mon, Feb 17, 2014 at 6:37 AM, Tim Streater t...@clothears.org.uk wrote: If I have a text column defined as it might be as MYCOL TEXT (that is with no default value), is there a way to distinguish in some row or other between a column into which no data has ever been entered, and a column that might have been set to a string, but later set to the empty string? (or even only ever set to the empty string, perhaps). Hi, Tim, I would think of two ways: 1) You might default the field to a value you know from your data to be impossible. E.g. MYCOL2 TEXT DEFAULT '#UNUSED#' Your initial building of the table would then OMIT the field name MYCOL2 in order to invoke the default, e.g. INSERT INTO MyTable (MyCol1, Mycol3) VALUES ('a', 'b'); 2) You might set and/or default the field to NULL. (Importantly, but as you may already know, NULL is not like an ordinary value. It means something like 'Value is unknown.' As a result, testing for a value equal to NULL will ALWAYS return FALSE, for example. If this would be your first exposure to NULL you'd want to read up on it first.) Now if instead what you want is to: Initialize a value to the empty string set it to something else set it back to the empty string Then have sqlite invoke a past memory of that value then, no, sql won't do that directly. If that's what you need then perhaps you could define a new boolean column to keep track of whether MyCol had ever been used. Update that new column directly or via a trigger. Others on this list may have better advice. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hi Teg, I tried putting delay of 3 seconds and 30 seconds but nothing happens. Still not able to access database and store anything. When i create a file to store data it stores. Strange.. Need more views please. Thanks, Tejas On Sun, Feb 16, 2014 at 12:35 AM, Teg t...@djii.com wrote: Hello tejas, I'm thinking it sounds like the processing collides and you don't have retry's built into the process. Basically a race condition. The fact you can run one process and it works, and then later run a second process and it works suggests the problem is in the startup when you have two starting at the same time. As an experiment, I might put a long delay in the thread of the second process so, the first process gets good and started before the second one tries. C Saturday, February 15, 2014, 7:29:14 AM, you wrote: tp Hi All, tp We have a small issue with sqlite database. tp Program structure: tp one windows service calls two windows processes process-1 and process-2. tp both processes use common database db1.s3db file to store data. tp When windows services starts and calls two processes p-1 and p-2 none of tp the process is able to store data in the database. tp But if i call only one p-1 with service then everything works well tp meanwhile if i run p-2 manually with double click then also two processes tp are able to send data in to database. tp Please help i am clueless. tp Thanks, tp Tejas tp ___ tp sqlite-users mailing list tp sqlite-users@sqlite.org tp http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
On Sat, Feb 15, 2014 at 7:29 AM, tejas parekh cntte...@gmail.com wrote: Hi All, We have a small issue with sqlite database. Program structure: one windows service calls two windows processes process-1 and process-2. both processes use common database db1.s3db file to store data. When windows services starts and calls two processes p-1 and p-2 none of the process is able to store data in the database. But if i call only one p-1 with service then everything works well meanwhile if i run p-2 manually with double click then also two processes are able to send data in to database. Have you set up an Error and Warning log (http://www.sqlite.org/errlog.html) to see if it gives you any additional information? -- 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] Once again about random values appearance
On Mon, Feb 17, 2014 at 2:27 PM, Simon Slavin slav...@bigfraud.org wrote: On 17 Feb 2014, at 7:59am, Max Vlasov max.vla...@gmail.com wrote: So the nanosec example modified Select v-v from ( Select nanosec() as v from TestTable ) ...shows non-zero values for the current (3.8.3) and for older (3.6.10) version. Erm ... that worries me a little. I don't know how SQLite handles sub-selects internally. But the conventional way of doing them is a JOIN to a VIEW. Which means that v-v should always equal zero. Explain lit a litle light ... 4Function0NanoSec(0) 5Function0NanoSec(0) 6Subtract2 7ResultRow1 8Next4 . So no intermediate storage probably for performance reasons. Also the listing looks very self-explainable while possible stateful one will add more complexity to the VDBE code. By the way, when figuring out how to optimize this still it's worth noting that a parameterless function is rare in SQL. It's rarely worth optimizing unnamed expressions because you rarely get the same unnamed expression (including parameters) used twice. Simon, I see your point, next time my test function will depend on at least a dozen of very important parameters and will have a very, very, very long name :) Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 17 Feb 2014, at 11:37am, Tim Streater t...@clothears.org.uk wrote: If I have a text column defined as it might be as MYCOL TEXT (that is with no default value), is there a way to distinguish in some row or other between a column into which no data has ever been entered, and a column that might have been set to a string, but later set to the empty string? (or even only ever set to the empty string, perhaps). In SQL, no data means NUL whereas an empty string is a string with zero characters in. So just do SELECT typeof(t) FROM myTable and see whether you get 'text' or not. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
Tim Streater wrote: If I have a text column defined as it might be as MYCOL TEXT (that is with no default value) All columns have a default value. With no _explicitly_ specified default value, the column's default value is NULL. is there a way to distinguish ... between ... no data ... and ... the empty string? When you have never entered data, the value is NULL, which is distinct from '', the empty string. As long as your program takes care to never confuse these two values, it is possible to assign the meaning never entered data to the NULL value and have it work as you want. (Please note that in SQL, the NULL value does _not_ have any predefined meaning such as unknown or missing or invalid. SQL defines only how the NULL value behaves; it is your job as the programmer or database designer to determine if you can assign a meaning to the NULL value that matches this behaviour with the intended usage.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows service with SQLITE
Hello tejas, You're using windows so, you should be using Procmon to watch file IO. You need to add and look at logging. This is sort of trouble-shooting 101. If you can't get the debugger on it, you'll have to debug with logging. I'd generate a log file and log everything to do with opening and accessing the DB. C Sunday, February 16, 2014, 11:45:27 PM, you wrote: tp Hi Teg, tp I tried putting delay of 3 seconds and 30 seconds but nothing happens. tp Still not able to access database and store anything. tp When i create a file to store data it stores. Strange.. tp Need more views please. tp Thanks, tp Tejas tp On Sun, Feb 16, 2014 at 12:35 AM, Teg t...@djii.com wrote: Hello tejas, I'm thinking it sounds like the processing collides and you don't have retry's built into the process. Basically a race condition. The fact you can run one process and it works, and then later run a second process and it works suggests the problem is in the startup when you have two starting at the same time. As an experiment, I might put a long delay in the thread of the second process so, the first process gets good and started before the second one tries. C Saturday, February 15, 2014, 7:29:14 AM, you wrote: tp Hi All, tp We have a small issue with sqlite database. tp Program structure: tp one windows service calls two windows processes process-1 and process-2. tp both processes use common database db1.s3db file to store data. tp When windows services starts and calls two processes p-1 and p-2 none of tp the process is able to store data in the database. tp But if i call only one p-1 with service then everything works well tp meanwhile if i run p-2 manually with double click then also two processes tp are able to send data in to database. tp Please help i am clueless. tp Thanks, tp Tejas tp ___ tp sqlite-users mailing list tp sqlite-users@sqlite.org tp http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Once again about random values appearance
On 2014/02/17 09:59, Max Vlasov wrote: Ok, I hope I found the topic, the title was racing with date('now') (was: Select with dates): one of the links to the archive https://www.mail-archive.com/sqlite-users@sqlite.org/msg79456.html CMIIW, but as I see it, the final modification was commented by Richard As a compromise, the current SQLite trunk causes 'now' to be exactly the same for all date and time functions within a single sqlite3_step() call. But this is just for now and date-related functions. I wanted to be sure so created a user function NanoSec() that returns nanoseconds as it is calculated with QueryPerformanceCounter and QueryPerformanceFrequency on Windows and clock_gettime(CLOCK_REALTIME... on Linux. Seems like it's not always real nanoseconds but value that is changed very frequently to be different for close VDBE instructions of sqlite engine. So Select nanosec() - nanosec() from ... returns non-zero values for most of the times, so there's no guarantee the user functions or any other functions will be called once for the step.//... etc. Did you mark your nanosec function as SQLITE_DETERMINISTIC http://www.sqlite.org/c3ref/c_deterministic.html? http://www.sqlite.org/c3ref/create_function.html Which, if not, it can and will very much return non-zero values. And if you did, either your function or your version of SQLite is broken. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The canonical example is a now() function which returns time(NULL) (there was a long thread on this topic a few months back). My concrete questions: - Does such function need to make the guaranty itself or is marking it as SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one statement? - Can within a single SQL statement be interpreted as within the lifetime of a given preparation of a given sqlite3_stmt handle without distorting the truth too much, or is there a more complex definition involving subselects and such? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
On Mon, Feb 17, 2014 at 11:47 AM, Stephan Beal sgb...@googlemail.comwrote: Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The canonical example is a now() function which returns time(NULL) (there was a long thread on this topic a few months back). My concrete questions: - Does such function need to make the guaranty itself or is marking it as SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one statement? The is a constraint on the function implementation, that allows SQLite to perform certain optimizations in the generated VDBE code that would otherwise not be valid. So the function must guarantee that it will always return the same values given the same inputs. - Can within a single SQL statement be interpreted as within the lifetime of a given preparation of a given sqlite3_stmt handle without distorting the truth too much, or is there a more complex definition involving subselects and such? In call to the function within any subquery or trigger must return the same value if it has the same inputs. But the return value can change after each sqlite3_reset(). -- 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] Text column: never used vs. set to empty string
On 17 Feb 2014 at 14:10, Simon Slavin slav...@bigfraud.org wrote: On 17 Feb 2014, at 11:37am, Tim Streater t...@clothears.org.uk wrote: If I have a text column defined as it might be as MYCOL TEXT (that is with no default value), is there a way to distinguish in some row or other between a column into which no data has ever been entered, and a column that might have been set to a string, but later set to the empty string? (or even only ever set to the empty string, perhaps). In SQL, no data means NUL whereas an empty string is a string with zero characters in. So just do SELECT typeof(t) FROM myTable and see whether you get 'text' or not. Thanks. I should perhaps have made it clearer that I'm looking at an issue a user has. The application gathers some data from the user via a set of fields they complete in a browser window, which data is then gathered up and sent using ajax to be processed by a PHP script, which writes it to an sqlite db. The user complains that some of this data doesn't make it, so I want to pin down where in the chain this might be failing. Hence my Q. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater t...@clothears.org.uk wrote: complete in a browser window, which data is then gathered up and sent using ajax to be processed by a PHP script, which writes it to an sqlite db. The user complains that some of this data doesn't make it, so I want to pin down where in the chain this might be failing. Hence my Q. FWIW, i have seen a similar problem in a legacy app which uses latin1 encoding in the DB. Latin1 doesn't always survive round-trip through PHP's JSON APIs. My case was similar to yours, and we eventually determined that the fields which got lost (set to null or empty values) were those which came out of the latin1-encoded MySQL db containing invalid UTF8 characters - the whole values were getting dropped upon transforming to JSON. TL;DR: double-check all encodings. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
On 2014/02/17 18:47, Stephan Beal wrote: Hi, all, Regarding SQLITE_DETERMINISTIC: http://www.sqlite.org/c3ref/create_function.html does specifying that flag guaranty that sqlite3 will only call my deterministic function one time during any given SQL statement, or must my function actually guaranty that deterministic behaviour itself? The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs within a single query. SQLite then uses this information to maybe cache the output and re-use it, but there is no guarantee the optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be undefined. The OP seems to have tested SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - but to your question specifically, no the flag does not force determinism (AFAICT), it only allows the optimisation, or more specifically, NOT specifying the flag forces SQLIte to call the function every time and not expect determinsitic results (even if your return values are constant). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 17 Feb 2014 at 17:01, Stephan Beal sgb...@googlemail.com wrote: FWIW, i have seen a similar problem in a legacy app which uses latin1 encoding in the DB. Latin1 doesn't always survive round-trip through PHP's JSON APIs. My case was similar to yours, and we eventually determined that the fields which got lost (set to null or empty values) were those which came out of the latin1-encoded MySQL db containing invalid UTF8 characters - the whole values were getting dropped upon transforming to JSON. I'm not using JSON, and I've been careful to have everything in UTF8 throughout. I'm asking the user to send me the db, so I can look at it. But you make a good point. I said I've been careful, but the possibility I overlooked something related to that is not excluded. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 2014/02/17 19:01, Stephan Beal wrote: On Mon, Feb 17, 2014 at 5:57 PM, Tim Streater t...@clothears.org.uk wrote: complete in a browser window, which data is then gathered up and sent using ajax to be processed by a PHP script, which writes it to an sqlite db. The user complains that some of this data doesn't make it, so I want to pin down where in the chain this might be failing. Hence my Q. FWIW, i have seen a similar problem in a legacy app which uses latin1 encoding in the DB. Latin1 doesn't always survive round-trip through PHP's JSON APIs. My case was similar to yours, and we eventually determined that the fields which got lost (set to null or empty values) were those which came out of the latin1-encoded MySQL db containing invalid UTF8 characters - the whole values were getting dropped upon transforming to JSON. TL;DR: double-check all encodings. Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character that doesn't conform to the specified encoding would drop the entire string in any encoding. This was hard to trace because 99 conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string. I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error from the high-level code's design, but nobody told me...). This seems the case for both Windows and OSX, I can't speak for Linux, Android, etc. maybe someone else knows? Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC
On Mon, Feb 17, 2014 at 6:23 PM, RSmith rsm...@rsweb.co.za wrote: The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs within a single query. i figured so, just wanted to be sure. SQLite then uses this information to maybe cache the output and re-use it, but there is no guarantee the optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be undefined. But it would be really handy if sqlite3 could somehow guaranty that the optimization would apply :). (No, i'm not asking for that as a feature - it doesn't sound feasible to me for all query constructs.) The OP seems to have tested SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - It was your response to that which lead me to that flag - i wasn't aware of it before and wanted to double-check before i go applying it to my local now() impl (which i now won't do, of course). But... i can set it on 5 or 6 others which do behave deterministically, so thank you for that :). but to your question specifically, no the flag does not force determinism (AFAICT), That coincides with Richard's answer (which i consider to be definitive ;). it only allows the optimisation But it would be cool if... ;) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
Forgot to add: My headache was essentially UTF-8 encoding, but the same would happen with others, though invalid chars do not really exist in UTF7 or ANSI, but in the higher level encodings they are plentiful. On 2014/02/17 19:35, RSmith wrote: Yeah, I too have had real problems with this - It isn't limited to Latin1 either - but in my case I found one invalid character that doesn't conform to the specified encoding would drop the entire string in any encoding. This was hard to trace because 99 conversions would succeed with all kinds of weird and wonderful encoded characters, and then one suddenly returns an empty string. I eventually traced this down to the OS's C API for encoding conversions which seems to simply drop the entire value and not even cause an error - just returns empty string. Peculiar behaviour if you ask me (though that is probably meant to prompt an error from the high-level code's design, but nobody told me...). This seems the case for both Windows and OSX, I can't speak for Linux, Android, etc. maybe someone else knows? Anyway, adding a conversion check is the key, but the point is moot if you rely on PHP or such to do the conversions for you. Scripts don't have GUIs... they cant really do much about it. Maybe an error log somewhere contains some info? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Loadable extensions
Is there a list of available loadable extensions for functions, virtual tables, etc? Pete lcSQL Software http://www.lcsql.com Home of lcStackBrowser http://www.lcsql.com/lcstackbrowser.html and SQLiteAdmin http://www.lcsql.com/sqliteadmin.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 17 Feb 2014, at 4:57pm, Tim Streater t...@clothears.org.uk wrote: Thanks. I should perhaps have made it clearer that I'm looking at an issue a user has. The application gathers some data from the user via a set of fields they complete in a browser window, which data is then gathered up and sent using ajax to be processed by a PHP script, which writes it to an sqlite db. The user complains that some of this data doesn't make it, so I want to pin down where in the chain this might be failing. Hence my Q. Ah. In that case it's more likely to be writing zero-length strings. In other words a column into which no data has ever been entered does not happen. I'm with Mister Beal and possibly yourself: your problem is outside of SQLite. My only answer is to convert to Unicode as fast as possible then handle everything with Unicode-safe APIs. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 17 Feb 2014 at 18:38, Simon Slavin slav...@bigfraud.org wrote: On 17 Feb 2014, at 4:57pm, Tim Streater t...@clothears.org.uk wrote: Thanks. I should perhaps have made it clearer that I'm looking at an issue a user has. The application gathers some data from the user via a set of fields they complete in a browser window, which data is then gathered up and sent using ajax to be processed by a PHP script, which writes it to an sqlite db. The user complains that some of this data doesn't make it, so I want to pin down where in the chain this might be failing. Hence my Q. Ah. In that case it's more likely to be writing zero-length strings. In other words a column into which no data has ever been entered does not happen. I'm with Mister Beal and possibly yourself: your problem is outside of SQLite. Yes. I'm concluding that there's something fishy with the way my data-gathering page is operating. I've recently added use strict; to my javascript and that may be exposing something. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Once again about random values appearance
On Mon, Feb 17, 2014 at 7:00 PM, RSmith rsm...@rsweb.co.za wrote: On 2014/02/17 09:59, Max Vlasov wrote: . So Select nanosec() - nanosec() from ... returns non-zero values for most of the times, so there's no guarantee the user functions or any other functions will be called once for the step.//... etc. Did you mark your nanosec function as SQLITE_DETERMINISTIC http://www.sqlite.org/c3ref/c_deterministic.html? http://www.sqlite.org/c3ref/create_function.html Which, if not, it can and will very much return non-zero values. Very interesting option, it did affected the results, they're now zero for both cases. Also I see this quite a young option listed as a new feature of 3.8.3. Added SQLITE_DETERMINISTIC as an optional bit in the 4th argument to the sqlite3_create_function() and related interfaces, providing applications with the ability to create new functions that can be factored out of inner loops when they have constant arguments So the query with this option Select nanosec() as v from TestTable where vv always returns empty dataset. But it seems this option still has something to explain since Select nanosec() - nanosec() from TestTable returns always zeros while Select nanosec(), nanosec() from TestTable returns different values for fields Either nanosec() - nanosec() is much faster than the granularity of performance counter on average windows hardware or they are different cases for some reasons. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Text column: never used vs. set to empty string
On 17 Feb 2014, at 7:08pm, Tim Streater t...@clothears.org.uk wrote: Yes. I'm concluding that there's something fishy with the way my data-gathering page is operating. I've recently added use strict; to my javascript and that may be exposing something. My web apps involve a hand-off between JavaScript (running on the user's browser) and PHP (running on the server, accessing the SQLite database). Over the years the two have communicated various ways as standards have evolved, HTTPS has become available, and both languages have improved. And each time I change how it works I run into some unexpected weird encoding problem. Every single time. The new debugging tools built into Chrome, FireFox and Safari are wonderful. I mean completely unbelievable even a decade ago. And these three lines for PHP transform it out of sight: error_reporting(E_ALL); ini_set('display_errors', '1'); ini_set('log_errors', '1'); Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loadable extensions
On 17 Feb 2014, at 6:33pm, Peter Haworth p...@lcsql.com wrote: Is there a list of available loadable extensions for functions, virtual tables, etc? I've seen some extensions lists on the web but none of them struck me as worth bookmarking. The SQLite site includes a page of contributions, which contains some extensions: http://www.sqlite.org/contrib Look especially at the last entry. Some entries on that page are obsolete, attempts at making up for things which have since been built into SQLite or things that don't work with up-to-date compilers. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users