Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Jay A. Kreibich
On Wed, Oct 26, 2011 at 11:35:33PM +0200, Tobias Mohrl?der scratched on the wall: > On Wednesday, Oct 26, 2011 at 02:26PM Jay A. Kreibich wrote: > > > On Tue, Oct 25, 2011 at 11:51:46PM +0200, Tobias Mohrl?der scratched on the > > wall: > >> Hello everybody, > &g

Re: [sqlite] Performance of SELECT: What am I doing wrong?

2011-10-26 Thread Jay A. Kreibich
tmt, > 0); Don't use "IS", use "=". The two operations are quite different. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong peo

Re: [sqlite] Tables as ASCII - is it possible?

2011-10-23 Thread Jay A. Kreibich
s drivers for most popular scripting languages. Just access the database as it was meant to be accessed. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to

Re: [sqlite] Doc bug in on-line description of round() function

2011-10-20 Thread Jay A. Kreibich
On Thu, Oct 20, 2011 at 04:03:28PM -0700, Peter Aronson scratched on the wall: > The "Using SQLite" book, I notice gets it right, however. Score! -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it

Re: [sqlite] does a unique constraint imply an index

2011-10-20 Thread Jay A. Kreibich
ze queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is li

Re: [sqlite] IN condition on virtual tables misbehavior

2011-10-20 Thread Jay A. Kreibich
hout a very detailed understanding of the access efficiency, it is often better to just fall back to the safest plan, which is to scan the v-table once and deal with all the conditionals internally. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like u

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

2011-10-19 Thread Jay A. Kreibich
On Wed, Oct 19, 2011 at 03:24:35PM -0500, Nico Williams scratched on the wall: > On Wed, Oct 19, 2011 at 2:23 PM, Jay A. Kreibich wrote: > > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the > > wall: > >> On Wed, Oct 19, 2011 at 4:00 AM, S

Re: [sqlite] How to make SQLite run safely on CIFS mounted file system?

2011-10-19 Thread Jay A. Kreibich
; Nothing. Even MS Access cannot (or could not way back when i used it) be > > safely used on SMB/CIFS storage. > > Can you elaborate as to why? http://sqlite.org/faq.html#q5 In short: buggy filesystem code that doesn't support distributed locks correctly. -j -- Jay A.

Re: [sqlite] Can DBI supply a more specific foreign key mismatch error?

2011-10-13 Thread Jay A. Kreibich
All indicators still point to your database having a foreign key issue. > If there is no such more better > reporting, ok, I will take other approaches. If you don't know the > answer, you could either say so or say something useful or say nothing. You might want to check in

Re: [sqlite] Query Problems - Keyword? Encoding?

2011-09-29 Thread Jay A. Kreibich
t; > SELECT * FROM recordings WHERE "key" = > '4df0247ce1a97685a782d2cb051b48ed952e666c'; > The only thing I can think of is some sort of encoding > issue that the LIKE operator is getting around somehow. Or perhaps the > fact that it is a keyword? = is case sensitive, LIKE

Re: [sqlite] Sqlite3 transactions without Durability

2011-09-25 Thread Jay A. Kreibich
ule: http://www.sqlite.org/asyncvfs.html It has it's own set of limitations and performance concerns, including concurrency limits from multiple processes, but it is another option. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is i

Re: [sqlite] Indices recreation

2011-09-22 Thread Jay A. Kreibich
incur a performance penalty for insert/update/delete operations. Stats (see ANALYZE) are *not* updated automatically, so if you are using those and your table stats are somewhat dynamic, you may need to re-run ANALYZE. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Jay A. Kreibich
s duplicates. Yes, please. Frequently when questions like this come up, the SELECTs don't actually show a problem. How are you inserting these? Your own code? Are you carefully checking all return codes? Do you actually know the dups are ending up in the database? -j -- Ja

Re: [sqlite] Does coalesce terminate early?

