Re: [sqlite] --prefix

[Dan Kennedy]

On Tue, 2007-05-29 at 13:11 -0700, Smith1, Robert E wrote:
> Hi,
> 
>  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
> cannot install to /usr/local.  I start configure with
> --prefix=/ptmp/usr/localto try to get it to install to a different
> directory. But I get the same
> 
> error:
> 
> /ptmp/bld/> make install
> 
> tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> 
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
> system

Tcl extensions have to be installed in the Tcl library directory,
in your case, under /usr/local/lib/. So the --prefix option doesn't
apply to the Tcl interface, only to the shell, library and include
files (the stuff you need for C programming).

If you don't care about Tcl, add "--disable-tcl" to the configure
line. If you do need it, you'll have to compile Tcl and install
it somewhere you have write permission. Then use environment
variables (PATH etc.) to make sure configure picks up the right
Tcl installation.

Dan.
 


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

Re: [sqlite] --prefix

[Nikola Miljkovic]

[In the message "[sqlite] --prefix" on May 29, 13:11, "Smith1, Robert E" 
writes:]
> Hi,
> 
>  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
> cannot install to /usr/local.  I start configure with
> --prefix=/ptmp/usr/localto try to get it to install to a different
> directory.

This is the correct syntax for what you are trying to do.

> But I get the same
> 
> error:
> 
> /ptmp/bld/> make install
> 
> tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> 
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
> system
> 
> while executing
> 
> "file mkdir $LIBDIR/sqlite3"
> 
> (file "../sqlite-3.3.17/tclinstaller.tcl" line 15)
> 
> *** Error code 1
> 
> make: Fatal error: Command failed for target `tcl_install'

I have not done this myself but I think I know what the problem is.

Your sqlite installation is correctly figuring out the prefix
and is being installed in the right place.

There is an additional piece of software that is being installed, though.

It is tclsqlite extension to tcl.
This is probably a shared library and a tcl loader, which reacts to
the "package require tclsqlite" command in tcl.
The question is, where are those being installed.
Since they are being used by tcl and not by sqlite, they get installed
where tcl can see them.
Since you are probably using system wide tclsh and not the one 
(not) installed in /ptmp/usr/local, the system wide one is trying to
install the extension where it will find it.
That is most likely its own library area.
The hint is:
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
Try running:  which tclsh
and you will probably find that it is
/usr/local/bin/tclsh8.4
or something like that.

> Is my syntax for --prefix correct and is that supposed to correct the
> above error?

So what is the solution here?

You can run:  make -k install
and it will install everyting that it can, excluding tcl extension.
If you want to run tclsqlite from tcl, you can by hand copy the
shared library which would be placed in: $LIBDIR/sqlite3
somewhere you can write, /ptmp/usr/local/lib perhaps.
Before you run "make -k install", run  "make -n install"  and save
the output, since it will tell you what it was planning to
do with tclsqlite files.

When in the future you want to use tclsqlite from tclsh,
instead of: package require tclsqlite 
you would have to try something like
load /ptmp/usr/local/lib/libtclsqlite3.so

I am sure there are other solutions, but this might get you moving.

> Robert

Nikola


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

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 3:56 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You are looking for a fit to one particular restrictive, proprietary
> environment.  Our approach has been to work with the spirit of Sqlite
> and to its strengths and to that end we designed out environment
> accordingly.  Sqlite's typing has become a major asset, not a
> difficulty.

All environments, proprietary or not, are restrictive in one way or another
- including C.  SQLite is flexible and adaptable, and capable of being
wedged into quite a few places -- which is what makes it a great little
engine.

For you that means captializing on SQLite's strengths and using its
typelessness as an asset.  For me, it means bringing SQLite to a mass of
.NET folks (Mono and MS) who would otherwise pass it by.  If that means
kludging a type system together to hide SQLite's typelessness, so be it.
I'd rather blunt the edge than throw the whole knife out.

Robert




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

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 8:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have just given an excellent explanation of why the wrapper
approach
is flawed.  Think about it.



Every approach not deliberately designed around a specific module's exposed
API is fundamentally flawed in that it is limited by the constraints of the
wrapper.  The answer is not "don't use a wrapper" though.

"Think about it"
In my case, I can only speak for .NET and Microsoft environments, so here
goes:

1.  The complexities of interop'ing with SQLite's API are enormous -- an
insurmountable feat for a novice programmer to get right.  As a matter of
fact, most would simply get it wrong, blame it on SQLite, and switch to a
database engine that works better with their chosen design environment.

2.  A mountain of custom code has to be written to interact with SQLite, all
to use an engine who's familiar SQL92 syntax is so tantalizingly close to
the other databases they've used that one can't help to wonder "why do I
have to go to all this trouble just to use the same SQL syntax as my other
database programs?"  A wrapper solves this problem.

3.  A user can learn one API and apply it to multiple databases, instead of
learning one database API and applying it to a single type of database.

4.  The user can write engine-agnostic code and concentrate on the SQL
variations rather than wondering if they get the API code right and called
things in the right sequence.

5.  The wrapper expands the influence and usage of SQLite significantly.

Lets face it ... everyone likes potato chips, but if you had to make them
yourself everytime you got a craving, you'd probably think twice.  On the
other hand, if someone already made them and all you had to do was pick them
off the shelf, you'd be much more inclined to eat them.

So yes, homemade potato chips do taste better than the ones you buy at the
store, provided you get good potatoes, prepare them, peel them with a proper
tool, and watch them in the fryer carefully to avoid burning them.  Of
course you stink up the house and make an enormous mess, but that's the
price you pay for good chips.

So yes, the wrapper approach is flawed, but most folks don't want to stink
up their house trying to roll their own access layer.

Robert

You are looking for a fit to one particular restrictive, proprietary 
environment.  Our approach has been to work with the spirit of Sqlite 
and to its strengths and to that end we designed out environment 
accordingly.  Sqlite's typing has become a major asset, not a difficulty.




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




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

Re: [sqlite] Problem using loadable extensions

[Joe Wilson]

> I'd like to figure out why the example
> with half function which was provided by sqlite team can't be loaded as a
> shared library.

Yeah, it's poorly documented. I'll just put this in the wiki:

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions=1180475067=1

How To Build a Loadable Extension Shared Library on Linux

0. untar latest sqlite3 source code in a new directory

1. cd to the newly untarred sqlite directory

2. Comment out the line in Makefile.in to enable loadable extensions:

 # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1

3. ./configure LIBS=-ldl && make sqlite3

4. export LD_LIBRARY_PATH="`pwd`:$LD_LIBRARY_PATH"

5. gcc -I`pwd` -shared src/test_loadext.c -o half.so

6. ./sqlite3

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .load half.so testloadext_init
sqlite> select half(7);
3.5



   
Yahoo!
 oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

RE: [sqlite] --prefix

[James Dennett]

Sorry, my mistake: I didn't notice the "ptmp" prefix.  Reminder to self:
don't post without a proper night's sleep.

We install SQLite3 to a custom path here (on Solaris, without Tcl
support enabled) with no problem, so I don't know what's happening in
your situation.

-- James

> -Original Message-
> From: Smith1, Robert E [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 1:48 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] --prefix
> 
> Why isn't it trying to install to /ptmp/usr/local since that is the
> value I gave --prefix??
> 
> -Original Message-
> From: James Dennett [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 1:44 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] --prefix
> 
> > -Original Message-
> > From: Smith1, Robert E [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, May 29, 2007 1:12 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] --prefix
> >
> > Hi,
> >
> >  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so
I
> > cannot install to /usr/local.  I start configure with
> > --prefix=/ptmp/usr/localto try to get it to install to a
different
> > directory. But I get the same
> >
> > error:
> >
> > /ptmp/bld/> make install
> >
> > tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> >
> > can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only
file
> > system
> >
> > while executing
> >
> > "file mkdir $LIBDIR/sqlite3"
> >
> > (file "../sqlite-3.3.17/tclinstaller.tcl" line 15)
> >
> > *** Error code 1
> >
> > make: Fatal error: Command failed for target `tcl_install'
> >
> >
> >
> > Is my syntax for --prefix correct and is that supposed to correct
the
> > above error?
> 
> Looks like it's correct syntax, and that it's doing the right thing
and
> attempting to install under /usr/local -- but that the user account
> you're using lacks permission to write there, possibly because
> /usr/local is mounted as read-only (hence the "read-only file system"
> message).
> 
> -- James
> 
> 
> 
> 
> 
>

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

