Re: [sqlite] Re: Re: Re: Calculating Average Time

[Jonathan Otsuka]

On Nov 19, 2007, at 10:59 PM, Igor Tandetnik wrote:


Jonathan O <[EMAIL PROTECTED]> wrote:

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:
What does the data in time_column look like?

Currently looks like HH:MM:SS or MM:SS.


The latter is going to cause difficulties - SQLite's date/time  
functions are going to interpret it as HH:MM with seconds omitted.  
If you insist on storing strings, normalize them all to HH:MM:SS  
format. Then you can do something like this:


select time(AVG(strftime('%s', time_column))  / 86400.0 - 0.5)
from tablename;


I was trying to figure out how this worked and came up with a better  
solution:


select time(avg(strftime('%s',time_column)), 'unixepoch') from  
tablename;


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

Re: [sqlite] Memory Usage

[Trevor Talbot]

On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote:
> Joe Wilson wrote:

> > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no
> > memory leaks, then garbage collection is irrelevant to the topic of
> > memory fragmentation. It's not like C can employ a copying garbage
> > collector that moves memory blocks after free() without the knowledge
> > or participation of the host program. The malloc() call is where
> > fragmentation happens. Fragmentation in malloc depends on your allocation
> > strategy: first-fit, best-fit, short-lived versus long-lived pools,
> > per-allocation-size pools, statistical prediction, etc. Malloc must
> > try to guess where an allocation must go to try to prevent future
> > memory fragmentation.

> If you never execute a free your dynamic memory is essentially contiguous.

Not necessarily, and that was his point about where fragmentation happens.

Many of the common allocators maintain multiple size classes to reduce
degenerative fragmentation under most workloads.  If you allocate
several different sizes, your allocations will in fact be spread all
over the available memory pool, and therefore be fragmented without
ever calling free().

Most common allocators are optimized to reach a steady-state quickly,
so they have the least fragmentation necessary to handle most
arbitrary workloads.  That means putting up with some fragmentation so
that applications that don't leak memory at the interface level will
also not leak memory due to the allocator's internal management, no
matter what allocation pattern they use.  The allocation pattern used
by the application can still affect how much fragmentation there is,
of course.

The overall point here, though, is that even commonly implemented
malloc/free interfaces can be reliable enough to keep applications
running for years without trouble.  Completely deterministic behavior
is not required when probabilistic determinism is sufficient.  (Sorry,
I just had to use big words there.  IOW, building an application to
average perfect behavior is fine when you don't need to guarantee
perfect behavior at every arbitrary point.  Most applications don't
measurably benefit from such a guarantee.)

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

Re: [sqlite] Re: Re: Re: Calculating Average Time

[Jonathan Otsuka]

On Nov 19, 2007, at 10:59 PM, Igor Tandetnik wrote:


Jonathan O <[EMAIL PROTECTED]> wrote:

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:
What does the data in time_column look like?

Currently looks like HH:MM:SS or MM:SS.


The latter is going to cause difficulties - SQLite's date/time  
functions are going to interpret it as HH:MM with seconds omitted.  
If you insist on storing strings, normalize them all to HH:MM:SS  
format. Then you can do something like this:


select time(AVG(strftime('%s', time_column))  / 86400.0 - 0.5)
from tablename


This did the trick.



You can also normalize on the fly, but that'll make for a rather  
horrific-looking statement:


select time(AVG(strftime('%s',
  case when length(time_column) < 8 then '00:' || time_column else  
time_column end

))  / 86400.0 - 0.5)
from tablename;


Didn't know there was a length() function!! Learned something new!





I think this is the answer I was needing. But how do I convert
seconds to HH:MM:SS?


select time(numSeconds / 86400.0 - 0.5);


Ahh a derivative of the above calculations.

Thanks!

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

Re: [sqlite] Memory Usage

[John Stanton]

You confused my point which is that your usual malloc/free definitely 
does no garbage collection.  That does not mean that a C language 
program cannot perform garbage collection, just look at a Java run time 
package for an example.


If you never execute a free your dynamic memory is essentially contiguous.

Joe Wilson wrote:

--- John Stanton <[EMAIL PROTECTED]> wrote:

Malloc is a concept implemented in various ways, some more successful 
than others but all of them hidden from the programmer.  Free tries to 
give back memory but as you can appreciate unless you use some garbage 
collection scheme with backwards pointers fragmentation and 
checkerboarding is very difficult to avoid.



You seem to be confusing the topics of malloc/free memory fragmentation 
with C garbage collection - they are orthogonal concepts.


C's free() has no power to free up any extra memory as active memory 
must stay where it was allocated until freed or else you'll have 
dangling pointers. A single call to free() typically releases only memory 
allocated by a single malloc() call. If, as result of the free, there 
happens to be block(s) of memory immediately before or after that 
released memory then that entire memory region can be coalesced and 
be made available as a larger block to future mallocs.


If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no 
memory leaks, then garbage collection is irrelevant to the topic of
memory fragmentation. It's not like C can employ a copying garbage 
collector that moves memory blocks after free() without the knowledge 
or participation of the host program. The malloc() call is where 
fragmentation happens. Fragmentation in malloc depends on your allocation 
strategy: first-fit, best-fit, short-lived versus long-lived pools, 
per-allocation-size pools, statistical prediction, etc. Malloc must 
try to guess where an allocation must go to try to prevent future 
memory fragmentation. 





  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 


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




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

[sqlite] Re: Re: Re: Calculating Average Time

[Igor Tandetnik]

Jonathan O <[EMAIL PROTECTED]> wrote:

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:
What does the data in time_column look like?

Currently looks like HH:MM:SS or MM:SS.


The latter is going to cause difficulties - SQLite's date/time functions 
are going to interpret it as HH:MM with seconds omitted. If you insist 
on storing strings, normalize them all to HH:MM:SS format. Then you can 
do something like this:


select time(AVG(strftime('%s', time_column))  / 86400.0 - 0.5)
from tablename;

You can also normalize on the fly, but that'll make for a rather 
horrific-looking statement:


select time(AVG(strftime('%s',
   case when length(time_column) < 8 then '00:' || time_column else 
time_column end

))  / 86400.0 - 0.5)
from tablename;


I think this is the answer I was needing. But how do I convert
seconds to HH:MM:SS?


select time(numSeconds / 86400.0 - 0.5);

See also
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Igor Tandetnik 



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

Re: [sqlite] Memory Usage

[Trevor Talbot]

On 11/19/07, John Stanton <[EMAIL PROTECTED]> wrote:

> Malloc is a concept implemented in various ways, some more successful
> than others but all of them hidden from the programmer.  Free tries to
> give back memory but as you can appreciate unless you use some garbage
> collection scheme with backwards pointers fragmentation and
> checkerboarding is very difficult to avoid.

