Re: [sqlite] Better way to export sqlite3 data than pipe?

On Mon, Aug 23, 2010 at 08:09:51AM -0500, Peng Yu scratched on the wall: > Hi, > > Since I don't find a command that can directly export the data into a > file, I use pipe to export data from sqlite3 to a tsv file. Is there a > better way to do so?A See ".output &quo

Re: [sqlite] partial index?

not. My personal opinion is that this is a unique enough feature, and workarounds exist (even if they aren't exactly pretty), that it does not justify the long-term testing and upkeep costs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like und

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

One of the "how to write a custom SQL function" examples in "Using SQLite" builds a wrapper around sqlite3_limit(). I know people that are not using the C interface are faced with additional challenges when it comes to loading extensions or modifying the core SQLite l

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

ragment and move blocks around with every write (including writes to existing space in existing files) to spread out the write cycles. -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] Why the deadlock?

however... the locks belong to the connection, not the statements, so two statements using the same connection can never deadlock. -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] Is there a table that show all the available function from sqlite3?

as I know, there is no way to extract the current function list. -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

Re: [sqlite] copying only new rows from one table to another

for this case. I don't actually know, 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 uncomfortable." -- Angela Joh

Re: [sqlite] threading error in sqlite 3 in python

On Wed, Aug 04, 2010 at 01:00:02PM -0500, Chris Hare scratched on the wall: > How do I figure out what version of sqlite3 is actually installed > in the python install? (It is python2.6) If it isn't too old: SELECT sqlite_version(); -j -- Jay A. Kreibich < J A Y @ K

Re: [sqlite] list table structure

On Mon, Aug 02, 2010 at 11:21:12AM -0500, Jay A. Kreibich scratched on the wall: > On Mon, Aug 02, 2010 at 11:11:25AM -0500, Chris Hare scratched on the wall: > > I read on the SQLite how to get the list of tables in the SQLite database. > > > > How do I get the table st

Re: [sqlite] list table structure

it in Oracle. http://www.sqlite.org/faq.html#q7 -- 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] crypt() as SQL core function

it when compiling SQLite. I think that's a good compromise. While it is a somewhat standard function in many database systems, it is also an oddball that most people don't use. As such, it is there if you need it, but the majority of people will never notice it isn't th

Re: [sqlite] crypt() as SQL core function

ore. -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] crypt() as SQL core function

pecially with SQLite's not-a-license distribution. Finally, if you just want to expose the the system crypt() function to the SQL environment, that's a 15 minute project. Maybe an hour if you've never written a custom SQL function before. Package it all up into an exten

Re: [sqlite] choosing an installation

e quite a trick, however. I assume you could also strip out the Tcl bindings, build them as a different library (dependent on a generic sqlite3.so build), and just link everything that way. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like under

Re: [sqlite] VACUUM not reclaiming space

it would be expected behavior. -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] how to retrieve the result of an sqlite_exec()

Where exactly were you expecting the result to show up? > It should be faster to name a specific column rather than use '*': Definitely not, for many reasons. It may not even provide the same answer. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intellige

Re: [sqlite] suggestion: bitwise shift right optimization

abases don't even use binary integers to store natural-number 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 the tendency to make them

Re: [sqlite] ATTACH DATABASE with connection pooling

mples in "Using SQLite" wraps PRAGMA database_list in a virtual table so that you can do just this. Of course, it calls PRAGMA database_list interally, so it isn't any faster than doing it yourself. http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ (Next month!

Re: [sqlite] VFS Layer for to split databaseinto several files?

eries. It is a tad bit ugly, but less so than modifying the default VFS. I'd be very concerned about locking under NFSv2 as well. You might look into dot-locking instead. NFSv2 has no locking. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like und

Re: [sqlite] EXTERNAL:Re: VFS Layer for to split database into several files?

it should support >2GB as long as > you have the disk space. -- 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] create one index on multiple columns or create multiple indexes, each of which is on one column?

first (for example, first_name, last_name). As Simon pointed out, the first one can also utilize an index, while the second one cannot. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is imp

Re: [sqlite] working with a many-to-many relationship

#x27;s really your only choice. >     If so, how do I specify that? CREATE TABLE a_b_link ( a_id INTEGER NOT NULL REFERENCES a( _id ), b_id INTEGER NOT NULL REFERENCES b( _id ), PRIMARY KEY ( a_id, b_id ) ); The PK will make an index over a_b_link(a_id,b_id). Chances are

Re: [sqlite] How much a table takes (will this query always work)

