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] ALTER COLUMN in sqlite

ent in the sqlite_master table, restart everything, and be done with it. Not exactly proper, but fast and easy. ...and a bit dangerous. Make sure you have a backup first... modifying sqlite_master lets you play a lot of tricks, but it only takes one minor mistake to trash the database.

Re: [sqlite] Dynamically generate SQL statements for SQLite

should be run through bind parameters. NEVER use string manipulations to deal with literal values... nearly all SQL Injection vulnerabilities could be avoided with this simple rule. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it

Re: [sqlite] Table name length in sqlite affects performance. Why?

any other large insert, try wrapping the whole thing in a transaction. SQLite is one of the few databases that allows transactions on DDL. -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] curious: Why no "shared library" or DLL?

e amalgamation encourages this. -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] recommended extension entry point name

init() entry point, you'll quickly get into namespace issues, and it can get somewhat messy. Easier to just use a custom entry point for all your extensions. Best practices for designing extensions, including entry points are covered in some detail in chapter 9 of the book "Using S

Re: [sqlite] Number of Colum!!!

On Tue, Aug 13, 2013 at 07:41:25PM +0530, techi eth scratched on the wall: > Is their any limit on number of Colum in SQLite3 table? Yes. http://www.sqlite.org/limits.html#max_column -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: i

Re: [sqlite] can column name come from a table?

imit 1) ); > > But I don't know if SQLite can do that in SQL; you might have to use > the host language. You have to use a host language. Bind parameters in prepared statements also won't work, because you cannot bind an identifier. -j -- Jay A. Kreibich < J A Y @ K R E I

Re: [sqlite] Passing collation to xBestIndex

ule will run under a modern version of SQLite just fine. http://www.sqlite.org/c3ref/module.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 tend

Re: [sqlite] Memory usage of sqlite3

_ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/li

Re: [sqlite] sqlite3_auto_extension - unloaded DLL issue

gin, and make sure it is built in a way that the SQLite APIs are not exported from the plugin's DLL (otherwise the different plugins will clash). -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] sqlite3_auto_extension - unloaded DLL issue

ithout passing a block of function pointers or some such nonesense, but it isn't the usual way of getting things done. -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 wro

Re: [sqlite] Array Accessing in SQLite3

sing a one-to-many relationship to another table. -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 th

Re: [sqlite] Literature on the information theory behind SQL(lite)?

int of view to a more relaxed "since you're going to work in SQL anyways, you may as well do it right." And *that* I can agree with. -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] SQLite3 extended API usage

le()... http://www.sqlite.org/c3ref/free_table.html Which, as the docs state, is not recommended. -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 mak

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

like every other aspect of the SQL language-- or any programming language-- is misused by clueless people. I don't care. It lets me sell more books. > Sort order isn't necessarily deterministic even if we know the column > order. So the possibility that we may not know it, makes life

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

s* deterministic, so the sort order would also be deterministic. Likely meaningless, but still deterministic. -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 t

Re: [sqlite] Shell: .IMPORT does not seem to obey .BAIL setting

option seems to be manual editing of the text file for each error line, > and try again.) > > Is this operation by design or a bug? By design. The .bail configuration applies to processing SQL batch files, not data imports. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

On Wed, Jun 26, 2013 at 10:53:38AM -0700, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 26/06/13 05:07, Jay A. Kreibich wrote: > > A year or so ago there was some effort to write a plug-in that would > > use the standard Pyt

Re: [sqlite] sqlite3: .import command handles quotation incorrectly

where the cells start and stop until you untangle the quotes. -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] Issue with 2 connections getting out of sync, sample code included

reset() to clear the statement is perfectly acceptable. Your other point still stands, however... as soon as _step() returns SQLITE_DONE, it is best to call _reset() before doing anything else. _finalize() can also be called if you know you're done with the statement. -j -- Jay A.

Re: [sqlite] Problem with getting previous command in shell

upport, one was not. It is not on by default. You can re-compile with "-DHAVE_READLINE -lreadline". -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] Obtain limit-less count of previous select

the full result set. -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 count(*) does not use primary key??

