Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
On Nov 27, 2007, at 7:26 PM, Jarl Friis wrote: "Nuno Lucas" <[EMAIL PROTECTED]> writes: If you will be sharing databases between different endienness systems then you care, so you will take appropriate actions to have the best result. The same is true with any other portable file format. So my question boils down to: Is the SQLite fileformat portable? Or is it only portable across endianess-equivalent architectures? It's portable between architectures with different endianness. There is some small conversion overhead if using UTF-16 and the endianness of the database doesn't match that of the machine it is used on. But not much. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transactional DDL
On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote: I noticed that CREATE TABLE works well within a transaction, which was a pleasant surprise. I can create a table and insert some rows in it, all quite ACIDly - wow! My question is, is that a declared contract or just a peculiarity that may disappear in future versions? I couldn't find any specs of that behavior in documentation. If I missed it, please point me to the URL. If there are no mentions of that in docs, well, it's probably worth mentioning. It's a supported feature. Also, which statements are not transactional? VACUUM is obviously one of them, are there any other? Some of the pragma statements. Can't think of anything else. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] Transactional DDL
On Nov 27, 2007, at 10:27 PM, Michael Ruck wrote: Are all CREATE ... statements transactional or is only CREATE TABLE transactional? All of the CREATE and DROP statements work properly within transactions. If the containing transaction is rolled back, the CREATE or DROP is rolled back along with everything else. Dan. Mike -Ursprüngliche Nachricht- Von: Dan [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 27. November 2007 15:59 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Transactional DDL On Nov 27, 2007, at 9:47 PM, Igor Sereda wrote: I noticed that CREATE TABLE works well within a transaction, which was a pleasant surprise. I can create a table and insert some rows in it, all quite ACIDly - wow! My question is, is that a declared contract or just a peculiarity that may disappear in future versions? I couldn't find any specs of that behavior in documentation. If I missed it, please point me to the URL. If there are no mentions of that in docs, well, it's probably worth mentioning. It's a supported feature. Also, which statements are not transactional? VACUUM is obviously one of them, are there any other? Some of the pragma statements. Can't think of anything else. Dan. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to run SQLite tests
On Nov 28, 2007, at 11:01 PM, Mark Brown wrote: Hi- Could someone please tell me how I can run the SQLite tests? I see them in CVS, but I'm not sure what to do with them. Since I'm running on vxWorks, I think they would provide an excellent way for me to know what problems I may have with my particular OS implementation. Depending on how you are compiling, you might just be able to run [make test]. Otherwise, you need to build a target called "testfixture". testfixture is a Tcl shell with the sqlite Tcl interface and some special test instrumentation built into it. Use testfixture to run the script "quick.test" from the test directory (takes from 2-4 minutes). Once that passes, run "all.test" (warning - takes upwards of 30 minutes to run). $ ./testfixture ../sqlite/test/quick.test Dan. Thanks, Mark -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ftruncate() for values greater than file size
On Nov 29, 2007, at 9:19 PM, Mark Brown wrote: Hi- I have finally tracked down a bug that has plagued by vxWorks port. It appears that our file system's implementation of ftruncate() does not like to "truncate" a file larger than its current size, and returns EINVAL for this operation. My question...assuming that I can't get ftruncate to actually expand the size of the database file by using this larger value, will SQLite have any trouble with that? If not, I'm just proposing I add a little check in this method to get the current size of the file and then make sure that whatever is passed to ftruncate does not exceed this value. I think that will work. You *might* run into a problem if you run the integrity-check after a crash or power failure in incr-vacuum mode. But I think the problem would only be with the integrity-check not with normal operation. Not 100% sure, just guessing... Dan. Thanks! Mark -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 4, 2007, at 10:27 AM, Dr Gerard Hammond wrote:
I have reported it as a bug - ticket is http://www.sqlite.org/
cvstrac/tktview?tn=2822
It appears as though the /src/select.c (Line1499) changed
from:
if( iCol<0 && mustComplete ){
to:
}else if( mustComplete ){
in version 1.336 of this file - http://www.sqlite.org/cvstrac/
filediff?f=sqlite/src/select.c=1.335=1.336
And this change results in this bug.
On 04/12/2007, at 4:59 AM, Joe Wilson wrote:
--- Marco Bambini <[EMAIL PROTECTED]> wrote:
Starting from version 3.4.2 I receive errors with queries like:
SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY
a.field
or even
SELECT a.field FROM a UNION ALL SELECT a.field FROM a ORDER BY
a.field
error is:
ORDER BY term number 1 does not match any result column
Tables are created by:
CREATE TABLE a (field);
CREATE TABLE b (field);
Please note that the above queries worked fine with sqlite 3.2.x or
3.3.x.
Any idea?
You probably know the workarounds:
SELECT a.field FROM a UNION ALL SELECT b.field FROM b ORDER BY 1;
or
SELECT a.field AS x FROM a UNION ALL SELECT b.field FROM b ORDER
BY x;
but it's odd that this one doesn't work as well:
create table t1(a);
create table t2(b);
select t1.a from t1 union all select t2.b from t2 order by a;
SQL error: ORDER BY term number 1 does not match any result column
At present, expressions in the ORDER BY clause attached to a compound
SELECT must be either:
1) An integer between 1 and the number of columns returned by
the SELECT statement (inclusive), or
2) A simple identifier (no quotes). In this case SQLite tries to
match
the identifier to one of the returned columns of data by scanning
the result-set of each of the individual SELECT statements,
starting
from the left. The identifier matches the column if the expression
in the result set is either "" or " as
"
This means you cannot specify an arbitrary sort key for a compound
statement, you can only nominate one of the returned columns to sort
on.
i.e., if we have:
CREATE TABLE x1(a, b, c);
CREATE TABLE x2(a, b, c);
then the following pairs of statements are equivalent:
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY a;
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
To my mind, the logical change to make would be to allow this:
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b";
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b];
SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1;
Because it is consistent with this kind of statement:
SELECT "x1.b" FROM (SELECT x1.b FROM x1);
Any opinions?
Dan.
Cheers.
--
Dr Gerard Hammond
Garvan Institute of Medical Research
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Cache resizing problem.
On Dec 4, 2007, at 3:49 PM, Sabyasachi Ruj wrote: Hi, I am getting a problem if I am modifying cache size. This can be reproduced by the following steps:- We need two connections to reprodce this. Say the database name is: "test.db" *"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH TIME WE WANT TO GET THE PROBLEM.* 1. Create a connection to test.db. Here "test.db" should be created physically. If it is existing please DELETE it. We will call this connection as First Connection 2. Set PRAGMA cache_size = for this connection 3. Create another connection to test.db. This is called the Second Connection. 4. Creat a a table (say "student_master") in this second connection. 5. Insert some data in this table through second connection. 6. Then try to select from the same table with the first connection. This is failing because the internal representation of the database schema used by the first connection has not yet been updated to include the changes made in step 4 by the second connection. After the sqlite3_prepare() in step 6 fails with the "no such table" error, SQLite realises that it may be using an old schema version and discards it. The new schema will be loaded fresh from the database next time a call is made to sqlite3_prepare(). So the easiest fix is just to retry the sqlite3_prepare(). I am getting error while preparing the SELECT statement. And the error is no such table: "student_master". So I think the changes we are making in the second connection are not visible. But I do not understand, why does it work if I am not executing that CACHE resize query! Because in the absence of the cache-resize query, the first connection does not initialise it's internal schema until step 6. By the time it is loaded for the first time in step 6 the "student_master" table has already been added. Hence no problem. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 4, 2007, at 10:35 PM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: i.e., if we have: CREATE TABLE x1(a, b, c); CREATE TABLE x2(a, b, c); then the following pairs of statements are equivalent: ... SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; Don't you mean ORDER BY 1? I'm talking about sqlite cvs, as the code is implemented right now (see matchOrderbyToColumn() in select.c). So 2 is correct, as the test you did shows. The "b" in the ORDER BY does not match "x1.b" because it is not a simple identifier (according to matchOrderbyToColumn()). It does not match either "" or " as ". After failing to find a match for "b" in the leftmost SELECT, SQLite searches the next leftmost and matches "b" to "b" (column 2). That's how it is at the moment, anyhow. http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html Cheers. I'm starting to realise why this little corner of sqlite is the way it is... Dan. __ __ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http:// mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache resizing problem.
On Dec 4, 2007, at 11:47 PM, Dennis Cote wrote: Dan wrote: This is failing because the internal representation of the database schema used by the first connection has not yet been updated to include the changes made in step 4 by the second connection. After the sqlite3_prepare() in step 6 fails with the "no such table" error, SQLite realises that it may be using an old schema version and discards it. The new schema will be loaded fresh from the database next time a call is made to sqlite3_prepare(). So the easiest fix is just to retry the sqlite3_prepare(). Doesn't sqlite do this automatically now if the OP were to switch to the new sqlite3_prepare_v2 API instead of using the sqlite3_prepare API? No. sqlite3_prepare_v2() behaves the same way in this case. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION and ORDER BY errors starting from sqlite 3.4.2
On Dec 5, 2007, at 1:24 AM, Joe Wilson wrote: --- Dan <[EMAIL PROTECTED]> wrote: The "b" in the ORDER BY does not match "x1.b" because it is not a simple identifier (according to matchOrderbyToColumn()). It does not match either "" or " as ". After failing to find a match for "b" in the leftmost SELECT, SQLite searches the next leftmost and matches "b" to "b" (column 2). That's how it is at the moment, anyhow. http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html Cheers. I'm starting to realise why this little corner of sqlite is the way it is... I believe that there are 2 different issues with the current implementation: 1. The result set column names of a compound SELECT should drop all table qualifiers, as they've lost all meaning once in a UNION. i.e., instead of: sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2; x1.b|a value|value you should see: b|a value|value as other databases do: mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY b; +--+--+ | b| a| +--+--+ |2 |1 | |9 |0 | +--+--+ mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY x1.b; ERROR 1054 (42S22): Unknown column 'x1.b' in 'order clause' 2. The compound SELECT's ORDER BY statement elements should only be matched against the leftmost SELECT. If there is no match in the leftmost SELECT, then an error should result - even if a match could potentially be found in non-leftmost SELECTs. Or do you disagree? Not in principle. But I think changes that break backwards compatibility would be more trouble than they're worth for something like this. In the absence of clearer guidance from sql-92, it's probably more important to be compatible with earlier sqlite versions than with mysql and friends. Maybe it would be better to document the current behaviour and move on. Dan. __ __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/ sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] unable to open a temporary database file for storing temporary tables
When issuing PRAGMAS, do they have to be issued from sqlite executable or can they also be issued via the Tcl API? Pragmas can be executed via the Tcl API, because they are a part of SQLite's SQL dialect. Unlike the 'dot-commands' that are only available in the shell tool. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Querying DATE column with date/time string.
On Dec 6, 2007, at 9:48 PM, Doug Van Horn wrote:
Hi,
I'm running into a problem with the database library in Django running
against SQLite. I'm trying to understand why the following happens:
$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01');
sqlite> select d from foo where d between '2008-01-01' and
'2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and
'2008-01-31 23:59:59.99';
sqlite> .quit
In English, why does adding the 'time' portion to the between
clause not
find the record?
Because according to the default collation sequence, strcmp(), the
string '2008-01-01 00:00:00' is larger than '2008-01-01'.
Dan.
Thanks for any help or insights...
Doug Van Horn
--
View this message in context: http://www.nabble.com/Querying-DATE-
column-with-date-time-string.-tf4956413.html#a14193493
Sent from the SQLite mailing list archive at Nabble.com.
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Simple question about optimization
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
Under the hood, the UPDATE statement above updates both the
table and index. SQLite does not realize that the index already
contains the correct data.
This:
UPDATE test set Field02='gamma';
does not touch the index.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). I'm not sure I understand the second part of the question. Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) help any? Dan. (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seg fault after calling sqlite3_exec while running under cygwin
On Dec 11, 2007, at 10:37 AM, John Williams wrote: I'm new to using sqlite, so I'm writing a rather simple piece of software for a friend that I would normally just use my own file stucture...but being that I want to learn about sqlite here I am. I should note that since the and program is destined for windows computers I'm doing my development from within cygwin to allow me to quickly and easily move to it's native environment. So (especially after looking at the backtrace below) it's hard for me to be sure if this is a problem with my code, sqlite, or cygwin itself. However I should note that if I compile with an option to use native win32 libraries instead of the cygwin ones...I still seg fault at the same point. When calling sqlite3_exec from within my add record function I am greeted with a seg fault. From viewing a backtrace in gdb I gather the following info: Does the sqlite3 shell work when compiled the same way? Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Dec 11, 2007, at 3:00 PM, Yuvaraj Athur Raghuvir wrote: 1) Can I assume that MAX_PAGE_COUNT * PAGE_SIZE = size of file on disk? No. It is the maximum size of the file. An attempt to insert data that would cause the file to grow larger than this will return SQLITE_FULL. 2) When insert statements are being executed, I would like to - for example - redirect the queries to another data base when 70% of the space is reached. How can I do that? I think you would have to check the size of the file on disk using external (non-SQLite) APIs before each insert. Dan. ~Yuva On Dec 11, 2007 11:48 AM, Dan <[EMAIL PROTECTED]> wrote: On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). I'm not sure I understand the second part of the question. Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) help any? Dan. (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache and 'no such table' issue
On Dec 27, 2007, at 5:51 PM, Pathompong Puengrostham wrote: I think I found a bug in sqlite3_close but don't know how to fix it. There is a race condition between sqlite3SchemaFree and sqlite3Init. The problem is if there are two threads with its own connection to the same database file with shared-cache mode enabled. Db.pSchema of both connections point to the same schema. When the first thread is closing the connection and in the middle of sqlite3SchemaFree, where all the hash tables are freed but DB_SchemaLoaded in pSchema->flags is not cleared yet. If the other thread calls to sqlite3LocateTable at this time, it'll return no such table. Because sqlite3Init will see that DB_SchemaLoaded is still set and will not do anything. The only mutex I see involved is db->mutex which not help in this case. Should there be a pSchema->mutex? Does anyone have this same problem? Should be fixed by: http://www.sqlite.org/cvstrac/chngview?cn=4643 Please post if you find this is not the case (or if you find it is). Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] C API: Manifest type SQLITE_INTEGER: Is it 32- or 64-bit?
On Jan 4, 2008, at 7:57 AM, Jerry Krinock wrote:
I need to read an sqlite database generated by others. So I wrote
an outer loop which steps through the rows of a table using
sqlite3_step, and an inner loop which steps through the columns.
The inner loop finds the type using sqlite3_column_type(), then
'switches' to get the value using the appropriate
sqlite3_column_X() function.
It works fine if, when encountering an SQLITE_INTEGER type, I use
sqlite_column_int64() to get the data.
Internally, integers are all 64-bits. If you call sqlite3_column_int()
to retrieve a value, it is truncated to 32-bits before returning it.
So to be safe, you're better off always using sqlite3_column_int64().
Dan.
I don't know whether or not I'm just "lucky" that the application
which wrote the database uses 64 bit for all of its integers? If
so, what if someone throws a 32-bit integer at me someday? How can
I tell whether integer data objects in a table are 32 or 64 bit?
The column specifications I get from pragma_table_info() are
likewise uninformative, saying simply type=INTEGER.
Thanks again,
Jerry Krinock
// Method Implementation (Objective-C for Mac OS X)
- (NSArray*)dicsOfRowsInTable:(NSString*)table {
// Will return nil if fails, empty array if no rows
void* db = [self db] ;
//char* errMsg = NULL ;
int result ;
NSString* statement = [[NSString alloc] initWithFormat:@"SELECT
* FROM '%@'", table] ;
// Compile the statement into a virtual machine
sqlite3_stmt* preparedStatement ;
result = sqlite3_prepare(db, [statement UTF8String], -1,
, NULL) ;
[statement release] ;
NSArray* output = nil ;
if (result != SQLITE_OK) {
[self showError:"prepare" from:11 code:result] ;
}
else {
NSMutableArray* rowDics = [[NSMutableArray alloc] init] ;
NSArray* keys = [self keysInTable:table] ;
int nColumns = [keys count] ;
while (result = sqlite3_step(preparedStatement) ==
SQLITE_ROW) {
NSMutableDictionary* rowDic = [[NSMutableDictionary
alloc] init] ;
int iColumn ;
for (iColumn= 0; iColumn<nColumns; iColumn++) {
int type = sqlite3_column_type(preparedStatement,
iColumn) ;
// The sqlite3_column_type() routine returns
datatype code
// for the initial data type of the result column.
// The returned value is one of SQLITE_INTEGER,
// SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or
SQLITE_NULL
// Initialize to null in case object is not found
const void* pFirstByte = NULL ;
int nBytes = 0 ;
id object = nil ;
long long int intValue ;
const unsigned char* utf8String ;
double doubleValue ;
switch(type) {
case SQLITE_BLOB:
nBytes = sqlite3_column_bytes
(preparedStatement, iColumn) ;
// "The return value from
sqlite3_column_blob() for a zero-length
// blob is an arbitrary pointer, possibly
even a NULL pointer."
// Therefore, we qualify...
if (nBytes > 0) {
pFirstByte = sqlite3_column_blob
(preparedStatement, iColumn) ;
object = [[NSData alloc]
initWithBytes:pFirstByte length:nBytes] ;
}
break ;
case SQLITE_INTEGER:
intValue = sqlite3_column_int64
(preparedStatement, iColumn) ;
object = [NSNumber
numberWithLongLong:intValue] ;
break ;
case SQLITE_TEXT:
// "Strings returned by sqlite3_column_text
() and sqlite3_column_text16(),
// even zero-length strings, are always
zero terminated."
// So, we ignore the length and just
convert it
utf8String = sqlite3_column_text
(preparedStatement, iColumn) ;
object = [NSString stringWithUTF8String:
(char*)utf8String] ;
break ;
case SQLITE_FLOAT:
doubleValue = sqlite3_column_double
(preparedStatement, iColumn) ;
object = [NSNumber
numberWithDouble:doubleValue] ;
break ;
case SQLITE_NULL:
default:
// Just leave object nil, will replace with
[NSNull null] soon.
;
}
if (object == nil) {
object = [NSNull null] ;
}
[rowDic setObject:object forKey:[keys
objectAtIndex:iColumn]] ;
[sqlite] configure script for amalgamation
Hi, Experimenting with distributing an autoconf configure script with the amalgamation package. So that it can do the usual "./configure && make install" installation. Prototype here: http://www.sqlite.org/sqlite-3.5.4-autoconf.tar.gz Could anybody who has time to try this out post if it fails to install, fails to detect readline, fails to build shared library on your AIX system, that sort of thing. Thanks in advance. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do I add primary key on existing table?
On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote: Hi, I was trying to add a primary key to existing table but could not. "alter table custsales add constraint pk_custsales primary key (id, type)" Is this supported? Is there a different syntax to do this? Using ALTER TABLE to add a constraint or a new column marked as PRIMARY KEY is not supported. You can get pretty much the same effect by doing: CREATE UNIQUE INDEX custsales_i ON custsales(id, type); Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Program is crahed on sqlite3_bind_int- Could you please help
On Jan 22, 2008, at 9:14 AM, Joanne Pham wrote: Hi All, I still had the problem below: sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/ vdbeapi.c:897 897./src/vdbeapi.c: No such file or directory. in ./src/vdbeapi.c Current language: auto; currently c when I used sqlite3_bind_int. I couldn't figure out what is the problem and how to fit it. It used to work last week. Could you please help. It's likely that the statement handle is invalid or has already been passed to sqlite3_finalize(). Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS3 Unicode support
On Jan 25, 2008, at 7:26 AM, Myk Melez wrote: Hi all, I'm working to enable FTS3 in the next version of Firefox [1] so that extenders can take advantage of it, although Firefox itself isn't using it for the next release. Given Firefox's international audience, it would be useful for FTS3 to support Unicode. We currently do this for upper(), lower(), and LIKE by redefining them with sqlite3_create_function [2]. For FTS3 it seems like we'd have to redefine the tokenizer and MATCH. Can that be done using sqlite3_create_function, and what's the status of the international support mentioned in a previous message on this list [3]? Hi Myk, The 'icu' and 'fts3' SQLite extensions can take advantage of the ICU library to provide internationalization if it is available. The ICU extension provides internationalized versions of upper(), lower(), collation sequences and a REGEXP operator. Details are available here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Fts3 has an API for creating new tokenizers. See here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/fts3/ README.tokenizers One of the example tokenizers uses the ICU library for localization. See the same document for details. It is built if the SQLITE_ENABLE_ICU macro is defined when fts3 is compiled. Regards, Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Compilation Problems on FreeBSD
On Jan 28, 2008, at 7:41 AM, L. S. wrote: Why doesn't SQLite 3.5.4 compile under FreeBSD 5.3? Can it be done in a sane manner? How can I link against SQLite under FreeBSD 5.3, using gcc? Is there any way that I could just link the source files into my code? * Using "./configure" and "make" with "sqlite-3.5.4.tar.gz", I got tcl and tcsh errors. * Using "sqlite-source-3_5_4.zip", with "tclsqlite.c" removed, I got pthread errors. They went away with "gcc ... -lpthread", but compilation still failed. * I think "sqlite-amalgamation-3_5_4.zip" produced the same errors as sqlite-source-. Hi, Can you try the experimental configure system in this package and let us know if it works: http://www.sqlite.org/sqlite-3.5.4-autoconf.tar.gz Thanks, Dan. I have compiled, installed, linked, and used it on other systems, but cannot do the same on my FreeBSD system. (I don't use the FreeBSD ports; I like to just download and build.) ** -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Solaris bus error
On Feb 1, 2008, at 6:37 AM, Ken wrote:
Recompiled without the -O2 flags:
#0 0x00049750 in findLockInfo (fd=4, ppLock=0x1345ad,
ppOpen=0x1345a9)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:670
670 *ppLock = pLock;
*ppLock is Null
Hi Ken,
This doesn't look quite right to me. In the debugger you can
do "print *ppLock" and it doesn't complain?
The test case you were running (async.test) uses more than one
thread, so maybe the one we're looking at is not the one that
caused the problem.
Can you try running [info threads] in the debugger, and then
see if you can get a stack trace for another thread? Thanks.
Dan.
Ken <[EMAIL PROTECTED]> wrote: Core/backtrace info:
(gdb) core-file core
Core was generated by `/home/ixion/LIB/sqlite3test/.libs/
testfixture ../sqliteSrc/sqlite-3.5.5/test/qu'.
Program terminated with signal 10, Bus error.
Reading symbols from /home/ixion/LIB/sqlite3test/.libs/
libsqlite3.so.0...done.
Loaded symbols for /home/ixion/LIB/sqlite3test/.libs/libsqlite3.so.0
Reading symbols from /usr/lib/libpthread.so.1...done.
Loaded symbols for /usr/lib/libpthread.so.1
Reading symbols from /usr/local/lib/libtcl8.5.so...done.
Loaded symbols for /usr/local/lib/libtcl8.5.so
Reading symbols from /usr/lib/libdl.so.1...done.
Loaded symbols for /usr/lib/libdl.so.1
Reading symbols from /usr/lib/libsocket.so.1...done.
Loaded symbols for /usr/lib/libsocket.so.1
Reading symbols from /usr/lib/libnsl.so.1...done.
Loaded symbols for /usr/lib/libnsl.so.1
Reading symbols from /usr/lib/libm.so.1...done.
Loaded symbols for /usr/lib/libm.so.1
Reading symbols from /usr/lib/librt.so.1...done.
Loaded symbols for /usr/lib/librt.so.1
Reading symbols from /usr/lib/libc.so.1...done.
Loaded symbols for /usr/lib/libc.so.1
Reading symbols from /usr/local/lib/libgcc_s.so.1...done.
Loaded symbols for /usr/local/lib/libgcc_s.so.1
Reading symbols from /usr/lib/libmp.so.2...done.
Loaded symbols for /usr/lib/libmp.so.2
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.
1...done.
Loaded symbols for /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1
Reading symbols from /usr/lib/libthread.so.1...done.
Loaded symbols for /usr/lib/libthread.so.1
#0 0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995,
ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
671 if( ppOpen!=0 ){
(gdb) backtrace
#0 0x000345d4 in findLockInfo (fd=567296, ppLock=0xd1995,
ppOpen=0xd1991)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:671
#1 0x000355b4 in unixOpen (pVfs=0x8a96c,
zPath=0xc0380 "/home/ixion/LIB/sqlite3test/chocolate/banana/
vanilla/file.db", pFile=0xd196d, flags=262, pOutFlags=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os_unix.c:2268
#2 0x000340bc in sqlite3OsOpen (pVfs=0x2102,
zPath=0xc0380 "/home/ixion/LIB/sqlite3test/chocolate/banana/
vanilla/file.db", pFile=0xd196d, flags=262, pFlagsOut=0x)
at ../sqliteSrc/sqlite-3.5.5/src/os.c:112
ppOpen os_unxi.c/671 is not !=0 but *ppOpen is 0...
James Dennett wrote: > -Original Message-
From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error
After addressing the -lrt sched yield on solaris.
make test resulted in:
async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)
Any ideas?
Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.
-- James
James Dennett wrote: > -Original Message-
From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error
After addressing the -lrt sched yield on solaris.
make test resulted in:
async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)
Any ideas?
Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.
-- James
James Dennett wrote: > -Original Message-
From: Ken [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 31, 2008 2:55 PM
To: sqlite
Subject: [sqlite] Solaris bus error
After addressing the -lrt sched yield on solaris.
make test resulted in:
async3-1.0... Ok
async3-1.1...make: *** [test] Bus Error (core dumped)
Any ideas?
Getting a stack trace out of that core file with a debugger would seem
to be the next step, and seeing the full text output if there is any
more.
-- James
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQLite Web Site
On Feb 7, 2008, at 6:12 AM, Scott Baker wrote: > Steven Fisher wrote: >> On 06-Feb-2008, at 12:33 PM, [EMAIL PROTECTED] wrote: >> >>> What do you mean "no longer found"? Do you mean that that >>> you cannot see anything at all, or that the new design is such >>> that it is not displayed correctly? >> >> Well, the page definitely doesn't validate: >> http://validator.w3.org/check?uri=http%3A%2F%2Fsqlite.org >> >> I don't know if that's the issue or not, of course. :) > > I did a quicky patch for the homepage to make it compliant: > > http://www.perturb.org/tmp/sqlite_homepage.patch > > It at least validates with my firefox plugin. This is a bit off-topic, sorry... What exactly is that firefox plugin checking? As far as I know, this kind of thing: is not valid HTML. It is valid XHTML of course. All browsers just ignore the "/" character, but I can't think of any document where this is defined. Does anybody know? The validator also complained about this: Ok, fine, you can't nest inside of . But the spec defines well what happens in this case. The first tag is closed automatically by the . The resulting empty paragraph is discarded (*special* html rule for - empty paragraphs have no effect on document layout). The final is discarded because it doesn't match any opening tag. So in this case the incorrect HTML is equivalent to the HTML suggested by the validator. Fair enough I guess, both versions are unambiguous and the suggested version is better. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
On Feb 8, 2008, at 6:10 AM, Jason Tudor wrote: > Thanks for the quick feedback, you all have good points. The data > transfer > example that Sam provided is the most compelling to me. Another reason is that a transaction that writes to more than one attached database is still an atomic operation (either all the changes will succeed or none of them). You can't do that with two separate database handles. Dan. > I was initially thinking that I could attach every database I had > with the > same schema and execute queries without specifying database names. > It's > good to know the limitations and uses. > > Thanks again > TUD > > > On Feb 7, 2008 2:39 PM, Nicolas Williams <[EMAIL PROTECTED]> > wrote: > >> Given the restrictions on views and triggers (they cannot make >> reference >> to tables from more than one database) I'd say "stay away from ATTACH >> where possible" -- don't create new uses of it without good reason. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this list available in *DIGEST* form??
On Feb 8, 2008, at 11:31 AM, Rob Sciuk wrote: > > How do I sign up for the digest rather than the regular feed?? > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Use the webpage linked above. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table xBestIndex and when to free index resource
On Feb 12, 2008, at 7:05 AM, Evans, Mark (Tandem) wrote:
> SQLite experts:
>
> The xBestIndex method of the SQLite virtual table interface
> implemented by the VT module returns an output to the core by
> setting idxNum member var of struct sqlite3_index_info to a value
> that is meaningful to the VT module. Assume that a memory resource
> was created in conjunction with the chosen index that will hold
> information passed by xFilter.
>
> The question is: How can VT module tell when it is safe to release
> that resource? I'm thinking, it's when the associated statement is
> finalized. But how does the VT module know that? I have found
> that xClose() call is not the answer because I have stumbled on a
> test sequence that shows this to be unsafe:
>
>
> do_test update-1.0 {
> execsql {DELETE FROM t1}
> execsql {insert into t1 values(1,2,3)}
> execsql {SELECT * FROM t1 }
> execsql {UPDATE t1 SET y=3 WHERE x=1}
> execsql {SELECT * FROM t1 }
> } {1 2 3 1 3 3}
>
> After execution of the UPDATE, the VT module call sequence for the
> next SELECT does not include xBestIndex as I was expecting. It
> calls xFilter with the idxNum that the previous SELECT created (I
> think). I crash and burn because I released the index resource in
> the xClose call for the first SELECT.
>
> I'd be most appreciative if an expert could steer me in the right
> direction.
Executing an SQL statement is broken into two parts: compilation
(sqlite3_prepare()) to virtual machine code and execution of that
virtual machine code (sqlite3_step()). The xBestIndex() method is
called as part of compilation, xFilter() is called as part of
execution. As is xClose().
The first time your SELECT statement is run the Tcl interface
calls sqlite3_prepare() to compile it, then
sqlite3_step()/sqlite3_reset() to execute it. The second time,
it is able to re-use the compiled statement. That is why xBestIndex
is not called for the second SELECT.
For passing context, you can also use the sqlite3_index_info.idxStr
variable. Set this to point at a string allocated by sqlite3_malloc()
and sqlite will automatically free it when it is no longer required.
This allows you to store a blob of context data instead of a single
integer.
If you need some resource that really does require a destructor (a
connection handle to some other database etc.), do not open it in
xBestIndex(). Open it in xFilter() and close it in xClose(). Each
xFilter() call should be matched by exactly one xClose().
Regards,
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested calls to prepare/step/prepare
On Feb 29, 2008, at 5:21 AM, Ken wrote:
> I wanted to find out if the following is allowed in Sqlite.
>
>sqlite3_prepare_v2
> while ( ) {
>sqlite3_step
> sqlite3_prepare_v2 --- I;m getting a segv here.
> while ( ) {
>sqlite3_step
> }
> }
>
>
> So my questing is, does sqlite allow a prepare to be started while
> a prior prepared statement is still open?
Hi,
This is supposed to be Ok. If you can supply an example program
to demonstrate the crash that would be very helpful.
Dan.
> Thanks,
> Ken
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases
On Mar 3, 2008, at 12:55 AM, Kee Wee wrote: > > I would be very interested to follow-up this subject closely, > as we are actually performing the same kind of work as you are > looking for. > > One of the answer you got was that it is not possible to reuse a > prepared > statement from one database to another. > This answer was too quick, and did not include any explanation -> > so I would > rather prefer to ask again the question, and obtain why it is not > possible. > > I would say that the test I have performed until now make this thing > possible. > I agree that this requires to tweak a little bit the way SQLite is > working > (without recompiling anything, only using the internal members of > the opaque > structures). > > If VDBE programmers think that I am getting too deep inside the VDBE > internal structures to handle this problem, (or that it is too > risky for > data integrity), please feel free to comment vigorously on this > subject. > > The work performed was to swap Database Handles. > Following code is included in XMLRAD DacSQLite3 to address exactly > this > problem: > > procedure StmtSwapVdbe(Statement: TDISQLite3StatementHandle; NewDB: > Pointer); > var > Vdbe: PVdbe; > begin > Vdbe := PVdbe(Statement); > if Vdbe.db = NewDB then > Exit; > // Uninstall Vdbe from its actual Vdbe.db > if Vdbe.pPrev <> nil then > Vdbe.pPrev.pNext := Vdbe.pNext > else > Vdbe.db.pVdbe := Vdbe.pNext; > if Vdbe.pNext <> nil then > Vdbe.pNext.pPrev := Vdbe.pPrev; > > // Install Vdbe to the NewDB > Vdbe.db := NewDB; > if Vdbe.db.pVdbe <> nil then > Vdbe.db.pVdbe.pPrev := Vdbe; > Vdbe.pNext := Vdbe.db.pVdbe; > Vdbe.pPrev := nil; > Vdbe.db.pVdbe := Vdbe; > end; > > Once again, if any programmer of the VDBE could comment on this, I > am even > willing to pay money to get audit of the code, and get confirmation > that > this technique is valid and does not put at risk data integrity of > databases. It think this risks database corruption. Vdbe programs hard-code the schema-version number and the locations of tables and indexes in the database file. These might be different for different databases, even those with the same schema. If the table/index locations are different but the schema cookie is the same, you are risking database corruption. Also, prepared statement structures contain some pointers back to the database handle that was used to create them. Not sure what the implications of this are, but it doesn't seem safe. There might be other reasons this is dangerous too. Dan. > -- > View this message in context: http://www.nabble.com/Multiple- > databases-tp15035409p15790869.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bus error with SQLite 3.5.6
Hi,
I tried this script here with the latest CVS version and
it didn't crash:
CREATE TABLE pages(page_id INTEGER PRIMARY KEY, page_name TEXT,
page_text TEXT);
CREATE VIRTUAL TABLE fts_pages USING fts3(page_name, page_text);
CREATE TRIGGER delete_fts
AFTER DELETE ON pages
BEGIN
DELETE FROM fts_pages WHERE rowid = old.page_id;
END;
CREATE TRIGGER insert_fts
AFTER INSERT ON pages
BEGIN
INSERT INTO fts_pages (rowid, page_text)
VALUES (new.page_id, new.page_text);
END;
CREATE TRIGGER update_fts
AFTER UPDATE OF page_text ON pages
BEGIN
UPDATE fts_pages
SET page_text = new.page_text
WHERE rowid = old.page_id;
END;
INSERT INTO pages (page_name, page_text) VALUES ('foo', 'bar');
Can you test this in your environment? If this fails to produce
the crash, can you post the offending database? Or send it to
me if you don't want it made public. Also, the exact sqlite version
might be helpful.
Alternatively, can you compile with debugging symbols enabled and
post a stack trace?
Thanks,
Dan.
On Mar 3, 2008, at 4:33 AM, P Kishor wrote:
> I have a fairly simple db with fts3
>
> TABLE pages (page_id INTEGER PRIMARY KEY, page_name TEXT, page_text
> TEXT);
>
> VIRTUAL TABLE fts_pages USING fts3 (page_name, page_text);
>
> and the following triggers --
>
> CREATE TRIGGER delete_fts
> AFTER DELETE ON pages
> BEGIN
> DELETE FROM fts_pages WHERE rowid = old.page_id;
> END;
>
> CREATE TRIGGER insert_fts
> AFTER INSERT ON pages
> BEGIN
> INSERT INTO fts_pages (rowid, page_text)
> VALUES (new.page_id, new.page_text);
> END;
>
> CREATE TRIGGER update_fts
> AFTER UPDATE OF page_text ON pages
> BEGIN
> UPDATE fts_pages
> SET page_text = new.page_text
> WHERE rowid = old.page_id;
> END;
>
> When I enter a new row like so via the sqlite3 (version 3.5.6) shell
> on my MBP with OS X 10.5.2, I get the following error
>
> sqlite> INSERT INTO pages (page_name, page_text) VALUES ('foo',
> 'bar');
> Bus error
>
> and the sqlite3 shell quits out to the bash shell.
>
> A small journal file is left, and if I enter sqlite3 again, I get the
> message that the db is locked. I can proceed once I delete the journal
> file. I have the following crash log to report
>
> Process: sqlite3 [1464]
> Path:/usr/local/bin/sqlite3
> Identifier: sqlite3
> Version: ??? (???)
> Code Type: X86 (Native)
> Parent Process: bash [291]
>
> Date///Time: 2008-03-02 15:24:45.729 -0600
> OS Version: Mac OS X 10.5.2 (9C31)
> Report Version: 6
>
> Exception Type: EXC_BAD_ACCESS (SIGBUS)
> Exception Codes: KERN_PROTECTION_FAILURE at 0x
> Crashed Thread: 0
>
> Thread 0 Crashed:
> 0 libsqlite3.0.dylib0x000ba7d8 sqlite3Step + 15862
> 1 libsqlite3.0.dylib0x000bde17 sqlite3_step + 75
> 2 libsqlite3.0.dylib0x000a333d sqlite3_exec + 209
> 3 sqlite3 0x6059 process_input + 1124
> 4 sqlite3 0x6c2a main + 2527
> 5 sqlite3 0x2596 start + 54
>
> Thread 0 crashed with X86 Thread State (32-bit):
> eax: 0x00807480 ebx: 0x000b69f3 ecx: 0x0013d808 edx: 0x
> edi: 0x0080b04c esi: 0x0018 ebp: 0xbfffed98 esp: 0xbfffe930
>ss: 0x001f efl: 0x00010206 eip: 0x000ba7d8 cs: 0x0017
>ds: 0x001f es: 0x001f fs: 0x gs: 0x0037
> cr2: 0x
>
> Binary Images:
> 0x1000 - 0x7fe7 +sqlite3 ??? (???) /usr/local/bin/sqlite3
>0x21000 -0x37fea libedit.2.dylib ??? (???)
> /usr/lib/libedit.2.dylib
>0x6a000 -0xc8fff +libsqlite3.0.dylib ??? (???)
> /usr/local/lib/libsqlite3.0.dylib
> 0x8fe0 - 0x8fe2da53 dyld 96.2 (???)
> <7af47d3b00b2268947563c7fa8c59a07> /usr/lib/dyld
> 0x9001c000 - 0x90020fff libmathCommon.A.dylib ??? (???)
> /usr/lib/system/libmathCommon.A.dylib
> 0x908cb000 - 0x908d2fe9 libgcc_s.1.dylib ??? (???)
> /usr/lib/libgcc_s.1.dylib
> 0x9126b000 - 0x9129aff7 libncurses.5.4.dylib ??? (???)
> <3b2ac2ca8190942b6b81d2a7012ea859> /usr/lib/libncurses.5.4.dylib
> 0x923c8000 - 0x92527ff3 libSystem.B.dylib ??? (???)
> <4899376234e55593b22fc370935f8cdf> /usr/lib/libSystem.B.dylib
> 0x - 0x1780 libSystem.B.dylib ??? (???) /usr/lib/
> libSystem.B.dylib
>
> Any advice?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Crashes
On Mar 2, 2008, at 4:42 AM, Shawn Wilsher wrote:
> Hey all,
>
> Over at Mozilla we've been seeing a large amount of crashes in
> sqlite3_enable_shared_cache. The stack frames don't make a whole lot
> of sense to me, so I thought I'd inform you and hope that you might
> have a better idea as to what is going on. If you have any questions,
> feel free to ask. If I don't know the answer, I'll get the people who
> should know involved. We'd really like to try and resolve this issue,
> so insight on this matter would be greatly appreciated.
>
> http://tinyurl.com/2393qs
>
> We are presently using the latest version of sqlite.
Hi Shawn,
I put a pointer to the mozilla bug report here:
http://www.sqlite.org/cvstrac/tktview?tn=2970
These stack traces don't make any sense to me either. The definition
of sqlite3_enable_shared_cache() in SQLite cvs is:
int sqlite3_enable_shared_cache(int enable){
sqlite3SharedCacheEnabled = enable;
return SQLITE_OK;
}
sqlite3SharedCacheEnable is a file scoped int.
Some of the stack traces have sqlite3_enable_shared_cache() being called
from Mozilla code, but some others show it being called from other
parts of SQLite that make no sense to me. This one is particularly
odd:
http://crash-stats.mozilla.com/report/index/8b54f1c5-e8aa-11dc-
b466-001a4bd43e5c
Stack overflow possibly? Will keep thinking this.
Regards,
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bus error with SQLite 3.5.6
On Mar 3, 2008, at 12:03 PM, P Kishor wrote:
> Dan,
>
>
> On 3/2/08, Dan <[EMAIL PROTECTED]> wrote:
>>
>> Hi,
>>
>> I tried this script here with the latest CVS version and
>> it didn't crash:
>>
>>CREATE TABLE pages(page_id INTEGER PRIMARY KEY, page_name TEXT,
>> page_text TEXT);
>>CREATE VIRTUAL TABLE fts_pages USING fts3(page_name, page_text);
>>
>>
>>CREATE TRIGGER delete_fts
>>AFTER DELETE ON pages
>>BEGIN
>> DELETE FROM fts_pages WHERE rowid = old.page_id;
>>END;
>>
>>CREATE TRIGGER insert_fts
>>AFTER INSERT ON pages
>>BEGIN
>> INSERT INTO fts_pages (rowid, page_text)
>> VALUES (new.page_id, new.page_text);
>>END;
>>
>>CREATE TRIGGER update_fts
>>AFTER UPDATE OF page_text ON pages
>>BEGIN
>> UPDATE fts_pages
>> SET page_text = new.page_text
>> WHERE rowid = old.page_id;
>>END;
>>
>>
>>INSERT INTO pages (page_name, page_text) VALUES ('foo', 'bar');
>>
>>
>> Can you test this in your environment?
>
> I am not sure what I should test in my environment. Did you mean to
> send me some script? Or, are you asking me to try the latest version
> from CVS?
I mean the set of SQL statements above. Create the schema and
execute a single INSERT statement. Does starting with an empty
database and feeding them to the sqlite shell cause a crash?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_get_table
On Mar 6, 2008, at 10:57 AM, Kenneth LO wrote:
> In previous versions (<=3.4.10) I use to do if (errmsg) { free(errmsg)
> ; } without problem.
>
> In 3.5.6 the free() will segfault (yes, even after if) with a "invalid
> pointer" message.
>
> It's my old understanding that sqlite3_get_table (previously
> sqlite_get_table) will malloc for *errmsg and I'll need to do the
> free(). Has anything changed? I'm just recompiling old codes with
> the new lib.
Technically you're supposed to use sqlite3_free(). They might be
different, depending on compilation options.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "sqlite3.h: No such file or directory" error when installing software on Linux
On Mar 10, 2008, at 6:44 PM, thomas Armstrong wrote: > Hi. > > I've just installed 'sqlite' (version 2.8.16) and 'sqlite-devel' > (version 2.8.16) from RPMs on my Linux Fedora Core, but I get this > error message when installing a software: Looks like you need to install version 3. Dan. > > -- > - > gcc -DHAVE_CONFIG_H -I. -I. -I../../.. -I../../../include > -I../../../include/sandesha2 -I. > -I/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/../axis2c/ > include > -I/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/../axis2c/ > axiom/include > -I/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/../axis2c/ > util/include > -I/usr/include -g -O2 -D_LARGEFILE64_SOURCE -ansi -Wall > -Wno-implicit-function-declaration -g -MT permanent_create_seq_mgr.lo > -MD -MP -MF .deps/permanent_create_seq_mgr.Tpo -c > permanent_create_seq_mgr.c -fPIC -DPIC -o > .libs/permanent_create_seq_mgr.o > In file included from permanent_create_seq_mgr.c:18: > sandesha2_permanent_bean_mgr.h:33:21: sqlite3.h: No such file or > directory > In file included from permanent_create_seq_mgr.c:18: > sandesha2_permanent_bean_mgr.h:196: error: syntax error before '*' > token > sandesha2_permanent_bean_mgr.h:198: error: > `sandesha2_permanent_bean_mgr_busy_handler' declared as function > returning a function > sandesha2_permanent_bean_mgr.h:199: error: syntax error before "void" > make[8]: *** [permanent_create_seq_mgr.lo] Error 1 > make[8]: Leaving directory > `/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/src/storage/ > sqlite' > make[7]: *** [all-recursive] Error 1 > make[7]: Leaving directory > `/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/src/storage' > make[6]: *** [all-recursive] Error 1 > make[6]: Leaving directory > `/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c/src' > make[5]: *** [all-recursive] Error 1 > make[5]: Leaving directory > `/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c' > make[4]: *** [all] Error 2 > make[4]: Leaving directory > `/root/install/wso2-wsf-php-src-1.2.0/wsf_c/sandesha2c' > make[3]: *** [all-recursive] Error 1 > make[3]: Leaving directory `/root/install/wso2-wsf-php-src-1.2.0/ > wsf_c' > make[2]: *** [all] Error 2 > make[2]: Leaving directory `/root/install/wso2-wsf-php-src-1.2.0/ > wsf_c' > make[1]: *** [all-recursive] Error 1 > make[1]: Leaving directory `/root/install/wso2-wsf-php-src-1.2.0' > make: *** [all] Error 2 > --- > > Any suggestion? Thank you very much. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't get concat operator to work
On Mar 11, 2008, at 7:36 AM, [EMAIL PROTECTED] wrote: > > > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) > FROM Sessions > > I get only the formatted date - missing the formatted time. > FORMAT_DATE is my own user-defined function that returns text data > type. When you call sqlite3_result_text() to return the result, does your result string include a nul-terminator character? If so, that byte should not be included in the "number of bytes" parameter passed to result_text(). i.e. if you were doing: sqlite3_result_text(pContext, "abc", 4, ...) you might get the result you are describing. Dan. > > Can someone *please* check into this. I must get this working. > > Thank you > -brett > > > > > This message was sent using IMP, the Internet Messaging Program. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] garbage mail messages
On Mar 11, 2008, at 3:02 PM, [EMAIL PROTECTED] wrote: > > > OK Dan, you have the solution. The count was including the > terminating NULL char. Making it not include the NULL char fixed > the problem. > >Another question: For an empty result, should I return 0 or -1? > And should the string be NULL or "" ? Bear in mind that its an empty > result - not a NULL result. sqlite3_result_text(pContext, "", 0, SQLITE_STATIC) will work. You could also pass -1 instead of 0 (-1 means use strlen() or its utf-16 equivalent for result_text16()) to discover the number of bytes in the string argument. Passing NULL as the second parameter would not work. That would result in the SQL user function returning an SQL NULL, not a zero length string. Dan. > >Thanks a million > -brett > >Quoting Dan Kennedy > > >> >> I'm trying to get the concat operator to work with my > user-defined >> function. This works fine: >> >> SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees >> >> But this doesn't work: >> >> SELECT FORMAT_DATE(login_time) || ' ' || FORMAT_TIME(login_time) >> FROM Sessions >> >> I get only the formatted date - missing the formatted time. >> FORMAT_DATE is my own user-defined function that returns text data >> type. > When you call sqlite3_result_text() to return the result, does your > result string include a nul-terminator character? If so, that byte > should not be included in the "number of bytes" parameter passed > to result_text(). i.e. if you were doing: > > sqlite3_result_text(pContext, "abc", 4, ...) > you might get the result you are describing. > Dan. > > > This message was sent using IMP, the Internet Messaging Program. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and (high) concurrency
On Mar 11, 2008, at 10:57 PM, Tore Austraatt wrote:
> Ken,
> sorry, it didn't make a difference, including _LOCKED in my test.
> None of the dropped records are involved in lock situations.
>
> an example...
> _exec("BEGIN IMMEDIATE..."
> sqlite3_mprintf("INSERT OR IGNORE INTO ..."...
^
Maybe that's got something to do with it. Knock out the OR IGNORE
and see what happens.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Param Binding Problem
On Mar 12, 2008, at 11:40 PM, Mike Marshall wrote:
> Hi all
>
>
>
> I'm trying to get param binding working and I'm obviously doing
> something
> wrong
>
>
>
> My code is
>
>
>
> acQuery = sqlite3_mprintf("SELECT rowid FROM fulltext_%s WHERE
> contents
> MATCH '? '",sTempTableRoot.c_str());
Don't put quotes around the ? character. ('? ') is a literal string. You
probably want (? || ' ') - an SQL variable with a single space appended
to it.
Dan.
>
> nError = sqlite3_prepare_v2(m_pHandle,acQuery,-1,,NULL);
>
> nError =
> sqlite3_bind_text(pStatement,1,m_sQuery.c_str(),-1,SQLITE_TRANSIENT);
>
>
>
> nError from the prepare is SQLITE_OK but from the bind its
> SQLITE_RANGE
>
>
>
> Anyone got any ideas where I'm going wrong, or is it the fact that I'm
> trying to do a bind on a MATCH (fts) that is giving me the problem.
>
>
>
> Any and all help gratefully received
>
>
>
> Mike
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting NULs with Dynamic Typing
> How does SQLite handle sorting with a column that contains values > (including zero) and NULs? Are the NULs converted to zero for the > purposes of sorting? A null value is considered less than all other values when sorting. http://www.sqlite.org/datatype3.html#comparisons Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regular Expressions and sqlite3_create_function
On Mar 20, 2008, at 10:38 PM, Jason Tudor wrote: > I am trying to enable the REGEXP operator. I have read on the > forums that > you must use the sqlite3_create_function. I would like to use the > boost > regular expressions library under the hood. Question is, does > anyone have > an example using this function (sqlite3_create_function) to override > regexp()? There is one in the ext/icu/icu.c file of the source distribution. http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c=1.7 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] endless loop example
On Mar 21, 2008, at 12:17 PM, Derek Developer wrote: > In trying to break my code with the seinfeld database examples, I > found this. > SELECT f.name as food, e1.name, e1.season, e2.name, e2.season FROM > episodes e1, foods_episodes fe1, foods f, episodes e2, > foods_episodes fe2 > Why does this put SQLite into an endless loop? Why do you figure it is an infinite loop? From the looks of the query it is probably just returning a very large number of rows. Dan. > (I am not using the shell tool, just preparing the statement as is > and stepping throug the rows) > > > > - > Looking for last minute shopping deals? Find them fast with Yahoo! > Search. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] limts change with switch from bit maps to vectors?
On Apr 2, 2008, at 11:57 PM, Shane Harrelson wrote: > Is the limit of 64 tables in a join changed now that it's using "bit > vectors" instead of "bit maps"? Similar on number of attached > databases? > > Or were the changes only to the bitmaps used for page tracking? Only the dirty-page tracking. The limits on the number of attached databases and tables in a join are the same as they always were. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode searches
On Apr 6, 2008, at 5:10 AM, Keith Stemmer wrote: > Yes, I can add a custom collation which works for ASCII chars LOL. > If you don't understand the problem, just don't reply. > > By the way, you can read on the SQLite website that the developer > describes > my problem as a BUG which is nice to read. At least he doesn't call > it a > feature. The answer you were provided with is correct and canonical in my opinion. The SQLite source archive (.tar.gz, not sure about the preprocessed versions) contains source code for an sqlite extension that binds the ICU library to SQLite using the custom collation sequence interface (and others). With this extension, SQLite uses the upper/lower case tables that are part of unicode. See ext/icu/README in the source distro for details. Dan. > Keith. > >>> Sort order is highly dependent on locale. You can add custom >>> collations to do this. > > On Sat, Apr 5, 2008 at 11:58 PM, Cory Nelson <[EMAIL PROTECTED]> > wrote: > >> They are one and the same. Look up collations. >> >> On Sat, Apr 5, 2008 at 2:55 PM, Keith Stemmer >> <[EMAIL PROTECTED]> wrote: >>> That was not was I was talking about. I was not talking about >>> Sort Order >> but >>> about Searches. >>> Keith >>> >>> >>> >>> On Sat, Apr 5, 2008 at 11:42 PM, Cory Nelson <[EMAIL PROTECTED]> >>> wrote: >>> >>>> Sort order is highly dependent on locale. You can add custom >>>> collations to do this. >>>> >>>> On Sat, Apr 5, 2008 at 10:41 AM, Keith Stemmer >>>> <[EMAIL PROTECTED]> wrote: >>>>> Hello! >>>>> >>>>> I found SQLite quite amazing, but I think there is one >>>>> showstopper >> for >>>> me. >>>>> It seems that searches for Unicode strings are case sensitive and >> there >>>> is >>>>> no (easy) way around that. >>>>> Could you please confirm or deny this? >>>>> >>>>> Your explanation... >>>>> >>>>> (A bug: SQLite only understands upper/lower case for 7-bit Latin >>>> characters. >>>>> Hence the LIKE operator is case sensitive for 8-bit iso8859 >> characters >>>> or >>>>> UTF-8 characters. For example, the expression 'a' LIKE 'A' is >>>>> TRUE >> but >>>> 'æ' >>>>> LIKE 'Æ' is FALSE.). >>>>> >>>>> seems to destroy all my hopes. >>>>> >>>>> Thank you very much! >> >> -- >> Cory Nelson >> http://www.int64.org >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] default sqlite3 IO methods for VFS
On Apr 8, 2008, at 2:03 AM, Ken wrote:
> Hi all,
>
> I'm trying to implement a vfs that will disable journalling.
>
> But i've hit a little stumbling block / learning curve.
>
> In the vfs implementation I need to implement a sqlite3_io_methods
> type. Basically I'd like to get the default sqlite3_io_methods and
> use those as part of my code as follows:
>
> // IO_METHOD_OPEN
> njOpen(..) {
> if (JOURNAL) return ;
>
> default_IO_Open(...) ;
> }
>
> // VFS call
> nonJournalOpen ( ... ) {
>static sqlite3_io_methods njMethods = {
> 1,
> njOpen, ... } ;
>
> }
>
>
> How do I get the address of sqlites default I/O methods?
sqlite3_vfs *pDefaultVfs = sqlite_vfs_find(0);
Dan.
>
> Thanks for any suggestions.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] default sqlite3 IO methods for VFS
On Apr 8, 2008, at 2:16 AM, Ken wrote:
> Dan,
>
> Doesn't that just return the VFS part ??? I'm looking for the
> sqlite3_io_methods part for the default vfs ( or is this really
> the sqlite3_file )?
Right. Sorry, I didn't read carefully. :)
I don't really see why you will need it unless you do intend
to open the underlying journal file. The sqlite3_io_methods
structure contains methods you can invoke on a file only after
it has been opened (i.e. xRead(), xWrite()).
When your vfs opens "real" files, you need to call:
pDefaultVfs->xOpen(pDefaultVfs, zFile, pOut, flags, );
Where pOut is a pointer of type sqlite3_file* that points to
at least pDefaultVfs->szOsFile bytes of free space. The default
vfs implementation will populate pOut->methods with a pointer
to the sqlite3_io_methods structure that contains the methods
for the default "real" file implementation.
Dan.
>
> Thanks,
> Ken
>
>
> Dan <[EMAIL PROTECTED]> wrote:
> On Apr 8, 2008, at 2:03 AM, Ken wrote:
>
>> Hi all,
>>
>> I'm trying to implement a vfs that will disable journalling.
>>
>> But i've hit a little stumbling block / learning curve.
>>
>> In the vfs implementation I need to implement a sqlite3_io_methods
>> type. Basically I'd like to get the default sqlite3_io_methods and
>> use those as part of my code as follows:
>>
>> // IO_METHOD_OPEN
>> njOpen(..) {
>> if (JOURNAL) return ;
>>
>> default_IO_Open(...) ;
>> }
>>
>> // VFS call
>> nonJournalOpen ( ... ) {
>>static sqlite3_io_methods njMethods = {
>> 1,
>> njOpen, ... } ;
>>
>> }
>>
>>
>> How do I get the address of sqlites default I/O methods?
>
>sqlite3_vfs *pDefaultVfs = sqlite_vfs_find(0);
>
> Dan.
>
>
>
>>
>> Thanks for any suggestions.
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] error in sqlite3_extension_init
On Apr 12, 2008, at 12:36 AM, dark0s dark0s wrote:
> Excuse me for my stupid topic, but I am crazying to find error in
> program below.
>
> My output is:
>
> bash-3.1# gcc -shared labsinf.c -o inf.so
> labsinf.c:61: error: expected ';', ',' or ')' before '*' token
>
> The errror is for sqlit3_extension_init row, but I don't see
> strange things
>
> #include
> #include
> #include
> #include
> SQLITE_EXTENSION_INIT1
>
> void soundex(sqlite3_context* ctx, int nargs, sqlite3_value**
> values) {
>
> int i,j;
> char c,r;
> int d;
> int count;
> char* str2;
> char* result;
> int dim;
> const char* str;
> char ret[4];
>
> str = sqlite3_value_text(values[0]);
> dim = strlen(str);
> for (i=0;i for (i=0;i<=dim;i++) str2[i] = toupper(str2[i]);
> for (i=0;i<=dim;i++)
> switch (str[i]) {
> case 'A': case 'E': case 'I': case 'O':
> case 'U': case 'H': case 'W': case 'Y': str2[i] = '0';
> }
> for (i=1;i switch (str2[i]) {
> case 'B': case 'F':
> case 'P': case 'V': str2[i] = '1'; break;
> case 'C': case 'G':
> case 'J': case 'K':
> case 'Q': case 'S':
> case 'X': case 'Z': str2[i] = '2'; break;
> case 'D': case 'T': str2[i] = '3'; break;
> case 'L': str2[i] = '4'; break;
> case 'M': case 'N': str2[i] = '5'; break;
> case 'R': str2[i] = '6'; break;
> }
>
> count=1;
> for (i=0;i if (str2[i] != str2[i+1]) count++;
> result = malloc(count);
> j=0;
> for (i=0;i if (str2[i] != str2[i+1]) {
> result[j]=str2[i];
> j++;
> }
> for (i=0;i<4;i++) printf("%c", result[i]);
> for (i=0;i<4;i++) ret[i] = result[i];
>
> printf("\n\n");
> sqlite3_result_text(ctx,ret, 4, SQLITE_TRANSIENT);
>
> }
>
>
> int sqlite3_extension_init(
> sqlite3 *db,
> char **pzErrMsg,
> const sqlite3_api_rountines *pApi) {
That bit is wrong. ^^^
> SQLITE_EXTENSION_INIT2(pApi)
> sqlite3_create_function(db, "soundex", 1, SQLITE_UTF8, NULL,
> soundex, NULL, NULL);
> return 0;
>
> }
>
> -
> Inviato da Yahoo! Mail.
> La casella di posta intelligente.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to set memory usage as high as possible -- but not too high?
On Apr 14, 2008, at 5:53 AM, Ralf Junker wrote: > I need to create a huge database (about 6 GB, more than 6 mio > records, blobs, and FTS text) in as little time as possible. Since > memory is the key to speed, I try to use as much memory as is > available. However, there is the danger of running out of memory. > This is where memory usage control comes into play. I can see there > are two options: > > > * OPTION 1: PRAGMA cache_size = 1000; > > Advantage: SQLite will use ample memory, but no more than that. > > Disadvantage: Difficulty to establish exact memory requirements in > advance. The help states that "Each page uses about 1.5K of > memory.", but I found this to be wrong. Memory usage obviously > depends on the page size, and my measurement shows that there is an > additional small overhead of undocumented size. Is there a formula > to calculate the required memory for a cache_size of x? > > > * OPTION 2: sqlite3_soft_heap_limit(max_mem_bytes); > > Advantage: Memory limit can be set to a known value (amount of free > memory as returned from the OS). > > Disadvantage: My tests indicate that SQLite slows down drastically > when it hits the memory limit. Inserts drop from a few hundred per > second to just one or two per sec. That is an odd result. How did you test it? What was the memory limit? Any chance the machine started using swap space? > > * OPTION 3: Catch out-of-memory errors and reduce cache_size > accordingly (untested scenario). > > Advantage: Use memory up to the least bits available. > > Disadvantage: How to avoid data loss after the out-of-memory error. > Can I just call sqlite3_release_memory(some_bytes) and sqlite3_step > again and again until it passes without the out-of-memory error? > > > This raises a few questions: > > * Do sqlite3_soft_heap_limit(), or "PRAGMA cache_size=x;", or both > establish SQLite's upper memory limit? Do they work independently > of each other, i.e. does the lower limit always kick in first? Both limits can be used simultaneously. The cache_size limit is per database cache, soft_heap_limit() sets a global parameter that governs all sqlite connections opened by the process. > > * Does "PRAGMA cache_size=some_smaller_value;" cause SQLite to free > used pages and release their memory straight away? No. If the cache_size parameter is set to a value that is less than the number of pages currently allocated for the cache, no more pages will be allocated. But no existing pages will be freed. > * Is there another runtime -- important! -- setting to establish a > maximum memory limit, possibly undocumented? There is the SQLITE_MEMORY_SIZE option. But that's not really useful for the very large memory limits you're talking about. So soft_heap_limit() and pragma cache_size are it. > In the end this boils down to a simple problem: > > * Wow to keep SQLite's memory usage as close to, but not exceeding > the memory available to applications? It's not really that simple. On a workstation, not all memory is equal. The maximum amount of memory available to an application is all of the RAM + all of the swap space. Best performance probably comes by using up all of the RAM and never using the swap. Realistically, you should probably just set a large cache_size as in option 1. Does SQLite really run faster with 1GB available than it would with 100MB? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.8
On Apr 17, 2008, at 12:23 AM, Ken wrote: > Sorry I should have been more clear: > > The amalgamation for 3.5.8 is missing the ability to build sqlite3 > shell command. > > The amalgamtion is missing configure/autoconf capability as well. > Where 3.5.7 had all of this. > > I was not so much concerned between zip vs .tar.gz format, but the > content of the amalgamation. "sqlite-amalgamation-3.5.8.tar.gz" which is the file that allows you do "./configure && make install" to build from the amalgamation is up now. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] understanding EXPLAIN
On Apr 18, 2008, at 9:37 AM, P Kishor wrote: > I am trying to learn EXPLAIN. I have a table like so with ~184K rows > > sqlite> .s > CREATE TABLE sg_rivers ( > ogc_fid INTEGER PRIMARY KEY, > wkt_geometry TEXT, > name TEXT, > xmin REAL, > ymin REAL, > xmax REAL, > ymax REAL > ); > CREATE INDEX ix_xmax__sg_rivers ON sg_rivers (xmax); > CREATE INDEX ix_xmin__sg_rivers ON sg_rivers (xmin); > CREATE INDEX ix_ymax__sg_rivers ON sg_rivers (ymax); > CREATE INDEX ix_ymin__sg_rivers ON sg_rivers (ymin); > sqlite> select xmin, ymin, xmax, ymax from sg_rivers limit 3; > xmin ymin xmax ymax > - > -89. 43.642034 -89. 43.6 > -89. 43.642501 -89. 43.6 > -89. 43.642991 -89. 43.6 > sqlite> explain select wkt_geometry from sg_rivers where xmin >= -90; > addr opcode p1p2p3p4 p5 comment > - - -- > - > 0 Trace 0 0 0 explain select wkt_geometry > from sg_rivers where xmin >= -90; 00 > 1 Goto 0 20000 > 2 OpenRead 0 100943 000 > 3 SetNumColumns 0 4 000 > 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 > 5 SetNumColumns 1 2 000 > 6 Integer-90 2 000 > 7 IsNull 2 17000 > 8 MakeRecord 2 1 5 eb 00 > 9 MoveGe 1 17500 > 10Column 1 0 500 > 11IsNull 5 16000 > 12IdxRowid 1 5 000 > 13MoveGe 0 0 500 > 14Column 0 1 600 > 15ResultRow 6 1 000 > 16Next 1 10000 > 17Close 0 0 000 > 18Close 1 0 000 > 19Halt 0 0 000 > 20Transaction0 0 000 > 21VerifyCookie 0 47000 > 22TableLock 0 100943 0 sg_rivers 00 > 23Goto 0 2 000 > > Am I to understand from the above that my query is *not* using the > index ix_xmin__sg_rivers? If not, why not? I think it is. Instruction 4 opens the index. Instruction 9 seeks to the first entry in the index where (xmin>=-90). Instructions 10-15 return you a row and instruction 16 advances to the next index entry. Try EXPLAIN QUERY PLAN for output that is easier to read. > In any case, would I be better off with a compound index if I modify > my query to > > select wkt_geometry from sg_rivers where xmin >= ? and ymin >= ? and > xmax <= ? and ymax <= ?; No. The B-Tree structures that SQLite uses are not really suitable for this kind of thing. Ideally you need an R-Tree structure. In SQLite, only one of the four binary constraints in the WHERE clause could be optimized using an index. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TlsAlloc
On Apr 18, 2008, at 2:27 PM, Vlastimil Miléř wrote: > Hello everybody, > > first of all, thanks for this great library! It works beautifully, but > I have encountered a small problem when running my app in Microsoft > Application Verifier. It reported an unreleased tls slot on dll > unloading and I traced it back to one of sqlite functions. I modified > (hacked) it for myself to be able to free the slot on request, but it > would be nice if future version addressed this problem. Upgrading will probably clear this problem. As of 3.5, SQLite no longer uses TlsAlloc(). Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: > > I'll ask this question. The answer is probably "no," but I'll ask it > for the sake of completeness. > > > Suppose I created an in-memory db. I use the attach command to > associate an additional in-memory db. Suppose I assign the main db to > thread 1 and the associated db to thread 2. Can I share the connection > across the 2 threads if each thread works exclusively in its own db? > > I am aware that the connection is generally not threadsafe, but will > it work if the two threads don't operate on the same db at the same > time? As of 3.5, sqlite connections are threadsafe by default. With earlier versions, this trick will not work. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple writers for in-memory datastore
On Apr 20, 2008, at 12:29 AM, James Gregurich wrote: > > oh good! That isn't the version that ships with Leopard, but I can > live with deploying my own version as part of my app. > > Will l get the writer parallelism I'm after as long as each thread > writes exclusively into its own attached db? > > > in other wordstwo bulk insert operations going on simultaneously > on the same connection but each insert operation going into a > different attached in-memory db. Probably not. Each sqlite3* handle has a single mutex that it uses to serialize operations. Dan. > > > On Apr 19, 2008, at 9:20 AM, Dan wrote: > >> >> On Apr 19, 2008, at 6:06 AM, James Gregurich wrote: >> >>> >>> I'll ask this question. The answer is probably "no," but I'll ask it >>> for the sake of completeness. >>> >>> >>> Suppose I created an in-memory db. I use the attach command to >>> associate an additional in-memory db. Suppose I assign the main >>> db to >>> thread 1 and the associated db to thread 2. Can I share the >>> connection >>> across the 2 threads if each thread works exclusively in its own db? >>> >>> I am aware that the connection is generally not threadsafe, but will >>> it work if the two threads don't operate on the same db at the same >>> time? >> >> As of 3.5, sqlite connections are threadsafe by default. With >> earlier versions, this trick will not work. >> >> Dan. >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concerns regarding sqlite on jffs2
On May 5, 2008, at 1:59 PM, John Passaniti wrote: > I am trying to decide if sqlite is appropriate for my system. > > The target is an embedded system. It's an ARM9 processor running > under Linux 2.6. The system's primary storage is NAND flash, using > jffs2. The system is essentially a datalogger, where at regular > intervals (typically once per minute) sensor data is written. The > table schema would be very simple-- each row would have a timestamp, > and one column for each sensor. The table would be indexed on the > timestamp. Also, as time marches forward, old data (more than a year) > will be periodically deleted. > > My concern is this: I gather sqlite keeps data in an indexed tree > structure. As I write new rows to the table, I would imagine the data > has the potential to move around in the tree. That is, as I add > entries, the tree will rebalance, possibly shuffling data around. > > I guess my question is in the situation I just described, how often > will data be shuffled around? I'm not looking for some absolute > number here, but rather a sense of if a sqlite database on a jffs2 > filesystem would be spending a lot of time rebalancing trees and thus, > wearing out the flash faster. Or if such is just inherent with > sqlite, are there programming pointers people can provide that would > minimize this? Use transactions if you did not already plan to. SQLite uses the b-tree structure (search for "b-tree comer" for a good paper describing the data structure), so shuffling the tree around is done incrementally during insert/delete operations. The algorithms try hard to minimize the number of pages written by this shuffling and are usually pretty good, but the theoretical worst performance is terrible. If you are using automatically generated rowids for your table (you are unless you have declared a column as INTEGER PRIMARY KEY) and the indexed timestamps are always increasing (I guess they are, right?) then your overhead will be mimimal. You should try to measure it first to make sure though... Dan. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import files with more than 2GB?
On May 6, 2008, at 3:38 PM, Michael Lackhoff wrote: > On 5 May 2008 at 11:50, D. Richard Hipp wrote: > >> The code to do an import is not part of the core SQLite, btw. It is >> part of the CLI. You can find the code by searching for "import" in >> the shell.c source file. > > I think I somehow managed to get it working. All I had to do is add > this line: > #define _FILE_OFFSET_BITS 64 /* enable large file support */ > to the beginning of shell.c > The hint came from here: > http://lawlor.cs.uaf.edu/~olawlor/ref/examples/unix/index.html > (what would we do without Google !?!) > > Is it possible to include this (or a better equivalent) to the next > version? I hate such hacks where I don't really know what I am doing. You might have some luck if you grab cvs as of last night. Some changes were made to the configure script that might fix things. Dan. > -Michael > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Actually delete deleted rows from databasefile
On May 8, 2008, at 2:22 PM, Roar Bjørgum Rotvik wrote: > Hi, > > I see that when I perform a "delete from ..." to delete a row in > the database, the actual > data still remains in the database file. > > Creates a test database and insert some values: > # sqlite3 test.db > SQLite version 3.3.6 > Enter ".help" for instructions > sqlite> create table foo (a integer, b text); > sqlite> insert into foo values (1, "one"); > sqlite> insert into foo values (2, "two"); > sqlite> insert into foo values (3, "three"); > sqlite> .q > > # hexdump -C test.db > 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite > format 3.| > 0010 04 00 01 01 00 40 20 20 00 00 00 04 00 00 00 00 > |.@ | > 0020 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 > || > 0030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 > || > 0040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 0060 00 00 00 00 0d 00 00 00 01 03 c8 00 03 c8 00 00 > || > 0070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 03c0 00 00 00 00 00 00 00 00 36 01 06 17 13 13 01 55 > |6..U| > 03d0 74 61 62 6c 65 66 6f 6f 66 6f 6f 02 43 52 45 41 | > tablefoofoo.CREA| > 03e0 54 45 20 54 41 42 4c 45 20 66 6f 6f 20 28 61 20 |TE > TABLE foo (a | > 03f0 69 6e 74 65 67 65 72 2c 20 62 20 74 65 78 74 29 | > integer, b text)| > 0400 0d 00 00 00 03 03 e4 00 03 f8 03 ef 03 e4 00 00 > || > 0410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 07e0 00 00 00 00 09 03 03 01 17 03 74 68 72 65 65 07 > |..three.| > 07f0 02 03 01 13 02 74 77 6f 06 01 03 09 13 6f 6e 65 > |.two.one| > 0800 > > Delete row with a=2: > # sqlite3 test.db > SQLite version 3.3.6 > Enter ".help" for instructions > sqlite> delete from foo where a=2; > sqlite> select * from foo; > 1|one > 3|three > sqlite> .q > > See that sqlite does not show row 2 as it is deleted. > > # hexdump -C test.db > 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite > format 3.| > 0010 04 00 01 01 00 40 20 20 00 00 00 05 00 00 00 00 > |.@ | > 0020 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 > || > 0030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 > || > 0040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 0060 00 00 00 00 0d 00 00 00 01 03 c8 00 03 c8 00 00 > || > 0070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 03c0 00 00 00 00 00 00 00 00 36 01 06 17 13 13 01 55 > |6..U| > 03d0 74 61 62 6c 65 66 6f 6f 66 6f 6f 02 43 52 45 41 | > tablefoofoo.CREA| > 03e0 54 45 20 54 41 42 4c 45 20 66 6f 6f 20 28 61 20 |TE > TABLE foo (a | > 03f0 69 6e 74 65 67 65 72 2c 20 62 20 74 65 78 74 29 | > integer, b text)| > 0400 0d 03 ef 00 02 03 e4 00 03 f8 03 e4 03 e4 00 00 > || > 0410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > || > * > 07e0 00 00 00 00 09 03 03 01 17 03 74 68 72 65 65 00 > |..three.| > 07f0 00 00 09 13 02 74 77 6f 06 01 03 09 13 6f 6e 65 > |.two.one| > 0800 > > But the data "two" (and possibly rest of data from this deleted > row) is still intact in > the database. > > I guess this is because the data blocks used to contain the second > row (deleted row) is > now marked as deleted by sqlite and may be reused later? Correct. > I tried two more inserts into the table, but the "two" string is > still visible in the > database file, the file instead grows. What is the rule for reusing > a deleted block (if it > is so)? I guess it depends on the size of the new records inserted how space is allocated for them. Assuming they are small (i.e. 4,'four') I would have expected them to be inserted somewhere between 0x400 and 0x800. > Is it possible to configure/tweak sqlite to actually delete or at > least memset(0) a > deleted row? I need to make sure that a deleted row is actually > deleted from the database > file (at the same time as the sql command "delete" is performed), > is this possible? Compile with SQLITE_SECURE_DELETE to memset(0) deleted data. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 17, 2008, at 2:51 AM, Shawn Wilsher wrote: > And a fun follow-up question. Will sqlite3_transfer_bindings transfer > bindings across connection objects if the two statements are for two > different connections to the same database? No. It will return SQLITE_MISUSE. Dan. > > Cheers, > > Shawn > > On Tue, May 13, 2008 at 2:05 PM, Shawn Wilsher > <[EMAIL PROTECTED]> wrote: >> I was looking through the documentation and was wondering why >> sqlite3_transfer_bindings has been marked as obsolete. It's >> something >> that we use currently in our code, and I was looking to use it again >> for something new. Is there a new way to accomplish the same thing >> that this function does? What was the rational for removing it. >> >> If you need a use case for why Mozilla needs it, I'd be happy to >> oblige. >> >> Cheers, >> >> Shawn Wilsher >> Mozilla Developer >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 17, 2008, at 7:59 PM, Shawn Wilsher wrote: > On Sat, May 17, 2008 at 2:39 AM, Dan <[EMAIL PROTECTED]> wrote: >>> And a fun follow-up question. Will sqlite3_transfer_bindings >>> transfer >>> bindings across connection objects if the two statements are for two >>> different connections to the same database? >> >> No. It will return SQLITE_MISUSE. > Drat. It doesn't look like there's a way to see what's already been > bound to a statement either, correct? Not easily done at the moment. How are you going to 'clone' the statement objects to pass to the second database handle? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
On May 18, 2008, at 12:01 AM, Shawn Wilsher wrote: > On Sat, May 17, 2008 at 10:13 AM, Dan <[EMAIL PROTECTED]> wrote: >> How are you going to 'clone' the statement objects to pass to >> the second database handle? > Our wrapper around the statement object already stores the string of > the sql statement, so that part is easy. Looks like we'll have to > keep track of bound parameters as well now. By recompiling it for the new connection. Fair enough. If you use sqlite3_prepare_v2() to create a statement, you could also use sqlite3_sql() to retrieve the original text of the SQL in utf-8 encoding: const char *sqlite3_sql(sqlite3_stmt *pStmt); Dan. > Cheers, > > Shawn Wilsher > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ticket 3127 & MJ file creation
> It Looks like a Master Journal is still being created even when > journal_mode=off. > > Is the master journal file supposed to be created? Well, there's no point in it being created if that's what you mean :) It's a bit more complex than it seems though, as journal_mode=off is set on a per-pager basis. I suppose the master journal should be omitted if all of the pagers being committed are in journal_mode=off mode. Dan. > Thanks, > Ken > > > Ken <[EMAIL PROTECTED]> wrote: Ticket 3127 created. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On Jun 3, 2008, at 10:03 PM, Darko Filipovic wrote: > I've tried...(not with UFO :D ). Nothing happens, database is not > corrupted and that is what confuses me...I thought it should not be > readable (malformed) ?! When SQLite needs to modify the content of a database page, it does two things: * writes the contents of that page out to the journal (so that it can be rolled back later if necessary), and * makes the change to an in-memory copy of the page. Later on, when the transaction is committed or enough changes have accumulated in memory, all pending changes are flushed through to the file. You probably abandoned the transaction to early for this to happen - so the journal file was in the file-system, but no actual changes had been made to the database file. Try it with a really big transaction and you will see the corruption. Dan. > Greetings, > Darko F. > > > > Federico Granata wrote: >> 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: >> >> >>> But, what happen if journal file is deleted before starting B >>> process? >>> >>> >> what if a UFO stole your pc ? :-D >> >> try to delete journal file and see what happens ... >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> __ NOD32 3154 (20080603) Information __ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] crash4 test fails (3.5.9)
On Jun 4, 2008, at 2:23 PM, kgs wrote:
> kgs wrote:
>> kgs wrote:
>>
>>> kgs wrote:
>>>
>>>
>>>> Hi all,
>>>> I've compiled sqlite3 for arm-linux(armv5t) as well as the tcl
>>>> environment.
>>>> sqlite3 3.5.9
>>>> tcl 8.4
>>>>
>>>> I've run testfixture all.test on the hardware it's compiled for.
>>>> Everything runs great until we come to the crash4-1.1.1 up to
>>>> crash4-1.1000.1.
>>>> the messages are :
>>>> Expected: [1 {child process exited abnormally}]
>>>> Got: [1 {couldn't create error file for command: no such
>>>> file or
>>>> directory}]
>>>>
>>>> for each crash4-1.x.1, then :
>>>> *** Giving up...
>>>> 1000 errors out of 26715 tests
>>>>
>>>> the crash4-1.x.2 and crash4-1.x.3 return Ok.
>>>>
>>>> Any ideas as to what may be wrong ?
>>>>
>>>> Thanks in advance
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>>
>>>>
>>> So this is apparently what is happening...
>>> this code in tester.tcl :
>>> set r [catch {
>>> exec [info nameofexec] crash.tcl >@stdout
>>> } msg]
>>>
>>> is returning :
>>> 1 {couldn't create error file for command: no such file or
>>> directory}
>>>
>>> so i think that >@stdout is trying to treat stdout as an open
>>> file...
>>> but it doesn't exist.
>>>
>>> I'm guessing that stdout is supposed to be a fixed thing in
>>> tcl... and
>>> that this is supposed to take the output from the exec and pipe
>>> it to
>>> stdout.
>>>
>>> So... if this is how its supposed to work, then why isn't it ?
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> Never mind that last message, >@stdout puts the error message i'm
>> getting in "msg" through the catch.
>> So that leaves, that the testfixture command is not being found. I
>> checked what "info nameofexec" is returning, and it returns the full
>> path and name of testfixture.
>> When I run "testfixture crash.tcl", I get no error... I am confused.
>>
>> Forgive my babbling, this is my first day with tcl.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> Ok, so now I think I know what really going on... from
> http://www.tcl.tk/faq/tclwin.htm
> B-14: exec: couldn't create error file for command: Error 0
> If exec returns an error message of "couldn't create error file for
> command: Error 0", the following may help:
> This problem exists under Unix and Windows NT. I have conclusive
> proof
> that under Unix it was being caused by the temporary directory not
> being
> world writable, which explains why superusers were able to "exec"
> something but not a regular user. The "gotcha!" is that /tmp is not
> necessarily the temporary directory that is used, so people were not
> seeing an obvious problem. Tcl uses the tmpnam() system call, which on
> many systems actually uses /var/tmp, and on some systems that
> directory
> was not world writable. I would like to call this a problem with one's
> site administration.
>
> Since root access to the hardware I'm using is not attainable, how
> do I
> work around this ? Any ideas ?
> Any help would be greatly appreciated.
Maybe you could get around the problem by setting the TMPDIR environment
variable to the path of a writable directory (see [man tmpnam]). Failing
that, maybe somebody on comp.lang.tcl will know.
Dan.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
On Jun 5, 2008, at 12:55 AM, Shane Harrelson wrote: > Once you get it working with your data, you may want to play around > with the > defines at the top of rtree.c. > > >> /* Either, both or none of the following may be set to activate >> ** r*tree variant algorithms. >> */ >> #define VARIANT_RSTARTREE_CHOOSESUBTREE 0 >> #define VARIANT_RSTARTREE_REINSERT 1 >> >> /* >> ** Exactly one of the following must be set to 1. >> */ >> #define VARIANT_GUTTMAN_QUADRATIC_SPLIT 0 >> #define VARIANT_GUTTMAN_LINEAR_SPLIT 0 >> #define VARIANT_RSTARTREE_SPLIT 1 > > > These defines affect the algorithms used for manipulating the internal > R-Tree data structures, and you may see improved performance by > tuning it > for your data. The algorithms turned on by default are those that seemed to create the best R-Tree structure for the randomly generated data set that was used to test performance while writing code (see the rtree_perf.tcl script in cvs). Messing around with them might speed up INSERT operations but generate a tree structure that is a bit slower to query. That's just a guess though. If you really care about performance, it is probably worth testing a few other combinations with "real" data. Please post any results you get! Dan. > -Shane > > > On 6/4/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > >> >>> You should modify the rtree.c source file and add the following >>> before >> each >>> public function: >>>__declspec(dllexport) >>> >>> So for instance, line 2772: >>>int sqlite3_extension_init( >>> becomes: >>>__declspec(dllexport) int sqlite3_extension_init( >>> >> Thank you, I got it to work!!! >> >> Now, let´s see how we can this thing to work with my data ... >> >> >> Thank you, >> >> >> Christophe Leske >> >> www.multimedial.de - [EMAIL PROTECTED] >> http://www.linkedin.com/in/multimedial >> Lessingstr. 5 - 40227 Duesseldorf - Germany >> 0211 261 32 12 - 0177 249 70 31 >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite and C works with "like" but not with "="
On Jun 14, 2008, at 10:39 AM, Daniel White wrote: > Hi guys, > > My first post to this list, and I'm new to SQL in general too. > > Just a couple of questions to start: > > ***1: > In the sqlite3_prepare_v2 function, can someone explain > to me the 5th parameter better than the site's help can? > > I quote: > /* OUT: Pointer to unused portion of zSql */ > > I'm a bit perplexed. zSql is just the SQL query string. > There isn't an unused portion really, and even if there > was, I can't imagine how it could be of use. I just want > SQlite to process the string up to the NULL terminator. > > I tend to use 0. eg: > sqlite3_prepare_v2(database,sqlstring,-1,,0); > > It seems to 'work'. However, I'm still curious as to what > that 5th parameter really does, or why you really need it. For when you have a string containing more than one SQL statement. i.e. "INSERT INTO songs VALUES(...) ; INSERT INTO songs VALUES(...) ; ..." If this string is passed to sqlite3_prepare_v2, the unused portion begins right after the first semi-colon. > ***2: > When I use the SQL query string: > "SELECT * FROM songs WHERE SongTitle like 'hexion'" > The song title is actually 'Hexion'. By default LIKE is case- insensitive, = is not. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite and C works with "like" but not with "="
On Jun 14, 2008, at 9:08 PM, Daniel White wrote: > The course of action I thought you implied was to change > it from "hexion" to "Hexion", and so I hoped that would > return the results, but it still doesn't. > > "Still" meaning, just like the small 'h', 'H' doesn't > work either. > The answer is in the error message. You're missing a collation sequence. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite and C works with "like" but not with "="
On Jun 15, 2008, at 8:55 AM, Daniel White wrote: > Cheers both of you, it seems this problem is indeed > linked with the "no such collation sequence: iunicode" > error as Dan mentioned. > > After some research, I found out that the root of > the problem is unsurmountable at present. I quote from: > http://www.mediamonkey.com/forum/viewtopic.php?p=84197 > > "It's a real shame that simple queries like "select * > from Songs where SongTitle = 'ABC'" aren't viable. > However, SQLite doesn't have good collation included, > there's absolutely no support for Unicode sorting or > case insensitive comparisons. We will try to do something > about it, but to be honest, I don't know if there's any > easy fix..." > > ...and... > > "IUNICODE is our collation that's there in order to > support Unicode sorting - which SQLite can't do internally." > > Oh well, there are one or two 'hacks' around it. The first is > to use COLLATE BINARY, or COLLATE NOCASE after the SQL query. > This appears okay on the surface, but probably ignores > unicode chars or something. It may also slow down the query (?) > > The other idea is to simply use "LIKE 'xyz'" instead of > "= 'xyz'". To my limited knowledge, apart from the case > sensivity of the latter, these don't differ in the outcome, > because there are no % signs around the former statement. > Although it would be nice, I'm not too bothered about case > sensitivity for my purposes. > > Which solution would you guys recommend? Just using 'COLLATE BINARY' is a good idea. There is a pretty good chance that memcmp() and whatever is being used for IUNICODE are the same for the '=' operator. If you don't have any non-ASCII characters in the song names, this will almost certainly work. Using COLLATE BINARY will prevent SQLite from using any index created on the song_title column (as the index will have been created using IUNICODE). Or you could copy all the data into a new table - one that uses only the default available collation sequences: CREATE TEMP TABLE my_songs AS SELECT * FROM songs; then query my_songs instead of songs. That wouldn't help you any more than using COLLATE binary explicitly in every query though. Or you could ask the vendor for the source code to the IUNICODE collation function. Using SQLite's ICU extension to try to create an equivalent collation sequence is also possible, but a bit dangerous. If the collation sequence you create turns out to be "mostly compatible" instead of "completely compatible", then you might wind up with segfaults or a corrupted database at some point in the future. Dan. > Cheers, Dan > > > >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? > > Correct. It's weird I know. I also tried with different > names in different fields (Artist etc.), and I get the > same problem. "like" is okay, but = doesn't work. > > Here are the files again: > http://www.skytopia.com/stuff/MMdatabase.zip (1.6 M) > http://www.skytopia.com/stuff/sqlite.cpp (1k) > > Dan > > > On Sat, 14 Jun 2008 15:54:50 +0100, Igor Tandetnik > <[EMAIL PROTECTED]> > wrote: > >> "Daniel White" <[EMAIL PROTECTED]> >> wrote in message news:[EMAIL PROTECTED] >>>> Which way is it stored in the database? Show the output of this >>>> statement: >>>> >>>> SELECT SongTitle FROM songs WHERE SongTitle like 'hexion'; >>> >>> There are 8 records of Hexion in the database, so after a printout >>> to the console with a carriage return after each value, I basically >>> get: >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >>> Hexion >> >> And you are saying the statement >> >> SELECT SongTitle FROM songs WHERE SongTitle='Hexion'; >> >> doesn't return any rows? With all due respect, I find it difficult to >> believe. Would it be possible for you to email a copy of the database >> file to me at [EMAIL PROTECTED] ? Perhaps a stripped-down version, >> with just enough data to reproduce the problem. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > www.skytopia.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integrity_check: Beating the system
> All: > I need to check a database for readability before my application > starts. I was originally going to keep an MD5 on the database and > check it each time at powerup. This seems to take a great deal of > time so instead I thought abou having the database do an integrity > check at powerup, however this too takes a great deal of time. My > last idea was to issue a set of simple select statements against the > database and check if they are successful. I realize this doesnt > 'guarantee' my data is fine like an MD5 would, nor does it really > validate the integrity of the database like an integrity check > would but can I assume to some degree of comfort that if these > select statements succeed then I can access these tables in the > database error free? Probably. But there is always the possibility that an index has become corrupted. Linear scans of database tables won't touch the indexes, so you won't know for sure. Another option, which is clearly marked as an experimental feature in the source code (interpret that as you will), is "PRAGMA quick_check". This is similar to integrity_check, but not as rigorous or time consuming. Dan. > I ran some test cases (I know you guys and gals like actual numbers > and not theory), and came up with the following > sqlite3 'pragma integrity_check' > real0m 11.20s > user0m 1.85s > sys 0m 8.70s > > md5sum -c ... > real0m 10.07s > user0m 1.32s > sys 0m 8.16s > > sqlite3 'SELECT STATEMENTS FROM IMPORTANT TABLES' > real0m 2.34s > user0m 0.19s > sys 0m 1.74s > > > Now for clarity The database contains AV metadata and data. I > have tables such as ImageData, ImageDescription, AudioData, and so on. > I also have tables that are not 'important' (suffice it to say, I > wont access them during runtime under normal conditions). I care > about the AV data being accessible I am optimistically assuming > that if the data is corrupt, the image will still display (with > perhaps some bad pixels) and / or the audio has some unwanted clicks > or pops. > > > The big question Using a group of select statements to check the > database for accessibility seem reasonable? Or am I taking a large > gamble doing so? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem using Attach to insert data from another table
On Jun 18, 2008, at 9:07 AM, danjenkins wrote:
>
> Hi. I battling my way through the Attach command and I can't get
> my simple
> test to work.
>
> Given a database named "ultra2008.sql" that contains a table named
> "ultra"
> and a database named "archive2007.sql" that also contains a table
> named
> "ultra" where the databases and tables have identical structures,
> shouldn't
> these statements insert ultra's data from archive2007.sql into
> ultra2008.sql?
> (For brevity I've removed the error checking lines for this posting.)
>
> sqlite3 *pDB = NULL;
> sqlite3_open("ultra2008.sql", );
> sqlite3_exec(pDB, "attach 'archive2007.sql' as arc", NULL, NULL,
> NULL);
> sqlite3_exec(pDB, "insert into main.ultra select * from arc.ultra",
> NULL,
> NULL, NULL);
> sqlite3_close(pDB);
>
> After this function runs, the databases are left with the same data
> inside
> of them so something isn't right.
Check the error codes returned by the two sqlite3_exec calls. If
one is not SQLITE_OK, examine the string returned by sqlite3_errmsg().
Dan.
>
> I appreciate your input.
> -Dan
>
> --
> View this message in context: http://www.nabble.com/Problem-using-
> Attach-to-insert-data-from-another-table-tp17958315p17958315.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] configure syntax error on HP
On Jun 19, 2008, at 10:49 PM, Matt Sergeant wrote: > On Wed, 18 Jun 2008 19:58:02 -0400, D. Richard Hipp wrote: >> >> On Jun 18, 2008, at 7:12 PM, Andrea Connell wrote: >> >>> I want to use the C API with a C++ class but when I try compiling... >>> >>> $ aCC -AA +W829 main.cpp sqlite3.c >>> main.cpp: >>> sqlite3.c: >>> Error 482: "sqlite3.c", line 532 # Array of unknown size; 'const >>> char >> >> SQLite is written in C, not C++. You have to use a C compiler to >> compile it. If you compile to object code, you can normally link it >> against C++ code without difficulty. But you cannot compile SQLite >> directly using a C++ compiler. > > Note that there are some C++ style comments crept back into the > code (I > noticed in the amalgamation, so I can't give you a direct pointer to > them). This causes compile failures on stricter C compilers. I think this problem was fixed by [5207]: http://www.sqlite.org/cvstrac/chngview?cn=5207 Please post a bug report or a message here if there are others that need to be removed. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] configure syntax error on HP
On Jun 21, 2008, at 1:27 AM, Matt Sergeant wrote: > On Thu, 19 Jun 2008 12:05:56 -0400, D. Richard Hipp wrote: >> >> On Jun 19, 2008, at 11:49 AM, Matt Sergeant wrote: >>> >>> Note that there are some C++ style comments crept back into the code >>> (I >>> noticed in the amalgamation, so I can't give you a direct pointer to >>> them). This causes compile failures on stricter C compilers. >> >> >> Already been fixed. http://www.sqlite.org/cvstrac/chngview?cn=5207 >> and http://www.sqlite.org/cvstrac/tktview?tn=3172 > > Good. You might want to consider adding a test for this - my > DBD::SQLite does one, but I'd rather you catch things upstream. How do you test this? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
On Jun 23, 2008, at 10:38 PM, Andrea Connell wrote: > >> Have you tried compiling with the profiler and seeing where the >> time is > being spent? > > I compiled with the profiler and used prof to analyze the mon.out > file. > The program took 47 seconds to run, but the results only account > for .39 > seconds Most likely all the time is being spent in IO related system calls - read(), write() and fsync(). Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Erratum
On Jun 24, 2008, at 1:55 PM, Richard Klein wrote: > On the page describing the various sqlite3_bind_xxx_yyy() functions: > > http://www.sqlite.org/c3ref/bind_blob.html > > 3rd paragraph: > > "The index for named parameters can be looked up using the > sqlite3_bind_parameter_name() API if desired." > > should be: > > "... sqlite3_bind_parameter_index() ..." > Thanks. http://www.sqlite.org/cvstrac/chngview?cn=5292 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug with NULL in NOT IN
On Jun 26, 2008, at 4:49 AM, Dennis Cote wrote: > D. Richard Hipp wrote: >> >> If I understand Peter correctly, he is saying that NULL should mean >> "unknown" in the context of the RHS of a NOT IN operator. SQLite >> does >> not currently operate this way. SQLite currently interprets a >> NULL in >> the RHS of a NOT IN operator to mean "nothing". >> >> Can you or anybody else point to text in any >> SQL spec that would suggest that SQLites behavior in this case is >> wrong? > > > I believe that your interpretation is correct, and that SQLite's > current > behavior is incorrect according to the standard, at least the SQL:1999 > standard. > > The IN predicate is a synonym for the quantified predicate = ANY, or = > SOME which is equivalent. The inversion of this predicate, NOT IN, is > therefore the inversion of the quantified predicate. The rules defined > in section 8.4 Syntax Rules 3 and 4 show how this is > transformed. > >col NOT IN subquery > > becomes > >NOT col IN subquery > > which becomes > >NOT (col = SOME subquery) > > And the inversion of the quantified predicate is > >col <> ALL subquery > > In the standard the rules for evaluating this quantified subquery > are in >section 8.8 General Rules 1 > through > 2e copied below. > > 1) Let R be the result of the and let T be > the result of the . > 2) The result of ‘‘R T’’ is derived by the > application of the implied ‘‘R > RT’’ to > every row RT in T: > Case: > a) If T is empty or if the implied is > true for every row RT in T, then ‘‘R T’’ is true. > b) If the implied is false for at > least > one row RT in T, then ‘‘R T’’ is false. > c) If the implied is true for at least > one row RT in T, then ‘‘R T’’ is true. > d) If T is empty or if the implied is > false for every row RT in T, then ‘‘R T’’ is false. > e) If ‘‘R T’’ is neither true nor > false, > then it is unknown . > > For the example given, the engine evaluates the following predicates. > >1 <> ALL (NULL, 3, 4, 5) >2 <> ALL (NULL, 3, 4, 5) >3 <> ALL (NULL, 3, 4, 5) > > In each case the first comparison is > >X <> NULL > > And from 8.2 General Rules 1a we have > > a) If either XV or YV is the null value, then > X Y > is unknown. > > The results of the comparisons are therefore: > >(unknown, true, true, true) >(unknown, true, true, true) >(unknown, false, true, true) > > So all three rows result in an unknown result for the first, NULL, > element. The last row is handled buy case b in section 8.8 General > Rule > 2 above since one subquery result is false. The other rows all fall > through to case e in section 8.8 General Rule 2 above. The result of > each quantified comparison is therefore unknown. > > The where clause only returns rows where the condition is true > according > to section 7.8 General Rule 1. > > 1) The is applied to each row of T. The result > of the is a table of those rows of T for which the > result > of the is true. > > It looks like this should be changed to match the other database > engines > for improved standard compliance. Wow (applauds). That was incredible! So the upshot is that if a set used with "NOT IN" contains a NULL, the "NOT IN" operation will never evaluate to true. It may evaluate to false, it may evaluate to NULL. But never true. If we have the expression: "x NOT IN (NULL, y, z)" this should be equivalent to what SQLite does for: "CASE WHEN x NOT IN (y, z) THEN NULL ELSE 0 END" (assuming x is itself not NULL). Is that correct? Do we also have a similar problem with the regular 'IN' operator? In SQLite at the moment: SQLite version 3.6.0 sqlite> select 1 IN (null, 2, 3), 2 IN (null, 2, 3); 0, 1 Should the leftmost column of the result row should be NULL, not "0"? Since rule (d) above is not true for "1 IN (null, 2, 3)", do we fall through to rule (e) and return NULL? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree module crashes
On Jun 29, 2008, at 3:02 AM, Hartwig Wiesmann wrote: > When I compile SQLite using SQLITE_ENABLE_RTREE set to 1 SQLite will > crash when opening a database (Mac OSX). The reason seems to be that > in rtree.c sqlite3ext.h is included instead of sqlite3.h. This can be > prevented by setting SQLITE_CORE to 1 but then the types i64, u8 etc. > are undefined. > > So, my solution: > > SQLITE_ENABLE_RTREE set to 1 > SQLITE_CORE set to 1 > and define i64, u8 etc. in all cases. > > Did I do anything wrong? That will probably work. The problem with the i64, u8 types not being defined was fixed here: http://www.sqlite.org/cvstrac/chngview?cn=5282 After this fix, you should be Ok with just SQLITE_ENABLE_RTREE and SQLITE_CORE defined. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and updating VIEWs
UPDATE and INSERT. So long as one doesn't UPDATE the table's primary key. Still not sure how to support DELETE operations. I'm starting to wonder if using an SQL view and triggers is the best way to implement this. It seems pretty straightforward to support INSERT, but UPDATE and DELETE start getting a bit complicated. It could be that the logic for distributing records between the temporary and real tables would be better done using a procedural programming language. Either as part of application logic or as a virtual table. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] patch to allow integer rtree keys
On Jul 12, 2008, at 2:42 AM, Steve Friedman wrote:
>
>
> Filip Navara wrote:
>> how about actually attaching the patch? :)
>>
>> - Filip
>>
>> On Fri, Jul 11, 2008 at 9:23 PM, Steve Friedman
>> <[EMAIL PROTECTED]> wrote:
>>> I've just started using the rtree extension, and have found that
>>> the 32-bit
>>> float for the range keys is not appropriate for me. Please find
>>> attached a
>>> patch for rtree.c (based on v1.5) that allows for int -OR-
>>> unsigned int -OR-
>>> float operation.
What kind of advantages does using int over float have here?
With a little work it might be possible to select int or float at
runtime. Do other people who know about such things think that this
would be a good option to have?
Dan.
>>> Steve Friedman
>
> Not sure where it got deleted (since my inbox shows the attachment).
> Included inline...
>
> --- rtree.c 2008-07-11 15:04:42.0 -0400
> +++ rtreemod.c 2008-07-11 15:04:31.0 -0400
> @@ -149,13 +149,36 @@
> RtreeConstraint *aConstraint; /* Search constraints. */
> };
>
> +#if defined( SQLITE_RTREE_TYPE_INT)
> +typedef int ConstraintType;
> +# define sqlite3_result_ConstraintType sqlite3_result_int
> +# define sqlite3_value_ConstraintType(x) ((int) sqlite3_value_int
> ((x)))
> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
> + sqlite3_snprintf( (a), (b), " %d", (c))
> +
> +#elif defined(SQLITE_RTREE_TYPE_UINT)
> +typedef u32 ConstraintType;
> +# define sqlite3_result_ConstraintType sqlite3_result_int64
> +# define sqlite3_value_ConstraintType(x) ((u32)
> sqlite3_value_int64((x)))
> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
> + sqlite3_snprintf( (a), (b), " %u", (c))
> +
> +#else
> +typedef float ConstraintType;
> +# define sqlite3_result_ConstraintType sqlite3_result_double
> +# define sqlite3_value_ConstraintType(x) ((float)
> sqlite3_value_double((x)))
> +# define sqlite3_snprintf_ConstraintType( a, b, c) \
> + sqlite3_snprintf( (a), (b), " %f", (double) (c))
> +#endif
> +
> +
> /*
> ** A search constraint.
> */
> struct RtreeConstraint {
> int iCoord; /* Index of constrained
> coordinate */
> int op; /* Constraining operation */
> - float rValue; /* Constraint value. */
> + ConstraintType rValue;/* Constraint value. */
> };
>
> /* Possible values for RtreeConstraint.op */
> @@ -198,7 +221,7 @@
> */
> struct RtreeCell {
> i64 iRowid;
> - float aCoord[RTREE_MAX_DIMENSIONS*2];
> + ConstraintType aCoord[RTREE_MAX_DIMENSIONS*2];
> };
>
> #define MAX(x,y) ((x) < (y) ? (y) : (x))
> @@ -211,14 +234,14 @@
> static int readInt16(u8 *p){
> return (p[0]<<8) + p[1];
> }
> -static float readReal32(u8 *p){
> +static ConstraintType readReal32(u8 *p){
> u32 i = (
> (((u32)p[0]) << 24) +
> (((u32)p[1]) << 16) +
> (((u32)p[2]) << 8) +
> (((u32)p[3]) << 0)
> );
> - return *(float *)
> + return *(ConstraintType *)
> }
> static i64 readInt64(u8 *p){
> return (
> @@ -243,9 +266,9 @@
> p[1] = (i>> 0)&0xFF;
> return 2;
> }
> -static int writeReal32(u8 *p, float f){
> +static int writeReal32(u8 *p, ConstraintType f){
> u32 i;
> - assert( sizeof(float)==4 );
> + assert( sizeof(ConstraintType)==4 );
> assert( sizeof(u32)==4 );
> i = *(u32 *)
> p[0] = (i>>24)&0xFF;
> @@ -543,7 +566,7 @@
> /*
> ** Return coordinate iCoord from cell iCell in node pNode.
> */
> -static float nodeGetCoord(
> +static ConstraintType nodeGetCoord(
> Rtree *pRtree,
> RtreeNode *pNode,
> int iCell,
> @@ -721,8 +744,8 @@
> for(ii=0; iinConstraint; ii++){
> RtreeConstraint *p = >aConstraint[ii];
>
> -float cell_min = cell.aCoord[(p->iCoord>>1)*2];
> -float cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
> +ConstraintType cell_min = cell.aCoord[(p->iCoord>>1)*2];
> +ConstraintType cell_max = cell.aCoord[(p->iCoord>>1)*2+1];
> assert( cell_min<=cell_max );
>
> switch( p->op ){
> @@ -769,7 +792,7 @@
> nodeGetCell(pRtree, pCursor->pNode, pCursor->iCell, );
> for(ii=0; iinConstraint; ii++){
> RtreeConstraint *p = >aConstraint[ii];
> -float cell_val = cell.aCoord[p->iCoord];
> +ConstraintType cell_val = cell.aCoord[p->iCoord];
> int res;
> switch( p->op )
Re: [sqlite] IOERR_DIR_FSYNC on main db
On Jul 15, 2008, at 4:46 AM, Ken wrote:
> Preprocessor Macro:
>#if defined (_AIX)
>
> Looks like the MJ file is not getting created, sqlite 3.5.9 This is
> also reproducible via the command line: Possibly an issue with the
> journal mode code that was introduced.
> access("/home/ixion/ix_propagator/data/db/ajax102/batch/bat_28.db-
> mj7DF9F4DF", 0) Err#2 ENOENT
> open("/home/ixion/ix_propagator/data/db/ajax102/batch/bat_28.db-
> mj7DF9F4DF", O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE) = 9
That bit above is creating the master journal file.
> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY|
> O_LARGEFILE) = 10
> kfcntl(10, F_GETFD, 0x) = 0
> kfcntl(10, F_SETFD, 0x0001) = 0
> kfcntl(9, F_GETFD, 0x) = 0
> kfcntl(9, F_SETFD, 0x0001) = 0
> fstatx(9, 0x2FF21170, 128, 010) = 0
> klseek(9, 0, 0, 0x) = 0
> kwrite(9, " / h o m e / i x i o n /".., 66) = 66
> klseek(9, 0, 66, 0x)= 0
> kwrite(9, " / h o m e / i x i o n /".., 74) = 74
> fsync(9)= 0
> fsync(10) Err#9 EBADF
The block above is syncing the master journal file, and the directly
it is stored in. An error occurs, so SQLite decides to attempt to
roll the transaction back. This also involves syncing the directory
containing the master journal, which fails a second time:
> unlink("/home/ixion/ix_propagator/data/db/ajax102/batch/bat_28.db-
> mj7DF9F4DF") = 0
> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY|
> O_LARGEFILE) = 9
> kfcntl(9, F_GETFD, 0x) = 0
> kfcntl(9, F_SETFD, 0x0001) = 0
> fsync(9)Err#9 EBADF
> close(9)= 0
> close(6)= 0
> close(5)= 0
> unlink("/home/ixion/ix_propagator/data/db/ajax102/batch/bat_28.db-
> journal") = 0
> kfcntl(3, 12, 0x2FF211F0) = 0
> kfcntl(3, 12, 0x2FF211F0) = 0
> kfcntl(3, 12, 0x2FF210F0) = 0
> close(8)= 0
> close(7)= 0
> unlink("/home/ixion/ix_propagator/data/db/ajax102/batch/../db/
> ixp_15a6.db-journal") = 0
> kfcntl(4, 12, 0x2FF211F0) = 0
> kfcntl(4, 12, 0x2FF211F0) = 0
> kfcntl(4, 12, 0x2FF210F0) = 0
> kwrite(1, 0xF0373B18, 26) = 26
> kfcntl(1, F_GETFL, 0x20002398) = 2
> kwrite(1, 0xF0373B18, 8)= 8
> kfcntl(1, F_GETFL, 0x20002398) = 2
> kread(0, 0xF036FAF0, 4096) = 1
So it looks like Richard is correct - the version of AIX you are
using just
doesn't support fsync() on directories. Probably the code that does this
should just be disabled on AIX (I guess using the _AIX macro).
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IOERR_DIR_FSYNC on main db
On Jul 15, 2008, at 9:00 AM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Ken wrote:
>
>> open("/home/ixion/ix_propagator/data/db/ajax102/batch", O_RDONLY|
>> O_LARGEFILE) = 10
>> kfcntl(10, F_GETFD, 0x) = 0
>> kfcntl(10, F_SETFD, 0x0001) = 0
>> fsync(10) Err#9 EBADF
>
> My best guess is that the call is rejected is because the directory
> was
> not opened for writing.
> http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/libs/
> basetrf1/fsync.htm
>
> In the error code section:
>
> EBADF The FileDescriptor parameter is not a valid file descriptor
> open
> for writing.
>
> (I doubt there is an modern UNIX that does allow opening
> directories for
> write though).
Ah... Quite possibly. In os_unix.c directories are opened with:
fd = open(zDirname, O_RDONLY|O_BINARY, 0);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with BLOBs
On Jul 17, 2008, at 10:47 AM, Nolan Darilek wrote: > Hello. I'm trying to add BLOB support to a Ruby interface to > SQLite3 and > am running into an issue. I've created a column of type BLOB and am > writing ruby strings of binary data, but it seems like sqlite might be > null-terminating these strings. Specifically, when I check the > length of > the string in my program, it is 25. When it's written to the database > and read back, though, it's 2. Character 3 is a null. > > In looking at the type support documentation, it would appear that I'm > supposed to use the X"ABCD" notation for entering BLOB literals. Could > this be why my blobs are being null-terminated, since it looks as > if the > problem isn't in ruby? If so, Can anyone explain what is meant by > X"ABCD" notation? Is this a string in the form 'X"mybinaryliteral"'? I > tried that from the command line, and the string is entered as it > appears. Is it a regular string with an X before it? I also tried this > and X was an unrecognized token. It's a hexadecimal representation of a blob of data. The literal X'ABCD' is a blob of length 2 bytes. The first byte is 0xAB, the second is 0xCD. If possible, it is easier to use sqlite3_bind_blob() (or whatever the ruby equivalent is) to insert binary data. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree woes with SQLITE_OMMIT_...
On Jul 19, 2008, at 1:50 AM, Ralf Junker wrote: > I am running the rtree module against an SQLite build which has > lots of functionality SQLITE_OMIT_...ed. Can you be more specific? Exactly which SQLITE_OMIT symbols are defined? > Surprisingly, I receive strange errors like SQLITE_NOMEM for simple > statements like > > CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2); > > or > > SELECT ii FROM t6 WHERE x1>2; > > Question: Does the rtree module rely on some SQLite functionality > which I might have omitted? > > I notice that rtree uses ALTER TABLE without checking for > SQLITE_OMIT_ALTERTABLE. Even though this is not causing me problems > right now, it might help to exclude alter table functionality from > rtree.c conditinally or issue an appropriate error when called. > > Btw, ALTER TABLE also applies to FTS3. Is this worth an extra > thread or even a ticket? Rtree uses ALTER TABLE as part of the xRename() callback. The xRename() callback is invoked when the sqlite user does an ALTER TABLE. So, the rtree code that uses ALTER TABLE will never be invoked when the module is being used by an sqlite build that does not support ALTER TABLE. I suspect the same is true of fts3. It looks like there are a few extra lines of code that could be omitted from the build when SQLITE_OMIT_ALTERTABLE is defined though. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Amount of memory for caching one page with x byte page size?
On Jul 21, 2008, at 2:10 AM, [EMAIL PROTECTED] wrote: > > Hello, > > to answer the following question: > > Amount of memory for caching one page with x byte page size? > > I found in the Draft 3.6.0 Doc the following information: > > > PRAGMA page_size = bytes; > > Query or set the page size of the database. > The page size may only be set if the database has not yet been > created. > The page size must be a power of two greater than or equal to 512 and > less than or equal to SQLITE_MAX_PAGE_SIZE. > The maximum value for SQLITE_MAX_PAGE_SIZE is 32768. > > > > PRAGMA default_cache_size = Number-of-pages; > > Query or change the maximum number of database disk pages that SQLite > will hold in memory at once. > Each page uses 1K on disk and about 1.5K in memory. ... > > > Obviously you have an overhand per page-size to calculate the memory > requirement for caching on page. > > Do I understand this right? Right. When a page is loaded into the cache, SQLite allocates memory for the page data (1024 bytes, or whatever size you are using) and also memory for a structure full of other variables required by each cached page (page number, dirty flag, is-journalled flag, various pointers for linked lists, lots of stuff). The overhead probably isn't quite 512 bytes, but as a rule of thumb this formula works Ok. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] busy_timeout and shared_cache
On Jul 21, 2008, at 5:46 AM, Daniel Önnerby wrote: > Hi all! > > I usually set the sqlite3_busy_timeout to 10 seconds or something like > that to make sure that my db isn't locked by any other connection > at the > same time. This way I usually do not need to check for SQLITE_BUSY. > Now I just tried out the sqlite3_enable_shared_cache and has enabled > shared cache on 3 different threads connected to the same db. > The funny thing is that now the bust_timeout seems to fail. Instead > sqlite3_step will now return SQLITE_LOCKED every now and then (and > I can > assure you that the timeout has not been reached). > > Is this a bug, or is this an undocumented expected behavior? The busy-handler is never called when a shared-cache client cannot procede because of a transaction, table or schema lock held by another client of the same shared cache. See section 2 of this: http://www.sqlite.org/sharedcache.html for details on those three types of locks. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
On Jul 21, 2008, at 8:10 PM, D. Richard Hipp wrote:
>
> On Jul 21, 2008, at 8:32 AM, C. Smith wrote:
>>
>> I didn't know the strings weren't nul terminated. I changed my
>> callback to:
>>
>> static int _cmp(void *pCtx, int alen, const void *a,
>> int blen, const void *b)
>> {
>> int r = _wcsnicmp((const wchar_t *)a, (const wchar_t *)b,
>> (alen < blen) ? alen : blen);
Maybe the length is still wrong. The lengths passed to an sqlite
collation sequence callback are in bytes. But _wcsnicmp() is
probably in characters, no?
Dan.
>>
>> if(r == 0)
>> r = alen - blen;
>>
>> return r;
>> }
>>
>> Still doesn't work though :(
>>
>
> How did you register the collating sequence? Did you use the
> SQLITE_UTF16_ALIGNED argument on the 3rd parameter?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hard time with blobs
On Jul 25, 2008, at 10:35 AM, Sherief N. Farouk wrote: > I want to use blobs to store binary objects in the database, but > I'm having > a hard time understanding how they work. First of all, I don't see > a way to > set a blob's size, and sqlite3_blob_write doesn't increase the size > of the > blob, which is putting me in a weird catch-22 situation. > > What's the best solution for storing a bunch of binary objects, > given the > two alternatives: They may be immutable (write once, read many), or > mutable > (write many, possibly overwriting regions or appending, write many)? You can work with blobs without using the sqlite3_blob_XXX() APIs. Insert blobs by binding them with sqlite3_bind_blob(), retrieve them using sqlite3_column_blob(). Sometimes it is better to use the sqlite3_blob_XXX() APIs because they allow sqlite to work with large blobs without creating a copy of the blob in memory. To create a large zeroed blob in the database, use either sqlite3_bind_zeroblob() or the "zeroblob" SQL function. Then populate it using sqlite3_blob_XXX() after it has been inserted. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hard time with blobs
On Jul 25, 2008, at 12:05 PM, Sherief N. Farouk wrote: > I'm having a problem with the last parameter, the function used to > dispose > of the blob. Does sqlite defer actually inserting the blob into the > database > somehow? In other words, will I get into trouble if I do: > > Sqlite3_bind_blob(Statement, 1, BlobData, BlobSize, do_nothing); > Delete [] BlobData; > Use SQLITE_TRANSIENT. Dan. > ? > > - Sherief > >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:sqlite-users- >> [EMAIL PROTECTED] On Behalf Of Dan >> Sent: Friday, July 25, 2008 12:51 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Hard time with blobs >> >> >> On Jul 25, 2008, at 10:35 AM, Sherief N. Farouk wrote: >> >>> I want to use blobs to store binary objects in the database, but >>> I'm having >>> a hard time understanding how they work. First of all, I don't see >>> a way to >>> set a blob's size, and sqlite3_blob_write doesn't increase the size >>> of the >>> blob, which is putting me in a weird catch-22 situation. >>> >>> What's the best solution for storing a bunch of binary objects, >>> given the >>> two alternatives: They may be immutable (write once, read many), or >>> mutable >>> (write many, possibly overwriting regions or appending, write many)? >> >> You can work with blobs without using the sqlite3_blob_XXX() APIs. >> Insert >> blobs by binding them with sqlite3_bind_blob(), retrieve them using >> sqlite3_column_blob(). >> >> Sometimes it is better to use the sqlite3_blob_XXX() APIs because >> they >> allow sqlite to work with large blobs without creating a copy of the >> blob in memory. To create a large zeroed blob in the database, use >> either sqlite3_bind_zeroblob() or the "zeroblob" SQL function. Then >> populate it using sqlite3_blob_XXX() after it has been inserted. >> >> Dan. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite Endianness
On Jul 25, 2008, at 10:50 PM, Dave Gierok wrote: > Does anyone know what endianness a Sqlite database is stored in? > Or does that depend on the endianness of the machine it was created > on? I am wondering because we ship a game that runs on Xbox 360, > which uses the PowerPC architecture and has a different endianness > than the PC. We run PC tools to create our database for the game. > I wonder if we are paying a performance overhead on the Xbox if the > database essentially needs to be 'converted' runtime. All the various integer values in sqlite are stored in big-endian format. The way they are encoded/decoded, I don't think there will be any difference at all between big and little-endian platforms. And even if you tried to speed it up by taking advantage of a big-endian processor, I don't think it would make much difference in the long run. If you are using utf-16 encoding for your database, SQLite lets you choose between little and big-endian encoding. All text values in the database are stored using the same encoding, which is set when the database is created. It might be worth trying to make sure you use the "native" utf-16 encoding for the target platform. Otherwise, your calls to sqlite3_column_text16() will be just a little bit slower. See the docs for "pragma encoding" for details. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and database lock
On Jul 28, 2008, at 10:18 PM, Sébastien Escudier wrote: > > > Hi > > I'd like to test thread safeness, so I created a test program which : > - spawns two threads (let call them R thread and W thread) > - R tread execute BEGIN EXCLUSIVE; and sleep for a long time > - Then W thread tries to write in the database and... succeeds > > sqlite3_threadsafe returns 1 and I don't change sqlite config at > runtime. > > Can you explain me why this can happen ? > I am using sqlite v. 3.5.9 Post the test program and somebody will tell you. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and database lock
On Jul 28, 2008, at 10:58 PM, Sébastien Escudier wrote: > Quoting Dan : >> Post the test program and somebody will tell you. > > ok, you can find my test source here : > http://cjoint.com/data/hCr0WHZYHf.htm > and create test.bdd with : CREATE TABLE test(test INTEGER); > > I have this output : > ./base_test > sqlite version : 3.5.9 > threadsafe = 1 > DEBUG : sleep after begin exclusive > R thread ok > > W thread ok > write done > DEBUG : end sleep after begin exclusive > > But I expected to have "write done" after "end sleep" Both of your threads are using the same database handle, no? Executing a "BEGIN EXCLUSIVE" would lock out a second database handle. The database lock is a property of the database handle, not the thread from which the "BEGIN EXCLUSIVE" happened to be executed. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
Do things improve any if you increase the temporary cache size? Compile with -DSQLITE_DEFAULT_TEMP_CACHE=100 or something? How much memory does the [sort] process consume in the shell version? What percentage of records are being trimmed by the first [uniq] in the pipeline? Dan. On Jul 29, 2008, at 1:26 PM, Robert Citek wrote: > Was doing some DB operations and felt they were going slower than they > should. So I did this quick test: > > $ time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | > uniq | wc -l > 209 > real 5.64 > user 5.36 > sys 1.51 > > $ time -p sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 209 > real 29.71 > user 26.09 > sys 1.32 > > Why the difference in time? > What can I do to make the DB operate closer to the times within the > shell? > > Regards, > - Robert > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
On Aug 27, 2008, at 4:03 PM, Hardy, Andrew wrote: > Is there a performance hit assosiated with manifest typing? Yes. No. Possibly. Difficult to tell unless somebody creates and optimizes a version of sqlite that does not do manifest typing. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the > case? Correct. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error A0A
On Sep 8, 2008, at 1:47 PM, [EMAIL PROTECTED] wrote: > > I'm using multiple connections to sqlite database from more threads > in my application. Few customers reports sqlite error in windows > vista which I cannot get in my system. > Error code is 0xA0A (I haven't turned on extended error codes). > This error number gives no sense to me because it is not defined in > extended error codes too. > Most of times it occurs when I'm trying to run "Commit" command, > sometimes while SELECT. > Anybady knows what can be the problem? 0xA0A is SQLITE_IOERR_DELETE. Indicates a delete operation has failed. Could be something to do with anti-virus software preventing a journal file from being deleted. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite 3.6.2 tests on Mac OS X
On Sep 8, 2008, at 9:46 PM, Jens Miltner wrote: > Hi, > > I just merged our code base with the sqlite 3.6.2 distribution and > decided to run the tests on Mac OS X (10.5.4). I had two issues: > > (1) make tests would not properly link the testfixture - it complains > about missing Tcl symbols - I guess the libtool link phase does not > know about the Tcl.framework (I also tried with 3.6.1 and get the same > errors) > > (2) when I finally built testfixture using a custom Xcode project, the > btree tests would fail, eventually causing the testfixture to crash: > >> ... >> btree-5.1... Ok >> btree-5.2... Ok >> btree-5.3... Ok >> btree-5.4... Ok >> btree-5.5... Ok >> btree-5.6... Ok >> btree-6.1... Ok >> btree-6.2... Ok >> btree-6.2.1... Ok >> btree-6.2.2... >> Error: SQLITE_CORRUPT >> btree-6.2.3... >> Error: can't read "::c2": no such variable >> btree-6.3... >> Expected: [2] >> Got: [1] >> btree-6.3.1... Ok >> btree-6.4... >> Error: can't read "::c2": no such variable >> btree-6.5... Ok >> btree-6.6... >> Error: can't read "::c2": no such variable >> btree-6.6.1... Ok >> btree-6.7... >> Error: SQLITE_CORRUPT >> btree-6.7.1... >> Expected: [1] >> Got: [0] >> btree-6.8... >> Expected: [2] >> Got: [3] >> btree-6.8.1... Ok >> btree-6.9... >> Error: SQLITE_CORRUPT >> btree-6.10... >> Error: can't read "::c2": no such variable >> btree-6.11...Segmentation fault > > > Now, this might be of course an artefact of my custom build of the > testfixture, but since I use almost identical build settings to build > our actual sqlite library, this might hint at potential problems in > our build of sqlite in our application... > > Did anybody successful build and run the sqlite tests on Mac OS X > 10.5.x lately? Those tests are from test file btree.test which was removed earlier this year. Looks like you need to remove some files from your repository. btree.test used to invoke the btree interface directly instead of using the public interface to sqlite. The failures (and even the segfault) are very likely just artifacts of an out of date test script. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite test suite - where is it?
On Sep 8, 2008, at 11:05 PM, Ribeiro, Glauber wrote: > Sorry for the newbie question, but how is the regression testing > invoked? > > I tried "make check" with the 3.6.2 "amalgamation" distribution in > Unix, > but that seemed to do nothing. You only get the test suite if you download the full source tree. This one: http://www.sqlite.org/sqlite-3.6.2.tar.gz Do "make test". You'll need tcl installed to run the test suite. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about vacuum
On Sep 9, 2008, at 10:10 PM, Ribeiro, Glauber wrote: > Hello, > > Here's another newbie question, but it doesn't look like the > documentation for vacuum covers this. > > I'm using SQLite to store an application's log. Suppose I have a > program > that runs nightly and deletes log records that are older than 60 days, > to keep the database from growing without boundaries. > > I understand that deleting the records doesn't shrink the database > file, > unless I also vacuum. However, will SQLite re-use the space taken > by the > deleted records to store new ones? (Would I be better off not doing > vacuum, and letting SQLite reuse the disk space instead?) I believe > this > is the case with PostgreSQL. It will reuse the freed space. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in the RTree module
>> I think I have found a bug in the RTree extension (v3.6.2) It got fixed a day or two ago here: http://www.sqlite.org/cvstrac/chngview?cn=5682 Grab the new rtree.c file from here if you want the fix before 3.6.3 comes out: http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/rtree/ rtree.c=1.9 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] infinite looping from sqlite3_close()
On Sep 16, 2008, at 5:19 AM, Sathish R wrote:
> Hi All,
> We are using sqlite3 (version 3.2.1) in our product and we are
> experiencing
> a problem of infinite loop from sqlite3_close(). The problem is
> intermittently reproduced and I am not sure about the exact sequence.
> I have described the details below. is anybody else faced similar
> problem
> before? Can someone please help me here?
>
> we have thread safe enabled.
>
> We collected a core when our process is stuck in infinte loop and
> one thread
> is infinitely looping in the below marked loop within sqlite3_close
> (). So,
> it holds the mutex and some other threads are blocked waiting for that
> mutex.
>
> #ifndef SQLITE_OMIT_GLOBALRECOVER
> {
> sqlite3 *pPrev = pDbList;
> sqlite3OsEnterMutex();
> while( pPrev && pPrev->pNext!=db ){ -> one thread is infinite
> looping in
> this while()
> pPrev = pPrev->pNext;
> }
> if( pPrev ){
> pPrev->pNext = db->pNext;
> }else{
> assert( pDbList==db );
> pDbList = db->pNext;
> }
> sqlite3OsLeaveMutex();
> }
> #endif
>
> I printed the pDbList from core and the list doesn't seem to end
> and the
> link list seems to have become a cyclic one somehow.
>
> (gdb) p pDbList
> $40 = (sqlite3 *) 0x36d00bb8
> (gdb) p pDbList->pNext
> $41 = (sqlite3 *) 0x107b77a8
> (gdb) p pDbList->pNext->pNext
> $42 = (sqlite3 *) 0x107b77a8 -> link list becomes cyclic...no NULL.
>
> Note: I saw that this entire logic of link list and global recovery
> doesn't
> exist in recent 3.6.2 code base. Should I consider upgrading to newer
> libsqlite library to avoid this problem?
Yes. 3.2.1 is over 3 years old now. Hundreds of bugs have been fixed
since then. Odds are that the problem you're experiencing has been
fixed,
or, if not, you stand a much better chance of getting help with it if
using 3.6.2.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Closing database fails due to unfinalized statements
On Sep 16, 2008, at 4:44 PM, Lothar Behrens wrote: > Hi, > > I do have any unfinalized statements in my application when compiled > on Windows, but not on Mac OS X. > > Is there any difference I am missing to attent for ? > > How could I see, wich statement (statement handle or SQL query to be > used in that statement) is unfinalized ? Use sqlite3_next_stmt() to find unfinalized statements. sqlite3_sql() to determine the SQL used to prepare them. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2
> Hi All, > We have been using SQLite 3.4.2 for some time. On investigating > upgrading to 3.6.2, we found that different results were produced for > one query. > The following illustrates: > ... > > On 3.4.2 we get: > 3.0|3660.5|3 > 6.0|1360.3|6 > > On 3.6.2 we get: > |5020.8|3 > 3.6.2 has a bug involving DISTINCT or GROUP BY queries that use expression aliases (AS clauses) in the select-list. Problem is fixed in cvs: http://www.sqlite.org/cvstrac/chngview?cn=5712 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac file locking
On Sep 22, 2008, at 12:37 PM, Dave Dyer wrote: > >> >> Yes, I've noticed and reported this same problem. It appears that the >> SQLite bundled in Mac OS X has a special flag set to allow opening >> database files on networked volume, and that works great. > > > So what's the name of this special flag? Probably this: http://www.sqlite.org/compile.html#enable_locking_style Compilation option "SQLITE_ENABLE_LOCKING_STYLE". Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't insert timestamp field with value CURRENT_TIME
On Sep 27, 2008, at 7:13 AM, Mark Wyszomierski wrote: > Hi, > > I'm trying to add a timestamp field to an existing table using the > following > statement: > >ALTER TABLE test ADD COLUMN lunchtime TIMESTAMP NOT NULL DEFAULT > CURRENT_TIME > > this fails with the following error: > >Cannot add a column with non-constant default > > Ok that makes sense - but why can we CREATE a table with a timestamp > field > whose default is CURRENT_TIME, but not alter one with that as the > default > value? I may be misusing the syntax - SQLite doesn't modify the underlying table structure when you add a column using ALTER TABLE. So after the ALTER TABLE is executed, the existing rows now contain one value less than the table has columns. When SQLite reads the table, it detects the short row and returns the default column value in place of any missing values. This wouldn't work with something like CURRENT_TIME. > Thanks, > Mark > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
