Re: [sqlite] Prohibitive indexing time with large tables

se the format "PRAGMA database.cache_size = value", or you're changing the wrong cache. I'm really surprised you're not seeing something from an increased cache size. It has always made a very noticeable difference in my own manipulations, but I think the larg

Re: [sqlite] Writing and reading a csv using sqlite3

ink CSV is simple, I invite you to have a look at the massive Python module and all the odd special-case bits it has. I suspect the SQLite team has just dismissed the whole issue out of hand, and personally I agree with that. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > &

Re: [sqlite] change path of journal file

trying to import data from a database on a DVD to a > local database and i'm getting an error because i can't write on the dvd. If you are "opening" the DVD DB and "attaching" the local DB, try doing it the other way around. -j -- Jay A. Kreibich <

Re: [sqlite] Prohibitive indexing time with large tables

cool and hugely useful, however. I'm surprised they aren't used more. If nothing else, they make great data importers. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We

Re: [sqlite] String Vs Integer Index

pends on what you're searching on, what you're searching for, what other columns you want, how the index is built, and about seven other things. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have

Re: [sqlite] Multi-Table constraint

( yz_yid, yz_xid) values (1, 1). > > Is there any alternative you can suggest? Thank you in advance. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Ja

Re: [sqlite] any command to find last rowid in a table

ck the value, possibly with AUTOINCREMENT. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a

Re: [sqlite] how to get the meta type of a column

t; http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -- > > * Mark Hessling, m...@rexx.org http://www.rexx.org/ > * Author of THE, a Free XEDIT/KEDIT editor, Rexx/SQL, Rexx/CURL, etc. > * Maintainer of Regina Rexx interpreter and Rexx/Tk > * Use Rexx? j

Re: [sqlite] how to get the meta type of a column

e what bind function to use. SQLite will then do any conversions required, given the column affinity, and it is generally going to do a better and smarter job than your code can because it has more information about what is going on. > > http://sqlite.org/datatype3.html > &g

Re: [sqlite] how to get the meta type of a column

g it in. SQLite will try its best to convert what you've given it into a format that makes sense for the column, but if it can't, it will store it in whatever format you sent. Which is more or less exactly the position you'd be in if you wanted to do the conversion "by-h

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

y exposed to the fact that physical I/O and disks are very very slow. If you don't need it, turn it all off or use an in-memory database. Just don't come crying when a power glitch or application crash makes your whole database blowup. -j -- Jay A. Kreibich < J A Y @ K R

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

t; with the prepared statement, but extremely slow write to the drive. If you are doing bulk updates, and are in a position to re-run the data in case of an error, wrap batches of 100 or more in a transaction. Just be sure to handle any error case that trips an automatic rollback. -j -- Ja

Re: [sqlite] How can I know whether the fields in the table by sql statement?

le_info You'll have to do it in your own code, but the info is there. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll g

Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite

atch a '-', make ** it the last character in the list. ** ** This routine is usually quick, but can be N**2 in the worst case. ** ** Hints: to match '*' or '?', put them in "[]". Like this: ** ** abc[*]xyzMatches "abc*xyz" only */

Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite

On Mon, Feb 01, 2010 at 01:49:59PM -0800, flakpit scratched on the wall: > > To to get columns that begin with a number range or any number, I would use > in SQL server: > > SELECT * FROM mytable WHERE myfield LIKE '[0-9]%' Use "...WHERE myfield GLOB '[0-9

Re: [sqlite] poor execution time with select queries

e. > rc = sqlite3_exec(db, "PRAGMA default_cache_size = 2000", NULL, 0, &zErr); I think you want "cache_size", not "default_cache_size", although this will still work. 2000 is the default, however. Depending on the size of your DB, the actual size

Re: [sqlite] negative numbers

gt; select * from signs where lon>-121 and lon<-119; > Given your data, you need lon >= -120 and lon < -119 > > You are not comparing with >=. You are using only > That's true, but he's using 121, not 120. -j -- Jay A. Kreibich < J A Y @ K R E

