[sqlite] SQLite with client/server support

[Stephen Woodbridge]

This looks interesting:
http://sqlitedbms.sourceforge.net/index.htm

Alexey would this work for your multi-master replication? Maybe you 
modify this to work for the specific task you have in mind.

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

Re: [sqlite] A question about the use of localtime

[Rich Rattanni]

Matthew:
I simply called tzset() after I extract the timezone file.  It worked
like a charm.  Thank you very much!  I was having a heck of a time
information online.

On Thu, Jun 19, 2008 at 4:53 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote:
> On Thu, Jun 19, 2008 at 4:35 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote:
>>
>> I think this behavior is probably due to the way localtime() works in
>> glibc.  From what I've seen (at least on my embedded ARM-Linux board),
>> localtime() only invokes tzset() the first time it's run by an
>> application.  So if your app starts and calls localtime() before the
>> timezone is changed, you'll get times formatted according to the old
>> timezone.
>>
>
> Correction - that's what happens when localtime_r() is called;
> localtime() is guaranteed to call tzset() on each invocation.
>
> So one option here is to just disable use of localtime_r(), since
> presumably the configure script detects it and defines
> HAVE_LOCALTIME_R in config.h.
>
> --
> Matthew L. Creech
> ___
> 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] A question about the use of localtime

[Matthew L. Creech]

On Thu, Jun 19, 2008 at 4:35 PM, Matthew L. Creech <[EMAIL PROTECTED]> wrote:
>
> I think this behavior is probably due to the way localtime() works in
> glibc.  From what I've seen (at least on my embedded ARM-Linux board),
> localtime() only invokes tzset() the first time it's run by an
> application.  So if your app starts and calls localtime() before the
> timezone is changed, you'll get times formatted according to the old
> timezone.
>

Correction - that's what happens when localtime_r() is called;
localtime() is guaranteed to call tzset() on each invocation.

So one option here is to just disable use of localtime_r(), since
presumably the configure script detects it and defines
HAVE_LOCALTIME_R in config.h.

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

Re: [sqlite] A question about the use of localtime

[Matthew L. Creech]

On Thu, Jun 19, 2008 at 3:28 PM, Rich Rattanni <[EMAIL PROTECTED]> wrote:
>
> So this email isn't blaming SQLite for my improper timestamps even
> after I set the correct zoneinfo file.  I am just trying to
> understand what is going on.  Should I assume that my observation of
> 'Set zoneinfo first, then use datetime functions' is
> a valid fix?  Or am I fooling myself?
> I just wanted to know the mechanism at work here.  Of course I do
> fully acknowledge this is could be an artifact in the
> GLIBC library.  If someone knows the particular reason this is
> happening, even if it is GLIBC fault, I would love to know.
>

I think this behavior is probably due to the way localtime() works in
glibc.  From what I've seen (at least on my embedded ARM-Linux board),
localtime() only invokes tzset() the first time it's run by an
application.  So if your app starts and calls localtime() before the
timezone is changed, you'll get times formatted according to the old
timezone.

Our solution was to simply call tzset() before calling localtime(),
since it wasn't in any sort of critical path.  That's probably not a
good solution in SQLite, but as a start, you may want to try calling
tzset() yourself before each time you modify the flags, and see if
that fixes the problem.

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

[sqlite] A question about the use of localtime

[Rich Rattanni]

All:

I noticed the following when using sqlite to timestamp flags in an
embedded system.  I will lay out the tests performed and the results I
got.

