f



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

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

0
assaph1 (122)
10/21/2005 6:10:02 AM
comp.lang.ruby 48886 articles. 0 followers. Post Follow

18 Replies
9682 Views

Similar Articles

[PageSpeed] 9

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


0
ezra3555 (133)
10/21/2005 6:20:47 AM
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

0
assaph1 (122)
10/21/2005 10:21:00 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?
>  
>
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.


0
cribbsj (92)
10/21/2005 1:16:55 PM
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
0
alex9061 (195)
10/21/2005 5:32:40 PM
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


     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
ezra@yakima-herald.com





0
ezra8478 (26)
10/21/2005 6:03:05 PM
> 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


0
assaph1 (122)
10/22/2005 10:01:44 AM
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


0
assaph1 (122)
10/22/2005 10:33:52 AM
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


0
jcribbs (57)
10/22/2005 2:47:36 PM
> 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


0
assaph1 (122)
10/22/2005 10:06:24 PM
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.


0
assaph1 (122)
10/22/2005 10:09:27 PM
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.  :-)

Jamey


0
jcribbs (57)
10/22/2005 11:59:10 PM
> 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 :-)

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


0
assaph1 (122)
10/23/2005 8:17:15 PM
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

0
alex9061 (195)
10/24/2005 12:14:30 AM
Assaph Mehr wrote:

>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


0
jcribbs (57)
10/24/2005 1:34:10 AM
> >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


0
assaph1 (122)
10/24/2005 5:04:50 AM
> 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


0
assaph1 (122)
10/24/2005 5:18:10 AM
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 :-)
> 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
0
alex9061 (195)
10/24/2005 9:40:08 AM
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.  :-)

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


0
cribbsj (92)
10/24/2005 10:30:10 AM
Reply: