RE: [sqlite] Query on database operations.

[Kalyani Tummala]

Hi Nicolas,
Thanks for the reply. My database is fixed in the number of tables and
fields. I mean Schema cannot be altered once it is created. But the
database records might grow even more than 20K in number. I have tried
select query with and without indexes. Selection without indexes is very
costly. Hence I don't think we can omit indexes.

-Original Message-
From: Nicolas Williams [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 30, 2006 8:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query on database operations.

On Thu, Nov 30, 2006 at 08:03:00PM +0530, Kalyani Tummala wrote:
> With indexes on every column(searched), the following is the heap size
> for different database operations on a database with 100 records and 6
> tables with an avg of 10 to 15 fields each.

You can probably get by quite well without indexes on such small
databases.  Dropping the indexes should lead to simpler compiled
queries, and that hopefully to less heap usage (certainly less page
cache usage).

Nico
-- 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
[EMAIL PROTECTED]
**


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

RE: [sqlite] Query on database operations.

[Kalyani Tummala]

Hi John,
Thanks for your help. I have another doubt? If I turn off the sql
compiler, should I have to fix my insert,delete,select,update
operations? My operations are almost fixed in nature. Can you tell me
how exactly I do this?


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 30, 2006 11:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query on database operations.

You might find that a version of Sqlite which omits the compiler might 
fit your application.  You get a smaller footprint, but at the cost of 
having to use pre-compiled SQL, which means that you cannot dynamically 
create SQL.

Kalyani Tummala wrote:
> Hi,
> 
> This mail is a bit lengthy. I thank you in advance for your patience
and
> help:-).
> 
>  
> 
> Here we go..
> 
>  
> 
>  I need to port sqlite to a platform where the RAM size is too small
> about 512kb.  For that I need to tune sqlite to make its footprint,
heap
> and stack memory as small as possible. 
> 
>  
> 
> As said in the documentation on sqlite.org, I could get the least
> possible footprint of 170KB with all the optional features turned off.
> 
>  
> 
> My Analysis of heap and stack usage is as follows.
> 
>  
> 
> Stack memory is consistent about 9-10K
> 
>  
> 
> Heap memory is highly variant on size and number of records. I tried
> modifying the page size(SQLITE_DEFAULT_PAGE_SIZE and
> SQLITE_MAX_PAGE_SIZE ) in pager.h from 512 to 2048 but found no
> reduction in heap size. 
> 
>  
> 
> With indexes on every column(searched), the following is the heap size
> for different database operations on a database with 100 records and 6
> tables with an avg of 10 to 15 fields each.
> 
>  
> 
> OperationMAX Heap
> Average Heap(Bytes)
> 
> insertion
> 
> 85939
> 
> 24166
> 
> deletion
> 
> 397834
> 
> 76541
> 
> Selection of all records
> 
> 246973
> 
> 79075
> 
> Update of all records
> 
> 249808
> 
> 196334
> 
>  
> 
> It seems quite a huge heap consumption. Are these numbers as expected?
> Is there a way that I can reduce these numbers in the range of 40-50kB
> by any means (including accessing pattern). 
> 
>  
> 
> I would like to know the reason for high heap consumption for deletion
> operation. 
> 
>  
> 
> Thanks in advance.
> 
> Kalyani 
> 
>  
> 
>  
> 
> 
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> [EMAIL PROTECTED]
> **
> 
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Re: sqlite_open

[John Stanton]

Dave Dyer wrote:

At 08:04 PM 11/30/2006, John Stanton wrote:


Those are Macintosh issues, not Sqlite, and you need to handle them in your 
application.



Yes indeed.  I'm only suggesting that sqlite would be a better
substrate if it provided a supported way to tell me "I can't open
the database" rather than "there is no table named xx".  



Sqlite has a carefully thought through minimalism.  Feature creep would 
detract from its function as a small footprint, embedded DB.  If you 
want different features there is nothing to stop you adding your own 
library extension, like "daves_sqlite3_open" which does just what you 
want.  You could also add the logic necessary to handle the different 
features of legacy and current Apple OS's.


If you have a nice result you might consider donating it to the Sqlite 
project as your appreciation for getting free software.


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

[sqlite] Re: sqlite_open

[Dave Dyer]

At 08:04 PM 11/30/2006, John Stanton wrote:
>Those are Macintosh issues, not Sqlite, and you need to handle them in your 
>application.

Yes indeed.  I'm only suggesting that sqlite would be a better
substrate if it provided a supported way to tell me "I can't open
the database" rather than "there is no table named xx".  



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

Re: [sqlite] Re: sqlite_open

[John Stanton]

Those are Macintosh issues, not Sqlite, and you need to handle them in 
your application.


Dave Dyer wrote:

t


??? Sqlite is agnostic about file names.  Give it a pathname to suit the 
underlying file system and it is happy.  You might be using some wrapper that 
messes with pathnames, but that is not Sqlite.



No, no wrappers.   The mac these days is pretty confused about
what kind of a system it really is.   There are both carbon
APIs which accept old file names, and darwin APIs which are
unix based.  Carbon can find "foo:bar.sq3" in a subdirectory
named foo, but darwin APIs will look for a file named "foo:bar.sq3"
in the current directory.


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




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

[sqlite] Re: sqlite_open

[Dave Dyer]

>>t
>??? Sqlite is agnostic about file names.  Give it a pathname to suit the 
>underlying file system and it is happy.  You might be using some wrapper that 
>messes with pathnames, but that is not Sqlite.

