sqlite file format, anyone?

Ben & Michelle Carlyle fuzzybsc at optusnet.com.au
Sun Jun 22 14:36:36 CDT 2003


Hello,

First up, apologies if I'm inadvertantly breaking any list nettiquite. I'm
using an unfamiliar mail reader[1] and am a little unfamiliar with the list
conventions per se. It appears that your standard way to reply to a message
is as a reply-to-all, so I'm following that convention for now :) I've been
playing with the settings in Outlook, and it's promised me that it will wrap
my text at 76 characters. If this, in fact, does not happen I'll look for
alternative ways to do this.

I've mostly replied to a single email on this list, but I've included a few
useful snippets from earlier posts at the top which I thought my input might
be relevant on:

From: "Derek Atkins" <warlord at MIT.EDU>
>         the developers made a concious decision that the minimum
> resolution is 1024x768 and have taken great efforts to make sure
> the standard distribution works on that resolution.  In that vein
> we decided that 800x600 was "too small" to display all the information
> we want.  Considering that the only machines that are limited to 800x600
> are at least 4-5 years old at this point, we decided this was
> "reasonable".

It's interesting to note that my windows machine which is capable of well
over 800x600 in fact runs 800x600 by default. Why? My wife needs glasses to
read. Every time I change the resolution to a higher number, even when I
increase the font sizes somewhat to compensate she refuses to keep the
settings. The icons are too small. The text is too small. There's always a
reason (excuse? :). Anyway, just as a little feedback when she read the
paragraph above she made a few dirty comments about discriminating against
people who can't see well :)

<rant> It's not just an issue with gnucash, of course. This is an issue I've
noticed for a long time as one of my first linux machines was a laptop with
only 640x480 resolution. I think that the free software windowing systems
and applications aren't quite there, yet, when it comes to using the space
that they have as efficiently as many windows applications do. Sure, if your
resolution isn't high enough on a windows machine you might not see the
whole of your whiz-bang toolbar, but you can usually cope reasonably well.
Under linux you'll often find programs are completely unusable as relevant
resizable fields get squished to nothing while irrelevant fixed size fields
take up too much space. </rant>

Anyway, my point is that it's not just a matter of what machines are capable
of doing. It's also about whether the users are comforable with operating at
the machine's full capabilities in these respects :)

In other words, it would be nice to be able to use the scheduled transaction
display, or the tax setup display at 800x600, even if it meant reducing the
font sizes. Perhaps this is already possible :) Again, gnome is not my usual
playpen. I'm actually more of an ion-man when it comes to window managers.
Minimalist me.

> One of the major problems with SQLite is that it doesn't have any
> data types.  They may consider this a feature, but I consider it a
> bug.  It means that the application needs to do all the conversions
> instead of the SQL engine.  Why not centralize that functionality
> instead of forcing all application writers to re-implement the same
> bindings?

True, and date handling is a very good example of this. In my application
I've ended up storing time essentially as UTC time_ts. This is a good
mapping for C/C++ but may not be for other languages. It's certainly not a
good implimentation for date without time. I belive the current
recommendation from the sqlite users group is something like "YYYYMMDD",
which sorts lexicographically so can be used in indices. It's also possible
to extract the datatype so that in your callback you could determine
automatically that a particular field was a date and deal with it
differently.

I'm curious as to whether any other data types would become a problem for
this work. Looking through the source of 8.1.4 I'm mostly seeing chars,
ints, text, and timestamp. If timestamp is the only one requiring special
processing you may find that it's not that much trouble to use sqlite.

From: "Derek Atkins" <warlord at MIT.EDU>
> Matthew Vanecek <mevanecek at yahoo.com> writes:
>           I would think we could just:
> BEGIN;
> UPDATE ...;
> UPDATE ...;
> COMMIT;
> Isn't that good enough?  I would think that this would be
> sufficient, right?
>
> The only thing I don't know is if this needs to be passed as a single
> query or multiple queries are "good enough".

