Re: [sqlite] Can sqlite manage a ramdisk file and a nvram file at same time.

[Jiao]

hi Da Silva

 Thank you very much. your advice is very appreciated, now I think I should 
give up the idea to set db file in flash, in our application,the flash only 
save some basic config data,i.e.,IP,mac,device_id,and its PPPOE user 
 speaking ,these  varibles  can be changed but  not frequently 
.however ,these information is quite simple,can be made fixed-varible size,so 
can  read and write directly from flash. 
My orignal idea is to create a db to manage all the informations of the 
whole system . just like  Registry table in Window OS. At boot time,these 
informations is loaded from server and be shared by all process in the 
system(embest Linux).these informations can be frequently changed. so it 's 
better to save them in ram only.

   Thank again for your help

Best regards
Jiao

- Original Message - 
From: "Jose Da Silva" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 22, 2006 11:25 AM
Subject: Re: [sqlite] Can sqlite manage a ramdisk file and a nvram file at same 
time.


> On February 21, 2006 05:43 pm, Jiao wrote:
> > all
> > I 'm designing an embedded system,Our system have limited flash,there
> > are much datas required to manage, but not all the data required to
> > save into flash,just leave them in ramdisk. so I intend to design two
> > db file ,one in flash and another in ramdisk,my question is can
> > sqlite easily support this application? Best regards
> 
> You have some interesting hardware quirks you will need to take into 
> account. For example, hardware flash has a limited amount of writes 
> allowed, somewhere on the order of 100,000 to 10,000,000 writes allowed 
> depending on which manufacturer you go with and where you write-to in 
> the nvram.
> You may think that 100,000 to 10,000,000 is far more than you need to 
> worry about, because you may write and save key files a dozen times, or 
> a hundred times, but you need to realize that there are some magic 
> spots that get pounded over and over again fairly often. These spots 
> are more often at the lower operating levels... for example, I'm not 
> too experienced with sqlite, but it does have some magic spots, such as 
> file locks (http://www.sqlite.org/faq.html#q7), meanwhile, disk formats 
> such as FAT, have partition tables, and when you start dealing with 
> several dozen or hundred items, you begin to realize that you tend to 
> pound these magic locations often, which brings you quickly on the 
> 100,000 or 10,000,000 "guaranteed" write limit.
> 
> If this is a problem, you may have to pick-up on hardware groups, like 
> www.piclist.com for suggestions where ideas like this get tossed around 
> often enough.
> 
> 1st place you want to begin, is by learning what the write-limits are 
> for the nvram you have in mind.
> 2nd, check the code for sqlite or anything else and look to see where 
> these magic spots that get pounded often are.
> 3rd, decide if you are still okay with the hardware limits (remember 1 
> file will affect 1 spot, but 100 files may affect the same spot 100 
> times).
> 4th, decide if you wish to continue along this path, or a modified 
> version of this path, or something altogether different.
> 
> If you read the above, I'm not telling you you can't do what you plan on 
> doing, I'm telling you that you need to take the underlying hardware 
> technology into account and you may need to have modifications made 
> based on what you need.
> 
> Hope this advice helps you get going in the right direction.

Re: [sqlite] Can sqlite manage a ramdisk file and a nvram file at same time.

[Jose Da Silva]

On February 21, 2006 05:43 pm, Jiao wrote:
> all
> I 'm designing an embedded system,Our system have limited flash,there
> are much datas required to manage, but not all the data required to
> save into flash,just leave them in ramdisk. so I intend to design two
> db file ,one in flash and another in ramdisk,my question is can
> sqlite easily support this application? Best regards

You have some interesting hardware quirks you will need to take into 
account. For example, hardware flash has a limited amount of writes 
allowed, somewhere on the order of 100,000 to 10,000,000 writes allowed 
depending on which manufacturer you go with and where you write-to in 
the nvram.
You may think that 100,000 to 10,000,000 is far more than you need to 
worry about, because you may write and save key files a dozen times, or 
a hundred times, but you need to realize that there are some magic 
spots that get pounded over and over again fairly often. These spots 
are more often at the lower operating levels... for example, I'm not 
too experienced with sqlite, but it does have some magic spots, such as 
file locks (http://www.sqlite.org/faq.html#q7), meanwhile, disk formats 
such as FAT, have partition tables, and when you start dealing with 
several dozen or hundred items, you begin to realize that you tend to 
pound these magic locations often, which brings you quickly on the 
100,000 or 10,000,000 "guaranteed" write limit.

If this is a problem, you may have to pick-up on hardware groups, like 
www.piclist.com for suggestions where ideas like this get tossed around 
often enough.

1st place you want to begin, is by learning what the write-limits are 
for the nvram you have in mind.
2nd, check the code for sqlite or anything else and look to see where 
these magic spots that get pounded often are.
3rd, decide if you are still okay with the hardware limits (remember 1 
file will affect 1 spot, but 100 files may affect the same spot 100 
times).
4th, decide if you wish to continue along this path, or a modified 
version of this path, or something altogether different.

If you read the above, I'm not telling you you can't do what you plan on 
doing, I'm telling you that you need to take the underlying hardware 
technology into account and you may need to have modifications made 
based on what you need.

Hope this advice helps you get going in the right direction.

[sqlite] Can sqlite manage a ramdisk file and a nvram file at same time.

[Jiao]

all
I 'm designing an embedded system,Our system have limited flash,there are much 
datas required to manage, but not all the data required to save into flash,just 
leave them in ramdisk. so I intend to design two db file ,one in flash and 
another in ramdisk,my question is can sqlite easily support this application?
Best regards

Jiao

Re: [sqlite] Older sources

[Manfred Bergmann]

Am 21.02.2006 um 00:12 schrieb Jens Miltner:



Am 20.02.2006 um 10:08 schrieb Manfred Bergmann:



Am 20.02.2006 um 19:12 schrieb Jose Da Silva:


On February 19, 2006 06:45 pm, Manfred Bergmann wrote:

Hi there.
I would need the sources of an older version (3.1.2) of SQLite.
Are they still available for download?


If nobody has it, go to google and search for these 3 words:
sqlite 3.1.2 rpm
or
sqlite 3.1.2 tar.gz

The response is many mirror sites, so here's the link for above.
watch for line-wrap if line too long shown here:
http://www.google.ca/search?hl=en=ISO-8859-1=sqlite+3.1.2 
+rpm=Search=


Ok, thx. I thought there is a "official" place where they are  
accessible.


I'm pretty sure you can fetch them from cvs. Check the timeline at  
 and search for the 3.1.2  
release milestone. Once you've found it, click it's link and you'll  
be taken to the milestone details page. There's a navigation link  
"[Tagging/Branching]", which will display the cvs commands to use  
to create a tag for this milestone. You can use the date specified  
for this tag to checkout the sources for that release...


(Unless there are tags for the releases, in which case you'd only  
need to find the appropriate tag and cvs checkout with this tag).


Yea, that worked very well, thx.


Best regards,
Manfred






___ 
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de

Re: [sqlite] Strange execution times

[Adrian Ho]

On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Sch??bel wrote:
> I don't think it's an interface problem. I'm using Tcl, more or less
> the 'natural' language for sqlite. Tcl doesn't have a garbage
> collection.

Tcl certainly *does* have garbage collection:




> The strangest thing is, I can reproduce this behaviour.
> I'm absolutely clueless. I stumbled over it by coincidence.
> Tried 1000 repetitions, was quite fast, so I tried 1,
> which was even faster. This led me to the (obviously wrong)
> conclusion, that sqlite spends some time parsing the sql.
> Next I tried 100 repetitions, expecting a bit more than
> 76 microseconds. 310 microsecs didn't bother me really,
> I tried the 10 reps expecting even more. Then came the surprise:
> only 67 microsecs.
> 
> My first feeling was, something like a busy disk or so came
> in just when I tried the 100 reps. But the results were reproducible,
> deviating only by a few microseconds.

Try running the following script and see if there's an odd pattern to
the timing variations:

#!/usr/bin/env tclsh
package require sqlite3
if {[file exists aho.db]} {
  file delete aho.db
}
sqlite3 db aho.db
db eval {create table cust_persons ( first_name string, last_name string
)}
db eval {insert into cust_persons values ('Adrian','Ho')}
db eval {insert into cust_persons values ('Thunder','Lightning')}
foreach rounds {1 5 10 50 100 500 1000 5000 1} {
  puts "t($rounds)=[time {db eval {select * from cust_persons where first_name 
= 'Adrian'}} $rounds]"
}
db close

- Adrian

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Nathaniel,

even if it is not absolutely accurate, a factor of about 5
is beyond accuracy tolerances.

Kind regards

Ulrich

On Wednesday 22 February 2006 01:38, Nathaniel Smith wrote:
> On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Schöbel wrote:
> > I tried the 10 reps expecting even more. Then came the surprise:
> > only 67 microsecs.
> >
> > My first feeling was, something like a busy disk or so came
> > in just when I tried the 100 reps. But the results were reproducible,
> > deviating only by a few microseconds.
>
> Perhaps your timing harness simply can't get accurate results at a
> mere 10 repetitions?
>
> -- Nathaniel

Re: [sqlite] Strange execution times

[Nathaniel Smith]

On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Schöbel wrote:
> I tried the 10 reps expecting even more. Then came the surprise:
> only 67 microsecs.
> 
> My first feeling was, something like a busy disk or so came
> in just when I tried the 100 reps. But the results were reproducible,
> deviating only by a few microseconds.

Perhaps your timing harness simply can't get accurate results at a
mere 10 repetitions?

-- Nathaniel

-- 
Eternity is very long, especially towards the end.
  -- Woody Allen

Re: [sqlite] Strange execution times

[Ulrich Schöbel]

Hi Thomas,

no, I can't reproduce it in C. The problem is not worth the effort,
I can live with these timings, it's just strange.