Malloc and free as a concept do not imply fragmentation; it depends on
the implementation and how you use it.

> Various OS's have ways of allocating memory under control of the virtual
> memory manager so that it is not in the heap and can be completely
> returned.

This is no different than the malloc and free interface: you can
easily fragment virtual address space.

One form of allocator that is sometimes used in large task-oriented
programs is an arena-style one.  Essentially, a given task creates an
arena and performs malloc/free as necessary within the arena.  When
the task completes, the entire arena is destroyed, much like cleaning
up a stack.  Thus no memory-related issue such as fragmentation is
propagated between separate tasks.  It can be quite reliable when used
properly.

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

Re: [sqlite] returning Blobs in an oldstyle query ...

[Trevor Talbot]

On 11/19/07, Rob Sciuk <[EMAIL PROTECTED]> wrote:

> When using the sqlite3_query command, and a callback function, is there a
> way of getting the _*STATEMENT*_ for the query string which is currently
> executing, reliably from the sqlite3 *opaque type??

No.  sqlite3_exec() is simply a wrapper around prepare/step/finalize;
you can see what it does in legacy.c.

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

Re: [sqlite] Where is sqlite3.h?

[James Steward]

On Tue, 2007-11-20 at 14:23 +1100, James Steward wrote:
> Any hints?

I answer my own question.  I found it in the preprocessed source zip
file.  I hope this will do...

Cheerio.
JS.


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

Re: [sqlite] Re: Re: Calculating Average Time

[Jonathan O]

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Jonathan O <[EMAIL PROTECTED]> wrote:
> > On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
> > wrote:
> >>
> >> Jonathan O
> >> <[EMAIL PROTECTED]>
> >> wrote:
> >>> I have searched (archives/google) and haven't found a solution for
> >>> what I need and can't think of a solution so below is my question.
> >>> I have:
> >>>   Job 1 run 1 with a time of '01:00:15'
> >>>   Job 1 run 2 with a time of '01:00:21'
> >>>
> >>> What I do is:
> >>>
> >>> select sum(strftime('%H%M%S', time_column))/2 from table;
> >>> 10018
> >>
> >> You probably want
> >>
> >> select strftime('%H:%M:%S', AVG(time_column)) from tablename;
> >
> >
> > Doesn't seem that way.
> >
> > select strftime('%H:%M:%S', avg('01:00:15' + '01:00:21')/2;
> > 12:00:00
>
> I don't understand - do you want an average of two fixed values, or an
> average of values of a particular column across all rows? The statements
> you show don't make any sense at all.



The latter, I was just showing that this didn't work how you specified show
it in the beginning.
>> select strftime('%H:%M:%S', AVG(time_column)) from tablename; <-- Like
this

What does the data in time_column look like?


Currently looks like HH:MM:SS or MM:SS.

> The time data type is how long something takes to finish in HH:MM:SS
> > or MM:SS or similar right?
>
> There is no time data type in SQLite - only strings, numbers, and a set
> of functions to convert between the two. To store time durations, it's
> probably easiest to just store them as integers representing time in
> seconds, and format with strftime whenever you need string
> representation (either in SQLite statement, or in your application
> code).


I think this is the answer I was needing. But how do I convert seconds to
HH:MM:SS?

[sqlite] Where is sqlite3.h?

[James Steward]

Hi,

I wanted to build a C app, copied the example C code from the
Documentation on the web site, and downloaded the prebuilt binaries.
There's no header in with the dll and def (for crummy windows).

I tried building the source with MSYS/MinGW, but it didn't work out of
the box.

Any hints?

Regards,
James.


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

Re: [sqlite] how do I edit the wiki? or do I?

[P Kishor]

On Nov 19, 2007 8:58 PM, Joe Wilson <[EMAIL PROTECTED]> wrote:
> Step 1: [Edit]

very funny! There is no step 2.

I feel silly. I saw the [not logged in] link and just assumed that I
couldn't edit. A bit after I emailed my query, I just hit the edit
link and voila!

Thanks.


>
>
> --- P Kishor <[EMAIL PROTECTED]> wrote:
> > I would like to put up step-by-step instructions that would make sense
> > to some one not so developer-like as I. How do I add stuff to the
> > wiki? or, how do I get a login for the wiki to do so?
>
>
>
>   
> 
> Be a better pen pal.
> Text or chat with friends inside Yahoo! Mail. See how.  
> http://overview.mail.yahoo.com/
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>



-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S&T Policy Fellow, The National Academies
http://www.nas.edu/

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

Re: [sqlite] how do I edit the wiki? or do I?

[Joe Wilson]

Step 1: [Edit]

--- P Kishor <[EMAIL PROTECTED]> wrote:
> I would like to put up step-by-step instructions that would make sense
> to some one not so developer-like as I. How do I add stuff to the
> wiki? or, how do I get a login for the wiki to do so?



  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

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

[sqlite] Re: Re: Calculating Average Time

[Igor Tandetnik]

Jonathan O <[EMAIL PROTECTED]> wrote:

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:


Jonathan O
<[EMAIL PROTECTED]>
wrote:

I have searched (archives/google) and haven't found a solution for
what I need and can't think of a solution so below is my question.
I have:
  Job 1 run 1 with a time of '01:00:15'
  Job 1 run 2 with a time of '01:00:21'

What I do is:

select sum(strftime('%H%M%S', time_column))/2 from table;
10018


You probably want

select strftime('%H:%M:%S', AVG(time_column)) from tablename;



Doesn't seem that way.

select strftime('%H:%M:%S', avg('01:00:15' + '01:00:21')/2;
12:00:00


I don't understand - do you want an average of two fixed values, or an 
average of values of a particular column across all rows? The statements 
you show don't make any sense at all.


What does the data in time_column look like?


The time data type is how long something takes to finish in HH:MM:SS
or MM:SS or similar right?


There is no time data type in SQLite - only strings, numbers, and a set 
of functions to convert between the two. To store time durations, it's 
probably easiest to just store them as integers representing time in 
seconds, and format with strftime whenever you need string 
representation (either in SQLite statement, or in your application 
code).


Igor Tandetnik 



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

Re: [sqlite] Need help reading 3.3.2 database files with 3.5.2...

[Richard Klein]

The previous statement is actually more general:  SQLite 
version 3.x.y can read and write any database created by

any prior version of SQLite.


Even SQLite 2.w.z ?


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

[sqlite] how do I edit the wiki? or do I?

[P Kishor]

I finally got this blessed full-text search thing working. So that
other clueless folks like me don't end up mucking around for 2 days+,
I would like to put up step-by-step instructions that would make sense
to some one not so developer-like as I. How do I add stuff to the
wiki? or, how do I get a login for the wiki to do so?

Many thanks,

-- 
Puneet Kishor

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

Re: [sqlite] Memory Usage

[Mark Spiegel]

Well said.

While it may be true that some memory allocators are lacking, the ones I 
use are quite good.  I view with great suspicion developers who thinks 
they can outsmart the pool allocator.  These folks usually add great 
complexity while having at best a neutral impact on performance and 
robustness.  As you point out, they can only optimize for their module, 
not globally.  Any changes of this type should be carefully tested of 
course, but just as importantly backed up by thorough performance data.


Joe Wilson wrote:

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
  

Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.



Pool allocators can be effective for certain classes of problems
and can exhibit desirable deterministic properties. But a library does 
not exist in isolation. You must consider the entire program memory space.
If every library used its own distinct pools then a program that 
uses many of such libraries (sqlite, apache portable runtime, GNU STL,
whatever) may ultimately end up with is sub-optimal memory utilization 
for the entire program. Space reserved for one library, but not currently 
in use might be better put to use by another library's short-lived 
operation, for example. 

Using the same allocator for the entire program can give it optimization 
opportunities that may not necessarily exist with distinct library-specific 
memory pools. 


An example from Hoard's docs (mostly speed related, as opposed to space):

http://www.cs.umass.edu/~emery/hoard/faqs.html

  I'm using the STL but not seeing any performance improvement. Why not?

  In order to benefit from Hoard, you have to tell STL to use malloc 
  instead of its internal custom memory allocator:


  typedef list mylist;

For some problems library-specific allocators are very useful. 
You have to consider other factors as well.




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 


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


  

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.

[Michael Ruck]

> > > #A more complicated query...runs quite slowly.  How can 
> this be sped
> > > up?
> > > db eval {SELECT position.odo, data.x, data.y from 
> position, data WHERE
> > > position.odo BETWEEN 1 AND 10020;}
> > 
> > First, you want an index on position.odo. Second, you don't 
> specify any 
> > relation between position and data tables, so you generate a full 
> > cross-product. You want
> > 
> > SELECT position.odo, data.x, data.y
> > FROM position JOIN data ON (position.position_id = data.position_id)
> > WHERE position.odo BETWEEN 1 AND 10020;
> 
> Ah.  I have been reading a PHP/MySQL book, that I thought said a MySQL
> server would see the common column names and automagically join the 2.
> Either I misremember what the book said (it's not with me 
> here), or this
> is a feature of MySQL, not present in SQLite.  Anyway, what 
> you suggest
> works just fine.

SQLite does not implement this feature. Its not in the SQL standard AFAIK.

> > And for that to work efficiently, you want another index on 
> > data.position_id
> 
> I'm guessing that is with;
> CREATE INDEX odo_index ON data (position_id ASC);

Yes.

> If the data is streaming in, and insertions are being made on the fly,
> will an index need to be regenerated periodically, i.e. REINDEX?

No. Indexes are automatically updated.

HTH,
Mike


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

Re: [sqlite] Calculating Average Time

[Jonathan O]

On 11/19/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On Nov 19, 2007 5:43 PM, Jonathan O <[EMAIL PROTECTED]> wrote:
> > I have searched (archives/google) and haven't found a solution for what
> I
> > need and can't think of a solution so below is my question.
> > I have:
> >Job 1 run 1 with a time of '01:00:15'
> >Job 1 run 2 with a time of '01:00:21'
> >
> > What I do is:
> >
> > select sum(strftime('%H%M%S', time_column))/2 from table;
> > 10018
> >
> > But I don't know how to get it back into a time format. Is there an
> easier
> > way?
> >
>
>
> SELECT AVG(strftime('%H', time_column)*3600+strftime('%M',
> time_column)*60+strftime('%S', time_column))  FROM table;
>

This returns the same value as I get but in seconds. I want '01:00:18'.

Re: [sqlite] Re: Performance tuning, and other (silly?) SQLite questions.

[James Steward]

On Mon, 2007-11-19 at 18:14 -0500, Igor Tandetnik wrote:
> James Steward 
> wrote:
> > db eval {CREATE TABLE position(\
> > position_id INTEGER PRIMARY_KEY, \
> > odo INTEGER, \
> > time CURRENT_TIMESTAMP);}
> 
> You probably meant
> 
> -- note no underscore between PRIMARY and KEY
> position_id INTEGER PRIMARY KEY

Oops!  Fixed now.  Thanks.

> -- note DEFAULT keyword
> time DEFAULT CURRENT_TIMESTAMP

I discovered that just before I saw your reply ;-)

> > #A more complicated query...runs quite slowly.  How can this be sped
> > up?
> > db eval {SELECT position.odo, data.x, data.y from position, data WHERE
> > position.odo BETWEEN 1 AND 10020;}
> 
> First, you want an index on position.odo. Second, you don't specify any 
> relation between position and data tables, so you generate a full 
> cross-product. You want
> 
> SELECT position.odo, data.x, data.y
> FROM position JOIN data ON (position.position_id = data.position_id)
> WHERE position.odo BETWEEN 1 AND 10020;

Ah.  I have been reading a PHP/MySQL book, that I thought said a MySQL
server would see the common column names and automagically join the 2.
Either I misremember what the book said (it's not with me here), or this
is a feature of MySQL, not present in SQLite.  Anyway, what you suggest
works just fine.

> And for that to work efficiently, you want another index on 
> data.position_id

I'm guessing that is with;
CREATE INDEX odo_index ON data (position_id ASC);

If the data is streaming in, and insertions are being made on the fly,
will an index need to be regenerated periodically, i.e. REINDEX?

Cheers,
James.



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

Re: [sqlite] Re: Calculating Average Time

[Jonathan O]

On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Jonathan O <[EMAIL PROTECTED]> wrote:
> > I have searched (archives/google) and haven't found a solution for
> > what I need and can't think of a solution so below is my question.
> > I have:
> >   Job 1 run 1 with a time of '01:00:15'
> >   Job 1 run 2 with a time of '01:00:21'
> >
> > What I do is:
> >
> > select sum(strftime('%H%M%S', time_column))/2 from table;
> > 10018
>
> You probably want
>
> select strftime('%H:%M:%S', AVG(time_column)) from tablename;


Doesn't seem that way.

select strftime('%H:%M:%S', avg('01:00:15' + '01:00:21')/2;
12:00:00

The time data type is how long something takes to finish in HH:MM:SS or
MM:SS or similar right?

Re: [sqlite] Re: building a custom DBD::SQLite

[Joe Wilson]

Grep for the unresolved external in all the *.[ch] files.
sqlite3Fts3InitHashTable lives in ext/fts3/fts3_tokenizer.c.
Make sure it is in your makefile.

Ditto for fts2, with a slightly different name.

If you're building fts2 make sure you compile with 

  -DSQLITE_ENABLE_BROKEN_FTS2
  -DSQLITE_ENABLE_FTS2
  -DSQLITE_CORE

When In doubt, grep and read the source files.
There's a lot of info there.

--- P Kishor <[EMAIL PROTECTED]> wrote:
> Here are my travails thus far. I have been singularly unable to build
> SQLite 3.5.2 with fts3 on Intel Mac OS X 10.4.11
> 
> I added the following to my Makefile.in
> 
> #TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1
> TCC += -DSQLITE_CORE=1
> TCC += -DSQLITE_ENABLE_FTS3=1
> 
> and then added all the fts3 .c and .h files in the appropriate places
> as documented by Joe Wilson in another email. Then I did the following
> 
> $ export MACOSX_DEPLOYMENT_TARGET=10.3
> $ ./configure
> $ make
> 
> make croaks with the following error
> 
> /usr/bin/ld: Undefined symbols:
> _sqlite3Fts3InitHashTable
> collect2: ld returned 1 exit status
> make: *** [sqlite3] Error 1



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

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

Re: [sqlite] Calculating Average Time

[P Kishor]

On Nov 19, 2007 5:43 PM, Jonathan O <[EMAIL PROTECTED]> wrote:
> I have searched (archives/google) and haven't found a solution for what I
> need and can't think of a solution so below is my question.
> I have:
>Job 1 run 1 with a time of '01:00:15'
>Job 1 run 2 with a time of '01:00:21'
>
> What I do is:
>
> select sum(strftime('%H%M%S', time_column))/2 from table;
> 10018
>
> But I don't know how to get it back into a time format. Is there an easier
> way?
>


SELECT AVG(strftime('%H', time_column)*3600+strftime('%M',
time_column)*60+strftime('%S', time_column))  FROM table;

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

[sqlite] Re: Calculating Average Time

[Igor Tandetnik]

Jonathan O <[EMAIL PROTECTED]> wrote:

I have searched (archives/google) and haven't found a solution for
what I need and can't think of a solution so below is my question.
I have:
  Job 1 run 1 with a time of '01:00:15'
  Job 1 run 2 with a time of '01:00:21'

What I do is:

select sum(strftime('%H%M%S', time_column))/2 from table;
10018


You probably want

select strftime('%H:%M:%S', AVG(time_column)) from tablename;

Igor Tandetnik

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

Re: [sqlite] Performance tuning, and other (silly?) SQLite questions.

[Kees Nuyt]

[Default] On Tue, 20 Nov 2007 09:56:11 +1100, James Steward
<[EMAIL PROTECTED]> wrote:

>Hi,
>
>I am new to SQL and SQLite, so please excuse me if I appear thick at
>times.
>
>I have an application that generates data from a moving vehicle.  At a
>position there may be 10s of pairs of data.  Position is given as an
>integer, and the data pairs are an integer and a float (real).  So I set
>up a database like this (in Tcl);
>
>db eval {CREATE TABLE position(\
>   position_id INTEGER PRIMARY_KEY, \
>   odo INTEGER, \
>   time CURRENT_TIMESTAMP);}
>db eval {CREATE TABLE data(\
>   data_id INTEGER PRIMARY_KEY, \
>   position_id INTEGER, \
>   x INTEGER, \
>   y REAL);}

Shouldn't that be PRIMARY KEY (without the underscore)?


>So far so good.  Now I have a heap of stored data in a binary file, that
>I use to insert into the tables, with statements like;
>
>db eval {BEGIN;}
>
># loop reading in file...
>while {..} {
>
>#when a new odo value is read...
>db eval {INSERT INTO position(odo) values($odo);} 
>set pos_id [db eval {SELECT last_insert_rowid();}]
>
>#for every data pair
>db eval {INSERT INTO data(position_id, x, y) values($pos_id, $x, $y);}
>
>}
>
>db eval {COMMIT;}
>
>There are no errors returned.  Now I try a couple of queries, which
>return data, but not quite what I expect.
>
>#This kinda works as expected, but position_id and time columns are not
>#poulated.  Why not?

That will have to do with the wrong spelling of PRIMARY KEY.

>db eval {SELECT * FROM position WHERE ROWID BETWEEN 100 AND 101;}
>
>#This does not work, returns nothing.  I thought (after reading the
>#SQLite doco) that position_id would be populated with ROWID...
>db eval {SELECT * FROM position WHERE position_id BETWEEN 100 AND 101;}
>
>#This kinda works, but data_id is not populated...
>db eval {SELECT * FROM data where position_id BETWEEN 100 AND 101;}
>
>#A more complicated query...runs quite slowly.  How can this be sped up?
>db eval {SELECT position.odo, data.x, data.y from position, data WHERE
>position.odo BETWEEN 1 AND 10020;}

An index on position.odo might help.
Try that and compare the 'EXPLAIN SELECT ...' and 'EXPLAIN QUERY
PLAN SELECT ...'  output with and without index.

>Is SQLite going to be able to handle, say, 2,000,000 data pairs, and say
>60,000 positions, efficiently and quickly?

In general, yes.

>How can I help SQLite perform queries like the last above, as fast as
>possible?

Indexes and pragmas. More specifically, the page_size and
cache_size pragmas.

>Regards,
>James.

HTH
-- 
  (  Kees Nuyt
  )
c[_]

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

[sqlite] Calculating Average Time

[Jonathan O]

I have searched (archives/google) and haven't found a solution for what I
need and can't think of a solution so below is my question.
I have:
   Job 1 run 1 with a time of '01:00:15'
   Job 1 run 2 with a time of '01:00:21'

What I do is:

select sum(strftime('%H%M%S', time_column))/2 from table;
10018

But I don't know how to get it back into a time format. Is there an easier
way?

Jonathan

[sqlite] Re: building a custom DBD::SQLite

[P Kishor]

Here are my travails thus far. I have been singularly unable to build
SQLite 3.5.2 with fts3 on Intel Mac OS X 10.4.11

I added the following to my Makefile.in

#TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1
TCC += -DSQLITE_CORE=1
TCC += -DSQLITE_ENABLE_FTS3=1

and then added all the fts3 .c and .h files in the appropriate places
as documented by Joe Wilson in another email. Then I did the following

$ export MACOSX_DEPLOYMENT_TARGET=10.3
$ ./configure
$ make

make croaks with the following error

/usr/bin/ld: Undefined symbols:
_sqlite3Fts3InitHashTable
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1

This train is not going anywhere at all. In another post, my
experience with fts2

On Nov 18, 2007 5:28 PM, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On Nov 18, 2007 2:59 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> > On Nov 17, 2007 2:22 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> > > I need to build a DBD::SQLite package using SQLite with fts
> > > capabilities. Can someone on this list kindly give me painless, easy
> > > to understand instructions to do so? I need to do this on a Mac OS X
> > > 10.4 first, and then on my web host subsequently (running some flavor
> > > of Linux, natch).
> > >
> >
> >
> > ok, let's try this again.
> >
> > I found instructions for building a loadable extension at
> > 
> >
> > Note to Richard on the subject of improving the website -- please make
> > such instructions easier to find by perhaps linking them somewhere
> > prominently under "building or compiling" right off the download page
> > for the source code.
> >
> > That said, I tried to build the fts2 extension and got the following --
> >
> > $ tar xvzf sqlite-3.4.2
> > $ mkdir sqlite-3.4.2-build
> > $ cd sqlite-3.4.2
> > 
> > 
> > $ cd ..
> > $ cd sqlite-3.4.2-build
> > $ ../sqlite-3.4.2/configure LIBS=-ldl
> > $ make
> > $ export LD_LIBRARY_PATH="`pwd`:$LD_LIBRARY_PATH"
> > $ gcc -I`pwd` -shared ../sqlite-3.4.2/ext/fts2/fts2.c -o fts2.so
> > i686-apple-darwin8-gcc-4.0.1: unrecognized option '-shared'
> > /usr/bin/ld: Undefined symbols:
> > _main
> > _sqlite3Fts2HashClear
> > _sqlite3Fts2HashFind
> > _sqlite3Fts2HashInit
> > _sqlite3Fts2HashInsert
> > _sqlite3Fts2InitHashTable
> > _sqlite3Fts2PorterTokenizerModule
> > _sqlite3Fts2SimpleTokenizerModule
> > collect2: ld returned 1 exit status
> >
> >
> > so, this is the first step that I have surmount. Once this is done, I
> > would really like fts2 to not be shared but be permanently jammed into
> > sqlite3 so I don't have to load it manually (unless, there is any
> > significant advantage to building a shared library).
>
> this is getting more and more a happy waste of time (hopefully I would
> have learned something out of this). After much searching, I found Joe
> Wilson's instructions at
> 
>
> Following those pretty much to the t, I almost got everything working.
> Except, I got the following during make
>
> ../sqlite-3.5.2/ext/fts1/fts1.c:7:2: error: #error fts1 has a design
> flaw and has been deprecated.
> make: *** [fts1.lo] Error 1
>
> Fantastic. If it is has a design flaw and has been deprecated, then
> why include it? Or, if it is included, where is the information that
> it has been deprecated that one can read *before* doing make?
>
> Anyway, I went back into Makefile.in and commented out all references
> to fts1 and ran make again. This time I got
>
> ../sqlite-3.5.2/ext/fts2/fts2.c:7:2: error: #error fts2 has a design
> flaw and has been deprecated.
> make: *** [fts2.lo] Error 1
>
> So, I went back into the ext library and discovered that there was
> fts3. So, I went back into Makefile.in and commented out all
> references to fts2, added similar looking lines for fts3 and ran make
> again. This time it all worked. Great!
>
> Well, not so great. Now when I run the new sqlite3, I get the following
>
> dyld: lazy symbol binding failed: Symbol not found: _sqlite3Fts1Init
>   Referenced from:
> /Users/punkish/Projects/sqlite-3.5.2-build/.libs/libsqlite3.0.dylib
>   Expected in: flat namespace
>
> dyld: Symbol not found: _sqlite3Fts1Init
>   Referenced from:
> /Users/punkish/Projects/sqlite-3.5.2-build/.libs/libsqlite3.0.dylib
>   Expected in: flat namespace
>
> Trace/BPT trap
>
> If I can get some clear instructions on how to do this successfully, I
> promise to record them on the wiki for other poor souls who might try
> this.
>
> Many thanks in advance.
>
> And, oh yes, still will need help with the following once the above is 
> possible.
>
>
>
> >
> > Once I am successful with the above, I would like to build a
> > DBD::SQLite with my new library.
> >
> > Many thanks for your guidance.
> >
> > --
> > Puneet Kishor
> >
>

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

[sqlite] Re: Performance tuning, and other (silly?) SQLite questions.

[Igor Tandetnik]

James Steward 
wrote:

db eval {CREATE TABLE position(\
position_id INTEGER PRIMARY_KEY, \
odo INTEGER, \
time CURRENT_TIMESTAMP);}


You probably meant

-- note no underscore between PRIMARY and KEY
position_id INTEGER PRIMARY KEY

-- note DEFAULT keyword
time DEFAULT CURRENT_TIMESTAMP


db eval {CREATE TABLE data(\
data_id INTEGER PRIMARY_KEY, \
position_id INTEGER, \
x INTEGER, \
y REAL);}


Same here - it's PRIMARY KEY with no underscore.


#A more complicated query...runs quite slowly.  How can this be sped
up?
db eval {SELECT position.odo, data.x, data.y from position, data WHERE
position.odo BETWEEN 1 AND 10020;}


First, you want an index on position.odo. Second, you don't specify any 
relation between position and data tables, so you generate a full 
cross-product. You want


SELECT position.odo, data.x, data.y
FROM position JOIN data ON (position.position_id = data.position_id)
WHERE position.odo BETWEEN 1 AND 10020;

And for that to work efficiently, you want another index on 
data.position_id


Igor Tandetnik 



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

[sqlite] Performance tuning, and other (silly?) SQLite questions.

[James Steward]

Hi,

I am new to SQL and SQLite, so please excuse me if I appear thick at
times.

I have an application that generates data from a moving vehicle.  At a
position there may be 10s of pairs of data.  Position is given as an
integer, and the data pairs are an integer and a float (real).  So I set
up a database like this (in Tcl);

db eval {CREATE TABLE position(\
position_id INTEGER PRIMARY_KEY, \
odo INTEGER, \
time CURRENT_TIMESTAMP);}

db eval {CREATE TABLE data(\
data_id INTEGER PRIMARY_KEY, \
position_id INTEGER, \
x INTEGER, \
y REAL);}

So far so good.  Now I have a heap of stored data in a binary file, that
I use to insert into the tables, with statements like;

db eval {BEGIN;}

# loop reading in file...
while {..} {

#when a new odo value is read...
db eval {INSERT INTO position(odo) values($odo);} 
set pos_id [db eval {SELECT last_insert_rowid();}]

#for every data pair
db eval {INSERT INTO data(position_id, x, y) values($pos_id, $x, $y);}

}

db eval {COMMIT;}

There are no errors returned.  Now I try a couple of queries, which
return data, but not quite what I expect.

#This kinda works as expected, but position_id and time columns are not
#poulated.  Why not?
db eval {SELECT * FROM position WHERE ROWID BETWEEN 100 AND 101;}

#This does not work, returns nothing.  I thought (after reading the
#SQLite doco) that position_id would be populated with ROWID...
db eval {SELECT * FROM position WHERE position_id BETWEEN 100 AND 101;}

#This kinda works, but data_id is not populated...
db eval {SELECT * FROM data where position_id BETWEEN 100 AND 101;}

#A more complicated query...runs quite slowly.  How can this be sped up?
db eval {SELECT position.odo, data.x, data.y from position, data WHERE
position.odo BETWEEN 1 AND 10020;}

Is SQLite going to be able to handle, say, 2,000,000 data pairs, and say
60,000 positions, efficiently and quickly?

How can I help SQLite perform queries like the last above, as fast as
possible?

Regards,
James.


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

Re: [sqlite] Memory Usage

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Malloc is a concept implemented in various ways, some more successful 
> than others but all of them hidden from the programmer.  Free tries to 
> give back memory but as you can appreciate unless you use some garbage 
> collection scheme with backwards pointers fragmentation and 
> checkerboarding is very difficult to avoid.

You seem to be confusing the topics of malloc/free memory fragmentation 
with C garbage collection - they are orthogonal concepts.

C's free() has no power to free up any extra memory as active memory 
must stay where it was allocated until freed or else you'll have 
dangling pointers. A single call to free() typically releases only memory 
allocated by a single malloc() call. If, as result of the free, there 
happens to be block(s) of memory immediately before or after that 
released memory then that entire memory region can be coalesced and 
be made available as a larger block to future mallocs.

If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no 
memory leaks, then garbage collection is irrelevant to the topic of
memory fragmentation. It's not like C can employ a copying garbage 
collector that moves memory blocks after free() without the knowledge 
or participation of the host program. The malloc() call is where 
fragmentation happens. Fragmentation in malloc depends on your allocation 
strategy: first-fit, best-fit, short-lived versus long-lived pools, 
per-allocation-size pools, statistical prediction, etc. Malloc must 
try to guess where an allocation must go to try to prevent future 
memory fragmentation. 




  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

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

Re: [sqlite] Sqlite corruption

[Steven Fisher]

On 19-Nov-2007, at 1:13 PM, Steve Palmer wrote:

At this point I'm unsure what may have happened to cause this  
corruption.
The same corruption has been reported by other users of the program  
so I'm
fairly confident this isn't a one-off. Is there any other analysis I  
could

conduct that might give a clue as to how the corruption is arising?


Hopefully someone will have a better answer for you, but have you  
looked here yet?



Off the top of my head, I don't see anything directly relevant to  
Vienna, though.


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

[sqlite] Sqlite corruption

[Steve Palmer]

Some of our users have been reporting corruption in their SQLite database.
Running pragma-integrity check on the database yields the following results:
Guinevere:corrupted-messages Steve$ sqlite3 messages.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> pragma integrity-check;
SQL error: near "-": syntax error
sqlite> pragma integrity_check;
*** in database main ***
On tree page 29677 cell 107: 2nd reference to page 29898
On tree page 29677 cell 107: Child page depth differs
On tree page 29677 cell 108: 2nd reference to page 29899
On tree page 29677 cell 109: 2nd reference to page 29900
On page 29677 at right child: invalid page number 29909
SQL error: database disk image is malformed
sqlite> .quit
Guinevere:corrupted-messages Steve$

The database was created with SQlite 3.4.0 on a Mac OSX 10.3.9 or later
system.

The user reported that he experienced a system crash and had to hard-power
his machine off and back on again. The database was open at the time however
the library was being run with synchronous=full. The following pragmas were
also applied:

pragma cache_size=2000

pragma default_cache_size=3

pragma temp_store=1

pragma auto_vacuum=0

At this point I'm unsure what may have happened to cause this corruption.
The same corruption has been reported by other users of the program so I'm
fairly confident this isn't a one-off. Is there any other analysis I could
conduct that might give a clue as to how the corruption is arising?

Thanks!
-Steve

Re: [sqlite] Memory Usage

[Joe Wilson]

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> Our studies to date indicate that SQLite neither leaks nor fragments
> memory.  Preventing leaks is relatively easy.  Preventing memory
> fragmentation less so.  Yet we are not seeing memory fragmentation
> as a problem for the workloads we have tested.
> 
> Nevertheless, we cannot *prove* that SQLite, in its current form,
> will never fragment memory.  However, we are working toward a
> future release where such a proof will be possible, at least for
> certain well-defined operating parameters.   We just are not quite
> there yet.

Pool allocators can be effective for certain classes of problems
and can exhibit desirable deterministic properties. But a library does 
not exist in isolation. You must consider the entire program memory space.
If every library used its own distinct pools then a program that 
uses many of such libraries (sqlite, apache portable runtime, GNU STL,
whatever) may ultimately end up with is sub-optimal memory utilization 
for the entire program. Space reserved for one library, but not currently 
in use might be better put to use by another library's short-lived 
operation, for example. 

Using the same allocator for the entire program can give it optimization 
opportunities that may not necessarily exist with distinct library-specific 
memory pools. 

An example from Hoard's docs (mostly speed related, as opposed to space):

http://www.cs.umass.edu/~emery/hoard/faqs.html

  I'm using the STL but not seeing any performance improvement. Why not?

  In order to benefit from Hoard, you have to tell STL to use malloc 
  instead of its internal custom memory allocator:

  typedef list mylist;

For some problems library-specific allocators are very useful. 
You have to consider other factors as well.



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

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

Re: [sqlite] Memory Usage

[John Stanton]

James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 12:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Dynamic allocation is not the problem, it is malloc and free.  there

is

a difference between being certain and being lucky.


Could/would you expand on that?  There's limited difference between
malloc/free and other dynamic allocation systems, and I'm assuming that
we're not considering transparent garbage-collection as an option.

What about malloc/free do you claim is problematic, and why is it not
problematic with other dynamic allocation approaches?  Do you speak of a
problem with the interface, or with specific implementations?

-- James

Malloc is a concept implemented in various ways, some more successful 
than others but all of them hidden from the programmer.  Free tries to 
give back memory but as you can appreciate unless you use some garbage 
collection scheme with backwards pointers fragmentation and 
checkerboarding is very difficult to avoid.


If you allocate memory directly and have your program reuse it in a way 
which cannot fragment you achieve dynamic memory allocation which is 
truly reversible.


When you think carefully about the memory usage in your program you can 
often use the stack rather than the heap and avoid problems.  Local 
memory on the stack never fragments.


Various OS's have ways of allocating memory under control of the virtual 
memory manager so that it is not in the heap and can be completely 
returned.  For example it can be an anonymous shared file.  If the 
machine has a disk, using virtual memory when suitable will avoid a heap 
overflow and crash, but be aware that you can have the situation where 
it will perform a lazy write through to disk.


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

Re: [sqlite] Memory Usage

[John Stanton]

D. Richard Hipp wrote:


On Nov 19, 2007, at 12:36 PM, James Dennett wrote:


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.



Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.

To be highly reliable and portable we tend to use brute force and 
ignorance and maintain free lists of memory.  It works reliably and 
programs run for years but it is tedious to implement.  It will 
eventually reach a high water point which represents the worst case 
memory allocation.



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

[sqlite] returning Blobs in an oldstyle query ...

[Rob Sciuk]

TWIMC,

When using the sqlite3_query command, and a callback function, is there a 
way of getting the _*STATEMENT*_ for the query string which is currently 
executing, reliably from the sqlite3 *opaque type??


The reason I need this, I'm using SQLITE 3.5.x to manage blobjects of 
various kinds, and while I have control when using the prep/step/finalize 
routines, and I can determine sqlite3_column_type of each variable 
returned, and indeed, if a blob, then I can determine the length 
sqlite3_column_bytes, and obtain the data sqlite3_column_blob.  These 
funcions require the use of the statment pointer.


As the query does not pass a link to the prepared *STATEMENT* in the exec 
form of the callback, I was wondering if there is a way to reliably obtain 
it from the sqlite3 *object (in this case: this ->Cx ) ;


this ->dbError = sqlite3_exec(
this ->Cx,
query,
lw_sqlite_callback,
this,
(char **) NULL
) ;
return( db_Error( this ) ) ;

where the callback is defined:

int  lw_sqlite_callback(void* this, int n_Cols, char**vals, char**vars){

Db_t*p ;
int  i = 0 ;

p = (Db_t *) this ;
if( !p ->Result ){
p ->Result = New( TList, n_Cols ) ;
}

for( i = 0 ; i < n_Cols ; i++ ){
at_Addstr( p ->Result, vars[i], vals[i] ) ;
}
return( SQLITE_OK ) ;
}

note that in the callback, this ->Cx constitutes the sqlite3 database 
pointer.


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=
Robert S. Sciuk http://www.controlq.com 259 Simcoe St. S.
Control-Q Research  tel: 905.576.8028   Oshawa, Ont.
[EMAIL PROTECTED]   fax: 905.576.8386   Canada, L1H 4H3

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

Re: [sqlite] Memory Usage

[John Stanton]

Dynamic memory allocation is not the problem, it it memory fragmentation 
and checkerboarding produced by "free".  Avoid the fragmentation and you 
can run forever.


James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  


-- James


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




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

RE: [sqlite] Memory Usage

[James Dennett]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 19, 2007 12:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Memory Usage
> 
> Dynamic allocation is not the problem, it is malloc and free.  there
is
> a difference between being certain and being lucky.

Could/would you expand on that?  There's limited difference between
malloc/free and other dynamic allocation systems, and I'm assuming that
we're not considering transparent garbage-collection as an option.

What about malloc/free do you claim is problematic, and why is it not
problematic with other dynamic allocation approaches?  Do you speak of a
problem with the interface, or with specific implementations?

-- James


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

Re: [sqlite] Begin and End Transaction.

[Kees Nuyt]

Hi JP,

On Mon, 19 Nov 2007 10:54:26 -0800 (PST), Joanne Pham
<[EMAIL PROTECTED]> wrote:

> Hi All,
> Do I need to have the commit/rollback statement 
> if I have "Begin" and "End" transaction in my program.


http://www.sqlite.org/lang_transaction.html :
END TRANSACTION is an alias for COMMIT.

So, you can use either. 

>Thanks,
>JP

-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Memory Usage

[John Stanton]

Dynamic allocation is not the problem, it is malloc and free.  there is 
a difference between being certain and being lucky.


James Dennett wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  


-- James


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




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

[sqlite] Re: Begin and End Transaction.

[Igor Tandetnik]

Joanne Pham  wrote:

Do I need to have the commit/rollback statement if I have "Begin" and
"End" transaction in my program. Thanks,


END is a synonym for COMMIT. You can use the two statements 
interchangeably.


Igor Tandetnik 



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

Re: [sqlite] Memory Usage

[D. Richard Hipp]

On Nov 19, 2007, at 12:36 PM, James Dennett wrote:


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, November 19, 2007 7:36 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory Usage

Not only applicable to real time systems.  If you want a program to

run

with stability over a long time the first step it to eliminate frees

and

if malloc is used confine it to the intialization.


I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses  
dynamic

allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software  
must

run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage  
its own

fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.



Our studies to date indicate that SQLite neither leaks nor fragments
memory.  Preventing leaks is relatively easy.  Preventing memory
fragmentation less so.  Yet we are not seeing memory fragmentation
as a problem for the workloads we have tested.

Nevertheless, we cannot *prove* that SQLite, in its current form,
will never fragment memory.  However, we are working toward a
future release where such a proof will be possible, at least for
certain well-defined operating parameters.   We just are not quite
there yet.



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

[sqlite] Begin and End Transaction.

[Joanne Pham]

Hi All,
Do I need to have the commit/rollback statement if I have "Begin" and "End" 
transaction in my program.
Thanks,
JP


  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

RE: [sqlite] Memory Usage

[James Dennett]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 19, 2007 7:36 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Memory Usage
> 
> Not only applicable to real time systems.  If you want a program to
run
> with stability over a long time the first step it to eliminate frees
and
> if malloc is used confine it to the intialization.

I have to challenge this, not because it's entirely wrong (it's not),
but because it's an over-simplification.

I've worked with a lot of software that runs for years, and uses dynamic
allocation without problems.  One of the keys to writing stable/robust
software is avoiding complexity, and *appropriate* use of dynamic
allocation can help with that in some situations.

There is, of course, a vast range of environments in which software must
run for years at a time, and the appropriate implementation techniques
vary.  A pacemaker doesn't have the same constraints as a telephone
switch, or a satellite, or a set-top box or mobile phone, and even
within each of those categories there are wide ranges.

It's certainly nice that SQLite offers the flexibility to manage its own
fixed-size memory pool, but this certainly is not the only way to
produce stable, robust, long-running software.  

-- James


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

Re: [sqlite] Memory Usage

[John Stanton]

Not only applicable to real time systems.  If you want a program to run 
with stability over a long time the first step it to eliminate frees and 
if malloc is used confine it to the intialization.


Jim Dodgen wrote:
One other note, just about all real-time systems limit the dynamic 
allocation of memory because you lose the deterministic behavior, 
typically all memory is allocated when the task starts, memory is 
usually managed internally in standard sized chunks.


Also for long running tasks (months/years) you would suffer 
fragmentation and for limited memory systems a malloc/new  may fail 
weeks after the task starts and that would not be pretty. Also it is 
very hard or even impossible to completely test,  it is best to design 
the problem out.


Thus it is nice to have the ability to turnoff sqlite's ability to malloc

D. Richard Hipp wrote:



On Nov 18, 2007, at 8:12 AM, Russell Leighton wrote:



On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote:



If you compile with -DSQLITE_MEMORY_SIZE= then SQLite
will *never* call malloc().  Instead, it uses a static
array that is  bytes in size for all of its memory
needs.  You can get by with as little as 100K or so of
memory, though the more memory you provide, the faster
it will run.  5MB is a good value.



Does using this setting (and eliminating malloc/free overhead) result
in a significant performance increase?



That depends on how good of a malloc you have on your system.
On Linux systems that typically use Doug Lea's malloc, there is
no measurable performance difference.  But I have had some
people running embedded systems tell me that using the
malloc-free SQLite results in a significant speed boost.  Your
mileage may vary.

D. Richard Hipp
[EMAIL PROTECTED]




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

RE: [sqlite] cannot commit - no transaction is active

[learning Sqlite3]

Thanks!

I just make sure if it was caused by sqlite3. It it is not, I will ask apr.



> Date: Mon, 19 Nov 2007 12:20:35 +0100
> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] cannot commit - no transaction is active
> 
> On Nov 19, 2007 9:15 AM, learning Sqlite3
> <[EMAIL PROTECTED]> wrote:
> 
> > I use the function:
> > apr_dbd_transaction_start(driver, pool, sql,
> >&transaction);
> > to start a transaction.
> >
> > But when I close this transaction with the function:
> > apr_dbd_transaction_end(driver, pool, transaction);
> >
> > It always gives the error message:
> >
> > cannot commit - no transaction is active
> >
> > What is the reason?
> 
> This is Apache/APR wrapper API. I suggest you ask the appropriate
> place instead of here.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 