2011-09-15 Thread Jay A. Kreibich
returns a > copy of its first non-NULL argument." Why? It is a function call. One would expect all the parameters to be evaluated, and then the function called. In almost all languages, short-circuit evaluation is reserved for operators, not function parameters. -j -- Jay A. K

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Jay A. Kreibich
report no rows, when it is really trying to report an API error. Depending on how this API maps to the native API, the issue may be the SELECT, not the UPDATE. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that yo

Re: [sqlite] No error on selecting non-grouped column

2011-09-14 Thread Jay A. Kreibich
is a relative thing. Result sets have no set or defined ordering without an ORDER BY, and you cannot use an ORDER BY in this case, as it is applied after the GROUP BY operation. Adding an index, or future query optimizations may cause the order to

Re: [sqlite] No error on selecting non-grouped column

2011-09-14 Thread Jay A. Kreibich
On Wed, Sep 14, 2011 at 12:24:57PM -0700, Gerry Snyder scratched on the wall: > Would be pragma to reverse unordered selects show a different result? Very likely, yes. http://sqlite.org/pragma.html#pragma_reverse_unordered_selects -j -- Jay A. Kreibich < J A Y @ K R E I B

Re: [sqlite] No error on selecting non-grouped column

2011-09-14 Thread Jay A. Kreibich
t is nice to be able to keep the groupings simple. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johns

Re: [sqlite] Index on BOOLEAN field

2011-09-13 Thread Jay A. Kreibich
ing for. > > > > Chunkiness? Surely you mean selectivity, no? > > I'm sorry, but I've failed to find a better word. Clumpy, not chunky. 8-) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that

Re: [sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

2011-09-12 Thread Jay A. Kreibich
On Mon, Sep 12, 2011 at 06:56:50PM +0200, Stephan Beal scratched on the wall: > On Mon, Sep 12, 2011 at 6:47 PM, Jay A. Kreibich wrote: > > > On Mon, Sep 12, 2011 at 12:29:56PM +0800, ?? scratched on the wall: > > > is there any limit about the data size? > >

Re: [sqlite] sqlite in-memory mode - Is there any limit about the data size using in-memory mode?

2011-09-12 Thread Jay A. Kreibich
In-memory DBs live in the cache, which also has a small per-page overhead, so the total memory usage will be slightly more than (page_size * page_count). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but sh

Re: [sqlite] Multi-column unique constraint in SQLite

2011-09-11 Thread Jay A. Kreibich
On Sun, Sep 11, 2011 at 03:00:10PM +0100, Simon Slavin scratched on the wall: > On 11 Sep 2011, at 2:49pm, Jay A. Kreibich wrote: > > >> I think that the 'OR REPLACE' clause refers to the primary key, > > > > No, it will trigger on any UNIQUE constraint viol

Re: [sqlite] Multi-column unique constraint in SQLite

2011-09-11 Thread Jay A. Kreibich
0","187","1","1","1","1","50","0","1","1","1","20","2","2011-09-05"); > > > >The two INSERT rows are identical except the value under "settim

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

2011-09-02 Thread Jay A. Kreibich
descriptor number) are going to be O(n). I would run a quick test that just calls the system level open(2) type call, and see if you observe the same type of slow-down. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important tha

Re: [sqlite] SQLite discovery otD -- ASCII newlines

2011-09-01 Thread Jay A. Kreibich
ng if there's a > problem depending on whether the field is UTF-8 or UTF-16. If there are other C-style escapes, it will incorrectly deal with something like "\\n". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is

Re: [sqlite] (no subject)

2011-08-31 Thread Jay A. Kreibich
ast recently attached. In other words, SQLite will generally search the temp database, the main database, and then all attached databases in index order. This brings up some odd edge cases, as the temp database is searched before the main database, even though the main database has

Re: [sqlite] Creating a database with a script or SQLite command

2011-08-30 Thread Jay A. Kreibich
ing like this: sqlite3 newdatabase.db << EOF CREATE TABLE IF NOT EXISTS t1 ( a, b, c ); CREATE TABLE IF NOT EXISTS t2 ( d, e, f ); EOF -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you hav

