Re: [sqlite] Compiling sqlite3.0.3 on dec-osf1 with gcc 3.04 failed
Hello, I compiled the new 3.0.4 sources without any changes on OSF-1 now. No need for the '-DUINTPTR_TYPE=unsigned long long' flag at all. However I am wondering that the shared lib for tcl is build only by invoking `make test' and that this shared lib is much smaller than the shared lib gotten with the old: gmake target_source cd tsrc rm shell.c gcc -fPIC -DNDEBUG=1 -shared *.c -o tclsqlite.so -L/project/amphioxus/lib -I/project/amphioxus/include -L/usr/shlib -ltcl8.4 -rpath "/usr/shlib:/project/amphioxus/lib:/usr/local/lib" approach ? How can I compile a smaller 2.8.15 shared tcl-lib? In 2.8.15 `make test' makes the static but not the dynamic library for tcl ? regards, Detlef -- Dr. Detlef Groth Max-Planck-Institut fuer Molekulare Genetik Ihnestr. 63/73 D-14195 Berlin Tel.: + 49 30 - 8413 1235
[sqlite] sqlite3_busy_timeout ignored while doing multi-threaded updates ?
Hello SQLite users, Hello Dr. Hipp, while doing updates from multiple threads, I'm only able to perform an update on one thread, all the other threads returning SQLITE_BUSY. This is ok: I've read the docs and it behaves as it should. However, what I don't understand is the behavior of sqlite when I set a busy timeout. The documentation states: ** This routine sets a busy handler that sleeps for a while when a ** table is locked. The handler will sleep multiple times until ** at least "ms" milleseconds of sleeping have been done. After ** "ms" milleseconds of sleeping, the handler returns 0 which ** causes sqlite3_exec() to return SQLITE_BUSY. So my question is this: given two threads, T1 and T2 which try to do simultaneous updates from two separate threads, and T1 is beginning and commiting the UPDATE, shouldn't T2 try for x milliseconds (value specified in sqlite3_busy_timeout) before returning SQLITE_BUSY ? In v3.0.4, the result is that T2 returns immediately reporting SQLITE_BUSY, as if it is ignoring the busy timeout. Regards, George Ionescu
[sqlite] column list with COPY command
hi, it would be useful if its possible to be able to give column list with COPY sql command. (like mysql's LOAD DATA command) and INTEGER PRIMARY KEY fields doesn't get it's value automatically. CREATE TABLE test (id INTEGER PRIMARY KEY,a CHAR(10)); file xxx: \Nvalue1 \Nvalue2 ... COPY test FROM 'xxx' --> SQL error: datatype mismatch ofcourse i can remove id field from db and i can use ROWID when i need it, but its realy neccessary? thank you and sorry for bad english. semseddin moldibi - bircom telecommunications
[sqlite] Sqlite on Zaurus
Hello, First, sorry for my english. I use Sqlite for Zaurus Application and for this, I generate a sqlite ipk. It can be download here : http://penbase.net/~jean/sqlite_2.8.15_arm.ipk I write a french documentation for compile and package Sqlite for Zaurus. Bye Jean
Re: [sqlite] Deadlock when doing threaded updates and inserts
Tito Ciuro wrote: I'm definitely not happy about this... Let me get this right... it seems that you're cruising along fine with SQLITE_OK's all over the place when suddenly one of your threads/processes get a SQLITE_BUSY signal in the middle of a transaction. In order to solve the crisis, one of the transactions *must* be rolled back: Questions: 1) Which one? Do I toss a coin? Rollback the one that returned SQLITE_BUSY 2) At the time when SQLITE_BUSY pops up, the app may very well be too deep in a transaction. Dr. Hipp suggests retrying the transaction that was rolled back, a solution I believe should be handled by the engine. Who is then responsible to keep track of the operations that make up a currently openened transaction? The app I suppose? It will add an amazing amount of ugly code testing/retesting/solving a SQLITE_BUSY signal. The SQLITE_BUSY will be returned the first time you try to modify the database. Any prior statements in the tranaction will have been only queries. (Assuming you have a busy_handler registered that handles conflicts in acquiring a PENDING lock.) If you start each transaction with database change of some kind (perhaps an UPDATE that doesn't really update anything) then it will immediately attempt to acquire a RESERVED lock and either succeed or return SQLITE_BUSY to let you know that you need to try the whole transaction again later. An example of an UPDATE that doesn't really change the database might be something like this: UPDATE table1 SET rowid=rowid WHERE rowid<0; I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire a RESERVED lock immediately. That will reduce the amount of confusion about this issue, I suppose. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > Tito Ciuro wrote: >> I'm definitely not happy about this... >> Let me get this right... it seems that you're cruising along fine with >> SQLITE_OK's all over the place when suddenly one of your threads/processes >> get a SQLITE_BUSY signal in the middle of a transaction. In order to solve >> the crisis, one of the transactions *must* be rolled back: >> Questions: >> 1) Which one? Do I toss a coin? > > Rollback the one that returned SQLITE_BUSY In the earlier examples, multiple threads each received an SQLITE_BUSY indication. If they both (all) rollback and retry, the deadlock condition will likely recur... I'm currently encountering the reader starvation problem in 2.8.x, and am eager to switch to 3.0 to solve it, but this rollback/retry issue has me waiting on switching. I will need to do a fair amount of redesign work to accommodate this procedure due to the nature of the transactions (many operations invoked by a number of functions), unless... > I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire > a RESERVED lock immediately. That will reduce the amount of confusion > about this issue, I suppose. Does enabling this PRAGMA regenerate the reader starvation problem of 2.8.x, or is that problem solved elsewise in 3.0? Derrell
Re: [sqlite] Deadlock when doing threaded updates and inserts
[EMAIL PROTECTED] wrote: I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire a RESERVED lock immediately. That will reduce the amount of confusion about this issue, I suppose. Does enabling this PRAGMA regenerate the reader starvation problem of 2.8.x, or is that problem solved elsewise in 3.0? Version 2 had a problem with writer starvation. I'm not familiar with the reader starvation problem. Can you describe your situation? The PRAGMA would still using the PENDING lock idea so I think the writer starvation problem would still be resolved. The PRAGMA would just disallow multi-statement read-only transactions. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > Version 2 had a problem with writer starvation. I'm not familiar with > the reader starvation problem. Can you describe your situation? Maybe I misremembered the terminology. What I'm seeing is a few tasks doing lots of writing to the database (with and without explicit transactions) preventing a reader task from getting a chance to read. A SELECT can block for a *very* long time (my 60 second timeout expires). > The PRAGMA would still using the PENDING lock idea so I think the > writer starvation problem would still be resolved. The PRAGMA would > just disallow multi-statement read-only transactions. Ok, great. Now is what I just described above what you had called "writer starvation"? Cheers, Derrell
Re: [sqlite] Deadlock when doing threaded updates and inserts
[EMAIL PROTECTED] wrote: > What I'm seeing is a few tasks doing lots of writing to the database (with > and without explicit transactions) preventing a reader task from getting a > chance to read. A SELECT can block for a *very* long time (my 60 second > timeout expires). > What you describe is not writer starvation. It is not clear to me how what you describe is occurring. Only one task can write at a time. During its COMMIT, no other task can access the database. When the COMMIT finishes, locks are removed and all tasks have an opportunity to access the database again. Writers and readers have equal opportunity and so none should starve. Writer starvation occurs when multiple readers are using the database. The readers always overlap (the next reader starts before the previous finishes) so that there is always a shared lock on the database. This prevents a writer from ever getting a turn. The new PENDING lock prevents writer starvation. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: > > What I'm seeing is a few tasks doing lots of writing to the database (with > > and without explicit transactions) preventing a reader task from getting a > > chance to read. A SELECT can block for a *very* long time (my 60 second > > timeout expires). > > > > What you describe is not writer starvation. It is not clear to me how > what you describe is occurring. Hmmm... Ok, well I'll have to investigate further. I had thought I was seeing a "known problem" and switching to 3.0 would solve the problem. Now I have a different sort of work to do, tracking this down. Thanks, Derrell
Re: [sqlite] Views and performances...
On Wed, 11 Aug 2004, Paolo Vernazza wrote: >Hi, >I would like to know if using views instead of queries has some >performance advantages. >If I must perform many times the same query (a select with a complex >where clause), is it better to have a precompiled query or to create a >view? Views are not so much of an optimization, more of an abstraction of underlying data. You use views to hide the structure of underlying data or filter. Such data may be spread across several tables using a join, but presented with a single view. AFAIK, views are compiled into their query definitions when executing a statement, so once the compilation is done, performance should be identical to their raw query form. If you precompile the views, then you have all the benefit of the view without the overhead of the extra compilation required. In short, create a view if you have a common filter on some data. If performance is a factor, precompile the view. > >Thanks > >Paolo > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Activestate PPM for DBD::SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Has anyone with access to a Windows development environment built an Activestate PPM version of the new DBD::SQLite module? I don't have such an environment, so I'm stuck trying to get my application ported to SQLite 3 under Windows. - --Wade -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (FreeBSD) iD8DBQFBG4g7o4DwsyRGDscRAt3jAJ9NSppK896sWmJcabFE/wgMeAIhNQCfRS6F wMLFkX4mnKTlkNzUzYff1Q0= =3DAg -END PGP SIGNATURE-
Re: [sqlite] Views and performances...
On Thu, Aug 12, 2004 at 03:38:21PM +0100, Christian Smith wrote: > AFAIK, views are compiled into their query definitions when executing a > statement, so once the compilation is done, performance should be > identical to their raw query form. > In short, create a view if you have a common filter on some data. If > performance is a factor, precompile the view. At least in Oracle and PostgreSQL, the problem with views is that if you then re-use them in a complicated query, you MAY get much worse performance than if you wrote the query against all the underlying tables directly. This is because: 1. Sometimes the query optimizer is just dumb about optimizing queries containing views. (E.g., doing joins against views in Oracle, sometimes.) 2. Sometimes it's not the query optimizer's fault at all; by using the view in your query, you actually end up asking the RDBMS for something subtly different, which (although the query results may happen to be the same) makes your query legitimately much slower. Tuning your query to fix this may require eliminating use of the view. SQLite's query optimizer is presumably much simpler than that of either Oracle PostgreSQL, so it probably shares these sorts of problems. But views are still good. Generally, I'd only remove use of a handy view once you see a real performance problem (a slow query with a bad query plan). -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
[sqlite] simple sql, or requires custom aggregate?
I'm having trouble generating what should be a simple sqlite query. I need a query that returns the ROW data corresponding to a GROUP BY and aggregate restriction. Here's an example: sqlite> create table t (t integer, x integer, y integer); sqlite> insert into t values (100, 10, 20); sqlite> insert into t values (101, 10, 21); sqlite> insert into t values (100, 20, 30); sqlite> select max(t), x, y from t group by x; max(t) = 100 x = 20 y = 30 max(t) = 101 x = 10 y = 20 sqlite> Note that the second row returned is not an actual row from the table. What I really want returned there is the (101, 10, 21) row. I haven't been able to come up with a HAVING clause that does what I want, either. Just doing HAVING MAX(t) evaluates to HAVING TRUE for each row with a non-zero value of t, so all rows are selected. I tried HAVING t = MAX(t) but then I don't get every possible x value: sqlite> select max(t), x, y from t group by x having t = max(t); max(t) = 100 x = 20 y = 30 sqlite> I could probably solve this by writing a custom aggregate function that took the rowid, x and t, and returned the rowid with the greatest t for the value of x, but that seems unnecessary and overly complicated... For clarification, what I want back from the query on the above table are two rows: (101, 10, 21) and (100, 20, 30) which are the rows with the maximum value of t for each value of x. Any help greatly appreciated! Derrell
Re: [sqlite] simple sql, or requires custom aggregate?
> I haven't been able to come up with a HAVING clause ... HAVING is used to select (or eliminate) groups, so it doesn't apply here; you want all the groups. Also remember that, although it doesn't generate a syntax error, it's not correct to specify an aggregate function and a column that's not in the GROUP BY clause: 'select max(t), x ... group by x' is OK, but 'select max(t), x, y ... group by x' is not. select t, x, y from tbl, (select max(t) tmax, x xx from tbl group by xx) where t = tmax and x = xx; (I changed your table name just to make it easier to read.) Regards
[sqlite] Keep getting SQLITE_MISUSE on a sqlite3_step
Hi all,
First I wanted to say that SQLite is pretty impressive ! Cheer to all
the dev team, you've made a wonderfull SQL DB Engin ^_^ Keep up the good
work.
Second, I'm currently working on wrapper that I will use in some of my
C++ project but I got a little problem that I've been stubbling on for
two days, I'm sure it's pretty dumb, but I can't find why I keep getting
this error.
sqlite3_step keep returning SQLITE_MISUSE so I must be forgetting
something, but I can't find it.
So here it goes, I'll strip my code to the bare minimum
My main look like this
//=
int main()
{
try
{
CConnection oConn;
oConn.open("test.db");
oConn.execute("DROP TABLE test");
oConn.execute("CREATE TABLE test(number);");
oConn.execute("BEGIN;");
oConn.execute("INSERT INTO test VALUES(1);");
oConn.execute("COMMIT;");
CRecordset rs = oConn.query("SELECT * FROM test;");
while(rs.read()) // This is the line I get the error
cout << "Reading" << endl;
rs.close();
oConn.close();
}
catch(CDBException ex)
{
cout << ex.message << endl;
}
return 0;
}
//=
Ok I open the connection like this
//=
void CConnection::open( const char *db )
{
if(!this->db)
{
if(sqlite3_open(db, (sqlite3**)&this->db) != SQLITE_OK)
throw CDBException("Unable to open the database");
}
else
throw CDBException("Connection is already open");
}
//=
I query like this
//=
CRecordset CConnection::query(const char *sqlQuery)
{
if(this->db)
{
CRecordset oRs;
oRs.oConn = this;
int result = sqlite3_prepare((sqlite3*)this->db, sqlQuery, -1,
(sqlite3_stmt**)&oRs.vm, NULL); //Not sure if I'm forgetting something
to do before the prepare.
if(result != SQLITE_OK)
throw CDBException("Error on query");
oRs.columnCount = sqlite3_column_count((sqlite3_stmt*)oRs.vm);
return oRs;
}
else
throw CDBException("Connection is closed");
}
//=
Then I read with this function
//=
bool CRecordset::read()
{
if(this->vm)
{
switch( sqlite3_step((sqlite3_stmt*)this->vm) )
{
case SQLITE_ROW: return true;
break;
case SQLITE_DONE: return false;
break;
case SQLITE_MISUSE: throw CDBException("This routine was called
inappropriately"); // This is where the error gets throwed
break;
default: throw CDBException("Error reading");
break;
}
}
else throw CDBException("Recordset is already closed");
}
//=
You'll say that it looks like the minimalist wrapper of int64.org which
is exactly what it is. I'm inspiring myself from this wrapper to
understand the API of SQLite 3, so that I can later add some
functionnalities to the wrapper.
--
WysG
"Codito Ergo Sum" - 'I code, therefore I am' - 'Je code, donc je suis'
GPG Public Key : http://wysg.hextudio.com/WysG.gpg