_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx

Re: [sqlite] cannot commit - no transaction is active

[Srebrenko Sehic]

On Nov 19, 2007 9:15 AM, learning Sqlite3
<[EMAIL PROTECTED]> wrote:

> I use the function:
> apr_dbd_transaction_start(driver, pool, sql,
>&transaction);
> to start a transaction.
>
> But when I close this transaction with the function:
> apr_dbd_transaction_end(driver, pool, transaction);
>
> It always gives the error message:
>
> cannot commit - no transaction is active
>
> What is the reason?

This is Apache/APR wrapper API. I suggest you ask the appropriate
place instead of here.

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

RE: [sqlite] Re: Any way to obtain explicit read lock?

[Igor Sereda]

Igor, thanks. I almost always use BEGIN IMMEDIATE, so I missed the BEGIN 
[DEFERRED] variant. Guess it solves the problem, though it seems the lock won't 
be acquired before SELECT happens. 

Best regards,
Igor

 
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 19, 2007 3:26 AM
To: SQLite
Subject: [sqlite] Re: Any way to obtain explicit read lock?

Igor Sereda <[EMAIL PROTECTED]> wrote:
> Suppose we need to read two tables in an isolated way, so no db change 
> is visible to the connection between first and second readout.
> As far as I see, there's no such SQL or API for that at the moment.  