Re: [sqlite] Creating a database with a script or SQLite command

2011-08-30 Thread Jay A. Kreibich
cess continues with a series of CREATE TABLE IF NOT EXISTS... statements, a new database will have the file created and defined, while an existing database will create/ignore the tables depending on the existing structure. ** Who are you, and what did you do with Igor? -j -- Jay A. K

Re: [sqlite] SQL ROWNUM option is failed - Want to Read records in chunks from table

2011-08-26 Thread Jay A. Kreibich
be using it. From the first line of the docs <http://sqlite.org/c3ref/free_table.html>: This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Int

Re: [sqlite] Bulk Insert

2011-08-12 Thread Jay A. Kreibich
. http://www.sqlite.org/faq.html#q19 -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable

Re: [sqlite] Field drop work around

2011-08-02 Thread Jay A. Kreibich
, and indexes that are using those columns is left as an exercise for the coder. 8-)Humm... maybe that's why we don't have a ALTER TABLE...DROP COLUMN. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have i

Re: [sqlite] Field drop work around

2011-08-02 Thread Jay A. Kreibich
part of table and database definition. Many people also use CHECK typeof() constraints, making specific columns more strictly typed. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the w

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

2011-08-01 Thread Jay A. Kreibich
to be used by any virtual table. > This problem did not exist in SQLite 3.7.4. What did earlier versions do? > Do I miss something or is this a bug? I assume it is a change in the query optimizer. Since this is a legit way to express an IS NOT NULL, it isn't exactly "

Re: [sqlite] Exclusive locking mode not working as expected?

2011-07-29 Thread Jay A. Kreibich
e access? You can set the file permissions. That's a more appropriate means to prevent this kind of operation. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has

Re: [sqlite] Exclusive locking mode not working as expected?

2011-07-29 Thread Jay A. Kreibich
t; to the database files. "PRAGMA locking_mode = EXCLUSIVE" never releases the locks. http://www.sqlite.org/pragma.html#pragma_locking_mode -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but

Re: [sqlite] Editing sqlite3 commands

2011-07-28 Thread Jay A. Kreibich
On Thu, Jul 28, 2011 at 12:57:45PM -0700, km4hr scratched on the wall: > > Is there a way to edit SQL commands typed in on the command line? Only if the "sqlite3" tool is compiled with a copy of the readline library. In that case, just hit up-arrow. -j -- Jay A.

Re: [sqlite] SELECT query first run is VERY slow

2011-07-27 Thread Jay A. Kreibich
create index idx2 on t(i,rowid); > Error: table t has no column named rowid > > Any particular reason it can't be included in an index? Because it is always included as the last column. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like u

Re: [sqlite] DEvelopment Error

2011-07-25 Thread Jay A. Kreibich
ry. in my > programs main healer i did : #include "sqlite3ext.h" and linked the > LIB file. You want to use "sqlite3.h" in applications. The "sqlite3ext.h" file is for building library extensions -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] Strange result using JOIN

2011-07-22 Thread Jay A. Kreibich
It is the application code that needs to get fixed to accept data in an order that the SQL standard can provide. This isn't true of only SQLite, but all SQL database systems. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is impor

Re: [sqlite] Strange result using JOIN

2011-07-22 Thread Jay A. Kreibich
; > 61311;18461F; In SQL terms, these are the exact same. Table rows are *unordered* and can be returned in any order the database wants. If you need a result in a specific order, you must use an ORDER BY clause in your SELECT statement. -j -- Jay A. Kreibich < J A Y

Re: [sqlite] "override" table?

2011-07-20 Thread Jay A. Kreibich
e. Using the OUTER JOIN allows you to use arbitrary WHERE clauses, including queries that return multiple rows. That said, most people would likely consider the UNION approach easier to understand. -j > > Keith > > On Jul 20, 2011 11:35am, "Jay A. Kreibich" wrote

Re: [sqlite] "override" table?

