From: walter@...
Date: 2004-01-08T02:44:56+09:00
Subject: Re: Database applications and OOness

> I write a lot of code that provides some sort of web based view or
> application to data stored in a database, and I find myself following
> a repeated model where I define a Struct that represents a row of
> data.  This is very convenient for my manipulation fo the data, but
> there are complications that arise.
> 
> The first is that if I change the database schema, I also have to then
> go change the struct code to match, and the second is that I write a
> lot of the exact same sort of SQL to query data with the same Ruby
> around it to put that data into my struct, and when I change the
> database schema, I then need to go change all of the SQL that is
> affected.
> 
> What I need is a layer that, given a db table, will generate a Struct
> that encapsulates that data and provides a few convenience methods for
> querying data from the table into one or more objects, and for pushing
> the data from the objects back to the db.
> 
> So far as I have been able to determine, there's nothing on RAA that
> does this.  Am I overlooking something, or is this something that I
> need to write myself?
>

I have a system that was developed in house.  It is not exactly what 
you are asking for.  I call it SPOM (Simple Persistent Object 
Manager).  The keyword is SIMPLE.

It basically has 2 layers.  SpomDB is a wrapper over a DBI 
connection.  It can take an arbitrary sql query and will return an 
array of newly created Objects.  The objects will have members that 
are the same names as the query names (unless they are invalid ruby 
names) but they can also be accessed via [], like an array would.  
This makes it convenient to do an arbitrary query.  But it currently 
does support modifying and resaving this type of object (would not be 
hard to add, but I haven't needed that).

Above this is SPOM,  a manager of sorts.  It takes a spomDB and you 
can add xml file mappings to it.  The xml file mappings allow you to 
map between the db and any given object.  You can add data type 
mappings (ie it is stored as a char in the db but in ruby it is a 
boolean).  You can also have sub-objects mapped.  So you can have a 
person object, that has an address object that has a zip code object. 
You can then access the ruby object via person.address.zip.code  (ie 
it creates the sub objects and fills them for you).  Please keep in 
mind this is a simple one object to one table mapping not 
relationships between tables (it is simple).

It is used in house to read in and transfer data between SQL Server, 
Informix, Dataflex, and DBF databases.  Its primary intent was to 
extract data from legacy systems.  Using the mapping files, you can 
also do basic CRUD operations, but there currently is NO transaction 
support, no connection pooling, no object query interface (ie you use 
sql), no multi-table mappings or join support.  I do plan to add 
these things, but it will take time, right not SPOM supports our 
basic in-house needs.

If there is an interest in this I could release it next tuesday to 
RubyForge.  (I would simply need to present this to our executive 
group meeting on tuesday, more of a formality, but that's our 
protocol for this)  Also note that there is very little documention, 
I'll try to add some if there is interest.

> 
> Thanks,
> 
> Kirk Haines



Walt
*****************************************************
Walter Szewelanczyk
IS Director
M.W. Sewall & CO.        email : walter@mwsewall.com     
259 Front St.            Phone : (207) 442-7994 x 128
Bath, ME 04530           Fax   : (207) 443-6284
*****************************************************