Re: [sqlite] ATTACH Limit

The built-in limit in code for attach is 30 (2 reserved for MAIN and TEMP, plus 30 bits more, to fit within a 32-bit integer type), which can be expanded by setting the macro SQLITE_MAX_ATTACH to the higher number when compiling sqlite3.c . The limit is per connection handle. There are, of

Re: [sqlite] SQLite Build in M$ Visual Studio 250+ warnings

I use this framework to build sqlite on VS2005.SP1: // \file import_sqlite.cpp // \brief Import the SQLITE database. // #pragma warning(push, 0) #pragma warning(disable: 4701) #pragma runtime_checks("", off) // enable multi-thread mode. #define THREADSAFE 1 // optimize for performance by using

[sqlite] _ROWID_ internal column, versus explicit INTEGER PRIMARY KEY with VACUUM

I realize that FTS1/2 has this slight flaw with the text indexes recording the _rowid_ of a table, in the expectation that a rowid was permanent. That would have caught me unawares, as in Oracle a ROWID is permanent... even if the row has migrated, there's a migrate record at the place where the

Re: [sqlite] Unusual use of the LIKE operator (long)

> possibilities. > > *Q*: In what ways have others used the //'string' LIKE column-name// form? > > *Q*: Is there a name for this usage in SQL? > > *Andrew*: How does your date table work? > > -R. > > Andrew Finkenstadt wrote: > > We've used this exact tech

Re: [sqlite] Unusual use of the LIKE operator (long)

We've used this exact technique in a table called "ANY_DATE_FORMAT" since 1997... admittedly in Oracle, but the principle still applies. It's great for coalescing the number of states necessary to implement parsers. :) --andy On 8/18/07, Rod Dav4is <[EMAIL PROTECTED]> wrote: > > Conventional

