[sqlite] Using sqlite3_backup* while the destination connection has "busy" read-only statements.

[Shaun Seckman (Firaxis)]

Greetings all,
I'm running into a situation in where our application is crashing during a call 
to sqlite_backup_finish inside of btreeParseCellPtr because some of the 
structure is corrupted.

Both the source and destination database are using the same page sizes and I'm 
running everything from within a single-thread.

I'd like to get clarification on one possible issue in order to determine 
whether this is the culprit or at least rule it out.  This destination 
connection (which is an in-memory database) has several prepared statements, 
some of which are marked as busy (from sqlite3_stmt_busy) due to having step 
called but not reset.  All of these statements are read-only (simple select 
statements).

My question is, is it safe to have these busy statements on the destination 
connection while the backup happens (again, this is all single threaded so 
nothing happens DURING the backup process)?

If not,  then should sqlite3_backup_init check for this (at least in debug) and 
return NULL if any statements are busy?

-Shaun


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Change busy error message

[Shaun Seckman (Firaxis)]

I got it from http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

"Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5). SQLITE_BUSY 
means that another database connection (probably in another process) is using 
the database in a way that prevents you from using it. SQLITE_LOCKED means the 
source of contention is internal and comes from the same database connection 
that received the SQLITE_LOCKED error."

Still the error codes and their equivalent messages are using confusing word 
choices

It would make sense to either have:
SQLITE_BUSY "database is busy."
SQLITE_LOCKED "database table is locked."

OR

SQLITE_LOCKED "database is locked."
SQLITE_TABLE_LOCKED "database table is locked."


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Wednesday, May 23, 2012 1:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Feature Request: Change busy error message

> SQLITE_LOCKED implies that the contention is on the same connection

Where did you get this from? Nothing can prevent execution of several 
statements on the same connection.

AFAIK, SQLITE_LOCKED implies that contention is from another connection using 
the same shared database cache. And it can be handled either via a busy handler 
just like SQLITE_BUSY or via sqlite3_unlock_notify().


Pavel


On Wed, May 23, 2012 at 12:39 PM, Shaun Seckman (Firaxis) 
<shaun.seck...@firaxis.com> wrote:
> The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also 
> very different.  SQLITE_LOCKED implies that the contention is on the 
> same connection whereas SQLITE_BUSY implies that the contention is 
> from another connection and can be handled via a busy handler.
>
>
>
> The error message reported from sqlite3_errmsg is very deceiving 
> though...
>
>
>
> SQLITE_BUSY -> "database is locked."
>
> SQLITE_LOCKED -> "database table is locked."
>
>
>
> For days now, I kept receiving a "database is locked." error in my 
> logs thinking the contention was due to a single connection only to 
> just now realize the error code was indeed SQLITE_BUSY.
>
>
>
> Can we instead change the error message to read:
>
> SQLITE_BUSY -> "database is busy."
>
> SQLITE_LOCKED -> "database table is locked"
>
>
>
> -Shaun
>
>
>
>
>
> Shaun Seckman
>
> Firaxis Games
> Programmer
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature Request: Change busy error message

[Shaun Seckman (Firaxis)]

The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also
very different.  SQLITE_LOCKED implies that the contention is on the
same connection whereas SQLITE_BUSY implies that the contention is from
another connection and can be handled via a busy handler.

 

The error message reported from sqlite3_errmsg is very deceiving
though...

 

SQLITE_BUSY -> "database is locked."

SQLITE_LOCKED -> "database table is locked."

 

For days now, I kept receiving a "database is locked." error in my logs
thinking the contention was due to a single connection only to just now
realize the error code was indeed SQLITE_BUSY.

 

Can we instead change the error message to read:

SQLITE_BUSY -> "database is busy."

SQLITE_LOCKED -> "database table is locked"

 

-Shaun

 

 

Shaun Seckman

Firaxis Games
Programmer

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can an online backup happen during a nested transaction?

[Shaun Seckman (Firaxis)]

Hello,

I'm attempting to save a backup of my in-memory database
using the online backup routines.  I noticed that I cannot seem to make
backups of the database when there is a pending save point.  The error
code is SQLITE_BUSY.  Is this the expected behavior?  Are there any ways
to make this work without committing the transaction?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite parsing of a .sql file

[Shaun Seckman (Firaxis)]

Checking for NULL on the statement fixed it :)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Friday, April 09, 2010 5:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite parsing of a .sql file

On 04/09/2010 01:25 PM, Shaun Seckman (Firaxis) wrote:
> I'm running into a nasty problem towards the end of the file however.
> At the very end there are 3 characters left that are just whitespace
> characters.  When I pass this string into sqlite3_prepare_v2() I get a
> return value of 0 as if it's a valid statement.

http://sqlite.org/c3ref/prepare.html

   If the input text contains no SQL (if the input is an empty string
   or a comment) then *ppStmt is set to NULL

Similarly your file could contain this.  I suggest using it for testing.

    select 3 


 > However, when I attempt
> to call sqlite3_sql() I will get a crash in sqlite.

Don't do that then.  prepare already told you there was no SQL.  Note
that 
pzTail is still set correctly.

> If I call
> sqlite3_step() I receive the error code SQLITE_MISUSE.

Don't do that either :-)

> What would be very useful is if whitespace or comments were the only
> data passed into sqlite3_prepvare_v2() for it to return an error code
> that it was an empty statement or something similar.

It does, except an empty statement is not an error.  You get NULL
returned 
as documented.

> Otherwise there's
> no other way that I can tell how you could prevent the misuse code or
> crash to occur.

You are way overthinking all this.  Additionally you do not need to call

sqlite3_sql since zSql was the start of the statement and pzTail points
to 
the end.  This will be the case even when there are empty statements.

> I know there is sqlite_complete() but I would have expected prepare to
> perform something similar.  Also sqlite_complete assumes a NULL
> terminated string which is different from most other API functions
that
> allow for a string/length combination.

complete is unrelated to your problem unless you are trying to a read
the 
contents of a file in the smallest chunks possible.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite parsing of a .sql file

[Shaun Seckman (Firaxis)]

So, using the tail pointer from sqlite3_prepare_v2() I'm now running
through a loop to process all statements in a file.  I'm presently using
pointer math in order to determine the length of the tail command since
no length value is returned as part of the function.

I'm running into a nasty problem towards the end of the file however.
At the very end there are 3 characters left that are just whitespace
characters.  When I pass this string into sqlite3_prepare_v2() I get a
return value of 0 as if it's a valid statement.  However, when I attempt
to call sqlite3_sql() I will get a crash in sqlite.  If I call
sqlite3_step() I receive the error code SQLITE_MISUSE.