> -
> 
> 
>

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

--
> ---


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

Re: [sqlite] --prefix

[Alexander Smondyrev]

I've run into a similar problem today and as far as I can see it has
something to do with tcl extensions. If I run configure using --disable-tcl,
then my 'make install' puts everything into the location specified using
prefix. But in the case of tcl extensions 'make install' attempts to put
something with other tcl files. Do correct me if I am wrong.

  - Alex

RE: [sqlite] --prefix

[Smith1, Robert E]

Why isn't it trying to install to /ptmp/usr/local since that is the
value I gave --prefix??

-Original Message-
From: James Dennett [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 1:44 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] --prefix

> -Original Message-
> From: Smith1, Robert E [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 1:12 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] --prefix
> 
> Hi,
> 
>  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
> cannot install to /usr/local.  I start configure with
> --prefix=/ptmp/usr/localto try to get it to install to a different
> directory. But I get the same
> 
> error:
> 
> /ptmp/bld/> make install
> 
> tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> 
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
> system
> 
> while executing
> 
> "file mkdir $LIBDIR/sqlite3"
> 
> (file "../sqlite-3.3.17/tclinstaller.tcl" line 15)
> 
> *** Error code 1
> 
> make: Fatal error: Command failed for target `tcl_install'
> 
> 
> 
> Is my syntax for --prefix correct and is that supposed to correct the
> above error?

Looks like it's correct syntax, and that it's doing the right thing and
attempting to install under /usr/local -- but that the user account
you're using lacks permission to write there, possibly because
/usr/local is mounted as read-only (hence the "read-only file system"
message).

-- James






-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

RE: [sqlite] --prefix

[James Dennett]

> -Original Message-
> From: Smith1, Robert E [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 1:12 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] --prefix
> 
> Hi,
> 
>  I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
> cannot install to /usr/local.  I start configure with
> --prefix=/ptmp/usr/localto try to get it to install to a different
> directory. But I get the same
> 
> error:
> 
> /ptmp/bld/> make install
> 
> tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3
> 
> can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
> system
> 
> while executing
> 
> "file mkdir $LIBDIR/sqlite3"
> 
> (file "../sqlite-3.3.17/tclinstaller.tcl" line 15)
> 
> *** Error code 1
> 
> make: Fatal error: Command failed for target `tcl_install'
> 
> 
> 
> Is my syntax for --prefix correct and is that supposed to correct the
> above error?

Looks like it's correct syntax, and that it's doing the right thing and
attempting to install under /usr/local -- but that the user account
you're using lacks permission to write there, possibly because
/usr/local is mounted as read-only (hence the "read-only file system"
message).

-- James


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

[sqlite] --prefix

[Smith1, Robert E]

Hi,

 I am trying to install sqlite3 on Sun Solaris 2.8. I am not root so I
cannot install to /usr/local.  I start configure with
--prefix=/ptmp/usr/localto try to get it to install to a different
directory. But I get the same

error:

/ptmp/bld/> make install

tclsh ../sqlite-3.3.17/tclinstaller.tcl 3.3

can't create directory "/usr/local/lib/tcl8.4/sqlite3": read-only file
system

while executing

"file mkdir $LIBDIR/sqlite3"

(file "../sqlite-3.3.17/tclinstaller.tcl" line 15)

*** Error code 1

