Re: [sqlite] Overhead of indexes?

[Simon Slavin]

On 23 Jul 2009, at 2:20am, Jim Showalter wrote:

> The query looks like this:
>
> select distinct * from tbl where a <> 0 order by a desc, b desc, c
> desc, d desc limit 2;
>
> where a, b, c, and d are all type INTEGER.

To add to Igor's point, how much use an index is varies depending on  
how many values there can be for each of its fields.  So, for example,  
if there are lots of possible values for 'b' then there having 'b' in  
the index can save a great deal of sorting.  But if 'b' is likely to  
have one of just three possible values then sorting on it takes less  
effort.

However, there's another possible payoff: SQLite is clever.  If it  
finds all the values it needs in the index it's using, it doesn't  
bother to read the record.  So if all you really want are the values  
from fields a, b, c and d, then making one index with all four fields  
in allows SQLite to do the WHERE, the ORDER BY, and the SELECT purely  
from the index, without having to read the data record at all.

Fortunately, you don't have to change your code to test all this out.   
Write a couple thousand records without the index and test its speed.   
Add one kind of index, see how much the file has grown, and see if the  
speed increase is worth it.  Try a different index and try it again.   
No need to rewrite any of your INSERT or SELECT code.

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

Re: [sqlite] Overhead of indexes?

[Igor Tandetnik]

Jim Showalter  wrote:
> What is the overhead in O notation for adding an index to a column in
> SQLite?

You mean size overhead? O(n): the index is basically just another table 
(usually with fewer columns than the original). There's one record in 
the index for every record in the underlying table.

Igor Tandetnik 



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

[sqlite] Overhead of indexes?

[Jim Showalter]

I have everything working now, and am just tidying up. I want to add 
indexes, if needed, but this is for a cellphone app, and I'm worried 
about using up space, particularly when the data is unlikely to be 
more than a couple thousand rows in total. But there is a query that 
might be slow without indexes, so it's kind of a catch-22.

The query looks like this:

select distinct * from tbl where a <> 0 order by a desc, b desc, c 
desc, d desc limit 2;

where a, b, c, and d are all type INTEGER.

What is the overhead in O notation for adding an index to a column in 
SQLite? 

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

Re: [sqlite] Ability to convert Access to SQLite

[Rob Sciuk]

I believe that one poster (Rich Shepard?) touched upon the MDB-Tools in a 
response.  I concur, if you have the skills to build these tools from 
source, the rest is easy ...

The MDB Tools project hosted on SourceForge has a set of utilities which 
can enumerate tables, pull data, and schema info and convert same from 
.jet or .mdb databases into a vanilla SQL dialect which is very easy to 
convert to SQLite.

I have copied numerous large .mdb files from windows onto BSD or Linux 
platforms and successfully converted same to SQL databases on various 
occasions, and the mdb-tools are quite useful in this regard.  YMMV.

http://sourceforge.net/projects/mdbtools/files/

Hope this helps ...

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

Re: [sqlite] Determine if UPDATE has worked.

[Steve Martin]

Simon Slavin wrote:

>On 22 Jul 2009, at 12:40am, Steve Martin wrote:
>
>  
>
>>$ fred testdb "create table bob(p1 text PRIMARY KEY, p2 text)"
>>SQLITE_DONE: 101 : unknown error
>>
>>
>
>You do know this, and all your subsequent commands, will fail if you  
>still have your data in here from the last test, don't you ?
>
>
>  
>
Hi Simon,

Yes I do.  I am more use to working with postgres and oracle.  I am just 
getting a feel on how sqlite works and what happens when errors occurs.

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

Re: [sqlite] Installing SQLite

[scabral]

i don't get it...



Nuno Magalhães-2 wrote:
> 
> On Wed, Jul 22, 2009 at 21:30, scabral wrote:
> 
> [...]
> 
>> the instructions on the SQLite website are pretty crappy to say the
>> least.
>> When i download the zip file, all i get is 3 txt files???
> 
> [...]
> 
> Oh... a troll.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Installing-SQLite-tp24614036p24614214.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Installing SQLite

[Rich Shepard]

On Wed, 22 Jul 2009, scabral wrote:

> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>
> sqlite3   C File
> sqlite3   H File
> sqlite3ext H File

> what am i supposed to do with those?

   Well, based on what others wrote about your initial comments, I suggest
that you replace XP with a linux distribution. Then you can compile that
source code all by yourself. On the other hand, if you insist on sticking
with Microsoft, download one of the pre-built Winduhs .zip files as I
indicated in my previous message.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Rich Shepard]

On Wed, 22 Jul 2009, scabral wrote:

> does someone have 'good' instructions on what i need and how i need to
> install SQLite on Windows XP?

Scott,

   I don't do Windows; haven't for more than a dozen years. However, it
doesn't look too hard.

> the instructions on the SQLite website are pretty crappy to say the least.
> When i download the zip file, all i get is 3 txt files???

   Well, those of us on non-Microsoft systems are used to installing new
applications either from distribution-specific packages or by building and
installing from the source code. There are probably generic installation
instructions for XP, too.

   If you go to the download section of the Web site you'll see this:

Precompiled Binaries For Windows
sqlite-3_6_16.zip
(246.32 KiB)A command-line program for accessing and
modifying SQLite databases. See the
documentation for additional information.

tclsqlite-3_6_16.zip
(314.99 KiB)Bindings for Tcl/Tk. You can import this
shared library into either tclsh or wish to
get SQLite database access from Tcl/Tk. See
the documentation for details.

sqlitedll-3_6_16.zip
(243.68 KiB)This is a DLL of the SQLite library without
the TCL bindings. The only external
dependency is MSVCRT.DLL.

sqlite3_analyzer-3.6.1.zip
(508.70 KiB)An analysis program for database files
compatible with SQLite version 3.6.1 and
later.

   So I presume that you downloaded both sqlite-3_6_16.zip and
sqlitedll-3_6_16.zip. Yes?

   Aren't there generic instructions for installing *.exe and *.dll files?

> I eventually want to create a desktop application to run on SQLite, but not
> sure what language i want to use yet (pythong, ruby, etc...)

   For stand-alone applications I suggest Python (without the 'g'); for
Web-based (or http server-based) applications I suggest Ruby on Rails.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Thomas Briggs]

   Not in the InstallShield/MSI format that I imagine you're looking
for no.  But that's the whole point, really.

   See the "Precompiled Binaries" section of the download page.