Re: [sqlite] what are the limitations for IN() lists?

red performance, is easy to maintain, and that you can get out the door in a reasonable amount of time. My biggest suggestion is to try a few different approaches and see. Throw a few larger datasets and your problem and see if it does what you need it to do, what you're most comfortable maintai

Re: [sqlite] what are the limitations for IN() lists?

tep #3) in the top IN-list approach become more > expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach? Using an actual temp table, I wouldn't be too concerned about this. Further, doing it this way avoids the need to build any SQL statements with string manipulations-- alwa

Re: [sqlite] how to get the type of a parameter

which can be good or bad, depending on what you're trying to do. Adding a CHECK typeof() constraint is easy enough for those situations where you need a specific type. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our oppo

Re: [sqlite] what are the limitations for IN() lists?

e are always cleaned up when the database connection is closed. And since temp tables and indexes go into the temp database, and not your main active database, there is no long-term maintenance. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien stars

Re: [sqlite] grabbing row contents?

b,query.c_str(),callback, this, &db_err); > > I'm using the above statement,the callback is getting called as expected. The function returns. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have

Re: [sqlite] Variable type and/or number of data in a row?

two tables. Find a good intro to database normalization; that should point you to a good example of this fairly quickly. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll

Re: [sqlite] Incremental i/o - Is anyone using ???sqlite3_blob_write????

ob handle. Look at the source for sqlite3_blob_open(). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of

Re: [sqlite] Incremental i/o - Is anyone using ???sqlite3_blob_write????

f the code makes me believe this is true. As you're aware, there are other reasons a handle can become invalid. I'd turn on tracing so that you have an exact record of all the statements that are executed. When you hit a handle error, start looking through the trace log to see wha

Re: [sqlite] Sort Alphanumeric, Numeric data in a VARCHAR

ally figure that out right here and now, however. I'll let Igor do that. Sort orders: http://www.sqlite.org/datatype3.html#comparisons Translation rules: http://www.sqlite.org/c3ref/column_blob.html (see table) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our o

Re: [sqlite] Q. about core SQLite library

On Wed, Jan 13, 2010 at 04:07:19PM -0800, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Jay A. Kreibich wrote: > > All you really need to do is splice your code between the VFS that > > the SQLite engine sees and the native V

Re: [sqlite] simple question about database lookup

On Tue, Jan 12, 2010 at 04:12:01PM -0500, Qianqian Fang scratched on the wall: > On 1/12/2010 4:08 PM, Jay A. Kreibich wrote: >> SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) > > thank you very much for your quick response. I guess

Re: [sqlite] Q. about core SQLite library

On Wed, Jan 13, 2010 at 11:07:50AM -0800, Mark Spiegel scratched on the wall: > > > Jay A. Kreibich wrote: > > On Tue, Jan 12, 2010 at 02:53:55PM -0500, Ray Gold scratched on the wall: > >> > >> My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Wash

Re: [sqlite] simple question about database lookup

how felt that > this will be very slow. I am wondering if there is a command > can do this lookup more efficiently.\ SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our oppo

Re: [sqlite] Q. about core SQLite library

the file-system layer. It is possible to write your own encryption layer if the extension did not meet your needs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go

Re: [sqlite] graphs and sql

s (river with a strong current?). It also simplifies the SQL quite a bit, which will likely make index utilization better. Of course, storage and maintenance goes up, but such are trade-offs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starsh

Re: [sqlite] graphs and sql

earching vast sets of data, it may also be possible to re-implement many types of graph algorithms as set manipulations on the representation data. That assumes a pretty in-depth understanding of both graph theory and relational theory, however. -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] Is there a way to "predict" autoincrement rowid range

d and done, the value returned by last_insert_rowid() is the one you'd expect. I didn't mean to imply otherwise. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor.&qu

Re: [sqlite] Is there a way to "predict" autoincrement rowid range

On Thu, Jan 07, 2010 at 03:39:42PM +, Simon Slavin scratched on the wall: > > On 7 Jan 2010, at 1:15pm, Jay A. Kreibich wrote: > > > There isn't a correct way of doing this. You need to manually loop > > over the SELECT, do the INSERT, and call sqlite3_last_inser

