Re: [sqlite] Crazy performance difference between Mac and Windows

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gavin Kistner wrote:
> Mostly I'm sharing this as a curiosity, though I'm quite interested if  
> anyone has a suggestion on why
> this might be so much slower on a roughly equivalent machine differing  
> only in OS.

See points zero and five at
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows

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

iD8DBQFI2Hv0mOOfHg372QQRAsnxAKDVR2TTViQDPNAFfNHMpBor6X7E0QCgucgc
QCKvGv9EuxrBotxHFLBhwNE=
=1col
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crazy performance difference between Mac and Windows

[P Kishor]

warning: I know nothing about Ruby.

On 9/22/08, Gavin Kistner <[EMAIL PROTECTED]> wrote:
> (Right off the bat, let me say that I'm not sure if the problem here
>  is Sequel, sqlite3-ruby, or
>  sqlite. Just in case...)
>
>  I have a Ruby script to migrate data from an old sqlite DB to a new
>  schema. It's quite simple, selecting rows from db 1 and creating
>  records in db 2. (A rough representation of the script is at the end
>  of this post.)

I am assuming you have good reason to not just ATTACH the old db to
the new db and INSERT INTO new_table SELECT * FROM old_db.old_table

Seems tedious to use Ruby to do this.

>
>  This script transforms a 2MB sqlite DB with about 5,000 rows into a
>  1.8MB sqlite DB with about the same number of rows. (A few fields and
>  tables get dropped along the way.)
>
>  On my mac laptop at home (2.3GHz Core 2 Duo, 2GB RAM, 5400 RPM drive)
>  this script runs in 22 seconds. In 'better battery life' mode.

All that said, 22 seconds for a 5000 row db on that machine (same as
my laptop) seems rather slow to me.

>
>  On my XP desktop at work (2GHz Dual Pentium, 2GB RAM, 7000 RPM drive)
>  this same script on the same DB runs in 11 minutes. 30x slower.
>
>  It's the same version of Ruby (1.8.6 p111), same version of sqlite3
>  (3.6.2), sqlite3-ruby (1.2.3), and same version of Sequel (2.5.0). I
>  know that the One-Click Installer of Ruby I'm using on XP isn't as
>  fast as some other builds, but 30x slower seems crazy.
>
>  If I turn off DB journaling on SQLite on Windows, I can get it down
>  from 11 minutes to 4 minutes. Only
>  12x slower than the Mac. (But then, the Mac also runs faster without
>  journaling.)
>
>  The only funky thing that I can point to is that the script uses two
>  different ORMs (sqlite3-ruby on db 1 and Sequel on db 2). I don't have
>  a really good reason for this, it's just how it happened to have been
>  written. If
>  this slowdown ever becomes a big issue I could try Sequel for both and
>  see if that helps in any way.
>
>  Mostly I'm sharing this as a curiosity, though I'm quite interested if
>  anyone has a suggestion on why
>  this might be so much slower on a roughly equivalent machine differing
>  only in OS.
>
>  Here's (roughly) what the script looks like:
>
>require 'rubygems'
>require 'sqlite3'
>require 'sequel'
>
>olddb = SQLite3::Database.new( OLD_FILE )
>olddb.results_as_hash = true
>
>newdb = Sequel.sqlite( NEW_FILE )
>newdb << IO.read( NEW_SCHEMA )
>
># Do the following sort of thing for about 10 different tables
>new_table = newdb[ :users ]
>olddb.execute "SELECT * FROM users" do |user|
>  new_table << {
>:id=> user['id'].to_i,
>:name  => user['name'],
>:active=> user['active']=='yes',
>:email => user['email']
>  }
>end
>
>  (As you might expect, this results in a SELECT from one DB followed by
>  N independent un-transactioned INSERTs run on the other DB.)
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Crazy performance difference between Mac and Windows

[Gavin Kistner]

(Right off the bat, let me say that I'm not sure if the problem here  
is Sequel, sqlite3-ruby, or
sqlite. Just in case...)

I have a Ruby script to migrate data from an old sqlite DB to a new  
schema. It's quite simple, selecting rows from db 1 and creating  
records in db 2. (A rough representation of the script is at the end  
of this post.)

This script transforms a 2MB sqlite DB with about 5,000 rows into a  
1.8MB sqlite DB with about the same number of rows. (A few fields and  
tables get dropped along the way.)

On my mac laptop at home (2.3GHz Core 2 Duo, 2GB RAM, 5400 RPM drive)  
this script runs in 22 seconds. In 'better battery life' mode.

On my XP desktop at work (2GHz Dual Pentium, 2GB RAM, 7000 RPM drive)  
this same script on the same DB runs in 11 minutes. 30x slower.

It's the same version of Ruby (1.8.6 p111), same version of sqlite3  
(3.6.2), sqlite3-ruby (1.2.3), and same version of Sequel (2.5.0). I  
know that the One-Click Installer of Ruby I'm using on XP isn't as  
fast as some other builds, but 30x slower seems crazy.

If I turn off DB journaling on SQLite on Windows, I can get it down  
from 11 minutes to 4 minutes. Only
12x slower than the Mac. (But then, the Mac also runs faster without  
journaling.)

The only funky thing that I can point to is that the script uses two  
different ORMs (sqlite3-ruby on db 1 and Sequel on db 2). I don't have  
a really good reason for this, it's just how it happened to have been  
written. If
this slowdown ever becomes a big issue I could try Sequel for both and  
see if that helps in any way.

Mostly I'm sharing this as a curiosity, though I'm quite interested if  
anyone has a suggestion on why
this might be so much slower on a roughly equivalent machine differing  
only in OS.

Here's (roughly) what the script looks like:

   require 'rubygems'
   require 'sqlite3'
   require 'sequel'

   olddb = SQLite3::Database.new( OLD_FILE )
   olddb.results_as_hash = true

   newdb = Sequel.sqlite( NEW_FILE )
   newdb << IO.read( NEW_SCHEMA )

   # Do the following sort of thing for about 10 different tables
   new_table = newdb[ :users ]
   olddb.execute "SELECT * FROM users" do |user|
 new_table << {
   :id=> user['id'].to_i,
   :name  => user['name'],
   :active=> user['active']=='yes',
   :email => user['email']
 }
   end

(As you might expect, this results in a SELECT from one DB followed by  
N independent un-transactioned INSERTs run on the other DB.)

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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote:

> On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched  
> on the wall:
>> I am reluctant to add to SQLite the ability to explicitly specify the
>> index for a query.  I agree with Alex Scotti that the whole idea  
>> seems
>> very un-RDBMS like.
>
>   Well it is outside of the Relational Model, that's for sure.
>
>   Then again, the whole concept of indexes are outside of the
>   Relational Model.

this isn't exactly a good argument.  an index surely doesn't break  
the relational model in any way.  it's existence or
absence may or may not effect execution time, but would never yield  
incorrect or different results.  the ubiquitous b-tree index
may not have been so obvious at the time, and who's to say it will  
continue to be a given forever.  the model is just that, a model -
abstracted away from implementation details, no matter how obvious  
they seem at the time to the implementers.

to beat a dead horse, the relational model doesn't discuss anything  
physical at all.  by your line of reasoning using disks would be  
outside.  heaven forbid a buffer pool caching your i/o.

on the other hand we have here a non standard sql extension which  
ties users to sqlite, and blatantly does fly in the face of the  
relational model.

that being said, as richard points out nobody would force anyone to  
use this extension.  i would simply pretend it didn't exist.  my fear  
is more along the lines of what a crutch for query optimization  
problems features like this can become.


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

Re: [sqlite] Mac file locking

[BareFeet]

> My sense is that most users don't want network share support, hence it
> is not default. For the relatively few that do want, it exists as an
> option.

I find that statement a bit ambiguous, so allow me to clarify my own  
interpretation:

1. Few or probably no users want to NOT have network share support.

2. It may be true that few users would ask for network share support,  
at least until they get a "file locked" error when using some SQLite  
GUI or using their own compiled code. I would argue that not having  
yet asked for it doesn't mean they don't want it, just that they don't  
know yet that they need it or can have it. In any case, it certainly  
doesn't mean that they want it to NOT be there (see 1).

3. All users have network share support built in with the SQLite that  
ships in Mac OS X and don't ask for it to be removed.

Thanks,
Tom
BareFeet

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

[sqlite] Network shares

[Dave Dyer]

In modern working environments, network shares are not an abnormality.

Sqlite Network shares work by default for pcs. 

If sqlite network shares don't work by default for macs, it looks like 
sqlite is broken or macs are broken.  I suppose that's why apple made
it unbroken by default in the software they ship.

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

Re: [sqlite] Mac file locking

[P Kishor]

On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> > Right, does sound easy. But what if I am on a Mac (which I am), but
>  > don't want network shares support (which I don't, well, not because I
>  > have anything against it, but I just don't need it) in case it comes
>  > with some side-effects?
>
>
> Well, when you're using the SQLite that shipped on your Mac (or newer
>  version that shipped as part of your latest Mac OS X upgrade), then
>  network shares support is already enabled by default. If you manually
>  compile a newer version of SQLite, then network share support will
>  suddenly disappear. This is inconsistent.

I never use any system provided software for my own creations (well,
not never, but mostly never). One, I can't depend on its ingredients;
two, I can depend on whether it will remain as i expect it to or not
in a subsequent upgrade; and three, it will always be a few versions
behind the most current version usually available from the developer.

I have Leopard 10.5.5 and the /usr/bin/sqlite3 (the factory installed
version) is 3.4.0. I compile my own, both libsqlite as well as
DBD::SQLite (it takes a few clicks of the mouse and builds
painlessly), and am at 3.6.2.

If you are using network shares as provided by the system sqlite, that
capability shouldn't suddenly disappear at all unless you have made
the huge mistake of writing over the factory installation (always
install your own stuff under /usr/local/)

>
>  The inconsistency also spreads to make some GUI apps support network
>  share support (those that use Mac OS X's built in library or are smart
>  enough to have "fixed" the omission in their included  SQLite source
>  code), while others don't (such as those powered by the Zentus JDBC
>  which uses the default SQLite source code settings).
>
>  Of course, if you want to disable network share support for some
>  reason in your own compile, you'd be free to override the flag.
>

My sense is that most users don't want network share support, hence it
is not default. For the relatively few that do want, it exists as an
option.

>
>  >
>  Thanks,
>  Tom
>  BareFeet
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac file locking

[BareFeet]

> Right, does sound easy. But what if I am on a Mac (which I am), but
> don't want network shares support (which I don't, well, not because I
> have anything against it, but I just don't need it) in case it comes
> with some side-effects?

Well, when you're using the SQLite that shipped on your Mac (or newer  
version that shipped as part of your latest Mac OS X upgrade), then  
network shares support is already enabled by default. If you manually  
compile a newer version of SQLite, then network share support will  
suddenly disappear. This is inconsistent.

The inconsistency also spreads to make some GUI apps support network  
share support (those that use Mac OS X's built in library or are smart  
enough to have "fixed" the omission in their included  SQLite source  
code), while others don't (such as those powered by the Zentus JDBC  
which uses the default SQLite source code settings).

Of course, if you want to disable network share support for some  
reason in your own compile, you'd be free to override the flag.

>
Thanks,
Tom
BareFeet

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

Re: [sqlite] tracking table row counts

[Igor Tandetnik]

"P Kishor" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> This is very cool. But, this raises another question (for my
> edification, natch). What if the transaction fails and the db rolls
> back? Do the triggered updates of the counts table also roll back?

Of course. These changes are made within the same transaction.

Igor Tandetnik 



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

Re: [sqlite] Mac file locking

[P Kishor]

On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> > h... I understood DRH's statement as "no, it can't be enabled by
>  > default in the source because that flag works only for Macs and it
>  > causes breakage on other platforms." Unless, he started providing
>  > platform specific code tarballs
>
>
> I wouldn't expect it to require multiple code versions, just one
>  version that sets the locking flag according to what platform it's on.
>  ie if platform == 'Mac' then SQLITE_ENABLE_LOCKING_STYLE = 1
>
>  Surely it's that easy? No?

Right, does sound easy. But what if I am on a Mac (which I am), but
don't want network shares support (which I don't, well, not because I
have anything against it, but I just don't need it) in case it comes
with some side-effects?

I don't know... I am assuming he has some wise reasons for not making
that special case a default. He seems like a very conservative guy
when it comes to fixing what ain't broken.

Still, you have a point, and maybe DRH will expound and enlighten us
on his reticence to enable locking style equal to one.

>
>  Thanks,
>  Tom
>
> BareFeet
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac file locking

[BareFeet]

> h... I understood DRH's statement as "no, it can't be enabled by
> default in the source because that flag works only for Macs and it
> causes breakage on other platforms." Unless, he started providing
> platform specific code tarballs

I wouldn't expect it to require multiple code versions, just one  
version that sets the locking flag according to what platform it's on.  
ie if platform == 'Mac' then SQLITE_ENABLE_LOCKING_STYLE = 1

Surely it's that easy? No?

Thanks,
Tom
BareFeet

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

Re: [sqlite] tracking table row counts

[P Kishor]

On 9/22/08, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> On Mon, Sep 22, 2008 at 04:02:37PM -0600, Dennis Cote scratched on the wall:
>  > P Kishor wrote:
>
>  > > How do I get the number of rows that were deleted by the above DELETE?
>  > > Do I first do a SELECT to find out the number of matches before doing
>  > > the DELETE?
>  >
>  > You don't need to the trigger fires for each row that is deleted,
>
>
>   ...unless the command is "DELETE FROM ;" with no WHERE clause.
>   In that case the table is truncated and no triggers are fired.

Thanks for the caveat. I will set the logic for that in the
application to update the counts table manually.


>
>   To avoid that, either manually clear the row-count for that table or
>   use "DELETE FROM  WHERE 1;".  It will be much slower, but it
>   will delete the rows one at a time, calling the trigger on each one.
>
>-j
>
>
>  --
>  Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
>  "Our opponent is an alien starship packed with atomic bombs.  We have
>   a protractor."   "I'll go home and see if I can scrounge up a ruler
>   and a piece of string."  --from Anathem by Neal Stephenson
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] tracking table row counts

[P Kishor]

On 9/22/08, Dennis Cote <[EMAIL PROTECTED]> wrote:
> P Kishor wrote:
>
> > So, I am creating a new database, and want to set up triggers to track
> > row counts in each table as rows are inserted or deleted.
> >
> > A couple of questions --
> >
> > 1. Do I have to create a separate pair of AFTER INSERT and AFTER
> > DELETE triggers for each table that I want to track,
> >
>
>  Yes.
>
>
> > or is there a way
> > to create a generic trigger that fires whenever any table is touched
> > and returns the table name and the number of rows affected?
> >
> >
>
>  No.
>
>
>
> > 2. Doing a trigger like so is fine
> >
> > sqlite> create trigger del_foo after delete on foo
> >   ...> begin
> >   ...> update counts set rows = rows - 1 where tablename = 'foo';
> >   ...> end;
> >
> > however, what if I
> >
> > DELETE FROM foo WHERE msg LIKE 'sqlite%'
> >
> > How do I get the number of rows that were deleted by the above DELETE?
> > Do I first do a SELECT to find out the number of matches before doing
> > the DELETE?
> >
>
>  You don't need to the trigger fires for each row that is deleted, so you
> always delete 1.
>
>
> >
> > 3. When doing a batch of INSERTs in a transaction, will the trigger
> > fire after the transaction is committed, or on every update? I am
> > assuming the former, but, in that case, how will know how many rows
> > were inserted?
> >
> >
>
>  Same for the insert trigger. It executes after each row is inserted. Always
> add 1.

This is very cool. But, this raises another question (for my
edification, natch). What if the transaction fails and the db rolls
back? Do the triggered updates of the counts table also roll back? I
am assuming "yes" but, that is, oh, so clever, so I want to confirm.


>
>  HTH
>  Dennis Cote
>
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac file locking

[P Kishor]

On 9/22/08, BareFeet <[EMAIL PROTECTED]> wrote:
> >> It only works on a Mac.  The build fails on other posix platforms.
>  >
>  > Then of course, it should only be enabled on Macs.
>
>
> Yes, I humbly agree. Can it please be enabled by default in the source
>  so that when compiled on a Mac,

h... I understood DRH's statement as "no, it can't be enabled by
default in the source because that flag works only for Macs and it
causes breakage on other platforms." Unless, he started providing
platform specific code tarballs, which, I am assuming, is unnecessary
burden for him and his team, this is just as easy to flag the build at
compile time. Not any much more should be required, no?


> it will enable opening of files on a
>  network volume, in the same way as does the SQLite distributed with
>  Mac OS X? This will facilitate consistency between default and user
>  compiled SQLite, and facilitate this functionality across the board
>  for the Mac OS X binary download on the SQLite web site, through to
>  all the GUI programs that use the Zentus (or other) JDBC plug ins.
>  Seems to be a simple fix with many rewards.
>
>  Thanks,
>  Tom
>  BareFeet
>
>   --
>  Compare SQLite GUI programs for Mac OS X:
>  http://www.tandb.com.au/sqlite/compare/?ml
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac file locking

[BareFeet]

>> It only works on a Mac.  The build fails on other posix platforms.
>
> Then of course, it should only be enabled on Macs.

Yes, I humbly agree. Can it please be enabled by default in the source  
so that when compiled on a Mac, it will enable opening of files on a  
network volume, in the same way as does the SQLite distributed with  
Mac OS X? This will facilitate consistency between default and user  
compiled SQLite, and facilitate this functionality across the board  
for the Mac OS X binary download on the SQLite web site, through to  
all the GUI programs that use the Zentus (or other) JDBC plug ins.  
Seems to be a simple fix with many rewards.

Thanks,
Tom
BareFeet

  --
Compare SQLite GUI programs for Mac OS X:
http://www.tandb.com.au/sqlite/compare/?ml

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

Re: [sqlite] tracking table row counts

[Jay A. Kreibich]

On Mon, Sep 22, 2008 at 04:02:37PM -0600, Dennis Cote scratched on the wall:
> P Kishor wrote:

> > How do I get the number of rows that were deleted by the above DELETE?
> > Do I first do a SELECT to find out the number of matches before doing
> > the DELETE?
> 
> You don't need to the trigger fires for each row that is deleted,

  ...unless the command is "DELETE FROM ;" with no WHERE clause.
  In that case the table is truncated and no triggers are fired.

  To avoid that, either manually clear the row-count for that table or 
  use "DELETE FROM  WHERE 1;".  It will be much slower, but it
  will delete the rows one at a time, calling the trigger on each one.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statement help

[Igor Tandetnik]

"Andrew Drummond" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> I am trying to write an sqlite select statement that performs the
> below pseudo code, but am getting nowhere.  Any help we be very
> appreciated.
>
> PSEUDO CODE
>
> for each element1 in (select address.* from numbers,address where
> numbers.number = "12345678"  and numbers.address_id =
> address.address_id) {
>for each element2 in (select numbers.* from numbers where
> address_id = element1.address_id LIMIT 20)
>return element1.* , elemen2.number
> }

select a.*, n.number
from address a join numbers n on (a.address_id=n.address_id)
where a.address_id in (select address_id from numbers where 
number='12345678') and
n.rowid in (select rowid from numbers nn where 
a.address_id=nn.address_id limit 20)

Igor Tandetnik 



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

Re: [sqlite] tracking table row counts

[Dennis Cote]

P Kishor wrote:
> So, I am creating a new database, and want to set up triggers to track
> row counts in each table as rows are inserted or deleted.
> 
> A couple of questions --
> 
> 1. Do I have to create a separate pair of AFTER INSERT and AFTER
> DELETE triggers for each table that I want to track, 

Yes.

> or is there a way
> to create a generic trigger that fires whenever any table is touched
> and returns the table name and the number of rows affected?
> 

No.


> 2. Doing a trigger like so is fine
> 
> sqlite> create trigger del_foo after delete on foo
>...> begin
>...> update counts set rows = rows - 1 where tablename = 'foo';
>...> end;
> 
> however, what if I
> 
> DELETE FROM foo WHERE msg LIKE 'sqlite%'
> 
> How do I get the number of rows that were deleted by the above DELETE?
> Do I first do a SELECT to find out the number of matches before doing
> the DELETE?

You don't need to the trigger fires for each row that is deleted, so you 
always delete 1.

> 
> 3. When doing a batch of INSERTs in a transaction, will the trigger
> fire after the transaction is committed, or on every update? I am
> assuming the former, but, in that case, how will know how many rows
> were inserted?
> 

Same for the insert trigger. It executes after each row is inserted. 
Always add 1.

HTH
Dennis Cote

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

[sqlite] tracking table row counts

[P Kishor]

So, I am creating a new database, and want to set up triggers to track
row counts in each table as rows are inserted or deleted.

A couple of questions --

1. Do I have to create a separate pair of AFTER INSERT and AFTER
DELETE triggers for each table that I want to track, or is there a way
to create a generic trigger that fires whenever any table is touched
and returns the table name and the number of rows affected?

2. Doing a trigger like so is fine

sqlite> create trigger del_foo after delete on foo
   ...> begin
   ...> update counts set rows = rows - 1 where tablename = 'foo';
   ...> end;

however, what if I

DELETE FROM foo WHERE msg LIKE 'sqlite%'

How do I get the number of rows that were deleted by the above DELETE?
Do I first do a SELECT to find out the number of matches before doing
the DELETE?

3. When doing a batch of INSERTs in a transaction, will the trigger
fire after the transaction is committed, or on every update? I am
assuming the former, but, in that case, how will know how many rows
were inserted?

Any other gotchas?


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

Re: [sqlite] Select statement help

[Dennis Cote]

Andrew Drummond wrote:
> 
> for each element1 in (select address.* from numbers,address where
> numbers.number = "12345678"  and numbers.address_id = address.address_id)
> {
> for each element2 in (select numbers.* from numbers where address_id =
> element1.address_id LIMIT 20)
> return element1.* , elemen2.number
> }
> 
> 
> the output would be
> 
> 1Peter12345678
> 1Peter09876654
> 2Paul 12345678
> 

The following query will produce the output above, but it does not 
implement the limit of 20 numbers per address that is shown in your 
pseudo code.

 select a.address_id, a.name, n.number
 from address as a
 join numbers as n on n.address_id = a.address_id
 where a.address_id in
 (select address_id from numbers where number = '12345678')
 order by a.address_id;

I am still working on a complete query, but I have managed to trigger a 
  crash in SQLite in the process.

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

[sqlite] maximum length for a column name

[Andy Chambers]

Hi,

I've checked the "Limits in sqlite" page and don't see any limits to
tablenames or columnnames.  Does that mean there isn't any? (or at
least they're only limited by the maximum length of a sql statement).

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

Re: [sqlite] Speeding up the simplest queries

[Igor Tandetnik]

"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote:
>
>> You need FTS3:
>>   http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
>> It comes with the command line version of SQLite 3.6
>
> You mean: it won't work using SQLite's module for TCL?

I don't think there's going to be any problem (though I have never used 
TCL myself). FTS is accessed using public SQLite API, the same TCL 
binding you use for regular queries should work just fine.

Igor Tandetnik



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

Re: [sqlite] Select statement help

[Seun Osewa]

select a.address_id, a.name, n.number from address a, numbers n where
a.address_id=b.address_id

On Mon, Sep 22, 2008 at 8:28 PM, Andrew Drummond <[EMAIL PROTECTED]> wrote:

> That is given
>
> table "address" contains
>
> address_id  ,   name
> --
> 1,Peter
> 2,Paul
> 3,Mary
> 4,Bob
>
>
> and table "numbers" contains
>
> address_id  , number
> -
> 1, 12345678
> 2, 12345678
> 1, 09876654
> 4, 87876765
>
> the output would be
>
> 1Peter12345678
> 1Peter09876654
> 2Paul 12345678
>
>
> Thanks in advance
>
> Andrew
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Seun Osewa
http://www.nairaland.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speeding up the simplest queries

[P Kishor]

On 9/22/08, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote:
>
>  > select exists (select 1 from MyTable where condition)
>  >
>  > which will return a boolean result, and stop scanning the table after
>  > the first match is found.
>
>
> Yes, thanks - that's right: it is partial solution indeed. "Partial" -
>  because in the case of non-existence it has to scan the table to the end
>  anyway.
>
>  But still it's better than "count(*)".

The bottom line is -- when using conditions, (WHERE constraints),
indexes will (should) speed up your queries except when using LIKE
constraints. In which case, looping through your result and counting
the results will be fast. When not using condition, indexes don't help
anyway, and count(*) involves a full table scan. If you don't want to
do that, maintain a separate table with the count of rows for every
table.


>  --
> pozdrawiam / regards
>
>
> Zbigniew Baniewski
>
> ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote:

> You need FTS3:
>   http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
> It comes with the command line version of SQLite 3.6

You mean: it won't work using SQLite's module for TCL?
-- 
pozdrawiam / regards

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

[sqlite] Slow Query with LEFT OUTER JOIN

[Jason Tudor]

Hello everyone,

Hoping that I could get some help with a performance problem.  Using version
3.5.2

Here are the tables:
CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT)
CREATE TABLE Keywords4Objects (ObjectId INTEGER, KeywordId INTEGER)
CREATE TABLE Keywords (KeywordId INTEGER PRIMARY KEY, Keyword TEXT NOT NULL
COLLATE NOCASE UNIQUE)

The Query:
SELECT DISTINCT o.ObjectId, o.Name
FROM Objects o
LEFT OUTER JOIN Keywords4Objects ok ON ok.ObjectId =
o.ObjectId
LEFT OUTER JOIN Keywords k ON k.KeywordId = ok.KeywordId
WHERE
k.Keyword LIKE 'abc' OR o.Name LIKE 'abc'

Initially, I was just using a join, but objects without keywords were not
getting picked up.  When I switched to Left Outer Joins.  I got the objects
that don't have keywords, but the query is unbearably slow.  It went from
being instantaneous to over a minute.

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

Re: [sqlite] The old bug strikes back

[Shane Harrelson]

This was my fault.   http://www.sqlite.org/cvstrac/chngview?cn=5654
strcasecmp() isn't available on all platforms, and I naively assumed
sqlite3StrICmp() would be (it's not in this case do to the way you
are compiling/linking).   I'll review the issue and see what I can do.

On Mon, Sep 22, 2008 at 4:30 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:

> On Mon, Sep 22, 2008 at 08:03:49AM -0400, D. Richard Hipp wrote:
>
> > Version 3.6.3 fixes several bugs in version 3.6.2, most notably the
> > problem with DISTINCT.
>
> Just tried to compile and link against TCL 8.5.4 - unfortunately, after the
> compilation:
>
> #v+
> % package require sqlite3
> couldn't load file "/usr/lib/tcl8.4/sqlite3/libtclsqlite3.so":
> % /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so: undefined symbol:
> sqlite3StrICmp
> #v-
>
> The problem was there before:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg32800.html
>
> And yes, renaming all "sqlite3StrICmp" occurences to "strcasecmp" (file
> tclsqlite.c) fixed the problem (I guess).
> --
>pozdrawiam / regards
>
>Zbigniew Baniewski
> ___
> 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] Speeding up the simplest queries

[Seun Osewa]

You need FTS3:
  http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
It comes with the command line version of SQLite 3.6

On Mon, Sep 22, 2008 at 8:43 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:

> On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:
>
> > > although not always I want
> > > to fetch all that data. Sometimes I would just to count it.
> >
> > Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But
> > again, if you want to get a count of records, you are unlikely to be
> > able to do any better than a statement using count().
>
> I've got a feeling, you know very good SQLite's internals. How do you
> think:
> is it technically possible to implement much faster searching routine for
> all the LIKE queries?
>
> I'm asking, because I've got no idea presently, whether (or not) the limit
> is just the storage ("flat database file"). So, perhaps supposed different
> one's own procedure has to do about the same, as the built-in, and it'll
> take about the same time? I mean: perhaps different approach to the subject
> is just not possible just because of the limits forced by the storage?
> --
>pozdrawiam / regards
>
>Zbigniew Baniewski
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Seun Osewa
http://www.nairaland.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The old bug strikes back

[Zbigniew Baniewski]

On Mon, Sep 22, 2008 at 08:03:49AM -0400, D. Richard Hipp wrote:

> Version 3.6.3 fixes several bugs in version 3.6.2, most notably the  
> problem with DISTINCT.

Just tried to compile and link against TCL 8.5.4 - unfortunately, after the
compilation:

#v+
% package require sqlite3
couldn't load file "/usr/lib/tcl8.4/sqlite3/libtclsqlite3.so":
% /usr/lib/tcl8.4/sqlite3/libtclsqlite3.so: undefined symbol: sqlite3StrICmp
#v-

The problem was there before:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg32800.html

And yes, renaming all "sqlite3StrICmp" occurences to "strcasecmp" (file
tclsqlite.c) fixed the problem (I guess).
-- 
pozdrawiam / regards

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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Ken]

Seems to me, 

That maybe  "index by" might be better served as an "access by" key phrase 
instead. That way the access to the table can be described, join order etc. Not 
just  pick an index. That way you can programatically describe the "access" 
path, index, full scan, rowid and potentially the join ordering of tables. 

How would you extend the "index by" if in the future you extend sqlite to 
utilise other indexing/(r-tree)/Hash based indexes ???  Or want the user to be 
able to specify other access components?

Some other areas for performance optimizations can occur when column data is 
not evenly distributed making some index paths poor when the column data is of 
low cardinality for a subset of data points. In oracle this is handled via 
histograms and the optimizer rejects indices based upon low cardinality where 
clause field qualifiers.


Just my .02.



--- On Mon, 9/22/08, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
From: Jay A. Kreibich <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Specifing which index to use. Was: Performance/bug in 
multikey 'group by' in 3.6.2
To: "General Discussion of SQLite Database" 
Cc: [EMAIL PROTECTED]
Date: Monday, September 22, 2008, 10:18 AM

On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on the wall:
> I am reluctant to add to SQLite the ability to explicitly specify the  
> index for a query.  I agree with Alex Scotti that the whole idea seems  
> very un-RDBMS like.

  Well it is outside of the Relational Model, that's for sure.
  
  Then again, the whole concept of indexes are outside of the
  Relational Model.

> There seems to be no standard SQL way of providing hints to the query  
> optimizer for which index to use.   Every SQL database engine does it  
> differently.  The MySQL approach is the simplest by far. 

  Since the FROM clause is the only area of a SELECT statement where
  you're always dealing with fully-realized tables (and those are the
  only objects that can be indexed) it does seem like the most direct
  and cleanest approach.

>   SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>   SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>   SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;

  How are the last two different?
  
  It seems that NOT INDEXED would use a table scan, which is essentially
  walking the ROWID b-tree, while "INDEX BY ROWID" is going to use
the
  "index" on ROWID, which isn't really an index, but the
table's root
  b-tree-- e.g. the ROWID b-tree.

  Unless the "BY ROWID" is just some kind of alias for "the
primary b-tree"
  and can be used regardless of if there is a INTEGER PRIMARY KEY or not?
  But wouldn't that still be the same as NOT INDEXED?

  Or would NOT INDEXED force a full table-scan no matter what, even if
  the query had something like "WHERE ROWID = 43" ?



  Also, reading the MySQL docs, it sounds like their syntax is only
  applied to JOIN operations (i.e. none of the examples above).  I'm
  not sure that implies that it forces JOINs to be first or not (given
  an index or NOT given an index).  Since different clauses in a SELECT
  may need to access data from the tables in different ways, it seems
  that telling the system to use or not use an index is only half the
  issue.  You also need to be able to tell the query optimizer what
  you want the index used for (since, in general, the index can only
  be used for the first operation).

  For example, the system might pull out a long series of rows "in
  order" via an index and then join them to a much smaller data set, or
  it might join two large tables with a small result set via in index
  and then sort them.  Even if you tell the optimizer to use (or not
  use) an index, that's only half the story if you can't tell it what
  order you want the operations performed in.

  Or are SELECT operations more or less set in a fixed order for SQLite?

> I further propose that if the specified index cannot be used, the  
> sqlite3_prepare_v2() call will fail with an error.  In other words,  
> the new syntax is a requirement, not a hint.

  Given that the SQLite query optimizer doesn't have access to a large
  amount of (expensive to maintain) statistics, there are always going
  to be limitation in what it can do.  This seems like a good balance
  between simple system design and real-world tuning needs.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a
ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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] Speeding up the simplest queries

[Zbigniew Baniewski]

On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:

> > although not always I want
> > to fetch all that data. Sometimes I would just to count it.
> 
> Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But 
> again, if you want to get a count of records, you are unlikely to be 
> able to do any better than a statement using count().

I've got a feeling, you know very good SQLite's internals. How do you think:
is it technically possible to implement much faster searching routine for
all the LIKE queries?

I'm asking, because I've got no idea presently, whether (or not) the limit
is just the storage ("flat database file"). So, perhaps supposed different
one's own procedure has to do about the same, as the built-in, and it'll
take about the same time? I mean: perhaps different approach to the subject
is just not possible just because of the limits forced by the storage?
-- 
pozdrawiam / regards

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

Re: [sqlite] Core dumps on AIX with optimization

[John Stanton]

If you are using the verson of Sqlite in one big file you are expecting 
a lot from the optimizer.  I would never compile Sqlite that way, 
instead do it the normal way and link the optimized. code.  Also make 
sure you remove the -g option which interferes with some AIX linking.

My experience with IBM's Xlc is that it has a very aggressive optimizer 
and will produce optimized code of high quality, running on one of my 
tests 40% better than gcc code.  I would not compile large files with it.

Ribeiro, Glauber wrote:
> Unfortunately gcc is not an option right now.
> I wouldn't be very surprised if it turned out to be that the very large
> source file is overwhelming IBM's optimizer. So far the non-optimized
> compiles of sqlite3 are working fine. 
> 
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 22, 2008 11:34 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Core dumps on AIX with optimization
> 
> We use gcc.
> 
> Ribeiro, Glauber wrote:
>> This issue continues with version 3.6.2
>>
>> g 
>>
>> -Original Message-
>> From: Ribeiro, Glauber 
>> Sent: Friday, September 19, 2008 11:55 AM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] Core dumps on AIX with optimization
>>
>> Just wondering, are there other AIX Sqlite users out there, and what
>> have you done in order to get a successful compile?
>>
>> I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
>> command line utility sqlite2 dumps core when running SQL that contains
>> an aggregation function, if it was compiled with any level of
>> optimization. It seems to work fine if C optimization is turned off.
>>
>> I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
>> (ticket 3344), and at least one more person reported seeing the same
>> problem.
>>
>> I'm not worried about the lack of optimization, just thinking that
> this
>> may be caused by an underlying problem in the C code.
>>
>> Thanks,
>>
>> glauber
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

On Sun, Sep 21, 2008 at 07:07:33PM -0500, Jay A. Kreibich wrote:

>   It sounds like you need to take a more general approach to speeding
>   up your queries.  If you've not yet looked at building appropriate
>   indexes, that seems like a good place to start.

Right, proper indexing gives significant "boost" (for "sharp" conditions).

I'm afraid, one has just to wait in the case of all the LIKE-s,
unfortunately.
-- 
pozdrawiam / regards

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

[sqlite] Select statement help

[Andrew Drummond]

I am trying to write an sqlite select statement that performs the below
pseudo code, but am getting nowhere.  Any help we be very appreciated.

PSEUDO CODE

for each element1 in (select address.* from numbers,address where
numbers.number = "12345678"  and numbers.address_id = address.address_id)
{
for each element2 in (select numbers.* from numbers where address_id =
element1.address_id LIMIT 20)
return element1.* , elemen2.number
}


That is given

table "address" contains

address_id  ,   name
--
1,Peter
2,Paul
3,Mary
4,Bob


and table "numbers" contains

address_id  , number
-
1, 12345678
2, 12345678
1, 09876654
4, 87876765

the output would be

1Peter12345678
1Peter09876654
2Paul 12345678


Thanks in advance

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

Re: [sqlite] Mac file locking

[Dave Dyer]

>
>It only works on a Mac.  The build fails on other posix platforms.

Then of course, it should only be enabled on Macs.

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

Re: [sqlite] Speeding up the simplest queries

[Zbigniew Baniewski]

On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote:

> select exists (select 1 from MyTable where condition)
> 
> which will return a boolean result, and stop scanning the table after  
> the first match is found.

Yes, thanks - that's right: it is partial solution indeed. "Partial" -
because in the case of non-existence it has to scan the table to the end
anyway.

But still it's better than "count(*)".
-- 
pozdrawiam / regards

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

Re: [sqlite] Mac file locking

[P Kishor]

On 9/22/08, Dave Dyer <[EMAIL PROTECTED]> wrote:
>
>  >
>  >Probably this:
>  >
>  >   http://www.sqlite.org/compile.html#enable_locking_style
>  >
>  >Compilation option "SQLITE_ENABLE_LOCKING_STYLE".
>
>
> Compiling the mac version with this flad defined seems to have
>  fixed my problem.   Is there any reason this shouldn't be on
>  by default?

I am guessing because all SQLite users are not Mac users (although I
am), and/or all SQLite users don't worry about network shares (I
don't).

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


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac file locking

[D. Richard Hipp]

On Sep 22, 2008, at 3:02 PM, Dave Dyer wrote:

>
>>
>> Probably this:
>>
>>  http://www.sqlite.org/compile.html#enable_locking_style
>>
>> Compilation option "SQLITE_ENABLE_LOCKING_STYLE".
>
> Compiling the mac version with this flad defined seems to have
> fixed my problem.   Is there any reason this shouldn't be on
> by default?


It only works on a Mac.  The build fails on other posix platforms.


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] Date/Time Pains

[Scott Baker]

jason weaver wrote:
> I've searched and searched but haven't found anything that really answers
> this question. If I've missed something, please point me in the right
> direction.
> 
> I want to put the "right" type of timestamp in my dbase.  According to my
> research, the "right" type is like this:
> - create table my_table(date_stuff real);

Where did you find the "right" way to do timestamps? I've always used 
unixtimes and never had a problem. It's extremely portable across all 
systems and languages.

Working with Julian datetimes is a little more complicated, in my 
experience.

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

Re: [sqlite] datatype - serial

[Seun Osewa]

INTEGER PRIMARY KEY AUTOINCREMENT

2008/9/22 Victor Hugo Oliveira <[EMAIL PROTECTED]>

> hi everybody,
>
> SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way
> that i can implement myself (i'm not a very experient programmer)?
>
> thanks
>
>
>
>  Novos endereços, o Yahoo! que você conhece. Crie um email novo com a
> sua cara @ymail.com ou @rocketmail.com.
> http://br.new.mail.yahoo.com/addresses
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Seun Osewa
http://www.nairaland.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datatype - serial

[P Kishor]

On 9/22/08, Victor Hugo Oliveira <[EMAIL PROTECTED]> wrote:
> hi everybody,
>
>  SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way that 
> i can implement myself (i'm not a very experient programmer)?

What do you mean by a serial datatype. If you want a sequence, you can
get one with INTEGER PRIMARY KEY


>
>  thanks
>
>
>
>   Novos endereços, o Yahoo! que você conhece. Crie um email novo com a 
> sua cara @ymail.com ou @rocketmail.com.
>  http://br.new.mail.yahoo.com/addresses
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] datatype - serial

[Victor Hugo Oliveira]

hi everybody,

SQLite 3 doesn't have a 'serial' datatype? If not is there a simple way that i 
can implement myself (i'm not a very experient programmer)?

thanks



  Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua 
cara @ymail.com ou @rocketmail.com.
http://br.new.mail.yahoo.com/addresses
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[P Kishor]

On 9/22/08, Steve Friedman <[EMAIL PROTECTED]> wrote:
>
>  >>
>  >> There seems to be no standard SQL way of providing hints to the query
>  >> optimizer for which index to use.   Every SQL database engine does it
>  >> differently.  The MySQL approach is the simplest by far.  But even it
>  >> is more complex than is really needed.  I propose syntax for SQLite as
>  >> follows:
>  >>
>  >>   SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>  >>   SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>  >>   SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;
>  >>
>  >> I further propose that if the specified index cannot be used, the
>  >> sqlite3_prepare_v2() call will fail with an error.  In other words,
>  >> the new syntax is a requirement, not a hint.
>  >>
>  >> Comments?  Objections?
>  >>
>
>
> As a pedant, I have two comments:
>
>  - INDEX BY is a verb form.  I would think that INDEXED BY (a past
>  participle) would be more accurate syntax since no new indices are being
>  constructed.

How about USING INDEX

SELECT * FROM tablex USING INDEX indexy WHERE...;
SELECT * FROM tablex USING INDEX ROWID WHERE ...;
SELECT * FROM tablex NOT USING INDEX WHERE ... ;

>
>  - I presume that the following is not contemplated (and the
>  documentation should reflect this just to avoid surprises):
>  SELECT * FROM (some sub-query) AS t INDEX BY ...
>
>
>  Steve Friedman
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Seun Osewa]

Why something like "USE INDEX a,b,c"?

On Mon, Sep 22, 2008 at 5:42 PM, Steve Friedman <[EMAIL PROTECTED]> wrote:

> As a pedant, I have two comments:
>
> - INDEX BY is a verb form.  I would think that INDEXED BY (a past
> participle) would be more accurate syntax since no new indices are being
> constructed.
>
> - I presume that the following is not contemplated (and the
> documentation should reflect this just to avoid surprises):
> SELECT * FROM (some sub-query) AS t INDEX BY ...
>
> Steve Friedman
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Seun Osewa
http://www.nairaland.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETEs using a range from an indexed column

[Tomas Lee]

On 2008 September 16 (Tue) 07:12:02am PDT, "Jay A. Kreibich" <[EMAIL 
PROTECTED]> wrote:
> On Mon, Sep 15, 2008 at 10:57:37PM -0700, Tomas Lee scratched on the wall:
> 
>> I want to delete the 1000 members with the lowest scores.  Assume that
>> it is extremely unlikely for two members to have identical scores.
>> Also, the 1000 is arbitrary -- it could be more or less on different
>> days.  But each time I do this deletion, it will be a fixed number.
>> 
>> What's the fastest way to do this?  
> 
> 
> Your best bet is to just try things out and see.
> 
> 
>> Method A:
>> * find the 1000th lowest score:
>> SELECT score FROM members ORDER BY score LIMIT 1 OFFSET 999;
>> * delete the records equal to or lower than that score
>> DELETE FROM members WHERE score <= $thousandth_lowest_score;
> 
> Do it as one command using a sub-select:
> 
> DELETE FROM members WHERE score <= (
> SELECT score FROM members ORDER BY score LIMIT 1 OFFSET 999);
> 
> Personally I don't like this, since it has the potential to delete
> too many records.  Having repeating scores may be rare, but that's
> not never.  On the other hand, getting rid of "ties" at the cut-off
> point may be desirable.
> 
>> Method B:
>> * find the uids for the 1000 lowest scores:
>> SELECT uid FROM members ORDER BY score LIMIT 1000;
>> * delete those records
>> DELETE FROM members WHERE uid IN ([join $uids ,]);
> 
> Again, do it as one command:
> 
> DELETE FROM members WHERE uid IN (
> SELECT uid FROM members ORDER BY score LIMIT 1000);
> 
> I'm not sure about speed, but I like this best from from a readability
> standpoint.  It is clean and straight forward, and I wouldn't be
> surprised to find out it is the fastest.
> 
> The only odd thing about this is if several records share the cut-off
> score, there isn't any good way of knowing which will be deleted and
> which will be left behind.  That may or may not matter.
> 
>> Method C:
>> * delete the records as you find them:
>> 
>> sqlite3_prepare_v2(db, "DELETE FROM members WHERE uid = ?", -1, _d, 
>> NULL);
>> sqlite3_prepare_v2(db, "SELECT uid FROM members ORDER BY score LIMIT 1000", 
>> -1, _q, NULL);
>> while (sqlite3_step(stmt_q) == SQLITE_ROW) {
>> int uid = sqlite3_column_int(stmt_q, 0);
>> sqlite3_bind_int(stmt_d, 0, uid);
>> sqlite3_step(stmt_d);
>> sqlite3_reset(stmt_d);
>> }
>> sqlite3_finalize(stmt_d);
>> sqlite3_finalize(stmt_q);
> 
> Essentially a manual version of Method B.
> 
> 
> Which is fastest may depend on the contents of the table and if the
> index will actually be used or not.  There might also be variations if
> the size (the 1000) changes significantly.
> 
> Again, just try it and see.  Using the sub-selects you can do this
> from the command line on a test database to give you a rough idea.

Thanks for the help.  I knew about using sub-selects, but I didn't
want to confuse the issue.  I didn't realize method B and method C
were the same -- I thought SQLite would need to keep all the uids in
memory in method B.

When I was doing my experiments, method A came out faster than method
B, and I like method A better anyway.  But I think either one would
work fast enough for my purposes anyway.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Steve Friedman]

>>
>> There seems to be no standard SQL way of providing hints to the query  
>> optimizer for which index to use.   Every SQL database engine does it  
>> differently.  The MySQL approach is the simplest by far.  But even it  
>> is more complex than is really needed.  I propose syntax for SQLite as  
>> follows:
>>
>>   SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>>   SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>>   SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;
>>
>> I further propose that if the specified index cannot be used, the  
>> sqlite3_prepare_v2() call will fail with an error.  In other words,  
>> the new syntax is a requirement, not a hint.
>>
>> Comments?  Objections?
>>

As a pedant, I have two comments:

- INDEX BY is a verb form.  I would think that INDEXED BY (a past 
participle) would be more accurate syntax since no new indices are being 
constructed.

- I presume that the following is not contemplated (and the 
documentation should reflect this just to avoid surprises):
SELECT * FROM (some sub-query) AS t INDEX BY ...

Steve Friedman

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

Re: [sqlite] Core dumps on AIX with optimization

[John Stanton]

We use gcc.