You'll find what you need (if not necessarily what you're looking for)
there.

   -T

On Wed, Jul 22, 2009 at 4:42 PM, scabral wrote:
>
> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>
> sqlite3   C File
> sqlite3   H File
> sqlite3ext H File
>
> what am i supposed to do with those?
>
> Is there no 'true' install?
>
> Scott
>
> P Kishor-3 wrote:
>>
>> On Wed, Jul 22, 2009 at 3:30 PM, scabral wrote:
>>>
>>> Hi,
>>>
>>> does someone have 'good' instructions on what i need and how i need to
>>> install SQLite on Windows XP?
>>>
>>> I eventually want to create a desktop application to run on SQLite, but
>>> not
>>> sure what language i want to use yet (pythong, ruby, etc...)
>>>
>>> the instructions on the SQLite website are pretty crappy to say the
>>> least.
>>
>> Well, declaring the sqlite website to be crappy is not going to win
>> you many friends here. Remember, it is all free, so if you think it
>> lacks in some way, offer some constructive criticism instead. Of
>> course, in reality, the sqlite website actually is quite the opposite
>> of crappy. I just downloaded the "zip file" (there are several of
>> them), and I got exactly what was stated on the tin -- the sqlite3.exe
>> executable.
>>
>> You have to decide what you want -- the executable is a command line
>> program. There is also a dll and other such paraphernalia that Windows
>> folks require... I am sure they will help you out, but please don't
>> put them off by calling the site crappy.
>>
>>
>>> When i download the zip file, all i get is 3 txt files???
>>>
>>> thanks
>>> Scott
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Installing-SQLite-tp24614036p24614036.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Installing-SQLite-tp24614036p24614205.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Reid Thompson]

On Wed, Jul 22, 2009 at 01:30:58PM -0700, scabral wrote:
>
> Hi,
>
> does someone have 'good' instructions on what i need and how i need to
> install SQLite on Windows XP?
>
> I eventually want to create a desktop application to run on SQLite, but not
> sure what language i want to use yet (pythong, ruby, etc...)
>
> the instructions on the SQLite website are pretty crappy to say the least.
> When i download the zip file, all i get is 3 txt files???
>

you did something wrong.  This file

http://sqlite.org/sqlite-3_6_16.zip

from  http://sqlite.org/download.html
contains the precompiled windows binary
and this page, referenced from where the above link is
http://sqlite.org/sqlite.html
tells you how to get started

utilizing sqlite from a language requires this file
http://sqlite.org/sqlitedll-3_6_16.zip
and you have to install the appropriate bindings for your lang of choice
or configure your compilation to utilize the dll
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[P Kishor]

On Wed, Jul 22, 2009 at 3:42 PM, scabral wrote:
>
> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>
> sqlite3   C File
> sqlite3   H File
> sqlite3ext H File
>
> what am i supposed to do with those?
>
> Is there no 'true' install?

Read that entire page carefully before clicking on the first available
link.. you will find what you need. This kind of trigger-happy
clicking will get your computer infected with some nasty virus.


>
> Scott
>
> P Kishor-3 wrote:
>>
>> On Wed, Jul 22, 2009 at 3:30 PM, scabral wrote:
>>>
>>> Hi,
>>>
>>> does someone have 'good' instructions on what i need and how i need to
>>> install SQLite on Windows XP?
>>>
>>> I eventually want to create a desktop application to run on SQLite, but
>>> not
>>> sure what language i want to use yet (pythong, ruby, etc...)
>>>
>>> the instructions on the SQLite website are pretty crappy to say the
>>> least.
>>
>> Well, declaring the sqlite website to be crappy is not going to win
>> you many friends here. Remember, it is all free, so if you think it
>> lacks in some way, offer some constructive criticism instead. Of
>> course, in reality, the sqlite website actually is quite the opposite
>> of crappy. I just downloaded the "zip file" (there are several of
>> them), and I got exactly what was stated on the tin -- the sqlite3.exe
>> executable.
>>
>> You have to decide what you want -- the executable is a command line
>> program. There is also a dll and other such paraphernalia that Windows
>> folks require... I am sure they will help you out, but please don't
>> put them off by calling the site crappy.
>>
>>
>>> When i download the zip file, all i get is 3 txt files???
>>>
>>> thanks
>>> Scott
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Installing-SQLite-tp24614036p24614036.html
>>> Sent from the SQLite mailing list archive at Nabble.com.
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Installing-SQLite-tp24614036p24614205.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Installing SQLite

[scabral]

When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:

sqlite3   C File
sqlite3   H File
sqlite3ext H File

what am i supposed to do with those?

Is there no 'true' install?

Scott

P Kishor-3 wrote:
> 
> On Wed, Jul 22, 2009 at 3:30 PM, scabral wrote:
>>
>> Hi,
>>
>> does someone have 'good' instructions on what i need and how i need to
>> install SQLite on Windows XP?
>>
>> I eventually want to create a desktop application to run on SQLite, but
>> not
>> sure what language i want to use yet (pythong, ruby, etc...)
>>
>> the instructions on the SQLite website are pretty crappy to say the
>> least.
> 
> Well, declaring the sqlite website to be crappy is not going to win
> you many friends here. Remember, it is all free, so if you think it
> lacks in some way, offer some constructive criticism instead. Of
> course, in reality, the sqlite website actually is quite the opposite
> of crappy. I just downloaded the "zip file" (there are several of
> them), and I got exactly what was stated on the tin -- the sqlite3.exe
> executable.
> 
> You have to decide what you want -- the executable is a command line
> program. There is also a dll and other such paraphernalia that Windows
> folks require... I am sure they will help you out, but please don't
> put them off by calling the site crappy.
> 
> 
>> When i download the zip file, all i get is 3 txt files???
>>
>> thanks
>> Scott
>> --
>> View this message in context:
>> http://www.nabble.com/Installing-SQLite-tp24614036p24614036.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Installing-SQLite-tp24614036p24614205.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Igor Tandetnik]

Zachary Turner 
wrote:
> I still don't understand the transactions.  For example, I issue a
> single BEGIN at the start of my application and then insert about
> 500MB of data through many small inserts (about 4KB each).  During
> this whole time I never issue a commit.  But the main db file grows
> very large, and the journal file remains small.  Is sqlite manually
> forcing commits for me at some fixed threshold?

SQLite uses an undo journal: changes are written to the database file, 
and the journal keeps the original pages. Committing a transaction 
simply means discarding the journal; rolling back means copying saved 
pages from journal back to database file.