I don't think it's an interface problem. I'm using Tcl, more or less
the 'natural' language for sqlite. Tcl doesn't have a garbage
collection.

The strangest thing is, I can reproduce this behaviour.
I'm absolutely clueless. I stumbled over it by coincidence.
Tried 1000 repetitions, was quite fast, so I tried 1,
which was even faster. This led me to the (obviously wrong)
conclusion, that sqlite spends some time parsing the sql.
Next I tried 100 repetitions, expecting a bit more than
76 microseconds. 310 microsecs didn't bother me really,
I tried the 10 reps expecting even more. Then came the surprise:
only 67 microsecs.

My first feeling was, something like a busy disk or so came
in just when I tried the 100 reps. But the results were reproducible,
deviating only by a few microseconds.

The solution to this riddle is not really urgent, it's just
totally confusing. I'm curious.

Thanks and kind regards

Ulrich


On Wednesday 22 February 2006 00:08, Thomas Chust wrote:
> On Tue, 21 Feb 2006, UlrichSchöbel wrote:
> > [...] Where do those 309.95 microseconds come from? What's the
> > difference between running a query 100 times or 1 times? Should I
> > avoid running a select exactly 100 times for some obscure reason? [...]
>
> Hello,
>
> can you reproduce similar timings in plain C? If not, I would guess that
> the "problem" lies somewhere at the interface between C and the language
> you are using. There can be lots of different reasons; a common one for
> high level languages are garbage collection cycles, for example.
>
> cu,
> Thomas Chust

Re: [sqlite] sqlite, odbc, any gotchas?

[Jose Da Silva]

On February 21, 2006 12:48 pm, Jay Sprenkle wrote:
> Sqlite has been very fast for me. If you need raw blazing

Well, sqlite is definitely fast in relation to other sqls, but every sql 
has overhead, and they all can't match direct access of a 
flattish-file-format by a computer doing it's own work of accessing the 
files directly (assuming smallish small-company files at this point, 
versus large user-base files).

> speed Access and ODBC are not the way to go.

True, but still have to deal with convincing and converting users who 
get fed their information via slick-glossy magazines, so at this point, 
there is still the stigma to overcome and the user-base needs to be 
introduced to alternatives slowly.
The odbc allows my options to remain open as to how to implement the 
webstuff, and possibly other alternatives of access. The alternative 
was accessing DBF files directly from the server, therfore allowing me 
to also read DBF files via linux and eventually the webserver stuff.