Re: [sqlite] temp directory?

default > value for the temporary directory? http://sqlite.org/pragma.html#pragma_temp_store_directory It is a PRAGMA, not an env var. Also see "PRAGMA temp_store". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packe

Re: [sqlite] SQlite query performs 10 times slower than MS Access query

how much this data is used, but it is one more thing to try. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I ca

Re: [sqlite] Is there a way to "predict" autoincrement rowid range

MENT guarantees that automatically chosen ROWIDs will be increasing but [does] not [guarantee] that they will be sequential. (BTW, someone needs to review this section... it looks half-edited.) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship

Re: [sqlite] How to compile the source code when I use the "Online Backup API" ?

ite3_backup_finish' > collect2: ld returned 1 exit status" The backup API is fairly new. Whatever SQLite library you're using is too old. You need to upgrade. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed wi

Re: [sqlite] bind for sub-queries

ical thing and exist because of limitations in the SQL language. From the perspective of the database engine, sub-queries are merged into the main query as one big operation. Depending on your situation, you might be able to use views, temporary views, or temporary tables. -j -- Jay

Re: [sqlite] uninstalling sqlite

sqlite3.pc ..prefix../share/man/man1/sqlite3.1 On most systems, the default prefix is "/usr/local", but sometimes it is just "/usr". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We hav

Re: [sqlite] Storing large numeric values

ml (Literal Values) If you're having other problems, I'd suspect the wrapper you're using is messing with the values before they get to the database. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs

Re: [sqlite] SQLitedb problem

Make a copy of the database file. Run the VACUUM command on the copy. If the data is gone from the raw file dump, it has already been deleted from the database structure. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bo

Re: [sqlite] long time to sort list of random integers

mething like the UNIX sort command is just going gobble up as much as it needs until it hits VM and just keeps going. Unless you make the SQLite cache several gig, you're not really getting an apples-to-apples comparison. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > &quo

Re: [sqlite] is this a bug?

a scalar expression of only one value (the first row), so you're IN test-set has only one value, and returns only one row in the super-SELECT. If you're using a sub-SELECT, the IN operator must see it directly. > Same with triple parenthesis enclosing: An expression in parent

Re: [sqlite] Using sqlite3: Excess memory consumed ?

nk the overhead described is out of line, especially if none of the user columns are marked INTEGER PRIMARY KEY. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll g

Re: [sqlite] sqlite3_complete always returning 1?

ntation). I was only offering support that this does, at first, appear to be an actual bug, and not a differing interpretation of the general call documentation. > If I am doing something wrong, does anyone have an idea of what it could be? It doesn't sound like it. -j >>

Re: [sqlite] sqlite3_complete always returning 1?

ete() or sqlite3_complete16() functions shall return a numeric 1 if and only if the input string contains one or more non-whitespace tokens and the last non-whitespace token in is a semicolon that is not in the middle of a CREATE TRIGGER statement. -j -- Jay A. Kreibich

Re: [sqlite] pragma database_list

You'll have to walk and parse the output yourself, however. Being a pragma, there is no way to wrap a WHERE clause around it. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor."

Re: [sqlite] BUG: The sqlite3 shell does not call setlocale

On Mon, Dec 14, 2009 at 10:41:04AM +0100, Jean-Denis Muys scratched on the wall: > > On 12/13/09 16:34 , "Jay A. Kreibich" wrote: > > > On Sun, Dec 13, 2009 at 01:47:43PM +0300, Alexey Pechnikov scratched on the > > wall: > > >> On Sunday

Re: [sqlite] BUG: The sqlite3 shell does not call setlocale