2011-07-20 Thread Jay A. Kreibich
S ( 2, 2, 22, 222 ); sqlite> sqlite> SELECT coalesce( over.a, real.a ) AS a, ...>coalesce( over.b, real.b ) AS b, ...>coalesce( over.c, real.c ) AS c ...> FROM real LEFT OUTER JOIN over USING ( id ); 1|10|100 2|22|222 3|30|300 sqlite> The order of the j

Re: [sqlite] Performance Improvement

2011-07-18 Thread Jay A. Kreibich
or both storage space and your specific access patterns. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela

Re: [sqlite] Transaction and SQLITE_CANTOPEN

2011-07-14 Thread Jay A. Kreibich
le associated with the transaction. Make sure your application has write/create permissions to the directory with the database file. If you cannot provide that, put the database in a subdirectory and provide the permissions on the subdirectory. -j -- Jay A. Kreibich < J A Y @ K R

Re: [sqlite] multidimensional representation

2011-07-06 Thread Jay A. Kreibich
he query syntax can get quite messy. I would put together a few example cases of what you're trying to do with your application. In addition to the data layout, pay specific attention to the types of queries you need to run and how you're going to set those up. -j -- Jay A.

Re: [sqlite] Minimal SQLite

2011-07-04 Thread Jay A. Kreibich
ngs. If you're using an extensive number of OMIT flags, a bit of code clean-up may be required. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people ha

Re: [sqlite] ISNULL in sqlite

2011-06-25 Thread Jay A. Kreibich
here MyField isnull is also supported. http://www.sqlite.org/lang_expr.html ("is null" is just a standard "is" with the right side expression being a literal NULL.) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear:

Re: [sqlite] openmpi parallel problem

2011-06-23 Thread Jay A. Kreibich
cgi-bin/mailman/listinfo/sqlite-users > >>> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >

Re: [sqlite] SQLITE_CANTOPEN on an open database

2011-06-20 Thread Jay A. Kreibich
ht try changing the journal mode to "truncate." http://www.sqlite.org/pragma.html#pragma_journal_mode -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people

Re: [sqlite] Implementing busy-waiting on SQLite

2011-06-13 Thread Jay A. Kreibich
d release all the locks. Of course, many, many applications out there don't deal with this correctly, so you would need to be careful with a general-access database. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that

Re: [sqlite] Implementing busy-waiting on SQLite

2011-06-13 Thread Jay A. Kreibich
f/busy_handler.html <= deadlock info http://sqlite.org/c3ref/busy_timeout.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable.&q

Re: [sqlite] ATTACH DATABASE .tables .schema ?

2011-06-13 Thread Jay A. Kreibich
> > .tables T > > Produce no results. You can't. The CLI dot-commands only show results for the "main" and "temp" databases. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important th

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

2011-06-07 Thread Jay A. Kreibich
al table that does something > pretty much like the above. We've recently started using it some for > our test cases. Ooo... that's even more nifty and simple. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you

Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?

2011-06-07 Thread Jay A. Kreibich
tes in the header file. src/test_intarray.h http://www.sqlite.org/cgi/src/artifact/489edb9068bb926583445cb02589344961054207 src/test_intarray.c http://www.sqlite.org/cgi/src/artifact/d879bbf8e4ce085ab966d1f3c896a7c8b4f5fc99 -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "I

Re: [sqlite] Dynamically loaded sqlite (linux)

2011-06-07 Thread Jay A. Kreibich
ary requires no code changes. The dlopen() and related functions are for application controlled linking. They're like the LoadLibrary() functions under Windows. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-07 Thread Jay A. Kreibich
t; range. ** In my own experiences working at NCSA, two national labs, and several university HPC projects, most people doing "scientific computing" don't have a damn clue. Most of the "scientific computing" code I saw was written by domain grad students or

Re: [sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension()

2011-06-07 Thread Jay A. Kreibich
-- Finally, you can call this in your application: sqlite3_auto_extension( (void(*)(void))extension_functions_init ); With that done, any database you open with that application should have the extens

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
t; retrieving non-numeric IEEE 754 values > > No, it doesn't support storing and retrieving NaNs. You're right... Even using the C API, if you try to call sqlite3_bind_double( stmt, p, 0.0 / 0.0 ), the NaN is converted into a NULL before it is written to the database. Inf works

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
doesn't deal with floating point numbers for your definition of "as it should." -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them f

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-06 Thread Jay A. Kreibich
in a data storage system. Write a C or assembly program and have all the close, fine-grain detail you want. As you've pointed out, SQLite is more than capable of storing and retrieving non-numeric IEEE 754 values, so it is doing its core job just fine. -j -- Jay A. Kreibich &

Re: [sqlite] Howto pivot in SQLite

2011-06-05 Thread Jay A. Kreibich
es together into a single, less clear value, just for the sake of making one query, rather than two? Or even one query, but with an extra line or two of code in the parse function? Why not just deal with values in their native, and more correct, "list of images" format? -j

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

2011-06-05 Thread Jay A. Kreibich
uot;very large but not exactly known" number, minus one of the same, might result in anything from +Inf to -Inf. Hence, the "unknown" NULL. I'd guess most IEEE 754 results of NaN will produce a NULL in SQL, since both can mean "unknown" or "missing/illog

Re: [sqlite] Bug in sqlite3_bind_parameter_name

2011-06-02 Thread Jay A. Kreibich
ed-- or the whole statement should be considered invalid and an error thrown. While the phantom parameter issue might be worth addressing, in this specific case I think it is fair to call the query incorrect. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] Need to be able to rename attributes

2011-05-16 Thread Jay A. Kreibich
,' || " right before. Even if the encoding issue could be solved, inserting a column def anywhere else requires context-aware parsing and understanding of the CREATE TABLE statement, so it can be understood where the modifications have to be made. -j -- Jay A. Kreibich < J

Re: [sqlite] Need to be able to rename attributes

2011-05-15 Thread Jay A. Kreibich
ow up. You have been warned. Be very, very sure the sub-string only appears once in the CREATE TABLE statement, or you'll be very, very sorry. You have been warned. You have been warned. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

2011-05-10 Thread Jay A. Kreibich
at 1 by default, limiting them to half the number domain-- hence 63. > will the sqlite database assign "un-used > primary keys" (previously deleted) to any NEW inserts? No, not with an AUTOINCREMENT: http://www.sqlite.org/autoinc.html

Re: [sqlite] Compile error's on Visual Studio 2010

2011-05-08 Thread Jay A. Kreibich
ts out-- the languages are different enough that this behavior seems questionable. If you are sure then project is configured to use the correct C-only compiler, then the warnings being thrown are bogus, and I'd suggest you turn them off. Phantom warnings are not useful.

Re: [sqlite] Determining how many columns were returned in a query

2011-05-08 Thread Jay A. Kreibich
On Sun, May 08, 2011 at 11:00:29AM -0400, Sam Carleton scratched on the wall: > How does one go about finding out how many rows a query returns? sqlite3_column_count() > Is there a way to find out the id of a particular column? sqlite3_column_name() -j -- Jay A. Kreibich &

Re: [sqlite] Profile API: triggered only when sqlite3_step() returns DONE?

2011-05-05 Thread Jay A. Kreibich
this in "Using SQLite" but, given the number of API calls, it is a huge amount of writing. I can understand why the development team would prefer to spend their time doing other things. Most common-language explanations are also very dependent on idiomatic language usage, which

[sqlite] ADV: "Using SQLite" ebook, 50% off today

2011-05-03 Thread Jay A. Kreibich
PDF, ePub, and Mobi. http://oreilly.com/ http://oreilly.com/catalog/9780596521189/ -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tende

Re: [sqlite] select * on empty virtual table

2011-04-29 Thread Jay A. Kreibich
ump the stack, and start digging. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel unco

Re: [sqlite] select * on empty virtual table

