Re: [sqlite] query 31 days
Perhaps you could create a table with the results (e.g. columns yearmonth, date, hasrecord=0|1) and create triggers for the main table so that if the records change, the triggers change the value in the result table. Jakub Tom Deblauwe wrote: Hello, I'm using SQLite 2.8.16, on an embedded system, and I have a question. I have a database with one table, containing all my records. Each has a field 'yearmonth' which contains for example '200508', and also a field 'date', which indicates the day of that month. I want now to make an overview of a month and see which days contain a record. I've created a combined index on 'yearmonth' and 'date'. My query to do this is: 'select distinct date from mytable where yearmonth = 200508;' This takes about 50 seconds, which is really slow to show my users(it is displayed on a webpage). I noticed that this takes a long time, because I guess almost all records are checked. My other idea was to do 31 queries(one for every day), like this: 'select ID from mytable where yearmonth = 200508 and date = 1 limit 1;'. This second method does not give me anymore speed improvement. for example: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ... ^ ^ ^ ^ the days marked with '^' contain records. To check with the second method for example day 7, it takes 2.6 seconds. Day 4 takes less, and from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and higher takes about 1 second each. When I do a random query om my database with select, and want to return 10 results for example, it takes 2.8 seconds. So getting 1 result, or getting 10 results only differs about 0.2 seconds. Is there anything I can do so things go faster? Maybe I should maintain a separate table with the monthoverview, but if it is possible to do some other solution it would be nicer ;-). Thanks for your time, kind regards, Tom, -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.dynavix.com Tel: +420 608 247197 Office: +420 224233470
Re: [sqlite] Index with two columns
Richard, thank you very much. Jakub D. Richard Hipp wrote: On Fri, 2005-06-03 at 13:20 +0200, Jakub Adamek wrote: Hello, please, is there any way to make SQLite use an index on two columns when I want to select all rows which have some combination of the two columns? SELECT * FROM PointFeature WHERE DsetId=203 AND SectId IN (4,400); SQLite does use multiple columns of a multi-column index for == constraints. But for an IN operator, it will only using a single column. This is something that I need to work on. In the meantime, I suggest the following work-around: SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=4 UNION ALL SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=400;
Re: [sqlite] updating records problem
I believe you are wrong. The rowid is the primary key in the SQLite B-tree representation. It is always unique and allows you not to use your own primary key. But if you have a column of type INTEGER PRIMARY KEY, the values in this column become rowid. Otherwise a new autoincremented column is created. Jakub Jay Sprenkle wrote: rowid won't work in a multiuser database. It's just the row number within the result set you retrieved. This sort of problem is the reason why people use a unique id field. The database automatically assigns you a number that won't change and isn't shown to the user (they can't change it either). On 5/17/05, Will Leshner <[EMAIL PROTECTED]> wrote: The problem is that the 'name' and 'age' fields are not sufficiently unique to identify the very row the user wanted to update. Instead every row that has matching names and ages are going to be updated.
Re: [sqlite] database table is locked
Hello, I had a similar experience, but there was too much code to create a simple bug report. Surprisingly, the error disappeared if I first made a "SELECT FROM table" before the "INSERT INTO table" on the same table. Also the error description "database TABLE is locked" is sometimes wrong because it is the whole database which is locked. Perhaps another error code/message should be created for "database is locked" (e.g. with open cursors). Jakub Marcel Strittmatter wrote: Hi all When I try to insert data into a table, sqlite3 responses with SQLITE_ERROR and the error message: "database table is locked". I searched already for unfinalized statements but couln't find any. The insert statement is not executed while a query is active... The problem exists only on Windows (local storage), on Linux everything (except known nfs problems) works well (same code). My question: Is there a "easy" way to test if a table is locked? Are there other possibilities to help debug such a problem (I already tried sqlit3_trace, but this doesn't help much because I don't see if a query is finalized or not). Marcel
Re: [sqlite] Sub-Select
Michael, it is because SQLite 3.1.3 changed (or has errors in, it is a matter of opinion) the column naming. Try >> SELECT max(Sendung) >> FROM >> (SELECT S.Sendung AS Sendung >> FROM Auftrag AS A, >> Sendung AS S >> WHERE(A.PosyDat = 1 AND A.PosyDat = S.PosyDat) AND >> (A.PosyNId = 6 AND A.PosyNId = S.PosyNId) AND >> (A.DateiNum = 1 AND A.DateiNum = S.DateiNum) AND >> (A.ReprintNum = 0 AND A.ReprintNum = S.ReprintNum) AND >> (S.Sendung >= 1 AND S.Sendung <= 600) >> LIMIT300) AS Sub; Michael Knigge wrote: Hi, sorry... I forgot I get the error "no such column: Sub.Sendung" So I guess/hope it's only a "syntax error" with my SQL - my SQL-Skills are rather bad ;-( Thanks, Michael All, could someone please tell me why this query doesn't work with 3.1.3 (with 3.0.8 it works): SELECT max(Sub.Sendung) FROM (SELECT S.Sendung FROM Auftrag AS A, Sendung AS S WHERE(A.PosyDat = 1 AND A.PosyDat = S.PosyDat) AND (A.PosyNId = 6 AND A.PosyNId = S.PosyNId) AND (A.DateiNum = 1 AND A.DateiNum = S.DateiNum) AND (A.ReprintNum = 0 AND A.ReprintNum = S.ReprintNum) AND (S.Sendung >= 1 AND S.Sendung <= 600) LIMIT300) AS Sub; Thanks a lot, Michael
Re: [sqlite] ticket 1147
For me it is not important to know from which table the column comes, but it is a must to have unique column names - because I address all columns by their names. I could also use the column order but this would lead to worse readability and maintainability. Therefore my wrapper protests when it encounters two columns with the same name. I never thought about * as a dangerous thing. The query is shorter and there are less places to modify when I change or add columns. But it is true that VIEWs work with * other way than SELECTs and also because of all this discussion I think I will refrain from using *. The query I posted was a very quick draft of the real query, it was SELECT * FROM BinaryTarget INNER JOIN TargetDescription ON BinaryTarget.FeatureItemId = TargetDescription.FeatureItemId; and I also tried SELECT TargetDescription.*, BinaryTarget.* which did not help. I than thought these are bugs in SQLite and did not try to change the queries. But now I see that even if the parser was modified to return the column names working for me now, it is a very vulnerable part which could easily change in next versions. Jakub D. Richard Hipp wrote: On Mon, 2005-02-28 at 08:48 -0700, Robert Simpson wrote: 5. What we do with the schema information or how well we compute it is irrelevant. No. It is exceedingly relevant if you want any cooperation from me in addressing the issue. There seem to be a lot of people who are emphatic about knowing which column in which table a value in the result set originated from. This makes no sense to me. Why do they care? What do these people do with result set values that originate from expressions or which are constants? What about the result set of compound selects or of natural joins where the origin column is ambiguous? If knowing the original column is so important, what do people do with those cases? Disallow them? What do other database engines (PostgreSQL, Oracle, MySQL) do in the way of revealing the originating column for result set values? Do they have some mysterious API that I have never seen? And why do people care? Can nobody give me a use case where it is important to know what the originating column for a result set value is?
[sqlite] Version 3.1.3 is a headache
Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I have already posted 3 tickets with rather serious problems with column names. The last one is really annoying and I can't believe the auto-tests could have missed it ... create table a (id, x); create table b (id, y); insert into a values (1,1); insert into b values (1,2); select * from a inner join b; column names returned: id,x,id,y How am I supposed to use such column names? Ouwey. No wonder that my C++ wrapper does not want to work with such a result set. Jakub
Re: [sqlite] String Concatenation
I think it is not possible. You can't express an arbitrary loop with SQL. Perhaps you could write a group-by user function doing this, I am not sure, but user functions are again C code. Jakub Keith Herold wrote: I have a semi-quick question about how to do successive string concatenation. I have a table: CREATE TABLE mystrings ( OwnerID INTEGER NOT NULL, AString VARCHAR(900), Sequence INTEGER DEFAULT 0 ); Each AString is associated with an Owner; a single owner can have multiple strings assigned to it, with the order of the strings held by sequence: OwnerID, AString, Sequence 1, 'concatenate', 0 1, 'some', 1 1, 'strings', 2 1, 'together', 3 What I need to do is create a single string out of the AString, Sequence pairs, for a given owner. Obviously I could do this through some C++ code, but I would prefer to do it within SQL code, but can't think of a way. The sequences are not always the same lenght, i.e., a particular owner may have 1, 10, or 1972 word long sequences. With cursors, I don't think this would be difficult, but I can't figure out how to do this within SQLite. Any help? --Keith -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
Re: [sqlite] Row count in database
Richard, and how about doing both? Using BEGIN ... COMMIT and at the same time turning off syncing? Jakub D. Richard Hipp wrote: Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. I've been running some tests. I find that INSERTs (into a table with 5 indices) are 3.5 times faster if you put them inside BEGIN...COMMIT rather than turning off syncing.
Re: [sqlite] coding style
Hi all, I have a nasty problem with VIEWs and column names. I create a VIEW by joining two tables. If I use this view to create the table 'problem', the column name "t1.a" appears instead of the expected (and needed) "a". Is it a bug? Do you have an idea how to use the view so as it works? Thanks, Jakub create table t1 (a,b); create table t2 (a,c); insert into t1 values (1,2); insert into t2 values (1,3); create view myview as select t1.a a from t1 inner join t2 on t1.a=t2.a; create table problem as select * from myview;
Re: [sqlite] Version 3.0.8
Does this version support the verious BEGIN TRANSACTION types? Thank you, Jakub D. Richard Hipp wrote: Version 3.0.8 is now available on the website. http://www.sqlite.org/. The primary change is a series of code size optimizations. There are also some obscure bug fixes and a few minor enhancments. If you are not having problems with version 3.0.7 there is little reason to upgrade. -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
Re: [sqlite] eVC++ 3.0 port
Did you try the sqlite-wince port? http://sqlite-wince.sf.net Jakub Stober, Mel wrote: has anyone ported sqlite 3.0 to PocketPC using eVC++ 3.0 compiler ? I'm trying it now and am having quite a few problems. For example, functions in are not supported as well as file locking. I'm also getting millions of warnings because program is attempting to downgrade from int64 to int without typcasting, and even with typcasting that is a dangerous thing to do. If it doesn't need a 64-bit integer when why use it in the first place??? Mel Stober -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
Re: [sqlite] Version 3.0.7
Thank you, I am looking forward to it. The Sqlite-Wince is really useful, I only copy it to my directory and compile on Win32, WinCE, Linux and QNX. Jakub Nuno Lucas wrote: Jakub Adamek, dando pulos de alegria, escreveu : That's GREAT ! Nuno, are you going to merge Sqlite-Wince STABLE ? I'll do that during this week. Regards, ~Nuno Lucas -- Jakub Adamek Programmer Telematix Software, a.s. Karoliny Svetle 12, Praha 1 http://www.telematix.cz Tel: +420 608 247197 Office: +420 224233470
[sqlite] SQLite 3 release
Hello, Dr. Hipp, please, what is the time estimate for releasing the version 3? I believe that it's not only me looking forward to it! It is too lovely, the version 3, how long should I live with version 2? :-)) Jakub
Re: [sqlite] Is version 3 seriously broken?
Nuno, hurrah, it works on first tests. I will run more later. Clever idea! I am now looking much forward to the release ... Thanks for your work. Jakub Nuno Lucas wrote: Jakub Adamek, dando pulos de alegria, escreveu : > Nuno, I am much surprised that version 3.0.5 helped you. It didn't help > me. Neither my nor your port. BUT your remark helped me! You are right > that it is because of stack space, and the default setting in Windows CE > projects is 0x1, i.e. 65 kB. After changing to 0x10, i.e. 1 MB > my test program which first added 1000 rows of size 0..2000, than > deleted all of them and created 1000 tables works fine. Did you perhaps > also change this setting? Now that that you talked about it, I remembered an irritating bug in VC++ that forces us to rebuild all after changing linking options (clean, build doesn't work). You are right, it's the same... But the good news is that I found the cure (well, thinking about it, I should have remembered about it earlier). There is a SQLITE_MAX_PAGE_SIZE constant exactly for the sake of embedded systems. I committed a new version, where I defined that to be 1024 (instead of the default of 8192) and it works now. I tried 2048 but it isn't low enough, too. If someone wants more, it will have to increase the stack size in the linking options, and it should be done by someone that understands what it wants to do. > Are you going to merge 3.0.6? Already done and committed, stay tuned for the release... Regards, ~Nuno Lucas
Re: [sqlite] Is version 3 seriously broken?
Nuno, I am much surprised that version 3.0.5 helped you. It didn't help me. Neither my nor your port. BUT your remark helped me! You are right that it is because of stack space, and the default setting in Windows CE projects is 0x1, i.e. 65 kB. After changing to 0x10, i.e. 1 MB my test program which first added 1000 rows of size 0..2000, than deleted all of them and created 1000 tables works fine. Did you perhaps also change this setting? Are you going to merge 3.0.6? Best regards, Jakub Nuno Lucas wrote: Jakub, I must ask you to forgive me as only now noticed you were not the sender of the tracker message for the bug. Sorry for the confusion (I must stop replying messages late at night). In the code I tested it was always the 113th row because I only created a single table. I would assume it would happen sooner to you because you have several tables created. It's a little difficult to test your code as it is dependent on other libraries. Could you post just the SQL statements used so we could check it with the new SQLite-wince v3.0.5? As I don't know the classes you are using I have no way of checking the stack use of them (could they be creating rowsets in the stack, for example?). Anyway, with the 3.0.5 port, I could now insert 5 rows without problems. Maybe you could check if my merge is different from yours and if it works now. Regards, ~Nuno Lucas Jakub Adamek, dando pulos de alegria, escreveu : Nuno, I have changed back to SQLite 2 and have my code ready to switch to version 3 again if the bug is repaired. The thing happens when first calling balance_nonroot, which is in your case always on the 113th row, in my case on the 33th row and in the mail message http://www.mail-archive.com/[EMAIL PROTECTED]/msg03112.html always at the sixth CREATE TABLE. I did try SQLite 3.0.5 and copied the changes as in SQLite-WinCE with no improvement. I would agree with too much stack space, which may be perhaps solved by using malloc() and free() in sqlite_nonroot. Jakub
Re: [sqlite] Is version 3 seriously broken?
Nuno, I have changed back to SQLite 2 and have my code ready to switch
to version 3 again if the bug is repaired. The thing happens when first
calling balance_nonroot, which is in your case always on the 113th row,
in my case on the 33th row and in the mail message
http://www.mail-archive.com/[EMAIL PROTECTED]/msg03112.html
always at the sixth CREATE TABLE. I did try SQLite 3.0.5 and copied the
changes as in SQLite-WinCE with no improvement.
I would agree with too much stack space, which may be perhaps solved by
using malloc() and free() in sqlite_nonroot.
Jakub
Nuno Lucas wrote:
Jakub,
the "official" sqlite version isn't broken, it's the WinCE port that is.
I decided to announce that in the SQLite-WinCE page so people wouldn't
expect it to just work. There are other issues with the WinCE port (like
the Unicode handling, and that is also in the official version), but
this is serious enough because it crashes (with a stack overflow
exception) after inserting just 113 rows (and always 113 rows).
My guess is that the official version allocated too much stack space,
resulting in that exception.
---
I now have tested the same program with SQLite v3.0.5 (inserted 5
rows) and the problem disappeared. So something was corrected in the
source code that makes it use less stack.
I would advise you to wait until I
commit those changes to CVS and make a new release so you can try by
yourself.
I annexed your test program (with your bugs corrected), so you can check
it works now.
Regards,
~Nuno Lucas
/* BUG: the right header to include is "sqlite3.h".
"sqliteint.h" is an internal header and can't be used safelly */
/*/
#include
#include
#include
#include
bool TestExecuter( sqlite3 *db_hnd, char *query, ... )
{
// char **_result_set;
// int _n_row=0;
// int _n_col=0;
// char *_error_msg;
va_list tmp_args;
va_start( tmp_args, query );
char *tmp_query = sqlite3_vmprintf( query, tmp_args );
sqlite3_stmt * stmt = 0;
const char* tail;
// int tmp_ec = sqlite3_get_table( db_hnd, tmp_query, &_result_set, &_n_row, &_n_col,
&_error_msg );
if ( sqlite3_prepare( db_hnd, tmp_query, strlen(tmp_query), , ) )
{
OutputDebugString( _T("sqlite3_prepare: error\n") );
return false;
}
int rc = sqlite3_step( stmt );
switch ( rc )
{
case SQLITE_ROW : // First row ready
break;
case SQLITE_DONE: // Ok, simply no results for this command
break;
default : // Some error
OutputDebugString(
(LPCWSTR)sqlite3_errmsg16(db_hnd) );
}
rc = sqlite3_finalize( stmt );
if ( rc != SQLITE_OK )
OutputDebugString( (LPCWSTR)sqlite3_errmsg16(db_hnd) );
// Libero la query.
sqlite3_free(tmp_query);
/** BUG: no release of result set */
// sqlite3_free_table( _result_set );
/**/
// if ( (_n_row == -1) || (_error_msg != NULL) )
// {
// TCHAR buf[512];
// _stprintf( buf, _T("Query Failed! - errmsg: %hs\n"), _error_msg );
// OutputDebugString( buf );
// /** BUG: no release of error message */
// sqlite3_free( _error_msg );
// /**/
// }
// speedup things while testing
// else
// OutputDebugString( "Query Ok!\n" );
/** BUG: no va_end(tmp_args) */
va_end( tmp_args );
/**/
// return (_n_row == -1) || (_error_msg != NULL);
return rc == SQLITE_OK;
}
int Test_Insert2(sqlite3 *db_hnd)
{
// Create some tables with data that we can select against
TestExecuter( db_hnd, "CREATE TABLE d1(n int, log int)" );
TestExecuter( db_hnd, "BEGIN" );
char tmp_str[256];
for (int i=1;i<=5;i++)
{
// for ( int j=0; (1 << j) < i; j++ )
{
sprintf(tmp_str,"INSERT INTO d1 VALUES(%d,%d)",i,i);
TestExecuter( db_hnd, tmp_str );
}
}
TestExecuter( db_hnd, "END" );
TestExecuter( db_hnd, "SELECT * FROM d1 ORDER BY n" );
// finish_test
return 0;
}
#define DB_TEST "\\Test.db"
int WINAPI WinMain( HINSTANCE,HINSTANCE,LPTSTR,int )
{
sqlite3 * db;
int err = sqlite3_open( DB_TEST, );
if ( err )
return -1;
Test_Insert2( db );
/ BUG: no sqlite3_close can eventually corrupt the database */
sqlite3_close( db );
//
return 0;
}
Re: [sqlite] Is version 3 seriously broken?
I didn't mention that I applied the diffs from SQLite-WinCE to version
3.0.5 with the same results.
Jakub
Jakub Adamek wrote:
Hello,
the version 3 is most probably broken. I traced down the crash problem
in Windows CE, see
http://sourceforge.net/mailarchive/forum.php?thread_id=5438459_id=35230
It happens in balance_nonroot but in the declare variable section! If I
change the variables in balance_nonroot to "static", the test goes OK.
But such an error must come from a wrong memory write before this
function. I am not able to find this. I attach my test program, it only
creates a table and starts inserting rows.
As the simiral problem appears on other OSes, see
http://www.mail-archive.com/[EMAIL PROTECTED]/msg03112.html,
I wonder if some of the great developers could solve it?
Best regards
Jakub
Darren Duncan wrote:
At 2:20 PM -0400 8/29/04, D. Richard Hipp wrote:
SQLite version 3.0.5 (beta) is now available on the website.
Richard, thank you very much for that release.
I have downloaded and compiled it under both Mac OS X 10.2.8 (GCC 3.1,
3.3), and Mac OS X 10.3.5 (GCC3.3), with each development environment
having the default set of libraries and headers. It compiles without
errors (but with warnings about long integer types) on all
configurations, and ./sqlite3 starts up and quits normally.
Under both Mac OS X 10.2.8 configurations, 'make test' continues to
fail due to my TCL library being out of date, as I had reported
previously.
Under the Mac OS X 10.3.5 configurations, 'make test' succeeds, with a
result of '0 errors out of 22364 tests'. 10.3.5 does have a new
enough TCL bundled.
So at least with the newer setup, I can confirm that everything works.
Matt, I will eagerly test a newer DBD::SQLite as soon as you release it.
-- Darren Duncan
if (! db.execute (
"CREATE TABLE test ("
"i INTEGER NOT NULL,"
"v VARCHAR (50),"
"PRIMARY KEY (i));")) ERROR_RETURN_FALSE;
vector values;
values.push_back (12);
values.push_back ("ahoj brouku");
values.push_back (13);
values.push_back ("nazdar brouku");
if (! db.execute (
"INSERT INTO test VALUES (12, 'ahoj brouku')")) ERROR_RETURN_FALSE;
if (db.execute (
"INSERT INTO test VALUES (12, 'nazdar brouku')")) ERROR_RETURN_FALSE;
if (! db.execute (
"INSERT INTO test VALUES (13, 'nazdar brouku')")) ERROR_RETURN_FALSE;
if (db.isExecutePrecompiledSupported()) {
vector params;
params.push_back (14);
params.push_back ("cau brundibare");
if (! db.executePrecompiled (
"INSERT INTO test VALUES (?, ?)", params)) ERROR_RETURN_FALSE;
if (db.executePrecompiled (
"INSERT INTO test VALUES (?, ?)", params)) ERROR_RETURN_FALSE;
values.push_back (14);
values.push_back ("cau brundibare");
}
COneWayRecordset rs (db);
if (! rs.openTable ("test")) ERROR_RETURN_FALSE;
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = 15;
rs ["v"] = "Hello world";
if (! rs.update()) ERROR_RETURN_FALSE;
values.push_back (15);
values.push_back ("Hello world");
if (! rs.open ("SELECT i, v FROM test")) ERROR_RETURN_FALSE;
int irecord = 0;
bool ok;
while (rs.next()) {
int i = rs ["i"].forceInt ();
string s = rs ["v"].asString();
if (i != values [irecord*2].forceInt ()) ERROR_RETURN_FALSE;
if (s != values [irecord*2+1].asString()) ERROR_RETURN_FALSE;
irecord ++;
}
// fill some rows with string and binary data
if (! db.begin()) ERROR_RETURN_FALSE;
if (! rs.openTable ("test")) ERROR_RETURN_FALSE;
vector vec;
string s;
for (int row=1; row < 1000; row ++) {
vec.push_back ((uint8_t) (row & 0xFF));
s += long2string (row);
CVariantValueBinary binary (& *vec.begin(), vec.size());
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = row * row;
rs ["v"] = s;
if (! rs.update()) ERROR_RETURN_FALSE;
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = row * row + 1;
rs ["v"] = binary;
if (! rs.update()) ERROR_RETURN_FALSE;
}
if (! db.close()) ERROR_RETURN_FALSE;
return true;
[sqlite] Is version 3 seriously broken?
Hello,
the version 3 is most probably broken. I traced down the crash problem
in Windows CE, see
http://sourceforge.net/mailarchive/forum.php?thread_id=5438459_id=35230
It happens in balance_nonroot but in the declare variable section! If I
change the variables in balance_nonroot to "static", the test goes OK.
But such an error must come from a wrong memory write before this
function. I am not able to find this. I attach my test program, it only
creates a table and starts inserting rows.
As the simiral problem appears on other OSes, see
http://www.mail-archive.com/[EMAIL PROTECTED]/msg03112.html,
I wonder if some of the great developers could solve it?
Best regards
Jakub
Darren Duncan wrote:
At 2:20 PM -0400 8/29/04, D. Richard Hipp wrote:
SQLite version 3.0.5 (beta) is now available on the website.
Richard, thank you very much for that release.
I have downloaded and compiled it under both Mac OS X 10.2.8 (GCC 3.1,
3.3), and Mac OS X 10.3.5 (GCC3.3), with each development environment
having the default set of libraries and headers. It compiles without
errors (but with warnings about long integer types) on all
configurations, and ./sqlite3 starts up and quits normally.
Under both Mac OS X 10.2.8 configurations, 'make test' continues to fail
due to my TCL library being out of date, as I had reported previously.
Under the Mac OS X 10.3.5 configurations, 'make test' succeeds, with a
result of '0 errors out of 22364 tests'. 10.3.5 does have a new enough
TCL bundled.
So at least with the newer setup, I can confirm that everything works.
Matt, I will eagerly test a newer DBD::SQLite as soon as you release it.
-- Darren Duncan
if (! db.execute (
"CREATE TABLE test ("
"i INTEGER NOT NULL,"
"v VARCHAR (50),"
"PRIMARY KEY (i));")) ERROR_RETURN_FALSE;
vector values;
values.push_back (12);
values.push_back ("ahoj brouku");
values.push_back (13);
values.push_back ("nazdar brouku");
if (! db.execute (
"INSERT INTO test VALUES (12, 'ahoj brouku')")) ERROR_RETURN_FALSE;
if (db.execute (
"INSERT INTO test VALUES (12, 'nazdar brouku')")) ERROR_RETURN_FALSE;
if (! db.execute (
"INSERT INTO test VALUES (13, 'nazdar brouku')")) ERROR_RETURN_FALSE;
if (db.isExecutePrecompiledSupported()) {
vector params;
params.push_back (14);
params.push_back ("cau brundibare");
if (! db.executePrecompiled (
"INSERT INTO test VALUES (?, ?)", params)) ERROR_RETURN_FALSE;
if (db.executePrecompiled (
"INSERT INTO test VALUES (?, ?)", params)) ERROR_RETURN_FALSE;
values.push_back (14);
values.push_back ("cau brundibare");
}
COneWayRecordset rs (db);
if (! rs.openTable ("test")) ERROR_RETURN_FALSE;
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = 15;
rs ["v"] = "Hello world";
if (! rs.update()) ERROR_RETURN_FALSE;
values.push_back (15);
values.push_back ("Hello world");
if (! rs.open ("SELECT i, v FROM test")) ERROR_RETURN_FALSE;
int irecord = 0;
bool ok;
while (rs.next()) {
int i = rs ["i"].forceInt ();
string s = rs ["v"].asString();
if (i != values [irecord*2].forceInt ()) ERROR_RETURN_FALSE;
if (s != values [irecord*2+1].asString()) ERROR_RETURN_FALSE;
irecord ++;
}
// fill some rows with string and binary data
if (! db.begin()) ERROR_RETURN_FALSE;
if (! rs.openTable ("test")) ERROR_RETURN_FALSE;
vector vec;
string s;
for (int row=1; row < 1000; row ++) {
vec.push_back ((uint8_t) (row & 0xFF));
s += long2string (row);
CVariantValueBinary binary (& *vec.begin(), vec.size());
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = row * row;
rs ["v"] = s;
if (! rs.update()) ERROR_RETURN_FALSE;
if (! rs.add()) ERROR_RETURN_FALSE;
rs ["i"] = row * row + 1;
rs ["v"] = binary;
if (! rs.update()) ERROR_RETURN_FALSE;
}
if (! db.close()) ERROR_RETURN_FALSE;
return true;
Re: [sqlite] QNX and PPC
Hello, if anyone was watching my efforts, I have just won!
The PowerPC machine I am using had the folder /tmp mapped into shared
memory and the file-locking did not work with it. I changed the
sqlite3OsTempFileName() function and everything is fine.
I only wonder, what is the file SQLite creates in /tmp? My program calls
"CREATE TABLE", "INSERT" and "SELECT". Why does SQLite need some
temporary file?
Jakub
Jakub Adamek wrote:
Still not running on Power PC QNX. I tried to comment out locking in
os.c and the test program works fine. So the problem is really hidden in
the findLockInfo() & co. functions. Does somebody have some experience?
Thanks,
Jakub
Jakub Adamek wrote:
Thank you for your help. I am trying to cross-compile SQLite 2.8.15 on
QNX-x86 to QNX-PPC. Is it possible to do so with some params for
configure?
I tried to create the Makefile for QNX-x86 by just running
"./configure" and to change it by hand:
TCC = qcc -g -O2 -V gcc_ntoppc -fno-inline -fno-pack-struct -EB
-DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I${TOP}/src
LTCOMPILE = $(LIBTOOL) --mode=compile --tag=CC $(TCC)
LTLINK = $(LIBTOOL) --mode=link --tag=CC $(TCC)
LTINSTALL = $(LIBTOOL) --mode=install --tag=CC $(INSTALL)
but the libtool linker failed with "/usr/bin/ntox86-ld: warning:
powerpc:common architecture of input file '.libs/attach.o' is
incompatible with i386 output'
thus I had to change the "libtool" script, replacing "gcc" with "qcc
-V gcc_ntoppc". Which does not seem an elegant solution.
Jakub
Christian Smith wrote:
On Mon, 9 Aug 2004, Jakub Adamek wrote:
Hi Armin,
thank you for your notes. Could you please tell me more about "That's
the case"? Is it possible to use SQLite on PPC or not possible? Did you
hear about a big-endian port of SQLite?
I've used SQLite on SPARC, PowerPC and PA-RISC, all big endian, with no
problems.
The SQLite database file is platform independent, being endian agnostic
and 32/64 bit clean.
BTW, you may want to upgrade to a later version of SQLite, 2.8.15 being
the latest 2.x release.
Thanks,
Jakub
Armin Steinhoff wrote:
[EMAIL PROTECTED] schrieb am 06.08.04 19:40:55:
Hello everybody,
does someone have any experience with building for the QNX operating
system? And for the Power PC processor?
QNX 6.2.1 is a real-time Linux but it behaves strangely sometimes.
No .. it doesn't behave 'strangely" at all :)
It has not gcc but a qcc compiler.
qcc is only a frontend of the gcc ... so QNX has gcc.
Our testing program runs correctly on
the x86 PC. It creates a database, a table, inserts and selects data.
But the same program cross-compiled on the same computer for PPC
ends at
the first "CREATE TABLE" statement with SQLITE_BUSY.
I am using SQLite 2.8.6. Is it possible that the little-or-big-endian
thing could cause this trouble?
That's the case ..
Regards
Armin Steinhoff
Or do you have any other ideas?
Thank you very much,
Jakub
Aufnehmen, abschicken, nah sein - So einfach ist
WEB.DE Video-Mail: http://freemail.web.de/?mc=021200
[sqlite] SQLite 3 for Pocket PC
Somebody mentioned here he/she has SQLite 3 working on Pocket PC. Could you please send the sources or diffs? Thanks, Jakub
Re: [sqlite] QNX and PPC
Still not running on Power PC QNX. I tried to comment out locking in
os.c and the test program works fine. So the problem is really hidden in
the findLockInfo() & co. functions. Does somebody have some experience?
Thanks,
Jakub
Jakub Adamek wrote:
Thank you for your help. I am trying to cross-compile SQLite 2.8.15 on
QNX-x86 to QNX-PPC. Is it possible to do so with some params for configure?
I tried to create the Makefile for QNX-x86 by just running "./configure"
and to change it by hand:
TCC = qcc -g -O2 -V gcc_ntoppc -fno-inline -fno-pack-struct -EB
-DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I${TOP}/src
LTCOMPILE = $(LIBTOOL) --mode=compile --tag=CC $(TCC)
LTLINK = $(LIBTOOL) --mode=link --tag=CC $(TCC)
LTINSTALL = $(LIBTOOL) --mode=install --tag=CC $(INSTALL)
but the libtool linker failed with "/usr/bin/ntox86-ld: warning:
powerpc:common architecture of input file '.libs/attach.o' is
incompatible with i386 output'
thus I had to change the "libtool" script, replacing "gcc" with "qcc -V
gcc_ntoppc". Which does not seem an elegant solution.
Jakub
Christian Smith wrote:
On Mon, 9 Aug 2004, Jakub Adamek wrote:
Hi Armin,
thank you for your notes. Could you please tell me more about "That's
the case"? Is it possible to use SQLite on PPC or not possible? Did you
hear about a big-endian port of SQLite?
I've used SQLite on SPARC, PowerPC and PA-RISC, all big endian, with no
problems.
The SQLite database file is platform independent, being endian agnostic
and 32/64 bit clean.
BTW, you may want to upgrade to a later version of SQLite, 2.8.15 being
the latest 2.x release.
Thanks,
Jakub
Armin Steinhoff wrote:
[EMAIL PROTECTED] schrieb am 06.08.04 19:40:55:
Hello everybody,
does someone have any experience with building for the QNX operating
system? And for the Power PC processor?
QNX 6.2.1 is a real-time Linux but it behaves strangely sometimes.
No .. it doesn't behave 'strangely" at all :)
It has not gcc but a qcc compiler.
qcc is only a frontend of the gcc ... so QNX has gcc.
Our testing program runs correctly on
the x86 PC. It creates a database, a table, inserts and selects data.
But the same program cross-compiled on the same computer for PPC
ends at
the first "CREATE TABLE" statement with SQLITE_BUSY.
I am using SQLite 2.8.6. Is it possible that the little-or-big-endian
thing could cause this trouble?
That's the case ..
Regards
Armin Steinhoff
Or do you have any other ideas?
Thank you very much,
Jakub
Aufnehmen, abschicken, nah sein - So einfach ist
WEB.DE Video-Mail: http://freemail.web.de/?mc=021200
Re: [sqlite] QNX and PPC
No, I meant Power PC. We use SQLite in our Pocket PC car navigation system with big success also. Jakub Mateusz Ĺoskot wrote: On 8/9/2004 9:45 AM, Jakub Adamek wrote: thank you for your notes. Could you please tell me more about "That's the case"? Is it possible to use SQLite on PPC or not possible? Did you hear about a big-endian port of SQLite? Do you mean Power PC or Pocket PC ? If you mean the second one, I can say sqlite works great on Pocket PC ;-) I use it for almost all database driven projects. Greets
Re: [sqlite] QNX and PPC
Hi Armin, thank you for your notes. Could you please tell me more about "That's the case"? Is it possible to use SQLite on PPC or not possible? Did you hear about a big-endian port of SQLite? Thanks, Jakub Armin Steinhoff wrote: [EMAIL PROTECTED] schrieb am 06.08.04 19:40:55: Hello everybody, does someone have any experience with building for the QNX operating system? And for the Power PC processor? QNX 6.2.1 is a real-time Linux but it behaves strangely sometimes. No .. it doesn't behave 'strangely" at all :) It has not gcc but a qcc compiler. qcc is only a frontend of the gcc ... so QNX has gcc. Our testing program runs correctly on the x86 PC. It creates a database, a table, inserts and selects data. But the same program cross-compiled on the same computer for PPC ends at the first "CREATE TABLE" statement with SQLITE_BUSY. I am using SQLite 2.8.6. Is it possible that the little-or-big-endian thing could cause this trouble? That's the case .. Regards Armin Steinhoff Or do you have any other ideas? Thank you very much, Jakub Aufnehmen, abschicken, nah sein - So einfach ist WEB.DE Video-Mail: http://freemail.web.de/?mc=021200
[sqlite] QNX and PPC
Hello everybody, does someone have any experience with building for the QNX operating system? And for the Power PC processor? QNX 6.2.1 is a real-time Linux but it behaves strangely sometimes. It has not gcc but a qcc compiler. Our testing program runs correctly on the x86 PC. It creates a database, a table, inserts and selects data. But the same program cross-compiled on the same computer for PPC ends at the first "CREATE TABLE" statement with SQLITE_BUSY. I am using SQLite 2.8.6. Is it possible that the little-or-big-endian thing could cause this trouble? Or do you have any other ideas? Thank you very much, Jakub
Re: [sqlite] A proposal for SQLite version 3.0
Yes, that is right, my database consists of mainly numbers. Some of them have 10 digits, some have less. This would explain part of the difference. I was also wondering if Access perhaps has some type of index comprimation, like cutting pre- and postfixes in the B-tree. Jakub Simon Berthiaume wrote: It might be true if you mostly store large quantities of NUMERICAL data (float, int, ...) since they are currently stored as strings (the value 1234567, would be stored using at least 7-8 bytes instead of 4). If you were in the same situation as I, where I use database mostly for strings, you would be in the opposite situation, the SQLite database would be about half the size of the MS Access one, since MS Access seems to save all strings as UTF-16. Simon B. On Wed, 2004-04-07 at 10:50, Jakub Adamek wrote: Hello, I am using SQLite in a car navigation system which should work on PDAs as well. Thus speed and size is crucial for us. SQLite is superb in the speed category, but the size of its file is not so superb. I see you mentioned something about file size. My experience is that SQLite makes roughly about 3x bigger files than MS Access. How would this change in 3.0? Thanks for your excellent work, Jakub Adamek D. Richard Hipp wrote: A design proposal for SQLite version 3.0 can be found at: http://www.sqlite.org/prop2.html Feedback from the user community is strongly encouraged. An executive summary of the proposed changes follows: * Support for UTF-16 * Better BLOB support * User-defined collating sequences (for better internationalization support) * Smaller and faster than 2.8.13. The plan is to continue to support the 2.8.X series indefinately and in parallel to the 3.X series. But the only changes to 2.8.X going forward will be bug fixes. New features will go into 3.X. Beta releases of version 3.X are expected within a few months. I do not have much experience with UTF-16 and am expecially interested in feedback on that area of the design. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] A proposal for SQLite version 3.0
Hello, I am using SQLite in a car navigation system which should work on PDAs as well. Thus speed and size is crucial for us. SQLite is superb in the speed category, but the size of its file is not so superb. I see you mentioned something about file size. My experience is that SQLite makes roughly about 3x bigger files than MS Access. How would this change in 3.0? Thanks for your excellent work, Jakub Adamek D. Richard Hipp wrote: A design proposal for SQLite version 3.0 can be found at: http://www.sqlite.org/prop2.html Feedback from the user community is strongly encouraged. An executive summary of the proposed changes follows: * Support for UTF-16 * Better BLOB support * User-defined collating sequences (for better internationalization support) * Smaller and faster than 2.8.13. The plan is to continue to support the 2.8.X series indefinately and in parallel to the 3.X series. But the only changes to 2.8.X going forward will be bug fixes. New features will go into 3.X. Beta releases of version 3.X are expected within a few months. I do not have much experience with UTF-16 and am expecially interested in feedback on that area of the design. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Database Full!
Hello, it seems to me the solution would be: Create a special small file just to protect some disc space. If you lack on disc space, delete the protecting file (and hope nobody will steal the disc space inbetween) and delete some records. Than again create the protecting file. Obviously, in a live system this may fail ... Jakub D. Richard Hipp wrote: Benoit Gantaume wrote: Hi, I am trying to handle a problem that occurs when the disk is full: when i try to insert, that return SQLITE_FULL. Ok. There is not problem to get some elements from the database. Then I try to remove some elements... But that returns SQLITE_BUSY! I have tryed to stop all operation with: sqlite_interrupt(this->cdb); But it seems to have no effect! How can I free the database so that I can remove some elements from it? DELETE requires some temporary disk space for the rollback journal. So if your disk is full, you cannot delete. Furthermore, just doing some DELETEs does not reduce the size of the database file. DELETE just adds some 1024-byte blocks of the file to an internal freelist where they can be reused later for other purposes. To actually reduce the size of the database file, you need to run VACUUM after you DELETE. VACUUM requires temporary disk space that is a little over 2x larger than the size of the original database. So (ironically) if you are low on disk space, VACUUM probably will not run. So, as you can see, it is difficult to get SQLite to run when you are low on disk space. Your best solution is to get a bigger disk. A bigger disk drive is the right answer for your desktop, but for an embedded solution (with perhaps a few MB of flash disk) that is not practical. That problem has been brought to my attention and work is underway to make SQLite behave better in a low diskspace environment. Unfortunately, the changes to accomplish this will not be available in the public version of SQLite for a least 3 more months and probably longer than that. Sorry. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