What would be very useful is if whitespace or comments were the only
data passed into sqlite3_prepvare_v2() for it to return an error code
that it was an empty statement or something similar.  Otherwise there's
no other way that I can tell how you could prevent the misuse code or
crash to occur.

I know there is sqlite_complete() but I would have expected prepare to
perform something similar.  Also sqlite_complete assumes a NULL
terminated string which is different from most other API functions that
allow for a string/length combination.

Perhaps I missed something else in the documentation?

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman
(Firaxis)
Sent: Friday, April 09, 2010 3:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite parsing of a .sql file

Ah, I missed that in the documentation.  This is perfect!  Precisely
what I needed with much less work than copying the shell.c code.

Thanks a bunch!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Friday, April 09, 2010 3:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite parsing of a .sql file


On Apr 10, 2010, at 1:51 AM, Adam DeVita wrote:

> Is this a 1 off import?  If so, perhaps the command line tool  
> can .read it.
>
> On Fri, Apr 9, 2010 at 2:13 PM, Shaun Seckman (Firaxis) <
> shaun.seck...@firaxis.com> wrote:
>
>> Greetings,
>>
>> I've got a .SQL file that contains multiple SQL insert statements for
>> various tables as well as comments embedded throughout.  When I
>> attempted to pass the file into sqlite3_exec, I found that only the
>> first SQL command was getting executed.

That should not be the case. sqlite3_exec() should execute the
whole script.

>> What is the best way to ensure
>> that all commands are executed?   Parsing the file line-by-line is
>> inaccurate as multiple statements may be on the same line and looking
>> for the next ';' character has parsing problems as well.
>>
>>
>>
>> The documents state that sqlite3_complete() only returns a 1 if the
>> statement is complete.  Were this method to return the index into the
>> character array to denote WHERE the SQL statement is complete, I  
>> could
>> use that to parse multiple statements.
>>
>>
>>
>> Does anyone have any suggestions?

See also the 5th parameter to sqlite3_prepare_v2().

This API allows you to compile the first statement in an SQL
script and returns a pointer to the start of the next statement
in the script at the same time.

   http://www.sqlite.org/c3ref/prepare.html



Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite parsing of a .sql file

[Shaun Seckman (Firaxis)]

Ah, I missed that in the documentation.  This is perfect!  Precisely
what I needed with much less work than copying the shell.c code.

Thanks a bunch!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Friday, April 09, 2010 3:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite parsing of a .sql file


On Apr 10, 2010, at 1:51 AM, Adam DeVita wrote:

> Is this a 1 off import?  If so, perhaps the command line tool  
> can .read it.
>
> On Fri, Apr 9, 2010 at 2:13 PM, Shaun Seckman (Firaxis) <
> shaun.seck...@firaxis.com> wrote:
>
>> Greetings,
>>
>> I've got a .SQL file that contains multiple SQL insert statements for
>> various tables as well as comments embedded throughout.  When I
>> attempted to pass the file into sqlite3_exec, I found that only the
>> first SQL command was getting executed.

That should not be the case. sqlite3_exec() should execute the
whole script.

>> What is the best way to ensure
>> that all commands are executed?   Parsing the file line-by-line is
>> inaccurate as multiple statements may be on the same line and looking
>> for the next ';' character has parsing problems as well.
>>
>>
>>
>> The documents state that sqlite3_complete() only returns a 1 if the
>> statement is complete.  Were this method to return the index into the
>> character array to denote WHERE the SQL statement is complete, I  
>> could
>> use that to parse multiple statements.
>>
>>
>>
>> Does anyone have any suggestions?

See also the 5th parameter to sqlite3_prepare_v2().

This API allows you to compile the first statement in an SQL
script and returns a pointer to the start of the next statement
in the script at the same time.

   http://www.sqlite.org/c3ref/prepare.html



Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite parsing of a .sql file

[Shaun Seckman (Firaxis)]

Ah, I believe it is something that the command line tool can .read.
Since I'm using just the static library, I'll have to copy the
functionality from shell.c into my own code.

Thanks!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
Sent: Friday, April 09, 2010 2:52 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite parsing of a .sql file

Is this a 1 off import?  If so, perhaps the command line tool can .read
it.

On Fri, Apr 9, 2010 at 2:13 PM, Shaun Seckman (Firaxis) <
shaun.seck...@firaxis.com> wrote:

> Greetings,
>
> I've got a .SQL file that contains multiple SQL insert statements for
> various tables as well as comments embedded throughout.  When I
> attempted to pass the file into sqlite3_exec, I found that only the
> first SQL command was getting executed.  What is the best way to
ensure
> that all commands are executed?   Parsing the file line-by-line is
> inaccurate as multiple statements may be on the same line and looking
> for the next ';' character has parsing problems as well.
>
>
>
> The documents state that sqlite3_complete() only returns a 1 if the
> statement is complete.  Were this method to return the index into the
> character array to denote WHERE the SQL statement is complete, I could
> use that to parse multiple statements.
>
>
>
> Does anyone have any suggestions?
>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite parsing of a .sql file

[Shaun Seckman (Firaxis)]

Greetings,

I've got a .SQL file that contains multiple SQL insert statements for
various tables as well as comments embedded throughout.  When I
attempted to pass the file into sqlite3_exec, I found that only the
first SQL command was getting executed.  What is the best way to ensure
that all commands are executed?   Parsing the file line-by-line is
inaccurate as multiple statements may be on the same line and looking
for the next ';' character has parsing problems as well.

 

The documents state that sqlite3_complete() only returns a 1 if the
statement is complete.  Were this method to return the index into the
character array to denote WHERE the SQL statement is complete, I could
use that to parse multiple statements.

 

Does anyone have any suggestions?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature Request: More descriptive error message to replace "Constraint Failed."

[Shaun Seckman (Firaxis)]

Hello everyone,

I've recently stumbled across a plethora of "Constraint
Failed" errors in my code due to a schema change that modified various
columns of tables to be unique.  It would be extremely useful if this
error message could contain more information such as which constraint
failed and even perhaps the data which caused the constraint to fail.
Even just knowing the column names would be a huge help to debugging
such errors.

 

What do you guys think?

 

-Shaun

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Verbosity of inner join queries

[Shaun Seckman (Firaxis)]

Hey guys,

This may be more of a question about the SQL language
itself rather than SQLite.  I'm finding inner join statements to be more
verbose than they should be and I'm curious if there is any sort of
syntax sugar that can be done to shorten them. 

 

An Example:

CREATE TABLE tblA( col1 integer, someValue integer);

CREATE TABLE tblB( col1 integer, fkcol integer, foreign
key (col2) references tblA(col1));

 

My current query looks something like this:

SELECT tblA.someValue from tblB inner join tblA on
tblB.fkcol = tblA.col1 where tblB.col1 = ?

 

Couldn't it just be:

SELECT fkcol.someValue from tblB where col1  = ?

 

The reasoning here is that SQL knows the schema of tblB
and knows exactly where the FK is pointing to.  If  fkcol was pointing
to an invalid row or was null, fkcol.someValue could be null.  It seems
like this would just be syntax sugar and both should  generate the same
query plan.  The other thing I like about this syntax is that it would
have to throw an error if fkcol wasn't a true FK.  In this example, the
inner join line was fairly short, but it gets pretty intense when you
have multiple inner joins.

 

Thoughts?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Request: SQLITE_USE_MALLOCA

[Shaun Seckman (Firaxis)]

Hello all,

SQLite currently has a SQLITE_USE_ALLOCA define in which
it will attempt to use alloca instead of malloc when the usage fits.
One of the common dangers with alloca is that if there is not enough
stack space, bad things happen and it's usually very difficult to debug.
Microsoft Visual Studio offers the function _malloca which will allocate
stack space if the amount is within a threshold OR it will malloc if the
amount is beyond the threshold.  I find myself frequently adding  the
following code to each new release of SQLite to support _malloca and was
curious if others would find it useful:

 

Just before "#ifdef SQLITE_USE_ALLOCA"..

#ifdef SQLITE_USE_MALLOCA

# define sqlite3StackAllocRaw(D,N)   _malloca(N)

# define sqlite3StackAllocZero(D,N)  memset(_malloca(N), 0, N)

# define sqlite3StackFree(D,P)   _freea(P);

#else

 

The two caveats I see that would probably prevent this from being
standard would be that _malloca may not exist on all platforms and that
_malloca can potentially bypass the memory management routines put in
place for SQLite (since _malloca will internally call malloc if there is
not enough stackspace).  Still, it serves as a nice safety net to those
who like using alloca but fear the dangers of it :)

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ideal way to check whether a table has a specific column

[Shaun Seckman (Firaxis)]

Hello all,

I just wanted to find out whether or not this is the
most ideal method for determining if a column exists in a table.  My
current technique is to do execute "pragma table_info(tableName)" then
step through the results and perform a string comparison against the
"name" column until I hit a match or I've finished stepping through the
record set. 

 

Is there a better way?

 

Shaun Seckman

Firaxis Games
Programmer

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap for SQLite

[Shaun Seckman (Firaxis)]

The SQLite Consortium membership would be great but so far this
newsgroup has answered every single one of my questions within a 2 hour
window of posting..for free!  (you guys frickin rock.)  So SQLite
development is purely guided by requests from consortium members?  There
aren't any planned features that the developers wish to include just for
maintenance or to add to already amazing feature set and polish of
SQLite?  As for buying the extension, I remember seeing it in the list
of professional support a few months ago but it currently isn't listed
anymore.

The limitations of the prepared byte code you mention are pretty
acceptable in our use case.  As you mentioned, my main reasons for
wanting this sort of feature is to reduce memory consumption and cpu
time from preparing statements and as an added benefit to have a
singular point where all prepared statements are located and could be
tested prior to use (granted, this could be done already via some
architecture and build process tweaks to my project). As for the byte
code not working with future versions of SQLite, we typically generate
the database file on the fly the first time the application is run, then
load it from disk for all subsequent runs.  This cached database is
destroyed whenever a newer version of the exe is built (so new version
of SQLite would just require us to invalidate our cache and regenerate
it).  The schema changes are a bit of a drag but would be rare and in
that case any prepared statement that is invalidated by them could just
be re-prepared.

Another potential feature that could benefit my usage and possibly
others is if prepared statements could use memory allocated from the
stack rather than the heap or if memory allocated for one statement
could be reused for another one (assuming that previous statement has
been completely finalized).  I can understand this being very tricky to
implement since you must clearly define what items have the same
lifetime as the statement and which ones may be passed on later (ie.
strings).

It's not an absolute requirement for me right now though, just a nice to
have.  So far I've really been impressed with the performance of SQLite
and the ability to bind parameters to prepared statements has met many
of my needs.  In fact, the only major problem I've had with SQLite is
that the amount of polish has spoiled me and often makes me disgusted
when using other third party libraries which I won't name. (If only
other libraries had 100% branch coverage..)

-Shaun


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Monday, November 02, 2009 4:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Roadmap for SQLite

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> I'm really interested in finding out what sort of major features or
> tweaks are planned to be in upcoming releases (within the next 6
> months).  

You get what you ask for :-)  Have a look at these two pages:

http://www.sqlite.org/consortium.html
http://www.hwaci.com/sw/sqlite/prosupport.html

> I'm also hoping
> that storing prepared statements inside of a database is a planned
> feature as well since having a bunch of SQL queries embedded in my C++
> leaves me nervous :)

You can buy an extension that does that (as has been pointed out several
times on this list).  However what is wrong with storing the unprepared
SQL
queries?

The prepared queries are byte code for the SQLite virtual machine (aka
VDBE).  The generated byte code changes with each release (that is how
many
features are added and bugs fixed), so you wouldn't want to keep them
over
SQLite version changes.  The normal use case is for limited environments
where you don't want the memory consumption of the SQLite parser.  The
parsing is done ahead of time on the build workstation before putting
the
database containing them onto the embedded device.  Note that you also
can't
change the database schema in any way since that invalidates the
pre-generated byte code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrvS4IACgkQmOOfHg372QTAEgCfRjO4Go5GrLeoB2Jj1as6sWwl
iI8An0Zinzh/Bt8X1GWMr4agWGSnTdGN
=vU0u
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Roadmap for SQLite

[Shaun Seckman (Firaxis)]

Hello,

Is there any sort of development road map for SQLite?
I'm really interested in finding out what sort of major features or
tweaks are planned to be in upcoming releases (within the next 6
months).  The foreign key constraint feature came as a pleasant surprise
but ended up invalidating a good chunk of code written to work around
the lack of checking (lots of trigger generation code).  I'm also hoping
that storing prepared statements inside of a database is a planned
feature as well since having a bunch of SQL queries embedded in my C++
leaves me nervous :)

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table aliases

[Shaun Seckman (Firaxis)]

Happy Friday everyone!

I've got several tables each representing a specific
language which my application uses to access translated strings.  It'd
be extremely useful if I were able to alias a table as "CurrentLanguage"
as opposed to directly referencing the actual name.  This saves me from
having to tweak my lookup statements on the fly to change the table name
being accessed.  Is it possible to perform such an alias?  I know that I
could execute the line "CREATE TABLE CurrentLanguage AS SELECT * from
SomeLanguage" but will that duplicate all the data or just reference the
data?  I'd rather not take the memory hit as these tables are quite
large.  Suggestions?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Diff libs or applications