Re: [sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005

On 8/15/07, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote: > > > > See http://www.sqlite.org/cvstrac/tktview?tn=2574 > > > > Apparently VC++ does not like for you to declare a constant > > with file scope before the constant is defined. I do not > > know how to work around this problem. Perhaps

[sqlite] SQLITE_CORRUPT: database disk image is malformed

Is there a good way/tool to determine what went wrong in a database file reporting SQLITE_CORRUPT? The details I have so far are: machine (running Windows XP) locked up hard while executing our application. Repeatable hard-lock at a certain point. (Hard lock is defined as no mouse, no keyboard,

Re: [sqlite] Proposed incompatible change to SQLite

On 8/9/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > We propose to modify this so > that these routines work across all database > connections in the same process. > > Double plus good for us.

Re: [sqlite] SQLite.org needs online forms

I expect that for most of us, we prefer to get our information "pushed" to us rather than having to go check laboriously each of the subject area's forums that we are interested in. Life is too short, otherwise.

[sqlite] Re: 3.4.1: select with no qualifying rows returns a row of all NULLs

...snip... http://www.sqlite.org/cvstrac/tktview?tn=2536 I am working on building a test case. The problem remained when I reverted to 3.3.17 using the identical database files.

[sqlite] Re: 3.4.1: select with no qualifying rows returns a row of all NULLs

On further research, it appears to be related to max_page_count being exceeded during insertion of a row, leaving behind a primary key reference in the (primary key) index on the BLOB instead of being cleaned up during rollback. On 7/24/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:

[sqlite] 3.4.1: select with no qualifying rows returns a row of all NULLs

Using 3.4.0 or 3.4.1 compiled for windows with -DMULTITHREAD, i seem to have a problem where sqlite3_step() will return SQLITE_ROW even when there are, in fact, no rows that meet the WHERE clause criteria. I'm currently attempting a reversion to 3.3.17 to see how it behaved. create table

Re: [sqlite] how can i bound signed integer (negative) to an identifier in select query

Is it possible that the data type of the column in the CREATE TABLE statement is not "INTEGER" but some other string? This web page: describes the data type affinities applied for the various values of the data type in the create table statement, and if the

Re: [sqlite] invalid subselect is not detected ?

On 7/21/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > (In the 5 hour lag it takes to post to the list, this has > > probably already been answered 5 times, but what the heck...) > > > > I'd love for you to work on the slow

Re: [sqlite] invalid subselect is not detected ?

On 7/20/07, Ken <[EMAIL PROTECTED]> wrote: Is this an error or by design? create table ss( ssid, ss_value); create table s(id, s_value); insert into ss values (1,1234); insert into ss values (2,1234); insert into s values (1, 567); insert into s values (2, 567); insert into s values (3, 567);

Re: [sqlite] 64bit on 32bit machine

64bit operations on a 32bit machine using C. How can I do that? or how to declare a 64bit integer on a 32bit machine? Thanks, Lloyd On Thu, 2007-07-12 at 15:06 -0500, Andrew Finkenstadt wrote: > It uses the feature built-in to the language compiler that you use. > > In the case of Visual St

Re: [sqlite] 64bit on 32bit machine

It uses the feature built-in to the language compiler that you use. In the case of Visual Studio (Microsoft, x86) the underlying compiler uses either the library implementations or direct assembly code using pairs of registers and whatever dual-register assembly instructions are available on the

Re: [sqlite] How to store 128 bit values

On 7/11/07, Steve Krulewitz <[EMAIL PROTECTED]> wrote: I was wondering if there is much to be gained by storing these 128 bit values in binary rather than as strings. We estimate roughly 20 properties per track, so in a moderate sized database you'd have 10k rows in the tracks table and 200k

Re: [sqlite] DISTINCT and Indexes

On 7/7/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: I have a query to get all states that a user might have entered, it is : SELECT DISTINCT customers.bill_state as the_result from customers UNION SELECT DISTINCT invoice_master.bill_state FROM invoice_master UNION SELECT DISTINCT

Re: [sqlite] Database File size not exceeding 2GB

According to http://en.wikipedia.org/wiki/File_Allocation_Table , the limit on FAT16 is 2 gigabytes per file, on FAT32 it's 4 gigabytes per file, and on NTFS it's very, very large. In my application I needed to deal with splitting my data into 2 gigabyte (maximum) database file sizes, and I had

Re: [sqlite] Seg Fault when using sqlite3_bind_blob

On 7/1/07, Rich Rattanni <[EMAIL PROTECTED]> wrote: I was trying to look through the SQLITE source code to see how the sqlite3_bind_blob routine worked. sqlite3_bind_blob passes the data pointer to bindText bindText passes the data pointer to sqlite3VdbeMemSetStr sqlite3VdbeMemSetStr then

Re: [sqlite] SQL query assistance...

On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but

Re: [sqlite] Multi-column "in" clause supported?

On 6/29/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 6/29/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > You can do it with a JOIN instead of IN and I'm pretty sure it will > still > use an index. > > > SELECT > COUNT

Re: [sqlite] Multi-column "in" clause supported?

On 6/29/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: You can do it with a JOIN instead of IN and I'm pretty sure it will still use an index. SELECT COUNT(*) FROM guid_version_map M LEFT JOIN latest_version V ON

Re: [sqlite] Re: Multi-column "in" clause supported?

On 6/29/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > I'm attempting to execute this SQL statement ( using SQLiteSpy, if > that matters, which is based on 3.3.16 ): > > select count(*) from guid_version_map > where (g

Re: [sqlite] Re: Multi-column "in" clause supported?

On 6/29/07, Ken <[EMAIL PROTECTED]> wrote: You can also do: select count(*) from guid_version_map where guid||version not in (select guid||version from latest_version) Its not exactly the same, typically only good for small results. Thanks... my results will have millions of rows, :)

[sqlite] Multi-column "in" clause supported?

I'm attempting to execute this SQL statement ( using SQLiteSpy, if that matters, which is based on 3.3.16 ): select count(*) from guid_version_map where (guid,version) not in (select guid, version from latest_version) Basically I want to retrieve the rows in guid_version_map whose primary key

Re: [sqlite] auto_increment and not null

On 6/28/07, Bruno S. Oliveira <[EMAIL PROTECTED]> wrote: Hi all, Now I see the what's happening. But what if I need to create a table with two primary keys and one auto incremented (and I DO need that!)? Isn't this possible? If by primary key you mean alternate (unique) key, then yes.

[sqlite] Doc change: clarifies max_page_count pragma

unt cannot be reduced below the current database size. If there are multiple sqlite3 instances referring to this database file, each can have its own, differing max_page_count setting. I, Andrew Finkenstadt, dedicate this documentation change and its corresponding notes and discussion to the public domain.

Re: [sqlite] Cannot set page_size pragma from file

On 6/27/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 6/27/07, Neil Hughes <[EMAIL PROTECTED]> wrote: > > PRAGMA default_cache_size=25; > PRAGMA page_size=4096; > CREATE TABLE timestamp (date TEXT, time TEXT, script_version REAL); > INSERT INTO timestamp V

Re: [sqlite] Seeing a file handle issue with sqlite.

On 6/27/07, Bill KING <[EMAIL PROTECTED]> wrote: Can someone else confirm this for me? Yes. On unix, I'm seeing processes that have been clone/exec'd inheriting file handles to sqlite databases that were opened in the parent process (and hence subsequently don't/can't get closed in the

Re: [sqlite] Cannot set page_size pragma from file

On 6/27/07, Neil Hughes <[EMAIL PROTECTED]> wrote: PRAGMA default_cache_size=25; PRAGMA page_size=4096; CREATE TABLE timestamp (date TEXT, time TEXT, script_version REAL); INSERT INTO timestamp VALUES('27/06/07', '14:38:18', '0.01'); I've only just learned this today during an "innocent"

Re: [sqlite] Sqlite - LISTEN/NOTIFY

On 6/27/07, Shilpa Sheoran <[EMAIL PROTECTED]> wrote: All, Does sqlite implement LISTEN or NOTIFY commands? Basically if one application modifies the database will sqlite notify other interested applications about the modification? I do not believe that SQLite implements the non-standard

[sqlite] cache_size documentation vs. page_size setting

In the documentation for cache_size, the description for how much memory a page takes up says "Each page uses about 1.5K of memory.". I believe that it is more accurate to say that "Each page uses the database page_size plus about 512 bytes." I don't know how best to phrase it, save as a series

Re: [sqlite] pragma page_count

On 6/26/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: Compile, test, debug ... contribute. :)

[sqlite] pragma page_count

How easy would it be for me to implement a "pragma page_count;" statement which returns the CURRENT page count of the database, and is much more cross-platform than my attempt to just "check the file size". --andy

Re: [sqlite] blob data streaming

On 4/10/07, Teg <[EMAIL PROTECTED]> wrote: Hello Andrew, Tuesday, April 10, 2007, 3:25:29 PM, you wrote: AF> Using sqlite3 (3.3.15 or later), is there a method to retrieve portions of a AF> blob rather than the whole thing? AF> If not, would others find it useful and handy? Store the file

Re: [sqlite] transaction best practices, post-committal headaches

On 6/21/07, James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 21, 2007 2:56 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] transaction best practices, post-committal heada