Ribeiro, Glauber wrote:
> This issue continues with version 3.6.2
> 
> g 
> 
> -Original Message-
> From: Ribeiro, Glauber 
> Sent: Friday, September 19, 2008 11:55 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Core dumps on AIX with optimization
> 
> Just wondering, are there other AIX Sqlite users out there, and what
> have you done in order to get a successful compile?
> 
> I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
> command line utility sqlite2 dumps core when running SQL that contains
> an aggregation function, if it was compiled with any level of
> optimization. It seems to work fine if C optimization is turned off.
> 
> I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
> (ticket 3344), and at least one more person reported seeing the same
> problem.
> 
> I'm not worried about the lack of optimization, just thinking that this
> may be caused by an underlying problem in the C code.
> 
> Thanks,
> 
> glauber
> 
> ___
> 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] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[John Stanton]

To me this is a very rational approach.  It is simple and unambiguous to 
understand and use and simple to implement compared to the alternative 
schemes.  That fits nicely with the "lite" approach.

Ad the directive to the SQL and measure the result and the effect is 
immediately obvious.  Hard to improve on that.

D. Richard Hipp wrote:
> I am reluctant to add to SQLite the ability to explicitly specify the  
> index for a query.  I agree with Alex Scotti that the whole idea seems  
> very un-RDBMS like.
> 
> On the other hand, just because a feature is there does not mean  
> people have to use it.  The documentation can make it clear that the  
> feature should be used rarely and only be experts.  We can make  
> arrangements to omit the feature at compile-time (or perhaps to  
> require a special compile-time option to enable it.)  And, there  
> really do seem to be a few rare cases where explicitly naming the  
> index is helpfull.
> 
> There seems to be no standard SQL way of providing hints to the query  
> optimizer for which index to use.   Every SQL database engine does it  
> differently.  The MySQL approach is the simplest by far.  But even it  
> is more complex than is really needed.  I propose syntax for SQLite as  
> follows:
> 
>   SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>   SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>   SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;
> 
> I further propose that if the specified index cannot be used, the  
> sqlite3_prepare_v2() call will fail with an error.  In other words,  
> the new syntax is a requirement, not a hint.
> 
> Comments?  Objections?
> 
> 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] Date/Time Pains

[Tomas Lee]

On 2008 September 22 (Mon) 06:25:34am PDT, jason weaver <[EMAIL PROTECTED]> 
wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
>>> However, due to database locking issues, I need to do a bunch of
>>> inserts in one transaction or batch.  Thus, I store them in a simple
>>> queue.  Therefore, the julianday('now') won't work because all of my
>>> batch inserts will have the same date and time.  And that doesn't
>>> work very well. ;)
> 
>> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>> You will have to deal with this in any case. E.g. on Windows the system
>> timer resolution is 15ms by default. You can insert quite a few records
>> in 15 ms.
>> It is unrealistic to expect that every record could be inserted with a
>> unique timestamp. Find some other way to ensure uniqueness (e.g. just
>> assign a sequential number to each).
> 
> 
> Thanks for your response.  However, my batch inserts and the uniqueness of
> my timestamps aren't the issue.  I guess I didn't explain my situation well
> enough.  Let me try again.
> 
> 
> 
> I take readings every X seconds which gives me plenty of uniqueness for each
> reading.  I save the SQL statements and then insert them in small batches.
> However, from reading this newsgroup I've learned that the correct way to
> put dates into SQLite is as I described before:
> 
> - create table my_table(date_stuff real);
> 
> - insert into my_table values(julianday('now'));
> 
> In my batch loop, I can't use julianday("now") - I need the timestamp
> to reflect when I took the reading.
> 
> 
> 
> If the right way to put datetime in the dbase is the julianday('now')
> format, I need to be able to create and capture that format in python.
> 
> 
> 
> What is the julianday("now") equivalent in python?  I can't find a simple,
> straight-forward answer to this question.

There isn't a Python equivalent of julianday().  Well, you could write
one yourself, and it's not that hard, but it's doesn't come with Python,
but you don't need it.

See, julianday() can convert a lot of different strings that represent
dates and times into the Julian day equivalents.  What you want is the
equivalent of 'now', and that's easy to get!

>>> import time  
>>> time.strftime("%Y-%m-%dT%H:%M:%S",time.gmtime())
'2008-09-22T16:26:10'

Then you use the string that you get and pass that into the julianday()
function in SQLite

  insert into my_table values(julianday($string_that_strftime_gave_you));

And that's that.

This only gives you resolution down to a second.  If you want
subsecond resolution, then you'll have to use time.time() in Python.
That gives you the number of seconds (which could be a non-integer)
since the epoch.  If you're on a Unix machine, then things are easy:

  julianday($secs_since_epoch, 'unixepoch')

is what you want.

The problem is that you might be on a different machine where the epoch
might be different.  I'm not sure if Python always gives you the Unix
epoch or not -- you'd have to check.  But you can still deal with different
epochs.

First, save the string representing the zero point of the epoch

>>> import time
>>> time.strftime("%Y-%m-%dT%H:%M:%S", time.gmtime(0))
'1970-01-01T00:00:00'

You only need to do this once at the beginning of the program.

Use the time.time() string as before.

Then what you want is

  julianday($epoch_string, '$secs_since_epoch seconds')

And julianday will just start at the epoch and add time.time() to it,
which is what you wanted.

Another thing you can do is just ask SQLite for julianday('now') at the
appropiate time, and save the real number it gives you for later use.

  select julianday('now');

This might be easier.

But do you really need to use the Julian day format?  What do you do with
these dates and times?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Core dumps on AIX with optimization

[Ribeiro, Glauber]

This issue continues with version 3.6.2

g 

-Original Message-
From: Ribeiro, Glauber 
Sent: Friday, September 19, 2008 11:55 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Core dumps on AIX with optimization

Just wondering, are there other AIX Sqlite users out there, and what
have you done in order to get a successful compile?

I found out that on our AIX 5.2, with IBM's Visual Age C v9.0, the
command line utility sqlite2 dumps core when running SQL that contains
an aggregation function, if it was compiled with any level of
optimization. It seems to work fine if C optimization is turned off.

I reported this at http://www.sqlite.org/cvstrac/tktview?tn=3344,2
(ticket 3344), and at least one more person reported seeing the same
problem.

I'm not worried about the lack of optimization, just thinking that this
may be caused by an underlying problem in the C code.

Thanks,

glauber

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

Re: [sqlite] Date/Time Pains

[John Stanton]

jason weaver wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
> news:[EMAIL PROTECTED]
>>> However, due to database locking issues, I need to do a bunch of
>>> inserts in one transaction or batch.  Thus, I store them in a simple
>>> queue.  Therefore, the julianday('now') won't work because all of my
>>> batch inserts will have the same date and time.  And that doesn't
>>> work very well. ;)
> 
>> From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>> You will have to deal with this in any case. E.g. on Windows the system
>> timer resolution is 15ms by default. You can insert quite a few records
>> in 15 ms.
>> It is unrealistic to expect that every record could be inserted with a
>> unique timestamp. Find some other way to ensure uniqueness (e.g. just
>> assign a sequential number to each).
> 
> 
> Thanks for your response.  However, my batch inserts and the uniqueness of
> my timestamps aren't the issue.  I guess I didn't explain my situation well
> enough.  Let me try again.
> 
> 
> 
> I take readings every X seconds which gives me plenty of uniqueness for each
> reading.  I save the SQL statements and then insert them in small batches.
> However, from reading this newsgroup I've learned that the correct way to
> put dates into SQLite is as I described before:
> 
> - create table my_table(date_stuff real);
> 
> - insert into my_table values(julianday('now'));
> 
> In my batch loop, I can't use julianday("now") - I need the timestamp
> to reflect when I took the reading.
> 
> 
> 
> If the right way to put datetime in the dbase is the julianday('now')
> format, I need to be able to create and capture that format in python.
> 
> 
> 
> What is the julianday("now") equivalent in python?  I can't find a simple,
> straight-forward answer to this question.
> 
> 
> 
> Thank you,
> 
> Chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
In your case you can just lift the code from the Sqlite date and time 
routines and include it in your application to get the correct time as a 
REAL.  Alternatively you can store the timestamp in some other way, say 
in ISO format and use the Sqlite functions to convert it to an Sqlite 
type timestamp when you insert.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Shawn Wilsher]

On Mon, Sep 22, 2008 at 8:23 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> In the two high-profile use cases, the programmers already have the
> statement using the "correct" index without an INDEX BY clause.  They
> just want to be alerted if some future schema change alters the index
> choice, perhaps by deleting one of the indexes that were being used.
> If the INDEX BY clause becomes a hint, then this function of the
> clause is removed.  And without the impetus of those two high-profile
> use cases, the functionality will not be added at all.  So, I am
> offering this choice:  (1) The ability to select and index with an
> error if that index won't work and (2) no new capabilities at all.

I can see option (1) being useful to Mozilla, so we'd like to see that if
possible.

Cheers,

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

Re: [sqlite] Date/Time Pains

[Jay A. Kreibich]

On Mon, Sep 22, 2008 at 08:25:34AM -0500, jason weaver scratched on the wall:

> What is the julianday("now") equivalent in python?  I can't find a simple,
> straight-forward answer to this question.

  Just ask SQLite for the time:

SELECT julianday('now');

  (It is single quotes, BTW).

  This should require no locks, no database I/O, and pretty much no
  other resources from SQLite, so you can call it each time you sample
  a new record.  You can then queue them up and and actually insert
  them as needed.  Asking SQLite also means you use the exact same
  date translation code for all operations, which is always a good thing.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Suggestions