Your massive insert operation doesn't modify many pages in the database 
file - it mostly creates new ones by extending the file. For this case, 
the only thing one needs to store in the journal is the original size of 
the database, so that the file could be truncated on rollback. That's 
why the journal file remains the same size even as the main file grows.

Igor Tandetnik 



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

Re: [sqlite] Installing SQLite

[P Kishor]

On Wed, Jul 22, 2009 at 3:30 PM, scabral wrote:
>
> Hi,
>
> does someone have 'good' instructions on what i need and how i need to
> install SQLite on Windows XP?
>
> I eventually want to create a desktop application to run on SQLite, but not
> sure what language i want to use yet (pythong, ruby, etc...)
>
> the instructions on the SQLite website are pretty crappy to say the least.

Well, declaring the sqlite website to be crappy is not going to win
you many friends here. Remember, it is all free, so if you think it
lacks in some way, offer some constructive criticism instead. Of
course, in reality, the sqlite website actually is quite the opposite
of crappy. I just downloaded the "zip file" (there are several of
them), and I got exactly what was stated on the tin -- the sqlite3.exe
executable.

You have to decide what you want -- the executable is a command line
program. There is also a dll and other such paraphernalia that Windows
folks require... I am sure they will help you out, but please don't
put them off by calling the site crappy.


> When i download the zip file, all i get is 3 txt files???
>
> thanks
> Scott
> --
> View this message in context: 
> http://www.nabble.com/Installing-SQLite-tp24614036p24614036.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Installing SQLite

[Nuno Magalhães]

On Wed, Jul 22, 2009 at 21:30, scabral wrote:

[...]

> the instructions on the SQLite website are pretty crappy to say the least.
> When i download the zip file, all i get is 3 txt files???

[...]

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

[sqlite] Installing SQLite

[scabral]

Hi,

does someone have 'good' instructions on what i need and how i need to
install SQLite on Windows XP?  

I eventually want to create a desktop application to run on SQLite, but not
sure what language i want to use yet (pythong, ruby, etc...)

the instructions on the SQLite website are pretty crappy to say the least. 
When i download the zip file, all i get is 3 txt files???

thanks
Scott
-- 
View this message in context: 
http://www.nabble.com/Installing-SQLite-tp24614036p24614036.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Ability to convert Access to SQLite

[Simon Slavin]

On 22 Jul 2009, at 6:29pm, scabral wrote:

> The OS is Windows XP.  I know some VB script, some pearl and i'v  
> used Visual
> VB and C# as well.

Post on programming fora about VB (I assume you mean Visual Basic)  
(whichever version you're using) about accessing SQLite databases.   
They'll tell you how to get started.

Do not get involved in the C# interface to SQLite unless you're  
willing to spend many hours writing your program.  The way you asked  
your question suggests you don't have the experience you'd need.

Note: it's important that you understand that if you have multiple  
computers all accessing data in one database, there will be a server  
involved here.  Whether that server is a file server, or a web server,  
or you write one yourself, you won't be able to get away from having  
one somewhere.

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

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Zachary Turner]

On Wed, Jul 22, 2009 at 1:42 PM, Pavel Ivanov wrote:
>
> Again, try to call sqlite3_memory_used() several times during work of
> your application. What does it say to you?
It says it's using about 3MB.  Which suggests maybe it's my application.

I believe I've identified the problem in my code but it takes some
work to fix so I can't report with 100% certainty how much of a
difference it will make.

> If you don't do commits (and "begins") of transactions by yourself
> then SQLite does that automatically after each executed statement. So
> when sqlite3_step returns you can be sure that everything is committed
> and everything is on disk. SQLite doesn't do any write-through
> caching. And as your transactions are small in volume then journal
> size is also always small.

I still don't understand the transactions.  For example, I issue a
single BEGIN at the start of my application and then insert about
500MB of data through many small inserts (about 4KB each).  During
this whole time I never issue a commit.  But the main db file grows
very large, and the journal file remains small.  Is sqlite manually
forcing commits for me at some fixed threshold?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The SQL Guide to SQLite

[Guido Ostkamp]

Robert Citek  wrote:
> Anyone know of a way to preview the index, the table of contest, or
> some sample chapters?  I tried viewing form the Lulu site, but no
> luck.

It appears it has not been scanned by Google yet. However a limited
preview of the other book "The Definite Guide to SQLite" can be found
at


Regards

Guido

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

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Pavel Ivanov]

> Assuming I don't do any manual commits, what does sqlite do with the
> data that has yet to be committed?

If you don't do commits (and "begins") of transactions by yourself
then SQLite does that automatically after each executed statement. So
when sqlite3_step returns you can be sure that everything is committed
and everything is on disk. SQLite doesn't do any write-through
caching. And as your transactions are small in volume then journal
size is also always small.

> Unfortunately I don't have access
> to memory leak detection tools, otherwise that would obviously be
> ideal.

Again, try to call sqlite3_memory_used() several times during work of
your application. What does it say to you?

Pavel