This technique defragements and re-packs the pages, not unlike the VACUUM command. It should give you pretty good numbers for the VACUUMed size, but not for the current size. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is importa

Re: [sqlite] Removing duplicate entries from a database

rimary key. -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] SQLITE_CANTOPEN returned from sqlite3_step

might try keeping temp files in memory. See: "PRAGMA temp_store = memory" -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

Re: [sqlite] Sqlite Insert Speed Optimization

ich means there are really good reasons why it isn't there. There is a really big gun right here (3.6.23.1): $ grep -n \"-journal\" sqlite3.c 35406:memcpy(&pPager->zJournal[nPathname], "-journal", 8); You're not finding "db-journal" bec

Re: [sqlite] Case insensitive join available?

=B.a; > select * from A inner join B on A.a=B.a; > select * from A join B on A.a=B.a; Replace "A.a=B.a" with "A.a COLLATE NOCASE = B.a" -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] How is the table getting locked and how to unlock it?

On Mon, Jul 12, 2010 at 12:00:40PM -0400, Sam Carleton scratched on the wall: > On Mon, Jul 12, 2010 at 10:36 AM, Jay A. Kreibich wrote: > > > > > Are you using sqlite3_exec() for all of these? My first guess is that > > you're not finalizing the INSERT statem

Re: [sqlite] How is the table getting locked and how to unlock it?

process has it open. Are you using sqlite3_exec() for all of these? My first guess is that you're not finalizing the INSERT statement (or allowing it to run to completion) before trying to drop the table. You can't drop a table if there are any in-progress statements. -j

Re: [sqlite] EXTERNAL: binding an IN

with, for example, 2, 4, 8, 16, etc., parameters. Find the one that fits what you need, and bind NULLs to the end (or just call sqlite3_clear_bindings()). NULL IN ( NULL ) returns NULL, but ( NULL IN ( NULL ) IS 1 ) will return 0. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C

Re: [sqlite] update trigger to require input

set." You can make the column NOT NULL with no DEFAULT. You can also add a CHECK constraint to make sure the string isn't empty. If you do it with triggers instead, make sure you create both update and insert triggers. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C

Re: [sqlite] restricting values allowed in a column

On Sun, Jul 11, 2010 at 02:15:09PM -0400, Igor Tandetnik scratched on the wall: > Jay A. Kreibich wrote: > > You can't add a CHECK constraint to an existing table. > > You should be able to do it by directly updating sqlite_master table: > > http://old.nabble.com/A

Re: [sqlite] restricting values allowed in a column

gives a GUI application some place to lookup the possible choices, which is useful for building drop-down menus, etc. -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] binding an IN

swer you're looking for. There is no general-purpose way of doing this that can be used for different size lists. You can have different statements with different numbers of parameters, but each statement parameter can represent only one value. -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] restricting values allowed in a column

On Sun, Jul 11, 2010 at 09:12:09AM -0500, Jay A. Kreibich scratched on the wall: > On Sun, Jul 11, 2010 at 01:45:50PM +, c...@comcast.net scratched on the > wall: > > > I would like to restrict the values that are entered into > > the "recommendation" column.

Re: [sqlite] restricting values allowed in a column

straint. > Since that table/column exists today without any restrictions, how > do I alter it to restrict the values entered? You can't. You need to build a new table and copy the data over. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like

Re: [sqlite] Null character problem

> This causes a problem because sprintf stops printing when it > encounters ?null?. Don't do that. Use statement parameters and bind the data directly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is impor

Re: [sqlite] Retrieve Specific record number in one shot.

ill return rows 211 through 420. If you want row 210, you need "LIMIT 1 OFFSET 209". Or "LIMIT 209, 1" -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] WAL questions

On Thu, Jul 08, 2010 at 08:06:23PM +0400, Alexey Pechnikov scratched on the wall: > 2010/7/8 Jay A. Kreibich > > > > It's not helpful for backward compability. How about version downgrade of > > > the Android or some other mobile OS and as result impossibility to o

Re: [sqlite] WAL questions

uot;PRAGMA wal=[on|off]" or "PRAGMA transaction=[wal|journal]" ? > Other suggestions? This has nothing to do with WAL, but it might be nice to expose the logic that does SQL-type => SQLite-affinity mappings (i.e. sqlite3AffinityType()): int sqlite3_get_affinity

Re: [sqlite] Temp views and sqlite_temp_master