No, no wrappers.   The mac these days is pretty confused about
what kind of a system it really is.   There are both carbon
APIs which accept old file names, and darwin APIs which are
unix based.  Carbon can find "foo:bar.sq3" in a subdirectory
named foo, but darwin APIs will look for a file named "foo:bar.sq3"
in the current directory.


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

Re: [sqlite] sqlite_open

[drh]

John Stanton <[EMAIL PROTECTED]> wrote:
> Unix has an API call which establishes 
> file existance efficiently.  On Windows I recall that I had to open it.
> 

SQLite contains the sqlite3OsFileExists(const char *zFilename)
function that is cross-platform.

Since this function does not begin with "sqlite3_", you
know that it is not a supported API and that it is subject to 
change without notice.  If you use it and it breaks with
a subsequent release of SQLite - no tears.  But on the other
hand, that particular function has been around and has been
unchanged for time out of mind, so you might be able to get
away with using it.  If nothing else, you could use it as
a template for your own implementation.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Cnichols]

So GROUP BY will separate the the grouping done by sum()? So that also means
I could factor in all discounts not just static ones? Like so? Modifier 0 =
Percent Of and 1 = Static amount?  Also which statement is going to be more
efficient the UPDATE or the REPLACE?

REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - 
SUM((CASE WHEN D.Modifier == 0 THEN (D.Amount / 100.00 * 500) ELSE D.Amount
END))
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
GROUP BY DA.MemberId



Igor Tandetnik wrote:
> 
> Cnichols <[EMAIL PROTECTED]> wrote:
>> Thanks alot the UPDATE works great!  Although the REPLACE INTO still
>> only executes the last row of a multi discount.
> 
> I see. MA to DA is a one-to-many relation, too. You need to move all the
> tables into an inner select: the goal is to use SUM aggregate to produce
> a single row (or no rows) for each MA.MemberId. Like this:
> 
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - SUM(D.Amount)
> FROM DiscountsApplied AS DA
> LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
> LEFT JOIN Members AS M ON DA.MemberId = M.Id
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
> WHERE
> DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
> (SELECT RegistrateDate FROM Config)))
> AND
> DATE(strftime('0001-%m-%d', M.Registered)) <=
> DATE(strftime('0001-%m-%d',
> 'now'))
> AND
> D.Type = 1 AND D.Modifier = 1
> GROUP BY DA.MemberId
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7632401
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Re: sqlite_open

[John Stanton]

Dave Dyer wrote:


I discovered when I started testing my model that it created a file called ' 
.db'. It took me a while to figure out the error. Now, I test for empty
databases and empty fields before trying to use them. :-)



It goes deeper.  I did install a "file exists" check pretty early in
the debugging process; it succeeded because I was using (and expected
sqlite to use) standard Macintosh pathnames with : as the file separator. 


It took another 1.5 days to get back to remembering that sqlite
thinks it is a unix application.


??? Sqlite is agnostic about file names.  Give it a pathname to suit the 
underlying file system and it is happy.  You might be using some wrapper 
that messes with pathnames, but that is not Sqlite.


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

[sqlite] Re: REPLACE INTO Only Executes last SELECT?

[Igor Tandetnik]

Cnichols <[EMAIL PROTECTED]> wrote:

So the sum() function brings the multiple discounts together for a
member in the UPDATE statement?

So I have a question regarding this then the Discount.Modifier stands
for 0 = percent of a value or 1 = static

The update you constructed would work for all static values since sums
groups all the numerics together.

So I assume if I used sum() for percent of values it would collect
the sum of all values calculated like so for percent of modifier?

sum(D.Amount / 100 * [C#VALUE])


How about

sum(case D.modifier when 1 then D.Amount else MA.Balance * D.Amount / 
100 end)


and calculate both kinds in one pass.

Igor Tandetnik 



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

[sqlite] Re: sqlite_open

[Dave Dyer]

>
>
>  I discovered when I started testing my model that it created a file called ' 
> .db'. It took me a while to figure out the error. Now, I test for empty
>databases and empty fields before trying to use them. :-)

It goes deeper.  I did install a "file exists" check pretty early in
the debugging process; it succeeded because I was using (and expected
sqlite to use) standard Macintosh pathnames with : as the file separator. 

It took another 1.5 days to get back to remembering that sqlite
thinks it is a unix application.



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

[sqlite] Re: REPLACE INTO Only Executes last SELECT?

[Igor Tandetnik]

Cnichols <[EMAIL PROTECTED]> wrote:

Thanks alot the UPDATE works great!  Although the REPLACE INTO still
only executes the last row of a multi discount.


I see. MA to DA is a one-to-many relation, too. You need to move all the
tables into an inner select: the goal is to use SUM aggregate to produce
a single row (or no rows) for each MA.MemberId. Like this:

REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - SUM(D.Amount)
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
GROUP BY DA.MemberId

Igor Tandetnik 



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

[sqlite] Re: REPLACE INTO Only Executes last SELECT?

[Igor Tandetnik]

Cnichols <[EMAIL PROTECTED]> wrote:

Thanks alot the UPDATE works great!  Although the REPLACE INTO still
only executes the last row of a multi discount.


I see. MA to DA is a one-to-many relation, too. You need to move all the
tables into an inner select: the goal is to use SUM aggregate to produce
a single row (or no rows) for each MA.MemberId. Like this:

REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - SUM(D.Amount)
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
GROUP BY DA.MemberId

Igor Tandetnik


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

Re: [sqlite] Re: sqlite_open

[Rich Shepard]

On Thu, 30 Nov 2006, Dave Dyer wrote:


In my particular case, sqlite created an empty database.  Since my
application expected database a with an established schema, I was left
wondering why my schema didn't seem to exist, and since this was a first
port to a new platform (Macintosh) there were lots of other likely sources
of the problem.


Dave,

  I discovered when I started testing my model that it created a file called 
' .db'. It took me a while to figure out the error. Now, I test for empty

databases and empty fields before trying to use them. :-)

  Of course, it's all on linux so it may not be portable.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] sqlite_open

