Re: [sqlite] sqlite3 mailing list broken
On Thu, Feb 12, 2015 at 2:45 PM, jus...@postgresql.org wrote: And yeah, I'm aware of fossil, but (to my thinking ;) that shouldn't hold back _this_ bit of software. ;) FWIW, fossil was/is designed _specifically_ for sqlite's hosting (that's neither a joke nor an exaggeration), so it's _exceedingly_ unlikely to go anywhere, regardless of how many +1s people collect to the contrary. -- - 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] Porting SQLite to another operating system (not supported out of the box)
On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian julian.ja...@capgemini.com wrote: So my question is, if there is any additional information, how to write a VFS? Or does anyone have a clue how to start best? This book: http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/ resp. http://shop.oreilly.com/product/9780596521196.do covers VFS creation in detail with a step-by-step example. -- - 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] Porting SQLite to another operating system (not supported out of the box)
On Tue, Feb 10, 2015 at 5:58 PM, Jay Kreibich j...@kreibi.ch wrote: No, it does not. Using SQLite covers Virtual Tables in great detail, but not VFS systems. They’re somewhat unusual, after all. My apologies - i mixed my terminology there! -- - 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] Encoding question
On Mon, Feb 2, 2015 at 12:49 AM, RSmith rsm...@rsweb.co.za wrote: guarantee that /IF/ you put valid UTF-8 data in there, it will be handled and returned correctly). For a given definition of correct. A relatively common topic on this list is the handling of locale-specific collations (a topic i'm not qualified to comment on), with ICU extension being a common part of any answers. Google says: http://www.sqlite.org/src/info/d9fbbad0c2f647c3fdf715fc9fd64af53aedfc43 -- - 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] Truncation of floating point numbers in SQLite?
On Sun, Feb 1, 2015 at 2:07 AM, Simon Slavin slav...@bigfraud.org wrote: So, having established that NaN and -0 do not make the round trip from a C variable through a database and back into a C variable ... at least I think we have ... If you're assuming C89 (which sqlite3 is, by and large), it's a technical fact that there is no standard representation of either negative zero, NaN, or Infinity. Any such support would be non-C-standard. Because there's nothing in the SQLite documentation that says it can store values like NaN or -0.0. The documentation just says it can store numbers. It should be possible to find out when the distinction between 0.0 and -0.0 gets lost. sqlite's platform (C89) doesn't natively support it, so if it's there then sqlite3 is either supporting it itself (custom code) or is relying on C99-specific APIs (which, to the best of my (fallible) knowledge, it does not do). -- - 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] database disk image is malformed error occurs more
On Fri, Jan 30, 2015 at 1:45 PM, Mario M. Westphal m...@mwlabs.de wrote: - From what I can tell, network-based databases are not more likely to corrupt than databases stored on built-in disks or SSDs or databases kept on disks or USB sticks connected via USB. That's a big assumption. Network filesystems are historically _notorious_ for locking-related problems (the root of many corruption problems). -- - 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] Truncation of floating point numbers in SQLite?
On Fri, Jan 30, 2015 at 3:45 AM, Donald Shepherd donald.sheph...@gmail.com wrote: I'm still not convinced whether it's the behaviour causing my problem, but it does look like negative zero is another special case: SQLite version 3.8.7.2 2014-11-18 20:57:56 Enter .help for usage hints. sqlite create table datatable2 (doublevalue real); sqlite insert into datatable2 values(-0.0); FWIW, special doubles like inf, nan, and -0 have no standardized C89 APIs (they were added in C99), so it is unsurprising that sqlite (C89, aside from its use of long long) treats signed and unsigned 0 the same. http://en.wikipedia.org/wiki/C99#IEEE.C2.A0754_floating_point_support http://stackoverflow.com/questions/9657993/negative-zero-in-c According to this page: http://stackoverflow.com/questions/5095968/does-float-have-a-negative-zero-0f the standard (it's not clear if they mean C89 or C99) _requires_ positive and negative zero to test as equal, an implication of which is that it would be impossible to tell them apart in SQL implementations based on that. -- - 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] database disk image is malformed error occurs more frequently...?
On Wed, Jan 28, 2015 at 4:19 PM, Richard Hipp d...@sqlite.org wrote: On 1/28/15, Mario M. Westphal m...@mwlabs.de wrote: Recently I get an increasing number of error reports about “database disk image malformed” errors from my users. These errors show up out of the blue, with databases held on local hard disks or even SSD’s, no power failures, Windows crashes or anything that’s in the rule book of “How to damage your SQLite database”. This shouldn't happen. (But read https://www.sqlite.org/howtocorrupt.html for more information). To paste in part of a recent thread from the Fossil list which _might_ be relevant here... On Sat, Jan 24, 2015 at 10:10 PM, Richard Hipp d...@sqlite.org wrote: On 1/24/15, Richard Hipp d...@sqlite.org wrote: On 1/24/15, Michai Ramakers m.ramak...@gmail.com wrote: ... SQLITE_CORRUPT: database corruption at line 53682 of [1412fcc480] Actually, Fossil version 331204dc93 contained a dodgy version of SQLite which could generate corruption reports such as the above. The database was not really corrupt. The error was in the corruption detection mechanism. That error has long since been fixed. -- - 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] Buffered stderr on Windows (Take 2)
On Fri, Jan 23, 2015 at 5:18 PM, Guilhem Malichier g...@eco-counter.com wrote: I've been experiencing an issue with SQLite's CLI tool on Windows 7, when used through a script or spawned process (not when used directly in the console). If i'm not mistaken, that was fixed just last week: http://sqlite.org/src/info/80541e8b94b713e8f9e588ae047ffc5ae804ef1c Or maybe this is a related problem, not quite the same. -- - 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] Fsync or fdatasync performs slowly
On Mon, Jan 19, 2015 at 11:23 AM, Wei, Catherine catherine@arris.com wrote: Thank you for your answer very much. But I have removed the disk from my set-up box, the data will be saved in memory and the system doesn't know there's no disk, it will still call fsync. What do you think in this case? man fsync says: SYNOPSIS #include unistd.h int fsync(int fd); int fdatasync(int fd); DESCRIPTION fsync() transfers (flushes) all modified in-core data of (i.e., modified buffer cache pages for) the file referred to by the file descriptor fd to the disk device (or other permanent storage device) ... i.e. if you have no disk (you are using an in-memory VFS), then you have no file descriptor, so fsync/datasync _cannot_ be (legally) called. -- - 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] Fsync or fdatasync performs slowly
On Mon, Jan 19, 2015 at 3:49 AM, Wei, Catherine catherine@arris.com wrote: Hi, I'm running a software in a set-up box with linux system and find that every time when I commit transaction, sqlite takes too much time when it executes fsync or fdatasync function. What could be the possible reasons? FWIW, fsync/fdatasync() are _system_ calls, so the OS or one of its drivers (not sqlite) is taking too much time to return. See also: https://www.sqlite.org/c3ref/c_sync_dataonly.html https://www.sqlite.org/pragma.html#pragma_synchronous -- - 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] Database corrupted 28 billion bytes
On Fri, Jan 16, 2015 at 12:18 PM, MikeD supp...@dotysoftware.com wrote: I have a database that has become 28,268,814,336 bytes so downloaded the sqlite3_analyzer and it has been running for over 15-minutes. ... The database is still working. What should I be doing? How about continue to use it? Why do you feel that something is broken just because it's big? -- - 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] Best Practice: Storing Dates
On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz keil...@gmail.com wrote: Since sqlite and most RDMS implementations have functions to convert to and from both options and using a LONG should allow the date/time to function way past 2038, In my experience, having the timestamp in Unix Epoch gives you something which can be easily converted by a wide variety of tools, and simplifies calculation of time deltas (provided you don't need to account for timezones, locale-specific summer/winter time changes, and similar absurdities). That said, for humans Unix timestamps are basically just a pain in the butt. If your data are there for the software, as opposed to the humans, i personally find Unix Epoch simpler to work with. If the data are strictly for display/reading by humans, without much app logic tied to them, ISO8601 is my preferred form (-MM-DD HH:ii:ss...). -- - 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] Support for millisecond
On Fri, Jan 9, 2015 at 12:15 AM, Keith Medcalf kmedc...@dessus.com wrote:
You mean iso-8601 strings in the database? Yes, you can format the
strings however you want (ie with an ...
...
sqlite select strftime('%Y-%m-%d %H:%M:%f', '2015-02-14
13:46:15.3948573647856354765 +04:00');
2015-02-14 09:46:15.394
i should have been careful to note that i was using custom conversions
(based on Wikipedia and its outbound links), as opposed to sqlite's
methods, and using ms precision for the ISO strings.
--
- 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] Support for millisecond
On Thu, Jan 8, 2015 at 7:28 PM, Petite Abeille petite.abei...@gmail.com wrote: On Jan 8, 2015, at 7:21 PM, Lance Shipman lship...@esri.com wrote: Can SQLite support millisecond precision in date time data? I looking at doc I think so, but it's not clear. There is no 'date time’ data type in SQLite. Feel free to store your time data as either text or number. To whatever precision suits you. There are a couple of built-in utility functions to convert things back and forth: http://www.sqlite.org/lang_datefunc.html Note that Julian Times provide _almost_ ms-precision, depending on the time range you want to cover. The Fossil SCM (based on sqlite) uses Julian Times almost exclusively. HOWEVER, there is some small amount of conversion precision when doing round-trip Julian/ISO8601 times, so i'd avoid them if 100% round-trip fidelity is required. In my experiments, i see round-trip conversion errors of +/-1ms in somewhere between 0.25% (64-bit systems) to 2% (32-bit systems) of all timestamp converted round-trip between Julian and ISO8601. -- - 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] PHP: squelch warning and error messages
On Tue, Jan 6, 2015 at 10:29 AM, Lev leventel...@gmail.com wrote:
I know what this error/warning is. I *DO* error handling in my code. I just
don't want the message printed on the webpage.
PHP's general-purpose mechanism for warning squelching is to prepend the
command which is warning with an @ sign:
if( @someFuncWhichWarns() ) { ... }
--
- 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] PHP: squelch warning and error messages
On Tue, Jan 6, 2015 at 10:45 AM, Stephan Beal sgb...@googlemail.com wrote:
PHP's general-purpose mechanism for warning squelching is to prepend the
command which is warning with an @ sign:
if( @someFuncWhichWarns() ) { ... }
To be clear: the @ does NOT change the result of the function in any way
(so it has no effect on whether or not the above if() block is entered). It
only squelches warning/error(?) output.
It can also be used on methods: @$obj-foo(). It cannot be used with
function-like keywords, e.g. @isset() doesn't work IIRC.
--
- 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] Parentheses in column name
On Mon, Dec 22, 2014 at 9:19 PM, Federico Jurio federicoju...@suremptec.com.ar wrote: E:\Pruebasogrinfo -sql select min((asd*)) MINASD from Geo1 Geo1.shp You're using double quotes inside double quotes, which cannot parse how you expect it to. You need to change the outer quotes (around the whole select) to single quotes, then use double quotes around (asd*) (as you've done). Alternately, i believe '[' and ']' can be used instead of quotes around table/field names. -- - 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] Table names length and content
On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera jic...@cinops.xerox.com wrote: When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/limits.html might have what you're looking for. -- - 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] Table names length and content
On Mon, Dec 8, 2014 at 7:36 PM, Roger Binns rog...@rogerbinns.com wrote:
SQLite supports all those, as well as zero length table names, column
types and names.
sqlite create table !@#$%^*()_+=-{}\|[]';:?/.,, etc., etc. in
the table name( );
To whichl add: sqlite allows it. Your fellow colleagues, on the other hand,
will hopefully not let such things through code review ;).
(Empty strings? Really?)
--
- 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] Table names length and content
On Mon, Dec 8, 2014 at 7:37 PM, Drago, William @ CSG - NARDAEAST william.dr...@l-3com.com wrote: Uh oh! I never use quotes when I create my tables. What are the implications of not quoting? If you restrict yourself to C-style identifiers, the primary implication is better ease of use and readability! ;) -- - 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] Bug report: USBAN failure
On Wed, Dec 3, 2014 at 5:35 PM, Scott Robison sc...@casaderobison.com wrote: standards have all been ISO standards. Pedantic? Yes. Obviously DRH is willing to make the code more portable as long as it doesn't violate ANSI-C, hence his patch early in the thread (see https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606). Just to harmlessly nitpick: sqlite (necessarily) uses long long for int64, which isn't strictly C89: but works on essentially every compiler. gcc -c -pedantic -std=c89 -Wall -Werror sqlite3.c sqlite3.c:377:16: error: ISO C90 does not support ‘long long’ [-Werror=long-long] typedef long long int sqlite_int64; ^ sqlite3.c:378:25: error: ISO C90 does not support ‘long long’ [-Werror=long-long] typedef unsigned long long int sqlite_uint64; ^ cc1: all warnings being treated as errors clang -c -pedantic -std=c89 -Wall -Werror sqlite3.c sqlite3.c:377:11: error: 'long long' is an extension when C99 mode is not enabled [-Werror,-Wlong-long] typedef long long int sqlite_int64; ^ sqlite3.c:378:20: error: 'long long' is an extension when C99 mode is not enabled [-Werror,-Wlong-long] typedef unsigned long long int sqlite_uint64; which can be squelched with: gcc|clang -c -pedantic -std=c89 -Wall -Werror -Wno-long-long sqlite3.c (clang now reports an unused var, but that's something else.) -- - 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] Serializing an object's vector or array using sqlite3 in c++
On Fri, Nov 21, 2014 at 10:45 AM, RSmith rsm...@rsweb.co.za wrote: Alternatively you can serialize anything object-like to XML or JSON and simply store the resulting string in the database. This allows you to not spend a lot on DB design and to use the same DB layout for any future changes to the base objects and save more since object-to-JSON etc. serializers are usually freely available and easy to code Apropos: for such a C++ library, see http://s11n.net (caveat: i haven't updated it in several years, but do respond to feedback and questions via private mail (not the list, as SourceForge can't keep the spammers out of it)). Alternately, a lighter-weight, JSON-only solution: http://fossil.wanderinghorse.net/repos/nosjob/ -- - 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] querying with BLOB in WHERE clause, possible?
On Fri, Nov 14, 2014 at 3:57 PM, bjdodo bjd...@hotmail.com wrote:
I've got it now. E.g. for delete you can do this:
A minor fix:
the 'try' needs to be moved to...
int argidx = 0;
SQLiteStatement statement = mDB.compileStatement(sql);
here. Because...
throw new RuntimeException(unexpected argument type);
if that happens then you're not closing it.
try {
return statement.executeUpdateDelete();
} finally {
statement.close();
}
--
- 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] sliteonline or sqlite in js
On Fri, Nov 14, 2014 at 4:31 PM, Clemens Ladisch clem...@ladisch.de wrote: I understand the desire to avoid storing data on the web server, but it would It doesn't - you can load local files. -- - 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] Recursive CTE on joined table
On Fri, Nov 14, 2014 at 6:16 PM, Paul Sanderson sandersonforens...@gmail.com wrote: I can simplify the SQL by creating a view andthen use the ciew in my recursive cte, but for various reasons I would rather not do this. Is it possible to use a recursive cte that refers to a cte? http://www.sqlite.org/lang_with.html You can combine several CTEs into one big SELECT, which seems to be what you're asking? Search that page for mandelbrot for an extreme example. -- - 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] [SQLite] Support for 23,10 Precision number format
On Thu, Nov 13, 2014 at 4:50 PM, Dinesh Navsupe dinesh.navs...@gmail.com wrote: I mean Oracle's NUMBER(23, 10), and given [1], that's more My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. The first answer to this thread might be helpful (but also probably not what you want to hear): http://sqlite.1065341.n5.nabble.com/How-point-numbers-are-they-stored-in-sqlite-td35739.html -- - 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] cnt(x) what do the brackets signify
On Wed, Nov 12, 2014 at 6:52 PM, Paul Sanderson sandersonforens...@gmail.com wrote: I have googled but can't see what cnt(x) actually signifies - cnt is not a function - could someone point me to a resource so I can understand this construct The diagram at the top of this page demonstrates what it is: http://www.sqlite.org/lang_with.html -- - 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] default ignore-glob for sqlite
On Thu, Nov 6, 2014 at 1:14 AM, E. Timothy Uy t...@loqu8.com wrote: Hi, I noticed that the ignore-glob for sqlite is empty. Is it just because fossil does not transfer this information? What should I be using? http://sqlite.org/src/dir sqlite doesn't have a .fossil-settings dir, i.e. no versioned settings, so no, they're not synced with clones. -- - 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] Is sqlite thread-safety sufficient for use with Go language ?
On Thu, Nov 6, 2014 at 4:12 AM, nicolas riesch nicolas.rie...@gmail.com wrote: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading Be aware that the cvstrac pages are all historical, possibly outdated, and no longer maintained. -- - 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] fixing time-warp
On Wed, Nov 5, 2014 at 6:24 PM, Andreas Kupries andre...@activestate.com wrote: How about looking into fossil test-shortest-path and see how it follows the path of revisions. ? Oh, but were's the fun in rolling a wheel someone else already made round ;). (moments later...) It turns out i already ported the whole shortest-path/PathNode bits to libfossil, but it hasn't yet been used anywhere except test code. Good thing Fossil remembers everything so well, because i only vaguely remember writing it. [stephan@host:~/cvs/fossil/libfossil/f-apps]$ ./f-sanity -1 fcli.appName=./f-sanity Checkout dir=/home/stephan/cvs/fossil/libfossil/ Checkout db=/home/stephan/cvs/fossil/libfossil/_FOSSIL_ Repo db=/home/stephan/cvs/fossil/libfossil.fsl test_path_1()... directOnly=1, oneWayOnly=0 Versions d7927376fa9d (5534) to c10d7424ae4c (5525): 3 steps #1: 5534 #2: 5532 begat 5534 #3: 5528 begat 5532 #4: 5525 begat 5528 checkout UUID=220da67a06ee577d4667718b2ffe2f94c48ca338 (RID 6660) Cached statement count: 3 If you made it this far, no assertions were triggered. Now try again with valgrind. Total run time: 0.007919 seconds of CPU time Interestingly, that test uses rid comparison for determining whether to say begat or derives from (not seen above), but i've learned in the mean time the rid comparison isn't strictly reliable because it's legal for artifacts to get blobified (getting a blob.rid value) in an arbitrary order. -- - 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] x64 vs x32 DLL
On Tue, Nov 4, 2014 at 9:20 PM, RSmith rsm...@rsweb.co.za wrote: miniscule. On the downside, a 64-bit system uses 64 bit values for all registers even where 32-bit values could have sufficed, and so all internal looping through bits or register shifts take longer, but generally by negligible amounts. To add to that: the doubling of the size of a (void*) in 64-bit costs many types of applications/libraries notably more memory, up to twice as much for pointer-only structures. -- - 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] fixing time-warp
On Wed, Nov 5, 2014 at 7:07 AM, E. Timothy Uy t...@loqu8.com wrote: The problem is ultimately not time-warps. DRH can confirm - the problem is actually inside fossil and sqlite.fossil. Very early on in sqlite.fossil there are entries in the plink table where the parent id (pid) is greater than the commit id (cid). There are over a thousand of these. Those IDs are _transient_, not part of the historical record. The child ID comes before parent ID behaviour also appears on completely benign repos (i've seen it before in my own while testing libfossil). If I had more brain cells, I could perhaps invent a way to efficiently use the plink table to generate the proper export list where parents always come before children regardless of mtime. i've also attempted something similar in libfossil, but haven't been successful. The RIDs cannot be used to figure it out, and neither can mtime. The only 100% reliable way i know of traversing the history is to read each manifest, as the P-cards give us that piece of context we need to know the ordering. -- - 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] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)
On Tue, Oct 28, 2014 at 8:24 AM, Hick Gunter h...@scigames.at wrote: Indices, foreign keys, ... all work only within a single DB file, so allowing a qualifier would suggest functionality that is not present and probably quite hard to provide. We've got no intention of trying to rely on such a thing, it just struck me as odd (while trying to port a schema file) that some places didn't allow it. We don't have any table name collisions across the DBs, so we're really never expecting any ambiguity problems. (This whole effort to support/standardize on fully qualified DB names in our SQL is largely just me being overly pedantic.) How would one keep an index residing in one DB file consistent with a table in a different file if only one of the files is attached? No idea - those details (if any!) are left to sqlite ;). Eg. CREATE INDEX db.index_name ON [=db.]table_name ... CREATE TABLE db.table_name (...) FOREIGN KEY (...) REFERENCES [=db.]referenced_table CREATE TRIGGER db.trigger_name ... ON [=db.]table_name ... CREATE VIEW db.view_name AS SELECT ... FROM [=db.]table_name ... Thanks for the details :). -- - 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] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)
On Tue, Oct 28, 2014 at 4:02 PM, Gerry Snyder mesmerizer...@gmail.com wrote: A regular (non TEMPORARY) VIEW can reference only tables in the file where it is stored. SQLite allows qualified names, but the practice should be avoided because the VIEW won't work if the file is attached under a different name. A TEMPORARY VIEW can reference tables in any attached file, and the use of qualified names is allowed and encouraged (at least by me). That info will most certainly save me some future head-scratching. Thanks! -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter h...@scigames.at wrote: SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems. The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file. That's the thing - if we leave out the explicit DB names then it works as expected (or against expectations, depending on one's world view). It's only when adding the explicit db name qualification that it locks. i agree, attaching an opened DB is a huge kludge, but the problem is (summarized): - this app (libfossil) managed 3 different databases. Which one of those gets opened first is unknown/unknowable, and there is no requirement than any of them get opened, or maybe only a subset will. fossil(1) has the same setup but juggles the dbs somewhat differently and does not expose any interface to the outside world, so this has so far remained an internal problem with no effect on clients. - sqlite automatically names the first db main, and provides to way to change that. - end effect is: client code must know which order the dbs were opened in order to know which name is correct for each logical DB. This is a painfully leaky abstraction, though. - a couple months back Simon suggested ATTACHing the db to itself so that we can effectively alias main to the well-known name we have specified for that db instance. It worked like a charm until Dave discovered this weird locking behaviour. -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 9:58 AM, Simon Slavin slav...@bigfraud.org wrote: On 27 Oct 2014, at 8:43am, Stephan Beal sgb...@googlemail.com wrote: - a couple months back Simon suggested ATTACHing the db to itself so that we can effectively alias main to the well-known name we have specified for that db instance. It worked like a charm until Dave discovered this weird locking behaviour. The suggestion I made originally was that sqlite3_open() should open a dummy database -- which doesn't need to contain any tables -- as 'main'. That way any attached database would never be 'main' too. However this solution was rejected in your case because introducing a new file into your project 'costs' a lot of work. That was a separate option which i had looked at beforehand, but it's far from ideal. If we can't get around this locking problem, that might be only option, but it would require a relatively painful transition of both code and documentation. The fact that sqlite behaves one way if we fully qualify the tables and another if we do not implies that the infrastructure is internally there for sqlite to figure out is this the same DB as that one, and if so, don't lock it. While i'm generally hesitant to say, bug, it behaving in two different ways for what is semantically the same code is arguably a bug (but _which_ of the two behaviours is correct is of course debatable). -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 10:44 AM, Hick Gunter h...@scigames.at wrote: How about always referencing all tables via attached db names? That way, main is never referenced, neither explicitly nor implicitly, and is therefore never locked. It's looking more and more as if that's what we'll have to do, but i really wanted to avoid that because... - Paying for N+1 db handles instead of the N handles we're really using. - TEMP tables get created in the MAIN db (assuming my memory of the docs is correct), which means we can (though accidental misuse or carelessness) end up filling up RAM with temporary tables (which we use regularly to process large data amounts). This is my biggest concern with this approach. Opening a dummy main DB in the filesystem isn't a viable option - this is library-level code for which we don't have a directory/location which belongs to us which we can pollute with temp DBs (other than $TEMP/$TMP, of course, but opening a db there as a main db would be a horribly ugly kludge). @Dave: i'll research what the side effects of such a change would be. (It'll likely break more docs than code.) In the mean time, i think the workaround is to simply leave off the db names (since we know we don't have table name collisions). -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:08 AM, Stephan Beal sgb...@googlemail.com wrote: - TEMP tables get created in the MAIN db (assuming my memory of the docs is correct), which means we can (though accidental misuse or carelessness) end up filling up RAM with temporary tables (which we use regularly to process large data amounts). This is my biggest concern with this approach. In fact, it seems impossible to use any db _except_ the main one for temp tables: sqlite .databases seq name file --- --- -- 0main /home/portal/tmp/bar.db 2foo /home/portal/tmp/foo.db sqlite create temp table foo.baz(z); Error: temporary table name must be unqualified Which rules out use of a :memory: db has the local main - we make use of temp tables with arbitrarily large data sets. -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter h...@scigames.at wrote: TEMP tables get created in database temp; which is located in a file or in memory depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. Which reveals my ignorance on the topic ;). IIRC we aren't using a specific temp store - we're using whatever's compiled in by default. So... maybe paying for a :memory: handle we don't really use won't be as painful as i first thought. Just add a pragma call to ensure that we're using disk instead of memory for temp store. -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:59 AM, Stephan Beal sgb...@googlemail.com wrote: On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter h...@scigames.at wrote: TEMP tables get created in database temp; which is located in a file or in memory depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. Which reveals my ignorance on the topic ;). IIRC we aren't using a specific temp store - we're using whatever's compiled in by default. So... maybe paying for a :memory: handle we don't really use won't be as painful as i first thought. Just add a pragma call to ensure that we're using disk instead of memory for temp store. Follow-up: injecting a :memory: db as the first-opened DB turned out to be a very small change (because the code was set up for that at one point), and it turns out that using ATTACH for all three of our library-level DBs gives us three or four minor features/benefits we didn't have before. e.g. it was impossible to close one of the three DBs in one particular (and as yet hypothetical) use case, but we can now attach/detach each one at will without regard for the others or which one was opened first (as that role is now taken by the :memory: placeholder). All in all, making that change was a win. Sidebar: it turns out there are some contexts where fossil does not allow db.table qualification (namely (REFERENCES D.T) and (CREATE INDEX ... ON D.T(F)) do not allow it), but that's a topic for another thread if/when it becomes problematic (so far it's only a hypothetical problem, and not one worth losing any sleep over). @Gunter: Vielen Dank for clarifying where TEMP tables go: that misunderstanding was why i migrated away from this setup in the first place. (Und schoenen Gruss aus Muenchen!) -- - 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] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 8:52 PM, Nico Williams n...@cryptonector.com wrote: Well, it could do something to detect duplicates, but it may not be easy (or even possible) to portably detect that two DB files are the same file. The same is true for the application, of course, but it seems reasonable [to me anyways] to put this burden on the application. It doesn't seem unreasonable, i agree, and i have no doubts that our use case is an odd one. (Background: we're migrating the fossil monolothic app to a library API, and that's where the history of these DBs, and how they are handled, originates.) Still, I can see how it could be useful for SQLite3 to detect dup DB ATTACHes. Suppose you had a two-DB application, but since there's no schema conflicts you later decide that it'd be easier (e.g., easier to backup, sync, manage) to merge the two DBs into one. But you still have older code that wants two DBs... That's conceptually the same problem we're trying to solve here: keep the public db names stable, regardless of where/how they're actually attached. Background: my initial feature request (from a thread in July) was the ability to assign an alias to any given db handle, such that myalias would work just like main or attachedName, but ATTACH was suggested and turned out to work like a charm until Dave went and broke it with a self-locking query (and then, bless his heart, went and debugged it to its conclusion) ;). The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file. And two journals, and so on. Oh, good point - hadn't thought of that. On a related note, as of an hour or so away we've migrated to a (initially :memory:, but (which was new to me) seems a better fit) plus (pragma temp_store=file), and we're attaching the other DBs as needed. There's one particular use case which has hypothetically has minor breakage if the power goes out at one particular point in one specific cross-attached-db transaction, but because (as i understand it) each individual attached DB is guaranteed to be consistent within itself, the damage (if any) for our case is harmless (record ID mismatches in the more transient of the DBs) and would be recoverable by a routine scan which client apps do anyway, so it's unlikely that a user would ever even see it. -- - 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] quasi-bug related to locking, and attached databases....
On Fri, Oct 24, 2014 at 11:16 PM, Simon Slavin slav...@bigfraud.org wrote:
Which version of SQLite are you using ?
What operating system are you using (including which version) ?
What formats are the volumes those files are stored on ?
Simon, FYI: this is the 'main' db aliasing problem i brought up a couple
months ago, which we solved by using your suggestion: re-attach the db
directly after opening it, so that (as Dave said) all DBs have well-known
internal names regardless of what order they get opened in.
Reproduced here with 3.8.6 on Linux/x64:
sqlite insert or replace into main.dest ( name, value ) values
('allow',(select value from aux.source where name = 'allow'));
Error: database is locked
now without the db names:
sqlite insert or replace into dest ( name, value ) values ('allow',(select
value from source where name = 'allow'));
--
- 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] CLI dump command ignores view dependencies
On Wed, Oct 1, 2014 at 3:53 PM, to...@acm.org wrote: If it indeed does matter, then shouldn’t dropping view a (in the above example) also drop view b, automatically? That assumes view 'a' somehow knows that it is the only consumer of 'b', which it cannot know. Views from other db files, possibly not attached, might be consumers of 'b'. -- - 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] calculating in the command line interface
On Wed, Oct 1, 2014 at 4:27 PM, jose isaias cabrera jic...@cinops.xerox.com wrote: So, I needed to get a really low percentage and I went to the SQlite3 command prompt and I typed, select round(133/122000,10) as t; that gave me 0.0. Then, I said, ok, let's try this, select round(1/2,10) as t; that also gave me 0.0. Then I said, h, let me try this, You're doing integer math. You need floating point: select round(1/2,10) as t; 0.5 -- - 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] calculating in the command line interface
On Wed, Oct 1, 2014 at 4:34 PM, Stephan Beal sgb...@googlemail.com wrote: You're doing integer math. You need floating point: select round(1/2,10) as t; 0.5 And this time with the right copy/paste buffer: sqlite select round(1.0/2,10) as t; 0.5 -- - 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] Trace callback is not called in a specific case
On Sat, Sep 27, 2014 at 11:08 PM, Hody Crouch hody.cro...@gmail.com wrote: $ gcc -g -I. dbtest.c -l sqlite3 -ldl -lpthread $ ./a.out TRACE: SELECT val1, val2 from t where val2 = 'A%' What is -l sqlite3 supposed to do? It's not a valid linker flag (-l and its argument should have no spaces between them). It looks to me like you are linking the sqlite3 _binary_ (from the current directory) into your dbtest app (which is a usage error). -- - 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] Version info in doc pages
On Sun, Sep 21, 2014 at 2:46 PM, HarryD d...@timeofday.nl wrote: I find it something of a shortcoming that the doc pages ( http://www.sqlite.org/lang_with.html) do not mention the applicable version of sqlite. Example: I read about the 'with clause'. Exciting! But when trying it, it didn't work because I am using an older version. Not that old but still old enough. fossil has its own built-in copy of sqlite. It sounds like you're using one which was built with the --internal-sqlite=0 flag. Try building it without that flag (or upgrading to a newer version, if you've got an old one) and it should just work. -- - 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] Version info in doc pages
On Sun, Sep 21, 2014 at 2:53 PM, Stephan Beal sgb...@googlemail.com wrote: fossil has its own built-in copy of sqlite. It sounds like you're using one which was built with the --internal-sqlite=0 flag. My UTMOST apologies - i'm confusing traffic from two lists here (and thought yours was for the fossil list). -- - 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] What if OpenDocument were built atop SQLite?
On Tue, Sep 9, 2014 at 12:45 AM, Nico Williams n...@cryptonector.com wrote: I've played with building a JSON extension for SQLite3 using jq's excellent JSON C library. The biggest problem with that work is that the extension has to serialize values to JSON (and, of course, parse) in many cases where it could be avoided with some help from SQLite3. FWIW: http://fossil.wanderinghorse.net/wikis/cson/?page=cson_sqlite3 -- - 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] What if OpenDocument were built atop SQLite?
On Tue, Sep 9, 2014 at 6:46 AM, Stephan Beal sgb...@googlemail.com wrote: On Tue, Sep 9, 2014 at 12:45 AM, Nico Williams n...@cryptonector.com wrote: I've played with building a JSON extension for SQLite3 using jq's excellent JSON C library. The biggest problem with that work is that the extension has to serialize values to JSON (and, of course, parse) in many cases where it could be avoided with some help from SQLite3. FWIW: http://fossil.wanderinghorse.net/wikis/cson/?page=cson_sqlite3 BTW: that's the same code we use in Fossil for the JSON API: https://docs.google.com/document/d/1fXViveNhDbiXgCuE7QDXQOKeFzf2qNUkBEgiUvoqFN4/view -- - 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] Request to change int parameter to size_t parameter / potential bug on iOS (64 bit)
On Sun, Sep 7, 2014 at 8:08 PM, Stephen Chrzanowski pontia...@gmail.com wrote: On Sun, Sep 7, 2014 at 1:02 PM, skywind mailing lists mailingli...@skywind.eu wrote: type?! On iOS 64bit the size of int is 4 bytes and the size of size_t is 8 bytes. In this case the fourth parameter is actually not even able (theoretically) to store the length of a blob or text variable correctly. http://www.sqlite.org/c3ref/bind_blob.html In those routines that have a fourth argument, its value is the number of bytes in the parameter. To be clear: the value is the number of *bytes* in the value, not the number of characters. If the fourth parameter to sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of the string is the number of bytes up to the first zero terminator. i.e. changing them to size_t would change the semantics and break and and all applications which rely on the current semantics (some of which are mine). See also: http://www.sqlite.org/limits.html which documents the 31-bit limit. -- - 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] papercut wish list : a PRAGMA encoding='UTF-8-SIG'
On Wed, Sep 3, 2014 at 9:56 PM, big stone stonebi...@gmail.com wrote: As a windows user, I would like that sqlite.exe would support the encoding 'UTF-8-SIG' for files. 'UTF-8-SIG' = normal 'UTF-8' file, but starting by a Byte-Order-Mark. (see http://en.wikipedia.org/wiki/Byte_order_mark#UTF-8) If you'll read the second paragraph: The Unicode Standard permits the BOM in UTF-8 http://en.wikipedia.org/wiki/UTF-8,[2] http://en.wikipedia.org/wiki/Byte_order_mark#cite_note-2 but does not require or recommend its use.[3] http://en.wikipedia.org/wiki/Byte_order_mark#cite_note-3 (though that should be nor instead of or) - if I try to read an utf-8-sig file generated by excel or other thing, my header is blowed up because of this lack of feature, That's a bug. A BOM is senseless in UTF8 because UTF8 has no byte-ordering issues. Many software chokes on a BOM (e.g. i've seen PHP-based sites go down because a dev's editor inserted one and it got deployed). -- - 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] papercut wish list : a PRAGMA encoding='UTF-8-SIG'
On Wed, Sep 3, 2014 at 11:13 PM, jose isaias cabrera jic...@cinops.xerox.com wrote: Stephan Beal wrote... On Wed, Sep 3, 2014 at 9:56 PM, big stone stonebi...@gmail.com wrote: issues. Many software chokes on a BOM (e.g. i've seen PHP-based sites go down because a dev's editor inserted one and it got deployed). PHP should handle the encoding whether or not it has the BOM. As should Excel! Unlike Excel, with PHP the fix is easy - remove the BOM, which is simple once you have a program which lets you know it's there (it's hidden in many editors). My point is only - adding a BOM is not a viable solution: it's a deprecated/discouraged/worst-practice because so many tools don't deal well with them. -- - 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] Curiosity question for an apparent change in Sqlite/timeline pattern
On Tue, Aug 26, 2014 at 6:58 PM, big stone stonebi...@gmail.com wrote: (maybe I'm wrong and all is as speedy as usual) Maybe this additional info will help: http://sqlite.org/src/reports?view=byweek http://sqlite.org/src/reports?view=bymonth -- - 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] Window functions?
On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille petite.abei...@gmail.com wrote: True. But what a quantum leap that would be. Like moving from the wheelbarrow to the jet engine. For the small percentage of users who need it (or would even know how to apply it). i've been following this list since 2006 or 2007 and i recall this topic having come up only a small handful of times, which implies that only a small minority of users feels the need for it. -- - 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] Window functions?
On Mon, Aug 25, 2014 at 9:43 PM, forkandwait webb.spra...@gmail.com wrote: Stephan Beal sgbeal@... writes: For the small percentage of users who need it (or would even know how to apply it). i've been following this list since 2006 or 2007 and i recall this topic having come up only a small handful of times, which implies that only a small minority of users feels the need for it. or money, just that lack of mention on the listserv doesn't necessarily imply such a fact. As a many-year veteran of open source projects, i can attest that the two primary indicators for do we need this? are: a) does it scratch a personal itch? b) are users asking for it? If (b) isn't happening where the devs can see it (the public lists/forums) then it is, in effect, not happening. i.e. i disagree with that point - not with the others brought up - i won't argue their utility, but i will argue that they don't fit in lite, given the multiple estimates given with regards to their development effort over the years. Many other products have them - use those. We don't need windowing functions taking up space on all the Android phones and embedded devices in the world (and those installations outnumber yours and mine by many times over). Now back to the in-progress horse beating, but whether the horse is dead or not apparently remains to be seen... -- - 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] How do I know the python functions registered on SQLite ?
On Thu, Aug 21, 2014 at 3:46 PM, Baruch Burstein bmburst...@gmail.com
wrote:
Any explanation? I ran into this issue today when using the fossil built-in
sqlite shell, and I thought I remembered that it has a function registered
for getting a raw blob, but couldn't remember the name.
There is one (select content('trunk')), but it's not available via the
'sqlite' command, which is basically just a thin shell (as it were) around
the standard sqlite3 shell. Before it launches, the Fossil instance gets
shut down, so the underlying sqlite handle has no association with Fossil,
i.e. with features like blob-fetching.
It's unfortunate, but i don't see a way to solve it without forking the
sqlite shell (which i'm not willing to do). OTOH, libfossil can do this ;)
http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/wiki?name=f-tools
(specifically, f-query)
http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/wiki?name=DbFunctions
--
- 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] Problem with coalesce in a where clause with bind variables
On Wed, Aug 6, 2014 at 5:29 PM, Martin Engelschalk
engelsch...@codeswift.com wrote:
Hello List,
I have a strange problem with a statement like
select id from some_table where coalesce(some_col, 1) = :1
where :1 is a bind variable which i bind in my program using
sqlite3_bind_text.
fwiw, i cannot reproduce this using:
#define SQLITE_VERSION3.8.5
#define SQLITE_VERSION_NUMBER 3008005
#define SQLITE_SOURCE_ID 2014-06-04 14:06:34
b1ed4f2a34ba66c29b130f8d13e9092758019212
so long as the value i bind is of the same type being returned. If, e.g.,
some_col is an integer and i bind '3' instead of integer 3, then i am
seeing what you describe. If i bind an integer to the integer column, or
bind a string to a coalesce-returned string value, then i see what one
would expect.
For completeness:
s2 var d = Fossil.Db.open(':memory:')
s2 d.exec(Create table t(a))
s2 d.exec(insert into t(a) values(1),(2),(NULL),(3))
s2 d.each({sql:'select * from t',callback:'print(this)'})
[1]
[2]
[null]
[3]
s2 d.each({sql:'select * from t where
coalesce(a,9)=$a',callback:'print(this)',bind:{$a:'9'}})
NO RESULT HERE
s2 d.each({sql:select * from t where
coalesce(a,'9')=$a,callback:'print(this)',bind:{$a:'9'}})
[null]
coalesce result of string type
s2 d.each({sql:select * from t where
coalesce(a,'9')=$a,callback:'print(this)',bind:{$a:'1'}})
result: Db@0x1eb4d20[scope=#1@0x7fffd16b3068 ref#=1] == Db@0x1eb4d20
integer column but string binding
s2 d.each({sql:select * from t where
coalesce(a,'9')=$a,callback:'print(this)',bind:{$a:1}})
[1]
integer column and integer binding
s2 print(d.selectValue(select 1='1'))
0
--
- 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
[sqlite] feature request: aliasing (or renaming) db (not table) names
Hi, sqlite team, i meant to address this directly to Richard a couple weeks back, but we got carried away with other topics... In the Fossil SCM and (by extension) libfossil, we juggle 1-3 db handles for the config, checkout, and repo dbs. The first db which gets opened (it's use-case dependent!) becomes the main db and the others get attached with a well-defined name. The problem is, an application does not (cannot) necessarily know which order the dbs were opened, so it doesn't really know if main is the repo db, the checkout db, or the config db. Normally this is not a problem because we have no table name collisions which would require full qualification. However, it becomes exceedingly tedious when, for example, a client app wants to install a new table in (e.g.) the repo db. He doesn't know if he needs to use create table main.foo... or ... repo.foo He can, thanks to C APIs, fetch this info, but it intrudes quite a lot on the client code. (@Richard: this hasn't come up in fossil(1) yet only because client's don't have a way to do it, but it has come up in fossil(3) while experimenting with client-installed extensions.) My request is the ability to alias or rename the db (not table) names, so that i can tell libfossil that, e.g., repo is always the repo db, even if it's really (also) main. Perhaps a pragma: pragma table_alias oldname aliasname or pragma table_rename oldname newname either one would suit my purposes just fine. :-? -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 3:18 PM, Simon Slavin slav...@bigfraud.org wrote: Create a fourth database with no content. That's always the main one. Everything else is always attached to it. i did in fact try that (way back in the beginning), using a :memory: db as my main db. However, the :memory: VFS is (interestingly) not capable of generating temp file names, and i need that feature :/. -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 3:51 PM, Stephan Beal sgb...@googlemail.com wrote: i did in fact try that (way back in the beginning), using a :memory: db as my main db. note that i can't justify using a file for this purpose, because that file has to live somewhere, and the only reasonable place for it is in the checkout directory. It would clutter the source trees. -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 4:02 PM, Simon Slavin slav...@bigfraud.org wrote: Really ? It would dramatically simplify your programming and not take up much space. Oh well. It's not the space, but the pile of files debate which has raged for years in SCMs. Fossil already has its one clutter file, just like svn/git have their .svn/.git dirs. libfossil (a library-based implementation of Fossil) can't justify adding its own clutter to that. A simpler solution which would serve my goals just as well: the ability to rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const * newName)). i don't need 'main' because main is fluid in these apps. i need a well-defined name which sticks with a db regardless of whether it is opened or attached. -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 4:10 PM, Simon Slavin slav...@bigfraud.org wrote: On 24 Jul 2014, at 3:07pm, Stephan Beal sgb...@googlemail.com wrote: A simpler solution which would serve my goals just as well: the ability to rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const * newName)). i don't need 'main' because main is fluid in these apps. i need a well-defined name which sticks with a db regardless of whether it is opened or attached. What happens if you open any SQLite database, then ATTACH the same database to the same connection ? i was almost ready to kiss you, but that seems to not work: [stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db SQLite version 3.8.4.1 2014-03-12 19:38:38 Enter .help for usage hints. sqlite create table t(a); sqlite attach 'foo.db' as foo; sqlite .schema foo.t sqlite .dump foo.t PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite .schema t CREATE TABLE t(a); sqlite And yet... sqlite insert into foo.t(a) values(1),(2),(3); sqlite select * from foo.t; 1 2 3 sqlite ^D [stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db SQLite version 3.8.4.1 2014-03-12 19:38:38 Enter .help for usage hints. sqlite select * from t; 1 2 3 So ... that seems to work (just not with those shell .commands, but that's okay). i'll try it out and come back crying if it doesn't. THANK YOU! -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 4:45 PM, Simon Slavin slav...@bigfraud.org wrote: On 24 Jul 2014, at 3:38pm, Stephan Beal sgb...@googlemail.com wrote: THANK YOU! You're welcome. I'm still learning more from this list than I'm putting out. Hope we never meet, because i will likely kiss you if we do: [stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e select * from ckout.vfile limit 1 -S SQL TRACE #1: PRAGMA foreign_keys=OFF; SQL TRACE #2: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil/_FOSSIL_' AS ckout; ^^^ that's your baby. SQL TRACE #3: SELECT value FROM vvar WHERE name='repository'; SQL TRACE #4: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil.fsl' AS repo; SQL TRACE #5: SELECT login FROM user WHERE uid=1; SQL TRACE #6: SELECT value FROM config WHERE name='allow-symlinks'; SQL TRACE #7: SELECT value FROM vvar WHERE name='checkout'; SQL TRACE #8: SELECT uuid FROM blob WHERE rid=5864; SQL TRACE #9: BEGIN TRANSACTION; SQL TRACE #10: select * from ckout.vfile limit 1; id vid chnged deleted isexe islink rid mrid mtime pathname origname 1397 5864 0 0 0 0 2605 2605 1395763875 .fossil-settings/binary-glob NULL SQL TRACE #11: COMMIT; SQL TRACE #12: DETACH DATABASE repo; before your patch, that would have failed with unknown db because ckout was only known as main. So [stephan@host:~/cvs/fossil/libfossil/src]$ f com -m Eureka: Simon Slavin found a way to apply a concrete name to the main db. Seems to work. fsl_cx.c Autosync: http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi Round-trips: 1 Artifacts sent: 0 received: 0 Pull finished with 2964 bytes sent, 2238 bytes received New_Version: 5abda43115e11c357aa36a1b7231780767b04c23 Autosync: http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi Round-trips: 1 Artifacts sent: 2 received: 0 Sync finished with 5625 bytes sent, 4906 bytes received Resulting in: http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/info/5abda43115e11c357aa36a1b7231780767b04c23 THANK YOU! (But i still think the ability to rename the main would be a useful feature!) -- - 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] feature request: aliasing (or renaming) db (not table) names
On Thu, Jul 24, 2014 at 4:51 PM, Stephan Beal sgb...@googlemail.com wrote: [stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e select * from ckout.vfile limit 1 -S BTW: the -S option has historically meant SQL Tracing, but i think i'll rename it to Simon now ;). i've been fighting with this db name juggling for almost exactly a year, and one line of code resolves it completely. -- - 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] Hexadecimal integer literals
On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp d...@sqlite.org wrote:
The current SQLite implementation (on the hex-literal branch) works by
converting hex literals of 64 bits or less into a signed 64-bit integer.
Overflow/underflow are unspecified for signed types, and the / ops
could certainly overflow.
1.84467440737096e+19. This approach means that all hexadecimal literals are
positive numbers, except literals 0x8000 through
0x which are negative. There is this range of negative
values smack in the middle of an otherwise uniformly increasing sequence of
positive numbers. That negative range seems discombobulating.
Indeed.
(1) Keep the current prototype behavior. Hex literals of 64 bits or less
are converted into twos-complement 64-bit integers, meaning that some
values are negative. Hex literals of 65 bits or more are converted into a
floating-point approximation.
Sounds reasonable.
(2) Declare that the values of hex literals greater than 64 bits are
undefined and convert them into random 64-bit integers.
Seriously: use
the random number generator to convert oversized hex literals into
integers, and thereby discourage programmers from using oversized hex
literals.
i wouln't go quite that far, but converting to NULL might be a middle
ground.
(4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
does.
Is reasonable, given that overflow for signed types is undefined (so any
approach is not wrong).
(7) Have the parser convert oversized hex literals into NULL, or throw
errors, and have attempts to coerce oversized hex literals strings into
numeric values fail, thus preventing affinity conversions.
+1 for NULL (sounds simplest and least surprising)
(8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
support hexadecimal integer literals.
Also good. Maybe, instead, add functions which take hex strings and return
an int. Then you've got much more freedom in terms of error reporting, as
it all happens via the function:
select hexint('0x10')
16
--
- 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] SQLite memory testing
On Fri, Jul 11, 2014 at 12:23 AM, RSmith rsm...@rsweb.co.za wrote: Next up, I proceeded to use the 64-bit DLL in a 64-bit build of the testbed and redid the DB and data population (just in case the DB itself was affected by 32-bitness, though unlikely since both DBs were equal in size and read correctly by both systems afterwards). Execution speeds were very similar, as were memory ramps. The query seemlessly executed, memory grew right up to the 6.4GB cache ceiling (above the baseline) without failing, without reporting an error and returned the correct result set. Almost an anti-climax, but nothing more to report really, it just worked. I increased the cache size to ~12GB but the next run topped out at just over 7GB before spitting out results and releasing the memory, so I assume my query just did not require more than that. And yet they refuse to remove 'lite' from the name ;). -- - 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] think I need better error-handling guidance in the C API
On Thu, Jul 3, 2014 at 4:27 PM, Eric Rubin-Smith eas@gmail.com wrote: I respectfully re-raise my issue. The TLDR is that the C API docs are critically lacking in specificity in a relatively wide range of areas, as exemplified below, making it difficult to write correct clients against the library. Do the SQLite authors disagree with my below points about the API docs for sqlite3_step(), sqlite3_exec(), and sqlite3_prepare_v2()? I think the criticisms can be extended to other API calls. If the authors do not disagree then can we create a ticket against the docs? (disclaimer: i'm not one of the authors) While i do fundamentally agree with your points, in practice it's not as difficult as you seem to be making it out to be. The rule is really simple: any non-0 code is an error unless the APIs specify otherwise, and the step() API does indeed document 2 non-error, non-0 error codes (SQLITE_STEP and SQLITE_DONE). Anywhere else in the API, non-0 means Bad News. There are relatively few cases (locking comes to mind) where an error returned due to something other than a misuse of the API should be considered recoverable (IMO). The fact that (literally) millions of applications get written despite any perceived shortcomings in the documentation suggests that the docs are at least adequate, if not perfect. -- - 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] think I need better error-handling guidance in the C API
On Thu, Jul 3, 2014 at 4:44 PM, Stephan Beal sgb...@googlemail.com wrote: non-error, non-0 error codes (SQLITE_STEP and SQLITE_DONE). Anywhere else in the API, non-0 means Bad News. There correction: SQLITE_ROW and SQLITE_DONE -- - 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] Porting SQLite to Nurit OS ver
On Wed, Jul 2, 2014 at 2:41 PM, Clemens Ladisch clem...@ladisch.de wrote: |Prototype causes non-standard conversion from `int' to `sqlite3_int64' This is just an overzealous and absurdly useless warning. Try to reduce the warning level. AFAIK, any conversion from a less precise type to a more precise type is does not constitute a warning. Only when precision could be lost (int64==int, or signedness differences) is a warning in order. Maybe sqlite3_int64 on that platform is of a type smaller than int, or is unsigned? That'd be weird, but i've seen weirder (8-byte booleans!). warning 572: Potentially dangerous pointer cast: |sizeof `struct FuncDef' (../sqlite/sqlite3.c,L8668/C24) differs from |sizeof `struct FuncDef[8]' and |accessing an lvalue through this pointer alias may violate assumptions |in the ANSI C Language Reference used by the optimizer. |(See (X3.159-1989 p.39,L18) on object access for more details.) I'm quite sure that this interpretation of the standard is plain wrong, +1, and unless i'm misunderstanding the problem (haven't got the code on this machine and am too lazy to google it up), it's simple to deduce that this is a compiler bug: in C, for any array of type X, any element of that array is guaranteed to be exactly sizeof(X). C guarantees this, or conventional array traversal (via ptr++ resp. ++ptr) could not work. -- - 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] Linux sqlite3 executable return value - what does it return?
On Mon, Jun 16, 2014 at 5:37 PM, c...@isbd.net wrote:
Does the return value contain anything that will tell me if the SQL
worked or not?
...
set_table_name(data, 0);
if( data.db ){
sqlite3_close(data.db);
}
sqlite3_free(data.zFreeOnClose);
return rc;
}
it returns the result code of the last run command. That said, the only
portable result codes are success (0) and not success (not 0, but which
not 0 is not specified). In my experience, most Linux shells support
result values -127 to 128, but that's just a common convention, not a rule.
Bash seems to support 0-255 on this machine:
[odroid@host:~/fossil/fossil]$ echo 'exit 129' foo.sh
[odroid@host:~/fossil/fossil]$ sh foo.sh
[odroid@host:~/fossil/fossil]$ echo $?
129
[odroid@host:~/fossil/fossil]$ echo 'exit 329' foo.sh
[odroid@host:~/fossil/fossil]$ sh foo.sh
[odroid@host:~/fossil/fossil]$ echo $?
73
If it doesn't then what's the easiest way for a script to tell if some
SQL executed by sqlite3 worked or not? I just want to do a single row
insert into a table and, if it succeeds, the source of the inserted
data can be removed.
Failing to SELECT or DELETE anything is not an error, so no, it won't fail
in that case. It will fail if your syntax is wrong:
[odroid@host:~/fossil/fossil]$ echo drop foo; | sqlite3
Error: near line 1: near foo: syntax error
[odroid@host:~/fossil/fossil]$ echo $?
1
--
- 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] Linux sqlite3 executable return value - what does it return?
On Mon, Jun 16, 2014 at 10:03 PM, c...@isbd.net wrote:
Remember - I said I'm doing an INSERT, either it will insert the
intended data or there will be an error.
If it fails because of a syntax error then the shell exits with non-0.
Presumably (based on a quick scan of the code) it does the same for any
app-fatal error (e.g. a read-only db, which could cause an insert to fail).
You can test this yourself very easily:
[odroid@host:~/fossil/cwal/s2]$ sqlite3 foo.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(a);
sqlite
[odroid@host:~/fossil/cwal/s2]$ chmod 0400 foo.db
[odroid@host:~/fossil/cwal/s2]$ echo insert into t values('aaa'); |
sqlite3 foo.db
Error: near line 1: attempt to write a readonly database
[odroid@host:~/fossil/cwal/s2]$ echo $?
1
So yes, it fails. Any specific error code it returns is moot, though,
because POSIX doesn't guaranty any specific range of errors other than OK
and Not OK. Additionally, a large-enough error code could overflow (as in
my first example), leading to unpredictable results.
--
- 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] lifetime of buffer referred to with SQLITE_STATIC
On Fri, Jun 13, 2014 at 7:20 PM, Eric Rubin-Smith eas@gmail.com wrote: If I say sqlite_bind_text(...SQLITE_STATIC), I am promising that the buffer is going to stick around for a while. How long am I promising that it will stick around? Til the next statement reset()? Til the statement finalize()? Til the database close()? Until the statement is done with that specific value. My rule of thumb is: the bytes must outlive the step() call. After step(), the statement won't do anything with those bytes (won't read/inspect them) because it has no need to. i use SQLITE_STATIC quite a lot for non-static data (b/c copying bytes for this type of thing pains me greatly), and the only guaranty i've ever needed making is that the bytes outlive the step() which accesses them. -- - 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] Comments loss in sqlite_master table
On Thu, May 29, 2014 at 10:48 AM, Luuk luu...@gmail.com wrote: According to this: http://www.sqlite.org/lang_createtable.html The comment is not part of the 'full statement'. According to the grammar charts, comments can't appear at all. Comments being allowed to appear is implicit. That said: there are very likely client programs out there which parse the schema from sqlite_master for various purposes, and comments could very well break them: create table foo(a) -- old version: create table foo(a,b) ; -- - 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] sqlite4 Windows build
On Thu, May 29, 2014 at 4:22 PM, jose isaias cabrera cabr...@wrc.xerox.com wrote: http://www.sqlite.org/draft/howtocompile.html#dll The draft page above will be promoted to the official website at the next release. Caveat: those instructions are for sqlite3 and may not apply for v4. There is, AFAIK, no pending release for v4 planned in the immediate future. -- - 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] Simple Select from IN - from a newbie.
On Fri, May 23, 2014 at 8:31 PM, Andy Goth andrew.m.g...@gmail.com wrote: CREATE TABLE table (id INTEGER PRIMARY KEY, a, b, c); table in the KEY of C won't cause any confusion in the context of music, will it ;) So that's what double quotes means. Single quotes, on the other hand, are used to enter string literals. FWIW, you can also use [table] instead of table. -- - 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] Simple Select from IN - from a newbie.
On Fri, May 23, 2014 at 8:33 PM, Stephan Beal sgb...@googlemail.com wrote: FWIW, you can also use [table] instead of table. With, apparently, some corner-cases: sqlite create table [t] (a,b,c); sqlite insert into [t] values(1,2,3); But... sqlite .dump [t] PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; ooops! sqlite .dump t PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [t] (a,b,c); INSERT INTO t VALUES(1,2,3); COMMIT; -- - 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] Simple Select from IN - from a newbie.
On Fri, May 23, 2014 at 8:53 PM, Humblebee fantasia.d...@gmail.com wrote: Does this mean that if the View is Temporary, then it's not cached? and for normal views, it's cached? No - a TEMP VIEW means the view is automatically destroyed when you close the db connection, and that view is ONLY visible to that specific connection. i believe what he meant was to contrast a VIEW with a TABLE - the VIEW will be re-processed on each run, whereas a TABLE is not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 4:55 PM, Dominique Devienne ddevie...@gmail.comwrote: On Thu, May 22, 2014 at 3:26 PM, Noel Frankinet noel.franki...@gmail.com wrote: I propose Musqlar, the Mighty universal sql Archiver :-) Sounds like Musk-lar, so you loose the S.Q.L. of sqlar which sounds to me like C-quel-ar ;) Maybe this is stretching it a bit, but how about... darh? -- - 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] Simple Select from IN - from a newbie.
On Wed, May 21, 2014 at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote: | 1 | 4 | 1,5,2,3,4 | | 2 | 5 | 2,6,3,5,1 | +--| ... @Simon, thank you for showing me a better way to setup the tables, Without doing what Simon suggests, there is no good solution to your problem with the data structure you have. SQL is made for normalized data, not strings containing arbitrary tokens separated by arbitrary other tokens. your way makes much more sense, only problem is that in this situation, the tables have already been defined and made by someone else so I cannot change it. I'm a bit stuck with the way it is. Then you're stuck with the problem you've got. -- - 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] Simple Select from IN - from a newbie.
On Wed, May 21, 2014 at 8:45 PM, Jim Dodgen j...@dodgen.us wrote: I fully agree a bad database design can impact you for the life of the application. If this is a class assignment and the instructor gave you this as a problem then I can understand I cannot change it otherwise fix it now or pay forever. If it IS a class assignment, the professor's point will almost certainly be along the lines of, see how difficult that was? Now let's see how much easier it can be done... (at which point he introduces normalization). -- - 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] Is sqlite case-sensitive?
On Sun, May 18, 2014 at 4:37 PM, Baruch Burstein bmburst...@gmail.comwrote: names), or without context (just validate syntax, e.g. that it can be parsed)? I am asking about this API since I think I remember seeing it once, but can't find it now i _think_ what you want is: http://sqlite.org/c3ref/complete.html -- - 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] LIKE operator and collations
On Mon, May 12, 2014 at 4:05 PM, Constantine Yannakopoulos alfasud...@gmail.com wrote: I understand that it is difficult to find the least greater character of a given character if you are unaware of the inner workings of a collation, but maybe finding a consistent upper limit for all characters in all possible collations is not impossible? i don't know if this helps, but i recently ran across a query both in the Fossil SCM and in SVN (via a post on this list) which does something similar for paths: char const * zCollation = fsl_cx_filename_collation(f); rc = fsl_db_prepare(db, st, SELECT id FROM vfile WHERE vid=%FSL_ID_T_PFMT AND (pathname=%Q %s OR (pathname'%q/' %s AND pathname'%q0' %s)), (fsl_id_t)vid, zName, zCollation, zName, zCollation, zName, zCollation ) that zCollation part resolves to either an empty string or COLLATE nocase, depending on application-level settings. zPath is a filename or dir name. the 'vid' part there is not relevant for you, but the pathname conditions sound similar to what you are trying to achieve. -- - 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] trying sqlite4 but compilation error
On Mon, May 12, 2014 at 5:06 PM, Alain Meunier dec...@hotmail.fr wrote: System Debian64 - testing When I try to compile the tarball that I've found here : http://www.sqlite.org/src4/info/8a39847dafa3047ba5d6107f0032c6b39d0ef104 Can you try this: make -f Makefile.linux-gcc that one works for me on Mint (Debian derivative). it fails to link pthread at the end, but that's easy enough to fix: there's a commented out line here: #THREADLIB = -lpthread THREADLIB = With that, it builds for me. (interestingly, it wants pthread even though THREADSAFE is set to 0?) -- - 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] trying sqlite4 but compilation error
On Mon, May 12, 2014 at 11:03 PM, Alain Meunier dec...@hotmail.fr wrote: /SQLite4-8a39847dafa3047b/src/fts5func.c:159: undefined reference to `log' collect2: error: ld returned 1 exit status make: *** [sqlite4] Error 1 Now fts seems to call an undefined ref.. :( Did you get the same problem ? Try adding -lm (math). i didn't see that error, but i remember seeing it a long time ago and it seems my local copy it pached for that already: [stephan@host:~/cvs/fossil/sqlite4]$ f diff Index: Makefile.linux-gcc ... #TLIBS = -lrt# fdatasync on Solaris 8 -TLIBS ?= +TLIBS ?= -lm -- - 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] WITHOUT ROWID option
On Fri, May 9, 2014 at 8:36 AM, Andy Goth andrew.m.g...@gmail.com wrote: On 5/8/2014 10:11 AM, Jim Morris wrote: To improve efficiency you could add where 1=2 to avoid returning any rows. Should just check validity. This being SQLite, as previously discussed, you could say where 0 :^) i don't think a WHERE is necessary to improve the efficiency. The statement only gets prepared, not stepped, and i would not expect any analysis of results until the first step() (but maybe i'm assuming too much). -- - 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] WITHOUT ROWID option
On Fri, May 9, 2014 at 11:38 AM, Stephan Beal sgb...@googlemail.com wrote: i don't think a WHERE is necessary to improve the efficiency. The statement only gets prepared, not stepped, and i would not expect any analysis of results until the first step() (but maybe i'm assuming too much). In fact, a WHERE, if it's not optimized out, might even add memory cost for the tokens required for parsing it. -- - 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] General error: 11 malformed database schema - near 'NOCASE':
On Fri, May 9, 2014 at 1:43 PM, Kleiner Werner sqliteh...@web.de wrote: The PHP connection to SQLite DB (3.3.7) is made with PDO. It's not clear in the docs whether 3.3 has NOCASE. Perhaps it did not? 3.3 is ancient. http://www.sqlite.org/releaselog/3_3_7.html -- - 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] General error: 11 malformed database schema - near 'NOCASE':
On Fri, May 9, 2014 at 2:09 PM, Stephan Beal sgb...@googlemail.com wrote: On Fri, May 9, 2014 at 1:43 PM, Kleiner Werner sqliteh...@web.de wrote: The PHP connection to SQLite DB (3.3.7) is made with PDO. It's not clear in the docs whether 3.3 has NOCASE. Perhaps it did not? 3.3 is ancient. http://www.sqlite.org/releaselog/3_3_7.html Nevermind: NOCASE appears in a changelog comment for 3.2.6: http://www.sqlite.org/changes.html -- - 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] WITHOUT ROWID option
On Wed, May 7, 2014 at 4:57 PM, Simon Slavin slav...@bigfraud.org wrote:
somehow ? Perhaps the ROWID field of a table might have its own
particular indication, and if you don't see any rows marked like that you
could deduce that the table had no ROWID column. I'm sure there are better
ways the
This isn't efficient, but it should work without corner cases: (pseudocode):
function hasRowId(tablename) {
prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
prepare SELECT rowid FROM tablename; // if this fails, rowid missing
return true only if the second PREPARE succeeds.
}
--
- 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] Foreign Key errors
On Tue, May 6, 2014 at 11:24 PM, Petite Abeille petite.abei...@gmail.comwrote: On May 6, 2014, at 11:17 PM, Richard Hipp d...@sqlite.org wrote: It is theoretically possible to keep track of which constraints are failing so that the particular constraint can be identified in the error message. But that woudl require more memory and CPU cycles. That would be resources well spent. And might even be justifiable given other recent speed improvements which offset them ;). -- - 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] select 1 where 1 - 1;
On Mon, May 5, 2014 at 6:56 PM, Petite Abeille petite.abei...@gmail.comwrote: select 1 where 1 = 1; Indeed. I would have expected a syntax error along the lines of 'invalid relational operator’ or such. And that’s that. Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b', and internally sqlite doesn't really know that it's comparing two constants with the same value until it has compared them to figure that out unless its optimizer does that somehow, but the end result must be the same with and without the optimizer. i.e. it's just a normal comparison operation, for all intents and purposes, and 1=1 === true in all languages i've worked with (except that SQL uses '=' instead of '==' or '===', but that's just a syntactical difference). (NaN tends to have unusual comparison rules, though, e.g. NaN does not compare equal to itself in some languages.) -- - 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] select 1 where 1 - 1;
On Mon, May 5, 2014 at 7:31 PM, Petite Abeille petite.abei...@gmail.comwrote: On May 5, 2014, at 7:15 PM, Stephan Beal sgb...@googlemail.com wrote: Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b’, I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or any of this nonsense. There is nothing to compare. It’s nonsensical. Oh, but there is: 1-1 is an expression, the result of which is integer 0 (as opposed to string '0'), which, in all programming environments except, IIRC, Xenix, is boolean false. In fact, the sqlite shell makes a handy ad hoc calculator for exactly that purpose: sqlite select 1-1; 0 sqlite select typeof(1-1); integer -- - 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] BLOBs and NULLs
On Wed, Apr 23, 2014 at 6:56 AM, Keith Medcalf kmedc...@dessus.com wrote: You don't ever really need a GUID at all. Simply use an integer primary key (an integer starting at 1) and simply pretend that it is being added to the applicable base GUID of your random choosing. Everything will still be unique and you will have saved yourself a crap load of storage space, index space, and conserved countless CPU cycles so that they can be spent on something more productive productive. I have never seen a need to actually use a GUID for anything, it is a ridiculous concept. Until one implements a DCVS (i.e. Fossil), at which point sequential numbers become literally impossible to generate. -- - 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] BLOBs and NULLs
On Tue, Apr 22, 2014 at 8:25 PM, Dominique Devienne ddevie...@gmail.comwrote: Yet I don't see the point of a BIGINT either. A blob can effectively act as a arbitrary sized integer already, albeit one stored in base 256 and on which you cannot do arithmetic, but that's OK and enough to use it as a PK / FK. A blob can store raw binary data, i.e. raw integers from memory. Just be sure to encode/decode them if you want their stored representations to be platform-portable (big vs little endian). You can bind a blob using (myInt, sizeof(myInt)) if you really want to, it just won't be platform-portable without settling on an encoding. If the goal is only performance, though, it might (without encoding) be (marginally) faster than using string-format data (be sure to use SQLITE_TRANSIENT when binding the memory, too). -- - 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] BLOBs and NULLs
On Tue, Apr 22, 2014 at 8:55 PM, Dominique Devienne ddevie...@gmail.comwrote: than using string-format data (be sure to use SQLITE_TRANSIENT when binding the memory, too). Sorry - i meant SQLITE_STATIC. If your memory will outlive the step() call then use that, _NOT_ SQLITE_TRANSIENT, to avoid that sqlite makes a copy of the memory. -- - 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] SQLite for single user data manipulation
On Wed, Apr 16, 2014 at 6:13 PM, RSmith rsm...@rsweb.co.za wrote: On 2014/04/16 03:39, Nick Eubank wrote: I started in Postgres, but discovered that in Windows one cannot increase some of the per-query RAM memory caps above 2gb (I know -- I would love to switch operating systems, but it's beyond my control). So I'm thinking of moving to SQLite. Firstly, Windows isn't limiting the memory you can use, you can use all 16gb installed memory, and in fact much more if needed (which will just cause a lot of drive-swapping and slowness, but no less...), the 2gb limit is in fact a 32bit limit and is self imposed by any 32 bit application See also: https://sqlite.org/limits.html -- - 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] Database corruption issue
On Fri, Apr 11, 2014 at 1:38 PM, Simon Slavin slav...@bigfraud.org wrote: I seem to recall that the sqlite3_open() call dos not really open the database. The open actually happens when the data is first accessed. So to do the above one process that opens the database does one need to do some kind of access (presumably a harmless SELECT) ? There was recently a post to this effect, where Richard said: On Mon, Sep 2, 2013 at 10:52 PM, Richard Hipp d...@sqlite.org wrote: sqlite3_open() and sqlite3_open_v2() defer the actual opening of the database file until you do something with the database. This is to give you the opportunity to issue PRAGMA statements that might influence the opening process. Running a very simple statement like SELECT 1 FROM sqlite_master LIMIT 1; -- - 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] Can sqlite access storage system directly?
On Fri, Apr 4, 2014 at 10:52 AM, 김병준 bjkm1...@naver.com wrote: filesystem help, but there will be performance gain. ( e.g. From not using Journaling See: http://www.sqlite.org/pragma.html#pragma_journal_mode -- - 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
