Page 1 of 2

Reading/Writing Classes from/to a database

Posted: Fri Jul 09, 2004 9:32 am
by xandor
I have been hacking around compulsively lately, having returned to coding after a few years' absence, seeing if I can first develop a "generic" method of storing objects into a database (mysql, in this case). It gets interesting when an object contains an array or an object, or more specifically, arrays of objects, or arrays of arrays (..etc.)

I'm simply coding my own sort of "MysqlObject" which contains and manages its own connection resource, you know, but is also "smart" enough, when called with its function insertIntoDB( $anObject ), will get all the vars of that object and create a table for it if necessary, before storing to its equivalent MySQL columns. This is more-or-less working, but I am toying with how to best implement the situation(s) described above without compromising rdbms integrity and without resorting to too much kludginess. At this point I'm thinking I could just have a separate table for arrays containing foreign keys from the object (or array) and its variable containing them..
But should I, for example, use recurson? Is there a danger of an "infinite" loop when, for example, objects/arrays contain either which might exist further "up" ("down"?) the containment hierarchy. Do I have to delve into some kind of database-implemented tree hierarchy (or would that be redundant, given that databases already ARE tree structures..). Perhaps I could just limit the depth of the object/array node level...?
...that sort of stuff..

Presumably this has already been thoroughly explored on these forums/codebases somewhere (yeah, I know I'm re-inventing wheels--but I'm having FUN!), but I can't seem to find it.
I would really prefer to just keep it simple (...s)!
Where should I look to see how others have explored this (these) issues?

AsI say, it's getting "interesting", and there's a hell of a lot of brainpower out here on these forums....

Posted: Fri Jul 09, 2004 10:23 am
by feyd
you may want to read into [php_man]serialize[/php_man]

Posted: Fri Jul 09, 2004 11:06 am
by xandor
Yeah, I played with serialize()--it's a bitch to parse (for me) and anyway, it's really optimized for just writing the string to a file handle.
It doesn't give me any more info than just using key/value pairs returned by casting the object to an array, as well as using gettype(), which is much easier to deal with.

But thanks, yeah.