Just do both SELECT's within a single transaction. See BEGIN, COMMIT

Igor Tandetnik

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



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

Re: [sqlite] Data encryption

[Günter Greschenz]

sorry, i have been on a business trip until this weekend (i have been 
some months in korea...),

and my server (at home) was turned off by my wife :-)
i will reenable it this evening...
here is the source again (attached to this mail)
cu, gg


A.J.Millan wrote:

Andreas:

Some time ago, in response to a similar question, Günter Greschenz 
sent to this forum a email:



hi,


i've written some sqlite-functions to crypt (blowfish) or compress 
(bzip) data in sqlite:



e.g.
  insert into blubs values (crypt('data','pwd'))
or
  select from xyz where decompress(data) = 'blablabla'
or
  select from xyz where data = compress('blablabla')


but you have to wait until next weekend, because i'v traveling for my 
company at the moment and >return on friday (i hope :-)


Some day later:

after a long time being on a business trip, i finally came home and 
have now the chance to upload >the sources to my webserver:



http://greschenz.dyndns.org/sqlite.html


these sources have never been in a productive system, i just 
implemented it for fun...



what i want to say: i never tested it really good !


To me it was a wonderful source of information and ideas, but this 
morning the URL was unable.  May be if you recite certain magic spell, 
Günter can appear again...