if "X" prohibits NULL entries (such as the rowid column), but not in the general case. This is not SQLite specific... this is standard SQL. -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] Serialize an in-memory database

If such a VFS does not actually exist, it shouldn't be all that hard to write, and might come in useful for this and other reasons. -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 wron

Re: [sqlite] query help

tch on > num1 for 11 and 9 Join the table to itself using an outer join on the condition that num2 == num1. Look for rows where num1 is NULL, indicating no join was found. Only works if num1 is never NULL in the DB. I need to run. Perhaps someone else can provide an example if that's no

Re: [sqlite] Create Table oddity

LT (AppID) ); Error: default value of column [SearchMask] is not constant -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.&quo

Re: [sqlite] How to read log file format into sqlite database?

ing SQLite" is all about virtual tables: http://shop.oreilly.com/product/9780596521196.do The big example in that chapter is about exposing Apache/httpd format log files the database through a virtual table. Example code can be found here: http://examples.oreilly.com/9780596

Re: [sqlite] Feature request: Generate 'INSERT' command

he code and use it. -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] Order of columns in group by statement affects query performance

quickly explodes. On the other hand, SQLite must already have assumptions about index costs (with or without ANALYZE), so at least there's an existing set of weights and assumptions to work from. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like un

Re: [sqlite] Order of columns in group by statement affects query performance

about the ordered result of a GROUP BY is broken. Use the out-of-order index. -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] Programming API vs console

build statements is really bad form and can easily open the code up to SQL injection attacks and other problems. For example, if a player's owner string has a quote in it, this code won't work. Using bound parameters fixes all this. -j -- Jay A. Kreibich < J

Re: [sqlite] Writing in a blob

in goal is to avoid seeks, an SQLite solution would be a Very Bad Idea. Rather, it is common to base the embedded filesystem off an archive format, like tar. Using IFF files was also popular back on the cartridge days. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] Variable-length records

On Fri, Apr 19, 2013 at 08:19:57AM +0200, Hick Gunter scratched on the wall: > IIRC temporary tables are limited to the connection that creates them. Yes. So are in-memory databases. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: i

Re: [sqlite] possible ordering issue since 3.7.15

cases where the application makes faulty assumptions about output order can be identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like

Re: [sqlite] 64bit compatibility warnings

ger. OK, yes... that is nearly everything these days (and likely *everything* that supports an i64 type, even if running in 32-bit mode), but is not actually fixed by the language. Yeah, I don't buy it either. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] Bug or some misunderstanding?

t equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. -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 ma

Re: [sqlite] ANN: user-defined functions

m that viewpoint, it isn't that unusual that it includes both string and math functions. -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] pragma table_info(database.table) not supported

On Wed, Mar 20, 2013 at 07:00:29PM +0100, Stephan Beal scratched on the wall: > On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > > That way I can use WHERE on them. In the past I've used virtual > > tables to wrap the PRAGMAs into somethi

Re: [sqlite] pragma table_info(database.table) not supported

any change I'd like to see, it is that all the PRAGMAs that return tabular data should really be system catalog tables. That way I can use WHERE on them. In the past I've used virtual tables to wrap the PRAGMAs into something that looked and acted more like a real table. -j --

Re: [sqlite] SQLITE_OPEN_MAIN_JOURNAL question

http://www.hwaci.com/sw/sqlite/see.html It is a paid product, but the license terms are extremely liberal. -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] A question about the ancient history of SQLite triggers

ot strictly recursive; the 'when' clause means that trigger 1 > will cause trigger 2 to be called etc. In this case, it is any trigger that invokes any other trigger. Prior to 3.6.18 there was no trigger "stack" and triggers could be only one layer deep. -j -- Jay A. Kr

Re: [sqlite] "SQLite" Pronunciation

ng SQLite" I got into a discussion with my editor about the usage "an SQL statement" vs "a SQL statement." (or "an SQLite database"... it goes on an on). We ended up going with "an SQL..." because it is more correct for the "ess-cue-ell

Re: [sqlite] like query