On Wed, Jul 22, 2009 at 2:30 PM, Zachary Turner wrote:
> On Wed, Jul 22, 2009 at 10:47 AM, Pavel Ivanov wrote:
>> SQLite synchronizes with disk during every commit (either issued by
>> yourself or automatic) at least 2 times (I don't know exact number).
>> So it's quite natural that it spends most of the time in winSync().
>> But I still didn't understand from your explanation how exactly your
>> application works and whether it's SQLite uses memory or your
>> application does.
>> BTW, how do you measure memory usage and how do you see leakage? What
>> does sqlite3_memory_used() returns for you?
>>
>> Pavel
>
> I was measuring memory usage by just looking at windows task manager.
> If I watch it for about 20 seconds, it goes up indefinitely until I
> stop reading more data from the file (and thus stop issuing insert
> statements), at which point it steadly declines for a while.
>
> Assuming I don't do any manual commits, what does sqlite do with the
> data that has yet to be committed?  I thought it would store it in the
> journal file, but the journal file always remains consistently very
> small (around 8K max), and data gets written to the actual database
> file even when I'm not doing commits.
>
> I have some ideas about the memory consumption problem that turns out
> to be related to my own code (I agree it's amazingly complicated, but
> it has to be for reasons outside of what we're doing with sqlite).  I
> will investigate that further and post back if I am able to pinpoint
> the issue to sqlite more closely.  Unfortunately I don't have access
> to memory leak detection tools, otherwise that would obviously be
> ideal.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Zachary Turner]

On Wed, Jul 22, 2009 at 10:47 AM, Pavel Ivanov wrote:
> SQLite synchronizes with disk during every commit (either issued by
> yourself or automatic) at least 2 times (I don't know exact number).
> So it's quite natural that it spends most of the time in winSync().
> But I still didn't understand from your explanation how exactly your
> application works and whether it's SQLite uses memory or your
> application does.
> BTW, how do you measure memory usage and how do you see leakage? What
> does sqlite3_memory_used() returns for you?
>
> Pavel

I was measuring memory usage by just looking at windows task manager.
If I watch it for about 20 seconds, it goes up indefinitely until I
stop reading more data from the file (and thus stop issuing insert
statements), at which point it steadly declines for a while.

Assuming I don't do any manual commits, what does sqlite do with the
data that has yet to be committed?  I thought it would store it in the
journal file, but the journal file always remains consistently very
small (around 8K max), and data gets written to the actual database
file even when I'm not doing commits.

I have some ideas about the memory consumption problem that turns out
to be related to my own code (I agree it's amazingly complicated, but
it has to be for reasons outside of what we're doing with sqlite).  I
will investigate that further and post back if I am able to pinpoint
the issue to sqlite more closely.  Unfortunately I don't have access
to memory leak detection tools, otherwise that would obviously be
ideal.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexes on the table

[Joanne Pham]

Thanks Simon for detail explaination about the indexes!
JP





From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Tuesday, July 21, 2009 3:57:22 PM
Subject: Re: [sqlite] Indexes on the table


On 21 Jul 2009, at 11:12pm, Joanne Pham wrote:

> CREATE TABLE myTable(
>    startTime INTEGER ...
>    appId INTEGER
>    myId INTEGER ...
>    trafficType INTEGER
> ..
> )
> StartTime can be from 1...59
> appId can be from 1...256
> myId can be from 1...5000
> trafficType can be from 1..3
>
> I would like to create index for this table on these columns  
> StartTime ,appId, myId, trafficType as :
> create unique index myTableIndex on myTable(appId, myId,  
> trafficType, startTime).
> Is the order of the columns in the create index statement  
> importance? If yes then what is rule of thumb here?

You choose what indexes to create depending on what SELECT commands  
you're going to use.  So if none of your SELECT instructions use  
trafficType in the WHERE or ORDER BY clause there is no need for it in  
any index.

Once you know which fields you want in an index, the principle is to  
reject as many rows as you can as soon as you can.  This leaves the  
software fewer records to worry about at the next step, which means it  
needs less memory and has less processing to do.

Suppose you have a thousand records and want something like

SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2

Suppose 1/3rd of your records have each traffic type, but 1/256th of  
your records have each appId.  Then selecting on trafficType first  
would reject 2 records out of every 3, meaning that the next step has  
to process just 333 records, which is good.  But selecting on appId  
first instead would reject 255 records out of every 256, meaning that  
the next step has to process just 4 records which is much better.

So in this case an index on (appId, trafficType) would be research in  
a faster SELECT than (trafficType, appId).

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



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

Re: [sqlite] How do bitwise operators work? Prototype done

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Since the solution is now specific to Python, I suggest continuing on
the pysqlite/APSW mailing list -
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite and
http://news.gmane.org/gmane.comp.python.db.pysqlite.user

Le Hyaric Bruno wrote:
> def blob_and(b1,b2):
> op1 = str(b1)
> op2 = str(b2)
> i = 0
> result = str()
> for char in op1:
> result = result + chr(ord(char) & ord(op2[i]))
> i = i + 1
> return buffer(result)

A few things wrong with this.  There is no need to convert the
parameters to str - you can iterate over the buffers directly.  That is
a horrendous way of doing string concatenation :-)  Lastly this code
assumes that the parameters are exactly the same length - it will throw
an exception if b2 is shorter than b1 and ignore any part of b2 that is
longer than b1!

This code is closer to being conceptually right although there many
optimisations that can be done:

def blob_byte_and(left, right):
if left is None or right is None:
return 0
return left & right

def blob_byte_or(left, right):
if left is None: return right
if right is None: return left
return left | right

def blob_operate(method, left, right):
result=array.array('B', [a for a in map(method, [ord(x) for x in
left], [ord(y) for y in right])])
return buffer(result)

Example use:  blob_operate(blob_byte_and, b1, b2)

If you are using Python 2.5+ then you can replace all the [] with ()
which will drastically cut down on memory usage.  If performance is at
all an issue then you'd get the best bang for the buck by coding the
functions in C which won't require round trips through Python data types
or iteration over them.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpnVPsACgkQmOOfHg372QSVWQCghE/usAqCb4i/uwAzLbN4VJxC
FRgAn21WSl0O9mUsROfCS3mQr/3g7XaC
=AohF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[Alan March]

I would recommend Talend (http://www.talend.com/). It allows for
export/import to/from almost any structured data source (including sqlite
and Access) and has very good scripting capabilities (a visual designer
which generates java or perl code). It doesn't have a scheduler but this may
be managed thru some other tool such as http://jobscheduler.sourceforge.net/
or, as you said, the windows task scheduler.




> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of scabral
> Sent: miércoles, 22 de julio de 2009 02:30 p.m.
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Ability to convert Access to SQLite
> 
> 
> The OS is Windows XP.  I know some VB script, some pearl and i'v used
> Visual
> VB and C# as well.
> 
> I was thinking that the script would called from a windows scheduled
> task
> and the script would then import the text file into the table in
> SQLite...Sounds easy, but i'm not sure what's available to create
> script.
> 
> thanks
> Scott
> 
> Simon Slavin-2 wrote:
> >
> >
> > On 22 Jul 2009, at 5:34pm, scabral wrote:
> >
> >> 1.  Need to automatically import text file into SQLite database on
> >> local
> >> machine (machine always on).  I was thinking of using scheduled task
> >> to run
> >> a script to do this, but not sure if that is possible.
> >
> > What programming languages or scripting languages do you know ?
> Which
> > operating system is that machine using ?
> >
> >> 2.  Neet to somehow create a front-end to query data from SQLite
> >> database
> >> (not sure if SQLite has front end application).
> >
> > It can talk to many many programming languages.  This will be
> > relatively easy, it's getting the data out of Access, and from the
> > text files that's hard.
> >
> > Do your users have access to web browsers ?  Can you write in PHP ?
> > Do you have a web server set up ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> 
> --
> View this message in context: http://www.nabble.com/Ability-to-convert-
> Access-to-SQLite-tp24609886p24610956.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.392 / Virus Database: 270.13.23/2254 - Release Date:
> 07/22/09 05:59:00

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

Re: [sqlite] Ability to convert Access to SQLite

[Rich Shepard]

On Wed, 22 Jul 2009, scabral wrote:

> The OS is Windows XP.  I know some VB script, some pearl and i'v used Visual
> VB and C# as well.
>
> I was thinking that the script would called from a windows scheduled task
> and the script would then import the text file into the table in
> SQLite...Sounds easy, but i'm not sure what's available to create script.

Scott,

   Given your needs and wants, I'll second the suggestion that you first
automate getting the scripts into Access, then work on the conversion to
SQLite.

   BTW, the language is Perl, not Pearl. :-) It's easy to get confused since
those who like it think it's a gem.

   I _strongly_ recommend that you get away from the Microsoft-specific
languages, and use one that works on any platform. While I prefer Python
because it has extensive support for science, mathematics, graphing, etc.
you might want to consider Ruby so you can build a web-hosted application.
Many open source tools have been ported to Microsoft over the years so you
cannot go wrong by learning to use these. Learn to work comfortably in a
terminal so you can write code and administrative tools in a text editor.
You'll not only learn the language better than if you relied on GUI tools to
draw things, but you'll know just what it's doing under the hood. As a
developer you'll want that knowledge and control.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[scabral]

The OS is Windows XP.  I know some VB script, some pearl and i'v used Visual
VB and C# as well.

I was thinking that the script would called from a windows scheduled task
and the script would then import the text file into the table in
SQLite...Sounds easy, but i'm not sure what's available to create script.

thanks
Scott

Simon Slavin-2 wrote:
> 
> 
> On 22 Jul 2009, at 5:34pm, scabral wrote:
> 
>> 1.  Need to automatically import text file into SQLite database on  
>> local
>> machine (machine always on).  I was thinking of using scheduled task  
>> to run
>> a script to do this, but not sure if that is possible.
> 
> What programming languages or scripting languages do you know ?  Which  
> operating system is that machine using ?
> 
>> 2.  Neet to somehow create a front-end to query data from SQLite  
>> database
>> (not sure if SQLite has front end application).
> 
> It can talk to many many programming languages.  This will be  
> relatively easy, it's getting the data out of Access, and from the  
> text files that's hard.
> 
> Do your users have access to web browsers ?  Can you write in PHP ?   
> Do you have a web server set up ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Ability-to-convert-Access-to-SQLite-tp24609886p24610956.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Ability to convert Access to SQLite

[Simon Slavin]

On 22 Jul 2009, at 5:34pm, scabral wrote:

> 1.  Need to automatically import text file into SQLite database on  
> local
> machine (machine always on).  I was thinking of using scheduled task  
> to run
> a script to do this, but not sure if that is possible.

What programming languages or scripting languages do you know ?  Which  
operating system is that machine using ?

> 2.  Neet to somehow create a front-end to query data from SQLite  
> database
> (not sure if SQLite has front end application).

It can talk to many many programming languages.  This will be  
relatively easy, it's getting the data out of Access, and from the  
text files that's hard.

Do your users have access to web browsers ?  Can you write in PHP ?   
Do you have a web server set up ?

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

Re: [sqlite] Ability to convert Access to SQLite

[cmartin]

On Wed, 22 Jul 2009, scabral wrote:

> i am new to SQLite and i currently have an access database that i would like
> to convert over to a SQLite database.
>
> 1.  Need to automatically import text file into SQLite database on local
> machine (machine always on).  I was thinking of using scheduled task to run
> a script to do this, but not sure if that is possible.
> 2.  Neet to somehow create a front-end to query data from SQLite database
> (not sure if SQLite has front end application).
>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).

If your only problem is automating the import of these text files, there 
are simpler solutions than switching the SQLite. You should be able to 
create a scheduled script (VBScript, Python, whatever) that will import 
the text files directly into Access--the work involved will not be 
appreciably different than what will be required to script/program a 
utility to import the data into SQLite. By sticking with Access, once you 
have the script in place everything you currently have should work the 
same as it does now.

Of course, there are possibly many other (good) reasons you want to 
migrate the whole thing to a SQLite backend, if so, proceed, but to 
solve just the problem you describe, you may want to stick to Access and 
just script the import.

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

Re: [sqlite] Ability to convert Access to SQLite

[P Kishor]

On Wed, Jul 22, 2009 at 12:12 PM, scabral wrote:
>
> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine

Applications that build applications? I am sure there must be, but as
you keep on abstracting, you lose control and things get messier.

Forget about local machines and servers.

First, poke around sqlite.org for about 45 mins and you will have
many, many of your questions answered and would have acquired a ton of
background knowledge.

Second, determine what programming language you know best. No matter
what anyone says about Perl being the best language, the most powerful
language is the one you know now.

Third, find out if that language supports working with a database.
That will help you determine whether you want to create a desktop
application or a web-based application, which, by the way, can also
run as a desktop application, that is, on a local machine.

Have fun, come back, ask more questions.


>
> thanks
> Scott
>
> P Kishor-3 wrote:
>>
>> On Wed, Jul 22, 2009 at 11:34 AM, scabral wrote:
>>>
>>> HI,
>>>
>>> i am new to SQLite and i currently have an access database that i would
>>> like
>>> to convert over to a SQLite database.
>>>
>>> there are a couple of processes that would need to take place in order
>>> for
>>> this to work and i'm not sure if SQLite has these capabilities:
>>>
>>> 1.  Need to automatically import text file into SQLite database on local
>>> machine (machine always on).  I was thinking of using scheduled task to
>>> run
>>> a script to do this, but not sure if that is possible.
>>
>> Sure it is possible. You will have to choose your favorite programming
>> language, write a program to locate and import the local text file,
>> and set up a scheduler to do so periodically.
>>
>>> 2.  Neet to somehow create a front-end to query data from SQLite database
>>> (not sure if SQLite has front end application).
>>
>> Lots of third-party applications. You might end up writing your own to
>> suit your own requirements.
>>
>>
>>>
>>> Basically, i want to get rid of the Access database because the user's
>>> have
>>> to manually update the tables everyday from the text files that get ftp'd
>>> over.  I was trying to figure out a solution that would automatically
>>> load
>>> the text files withouth any human intervention and then give the user's
>>> the
>>> ability to view the data through some sort of front-end (similar to
>>> Access
>>> form).
>>>
>>> Thanks
>>> scott
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Ability-to-convert-Access-to-SQLite-tp24609886p24610625.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Ability to convert Access to SQLite

[Rich Shepard]

On Wed, 22 Jul 2009, scabral wrote:

> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine

Scott,

   Each language has its own. However, from your description it appears that
you want to keep the SQLite database on a server yet have multiple users
remotely acess it for data entry, queries, and reporting.

   If this is correct, I strongly recommend that you use Ruby on Rails to
build the system. The UI is any Web browser. Users log in to the application
from any machine with a browser and connection to the Internet (or your
local intranet), and work with a single instance of the database. This
approach makes the system scalable, flexible, and broadly useful.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[Nuno Magalhães]

> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine

That would be OS-dependent now, wouldn't it? Unless you're going to
use Java or PHP or similar. If the former there are JDBC drivers
available.

-- 
()  ascii ribbon campaign - against html e-mail
/\  ascii-rubanda kampajno - kontraŭ html-a retpoŝto
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[scabral]

Do you know of any third party applications that i could use to build the
front end?  This will need to run on local machines, not on servers.  So,
some sort of desktop application i would imagine

thanks
Scott

P Kishor-3 wrote:
> 
> On Wed, Jul 22, 2009 at 11:34 AM, scabral wrote:
>>
>> HI,
>>
>> i am new to SQLite and i currently have an access database that i would
>> like
>> to convert over to a SQLite database.
>>
>> there are a couple of processes that would need to take place in order
>> for
>> this to work and i'm not sure if SQLite has these capabilities:
>>
>> 1.  Need to automatically import text file into SQLite database on local
>> machine (machine always on).  I was thinking of using scheduled task to
>> run
>> a script to do this, but not sure if that is possible.
> 
> Sure it is possible. You will have to choose your favorite programming
> language, write a program to locate and import the local text file,
> and set up a scheduler to do so periodically.
> 
>> 2.  Neet to somehow create a front-end to query data from SQLite database
>> (not sure if SQLite has front end application).
> 
> Lots of third-party applications. You might end up writing your own to
> suit your own requirements.
> 
> 
>>
>> Basically, i want to get rid of the Access database because the user's
>> have
>> to manually update the tables everyday from the text files that get ftp'd
>> over.  I was trying to figure out a solution that would automatically
>> load
>> the text files withouth any human intervention and then give the user's
>> the
>> ability to view the data through some sort of front-end (similar to
>> Access
>> form).
>>
>> Thanks
>> scott
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Ability-to-convert-Access-to-SQLite-tp24609886p24610625.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Ability to convert Access to SQLite

[Rich Shepard]

On Wed, 22 Jul 2009, P Kishor wrote:

> Sure it is possible. You will have to choose your favorite programming
> language, write a program to locate and import the local text file, and
> set up a scheduler to do so periodically.

> Lots of third-party applications. You might end up writing your own to
> suit your own requirements.

   Allow me to expand on these thoughts. SQLite is a database engine that has
a command line interface. However, it is most often used embedded in an
application. In Scott's case, the application would consist of the user
interface, the middleware, and reporting capabilities.

   Just about any programming language can be used. I prefer python but ruby
is also quick, easy, and reliable. The UI would have whatever is wanted or
needed by the users. The middleware controls the acquisition and import of
the text files can be run as a cron job or by selecting a menu item. Any
needed reports can be written in the tools available for that language.

   There are a suite of tools called 'mdb' that facilitate migration of data
from Access to real RDBMSs. I've not used them so cannot comment on their
effectiveness.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[P Kishor]

On Wed, Jul 22, 2009 at 11:34 AM, scabral wrote:
>
> HI,
>
> i am new to SQLite and i currently have an access database that i would like
> to convert over to a SQLite database.
>
> there are a couple of processes that would need to take place in order for
> this to work and i'm not sure if SQLite has these capabilities:
>
> 1.  Need to automatically import text file into SQLite database on local
> machine (machine always on).  I was thinking of using scheduled task to run
> a script to do this, but not sure if that is possible.

Sure it is possible. You will have to choose your favorite programming
language, write a program to locate and import the local text file,
and set up a scheduler to do so periodically.

> 2.  Neet to somehow create a front-end to query data from SQLite database
> (not sure if SQLite has front end application).

Lots of third-party applications. You might end up writing your own to
suit your own requirements.


>
> Basically, i want to get rid of the Access database because the user's have
> to manually update the tables everyday from the text files that get ftp'd
> over.  I was trying to figure out a solution that would automatically load
> the text files withouth any human intervention and then give the user's the
> ability to view the data through some sort of front-end (similar to Access
> form).
>
> Thanks
> scott

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

[sqlite] Ability to convert Access to SQLite

[scabral]

HI,

i am new to SQLite and i currently have an access database that i would like
to convert over to a SQLite database.

there are a couple of processes that would need to take place in order for
this to work and i'm not sure if SQLite has these capabilities:

1.  Need to automatically import text file into SQLite database on local
machine (machine always on).  I was thinking of using scheduled task to run
a script to do this, but not sure if that is possible.
2.  Neet to somehow create a front-end to query data from SQLite database
(not sure if SQLite has front end application).

Basically, i want to get rid of the Access database because the user's have
to manually update the tables everyday from the text files that get ftp'd
over.  I was trying to figure out a solution that would automatically load
the text files withouth any human intervention and then give the user's the
ability to view the data through some sort of front-end (similar to Access
form).

Thanks
scott
-- 
View this message in context: 
http://www.nabble.com/Ability-to-convert-Access-to-SQLite-tp24609886p24609886.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Marcus Grimm]

just a point:
In your pseudocode you precreate statements for the
begin and commit and trying to reuse them later on.
If your real code is like this I would suggest to
take a look at this. I think it is not possible
to "re-step" a begin or commit, you may try to
recreate these statements completely.
Maybe that is a reason you are seeing a lot of file-syncs
because the begin/commit mechanism doesn't actually
work as intended by you ?

Marcus

Zachary Turner wrote:
> On Tue, Jul 21, 2009 at 6:45 PM, Pavel Ivanov wrote:
>>> If I remove or comment out the
>>> three lines with //* in the code above, I get no memory leaks.
>> So basically you're saying that if you don't insert any data into your
>> database and thus effectively don't do with your database anything and
>> thus SQLite don't have to cache anything from database then you
>> observe no memory leaks, right? Is it by any chance a growing database
>> cache is what you see? What if you execute at the very beginning of
>> your application this:
>> pragma cache_size = 1;
>>
>> Pavel
> 
> Thanks for your suggestion.  I didn't have too much luck with it
> unfortunately, but I did learn a few more things about the problem.
> Just to make sure I understood you though, after creating the database
> with sqlite3_open_v2 I ran the command
> 
> sqlite3_exec(db_, "pragma cache_size=1;");
> 
> The return value was successful so I assume it worked.
> 
> This doesn't appear to change the allocation behavior.  I feel like it
> might be related to me issuing INSERT queries too fast for sqlite to
> deal with.  This is asynchronous (not using the experimental Sqlite
> asynchronous vfs but a completely different async model I wrote
> myself) and the thread that issues inserts to sqlite is different than
> the thread that reads data from the file.
> 
> I changed the code a little so that it permanently stops reading more
> data from the file after some fixed amount (I chose about 500MB) but
> continues writing until all data has been committed to the database.
> when I do this, I notice that after I stop my reads, it takes a *long
> time* before all the writes are finished in sqlite.  99% of this time
> is spent in the winSync() function in the sqlite source code
> constantly calling FlushFileBuffers().
> 
> Upon reflection it makes sense that this would happen in an
> asynchronous environment, but I thought that after a certain threshold
> running an insert statement on a non-in-memory table would generate a
> sync and simply block my sqlite background thread until there was no
> more buffered data.
> 
> In my async model I post messages between threads, so for example the
> main thread posts a message to the sqlite thread saying "write this
> data", which runs the pseudocode I posted in the OP, and after that's
> done it posts a message to the main thread saying "I'm done writing
> that data".  the main thread only issues new read requests as a result
> of getting that message, so if sqlite were syncing every so often then
> I could guarantee that I wouldn't be in this situation because the
> main thread wouldn't be able to issue any more reads until the sync
> was done.
> 
> 
> Am I misunderstanding sqlite's filesystem sync'ing algorithm?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Pavel Ivanov]

