Re: [sqlite] INTEGER PRIMARY KEY AUTOINCREMENT

On 6/14/2017 5:42 AM, R Smith wrote: On 2017/06/14 7:08 AM, Wout Mertens wrote: Is there a way to specify the starting rowid when using autoincrement? Or should I insert and then remove a row with the id set to one less than the desired id? This is quite easy, but first it is helpful to

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

If you're creating GeoPackages with the F.3 RTREE Spatial Indexes extension, you do not "wrap" a bounding box. You need to define 5 functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and ST_IsEmpty -- that take a geometry blob as input and return (for the first four) a floating point

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

But you could use an INSERT  trigger instead. Also "does contains no sub-queries" ought to "doesn't contain any sub-queries", shouldn't it? Peter On Monday, June 20, 2016 5:55 PM, Peter Aronson <pbaron...@att.net> wrote: According to https://www.sqlite

Re: [sqlite] Unable to create table, default value of column [ID] is not constant

According to https://www.sqlite.org/lang_createtable.html: "An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special

Re: [sqlite] Trouble coding conditional UNIQUE

CREATE UNIQUE INDEX bcuif0e ON (b,c) WHERE e = 0; Assuming you're not using a pre-WHERE clause on Indexes version of SQLite.   Since unique constraints and unique indexes are functionally identical. Peter On Monday, June 13, 2016 12:11 PM, "Drago, William @ CSG - NARDA-MITEQ"

[sqlite] autoincrement

argument. Peter On 4/13/2016 7:58 PM, Igor Korot wrote: > Hi, > > On Wed, Apr 13, 2016 at 1:54 PM, Igor Korot wrote: >> Hi, Peter, >> >> On Wed, Apr 13, 2016 at 1:16 PM, Peter Aronson wrote: >>> There is one limitation to this approach, however. The en

[sqlite] autoincrement

There is one limitation to this approach, however. ?The entry for an autoincrement column in the sqlite_sequence table isn't made until the first row is inserted into the table. ?If you are also using the C interface, you can identify autoincrement columns using?sqlite3_table_column_metadata.

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

If this change also applies to?sqlite3_table_column_metadata()'s behavior with views, then I would consider it a real negative. ?Currently I can get my declaration types using that call, which are hints on how to interpret the values in the databases I create, from simple views which is

[sqlite] A question about sqlite3_get_auxdata()/sqlite3_set_auxdata().

I had written code that I thought should cache the repeated user function arguments used in a SQL statement generally like so: SELECT a.id FROM a,b WHERE MyFunc(a.data,b.data); But to my surprise, even though I was saving my (parsed and processed) arguments using at the end of my function

[sqlite] SQLite crashing

How are you building the DLL and the executable? Are they both including a copy of sqlite3.obj? Or are you using a sqlite3.dll? Peter On 1/25/2016 9:16 AM, Igor Korot wrote: > Hi, Peter, > > On Mon, Jan 25, 2016 at 10:50 AM, Peter Aronson wrote: >> Igor, >> >> You

[sqlite] SQLite crashing

Igor, You can't safely pass a SQLite handle between different SQL DLLs that way if they're both built with their own copy of the amalgamation (or link to things built with different copies). SQLite uses a handful of global variables, but each DLL has its own copy of each of these global

[sqlite] SQLite version 3.8.12 enters testing

Currently if you have a check constraint with a user-defined function that is not defined in the current environment, you can not execute any SQL statements in that database -- you get the same error you get with features not supported in the current release. ?I suspect the same thing would

[sqlite] SQLite version 3.8.12 enters testing

I assume, like with similar enhancements, a database created at SQLite 3.8.12 with an expression index will be unreadable with earlier versions of SQLite? ?As, for instance, a database created at 3.8.11 with an index with a where clauses causes SQLite 3.715.2 to fail when trying execute any SQL

[sqlite] bug in PRAGMA ignore_check_constraints?

nto cc values (1,1); sqlite> pragma ignore_check_constraints = yes; sqlite> insert into cc values (1,null); sqlite> On Thursday, September 10, 2015 6:11 PM, Peter Aronson wrote: That would be my assumption. ?And experimentation seems to back it up (at least for NOT NULL):

[sqlite] bug in PRAGMA ignore_check_constraints?