worked > properly and you should use something like The only issue there is that the default case-insensitive nature of LIKE won't work. Otherwise LIKE should have no problems with matching unicode strings. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligenc

Re: [sqlite] Return Value from sqlite3_exec()

asked: updated every single record that met the specified condition. -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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

ries on behalf of the developer, or just assume the developer knows what they're doing and do the best it can with what it was given. For good or bad, SQL is definitely a "shoot yourself in the foot" language. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] Strange eviction from Linux page cache

mber of changes to the process code. -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] Are Foreign Keys indexed?

base Indexes [...] Indices are not required for child key columns but they are almost always beneficial. -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 h

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

able has two or more UNIQUE indexes, the IGNORE resolution may be triggered by different rows through each index. -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] How to verify referential integrity of SQLite database

g applications won't benefit from a new PRAGMA, but existing apps don't know how to react to any errors that might be found. -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] SQLite :Is there any subtle distinction between UNIQUE and DISTINCT?

uld explain when you can use one term or the other. -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 t

Re: [sqlite] Database size bigger than before deleting records

n rows have been deleted from the database, however. Not to ask the obvious, but are you sure the rows were actually deleted? Was auto-vacuuming on? -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] What is the easiest way of changing the field type?

On Sun, Dec 02, 2012 at 12:52:33PM -0800, Igor Korot scratched on the wall: > Jay, > > On Sun, Dec 2, 2012 at 12:16 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > On Sun, Dec 02, 2012 at 11:58:54AM -0800, Igor Korot scratched on the wall: > >> Hi, ALL, > >>

Re: [sqlite] What is the easiest way of changing the field type?

thod is to edit sqlite_master directly. > I know it is all saved as text No, it isn't. That was true of SQLite 2, but SQLite 3 stores types in their native format. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important t

Re: [sqlite] Memory using: is it normal

systems the default page size is 1K, but it can be 4K on some Windows systems. That makes the 16MB look a shade big, but it might be about right if you're running on a Windows system, or if you've adjusted the default page size and/or cache size. -j -- Jay A. Kreibich < J

Re: [sqlite] Foreign keys needing an index

references a column or set of columns that does not have a UNIQUE constraint, either the FK is broken or the parent table is broken. ...which is not to say a general purpose tool still needs to deal with this, as there are plenty of broken database designs out there. -j -- Jay A. Kreib

Re: [sqlite] Converting in-memory sqlite database to char array

p gets caught in a restart loop, some people choose to make the backup a more atomic operation by having the backup "step" function copy all the pages in one go. In that case it is likely that the majority of pages are written out in-order, but I wouldn't want to bank on that.

Re: [sqlite] Converting in-memory sqlite database to char array

ot assume the backup API writes the file front to back, especially if the database is modified while the backup is taking place. A custom VFS that just "writes" the file to a big chunk of memory makes the most sense. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H &g

Re: [sqlite] creating indexes on empty columns

On Sun, Nov 25, 2012 at 12:41:21PM -0700, Keith Medcalf scratched on the wall: > > On Sunday, 25 November, 2012, 11:58, Jay A. Kreibich wrote: > > > each column is usually undesirable. A given SELECT can usually only > > use one index per query (or sub-query), so

Re: [sqlite] creating indexes on empty columns

l slow down, rather than speed up, a query. Indexes are not magic bullets, and using them properly requires understanding how they work and how they are used. -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] creating indexes on empty columns

o when time permits. > > In SQLite, all columns are in all indexes even if the column contains a > NULL. NULL has a sorting order, and anything that does Rows, Simon, rows... not columns. Watch your terminology or your answers will be more confusing than the questions. -j -- Jay A. Krei

Re: [sqlite] in memory to void *data (and back)

les ? Not simpler, but cleaner... write a VFS plugin that reads/writes to a memory block. Use the backup API to go straight in and out of that, rather then a file. -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] how to select "

t work. SQL string literals use single quotes. -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] VTab & xRename

table, the whole application will simply crash. Your fault. -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] Feature request: Add pragma CONCAT_NULL_YIELDS_NULL

cludes a concat() function. That way you get the function you want and the behavior you want. -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 mak