[John Stanton]

Once it is explained a bug becomes a feature and at worst an annoyance. :-)

It is a tiny change to Sqlite to make it the way you want it to work. 
In my systems I have not changed Sqlite but instead put a jacket around 
sqlite3_open and tested first for the existence of the file if I don't 
want a new file to be created.  Unix has an API call which establishes 
file existance efficiently.  On Windows I recall that I had to open it.


Dave Dyer wrote:

It would have saved me 2 days of chasing bogons if sqlite_open
had an alternative form which only worked if the database file
exists and is well-formed.

The fact that a mistyped file name becomes a new database is a bug.


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




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

Re: [sqlite] sqlite_open

[Cnichols]

I disagree I myself see this as a feature.  I think the programmer should be
responsible for file checking because if it is not a SQLite database SQLite
will inform you.


Dave Dyer wrote:
> 
> 
> It would have saved me 2 days of chasing bogons if sqlite_open
> had an alternative form which only worked if the database file
> exists and is well-formed.
> 
> The fact that a mistyped file name becomes a new database is a bug.
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite_open-tf2735598.html#a7631881
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] sqlite_open

[Dave Dyer]

It would have saved me 2 days of chasing bogons if sqlite_open
had an alternative form which only worked if the database file
exists and is well-formed.

The fact that a mistyped file name becomes a new database is a bug.


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Cnichols]

So the sum() function brings the multiple discounts together for a member in
the UPDATE statement?

So I have a question regarding this then the Discount.Modifier stands for 0
= percent of a value or 1 = static

The update you constructed would work for all static values since sums
groups all the numerics together.

So I assume if I used sum() for percent of values it would collect the sum
of all values calculated like so for percent of modifier?

sum(D.Amount / 100 * [C#VALUE])



Also would it be logical to have separate Updates (One for Static, One for
Percent of Value)  or would it be more logical to try to achieve this by
using ifnull and nullif on the D.Modifier to get the correct sum function()?




Igor Tandetnik wrote:
> 
> Cnichols <[EMAIL PROTECTED]> wrote:
>> I have a complex SQL statement which attempts to update all Member
>> Accounts balance with a discount if there anniversary falls between
>> last handled date and the current day.
>> 
>> 
>> REPLACE INTO MemberAccounts (MemberId, Balance)
>> 
>> SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
>> FROM DiscountsApplied AS DA
>> LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
>> LEFT JOIN Members AS M ON DA.MemberId = M.Id
>> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
>> WHERE
>> DATE(strftime('0001-%m-%d', M.Registered)) >
>> DATE(strftime('0001-%m-%d', (SELECT RegistrateDate FROM Config)))
>> AND
>> DATE(strftime('0001-%m-%d', M.Registered)) <=
>> DATE(strftime('0001-%m-%d', 'now'))
>> AND
>> D.Type = 1 AND D.Modifier = 1
>> 
>> 
>> The SELECT statement will return multiple rows.  A member may have
>> more than one discount that can be applied.  When this case occurs
>> the last row for that member is the only one that actually executes.
>> The rows before that are not replacing the value of BALANCE only the
>> last row for that member will affect the balance.
> 
> But of course. You want something like this:
> 
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - ifnull(
> (SELECT sum(D.Amount)
>   FROM Discounts AS D, Members AS M
>   WHERE
>   DA.DiscountId = D.Id AND
>   DA.MemberId = M.Id AND
>   DATE(strftime('0001-%m-%d', M.Registered)) >
> DATE(strftime('0001-%m-%d',
>   (SELECT RegistrateDate FROM Config)))
>   AND
>   DATE(strftime('0001-%m-%d', M.Registered)) <=
> DATE(strftime('0001-%m-%d',
>   'now'))
>   AND
>   D.Type = 1 AND D.Modifier = 1
> ), 0)
> FROM DiscountsApplied AS DA
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
> 
> 
> Or perhaps
> 
> UPDATE MemberAccounts SET Balance = Balance - ifnull(
> (SELECT sum(D.Amount)
>   FROM DiscountsApplied AS DA
>   LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
>   LEFT JOIN Members AS M ON DA.MemberId = M.Id
>   WHERE
>   DA.MemberId = MemberAccounts.MemberId AND
>   DATE(strftime('0001-%m-%d', M.Registered)) >
> DATE(strftime('0001-%m-%d',
>   (SELECT RegistrateDate FROM Config)))
>   AND
>   DATE(strftime('0001-%m-%d', M.Registered)) <=
> DATE(strftime('0001-%m-%d',
>   'now'))
>   AND
>   D.Type = 1 AND D.Modifier = 1
> ), 0)
> 
> Igor Tandetnik
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7631805
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Clark Christensen]

DOH!  I stand corrected.  Guess I should RTFM before I speak :-))

Thanks!

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 4:52:25 PM
Subject: Re: [sqlite] REPLACE INTO Only Executes last SELECT?

Clark Christensen <[EMAIL PROTECTED]> wrote:
> I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE" =
> is a conflict action in SQLite.  
> 
> Perhaps "INSERT OR REPLACE INTO Memb=
> erAccounts (MemberId, Balance) SELECT..." will do what you want?
> 

