Hi Misc,

i'm looking for experience of using SQLite on OpenBSD.

if anybody in the list can share

1) how SQLite is being used
2) size of the database
3) performance metrics (if you have them)

anything about SQLite on OpenBSD. link would be appreciated too.

cheers!

/e

[ message continues ]

Well, we enjoy nice relations with the people developping sqlite, who seem
to be very nice people.

Apart from that, sqlite is currently in our ports tree, most specifically
because of a 'chicken and egg' issue: it could be useful in base, but nothing
uses it yet, so there's no incentive to put it in base. And since it's not
in base, nothing develops to use it.

Personally, if I did use it, it would be through the perl frontend, so that's
even more daunting, as I don't believe DBI is audited enough nor sane enough
to fit in base yet...

Apart from that, it's used for sqlports, a very useful database that holds
about every meta-information for every port (very useful when you're looking
for all usage of a specific construct before changing it). It weighs in
at roughly 40M. There is a smaller version at 24M, with a "saner" schema,
but not a lot of things use it.

I think landry has tools that use this as well.

In all, it's used precisely for its defining purposes: a simple database when
you don't want to go to the hastle of setting up a server.

In my personal case, it's been the first time ever I started using databases.
All the shit required to set up mysql/postgres and adding "new" users with
new priority did not seem relevant in a world where I have limited time to
do enjoyable stuff.   I've since learnt minimal amounts of database knowledge
thanks to drupal, and ended up with an healthy loathing of the bloated
industry giant that calls itself Oracle, along with its mafia of instructors
and certifications, and paid services that could often run on much lighter
systems, but would be ways less profitable to all those crooks...

[ message continues ]

I use it left and right on a product we are developing and it is very
very good and easy to use.  The API is surprisingly good considering the
underlying complexity.  The docs are pretty good once you get a grip on

We use them in all kinds of sizes and we will use it in the (100s of)
millions of records in the near future.  So far I have not seen any

I have no metrics for you just some advice.  Use the prepare/commit
thing and life is good.  If you don't then you'll get max 160 inserts

I just used the sqlite webpages at http://www.sqlite.org/capi3ref.html

Again the docs are very good but require "practice".

The only thing I don't like is not having access to a non-sql API.  One
of the things I use it for is for a basic b+tree and I really could have
done without the sql shiz.

That said, I have nothing ugly to say about it and in fact am very
pleased with it.

[ message continues ]

Thanks to Marco, Marc and Jim for the responses.

gives me a warm, fuzzy feeling to go on ahead and continue using SQLite.

On Tue, Dec 7, 2010 at 11:48 PM, Marco Peereboom <slash@peereboom.us> wrote:


having looked into its innards, i'm trying to see how much time it
would take me to remove the SQL shiz from it. maybe in a year's time.

thanks again and have a wonderful new year to all.

/e

[ message continues ]

little need for that.  a key/value table and a prepared statement with
bound parameters is basically a direct interface.  the C interface to
a btree isn't going to look much different.  the overhead is about 0.

i've built what amounted to a file system in sqlite without taxing it
too heavily.  several gigabytes of data.

the only problem i've ever had with sqlite was shortly after the query
optimizer was added, it rather spectacularly unoptimized a hand tuned
query.  this only affects fairly complicated sql queries, and is
corrected easily enough by rewriting the query using some tricks.

i will also add that you should never put a sqlite database on nfs if
you can help it.  this is not sqlite's fault, but file locking on nfs
simply doesn't work.  bad things will happen, sometimes even if you're
not sharing the database.

[ message continues ]

I started using it indirectly with fossil, which is a scm made by the same guy
that made SQlite.

It gets slow(er than CVS) with big repos; e.g., NetBSD or OpenBSD.  But it's
still nice nice and I believe they're working on it.


The advantage of having a robust api, instead of interfacing with the db the
scm's utility's restricted cmd line arguments, like git, paysoff greatly.  Once
you're familiar with SQlite you don't really have to interface with the repo

[ message continues ]