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
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
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
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 usage
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 technique in a table called ANY_DATE_FORMAT
since
1997... admittedly in Oracle, but the principle still applies
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 someone who
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,
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.
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.
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:
Using
...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.
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
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: http://www.sqlite.org/datatype3.html describes the data
type affinities applied for the various values of the data type in the
create table statement, and if the
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 email problem
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);
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
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 Studio (Microsoft, x86
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
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
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
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
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
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
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, :) but
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 (guid,version) not in (select guid
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
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(*)
FROM
guid_version_map M
LEFT
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.
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.
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
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
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 child
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 VALUES('27/06/07', '14:38:18', '0.01
On 6/26/07, Dan Kennedy [EMAIL PROTECTED] wrote:
Compile, test, debug
... contribute. :)
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 of
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
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 in
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
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 headaches
In my sqlite framework
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
so others could use
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
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
rowid
for a table, and then potentially O(1
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
indexing within SQLite specifically to support O(1) retrieval
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
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
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
a
- 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 *
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
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
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
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?
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).
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. To make
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 realize that I didn't explain the problem well enough (thus is the
peril
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 close to it for a long time).
I can have an (org
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
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
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 unsigned chars and the result
is stored in an unsigned
)
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 unsigned chars and the result
is stored in an unsigned char. Am I to understand that you have
a compiler
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(filename, handle) creates the file, which
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, handle) creates the file, which prevents the
changing of the page size, as the sqlite master tables are created, thereby
rubbing up against
] 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_bind_blob to pass arbitrary
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
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
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
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,
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
rather simple:
ATTACH_MAX can be made
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 designing
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
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
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)
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
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
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
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.
75 matches
Mail list logo