I went to the extra trouble of making REPLACE INTO an
alias for INSERT OR REPLACE INTO for compatibility with
MySQL.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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





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

Re: [sqlite] Size of INDEX in database

[drh]

Dr Gerard Hammond <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> Is there a SELECT call I can issue to the SQLite engine to determine 
> the number of bytes that INDEXs occupy.

No.

But you can download the sqlite3_analyzer utility from
http://www.sqlite.org/download.html and use it to get
detailed measurements and statistics on the sizes of all
of your tables and indices.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[drh]

Clark Christensen <[EMAIL PROTECTED]> wrote:
> I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE" =
> is a conflict action in SQLite.  
> 
> Perhaps "INSERT OR REPLACE INTO Memb=
> erAccounts (MemberId, Balance) SELECT..." will do what you want?
> 

I went to the extra trouble of making REPLACE INTO an
alias for INSERT OR REPLACE INTO for compatibility with
MySQL.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] Size of INDEX in database

[Dr Gerard Hammond]

Hi,

Is there a SELECT call I can issue to the SQLite engine to determine 
the number of bytes that INDEXs occupy.

--

Cheers,

Dr Gerard Hammond

PowerPC Mac the world's most advanced obsolete computer.

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

Re: [sqlite] problem with Triggers

[Dennis Cote]

chetana bhargav wrote:

Hi,

I was trying out to test some trigger stuff, basically if I have 2 connections 
open,  and one of the connection registered a user defined function  to be 
invoked. Now when the other connection inserts into table was trying to see if 
the first functions registered function is called or not.

I am having a trigger in this format,

CREATE TRIGGER trigs_partid_t01
AFTER INSERT
  ON ids
BEGIN 
 SELECT foo_bar_trigs(new.mId);

END;

I have proper implementation for the foo_bar_trigs function registered in the 
first function, and that is open always.

When I prepared a statement to insert in the second connection, while preparing,  I am 
getting an error as, "no such function: foo_bar_trigs".

Is it always necessary that the second function also need to have this function 
registered, as I am not even able to prepare a statement, and its coming out 
with this error.



  

Chetana,

Yes, you will need to register your user defined function with each 
connection that will execute SQL statements that cause the trigger to 
fire. The trigger itself is stored in the database along with the table 
definitions. The user defined functions are registered with the 
connection only, they are not saved in the database, and are not 
available to other connections.


The best way to handle this is to move your sqlite_open and 
sqlite_create_function calls into a subroutine that you use to open all 
your database connections.


   int my_sqlite_open(const char *filename, sqlite3 **ppdb)
   {
   int rc = sqlite3_open(filename, ppdb);
   if (rc == SQLITE_OK) {
   sqlite_create_function(*ppdb, "function_one", ...);
   sqlite_create_function(*ppdb, "function_two", ...);
   ...
   }
   return rc;
   }

This way you are sure you have all the functions registered with each 
connection you use. Now you can safely execute any SQL statement. If it 
causes the trigger to fire, it will have the correct user defined 
function available.


HTH
Dennis Cote


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Cnichols]

Thanks alot the UPDATE works great!  Although the REPLACE INTO still only
executes the last row of a multi discount.


Igor Tandetnik wrote:
> 
> Cnichols <[EMAIL PROTECTED]> wrote:
>> I have a complex SQL statement which attempts to update all Member
>> Accounts balance with a discount if there anniversary falls between
>> last handled date and the current day.
>> 
>> 
>> REPLACE INTO MemberAccounts (MemberId, Balance)
>> 
>> SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
>> FROM DiscountsApplied AS DA
>> LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
>> LEFT JOIN Members AS M ON DA.MemberId = M.Id
>> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
>> WHERE
>> DATE(strftime('0001-%m-%d', M.Registered)) >
>> DATE(strftime('0001-%m-%d', (SELECT RegistrateDate FROM Config)))
>> AND
>> DATE(strftime('0001-%m-%d', M.Registered)) <=
>> DATE(strftime('0001-%m-%d', 'now'))
>> AND
>> D.Type = 1 AND D.Modifier = 1
>> 
>> 
>> The SELECT statement will return multiple rows.  A member may have
>> more than one discount that can be applied.  When this case occurs
>> the last row for that member is the only one that actually executes.
>> The rows before that are not replacing the value of BALANCE only the
>> last row for that member will affect the balance.
> 
> But of course. You want something like this:
> 
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - ifnull(
> (SELECT sum(D.Amount)
>   FROM Discounts AS D, Members AS M
>   WHERE
>   DA.DiscountId = D.Id AND
>   DA.MemberId = M.Id AND
>   DATE(strftime('0001-%m-%d', M.Registered)) >
> DATE(strftime('0001-%m-%d',
>   (SELECT RegistrateDate FROM Config)))
>   AND
>   DATE(strftime('0001-%m-%d', M.Registered)) <=
> DATE(strftime('0001-%m-%d',
>   'now'))
>   AND
>   D.Type = 1 AND D.Modifier = 1
> ), 0)
> FROM DiscountsApplied AS DA
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
> 
> 
> Or perhaps
> 
> UPDATE MemberAccounts SET Balance = Balance - ifnull(
> (SELECT sum(D.Amount)
>   FROM DiscountsApplied AS DA
>   LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
>   LEFT JOIN Members AS M ON DA.MemberId = M.Id
>   WHERE
>   DA.MemberId = MemberAccounts.MemberId AND
>   DATE(strftime('0001-%m-%d', M.Registered)) >
> DATE(strftime('0001-%m-%d',
>   (SELECT RegistrateDate FROM Config)))
>   AND
>   DATE(strftime('0001-%m-%d', M.Registered)) <=
> DATE(strftime('0001-%m-%d',
>   'now'))
>   AND
>   D.Type = 1 AND D.Modifier = 1
> ), 0)
> 
> Igor Tandetnik
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630798
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Cnichols]