[sqlite] transaction best practices, post-committal headaches

In my sqlite framework I have the concept of a transaction, which uses a pair of begin transaction (immediate, exclusive, normal) / end transaction or rollback transaction statements that execute based on C++ object construction and stack unwinding destruction. The transaction, if it's marked as

Re: [sqlite] Alternative index methods (hash key)

On 6/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Scott Hess" <[EMAIL PROTECTED]> wrote: > On 6/20/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > > How difficult do you think it would be to support an alternative method of > > indexin

Re: [sqlite] Alternative index methods (hash key)

On 6/20/07, Scott Hess <[EMAIL PROTECTED]> wrote: On 6/20/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > How difficult do you think it would be to support an alternative method of > indexing within SQLite specifically to support O(1) retrieval of the r

[sqlite] Alternative index methods (hash key)

How difficult do you think it would be to support an alternative method of indexing within SQLite specifically to support O(1) retrieval of the rowid for a table, and then potentially O(1) retrieval of the row data for a table, when in-order retrieval is undesired? My database design is highly

Re: [sqlite] Step Query

On 6/20/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Andrew Finkenstadt wrote: > > > I ended up writing a (multi-thread aware) C++ framework to keep me > out of trouble. In the SQLite namespace I have Is there any chance that your framework is freely licensed open source s

Re: [sqlite] Step Query

On 6/19/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: I had lots of problems here when starting with SQLite and painstaking I think I've figured it out. Me too. You have sqlite3_prepare, which compiles the sql into byte code, then Sqlite3_step to execute the query or update, if it is an

Re: [sqlite] Cache invalidation after insert statements.