[Shaun Seckman (Firaxis)]

I've been looking into using triggers to create a replay table.  This seems to 
be pretty restrictive in that it requires a trigger to be created for each 
table and cannot track when tables are dropped or created (documentation says 
that triggers will not be applied to tables like sqlite_master) 

Is there a way around this?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Shaun Seckman (Firaxis)
Sent: Thursday, October 01, 2009 10:13 AM
To: punk...@eidesis.org; General Discussion of SQLite Database
Subject: Re: [sqlite] Database Diff libs or applications

Are there any limitations to this?  Will this track any and all SQL actions 
done to the database or just table data changes?  I'll hunt down the post now :)

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Thursday, October 01, 2009 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database Diff libs or applications

On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis)
<shaun.seck...@firaxis.com> wrote:
> Hello,
>
>                I'm looking to externally track the actions made to a
> database so that I can apply those same actions to another database
> (assuming the other database has a similar schema).   I've searched the
> documentation and there doesn't seem to be an easy way to extract this
> data so my only option seems to be utilizing some sort of library or
> application to diff the two databases and generate a SQL script based on
> the changes.
>
>

Store the "actions made to a database," in other words, the SQL, in a
log and then replay that log against the other database.

You can use TRIGGERs to store the SQL in a replay table. Search the
archives. Dennis Cote has contributed some code for that in one of the
threads.


>
> Has anyone done this before?  Does such a library or application exist?
> I'm sure I could write my own if needed.
>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Diff libs or applications

[Shaun Seckman (Firaxis)]

Are there any limitations to this?  Will this track any and all SQL actions 
done to the database or just table data changes?  I'll hunt down the post now :)

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Thursday, October 01, 2009 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database Diff libs or applications

On Thu, Oct 1, 2009 at 8:44 AM, Shaun Seckman (Firaxis)
<shaun.seck...@firaxis.com> wrote:
> Hello,
>
>                I'm looking to externally track the actions made to a
> database so that I can apply those same actions to another database
> (assuming the other database has a similar schema).   I've searched the
> documentation and there doesn't seem to be an easy way to extract this
> data so my only option seems to be utilizing some sort of library or
> application to diff the two databases and generate a SQL script based on
> the changes.
>
>

Store the "actions made to a database," in other words, the SQL, in a
log and then replay that log against the other database.

You can use TRIGGERs to store the SQL in a replay table. Search the
archives. Dennis Cote has contributed some code for that in one of the
threads.


>
> Has anyone done this before?  Does such a library or application exist?
> I'm sure I could write my own if needed.
>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Diff libs or applications

[Shaun Seckman (Firaxis)]

It's mostly context A but sometimes context B :)
Here's a better description of what will happen..

I start out with database A.  This database is mostly constant.  

User Bob opens database A in a custom editor and performs changes and
tweaks to the database.  Bob then saves out his changes either in the
form of some separate transaction file.

User Joe opens database A in the same editor and performs different
changes to the database and saves them out to a separate file as well.

The third user Adam then loads up database A in another application,
applies the changes from Bob, applies the changes from Joe, and then
performs read-only operations from that point on.

When a user has the database open in the editor to make changes,
Database A can be considered constant and read-only.  I needn't worry
about maintaining a constant connection as the file will only ever be
updated outside the use of that editor.

The final order in which to apply changes needs to be flexible and at
the user's discretion (this will result in a different final output but
that is expected).

I'm assuming I'll need a custom editor in order to load/save incremental
changes as opposed to the final database.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
Sent: Thursday, October 01, 2009 9:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database Diff libs or applications

There has been a lot of discussion of this and several of us are doing
it.

Are you talking about

A)
DB1 which has modify data
and
DB2 which only receives modifications from DB1 only,

or
B)
DB1 and DB2 both get updates independently and need to be synchronized?

or
C)
  something else Not (A or B)

Your context implies what automated or custom solution you would use.

On Thu, Oct 1, 2009 at 9:44 AM, Shaun Seckman (Firaxis) <
shaun.seck...@firaxis.com> wrote:

> Hello,
>
>I'm looking to externally track the actions made to a
> database so that I can apply those same actions to another database
> (assuming the other database has a similar schema).   I've searched
the
> documentation and there doesn't seem to be an easy way to extract this
> data so my only option seems to be utilizing some sort of library or
> application to diff the two databases and generate a SQL script based
on
> the changes.
>
>
>
> Has anyone done this before?  Does such a library or application
exist?
> I'm sure I could write my own if needed.
>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Diff libs or applications

[Shaun Seckman (Firaxis)]

Hello,

I'm looking to externally track the actions made to a
database so that I can apply those same actions to another database
(assuming the other database has a similar schema).   I've searched the
documentation and there doesn't seem to be an easy way to extract this
data so my only option seems to be utilizing some sort of library or
application to diff the two databases and generate a SQL script based on
the changes.  

 

Has anyone done this before?  Does such a library or application exist?
I'm sure I could write my own if needed.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite ?

[Shaun Seckman (Firaxis)]

Not really...
Natural joins require that the column names be equivalent on each table.
In my current database schema, the common practice is to use
 as the column name of the FK. (i.e. "CarType" to
reference the "Type" column of a "Cars" table)

But yea, what I'm looking for is a form of NATURAL JOIN just..more
natural :)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Monday, September 21, 2009 3:25 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Most wanted features of SQLite ?

Shaun Seckman (Firaxis)
<shaun.seck...@firaxis.com> wrote:
> * I really would like some SQL syntax sugar that will
> automatically include rows referenced by foreign keys in a table.  I
> haven't seen any other SQL database do this as all expect you to
> explicitly include the conditions and joins which can become quite the
> hassle.

Does NATURAL JOIN help?

> * My second awesome feature request would be support for storing
> prepared statements into the database file to be used at later times.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite ?

[Shaun Seckman (Firaxis)]

Here are my most wanted features and they don't even require locking or
threading fu ;) 

* I really would like some SQL syntax sugar that will
automatically include rows referenced by foreign keys in a table.  I
haven't seen any other SQL database do this as all expect you to
explicitly include the conditions and joins which can become quite the
hassle.

* My second awesome feature request would be support for storing
prepared statements into the database file to be used at later times.
It's been documented that the creation of statements is a performance
hit so it would be nice if my application doesn't have to recreate these
statements every time it's executed.  This would also cut down on the
amount of hard-coded SQL I have scattered throughout my C++ application
as I could instead use some sort of preprocessing tool to generate the
statements and merely reference those statements by name or some other
identifier in my code.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brad House
Sent: Monday, September 21, 2009 1:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Most wanted features of SQLite ?