2011-04-29 Thread Jay A. Kreibich
e status of your xOpen() call? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson __

Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?

2011-04-19 Thread Jay A. Kreibich
e as an in-memory database, right up until it runs out of memory. After that, the performance should be a bit better, as SQLite's cache is likely to be more efficient than paging memory to disk. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is lik

Re: [sqlite] SQLITE_MISUSE error code

2011-04-19 Thread Jay A. Kreibich
but can also point to buggy flow control. Are you having issues with a specific call returning MISUSE? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the ten

Re: [sqlite] SELECT help for newbie

2011-04-18 Thread Jay A. Kreibich
CHAPTERTOT_VERSES > 1 1 31 > 1 2 22 > 1 3 99 ETC., ETC. SELECT book, chapter, count(verse) AS total_verses FROM scripture GROUP BY 1, 2; -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > &qu

Re: [sqlite] 'integer'

2011-04-16 Thread Jay A. Kreibich
ny other column is a different story, as others have pointed out, but an INTEGER PRIMARY KEY column is limited to only integer values. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it,

Re: [sqlite] Corruption on many Windows XP hosts

2011-04-13 Thread Jay A. Kreibich
er was changed, other SQLite processes can't touch the file until the first process finishes writing and unlocks the database. At that point, all the pages should be in the file cache. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence

Re: [sqlite] Weird CHECK behavior

2011-04-07 Thread Jay A. Kreibich
ESUCH' to 0.0, which is no help. What about CHECK( value == CAST( value AS real ) ) ? Otherwise, I'm not a Tcl guy, so someone else will need to jump in. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that y

Re: [sqlite] Weird CHECK behavior

2011-04-07 Thread Jay A. Kreibich
/sqlite.org/datatype3.html#comparisons You might want to addCHECK ( typeof( value ) == 'real' ) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the t

Re: [sqlite] Is this normal?

2011-04-02 Thread Jay A. Kreibich
qlite> select 'tr' as type union all select 2; # unsorted result type tr 2 -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel unco

Re: [sqlite] Insert BLOB data from the command line

2011-03-30 Thread Jay A. Kreibich
On Wed, Mar 30, 2011 at 12:51:11PM -0400, Santin, Gloria scratched on the wall: > I need to open a file and store the contents into the BLOB field. > Can I do that using just SQL commands from a command line? No. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > &quo

Re: [sqlite] Insert BLOB data from the command line

2011-03-30 Thread Jay A. Kreibich
a 4-byte BLOB. You would use it like this: INSERT INTO table ( b_col ) VALUES ( X'deadbeef' ); More info: http://sqlite.org/lang_expr.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have

Re: [sqlite] What foolproof strategies for database file non-corruption are there, as regards app and OS crashes (not hardware) when in WAL mode?

2011-03-30 Thread Jay A. Kreibich
WAL, things get a bit more complex. I haven't played with it enough to offer specific advice, but your general idea of running a checkpoint to completion, and then holding the lock while things are copied, should work. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] sqlite3_analyze doesn't run on OSX 10.6.7

2011-03-30 Thread Jay A. Kreibich
#x27;m not sure if that was intentional or not. If you just want to roll with it, you can download a 8.6 installer here: http://www.activestate.com/activetcl/downloads -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that y

Re: [sqlite] Problem with VACUUM feature

2011-03-28 Thread Jay A. Kreibich
alled within Sqlire3RunVacuum(). I'm not on the development team. I don't know the internal code all that well. Somebody else will have to help you out. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but s

Re: [sqlite] primary key on two columns of an associative table

