Re: [sqlite] EXPLAIN QUERY PLAN

t do you need it to be? If it's still not fast enough, consider > denormalizing selectively, and what the overhead will be in maintaining > redundant data. "Normalize 'til it hurts. Denormalize 'til it works." (and in that order!) -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] Implementing sequence nextval in sqlite

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 numbers c

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

acuuming 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 -- Jay A

Re: [sqlite] Failure during Rollback statement

cause 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] Creation and modification timestamps with "insert or replace" possible?

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] Creation and modification timestamps with "insert or replace" possible?

fore the new row is INSERTed. 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

Re: [sqlite] duplicate a prepared statement?

ultiple 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 people has the tendency to

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

And even > PostgreSQL compresses the 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

Re: [sqlite] strange UB detected

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] Best approach for xBestIndex/xFilter effectiveness

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.

Re: [sqlite] Full Table Scan after Analyze

ngs 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] primary key on two columns of an associative table

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 Favorit

Re: [sqlite] Problem with VACUUM feature

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 showing it

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

8.6 libs. I'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 you ha

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

en'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 > "Intelligence is like underwear: it is important th

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

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 it, but showing it to the

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

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 > &

Re: [sqlite] Is this normal?

t '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 uncomfortable." -- Angel

Re: [sqlite] Weird CHECK behavior

l#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 tendency to make them feel uncomfor

Re: [sqlite] Weird CHECK behavior

NESUCH' 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 you have it, bu

Re: [sqlite] Corruption on many Windows XP hosts

r 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 is like underwear: i

Re: [sqlite] 'integer'

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, but showing it to the wrong people has

Re: [sqlite] SELECT help for newbie

PTERTOT_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 >

Re: [sqlite] SQLITE_MISUSE error code

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] What happens if you insert more than your RAM size into an in memory database?

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 like unde

Re: [sqlite] select * on empty virtual table

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] select * on empty virtual table

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 uncomfort

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

d 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 tendency to make the

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

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] Compile error's on Visual Studio 2010

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. -j -- Jay

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

ossible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error." -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] Need to be able to rename attributes

-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 > "Intelligence is like underwear: it is important that you ha

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

ght 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 A Y @

Re: [sqlite] Bug in sqlite3_bind_parameter_name

/or corrected-- 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 > "In

Re: [sqlite] Howto pivot in SQLite

r 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 -- Jay

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

m. 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 < J A Y @ K R E I B I.C

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

sn'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

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

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 just fine, however. In other words, SQLite is actually following a somewhat more consistent mapping than I had realized. -j -- Jay A. Kreibich < J A Y @ K R E

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

--- sqlite3_auto_extension( (void(*)(void))extension_functions_init ); With that done, any database you open with that application should have the extension-functions loaded and available. Just make sure you bu

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

tional 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 interns that would have a hard time writing Hello World without

Re: [sqlite] Dynamically loaded sqlite (linux)

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 have it, but showin

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

n 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 > &q

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

lementation of a virtual 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

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

bles 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 that you have it, but

Re: [sqlite] Implementing busy-waiting on SQLite

dler.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." -- An

Re: [sqlite] Implementing busy-waiting on SQLite

cks. 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 you have it, but showin

Re: [sqlite] SQLITE_CANTOPEN on an open database

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 has the tendenc

Re: [sqlite] ISNULL in sqlite

where 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 under

Re: [sqlite] Minimal SQLite

ou'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 has the tendency

Re: [sqlite] multidimensional representation

an 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. Kreibich < J A Y @ K

Re: [sqlite] Transaction and SQLITE_CANTOPEN

action. 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 E I B I.C H > "Intell

Re: [sqlite] Performance Improvement

s highly optimized for 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 peop

Re: [sqlite] "override" table?

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 join and co

Re: [sqlite] Strange result using JOIN

; > 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 <

Re: [sqlite] Strange result using JOIN

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 important that yo

Re: [sqlite] DEvelopment Error

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] SELECT query first run is VERY slow

te 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] Editing sqlite3 commands

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] Exclusive locking mode not working as expected?

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,

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

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 the tendency to make them feel uncomfortable." -

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

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 "wrong", just diffe

Re: [sqlite] Field drop work around

art 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 wr

Re: [sqlite] Field drop work around

hat 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 it, but showing it to the

Re: [sqlite] Bulk Insert

. 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] SQL ROWNUM option is failed - Want to Read records in chunks from table

sing 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 > "Inte

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

e start-up process 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 --

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

his: 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 have it, but

Re: [sqlite] (no subject)

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 ha

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

ing 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 important that you have it, but showin

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

ber) 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 that you have it,

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

"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 "settimana". I &g

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

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 violation. >

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

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 showing

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

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 <j...@kreibi.ch> wrote: > > > On Mon, Sep 12, 2011 at 12:29:56PM +0800, ?? scratched on the wall: > > > is there any limit about the

Re: [sqlite] Index on BOOLEAN field

gt; > > > 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 you have it, but s

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

le 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 Johnson _

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

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

tive 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 change.

Re: [sqlite] UPDATE of field deletes record

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 you have it, but sho

Re: [sqlite] Does coalesce terminate early?

turns 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. Kreibi

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

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 -- Jay A. Kreibich < J A Y @ K R E

Re: [sqlite] Indices recreation

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] Sqlite3 transactions without Durability

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 importan

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

ELECT * 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 is not (by default).

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

tors 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 into who Dan is before

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

t can I do to make SQLite run safely on CIFS? > > > > 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 doe

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

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 <j...@kreibi.ch> wrote: > > On Wed, Oct 19, 2011 at 02:13:35PM -0500, Nico Williams scratched on the > > wall: > >> On Wed, Oct 19, 2011

Re: [sqlite] IN condition on virtual tables misbehavior

ess 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 underwear: it is important that you have it, but s

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

eries. 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 like un

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

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 y

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

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] Performance of SELECT: What am I doing wrong?

; 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 people has the te

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

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] cache size and insert-only

down unless performance is not an issue. -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 fee

Re: [sqlite] cache size and insert-only

On Fri, Oct 28, 2011 at 07:36:24AM -0500, Jay A. Kreibich scratched on the wall: > On Fri, Oct 28, 2011 at 01:33:31PM +0200, Gert Corthout scratched on the wall: > > > > hello all, > > > > we have a database that only performs insert statements on a table > > (

Re: [sqlite] Indirect Referencing

es, it would be a minor convenience from time to time, but generally reuse comes from very simple statements that are easy to simply rebuild. -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

Re: [sqlite] ORDER BY disregarded for GROUP BY

MySQL and respect > the ordering when grouping. MySQL would likely throw an error with this query. Most DBs will not not let you have "v" in the results without being in the GROUP BY clause and/or using it as a parameter in an aggregate function. -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] Default column values conflict with not null option

ing a DEFAULT constraint, then you cannot use default values. This is normal for 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 important that you have it, but showing it to the wrong people has the tendency to m

Re: [sqlite] Learning SQL using SQLite

wn. That said, while I would be happy for you to send your hard earned money my way, if all you want is information on the SQL language itself, there are about six thousand web tutorials out there and dozens and dozens of books. For most basic operations, the language is compatible enoug

Re: [sqlite] INDEX Types

the rebuilt index will suffer from inserted values and re-balanced nodes. -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

  1   2   3   4   5   6   7   8   9   10   >