>> It could probably benefit a large number of integrations to
>> have finer grained locking even if it could not be implemented for
all
>> integration types.
> 
> It makes the system a great deal slower, since you need to keep  
> checking all the levels of lock you have implemented.  For instance,
> 
> UPDATE props SET colour='black',condition='poor' WHERE  
> description='fake sword'
> 
> needs to check for locks on the file, the record, and three fields.   
> That's five operations before you can even start to modify the data.

> Could easily double the amount of time it takes to perform the  
> update.  And if you implement column locks there are even more.  And  
> implementing fine-grain locks leads to lock-contention: if someone  
> locks a record and you try to lock a field in that record, what should

> happen ?  Now before trying to modify data and having locks interfere,

> you're trying to modify locks and having lock-interaction interfere.

I definitely don't agree here as we're talking about these additional
locks existing _only_ in memory, not on disk.  There'd be no reason to
implement on-disk locking or even notifying the OS of sections of the
file
which are locked since we're only talking about multiple threads in the
same process.  Any other process would hit the OS file lock and be
forced
to wait.  The overhead of in-memory locking going to be extremely
minimal,
and only affect those who specifically enable this fine-grained locking.

That said, I do think the on-disk journal file format might need to
change to accomplish even this, and I think that is probably the
biggest show stopper.

I'm not suggesting that this would be easy to implement either, and yes,
you'd need to figure out if SQLite will block on a lock, or return BUSY,
but by limiting the implementation scope to multithreaded applications,
it at least makes the implementation feasible, and would provide great
benefit
to many users of SQLite.  In our own synthetic benchmark of our
application,
which is extremely write-heavy, we see roughly 15 txns/sec with SQLite,
but
1000 txns/sec with multiple connections to MySQL.  Biggest difference
here
is MySQL allows multiple writers. (That said, I need to actually try to
benchmark MySQL with only 1 connection to be able to normalize those
numbers a bit).  Typically though, those with large transaction volumes
are going to go to some other database besides SQLite for other features
of a server-based engine, like replication.

Not complaining here though, SQLite definitely fits the bill for the
default database of our application.  What it does, it does well!

-Brad

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on converting objects into relational db

[Shaun Seckman (Firaxis)]

You could either store the object as a blob in the database or if you
had your own custom collection of these objects it's possible that you
could create a virtual table that can be used as the glue between SQLite
and this collection.

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
Sent: Thursday, September 10, 2009 12:02 PM
To: kennethinbox-sql...@yahoo.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Question on converting objects into relational db

The entity consists of an entity ID, and an attribute list. The
attribute list could
be thought of as an array of integers or vector of integers, and the
like.

The problem I'm encountering by my affinity towards object oriented is:
I dont want to keep 2 sets of data. One inside sqlite, and one to read
from sqlite and model it as an object.
It would be cool if I could store this object
in the database.

- Original Message -
From: "Ken" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, September 10, 2009 8:45:11 AM GMT -08:00 US/Canada
Pacific
Subject: Re: [sqlite] Question on converting objects into relational db

table might have columns, integer ID and BLOB entity.

But it would be better if you could fully describe the "Entity" internal
types instead of just a var arg...



--- On Thu, 9/10/09, Kavita Raghunathan
 wrote:

> From: Kavita Raghunathan 
> Subject: [sqlite] Question on converting objects into relational db
> To: "sqlite-users" 
> Date: Thursday, September 10, 2009, 10:30 AM
> 
> I have a very high level question, not dealing with bits
> and bytes of sqlite: 
> 
> I currently have an object called entity_list, that has a
> list of all entities on the system. 
> While thinking about it, it sounds like a list of entities
> is like having a database. Would 
> it be possible to transfer the object entity-list into a
> sqlite database ? 
> 
> 
> How would my schema look ? 
> 
> 
> I have enclosed an idea of how my class looks. Any links to
> useful information is 
> appreciated! 
> 
> 
> Kavita 
> 
> 
> --- 
> 
> class entity_list 
> { 
> public: 
> entity_list(); 
> ~entity_list(); 
> entity *get_specific_entity(int entity_id); 
> entity *add_entity(int entity_id, void *attr_list,...); 
> remove_entity(int entity_id); 
> vector m_entity_list; 
> 
> 
> private: 
> }; 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Shaun Seckman (Firaxis)]

I'm sold :)  Do you know if there are any MS Visual Studio or MS
Explorer integrations?  

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, September 09, 2009 2:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite version 3.6.18 scheduled for Monday


On Sep 9, 2009, at 2:04 PM, Shaun Seckman (Firaxis) wrote:

> It's always a pleasure to have a new version of one of my favorite
> tools.  I noticed in the draft that you migrated the project to  
> Fossil.
> I am curious, how was your experience with the migration?  Did it go
> smoothly or were there many kinks to work out.  Also, what was your
> primary motivation for migrating in the first place?
>

The transfer of version history from CVS to Fossil went relatively  
smoothly.  There were, in fact, some anomalies in the CVS revision  
history of SQLite back in the 1.x days and so I had to develop work- 
arounds for those anomalies.  But otherwise everything went smoothly.