Your suggestion about database corruption is a definite drop of DBF and 
committing with odbc.  Thanks

Re: [sqlite] Strange execution times

[drh]

Ulrich =?iso-8859-1?q?Sch=F6bel?= <[EMAIL PROTECTED]> wrote:
> 
> % time {db eval {select * from cust_persons where first_name='Ulrich'}} 1000
> 75.498 microseconds per iteration
> % time {db eval {select * from cust_persons where first_name='Ulrich'}} 1
> 51.6179 microseconds per iteration
> % time {db eval {select * from cust_persons where first_name='Ulrich'}} 100
> 309.95 microseconds per iteration
> % time {db eval {select * from cust_persons where first_name='Ulrich'}} 10
> 66.8 microseconds per iteration
> 
> Where do those 309.95 microseconds come from? What's the
> difference between running a query 100 times or 1 times?
> Should I avoid running a select exactly 100 times for some
> obscure reason?
> 

Hmmm  Most puzzling.  Can you provide your scheme and
data so that I can run some experiments?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Strange execution times

[Thomas Chust]

On Tue, 21 Feb 2006, UlrichSchöbel wrote:

[...] Where do those 309.95 microseconds come from? What's the 
difference between running a query 100 times or 1 times? Should I 
avoid running a select exactly 100 times for some obscure reason? [...]


Hello,

can you reproduce similar timings in plain C? If not, I would guess that 
the "problem" lies somewhere at the interface between C and the language 
you are using. There can be lots of different reasons; a common one for 
high level languages are garbage collection cycles, for example.


cu,
Thomas Chust

[sqlite] Please help, am I doing something wrong?

[ed nospam]

 Hi,
 I know that sqlite3 uses reader-writer locks and I've read the part
of FAQ that describes the multiple client access. But I'm still having
a lot of problem.

 I use sqlite3-ruby to access the database file with Rails/ActiveRecord.
 I also use sqlite3 C API to access the database file using C in my
other client.
 I have another program that uses regular ruby code and sqlite3-ruby
to access the same database file.
 I kept on getting database is locked error. I have busy-handler
registered for all of them and the write should take only milliseconds
(very simple write). All the programs do some writing.
 Is there a way to only lock the whole file when writing and not when reading?
 Actually, for anyone experienced in sqlite3, what's the correct way
of using it in my situation?

 Thank you very much.
--ed

[sqlite] Strange execution times

[Ulrich Schöbel]

Hi all,

I just made my first steps into sqlite (3.3.4).

I created  a small table, filled it with two rows of data
and timed a select. Those were the results:

% time {db eval {select * from cust_persons where first_name='Ulrich'}} 1000
75.498 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 1
51.6179 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 100
309.95 microseconds per iteration
% time {db eval {select * from cust_persons where first_name='Ulrich'}} 10
66.8 microseconds per iteration

Do you notice the 309.95 microseconds? I first blamed it on a
busy harddisk and repeated all four commands several times.
The results vary slightly but keep in the above picture.

Where do those 309.95 microseconds come from? What's the
difference between running a query 100 times or 1 times?
Should I avoid running a select exactly 100 times for some
obscure reason?

Maybe someone here knows the secret.

Kind regards

Ulrich

Re: [sqlite] sqlite, odbc, any gotchas?

[Jay Sprenkle]

On 2/21/06, Jose Da Silva <[EMAIL PROTECTED]> wrote:
> I was a bit hesitant about using sqlite when I read elsewhere that a sql
> type database is about 15x slower than direct access.
> However, since this will eventually be multiuser, the
> access-trashing-aspect is definitely something I want to avoid.  :-)
> Thanks, that's a good suggestion.

Sqlite has been very fast for me. If you need raw blazing
speed Access and ODBC are not the way to go.

If you do have problems with speed getting the query and the schema
correct can make the difference betweens many minute response
times and sub second response times.

Good luck!

Re: [sqlite] sqlite, odbc, any gotchas?

