Re: [sqlite] to retreive OID

[Roger Binns]

abiramip wrote:
> Hi,
> In the command prompt if i give the following,
> select OID from tablename;
> am able to get the corresponding OID...
> If i use sqlite3_prepare() APIhow do i proceed inorder to do the same ?
> 
> Please suggest a procedure to  perform this.

Look at this page:

  http://sqlite.org/capi3ref.html

In particular you'll want to read on sqlite3_step, sqlite3_finalize,
sqlite3_column_count, sqlite3_column_type etc

Alternately, you may want to consider the simpler sqlite3_exec api with
an example at

  http://sqlite.org/quickstart.html

Roger

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] to retreive OID

[abiramip]

Hi,
In the command prompt if i give the following,
select OID from tablename;
am able to get the corresponding OID...
If i use sqlite3_prepare() APIhow do i proceed inorder to do the same ?

Please suggest a procedure to  perform this.
Thanks in advance .

Regards,
abirami p

[sqlite] Error: database is locked on _all_ sqlite tables

[Jay Johnston]

I'm having a strange problem since earlier today where all sqlite tables on
my shared webserver seem to be locked.  I have a trac installation using
sqlite that started giving me "database is locked" errors earlier today and
continues to do so.  This is not an intermittent problem -- since it began I
have not been able to open a sqlite file.  If I copy a database file and try
and open it, I receive the same error.  Further, if I create a sqlite file
on my laptop and upload it to the server, even as a different user, I
receive the same error.  Could a change have been made on this server
causing POSIX locking to fail?

Thanks, Jay

[sqlite] null pointer problem

[Dixon Hutchinson]

I am running version 3.3.6 compiled with Visual Studio .net 2003 in a 
single threaded app.


While using sqlite3_exec to execute a "COMMIT;", in run into a null 
pointer problem in sqlite3_step.


I added tests for the null pointer to vdbeapi.c:

if (NULL == p) {
 DebugBreak();
 }
   #ifndef SQLITE_OMIT_EXPLAIN
 if( p->explain ){
   rc = sqlite3VdbeList(p);
 }else
   #endif /* SQLITE_OMIT_EXPLAIN */
 {
   rc = sqlite3VdbeExec(p);
 }

 if (NULL == p) {
 DebugBreak();  /* This is the point the failure is detectected */
 }


So it seems that sqlite3VdbeExec(p) is somehow clobbering my statement 
pointer.


Anybody have a suggestion?

[sqlite] legacy_file_format

[Mark Richards]

With sqlite version 3.3.7 if I create a new database using:

if (sqlite3_open("/var/tmp/solarwave/aem.db", )!=0)
{
printf("Cannot open db\n");
return(false);
}

and then issue the following:

snprintf(query, QUERY_SIZE, "%s", "PRAGMA legacy_file_format = ON");
nResult=sqlite3_exec(db, query, NULL, NULL, );


snprintf(query, QUERY_SIZE, "%s", "PRAGMA auto_vacuum = 1");
nResult=sqlite3_exec(db, query, NULL, NULL, );
snprintf(query, QUERY_SIZE, "%s", "PRAGMA empty_result_callbacks = 
 1");

nResult=sqlite3_exec(db, query, NULL, NULL, );
snprintf(query, QUERY_SIZE, "%s", "PRAGMA synchronous = NORMAL");
nResult=sqlite3_exec(db, query, NULL, NULL, );


Everything works fine - within the application.  However I have a 
php/sqlite combo that also talks to the same database.  When it does, I get


Warning: sqlite_open(): file is encrypted or is not a database
in /mnt/flash/runtime/exec/dumprecords.php on line 46

Doesn't PRAGMA legacy_file_format = ON cover this?  The docs state:

When this flag is on, new SQLite databases are created in a file
format that is readable and writable by all versions of SQLite
going back to 3.0.0. When the flag is off, new databases are
created using the latest file format which might to be readable
or writable by older versions of SQLite.

Now since the docs say "When this flag is ON, new SQLite databases are 
created in a file format that is readable...", I wondered if this meant 
that I have to set the PRAGMA *before* creating the db?  How is that 
possible??


Or maybe this PRAGMA is broken?

/m

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] met "ARM7TDMI raised an exception,data abort" when executing sqlite3Parser() in ARM environment