SQLite is the largest (in terms of history) project that we are aware  
of that is using Fossil, so this was a big step for Fossil.  Very  
early on, the SQLite repository revealed an inefficiency in the  
implementation of the synchronization wire protocol, which we quickly  
fixed (http://www.fossil-scm.org/fossil/vinfo/7646ee13e) but other  
than that, no problems have yet arisen.  Fossil is working out very  
well.  We were pleasantly surprised to find that the Fossil repository  
was an order of magnitude smaller than the original CVS repository.   
The entire 9.25-year revision history of SQLite can be cloned with  
only about 13MB of network traffic.

There are multiple reasons for changing from CVS to Fossil.

(1) The whole point of writing fossil to begin with is so that we  
could keep tighter configuration control of SQLite.  Some customers  
require this.  Others simply want it.  As an example of the new  
capabilities that Fossil provides us, see the new sqlite3_sourceid() C/ 
C++ interface and the new sqlite_source_id() SQL functions:

 http://www.sqlite.org/draft/c3ref/libversion.html
 http://www.sqlite.org/draft/lang_corefunc.html#sqlite_source_id

(2) Fossil is a distributed VCS which enables us to work off-line.  We  
are also much less concerned about backups now, since every clone of  
the repository is a full backup.  And we can easily make read/write  
mirrors of the repository:

  http://www.sqlite.org/src/timeline  (in Dallas, TX)
  http://www2.sqlite.org/src/timeline  (in Atlanta, GA)

(3) With Fossil, it is much much easier to go back and create branches  
to fix bugs in archaic versions of SQLite, in order to support those  
companies who actually pay us.  (example:
http://www.sqlite.org/src/vinfo/715cecb8c7)

(4) Having the canonical SQLite sources in Fossil makes it easier to  
create and maintain a private branch of SQLite.  See
http://www.sqlite.org/privatebranch.html 
  for further discussion.

(5) In our experience, Fossil works better than CVS over slow and/or  
unreliable networks.  And, Fossil allows users behind restrictive  
firewalls to get direct access to the code - something that was never  
possible with CVS.

(6) "fossil diff" is way faster than "cvs diff" (since there is no  
network traffic with "fossil diff").

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.18 scheduled for Monday

[Shaun Seckman (Firaxis)]

It's always a pleasure to have a new version of one of my favorite
tools.  I noticed in the draft that you migrated the project to Fossil.
I am curious, how was your experience with the migration?  Did it go
smoothly or were there many kinks to work out.  Also, what was your
primary motivation for migrating in the first place?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, September 09, 2009 11:55 AM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLite version 3.6.18 scheduled for Monday

We are on schedule to release SQLite version 3.6.18 on Monday,  
2009-09-14.  For a preview, see

   http://www.sqlite.org/draft/index.html

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Shaun Seckman (Firaxis)]

It seems like the more ideal solution would be to create a third table for 
ownership that contains a person ID and a vehicle ID.  This will result in more 
tables (significantly more if you're trying to migrate object oriented data 
into a relational model) but is that really a bad thing?

I managed to migrate similar data using this tactic and the biggest issue I've 
had with it is in the generation of long SQL statements that contain many inner 
joins (I really wish there was some syntax sugar for automatically joining all 
foreign keys).  This can become tricky if performance is a priority as I found 
myself needing many unique SQL statements.

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Wednesday, September 09, 2009 1:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to get "One to many" in 1 row of a table ?

On Wed, Sep 9, 2009 at 12:32 PM, Simon Slavin
 wrote:
>
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build-
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>
> Well you cannot have one vehicle owned by more than one person.  So
> you do not need your 'bind' table at all.  You just need to add a
> column to the vehicle table which contains the person_ID.


huh! even though my Saturn is a clunker, it is owned by my wife and I,
and until recently, was also owned partially by the credit union via
their auto loan...

..


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Booleans in SQLite

[Shaun Seckman (Firaxis)]

Hello everyone,

I'm just curious how difficult it would be to add
support for booleans in SQLite.  This would most likely involve adding a
new type affinity as well as adding "true" and "false" keywords to the
lexer.  There's much more that could be done but I'm just looking for
rudimentary support.



I understand that ANSI C doesn't have native support for
booleans and that's fine, I would just like to reduce some memory
overhead as well as use those keywords instead of creating integer
fields using 0 and 1.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can sqlite result be sorted by using "prepare-step" API

[Shaun Seckman (Firaxis)]

This is a side question to the topic, but is it possible to generate the
prepared statement opcodes at compile-time and store them in some sort
of data file instead of running through the lexical parser?  It seems
like for embedded tightly controlled systems where the database schema
will rarely change then this is a performance hit that could be
bypassed.  Baking the prepared statement to a file seems like it would
save quite a bit of CPU time as well as frequent small memory
allocations.

-Shaun


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Monday, August 24, 2009 10:28 AM
To: General Discussion of SQLite Database
Subject: [sqlite] can sqlite result be sorted by using "prepare-step"
API


> I am currently involved in porting sqlite on mobile phone

  As an aside, you do realize that most smartphone OSes already have
  SQLite available?  WinCE, iPhone OS, Symbian, PalmOS, Pre, and Android
  either have SQLite libs built-in to the SDK, or have a version of
SQLite
  that compiles without modification.  If you're using one of those
  environments, there is no reason to re-port SQLite unless you need a
  cutting-edge feature that just appeared in a newer version.

> And that's why I am interested to see if the first awailable row
> can return ASAP.

  This gets back to the original presumption that if you want it
  sorted, you want it sorted, and all the wishing and hoping isn't
  going to change the fact that sorting is often more computationally
  complex than returning rows in random order.  The database engine
  will do what it takes to return a sorted result.  If that takes more
  time, the database engine doesn't care.  The database engine will not
  return an incorrect unsorted result if you asked for a sorted result,
  regardless of the API.

  If you have an extremely tight reset timer you have to deal with, you
  can just have the rows returned and sort them yourself.  The overall
  time is not likely to be much faster, but if you don't do the sort in
  one pass, you can keep the phone from resetting.

> And if "prepare-step" can not do better than "execute" or "GetTable",

  As I said before, prepare/step is the only way to get data out of the
  database.  sqlite3_exec() and sqlite3_get_table() call prepare and
step
  internally.  exec and get_table are just wrapper functions.  There is
  nothing special about them.  You could write identical functions
yourself
  with the public prepare/step API.

> then what is meaning to use "prepare-step"? 

  That's a long discussion. 
  
  I'd suggest you start here:   http://sqlite.org/cintro.html

  A quick summary:

  1) The "prepare" process is fairly expensive.  Using bound variables,
 you can prepare a statement once and use it over and over without
 having to re-prepare it.

  2) Prepare/step is required to use bound variables.  Bound variables
 prevent SQL injection attacks and a number of other issues.  In
 general, you should be using bound variables and not doing string
 manipulations on SQL statements.  If it was up to me, functions
 like sqlite3_exec, sqlite3_get_table, and sqlite3_vmprintf
 wouldn't be included in the API unless you compiled SQLite with
 SQLITE_BIG_SECURITY_HOLE or some similar flag.

  3) Prepare/step is generally faster and uses less memory.  All the
 other APIs return results as strings, which requires further
 processing and memory management.

  4) The step API allows you to break out of queries mid-way through
 and allows better processing and flow-control.

  I'd also point out that the prepare/step paradigm is not unique to
  SQLite.  Most database APIs have a similar construction, even if they
  use different terms.
  
> if I create a view with  clause of "order by" to ask sqlite to sort
> on that index. Can I get sorted result by querying the view with
> prepare-step?

  Sure, but it is going to take the same amount of processing as just
  issuing whatever the underlying query is.  Views don't pre-sort or
  cache results, they're more like named sub-SELECTs.
  
> > sqlite3_step() returns rows as they become available. You're reading
> > too deeply into "as they become available", however. The database
> > engine is still required to return the correct result set. If the
> > query needs to be sorted, it needs to be sorted. That may or may not
> > require computing the full result set before returning the first
row.
> > It depends on the query. But either way, the database will do the
> > right thing and return the correct results.

