I got something running which provides perl persistence using RDBMS'es
(Ora8.1.5 + Sybase + M$SQLserver). And most of the time you don't really
need to write SQL to manipulate & retrieve the RDBMS content.

Unfortunately I'm not allowed to give the source away. But can share some
of the knowledge I gained.

Here is a sample of code using my Data Access layer:

    #!/usr/local/bin/perl -w
    use strict; # user_test.pl                 # strict programming
    use TDS::Application qw($PROGRAM_NAME);    # script name
    use TDS::PDO::User;                        # declare user
    TDS::PDO ->  stringify_mode(1);             # no <![CDATA[...]> packing
    my $db_name = $ARGV[0] or                  # first arg of script
        die "usage: $PROGRAM_NAME database_instance\n"; # usage message
    my $app     = new TDS::Application name =>  $db_name;
    my $channel = $app ->  getDALchannel;       # acquire a channel
    my $user = new TDS::PDO::User              # create a user
     NAME         =>  'xxuser',                 # set name
     PASSWORD     =>  'm$',                     # set password
     XML_FRAGMENT =>  '<catagory>foo</catagory>'; # piece of xml
    $channel += $user;                         # add user to database
    print "---------------------\n";           # separator
    print $user;                               # print user as XML
    $user ->  password('joe');                  # set password to bill
    $user ->  xml_fragment ('<catagory>bar<catagory>');  # other piece of xml
    $channel << $user or die;                  # put user back in database
    my $user_id = $user ->  user_id;            # get pk user_id of user
    undef $user;                               # remove user from memory
    my $u2 = new TDS::PDO::User USER_ID =>  $user_id;
    $u2 << $channel or die;                    # get the user back, by id
    print "---------------------\n";           # separator
    print $u2;                                 # print user as XML
    $channel -= $u2;                           # delete user from database
    print "---------------------\n";           # separator
    $channel ->  attr (die_get_notfound => 1);  # trow exception on not found
    eval { $u2 << $channel or die};            # try to fech user
    my $fail = ($@ !~ /no match/);             # expect "no match" exception
    print 'user ', $fail ? 'not ' : '', "deleted\n";   # print result
    exit $fail;

The trick is parsing the meta schema of the DB. create for each table a
class of which instances represent a row in a table. each table class
inherits from a abstract table class, which implements abstact methods such
as primary_keys, type_of(col_name).

setters and getters can dynamically generated for each colname given table
class.

The second concept is a channel which is a facade for a database. channel
has methods such
put , get, insert and delete. The insert is the tricky one there you need
identity cols for Sybase/m$sqlserver and sequences for oracle.

In the sample above I overloaded the
+=,-=, > >,<< operators in the base class of the table objects to do
insert, delete, put and get (just syntax sugar...)

After implemented this you might want to consider collection methods for
channel such as:

@objects = $channel ->  retrieve (
	object  =>  $class_name ,
	select  =>  [COL_LIST],
	where   =>  $sql_where_clause,
	sorting =>  $sql_sorting_clause);

Finally you need to be able to execute sql to take advantage of joins...

$channel ->  exec_sql('SELECT ...')
while (%one_row = $channel ->  next_row_hash) {
	my $o = new TDS::PDO::User %one_row;
	...
}

Me too like to know more to automate RDBMS/perl-xml persistence....
Manning/Conway/OO perl and O'reilly/Advanced Perl/Sriram Srinivasan
give some info on persistence, but unfortunately not enough...

cheers
- J