SQLite synchronizes with disk during every commit (either issued by
yourself or automatic) at least 2 times (I don't know exact number).
So it's quite natural that it spends most of the time in winSync().
But I still didn't understand from your explanation how exactly your
application works and whether it's SQLite uses memory or your
application does.
BTW, how do you measure memory usage and how do you see leakage? What
does sqlite3_memory_used() returns for you?

Pavel

On Wed, Jul 22, 2009 at 11:26 AM, Zachary Turner wrote:
> On Tue, Jul 21, 2009 at 6:45 PM, Pavel Ivanov wrote:
>>> If I remove or comment out the
>>> three lines with //* in the code above, I get no memory leaks.
>>
>> So basically you're saying that if you don't insert any data into your
>> database and thus effectively don't do with your database anything and
>> thus SQLite don't have to cache anything from database then you
>> observe no memory leaks, right? Is it by any chance a growing database
>> cache is what you see? What if you execute at the very beginning of
>> your application this:
>> pragma cache_size = 1;
>>
>> Pavel
>
> Thanks for your suggestion.  I didn't have too much luck with it
> unfortunately, but I did learn a few more things about the problem.
> Just to make sure I understood you though, after creating the database
> with sqlite3_open_v2 I ran the command
>
> sqlite3_exec(db_, "pragma cache_size=1;");
>
> The return value was successful so I assume it worked.
>
> This doesn't appear to change the allocation behavior.  I feel like it
> might be related to me issuing INSERT queries too fast for sqlite to
> deal with.  This is asynchronous (not using the experimental Sqlite
> asynchronous vfs but a completely different async model I wrote
> myself) and the thread that issues inserts to sqlite is different than
> the thread that reads data from the file.
>
> I changed the code a little so that it permanently stops reading more
> data from the file after some fixed amount (I chose about 500MB) but
> continues writing until all data has been committed to the database.
> when I do this, I notice that after I stop my reads, it takes a *long
> time* before all the writes are finished in sqlite.  99% of this time
> is spent in the winSync() function in the sqlite source code
> constantly calling FlushFileBuffers().
>
> Upon reflection it makes sense that this would happen in an
> asynchronous environment, but I thought that after a certain threshold
> running an insert statement on a non-in-memory table would generate a
> sync and simply block my sqlite background thread until there was no
> more buffered data.
>
> In my async model I post messages between threads, so for example the
> main thread posts a message to the sqlite thread saying "write this
> data", which runs the pseudocode I posted in the OP, and after that's
> done it posts a message to the main thread saying "I'm done writing
> that data".  the main thread only issues new read requests as a result
> of getting that message, so if sqlite were syncing every so often then
> I could guarantee that I wouldn't be in this situation because the
> main thread wouldn't be able to issue any more reads until the sync
> was done.
>
>
> Am I misunderstanding sqlite's filesystem sync'ing algorithm?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak on inserting from sqlite c/c++ interfaces

[Zachary Turner]

On Tue, Jul 21, 2009 at 6:45 PM, Pavel Ivanov wrote:
>> If I remove or comment out the
>> three lines with //* in the code above, I get no memory leaks.
>
> So basically you're saying that if you don't insert any data into your
> database and thus effectively don't do with your database anything and
> thus SQLite don't have to cache anything from database then you
> observe no memory leaks, right? Is it by any chance a growing database
> cache is what you see? What if you execute at the very beginning of
> your application this:
> pragma cache_size = 1;
>
> Pavel

Thanks for your suggestion.  I didn't have too much luck with it
unfortunately, but I did learn a few more things about the problem.
Just to make sure I understood you though, after creating the database
with sqlite3_open_v2 I ran the command

sqlite3_exec(db_, "pragma cache_size=1;");

The return value was successful so I assume it worked.

This doesn't appear to change the allocation behavior.  I feel like it
might be related to me issuing INSERT queries too fast for sqlite to
deal with.  This is asynchronous (not using the experimental Sqlite
asynchronous vfs but a completely different async model I wrote
myself) and the thread that issues inserts to sqlite is different than
the thread that reads data from the file.

I changed the code a little so that it permanently stops reading more
data from the file after some fixed amount (I chose about 500MB) but
continues writing until all data has been committed to the database.
when I do this, I notice that after I stop my reads, it takes a *long
time* before all the writes are finished in sqlite.  99% of this time
is spent in the winSync() function in the sqlite source code
constantly calling FlushFileBuffers().

Upon reflection it makes sense that this would happen in an
asynchronous environment, but I thought that after a certain threshold
running an insert statement on a non-in-memory table would generate a
sync and simply block my sqlite background thread until there was no
more buffered data.

In my async model I post messages between threads, so for example the
main thread posts a message to the sqlite thread saying "write this
data", which runs the pseudocode I posted in the OP, and after that's
done it posts a message to the main thread saying "I'm done writing
that data".  the main thread only issues new read requests as a result
of getting that message, so if sqlite were syncing every so often then
I could guarantee that I wouldn't be in this situation because the
main thread wouldn't be able to issue any more reads until the sync
was done.


Am I misunderstanding sqlite's filesystem sync'ing algorithm?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do bitwise operators work? Prototype done

[Le Hyaric Bruno]

Hi thanks again Igor,

I've successfully use sqlite3 on a quite real volume of data with custom 
bitwise functions for data selection.
Here is the script Python 2.5 that serves as a proof of concept :

import os
import sqlite3



def blob_and(b1,b2):
op1 = str(b1)
op2 = str(b2)
i = 0
result = str()
for char in op1:
result = result + chr(ord(char) & ord(op2[i]))
i = i + 1
return buffer(result)



def blob_and_not_null(b1,b2):
result = blob_and(b1,b2)
for byte in result:
if ord(byte)!=0:
return 1
return 0



def select(requester,request):
print 'Selected by "' + request[:60] + '" : '
for row in requester.execute(request):
print '\t' + str(row[0])



def create_blob_from_node_list(length, arg):
if isinstance(arg,int):
nodes = [ arg ]
if isinstance(arg,list):
nodes = arg
nodes.sort()
result = str()
byte = 0
offset = 0
for node in nodes:
if node/8 > offset:
byte_char = hex(byte)[2:]
if len(byte_char)==1:
byte_char = '0'+ byte_char
result = result + byte_char
result = result + '00' * (node/8 - (len(result)/2))
byte = 0
offset = node/8
byte = byte | (0x80>>node%8)
byte_char = hex(byte)[2:]
if len(byte_char)==1:
byte_char = '0'+ byte_char
result = result + byte_char
result = result + '00' * (length/8 - (len(result)/2))
return "X'" + result + "'"



if(os.path.isfile('test_coveragedb.db')):
os.unlink('test_coveragedb.db')
connection = sqlite3.connect('test_coveragedb.db')
requester = connection.cursor()
request = 'CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, bits 
BLOB);'
print request
requester.execute(request)