> > input.) > > The libsqlite is locale-independent but sqlite3 shell linked with > readline and it's locale-dependent. That's a user choice. It does not link with readline by default. (Well, the UNIX autoconf version does, but the raw source has it off by defaul

Re: [sqlite] Where is CASE documented ?

in C/C++: sqlite> SELECT CASEWHEN NULL THEN 'null' WHEN 0 THEN 'zero' ...> WHEN 1 THEN 'one' WHEN 2 THEN 'two' END; one -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed w

Re: [sqlite] Not matching numbers in where clause

gt; 2009| 9| 29 > 2009| 9| 30 > > So I *KNOW* that there are rows with local_year = 2009. But... No, there are rows with a TEXT value of ' 2009'. Note the space. Your other columns have leading whitespace as well. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H &

Re: [sqlite] The next release of SQLite....

> Another possibility is that they have included only part of SQLite in > their compiled product Or, simply forgotten to enable selected function. Maybe on accident, maybe on purpose. If they're intercepting and canceling PRAGMA commands, they likely did it on purpose. Regard

Re: [sqlite] The next release of SQLite....

ly do not call the C function sqlite3_enable_load_extension( ) either on purpose, or just as an oversight. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I c

Re: [sqlite] The next release of SQLite....

t a C coder. If you want changes to Adobe's implementation, you should probably be talking to Adobe. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home an

[sqlite] Virtual Tables & Transactions

Documentation error and/or bug: http://sqlite.org/vtab.html#xsync "This method is only invoked after call to the xBegin method..." Not true (3.6.20). An xSync/xCommit pair is given after the initial xCreate call. I'm not sure if that is intentional or not.

Re: [sqlite] Possibly a bug in SQLite?

e.org/syntaxdiagrams.html#column-constraint The diagrams are for clear human readability, not to define the accepted language. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I

Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY

without warning. For any particular release, some of these options may cause compile-time or run-time failures, particularly when used in combination with other options. The "Important Note" is in bold and italics. I suggest you re-read the whole intro to section 1.6. -j

Re: [sqlite] WHERE CLAUSE in UNICODE

e_sensitive_like is OFF. I've done nothing to change > turn it on. How can I be sure? SELECT 'A' LIKE 'a'; -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor."

Re: [sqlite] WHERE CLAUSE in UNICODE

ation without having to index it. It provides the "natural" order for an ORDER BY. You can also give ORDER BY an explicit collation to use for that specific query. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic

Re: [sqlite] command line does not accept arrow keys

-o sqlite3 sqlite3.c shell.c -lreadline -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string."

Re: [sqlite] Force cache flush?

ut these > don't appear to be written to the actual database file until the > application quits. If you're using the C API, make sure you call sqlite3_reset() and/or sqlite3_finalize() to close out the statement. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > &

Re: [sqlite] Suggested user-defined-function example

s in the SQLite library, rather than in your application code or in an external library. You can do the same. You could also just write your own custom collation, but that's a whole different story. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is

Re: [sqlite] listing free bytes in sqlite files

size will return the number of bytes per page. You can expect a VACUUM to shrink a database file at least freelist_count * page_size bytes. Maybe a bit more if it can recover some of the intra-page free space. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is

Re: [sqlite] Fine-grainy error report needed

other answers are correct. A quick scan of the code makes me thing the only column capable of returning SQLITE_MISMATCH is a ROWID or INTEGER PRIMARY KEY alias, and there can only be one of those per table, and there can only be one table per INSERT. -j -- Jay A. Kreibich < J A Y

Re: [sqlite] collation bug?

"PtrToClassInstance" must be a pointer to the class instance you want called. If you're registering this inside your C++ class, you can use "this". -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atom

Re: [sqlite] sqlite3 for Mac OSX 10.5

ry/library. No use installing a new one if you keep using the old one -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go hom

Re: [sqlite] Need help constructing a query

FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) AND > (SELECT IPTo FROM IPtoCC JOIN Exclude ON IPtoCC.CC = Exclude.CC) I think you need to add WHERE clauses to the sub-selects so you pick the proper upper and lower bound for that base value. Something like "...WHERE base.ip

Re: [sqlite] sqlite3 for Mac OSX 10.5

ady compiled) for the Mac? > > From here: > >http://www.sqlite.org/download.html The only pre-compiled OS X binary up there is the analyzer. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a pr