to cc values (1,null);Error: CHECK constraint failed: ccsqlite> insert into cc values (1,1); sqlite> pragma ignore_check_constraints = yes;sqlite> insert into cc values (1,null);sqlite> Peter On Thursday, September 10, 2015 6:02 PM, Simon Slavin wrote: On 11 Sep 2015, at 1

[sqlite] bug in PRAGMA ignore_check_constraints?

I do not believe NOT NULL is a CHECK constraint, though you could use gender TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way, though possibly with less?efficiency. Peter? On Thursday, September 10, 2015 4:48 PM, Roman Fleysher wrote: Dear SQLiters, I am

[sqlite] order by not working in combination with random()

If you're talking about Database Browser for SQLite (formally named SQLite Database Browser), at least at one time (version 3.5) it executed each query twice, apparently the first time to figure out the return types, and the second time to display the results (this caused me a certain amount of

[sqlite] CSV excel import

Actually there exists an open source tool that convert Excel data into SQLite tables -- the ogr2ogr command line tool of OSGeo's GDAL library (http://www.gdal.org/). ?You do need a version of GDAL built with the SQLite and XLSX and/or XLS drivers. ?Actually, if you don't mind adding SpatiaLite

[sqlite] create temporary virtual table

Most types of virtual table can be created as temporary tables with no trouble by specifying the temp database when you create them. ?IE: CREATE VIRTUAL TABLE temp.myrtree USING RTREE (id,minx,maxx); However, the virtual table method xDestroy does not get called for a virtual table in the temp

[sqlite] Error: no such column: When column exists!

When I look at your queries table in sqlite3 in a windows command prompt, the failing SQL has a non-ASCII character instead of a semicolon. Peter On Wednesday, July 15, 2015 12:53 PM, Jason H wrote: So yes, I was missing 'S's, but that is only because I've been hacking at the tables

[sqlite] AUTOINC vs. UUIDs

Never mind about the 122 vs 124 bit thing. ?I could have swore I read that... On Wednesday, May 20, 2015 4:52 PM, Peter Aronson wrote: Now you're just getting silly. ?What if the application sets all rowids, everywhere to 1? ?The fact is, the chance of collision on a UUID is pretty

[sqlite] AUTOINC vs. UUIDs

Now you're just getting silly. ?What if the application sets all rowids, everywhere to 1? ?The fact is, the chance of collision on a UUID is pretty astronomically low as long as a decent source of entropy is used

[sqlite] Fwd: bulk upload from Excel

I don't know what happened to my links in the previous email. ?Again: http://www.gdal.org/ogr_sql_sqlite.html http://www.gdal.org/drv_sqlite.html ? On Monday, May 11, 2015 12:01 PM, Peter Aronson wrote: It's a little indirect, but starting with GDAL 1.10, if you have a version oft he

[sqlite] Fwd: bulk upload from Excel

It's a little indirect, but starting with GDAL 1.10, if you have a version oft he GDAL dynamic library built with both SQLite support and XLS and/or XLSX support (depending on which you need), you can load the GDAL library as a SQLite extension using the load_extension SQL function, and then

[sqlite] Two Threads Share In-Memory Table

See https://www.sqlite.org/sharedcache.html and in particular, section 6 at the bottom. Peter On 5/10/2015 10:42 AM, Joe Pasquariello wrote: > Hello, > > One thread of our Windows application is an HTTP server reading data > from an SQLite database for display by web clients. Some tables are

[sqlite] Determine type of prepared statement via C Interface?

Well, there's sqlite3_stmt_readonly which appears to do pretty much what you're asking for: https://www.sqlite.org/c3ref/stmt_readonly.html. Or, if you want more detailed control, there's the whole authorizer interface: https://www.sqlite.org/c3ref/set_authorizer.html. Peter On 5/8/2015

[sqlite] Possible bug with locking/retying

If you look here: http://beets.radbox.org/blog/, you can see the blog entry is dated August 24th, 2012. Peter On 5/2/2015 5:18 PM, Simon Slavin wrote: > In searching for something else I came across this: > > > > I don't like the fact that

[sqlite] List duplication

I've seen it too. All of the duplicate messages appear to have been sent to both sqlite-dev at mailinglists.sqlite.org and sqlite-dev at sqlite.org or to both sqlite-usersmailinglists.sqlite.org and sqlite-users at sqlite.org. Peter On Fri,