I belive that all database engines relevant to this discussion are capable
of issuing these commands in seperate calls without any trouble. Ultimately
transactions should be managed at the same level as the change is being
logically processed. If it's an update to a transaction it should be managed
by whatever code is activated when you press the enter key to logically
update the backend.

> > > > in postgres, you can broadcast an 'event' which can be any string
> > > > (typically a table name), and anyone 'listening' would get it.
> > > Hmm, ok..  How do you set this up?  I dont know if there is a good
> > > way to go this generically.  I don't know if MySQL or SQLite have
> > > events, or if they do I have no clue how to set them up.
> > You just call PQnotify() in Postgresql.  Any registered listener will
> > receive the notification the next time it queries for events.  It's not
> > automatic--you actually have to query for any notifies.
> Ok, what do you register for?  Do you register for a particular row
> in a table?  Or register for a table?  Or register for something else?
> I know that SQLite doesn't have this.  I don't know if MySQL does.

It seems to me that this functionality is logically equivilent to having a
"notifications" table which contains the set of notifications you might want
to send. Every time you want to send a notification you SET count = count+1,
and the next time it's queried the count could be compared with the value
cached in memory. It seems to me that if you're going to generalise your sql
backend that's the way you'd do your notifications because I'm sure there's
no standard SQL notification scheme :)

Anyway, it stll requires a query. This means you need to either periodically
or based on some event trigger go and query the database.

> > I'm hoping my version of the PG backend is pluggable enough that you can
> > just plug-n-play the db access code.  The bulk of the work is in the
> > Query decoding and Engine loading.  Simple things like submitting
> > queries and parsing results are rather easily coded.
> Good.  I'm hoping for this, too.

Even if there turn out to be differences in how different databases need to
be treated you may be able to have some kind of back-end back-end for each
;) Different sql engines may optimise queries differently, for one example.
You may have to send slightly different queries to each in order to achieve
appropriate performance levels. I've certainly found that even the most
subtle differences between back-end technologies can make life very
difficult if you're trying to write code (including SQL) which is both
portable and performs well. On the other hand, you could just use libdbi and
ignore the possibility of needing this kind of tuning. After all, small
databases perform well even when they're not doing things as efficiently as
possible. libdbi supports sqlite, also, for the record ;)

> > My only objection to libdbi, really, is that it won't let you have the
> > embedded mysql.
> Well, it would -- we'd just need to build the libdbi backend for
> embedded-mysql.  It shouldn't be too hard to do it based on the
> existing mysql backend that's already written.

Correct me if I'm wrong, and it's quite likely that I am, but mysql doesn't
have transaction support on it's native tables, does it? It has some extra
table types that do support transactions, but the transactions only work
within the table, not across tables...
... at least that was what I came to understand about mysql's file formats
when I was investigating various embedded database possibilities for my
commerical work. To put this in context I use sqlite as database backend for
a historical data recording system on SCADA systems. The databases
themselves are pretty trivial (two tables, a few columns), but they stand up
to what I throw at them very well.

> >    If I work it right, however, Derek ought to be able to
> > write the mysql code and just tack it on to the back of the SQL
> > backend--replacing the PG or libdbi code.
> That's my hope...  I'm just getting anxious to start in... ;)

:) I come mostly from a C++ background, so I have a little bit of catching
up to do with much of the gnome functionality but I'm very interested in
helping out. I really think that the current no-transaction load/save
default backend for gnucash is the biggest hurdle to it being a truely
fantastic product. I need to know that when I pull out the power cord on my
machine that the last transaction I pressed enter on really is safely on
disk. I'm involved with railways, so I do work that comes under safety
audit... but it's a common phrase around my office "There's
safety-criticial, then there's money-critical". Even individual hobbyist
users of gnucash may take their transactions very seriously and may not like
to lose them :)

Benjamin.



More information about the gnucash-devel mailing list