Re: [sqlite] Database is locked

rg/c3ref/busy_timeout.html This will keep trying if things still look safe, but will return SQLITE_BUSY right away if SQLite detects a possible deadlock. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a prot

Re: [sqlite] Avoiding "Ambigious column"?

first (and only the first) column is getting merged, or the third query wouldn't have two "b" columns. I'm not sure what is going on here, but it doesn't look right. I'm just not exactly sure how it is wrong. Thinking it might be related to the t-NJ-t iss

Re: [sqlite] Locking bug?

u need to handle this situation yourself. Is process A cleanly exiting when the lock is left behind? What OS and filesystem are you using? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor.

Re: [sqlite] User-defined infix functions

gt; optional parameter to the registering interface, or would it require > too much deep surgery in the parser guts? I suspect it would be far too deep to do at runtime. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with

Re: [sqlite] in-memory database concept

r between > C++ and Java. It requires some efforts. Yes, that's how it is supposed to work. A ":memory:" database can only be accessed by the database handle that created it. They're not designed to be shared. http://www.sqlite.org/inmemorydb.html -j -- Jay A

Re: [sqlite] Table within a table??

el is that a system can *physically* store the data in all kinds of interesting ways because all the transforms are 100% reversible. You don't really need to store the table, as defined, in any physical way as long as you can compute it from the data you've stored. RVAs, as

Re: [sqlite] sqlite3 commandline interface questions

mmand, which I do not recall seeing on the sqlite.org web > site documentation. I wonder what else I'm missing. $ sqlite3 SQLite version 3.6.18 Enter ".help" for instructions <=== You're missing this. sqlite> It isn't "full", but it will tell you

Re: [sqlite] Table within a table??

esent a relation without adding or losing state or informational value is... (surprise!) another relation. On the details of this you're just going to have to trust me. Or, better yet: don't. Pick up a copy of "Database In Depth" yourself. You're likely to look at

Re: [sqlite] Table within a table??

quite thankful that SQLite is so easy to integrate and makes so many ugly data management issues just disappear. I know that's where I am. Anyways... I've gone on long enough. Good luck with your design. Think a bit, ask good questions, and hopefully we can all see a differen

Re: [sqlite] feature proposal - strong but dynamic typing

expressions need to go into the CREATE TABLE definition. They can't be added after-the-fact, like key triggers. There are ways around this, of course, but they're kind of messy. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starsh

Re: [sqlite] Late data typing. Am I missing something?

date and time functions (mixed in with SQL commands) http://sqlite.org/lang_datefunc.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I

Re: [sqlite] Late data typing. Am I missing something?

documentation is poor, which may be partly correct, but I think the real issue here is that dates, times, and timezones are hard. They sound very simple, and we all use them every day, so it feels like it just shouldn't be hard or complex. But it is. And making more and more datatypes

Re: [sqlite] Late data typing. Am I missing something?

but it should have been exposed in > the API. No, no... Something like this, in SQL: CREATE TABLE t ( i INTEGER CHECK (typeof(i) = 'integer') ); This allows "strong" type checking on just the columns you care about. Just be aware that it also prevents NULLs.

Re: [sqlite] Grammar of "X is Y"

On Tue, Oct 27, 2009 at 04:07:37PM -0700, Darren Duncan scratched on the wall: > Jay A. Kreibich wrote: > > On Tue, Oct 27, 2009 at 04:15:57PM +, Tom Sillence scratched on the > > wall: > >> because I really want to write neat queries like: > >> &g

Re: [sqlite] Conditional JOIN

eTable THEN ...> A1.PriceTable = B2.PriceTable AND B1.rowid = B2.rowid ...> ELSE ...> 'STANDARD' = B2.PriceTable ...> END ); I think that will do something closer to what you want. -j, now my brain hurts -- Jay A. Kreibich < J A Y @

Re: [sqlite] Grammar of "X is Y"