First, an overview.  I am working with a linux 2.6.26 kernel and
sqlite v3.5.0.  For the handling of timezones, I use the zoneinfo
files.  /etc/localtime
is a symbolic link to /var/tmp/localtime.  At boot the proper timezone
file is copied to /var/tmp/localtime (the reason for this setup is
/etc resides
in a read only partition.  I timestamp each flag using 'Insert into
flags (timestamp, FLAGDATA...) values (datetime('now','localtime),
FLAGDATA)'.
Now on to my issue.

--Scenario A--
Boot System
Update /var/localtime with correct zoneinfo file
Write Flag(s)
--
Result All time stamps match my localtime.

--Scenario B--
Boot System
Write 1 Flag
Update /var/localtime with correct zoneinfo file
Write a few flags (5-10)
Some time elapses
Write some more flags
---
Result...
The first flag ,written before /var/localtime, is written in UTC.  I
expect this since the symbolic link does not yet point to a valid
zoneinfo file.
The next few flags (5-10 depending on what the system is doing) are
also in UTC, these flags are the same flags written
in Scenario A after the zoneinfo update.
The system may briefly stop writing flags, then write some more due to
a button press or something.
Eventually the flags receive the correct timestamp for my timezone
settings.  I think, but cant say for certain, that the
timestamp becomes 'correct' after a small amount of time elapses.

So this email isn't blaming SQLite for my improper timestamps even
after I set the correct zoneinfo file.  I am just trying to
understand what is going on.  Should I assume that my observation of
'Set zoneinfo first, then use datetime functions' is
a valid fix?  Or am I fooling myself?
I just wanted to know the mechanism at work here.  Of course I do
fully acknowledge this is could be an artifact in the
GLIBC library.  If someone knows the particular reason this is
happening, even if it is GLIBC fault, I would love to know.

My last bit of information which may be relevant is all flags are
written in individual transactions.

Thanks in advance,
Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multy-master replication of SQLite databases

[Alexey Pechnikov]

В сообщении от Thursday 19 June 2008 21:47:12 Stephen Woodbridge написал(а):
> Alexey Pechnikov wrote:
> > В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge 
написал(а):
> >> Alexey Pechnikov wrote:
> >>> Hello!
> >>>
> >>> Is any method for multy-master replication of SQLite databases?
> >>
> >> http://www.google.com/search?num=100=en=1=sqlite++replica
> >>tion
> >
> > Are you really saw solution for  replication in this links?
> > Especially as sync as async.
>
> I did do a search "multi master sqlite replication" and got a different
> set of results, but I did not read all the results so there might not be
> a solution, but if there is it is likely in these results.

I did read a lot of google search results and did not found multy-master 
raplication solution for Sqlite. These are universal messaging services for 
distributed applications building but I did not found sqlite-specific.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regex parsing create statements

[John Stanton]

A limited SQL parser is fairly simple.  You could write the program and 
launch it from a scripting language as a filter program with input and 
output piped.

Dennis Cote wrote:
> BareFeet wrote:
> 
>>So, before I get too far into it, I figured others of you out there  
>>must already have some regexes that are suitable for this, or know of  
>>a simpler approach. Or at worst any regex gurus out there that can  
>>help fine tune the above?
>>
> 
> 
> Regular expressions are generally not powerful enough to parse all the 
> variations that a language like SQL requires. If they were they would be 
> used by language processors such as SQLite.
> 
> What you really need is a parser.
> 
> SQLite uses a parser generated by the program lemon (see 
> http://www.hwaci.com/sw/lemon/) from the source file parse.y. You could 
> take this source file and modify it to suit your purposes, then build 
> your own parser that allows you to collect the information you need as 
> your SQL strings are parsed (instead of generating VDBE op-codes to 
> execute the queries as SQLite does). Unfortunately this may be difficult 
> from a scripting language.
> 
> Some scripting languages may have libraries that allow you to build a 
> parser similar to lemon ( 
> http://search.cpan.org/~yvesp/llg-1.07/LLg.en.pod is an example from 
> PERL's CPAN site).
> 
> Learning and using a parser generator can require a substantial 
> investment of time, but it usually results in more robust and flexible 
> handling of language processing tasks.
> 
> HTH
> Dennis Cote
> ___
> 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] Multy-master replication of SQLite databases

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge написал(а):
>> Alexey Pechnikov wrote:
>>> Hello!
>>>
>>> Is any method for multy-master replication of SQLite databases?
>> http://www.google.com/search?num=100=en=1=sqlite++replication
> 
> Are you really saw solution for  replication in this links? 
> Especially as sync as async.

I did do a search "multi master sqlite replication" and got a different 
set of results, but I did not read all the results so there might not be 
a solution, but if there is it is likely in these results.

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

Re: [sqlite] Long update times

[Jay A. Kreibich]

On Thu, Jun 19, 2008 at 09:20:24AM -0700, Jason Tudor scratched on the wall:
> The blob sizes are about 24KB.

  You do understand the command "UPDATE Objects SET Lock = 1" will
  update all 12,243 rows, right?  And that requires the whole table to
  be sucked in off disk, modified, and written back out?  Even without
  the full sync, that's going to take some time... if the blobs are
  around 24K you're moving at least ~300MB off disk and writing it back.

  How fast do you expect that to be?

> Also, the time I gave was wrong, it was closer to 55 seconds.  I set the
> synchronous flag to 0 (NONE) and the time decreased to 30 seconds.  Problem
> was that the synch flag did not stick.  When I reopened the database, the
> flag was set to 2 (FULL)

  Many (most?) of the PRAGMAs don't stick.  They're designed that way.

   -j


> On Thu, Jun 19, 2008 at 7:21 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> > On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote:
> >
> > > Hello Everyone,
> > >
> > > I was hoping that someone could explain why my updates are taking so
> > > long.
> > > My schema is as follows:
> > >
> > > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type
> > > INTEGER,
> > > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL)
> > >
> > > My table has 12,243 rows.
> > >
> > > when I execute the following:  "UPDATE Objects SET Lock = 1"
> > >
> > > I wait for about 20 seconds.  I would think that the update should
> > > be much
> > > faster.
> > > I tried wrapping the statement with a transaction, but no luck.
> >
> >
> > I routinely do such operations in milliseconds.  Dunno what you are
> > doing wrong.  What hardware are you running on?  How big are the BLOBs
> > in your table.  Can you post the output from running the
> > sqlite3_analyzer utility (available from
> > http://www.sqlite.org/download.html)
> >  on your database file so that we have a better idea of what kind of
> > data we are dealing with?
> >
> > D. Richard Hipp
> > [EMAIL PROTECTED]
> >
> >
> >
> > ___
> > 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

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long update times

[Jason Tudor]

The blob sizes are about 24KB.

I downloaded the sqlite3_analyzer, is there any documentation on this?  It's
just an executable and when I dbl click I get a console window that
disappears.

Also, the time I gave was wrong, it was closer to 55 seconds.  I set the
synchronous flag to 0 (NONE) and the time decreased to 30 seconds.  Problem
was that the synch flag did not stick.  When I reopened the database, the
flag was set to 2 (FULL)

Thanks again for the help



On Thu, Jun 19, 2008 at 7:21 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote:
>
> > Hello Everyone,
> >
> > I was hoping that someone could explain why my updates are taking so
> > long.
> > My schema is as follows:
> >
> > CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type
> > INTEGER,
> > Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL)
> >
> > My table has 12,243 rows.
> >
> > when I execute the following:  "UPDATE Objects SET Lock = 1"
> >
> > I wait for about 20 seconds.  I would think that the update should
> > be much
> > faster.
> > I tried wrapping the statement with a transaction, but no luck.
>
>
> I routinely do such operations in milliseconds.  Dunno what you are
> doing wrong.  What hardware are you running on?  How big are the BLOBs
> in your table.  Can you post the output from running the
> sqlite3_analyzer utility (available from
> http://www.sqlite.org/download.html)
>  on your database file so that we have a better idea of what kind of
> data we are dealing with?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Multy-master replication of SQLite databases

[Alexey Pechnikov]

В сообщении от Thursday 19 June 2008 20:23:22 Stephen Woodbridge написал(а):
> Alexey Pechnikov wrote:
> > Hello!
> >
> > Is any method for multy-master replication of SQLite databases?
>
> http://www.google.com/search?num=100=en=1=sqlite++replication

Are you really saw solution for  replication in this links? 
Especially as sync as async.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] configure syntax error on HP

[D. Richard Hipp]

On Jun 19, 2008, at 11:49 AM, Matt Sergeant wrote:
>
> Note that there are some C++ style comments crept back into the code  
> (I
> noticed in the amalgamation, so I can't give you a direct pointer to
> them). This causes compile failures on stricter C compilers.


Already been fixed.  http://www.sqlite.org/cvstrac/chngview?cn=5207  
and http://www.sqlite.org/cvstrac/tktview?tn=3172

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] configure syntax error on HP

[Dan]

On Jun 19, 2008, at 10:49 PM, Matt Sergeant wrote:

> On Wed, 18 Jun 2008 19:58:02 -0400, D. Richard Hipp wrote:
>>
>> On Jun 18, 2008, at 7:12 PM, Andrea Connell wrote:
>>
>>> I want to use the C API with a C++ class but when I try compiling...
>>>
>>> $ aCC -AA +W829 main.cpp sqlite3.c
>>> main.cpp:
>>> sqlite3.c:
>>> Error 482: "sqlite3.c", line 532 # Array of unknown size; 'const  
>>> char
>>
>> SQLite is written in C, not C++.  You have to use a C compiler to
>> compile it.  If you compile to object code, you can normally link it
>> against C++ code without difficulty.  But you cannot compile SQLite
>> directly using a C++ compiler.
>
> Note that there are some C++ style comments crept back into the  
> code (I
> noticed in the amalgamation, so I can't give you a direct pointer to
> them). This causes compile failures on stricter C compilers.


I think this problem was fixed by [5207]:

   http://www.sqlite.org/cvstrac/chngview?cn=5207

Please post a bug report or a message here if there are others
that need to be removed.

Dan.




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

Re: [sqlite] configure syntax error on HP

[Matt Sergeant]

On Wed, 18 Jun 2008 19:58:02 -0400, D. Richard Hipp wrote:
> 
> On Jun 18, 2008, at 7:12 PM, Andrea Connell wrote:
> 
>> I want to use the C API with a C++ class but when I try compiling...
>> 
>> $ aCC -AA +W829 main.cpp sqlite3.c
>> main.cpp:
>> sqlite3.c:
>> Error 482: "sqlite3.c", line 532 # Array of unknown size; 'const char
> 
> SQLite is written in C, not C++.  You have to use a C compiler to  
> compile it.  If you compile to object code, you can normally link it  
> against C++ code without difficulty.  But you cannot compile SQLite  
> directly using a C++ compiler.

Note that there are some C++ style comments crept back into the code (I 
noticed in the amalgamation, so I can't give you a direct pointer to 
them). This causes compile failures on stricter C compilers.

Matt.

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] configure syntax error on HP