[Jose Da Silva]

I was a bit hesitant about using sqlite when I read elsewhere that a sql 
type database is about 15x slower than direct access.
However, since this will eventually be multiuser, the 
access-trashing-aspect is definitely something I want to avoid.  :-)
Thanks, that's a good suggestion.

If anyone is interested in compiling the latest sqliteodbc (0.65), I've 
got a modified configure file to replace the 0.65 sqliteodbc configure 
file located at:  http://www.joescat.com/configure.tar.gz
(sorry, no webpage setup due to lack of time)  :-(

The modified configure file defaults to use sqlite3 versus sqlite2 
headers and information, and you don't have-to-have sqlite2 installed 
to get past the configure command, plus you don't need to type options 
on the configure line if your sqlite is located in default file 
locations:

./configure
make
make install

the sqliteodbc authour already has been notified and been given a copy 
now too, so not necessary to send multiple copies his way (since this 
is a mailinglist and I'm sure several people will want to mention 
it).  ;-)


On February 21, 2006 07:15 am, Jay Sprenkle wrote:
> That might be a good way to get some basic tools over the top
> of the database. Access does tend to trash its database files if used
> by multiple users on a network. If you connect to sqlite files via
> odbc they should be ok though. Backup your mdb file to be safe!
>
> On 2/19/06, Jose Da Silva <[EMAIL PROTECTED]> wrote:
> > If read a bit already and see limitations and benefits in SQlite.
> > Our office still wants to use the GUI present in MS-Access.
> > I like the simplicity of maintaining SQLite plus it's rollback
> > features etc.   Later, there are aspirations for a webbrowser
> > interface to same database.
> > Anyone have recommendations or suggestions to this scenario:
> > Windows(MS-Access)<---office network--->(sqliteodbc-sqlite)linux

Re: [sqlite] import

[Marian Olteanu]

I have them installed in Windows. Cygwin brought them.

But I'm sure it's easy to find on the internet stand-alone versions of 
them. A quick google search took me to http://www.bastet.com/ for example.


On Tue, 21 Feb 2006, Jay Sprenkle wrote:


If you're on unix/linux:

cat yourfile | sed 's/\n//g' > transformed file


On 2/20/06, Marian Olteanu <[EMAIL PROTECTED]> wrote:

dos2unix, unix2dos tools will do the conversion

Re: [sqlite] import

[Jay Sprenkle]

If you're on unix/linux:

cat yourfile | sed 's/\n//g' > transformed file


On 2/20/06, Marian Olteanu <[EMAIL PROTECTED]> wrote:
> dos2unix, unix2dos tools will do the conversion

Re: [sqlite] sqlite, odbc, any gotchas?

[Jay Sprenkle]

That might be a good way to get some basic tools over the top
of the database. Access does tend to trash its database files if used
by multiple users on a network. If you connect to sqlite files via odbc
they should be ok though. Backup your mdb file to be safe!



On 2/19/06, Jose Da Silva <[EMAIL PROTECTED]> wrote:
> If read a bit already and see limitations and benefits in SQlite.
> Our office still wants to use the GUI present in MS-Access.
> I like the simplicity of maintaining SQLite plus it's rollback features
> etc.   Later, there are aspirations for a webbrowser interface to same
> database.
> Anyone have recommendations or suggestions to this scenario:
> Windows(MS-Access)<---office network--->(sqliteodbc-sqlite)linux

Re: [sqlite] where to download previous source

[Manfred Bergmann]

Am 21.02.2006 um 19:12 schrieb jack wu:

  i was trying to use the files in $phphome/ext/pdo_sqlite/src but  
it seems to be missing some files. at least the sqlite3.h.


The sqlite3.h (and maybe other files) file is IIRC generated on  
configure.



Best regards,
Manfred






___ 
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de

[sqlite] where to download previous source

[jack wu]

i need to build sqlite 328 in order to match the one that comes with php5. does 
any one know where i can download that particular version? 
   
  i was trying to use the files in $phphome/ext/pdo_sqlite/src but it seems to 
be missing some files. at least the sqlite3.h.
   
  many thanks.