On Tue, Oct 27, 2009 at 04:15:57PM +, Tom Sillence scratched on the wall: > because I really want to write neat queries like: > > select col1 is col2 from table Are you sure? You just want a result set of true/false values? -j -- Jay A. Kreibich < J A Y @ K R E I B I

Re: [sqlite] Conditional JOIN

a double-join, but that's one baisc approach. The heart of the issue is that there is no way to test if a row has a match or not within a single JOIN. You must do the LEFT JOIN to see if there was a match or not. Then, based off the information of that JOIN, you can do the JOIN again and

Re: [sqlite] SQLITE_ENABLE_COLUMN_METADATA question ...

naltered, column directly from a specific table. Any kind of expression (including aggregate functions) means the data values in that result did not come directly from a specific table column, and therefore these functions return NULL. If you need a more general way to get the name of any column in

Re: [sqlite] "x NATURAL JOIN x" BUG

On Mon, Oct 19, 2009 at 09:28:35PM +0200, Kristoffer Danielsson scratched on the wall: > > I'll investigate this bug later. I'm quite sure it's no more than a > couple of months old, Just tested in 3.4.0 (June 2007) and it does the exact same thing. -j -- Jay A

Re: [sqlite] "x NATURAL JOIN x" BUG

On Sun, Oct 18, 2009 at 03:54:11PM -0700, Darren Duncan scratched on the wall: > Jay A. Kreibich wrote: > > On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched > > on the wall: > >> Clearly, SQLite executes a cartesian product! > > > >

Re: [sqlite] "x NATURAL JOIN x" BUG

values ( 1, 2 ); sqlite> insert into t values ( 3, 4 ); sqlite> insert into t values ( 5, 6 ); sqlite> select * from t natural join t; 1|2 1|2 1|2 3|4 3|4 3|4 5|6 5|6 5|6 I'm not sure I'd call it correct, but it isn't a product. -j -- Jay A. Kreibich < J A Y @

Re: [sqlite] DIVIDE (was Re: INTERSECT?)

r example, you can do it with a nested pair of NOT EXISTS subqueries. This is the best solution I've found. Read the Celko article. The pilot/airplane example is also covered in SQL For Smarties and a number of his other books. Just be warned that the SQL can get really interesting.

Re: [sqlite] Exception writing to database from multiple processes

citly issuing a ROLLBACK command." In short, you can poke at a SQLITE_BUSY state for a bit, but fairly soon you should give up and back all the way out. If you don't, a deadlock is possible. But that would be considered an application bug, not an SQLite bug. -j -- Jay A

Re: [sqlite] sqlite in-memory database far too slow in my use case

lize this may be a general question, but given that the topic of the thread was in-memory databases, I'd just point out that there is no such thing as "multiple connections" to an in-memory DB. Even within the same process, you cannot open another database handle to an in-memory DB

Re: [sqlite] sqlite in-memory database far too slow in my use case

10% you're seeing). It's averaging about 4.3 seconds in auto-commit mode, and 3.4 seconds in an explicit transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bo

Re: [sqlite] Imprecise entry into rtree

ng to see rounding and clipping issues. Also see this discussion from a few days ago: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg47004.html If you can use integers, there appears to be some undocumented methods to convert RTrees to integer indexes. -j -- Jay A. Krei

Re: [sqlite] PRAGMA journal_mode

l "PRAGMA table_info( )", you'll get a six-column result with one row for each column of the specified table. In all cases, these are just values returned by SQL commands. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship pac

Re: [sqlite] sqlite in-memory database far too slow in my use case

ding a text value. As written, that statement has a single character string literal and no parameters. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll

Re: [sqlite] sqlite in-memory database far too slow in my use case

ging any data. Using transactions speeds up a long series of SELECTs because it eliminates the need to re-acquire a read-only file-lock for each individual SELECT. Since in-memory databases have no file locks, I'm not sure that is relevant to this specific case. -j -- Jay A. Kr

Re: [sqlite] sqlite in-memory database far too slow in my use case

s, rather than exec or get_table. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piec

<    2   3   4   5   6   7   8   9   10   >