REPLACE is just an alias for INSERT OR REPLACE.

This SQL Statement works off a Primary key as stated here.
http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSqlUpdateWithFrom


I have an SQL statement that works using this concept. 

This SQL Statement will update all accounts for members with an anniversary
betweens dates by 100.  Insert will fail because of the Primary Key and the
Replace will occur. 

REPLACE INTO MemberAccounts (MemberId, Balance)

SELECT M.Id, (MA.Balance + 100) 
FROM MemberAccounts AS MA
LEFT JOIN Members AS M ON MA.MemberId = M.Id
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <= DATE(strftime('0001-%m-%d',
'now'))


Although for the previous SQL Statement the replace is not occuring for each
row (if multiple discount) only the last row for that member.



Clark Christensen wrote:
> 
> I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE"
> is a conflict action in SQLite.  
> 
> Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT..." will do what you want?
> 
>  -Clark
> 
> - Original Message 
> From: Cnichols <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, November 30, 2006 2:22:19 PM
> Subject: [sqlite] REPLACE INTO Only Executes last SELECT?
> 
> 
> I have a complex SQL statement which attempts to update all Member
> Accounts
> balance with a discount if there anniversary falls between last handled
> date
> and the current day.
> 
> 
> REPLACE INTO MemberAccounts (MemberId, Balance)
> 
> SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
> FROM DiscountsApplied AS DA
> LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
> LEFT JOIN Members AS M ON DA.MemberId = M.Id
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
> WHERE
> DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
> (SELECT RegistrateDate FROM Config)))
> AND
> DATE(strftime('0001-%m-%d', M.Registered)) <= DATE(strftime('0001-%m-%d',
> 'now'))
> AND
> D.Type = 1 AND D.Modifier = 1
> 
> 
> The SELECT statement will return multiple rows.  A member may have more
> than
> one discount that can be applied.  When this case occurs the last row for
> that member is the only one that actually executes.  The rows before that
> are not replacing the value of BALANCE only the last row for that member
> will affect the balance.
> 
> Is there a way I can accumalate the discounts for a member and apply it to
> their balance with an SQL statement?  Or will this have to be done
> programmtically with SELECT and then nested in a transaction UPDATES?
> -- 
> View this message in context:
> http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630506
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] Re: REPLACE INTO Only Executes last SELECT?

[Igor Tandetnik]

Clark Christensen 
wrote: 

I don't think SQLite supports "REPLACE INTO..."


Does too. http://www.sqlite.org/lang_replace.html

Igor Tandetnik

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

[sqlite] Re: REPLACE INTO Only Executes last SELECT?

[Igor Tandetnik]

Cnichols <[EMAIL PROTECTED]> wrote:

I have a complex SQL statement which attempts to update all Member
Accounts balance with a discount if there anniversary falls between
last handled date and the current day.


REPLACE INTO MemberAccounts (MemberId, Balance)

SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d', (SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d', 'now'))
AND
D.Type = 1 AND D.Modifier = 1


The SELECT statement will return multiple rows.  A member may have
more than one discount that can be applied.  When this case occurs
the last row for that member is the only one that actually executes.
The rows before that are not replacing the value of BALANCE only the
last row for that member will affect the balance.


But of course. You want something like this:

REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - ifnull(
(SELECT sum(D.Amount)
 FROM Discounts AS D, Members AS M
 WHERE
 DA.DiscountId = D.Id AND
 DA.MemberId = M.Id AND
 DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d',
 (SELECT RegistrateDate FROM Config)))
 AND
 DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
 'now'))
 AND
 D.Type = 1 AND D.Modifier = 1
), 0)
FROM DiscountsApplied AS DA
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId


Or perhaps

UPDATE MemberAccounts SET Balance = Balance - ifnull(
(SELECT sum(D.Amount)
 FROM DiscountsApplied AS DA
 LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
 LEFT JOIN Members AS M ON DA.MemberId = M.Id
 WHERE
 DA.MemberId = MemberAccounts.MemberId AND
 DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d',
 (SELECT RegistrateDate FROM Config)))
 AND
 DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
 'now'))
 AND
 D.Type = 1 AND D.Modifier = 1
), 0)

Igor Tandetnik

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

Re: [sqlite] REPLACE INTO Only Executes last SELECT?

[Clark Christensen]

I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE" is a 
conflict action in SQLite.  

Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance) SELECT..." 
will do what you want?

 -Clark

- Original Message 
From: Cnichols <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 2:22:19 PM
Subject: [sqlite] REPLACE INTO Only Executes last SELECT?


I have a complex SQL statement which attempts to update all Member Accounts
balance with a discount if there anniversary falls between last handled date
and the current day.


REPLACE INTO MemberAccounts (MemberId, Balance)

SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <= DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1


The SELECT statement will return multiple rows.  A member may have more than
one discount that can be applied.  When this case occurs the last row for
that member is the only one that actually executes.  The rows before that
are not replacing the value of BALANCE only the last row for that member
will affect the balance.

Is there a way I can accumalate the discounts for a member and apply it to
their balance with an SQL statement?  Or will this have to be done
programmtically with SELECT and then nested in a transaction UPDATES?
-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
Sent from the SQLite mailing list archive at Nabble.com.


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





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

