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] (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] 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] 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

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

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

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

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

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

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

[sqlite] "Using SQLite" - O'Reilly Deal of the Day

mazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ B&N ($44.99): http://search.barnesandnoble.com/Using-SQLite/Jay-A-Kreibich/e/9780596521189/ Borders ($49.99): http://www.borders.com/online/store/TitleDetail?sku=0596521189 Both Amazon "Look Inside" and Google Books have p

Re: [sqlite] database writes by multiple processes (on a very old computer)

is working 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 showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___

Re: [sqlite] Length=10

t; in the sqlite3 shell, set output mode to file,... ...via the ".output " command... > ...and then select as > above. All your select output will go to your file. You can also modify the format of the output using the .mode comman

Re: [sqlite] Parameters in views preparation

s get saved in the file, > >> they're not part of the attachment. > > > > Yes it does > > So you are trying to create a VIEW which does already exist. In that > case, there's no mystery about why you're getting an error message. No, he's tryin

Re: [sqlite] Parameters in views preparation

On Thu, Aug 26, 2010 at 11:59:03AM -0400, Igor Tandetnik scratched on the wall: > Jay A. Kreibich wrote: > >> So you are trying to create a VIEW which does already exist. In that > >> case, there's no mystery about why you're getting an error message. > >

Re: [sqlite] Is there a design doc for the virtual machine re-write?

d is a very good fit for the needs of SQLite. I'm less convinced it could be expanded to deal with more generic programming abstractions, such as recursive function calls-- but I don't have an extensive amount of VM experience, so I wouldn't put much into that opinion. -j --

Re: [sqlite] Feature request: copying vacuum

pplication shouldn't need to be spending a lot of time worrying about this level of filesystem performance. No matter what, you're just hinting and setting up conditions that should allow the filesystem driver to do something smart and fast. It may, or it may not, actually do so

Re: [sqlite] Feature request: copying vacuum

On Thu, Sep 02, 2010 at 05:42:17AM +0200, Ben Danper scratched on the wall: > > On Wed, Sep 1, 2010 at 12:46 PM, Jay A. Kreibich wrote: > > There is no reason to assume the filesystem > > will over-write the existing allocations, rather than just create new > > ones, esp

Re: [sqlite] Detecting storage class from C

e-domain checking. Simply limiting a value to an integer (or whatever) is no more or less "safe" unless you further limit things to a task-specific sub-domain of that type. I also have to say that handling NULLs as a value-less type is a very clean and handy model. -j

Re: [sqlite] Backing up SQLite file

the command: BEGIN EXCLUSIVE If that works, you know nobody else can touch the database. You're then free to copy it. Once the copy is done, you can rollback the transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is i

Re: [sqlite] Backing up SQLite file

nsql("sync database somehow?"); //do I need this? No. > runsql("BEGIN EXCLUSIVE"); > copydatabasefile(); > runsql("ROLLBACK"); Assuming runsql() will re-run a statement until it works (which is normally a bad practice), then that's the general idea

Re: [sqlite] New to SQLite and I have a question

s spends a little time on non-C APIs, VB is not one of the languages that is covered. Known for having a sub-standard index. > Using SQLite by Jay A. Kreibich Very new. Also covers SQL, as well as database design theory, but I must admit that it only covers the C APIs. Very larg

Re: [sqlite] WHERE Clause Not Working On Database

ot; That will put quotes around the values. Look for leading/trailing spaces. -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] WHERE Clause Not Working On Database

gt; 'ID' > 'NV' > 'OR' > 'UT' > 'WA' If that's a copy-paste from a default sqlite3 session, the single quotes are part of the value. i.e. you have four-character values, such as "'ID'". -j -- Jay A.

Re: [sqlite] Automatic index detail

On Wed, Sep 15, 2010 at 06:00:40PM -0700, Cory Nelson scratched on the wall: > SQLite doesn't support automatic indexing, It does as of 3.7. -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] PATCH: sqlite3_table_column_metadata does not return the default column value.

On Fri, Sep 17, 2010 at 06:32:13PM +, Cesar Crusius scratched on the wall: > In one of my applications I have the need to know what is the > declared default value for any given column. The default expression is also available from PRAGMA table_info(). -j -- Jay A. Kreibich &

Re: [sqlite] VACUUM can actually increase the DB file?

variable-length value) it is conceivable that a VACUUM will increase the overall file size. This possibility will be stronger if the rows were initially inserted more or less in index order, but using explicit, non-sequential ROWID/INTEGER PRIMARY KEYs. -j -- Jay A. Kreibich < J A Y @ K R

Re: [sqlite] Performance problems and large memory size