Cheers

A.J.Millan

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







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

Re: [sqlite] Can't checkout from cvs

[Dan Kennedy]

On osx I was able to use this to avoid having to type the
password interactively:

cvs -d :pserver:anonymous:[EMAIL PROTECTED]:/sqlite login

Dan.

On 11/19/07, Rael Bauer <[EMAIL PROTECTED]> wrote:
> Hi,
>
>   I'm trying to checkout source from cvs but running into problems:
>   At command prompt I type: (using cvs from www.nongnu.org/cvs/)
>   cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login
>   then I get the line:
>   cvs password:
>   I try to type in "anonymous" but I can't type in any keys at all to the 
> console window (it's like the window freezes. only enter works once)! I wait 
> until time out response.
>
>   Any idea what could be causing this?
>   Using winxp sp2.
>
>   Is it possible to checkout using tortoisecvs? I've tried this but also 
> doesn't work.
>   (my settings are:
>   cvsroot:  :pserver:[EMAIL PROTECTED]:/sqlite
>   server:  www.sqlite.org
>   repository folder: /sqlite
>   username: anonymous
>   module: sqlite)
>
>   thanks
>   Rael
>
>
> -
> Never miss a thing.   Make Yahoo your homepage.

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

[sqlite] cannot commit - no transaction is active

[learning Sqlite3]

Hello,

I use the function: 
apr_dbd_transaction_start(driver, pool, sql,
   &transaction);
to start a transaction.

But when I close this transaction with the function:
apr_dbd_transaction_end(driver, pool, transaction);

It always gives the error message:

cannot commit - no transaction is active

What is the reason?

Thanks!

_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx

[sqlite] Can't checkout from cvs

[Rael Bauer]

Hi,
   
  I'm trying to checkout source from cvs but running into problems:
  At command prompt I type: (using cvs from www.nongnu.org/cvs/)
  cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login
  then I get the line:
  cvs password:
  I try to type in "anonymous" but I can't type in any keys at all to the 
console window (it's like the window freezes. only enter works once)! I wait 
until time out response.
   
  Any idea what could be causing this?
  Using winxp sp2.
   
  Is it possible to checkout using tortoisecvs? I've tried this but also 
doesn't work.
  (my settings are:
  cvsroot:  :pserver:[EMAIL PROTECTED]:/sqlite
  server:  www.sqlite.org
  repository folder: /sqlite
  username: anonymous
  module: sqlite)
   
  thanks
  Rael

   
-
Never miss a thing.   Make Yahoo your homepage.