a bug. Has this been fixed? Not if you understand how it works. Not fixing what isn't broken. > Letter c) makes me wonder: is there is a way to reference both views? As others have answered, just qualify the identifier with a database name. -j -- Jay A. Kreibich < J A

Re: [sqlite] WAL questions

APIs not working on an older version of the OS. If you want the ability to do this, don't use new features (or turn it off every time you close the database). -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] Getting declared datatype of a column in C

s in it, you can also just keep asking for BLOBs. SQLite will covert them using the rules defined here: http://sqlite.org/c3ref/column_blob.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

Re: [sqlite] Which SQLite API return SQLITE_BUSY?

ious. I think that's the bulk of it, 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 uncomfortable." -- Angela Johns

Re: [sqlite] Books which cover C API

Lite" by Mike Owens. It is a bit older, but the core APIs haven't really changed: http://www.amazon.com/Definitive-Guide-SQLite-Mike-Owens/dp/1590596730/ This one is coming out next month. I like it. http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ -j -- Jay A

Re: [sqlite] How to determine when to VACUUM?

our files are significant in size, the database lifespan is significant, and the database will be subjected to an extremely high amount of flux and row thrashing. -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] Getting declared datatype of a column in C

be in hex. For example, these will both insert the same three bytes (ASCII 'A' = 0x41): X'414243'is a BLOB value. 'ABC' is a text value. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it

Re: [sqlite] Getting declared datatype of a column in C

lt-set column is a direct column reference, and only if SQLite is compiled to handle meta-data. Declared types are fairly unimportant in SQLite. They don't mean much. Most applications never need to deal with them. -j > On 07/07/10 04:41, Jay A. Kreibich wrote: > > On Tue

Re: [sqlite] Numbers as CHARs.

uot; and "d" in these statements? Without looking at the docs? Do you think most programmers do? Do you think they usually get it "right" ? int i, d; sscanf( "0123", "%i", &i ); sscanf( "0123", "%d", &d ); -j -- Jay A.

Re: [sqlite] column types (was Re: Numbers as CHARs)

text notations to determine a column affinity, but the mapping is somewhat indirect as defined in section 2.1 of <http://www.sqlite.org/datatype3.html#affname>. -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] Getting declared datatype of a column in C

ared type of a column you already know about, it use "PRAGMA table_info". http://www.sqlite.org/pragma.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

Re: [sqlite] Import all tables from one db file to another

ttempt to copy the data with an INSERT...SELECT command. This usually only works with simple tables, however, and gets messy with indexes, foreign keys, and other constraints. If you really want to see how to do it, look at the code for VACUUM. -j -- Jay A. Kreibich < J A Y @ K R E

Re: [sqlite] How to determine when to VACUUM?

databases (prefs, configs, and even document files) that just never VACUUM. -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] create table {table-name} as select.... table definition is imperfectly cloned

purest representation of an integer affinity, the only logical answer is 'INT.' -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] Bug with commandline option -csv and -separator

de csv sqlite> select * FROM t; 1,2,3 sqlite> Basically, the "mode" operators may set the separator. It wouldn't really be a *C*SV output if it didn't. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is import

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

its purpose very well producing the expected results. Your concerns about PKs and FKs don't even come into play, as they can't happen-- even within the same implementation. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

s like it has been there, in some form, for some time. I suspect I'm mis-remembering a big discussion from some months ago about why a generated table that uses a numeric expression doesn't have numeric columns, or something like that. -j -- Jay A. Kreibich < J A Y @ K R

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

o do with it. If you read that SQL into any other database, all best are off, and this is the very least of your compatibility concerns. -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] create table {table-name} as select.... table definition is imperfectly cloned

actually surprises me, since I was under the impression CREATE TABLE ... AS SELECT always produced NONE affinities. Is this a semi-recent (last year) change? -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] (python) how to define unchangeable global ID in a table?

does this with a VACUUM, for example. ROWID values can change, but PK values cannot be changed or altered. -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] replacing a table

master table to change the name of target_table2 to > target_table > > According to the FAQ, sqlite_master is read-only. Is there any to pull of > this trick? You can do this with "PRAGMA writeable_schema", but I think the real answer you're looking for is ALTER TAB

Re: [sqlite] german locale: queries calculated float fields deliver wrong dec.sep.

alues into the database, and not a string that might be converted (in the case of the functions) but given raw in the case of just returning a raw column. For example: SELECT X, typeof( X ) FROM MY_POINTS; -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intellige

Re: [sqlite] normalization example(s)

