Madeleine, SQLite and multi-platform issues (in ruby :-)

ruby-talk
1

Hi list,

I need some advice around Ruby, SQLite an cross-platform problems. I
maintain Pimki, a wiki-based personal organiser[1]. So far, it was
using Madeleine as the backend storage format. However Madeleine seems
problematic: troubles in refactoring class/method names between Pimki
revision, occasional crashes and problems with the zip compression.

For Pimki2 I have the option of continuing with Madeleine or to switch
to SQLite/ActiveRecord. This will (hopefully) make Pimki more stable
and development easier, but will it make users happier? SQLite may just
be an unnecessary dependency / complexity.

I started to collect user profiles via a survey
(http://rubyforge.org/survey/survey.php?group_id=447&survey_id=25) to
get a better impression, but I'd also highly appreciate input from this
list on:

* Ease of SQLite install on major platforms
* Moving applications from Madeleine to a proper database
* SQLite as a proper database
* Including a binary build of SQLite in the distro to make a
stand-alone app
* Anything else you deem relevant to this kind of a problem

Cheers,
Assaph

ps. You can a read full account of my dilemma on:
http://www.bloglines.com/blog/AssaphMehr?id=26

[1] http://pimki.rubyforge.org

2

What about using a pure ruby solution like KirbyBase?

-Ezra

···

On Oct 20, 2005, at 11:11 PM, Assaph Mehr wrote:

Hi list,

I need some advice around Ruby, SQLite an cross-platform problems. I
maintain Pimki, a wiki-based personal organiser[1]. So far, it was
using Madeleine as the backend storage format. However Madeleine seems
problematic: troubles in refactoring class/method names between Pimki
revision, occasional crashes and problems with the zip compression.

For Pimki2 I have the option of continuing with Madeleine or to switch
to SQLite/ActiveRecord. This will (hopefully) make Pimki more stable
and development easier, but will it make users happier? SQLite may just
be an unnecessary dependency / complexity.

I started to collect user profiles via a survey
(http://rubyforge.org/survey/survey.php?group_id=447&survey_id=25\) to
get a better impression, but I'd also highly appreciate input from this
list on:

* Ease of SQLite install on major platforms
* Moving applications from Madeleine to a proper database
* SQLite as a proper database
* Including a binary build of SQLite in the distro to make a
stand-alone app
* Anything else you deem relevant to this kind of a problem

Cheers,
Assaph

ps. You can a read full account of my dilemma on:
http://www.bloglines.com/blog/AssaphMehr?id=26

[1] http://pimki.rubyforge.org

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732

3

Hi Assaph

I use SQLite as the storage format for a gui app; my experience
(mainly sqlite not sqlite3) is that it is easy to work with on different
platforms.

* Ease of SQLite install on major platforms

SQLite library is available as 'official' binary for windows and
in most linux distribs. It's pretty simple to compile from scratch.

ruby-sqlite has windows binaries, and I've never had problems
with source builds on os x or linux.

* SQLite as a proper database

Very good - good Ruby api, useful SQL features (transactions,
triggers etc). Stable on different platforms. Fast, file-based
storage can make unit tests easier.

* Including a binary build of SQLite in the distro to make a
stand-alone app

Rubyscript2exe and related tools can bundle sqlite.dll/lib and
ruby- sqlite as part of a single executable file. Windows binaries
are portable across multiple OS versions.

* Anything else you deem relevant to this kind of a problem

- I'm considering using ActiveRecord in version 2.0 of my app
to cut the laborious SQL - I'd be interested to hear how people
get on porting custom SQL to AR.

- I don't know whether concurrency might be an issue for you,
given that it's a web app. I've only used it in a single-user
application.

a

4

Ezra Zygmuntowicz wrote:

What about using a pure ruby solution like KirbyBase?

Nice idea. I like the plain-text backend (particularly fitting for my
domain), and can live without SQL and a gem release.

My only question is about integrating with Rails. Any experience with
something like this?
I saw KirbyRecord, which is *like* ActiveRecord for KB, but I need
proper integration. Not necessarily all the trimmings of AR, but it
should work fairly painlessly. Any comments?

Cheers,
Assaph

5

Hi Alex,

Thanks for your replies!

> * SQLite as a proper database
Very good - good Ruby api, useful SQL features (transactions,
triggers etc). Stable on different platforms. Fast, file-based
storage can make unit tests easier.

How well does it handle db upgrades and schema changes? I expect to do
quite a bit of changes to the data model between versions.
Have you ever experience data corrupions?

- I don't know whether concurrency might be an issue for you,
given that it's a web app. I've only used it in a single-user
application.

Not an issue. Even though the interface is through a web browser, it's
normally used by a single person / small group only. I need SQLite
precisely because it is a lightweight solution to data management.

Cheers,
Assaph

6

Assaph Mehr wrote:

Ezra Zygmuntowicz wrote:

What about using a pure ruby solution like KirbyBase?
   
Nice idea. I like the plain-text backend (particularly fitting for my
domain), and can live without SQL and a gem release.

My only question is about integrating with Rails. Any experience with
something like this?
I saw KirbyRecord, which is *like* ActiveRecord for KB, but I need
proper integration. Not necessarily all the trimmings of AR, but it
should work fairly painlessly. Any comments?

I've thought a little bit about writing an ActiveRecord adapter for KirbyBase since getting back from RubyConf, but, since I haven't had the opportunity to play with Rails yet (I think I might be the last Ruby programmer left who hasn't done this :slight_smile: ), I'm not sure what that will entail.

From what I have heard, ActiveRecord uses SQL heavily in it's internals. Also, I read somewhere that David mentioned that someone had Rails going to a text file backend, but I think they may have bypassed ActiveRecord altogether.

So, to get KirbyBase working with Rails, it looks like there are two choices:

1. Write a SQL parser for KirbyBase (ugh, lots of work, unless it only has to support a subset of SQL).

2. Figure out how to connect the two without using ActiveRecord (is this how Madeleine does it?).

To do #2, I don't know how familiar you have to be with the inner workings of Rails. It sounds like an interesting thing to work on, but I am not sure when/if I will get a chance to work on it.

Jamey Cribbs

Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. If you are not the intended recipient(s), you are hereby notified that any dissemination, unauthorized review, use, disclosure or distribution of this email and any materials contained in any attachments is prohibited. If you receive this message in error, or are not the intended recipient(s), please immediately notify the sender by email and destroy all copies of the original message, including attachments.

7

Well kirbybase is really a nice little database. It now has one to one and one to many relations with tables and a bunch of other new features. I haven't got to play with KirbyRecord but that might be an option. But rails would work fine for the controllers and views and routes, giving you a great solid base to run your app on. And then you can always just make your models not inherit from ActiveRecord.base . Just make them kirkbyBase classes and add whatever methods you need to them. I know it wouldn't be as nice as active record with all the features that brings, but kirbybase uses blocks in a very nice and rubyish way to query the tables. It has also added indexes now so you can have very fast queries. I really like kirbyBase and its all pure ruby and text files so its *great* for portability,
     I think it would work seamlessly with rails. You won't get the full integration of AR but using ruby and blocks as the query method is very attractive to me. If you end up working on this I might pitch in a little and maybe we can make kirkybase a nice little alternative for rails projects that need portablity as a main feature.

     Let me know what you think

Cheers-

-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster

509-577-7732
ezra@yakima-herald.com

···

On Oct 21, 2005, at 3:21 AM, Assaph Mehr wrote:

Ezra Zygmuntowicz wrote:

What about using a pure ruby solution like KirbyBase?

Nice idea. I like the plain-text backend (particularly fitting for my
domain), and can live without SQL and a gem release.

My only question is about integrating with Rails. Any experience with
something like this?
I saw KirbyRecord, which is *like* ActiveRecord for KB, but I need
proper integration. Not necessarily all the trimmings of AR, but it
should work fairly painlessly. Any comments?

Cheers,
Assaph

8

Assaph Mehr wrote:

How well does it handle db upgrades and schema changes? I expect to do
quite a bit of changes to the data model between versions.

No problems with compatibility between minor versions of sqlite-ruby;
moving between sqlite and sqlite3 (both current versions of the
underlying library) is also very easy; the main differences
i've come across are in text encoding and support for binary objects.

Altering tables is slightly more laborious than in MySQL because the
ADD COLUMN syntax was only added in recent versions, so you have
to copy and recreate tables. It's not a huge loss. Code will break if you
SELECT columns that don't exist or if you assume that you're getting a
string when you're fetching a NULL cell. But even if you use a 'pure'
custom serialisation strategy like Marshal (which can be combined
quite easily with SQLite, btw) and change the data model (instance vars)
between versions you still need to program defensively if
you want to be able to load objects marshalled by an older version of
an class's definition into a newer one.

> Have you ever experience data corrupions?

Haven't had any data corruptions or segfaults.

hth
a

9

1. Write a SQL parser for KirbyBase (ugh, lots of work, unless it only
has to support a subset of SQL).

Too much work for something I can do without :slight_smile:
There is no reason I have to have SQL, it is just convenient. Plus, I
don't know that AR internals well enough to write an adapter anyway.

2. Figure out how to connect the two without using ActiveRecord (is
this how Madeleine does it?).

Yep. There are a few "special" objects that are proxied. each method
invocation is recorded (and occasionally a complete state snapshot is
taken), so you're guaranteed the same state when you resume. If I can
replace those objects to be handled by KB instead of madeleine
transparently it should be easy to replace. How invasive is KB
regarding the objects it manages? How does it support various
relationships?

Thanks for your replies,
Assaph

10

Hi Ezra,

I think it would work seamlessly with rails. You won't get the
full integration of AR but using ruby and blocks as the query method
is very attractive to me.

That's encouraging. Condering my "model" is already non-AR and that
I'm particulalry after SQL this could be an advantage :slight_smile:

If you end up working on this I might
pitch in a little and maybe we can make kirkybase a nice little
alternative for rails projects that need portablity as a main feature.

I propbably don't have the time / knowledge of AR & KB internals, but
if I do go with KB I'd certainly appreciate help and would love to
collaberate on something like this.

11

Altering tables is slightly more laborious than in MySQL because the
ADD COLUMN syntax was only added in recent versions, so you have
to copy and recreate tables.

Is that the way to also deal with dropping columns? Would the right
approach be to simply upon start-up read the affected tables, drop the
old one, recreate the new ones and then write the (massaged) data
back?

I understand that Rails' ActiveRecord does something similar for its
migrations. Have you had occasion to use it (with and without
migrations) over SQLite?

Code will break if you
SELECT columns that don't exist or if you assume that you're getting a
string when you're fetching a NULL cell.

I guess that can be managed with a 'version' fields plus a set of
migrations for db upgrades, right?

As for programming defensively - I think I'd rather program paranoidally :slight_smile:
One of the problems I experienced with madeleine is indeed in changes
between revisions of the software. That's why I'm trying to find out
as much as I can before committing to a backend change that'll prove
inadequate.

Cheers,
Assaph

12

Assaph Mehr wrote:

2. Figure out how to connect the two without using ActiveRecord (is
this how Madeleine does it?).
   
Yep. There are a few "special" objects that are proxied. each method
invocation is recorded (and occasionally a complete state snapshot is
taken), so you're guaranteed the same state when you resume. If I can
replace those objects to be handled by KB instead of madeleine
transparently it should be easy to replace. How invasive is KB
regarding the objects it manages? How does it support various
relationships?

Not sure what you mean by "how invasive is KB regarding the objects it manages". KirbyBase does give you the ability to create a custom "record class". All the records in the result set will be returned as instances of this class. So, if you need to give the returned records custom behavior, you can define the methods in this custom record class and each record will have that behavior. Don't know if this helps answer the first question or not.

Regarding the second question, KirbyBase supports one-to-one relationships (called "lookup fields" in KB) and one-to-many relationships. You also have the ability to define "calculated fields".

HTH,

Jamey

13

Assaph Mehr wrote:

> Would the right
> approach be to simply upon start-up read the affected tables, drop the
> old one, recreate the new ones and then write the (massaged) data
> back?

Yes, or it's probably safer to start a transaction, rename the old table to a
temporary name

ALTER TABLE foo RENAME TO foo_temp;

then create the updated table definition and copy into it.

CREATE TABLE foo (...)
INSERT INTO foo SELECT * FROM foo_temp;

I understand that Rails' ActiveRecord does something similar for its
migrations. Have you had occasion to use it (with and without
migrations) over SQLite?

The Migration API in AR does look useful, but I haven't had cause to try
it (yet).

Code will break if you
SELECT columns that don't exist or if you assume that you're getting a
string when you're fetching a NULL cell.

I guess that can be managed with a 'version' fields plus a set of
migrations for db upgrades, right?

Yep. That's just how I do it (though my data model is fairly stable).

http://rubyforge.org/cgi-bin/viewcvs.cgi/weft-qda/lib/weft/backend/sqlite/upgradeable.rb?rev=1.4&cvsroot=weft-qda&content-type=text/vnd.viewcvs-markup

As for programming defensively - I think I'd rather program paranoidally :slight_smile:
One of the problems I experienced with madeleine is indeed in changes
between revisions of the software. That's why I'm trying to find out
as much as I can before committing to a backend change that'll prove
inadequate.

Sounds good. SQLite's maturity has been a plus.

hth
a

14

Not sure what you mean by "how invasive is KB regarding the objects it
manages". KirbyBase does give you the ability to create a custom
"record class". All the records in the result set will be returned as
instances of this class.

Do I need to inherit from a certain base class (like in AR)? Do I need
to define fields as special properties (like Og)? In short, how much
KB specific aspects does a record class have over a regular class?

Regarding the second question, KirbyBase supports one-to-one
relationships (called "lookup fields" in KB) and one-to-many
relationships. You also have the ability to define "calculated fields".

How about many-to-many? Constraints on relationships? I don't mind
doing this with a special relationship-class, just want to know what
is the KB-way?

Thanks again for your time,
Assaph

15

Assaph Mehr wrote:

Not sure what you mean by "how invasive is KB regarding the objects it
manages". KirbyBase does give you the ability to create a custom
"record class". All the records in the result set will be returned as
instances of this class.
   
Do I need to inherit from a certain base class (like in AR)? Do I need
to define fields as special properties (like Og)? In short, how much
KB specific aspects does a record class have over a regular class?

No, you don't need to inherit from any specific class. The only thing your class needs to have is #kb_create method. KirbyBase calls this method, once for each record in the result set, to create an instance of the class. It passes in the fields of the record as arguments to #kb_create. That's it. Everything else in the class is definable by you.

There is an example in the distribution called "record_class_test.rb" that shows how this works.

In fact, you don't even have to define a custom class if you don't want to. Records in a result set default to being simple Struct objects if there is no custom class defined.

Regarding the second question, KirbyBase supports one-to-one
relationships (called "lookup fields" in KB) and one-to-many
relationships. You also have the ability to define "calculated fields".
   
How about many-to-many? Constraints on relationships? I don't mind
doing this with a special relationship-class, just want to know what
is the KB-way?

Nope and nope.

Thanks again for your time,

No problem. :slight_smile:

Jamey

16

There is an example in the distribution called "record_class_test.rb"
that shows how this works.

Oh well, time to read the docs I guess :slight_smile:

>How about many-to-many? Constraints on relationships? I don't mind
>doing this with a special relationship-class, just want to know what
>is the KB-way?
>
Nope and nope.

So how are many-to-many relationshops handled (e.g. authors and books)?

Also, how well does KB deal with "schema" changes between revisions? I
mean a new class version vs old data-store - how do I handle old
record data vs. new fields?

Cheers,
Assaph

17

Assaph Mehr wrote:

Oh well, time to read the docs I guess :slight_smile:

Feel free to keep asking questions. I just mentioned the examples in the distribution in case you wanted to see "record classes" in action.

So how are many-to-many relationshops handled (e.g. authors and books)?

Hmm, let me think about this. I guess I would create an author table, a book table, and a book_author table in the middle that would link the two. I think that would work for a many-to-many.

Also, how well does KB deal with "schema" changes between revisions? I
mean a new class version vs old data-store - how do I handle old
record data vs. new fields?

You are free to modify the record class as much as you want. KirbyBase is going to pass in the field values to the #kb_create method. After that, it is up to you to do with them what you will.

You can also do an #add_table_column and #drop_table_column to add/delete fields from the table after it is created.

Jamey

18

>So how are many-to-many relationshops handled (e.g. authors and books)?
>
Hmm, let me think about this. I guess I would create an author table, a
book table, and a book_author table in the middle that would link the
two. I think that would work for a many-to-many.

Sounds like standard practice to my (inexperienced) mind. I was just
wondering if there was something like the ActiveRecord
#has_and_belong_to_many magic.

>Also, how well does KB deal with "schema" changes between revisions? I
>mean a new class version vs old data-store - how do I handle old
>record data vs. new fields?
>
>
You are free to modify the record class as much as you want. KirbyBase
is going to pass in the field values to the #kb_create method. After
that, it is up to you to do with them what you will.

OK, here's a specific example.:

Suppose I had Foo with two fields :bar and :baz. The #kb_create had
those as parameters and KB passed them in as needed.

Now version 2 has the same class Foo, but with the fields :baz and
:qux. I.e. one field was deleted and another was added. Their relative
'position' in the method arguments has also changed. Now if I send my
shiny new v2.0 to a user who has a smelly old v1 database, what will
happen? What do I need to do to respond, recover and/or upgrade the
existing database?

Cheers,
Assaph

19

Assaph Mehr wrote:

Sounds like standard practice to my (inexperienced) mind. I was just
wondering if there was something like the ActiveRecord
#has_and_belong_to_many magic.

Unfortunately, no. That might be cool to have; I'll have to put that on the wishlist. :slight_smile:

OK, here's a specific example.:

Suppose I had Foo with two fields :bar and :baz. The #kb_create had
those as parameters and KB passed them in as needed.

Now version 2 has the same class Foo, but with the fields :baz and
:qux. I.e. one field was deleted and another was added. Their relative
'position' in the method arguments has also changed. Now if I send my
shiny new v2.0 to a user who has a smelly old v1 database, what will
happen? What do I need to do to respond, recover and/or upgrade the
existing database?

Good question. Let's see. Well, since the fields are passed to #kb_create as positional arguments, that won't be good for the user still on v1.0. So, I guess you would have to include a conversion script that would:

1. #delete_table_column(:foo, :bar)
2. #add_table_column(:foo, :qux)
3. Include a new class defintion referring to :qux and not referring to :bar.

Jamey

Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. If you are not the intended recipient(s), you are hereby notified that any dissemination, unauthorized review, use, disclosure or distribution of this email and any materials contained in any attachments is prohibited. If you receive this message in error, or are not the intended recipient(s), please immediately notify the sender by email and destroy all copies of the original message, including attachments.