Re: [sqlite] How much disk space is required to checkpoint?

rm standard, as well as the ISO C-90 standard. I'm sure one could trace its roots back pretty far into the history of C and UNIX. There is a strong history of open-and-unlink being the standard practice for this kind of thing. It is exactly what I would expect SQLite to be doing with it's temp

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

ast, vast majority of SQLITE_BUSY errors, however. -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] Sending SQLite3 .timeout command in Command-Line Mode

nderstand that this will not solve every problem. Even with a timeout, there are situations when you can still get a locking error and your only choice is to rollback and try again. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you h

Re: [sqlite] How much disk space is required to checkpoint?

s, on many Unix systems. Some don't clean /tmp at all. The "create and unlink" pattern is so common, many UNIX systems have a tmpfile() or similar library call to do the whole thing... create a unique file in /tmp, open it, unlink it, and return the file pointer. -j -- Ja

Re: [sqlite] Unofficial poll

o do. Just add a check constraint to your column defs: CREATE TABLE t ( i integer CHECK ( typeof( i ) == 'integer' ), t text CHECK ( typeof( t ) == 'text' ), r float CHECK ( typeof( r ) == 'real' ), b blob CHECK ( typeof( b ) == 'blob' ) );

Re: [sqlite] Unofficial poll

of thing in a traditional database. Did it make my life easier, the code simpler, and the database smaller and more compact? Heck, yes. -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] Virtual tables are driving me insane!

your head around, but don't worry about it too much. Unless you're writing a VT that provides a specialized index, you can usually just ignore it and get the basic VT working with table scans before you worry about making the VT index aware. A lot of the VT modules I write don't us

Re: [sqlite] Count(*) help

"NULL OR 1" is 1 (true) and "NULL AND 0" is 0 (false). Arguments about the semantic details of Relational algebra aside, if you treat NULL as "unknown", most of the database operators and functions make sense. http://en.wikipedia.org/wiki/Three-valued_logic

Re: [sqlite] WAL, synchronous=1 and durability

On Mon, Sep 10, 2012 at 09:50:58PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > > On 11 Sep 2012, at 12:55am, Keith Chew <keith.c...@gmail.com> wrote: > > > > and I know FULL (1) will

Re: [sqlite] WAL, synchronous=1 and durability

escribe this fairly well, but from the sound of it you need FULL for durability. On the other hand, WAL requires fewer write to commit a transaction, so (if I'm reading this correctly) FULL in WAL mode is much faster than FULL in non-WAL mode. -j -- Jay A. Kreibich < J A Y @ K R E I B

Re: [sqlite] WAL, synchronous=1 and durability

on-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. -j -- Jay A. Kreibich < J A Y @ K R E

Re: [sqlite] some questions about sqlite

On Sat, Sep 01, 2012 at 11:56:33PM -0700, J Decker scratched on the wall: > On Sat, Sep 1, 2012 at 8:24 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > > On Sat, Sep 01, 2012 at 07:37:04PM -0700, J Decker scratched on the wall: > >> On Sat, Sep 1, 2012 at 7:32 PM, Simon Slav

Re: [sqlite] some questions about sqlite

rrency is always an issue, and you don't want hundreds of connections banging on the same file, but that's true no matter if the connections come from the same process or not. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important t

Re: [sqlite] Any plans to provide the sqlite3RunParser in the API

;: http://shop.oreilly.com/product/9780596521196.do -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

Re: [sqlite] SQLITE_STATIC and temporary data

inalize() or _clear_bindings(). But yes... the key is that the memory remains valid for the lifetime of the binding, not the fact that is or isn't statically allocated. -j > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org

Re: [sqlite] SQLITE_STATIC and temporary data

lled, but, yes... this use of SQLITE_STATIC is acceptable (and somewhat common). -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 unc

Re: [sqlite] C++ - Finalizing my SQLite interface

really something of an error condition. It means your application lost track of something, and failed to free a dependent resource. Having your object blindly finalize statements is very likely to leave a dangling pointer elsewhere in the application. -j -- Jay A. Kreibich <

Re: [sqlite] select speed

re than table and made join select > > Single table with 20 columns. Unless your data is very unusual. However, insert/updates/deletes are likely to be faster on the smaller tables. Worry about design first, then optimize for speed. "Normalize 'till it hurts, denormalize until

Re: [sqlite] (no subject)

the occasional spam message. -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] Virtual Tables: xSync without xBegin