On 6/19/07, pompomJuice <[EMAIL PROTECTED]> wrote: Running a huge Oracle 10G database Running a 3rd party application that generates HUGE IO. Part of this 3rd party application is my application that does lookups. 1.) Data comes in in the form of files. 2.) 3rd party application decodes and

Re: [sqlite] Version 3.4.0

On 6/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: SQLite version 3.4.0 is now available for download from the SQLite and it's in production use here as of this morning. Version 3.4.0 also includes support for new features such as: * Incremental BLOB I/O * The zeroblob() SQL

Re: [sqlite] SQLITE_CORRUPT recover

- Make sure you're compiling SQLite with *-DTHREADSAFE=1*. - Make sure that each thread opens the database file and keeps its own sqlite structure. - Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle *

Re: [sqlite] Journal File Optimization

On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: Andre du Plessis wrote: > How can one optimize the creation of the journal file. The problem is > this, for our system which is an event based one each message needs to > be insterted and committed to the database (guaranteed), this results in

Re: [sqlite] Multiple *processes* accessing one database

On 6/13/07, Andrew Roark <[EMAIL PROTECTED]> wrote: My question: how well does sqlite handle one database file being accessed by multiple processes? Very well, so far. You'll want to make sure you use an appropriate locking (transaction) strategy, and accomodate SQLITE_BUSY even when you

Re: [sqlite] "Make" with VisualStudio/Windows

On 6/13/07, Jonas Sandman <[EMAIL PROTECTED]> wrote: Hello, Is it possible to get the latest SQLite somehow which doesn't require you to actually run "make"? With the amalgamation (?) you can just compile the whole thing in Visual Studio but it seems that the compiler isn't very successful in

[sqlite] version 3.3.15, is SQLITE_BUSY_RESERVED_LOCK implemented

I see a reference to SQLITE_BUSY_RESERVED_LOCK in version 3.3.15 (the first one with the amalgamation), but there does not appear to be support for it in the remainder of the source file. Is that correct?

[sqlite] Hashed Index proposed

I updated the Incompatible Changes page with an idea for using hashed-key indexes instead of b-tree indexes. exact-match retrievals could reduce the number of disk pages necessary to read to find the rowid from an average of Log(B)(n) (where B is the number of buckets per b-tree) to Log(2^B)(n).

Re: [sqlite] Re: db design mixing different entities in the same table

On 6/1/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: > > On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > > On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: > > > > > > I reali

Re: [sqlite] Re: db design mixing different entities in the same table

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: > > > > I realize that I didn't explain the problem well enough (thus is the > > peril of being too

Re: [sqlite] Re: db design mixing different entities in the same table

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote: I realize that I didn't explain the problem well enough (thus is the peril of being too close to it for a long time). I can have an (org <--> org) relationship or an (org <--> person) relationship as well as a (person <--> person) relationship.

Re: [sqlite] One more SQLite threading question

I have recently encountered the same cross-thread library misuse bug, and ended up re-implement a C++ wrapper to be thread-aware and thread-safe. It's not yet feature complete, compared to CPPSQLite3DB, but it does have several good enhancements: * signed 64-bit integer parameter binding

Re: [sqlite] runtime errors in DEBUG with byte overflow in RC4-based random (3.3.15)

n RC4- > based random (3.3.15) > > "Andrew Finkenstadt" <[EMAIL PROTECTED]> wrote: > > > > Any objections to taking back patches that bit-wise and the result of > the > > overflowing addition with 0xff ? > > > > The variables being added are uns

Re: [sqlite] runtime errors in DEBUG with byte overflow in RC4-based random (3.3.15)

On 5/15/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Andrew Finkenstadt" <[EMAIL PROTECTED]> wrote: > > Any objections to taking back patches that bit-wise and the result of the > overflowing addition with 0xff ? > The variables being added are unsig

[sqlite] runtime errors in DEBUG with byte overflow in RC4-based random (3.3.15)

I am having issues with certain portions of sqlite3.c in DEBUG mode compiled under Visual Studio 8 (2005) using VC++, where the rc4-based randomizing code throws static_cast overflow errors in the byte-based array. Any objections to taking back patches that bit-wise and the result of the

Re: [sqlite] chickens and egg problem: how to set page size before creating