[sqlite] REPLACE INTO Only Executes last SELECT?

[Cnichols]

I have a complex SQL statement which attempts to update all Member Accounts
balance with a discount if there anniversary falls between last handled date
and the current day.


REPLACE INTO MemberAccounts (MemberId, Balance)

SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <= DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1


The SELECT statement will return multiple rows.  A member may have more than
one discount that can be applied.  When this case occurs the last row for
that member is the only one that actually executes.  The rows before that
are not replacing the value of BALANCE only the last row for that member
will affect the balance.

Is there a way I can accumalate the discounts for a member and apply it to
their balance with an SQL statement?  Or will this have to be done
programmtically with SELECT and then nested in a transaction UPDATES?
-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] Re: Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Igor Tandetnik]

Thomas Zangl <[EMAIL PROTECTED]> wrote:

Compare with this program:

string userInput;
string sql = "update UserPrefs set innocuousPref=? where
userid=123;"; sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, , 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);


Well - ok ;-) I am quite sure that my version is safe but anyway I
tried sqlite3_prepare. Now I have some problems debugging it.

I tried to use

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*),
void*);
but I get the input of the prepare call - no ? replaced by the actual
values.


No, it's not. That's the whole point of the exercise - no textual 
substitution is performed, so no risk of SQL injection attacks. 
Unfortunately, yes, I don't see any way to capture the values of bound 
parameters in the trace function.



Any ideas how to debug this? I have doubts that my SQL statement is
bound correctly.


What makes you think so? Do you not get expected results?


What I try todo is something like this:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
qlite3_bind_text(prepared_statement, 0,search , search ,
SQLITE_STATIC);


Parameters are numbered from 1, not 0 (not sure why). I also don't see 
how passing "search" twice could compile: one parameter expects char* 
while the other expects int.


Igor Tandetnik


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

Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Trevor Talbot]

On 11/30/06, Thomas Zangl <[EMAIL PROTECTED]> wrote:


char* sql_parameter_search = '%test%'


This is not valid C.  If you want help with your code, paste EXACTLY
what you're using please!

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

Re: [sqlite] problem with Triggers

[chetana bhargav]

But should it cause the prepare's to fail, because of not having the function 
registered.

...
Chetana.


- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 11:43:34 PM
Subject: Re: [sqlite] problem with Triggers


chetana bhargav <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I was trying out to test some trigger stuff, basically 
> if I have 2 connections open,  and one of the connection
> registered a user defined function  to be invoked. Now
> when the other connection inserts into table was trying
> to see if the first functions registered function is
> called or not.

Triggers run in the same connection as the statement that
invoked the trigger runs.  Triggers do not do IPC.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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


 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

Re: [sqlite] problem with Triggers

[drh]

chetana bhargav <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I was trying out to test some trigger stuff, basically 
> if I have 2 connections open,  and one of the connection
> registered a user defined function  to be invoked. Now
> when the other connection inserts into table was trying
> to see if the first functions registered function is
> called or not.

Triggers run in the same connection as the statement that
invoked the trigger runs.  Triggers do not do IPC.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] How safe is sqlite3_prepare compared to sqlite3_exec?

[John Stanton]

As Igor very astutely pointed out, the other very strong benefit of this 
approach is that you always bind to a compiled SQL statement and 
quarantine your application from the possibility of having a malicious 
SQL attack.  You win on all counts.


John Stanton wrote:
Sqlite3_exec is merely a wrapper around sqlite3_prepare.  The name on 
sqlite3_prepare is a little misleading.  It is actually sqlite3_compile 
and compiles your SQL into an internal metacode and applies syntax 
rules.  Incorrect SQL will fail on sqlite3_prepare and you can make your 
program more robust by performing your prepares before you perform any 
processing so that it does not fail indeterminately with a bad SQL 
statement after others have been executed.


You have more program control if you do not use sqlite3_exec.

Thomas Zangl wrote:


Dear List!

I am currently in doubt if the usage of  sqlite3_prepare(...) makes my 
application safer.


It is usually recommended to preapre a SQL statement before using it. 
In my case, I have no need to re-use them so a simple sqlite3_exec 
would be sufficient. On the other side I have been told that preparing 
the sql statement makes the application better protected against 
overflows (buffer, integer) or sql injection.


Is that true? How does sqlite3_prepare internally work? I usually 
sanitize every user input before using it as a parameter for e.g. a 
LIKE search in the database. In detail I do this:


* make sure to remove all non-printable characters from the parameter 
string
* escape all "_", "\" and "%" with "\" (using escapeSQLLikeString - 
homemade escape function)
* print the SQL statement like this: sqlite3_mprintf("author like 
'%%%q%%' ESCAPE '\\' ",

   escapeSQLLikeString(author));

Is that as safe as preparing a statement? Are there any sideeffects? 
Please comment :-)


TIA,
Thomas


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

[sqlite] problem with Triggers

[chetana bhargav]

Hi,

I was trying out to test some trigger stuff, basically if I have 2 connections 
open,  and one of the connection registered a user defined function  to be 
invoked. Now when the other connection inserts into table was trying to see if 
the first functions registered function is called or not.

I am having a trigger in this format,

CREATE TRIGGER trigs_partid_t01
AFTER INSERT
  ON ids
BEGIN 
 SELECT foo_bar_trigs(new.mId);
END;

I have proper implementation for the foo_bar_trigs function registered in the 
first function, and that is open always.

When I prepared a statement to insert in the second connection, while 
preparing,  I am getting an error as, "no such function: foo_bar_trigs".

Is it always necessary that the second function also need to have this function 
registered, as I am not even able to prepare a statement, and its coming out 
with this error.


