Re: [sqlite] 64 bits Dll
On 09.05.2014 12:36, Carlos Ferreira wrote: I am using a win32 DLL built sqlite-dll-win32-x86-3071700 . the DLL is from 20-5-2013. I am using it with Delphi, and it works fine for what I need. I need however a 64 bits version of the DLL. SQLite3 for Delphi, both Win32 and Win64, with many extensions and extras here: http://yunqa.de/delphi/doku.php/products/sqlite3/index Enjoy, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64 bits Dll
On 09.05.2014 13:50, Carlos Ferreira wrote: XE6 itself seems to native have access to both... FireDAC only, and outdated by 2 months at the day of release. Delphi XE5 SQLite is still at 3.7.17, almost one year behind. DISQLite3 has always been up to date for years and Delphi versions back to Delphi 4: http://yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 24.01.2014 10:06, Max Vlasov wrote: BCC 5.5 (freely downloadable) compiles any version of sqlite3 to object files linkable to Delphi 5 and later, the only drawback I noticed is that for memory-intensive operations (memory databases) the performance is twice as worst comparing to the dll on the site (probably VC compiled), but for databases on disk the difference is small since I/O overhead compensate it. Don't know about DISQLite3 , but one of the main performance issues DISQLite3 does _not_ show the performance issues you describe for your BCB 5.5 compiled object files. Quite the opposite: DISQLite3 outperformed sqlite3.dll whenever I tested. You can test yourself with the example projects located in \DISQLite3_Log_Inserts\ and \DISQLite3_20_Million\ sub-folders of the \Demo\ directory. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] latest sqlite 3 with Delphi 5 professional
On 23.01.2014 11:28, dean gwilliam wrote: More specifically...is there the equivalent of that powerbasic include file for D5 i.e. that enables you to access the dll's function calls unchanged? Failing that...anything that will let me work with the latest sqlite 3 dll The meaner and leaner...the better. DISQLite3 meets your requirements: http://yunqa.de/delphi/doku.php/products/sqlite3/index * Delphi 5 support, among a dozen other Delphi compilers. * Compiles directly into applications, no need for sqlite3.dll. * Supports the *complete* SQLite3 API. * Includes FTS and numerous extensions. * UTF-8 string conversion functions provided. * Many Delphi demo projects. * Exhaustive documentation. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_FLAG_PRAGMAS issue: sqlite3_busy_timeout() called for all flag pragmas
SQLite 3.8.1 compiled with SQLITE_OMIT_FLAG_PRAGMAS #defined calls sqlite3_busy_timeout() for all flag pragmas. Example: PRAGMA legacy_file_format=100; should do nothing but sets sqlite3_busy_timeout(db, 100); The change was introduced here: http://www.sqlite.org/src/artifact/249742bd762770e5e6e5b67cfcb2fa9b1049?ln=1960 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 10.10.2013 03:53, Richard Hipp wrote: I think that http://www.sqlite.org/src/info/e97d7d3044 fixes this issue. Works well for me. Please correct me if I've missed something. You committed to the row-size-est branch. I guess this will be merged into trunk for 3.8.1? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
analyze.c always prints 32-bit variables as 64-bit here: http://www.sqlite.org/src/artifact/d322972af09e3f8debb45f420dfe3ded142b108b?ln=746 http://www.sqlite.org/src/artifact/d322972af09e3f8debb45f420dfe3ded142b108b?ln=792 This can cause wrong sqlite_statX tables which I have experienced compiling for 32-bit. With this change, tests pass again: #if sizeof(p-nRow) == sizeof(long long) sqlite3_snprintf(24, zRet, %lld, p-nRow); #elseif sizeof(p-Row) = sizeof(long) sqlite3_snprintf(24, zRet, %ld, p-nRow); #else sqlite3_snprintf(24, zRet, %d, p-nRow); #endif Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 09.10.2013 15:50, Eric Minbiole wrote: With this change, tests pass again: #if sizeof(p-nRow) == sizeof(long long) sqlite3_snprintf(24, zRet, %lld, p-nRow); #elseif sizeof(p-Row) = sizeof(long) sqlite3_snprintf(24, zRet, %ld, p-nRow); #else sqlite3_snprintf(24, zRet, %d, p-nRow); #endif Slightly off-topic, but I didn't think that sizeof() could be used as part of a preprocessor directive? (I.e., that #if sizeof(x) doesn't work as intended, or at least not portably.) This is more portable: #ifdef SQLITE_64BIT_STATS sqlite3_snprintf(24, zRet, %lld, p-nRow); #else sqlite3_snprintf(24, zRet, %d, p-nRow); #endif Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
I suggest a verb to express what the function is actually doing, namely to reduce its argument in rank or degree for the query planner: DEGRADE 1. to reduce in worth, character, etc; disgrace; 2. to reduce in rank, status, or degree; remove from office; 3. to reduce in strength, quality, intensity, etc Source: http://www.collinsdictionary.com/dictionary/english/degrade On 10.09.2013 21:26, Richard Hipp wrote: Please feel free to suggest other names if you think of any. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
On 11.09.2013 16:07, Ryan Johnson wrote: Perhaps you meant demote rather than degrade ? That would be a better fit (an external action that does not necessarily make the object worse or less useful), and less vague, but it still carries a negative connotation. demote sounds fine to me, especially since its antonym promote may be used for a function name to raise an expression's rank for the query planner rather than the 2nd argument. The negative connotation of both degrade and demote does not feel bad for me as a non native English speaker. Both, however, express an action rather than a quality which is more telling to me than unlikely or the other adjectives suggested so far. Maybe the function name could be prefixed by qp_ (for query planner) or similar to clarify their functionality even more: qp_demote and qp_promote? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few questions about SQLite Encryption Extension
On 19.07.2013 09:21, Sqlite Dog wrote: Our database manager is developed using Delphi (Pascal). Thus it is not possible to statically link SQLite library, SQLite.dll is used. Is there some other way to support SEE in our project? You have two options: * Create your own sqlite.dll and compile SEE into it. * Statically link SQLite to your Delphi application. My DISQLite3 enables you to do just that and has numerous extensions: One is a custom encryption algorithm. This is not compatible with SEE, but if you like I can replace it with your original SEE code for you. http://www.yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A few questions about SQLite Encryption Extension
On 19.07.2013 15:27, Sqlite Dog wrote: * Statically link SQLite to your Delphi application. My DISQLite3 enables you to do just that and has numerous extensions: One is a custom encryption algorithm. This is not compatible with SEE, but if you like I can replace it with your original SEE code for you. http://www.yunqa.de/delphi/doku.php/products/sqlite3/index Is it a pascal wrapper around SQLite or something bigger? DISQLite3 is the only Delphi product which includes the _complete_ SQLite API, AFAIK. Using register calling conventions and the Delphi memory manager, DISQLite3 surprised many users to perform noticeably faster than other implementations. Features include: * Complete SQLite API. * Supports Win32 and Win64. * Delphi class wrapper. * TDataSet descendant. * Delphi smart linking for smallest possible binaries. * Full Text Search (FTS) with customizable tokenizer, prefix matching, and optional word stemming for 15 languages. * Custom encryption (not SEE compatible). * Async IO Backend. * SQLite virtual table extensions: rtree, spellfix, fuzzer, closure, wholenumber, amatch. * SQLite SQL function extensions: ieee754, nextchar. * Extensive documentation and lots of demo projects. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
On 19.06.2013 17:18, Tom Holden wrote: I use the SQLiteSpy compilation of SQLite3 which recently upgraded from pre SQLite 3.7.8 to pre 4.2.0. You must be mistaken. As the author of SQLiteSpy, I can clearly say that there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current version is SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1. I also occasionally use the SQLite3 Windows command-line shell. I am now encountering an error with the current versions that I did not with earlier ones – sorry, I cannot be precise at this time as to when it began. The problem arises with simple SELECTs on a table which has one or more fields defined with an unavailable collation sequence. Previously, the only time an error was thrown would be when such field was ordered or an index was engaged that included it. Now the error is thrown on a simple SELECT of the field or of the PRIMARY KEY, even though the latter is not defined with the missing collation. No error is thrown if the Primary Key is included with other fields that do not use the missing collation. The collation is missing because the database is created by proprietary software. Example: CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE CUSTOM, RefNumber TEXT, ...) Your SQL is incomplete and does not allow testing SQLiteSpy. This SQL does raise an error no such collation sequence: CUSTOM: CREATE TABLE SourceTable ( SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE CUSTOM, RefNumber TEXT); Error not thrown: SELECT RefNumber ... or any combination of fields other than the Name field, the only one to have the proprietary collation “Error: no such collation sequence: CUSTOM”: SELECT Name FROM ... SELECT SourceID FROM ... SELECT SourceID, Name FROM ... SELECT Name and any combination of other fields FROM ... Further, even with ORDER BY, prior versions used to tolerate SELECT Name COLLATE NOCASE ... FROM table ORDER BY Name The current versions throw the error. From my perspective, this is a bug that limits the tools available to work with a proprietary database to fewer than there used to be – the command line shell being one rendered incapable. So far I have not been able to verify a single case where SQLiteSpy does no behave exactly as SQLite. If you believe otherwise, please provide working example SQL for testing. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
On 21.06.2013 05:10, Tom Holden wrote: Ralf Junker ralfjunker at gmx.de Thu Jun 20 18:44:15 EDT 2013 wrote: On 19.06.2013 17:18, Tom Holden wrote: I use the SQLiteSpy compilation of SQLite3 which recently upgraded from pre SQLite 3.7.8 to pre 4.2.0. You must be mistaken. As the author of SQLiteSpy, I can clearly say that there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current version is SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1. --- Tom replies: The versions are those of SQLite3 or of DISQLite3 according to your version history for SQLIteSpy at http://www.yunqa.de/delphi/doku.php/products/sqlitespy/history. I am not sure what you mean by pre SQLite 3.7.8 for SQLiteSpy 1.9.1 - was it compiled from SQLite 3.7.7? Thanks for pointing this out, the SQLiteSpy version history is now corrected. I agree that the SQLiteSpy 1.9.3 behaviour is the same as that of sqlite3.exe 3.7.17 with respect to these error messages for my examples just as there were no error messages for the same examples with SQLiteSpy 1.9.1 and sqlite3.exe 3.7.5. Good to know. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Security concern with new fts3tokenize virtual table
The new fts3tokenize virtual table calls the fts3_tokenizer() SQL function internally to retrieve the pointer to the tokenizer implementation for a FTS table: http://www.sqlite.org/src/artifact/a29f126b9e6c6a6f1021a8f7440bf125e68af1f9?ln=74-100 However, this call is rejected if, for security reasons, the fts3_tokenizer() function is blocked by an authorizer callback: SECURITY WARNING: If the fts3/4 extension is used in an environment where potentially malicious users may execute arbitrary SQL, they should be prevented from invoking the fts3_tokenizer() function, possibly using the authorization callback. (http://www.sqlite.org/fts3.html#section_8_1). So the problem is that with the authorizer block in place, the fts3tokneize virtual table does not work. One would have to compromise functionality for security. Is there no other way to retrieve the tokenizer besides calling fts3_tokenizer()? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.7.17 Preview
Current trunk still does not compile with SQLITE_OMIT_WAL #defined. Details here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg76672.html Ralf On 26.04.2013 17:34, Richard Hipp wrote: Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the proposed enhancements and changes in SQLite version 3.7.17. Your comments, criticisms and suggestions are welcomed and encouraged. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] amatch extension
I am unsure about how the amatch extension works. According to the
documentation, I believe that the SQL below should return at least one
record, but it does not. No errors are reported either.
I compiled the shell executable with the 201304290917 draft amalgamation
and today's amatch.c from Fossil trunk.
Is there something I am missing?
Ralf
DROP TABLE IF EXISTS f;
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS v;
CREATE TABLE v (w TEXT, l INTEGER);
CREATE INDEX v_index ON v(w);
INSERT INTO v VALUES ('abc', 0);
INSERT INTO v VALUES ('bcd', 0);
INSERT INTO v VALUES ('def', 0);
CREATE TABLE c(iLang INTEGER, cFrom TEXT, cTo TEXT, Cost INTEGER);
INSERT INTO c VALUES(0, '', 'a', 100);
INSERT INTO c VALUES(0, 'b', '', 87);
INSERT INTO c VALUES(0, 'o', 'oe', 38);
INSERT INTO c VALUES(0, 'oe', 'o', 40);
INSERT INTO c VALUES(0, '?', '', 97);
INSERT INTO c VALUES(0, '', '?', 98);
INSERT INTO c VALUES(0, '?', '?', 99);
CREATE VIRTUAL TABLE f USING approximate_match(
vocabulary_table=v,
vocabulary_word=w,
vocabulary_language=l,
edit_distances=c
);
SELECT * FROM f WHERE word MATCH 'abc';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pager.c does not compile with SQLITE_OMIT_WAL
The current SQLite Fossil snapshot does not compile with SQLITE_OMIT_WAL defined. Reason: The Pager-pWal element is compiled out in pager.c here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=691-694 but still accessed here: http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=2876 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5243 http://www.sqlite.org/src/artifact/6c3a8a5d665498b0344395a2c9f82d5abc4cc771?ln=5333 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Link pragma.html#pragma_user_version invalid
In the list of PRAGMAs in pragma.html the PRAGMA user_version documentation link to http://www.sqlite.org/pragma.html#pragma_user_version is invalid. It should point to http://www.sqlite.org/pragma.html#pragma_schema_version instead. This is also present in today's documentation draft. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] AsyncIO fails with current 3.7.15 trunk
I know that AsyncIO is now superseded by WAL [1], but according to
Richard it should still work for existing applications [2].
However, I experience the opposite.
The small C application below runs well when linked against SQLite
3.7.14. With SQLite 3.7.15, it mysteriously fails. I see two types of
outcomes:
1. Error SQLITE_IOERR, followed by SQLITE_ERROR. Nothing is written to
the target database.
2. No errors, but nothing is written to the target database.
Both which happen randomly with no obvious pattern. Could this hint at a
missing memory initialization or overrun?
Even though AsyncIO is no longer actively maintained, can anyone
reproduce my findings? I am running on Windows and have limited testing
capabilities like no Valgrind, etc.
Ralf
[1] http://www.sqlite.org/src/info/3d548db7eb
[2] http://www.mail-archive.com/sqlite-users@sqlite.org/msg74170.html
#include stdio.h
#include tchar.h
#include windows.h
#include sqlite3.h
#include sqlite3async.h
#pragma hdrstop
sqlite3 *db;
void sqlite3_check(int e) {
if (e != SQLITE_OK) {
printf(Error %d\n, e);
}
}
#ifdef SQLITE_ENABLE_ASYNCIO
int StopThread = 0;
DWORD WINAPI MyThreadFunction( LPVOID lpParam )
{
do {
printf (sqlite3async_run() ...);
sqlite3async_run();
sqlite3_sleep (0);
printf (OK\n);
}
while (!StopThread);
return 0;
}
#endif /* SQLITE_ENABLE_ASYNCIO */
char* FILE_NAME = test.db3;
int main(int argc, _TCHAR* argv[])
{
int i;
#ifdef SQLITE_ENABLE_ASYNCIO
HANDLE ThreadHandle;
#endif /* SQLITE_ENABLE_ASYNCIO */
if (!DeleteFile(FILE_NAME)) {
printf(Error deleting file %s\n, FILE_NAME);
}
#ifdef SQLITE_ENABLE_ASYNCIO
sqlite3_check(sqlite3async_initialize(NULL, 1));
sqlite3async_control(SQLITEASYNC_HALT, SQLITEASYNC_HALT_IDLE);
ThreadHandle = CreateThread(
NULL, // default security attributes
0, // use default stack size
MyThreadFunction, // thread function name
NULL, // argument to thread function
0, // use default creation flags
NULL); // returns the thread identifier
#endif /* SQLITE_ENABLE_ASYNCIO */
sqlite3_check(sqlite3_open_v2 (FILE_NAME, db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL));
sqlite3_check(sqlite3_exec(db, create table if not exists t (a),
NULL, NULL, NULL));
for (i = 0; i 10; i++) {
char *s = sqlite3_mprintf(insert into t values (%d);, i);
sqlite3_check(sqlite3_exec(db, s, NULL, NULL, NULL));
sqlite3_free(s);
}
sqlite3_check(sqlite3_close(db));
#ifdef SQLITE_ENABLE_ASYNCIO
StopThread = 1;
WaitForSingleObject(ThreadHandle, INFINITE);
CloseHandle(ThreadHandle);
sqlite3async_shutdown();
#endif /* SQLITE_ENABLE_ASYNCIO */
printf(Done.\nPress enter a number to quit.\n);
scanf(%d, i);
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Async IO in SQLite 3.7.15
I experience problems with Async IO testing the latest 3.7.15 trunk. Simple statements like CREATE TABLE fail with SQLITE_IOERR. I read the note in the Async IO README.txt that Async IO is now superceded by WAL mode and no longer maintained. Does this also mean that it will no longer be working with SQLite 3.7.15? Is it just deprecated for new development? Or has something else changed that I should take care of? Ralf On 03.12.2012 21:25, Richard Hipp wrote: We want 3.7.15 to be a good release, so please do have a look at the amalgamation snapshot described in the previous email (and copied below) if you have not done so already, and let us know if you encounter any problems. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Async IO in SQLite 3.7.15
On 04.12.2012 22:14, Richard Hipp wrote: Does this also mean that it will no longer be working with SQLite 3.7.15? Is it just deprecated for new development? Or has something else changed that I should take care of? That means that we are not willing to devote large amounts of time to it anymore. If you have patches that you want us to put in, we'll be glad to do that. Thanks, this is well understood and clear from the README note. My question is if existing applications which Async IO should continue to work with SQLite 3.7.15? Or has something in the SQLite core changed so that you'd expect Async IO failures in 3.7.15, or later versions? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Async IO in SQLite 3.7.15
On 04.12.2012 22:25, Richard Hipp wrote: My question is if existing applications which Async IO should continue to work with SQLite 3.7.15? Or has something in the SQLite core changed so that you'd expect Async IO failures in 3.7.15, or later versions? They should continue to work, as far as we are aware. Nothing in the core has changed to break them. Thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to restore from backup or alter db in multi-user envionment?
On 02.12.2012 04:37, Simon Slavin wrote: I'm using DISQLite and the online api in delphi, not the command line thing. I have seen some bad drivers which assume that underlying schema will not be changed by another user while they have a database connection open. I have no reason to believe that this is one of them, but it might be worth reading the documentation. As the author of DISQLite3 I confirm that DISQLite3 is NOT a bad driver in that it assumes that the underlying schema will not be changed. DISQLite3 is plain SQLite3, just conveniently made available for the Delphi programming language. What works in SQLite3 also works in DISQLite3. Some extras are added, but nothing left out or modified. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting when a table is created
On 26.06.2012 14:33, Vivien Malerba wrote: The code is some SQL entered by the user, I have no control over it. There is effectively the possibility to parse the SQL entered, detect the CREATE table statement and act accordingly, but I would like to see if SQLite has some kind of builtin feature I could use first. Two options, both not intended for it, but could work well: - http://sqlite.org/c3ref/set_authorizer.html - http://sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfiglog Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_CREATE required by named memory database?
Testing SQLite Fossil [0abdc2903d], sqlite_open_v2() returns SQLITE_PERM when
trying to open a named memory database if the flag SQLITE_OPEN_CREATE is not
passed. This is in contrast to unnamed memory databases. Is this intentional?
Basic C code below demonstrates the issue.
Ralf
--
sqlite3 *db = 0;
void check (int i) {
switch (i) {
case SQLITE_DONE:
case SQLITE_OK:
case SQLITE_ROW:
break;
default:
printf (Error %d: %s\n, i, sqlite3_errmsg(db)) ;
}
};
int main(int argc, char* argv[])
{
char * fn;
/* Test opening a named memory database using a URI file name. */
fn = file:test.db?cache=sharedmode=memory;
check(sqlite3_open_v2(fn, db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL));
check(sqlite3_close(db));
/* Test opening a named memory database using a URI file name.
This fails because SQLITE_OPEN_CREATE is missing. */
fn = file:test.db?cache=sharedmode=memory;
check(sqlite3_open_v2(fn, db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL));
check(sqlite3_close(db));
/* Test opening an unnamed memory database using a URI file name. */
fn = file::memory:?cache=shared;
check(sqlite3_open_v2(fn, db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL));
check(sqlite3_close(db));
/* Test opening an unnamed memory database. */
fn = :memory:;
check(sqlite3_open_v2(fn, db,
SQLITE_OPEN_READWRITE, NULL));
check(sqlite3_close(db));
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation glitch: Repeated or ...
... at both end and beginning of these lines: http://www.sqlite.org/src/artifact/45a846045ddb8c4318f2919f3a70f011df5ca783?ln=2584-2585 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
On 04.05.2012 16:39, Richard Hipp wrote: If a single min() or max() aggregate function appears in a query, then any other columns that are not contained within aggregate functions and that are not elements of the GROUP BY will take values from one of the same rows that satisfied the one min() or max() aggregate function. Given that more than one row satisfies the one min() or max() aggregate function (think of multiple, identical smallest or largest values). Which row will SQLite pick? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3 64-bit version
On 27.04.2012 11:38, Gabriel Corneanu wrote: There is one more reason to use DLLs, I'm surprised noone mentioned it. What if you don't use C??? (I use myself Delphi with a header conversion). DISQLite3 compiles right into your Delphi application. Both 32-bit and 64-bit supported: http://www.yunqa.de/delphi/doku.php/products/sqlite3/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3 64-bit version
On 27.04.2012 15:08, Gabriel Corneanu wrote: With the amalgamation, it's also quite easy to compile to one obj and link directly in Delphi (similar to jpeg; that's probably what you also do). No pun intended, why should I pay for it? - DISQLite3 Personal edition if free! - DISQLite3 is 64-bit and you are looking for 64-bit SQLite. - DISQLite3 is *not* compiled from the amalgamation. This enables Delphi's smart linking by excluding unused features like FTS, RTree, etc. Your application file size shrinks. - DISQLite3 contains the *complete* SQLite3 API, not only those most commonly used. It also includes extensions not part of sqlite3.dll. - DISQLite3 is well tested before each release. New APIs and functionality are added to the internal DUnit test suite. Problems are reported back to the SQLite community. - DISQLite3 uses fastcall / register calling conventions and Delphi's internal memory manager. This makes it faster then sqlite3.dll. - DISQLite3 is regularly updated. This saves you from maintaining your own, local API translations so you can focus on your application. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 14:50, Rob Richardson wrote: I keep hoping to find something better, because SQLiteSpy does not offer the ability to edit a table inside a grid. Not quite true. SQLiteSpy can edit table cells inside a grid. Double-click the table-icon in the schema treeview to display the table. When you then select a table cell in the grid and press F2, the edit window will pop up. See feature item 2 and the screenshot at http://yunqa.de/delphi/doku.php/products/sqlitespy/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 16:11, Rob Richardson wrote: With the latest version, I think you can. SQLiteSpy grid editing has been around for years, it is available since version 1.6.0., 7 Feb 2007. But I haven't been able to get the latest version. The link you provided does not work for me. Do you know any site other than yunqa.de where the latest SQLite can be downloaded? SQLiteSpy downloads might be available from various portals. But usually http://www.yunqa.de works just fine. Just try again later. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
On 05.03.2012 18:45, Rob Richardson wrote: I can't get to www.yunqa.de now. I tried in IE8 and FireFox. Most strange. Luckily, search engines find plenty of SQLiteSpy download alternatives. Here is one of the more well known ones: http://www.softpedia.com/get/Internet/Servers/Database-Utils/SQLiteSpy.shtml Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
On 13.02.2012 10:18, Dan Kennedy wrote: On 02/10/2012 11:06 PM, Ralf Junker wrote: The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Has the team seen this or has it been overlooked? Shall I file a ticket? I get this: SQLite version 3.7.11 2012-02-13 08:50:23 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE t1(x, y); sqlite INSERT INTO t1 VALUES(1, 2), (3, 4); Error: near ,: syntax error Is this what you are seeing? That the syntax is rejected with an error message? Or some other problem? I believe Richard has already take care of this and disabled multiple rows in the VALUES clause if SQLITE_OMIT_COMPOUND_SELECT is defined: http://127.0.0.1:8080/info/92131195d0 Before this, I did not see any error message. After, I expect an error message similar to yours (from reading the code, I did not compile and run). So do I guess correctly that you were testing with trunk later than check-in [92131195d0]? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
On 07.02.2012 12:28, Ralf Junker wrote: The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Has the team seen this or has it been overlooked? Shall I file a ticket? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT
The new feature to insert multiple rows of VALUES in a single INSERT http://www.sqlite.org/src/info/eb3b6a0ceb gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT. Example: CREATE TABLE t10(a,b,c); INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); SELECT * FROM t10; For me returns 7, 8, 9 It seems that with SQLITE_OMIT_COMPOUND_SELECT, only the last multiple rows value is inserted. Error messages also differ: INSERT INTO t10 VALUES(11,12,13), (14,15); returns the error message: table t10 has 3 columns but 2 values were supplied instead of: all VALUES must have the same number of terms Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_uri_int64() doc error
The sqlite3_uri_int64() doc reads: If the value of P is something other than an integer, then zero is returned. I found this to be not true. Instead, it returned default. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_db_release_memory(): Return value undocumented.
Reading the sources, sqlite3_db_release_memory() always returns SQLITE_OK. Will it stay this way or will it some day return another value like the amount of memory released? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiplex VACUUM fails to delete chunks on Windows
VACUUM with multiplex does not delete chunks on Windows (fossil [8ce2b74a82]).
It seems this is because the file handle(s) are still held open by the
multiplex layer when xDelete is triggered. Since Windows can not delete open
files, they are kept.
I have not investigated this in depth, but closing the file handle before
deleting the file works well for my simple test case. Here is the change in
multiplexSubClose():
static void multiplexSubClose(
multiplexGroup *pGroup,
int iChunk,
sqlite3_vfs *pOrigVfs
){
sqlite3_file *pSubOpen = pGroup-aReal[iChunk].p;
if( pSubOpen ){
pSubOpen-pMethods-xClose(pSubOpen); /* -- Moved here */
if( pOrigVfs ) pOrigVfs-xDelete(pOrigVfs, pGroup-aReal[iChunk].z, 0);
/* pSubOpen-pMethods-xClose(pSubOpen); -- Moved above */
sqlite3_free(pGroup-aReal[iChunk].p);
}
sqlite3_free(pGroup-aReal[iChunk].z);
memset(pGroup-aReal[iChunk], 0, sizeof(pGroup-aReal[iChunk]));
}
By the way: No error is returned if multiplex VACUUM fails to delete a chunk.
Maybe it should, to warn curious end users who like to investigate files with
uncommon names?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
For me, this test from test/exists.test fails if both database
connections are opened in WAL mode, that is if PRAGMA journal_mode=WAL
is issued right after each DB is opened.
# TABLE objects.
#
do_test{
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { CREATE TABLE t1(x) }
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
} {}
This is against http://www.sqlite.org/src/info/f346dae127.
I am running a ported version of the test instead of the original tcl so
my finding could wrong. Still, I believe it better to let you know in
case you want to check this last-minute change before you release 3.7.6.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
The test passes well if I recreate the database file anew just prior to
running it so it acts upon an empty database.
If this is what is intended, I apologize for the false alarm.
Ralf
On 10.04.2011 10:28, Ralf Junker wrote:
For me, this test from test/exists.test fails if both database
connections are opened in WAL mode, that is if PRAGMA journal_mode=WAL
is issued right after each DB is opened.
# TABLE objects.
#
do_test {
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { CREATE TABLE t1(x) }
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
} {}
This is against http://www.sqlite.org/src/info/f346dae127.
I am running a ported version of the test instead of the original tcl so
my finding could wrong. Still, I believe it better to let you know in
case you want to check this last-minute change before you release 3.7.6.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode
For me, this test from test/exists.test fails if both database
connections are opened in WAL mode, that is if PRAGMA journal_mode=WAL
is issued right after each DB is opened.
# TABLE objects.
#
do_test{
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { CREATE TABLE t1(x) }
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
} {}
This is against http://www.sqlite.org/src/info/f346dae127.
I am running a ported version of the test instead of the original tcl so
my finding could wrong. Still, I believe it better to let you know in
case you want to check this last-minute change before you release 3.7.6.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Extremely large values error
On 29.03.2011 15:17, Max Vlasov wrote:
Thanks, this forced me to search more on the topic. I use Delphi and it
appears that all Borland compilers has their own floating-point exception
handling chain and it is on by default so if any division by zero or
overflow appears inside sqlite3_step Delphi will handle this with its own
exception handling.
When running SQLite from Delphi, you must disable FPU exceptions. Below
is a simple console project which uses the DISQLite3 Delphi port of
SQLite to demonstrate how this works.
Ralf
---
{ DISQLite3 example project to show how to disable FPU exceptions so
that very large integer numbers are correctly converted to 'Inf' text.
Exceptions should always be disabled before running any C library from
Delphi. This is important because exceptions do not exist in C and C
does not know how to handle them. Exceptions will therefore cause the
entire C library stack to unwind undisturbed until the exception is
finally handled by Delphi code. The result of this is undefined and
might range from simple memory leaks to more severe and complicated
problems.
This applies to all C libraries, both internal (*.obj) and extenal
(*.dll).
Visit the DISQLite3 Internet site for latest information and updates:
http://www.yunqa.de/delphi/
Copyright (c) 2011 Ralf Junker, Delphi Inspiration del...@yunqa.de
- }
program DISQLite3_Very_Large_Integer;
{$APPTYPE CONSOLE}
{$I DI.inc}
{$I DISQLite3.inc}
uses
{$IFDEF FastMM}FastMM4, {$ENDIF}DISystemCompat, SysUtils,
DISQLite3Api;
var
DB: sqlite3_ptr;
procedure ExecSql(const SQL: Utf8String);
var
i: Integer;
Stmt: sqlite3_stmt_ptr;
begin
sqlite3_check(sqlite3_prepare_v2(DB,
PUtf8Char(SQL), Length(SQL), @Stmt, nil), DB);
try
while sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW do
begin
for i := 0 to sqlite3_column_count(Stmt) - 1 do
begin
if i 0 then Write(', ');
Write(sqlite3_column_str(Stmt, i));
end;
WriteLn;
end;
finally
sqlite3_finalize(Stmt);
end;
end;
const
DB_FILE_NAME = 'test.db3';
begin
{ Disable FPU exceptions. No need to restore, setting is process
specific. }
Set8087CW($133F);
try
sqlite3_initialize;
DeleteFile(DB_FILE_NAME);
sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB));
try
ExecSql('CREATE TABLE t1(v TEXT);');
ExecSql('INSERT INTO t1 VALUES(''' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'1234567890'');');
ExecSql('INSERT INTO t1 VALUES(''-' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'123456789012345678901234567890123456789012345678901234567890' +
'1234567890'');');
ExecSql('SELECT length(v), CAST(v AS float) FROM t1;');
finally
sqlite3_check(sqlite3_close(DB), DB);
sqlite3_shutdown;
end;
except
on e: Exception do
WriteLn(e.Message);
end;
WriteLn;
WriteLn('Done - Press ENTER to Exit');
ReadLn;
end.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Doc errors: Too many checked out. in Status Parameters for database connections
There are far too many checked out. in the following docs, probably left-overs from copy-paste operations: ** ^(dtSQLITE_DBSTATUS_LOOKASIDE_HIT/dt ** ddThis parameter returns the number malloc attempts that were ** satisfied using lookaside memory. Only the high-water value is meaningful; ** the current value is always zero. ** checked out./dd)^ ** ** ^(dtSQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE/dt ** ddThis parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to the amount of ** memory requested being larger than the lookaside slot size. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out./dd)^ ** ** ^(dtSQLITE_DBSTATUS_LOOKASIDE_MISS_FULL/dt ** ddThis parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to all lookaside ** memory already being in use. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out./dd)^ Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug? 3.7.5 RC2 FTS4 division by zero exception
This SQL:
drop table if exists t11;
CREATE VIRTUAL TABLE t11 USING fts4;
INSERT INTO t11 VALUES('quitealongstringoftext');
INSERT INTO t11 VALUES('anotherquitealongstringoftext');
UPDATE t11_stat SET value = X'';
SELECT matchinfo(t11, 'nxa') FROM t11 WHERE t11 MATCH 'a*';
leads to a division by zero exception in fts3_snippet.c, line 1171:
pInfo-aMatchinfo[iCol] = (u32)(((u32)(nToken0x)+nDoc/2)/nDoc);
nDoc is zero here, which is not tested for, but I believe it should.
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 + SQLITE_OMIT_PAGER_PRAGMAS causes division by zero exception
Recent changes to FTS3 apparently require that SQLite must be compiled with pager pragmas, otherwise FTS3 will cause a division by zero exception as I have experienced right now. This means that the FTS3 extension can crash an application if the core SQLite library is compiled with SQLITE_OMIT_PAGER_PRAGMAS. The following SQL triggers the exception, based on the table data in fts4aa.test: SELECT docid, mit(matchinfo(t1)) FROM t1 WHERE t1 MATCH 'spake hebrew' ORDER BY docid; Debugging is not available to me, but as far as I read the code, FTS3's dependency on pager pragmas was introduced by check-in 5ae0ba447a on 2010-10-19. With SQLITE_OMIT_PAGER_PRAGMAS, the PRAGMA %Q.page_size command in fts3.c, fts3DatabasePageSize() silently returns 0 instead of reporting an appropriate error message, which can then lead to a division by zero exception, most likely in fts3_write.c, sqlite3Fts3SegReaderCost(). Given that the FTS3 extension can potentially be linked against a SQLite core compiled with SQLITE_OMIT_PAGER_PRAGMAS and cause unexpected failure, this might be worth a workaround or error message. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check-in [876845661a] incomplete
Check-in [876845661a] (http://www.sqlite.org/src/info/876845661a) is incomplete: fts2_tokenizer.c still contains calls to isalnum() and tolower() for FTS2. FTS1 also #includes ctype.h, just in case you care to remove it from SQLite completely. The changes are just like for FTS2. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a program that converts sqlite database from windows-1252 to utf-8 ?
On 11.09.2010 20:42, Stef Mientki wrote: is there a program that converts sqlite database from windows-1252 to utf-8? Yes, SQLiteSpy does this: * Open database file * Menu Execute - Text to Unicode Conversion. * Select tables and columns to convert. * Choose database codepage. * Press OK to start the conversion. After conversion, browse the database contents to verify the result. As SQLiteSpy supports Unicode text display and entry, you can do it right there. If you like what you see, execute COMMIT to store the conversion changes. ROLLBACK in case of conversion errors and try again with a different codepage. Download: http://yunqa.de/delphi/doku.php/products/sqlitespy/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rollback to savepoint regression / bug with journal_mode=off and locking_mode=exclusive
SQLite 3.7.2 has a regression with journal_mode=off and locking_mode=exclusive. Here is the SQL reproduce: drop table if exists t1; PRAGMA locking_mode=exclusive; pragma locking_mode; CREATE TABLE t1(a PRIMARY KEY, b); PRAGMA journal_mode = off; BEGIN; INSERT INTO t1 VALUES(13, 14); SAVEPOINT s1; INSERT INTO t1 VALUES(15, 16); ROLLBACK TO s1; ROLLBACK; SELECT * FROM t1; - SQLite3 3.7.2 rolls back the savepoint insert and yields: exclusive exclusive off 13|14 SQLite3 3.6.23.1 commits the savepoint insert and yields: exclusive exclusive off 13|14 15|16 The SQL was shortened from savepoint.test. It works well in locking_mode=normal so I dare say this is most likely a locking_mode=exclusive bug. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rollback to savepoint regression / bug with journal_mode=off and locking_mode=exclusive
On 27.08.2010 18:58, Noah Hart wrote: http://www.sqlite.org/pragma.html#pragma_journal_mode states The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF I had read this, too, before posting my report, and that's why I acknowledged that it might just be a regression and not a bug. I don't think this is a bug, just different undefined results. But the fact that this is actually part of the test suite (savepoint.test) where it works well with locking_mode=normal makes me believe that it is not just some undefined behavior, even if the documentation says so. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Different error message after sqlite3_reset() or sqlite3_finalize()
Related to constraint violations, an error message returned by
sqlite3_errmsg() changes to the better after calling sqlite3_reset() or
sqlite3_finalize().
Example code is below, here is the output:
sqlite3_step: 19 constraint failed
sqlite3_reset:19 t.c may not be NULL
sqlite3_finalize: 0 t.c may not be NULL
As can be seen, sqlite3_reset() enhances the error message returned by
sqlite3_errmsg() by filling in the column name and reason why the
constraint failed.
I would like to ask if this could be changed so that sqlite3_step() by
itself generates the more meaningful error message, even without calling
sqlite3_reset() or sqlite3_finalize()?
IIRC, having sqlite3_step() generate the final error codes and
messages was one of the reasons that brought about sqlite3_prepare_v2().
I observe that the error message generated after sqlite3_prepare_v2()
(constraint failed) is far better than that of sqlite3_prepare() (SQL
logic error or missing database), but it is not as good as it gets
after resetting the statement.
Ralf
Example code:
int main(int argc, char* argv[])
{
sqlite3* db;
sqlite3_stmt* stmt;
int e;
sqlite3_open(test.db3, db);
sqlite3_exec(db, create table if not exists t (c text not null),
NULL, NULL, NULL);
sqlite3_prepare_v2 (db, insert into t values (null), -1,
stmt, NULL);
e = sqlite3_step(stmt);
printf(sqlite3_step: %d %s\n, e, sqlite3_errmsg(db));
e = sqlite3_reset(stmt);
printf(sqlite3_reset:%d %s\n, e, sqlite3_errmsg(db));
e = sqlite3_finalize(stmt);
printf(sqlite3_finalize: %d %s\n, e, sqlite3_errmsg(db));
sqlite3_close (db);
printf (\nDone);
scanf (*%s);
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE VIEW ... LIMIT 1; -- apparently no effect?
This is SQLite 3.6.23.1 compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT
(plus a few others, which should not matter to the problem).
The UPDATE ... LIMIT clause works fine when applied to tables, but
suppresses any updates when applied to a view with an update trigger.
Here is some example SQL:
create table test (data,rownum integer);
insert into test values ('one',1);
insert into test values ('two',2);
create view vtest as select * from test;
create trigger Trig1 instead of update of data on vtest
begin
update test set data = new.data where rownum = new.rownum ;
end;
-- No LIMIT - this works.
update vtest set data = 'yyy'; --works
-- LIMIT clause present - nothing is updated.
update vtest set data = 'zzz' limit 1;
It is unfortunately not possible to reproduce this with the reference
binaries from sqlite.org since they are compiled without
SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Searching the timeline and previous
list messages did not turn up any applicable results.
So here are my questions:
Can anybody confirm my findings?
If so, is this the expected behavior? Or should not UPDATE ... LIMIT on
views work just like on tables?
Could this be a bug worth creating a ticket for?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SUBSTR overload and ALTER TABLE
We just experienced the hard way that overloading certain built-in SQL function can interfere with core SQL commands if the overloaded function behaves differently from the built-in function. Not surprising, after looking at the sources: * ALTER TABLE - alter.c uses SUBSTR and LIKE. * VACUUM - vacuum.c uses SUBSTR, LIKE, and QUOTE. * Possible others? Would it be possible that the SQLite core always uses the built-in functions instead of the overloaded ones? Or should overloading be disabled for core critical SQL functions? If not, maybe those critical functions could be mentioned in the documentation to warn developers that strange things might happen if they change their behavior? IMHO, LIKE is especially critical since it is a likely candidate for overriding to add UNICODE LIKE to applications. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 bug with MATCH plus OR
Thanks to both Scott and Dan for your answers! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 bug with MATCH plus OR
The recent changes to FTS3 fixed a long standing problem with MATCH and
AND operators combined. Take this schema:
drop table if exists myfts;
create virtual table myfts using fts3 (a);
insert into myfts values ('one');
insert into myfts values ('two');
This following query produced an unable to use function MATCH in the
requested context error up to 3.6.21, IIRC. The workaround was to add a
+ sign in front of the rowid. Since 3.6.22 it gladly works even without
the + sign:
select * from myfts where (myfts MATCH 'one') and (rowid=1);
However, a similiar problem is still present using or instead and.
Even more problematic, the +rowid workaround no longer helps. Both these
queries fail:
select * from myfts where (myfts MATCH 'one') or (rowid=1);
select * from myfts where (myfts MATCH 'one') or (+rowid=1);
Is this something that should be addressed?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3BtreeSetPageSize() compile error with SQLITE_OMIT_PAGER_PRAGMAS and SQLITE_OMIT_VACUUM
The current Fossil trunk [dd4962aa34] does not compile with both * SQLITE_OMIT_PAGER_PRAGMAS * SQLITE_OMIT_VACUUM enabled. These defines exclude btree.c sqlite3BtreeSetPageSize(), but it is still referenced from build.c. The problem was introduced by Check-in [5dcfb0c9e4]: Make the TEMP file tables use the page size set for the main database. Here is the link: http://www.sqlite.org/src/ci/5dcfb0c9e420d27e54a299b3991b98776651a507 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On 01.12.2009 09:01, Dan Kennedy wrote: Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a): I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case they are invalid, I would like to return an explaining error message in addition to SQLITE_ERROR. I did not find a way to do this. Is it at all possible? I don't think it is possible at the moment. Unfortunately. Dan, thanks for the confirmation. Related to this, I noticed check-in [620a8a2b38] which avoids using an uninitialized variable for the error message issued when sqlite3_tokenizer_module.xCreate returns an error. The error message is now: pzErr = sqlite3_mprintf(unknown tokenizer); I believe that the message is misleading because the tokenizer is not unknown. The error is that a registered tokenizer can not be created. Personally, I would like to see an error message giving the proper reason and the name of the tokenizer: pzErr = sqlite3_mprintf( cannot create tokenizer %s); Also giving the tokenizer arguments is naturally helpful for debugging and end-user support: pzErr = sqlite3_mprintf( cannot create tokenizer %s with arguments %s); Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?
On 02.12.2009 20:00, Dan Kennedy wrote: On Dec 2, 2009, at 3:07 PM, Ralf Junker wrote: * Is it now intentionally possible to update FTS3 DOCIDs? It is. The failing sqlite test case has been replaced. Thanks you, Dan! I had already noticed! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts3b-4.9 fails - FTS3 docid updates now possible with latest version?
With the latest FTS3 changes, fts3b-4.9 no longer passes. This short SQL emulates the test: DROP TABLE IF EXISTS t4; CREATE VIRTUAL TABLE t4 USING fts3(c); INSERT INTO t4 (docid, c) VALUES (12, 'still testing'); UPDATE t4 SET docid = 14 WHERE docid = 12; Note that SQLite 3.6.20 chokes with SQL logic error or missing database on the last line. Everything runs fine with the latest FTS3. I can even query SELECT docid, * FROM t4 WHERE t4 MATCH 'still'; and it happily returns that the updated docid equals 14. Questions: * Is it now intentionally possible to update FTS3 DOCIDs? * Should I modify my tests accordingly? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On 01.12.2009 09:01, Dan Kennedy wrote: I don't think it is possible at the moment. Unfortunately. Thanks for the clarification, Dan! I observe that you are currently writing the official FTS3 documentation in preparation for the next release of SQLite. Maybe you want to make tokenizer error messages possible before the docs are finished and the unfortunate xCreate API is carved in stone? ;-) Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with #include sqlite3.h in fts3Int.h
I have a compilation problem with line 22 in fts3Int.h: #include sqlite3.h According to http://gcc.gnu.org/onlinedocs/cpp/Include-Syntax.html, file includes are used for system header files, which sqlite3.h is not IMHO. I tried to replace with #include sqlite3.h but this resulted in a number of unknown identifiers further down the code. Finally I changed it to #include sqliteInt.h and the code compiled happily. Testing showed that it also worked OK. Am I on the right track? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
Compiling with SQLITE_OMIT_SUBQUERY, sqlite3ExprCodeIN() is compiled out but at the same time still required at other places in expr.c. As such, expr.c does not link well with SQLITE_OMIT_SUBQUERY defined. After I wrapped the remaining references to sqlite3ExprCodeIN() by #ifndef SQLITE_OMIT_SUBQUERY it compiles fine. Am I safe to do so or are there any caveats? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On 01.12.2009 18:05, D. Richard Hipp wrote: The key point to bare in mind here is that SQLITE_OMIT_SUBQUERY is not a supported compile-time option. None of the major users of SQLite make use of SQLITE_OMIT_SUBQUERY. We do not test it. And it appears that it is broken in the current implementation. It is unlikely to be fixed for 3.6.21. Thank you for pointing this out! I was not aware that there are supported and unsupported compile-time options. Reading http://www.sqlite.org/compile.html always made me believe that all options to omit features are supported. Now I am not sure which ones I can really trust. Could you clear up my confusion? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3ExprCodeIN() problems with SQLITE_OMIT_SUBQUERY
On 01.12.2009 18:35, Jay A. Kreibich wrote: Important Note: The SQLITE_OMIT_* compile-time options are unsupported. Oops! Thanks for focusing my eyes - they tend to skip introductions and move right to the details. Now having that read, let me point out that in spite of the statement the SQLITE_OMIT_... options have worked nearly flawlessly for me over the last few years. If there were little glitches, they were usually fixed very quickly. Thank you, SQLite team! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error message from sqlite3_tokenizer_module.xCreate?
I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case they are invalid, I would like to return an explaining error message in addition to SQLITE_ERROR. I did not find a way to do this. Is it at all possible? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error message from sqlite3_tokenizer_module.xCreate?
I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case they are invalid, I would like to return an explaining error message in addition to SQLITE_ERROR. I did not find a way to do this. Is it at all possible? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from sqlite3_tokenizer_module.xCreate?
On 30.11.2009 20:33, Grzegorz Wierzchowski wrote:
Monday 30 of November 2009 12:29:10 Ralf Junker napisał(a):
I am passing various arguments to sqlite3_tokenizer_module.xCreate. In case
they are invalid, I would like to return an explaining error message in
addition to SQLITE_ERROR. I did not find a way to do this. Is it at all
possible?
Thanks, Ralf
The last argument of xCreate() is char **pzErr.
It is exactly designed for the purpose you describe.
See also description in doc-zip: sqlite-3_6_18-docs/vtab.html, or
http://www.sqlite.org/vtab.html
Thank you for your answer! I believe you are mixing up the virtual table
sqlite3_module.xCreate() in sqlite3.h and
sqlite3_tokenizer_module.xCreate() in fts3_tokenizer.h.
The latter does not have the pzErr argument:
struct sqlite3_tokenizer_module {
snip
int (*xCreate)(
int argc, /* Size of argv array */
const char *const*argv, /* Tokenizer argument strings */
sqlite3_tokenizer **ppTokenizer /* OUT: Created tokenizer */
);
So I believe my question remains unanswered. Any suggestion, anyone?
Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: Memory reused after freed
My memory manager reports that the SQL below results in memory being reused
after it has already been freed when it is RUN FOR A SECOND TIME on the same
database connection.
Psydocode (I do not run C):
sqlite3_initialize;
sqlite3_open(DB_FILE_NAME, DB);
sqlite3_exec(DB, SQL, NULL, NULL, NULL);
sqlite3_exec(DB, SQL, NULL, NULL, NULL);
sqlite3_close(DB);
sqlite3_shutdown;
The database file does not exist before running sqlite3_open().
I can reproduce the problem reliably with [e4eb227b14]. There were simmilar
memory reports during the development of fkeys.c. They have been fixed by
[5b4d46374a] except for the one mentioned here.
Finally, here is the SQL:
PRAGMA foreign_keys = 0;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
PRAGMA foreign_keys = 1;
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO t1 VALUES('aaa', 1);
INSERT INTO t2 VALUES('aaa');
UPDATE t1 SET a = 'bbb';
DELETE FROM t1;
PRAGMA foreign_keys = 0;
DROP TABLE t1;
DROP TABLE t2;
PRAGMA foreign_keys = 1;
CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t2 VALUES(1, 2);
UPDATE t1 SET x = 5;
Ralf
Call stack leading to the problem:
Modified byte offsets (and lengths): 512(6)
The previous block size was: 2048
This block was previously allocated by thread 0x8BC, and the stack trace
(return addresses) at the time was:
4088CE [DebugReallocMem]
413A48 [realloc][4198]
453A7E [...@sqlite3realloc]
453AF9 [sqlite3_realloc]
453C22 [...@sqlite3dbrealloc]
440AE6 [...@sqlite3vdbeswap]
440B37 [...@sqlite3vdbeaddop3]
440B8F [...@sqlite3vdbeaddop0]
4256CE [...@sqlite3triggersexist]
4539A3 [...@sqlite3dbfree]
425894 [...@sqlite3triggersexist]
The allocation number was: 632
The block was previously freed by thread 0x8BC, and the stack trace (return
addresses) at the time was:
413781 [Free][3285]
45397C [sqlite3_free]
4539C7 [...@sqlite3dbfree]
440FA2 [...@sqlite3vdbejumphere]
440FD2 [...@sqlite3vdbeprogramdelete]
440F5F [...@sqlite3vdbejumphere]
440F88 [...@sqlite3vdbejumphere]
44265B [...@sqlite3vdbedelete]
4425A5 [...@sqlite3vdbefinalize]
42DA71 [sqlite3_exec]
45B9F7 [D:\DI_Stable\Lib\SQLite3\Test\Test.dpr][Test][initialization][45]
The current thread ID is 0x8BC, and the stack trace (return addresses) leading
to this error is:
408698 [DebugGetMem]
4088CE [DebugReallocMem]
413A48 [realloc][4198]
453A7E [...@sqlite3realloc]
453AF9 [sqlite3_realloc]
453C22 [...@sqlite3dbrealloc]
440AE6 [...@sqlite3vdbeswap]
440B37 [...@sqlite3vdbeaddop3]
440B8F [...@sqlite3vdbeaddop0]
4256CE [...@sqlite3triggersexist]
4539A3 [...@sqlite3dbfree]
Current memory dump of 256 bytes starting at pointer address 7FEE3CD0:
A0 DC 46 00 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80
80 80 80 80 80 80
Ü F .
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: Memory reused after freed
At 14:04 29.09.2009, Dan Kennedy wrote: On Sep 29, 2009, at 4:30 PM, Ralf Junker wrote: My memory manager reports that the SQL below results in memory being reused after it has already been freed when it is RUN FOR A SECOND TIME on the same database connection. Hi Ralf, Thanks for this report. I'm unable to reproduce the problem so far though. No longer needed. The problem no longer shows with check-in [582bd76828] applied. In fact I believed that it was initiated by my report on this list. Information below is just FYI. Are you compiling SQLite with any special symbols or anything like that? Not that I am aware of. Certainly nothing except the usual which I did before the problem surfaced. Are you able to build other programs using this memory manager? If so, does the bug show up when you pass the problematic SQL to the command line tool twice? Or if I send you a C program are you able to build and test it? I am not able to use the exact same memory manager when I run C code directly. Alternatively, I did just now run SQLite on Embarcadero's C++ Builder with CodeGuard enabled. This results in a magnitude of error reports while the SQLite code still produces correct results AFAIKS. It is impossible for me to tell if these reports also contain the one I reported earlier. Knowing little about how well CodeGuard does its job, I am not sure how seriously I shall take its warnings as long as all works well. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: Memory reused after freed
At 14:04 29.09.2009, Dan Kennedy wrote: On Sep 29, 2009, at 4:30 PM, Ralf Junker wrote: My memory manager reports that the SQL below results in memory being reused after it has already been freed when it is RUN FOR A SECOND TIME on the same database connection. Hi Ralf, Thanks for this report. I'm unable to reproduce the problem so far though. No longer needed. The problem no longer shows with check-in [582bd76828] applied. In fact I believed that it was initiated by my report on this list. Information below is just FYI. Are you compiling SQLite with any special symbols or anything like that? Not that I am aware of. Certainly nothing except the usual which I did before the problem surfaced. Are you able to build other programs using this memory manager? If so, does the bug show up when you pass the problematic SQL to the command line tool twice? Or if I send you a C program are you able to build and test it? I am not able to use the exact same memory manager when I run C code directly. Alternatively, I did just now run SQLite on Embarcadero's C++ Builder with CodeGuard enabled. This results in a magnitude of error reports while the SQLite code still produces correct results AFAIKS. It is impossible for me to tell if these reports also contain the one I reported earlier. Knowing little about how well CodeGuard does its job, I am not sure how seriously I shall take its warnings as long as all works well. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUPY BY alias backward incompatibility
At 15:37 28.05.2009, D. Richard Hipp wrote: Have you tried these two queries on other SQL database engines besides SQLite? What do PostgreSQL and MySQL make of them? I could now run the queries on Oracle Database 10g Express Edition Release 10.2.0.1.0. Prepare the table: create table t (c integer); insert into t values (1); insert into t values (2); Query with t_outer (the one that fails SQLite 3.5.3) runs without error: select (select count(*) from t t_inner group by t_outer.c) -- t_outer !!! from t t_outer; Query with t_inner (which works on all SQLite versions) fails with error ORA-01427: single-row subquery returns more than one row: select (select count(*) from t t_inner group by t_inner.c) -- t_inner !!! from t t_outer; Preliminary conclusion: * Behaviour is inconsistent between MySQL and Oracle. * The query that fails on SQLite 3.5.3 works on both MySQL and Oracle. * The query that works on SQLite all versions fails on Oracle. Additional findings from other DB engines would be helpful. Anyone? Other than that, I believe it would be desirable if SQLite would support the t_outer query as it did up to 3.5.3. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUPY BY alias backward incompatibility
Hello! Up to version 3.5.3, SQLite would happily execute this SQL without complaints: drop table if exists t; create table t (c); select (select count() from t t_inner group by t_outer.c) from t t_outer; This behaviour changed in version 3.5.4. From then on, SQLite issues an SQL error near line 4: no such column: t_outer.c. This also shows in the most recent version (3.6.14). I searched both the change log and the timeline for 3.5.4 but could not find any explicit mention of GROUP BY. I did note, however, that http://www.sqlite.org/releaselog/3_5_4.html talks about bringing the processing of ORDER BY into compliance with the SQL standard. Questions: * Does the ORDER BY change also apply to GROUP BY? * Are there any test cases for the new behaviour? I failed to find any in 3.6.14 test suite. * Post 3.5.3, the query works fine if I GROUP BY the inner table: select (select count() from t t_inner group by t_inner.c) from t t_outer; Is this the intended behaviour and should users update their SQL accordingly? Thanks for any answers, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUPY BY alias backward incompatibility
At 13:27 28.05.2009, D. Richard Hipp wrote: On May 28, 2009, at 7:01 AM, Ralf Junker wrote: Hello! Up to version 3.5.3, SQLite would happily execute this SQL without complaints: drop table if exists t; create table t (c); select (select count() from t t_inner group by t_outer.c) from t t_outer; This query does not make any sense. What are you trying to do? The above query is just a shortened version to demonstrate the backward incompatibilty. The original is far longer and calculates counts and average counts: DROP TABLE IF EXISTS RemSymptoms; DROP TABLE IF EXISTS SymCategory; CREATE TABLE RemSymptoms (SymId); CREATE TABLE SymCategory (CatID, SymId); SELECT SymCategory.CatID, round(1000*COUNT(*) / (SELECT COUNT(*) FROM SymCategory TempSymCategory WHERE TempSymCategory.CatId=SymCategory.CatID GROUP BY SymCategory.CatID)) AS CatAvgRemCount, (SELECT COUNT(*) FROM SymCategory TempSymCategory WHERE TempSymCategory.CatId=SymCategory.CatID GROUP BY SymCategory.CatID) AS CatSymCount FROM RemSymptoms INNER JOIN SymCategory ON RemSymptoms.SymId=SymCategory.SymId GROUP BY SymCategory.CatID; A user found that this query no longer works after upgrading to SQLite 3.5.4 or later. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUPY BY alias backward incompatibility
At 13:27 28.05.2009, D. Richard Hipp wrote: On May 28, 2009, at 7:01 AM, Ralf Junker wrote: Hello! Up to version 3.5.3, SQLite would happily execute this SQL without complaints: drop table if exists t; create table t (c); select (select count() from t t_inner group by t_outer.c) from t t_outer; This query does not make any sense. What are you trying to do? The above query is just a shortened version to demonstrate the backward incompatibilty. The original is far longer and calculates counts and average counts: DROP TABLE IF EXISTS RemSymptoms; DROP TABLE IF EXISTS SymCategory; CREATE TABLE RemSymptoms (SymId); CREATE TABLE SymCategory (CatID, SymId); SELECT SymCategory.CatID, round(1000*COUNT(*) / (SELECT COUNT(*) FROM SymCategory TempSymCategory WHERE TempSymCategory.CatId=SymCategory.CatID GROUP BY SymCategory.CatID)) AS CatAvgRemCount, (SELECT COUNT(*) FROM SymCategory TempSymCategory WHERE TempSymCategory.CatId=SymCategory.CatID GROUP BY SymCategory.CatID) AS CatSymCount FROM RemSymptoms INNER JOIN SymCategory ON RemSymptoms.SymId=SymCategory.SymId GROUP BY SymCategory.CatID; A user found that this query no longer works after upgrading to SQLite 3.5.4 or later. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUPY BY alias backward incompatibility
At 15:37 28.05.2009, D. Richard Hipp wrote: Have you tried these two queries on other SQL database engines besides SQLite? What do PostgreSQL and MySQL make of them? MySQL (5.0.21) reports no erros on either of both queries: select (select count(*) from t t_inner group by t_outer.c) -- t_outer !!! from t t_outer; select (select count(*) from t t_inner group by t_inner.c) -- t_inner !!! from t t_outer; I do not have access to PostgreSQL right now. The SQLite help [1] says: The expressions in the GROUP BY clause do not have to be expressions that appear in the result. Reading this, I'd expect that both queries should run - even if the 1st one does not make much sense. Opinions? Ralf [1] http://www.sqlite.org/lang_select.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table-Alias Confusion or Bug?
Hello! Given this SQL: drop table if exists t; create table t (c); select * from t as ta where t.c = ta.c; SQLite3 chokes with the following error: SQL error near line 5: no such column: t.c Question: Is this the expected behaviour? I know that the where clause does not make sense, but shouldn't SQLite see both t.c and ta.c as the same columns instead of reporting the error? Thanks for any response, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table-Alias Confusion or Bug?
At 17:33 27.05.2009, D. Richard Hipp wrote: On May 27, 2009, at 11:26 AM, Ralf Junker wrote: Hello! Given this SQL: drop table if exists t; create table t (c); select * from t as ta where t.c = ta.c; SQLite3 chokes with the following error: SQL error near line 5: no such column: t.c Question: Is this the expected behaviour? I know that the where clause does not make sense, but shouldn't SQLite see both t.c and ta.c as the same columns instead of reporting the error? This is the behavior that I expected because I deliberately coded it that way. Once you us an AS clause, the table is thereafter known only by its AS name, not its original name. Thanks for the clarification! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat bug
Confirmed and created ticket http://www.sqlite.org/cvstrac/tktview?tn=3841. Ralf At 03:10 07.05.2009, Steve Bauer wrote: The following example demonstrates what seems to be a bug in group_concat. With the latest version of CVS: CREATE TABLE example (id INTEGER, x TEXT); CREATE TABLE table2 (key TEXT, x TEXT); CREATE TABLE list (key TEXT, value TEXT); INSERT INTO example VALUES (1, a); INSERT INTO table2 VALUES (a, alist); INSERT INTO table2 VALUES (b, blist); INSERT INTO list VALUES (a, 1); INSERT INTO list VALUES (a, 2); INSERT INTO list VALUES (a, 3); INSERT INTO list VALUES (b, 4); INSERT INTO list VALUES (b, 5); INSERT INTO list VALUES (b, 6); SELECT example.id, table2.x, (SELECT group_concat(list.value) FROM list WHERE list.key = table2.key) FROM example, table2; Output: 1|alist|1,2,3 1|blist|,4,5,6 I expected: 1|alist|1,2,3 1|blist|4,5,6 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive
At 15:37 20.04.2009, D. Richard Hipp wrote: Ticket #3811 has been addressed by enhancing the documentation to explain that journal_mode changes are only guaranteed to work if they occur prior to the first transaction. Whether or not a journal_mode change works after the start of the first transaction is undefined. Thanks! Even as I type this message, it occurs to me that we should probably lock down the journal_mode at the start of the first transaction. Otherwise, there are a bazillion cases of journal_mode changes at strange times (such as in the middle of a nested transaction) that could cause problems (segfaults) even if their behavior is undefined. In this case, it would make sense IMO that setting the journal_mode should generally (and especially after the 1st transaction) report the active mode so applications can check if the journal_mode change was in fact effective. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange SAVEPOINT behaviour with locking_mode=exclusive
I experience some strange behaviour with SAVEPOINT in combination with locking_mode=exclusive. Below is a script which I assembled from savepoint.text 13.1 to 13.4. Those tests run OK in normal locking mode, but fail in exclusive locking more. To reproduce, run the script below on a NEW and EMPTY database. The result difference is marked as follows: a b 1 2 3 4 5 6 7 8 9 10 11 12 13 14 - missing rows if run in exclusive mode 15 16 - on a new and empty database SQL PRAGMA locking_mode=exclusive; DROP TABLE IF EXISTS t1; BEGIN; CREATE TABLE t1(a PRIMARY KEY, b); INSERT INTO t1 VALUES(1, 2); COMMIT; PRAGMA journal_mode = off; BEGIN; INSERT INTO t1 VALUES(3, 4); INSERT INTO t1 SELECT a+4,b+4 FROM t1; COMMIT; BEGIN; INSERT INTO t1 VALUES(9, 10); SAVEPOINT s1; INSERT INTO t1 VALUES(11, 12); COMMIT; BEGIN; INSERT INTO t1 VALUES(13, 14); SAVEPOINT s1; INSERT INTO t1 VALUES(15, 16); ROLLBACK TO s1; ROLLBACK; SELECT * FROM t1; /SQL I wonder if this behaviour is intended of if there is an error in the library? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Report constraint name(s) in error message
Roger Binns wrote: when a named constraint is violated, the name of the constraint which actually failed is not included in the error message. There has been a ticket about this for over 3 years, and also includes a patch to fix it: http://www.sqlite.org/cvstrac/tktview?tn=1648 Thanks, the ticked escaped my notice. Given that there is a patch suggestion already, let's hope that it will eventually make it into the SQLite core. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tool to browse a sqlite database
BareFeet wrote: See a comparison of several GUI SQLite tools here: http://www.tandb.com.au/sqlite/compare/?ml SQLiteSpy is missing from the list. It is available from http://www.yunqa.de SQLiteSpy is a Unicode SQLite3 database browser GUI for Win32. Features include: * Database at a Glance The schema treeview displays all items contained in a database, including tables, columns, indexes and triggers. Press F5 to update the schema tree, double-click a table or view to display its data, use the context menu for frequently used commands. * Grid Cell Editing Table cells are editable in the grid: Display a table via the schema treeview, select a cell and press F2 to invoke the editor. Then modify and confirm to write your changes back to the table. * Data Type Display The native SQL data types are displayed with different background colors to help detect type errors. Type errors can cause performance degradation or wrong SELECT result sets if NULL values are confused with empty strings. * Full Unicode SQLiteSpy fully supports SQLite's Unicode capabilities. Data display and entry is completely realized as Unicode, including SQL commands. * Multiple SQL Edits Modern tabs are used to edit and display multiple SQL queries for easy comparison of query statements and results. SQL queries are executed by typing or loading them into the SQL edit. Then press F9 to run the query, or CTRL+F9 to run the current line or selection only. * Time Measurement SQL execution time is automatically measured and displayed to help optimize queries. * Regular Expressions The SQL keyword REGEXP is supported and adds the complete regular expression syntax of Perl 5.10 to SQLiteSpy. * Mathematical SQL Functions The following mathematical SQL functions are available in addition to the SQLite default: ACOS(), ASIN(), ATAN(), ATAN(), ATAN2(), CEIL(), CEILING(), COS(), COT(), DEGREES(), EXP(), FLOOR(), LN(), LOG(), LOG(), LOG2(), LOG10(), MOD(), PI(), POW(), RADIANS(), SIGN(), SIN(), SQRT(), TAN(), TRUNCATE(). * Data Compression The SQL functions COMPRESS() applies zlib's deflate to any text or BLOB value. The raw deflate data stream is returned. UNCOMPRESS() inflates this stream back to the original. Integers, Doubles, and Nulls are returned unchanged. * Compact Result Storage The internal data storage mechanism uses SQLite's native data types for optimal compatibility. As a result, SQLiteSpy uses far less memory than other SQLite managers and handles large tables much more efficiently. * Built in SQLite Engine SQLiteSpy comes as a single file executable with the SQLite database engine already build into the application. There is no need to distribute any DLLs, which makes SQLiteSpy easy to deploy with customers. * Easy Install Uninstall To run SQLiteSpy, just extract the SQLiteSpy.exe file to any directory and execute the file. No installation is needed when first started, the program creates a single file SQLiteSpy.db3 (a SQLite3 database) to store options and settings. It does not write any other files or to the registry. Uninstalling is as simple as deleting two files only: The application's executable and its options database file. * Freeware SQLiteSpy is Freeware for personal and educational use. If you are using SQLiteSpy commercially, your donation is welcome to promote the ongoing development of this software. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: Report constraint name(s) in error message
Hi, when a named constraint is violated, the name of the constraint which actually failed is not included in the error message. Example 1: create table con ( a text constraint must_not_be_null not null); insert into con values (null); Returns error con.a may not be null. Example 2: create table con ( a text constraint must_not_be_null check (a not null)); insert into con values (null); Returns error constraint failed. Especially with many constraints in place, constraint names can be really helpful to fulfill the constraint. In this regard, error message 2 in particular is not helpful at all in fixing the violation. It would therefore be nice if error messages could include the name of the constraint that failed. Also nice to have: If multiple constraints fail at the same time, mention all failing constraints and their names in a single error message. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tool to browse a sqlite database
RB Smissaert wrote: What SQLite version produced the file World.db3? I am not 100% sure about the exact SQLite version which I used to create the original World.db3, but I am VACUUMing it regularly to bring it up to date with recent versions. So I expect it should be some version after 3.6.8. I ask as my wrapper doesn't pick correctly the fields of a table. This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11. I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v. 3.6.11 and it reports 'ok'. Looks like a wrapper problem to me. BTW, SQLiteSpy looks a very nice GUI tool. Thanks! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Impossible to declare field type BIGINT PRIMARY KEY
Markus Hoenicka wrote: If there's a way to find out at runtime that a column has been defined as INTEGER PRIMARY KEY instead of as INTEGER, all is well and I'll be able to fix the sqlite driver accordingly. pragma table_info(table); In SQLite, this SQL returns all columns defined for table along with their types. Check the pk result column to see if a named column is a PRIMARY KEY column. Official documentation is here http://www.sqlite.org/pragma.html#pragma_table_info Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS 3 Crash in 3.6.5
Shawn Wilsher wrote: I seem to have found a crash that is 100% reproducible in SQLite 3.6.5. I'm managed to make a reduced test case in a C file that can be found here: http://files.shawnwilsher.com/2008/11/12/test.c I could reproduce the problem and opened this ticket to make sure it does not get overlooked: http://www.sqlite.org/cvstrac/tktview?tn=3497 Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memsys3 vs memsys5
Is there any reliable method to determine the minimum page-cache allocation size needed for a given page_size? sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, ...) Unfortunately, there is extra overhead involved which is not handled by the page cache but is allocated by malloc() if I understand correctly. From reading the sources (some weeks ago, the exact numbers might have changed) I determined an extra of at least 152 bytes are allocated for each page. Reference is nByteHdr in pager.c, plus an extra 4 byte (on 32 bit) for the slot the page hash (probably a little more to accommodate for empty slots). Interestingly, the page cache overhead size can differ between SQLite versions and also depends on SQLITE_OMIT_... compiler settings. 152 bytes might not seem much but this is nearly 15% for a page cache size of 1024 bytes, and nearly 30% for a 512 byte page cache. So you safe overhead by choosing a larger page size. Concluding, memory requirements are much higher than just pragma page_size * pragma cache_size This can sum up to lots of RAM when using page_size = 10 or more to speed up indexing of very large databases. Users should be aware of this when running SQLite on extreme (low RAM or high size database) scenarios. Ralf ___ 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_...
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? Sure. Here is the list: SQLITE_OMIT_ALTERTABLE SQLITE_OMIT_ANALYZE SQLITE_OMIT_ATTACH SQLITE_OMIT_AUTHORIZATION SQLITE_OMIT_AUTOINCREMENT SQLITE_OMIT_AUTOVACUUM SQLITE_OMIT_BETWEEN_OPTIMIZATION SQLITE_OMIT_BLOB_LITERAL SQLITE_OMIT_CAST SQLITE_OMIT_CHECK SQLITE_OMIT_COMPLETE SQLITE_OMIT_COMPOUND_SELECT SQLITE_OMIT_EXPLAIN SQLITE_OMIT_FLAG_PRAGMAS SQLITE_OMIT_FOREIGN_KEY SQLITE_OMIT_GET_TABLE SQLITE_OMIT_GLOBALRECOVER SQLITE_OMIT_INCRBLOB SQLITE_OMIT_INTEGRITY_CHECK SQLITE_OMIT_LIKE_OPTIMIZATION SQLITE_OMIT_LOAD_EXTENSION SQLITE_OMIT_MEMORYDB SQLITE_OMIT_OR_OPTIMIZATION SQLITE_OMIT_PAGER_PRAGMAS SQLITE_OMIT_PROGRESS_CALLBACK SQLITE_OMIT_QUICKBALANCE SQLITE_OMIT_REINDEX SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS SQLITE_OMIT_SHARED_CACHE SQLITE_OMIT_SUBQUERY SQLITE_OMIT_TCL_VARIABLE SQLITE_OMIT_TEMPDB SQLITE_OMIT_TRACE SQLITE_OMIT_TRIGGER SQLITE_OMIT_UTF16 SQLITE_OMIT_VACUUM SQLITE_OMIT_VIEW SQLITE_OMIT_XFER_OPT I have reconstructed a few tests from rtree1.test and the errors only show with the SQLITE_OIMIT_... symbols defined. Otherwise the tests run just fine. 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. Right, I did not think of the fact that with SQLITE_OMIT_ALTERTABLE defined the parser already triggers a syntax error so it never reaches the virtual tree. 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. Agreed, but this would not save much footprint so it might not be worth the effort. I was more thinking in terms of detecting potential errors at compilation before they occur at run-time. Many thanks for looking into this, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree woes with SQLITE_OMMIT_...
I am running the rtree module against an SQLite build which has lots of functionality SQLITE_OMIT_...ed. 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 x12; 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? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem building reduced-size sqlite
Steve Friedman wrote: When trying to build sqlite (latest cvs update) with the following: ../sqlite-latest/configure --disable-threadsafe --enable-tempstore=always \ --disable-tcl --enable-extensions \ CFLAGS=-g -O3 -DSQLITE_ENABLE_RTREE=1 \ -DTEMP_STORE=2 -DSQLITE_DEFAULT_CACHE_SIZE=100 \ -DSQLITE_DEFAULT_PAGE_SIZE=32768 \ -DSQLITE_OMIT_ALTERTABLE=1 \ From http://www.sqlite.org/compile.html: 1.5 Options To Omit Features The following options can used to reduce the size of the compiled library by omitting optional features. This is probably only useful in embedded systems where space is especially tight, as even with all features included the SQLite library is relatively small. Don't forget to tell your compiler to optimize for binary size! (the -Os option if using GCC). Telling your compiler to optimize for size usually has a much large impact on library footprint than employing any of these compile-time options. The macros in this section do not require values. The following compilation switches all have the same effect: -DSQLITE_OMIT_ALTERTABLE -DSQLITE_OMIT_ALTERTABLE=1 -DSQLITE_OMIT_ALTERTABLE=0 If any of these options are defined, then the same set of SQLITE_OMIT_XXX options must also be defined when using the 'lemon' tool to generate a parse.c file. Because of this, these options may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-UNIX like platforms on the website. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Follow-up: changed default ordering of SELECT queries in 3.5.9?
Hello Jens Miltner, However, I'd still like to know whether the new behavior of returning the DISTINCT results in 'arbitrary' order is expected. Unless ORDER BY is specified, the result of any SELECT is by definition undefined. This undefined sort order has changed between previous versions and might do so again in the future. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Darren Duncan wrote: Ralf Junker wrote: Can you suggest an alternative to a single reserved name to represent the column which uniquely identifies a database record under any and all circumstances? Yes, change the interface to RowID into a routine call rather than a column name; eg use RowID() rather than RowID. I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not? Then when using it in a SELECT, you can say RowID() as foo in the select list where foo is different than a normal table field. Such is how 'standard' SQL does it. What is 'standard SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? Any manager app can read the database schema first and generate a name foo that is distinct. As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three RowID, _rowid_, and OId column names are overridden. This applies to SQL as well as to user-defined functions. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Bradley A. Town wrote: Ralf Junker wrote: This alerts me to a potential danger for SQLite managers which must rely on some means to retrieve THE RowID which uniquely identifies a record for in-grid table editing. If the RowID name can be hijacked by other columns and given another purpose, it poses the danger that wrong wrong columns are updated and data is corrupted. How can I access the RowID given the above table declaration? I know about the OID and _ROWID_ synonyms, but searching the documentation I find that they, too, can be used by other columns. I can therefore not see any non-ambiguous, reserved column name or API call to retrieve the implicit RowID value in such cases, especially if no primary key has been set like in the above schema. Any thoughts, especially from the SQLite developers? Thanks, Ralf Creating another thread for this to avoid thread hijacking. Thanks, I did not mean to hijack the thread. But thinking more about hijacking RowID I am glad this is now a separate thread. Lack of a reseverd RowID column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I would very much appreciate if this could be addressed in a future version of SQLite! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Federico Granata wrote: have you seen here http://www.sqlite.org/autoinc.html ? Yes, I did. This documentation actually made me realize that the problem is not an implementation flaw but a design error, IMO. See my other answer in this thread for more rationale. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Darren Duncan wrote: I think the real problem here is that SQL allows you to have non-distinct rows in a table, when all rows should be distinct. SQLite's implicit RowID does not allow non-distinct values (except for NULL, but this is documented behavior and only maintained for backwards compatability. It might change in a future version. The sooner, the better, IMHO). Working within SQL's flaws, the solution here is for every table to have a unique constraint on one or more table columns. Then applications just use that to uniquely identify the row. This is exactly the concept of RowID. Nothing wrong with that. My only criticism is that this concept can be rendered non-functional by redefining the RowID so that it violates the uniqueness constraint. Example: CREATE TABLE x ( RowID TEXT); Now the implicit unique RowID is no longer accessible via the RowID column. Workarounds are _rowid_ or OID, but they can be overwritten as well: CREATE TABLE x ( RowID TEXT, _rowid_ text, oid text); For this table, it is no longer possible to access the implicit, unique RowID. General database applications (GUI managers, for example) can no longer (re-) identify a particular record! Rows should be identifiable by user-visible data, not hidden data, since a database is supposed to model reality and people identify things based on their someway-visible attributes. This is what INTEGER PRIMARY KEY is for: It works as a duplicate for the implicity RowID: CREATE TABLE x ( ID INTEGER PRIMARY KEY); For this table, the visible ID and the implicit RowID access the same unique data. This is the recommended usage and poses no problems. Problems only arise if RowID is re-defined differently as demonstrated above! On a separate note, it is best for one to be able to name a table or column et al anything one wants, with all the choice of names as you can store in a text column for user data. Reserved words aren't an issue as long as entity names are referred to with an unambiguously different syntax, such as quoted identifiers as SQL does support. Then database users don't have to worry about implementation details and can name tables and columns whatever they want; saying they can't name their column RowID is a leaky abstraction. Sure we all dislike restrictions. Can you suggest an alternative to a single reserved name to represent the column which uniquely identifies a database record under any and all circumstances? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT - GROUP BY can lead to performance degradation
Correcting myself: This should NOT happen as SQLite usually rejects duplicate rowids with a constraint error. This should happen as SQLite usually rejects duplicate rowids with a constraint error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT - GROUP BY can lead to performance degradation
I looked at the database attached to the ticked and noticed that the table contains NULL rowids as well duplicate rowids. This should happen as SQLite usually rejects duplicate rowids with a constraint error. When I run this query: select rowid, count() from ndxparamvalues_localizedstring group by rowid order by 2 desc I get the following results: rowid count () NULL 1759 10 1601 20 1341 30 1281 40 1254 50 1200 ... more results follow, 3161 in total. So I wonder how you managed to fill your database with duplicate rowids? I also wonder if this is somehow related to your problem? Ralf I've only found one reference to slower queries with the DISTINCT/GROUP BY optimization that went in back in November for 3.5.3 and later. I would have expected more given the number of our queries causing problems. The problem as I wrote in ticket 3128 http://www.sqlite.org/cvstrac/tktview?tn=3128 appears to be with queries that use DISTINCT and LIMIT. If the query is somewhat slow (in my example, it's joining several large tables), the new DISTINCT (implemented in the code as GROUP BY) can be much, much slower because it (apparently) collects all or most of the rows before applying GROUP BY, even when there's a LIMIT. Before the change (3.5.2 and earlier), DISTINCT was aided by the LIMIT quite a bit. In my example, the query takes around 0.02 seconds in 3.5.2 and earlier, 3.1 seconds in 3.5.3 and later. Has anyone else seen similar behavior? If so, have you found a workaround? My workaround is actually a patch to the source to disable the optimization when a LIMIT is given, though there might be cases where this is undesirable (like maybe cases where the table has indices that can be used by GROUP BY and the LIMIT is sufficiently high). Brad Town ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticket 3128: DISTINCT - GROUP BY can lead to performance degradation
This should NOT happen as SQLite usually rejects duplicate rowids with a constraint error. For this table, the badly-named column RowID is actually a text field that's allowed to be null, not a primary key. -- Describe NDXPARAMVALUES_LOCALIZEDSTRING CREATE TABLE 'ndxParamValues_LocalizedString' ( 'FamilyId' INTEGER NOT NULL , 'ParamId' INTEGER NOT NULL , 'RowID' TEXT COLLATE NOCASE, 'Index' INTEGER , 'Value' INTEGER NOT NULL ) My oversight, sorry for that. I never questioned that SQLite would reject reserved word column names, but I now see that this is not so. This alerts me to a potential danger for SQLite managers which must rely on some means to retrieve THE RowID which uniquely identifies a record for in-grid table editing. If the RowID name can be hijacked by other columns and given another purpose, it poses the danger that wrong wrong columns are updated and data is corrupted. How can I access the RowID given the above table declaration? I know about the OID and _ROWID_ synonyms, but searching the documentation I find that they, too, can be used by other columns. I can therefore not see any non-ambiguous, reserved column name or API call to retrieve the implicit RowID value in such cases, especially if no primary key has been set like in the above schema. Any thoughts, especially from the SQLite developers? Thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
Drat. It doesn't look like there's a way to see what's already been bound to a statement either, correct? See this thread for a previous disuccsion of the problem: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode not implemented?
In PERSIST mode, you have two files associated with your database instead of one. Whenever you move, copy, or rename the database file you *must* also move, copy or rename the journal file to prevent database corruption. The persistent journal file uses disk space that might otherwise have been returned to the operating system and made available to other programs. Say I have a database open in PERSIST mode for fast operation. Before the app shuts down, I would like to detach the database of its journal file (to free some storage space and guarantee a self contained, single file database). Will a simple PRAGMA journal_mode=DELETE do this for me and automatically delete the journal file when (or even before) I close the database? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