On 5/12/07, Jens Miltner <[EMAIL PROTECTED]> wrote: Am 12.5.07 um 04:26 schrieb Andrew Finkenstadt: > It would appear that I need one "sqlite3* handle" in order to execute > statements such as "pragma page_size=32768;", but the act of calling > sqlite3_open(

[sqlite] chickens and egg problem: how to set page size before creating

It would appear that I need one "sqlite3* handle" in order to execute statements such as "pragma page_size=32768;", but the act of calling sqlite3_open(filename, ) creates the file, which prevents the changing of the page size, as the sqlite master tables are created, thereby rubbing up against

Re: [sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary

AIL PROTECTED]> wrote: Andrew Finkenstadt wrote: > It would appear that by using bind variables instead of '%Q' in the SQL > string, the need for sqlite3_encode_binary and sqlite3_decode_binary is > eliminated. Is that indeed the case? > Andy, Yes that is the case. You can use sqlite3

Re: [sqlite] ATTACH and sqlite3_open()

On further inspection of your code fragment, it appears you aren't really using (extra) attached databases, but merely specifying an alternative file to use if the first file is not available. Calling sqlite3_close(...) will do the right thing, by closing the actual database that succeeded in

Re: [sqlite] ATTACH and sqlite3_open()

From the comments around the attach function, you'd have to execute a SQL statement "attach database x as y KEY z". I assume that 'key Z' is for the encrypting version of SQLite3 distributed by drh. /* ** An SQL user-function registered to do the work of an ATTACH statement. The ** three

[sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary

It would appear that by using bind variables instead of '%Q' in the SQL string, the need for sqlite3_encode_binary and sqlite3_decode_binary is eliminated. Is that indeed the case? --andy

Re: [sqlite] Increasing ATTACH_MAX

On 4/24/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Andrew Finkenstadt" <[EMAIL PROTECTED]> wrote: > I'm exploring what it would take to support more than 10 attached databases > within one SQLite connection object. It appears that the change will be > ra

[sqlite] Increasing ATTACH_MAX

I'm exploring what it would take to support more than 10 attached databases within one SQLite connection object. It appears that the change will be rather simple: ATTACH_MAX can be made as high as 30 without further issues, given the writeMask and cookieMask field's bit width being 32 bits,

Re: [sqlite] DB design questions

By having "versioned attributes" do you mean the entire set of attributes is versioned as a group, or individually? Is it an object's state at a specific point in time that you want to version? --a On 4/20/07, Michael Ruck <[EMAIL PROTECTED]> wrote: Hello, I'm currently modelling and

Re: [sqlite] SQLite and memory usage

Is it conceivable that the buffer cache is what occupies this undeallocated memory? --andy On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I performed a simple experiment where i placed printf statements in the routines sqlite3FreeX and sqlite3MallocRaw. They seem to be the two lowest

Re: [sqlite] Insert order maintained?

The order of the rows returned by a select that does not have an ORDER BY clause is guaranteed by the standard to be in any arbitrary order, even from one execution to another due to changes in the underlying data, index statistics, amount of memory available, or even the phase of the moon. Use

Re: [sqlite] Help with SQL syntax. Ticket #2296

My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively)

Re: [sqlite] sqlite3BtreeMoveto broke between 3.3.12->3.3.14/15

I'm speaking purely from an intellectual knowledge of "reading the docs" (and having been an Oracle database developer since 1990), but I would do two things in your coding of the SQL query: 1. use a bind variable instead of inlining the SQL if you aren't already, so that you can prepare the

[sqlite] blob data streaming

Using sqlite3 (3.3.15 or later), is there a method to retrieve portions of a blob rather than the whole thing? The equivalent concept in Oracle's programmatic interfaces to C/C++ is by retrieving a "blob locator" structure instead of the blob itself, and then using procedural code to access as

[sqlite] efficacy of indexing a blob

It would appear that SQLite supports indexing every data type. What would the efficienty of the index by if I indexed a binary data column? Similarly, if I wanted to have a datatype which was the equivalent of "RAW" (non-interpreted text), it would appear I want to create a database using UTF-8

Re: [sqlite] sqlite segfault using libc library

On 3/24/07, John Stanton <[EMAIL PROTECTED]> wrote: Compilers do not terminate strings, library functions do. You are guaranteed by the C standard that the string referred to by const char message[] = "this string"; is null-terminated by the compiler.