make: Fatal error: Command failed for target `tcl_install'

 

Is my syntax for --prefix correct and is that supposed to correct the
above error?

 

Robert

Re: [sqlite] using vacuum

[Scott Baker]

If there is no redundancy to remove then the database size will
remain the same. Vacuum is only really effective after you delete a
large amount of data from a DB.

- Scott

Li, Charles wrote:
> Hi,
> I use the vacuum command on a database, but the size remains the same.  What
> should I check?
>  
> Thanks,
> Charles Li

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

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

[sqlite] using vacuum

[Li, Charles]

Hi,
I use the vacuum command on a database, but the size remains the same.  What
should I check?
 
Thanks,
Charles Li

Re: [sqlite] Re: CAST

[Don Lewis]

I agree as well.  We are upgrading a large, old application originally
written almost 20 years ago.  The additional time necessary  for
the most elegant solution can be very expensive.  PCs today are
powerful and cheap.  The end user does not care if there is another
layer inside, he only wants the application to work.  Some of our
customers are very small where SQLite, with its one database file
and no administration required, is perfect.  Many customers will
insist that we connect to their corporate database servers.  Robert's
wrapper should make this possible with minimal fuss.

Don

- Original Message - 
From: "Samuel R. Neff" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, May 29, 2007 11:06 AM
Subject: RE: [sqlite] Re: CAST



Actually I'd say he gave a great explanation of why the wrapper approach is
so important.  Robert went through all the work to make SQLite perform in a
scenario compatible with many other databases so now the users of his
wrapper don't have to.

Saying not to use wrappers when programming in straight C and using only
SQLite is one thing, but of course when developing in any other language or
when supporting multiple databases wrappers are essential (all of our
applications are in .NET and some support both SQLite and MSSQL).  We would
never have considered using SQLite for our product if it wasn't for Robert's
SQLite.NET wrapper.

Sam



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

RE: [sqlite] Re: CAST

[Samuel R. Neff]

Actually I'd say he gave a great explanation of why the wrapper approach is
so important.  Robert went through all the work to make SQLite perform in a
scenario compatible with many other databases so now the users of his
wrapper don't have to.

Saying not to use wrappers when programming in straight C and using only
SQLite is one thing, but of course when developing in any other language or
when supporting multiple databases wrappers are essential (all of our
applications are in .NET and some support both SQLite and MSSQL).  We would
never have considered using SQLite for our product if it wasn't for Robert's
SQLite.NET wrapper.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 11:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have just given an excellent explanation of why the wrapper approach 
is flawed.  Think about it.


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

Re: [sqlite] Problem using loadable extensions

[Alexander Smondyrev]

On 5/24/07, Joe Wilson <[EMAIL PROTECTED]> wrote:


--- Alexander Smondyrev <[EMAIL PROTECTED]> wrote:
> I am trying to use loadable extensions in Sqlite and I've run into the
> following 2 problems:
>
> 1) I've downloaded src for 3.3.17 Sqlite and build it, but the '.load'
> option does not seem to appear when I run the shell. I've used the
default
> build mechanism with one minor change. I set the flag to disable tcl
> extensions. It is likely that I am missing some ./configure option, but
I
> can't figure out what I need to do. Any advise would be greatly
appreciated.

Comment out the line in Makefile.in (or the generated Makefile) to enable
loadable extensions:

  # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1



Thanks for the advise. This almost  worked  as is.  I had to add  '-ldl'
when building sqlite3 executable. Otherwise I was getting unidentified
dlopen, dlclose etc error even though I am not building tcl extensions.

Then "./configure && make" as normal.


If you want the FTS1 and FTS2 modules "pre-loaded" in sqlite3, just apply
the attached patch to the latest version of the sqlite source tree, and
"./configure && make". The fts1 and fts2 modules will be statically linked
into sqlite3. No need to load the FTS modules at runtime.



With my new build now succeeding I am still having problems loading shared
libraries that I've described in my original message. For a number of
reasons I would really like to load my modules at run time, rather than
'pre-load' in sqlite3. For starters, I'd like to figure out why the example
with half function which was provided by sqlite team can't be loaded as a
shared library. Am I missing something there? Any suggestions would be
greatly appreciated.

Thanks,

- Alex

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

I think I know what's going on. 

When you insert new rows in the presence of indexes then sqlite must
touch a lot of pages in each trascation to satisfy the rebuilding of
the index(es). These pages are built up in the transaction log which is 
stored in temp_store, which happens to be memory in your case.
SQLite's transaction log cannot be disabled.

If you drop the indexes, then your inserts will touch fewer pages resulting
in a smaller transaction log, and less temp_store use. But this is not
really a solution. To avoid this problem, you cannot use memory for 
temp_store.

--- Kalyani Tummala <[EMAIL PROTECTED]> wrote:
> I am planning to use sqlite as a database for storing and retrieving
> media data of about 5-10k records in a device whose main memory is
> extremely small. A sequence of insert statements increasing the heap
> usage to nearly 70K(almost saturating point) which is crashing my
> application. I want to restrict this to 30K. 
> 
> I tried closing database and reopen after some inserts but of no use. 
> 
> I have observed that, when I open the database with about 1K to 2K
> records in it, inserts and updates take more heap and also gradually
> increase than a a database with less than 1k records in it. 
> 
> My objective is to reduce the peak heap usage during inserts, updates
> and also deletes with little or no performance degradation.



   
Sick
 sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

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

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 8:40 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You have just given an excellent explanation of why the wrapper
> approach
> is flawed.  Think about it.

Every approach not deliberately designed around a specific module's exposed
API is fundamentally flawed in that it is limited by the constraints of the
wrapper.  The answer is not "don't use a wrapper" though.

"Think about it"
In my case, I can only speak for .NET and Microsoft environments, so here
goes:

1.  The complexities of interop'ing with SQLite's API are enormous -- an
insurmountable feat for a novice programmer to get right.  As a matter of
fact, most would simply get it wrong, blame it on SQLite, and switch to a
database engine that works better with their chosen design environment.

2.  A mountain of custom code has to be written to interact with SQLite, all
to use an engine who's familiar SQL92 syntax is so tantalizingly close to
the other databases they've used that one can't help to wonder "why do I
have to go to all this trouble just to use the same SQL syntax as my other
database programs?"  A wrapper solves this problem.

3.  A user can learn one API and apply it to multiple databases, instead of
learning one database API and applying it to a single type of database.

4.  The user can write engine-agnostic code and concentrate on the SQL
variations rather than wondering if they get the API code right and called
things in the right sequence.

5.  The wrapper expands the influence and usage of SQLite significantly.

Lets face it ... everyone likes potato chips, but if you had to make them
yourself everytime you got a craving, you'd probably think twice.  On the
other hand, if someone already made them and all you had to do was pick them
off the shelf, you'd be much more inclined to eat them.

So yes, homemade potato chips do taste better than the ones you buy at the
store, provided you get good potatoes, prepare them, peel them with a proper
tool, and watch them in the fryer carefully to avoid burning them.  Of
course you stink up the house and make an enormous mess, but that's the
price you pay for good chips.

So yes, the wrapper approach is flawed, but most folks don't want to stink
up their house trying to roll their own access layer.

Robert
 



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

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Joe Wilson]

The default is auto-commit, so unless you've opened an explicit transaction
with BEGIN and do a number of inserts, the COMMIT suggestion is not useful
in reducing memory footprint.

(apologies in advance if this is obvious...)
SDRAM is the normal volatile RAM, right?
You know that temp_store is competing with sqlite for your RAM.
If you point your temp_store to "disk" to use non-volatile storage 
(disk or flash) then you'd have more RAM available for sqlite. 
Or do you lack such non-volatile storage?

You can run the tests on Windows by installing either Cygwin or
MinGW/MSYS, installing Tcl 8.4, running configure, and then running "make test".
You can change the generated Makefile to define various sqlite compile-time
flags for memory debugging. Just scan the ifdef's in the source code for
ideas what to enable. It's much easier to do this under Linux than under 
Windows, in my opinion.

--- Kalyani Tummala <[EMAIL PROTECTED]> wrote:
> My temp_store is SDRAM. Thanks for your suggestion of using COMMIT. I
> have not used it. Any other pointers?
> 
> Best Regards
> Kalyani
> 
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 29, 2007 10:27 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to restrict the peak heap usage during
> multiple inserts and updates?
> 
> --- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > > I am working at porting sqlite ( ver 3.3.8 ) on an embedded device
> with
> > > extremely low main memory.
> > > 
> > > I tried running select queries on the tables( with about 2k records
> each
> > > having about 5 strings) and they do well within 20kB of runtime heap
> > > usage.
> > > 
> > > But, when I try new insertions, the heap usage grows tremendously
> (about
> > > 70 kB at peak).
> > 
> > Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
> > use somewhat.
> > 
> > Can you post an example of your schema and these insert statements?
> 
> Is your temp_store in memory or flash ram or other?
> 
> Another thought... are you performing a COMMIT after each INSERT?
> (or at least every X inserts, where X<100)?
> It might help to reduce memory (at the cost of reduced speed).



   
Get
 the free Yahoo! toolbar and rest assured with the added security of spyware 
protection.
http://new.toolbar.yahoo.com/toolbar/features/norton/index.php

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

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Christian Smith]

Kalyani Tummala uttered:


I am planning to use sqlite as a database for storing and retrieving
media data of about 5-10k records in a device whose main memory is
extremely small. A sequence of insert statements increasing the heap
usage to nearly 70K(almost saturating point) which is crashing my
application. I want to restrict this to 30K.

I tried closing database and reopen after some inserts but of no use.

I have observed that, when I open the database with about 1K to 2K
records in it, inserts and updates take more heap and also gradually
increase than a a database with less than 1k records in it.



When updating the database, SQLite will keep a bitmap representing 
modified pages in memory, so as to manage the rollback journal. Therefore, 
making your minimum pages size smaller will now require more bits to track 
all the potentially modified pages in the database file. Instead, using 
the stock SQLite parameters, increase the page size and reduce the number 
of buffers. Increasing the page size will reduce the number of pages being 
tracked, as well as increasing the number of rows in each page. But, 
depending on how big your database is, this may not be a significant 
amount of memory. How big is a typical database?





My objective is to reduce the peak heap usage during inserts, updates
and also deletes with little or no performance degradation.



You'll get a certain amount of slowdown when reducing the number of 
available buffers, as you'll be spilling dirty buffers to disk more often.





Please suggest me if I can do anything to do so.



You could try profiling memory usage before randomly changing parameters. 
In the source, perhaps on a test machine rather than the target platform, 
replace sqliteMalloc with a macro to log memory allocation, along with 
source file and line number information, something like what is done now 
with memory debugging turned on (see src/malloc.c and src/sqliteInt.h).





Thank you in advance
Kalyani





-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 6:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?

Since you are only using part of Sqlite have you considered using a much

smaller footprint storage system which only implements the functions you

are using?

Kalyani Tummala wrote:

Hi joe,

Thanks for your response.

In order to reduce the footprint size, I have bypassed parser

completely

and using byte codes directly as my schema and queries are almost
compile time fixed. Hence I am not using sqlite3_prepare().

The following is the schema and inserts I am using.
CREATE TABLE OBJECT(

PUOIINTEGER  PRIMARY KEY,
Storage_Id  INTEGER,
Object_Format   INTEGER,
Protection_Status   INTEGER,
Object_Size INTEGER,
Parent_Object   INTEGER,
Non_Consumable  INTEGER,
Object_file_nameTEXT,
NameTEXT,
File_Path   TEXT
);

CREATE TABLE AUDIO(

PUOIINTEGER PRIMARY KEY,
Use_Count   INTEGER,
Audio_Bit_Rate  INTEGER,
Sample_Rate INTEGER,
Audio_Codec_TypeINTEGER,
Number_of_Channels  INTEGER,
Track   INTEGER,
Artist  TEXT,
Title   TEXT,
Genre   TEXT,
Album_Name  TEXT,
File_Path   TEXT
);

INSERT INTO OBJECT VALUES (
 7, 65537, 12297, 0,
 475805, 6, 0,
 'ANJANEYASTOTRAM.mp3', NULL,
'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);


INSERT INTO AUDIO VALUES (
 7, 6, 144100, 0,
 0, 0, 6,
 NULL, NULL, NULL, NULL,
'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);

INSERT INTO OBJECT VALUES (
 8, 65537, 12297, 0,
 387406, 6, 0,
 'BHADRAM.mp3', NULL,
'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


 INSERT INTO AUDIO VALUES (
 8, 6, 144100, 0,
 0, 0, 6,
 NULL, NULL, NULL, NULL,
'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


Warm regards
Kalyani

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 9:42 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?



I am working at porting sqlite ( ver 3.3.8 ) on an embedded device


with


extremely low main memory.

I tried running select queries on the tables( with about 2k records


each


having about 5 strings) and they do well within 20kB of runtime heap
usage.

But, when I try new insertions, the heap usage grows tremendously


(about


70 kB at peak).



Perhaps preparing the statements (sqlite3_prepare) might decrease RAM
use somewhat.

Can you post an example of your schema and these insert statements?








Choose the right car based on your needs.  Check out

Yahoo!

Autos 

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[John Stanton]

In your case we would not use Sqlite and instead use a much simpler 
storage method.  Since your storage appears to be RAM resident that 
approach is indicated a fortiori.


We have had success with using storage based on AVL trees.  It is very 
fast and remains so despite repeated insertions and deletions.  The code 
footprint is tiny (10K) and there is no heap usage so memory leakage can 
never be a problem.  You do not have SQL in that environment but it 
would appear that you are not using it anyway.  Since your data is 
memory resident ACID compliance and logging are not an issue.


Even with quite detailed data manipulation you would be hard pressed to 
have a footprint greater than 30K.  You could cut that down by defining 
code like VDBE with a high information density and using a simple engine 
to interpret that metacode.  We have successfully used that approach at 
times.


Kalyani Tummala wrote:

Hi John,
I could not understand your query properly. Let me tell you my
application scenario. 


I am planning to use sqlite as a database for storing and retrieving
media data of about 5-10k records in a device whose main memory is
extremely small. A sequence of insert statements increasing the heap
usage to nearly 70K(almost saturating point) which is crashing my
application. I want to restrict this to 30K. 

I tried closing database and reopen after some inserts but of no use. 


I have observed that, when I open the database with about 1K to 2K
records in it, inserts and updates take more heap and also gradually
increase than a a database with less than 1k records in it. 


My objective is to reduce the peak heap usage during inserts, updates
and also deletes with little or no performance degradation.

Please suggest me if I can do anything to do so.

Thank you in advance
Kalyani



  


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 6:51 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?

Since you are only using part of Sqlite have you considered using a much

smaller footprint storage system which only implements the functions you

are using?

Kalyani Tummala wrote:


Hi joe,

Thanks for your response. 


In order to reduce the footprint size, I have bypassed parser


completely


and using byte codes directly as my schema and queries are almost
compile time fixed. Hence I am not using sqlite3_prepare(). 

The following is the schema and inserts I am using. 
CREATE TABLE OBJECT(


PUOIINTEGER  PRIMARY KEY,
Storage_Id  INTEGER,
Object_Format		INTEGER, 
Protection_Status	INTEGER,

Object_Size INTEGER,
Parent_Object   INTEGER,
Non_Consumable  INTEGER,
Object_file_nameTEXT,
NameTEXT,
File_Path   TEXT
);

CREATE TABLE AUDIO(

PUOIINTEGER PRIMARY KEY,
Use_Count   INTEGER,
Audio_Bit_Rate  INTEGER,
Sample_Rate INTEGER,
Audio_Codec_TypeINTEGER,
Number_of_Channels  INTEGER,
Track   INTEGER,
Artist  TEXT,
Title   TEXT,
Genre   TEXT,
Album_Name  TEXT,
File_Path   TEXT
);

INSERT INTO OBJECT VALUES (
7, 65537, 12297, 0,
475805, 6, 0, 
'ANJANEYASTOTRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);


INSERT INTO AUDIO VALUES (
7, 6, 144100, 0,
0, 0, 6, 
NULL, NULL, NULL, NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);

INSERT INTO OBJECT VALUES (
8, 65537, 12297, 0,
387406, 6, 0, 
'BHADRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


INSERT INTO AUDIO VALUES (
8, 6, 144100, 0,
0, 0, 6, 
NULL, NULL, NULL, NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


Warm regards
Kalyani

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 9:42 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?




I am working at porting sqlite ( ver 3.3.8 ) on an embedded device


with



extremely low main memory.

I tried running select queries on the tables( with about 2k records


each



having about 5 strings) and they do well within 20kB of runtime heap
usage.

But, when I try new insertions, the heap usage grows tremendously


(about



70 kB at peak).



Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
use somewhat.


Can you post an example of your schema and these insert statements?



  






Choose the right car based on your needs.  Check out


Yahoo!


Autos new Car Finder tool.
http://autos.yahoo.com/carfinder/







-
To unsubscribe, 

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 6:18 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

Your comments endorse the approach we took which was to avoid the
wrapper concept entirely with its inherent limitations  We use Sqlite
as
an embedded database in an application server rather than trying to
integrate an API which wraps Sqlite.

In the case of the date we implement date arithmetic and comparison
functions building upon the excellent date primitives in the Sqlite
source.  However your case does not require any special functions as it
is handled simply by the regular Sqlite date functions.

An example of the use of date arithmetic might be:

SELECT * FROM mytab WHERE days_overdue(due_date) > 90;

We implement the user functions either as native code or as Javascript.
 The Javascript has the advantage that the text is stored in the
database so the functions can be state-driven.



Ok, I grant you that was a bad example -- I was in a hurry when I posted it.
It also requires that datetimes are stored in the database as a string.  If
someone used a numeric type instead, you'd be forced into using a
user-defined function for a comparison against a literal string.  If all
users were nice and tidy and used parameterized queries, the world would be
a better place -- but try as you might to drill it into folks, they still
throw their literals into a query and defeat your nice inline type
adjusters.

I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into
the ADO.NET vNext (Entity Framework) recently, so a lot of my type
frustrations stem from that effort.

MS's Entity Framework uses CAST() liberally in its SQL construction, and
expects the returned values to match up to the type it was CAST() to --
which is currently impossible to do.  I can't cast to a datetime, guid,
int32, float, etc in SQLite and there's no way for me to tell what datatype
was mentioned in the CAST statement for a given returned column.  So when
I'm queried for the type of a column returned from one of these SELECT's,
there's no way for me to give back an accurate type.

I've hacked it up and done it, but its a bit ugly.  Fortunately users don't
need to see the underlying SQL generated from the Entity Framework :)

Robert

You have just given an excellent explanation of why the wrapper approach 
is flawed.  Think about it.




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




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

RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[Kalyani Tummala]

Hi John,
I could not understand your query properly. Let me tell you my
application scenario. 

I am planning to use sqlite as a database for storing and retrieving
media data of about 5-10k records in a device whose main memory is
extremely small. A sequence of insert statements increasing the heap
usage to nearly 70K(almost saturating point) which is crashing my
application. I want to restrict this to 30K. 

I tried closing database and reopen after some inserts but of no use. 

I have observed that, when I open the database with about 1K to 2K
records in it, inserts and updates take more heap and also gradually
increase than a a database with less than 1k records in it. 

My objective is to reduce the peak heap usage during inserts, updates
and also deletes with little or no performance degradation.

Please suggest me if I can do anything to do so.

Thank you in advance
Kalyani



  

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 6:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?

Since you are only using part of Sqlite have you considered using a much

smaller footprint storage system which only implements the functions you

are using?

Kalyani Tummala wrote:
> Hi joe,
> 
> Thanks for your response. 
> 
> In order to reduce the footprint size, I have bypassed parser
completely
> and using byte codes directly as my schema and queries are almost
> compile time fixed. Hence I am not using sqlite3_prepare(). 
>  
> The following is the schema and inserts I am using. 
> CREATE TABLE OBJECT(
> 
> PUOI  INTEGER  PRIMARY KEY,
> Storage_IdINTEGER,
> Object_Format INTEGER, 
> Protection_Status INTEGER,
> Object_Size   INTEGER,
> Parent_Object INTEGER,
> Non_ConsumableINTEGER,
> Object_file_name  TEXT,
> Name  TEXT,
> File_Path TEXT
> );
> 
> CREATE TABLE AUDIO(
> 
> PUOI  INTEGER PRIMARY KEY,
> Use_Count INTEGER,
> Audio_Bit_RateINTEGER,
> Sample_Rate   INTEGER,
> Audio_Codec_Type  INTEGER,
> Number_of_ChannelsINTEGER,
> Track INTEGER,
> ArtistTEXT,
> Title TEXT,
> Genre TEXT,
> Album_NameTEXT,
> File_Path TEXT
> );
> 
> INSERT INTO OBJECT VALUES (
>  7, 65537, 12297, 0,
>  475805, 6, 0, 
>  'ANJANEYASTOTRAM.mp3', NULL,
> 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
> );
> 
> 
> INSERT INTO AUDIO VALUES (
>  7, 6, 144100, 0,
>  0, 0, 6, 
>  NULL, NULL, NULL, NULL,
> 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
> );
> 
> INSERT INTO OBJECT VALUES (
>  8, 65537, 12297, 0,
>  387406, 6, 0, 
>  'BHADRAM.mp3', NULL,
> 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
> );
> 
> 
>  INSERT INTO AUDIO VALUES (
>  8, 6, 144100, 0,
>  0, 0, 6, 
>  NULL, NULL, NULL, NULL,
> 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
> );
> 
> 
> Warm regards
> Kalyani
> 
> -Original Message-
> From: Joe Wilson [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 29, 2007 9:42 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to restrict the peak heap usage during
> multiple inserts and updates?
> 
> 
>>I am working at porting sqlite ( ver 3.3.8 ) on an embedded device
> 
> with
> 
>>extremely low main memory.
>>
>>I tried running select queries on the tables( with about 2k records
> 
> each
> 
>>having about 5 strings) and they do well within 20kB of runtime heap
>>usage.
>>
>>But, when I try new insertions, the heap usage grows tremendously
> 
> (about
> 
>>70 kB at peak).
> 
> 
> Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
> use somewhat.
> 
> Can you post an example of your schema and these insert statements?
> 
> 
> 
>
>

> Choose the right car based on your needs.  Check out
Yahoo!
> Autos new Car Finder tool.
> http://autos.yahoo.com/carfinder/
> 
>

> -
> 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]
>

-
> 

[sqlite] Memory and Performance Profiling of sqlite commands

[Kalyani Tummala]

Dear Group,
Is there any provision in the sqlite library for memory and performance
profiling of SQL commands. How can I use the test folder where tcl
script files are available for regression testing? How can I run those
scripts in Windows XP? 

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] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 6:18 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> Your comments endorse the approach we took which was to avoid the
> wrapper concept entirely with its inherent limitations  We use Sqlite
> as
> an embedded database in an application server rather than trying to
> integrate an API which wraps Sqlite.
> 
> In the case of the date we implement date arithmetic and comparison
> functions building upon the excellent date primitives in the Sqlite
> source.  However your case does not require any special functions as it
> is handled simply by the regular Sqlite date functions.
> 
> An example of the use of date arithmetic might be:
> 
> SELECT * FROM mytab WHERE days_overdue(due_date) > 90;
> 
> We implement the user functions either as native code or as Javascript.
>   The Javascript has the advantage that the text is stored in the
> database so the functions can be state-driven.

Ok, I grant you that was a bad example -- I was in a hurry when I posted it.
It also requires that datetimes are stored in the database as a string.  If
someone used a numeric type instead, you'd be forced into using a
user-defined function for a comparison against a literal string.  If all
users were nice and tidy and used parameterized queries, the world would be
a better place -- but try as you might to drill it into folks, they still
throw their literals into a query and defeat your nice inline type
adjusters.

I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into
the ADO.NET vNext (Entity Framework) recently, so a lot of my type
frustrations stem from that effort.

MS's Entity Framework uses CAST() liberally in its SQL construction, and
expects the returned values to match up to the type it was CAST() to --
which is currently impossible to do.  I can't cast to a datetime, guid,
int32, float, etc in SQLite and there's no way for me to tell what datatype
was mentioned in the CAST statement for a given returned column.  So when
I'm queried for the type of a column returned from one of these SELECT's,
there's no way for me to give back an accurate type.

I've hacked it up and done it, but its a bit ugly.  Fortunately users don't
need to see the underlying SQL generated from the Entity Framework :)

Robert




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

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[John Stanton]

Since you are only using part of Sqlite have you considered using a much 
smaller footprint storage system which only implements the functions you 
are using?


Kalyani Tummala wrote:

Hi joe,

Thanks for your response. 


In order to reduce the footprint size, I have bypassed parser completely
and using byte codes directly as my schema and queries are almost
compile time fixed. Hence I am not using sqlite3_prepare(). 
 
The following is the schema and inserts I am using. 
CREATE TABLE OBJECT(


PUOIINTEGER  PRIMARY KEY,
Storage_Id  INTEGER,
Object_Format		INTEGER, 
Protection_Status	INTEGER,

Object_Size INTEGER,
Parent_Object   INTEGER,
Non_Consumable  INTEGER,
Object_file_nameTEXT,
NameTEXT,
File_Path   TEXT
);

CREATE TABLE AUDIO(

PUOIINTEGER PRIMARY KEY,
Use_Count   INTEGER,
Audio_Bit_Rate  INTEGER,
Sample_Rate INTEGER,
Audio_Codec_TypeINTEGER,
Number_of_Channels  INTEGER,
Track   INTEGER,
Artist  TEXT,
Title   TEXT,
Genre   TEXT,
Album_Name  TEXT,
File_Path   TEXT
);

INSERT INTO OBJECT VALUES (
 7, 65537, 12297, 0,
 475805, 6, 0, 
 'ANJANEYASTOTRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);


INSERT INTO AUDIO VALUES (
 7, 6, 144100, 0,
 0, 0, 6, 
 NULL, NULL, NULL, NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);

INSERT INTO OBJECT VALUES (
 8, 65537, 12297, 0,
 387406, 6, 0, 
 'BHADRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


 INSERT INTO AUDIO VALUES (
 8, 6, 144100, 0,
 0, 0, 6, 
 NULL, NULL, NULL, NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


Warm regards
Kalyani

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 9:42 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?



I am working at porting sqlite ( ver 3.3.8 ) on an embedded device


with


extremely low main memory.

I tried running select queries on the tables( with about 2k records


each


having about 5 strings) and they do well within 20kB of runtime heap
usage.

But, when I try new insertions, the heap usage grows tremendously


(about


70 kB at peak).



Perhaps preparing the statements (sqlite3_prepare) might decrease RAM 
use somewhat.


Can you post an example of your schema and these insert statements?



   


Choose the right car based on your needs.  Check out Yahoo!
Autos new Car Finder tool.
http://autos.yahoo.com/carfinder/


-
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]
-




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

Re: [sqlite] Join

[John Stanton]

See the ATTACH statement.

Shilpa Sheoran wrote:

Does sqlite allow joining tables in different database files using
triggers or any other mechanism? Does it affect the performance?

Thanks
Shilpa

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, May 28, 2007 4:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

We actually do that with our Sqlite interfaces.  We use the declared
type to specify the type and perform a conversion when necessary.  For
example if the declared type of a column is DATE we know that we
actually have a FLOAT so when we call a Javascript SQL function for
example the Sqlite user function transforms the floating point number
declared as a DATE to be a Javascript Date object.

A FLOAT with declared type DATE may be transformed into a date string
in
accordance with the rules of the chosen locale when being embedded in
an
HTML page.

Our applications use DECIMAL arithmetic for accuracy so when a column
is
declared as DECIMAL its actual type is TEXT but arithmetic rules are
applied.  If Sqlite has decided to make it an INTEGER or a FLOAT the
correct conversion is made with the declared precision and scale.



I use the declared type as well (where possible) in the SQLite .NET
provider.  Still, it'd be better if we could have an extensible type system
built into the engine itself so one could perform literal to column
comparisons and other SQL statements that are beyond the scope of a wrapper
to provide type adjustments for.

Your date example is a perfect example of a place where an extensible type
system would be ideal.  A SQL statement against a DATE field such as ...

  SELECT * FROM FOO WHERE [TheDate] < '2007/01/01'

...is pretty much impossible to fix in a wrapper -- but if we had an
extensible type system we could provide our own comparison func that takes
[TheDate]'s value and the literal value and figures out how to interpret and
compare the two values.

Robert

Your comments endorse the approach we took which was to avoid the 
wrapper concept entirely with its inherent limitations  We use Sqlite as 
an embedded database in an application server rather than trying to 
integrate an API which wraps Sqlite.


In the case of the date we implement date arithmetic and comparison 
functions building upon the excellent date primitives in the Sqlite 
source.  However your case does not require any special functions as it 
is handled simply by the regular Sqlite date functions.


An example of the use of date arithmetic might be:

SELECT * FROM mytab WHERE days_overdue(due_date) > 90;

We implement the user functions either as native code or as Javascript. 
 The Javascript has the advantage that the text is stored in the 
database so the functions can be state-driven.




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




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

[sqlite] Re: sqlite3_reset (or sqlite3_finalize) and error reporting?

[Jef Driesen]

Scott McDonald wrote:

Jef Driesen wrote:

Scott McDonald wrote:

Jef Driesen wrote:
I have some questions on the usage of sqlite3_reset (or 
sqlite3_finalize) after sqlite3_step.


In the legacy interface I use sqlite3_reset after sqlite3_step to obtain 
a more specific error code for SQLITE_ERROR (to be able to detect schema 
errors and automatically reprepare the statement, like the v2 interface 
does):


int myprepare(sqlite3* db, const char* sql, int nbytes, sqlite3_stmt** 
stmt, const char** tail)

{
#ifdef USE_LEGACY
int rc = sqlite3_prepare (db, sql, nbytes, stmt, tail);
#else
int rc = sqlite3_prepare_v2 (db, sql, nbytes, stmt, tail);
#endif
if (rc != SQLITE_OK && *stmt != 0) {
   sqlite3_finalize (*stmt);
   *stmt = 0;
}
}
int mystep(sqlite3_stmt* stmt)
{
int rc = sqlite3_step (stmt);
#ifdef USE_LEGACY
if (rc == SQLITE_ERROR)
   rc = sqlite3_reset (stmt);
#endif
return rc;
}

This works well, but now I also want to report an appropriate error 
message to the user (by throwing an exception). But I'm having some 
problems with that. In some cases, the correct errcode and errmsg (from 
the sqlite3_errcode and sqlite3_errmsg functions) can be obtained 
directly after calling sqlite3_step, but sometimes sqlite3_reset is 
required because sqlite3_step only returns a generic error. My idea was 
now to always use sqlite3_reset (see example results below):


int mystep(sqlite3_stmt* stmt)
{
int rc = sqlite3_step (stmt);
if (rc != SQLITE_DONE && rc != SQLITE_ROW)
   rc = sqlite3_reset (stmt);
return rc;
}

This also makes my code behave the same for both the legacy and the v2 
interface, since I have to use sqlite3_reset anyway in the legacy 
interface (except for the few return codes that are reported directly).


This works well in most cases, but as you can see from the results 
below, I can't get a correct error message for SQLITE_MISUSE. Now my 
questions are:


1. How do I retrieve the errmsg for SQLITE_MISUSE? Are there any other 
codes with this problem? Maybe it's worth adding a function to obtain 
the errmsg from an error code (e.g. not only the most recent one).


2. Is it normal that sometimes the rc value is different from the 
errcode (and its associated errmsg)? The documentation for 
sqlite3_errcode seems to suggest that this should not happen.


Sample output (in the format "function: rc, errcode, errmsg") for a few 
errors:


SQLITE_CONSTRAINT (legacy)
sqlite3_step: 1, 1, SQL logic error or missing database
sqlite3_reset: 19, 19, column is not unique

SQLITE_CONSTRAINT (v2)
sqlite3_step: 19, 1, SQL logic error or missing database
sqlite3_reset: 19, 19, column is not unique

SQLITE_BUSY (legacy and v2)
sqlite3_step: 5, 5, database is locked
sqlite3_reset: 5, 5, database is locked

SQLITE_MISUSE (legacy and v2)
sqlite3_step: 21, 0, not an error
sqlite3_reset: 0, 0, not an error


I was getting similar results, for me I noticed this behavior with a
CONSTRAINT failure was getting a 19 return code but in my logging for this
I use the error code and error message API and was getting 1 and "SQL logic
or missing database" or something like that.

On the "sqlite_finalize" I would get a 19 return code and in my logging
would get a 19 error code and error message of "PRIMARY KEY must be unique"
- this is what I would expect after calling "sqlite_step" not after calling
"sqlite_finalize" - this is like the legacy behavior you mentioned, etc.


In the v2 interface, the return code is always reported immediately,
without the need to call sqlite3_reset or sqlite3_finalize. This is
explained in the documentation.

So far no problem, but I noticed the functions sqlite3_errcode and
sqlite3_errmsg are always behaving as they did with the legacy
interface. The correct error code and error message is only returned
after calling sqlite3_reset or sqlite3_finalize. But this is something
that is *NOT* mentioned in the documentation at all. As it turns out I'm
not the only one who finds this really confusing, so this should really 
be added to the documentation.



I noticed you took it a couple of steps further and analyzed other possible
"prepared statement" errors in this area, nice work...

Just wondering if you got any resolution on this as it doesn't feel "clean"
to me - I would like my logging statements to actually provide some useful
information when the error occurs, etc.


You can get the useful error information from sqlite3_step after calling
sqlite3_reset (no matter which interface you used to prepare the statement).

But as I found out, this does not work for SQLITE_MISUSE (and maybe
others?). In this case I never get an appropriate error message (I
always get "not an error") and the error code is even lost after calling
sqlite3_reset! I'm not aware of a solution for this problem.


Thanks Jef for responding. 


You're welcome... But can you send your replies to the mailinglist also,
because the discussion might be useful for someone else 

Re: [sqlite] how to change SQLite column definition

[qinligeng]

Thanks! But it will take a long time if the table have a large number of rows.
I hope that SQLite will support "Alter Column" in next version!

- Original Message - 
From: "Scott Hess" <[EMAIL PROTECTED]>
To: 
Sent: Monday, May 28, 2007 10:38 PM
Subject: Re: [sqlite] how to change SQLite column definition


> In case it wasn't obvious, the "more complicated way" would probably
> be something like:
> 
> BEGIN;
> ALTER TABLE RENAME MyTable TO MyTableOld;
> CREATE TABLE MyTable (
>  SameColumn INTEGER,
>  NewColumn TEXT
> );
> INSERT INTO MyTable SELECT SameColumn, OldColumn FROM MyTableOld;
> DROP TABLE MyTableOld;
> COMMIT;
> 
> As prose: Within a transaction, create the new table, populate it from
> the old table, and rename it into place.
> 
> -scott
> 
> 
> On 5/28/07, Dennis Povshedny <[EMAIL PROTECTED]> wrote:
>> AFAIK SQLite does not have such functionality.
>>
>> Please see http://sqlite.org/lang_altertable.html . So you have to do it
>> in a bit more complicated way.
>>
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> Sent: Monday, May 28, 2007 10:50 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] how to change SQLite column definition
>>
>>
>> Is there any simple way to change the definition of a column in sqlite
>> database? we can do this in sqlserver using a sql statement like this:
>> alter table  alter column 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
>