2011-03-28 Thread Jay A. Kreibich
t; > CREATE TABLE Invoice_Item_Favorite( > Invoice_Item_Id INTEGER, > FavoriteId INTEGER, PRIMARY KEY ( Invoice_Item_Id, FavoriteId ), > FOREIGN KEY(Invoice_Item_Id) REFERENCES Invoice_Item(Invoice_Item_Id), > FOREIGN KEY(FavoriteId) REFERENCES Favorite(

Re: [sqlite] Full Table Scan after Analyze

2011-03-25 Thread Jay A. Kreibich
ou want it to do things the slow way? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

2011-03-22 Thread Jay A. Kreibich
same value. I realize that most systems will only evaluates random() once, even in a larger expression, but I've always found it nice that SQLite did not in expressions like the one I gave. It makes it much easier to sample data. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C

Re: [sqlite] strange UB detected

2011-03-13 Thread Jay A. Kreibich
On Sun, Mar 13, 2011 at 06:14:49PM +0200, Eugene N scratched on the wall: > uchar* pblah[1]; > pblah[0] = (uchar*)malloc(10); > pblah[1] = (uchar*)malloc(10); // notice the order > Any ideas why? Because pblah is a *one*-element array. -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] Rowids, bit-arrays, recycling, and performance

2011-03-10 Thread Jay A. Kreibich
e gaps. At some point in the past, didn't SQLite use a bit-vector to keep track of dirty pages? I vaguely remember there being some issue with super-large databases being limited by the size of a bit-vector. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Int

Re: [sqlite] duplicate a prepared statement?

2011-03-10 Thread Jay A. Kreibich
only way to get multiple statements. This isn't a bad technique for making sure you get the exact same statement. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

2011-03-09 Thread Jay A. Kreibich
In some situations a single INSERT OR REPLACE can actually cause multiple existing rows to be deleted before the new row is inserted. So it is always an INSERT, but sometimes the INSERT triggers one or more internal DELETEs first. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

2011-03-09 Thread Jay A. Kreibich
OR REPLACE" is called "INSERT OR **REPLACE**" not "INSERT OR UPDATE." The old row is completely DELETEed before the new row is INSERTed. There is no UPDATE in a INSERT OR REPLACE. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like

Re: [sqlite] Failure during Rollback statement

2011-03-09 Thread Jay A. Kreibich
se the ROLLBACK to return an error. What is the error code returned by the ROLLBACK? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel unco

Re: [sqlite] "Error: disk I/O error" on big databases vacuuming

2011-03-08 Thread Jay A. Kreibich
ed on vacuuming process (may be > created journal, does not it?). Yes. A copy of the database and a journal file. Both may reach the size of the original database. So, if you database is 11GB in size, you may need as much as 22GB of free disk space to complete the vacuum process. -j

Re: [sqlite] Implementing sequence nextval in sqlite

2011-03-08 Thread Jay A. Kreibich
more specific, the sqlite_sequence table can be used to find the lowest sequence number that *may* be assigned. SQLite guarantees sequence numbers are assigned in increasing order, but it does not guarantee they will be assigned in a strict sequential order. In short, sequence number

Re: [sqlite] Bug: rtree2 tests fail due to incorrect format string in rtree.c

2011-03-03 Thread Jay A. Kreibich
argv) { long long int i = 0xFFEFLL; double *p = (double*)&i; charb[512]; snprintf( b, sizeof(b), "%f", *p ); printf( "%d\n%s\n", strlen( b )+1, b ); /* add one for \0 */ } --

Re: [sqlite] (How) can I test for an open transaction?

2011-03-02 Thread Jay A. Kreibich
plicit transaction. Conversely, if sqlite3_get_autocommit() returns 0 (false), you are inside an explicit transaction. If it returns true, you are NOT inside a explicit transaction (but there may still be open statements). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Int

Re: [sqlite] possible bug handling group by and where clauses

2011-03-02 Thread Jay A. Kreibich
rows. If you're using any collations (other than the provided ones) I would also be suspect of that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the te

Re: [sqlite] Bug: rtree2 tests fail due to incorrect format string in rtree.c

2011-03-02 Thread Jay A. Kreibich
rmation, how big must buf_size be to never clip the output? You can assume the default 1.6 precision ("%1.6f"). snprintf( buf, buf_size, "%f", v ); The answer? At least 318 characters. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligen

<    1   2   3   4   5   6   7   8   9   10   >