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

[Assaph Mehr]

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]

What about using a pure ruby solution like KirbyBase?

-Ezra

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
(e-mail address removed)
509-577-7732

 

[Assaph Mehr]

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

 

[Jamey Cribbs]

Ezra Zygmuntowicz wrote:



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 ), 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.

 

[Alex Fenton]

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

 

[Ezra Zygmuntowicz]

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

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
http://yakimaherald.com
509-577-7732
(e-mail address removed)

 

[Assaph Mehr]

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
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

 

[Assaph Mehr]

Hi Alex,

Thanks for your replies!

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

 

[Jamey Cribbs]

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

 

[Assaph Mehr]

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

 

[Assaph Mehr]

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

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.

 

[Jamey Cribbs]

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.

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.

Jamey

 

[Assaph Mehr]

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

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

 

[Jamey Cribbs]

Oh well, time to read the docs I guess

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

 

[Assaph Mehr]

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.

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

 

[Assaph Mehr]

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 :-=
)
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

 

[Alex Fenton]

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).

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/viewcv...weft-qda&content-type=text/vnd.viewcvs-markup

As for programming defensively - I think I'd rather program paranoidally
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

 

[Jamey Cribbs]

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.

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_columnfoo, :bar)
2. #add_table_columnfoo, :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.