[Enrique Ramirez]

On Sat, Sep 20, 2008 at 2:07 PM, ivo welch <[EMAIL PROTECTED]> wrote:
> * sqlitebrowser is very buggy under linux when it comes to importing
> csv files.  often, nothing happens.  sometimes, trying a second import
> works.  just buggy.

sqlitebrowser is a third party application made by someone external to
the original author of SQLite. If I may, I can try and recommend the
GUI client that has worked for me flawlessly.
http://code.google.com/p/sqlite-manager/ is a Firefox addon for
managing SQLite Databases. It has a very active developer, and can be
made to run with XULRunner or other XUL enabled applications if
Firefox isn't your kind of browser.


-- 
// --
Enrique Ramirez Irizarry
Lead Developer
Indie Code Labs
http://www.indiecodelabs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[D. Richard Hipp]

On Sep 22, 2008, at 10:53 AM, Jeffrey Becker wrote:

>   I think the policy other dbms
> systems have of making these things hints rather than requirements is
> a good one because it still allows the query optimizer to make the
> best choice when the hints its given become incorrect.

If you want the query optimizer to make the choice, omit the INDEX BY  
clause all together.  If you include an INDEX BY clause, that is  
saying that you the programmer know better than the optimizer and the  
optimizer is not to second-guess you.

In the two high-profile use cases, the programmers already have the  
statement using the "correct" index without an INDEX BY clause.  They  
just want to be alerted if some future schema change alters the index  
choice, perhaps by deleting one of the indexes that were being used.   
If the INDEX BY clause becomes a hint, then this function of the  
clause is removed.  And without the impetus of those two high-profile  
use cases, the functionality will not be added at all.  So, I am  
offering this choice:  (1) The ability to select and index with an  
error if that index won't work and (2) no new capabilities at all.

> I'd prefer to
> see some sort of programmatic method of doing this.  The method I'd
> find ideal would be to have some sort of sqlite_suggest_* api which
> would allow a user to apply hints to an already prepared statement.

The indices are already chosen by the time the sqlite3_stmt is  
constructed.  It is too late to offer hints after the fact.

>  I
> forget if sqlite_stmt keeps a copy of the sql so I may well be
> suggesting the impossible here.  The api would reinforce the
> non-standard nature of the action while keeping the sql dialect free
> of non-standard sql.

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] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Jay A. Kreibich]

On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched on the wall:
> I am reluctant to add to SQLite the ability to explicitly specify the  
> index for a query.  I agree with Alex Scotti that the whole idea seems  
> very un-RDBMS like.

  Well it is outside of the Relational Model, that's for sure.
  
  Then again, the whole concept of indexes are outside of the
  Relational Model.

> There seems to be no standard SQL way of providing hints to the query  
> optimizer for which index to use.   Every SQL database engine does it  
> differently.  The MySQL approach is the simplest by far. 

  Since the FROM clause is the only area of a SELECT statement where
  you're always dealing with fully-realized tables (and those are the
  only objects that can be indexed) it does seem like the most direct
  and cleanest approach.

>   SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>   SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>   SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;

  How are the last two different?
  
  It seems that NOT INDEXED would use a table scan, which is essentially
  walking the ROWID b-tree, while "INDEX BY ROWID" is going to use the
  "index" on ROWID, which isn't really an index, but the table's root
  b-tree-- e.g. the ROWID b-tree.

  Unless the "BY ROWID" is just some kind of alias for "the primary b-tree"
  and can be used regardless of if there is a INTEGER PRIMARY KEY or not?
  But wouldn't that still be the same as NOT INDEXED?

  Or would NOT INDEXED force a full table-scan no matter what, even if
  the query had something like "WHERE ROWID = 43" ?



  Also, reading the MySQL docs, it sounds like their syntax is only
  applied to JOIN operations (i.e. none of the examples above).  I'm
  not sure that implies that it forces JOINs to be first or not (given
  an index or NOT given an index).  Since different clauses in a SELECT
  may need to access data from the tables in different ways, it seems
  that telling the system to use or not use an index is only half the
  issue.  You also need to be able to tell the query optimizer what
  you want the index used for (since, in general, the index can only
  be used for the first operation).

  For example, the system might pull out a long series of rows "in
  order" via an index and then join them to a much smaller data set, or
  it might join two large tables with a small result set via in index
  and then sort them.  Even if you tell the optimizer to use (or not
  use) an index, that's only half the story if you can't tell it what
  order you want the operations performed in.

  Or are SELECT operations more or less set in a fixed order for SQLite?

> I further propose that if the specified index cannot be used, the  
> sqlite3_prepare_v2() call will fail with an error.  In other words,  
> the new syntax is a requirement, not a hint.

  Given that the SQLite query optimizer doesn't have access to a large
  amount of (expensive to maintain) statistics, there are always going
  to be limitation in what it can do.  This seems like a good balance
  between simple system design and real-world tuning needs.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Where to use PRAGMAs

[Ed Hawke]

Hi all,

I'm using a C++ wrapper to SQLite (CppSQLite3) and want to be able to 
set the temp_store, page_size, cache_size etc. I am currently doing this 
directly after opening the database (but before creating any tables) 
with the sqlite3_exec statement, however when I come to do anything to 
the tables if I check their statuses they have been reset to the default 
values. What am I doing wrong?

Regards,

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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Jeffrey Becker]

First, I have to agree that this is very 'un-rdbmsish'.  I understand
that sometimes the programmer really does know better than the DB
engine which indexes it should use.  However, the RDBMS is
fundamentally an abstraction layer.  I think the policy other dbms
systems have of making these things hints rather than requirements is
a good one because it still allows the query optimizer to make the
best choice when the hints its given become incorrect. I'd prefer to
see some sort of programmatic method of doing this.  The method I'd
find ideal would be to have some sort of sqlite_suggest_* api which
would allow a user to apply hints to an already prepared statement.  I
forget if sqlite_stmt keeps a copy of the sql so I may well be
suggesting the impossible here.  The api would reinforce the
non-standard nature of the action while keeping the sql dialect free
of non-standard sql.

On Mon, Sep 22, 2008 at 10:07 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> I am reluctant to add to SQLite the ability to explicitly specify the
> index for a query.  I agree with Alex Scotti that the whole idea seems
> very un-RDBMS like.
>
> On the other hand, just because a feature is there does not mean
> people have to use it.  The documentation can make it clear that the
> feature should be used rarely and only be experts.  We can make
> arrangements to omit the feature at compile-time (or perhaps to
> require a special compile-time option to enable it.)  And, there
> really do seem to be a few rare cases where explicitly naming the
> index is helpfull.
>
> There seems to be no standard SQL way of providing hints to the query
> optimizer for which index to use.   Every SQL database engine does it
> differently.  The MySQL approach is the simplest by far.  But even it
> is more complex than is really needed.  I propose syntax for SQLite as
> follows:
>
>  SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
>  SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
>  SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;
>
> I further propose that if the specified index cannot be used, the
> sqlite3_prepare_v2() call will fail with an error.  In other words,
> the new syntax is a requirement, not a hint.
>
> Comments?  Objections?
>
> 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

[sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[D. Richard Hipp]

I am reluctant to add to SQLite the ability to explicitly specify the  
index for a query.  I agree with Alex Scotti that the whole idea seems  
very un-RDBMS like.

On the other hand, just because a feature is there does not mean  
people have to use it.  The documentation can make it clear that the  
feature should be used rarely and only be experts.  We can make  
arrangements to omit the feature at compile-time (or perhaps to  
require a special compile-time option to enable it.)  And, there  
really do seem to be a few rare cases where explicitly naming the  
index is helpfull.

There seems to be no standard SQL way of providing hints to the query  
optimizer for which index to use.   Every SQL database engine does it  
differently.  The MySQL approach is the simplest by far.  But even it  
is more complex than is really needed.  I propose syntax for SQLite as  
follows:

  SELECT * FROM tablex INDEX BY indexy WHERE ... ORDER BY ...;
  SELECT * FROM tablex INDEX BY ROWID WHERE ... ORDER BY ...;
  SELECT * FROM tablex NOT INDEXED WHERE ... ORDER BY ...;

I further propose that if the specified index cannot be used, the  
sqlite3_prepare_v2() call will fail with an error.  In other words,  
the new syntax is a requirement, not a hint.

Comments?  Objections?

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] Date/Time Pains

[Jeffrey Becker]

Instead of doing julianday('now') why not put a text-based timestamp
into whatever object you're queueing up and pass that into the
julianday() function to convert it to a real.