[Dennis Cote]

Sarah wrote:
I'm facing new problems right now. the record cann't be inserted correctly when I execute the following statements. 
..

char * database = ":memory:";
 sqlite3 * db;
  sqlite3_open(database, );
  sqlite3_exec(db, "create table myt(name varchar(30),age smallint)", NULL, 
NULL, NULL);
  sqlite3_exec(db, "insert into myt values('sarah',27)", NULL, NULL, NULL);
  sqlite3_exec(db, "select * from myt", NULL, NULL, NULL);
  sqlite3_close(db);
..

The behavior is that , when executing "insert into myt values('sarah',27)",  the program goes into the following statements in sqlite3RunParser() and returns 
SQLITE_NOMEM. How and why? 
..

abort_parse:
  if( zSql[i]==0 && nErr==0 && pParse->rc==SQLITE_OK ){
if( lastTokenParsed!=TK_SEMI ){
  sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);
  pParse->zTail = [i];
}
sqlite3Parser(pEngine, 0, pParse->sLastToken, pParse);
  }
  sqlite3ParserFree(pEngine, sqlite3FreeX);
  if( sqlite3MallocFailed() ){
pParse->rc = SQLITE_NOMEM;
  }
..

I totally have no idea of the principals of parser and how it works. Could 
someone tell me or give me some links?

finally, a stupid question:
should I add a semicolon at the end of the sql statement in sqlite3_exec(); 
Will that affect the execution of parser?

  

Sarah,

First adding a semicolon at the end of your SQL statements will make no 
difference.


What happened was that sqlite ran out of memory at some point while 
parsing your SQL. The sqlite3MallocFailed call simply checks if sqlite's 
internal malloc has ever failed to get the requested memory.  Since this 
is a very simple SQL statement, the memory requirements for parsing 
should be quite low. I would suspect a problem with your memory 
allocator which is being called by sqlite through the standard malloc 
API. You should be able to see where this is happening by setting a 
breakpoint on sqlite3FailedMalloc in util.c and looking back through the 
call stack.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Retrieving data

[Dennis Cote]

Richard Stern wrote:

Hi all.

I'm using sqlite in VC++ 2005.
When I started this I knew nothing about sqlite or indeed SQL at all so its
been tough going trying to work out how this all works.

So far I have created a database and a table and added columns and rows
filled with data. But I'm having trouble retrieving that data.

Lets say I have the columns MemberNo, Name and Address.
I want to use a specific MemberNo to retrieve a name and address and store
them in separate variables.