database connection has its own cache. If you need to hard-limit SQLite's total memory footprint, you use sqlite3_config() and several other functions to provide a static memory pool, or provide your own memory allocator. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C

Re: [sqlite] Performance problems and large memory size

) and several other functions to provide a static >>memory pool, or provide your own memory allocator. >> >> -j >> >> > > > -- > Selea s.r.l. > > >Michele Pradella R&D > > >SELEA s.r.l. > > Via Aldo Moro 69 > I

Re: [sqlite] Performance problems and large memory size

1024, and for the maximum default page size to be set to 8192. The default xSectorSize method on workstation implementations always reports a sector size of 512 bytes. Hence, the default page size chosen by SQLite is usually 1024 bytes." -- Jay A. Kreibich < J A Y

Re: [sqlite] Getting the next row

> I can't necessarilly guarantee that id + 1 exists, as it may be deleted. > > > > Try WHERE id>previousid ORDER BY id LIMIT 1. > > Note that this is O(log N), not O(1). There is no O(1) method to do > this (or pretty much anything else in SQLite), to my knowledge.

Re: [sqlite] how to know the primary key of a table?

a PK might consist of more than one column. Yes, I know, the docs <http://sqlite.org/pragma.html#pragma_table_info> fail to mention the PK 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, bu

Re: [sqlite] SQLite working with C++'s iostream

lementation. -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] When do I need SQLITE_THREADSAFE?

e can be used in a multithreaded program so long as no two threads attempt to use the same database connection at the same time. -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] errors after sqlite3_step

t that-- or I want an error. If I had wanted it READONLY, I would have asked for read-only. -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] When do I need SQLITE_THREADSAFE?

On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the wall: > > On Oct 4, 2010, at 14:46, Jay A. Kreibich wrote: > > > If you're treating the threads independently, each with their own > > database connections, you should be safe with =2 (

Re: [sqlite] Slow operation

e O(N), but not nearly as bad as O(NN). > Also, PRIMARY KEY UNIQUE is redundant. A primary key is always > unique. True, but it does no harm. SQLite is smart enough to only create one index. The NOT NULL would be redundant in most database systems as wel

Re: [sqlite] When do I need SQLITE_THREADSAFE?

On Tue, Oct 05, 2010 at 12:44:59PM +0200, Zaher Dirkey scratched on the wall: > On Tue, Oct 5, 2010 at 4:55 AM, Jay A. Kreibich wrote: > > > On Mon, Oct 04, 2010 at 07:25:05PM -0700, Dustin Sallings scratched on the > > wall: > > > > > > The main difference

Re: [sqlite] No error on UPDATE setting duplicated value on UNIQUE column.

olation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is

Re: [sqlite] When do I need SQLITE_THREADSAFE?

is that the performance difference between "multithread" (=2) and "serial" (=1) is very minimal on most systems, especially next to any physical I/O costs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is

Re: [sqlite] Slow operation

es, which can get clumsy and complex in a text field containing a CSV list, or something similar. A traditional design will also let you prototype and test using the database in a full SQL style, and then optimize the common-case queries, perhaps pulling some of the processing or busines

Re: [sqlite] Speed up DELETE of a lot of records

gt; is an index on it. When deleting 20 to 25% of the rows, an index is likely to slow things 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, but showing it to the wrong people has the tendency to make them

Re: [sqlite] Speed up DELETE of a lot of records

torage is cheap. Fast storage is not. Don't confuse the two. -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.&qu

Re: [sqlite] EXTERNAL:Re: Speed up DELETE of a lot of records

readed programming, it is best to use transactions to protect "whole operations" in database programming. In this case, that includes the initial SELECT. -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] Speed up DELETE of a lot of records

you could allow the database to expand as needed (that is, get the oldest date, and if it is not outside your window, INSERT rather than UPDATE). There are lots of ways to do this, the specifics depend on your needs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intel

Re: [sqlite] What happens when PK reaches max integer

he table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. > This maybe a common problem you all may have ran into. No, not normally. 64-bits is a *really* big domain.

Re: [sqlite] Incrementing a counter vs adding a row

nsiderations is if you have a huge number of rows, and if the database is write-mostly, or a more even balance. In most cases that's not a huge concern, however, other than reading a single value from a single row is usually much faster than a full table scan. -j -- Jay A. Kreibich &

Re: [sqlite] Incrementing a counter vs adding a row

ithout the transaction, it is possible for the state of the database to change between the SELECT and the INSERT (e.g. some other connection might make the same insertion).. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important tha

Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

values, but getting SQL to do something dumb isn't exactly hard. At the end of the day, I know my database much better than the RDBMS does, and the "we need to protect you from yourself" error is not really appreciated. -j -- Jay A. Kreibich < J A Y @ K R E I B I.

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