> According to your explanation, it seems the sorting prevents 
> "prepare-step" from returning faster than "execute".

  If you're talking about "time to first row returned", then yes.

  The time it takes for the first row to be available via step when
using
  prepare/step and the time it takes for your first callback using exec
  is going to be the 

Re: [sqlite] Reverse string comparison for searches

[Shaun Seckman (Firaxis)]

This is great!  Exactly what I was looking for.  Do you by any chance
know the function name of the standard collation used for string
comparisons?  I'd like to use that as a foundation for building a new
one.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Friday, August 21, 2009 2:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Reverse string comparison for searches

Shaun Seckman (Firaxis)
<shaun.seck...@firaxis.com> wrote:
>Currently, in my database I'm storing thousands of
> strings that are formatted in such a way where they share similar
> prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR).  Sadly, this format cannot
> be changed so I was wondering if it would be possible and perhaps
> faster to have SQLite perform reverse string comparisons for looking
> up specific strings.

You can write a custom collation that compares the strings the way you 
want, then build an index and perform searches based on that collation. 
See http://sqlite.org/c3ref/create_collation.html

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reverse string comparison for searches

[Shaun Seckman (Firaxis)]

Unfortunately, not all the prefixes are the same or even the same width, they 
just all share very similar prefixes.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Friday, August 21, 2009 2:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reverse string comparison for searches

On Fri, Aug 21, 2009 at 1:33 PM, Shaun Seckman
(Firaxis)<shaun.seck...@firaxis.com> wrote:
> Hello everyone,
>
>                Currently, in my database I'm storing thousands of
> strings that are formatted in such a way where they share similar
> prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR).  Sadly, this format cannot be
> changed so I was wondering if it would be possible and perhaps faster to
> have SQLite perform reverse string comparisons for looking up specific
> strings.  Has anyone done this?  Is it practical or worth doing?  These
> lookups are performed very frequently so any amount of time that can be
> shaved off will be noticed.

If the prefix is just 'TXT_KEY_', you could split the strings into two
columns, or heck, only one column with just the unique part, index
that, and be happy.

>
>
>
> -Shaun
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reverse string comparison for searches

[Shaun Seckman (Firaxis)]

Hello everyone,

Currently, in my database I'm storing thousands of
strings that are formatted in such a way where they share similar
prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR).  Sadly, this format cannot be
changed so I was wondering if it would be possible and perhaps faster to
have SQLite perform reverse string comparisons for looking up specific
strings.  Has anyone done this?  Is it practical or worth doing?  These
lookups are performed very frequently so any amount of time that can be
shaved off will be noticed.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Clarification of string pointer lifetime

[Shaun Seckman (Firaxis)]

Hey everyone,

The documentation for sqlite3_column_text() states that
the string returned will always be NULL and that the pointers are valid
until sqlite3_step(), sqlite3_reset() or sqlite3_finalize() is called.
Is this ONLY when the same prepared statement is used in those methods
or is it when those functions are called with ANY prepared statement
pointer?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI design & managment tool?

[Shaun Seckman (Firaxis)]

I've tried many free tools as well as shareware tools.  Here are my two
favorites and why:

SQLite Manager for Firefox, Thunderbird, etc:
http://code.google.com/p/sqlite-manager/
This one is simply amazing!  It does everything I want, supports
multiple platforms and is completely free.  It doesn't include any nice
new user features like a query builder and what not but it includes
everything an end user would need to view/manage their database.  After
using this I stopped looking.


SQLite Maestro: http://www.sqlmaestro.com/products/sqlite/maestro/
This is a shareware tool that is free for 30 days.  This tool is
very good and does everything you would expect a SQL database tool to
do.  One of my subtle gripes with it however is how it displays certain
data.  I have many many several text-type foreign keys and instead of
displaying the text value, it displays it as "MEMO".  Other than that
though the tool is much better than any others I've tried prior to
Firefox's plugin.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Allen Fowler
Sent: Tuesday, August 04, 2009 10:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] GUI design & managment tool?


Hello,

Can anyone recommend a Free, or reasonably priced Non-Free, GUI tool for
creating and maintaining an SQlite databases that can run on both
Windows and Linux?

(Support for visual relation design would be great, too.)

I found a list at:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

But, I was wondering if anyone has personal experience to share...

Thanks,
:)


  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint checking

[Shaun Seckman (Firaxis)]

Hello,

 

I am well aware that SQLite doesn't enforce FK's and that a way around
this is to use triggers.  This lack of enforcing actually has worked to
my advantage as when populating my database I can insert data out of
order.  However, after all data has been inserted into the database, I'd
like to perform some constraint checking to ensure that any FK column
value correctly matches a row that the FK references.  Is there any
built-in methods or code snippits I can use for this or will I just have
to roll my own?

 

-Shaun

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] inner joins on prepared statements

[Shaun Seckman (Firaxis)]

Hello All,

Say I have the following prepared statement "select
B.foo from A inner join B on A.bar = B.bar where A.foo = ?" used to
populate values from a reference table.  If I intend on executing this
statement several times with different bound values,  is it better for
me to generate a temporary table to remove the inner join or will SQLite
internally keep a temporary table  for this statement?

 

-Shaun

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert multiple entries in a single INSERT statement

[Shaun Seckman (Firaxis)]

Hello,

Looking at the SQL syntax chart it doesn't seem like
this is possible.  In other SQL servers I'm able to use the statement
"insert into foo('col1', col2') values ('1', '1'), ('2', '2'), ('3',
'3');".  Is this possible in SQLite or must I instead insert one at a
time.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance tuning the Lookaside memory allocator

[Shaun Seckman (Firaxis)]

Hello,

I'm in the process of tuning my allocators such that the
fixed buffers will be enough to prevent any overflow from occuring.
I've been using the memory statistics to tweak the page cache, and
scratch buffers accordingly but found that there are no such mechanisms
for tweaking the lookaside memory allocator.  How can I determine what
the largest allocation is on the Lookaside allocator as well as whether
or not an overflow occurred either due to the allocation being to large
or not enough space is available.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

I currently have all my tables with a column called "ID" that is defined
as Integer Primary Key. For legacy code purposes, I need ID to start at
0 and not at 1 however I'd like to have this be defined as part of the
table schema and not as part of the insertion statement.  I tried
defining it as "integer primary key default 0" but that didn't work.
Any other ideas?

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Thursday, July 23, 2009 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Defining a table that starts rowid as 0

On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the
wall:
> 
> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
> 
> >   Using rowid for anything is not a good idea. There's no guarantee