On Sat, Jun 26, 2010 at 03:51:12AM +0100, Simon Slavin scratched on the wall: > So you're posting to a mailing list you don't read ? People replay without seeming to read all the time... -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like un

Re: [sqlite] Use of strftime in a CREATE INDEX sintax.

t)) ON CONFLICT FAIL; > > > > Could it be translated into a corret sintax without add another field > > (fat_year)? > > No. You could, however, write a collation that only pays attention to the year, and then build your index and queries using that. -j -- Jay A. Kreibich

Re: [sqlite] TRIGGER ?

h column: table1.a at SQLiteTrigger.plx > line 35. > > I would think that I have referred to the table properly... Try "OLD.a". http://www.sqlite.org/lang_createtrigger.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: i

Re: [sqlite] when foreign key is PRIMARY KEY AUTOINCREMENT

name TEXT, > PRIMARY KEY(ChildId), FOREIGN KEY(ParentId) REFERENCES > Parent(ParentId));after insert into Parent values('name1'); how to get > parentId and set the parentId in table Child.Thanks, > Zeal. last_insert_rowid() http://www.sqlite.org/lang_corefunc.ht

Re: [sqlite] Why null is not printed?

> But I tried it in sqlite3, only an empty line is printed. Is it the > cases that sqlite3 change the way to show null after the book was > published? Likely. See sqlite3 command ".nullvalue". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intel

Re: [sqlite] How accept sqlite3 commands from stdin

be worth creating a -initcli or something that simply ignored the first line of input. It could also imply -batch. -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] How accept sqlite3 commands from stdin

ou can fix the second problem without modifications to sqlite3. -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] marking transaction boundaries

ion to get the value. Every time a transaction commits, it prepares a fresh ID for the next transaction. You would need per-connection values, but if your application is only using one connection at a time, it is pretty simple. You could even do all that in an external extension. You'd

Re: [sqlite] [OT] Avoiding Out Of Office Auto Reply To Group

there anything I should do on my end to minimize the chances the > > group will receive auto replies? > > > > Thanks, > > Shawn > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > &

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

Uggg On Tue, Jun 22, 2010 at 05:12:38PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Jun 22, 2010 at 04:16:42PM -0400, Eric Smith scratched on the wall: > > Jim Wilcoxson wrote: > > > > > Insert times should be constant for the 2nd case: no primary ke

Re: [sqlite] linear behavior on INSERT (was Re: unexpected large journal file)

ar access times, however, especially with larger files. -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 Joh

Re: [sqlite] When to close a db, take 2...

w-average programmers). It also adds enough complexity that a net positive gain in stability and reliability is somewhat questionable. Invariants are a powerful tool, and they have a strong place in computer science to write verifiable algorithm proofs. But like any powerful tool, they're

Re: [sqlite] When to close a db, take 2...

ywhere, when the object is destroyed it will do a double close. -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] When to close a db?

y guess (and this is a pretty wild one) is the code is forcibly finalizing statements, leaving behind stale pointers. As soon as one of those is dereferenced, things crash. This is even more likely if you're using FTS. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H >

Re: [sqlite] When to close a db?

n't, you don't. Check the return value, but call sqlite3_close(). Always. It will accept a NULL pointer. -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] unexpected large journal file

On Sat, Jun 19, 2010 at 12:58:45PM -0400, Eric Smith scratched on the wall: > Jay A. Kreibich wrote: > > I think the use case will usually be (only) writes followed by (only) > reads. There may be incremental writes later, but they will hopefully > be small compared to the i

Re: [sqlite] unexpected large journal file

On Fri, Jun 18, 2010 at 07:01:25PM -0700, Scott Hess scratched on the wall: > On Fri, Jun 18, 2010 at 5:24 PM, Eric Smith wrote: > > Jay A.  Kreibich wrote: > >> Yes.  Hence the "and this is the important part" comment.  Most of > >> the time when people ar

Re: [sqlite] unexpected large journal file

On Fri, Jun 18, 2010 at 08:24:47PM -0400, Eric Smith scratched on the wall: > Jay A. Kreibich wrote: > > > Yes. Hence the "and this is the important part" comment. Most of > > the time when people are building billion-row files, they're building > > a

Re: [sqlite] unexpected large journal file

On Fri, Jun 18, 2010 at 04:07:53PM -0400, Eric Smith scratched on the wall: > Jay A. Kreibich wrote: > > > > I'd really love to avoid writing a big journal file. And I'd love to > > > avoid doing a billion-row insert in one transaction. > > > >

Re: [sqlite] unexpected large journal file