:-) -----Original Message-----
:-) From: Michael Nachbaur [mailto:MNachba@[...].com]
:-) Sent: Friday, July 28, 2000 5:34 PM
:-) To: Mod_perl Mailing List (E-mail)
:-) Cc: Perl-Xml Maillist (E-mail)
:-) Subject: [OT] Abstract-Database Class
:-)
:-)
:-) <Warn priority="low"> 
:-)   <warning class="Off Topic"/> 
:-)   <warning class="Long"/> 
:-) </Warn> 
:-)
:-) I'm not exactly sure where to send this, but here it goes:
:-) Has anyone completed/started an abstract database class at
:-) all?  I've written some for very specific applications, but
:-) I want a more general-purpose tool.  I'm about to start
:-) development on one, but wanted to get feedback from the
:-) world-at-large to see what features you would want first
:-) (or if anyone has already started one).
:-)
:-) The classes I've created/used before, are like:
:-) my $order = new Order( oid =>  '1234567' );
:-) $order-> fetch();
:-) foreach my $order_line ( $order-> Lines ) {
:-)   print $order_line-> Description, $order_line->Price;
:-) }
:-)
:-) I'd like to interact with a database, update data, save
:-) data, etc.  without having to actually call any SQL code.
:-) And, if I modify my database schema, I'd like the classes
:-) to automatically adapt.
:-)
:-) The way I've done this before, is create a series of
:-) classes with AUTOLOAD subs in them which intercept the name
:-) of the method being called, and then updating that specific
:-) field in the record.
:-)
:-) How I"d like to implement it would be to define an XML file
:-) with represents the way my data is structured in the
:-) database.  This way, the objects can determine when to
:-) cache data, and when to commit its values to the database
:-) for maximum efficiency.  An example could be:
:-) <schema name="mp3_site" database="DB0"
:-) database_vendor="Oracle" database_version="8.1.5"
:-) username="nachbaur" password="you_wish"> 
:-)     <table name="artist" primary_key="id"> 
:-)         <field name="name" type="varchar" size="256"
:-) options="not null"> 
:-)     </table> 
:-)     <table name="album" primary_key="id"> 
:-)         <field name="parent_id" references="artist"
:-) options="not null"/> 
:-)         <field name="name" type="varchar" size="256"
:-) options="not null"/> 
:-)         <field name="image" type="varchar" size="4000" /> 
:-)     </table> 
:-)     <table name="track" primary_key="id"> 
:-)         <field name="parent_id" references="album"
:-) options="not null"/> 
:-)         <field name="name" type="varchar" size="256"
:-) options="not null"/> 
:-)         <field name="filename" type="varchar" size="4000"/> 
:-)         <field name="filesize" type="integer"/> 
:-)     </table> 
:-) </schema> 
:-)
:-) Now, obviously thats not that perfect of an example (but
:-) I"m in a hurry, 'cause I need to go get some coffee), but
:-) it illustrates that the class could import that XML file,
:-) which defines the way it should interpret data.  So, it
:-) could fetch a particular record, and depending on how you
:-) tell it to operate, it could suck down all the data that
:-) the inital record refers to, or could retrieve it as-needed.
:-)
:-) It may not operate as efficiently as possible by default,
:-) but would give much more flexibility to the programmer.  I
:-) know that when I'm lazy, I don't want to bind all my
:-) values, and make sure I'm using optimized SQL statements
:-) (explain plan, and that sorta thing).  With this, I could
:-) "pre-define" SQL statements in this XML file that are
:-) pre-optimized, and I can just say:
:-) $db-> run('do_really_scarry_join', %options);
:-)
:-) Sory for the long message.  Would anyone like a module like
:-) this, or would I be just wasting my time? Anyone already
:-) have something like this?  Any feedback would be
:-) appreciated (except flames and spam).
:-)
:-) --man
:-) Michael A. Nachbaur (KE6WIA)
:-) mike(at)nachbaur(dot)com
:-) http://www.nachbaur.com
:-) "Only two things are infinite, the universe and human
:-) stupidity, and I'm not sure about the former." - Albert Einstein
:-)
:-)
:-) _______________________________________________
:-) Perl-XML mailing list
:-) Perl-XML@[...].com
:-) http://listserv.ActiveState.com/mailman/listinfo/perl-xml
:-)