On Fri, 2005-12-16 at 11:11 -0800, Kevin Brown wrote:
> Geo Carncross wrote:
> > On Thu, 2005-12-15 at 14:16 -0800, Kevin Brown wrote:
> > > Paul J Stevens wrote:
> > > > And I suspect 5.0 will be become a requirement for 2.3+ so we can
> > > > start working with views, triggers, etc...
> > >
> > > Hmm...that would imply ditching SQLite, wouldn't it?
> > >
> > > Not that I have a problem with that, mind you.
> >
> > Why? SQLite has subselects, transactions, views and triggers.
>
> Oh. Wow. I'm surprised and impressed.
>
> It has stored procs as well? I have no real experience with SQLite...
Not exactly. You can use triggers, but it's single process and that
process is always local: You can make custom functions and aggregates,
either in C, perl, TCL (possibly others) - which albeit, isn't the same
thing, but it's "good enough" for most cases.
Look at sqlite/dbsqlite.c - note the dbsqlite_current_timestamp()
function for an example of a custom function (CURRENT_TIMESTAMP)
Although, now that I'm looking at it, what's this SQL_REPLYCACHE_EXPIRE
thingy? That's just wrong.
The correct statement is:
const char *SQL_REPLYCACHE_EXPIRE = "(CURRENT_TIMESTAMP_UNIX()-%s)";
Then a:
static void dbsqlite_current_timestamp_unix(sqlite_func *f, int argc
UNUSED, const char **argv UNUSED)
{
char buf[63];
sprintf(buf, "%ld", time(NULL)); /* assumes time() is signed int */
(void)sqlite_set_result_string(f,buf,-1);
}
And in db_connect,
if (sqlite_create_function(conn, "CURRENT_TIMESTAMP_UNIX", 0,
dbsqlite_current_timestamp_unix, 0) != SQLITE_OK) {
sqlite_close(conn);
trace(TRACE_ERROR,
"%si,%s: sqlite_create_function failed", __FILE,__func__);
return -1;
}
(right before the return 0);
> > Historically, I've found it faster than Pg7 and MySQL 3 and 4 in most of
> > my own applications, and I've even used it in a distributed database
> > application.
>
> I wonder how well it handles lots of concurrency...
Read concurrency? Just fine.
Write concurrency? Very poorly :)
There are things you can do to speed it up (the ATTACH syntax is a good
one)- but see for dbmail, I have one SQLite database per mailbox because
I'm not interested in sharing them -- If I were, I would use the ATTACH
syntax to join two database files into one database (for the session).
Since writing a message to the database doesn't take long, and the IMAP
user wouldn't notice anyway (in Pg because of row-versioning) it's not
so bad, really.
In fact: for most tasks, I've found SQLite is just fine. CVSTrac (for
example) is a web-based wiki + CVS-oriented bugtracker that uses SQLite,
and it handles plenty of traffic :)
... but then, writes are less frequent than reads, and in general very
very fast.
About the only time SQLite performs poorly is when writes take a long
time AND are monopolizing the time. In those cases, Pg is usually what I
use (because of versioning) -as usually in these cases, MySQL would
never perform any better (usually much worse with global table locking)
- anyway, I haven't used MySQL for a while :)
--
Internet Connection High Quality Web Hosting
http://www.internetconnection.net/