ata import. It would also help to bump the cache up... if you're on a nice desktop with a few gigs of RAM, bump it up 10x to 100x. There are PRAGMAs to do all this. -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] could be memory leak

is written into memory obtained from sqlite3_malloc() and passed back through the 5th parameter. To avoid memory leaks, the application should invoke sqlite3_free() on error message strings returned through the 5th parameter of of sqlite3_exec() after the error message string is n

Re: [sqlite] SELECT behaviour with INDEX

ly the OR chain to each row. Yeah, it gets complex real fast -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 behaviour with INDEX

only use one index from one table per query (or subquery). There are a small handful of exceptions (such as chained OR conditions), but that's a good place to start. > And in this specific case unless you have a boat load of utc's for > each client_id the utc index isn't go

Re: [sqlite] c compiler during install

log' for more details. > I am not a programmer, but I assume that this a c compiler: > > [r...@selkirk sqlite-3.6.23.1]# which cc1 > /usr/libexec/gcc/i386-redhat-linux/4.1.1/cc1 Assuming that is your C compiler, try this: # CC=cc1 ./configure -j -- Jay A. Kreibich <

Re: [sqlite] Sqlite 3.6.23 string functions do not follow case sensitivity in comparions

result seems incorrect. I'm can't offer a definitive answer, however. > > select * from foo where splitstr( value, '@', 1 ) = 'foo'; > 4|f...@bar As a workaround, try: SELECT * FROM foo WHERE splitstr( value, '@', 1 ) COLLATE NOCASE = &#

Re: [sqlite] create index before or after many inserts?

sequence of pages (assuming no existing free pages). Contiguous, yes, but the pages may not be in any logical order. The internal node pages will get shuffled as the tree is built, meaning you might still have a significant number of seeks. -j -- Jay A. Kreibich < J A Y @ K R E I B

Re: [sqlite] notify all processes of database modification

On Tue, Jun 15, 2010 at 09:47:23PM -0500, Jay A. Kreibich scratched on the wall: > On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the > wall: > > Rich Rattanni wrote: > > > The creator of SQLite actually gave a talk about using an SQLite > > &g

Re: [sqlite] notify all processes of database modification

t bet is likely to be PRAGMA user_version. http://www.sqlite.org/pragma.html#pragma_schema_version -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] Avoiding Database Is Locked Error

enough to realize you MUST wrap the whole process in a manual transaction, and you still need to know how to deal with all the locking and busy issues that come with that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligenc

Re: [sqlite] Parametrized Queries issue (Possible bug)

you do it anyways you will likely be confused. -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] Parametrized Queries issue (Possible bug)

one name, so it is over-written with the last-used representation. To avoid such problems, it is strongly advised you do not mix parameter types. Either use bare parameters, explicit indexes, or names-- but pick one and stick with it. -j -- Jay A. Kreibich < J A Y @ K

Re: [sqlite] Parametrized Queries issue (Possible bug)

e same value will be used in both locations in the query. -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] Avoiding Database Is Locked Error

into some gray areas on autocommit transactions that I don't know all that well. I do know is that intermixing modifications while walking through a SELECT has always worked exactly the way I expected. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intellig

Re: [sqlite] Avoiding Database Is Locked Error

tical. But your general approach of looping over a select and doing other things is completely valid. You just need to be ready to handle a busy condition at the first modification. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is

Re: [sqlite] [bug] documentation error SELECT statement GROUP BY clause

N t2 ON (ex)" is allowed by the diagrams, yet that makes no sense. There are several other examples. -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

Re: [sqlite] Avoiding Database Is Locked Error

ailing list. I can no longer provide a reference, however. The book has entered final production and the online version has been removed from O'Reilly's feedback site. http://oreilly.com/catalog/9780596521196/ http://www.amazon.com/Using-SQLite-Jay-Kreibich/d

Re: [sqlite] SQLite lets selecting a column that is not in the Group by clause

group. Of course, the definition of "last" is somewhat undefined. This is actually true of grouped columns as well. If you GROUP BY using a non-unique collation (such as NOCASE), you'll see the same behavior-- the returned value will simply be the value from

Re: [sqlite] CREATE TABLE work arounds?

On Thu, Jun 10, 2010 at 12:12:54PM -0500, Jay A. Kreibich scratched on the wall: > On Thu, Jun 10, 2010 at 07:02:02PM +0200, Jean-Christophe Deschamps scratched > on the wall: > > > >I am parsing fields on the fly and then creating tables, > > > Can you wrap ever

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