(here's an example of serialize's output:)
O:9:"testclass":8:{s:2:"id";i:0;s:6:"intVar";i:1089389067;s:9:"stringVar";s:22:"July 9, 2004, 11:04 am";s:8:"floatVar";d:234.125;s:7:"boolVar";b:1;s:8:"arrayVar";a:3:{s:5:"color";
s:3:"red";i:0;i:234;i:1;d:35.030000000000001136868377216160297393798828125;}s:6:"objVar";

..nice...

Posted: Fri Jul 09, 2004 11:12 am
by feyd
[php_man]unserialize[/php_man] will reconstitute the object from the serialized string..

Posted: Fri Jul 09, 2004 12:03 pm
by xandor
Yes, okay, but what, I'm just writing each object as a long string to the database? I wouldn't be able to really do any operations on them within the database, then, would I? I might just as well write them to a "dumb" file. The idea is not merely to store the objects, but to "database-ize" them so that they can be modified/joined/selected (etc.) within the db itself.

Am I making sense? Am I overlooking something here? (wouldn't surprise me in the least...)

Posted: Fri Jul 09, 2004 12:15 pm
by patrikG
xandor wrote:Yes, okay, but what, I'm just writing each object as a long string to the database? I wouldn't be able to really do any operations on them within the database, then, would I? I might just as well write them to a "dumb" file. The idea is not merely to store the objects, but to "database-ize" them so that they can be modified/joined/selected (etc.) within the db itself.

Am I making sense? Am I overlooking something here? (wouldn't surprise me in the least...)
That sounds as if you want to create Stored Procedures - which in itself is a great thing, however, simply because of the design of your application, it would be very resource-hungry (PHP would need to read/write to MySQL excessively which takes away the major advantage of stored procedures away: speed).
MySQL 5 alpha has stored procedures already, postgreSQL had it since its inception (and its growing in popularity).

Generally, stored procedures are the lowest level of logic which, if your PHP application is properly abstracted, increases performance.

Posted: Fri Jul 09, 2004 12:44 pm
by bg
patrikG wrote:
xandor wrote:Yes, okay, but what, I'm just writing each object as a long string to the database? I wouldn't be able to really do any operations on them within the database, then, would I? I might just as well write them to a "dumb" file. The idea is not merely to store the objects, but to "database-ize" them so that they can be modified/joined/selected (etc.) within the db itself.

Am I making sense? Am I overlooking something here? (wouldn't surprise me in the least...)
That sounds as if you want to create Stored Procedures - which in itself is a great thing, however, simply because of the design of your application, it would be very resource-hungry (PHP would need to read/write to MySQL excessively which takes away the major advantage of stored procedures away: speed).
MySQL 5 alpha has stored procedures already, postgreSQL had it since its inception (and its growing in popularity).

Generally, stored procedures are the lowest level of logic which, if your PHP application is properly abstracted, increases performance.
Do you know if the syntax will be the same as oracles pl/sql? I'm vagualy familiar with it but havent even looked at mysql 5 or postgre's implementation of it.

Posted: Fri Jul 09, 2004 12:49 pm
by xandor
Umm.....no, I don't think that I necessarily want to use stored procedures (didn't think that humble mysql even HAD them actually...).
Merely wanted to harness the formidable power of a rdbms that MySql is to manipulate my data in the "usual" (i.e. not stored procedural) fashion. I would have my "stuff" as objects in my php code, which could be stored/retrieved via MySql where they would reside in tables, and within which they could be manipulated sql-ly. Play with stuff as objects in PHP, play with same stuff as tables (perhaps several, joined) in MySQL.

Come on! Lotsa people (much savvier than I ) must have been doing this, no?
Am I STILL missing something here?

You know, actually, it's got me thinking WAY back to about the last version of NeXTStep where they were implementing these "WebObjects" (It think they were called..) that acted as a sort of intermediary between (ObjectiveC) objects and database (don't remember what kind--probably some kinda sql) entities. They always were WAY ahead of the game, of course...

(and here I am....WAY-ay at the rear....shuffling along....)

...now, what was I talking about?

Posted: Sat Jul 10, 2004 10:17 am
by patrikG
bgzee wrote:Do you know if the syntax will be the same as oracles pl/sql? I'm vagualy familiar with it but havent even looked at mysql 5 or postgre's implementation of it.
As far as I know, mySQL's stored procedures follow pretty much the exact syntax of PL/SQL. Exact details are at MySQL's Stored Procedues.
xandor wrote:Merely wanted to harness the formidable power of a rdbms that MySql is to manipulate my data in the "usual" (i.e. not stored procedural) fashion. I would have my "stuff" as objects in my php code, which could be stored/retrieved via MySql where they would reside in tables, and within which they could be manipulated sql-ly. Play with stuff as objects in PHP, play with same stuff as tables (perhaps several, joined) in MySQL.
Perhaps it would help if you clarify which particular aspects of MySQL you find appealing and want to harness. MySQL is simply a data-repository, the "power" comes in through SQL. In terms of data-manipulation, data-relationship management etc. proper object oriented PHP (or any OO computer language) is way more relational and poweful than SQL could ever be. So, as said above, it would help if you outline what it is about SQL that you want to harness.

Posted: Sun Jul 11, 2004 10:47 pm
by lazy_yogi
xandor wrote:Come on! Lotsa people (much savvier than I ) must have been doing this, no?
Am I STILL missing something here?
Yes you are actually.
Look into automatic generation of DAO's (code generators)

I used to have a link to one that read the mysql db and produced a php class for each table. I took that and improved upon it. Now I have 2 classes for each table auto generated such that I just have to put in this code into the page:

Code: Select all

$user = new User();
$user->name = $_POST['name'];
$user->age = $_POST['age'];

$user_manager = new UserManager();
$user_manager->insert($user);
That's all I write to insert. Similar stuff for delete, update, exists, etc ...
And these 2 auto generated classes hide all the db connection stuff for you. It saves time, and produces immaculate code that is easy to read and modify when needed.


I doubt you will find a generic way to insert data into mysql since each object, as you said, can get very complex. But this does the job faster and easier - and since I've done it, I can say it's very do-able and the benefits are extraordinary.

Posted: Mon Jul 12, 2004 4:26 pm
by xandor
First, an apology is owed since I somehow missed the second half of your response, PatrikG, (how DO you scroll down?) and thought the thread had changed into one concerned with stored procedures. (I took my question to the database forum, actually).

I can't really give you a specific answer to your question, just that I am reasonably comfortable with both sql modelling/querying (etc.) as well as with oo programming, so it just seems natural to (attempt to) combine the two. I guess I like the ability to link together disparate entities from different tables on the fly with dynamically generated queries (there--I guess that about says it!)
If I didn't think that the sql engine didn't offer a lot of power that I would have to otherwise (re-) implement in my OOP (PHP), then perhaps I WOULD merely serialize the data into flat files for persistence and do everything else solely with PHP. But your comment that PHP offers considerably more power that sql is certainly food for thought, although I suspect "religion" might come into the equation.

And thank you, lazi_yogi, for pointing me into a relevant direction! I am looking at the (Pear) DataObject code and can see that it IS along the lines of what I am trying to (re-?)accomplish. I was just a little leery of Pear, since it seems to necessarily involve a lot of (possibly) superfluous code (or a least a lot of pretty complex--and undocumented--code).

As I say, what I have is a sort of simple stripped-down (Mysql) Db class which encapsulates all PHP resources (database connection, query results..) and, when passed an object to store, will do so by first creating the necessary table(s) if necessary by parsing all the object's vars and creating "equivalent" db column types (would be easier if PHP was more strongly typed), then storing them to the table(s). I am even beginning to successfully implement contained objects and arrays by using intermediary tables that use objectName/varname as keys pointing back to the object as well as those contained. I really AM trying to maintain sql referential integrity. The interesting thing here, I believe, is that there is no need for definition files to do this; My class can do this (well, is getting NEAR doing this..) just by being passed the object itself. It still remains to be seen, however, how complex the relationships might be amongst the various sql tables and whether this might unduly complicate their use within the sql context itself(hmm...stored procedures just MIGHT be useful here...).

But then again, perhaps I WILL just go the pear route...

BTW, I found an interesting document, for anybody else who is interested in this, about mapping objects into sql databases. It actually uses java, but still applies. It is at:
http://www.objectmatter.com/vbsf/docs/m ... pping.html


Thanks you guys,
xandor

Posted: Mon Jul 12, 2004 9:44 pm
by lazy_yogi
xandor wrote:As I say, what I have is a sort of simple stripped-down (Mysql) Db class which encapsulates all PHP resources (database connection, query results..)
OOP is about making small objects that each do a single job.
If you have one class doign everything, you're missing the point of OOP and simply putting functions into a class, which is really just proceedural code that happens to be inside a class

Regards,
Eli

Posted: Mon Jul 12, 2004 10:37 pm
by xandor
Elis points out:
If you have one class doign everything, you're missing the point of OOP and simply putting functions into a class, which is really just proceedural code that happens to be inside a class
Yes, true, but as I said, it really is stripped down. I'm merely hiding the database layer (which uses the aforementioned resources) from other objects. It certainly isn't doing "everything". It's simpler than the Pear db classes, except that it also happens to handle objects.

Your point is well-taken though. I did come across a proposed model for this that required each class type to have its own classManager to act as db intermediary! At this point I'm thinking that's overkill though.

Here, for example, are the functions my class uses:

Code: Select all

// |  myDB($host, $username, $passwd, $dbName) -- constructor
// |  tableList() --  array of db tables
// |  getError() --  last error msg
// |  changeDB($dbName) -- changes database
// |  doQuery($qString) --  array of results (rows) or TRUE (if manip) on successful query
// |  numRows() --  number of rows returned or affected by last query
// |  fetchObjects($className, $varName = "id", $varVal) -- array of object(s) with spec'd varVal for varName
// |  storeObject($anObject) -- Stores object, creating required tables, if nec.
// |  
// |  INTERNAL ("private") FUNCTIONS:
// |  vars2Cols($objArray) -- array of column types corresponding to object's vars
// |  existingCols($className) -- array of column types from existing table 
// |  print_foreach ($array, $tag) -- prints array elements enclosed by an html tag
// |  isManip($query) -- is query data manipulation type (as opposed to returning results)
It's still only preliminary, but as you can see there's not all that much to it, really. I suppose if it starts getting really hairy, though, I could separate al the object-managing functions into their own class. We'll see...

Thanks,

Sander

Posted: Tue Jul 13, 2004 12:15 am
by lazy_yogi
When you say "array of db tables" what exactly do you mean?

Apart from that, I've set that up over 3 different areas to simplify it.
Each class should do one thing and one thing only.
Your object is a connection object and an iterator object and an error handler all in one.
This needs some refactoring.

Here's what I have.

DB - connection class
MyResultIterator - class that holds the result set and you can iterate over it
Errors - are handled seperately. Prefereably with exceptions if you have php5

The DB class connects:

Code: Select all

$db = new DB('host','db','user','pass');
then you pass in the sql through a query method in your db class which returns a ResultIterator object containing the result

Code: Select all

$result = $db->query('select * from table');
Then the ResultIterator class allows you to iterate through the results stored in that object

Code: Select all

while ($row = $result->next())
{
  // do something with $row
}
Hope that's of some help

Regards,
Eli

Posted: Tue Jul 13, 2004 6:17 am
by patrikG
If I understand you correctly, you want the DB to be the data-repository for objects and arrays. When accessing the data-repository the data-format should be irrelevant (much like PHP's Scalar-variables, only type-cast post-access and global, though).

The main reasons for keeping objects in the DB would be to keep them alive. On top of that, you want to add the additional "indiscriminate data-repository" functionality.

Do I understand you correctly so far? If so, then looking at the data access object pattern in combination with a decorator pattern might do the job.
But I as you can see, all of my logic still resides on the PHP-side of things, and SQL is reduced to its original simple data-repository functionality.
xandor wrote: Here, for example, are the functions my class uses:

Code: Select all

// |  myDB($host, $username, $passwd, $dbName) -- constructor
// |  tableList() --  array of db tables
// |  getError() --  last error msg
// |  changeDB($dbName) -- changes database
// |  doQuery($qString) --  array of results (rows) or TRUE (if manip) on successful query
// |  numRows() --  number of rows returned or affected by last query
// |  fetchObjects($className, $varName = "id", $varVal) -- array of object(s) with spec'd varVal for varName
// |  storeObject($anObject) -- Stores object, creating required tables, if nec.
// |  
// |  INTERNAL ("private") FUNCTIONS:
// |  vars2Cols($objArray) -- array of column types corresponding to object's vars
// |  existingCols($className) -- array of column types from existing table 
// |  print_foreach ($array, $tag) -- prints array elements enclosed by an html tag
// |  isManip($query) -- is query data manipulation type (as opposed to returning results)
You need to abstract your API more - there is still much confusion with regard to objects and arrays - which one do you want? As described above, your class seems a mix of many (and few) things at the same time: a bit of database abstraction, some data-manipulation, some presentation (the HTML-tag enclosed array) etc.
I am still not 100% clear about what you want to do.