I tried:
sqlite3_exec(AccDataBase,"SELECT Name,Address FROM Accounts WHERE MemberNo =
2;",Callback(cError,10,,),test,);

Now I don't fully understand how the callback part works so I just made the
variables that seemed appropriate and threw them in. I thought the "result"
one was supposed to get filled by the result of the SELECT, but it wasn't.

When I ran this, no error was returned but the callback didn't seem to do
anything.

So is this the correct command to use? Is there a better/easier way?

  

Rick,

To use the callback interface you need to define a callback function in 
your C code. This function MUST have the correct signature so it can be 
called correctly by sqlite. This signature is defined in sqlite3.h as


/*
** The type for a callback function.
*/
typedef int (*sqlite3_callback)(void*,int,char**, char**);

The sample program at http://www.sqlite.org/quickstart.html shows how to 
define a callback function.


static int callback(void *user_data, int argc, char **argv, char **azColName)
{
'''
}

Now, when you call sqlite3_exec() you need to pass the address of this 
function (i.e. its name) to sqlite so that it can call your function 
when it has a result. Your call will look something like the one on the 
quickstart page.


rc = *sqlite3_exec*(db, sql_query, callback, my_data, );


Each time sqlite has a result from your query it will call your callback 
function and pass it the result data and the column names as parameters. 
Your function can then do whatever it wants to with this data before it 
returns. If your query generates 100 result rows, your callback function 
will be called 100 times.


After you get the sqlite3_exec version working, you should look at the 
sqlite3_prepare, sqlite3_step, sqlite3_column_* set of APIs which are 
newer and generally easier to use for queries.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: to retrieve OID

[Igor Tandetnik]

abiramip <[EMAIL PROTECTED]> wrote:

In the command prompt if i give the following,
select OID from tablename;
am able to get the corresponding OID...
Is it possible to do the same by using APIs?


Sure. You use the API to run the query "select OID from tablename;", 
just as you would any other query.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] to retrieve OID

[Dennis Cote]

abiramip wrote:

In the command prompt if i give the following,
select OID from tablename;
am able to get the corresponding OID...
Is it possible to do the same by using APIs?If so which API can i use inorder 
to retreive OID  from a table.
Can i use sqlite3_get_table() or sqlite3_prepare() APIs?
Or is there any other way to do this ?

  
Yes, you can use either the sqlite3_get_table or 
sqlite3_prepare/sqlite3_step/sqlite3_column_* APIs to get the results of 
your select query. You could also use the sqlite3_exec API. You will 
probably be better of using the prepared statement API routines rather 
than the older API's (sqlite3_get_table and sqlite3_exec) which are 
intended primarily for backwards compatibility.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: RE: [sqlite] Retrieving data

[thomas . l]

Hi Richard

>> You don't need a Callback-Function in any case. Try it
>> without

> I'm confused.
> How does the SELECT command return any data? In what
> form would it give you this data back? There doesn't seem to
> be a pointer to pass by reference and no out variables.

The Prepare-Command with it's given SQL-Statement doesn't
return Data. *Imho* it prepares only the Database-Engine. I
fetch the Data after I "prepared" the Database with
"_sqlite3_step()".

I say it again. Spend a little time to my samples.

Best Regards
Thomas

--
www.thlu.de



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Very wide tables and performance

[jose simas]

Hi Dennis,

This is an OLAP product and indeed we don't know upfront how much of
that data is relevant to the processing. I had a normalized group of
tables for those sets but it took an awfull time to load and to
process. In practice the wider tables are usually 25,000 columns per
50 to 100 rows and it would make sense to "rotate" the data before
processing and store it in 50 to 100 columns per 25k rows. But the
problem is that the same application has to cope with sets with say 5
columns and some 100,000 rows. Rotating sometimes and not in others
would increase the complexity of the code. The application is very
"interactive" and we try to keep the data load as low as possible so
load time is key for us.

SQLite performance is oustanding in comparison with what we used
before (Access), our load times improved up to ten times and with
Access we were limited to 250 columns and the normalized version took
forever to load one million data points.

Of course the schemas are generated when the user loads their data...
and you are right, they are unreadable :-)

Cheers
Jose

On 9/14/06, Denis Povshedny <[EMAIL PROTECTED]> wrote:

Hi Jose!

It is really hard to believe that you do not have a sparse matrix. I
mean that for every single row: from 2 colums only a several columns
are used and others are nil. This is a point to perform so-called
normalizations for the table. The single exception what I remember is
OLAP databases.

Anyway, I (and probably not only me) am very excited to see definition
for this table ;)

WBR, Denis

-Original Message-
From: jose simas [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 13, 2006 11:05 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Very wide tables and performance


Thanks for your answers! I am very happy with SQLite as it is I was just
wondering if I could improve it for this case.

I am using tables with this configuration for performance reasons. I
have to support an indeterminate number of columns (user data) and a
"normal" design is not as fast as this solution. I can't remember the
results of the tests right now but the differences in loading data into
the database and reading it to memory were very large.

Thanks,
Jose

On 9/13/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> jose simas wrote:
> > My application uses SQLite as its file format and up to two of the
> > tables can have several thousand columns (up to 20 or 30 thousand at

> > times).
> >
> > When I open a connection there's a noticeable pause (around one
> > second on a file with a table of 7,000 columns, for example). There
> > is also a noticeable delay in sorting them by the primary key.
> >
> > Is there anything I can do to favour this kind of tables?
> >
> Jose,
>
> What can you possibly be doing with tables that have that many
> columns? Are you sure you don't mean 20K-30K rows? In SQL a row
> corresponds to a record, and a column corresponds to a field in a
> record.
>
> If you really mean columns, then your best approach is probably to
> redesign your tables to move much of the data into other related
> tables. Can you give us some idea of your table schema and how it is
> used?
>
> There will be little or no benefit to compiling sqlite yourself.
>
> Dennis Cote
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Retrieving data

[Richard Stern]

 
> > I tried:
> > sqlite3_exec(AccDataBase,"SELECT Name,Address FROM Accounts WHERE
> MemberNo =
> > 2;",Callback(cError,10,,),test,);
> 
> You don't need a Callback-Function in any case. Try it
> without

I'm confused.
How does the SELECT command return any data? In what form would it give you
this data back? There doesn't seem to be a pointer to pass by reference and
no out variables.

Rick



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] met "ARM7TDMI raised an exception,data abort" when executing sqlite3Parser() in ARM environment

[Sarah]

Hi, 

When you ported sqlite to your os-less ARM based platform, did you do any other 
changes to the sqlite source code?

I'm facing new problems right now. the record cann't be inserted correctly when 
I execute the following statements. 
..
char * database = ":memory:";
 sqlite3 * db;
  sqlite3_open(database, );
  sqlite3_exec(db, "create table myt(name varchar(30),age smallint)", NULL, 
NULL, NULL);
  sqlite3_exec(db, "insert into myt values('sarah',27)", NULL, NULL, NULL);
  sqlite3_exec(db, "select * from myt", NULL, NULL, NULL);
  sqlite3_close(db);
..

The behavior is that , when executing "insert into myt values('sarah',27)",  
the program goes into the following statements in sqlite3RunParser() and 
returns 
SQLITE_NOMEM. How and why? 
..
abort_parse:
  if( zSql[i]==0 && nErr==0 && pParse->rc==SQLITE_OK ){
if( lastTokenParsed!=TK_SEMI ){
  sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);
  pParse->zTail = [i];
}
sqlite3Parser(pEngine, 0, pParse->sLastToken, pParse);
  }
  sqlite3ParserFree(pEngine, sqlite3FreeX);
  if( sqlite3MallocFailed() ){
pParse->rc = SQLITE_NOMEM;
  }
..

I totally have no idea of the principals of parser and how it works. Could 
someone tell me or give me some links?

finally, a stupid question:
should I add a semicolon at the end of the sql statement in sqlite3_exec(); 
Will that affect the execution of parser?

a lot of thanks!



- Original Message - 
From: "Barrass, Richard" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, September 12, 2006 7:41 PM
Subject: RE: [sqlite] met "ARM7TDMI raised an exception,data abort" when 
executing sqlite3Parser() in ARM environment


Hi

We have sqlite (v3.3.7) running on an os-less ARM based platform - we
use MINGW cross compilers (GCC3.4.4) - from CodeSourcery.

There was an issue earlier on that seems to be an issue with 64 bit
support - we switched off the 64bit support in the compilation of the
sqlite library and this for now has got the data base up and running on
our platform.

Richard



-Original Message-
From: Nuno Lucas [mailto:[EMAIL PROTECTED] 
Sent: 11 September 2006 21:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] met "ARM7TDMI raised an exception,data abort" when
executing sqlite3Parser() in ARM environment


[forgot to reply to the list]

-- Forwarded message --
From: Nuno Lucas <[EMAIL PROTECTED]>
Date: Sep 11, 2006 9:07 PM
Subject: Re: Re: [sqlite] met "ARM7TDMI raised an exception,data abort"
when executing sqlite3Parser() in ARM environment
To: [EMAIL PROTECTED]


On 9/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I don't think NULL callback and error pointer will be the reason. 
> Because I've seen this kind of usage in the list for several times.

As I said earlier, I don't think this is the reason also. Just one more
thing you could check.

> Anyway, I'll try later and report the result to the list.
>
> If it is a misaligned-pointer problem, what can I do?
>
> I don't make any change to the sqlite source code, how should this 
> happen? I can run it correctly on PC(windows XP), why can't in an 
> embedded environment? I'm confused...

You are aware they are completely different architectures, don't you?

You also didn't specify what OS you are running, which could make it
easier to get help from other experienced programmers for your platform
(if you are using CodeWarrior I would guess it's not Windows CE).

A misaligned-pointer can never occur on Windows (desktop editions, not
Windows CE), because Windows only runs on the Intel x86 processor
family, which mask that kind of things from you (although you usually
have a performance hit).

Basically it means you are accessing memory that is not aligned to the
minimum granularity the processor supports (which depends on the
processor and processor mode, but usually is 32 bits - 4 bytes - for
32-bits cpus, 64 bits - 8 bytes - for 64-bits cpus, etc).

I don't know if that is your case, but I have seen it before on Windows
CE (using a StrongArm processor) and because there aren't so many people
using SQLite with those processors, the code path is not so much tested
as the x86 case (which doesn't trigger a cpu exception, only a
performance hit).

It's up to you to confirm this is the case, but there are other things
which can be wrong, like little/big endian problems, compiler bugs
(recent platforms don't have so much testing as older ones), bad
compiler/linker options, stack overflows (because in embedded systems
you usually have a much lower default stack size), etc.

Without more info that's all I can say.


Regards,
~Nuno Lucas


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

_

This message is for the designated recipient only and may contain 

RE: [sqlite] Very wide tables and performance

[Denis Povshedny]

Hi Jose!

It is really hard to believe that you do not have a sparse matrix. I
mean that for every single row: from 2 colums only a several columns
are used and others are nil. This is a point to perform so-called
normalizations for the table. The single exception what I remember is
OLAP databases.

Anyway, I (and probably not only me) am very excited to see definition
for this table ;)

WBR, Denis

-Original Message-
From: jose simas [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 13, 2006 11:05 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Very wide tables and performance


Thanks for your answers! I am very happy with SQLite as it is I was just
wondering if I could improve it for this case.

I am using tables with this configuration for performance reasons. I
have to support an indeterminate number of columns (user data) and a
"normal" design is not as fast as this solution. I can't remember the
results of the tests right now but the differences in loading data into
the database and reading it to memory were very large.

Thanks,
Jose

On 9/13/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> jose simas wrote:
> > My application uses SQLite as its file format and up to two of the 
> > tables can have several thousand columns (up to 20 or 30 thousand at

> > times).
> >
> > When I open a connection there's a noticeable pause (around one 
> > second on a file with a table of 7,000 columns, for example). There 
> > is also a noticeable delay in sorting them by the primary key.
> >
> > Is there anything I can do to favour this kind of tables?
> >
> Jose,
>
> What can you possibly be doing with tables that have that many 
> columns? Are you sure you don't mean 20K-30K rows? In SQL a row 
> corresponds to a record, and a column corresponds to a field in a 
> record.
>
> If you really mean columns, then your best approach is probably to 
> redesign your tables to move much of the data into other related 
> tables. Can you give us some idea of your table schema and how it is 
> used?
>
> There will be little or no benefit to compiling sqlite yourself.
>
> Dennis Cote
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
>
>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] met "ARM7TDMI raised an exception,data abort" when executing sqlite3Parser() in ARM environment

[Sarah]

I've got the reason. That is because some stupid mistake I've made.

During the execution of sqlite3Parser(), realloc() is called. Because here 
SQLite is running in an embedded environment without OS, so I need to 
wrap realloc() of my platform. I just wrapped malloc() and free(), but forgot 
this one.

In my malloc() and free(), I have the mechanism to guarantee the alignment of 
32-bits.

Thank you very much for the help!


- Original Message - 
From: "Barrass, Richard" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, September 12, 2006 7:41 PM
Subject: RE: [sqlite] met "ARM7TDMI raised an exception,data abort" when 
executing sqlite3Parser() in ARM environment


Hi

We have sqlite (v3.3.7) running on an os-less ARM based platform - we
use MINGW cross compilers (GCC3.4.4) - from CodeSourcery.

There was an issue earlier on that seems to be an issue with 64 bit
support - we switched off the 64bit support in the compilation of the
sqlite library and this for now has got the data base up and running on
our platform.

Richard



-Original Message-
From: Nuno Lucas [mailto:[EMAIL PROTECTED] 
Sent: 11 September 2006 21:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] met "ARM7TDMI raised an exception,data abort" when
executing sqlite3Parser() in ARM environment


[forgot to reply to the list]

-- Forwarded message --
From: Nuno Lucas <[EMAIL PROTECTED]>
Date: Sep 11, 2006 9:07 PM
Subject: Re: Re: [sqlite] met "ARM7TDMI raised an exception,data abort"
when executing sqlite3Parser() in ARM environment
To: [EMAIL PROTECTED]


On 9/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I don't think NULL callback and error pointer will be the reason. 
> Because I've seen this kind of usage in the list for several times.

As I said earlier, I don't think this is the reason also. Just one more
thing you could check.

> Anyway, I'll try later and report the result to the list.
>
> If it is a misaligned-pointer problem, what can I do?
>
> I don't make any change to the sqlite source code, how should this 
> happen? I can run it correctly on PC(windows XP), why can't in an 
> embedded environment? I'm confused...

You are aware they are completely different architectures, don't you?

You also didn't specify what OS you are running, which could make it
easier to get help from other experienced programmers for your platform
(if you are using CodeWarrior I would guess it's not Windows CE).

A misaligned-pointer can never occur on Windows (desktop editions, not
Windows CE), because Windows only runs on the Intel x86 processor
family, which mask that kind of things from you (although you usually
have a performance hit).

Basically it means you are accessing memory that is not aligned to the
minimum granularity the processor supports (which depends on the
processor and processor mode, but usually is 32 bits - 4 bytes - for
32-bits cpus, 64 bits - 8 bytes - for 64-bits cpus, etc).

I don't know if that is your case, but I have seen it before on Windows
CE (using a StrongArm processor) and because there aren't so many people
using SQLite with those processors, the code path is not so much tested
as the x86 case (which doesn't trigger a cpu exception, only a
performance hit).

It's up to you to confirm this is the case, but there are other things
which can be wrong, like little/big endian problems, compiler bugs
(recent platforms don't have so much testing as older ones), bad
compiler/linker options, stack overflows (because in embedded systems
you usually have a much lower default stack size), etc.

Without more info that's all I can say.


Regards,
~Nuno Lucas


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

_

This message is for the designated recipient only and may contain privileged, 
proprietary, or otherwise private information. If you have received it in 
error, please notify the sender immediately and delete the original. Any other 
use of the email by you is prohibited.

Dansk - Deutsch - Espanol - Francais - Italiano - Japanese - Nederlands - Norsk 
- Portuguese - Svenska: www.cardinalhealth.com/legal/email

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Perfornace using sqlite3_create_function() PI

[Ritesh Kapoor]

Hi,

This is my observation for performance when using
sqlite3_create_function() PI.

Database -
1. Contains 7 tables.
2. Only one table is very large i.e. it contains 100k rows or more.
3. Rest of the tables contain 10 to 200 rows.
4. Each table contains between 4 to 8 columns.

When running a query to extract all the information in the database and
populate a simple C++ structure for each row it took around 30 minutes. 
The query involved joining all the 7 tables.

Here is an example analogous to my database -
1. There is a table EmployeeTable that contains the following fields
  EmpID - INT (PRIMARY KEY)
  Name  - VARCHAR(1024)
  Address   - VARCHAR(1024)
  DepartmentID  - INT
  DesignationID - INT

Lets assume that there are 100k employees, and each employee belongs to
a single Department and has a single Designation ID.

2. There is a DepartmentTable that contains the following fields
  DepartmentID - INT (PRIMARY KEY)
  Name - VARCHAR(1024)
  DeptHead - VARCHAR(1024)
  Location - VARCHAR(1024)

Assume that there are 10 departments only, with each department
containing 10k employees.

3. There is a DesignationTable that contains the following fields
  DesignationID - INT (PRIMARY KEY)
  Designation   - VARCHAR(1024)
  MinSalary - INT
  MaxSalary - INT
  Description   - VARCHAR(1024)

Assume that there are 10 different types of designations and there are
10k employees at each designation.

4. Lastly there is a EmpDependentsTable which contains each employees
dependents and has these fields
  EmployeeID- INT
  DependentName - VARCHAR(1024)

Assuming that some employees will have zero dependents and that some
will have dependents ranging from 1 to N, lets say this table has around
40k rows.

Lets have a simple C++ structure like this to hold an employee
information

struct EmployeeInfo
{
  int ID;
  char * name;
  char * address;
  int departmentId;
  int designationId;
  char * departmentName;
  char * departmentHead;
  char * departmentLocation;
  char * designation;
  int minSalary;
  int maxSalary;
  char * description;
  list dependents;
};

Suppose you run a query to extract information and populate the above
structure for each employee the query would look like this -

---
select BaseTable.EmpID, BaseTable.FullName, BaseTable.Address,
BaseTable.DepartmentID, BaseTable.DesignationID, BaseTable.Name,
BaseTable.Location, BaseTable.DeptHead, BaseTable.Designation,
BaseTable.MaxSalary, BaseTable.MinSalary, BaseTable.Description, 

createDependentList(EmployeeTable.EmpID, EmpDepnedentsTable.EmpID,
EmpDependentsTable.DependentName) 

from EmployeeTable, DepartmentTable, DesignationTable,
EmpDependentsTable

where EmployeeTable.DepartmentID = DepartmentTable.DepartmentID
and EmployeeTable.DesignationID = DesignationTable.DesignationId
and EmployeeTable.EmpID = EmployeeDependentsTable.EmpID

as BaseTable;
---

createDependentList - is defined using the sqlite3_create_function() PI.

This query might not be semantically correct but its just an example to
give you an idea of what i'm pointing to.

This query would take really long to execute - 30 mins.


Since SQLite doesn't support "Linking Table Columns" we could simulate
it -
1. by keeping an ID field in the EmpDependentsTable
2. making sure that for each employee when her information is entered in
the database her dependents are entered consecutively

Suppose for employee "Martina Hingis" we enter her dependents - "A",
"B", and "C" one after the other in the EmpDependentsTable, the table
would appear like this -

EmployeeID  DependentID  DependentName
2   1A
2   2B
2   3C
...

Now in the EmployeeTable we enter Martina's information as - after
adding another column DependentIDs of VARCHAR type in the EmployeeTable

EmpID  Name Address DepartmentID DesignationID DependentIDs
2  Martina  77   881,3
...

Now the above query can be re-written as -
---
select BaseTable.EmpID, BaseTable.FullName, BaseTable.Address,
BaseTable.DepartmentID, BaseTable.DesignationID, BaseTable.Name,
BaseTable.Location, BaseTable.DeptHead, BaseTable.Designation,
BaseTable.MaxSalary, BaseTable.MinSalary, BaseTable.Description,

BaseTable.DependentIDs 

from EmployeeTable, DepartmentTable, DesignationTable,
EmpDependentsTable

where EmployeeTable.DepartmentID = DepartmentTable.DepartmentID
and EmployeeTable.DesignationID = DesignationTable.DesignationId
and EmployeeTable.EmpID = EmployeeDependentsTable.EmpID

as BaseTable;
---

When the callback function for the above query is invoked and as soon as
the BaseTable.DependentIDs column is passed to it we can run another
query to to extract the