[Andrea Connell]

 
Okay that worked, thanks.

Just curious though... I've compiled C and C++ code together many times.
I've never had a problem before since C is basically a subset of C++.
Why doesn't it work here?


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, June 18, 2008 6:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] configure syntax error on HP


On Jun 18, 2008, at 7:12 PM, Andrea Connell wrote:

> I want to use the C API with a C++ class but when I try compiling...
>
> $ aCC -AA +W829 main.cpp sqlite3.c
> main.cpp:
> sqlite3.c:
> Error 482: "sqlite3.c", line 532 # Array of unknown size; 'const char

SQLite is written in C, not C++.  You have to use a C compiler to
compile it.  If you compile to object code, you can normally link it
against C++ code without difficulty.  But you cannot compile SQLite
directly using a C++ compiler.

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Multy-master replication of SQLite databases

[Stephen Woodbridge]

Alexey Pechnikov wrote:
> Hello!
> 
> Is any method for multy-master replication of SQLite databases?
> 
http://www.google.com/search?num=100=en=1=sqlite++replication
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multy-master replication of SQLite databases

[Alexey Pechnikov]

Hello!

Is any method for multy-master replication of SQLite databases?

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

Re: [sqlite] Long update times

[D. Richard Hipp]

On Jun 19, 2008, at 9:17 AM, Jason Tudor wrote:

> Hello Everyone,
>
> I was hoping that someone could explain why my updates are taking so  
> long.
> My schema is as follows:
>
> CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type  
> INTEGER,
> Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL)
>
> My table has 12,243 rows.
>
> when I execute the following:  "UPDATE Objects SET Lock = 1"
>
> I wait for about 20 seconds.  I would think that the update should  
> be much
> faster.
> I tried wrapping the statement with a transaction, but no luck.


I routinely do such operations in milliseconds.  Dunno what you are  
doing wrong.  What hardware are you running on?  How big are the BLOBs  
in your table.  Can you post the output from running the  
sqlite3_analyzer utility (available from http://www.sqlite.org/download.html) 
  on your database file so that we have a better idea of what kind of  
data we are dealing with?

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Updating a BLOB field

[Dennis Cote]

flakpit wrote:
> 
> The SQL to insert the blob into my database is :
> 
> "INSERT INTO archive (docnumber, document) VALUES (?,?)"
> 
> How to change the syntax to update a blob field? Is it the same as updating
> normal data or a variant on the above?

You can replace the data using an replace statement (an abbreviation of 
insert or replace) assuming that docnumber is the primary key.

   replace into archive (docnumber, document) VALUES (?,?)

Otherwise you would use an update statement to do an update.

   update archive set document = ? where docnumber = ?

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

Re: [sqlite] Regex parsing create statements

[Dennis Cote]

BareFeet wrote:
> So, before I get too far into it, I figured others of you out there  
> must already have some regexes that are suitable for this, or know of  
> a simpler approach. Or at worst any regex gurus out there that can  
> help fine tune the above?
> 

Regular expressions are generally not powerful enough to parse all the 
variations that a language like SQL requires. If they were they would be 
used by language processors such as SQLite.

What you really need is a parser.

SQLite uses a parser generated by the program lemon (see 
http://www.hwaci.com/sw/lemon/) from the source file parse.y. You could 
take this source file and modify it to suit your purposes, then build 
your own parser that allows you to collect the information you need as 
your SQL strings are parsed (instead of generating VDBE op-codes to 
execute the queries as SQLite does). Unfortunately this may be difficult 
from a scripting language.

Some scripting languages may have libraries that allow you to build a 
parser similar to lemon ( 
http://search.cpan.org/~yvesp/llg-1.07/LLg.en.pod is an example from 
PERL's CPAN site).

Learning and using a parser generator can require a substantial 
investment of time, but it usually results in more robust and flexible 
handling of language processing tasks.

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

Re: [sqlite] The APress SQLite Book

[Rich Shepard]

On Wed, 18 Jun 2008, Roger Binns wrote:

> From the few authors I have spoken to, they don't make the index
> themselves.  The publisher does it using someone else and you end up with
> junk, and they bill the author for it!

Roger,

   That's not always the case. Springer would not hire a professional indexer
for my book, so I looked at a few texts on how to create indices, bought and
read one, and went through several iterations until I had an index that I
felt was usable. Not perfect, but not bad for a first attempt. I've not had
any negative feedback on it.

> It is a miracle any computer books are any good.

   I have several with indices that are highly usable. The topics include
wxPython, SQL, PostgreSQL, and a bunch of others. In my experience, the
SQLite book is an unfortunate exception.

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] Getting a table size without counting

[Dennis Cote]

Stephen Woodbridge wrote:
> 
> Well if the index requires fewer page reads then it should be 
> proportionally faster. For example if you can only get 5 rows on a page 
> but 25 index entries, you have 1/5 the number of pages to read.
> 

Yes, that is true, but this effect is offset by the fact that these 
index pages compete with the table pages for the finite space in the 
page cache. The speed of counting is usually only an issue with large 
databases where the table doesn't fit in the cache. When loading index 
pages to speed the count() you are also ejecting table pages that might 
be used for the next data query, and hence slowing that data query down 
because it now has to reload the data pages.

Adding such an index also slows down all insert, update, and delete 
operations because they must modify the index as well as the table.

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

Re: [sqlite] Long delay for delete/select row in database

[Dennis Cote]

Raphaël KINDT wrote:
> 
> All events come sequentially (because it's a time line).
> I can have more than one event during the same time but I use 1 different
> bit to identify each event types.
> 
> I think it's important to use this sequential constraint to do the delete
> request.
> But SQLite3 use a B-Tree algorithm...
> When I delete some rows it's always for oldest events (before a specific
> time = 'Ts').
> I mean I delete all rows before 'Ts' (like a fifo...)
> I don't want to check all my event table but only the oldest events (first
> rows).
> When I find a time greater than 'Ts' I can stop the delete operation.
> 
> Do you think It's better to use one more table for optimization?
> 

A normal index on time will allow SQLite to locate the rows with time 
less than Ts very quickly. I don't see any need for another table to 
optimize this function.

> For example, creating a "summarised" table which could, in 1 row, summarise
> all the events (detailed in the general table)  that have happened in a time
> lapse (= time between
> two rows).  This "summarised" table could be created with the following
> request :
> 
> CREATE TABLE bloc_events(
>bloc_idINTEGER NOT NULL PRIMARY KEY,
>bloc_start_timeREAL,
>events_resume  BLOB
> );
> 
> And now my event table looks like this:
> 
> CREATE TABLE events(
>time   REAL NOT NULL PRIMARY KEY,
>event  BLOB,
>bloc_idINTEGER
> );
> 
> 'events_resume' is a logical 'OR' result of all event types detected in the
> correspondent event groups in the events table.
> 'bloc_start_time' is the start time of a new group of events.
> The difference between two successive bloc_events rows is the group
> precision (maybe 10 minutes)
> To know the oldest event (bloc_id) to delete I check bloc_events table
> first.
> Then I delete row on events table with the same bloc_id found on bloc_events
> table.
> 
> How to create a FOREIGN KEY with SQLite3?
> 

SQLite allows you to use foreign keys for joins, but it does not enforce 
any referential integrity when rows are deleted. There are triggers you 
can add to your table to do this if need be, but it is often just a easy 
to do it in your application. See 
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers for more info.

HTH
Dennis Cote


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

Re: [sqlite] Long delay for delete/select row in database

[Dennis Cote]

Alexey Pechnikov wrote:
> 
> P.S. Is any method for multy-master replication of SQLite databases? 

Nothing built in, but I believe several user have developed their own 
systems to do this. You would probably be better off asking this 
question in a new thread.

Dennis Cote


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

Re: [sqlite] The APress SQLite Book

[Jason Tudor]

> First, the index is completely unusable.

I'll second that,  worst index that I have ever tried to use.

On Wed, Jun 18, 2008 at 11:05 PM, Roger Binns <[EMAIL PROTECTED]> wrote:

> Stefan Arentz wrote:
> > First, the index is completely unusable.
>
> From the few authors I have spoken to, they don't make the index
> themselves.  The publisher does it using someone else and you end up
> with junk, and they bill the author for it!
>
> It is a miracle any computer books are any good.  I suggest the
> following two links for background:
>
> http://philip.greenspun.com/wtr/dead-trees/story.html
>
> http://www.xaprb.com/blog/2008/06/15/what-is-it-like-to-write-a-technical-book/
>
> Roger
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Updating a BLOB field

[flakpit]

I use a language called PureBasic and am playing with BLOB data. Got data
saving successfully but read in these forums that updating is the same. Yes,
I know it's a compiled statement, saving to database is fine, I can even
view my saved blobs so I know my code works..

The SQL to insert the blob into my database is :

"INSERT INTO archive (docnumber, document) VALUES (?,?)"

How to change the syntax to update a blob field? Is it the same as updating
normal data or a variant on the above?
-- 
View this message in context: 
http://www.nabble.com/Updating-a-BLOB-field-tp18008842p18008842.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] Long update times

[Jason Tudor]

Hello Everyone,

I was hoping that someone could explain why my updates are taking so long.
My schema is as follows:

CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT, Type INTEGER,
Lock INTEGER, Data BLOB 3DDataId INTEGER DEFAULT NULL)