...
Chetana.


 

Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

Re: [sqlite] Query on database operations.

[John Stanton]

You might find that a version of Sqlite which omits the compiler might 
fit your application.  You get a smaller footprint, but at the cost of 
having to use pre-compiled SQL, which means that you cannot dynamically 
create SQL.


Kalyani Tummala wrote:

Hi,

This mail is a bit lengthy. I thank you in advance for your patience and
help:-).

 


Here we go..

 


 I need to port sqlite to a platform where the RAM size is too small
about 512kb.  For that I need to tune sqlite to make its footprint, heap
and stack memory as small as possible. 

 


As said in the documentation on sqlite.org, I could get the least
possible footprint of 170KB with all the optional features turned off.

 


My Analysis of heap and stack usage is as follows.

 


Stack memory is consistent about 9-10K

 


Heap memory is highly variant on size and number of records. I tried
modifying the page size(SQLITE_DEFAULT_PAGE_SIZE and
SQLITE_MAX_PAGE_SIZE ) in pager.h from 512 to 2048 but found no
reduction in heap size. 

 


With indexes on every column(searched), the following is the heap size
for different database operations on a database with 100 records and 6
tables with an avg of 10 to 15 fields each.

 


OperationMAX Heap
Average Heap(Bytes)

insertion

85939

24166

deletion

397834

76541

Selection of all records

246973

79075

Update of all records

249808

196334

 


It seems quite a huge heap consumption. Are these numbers as expected?
Is there a way that I can reduce these numbers in the range of 40-50kB
by any means (including accessing pattern). 

 


I would like to know the reason for high heap consumption for deletion
operation. 

 


Thanks in advance.

Kalyani 

 

 



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
[EMAIL PROTECTED]
**





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

Re: [sqlite] How safe is sqlite3_prepare compared to sqlite3_exec?

[John Stanton]

Sqlite3_exec is merely a wrapper around sqlite3_prepare.  The name on 
sqlite3_prepare is a little misleading.  It is actually sqlite3_compile 
and compiles your SQL into an internal metacode and applies syntax 
rules.  Incorrect SQL will fail on sqlite3_prepare and you can make your 
program more robust by performing your prepares before you perform any 
processing so that it does not fail indeterminately with a bad SQL 
statement after others have been executed.


You have more program control if you do not use sqlite3_exec.

Thomas Zangl wrote:

Dear List!

I am currently in doubt if the usage of  sqlite3_prepare(...) makes my 
application safer.


It is usually recommended to preapre a SQL statement before using it. In 
my case, I have no need to re-use them so a simple sqlite3_exec would be 
sufficient. On the other side I have been told that preparing the sql 
statement makes the application better protected against overflows 
(buffer, integer) or sql injection.


Is that true? How does sqlite3_prepare internally work? I usually 
sanitize every user input before using it as a parameter for e.g. a LIKE 
search in the database. In detail I do this:


* make sure to remove all non-printable characters from the parameter 
string
* escape all "_", "\" and "%" with "\" (using escapeSQLLikeString - 
homemade escape function)
* print the SQL statement like this: sqlite3_mprintf("author like 
'%%%q%%' ESCAPE '\\' ",

   escapeSQLLikeString(author));

Is that as safe as preparing a statement? Are there any sideeffects? 
Please comment :-)


TIA,
Thomas


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Thomas Zangl]

Vitali Lovich schrieb:

Regarding your code snippet:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
sqlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

First I'm not sure what language you're using - it seems Perl-like.

Anyways, the documentation for 
http://www.sqlite.org/capi3ref.html#sqlite3_bind_text gives the 4th 
param as the number of bytes (not chars) while you're passing the 
original string.  Since I'm assuming it's Perl, it won't generate an 
error on the type mismatch.  You generally want to pass -1 for the 
fourth parameter (from what I understand, -1 is always safe for 
sqlite3_bind_text).  Also, take care in using SQLITE_STATIC and make 
sure that the string you pass remains on the heap (i.e. delete isn't 
called, not sure if this is possible in Perl) or the stack (i.e. local 
variable in scope) when you execute the statement.

Its C :-)

Anyway, I tried your suggestion and free the char* after 
sqlite3_finalize. Does not help.
  
char* sql_parameter_search = '%test%'

rc = sqlite3_bind_text(prepared_statement, 1,
sql_parameter_search, strlen(sql_parameter_search), SQLITE_STATIC);
sql_check_result(rc);
logDebug("Added search = %s", sql_parameter_search);

the result is:

Added search = %test%
my_sqlite_logger-SQLITE said: (0) SELECT * FROM table WHERE name LIKE ?


So - no variable substitution done?

Somebody with a working LIKE example?

TIA,

Thomas

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

Re: [sqlite] How safe is sqlite3_prepare compared to sqlite3_exec?

[drh]

Thomas Zangl <[EMAIL PROTECTED]> wrote:
> 
> I am quite sure that my version is safe

Famous last words.  :-)

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Vitali Lovich]

Regarding your code snippet:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
sqlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

First I'm not sure what language you're using - it seems Perl-like.

Anyways, the documentation for 
http://www.sqlite.org/capi3ref.html#sqlite3_bind_text gives the 4th 
param as the number of bytes (not chars) while you're passing the 
original string.  Since I'm assuming it's Perl, it won't generate an 
error on the type mismatch.  You generally want to pass -1 for the 
fourth parameter (from what I understand, -1 is always safe for 
sqlite3_bind_text).  Also, take care in using SQLITE_STATIC and make 
sure that the string you pass remains on the heap (i.e. delete isn't 
called, not sure if this is possible in Perl) or the stack (i.e. local 
variable in scope) when you execute the statement.