blob = create_blob_from_node_list(20, [])
request = 'INSERT INTO test(id,bits) VALUES(NULL, ' + blob + ');'
print request[:60]
requester.execute(request)
for i in range(0,999):
blob = create_blob_from_node_list(20, [9,i])
request = 'INSERT INTO test(id,bits) VALUES(NULL, ' + blob + ');'
print request[:60]
requester.execute(request)
connection.commit()
print 'DB created and filled...'

connection.create_function('blob_and',2,blob_and)
print 'blob_and() function added...'
connection.create_function('blob_and_not_null',2,blob_and_not_null)
print 'blob_and_not_null() function added...'

print '[TEST] Selection of all tests which activate one node or more :'
select(requester,'SELECT id FROM test WHERE blob_and_not_null(bits,X\'' 
+ 'FF' * 25000 + '\');')

print '[TEST] Selection of all tests which don\'t activate any node :'
select(requester,'SELECT id FROM test WHERE 
NOT(blob_and_not_null(bits,X\'' + 'FF' * 25000 + '\'));')

print '[TEST] Selection of the test no 11 knowing it activates the 10th 
node :'
select(requester,'SELECT id FROM test WHERE bits=' + 
create_blob_from_node_list(20,[9]) + ';')

print '[TEST] Selection of all nodes which activate the 10th node :'
select(requester,'SELECT id FROM test WHERE blob_and_not_null(bits,' + 
create_blob_from_node_list(20,[9]) + ');')

requester.close()
connection.close()
print 'End of execution...'

Sorry, there is no comment.

I will try blob_open() and blob_read() later, to see if it really 
increases performances...


Best regards,

Bruno.

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

[sqlite] Determine if UPDATE has worked.

[Steve Martin]

Hi List,

I am new to sqlite and having trouble determining if an update has worked.

I am using the C/C++ API.

For an update, when sqlite3_step is executed it returns SQLITE_DONE when 
a record is updated or if a record does not exist.  I have not found an 
answer by reading and  searching the documentation and searching the 
internet

This is the same for the command line tool.

Using code based on "http://www.sqlite.org/cvstrac/wiki?p=SimpleCode;

$ fred testdb "create table bob(p1 text PRIMARY KEY, p2 text)"
SQLITE_DONE: 101 : unknown error
$ fred testdb "insert into bob (p1, p2) values('foo', 'sue')"
SQLITE_DONE: 101 : unknown error
$ fred testdb "insert into bob (p1, p2) values('foo', 'sue')"
Error: 19 : constraint failed
$ fred testdb "update bob set p2 = 'fred' where p1 = 'foo'"
SQLITE_DONE: 101 : unknown error <-- update ok
$ fred testdb "update bob set p2 = 'fred' where p1 = 'fo1'"  
SQLITE_DONE: 101 : unknown error <-- update fail
$ fred testdb "select count(*) from bob"
count(*) = 1
SQLITE_DONE: 101 : unknown error
$ fred testdb "select * from bob"
p1 = foo
p2 = fred
SQLITE_DONE: 101 : unknown error
$

When using the command line tool.
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table bob(p1 text PRIMARY KEY, p2 text);
sqlite> .header on
sqlite> .show
 echo: off
  explain: off
  headers: on
 mode: list
nullvalue: ""
   output: stdout
separator: "|"
width:
sqlite> update bob set p2 = 'fred' where p1 = 'foo';
sqlite> insert into bob (p1, p2, p3) values('foo', 'sue', 'todd');
SQL error: table bob has no column named p3
sqlite> insert into bob (p1, p2) values('foo', 'sue');
sqlite> select * from bob;
p1|p2
foo|sue
sqlite> update bob set p2 = 'fred' where p1 = 'foo';
sqlite> select * from bob;
p1|p2
foo|fred
sqlite> update bob set p2 = 'fred' where p1 = 'fo1';
sqlite>


Thanks
Steve


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