On Mon, Sep 22, 2008 at 9:51 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrote:
>> "jason weaver" <[EMAIL PROTECTED]> wrote:
>>  news:[EMAIL PROTECTED]
>>  >> However, due to database locking issues, I need to do a bunch of
>>  >> inserts in one transaction or batch.  Thus, I store them in a simple
>>  >> queue.  Therefore, the julianday('now') won't work because all of my
>>  >> batch inserts will have the same date and time.  And that doesn't
>>  >> work very well. ;)
>>
>>
>> >From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>>
>> >You will have to deal with this in any case. E.g. on Windows the system
>>  >timer resolution is 15ms by default. You can insert quite a few records
>>  >in 15 ms.
>>  >It is unrealistic to expect that every record could be inserted with a
>>  >unique timestamp. Find some other way to ensure uniqueness (e.g. just
>>  >assign a sequential number to each).
>>
>>
>>
>> Thanks for your response.  However, my batch inserts and the uniqueness of
>>  my timestamps aren't the issue.  I guess I didn't explain my situation well
>>  enough.  Let me try again.
>>
>>
>>
>>  I take readings every X seconds which gives me plenty of uniqueness for each
>>  reading.  I save the SQL statements and then insert them in small batches.
>>  However, from reading this newsgroup I've learned that the correct way to
>>  put dates into SQLite is as I described before:
>>
>>
>>  - create table my_table(date_stuff real);
>>
>>
>> - insert into my_table values(julianday('now'));
>>
>>
>> In my batch loop, I can't use julianday("now") - I need the timestamp
>>  to reflect when I took the reading.
>>
>>
>>
>>  If the right way to put datetime in the dbase is the julianday('now')
>>  format, I need to be able to create and capture that format in python.
>>
>>
>>
>>  What is the julianday("now") equivalent in python?  I can't find a simple,
>>  straight-forward answer to this question.
>
>
> I have no idea what the julianday("now") equivalent in Python is, but
> why not let SQLite do the work? Here is how I would do it with Perl...
> (mix of pseudo-code and Perl ahead)
>
> # prepare statement
> $sth = $dbh->prepare(qq{
>  INSERT INTO table (somecol, timestamp)
>  VALUES (?, julianday("now"))
> });
>
> open transaction...
>
> # loop through your readings
> $sth->execute($somecol);
>
> end transaction...
>
> $dbh->commit;
>
> or throw error...
>
> The above works for me.
>
> Keep in mind though... Igor's caveat about not having enough
> resolution for timing might apply. So, you might have to use some kind
> of high resolution timer. CPAN has one us Perl aficionados...
> conveniently, it is called Time::HiRes
> 
>
> I am sure you have one for your Python world as well. If you decide to
> use something like that, then you will have to modify your code
> accordingly.
>
>>
>>
>>
>>  Thank you,
>>
>>  Chris
>>
>> ___
>>  sqlite-users mailing list
>>  sqlite-users@sqlite.org
>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> 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/
> ___
> 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] Date/Time Pains

[P Kishor]

On 9/22/08, jason weaver <[EMAIL PROTECTED]> wrote:
> "jason weaver" <[EMAIL PROTECTED]> wrote:
>  news:[EMAIL PROTECTED]
>  >> However, due to database locking issues, I need to do a bunch of
>  >> inserts in one transaction or batch.  Thus, I store them in a simple
>  >> queue.  Therefore, the julianday('now') won't work because all of my
>  >> batch inserts will have the same date and time.  And that doesn't
>  >> work very well. ;)
>
>
> >From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>
> >You will have to deal with this in any case. E.g. on Windows the system
>  >timer resolution is 15ms by default. You can insert quite a few records
>  >in 15 ms.
>  >It is unrealistic to expect that every record could be inserted with a
>  >unique timestamp. Find some other way to ensure uniqueness (e.g. just
>  >assign a sequential number to each).
>
>
>
> Thanks for your response.  However, my batch inserts and the uniqueness of
>  my timestamps aren't the issue.  I guess I didn't explain my situation well
>  enough.  Let me try again.
>
>
>
>  I take readings every X seconds which gives me plenty of uniqueness for each
>  reading.  I save the SQL statements and then insert them in small batches.
>  However, from reading this newsgroup I've learned that the correct way to
>  put dates into SQLite is as I described before:
>
>
>  - create table my_table(date_stuff real);
>
>
> - insert into my_table values(julianday('now'));
>
>
> In my batch loop, I can't use julianday("now") - I need the timestamp
>  to reflect when I took the reading.
>
>
>
>  If the right way to put datetime in the dbase is the julianday('now')
>  format, I need to be able to create and capture that format in python.
>
>
>
>  What is the julianday("now") equivalent in python?  I can't find a simple,
>  straight-forward answer to this question.


I have no idea what the julianday("now") equivalent in Python is, but
why not let SQLite do the work? Here is how I would do it with Perl...
(mix of pseudo-code and Perl ahead)

# prepare statement
$sth = $dbh->prepare(qq{
  INSERT INTO table (somecol, timestamp)
  VALUES (?, julianday("now"))
});

open transaction...

# loop through your readings
$sth->execute($somecol);

end transaction...

$dbh->commit;

or throw error...

The above works for me.

Keep in mind though... Igor's caveat about not having enough
resolution for timing might apply. So, you might have to use some kind
of high resolution timer. CPAN has one us Perl aficionados...
conveniently, it is called Time::HiRes


I am sure you have one for your Python world as well. If you decide to
use something like that, then you will have to modify your code
accordingly.

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


-- 
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/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Suggestion

[Griggs, Donald]

Hello, Ivo,

Regarding:
   
   "I hope my logarithm and standard deviation suggestion make it into
the list of possible future features."

Since sqlite is used for everything from tiny mp3 player firmware to
multi-gigabyte databases, the "lite" requirement precludes inclusion of
too many functions in the base package.   It appears you may find
exactly what you're looking for in a contributed extension referenced
below.   

Sqlite also makes it easy to program your own extensions.


http://sqlite.org/contrib

extension-functions.c (50.49 KB) contributed by Liam Healy on 2008-09-14
19:11:08

Provide mathematical and string extension functions for SQL queries
using the loadable extensions mechanism. Math: acos, asin, atan, atn2,
atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan,
cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square,
ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim,
rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
Aggregate: stdev, variance, mode, median, lower_quartile,
upper_quartile. 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of ivo welch
Sent: Sunday, September 21, 2008 9:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Simple Suggestion

I know I could get columne names.  nevertheless, I find that
  SHOW columns FROM table
was a pretty intuitive way to get this information.  information schema
is less intuitive, but I would be happy to have something that works
across the board.  why not have all of them?

I hope my logarithm and standard deviation suggestion make it into the
list of possible future features.

thanks for the sqlitebrowser note.

regards,

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


This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date/Time Pains

[jason weaver]

"jason weaver" <[EMAIL PROTECTED]> wrote:
news:[EMAIL PROTECTED]
>> However, due to database locking issues, I need to do a bunch of
>> inserts in one transaction or batch.  Thus, I store them in a simple
>> queue.  Therefore, the julianday('now') won't work because all of my
>> batch inserts will have the same date and time.  And that doesn't
>> work very well. ;)

>From: "Igor Tandetnik" <[EMAIL PROTECTED]>
>You will have to deal with this in any case. E.g. on Windows the system
>timer resolution is 15ms by default. You can insert quite a few records
>in 15 ms.
>It is unrealistic to expect that every record could be inserted with a
>unique timestamp. Find some other way to ensure uniqueness (e.g. just
>assign a sequential number to each).


Thanks for your response.  However, my batch inserts and the uniqueness of
my timestamps aren't the issue.  I guess I didn't explain my situation well
enough.  Let me try again.



I take readings every X seconds which gives me plenty of uniqueness for each
reading.  I save the SQL statements and then insert them in small batches.
However, from reading this newsgroup I've learned that the correct way to
put dates into SQLite is as I described before:

- create table my_table(date_stuff real);

- insert into my_table values(julianday('now'));

In my batch loop, I can't use julianday("now") - I need the timestamp
to reflect when I took the reading.



If the right way to put datetime in the dbase is the julianday('now')
format, I need to be able to create and capture that format in python.



What is the julianday("now") equivalent in python?  I can't find a simple,
straight-forward answer to this question.



Thank you,

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

Re: [sqlite] [3.6.2] make test errors

[Frederik Himpe]

On Sun, 21 Sep 2008 10:03:07 +0400, Alexander Batyrshin wrote:

> I don't know, if it's really needed by community. But I got this erros
> by "make test" on x86_64-pc-linux-gnu platform:
> 
> 12 errors out of 23377 tests
> Failures on these tests: lookaside-1.4 lookaside-1.5 memsubsys1-2.3
> memsubsys1-2.4 memsubsys1-3.2.4 memsubsys1-4.3 memsubsys1-4.4
> memsubsys1-5.3 memsubsys1-6.3 memsubsys1-6.4 memsubsys1-7.4
> memsubsys1-7.5

http://www.sqlite.org/cvstrac/tktview?tn=3362

-- 
Frederik Himpe

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

Re: [sqlite] Simple Suggestion

[ivo welch]

I know I could get columne names.  nevertheless, I find that
  SHOW columns FROM table
was a pretty intuitive way to get this information.  information
schema is less intuitive, but I would be happy to have something that
works across the board.  why not have all of them?

I hope my logarithm and standard deviation suggestion make it into the
list of possible future features.

thanks for the sqlitebrowser note.

regards,

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

[sqlite] SQLite version 3.6.3

[D. Richard Hipp]

SQLite version 3.6.3 is now available for download from the website

 http://www.sqlite.org/download.html

Version 3.6.3 fixes several bugs in version 3.6.2, most notably the  
problem with DISTINCT.


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] Mac file locking

[Dan]

On Sep 22, 2008, at 12:37 PM, Dave Dyer wrote:

>
>>
>> Yes, I've noticed and reported this same problem. It appears that the
>> SQLite bundled in Mac OS X has a special flag set to allow opening
>> database files on networked volume, and that works great.
>
>
> So what's the name of this special flag?

Probably this:

   http://www.sqlite.org/compile.html#enable_locking_style

Compilation option "SQLITE_ENABLE_LOCKING_STYLE".

Dan.


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