[sqlite] Fwd: In-Memory Database - problem with DROP TABLE
Dear All,
I have a piece of code which is shown below, Im doing In-Memory database
operation
I have done these settings in SQLite, in btree.h SQLITE_DEFAULT_AUTOVACUUM =
1, TEMP_STORE=2 in sqliteint.h (this should be made compulsory, if
concentrating only on in-memorydatabase, where no disk is available)
Now the problems I'm facing
---
1) Last two queries, i.e., DROP TABLE query, since AUTOVACUUM is on
(overriding pBt->autoVacuum=SQLITE_DEFAULT_AUTOVACUUM in Btree.c in
sqlite3BtreeOpen function), while dropping the table, the memory increases
double the original table occupied. I have observed at sqlite3BtreeClearTable
in btree.c. why is this?
2) I'm afraid, If I do the DROP TABLE in sequence i.e., how I created my
tables, memory doesn't get deleted. It is expecting stack basis. ie., if I
DROP recently created TABLE then it works(memory frees perfectly). why is
this unexpected behaviour? If you see my code below, as it is code doesn't
work for DROP (memory is freed only for the last TABLE). If the two queries
are interchanged, memory free happens correctly.
any reply's are welcome.
Thanks in advance.
#define SCHEMA_TWO_USED
#ifdef SCHEMA_TWO_USED
static char *database_name = ":memory:";
static sqlite3* db_handle;
static char queryString[1024];
#define PRINT_TIME \
{ \
unsigned long millisec = clock(); \
printf("milliseconds = %ld\n", millisec); \
}
static const char *SchemaTwo[] = {
"CREATE TABLE testTbl_1 (t_id INTEGER, t_id2 INTEGER, t_name TEXT,
t_desc TEXT, PRIMARY KEY (t_id2));",
"CREATE TABLE testTbl_2 (t_id INTEGER, t_id2 INTEGER, t_name TEXT,
t_desc TEXT, PRIMARY KEY (t_id2));",
"INSERT INTO testTbl_1 (t_id,t_id2,t_name,t_desc) VALUES
(%d,%d,'%s','%s');",
"INSERT INTO testTbl_2 (t_id,t_id2,t_name,t_desc) VALUES
(%d,%d,'%s','%s');",
};
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
for (i = 0; i < argc; i++)
{
printf("%s ", argv[i]);
}
printf("\n");
return 0;
}
int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status, i = 0;
/* open the database */
status = sqlite3_open(database_name, _handle);
if (status)
{
printf("%d", status);
return 0;
}
/* Create Table 1 */
PRINT_TIME
status = sqlite3_exec(db_handle, SchemaTwo[0], NULL, 0, );
if (SQLITE_OK == status)
{
char name[30];
char desc[50];
for (i = 0; i < 1 && status == SQLITE_OK; i++)
{
sprintf(name,"TableName""%d", i);
sprintf(desc,"Moves the selected control or dialog down""%d",
i);
sprintf(queryString,
SchemaTwo[2], i, i+125436, name, desc);
status = sqlite3_exec(db_handle, queryString, NULL, 0,
);
}
}
PRINT_TIME
/* Create Table 2 */
status = sqlite3_exec(db_handle, SchemaTwo[1], NULL, 0, );
if (SQLITE_OK == status)
{
char name[30];
char desc[50];
for (i = 0; i < 1 && status == SQLITE_OK; i++)
{
sprintf(name,"TableName""%d", i);
sprintf(desc,"Moves the selected control or dialog down""%d",
i);
sprintf(queryString,
SchemaTwo[3], i, i+125436, name, desc);
status = sqlite3_exec(db_handle, queryString, NULL, 0,
);
}
}
PRINT_TIME
sprintf(queryString,
"SELECT * FROM sqlite_master;");
status = sqlite3_exec(db_handle, queryString, callback, 0, );
printf("Before Start of SELECT *: "); PRINT_TIME
sprintf(queryString,
"SELECT * FROM testTbl_1;");
status = sqlite3_exec(db_handle, queryString, callback, 0, );
printf("After Complete of SELECT *: "); PRINT_TIME
sprintf(queryString,
"DROP TABLE IF EXISTS testTbl_1;");
status = sqlite3_exec(db_handle, queryString, NULL, NULL, NULL);
sprintf(queryString,
"DROP TABLE IF EXISTS testTbl_2;");
status = sqlite3_exec(db_handle, queryString, NULL, NULL, NULL);
return 0;
}
#endif /* SCHEMA_TWO_USED */
[sqlite] Random SQLITE_SCHEMA errors on Windows Mobile 6
I have a NETCF application that uses SQLite3 version 3.3.5. Its initialization code creates the database and several tables, and creates indexes on the tables. It works fine on devices both WinCE 4.20 (2003SE) and 5.00 (WM5), but on some 5.10 WM6 devices, it's failing. In each confirmed case so far, it's been a WM6 SmartPhone. The problem seems to occur when processing a CREATE UNIQUE INDEX statement immediately after a CREATE TABLE statement. sqlite3_step is returning SQLITE_ERROR, and the subsequent sqlite3_finalize returns SQLITE_SCHEMA. There's no reason this should be happening. The initialization code runs in a separate thread than the UI, but the main thread doesn't touch the database until later. In addition, if I set breakpoints and step through my code, it works fine. The SQLITE_SCHEMA error is generated by sqlite3VdbeExec processing opcode OP_VerifyCookie. The p1 parameter is zero (main database) and p2 is one more than the database schema version returned by sqlite3BtreeGetMeta. I added the retry code from the SQLite FAQ, but it doesn't help. After retrying the SQLITE_SCHEMA error zero or more times (again, apparently random), sqlite3_prepare fails with "no such table: main." (even though the table was created immediately prior, without error). I found a comment in vdbe.c above the OP_SetCookie code (which is used to update the schema version) that says "A transaction must be started before executing this opcode." So I added code to do just that before my table creation code, and it fixed the problem. My question is why would this work on earlier versions of WinCE, and even on other devices running WM6? It's the same binary. I suspect an uninitialized variable somewhere. Regards, David - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: where is SQLITE_OPEN_READONLY defined?
Ah shoot didn't realize that, thanks. Do you happen to know by any chance if opening in read-only mode improves performance at all? Or is it really just a safe-guard feature. Thanks, Mark On 9/23/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Mark Wyszomierski <[EMAIL PROTECTED]> > wrote: > > I wanted to open a sqlite database read-only - where is: > > > >SQLITE_OPEN_READONLY > > > > defined? > > It's new in SQLite v3.5 . Which version are you using? > > Igor Tandetnik > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: where is SQLITE_OPEN_READONLY defined?
Mark Wyszomierski <[EMAIL PROTECTED]> wrote: I wanted to open a sqlite database read-only - where is: SQLITE_OPEN_READONLY defined? It's new in SQLite v3.5 . Which version are you using? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] test failures on cygwin
On Saturday 22 September 2007 14:20, Evans, Mark (Tandem) wrote: > It's hard to drag my Linux server to Starbucks. :-) > > Next Windows laptop, though, will definitely have to have a > Linux/Windows dual personality. I'll have to wait until the next > mega-merger for a Mac. > > Mark > If your HD has the space load Suse Linux from Novel and run Windos or Linux. You can down load for free Novel's latest version. jim-on-linux http://www.inqvista.com > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Friday, September 21, 2007 6:05 PM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] test failures on cygwin > > > > > Of course, the easiest option by far is to use a Linux box or > > a Mac. :-) > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > --- >-- To unsubscribe, send email to > [EMAIL PROTECTED] > --- >-- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] where is SQLITE_OPEN_READONLY defined?
Hi, I wanted to open a sqlite database read-only - where is: SQLITE_OPEN_READONLY defined? Thanks - To unsubscribe, send email to [EMAIL PROTECTED] -