Hope this helps.

PS.  As for the trace, I can't help you as I've never used it (try 
looking up the API reference).  What I do though for my program is save 
to the log the string I pass to prepare.  Then I print the values that 
in the order they are bound.


Thomas Zangl wrote:

Igor Tandetnik schrieb:

Hi!

Compare with this program:

string userInput;
string sql = "update UserPrefs set innocuousPref=? where userid=123;";
sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, , 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

Well - ok ;-) I am quite sure that my version is safe but anyway I 
tried sqlite3_prepare. Now I have some problems debugging it.


I tried to use

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);

but I get the input of the prepare call - no ? replaced by the actual 
values.


Any ideas how to debug this? I have doubts that my SQL statement is 
bound correctly.


What I try todo is something like this:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
qlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

Any ideas?

TIA,
Thomas


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Thomas Zangl]

Igor Tandetnik schrieb:

Hi!

Compare with this program:

string userInput;
string sql = "update UserPrefs set innocuousPref=? where userid=123;";
sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, , 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

Well - ok ;-) I am quite sure that my version is safe but anyway I tried 
sqlite3_prepare. Now I have some problems debugging it.


I tried to use

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);

but I get the input of the prepare call - no ? replaced by the actual values.

Any ideas how to debug this? I have doubts that my SQL statement is bound 
correctly.

What I try todo is something like this:

// SQL Statement is: "SELECT * FROM table WHERE name LIKE ?"
search = '%test%';
qlite3_bind_text(prepared_statement, 0,search , search , SQLITE_STATIC);

Any ideas?

TIA,
Thomas


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

[sqlite] Re: How safe is sqlite3_prepare compared to sqlite3_exec?

[Igor Tandetnik]

Thomas Zangl <[EMAIL PROTECTED]> wrote:

I am currently in doubt if the usage of  sqlite3_prepare(...) makes my
application safer.

It is usually recommended to preapre a SQL statement before using it.
In my case, I have no need to re-use them so a simple sqlite3_exec 
would
be sufficient. On the other side I have been told that preparing the 
sql

statement makes the application better protected against overflows
(buffer, integer) or sql injection.


It's not sqlite3_prepare vs sqlite3_exec per se makes it safer - it 
doesn't. sqlite3_exec is implemented internally in terms of 
sqlite3_prepare anyway. What is safer is using parameters vs just 
concatenating user-supplied values into query string. Consider:


string userInput;
string sql = "update UserPrefs set innocuousPref='" + userInput + "' 
where userid=123;";

sqlite3_exec(db, sql.c_str(), ...);

A malicious user could supply something like this in userInput:

x'; update Users set password=''; --

All the passwords are reset, and the attacker can now log into any 
account.


Compare with this program:

string userInput;
string sql = "update UserPrefs set innocuousPref=? where userid=123;";
sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, , 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);

A little bit more verbose, but safe from injection attack. No matter 
what weird string might be supplied, it will only affect one field of 
one record in UserPrefs table.



Is that true? How does sqlite3_prepare internally work? I usually
sanitize every user input before using it as a parameter


Why go to the trouble, if you can use SQL parameters?


Is that as safe as preparing a statement?


Possibly. But it's so much more work, and there's a risk that a clever 
attacker outsmarts your escaping rules and manages to get a malicious 
string past your defences.


Igor Tandetnik 



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

Re: [sqlite] Query on database operations.

[Nicolas Williams]

On Thu, Nov 30, 2006 at 08:03:00PM +0530, Kalyani Tummala wrote:
> With indexes on every column(searched), the following is the heap size
> for different database operations on a database with 100 records and 6
> tables with an avg of 10 to 15 fields each.

You can probably get by quite well without indexes on such small
databases.  Dropping the indexes should lead to simpler compiled
queries, and that hopefully to less heap usage (certainly less page
cache usage).

Nico
-- 

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

Re: [sqlite] Query on database operations.

[drh]

"Kalyani Tummala" <[EMAIL PROTECTED]> wrote:
> 
> Heap memory is highly variant on size and number of records. I tried
> modifying the page size(SQLITE_DEFAULT_PAGE_SIZE and
> SQLITE_MAX_PAGE_SIZE ) in pager.h from 512 to 2048 but found no
> reduction in heap size. 
> 

Try changing SQLITE_DEFAULT_CACHE_SIZE and
SQLITE_DEFAULT_TEMP_CACHE_SIZE.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] How safe is sqlite3_prepare compared to sqlite3_exec?

[Thomas Zangl]

Dear List!

I am currently in doubt if the usage of  sqlite3_prepare(...) makes my 
application safer.


It is usually recommended to preapre a SQL statement before using it. In 
my case, I have no need to re-use them so a simple sqlite3_exec would be 
sufficient. On the other side I have been told that preparing the sql 
statement makes the application better protected against overflows 
(buffer, integer) or sql injection.


Is that true? How does sqlite3_prepare internally work? I usually 
sanitize every user input before using it as a parameter for e.g. a LIKE 
search in the database. In detail I do this:


* make sure to remove all non-printable characters from the parameter string
* escape all "_", "\" and "%" with "\" (using escapeSQLLikeString - 
homemade escape function)
* print the SQL statement like this: sqlite3_mprintf("author like 
'%%%q%%' ESCAPE '\\' ",

   escapeSQLLikeString(author));

Is that as safe as preparing a statement? Are there any sideeffects? 
Please comment :-)


TIA,
Thomas


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