> > that the
> > column values associated with each rowid are static. It's much  
> > better to
> > ignore the rowid and use either a natural primary key or a defined  
> > one.
> 
> Agreed.  And note that if you have a column which is an integer that  
> has doesn't allow duplicates, 

  The column has to very specifically be defined "INTEGER PRIMARY KEY". 
  "INT UNIQUE" won't cover it.

  http://sqlite.org/lang_createtable.html#rowid

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

Hello,

Is it possible in the create a table where the rowid
will start at 0 instead of 1 prior to inserting a row and explicitly
stating that the rowid is 0?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple connections to :memory: database

[Shaun Seckman (Firaxis)]

Is it possible to have multiple connections to a purely in-memory
database and if so how can one go by doing that?  Would it be safe if
the connections were on separate threads?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Type conversion performance

[Shaun Seckman (Firaxis)]

Consider the following SQL Statement:

 

create table foo(val1 integer, val2 integer, val3 integer);

insert into foo values('1', '2', '3');

 

When I call sqlite3_column_int(stmt, columnNum), must it convert the
string to integer each time or will it perform that conversion on
insertion?

Is it faster at retrieving the values as integers if I insert them as
integers instead of strings?

 

-Shaun

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Anyway to set initial value of autoincrement primary key to 0?

[Shaun Seckman (Firaxis)]

Is there any way to set the initial value for an integer primary key
auto-increment column to 0? Currently it always defaults to 1.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL result to struct

[Shaun Seckman (Firaxis)]

I also wanted a similar functionality but in the end decided to use a
prepared statement w/ sqlite3_column_* methods.  My two main reasons
were a) it didn't seem like there were any methods to dumping the data
to a struct like that and b) I was able to do any sort of validation I
desired such as ensuring equivalent types and validating against column
names.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Monday, June 29, 2009 8:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL result to struct

Mark Flipphi wrote:
> I need some help with storing the result from a query into a struct.
>
> I have a query like :
>
> SELECT Echo.Position, Echo.Depth, Echo.dBLevel, Echo.Time,
> Echo.CommonIdx, Common.ChanNr
> FROM Echo JOIN Common ON Echo.CommonIdx = Common.CommonIdx
> ORDER BY Echo.Position ASC
>
> and i need to store the information in a struct like :
>
> typedef struct
> {
>UINT64 nPosition;
>UINT16 nDepth;
>UINT8   ndBLevel;
>UINT64 nTime;
>UINT64 nIdx;
>UINT8   nChannel;
> }
>
> now i use the function :
> rc = sqlite3_exec(m_SqLiteDB, sQuery, callback, 0, );

You may find it easier to use sqlite3_prepare, sqlite3_step, 
sqlite3_column_* et al. For one thing, you'll avoid conversion from 
integers to strings and back.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ROWID of 0

[Shaun Seckman (Firaxis)]

I have a column ("ID") in a table that is the primary key integer so it
should be an alias for ROWID.   Is it safe to have  a ROWID of 0?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two simple questions

[Shaun Seckman (Firaxis)]

Just two quick and simple questions :)

 

In a prepared statement, is there a quick way to
determine what the index of a column with a specific name is or must I
just iterate through all columns and perform a string comparison?

 

How can I go by determining the default value of a
column?

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

Ah, that makes more sense :)  Thanks a bunch for the clarification!

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Friday, June 19, 2009 10:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Literal replacements in prepared statements

Yes, it's impossible to replace table or column names. You can replace
any constant values like this:

select table.column2, column3 + ?
from table
where column1 = ? and column2 + ? > column3
limit ?, ?

Pavel

On Fri, Jun 19, 2009 at 10:06 AM, Shaun Seckman
(Firaxis)<shaun.seck...@firaxis.com> wrote:
> Not sure I fully understand what you mean.  Is it not possible to replace the 
> table name in the prepared statement?  What sort of things can I replace then?
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Friday, June 19, 2009 10:03 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Literal replacements in prepared statements
>
> You're trying identifier replacement, not literal replacement. It's
> not allowed. You have to write table name without binding.
>
> Pavel
>
> On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
> (Firaxis)<shaun.seck...@firaxis.com> wrote:
>> I'm trying to add some literal replacements in my prepared SQL statement
>> but I'm currently getting a SQL syntax error.
>>
>> Here's a snippit of what I'm trying to do:
>>
>>
>>
>> ...
>>
>> sqlite3_stmt* stmt;
>>
>> sqlite3_prepare_v2(db, "Select * from ?", -1, , NULL);   <-- near
>> "?": syntax error
>>
>> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>>
>> ...
>>
>>
>>
>> Any idea what I'm doing wrong?
>>
>>
>>
>> -Shaun
>>
>>
>>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

Not sure I fully understand what you mean.  Is it not possible to replace the 
table name in the prepared statement?  What sort of things can I replace then?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Friday, June 19, 2009 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Literal replacements in prepared statements

You're trying identifier replacement, not literal replacement. It's
not allowed. You have to write table name without binding.

Pavel

On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
(Firaxis)<shaun.seck...@firaxis.com> wrote:
> I'm trying to add some literal replacements in my prepared SQL statement
> but I'm currently getting a SQL syntax error.
>
> Here's a snippit of what I'm trying to do:
>
>
>
> ...
>
> sqlite3_stmt* stmt;
>
> sqlite3_prepare_v2(db, "Select * from ?", -1, , NULL);   <-- near
> "?": syntax error
>
> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>
> ...
>
>
>
> Any idea what I'm doing wrong?
>
>
>
> -Shaun
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

I'm trying to add some literal replacements in my prepared SQL statement
but I'm currently getting a SQL syntax error.

Here's a snippit of what I'm trying to do:

 

... 

sqlite3_stmt* stmt; 

sqlite3_prepare_v2(db, "Select * from ?", -1, , NULL);   <-- near
"?": syntax error

sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);

...

 

Any idea what I'm doing wrong?

 

-Shaun

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Duplicating an sqlite3_stmt

[Shaun Seckman (Firaxis)]

Is there any way to duplicate a sqlite3_stmt instance?  I like being
able to prepare and cache multiple statements but since the results are
also tied into the statement structure, I'd like to be able to duplicate
the statement to be used in another query.   My only reasoning for doing
this is to bypass the byte code compilation of the SQL statement,  I
don't need to duplicate the results or even the bound values.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Questions from a new user

[Shaun Seckman (Firaxis)]

Hello everyone,

I'm in the process of writing some C++ wrappers to
SQLite in order to shoehorn it into some legacy code.  I'm curious if
there is any performance impact to having several prepared statements
active at any given time.  I was thinking about using a prepared
statement per instance of a Query class.  Also, what is the lifetime of
the text received by sqlite3_column_text?

 

-Shaun

 

Shaun Seckman

Firaxis Games
Programmer

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users