My table has 12,243 rows.

when I execute the following:  "UPDATE Objects SET Lock = 1"

I wait for about 20 seconds.  I would think that the update should be much
faster.
I tried wrapping the statement with a transaction, but no luck.

Thanks for your help.
-Tudor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long delay for delete/select row in database

[Raphaël KINDT]

First, thanks for all your answers.
I would like to come back to my question (see first post).

All events come sequentially (because it's a time line).
I can have more than one event during the same time but I use 1 different
bit to identify each event types.

I think it's important to use this sequential constraint to do the delete
request.
But SQLite3 use a B-Tree algorithm...
When I delete some rows it's always for oldest events (before a specific
time = 'Ts').
I mean I delete all rows before 'Ts' (like a fifo...)
I don't want to check all my event table but only the oldest events (first
rows).
When I find a time greater than 'Ts' I can stop the delete operation.

Do you think It's better to use one more table for optimization?

For example, creating a "summarised" table which could, in 1 row, summarise
all the events (detailed in the general table)  that have happened in a time
lapse (= time between
two rows).  This "summarised" table could be created with the following
request :

CREATE TABLE bloc_events(
   bloc_idINTEGER NOT NULL PRIMARY KEY,
   bloc_start_timeREAL,
   events_resume  BLOB
);

And now my event table looks like this:

CREATE TABLE events(
   time   REAL NOT NULL PRIMARY KEY,
   event  BLOB,
   bloc_idINTEGER
);

'events_resume' is a logical 'OR' result of all event types detected in the
correspondent event groups in the events table.
'bloc_start_time' is the start time of a new group of events.
The difference between two successive bloc_events rows is the group
precision (maybe 10 minutes)
To know the oldest event (bloc_id) to delete I check bloc_events table
first.
Then I delete row on events table with the same bloc_id found on bloc_events
table.

How to create a FOREIGN KEY with SQLite3?

Thanks in advance for your answers.

Regards,

Raphaël

-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] la part de Dennis Cote
Envoyé : mardi 17 juin 2008 18:59
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Long delay for delete/select row in database


Raphaël KINDT wrote:
>
> I'm working on a video monitoring program that uses frame grabber (12
> cameras).
> This program records videos and detects some (input/output) events such as
:
> motion detection, blue screens (cut camera cable), I/Os 1 to 24, and much
> more...
>
> I save all events in a database (sqlite3) with an INSERT request directly
> after their detection.
> Here is the table I use:
>
> CREATE TABLE events(
>instantDATETIME,
>instant_msec   SMALLINT UNSIGNED,
>events_int0INT UNSIGNED,
>events_int1INT UNSIGNED,
>events_int2INT UNSIGNED,
>events_int3INT UNSIGNED
> );
>
> As you can see I don't use PRIMARY KEY and INDEX...
> Each bits of events_intx is a particular event detected (1 = detected).
>
> When a video is too old, I delete it.
> But I must delete all events detected during this video record.
> To do that I use a SQL request described hereunder:
>
> DELETE FROM events WHERE ( (instant < datetime('2008-06-16 10:21:55.806'))
> OR ((instant = datetime('2008-06-16 10:21:55.806')) AND (instant_msec <=
> 806)) );
>
> Sometimes a 'client' wants some information of "what's happened yesterday"
> and he sends a request such as:
>
> SELECT * FROM events WHERE ( (instant < datetime('2008-06-16
23:59:59.999'))
> OR ((instant = datetime('2008-06-16 23:59:59.999')) AND (instant_msec <=
> 999)) ) AND ( (instant > datetime('2008-06-16 00:00:00.000')) OR ((instant
=
> datetime('2008-06-16 00:00:00.000')) AND (instant_msec >= 0)) ) ORDER BY
> instant, instant_msec;
>
> All this works well for a 'light' database.
> But I have forced the application to detect a lot of motion detection
events
> (each second during 10 days).
> Thus, each second, my application is using INSERT to add a row to the
event
> table.
> Now my database's size is about 120 MB... Very big...
> When the DELETE (or SELECT) request starts, it takes about 75 seconds to
be
> executed (very, very lng)!!!
>
> How can I reduce this time for a big database?
> I must have a delay that doesn't go up to 40 msec to be efficient.
> Do I have to split my database into several small databases to reduce this
> delay?
> Do I have to create another table and use PRIMARY KEY, INDEX etc?
>

I would recommend that you change your database schema an combine your
timestamp string and subsecond integer field into a single floating
point julian day number that provides both pieces of information in
units of days (since the julian day epoch). The integer portion is the
number of days, the fractional part is the point in the day to very high
resolution (because SQLite uses 64 bit double precision floating point
values). See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
for more info on the julian date support.

 CREATE TABLE events(
time   REAL, -- julian day number
events_int0INT UNSIGNED,

[sqlite] Regex parsing create statements

[BareFeet]

Hi all,

Short question:

Do you have or know where to find some regex (regular expressions) for  
parsing SQLite statements such as create table, create trigger etc  
into their component parameters?


More detail:

SQLite gives us the Name, Tbl_Name and SQL of create statements via  
the SQLite_Master table. For instance, I can get all the triggers via:

select Name, Type, SQL, from SQLite_Master where type = 'trigger';

But as far as I know, there's no way to parse the SQL further, for  
example to show column details (for tables, views and indexes) or  
trigger steps (in triggers). "pragma table_info" does show some basic  
info on tables and views such as column names, but I need the full  
breakdown of the data.

Since SQLite must be parsing this information internally, I would hope  
that the developers would provide some simple hooks to get at the  
information from SQL, such as:

select Name, Type, Constraints, Constraint_Data from  
SQLite_Table_Columns where Entity_Name = 'My Table';

select Name, Type, Join, Expression from SQLite_View_Columns where  
Entity_Name = 'My View';

select Name, Database_Event, Occurs, Event, For_Each, "When" from  
SQLite_Triggers;
select SQL from SQLite_Trigger_Steps where Entity_Name = 'My Trigger'

I am only accessing SQLite from scripting environments such as  
sqlite3, perl, AppleScript, PHP, not C.

I've started working on some regex (regular expressions) to try to  
parse the data out of the SQL returned from SQLite_Master. My regex  
skills aren't great. For instance this is what I have so far to parse  
out the trigger parameters:

(?x)# ignore spaces and comments
\s*create\strigger\s+
["'\[]?(.*?)["'\]]?\s+  # 1 name: extracted from 
possible quotes
(before|after|instead\sof)\s+   # 2 occur: before, after or 
instead of
(
insert|delete|update|(update\sof)\s+# 4 database event
(.*?)   # 5 update of columns
)\s+on\s+["'\[]?(.*?)["'\]]?\s+ # 6 table or view name
(?:for\seach\s(.*?)\s+)?# 7 for each row or statement
(?:when\s+(.*?)\s+)?# 8 when
begin\s+(.*?)\s+end\s*  # 9 trigger steps

It does a fairly good job, for instance parsing this:

create trigger "Orders Entry update Customer"
instead of update of Customer on "Orders Entry"
for each row when new.Customer not null
begin
update Invoices set Customer = new.Customer where ID = old.Invoice;
end

into:

Orders Entry update Customer# 1 name: extracted from 
possible quotes
instead of  # 2 occur: before, after or 
instead of
update of Customer
update of   # 4 database event
Customer# 5 update of fields
Orders Entry# 6 table or view name
row # 7 for each row or statement
new.Customer not null   # 8 when
update Invoices set Customer = ...; # 9 trigger steps

But it has a few problems, such as:

1. returns insert, delete and update in parameter 3, but "update of"  
in parameter 4. I want them consistently in the same output parameter.

2. The dequoting of the entity names (parameter 1 and 6) isn't robust,  
for instance failing if one quote is inside another, such as "My  
table's Name".

So, before I get too far into it, I figured others of you out there  
must already have some regexes that are suitable for this, or know of  
a simpler approach. Or at worst any regex gurus out there that can  
help fine tune the above?

Any help appreciated.

Thanks,
Tom
BareFeet

  --
SQLite apps for Mac OS X compared:
http://www.tandb.com.au/sqlite/compare/

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

Re: [sqlite] Long delay for delete/select row in database

[Alexey Pechnikov]

В сообщении от Thursday 19 June 2008 02:37:57 Dennis Cote написал(а):
> I'm not sure if you even need unique id numbers for these records, or
> why you are concerned that there might be multiple records with the same
>   save_date for that matter? Is this table linked to any others?

I'm using URL /../[save_date] or /../[ROWID] for my site. Well, I can use 
second key for host identity as /../[host_id]/[ROWID] and bind user session 
to the host.

P.S. Is any method for multy-master replication of SQLite databases? 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The APress SQLite Book

[Roger Binns]

Stefan Arentz wrote:
> First, the index is completely unusable.

>From the few authors I have spoken to, they don't make the index
themselves.  The publisher does it using someone else and you end up
with junk, and they bill the author for it!

It is a miracle any computer books are any good.  I suggest the
following two links for background:

http://philip.greenspun.com/wtr/dead-trees/story.html
http://www.xaprb.com/blog/2008/06/15/what-is-it-like-to-write-a-technical-book/

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