Re: [sqlite] Design of application using embedded database
I am not sure what that means. I am looking for undo feature the way it is implemented for example in a drawing application or in a word processor. When the user makes a mistake or change his mind, he can undo several steps and try again. On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/19/06, Ran <[EMAIL PROTECTED]> wrote: > I think I didn't explain my question well enough. > I know all what you wrote about transactions. The undo functionality I am > looking for is over _several_ transactions. Does nested transactions do what you want? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
What exactly do you mean by "own log of each transaction"? As I explained, I have a complex database with many tables and triggers. It will not be simple to implement undo the way it is explained in the wiki, and I suspect it will cost much in performance (but maybe I am wrong here...). If journals can be used the way I explained, this simplify the undo (because the database should not be changed at all), and also I suspect that the performance is not affected (apart from the fact that we have to store the journals somewhere). I am not sure about the size of the journals, and especially if they could be used at all after the transaction is committed. On 11/19/06, John Stanton <[EMAIL PROTECTED]> wrote: Ran wrote: > I think I didn't explain my question well enough. > I know all what you wrote about transactions. The undo functionality I am > looking for is over _several_ transactions. > > I just wonder if it is possible to twist sqlite to keep the journals > created > during transactions (so to store them just before they are deleted when a > commit is called). > Then, when one wants to undo several transaction (each might include indeed > many inserts/updates/deletes), those kept journals will help him to > "rollback" several times to a former situation. > > So to implement undo by keeping journals. This will give undo functionality > for practically any database (so the database design - the tables, triggers > etc. will not have to be taken into account). I just wonder if it is > possible/reasonable to implement that way undo functionality, and if not - > why not. > > Ran > > On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > >> >> On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: >> > The way the undo-redo is described in the wiki involves triggers to >> insert >> > the information of the change in each table to other table which logs >> the >> > changes. This will have a price in performance. It also complicates >> things >> > when triggers are already used for other things. >> > >> > So I wonder if journals might be used to implement undo: >> > If I understand it correctly, for each transaction there is journal >> that >> > keeps the information so the transaction could be rolled back. If the >> > journals are kept somewhere could they be used to rollback _successful_ >> > transactions? >> >> Transactions let you "undo" whatever you have done since the transaction >> started. You decide if it was "successful" or not and either commit >> the transaction >> to make it permanent or roll it back to undo it. >> >> In the case of large numbers of insertions it's faster to put them >> into a transaction >> and commit them than to do them separately. Performance in most >> installations is >> very quick since the database generally ends up in operating system >> cache. >> I do recall the author of mysql writing "he had no intention of >> implementing >> transaction since it was much slower and proper design eliminated the >> need >> for them." Sqlite has a much lower code overhead than mysql so I >> imagine >> it's just as fast or faster in most cases. >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> Why not maintain your own log of each transaction? Then you can undo and redo without limit. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
I think I didn't explain my question well enough. I know all what you wrote about transactions. The undo functionality I am looking for is over _several_ transactions. I just wonder if it is possible to twist sqlite to keep the journals created during transactions (so to store them just before they are deleted when a commit is called). Then, when one wants to undo several transaction (each might include indeed many inserts/updates/deletes), those kept journals will help him to "rollback" several times to a former situation. So to implement undo by keeping journals. This will give undo functionality for practically any database (so the database design - the tables, triggers etc. will not have to be taken into account). I just wonder if it is possible/reasonable to implement that way undo functionality, and if not - why not. Ran On 11/19/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 11/18/06, Ran <[EMAIL PROTECTED]> wrote: > The way the undo-redo is described in the wiki involves triggers to insert > the information of the change in each table to other table which logs the > changes. This will have a price in performance. It also complicates things > when triggers are already used for other things. > > So I wonder if journals might be used to implement undo: > If I understand it correctly, for each transaction there is journal that > keeps the information so the transaction could be rolled back. If the > journals are kept somewhere could they be used to rollback _successful_ > transactions? Transactions let you "undo" whatever you have done since the transaction started. You decide if it was "successful" or not and either commit the transaction to make it permanent or roll it back to undo it. In the case of large numbers of insertions it's faster to put them into a transaction and commit them than to do them separately. Performance in most installations is very quick since the database generally ends up in operating system cache. I do recall the author of mysql writing "he had no intention of implementing transaction since it was much slower and proper design eliminated the need for them." Sqlite has a much lower code overhead than mysql so I imagine it's just as fast or faster in most cases. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Design of application using embedded database
The way the undo-redo is described in the wiki involves triggers to insert the information of the change in each table to other table which logs the changes. This will have a price in performance. It also complicates things when triggers are already used for other things. So I wonder if journals might be used to implement undo: If I understand it correctly, for each transaction there is journal that keeps the information so the transaction could be rolled back. If the journals are kept somewhere could they be used to rollback _successful_ transactions? Ran On 11/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: ... When your changes are saved to disk immediately, it is important to have a good undo/redo mechanism. There is some example code on the wiki showing how to implement undo/redo using triggers. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Q about new SQLite API
1. sqlite3_re_prepare or simply sqlite3_reprepare On 11/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? Some possibilities include: sqlite3_prepare_ex1 sqlite3_prepare_ng sqlite3_new_prepare sqlite3_compile
[sqlite] locks and attached databases
Hi all, I am not sure of the way attached database behaves in respect to locks and could not find an answer in the documentation about it. I have a very complex query which I simplify by using temporary tables. However, this means that every time this complex query is executed, the database is exclusively locked (because I create temporary tables and insert the intermediate results into them). This means that when data is read from my database I get exclusive lock, so no two readers can read in parallel. I thought to solve this by attaching another database I will create temporarily for each query, and to place the temporary tables in this attached temporary database. This way, *I think*, my main database will be locked shared (I will only select from it) and the temporary database will be locked exclusively. So I will be able to run such queries complex in parallel. Am I correct in my assumption that the attached database will be the only one to be locked? Or maybe when database is attached the locks of the two databases are common? Thanks in advance, Ran
Re: [sqlite] sqlite3_close doesn't release always the file handle.
I filed the bug report as you suggested: #1990. Ran. On 9/23/06, Will Leshner <[EMAIL PROTECTED]> wrote: On 9/22/06, Ran <[EMAIL PROTECTED]> wrote: > What I do is to open a database, and start a transaction in it. Then, > without ending the transaction, open again the database and simply close it. > > I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the > file handle is not released. So if I do it too many times, I run out of file > handles. Would you be willing to file a bug report about this? I have a "customer" who is claiming a similar problem and if you can demonstrate the problem with your code it might be worth having it checked out by the SQLite team. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_close doesn't release always the file handle.
Hi all,
I *think* that sqlite3_close behave strangly.
I use version 3.3.7 on Linux (Fedora Core 5).
What I do is to open a database, and start a transaction in it. Then,
without ending the transaction, open again the database and simply close it.
I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the
file handle is not released. So if I do it too many times, I run out of file
handles.
You are free to ask why I open and close that many times the same database
while it is already in transaction.
This is my mistake. Actually, it is already fixed.
But I still wonder - shouldn't the sqlite3_close return other thing then
just SQLITE_OK? Especially if the file handle is not released? If it did, I
would find my mistake much earlier.
Here is my script that demonstrate it (you can use /usr/sbin/lsof in linux
to see how many times the file is opened):
#include
int main(int argc, char **argv) {
sqlite3* db;
sqlite3* db_inner;
int rc;
int i;
system("rm -f open_many_test.db");
rc = sqlite3_open("open_many_test.db", );
sqlite3_exec(db, "begin", 0, 0, 0);
sqlite3_stmt *pStmt;
rc = sqlite3_prepare(db,
"create table a (id varchar)",
-1,
,
0);
rc = sqlite3_step(pStmt);
sqlite3_finalize(pStmt);
rc = sqlite3_prepare(db,
"insert into a values('bla')",
-1,
,
0);
rc = sqlite3_step(pStmt);
sqlite3_finalize(pStmt);
for (i = 0; i < 1; i++) {
rc = sqlite3_open("open_many_test.db", _inner);
printf("sqlite3_open gives %d\n", rc);
rc = sqlite3_close(db_inner);
printf("sqlite3_close gives %d\n", rc);
}
sqlite3_exec(db, "commit", 0, 0, 0);
rc = sqlite3_close(db);
}
I will appreciate any explaination.
Thanks,
Ran
Re: [sqlite] Regarding Lemon Parser
See http://www.hwaci.com/sw/lemon/index.html "Both the source code to lemon itself and the code that lemon generates are in the public domain." Ran On 7/13/06, Arnav Kumar <[EMAIL PROTECTED]> wrote: Hi, I am currently exploring use of lemon parser generator for use in a project. I wanted to know if there are any licensing restrictions on the code generated by the parser for e.g. whether the generated code itself has to be under GPL or can be used in closed/proprietary programs without any restrictions. Thanks for help, Arnav
Re: [sqlite] Re: Re: Opening the database file for read on Windows XP
Actually, I have just realized that I indeed cannot use BEGIN IMMEDIATE to
do the locking because it has to be locked from the process that has no
SQLite in it...
And you already delievered the exact answer of how to do it!
Thanks a lot!
Ran
On 6/22/06, Christian Smith <[EMAIL PROTECTED]> wrote:
Igor Tandetnik uttered:
> Christian Smith
> <[EMAIL PROTECTED]> wrote:
>> Igor Tandetnik uttered:
>>> You want to enable sharing. Pass FILE_SHARE_READ | FILE_SHARE_WRITE
>>> as the third parameter.
>>
>>
>> Surely not FILE_SHARE_WRITE! You don't want other processes writing
>> the database while you're copying it.
>
> The file is already opened by another process for read/write, you must
> specify FILE_SHARE_WRITE otherwise you won't be able to open it. You
have to
> impose a locking mechanism separate from that provided by the OS. Hence
BEGIN
> IMMEDIATE command which guarantees that no writes will occur via SQLite.
But the OP didn't want to use SQLite in the copying program (for whatever
reason). Without SQLite to arbitrate locking, using
FILE_SHARE_WRITE won't help any as the file can still be updated
regardless while we're copying. If the file is already open with SQLite,
then we're stuck with it I suppose.
The OP's best bet, then, is to lock the file an a way compatible with
SQLite. The easiest way to do this is to use the Win95 compatible LockFile
similar to the function getReadLock in the os_win.c source. Use the
following code to read lock the file in a SQLite compatible way:
#define PENDING_BYTE 0x4000 /* First byte past the 1GB boundary */
#define SHARED_FIRST (PENDING_BYTE+2)
#define SHARED_SIZE 510
static int getReadLock( HANDLE fhandle )
{
int lk = random();
int sharedLockByte = (lk & 0x7fff)%(SHARED_SIZE - 1);
return res = LockFile( fhandle, SHARED_FIRST+sharedLockByte, 0, 1, 0);
}
Note, this function will fail (return 0) if the file is already locked for
writing, as the entire region from SHARED_FIRST to
SHARED_FIRST+SHARED_SIZE is locked. The function will also fail on NT if
the SQLite library already has a read lock on the file. If you want a more
complete function that is more capable on NT, look at the getReadLock() in
os_win.c.
If closing the handle does not clear the lock, you'll need to record the
sharedLockByte value and unlock the file first. MSDN is unclear whether
this is the case (no surprises there!)
>
> Igor Tandetnik
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] Opening the database file for read on Windows XP
You'll need to have some communication between your processes so one knows that the other has locked the file and the copy can proceed. I wrote my replication program to be run from cron. It waits for a time trying to establish the correct lock, you might try the 'delay and retry' method. True. The two process are COM server and client so they do speak with each other and indeed one will lock using the "BEGIN IMMEDIATE" and the other will read and then the first will COMMIT. Thanks, Ran
Re: [sqlite] Re: Opening the database file for read on Windows XP
Ha! This made the trick. I tried only with FILE_SHARE_READ and this didn't work, but I didn't try with both of them. Thanks a lot! Ran On 6/21/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ran <[EMAIL PROTECTED]> wrote: > Thanks for your reply. I know that I should lock the file before > copying it, > and the "BEGIN IMMEDIATE" is indeed a nice trick. > However, I think I didn't explain my problem clearly. I would like to > copy > that file _without_ using the sqlite library (so using the windows API > only). > > When I try to do that with: > CreateFile(db_file, >GENERIC_READ, >0, >NULL, >OPEN_EXISTING, >FILE_ATTRIBUTE_NORMAL, NULL); > > I get error 0x20 - "the process cannot access the file becuase it is > beging > used by other process". You want to enable sharing. Pass FILE_SHARE_READ | FILE_SHARE_WRITE as the third parameter. Igor Tandetnik
Re: [sqlite] Opening the database file for read on Windows XP
Thanks for your reply. I know that I should lock the file before copying it, and the "BEGIN IMMEDIATE" is indeed a nice trick. However, I think I didn't explain my problem clearly. I would like to copy that file _without_ using the sqlite library (so using the windows API only). When I try to do that with: CreateFile(db_file, GENERIC_READ, 0, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); I get error 0x20 - "the process cannot access the file becuase it is beging used by other process". I have two processes - one is linked with sqlite, and the other (which does the copying) is not. I can lock using the first process, but I need to make the copy with the other, and without linking to sqlite (although sqlite is small, I find it a pity to link to it _only_ in order to do such a copy). Thanks again, Ran On 6/21/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 6/21/06, Ran <[EMAIL PROTECTED]> wrote: > I have an application that uses sqlite3 API, and open the database file. > While the file is opened (for reading) by sqlite3, I would like to copy the > database file (so to have a copy of the file). I guess I need to place a > shared lock on the file (like sqlite3 does when reading from the file). I > wonder what is the recommended way of doing that. Ran, I do this to replicate the database for backup. Aquire an immediate lock ( "begin immediate" ) then you can copy the file. Source code in C++ is downloadable from my sqlite support page (see the replicator). -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
[sqlite] Opening the database file for read on Windows XP
Hi all, I wonder if someone can guide me how to open for reading the database file of sqlite3 on WindowsXP, while the database is already opened by sqlite3 API. I have an application that uses sqlite3 API, and open the database file. While the file is opened (for reading) by sqlite3, I would like to copy the database file (so to have a copy of the file). I guess I need to place a shared lock on the file (like sqlite3 does when reading from the file). I wonder what is the recommended way of doing that. I saw that in http://www.sqlite.org/cvstrac/getfile/sqlite/src/os_win.cthere are some functions that might help me doing that, for example: int sqlite3WinOpenReadOnly(const char *zFilename, OsFile **pId) but those functions are internal to sqlite3 (so they are not exposed in the API). So I am not sure if it is a good idea to use them. I will appreciate any help in this matter, Thanks in advance, Ran
Re: [sqlite] List of functions
http://www.sqlite.org/lang_expr.html On 5/24/06, Unit 5 <[EMAIL PROTECTED]> wrote: I see references to typecasting functions here in the mailing list. I have not found where they are discussed on the website. I saw some of them in the "expressions" page but seems to cover a subset of them. Is there a page that provides a list of all supported functions? These could be typecasting as well as other mathematical functions. Thanks in advance! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] "SQL logic error or missing database"
Actually, the multiple connections are created from different threads. But
those threads did not access the database in the same moment when the
problem occured, so I assumed (correctly) that the bug happens also when the
connections are done from the same thread. And this is how I created the
script that demonstrated the problem.
Currently the problem is fixed according to the guidelines I got from all of
you. This is how I now step:
Have a prepared statement.
while (true) {
try to step the prepared statement.
If failed to step {
reset the prepared statement to get the correct error.
if the error is not SQLITE_SCHEMA {
deal with the error and break from the loop.
}
if the error is SQLITE_SCHEMA {
finalize the prepared statement.
prepare the statement again.
continue in the loop (so step again with the newly prepared
statement).
}
}
if the step was successful - break from the loop and continue as usuall
after a successful
step.
}
I write it here because I could not find an explanation like this in the
documentation (did I miss it somewhere?). Obviously, if anyone still finds
mistakes above, I will be happy if those mistakes are explained.
In addition, I think it might be helpful for others to add the comment that
sqlite3_errmsg() does not return the correct text till sqlite3_reset() or
sqlite3_finalize() are called.
Thanks again,
Ran
On 5/23/06, John Stanton <[EMAIL PROTECTED]> wrote:
Why do you connect twice to the DB? You then run into synchronization
issues.
JS
Re: [sqlite] Re: "SQL logic error or missing database"
I would like to thank all of you. Although it all sounds logic to me, I was very far from finding the problem alone. So thanks again, Ran On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 5/23/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > > When a new connection is opened, SQLite reads and caches the schema. > Prepare operation uses this cached schema, without reading from disk. > This is why prepare doesn't know that schema has changed. > > It would be pretty pointless to have prepare check for schema changes. > This would incur a disk read, and the schema can very well change again > between prepare and step so this read won't achieve anything. Now, the > first step operation has to perform a disk I/O anyway, in order to > acquire a lock. This is when the schema is checked and, if a change is > detected, the current schema is retrieved and cached for the connection. Learned something important there, thanks Igor!
Re: [sqlite] "SQL logic error or missing database"
Indeed if I reset after the first step failed, and than prepare again, the select works. But I guess this is not the usual way to do things right? I mean - shouldn't the first prepare be aware of the fact that the database was changed? Or maybe CREATE TABLE is a special case? On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote: > > That doesn't seem right. > > The change was made and committed then the database statement > > prepared. > > The change should have already been written so the prepare > > should have gotten the latest stuff. Does it need to be > > closed and reopened for a schema change to be recognized?= > > Beats me, but that's the way it seems to work. You should always reset() a > statement after a failed step(), and check the reset()'s error code to see > if it's SQLITE_SCHEMA. If it is, you should re-prepare the statement. I > don't think it matters when the statement was prepare()'d as long as you > follow that rule. It was prepared after the schema was changed and written to disk. That seems pretty unintuitive to me. When you prepare the statement it evidently doesn't use the current schema, though it knows the schema has changed... I guess that implies the call to reset() reloads the schema I'll check that
Re: [sqlite] "SQL logic error or missing database"
Oh! Did you run it with a parameter so:
./bug 1
(otherwise the second connection is not created - sorry, I guess this is
confusing - I just wanted to show that the second connection or the second
table create the problem).
In any case, I reinstalled sqlite-3.3.4 on my Linux - and the bug is still
there. In addition, the bug was found originally on Windows (the same
version of sqlite), so I suspect it should be also there.
Ran
On 5/23/06, Robert Simpson <[EMAIL PROTECTED]> wrote:
> -Original Message-
> From: Ran [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 8:37 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "SQL logic error or missing database"
>
> On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > hmmm...
> > it gives a schema changed because 'delete * from x'
> actually drops the
> table
> > but I'm not sure why it gave an error since the prepare was
> done after
> > the other change was committed...
> > ...
>
> Thanks for your efforts!
>
> I am afraid that the delete is not connected to the problem.
> Also the prepare/step is not. Even the reset/finalize are not
> connected to it. The script below gives the problem, while
> all the create statements are run by sqlite3_exec (so no
> reset/finalize are used there at all). The problem happens
> only when stepping the select. See the updated script below:
I pasted your code into my Windows environment and ran it. It completed
successfully with no errors.
> // compile with: gcc -g bug.cpp -lsqlite3 -o bug #include
> #include #include #include
>
> int main(int argc, char** argv) {
> int rc;
> sqlite3* db1;
> sqlite3* db2;
> sqlite3_stmt *pStmt3;
>
> unlink("bug.db"); // for the test, we make sure we have a
> new database.
>
> // create first connection to the database: db1.
> rc = sqlite3_open("bug.db", );
> if (rc) {
> printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
> exit(1);
> }
> printf("Opened the database.\n");
>
> // create table bla using the first connection db1, inside
> a transaction.
> sqlite3_exec(db1, "begin", 0, 0, 0);
> rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
> if (rc != SQLITE_OK) { // if we failed, we show it.
> printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
> }
> sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the
> transaction.
>
> // here we, optionally, create another connection to the
> same database,
> // and then create other table in a transaction.
> if (argc > 1) {
> rc = sqlite3_open("bug.db", ); // create the second
> connection.
> if (rc) {
> printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
> exit(1);
> }
> else {
> printf("Opened the database.\n");
> }
>
> // create table foo
> sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
> rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
> if (rc != SQLITE_OK) { // if we failed, we show it.
> printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
> }
> sqlite3_exec(db2, "commit", 0, 0, 0);
> }
>
> // select from table bla using the first connection.
> sqlite3_exec(db1, "begin", 0, 0, 0);
> rc = sqlite3_prepare(db1,// Database handle
>"select * from bla",
>-1, // Length of the statement
>,// OUT: Statement handle
>0); // OUT: Pointer to
> unused portion
>
>// of the statement
> if (rc != SQLITE_OK) {
> printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
> }
> rc = sqlite3_step(pStmt3);
> if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
> }
> else {
> printf("deleted all from bla successfully\n");
> }
> rc = sqlite3_reset(pStmt3);
> sqlite3_exec(db1, "commit", 0, 0, 0);
> rc = sqlite3_finalize(pStmt3);
> }
>
Re: [sqlite] "SQL logic error or missing database"
On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
hmmm...
it gives a schema changed because 'delete * from x' actually drops the
table
but I'm not sure why it gave an error since the prepare was done after the
other change was committed...
...
Thanks for your efforts!
I am afraid that the delete is not connected to the problem. Also the
prepare/step is not. Even the reset/finalize are not connected to it. The
script below gives the problem, while all the create statements are run by
sqlite3_exec (so no reset/finalize are used there at all). The problem
happens only when stepping the select. See the updated script below:
// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include
#include
#include
#include
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt3;
unlink("bug.db"); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open("bug.db", );
if (rc) {
printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
exit(1);
}
printf("Opened the database.\n");
// create table bla using the first connection db1, inside a transaction.
sqlite3_exec(db1, "begin", 0, 0, 0);
rc = sqlite3_exec(db1, "create table bla(c int,d int)", 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf("Failed to exec statement: %s\n", sqlite3_errmsg(db1));
}
sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc > 1) {
rc = sqlite3_open("bug.db", ); // create the second connection.
if (rc) {
printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
exit(1);
}
else {
printf("Opened the database.\n");
}
// create table foo
sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
rc = sqlite3_exec(db2, "create table foo(c int,d int)", 0, 0, 0);
if (rc != SQLITE_OK) { // if we failed, we show it.
printf("Failed to exec statement: %s\n", sqlite3_errmsg(db2));
}
sqlite3_exec(db2, "commit", 0, 0, 0);
}
// select from table bla using the first connection.
sqlite3_exec(db1, "begin", 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
"select * from bla",
-1, // Length of the statement
,// OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
if (rc != SQLITE_OK) {
printf("Failed to prepare statement: %s\n", sqlite3_errmsg(db1));
}
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
else {
printf("deleted all from bla successfully\n");
}
rc = sqlite3_reset(pStmt3);
sqlite3_exec(db1, "commit", 0, 0, 0);
rc = sqlite3_finalize(pStmt3);
}
Re: [sqlite] "SQL logic error or missing database"
Thanks for your answer.
Actually, I tried to have resets there although I think that finalize is as
good in releasing the locks on tables. It didn't help.
I do it with prepare and step because this script is a demo of a bug I have
in my code, where I use all over a certain function that prepare and step.
It is true that here I could use also exec.
However, if I change the script to have the first two create statements run
by sqlite3_exec, and change the last statement to a select (to justify the
use of prepare) I still get the "SQL logic error or missing database". So I
suspect that the problem is because I use two open connections to the
database (again, this is a demo of the bug which happen in much bigger
application where opening two connections to the same database make sense),
and somehow when I create a second table with the second connection it
locks/invalidate or whatever the first connection and I get this error. I
even checked the code with valgrind, but got nothing...
Any help will be appreciated.
Ran
On 5/23/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
On 5/23/06, Ran <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>
> rc = sqlite3_prepare(db1,// Database handle
>"create table bla(a int,b int)",
>-1, // Length of the statement
>, // OUT: Statement handle
>0); // OUT: Pointer to unused
portion
>// of the statement
>
> rc = sqlite3_step(pStmt1);
> if (rc != SQLITE_DONE) { // if we failed, we show it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
> }
> rc = sqlite3_finalize(pStmt1);
> sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the
transaction.
You forgot the reset here:
int sqlite3_reset(sqlite3_stmt *pStmt);
http://sqlite.org/capi3ref.html#sqlite3_reset
Why are you preparing this statement?
Just sqlite3_exec() it.
>
> // now we suppose to have inside the database the table bla.
>
> // here we, optionally, create another connection to the same
database,
> // and then create other table in a transaction.
> if (argc > 1) {
> rc = sqlite3_open("bug.db", ); // create the second connection.
> if (rc) {
> printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
> exit(1);
> }
> else {
> printf("Opened the database.\n");
> }
You still have a valid handle to the open database. Why create a second
one?
>
> // create table foo
> sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
> rc = sqlite3_prepare(db2,// Database handle
> "create table foo(c int,d int)",
> -1, // Length of the statement
> ,// OUT: Statement handle
> 0); // OUT: Pointer to unused
> portion
> // of the statement
>
> rc = sqlite3_step(pStmt2);
> if (rc != SQLITE_DONE) { // if we failed, we show it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
> }
> rc = sqlite3_finalize(pStmt2);
> sqlite3_exec(db2, "commit", 0, 0, 0);
> }
>
> // delete from table bla using the first connection.
> sqlite3_exec(db1, "begin", 0, 0, 0);
> rc = sqlite3_prepare(db1,// Database handle
>"delete from bla",
>-1, // Length of the statement
>, // OUT: Statement handle
>0); // OUT: Pointer to unused
portion
>// of the statement
>
> rc = sqlite3_step(pStmt3);
> if (rc != SQLITE_DONE) { // if we failed, we log it.
> printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
> }
> else {
> printf("deleted all from bla successfully\n");
> }
> rc = sqlite3_finalize(pStmt3);
> sqlite3_exec(db1, "commit", 0, 0, 0);
> }
Again, why prepare something that returns no results and will
not be used more than once?
[sqlite] "SQL logic error or missing database"
Hi all,
Could someone help me with the script below? I get an "SQL logic error or
missing database" and cannot find what I do wrong.
I use sqlite 3.3.4 on Linux.
What I do there is:
1. Open connection to a new database.
2. Create table bla in a transaction.
3. Open another connection to the database.
4. Create table foo in a transaction using the second connection.
5. Try to delete from the table bla using the first connection. ==> this
gives "SQL logic error or missing database".
Here is how it runs (without arguments, the second connection is not opened,
and the table foo is not created - and this runs OK):
./bug
Opened the database.
deleted all from bla successfully
./bug 1
Opened the database.
Opened the database.
Failed to step statement: SQL logic error or missing database
Here is the script:
// compile with: gcc -g bug.cpp -lsqlite3 -o bug
#include
#include
#include
#include
int main(int argc, char** argv) {
int rc;
sqlite3* db1;
sqlite3* db2;
sqlite3_stmt *pStmt1;
sqlite3_stmt *pStmt2;
sqlite3_stmt *pStmt3;
unlink("bug.db"); // for the test, we make sure we have a new database.
// create first connection to the database: db1.
rc = sqlite3_open("bug.db", );
if (rc) {
printf("Cannot open database: %s\n", sqlite3_errmsg(db1));
exit(1);
}
printf("Opened the database.\n");
// create table bla using the first connection db1, inside a transaction.
sqlite3_exec(db1, "begin", 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
"create table bla(a int,b int)",
-1, // Length of the statement
, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt1);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
rc = sqlite3_finalize(pStmt1);
sqlite3_exec(db1, "commit", 0, 0, 0); // here we commit the transaction.
// now we suppose to have inside the database the table bla.
// here we, optionally, create another connection to the same database,
// and then create other table in a transaction.
if (argc > 1) {
rc = sqlite3_open("bug.db", ); // create the second connection.
if (rc) {
printf("Cannot open database again: %s\n", sqlite3_errmsg(db2));
exit(1);
}
else {
printf("Opened the database.\n");
}
// create table foo
sqlite3_exec(db2, "begin", 0, 0, 0); // start the transaction.
rc = sqlite3_prepare(db2,// Database handle
"create table foo(c int,d int)",
-1, // Length of the statement
,// OUT: Statement handle
0); // OUT: Pointer to unused
portion
// of the statement
rc = sqlite3_step(pStmt2);
if (rc != SQLITE_DONE) { // if we failed, we show it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db2));
}
rc = sqlite3_finalize(pStmt2);
sqlite3_exec(db2, "commit", 0, 0, 0);
}
// delete from table bla using the first connection.
sqlite3_exec(db1, "begin", 0, 0, 0);
rc = sqlite3_prepare(db1,// Database handle
"delete from bla",
-1, // Length of the statement
, // OUT: Statement handle
0); // OUT: Pointer to unused portion
// of the statement
rc = sqlite3_step(pStmt3);
if (rc != SQLITE_DONE) { // if we failed, we log it.
printf("Failed to step statement: %s\n", sqlite3_errmsg(db1));
}
else {
printf("deleted all from bla successfully\n");
}
rc = sqlite3_finalize(pStmt3);
sqlite3_exec(db1, "commit", 0, 0, 0);
}
Re: [sqlite] segmentation fault error?
Find out yourself :-)
If you are using gcc, add -g flag when compiling, and then run it using gdb:
gdb your-executable
and then
> run
and then when it give the seg-fault, run:
> backtrace
This will give you a good hint about what is wrong.
Ran
On 4/6/06, 杰 张 <[EMAIL PROTECTED]> wrote:
>
> Hi all,
> I just want to get the values of a table.The result implemented is
> "Open OK!
> segmentation fault ". Why did I got this result ? The following is my
> code:
>
> #include
> #include
> #include "sqlite3.h"
> main()
> {
> char **errmsg;
> int ret;
> int rc;
> sqlite3 *db;
> char *sql = "SELECT * FROM light;";
> char ***resultp;
> int *nrow;
> int *ncolumn;
> ret = sqlite3_open("sensor.db",);
> if (ret)
> {
> fprintf(stderr, "Could not open database:%s\n",
> sqlite3_errmsg(db));
> exit (1);
> }
> else
> { printf("Open OK!\n");
> rc = sqlite3_get_table(db,sql,resultp,nrow,ncolumn,errmsg);
> printf("rc=%d\n",rc);
> if (rc)
> {
> fprintf(stderr,"can't open the
> table:%s\n",sqlite3_errmsg(db));
> exit(1);
> }
> else printf("open the table ok");
>}
>
> sqlite3_close(db);
> printf("Close OK!");
> }
>
> Thank you so much!
>
> zhangjie
>
>
>
> -
> 雅虎1G免费邮箱百分百防垃圾信
> 雅虎助手-搜索、杀毒、防骚扰
>
Re: [sqlite] Re: Using a table as a negative filter.
Thanks Igor!
This solves a big question for me :-)
Ran
On 3/29/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Ran <[EMAIL PROTECTED]> wrote:
> > For example:
> >
> > create temp table A(col1 varchar, col2 varchar, col3 varchar, col4
> > varchar);
> >
> > insert into A values('a', 'A', '1', 'n');
> > insert into A values('a', 'a', '2', 'e');
> > insert into A values('b', 'B', '3', 'n');
> > insert into A values('a', 'A', '4', 'n');
> > insert into A values('b', 'b', '5', 'e');
> > insert into A values('c', 'c', '6', 'n');
> >
> > create temp table B(col1 varchar, col2 varchar, col3 varchar);
> >
> > insert into B values('a', '1', 'a');
> > insert into B values('b', '2', 'b');
> > insert into B values('d', '3', 'd');
> > insert into B values('e', '3', 'e');
> >
> > So, in order to get all the rows of table A where col1 and col2 are
> > equal to any of the col1 and col3 of the rows of table B, one can
> > write:
> >
> > select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 =
> > B.col1 and A.col2 = B.col3;
> >
> > Which returns:
> >
> > a|a|2|e
> > b|b|5|e
> >
> > However, how could I get the other rows - so those rows in table A
> > which do NOT match to the rows of table B (using the same columns)?
>
> select * from A where not exists
> (select * from B where A.col1 = B.col1 and A.col2 = B.col3);
>
> or
>
> select A.* from A left join B on (A.col1 = B.col1 and A.col2 = B.col3)
> where B.col1 is null;
>
> Igor Tandetnik
>
[sqlite] Using a table as a negative filter.
Hi all,
I would like to ask if anyone can suggest how to filter out rows of one
table by using negation over several columns of rows of other table.
For example:
create temp table A(col1 varchar, col2 varchar, col3 varchar, col4
varchar);
insert into A values('a', 'A', '1', 'n');
insert into A values('a', 'a', '2', 'e');
insert into A values('b', 'B', '3', 'n');
insert into A values('a', 'A', '4', 'n');
insert into A values('b', 'b', '5', 'e');
insert into A values('c', 'c', '6', 'n');
create temp table B(col1 varchar, col2 varchar, col3 varchar);
insert into B values('a', '1', 'a');
insert into B values('b', '2', 'b');
insert into B values('d', '3', 'd');
insert into B values('e', '3', 'e');
So, in order to get all the rows of table A where col1 and col2 are equal to
any of the col1 and col3 of the rows of table B, one can write:
select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and
A.col2 = B.col3;
Which returns:
a|a|2|e
b|b|5|e
However, how could I get the other rows - so those rows in table A which do
NOT match to the rows of table B (using the same columns)?
Just to change the equal signs to non-equal won't work because the tables
are joined - so many rows that are not wanted are returned.
Of course I could write:
select A.col1, A.col2, A.col3, A.col4 from A
except
select A.col1, A.col2, A.col3, A.col4 from A, B where A.col1 = B.col1 and
A.col2 = B.col3;
Which gives correctly:
a|A|1|n
a|A|4|n
b|B|3|n
c|c|6|n
But I wonder if there is a better way (so which perform better) to get those
rows. Any suggestions?
Thanks a lot,
Ran
Re: [sqlite] Stored procedures in triggers
See the email of Igor Tandetnik from 18-Dec-2005: "Vishal Kashyap" wrote > Is their any way we can write simple stored procedures or functions > in sqlite. If yes please do guide me I need this functionality in one > of my open source project. Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your "stored procedure" manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Ran On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > > Hi, > > Can you tell me how to create a stored procedure in an sqlite3 database > and use the same in a trigger? Please provide an example (as complete as > possible). In the stored procedure I need to execute few queries on some > tables. Can you tell me how to do that also? > > Any help is deeply appreciated. > > Best Regards, > > Chethana >
Re: [sqlite] performance statistics
My question is not about extending/improving SQLite but about having an extra tool which helps to optimize the SQL written for SQLite. So SQLite stays indeed lightweight and fast, but the SQL it is fed with is automatically optimized. Ran On 3/1/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > On 3/1/06, Ran <[EMAIL PROTECTED]> wrote: > > In light of your answer, I wonder if it is possible to implement such > > optimizer that does the hand-optimizing automatically, but of course > BEFORE > > they are actually being used by SQLite. > > > > So the idea is not to make SQLite optimizer better, but to create a kind > of > > SQL optimizer that gets as input SQL statements and gives as output > > optimized (specifically for SQLite) SQL statements. > > I think the concept so far has been that the programmer is the query > optimizer so it stays fast and lightweight. ;) >
Re: [sqlite] performance statistics
In light of your answer, I wonder if it is possible to implement such optimizer that does the hand-optimizing automatically, but of course BEFORE they are actually being used by SQLite. So the idea is not to make SQLite optimizer better, but to create a kind of SQL optimizer that gets as input SQL statements and gives as output optimized (specifically for SQLite) SQL statements. Ran On 3/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > PostgreSQL has a much better query optimizer than SQLite. > (You can do that when you have a multi-megabyte memory footprint > budget versus 250KiB for SQLite.) In your particular case, > I would guess you could get SQLite to run as fast or faster > than PostgreSQL by hand-optimizing your admittedly complex > queries. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] How to differentiate between Zero and NULL in an integer field
I think that sqlite3_column_type suppose to tell you if it is NULL. http://www.sqlite.org/capi3ref.html#sqlite3_column_type It returns SQLITE_NULL in that case. However I don't see how you can avoid calling two functions in a scenario you suspect a NULL (so if sqlite3_column_int returns a zero, you should call sqlite3_column_type to check if it is NULL). Ran On 2/28/06, Luiz Americo Pereira Camara <[EMAIL PROTECTED]> wrote: > > The sqlite documentation states that when the stored value of an Integer > field is NULL and is used sqlite3_column_int to retrieve the data, the > returned value is 0. > Currently to know if the value is zero or NULL i check the value of > sqlite3_column_text (that returns NULL in that case). The drawback of > this solution is that i retrieve two times the same record. Is there a > more straight forward way of doing this? > > Thanks In Advance, > Luiz > <http://www.sqlite.org/capi3ref.html#sqlite3_column_int> >
Re: [sqlite] Strange execution times
Could it be connected to the stepping up of the CPU? Do you run those tests
on a laptop? This at least could explain how the many iterations are faster
(the CPU has time to step up).
It does not explain why the 10 and 5 are fast as well (maybe when doing few
iterations, the time calculation is less accurate), but mmm... maybe it
could explain part of the phenomena?
Ran
On 2/22/06, Ulrich Schöbel <[EMAIL PROTECTED]> wrote:
>
> Hi Adrian,
>
> I tried your script and got, after a slight modification, quite
> consistent results. When I tried it as is, I got slightly varying
> time results with a peak in the 50 to 100 region. Then I
> commented out all lines concerning the deletion, creation
> and filling to get the pure retrieval times. Drom then on
> I got the following almost invariable results,
>
> t(1)=538 microseconds per iteration
> t(5)=69.2 microseconds per iteration
> t(10)=39.9 microseconds per iteration
> t(50)=391.48 microseconds per iteration
> t(100)=215.61 microseconds per iteration
> t(500)=73.154 microseconds per iteration
> t(1000)=54.753 microseconds per iteration
> t(5000)=40.9094 microseconds per iteration
> t(1)=39.4558 microseconds per iteration
>
> The t(1) time is probably due to Tcls bytecode engine, but
> the t(50) and t(100) times are inexplicable, at least for me.
>
> The 'mini database' you use is, apart from a few additional
> fields, almost identical to the one I used in my previous tests.
>
> Do you come to similar results?
>
> I have to oppose your statement, Tcl has garbage collection.
> It doesn't, at least in the sense, that it calls a routine to
> collect unused space and free it at arbitrary times, i.e. during
> idle times. Tcl collects its garbage when there is some. Tcls
> objects are reference counted and as soon as this count
> reaches zero the object is cleaned up. This costs time, of
> course, but it happens each time the garbage is due. That
> has the effect, that garbage collection times are simply
> included in execution times, regularly. It should not produce
> the peak times I see at t(50) and t(100).
>
> Thanks for your help
>
> Ulrich
>
> On Wednesday 22 February 2006 02:45, Adrian Ho wrote:
> > On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Sch??bel wrote:
> > > I don't think it's an interface problem. I'm using Tcl, more or less
> > > the 'natural' language for sqlite. Tcl doesn't have a garbage
> > > collection.
> >
> > Tcl certainly *does* have garbage collection:
> >
> > <http://wiki.tcl.tk/3096>
> > <http://wiki.tcl.tk/12144>
> >
> > > The strangest thing is, I can reproduce this behaviour.
> > > I'm absolutely clueless. I stumbled over it by coincidence.
> > > Tried 1000 repetitions, was quite fast, so I tried 1,
> > > which was even faster. This led me to the (obviously wrong)
> > > conclusion, that sqlite spends some time parsing the sql.
> > > Next I tried 100 repetitions, expecting a bit more than
> > > 76 microseconds. 310 microsecs didn't bother me really,
> > > I tried the 10 reps expecting even more. Then came the surprise:
> > > only 67 microsecs.
> > >
> > > My first feeling was, something like a busy disk or so came
> > > in just when I tried the 100 reps. But the results were reproducible,
> > > deviating only by a few microseconds.
> >
> > Try running the following script and see if there's an odd pattern to
> > the timing variations:
> >
> > #!/usr/bin/env tclsh
> > package require sqlite3
> > if {[file exists aho.db]} {
> > file delete aho.db
> > }
> > sqlite3 db aho.db
> > db eval {create table cust_persons ( first_name string, last_name string
> > )}
> > db eval {insert into cust_persons values ('Adrian','Ho')}
> > db eval {insert into cust_persons values ('Thunder','Lightning')}
> > foreach rounds {1 5 10 50 100 500 1000 5000 1} {
> > puts "t($rounds)=[time {db eval {select * from cust_persons where
> > first_name = 'Adrian'}} $rounds]" }
> > db close
> >
> > - Adrian
>
[sqlite] print for debugging from triggers
Hi all,
I use many triggers and for debugging purposes I wanted to know which one is
triggered and when.
At first I thought that if I write a SELECT within the BEGIN-END block, this
SELECT results will be printed (at least when using sqlite3 command line
program). But this didn't work. So I wrote a simple print method that takes
one argument and printf it to the standard out. This works and actually
solves my problem.
I can write something like:
create trigger bla after delete on foo
begin
select print('bla trigger is triggered');
end;
But I still wonder - this solution is quite simple, yet very useful - so I
suspect I missed an existing feature. Is there a builtin feature like this?
Thanks,
Ran.
Re: [sqlite] Checking for string length
See http://www.sqlite.org/lang_expr.html Ran On 1/20/06, malcom <[EMAIL PROTECTED]> wrote: > > Hello, is possible with sqlite to return a row with a column string > length > n chars? > > ex: SELECT * FROM TABLE WHERE MY_COLUMN LENGTH > 0 > > thanks >
Re: [sqlite] Sqlite and Java
If I am not mistaken, the following thread might be relevant: http://www.mail-archive.com/sqlite-users@sqlite.org/msg11005.html Ran On 1/19/06, Nilo Paim <[EMAIL PROTECTED]> wrote: > > Hi all, > > Does anybody here knows something about a port of sqlite to java? > > Please, note that I'm not talking about java calling sqlite via JNI, but > about a real rewrite of sqlite using java. Obviously, a second step > would be the writing of a JDBC driver. > > Would be useful that port? > > Comments? Suggestions? > > Thanks to all. > > Nilo > Porto Alegre - Brasil >
Re: [sqlite] Final Year Project/Dissertation help required!!
Dear John, Unlike some other readers here, I appreciate very much your question. I find it very good that students choose to work with open source in their final projects. Many successful open source projects started this way. So I call this healthy cooperation and not "asking the community to do your homework". And to your question: An original (I think) feature I would really be happy to find in such a GUI is some sort of graphical representation of the explanation of a query (EXPLAIN). Something that will be clear enough so the users can understand why the query runs slower then expected, how to rewrite it to run faster, or which index could be added. I know that some of you out there can read vdbe while asleep. But I am not that fast, and although I spent the time learning it, I still find it tough to understand for complex queries. Ran. On 12/11/05, John Newby <[EMAIL PROTECTED]> wrote: > > Hi, I am doing my final year project/dissertation on "Creating a GUI to > SQLite" > > I am after help for my analysis phase and would like any comments/help > from > anyone or if you could put me in the right direction as to where I could > find out this information. > > I would like information regarding SQLite for the following if at all > possible:- > > Why users use SQLite instead of other SQL databases? > Would a GUI detract users from using SQLite? > > Regarding a GUI to SQLite:- > > What features a GUI MUST have? > What features you would LIKE a GUI to have? > What features you would NOT LIKE to have? > > Any help on these questions or any other information you may feel would > help > me on my journey would be greatfully appreciated. > > You can contact me at my University email address on [EMAIL PROTECTED] > > Many thanks for your help. > > John. > >
[sqlite] Compiling from source code on Windows
Hi all, I have added some functionality to the source of SQLite3 to be used in a project I work on. The changes were done in func.c and in sqlite.h.in<http://sqlite.h.in>(I added one function to the API). This was done in Linux, and compiles and run fine. Now I would like to port it also to windows and I am not sure how I can build it using VC++. In the site of sqlite, there is a zip file with already processed files for building in Windows. But obviously, I prefer to build from the same source I have instead of copying/hacking the files in that zip file. Maybe I missed the instruction in the site, but I could not find it there, nor in the archives of the mailing list. Can anyone can tell which tools should I install on Windows for preparing the files from the tar.gz sources to be built on windows? In addition, I wonder if anyone has a solution file or a project file for VC++ for building the .lib and the sqlite3 executable, or can give any hints about how to build SQLite on Windows using VC++. Thanks in advance, Ran