rds > >Olivier > >___ > >sqlite-users mailing list > >sqlite-users@sqlite.org > >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > ___ > sqlite-

Re: [sqlite] Multiple connections to in-memory database

latest version of SQLite, otherwise it won't work. Also, not to state the obvious, but you can only share a :memory: database across connections that originate from the same process. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is impor

Re: [sqlite] When to call VACUUM - revisited

e concerned about filesystem fragmentation than I would be about SQLite fragmentation. > You could use the shell tool to turn the database file into SQL commands, > and then back into a new database file on disk. This will both > defragment the file, and make sure it's n

Re: [sqlite] Standalone LUA regex that can be used in sqlite

.sqlite.org/contrib/ Also see: sqlite3_auto_extension() http://www.sqlite.org/c3ref/auto_extension.html sqlite3_load_extension() http://www.sqlite.org/c3ref/load_extension.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: i

Re: [sqlite] Bug in sqlite3_step

On Wed, Jul 04, 2012 at 08:29:33AM -0500, Jay A. Kreibich scratched on the wall: > On Wed, Jul 04, 2012 at 01:09:01AM -0500, Nico Williams scratched on the wall: > > > But this would > > just be a glorified (if safer) variant of sqlite3_mprintf() -- for > > apps that a

Re: [sqlite] Bug in sqlite3_step

a %z and %p, but they're not really important for this discussion. -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." -- Ange

Re: [sqlite] Bug in sqlite3_step

name ); sqlite3_prepare_v2( db, sql_str, -1, , NULL ); sqlite3_free( sql_str ); -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 t

Re: [sqlite] Bug in sqlite3_step

de-effect of the fact that CREATE statements are copied into the sqlite_master table as literals, and not re-written? (Is that even true?) -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] Update trigger fires for all rows even if sqlite3_changes returns 0

2<>?2 AND col3<>?3. > (passing a null parameter to the above won't even work!) Well, no, it won't, because you're using the wrong operator. Use "WHERE col1 IS NOT ?1 AND..." and it all works fine. > No surprises there. Oracle has never managed to impress me. I know what yo

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

doesn't change what it does. There is, however, little argument that the trigger is doing exactly what one would expect. You are applying an update operation to every row, and the trigger is firing for every row. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "

Re: [sqlite] Update trigger fires for all rows even if sqlite3_changes returns 0

L rows, you need to update only the non-NULL rows: UPDATE table SET column=NULL WHERE column IS NOT NULL; As for sqlite3_changes() returning 0, that doesn't sound right unless you're checking inside the trigger. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] max size of a TEXT field

n be altered, but there is a hard limit of (2^31 - 1), or 2GB. -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 uncomforta

Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?

use the encoder function for inputs and the decoder for all outputs, you should be good. That starts to get deep into your SQL, however. The ability to define native types is similar in complexity to adding user-defined functions. Just a thought. Any opinions? -j -- Jay A. Kreibic

Re: [sqlite] Error indicated through SQLITE_CONFIG_LOG callback but sqlite3_step() does not fail

re_v2() deals with this condition automatically is generally considered to be a feature. -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] sqlite time is 2 hours to late

On Wed, Jun 27, 2012 at 05:45:41PM +0200, deltagam...@gmx.net scratched on the wall: > Am 27.06.2012 17:40, schrieb Jay A. Kreibich: > >On Wed, Jun 27, 2012 at 05:37:55PM +0200, deltagam...@gmx.net scratched on > >the wall: > > > >>Hello, > >> >

Re: [sqlite] sqlite time is 2 hours to late

ow can this be fixed ? Move two timezones to the west. (By default all times and dates are UTC.) -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

  1   2   3   4   5   6   7   8   9   10   >