Re: [sqlite] binding multiple values in a query

You can't do that in standard SQLite -- you can only bind single values. However, if you download the full source package (as opposed to the amalgamation) of SQLite, there are a pair of files under src, test_intarray.c and test_intarray.h, which implement a virtual table that would let you do

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

You could add VFS creation if you ever do a revised edition (along with a virtual table example that actually used xBestIndex and xFilter...). On Tuesday, February 10, 2015 9:58 AM, Jay Kreibich wrote: > > > > >No, it does not. Using SQLite covers Virtual Tables in great

Re: [sqlite] using a hex integer as input to DateTime

If you're accessing SQLite from your own program, adding a hextoint function would be pretty easy. Even if you were using sqlite3, defining your own extension isn't that hard (and is documented on the SQLite website to some extent). That would allow you to do most of the things you've been

[sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

** to avoid deadlock issues involving multiple sqlite3.mutex mutexes. I'm not sure exactly what this means, but it implies that xDisconnect can be called in the middle of a session. Peter On 1/2/2015 3:00 PM, Peter Aronson wrote: If only the xDisconnect method is called on a virtual table

Re: [sqlite] Suggestion for syntax enhancement for virtual tables

If only the xDisconnect method is called on a virtual table create in the temp database at disconnect time, is that the only time xDisconnect will be called? The documentation at sqlite.org doesn't seem to say. Jay Krebich's Using SQLite says xDisconnect is "Called when a database containing

Re: [sqlite] JSON indexing

I've never used it myself, but there is http://www.unqlite.org/, which is an embedded document store database library. I believe it uses JSON as native storage format, and it supports an embedded scripting language to access the contents. The Unqlite forums seem active, so it seems to be

Re: [sqlite] [sqlite-announce] SQLite version 3.8.7

No big deal, but on line 885 of shell.c, did you really mean to test if azArg (of type char**) was greater than 0 rather than not equal to 0? It throws a warning on Solaris 9 with the SUNPro compiler. On Friday, October 17, 2014 10:00 AM, D. Richard Hipp wrote: > > >SQLite

Re: [sqlite] Sqlite RTree nearest neighbour

According to R-Trees: Theory and Applications by Yannis Manolopoulos, Alexandros Nanopoulos, Apostolos N. Papadopoulos and Yannis Theodoridis, there are a number of algorithms for efficiently determining the nearest neighbor(s) using an R-Tree (an internet search on the two terms will pull up

Re: [sqlite] Prevent non-Integers going into int fields.

If you don't mind requiring that the value being inserted be typed integer, you can use: create table t1 (c1 integer check (typeof(c1) = 'integer')); But this means that values that could be converted to integers by the integer affinity of the column, like 4.0 or '1' will be cause a check

[sqlite] Entry Points missing from sqlite3ext.h

Found in 3.8.4.3.  Missing from sqlite3ext.h are sqlite3_auto_extension() and sqlite3_cancel_auto_extension().  Now, you might ask, why would I need these functions in an extension?  Well, it turns out I'm writing a virtual table that accesses someone else's library that also uses SQLite, at

Re: [sqlite] Issue with sqlite3_uri_parameter

Actually, SQLite does use sqlite3_uri_parameter indirectly via sqlite3_uri_boolean, but I haven't been able to blow up sqlite3 by supplying malformed URIs.  I thing sqlite3ParseUri throws bad options away. On Thursday, July 3, 2014 3:20 PM, Peter Aronson <pbaron...@att.net> wrote:

Re: [sqlite] Issue with sqlite3_uri_parameter

I believe the function expects URIs of the general form (before separators are converted to Nuls) of file:filename?param1=value1=v2 coverted into file nul param1 nul value1 ... which means that the argument to this function is always expected to have an odd number of strings.  Your input has an

[sqlite] xCommit is called without a prior xBegin when creating a virtual table.

According to the documentation for the xCommit virtual table method "A call to this method always follows a prior call to xBegin and xSync."  However, this does not seem to be the case when actually creating a virtual table.  The post-create xCommit call is made without any prior xBegin call.  

Re: [sqlite] PRAGMA table_info(second.table)

You should look more closely at the syntax for the Pragma statement. What you need is: PRAGMA test.table_info(tab1); This makes sense when you consider not all pragmas have arguments. Peter On 6/4/2014 11:19 PM, LacaK wrote: Hi, when I attach database using f.e ATTACH DATABASE 'test.db' AS

Re: [sqlite] Understanding Sqlite

What worked for me was, before starting to code with SQLite,  I read the first two books on this page http://www.sqlite.org/books.html cover-to-cover, and spent a lot of time reading various articles on the SQLite website.  I also find it useful to read the SQLite code itself. Peter On

Re: [sqlite] BLOBs and NULLs

SQLite seemed to provide good randomness in my (admittedly informal) tests.   Peter From: jose isaias cabrera <cabr...@wrc.xerox.com> >To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database ><sqlite-users@sqlite.org> >Sent: Tuesday, April 22

Re: [sqlite] BLOBs and NULLs

If you want to use sqlite3_randomness to generate a Version 4 UUID according to RFC4122, the following code will can be used:     unsigned char  uuid_data[16];   /* We'll generate a version 4 UUID as per RFC4122.  Start by generating 128 bits of randomness (we will use 122 of them).

Re: [sqlite] UNIQUE index not working as expected - what am I overlooking?

On 3/27/2014 4:21 AM, Richard Hipp wrote: On Thu, Mar 27, 2014 at 12:49 AM, SongbookDB wrote: Gidday guys I'm working with SQLite in Flash. I have this unique index: CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title ) I have a parametised

Re: [sqlite] Command line shell not flushing stderr when interactive

Microsoft seems to only make the stderr stream unbuffered when writing to a character device: "The stdout and stderr functions are flushed whenever they are full or, if you are writing to a character device, after each library call."  It doesn't seem to consider pipe that emacs is reading from

Re: [sqlite] Transaction involving multiple attached databases

And you can't execute it at all if any of the attached databases are read-only.   Peter >On 11/26/2013 2:24 PM, Joshua Grauman wrote: >> If I have multiple databases attached and then do a: >> BEGIN EXCLUSIVE >> >> I assume all the sqlite3 tables involved get locked? > >All the database files

Re: [sqlite] Intended use case for 'without rowid'?

For Peter & Pepijn - I think the issue is essentially a forward-compatibility problem moreso than a backward-compatibility one. So I think your idea on introducing some version control would be the least painful. Indeed. The lack of rowid itself is not an issue. It's that someone could

Re: [sqlite] Intended use case for 'without rowid'?

On 11/16/2013 10:46 AM, RSmith wrote: Perhaps we should make the allowed DDL subset a part of the spec. That way we make explicit what is allowed and anything outside of that is forbidden. Pepijn Perhaps. It would involve a rather large document though, one which an average user is sure

Re: [sqlite] Intended use case for 'without rowid'?

lav...@bigfraud.org> >To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database ><sqlite-users@sqlite.org> >Sent: Friday, November 15, 2013 3:29 PM >Subject: Re: [sqlite] Intended use case for 'without rowid'? > > >I'm confused.  By 'Sc

Re: [sqlite] Intended use case for 'without rowid'?

houdt wrote: >> Will without rowid introduce a new schema version number? >> >> If so, we’ll be ok since GeoPackage requires schema version 4. >> >> Pepijn >> >> On 15 Nov 2013, at 16:33, Peter Aronson <pbaron...@att.net> wrote: >> &g

Re: [sqlite] Intended use case for 'without rowid'?

One additional thing not listed in this document -- use of a internal rowid alias (OID, ROWID or _ROWID_) will produce a parse error on a query against a WITHOUT ROWID table (unless, of course, it has an actual column with the specified name), which makes sense, of course, but could be an

Re: [sqlite] How to use aux_data effectively?

if it had constant inputs, its output would be treated as a constant input itself when fed into another function. Peter - Original Message - > From: Pepijn Van Eeckhoudt <pep...@vaneeckhoudt.net> > To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Databas

Re: [sqlite] How to use aux_data effectively?

Actually, as it turns out, you can get the result you want by having the GeomFromText function use auxdata to store the geometry blob generated from the WKT string, since it's a constant.  Then all the GeomFromText has to do is to return the Geometry blob when sqlite3_get_auxdata returns

[sqlite] Changed behavior from 3.7.17 to 3.8.0.

I don't know if this is serious, but a SAVEPOINT command fails with an open statement handle used to execute PRAGMA journal_mode = off at SQLite 3.8.0, but appears to succeed at SQLite 3.7.17.  This does not appear to be documented.  Note that a BEGIN TRANSACTION will appear to succeed where

Re: [sqlite] name resolutionn in GROUP BY

clever thing to do in the first place, even if legal.   Peter From: Richard Hipp <d...@sqlite.org> >To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database ><sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 2:05 PM >Subject: Re: [sql

Re: [sqlite] name resolutionn in GROUP BY

If I understand Dominique's post, Oracle works like SQLite 3.7.15 as well.  Things only got confusing when we moved from discussing GROUP BY to discussing ORDER BY for some reason. From: Richard Hipp >To: General Discussion of SQLite Database >Sent:

Re: [sqlite] name resolution in GROUP BY

Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY or HAVING clauses.  So I'm not sure what all tests with ORDER BY are demonstrating, since the original question was about GROUP BY, which is a different thing, since ORDER BY operates strictly on the derived table.  

Re: [sqlite] name resolution in GROUP BY

I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and Darwen, and it states (in a footnote on page 151) that name specified for a scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or HAVING clause as it is a column in the derived table, not the

Re: [sqlite] Trigger Logic!!!

If you write a function in C, and register it with sqlite3_create_function (or one of its variants), you can then have a trigger like so: CREATE TRIGGER Event_test1 AFTER INSERT ON test BEGIN SELECT my_notifier_function(); END; And since it's a C function, you can do pretty much anything

[sqlite] sqlite3_column_count vs sqlite_data_count

It seems like a very subtle difference, but I think sqlite3_column_count will return the number of columns returned by a prepared statement regardless of whether there is data available to get with sqlite3_column_* functions, whereas sqlite3_data_count requires that there be a current result

[sqlite] Problem compiling 3.8.0 (New Query Planner version) on Solaris 9

At least in my setup (Solaris 9 with SUNPro C compiler), the amalgamation downloaded from http://www.sqlite.org/draft/download.html fails to compile with the following errors (line numbers may vary slightly from standard as I am using a slightly modified pragma index_info):  

Re: [sqlite] How to interrupt a long running update without rollback?

;   begin; update iupdate set tag = 'updated'; select * from iupdate; end;     Peter From: jhnlmn <jhn...@yahoo.com> >To: sqlite-users@sqlite.org >Sent: Monday, June 24, 2013 11:02 AM >Subject: Re: [sqlite] How to interrupt a long running update without roll back? > > >

Re: [sqlite] How to interrupt a long running update without roll back?

From: jhnlmn To: sqlite-users@sqlite.org Sent: Friday, June 21, 2013 3:20 PM Subject: Re: [sqlite] How to interrupt a long running update without roll back? Alas, none of the above answered my original question "How to interrupt a long running update without roll back". But,

[sqlite] Are RTREE virtual tables supposed to treat NULL values as 0.0?

So, I was looking at some triggers to update an RTREE virtual table that someone else wrote.  I noticed that the trigger didn't handle NULLs.  I was curious, and decided to see what happened if you tried to insert NULL values into an RTREE.  Actually, I rather expected it to throw an error. 

Re: [sqlite] table with check

The "OR NULL" doesn't work the way you think -- it's going to make the whole expression null, which apparently check constraints treat the same as not false.  What you want there is "OR typeof(handedness)='null'". Peter - Original Message > From: Roman Fleysher

Re: [sqlite] Update statement

At least for SQLite it appears to be.  From the Update doc page (http://www.sqlite.org/lang_update.html): "The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a column name to the

Re: [sqlite] Changed behavior in SQLite 3.7.16 beta

; amalgamation snapshot from http://www.sqlite.org/test/download.html > > The testing checklist (http://www.sqlite.org/checklists/3071600) has been > restarted once again on account of this issue. > > On Thu, Mar 7, 2013 at 7:07 PM, Peter Aronson <pbaron...@att.net> wrote: > > > I

[sqlite] Changed behavior in SQLite 3.7.16 beta

I've encountered the following changed behavior (which I believe is a bug).  The following script works at 3.7.15.2 but fails at 3.6.16 beta: create table qa_data_edit (str_col text,int_col integer,rowidcol integer); insert into qa_data_edit values ('this',1000,1); insert into qa_data_edit

[sqlite] Minor documentation correction for CREATE INDEX

No big deal, but I had to look at this recently, so I though I'd point it out  before I forget about it. First, according to the SQLite documentation for CREATE INDEX:   "If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a

Re: [sqlite] Ongoing SELECT sees INSERTed rows.

- Original Message > From: Richard Hipp <d...@sqlite.org> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Thu, February 21, 2013 11:39:44 AM > Subject: Re: [sqlite] Ongoing SELECT sees INSERTed rows. > > On Thu, Feb 21, 2013 at

[sqlite] Ongoing SELECT sees INSERTed rows.

SQLite 3.7.15.2, Solaris and Windows (and presumably others).  Journal mode is delete, but WAL appears to behave in the same manner. One of my coworkers ran into an infinite loop when accessing SQLite. Essentially an outer select loop was reading records, and an inner select loop was

Re: [sqlite] How to get the RecNo ???

You can add it to the select list as OID, ROWID or _ROWID_ or, if the table has a column defined INTEGER PRIMARY KEY (but not INTEGER PRIMARY KEY DESC) it'll also be this value. See: http://www.sqlite.org/lang_createtable.html#rowid Peter On 2/10/2013 5:23 PM, roystonja...@comcast.net wrote:

[sqlite] Peter Aronson

http://google.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] An undefined function in a check constraint will cause selecting from sqlite_master to fail.

I've thought about this a bit more, and concluded this is probably a bug. If you define your own function, and reference it in a check constraint, and then try to select from sqlite_master when that function is not currently defined, the select fails with Error: malformed database schema.  I've

[sqlite] Why does an undefined function call in a check constraint cause selecting from sqlite_master fail?

I'm a bit puzzled by this behavior.  If you define your own function, and reference it in a check constraint, and then try to select from sqlite_master, the select fails with Error: malformed database schema.  I've noticed this since 3.7.2 and it still happens at 3.7.15.1. Example (hopefully

[sqlite] A minor bug in the SQLite shell program.

I have found a minor bug in the sqlite3 shell program. If you use .read to run a file with exactly two errors in it, it will exit from sqlite3 to the operating system. To demonstrate, I first create a file with two lines each of which causes an (called e2.sql)   select nonexistentfunction();

Re: [sqlite] Alter table constraint question

As pointed out, there are products out there that will add or drop constraints (by doing all the tedious table creation/rename/drop under the covers) for SQLite. The other approach is to do what SpatiaLite does in general -- use triggers instead of check constraints but for the same purpose

Re: [sqlite] Could anyone recommend books for SQLite

There's a page on the SQLite website about SQLite books at http://www.sqlite.org/books.html. I own the first two on the page, and like them both. If I was only to get one SQLite book, it'd be the second one on the list: Using SQLite by JayKreibich. Best, Peter On 7/28/2012 6:44 PM, 黃楨民

Re: [sqlite] auto-incrementing integer in composite primary key

You might want to look at the book Temporal Data and the Relational Model by Date, Darwin and Lorentzos, which goes into the subject in fairly great detail.  There are subtleties.   Best regards,   Peter ___ sqlite-users mailing list

Re: [sqlite] VACUUMing large DBs

Actually, it can't be in a transaction.  To quote: "A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run."  (See http://www.sqlite.org/lang_vacuum.html). Best regards, Peter From: Pete

[sqlite] 3.7.11 Shouldn't the new API calls also be added to sqlite3ext.h?

Just a question about the latest release: shouldn't the new API calls be added to sqlite3ext.h?  For that matter, the new API calls from 3.7.10 haven't been added, either.   Is this one of those things that only gets updated on larger releases? Best regards, Peter

Re: [sqlite] PRAGMA user_version of attached database

"A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be "main" or "temp" for the main and the TEMP databases. If the optional database name is omitted, "main" is assumed. In some pragmas, the database name is

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

t in C Syntax. How can I do it? Peter Aronson-3 wrote: > > You got to be a bit careful there, SQLite isn't case-sensitive about table > names, but sqlite_master will preserve the case from the CREATE TABLE > statement.  Instead of > > > select count(*) from sqlite_master wh

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

You got to be a bit careful there, SQLite isn't case-sensitive about table names, but sqlite_master will preserve the case from the CREATE TABLE statement.  Instead of select count(*) from sqlite_master where type='table' and name='tablename'; You need something like select count(*) from

Re: [sqlite] Building an SQLite Extension: How to Persist Configurations/Variables for a database

  From: Grace Simon Batumbya <grace.batum...@senecacollege.ca> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Cc: Peter Aronson <pbaron...@att.net> Sent: Tue, March 6, 2012 10:57:48 AM Subject: Re: [sqlite] Building an SQLite Extension: How to Persi

Re: [sqlite] Building an SQLite Extension: How to Persist Configurations/Variables for a database

It's just basic SQLite reading and writing logic, like in here: http://www.sqlite.org/cintro.html Best, Peter On 3/6/2012 9:44 AM, Grace Simon Batumbya wrote: On 3/6/2012 11:22, Peter Aronson wrote: The simplest approach would be to create a table to hold this information. I have

Re: [sqlite] Building an SQLite Extension: How to Persist Configurations/Variables for a database

The simplest approach would be to create a table to hold this information. I have an extension that works way -- you have to run an initialization function (SELECT function();) to create metadata tables before creating any tables using the extension. Best, Peter On 3/6/2012 6:35 AM, Grace

Re: [sqlite] Function context

tion context [Peter Aronson] > (2) You can associate data with an argument to a regular user-defined > function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long > as the value of the argument is static.  If you don't normally have a > static argument to your function, you c

Re: [sqlite] Function context

You can't call sqlite3_aggregate_context() in a non-aggregate function. However, there *are* a couple of other things you can do. (1) All forms of sqlite3_create_function() take as 5th argument a void pointer to an arbitrary user data structure. This data structure can be accessed in a

[sqlite] Why does an RTREE leave its statement handles open after access?

r in the debugger, I saw the statements get finalized swhen qlite3_close was called.  Is this the expected behavior?  I could see it as an optimization, although I don't see equivalent logic in the FTS code. Best regards, Peter Aronson ___ sqlite-users

Re: [sqlite] Accessing temporary tables

Each database has a SQLITE_TEMP_MASTER table for temp tables and temp triggers. See http://www.sqlite.org/faq.html#q7 Best regards, Peter From: Pete To: sqlite-users@sqlite.org Sent: Sat, February 4, 2012 5:26:21 PM Subject:

[sqlite] SQLite not processing triggers on ALTER TABLE RENAME when table name case mismatches

This had me scratching my head for a while.  Normally, when you rename a table with triggers on it using ALTER TABLE old_name RENAME TO new_name, it will fix the table association in sqlite_master to indicate the relationship with the new table. However, it turns out, if the table name is

Re: [sqlite] R*Tree insert error ...

The order for RTREEs is min_dimension1,max_dimension1,min_dimension2,max_dimension2... 51.51340259 is definitely greater than -1.86352760 Best regards, Peter From: "g...@novadsp.com" To: sqlite-users@sqlite.org Sent: Wed, January 18, 2012

Re: [sqlite] How to get column datatype

Well, to begin with, SQLite doesn't exactly have column data types like most other DBMS, rather, columns have affinities, which might be looked at as sort of preferences. See http://www.sqlite.org/different.html#typing and http://www.sqlite.org/datatype3.html#affinity However, this command

Re: [sqlite] SSD with TRIM

You know, on some platforms, such as Solaris, /tmp can be configured to use memory instead of disk (called tmpfs on many unix variants). Are you sure your /tmp is actually using disk? It's the default in a lot of setups. Best Peter > -Original Message- > From:

Re: [sqlite] SegV at sqlite3_open() (Was: Re: How to sort within a text field)

irectorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > ________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Peter Aronson [pbaron...@att.net] > Sent: Wed

[sqlite] SegV at sqlite3_open() (Was: Re: How to sort within a text field)

> But it segfaults on me at sqlite3_open() and I can't figure out why...there's >no stack info. That's because you have it in the same file that sqlite3ext.h is included in, and the call to sqlite3_open() is really to sqlite3_api->open(), but since you haven't executed SQLITE_EXTENSION_INIT2

Re: [sqlite] Parameters are not allowed in views

Another possibility might be to create a parameters table, say: CREATE TABLE tabparams (p1,p2,p2,p4,p5); INSERT INTO tabparams VALUES (null,null,null,null,null); And when creating the view, access tabparams.p1, tabparams.p2, etc. instead of variables (with an appropriate join clause). Then,

  1   2   >