[sqlite] In-Memory Database - problem with DROP TABLE

[Babu, Lokesh]

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, &db_handle);
    if (status)
    {
        printf("%d", status);
        return 0;
    }

    /* Create Table 1 */
    PRINT_TIME
    status = sqlite3_exec(db_handle, SchemaTwo[0], NULL, 0, &zErrMsg);
    if (SQLITE_OK == status)
    {
        char name[30];
        char desc[50];

        for (i = 0; i < 10000 && 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,
&zErrMsg);
        }
    }
    PRINT_TIME
    /* Create Table 2 */
    status = sqlite3_exec(db_handle, SchemaTwo[1], NULL, 0, &zErrMsg);
    if (SQLITE_OK == status)
    {
        char name[30];
        char desc[50];

        for (i = 0; i < 10000 && 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,
&zErrMsg);
        }
    }
    PRINT_TIME

    sprintf(queryString,
        "SELECT * FROM sqlite_master;");
    status = sqlite3_exec(db_handle, queryString, callback, 0, &zErrMsg);

    printf("Before Start of SELECT *: "); PRINT_TIME
    sprintf(queryString,
        "SELECT * FROM testTbl_1;");
    status = sqlite3_exec(db_handle, queryString, callback, 0, &zErrMsg);
    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 */