Active Record Save (insert/update) And destroy Feature

[oddz]

The logic to build the SQL and collect the bind data doesn't reside inside the ActiveRecord class.

For example, this is the static find method that resides inside the ActiveRecord class:

    public static function _find($pClassName,$pOptions) {
        
        if(self::isConnected()===false) throw new Exception('A database Adaptor has not been set for the '.__CLASS__.' Class.');
    
        $model = ActiveRecordModelConfig::getModelConfig($pClassName);
        
        $mode = !empty($pOptions) && is_array($pOptions[0])===false?array_shift($pOptions):self::findAll;
        
        ActiveRecordSelectNode::resetCount();
        $node = new ActiveRecordSelectNode($model,new ActiveRecordFindConfig(!empty($pOptions)?$pOptions[0]:array()));
        $select = strcasecmp($mode,self::findCount)==0?new ActiveRecordCount($node,$pOptions):new ActiveRecordSelect($node,$pOptions);        
        
        //echo '<p>',$select->toSql(),'</p>';
        $stmt = $select->query(self::$_db);
        $collectionAgent = new ActiveRecordCollectionAgent($select);
        
        if(strcmp($mode,self::findCount)==0) { return $stmt->fetchColumn(); }

        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $collectionAgent->process($row,$node);
        }

        $records = $collectionAgent->getRecords();
        
        return strcmp($mode,self::findOne)==0?!empty($records)?$records[0]:null:$records;
        
    } 

What exactly is the appeal of ActiveRecord? It just seems so much cleaner to me to separate out database code into a Gateway or Mapper.

To be honest I just like and find the aesthetics/interface nicer to use. More of a personal preference then anything. Although this implementation in many ways is a border between the two because all the data and sql building logic are kept in separate objects that the ActiveRecord class manages.

This the code for the save method. As you can see the responsibility of generating the save sql and executing it is not responsibility of the ActiveRecord directly.

    public function save($pValidate=true) {
    
        $save = new ActiveRecordSave($this);        
        return $save->query(self::$_db);
    
    } 

Personally, I rather do this:

$entry = new Entry(1); 

Then this:

$entryGateway = new EntryGateway($db);
$entry = $entrygateway->find('one',array('id'=>1)); 

Managing one vs. managing two… I'll take one.

I've never seen the benefit of using the gateway if the active record has the sql creation logic and property repository factored out of it directly.

So unlike other implementation you may have come across the one I am working on here does actually separate the code. However, to me that's just good programming. Making sure that each class only has two or three main responsibilities and not cramming everything into one just because you can.

So I guess you could say this approach is more or less a hybrid between the Gateway and ActiveRecord perhaps…

[wysiwyg]

I think the problem with ActiveRecord still remains even if the SQL creation isn't done inside of the ActiveRecord class itself. The problem is that the Domain Object is somewhat aware of it's persitance mechanisms. If you take out the persistance stuff, and place it into Mapper/Gateway objects, you're able to do stuff like:

<?php
$dbmapper = new DbUserMapper($db);
$user = $dbmapper->find(1);

$xmlmapper = new XmlUserMapper('users.xml');
$user = $xmlmapper->save($user);

$mockmapper = new MockUserMapper($fakedb);
$user = $mockmapper->find(1);
$mockmapper->save($user); // Does nothing except perhaps writes to a log file
?>

If you abstract the persistance with ActiveRecord, you might be able to do:

<?php
// Get a user from the database
$user = User::find(1);
$user->save();

$user = User::find(1, new MockDatabase());
$user->save(); // Does nothing except perhaps writes to a log file
?>

But then you need to modify how all User-objects are created to change the persistance. To get around that, you could have some sort of Factory, like:

<?php
$factory = new UserFactory(new MockDatabase());
$user = $factory->find(1);
$user->save();
?>

But then what's the point of not extracting it and making a Mapper/Gateway out of it?

But I do see the charm in ActiveRecord, it's easy and fast to work with. But you can run into situations where you'd wish you went with the Mapper/Gateway approach instead.

[oddz]

I think the problem with ActiveRecord still remains even if the SQL creation isn't done inside of the ActiveRecord class itself. The problem is that the Domain Object is somewhat aware of it's persitance mechanisms. If you take out the persistance stuff, and place it into Mapper/Gateway objects, you're able to do stuff like:

Never part of the intended purpose. I see where it may be useful but when I set out to create this those ideas weren't part of the plan. The idea was to create a mechanism to easily move hierarchical data between the domain and database and vice versa. That's really all I am attempting to accomplish. Keep it simple stupid. When you start to cram in to many ideas everything falls apart. With that said, I'm sure the algorithms could be easily reapplied to a gateway I just don't see a need to do that. Maybe once I'm finished, but until then I'm enjoying the ease of use that the "ActiveRecord" here provides.

[wysiwyg]

oddz:
I agree, go with what you have right now, if that fits your needs. And that could be the best possible solution for your problem, you might never need to change it. My point is just that people should be aware of the possible problems that may occur when you go with ActiveRecord. And you obviously know, so that's good.

[oddz]

After looking into the delete mechanism I've come to to the conclusion that it may be most appropriate to break it up into separate parts.

The first part would be based on deleting dependencies expressed by the models configuration has one, has many and many to many relationships.

The other option would be similar to a update and would remove items only if they are properties of the model.

To further clarify.

A user has many threads, posts and blog entries and comments. Thus by calling the destroy method on a single object all its dependencies would be removed. The system would also need to go through the dependencies, dependencies and so on until the leaf is reached for each branch. That seems like the correct approach.

$user = new User(1);
$user->destroy(); 

In contrast the delete method would only remove dependencies that were brought into memory with the object.

$user = User::find('one',array('include'=>'threads','id'=>10));
$user->delete(); 

The user in this case would not be deleted only that users threads and its dependencies would be removed. That seems like it would be the right approach for the delete mechanism.

Another scenario I was thinking about would be th ability to pass in what needs to be deleted to delete.

$user = new User(3);
$user->delete('posts'); 

The above then would essentially remove all of that users posts and any dependencies (if they exists) for the posts that are being removed that belong to the user.

Then for those who just need to delete the single entity based on the primary key have a method such as remove. It will only delete one item that matches the primary key represented by the record in memory that was acted upon.

$user = new User(3);
$user->remove(); // remove user with id of 3 only 

Do these ideas sound like they are on the right track logically?

thanks

[oddz]

If I were to run a query like:

SELECT GROUP_CONCAT(t1.id) AS threads FROM threads AS t1 WHERE user_id = 1 GROUP BY user_id;

Say that the threads field resulted in 200+ values. Would it be "safe" to build a WHERE clause that had 200 conditions and bound values in this fashion:

DELETE FROM posts WHERE thread_id = ? OR thread_id = ? OR thread_id = ? OR thread_ id = ? OR thread_id =? OR thread_id = ? OR thread_id = ?...

Would that be the most safe and secure way to remove all the threads posts?

This is a hypothetical situation. Would that amount of conditions and/or bound variables kill the system. 200 is just a potential approximation but really I would like it to work regardless of the number. Would that be unsafe in theory? What be the best and most secure fashion to remove that much information at once. Can I trust the system to be able to handle almost any level as long as the sql and binding is correct?

[oddz]

Incredibly hypothetical, but highly possible.

DELETE FROM posts WHERE thread_id=0 OR thread_id = 2 OR thread_id = 4 OR thread_id = 6 OR thread_id = 8 OR thread_id = 10 OR thread_id = 12 OR thread_id = 14 OR thread_id = 16 LIMIT 500

Again, hypothetical but I think you can see where I'm attempting to go.

[oddz]

Seems like the more elegant solution:

DELETE FROM users WHERE id = :id

DELETE FROM projects WHERE user_id = :id

DELETE FROM won_projects WHERE project_id IN (SELECT DISTINCT t0.id FROM projects AS t0 INNER JOIN users AS t1 ON t0.user_id = t1.id WHERE t1.id = :id)

DELETE FROM bids WHERE project_id IN (SELECT DISTINCT t0.id FROM projects AS t0 INNER JOIN users AS t1 ON t0.user_id = t1.id WHERE t1.id = :id)

DELETE FROM bids WHERE user_id = :id

DELETE FROM blog_entries WHERE user_id = :id

DELETE FROM blog_comments WHERE blog_entry_id IN (SELECT DISTINCT t0.id FROM blog_entries AS t0 INNER JOIN users AS t1 ON t0.user_id = t1.id WHERE t1.id = :id)

DELETE FROM blog_comments WHERE user_id = :id

DELETE FROM threads WHERE user_id = :id

DELETE FROM posts WHERE thread_id IN (SELECT DISTINCT t0.id FROM threads AS t0 INNER JOIN users AS t1 ON t0.user_id = t1.id WHERE t1.id = :id)

DELETE FROM posts WHERE user_id = :id

[oddz]

Since, this module can be incredibly destructive to you think its a good idea to not include it by default for the active record? Instead it would need to imported individually perhaps?

Calling something like:

$user = new User(1);
$user->destroy(); 

Those two lines of code would be all that is needed to remove everything that is linked to that user. Seems a bit dangerous to include that module my default. One little slip could essentially wipe out something unattended although perhaps unlikely.

[wysiwyg]

Those two lines of code would be all that is needed to remove everything that is linked to that user. Seems a bit dangerous to include that module my default. One little slip could essentially wipe out something unattended although perhaps unlikely.

What you're describing is basically foreign key constraints in a database, and I wouldn't call those dangerous. If you decide to remove a user, then what's the point of keeping everything that is linked to that user? That information will be useless without the user data. If you want to be on the safe side, don't delete the user data, just set an "is_deleted" flag instead.

[oddz]

What you're describing is basically foreign key constraints in a database, and I wouldn't call those dangerous. If you decide to remove a user, then what's the point of keeping everything that is linked to that user?

That is exactly what I'm trying to represent. I was thinking about the flags, but really in most situations a dependency can't exists without the other element. However, I'll probably implement some type of flag just in case. Perhaps maybe some of flag that just deletes lower elements as well. So a user could be pulled in and everything linked to that item would be removed, but the item itself would stay in contact. I think that might be useful and it wouldn't be all that difficult to implement based on the current algorithm.

[sysk]

Your idea is very similar to dORM. dORM is a library I wrote because I wasn't satisfied by current ORM implementations (propel, doctrine, etc.). Give it a try !

Taken from http://www.getdorm.com/#getting-started :

<?php
// See Mapping to learn more about this config file
$dorm = new Dorm("config.xml");

// Loading an object from the database
$user = $dorm->getUser($id); // $id could be "1", array("username" => "john"), etc. depending on what your primary key is

// Modifying the object and saving it
$user->email = "new-email@domain.com";
$dorm->save($user);

// This will cause an insert since $new_user was not retrieved from database
$new_user = new User();
$dorm->save($new_user);

// Now, if we want to delete $user, all we have to do is
$dorm->delete($user);

// This will return an array with all users
$users = $dorm->getUserCollection();

// This will return an array with all users with an ID > 2
$users = $dorm->getUserCollection("user_id > :id", array("id" => 2));

[oddz]

I've taken a look at your library and the docs leave much to be desired. Furthermore, out of common courtesy if you would like to promote your work I would expect you to have the decency to begin your own thread as well.

I rather have you start your own thread, but I can go find many other ORM implementations that provide this same basic functionality.

<?php
// See Mapping to learn more about this config file
$dorm = new Dorm("config.xml");

// Loading an object from the database
$user = $dorm->getUser($id); // $id could be "1", array("username" => "john"), etc. depending on what your primary key is

// Modifying the object and saving it
$user->email = "new-email@domain.com";
$dorm->save($user);

// This will cause an insert since $new_user was not retrieved from database
$new_user = new User();
$dorm->save($new_user);

// Now, if we want to delete $user, all we have to do is
$dorm->delete($user);

// This will return an array with all users
$users = $dorm->getUserCollection();

// This will return an array with all users with an ID > 2
$users = $dorm->getUserCollection("user_id > :id", array("id" => 2));

In fact this is how simple it is with mine:

$user = new User(1);
$user->email = "new-email@domain.com";
$user->save();

$user->annihilate(); // removes user and everything linked recursively

$users = User::find('one',array('id'=>2)); 

In that respect I wouldn't see the benefit of using your system.

Not meant to be a "battle" just a challenge. From your docs I see very little in terms of support for advanced features. Every ORM on the planet has support for the simple stuff.

[sysk]

I didn't have time to write any documentation yet. I released this library 2 days ago. However, I would say the benefit of dORM is that it doesn't rely on class extending. You don't ever have to touch your domain classes for dORM to be effective. Plus, you can use your own database structure. This is how it would look with dORM. Eventually, I'll make the config.xml optional if you would like the structure to be automatically generated.

Let's say we have a class User like this:

class User() {
public $username;
public function __construct($username) {$this->username = $username;}
}

$dorm = new Dorm('config.xml');
$user = $dorm->getUser('username');
$user->email = "new-email@domain.com";
$dorm->save($user);

$user->delete();

$users = $dorm->getUserCollection('id=:id', array('id' => 2));

It keeps every loaded object in cache so you don't have 2 instances of the same table row.

$user1 = $dorm->getUser(1);
$user2 = $dorm->getUser(1);
if ($user1 === $user2) echo "user1 and user2 are the same";

It also allows one-to-many and many-to-many relationships.

$user = new User('bob');
$user2 = new User('john');
$user3 = new User('mike');

$user->friends = array($user2, $user3);
$user->bestFriend = $user2;

$dorm->save($user2);

Now, if we load the user bob,
$user = $dorm->getUser('bob');

It will also (lazy) loads its friends and bestfriend
foreach($user->friends as $friend) { echo $friend->username; }
echo $user->bestFriend->username;

if ($user->bestFriend === $dorm->getUser('john')) echo 'this is true';

Btw, sorry for hi-jacking your thread. I didn't read it fully and thought you were looking for a library that had the characteristics described. Good luck on your ORM !

[oddz]

This a current problem in my system. It does not support multiple foreign keys that reference the same table/model. Any ideas of how to go about this taking into consideration the below example and problems.

class Foo extends ActiveRecord {

    public static $table = 'foos';
    
    public static $primaryKey = 'id';

    public static $fields = array(    
        'id'
        ,'a_id'
        ,'b_id'    
    );
    
    public static $foreignKeys = array(
        'a_id'=>array('Bar','id')
        ,'b_id'=>array('Bar','id')
    );

} 

class Bar extends ActiveRecord {

    public static $table = 'bars';
    
    public static $primaryKey = 'id';

    public static $fields = array(    
        'id'
        ,'name'
    );
    
    public static $hasMany = array('foos');

} 

$foo = Foo::find('one',array('id'=>7,array('include'=>'bar'))); 

Problems:
1.) bar can be associated with two columns (a_id or b_id).
2.) $foo->bar may only reference one bar instance.

Now, $foo->a_id would return the bar id but not the the associated bar object. The same is true for $foo->b_id.

Thus, which association(a_id,b_id) would be $foo->bar resolve to? Furthermore, how would one be able to access both
associations? Maybe $foo->bar1 and $foo->bar2? If so which association resolves to 1 and which to 2?

Maybe another find option to say which association to resolve?

$foo = Foo::find('one',array('id'=>7,array('include'=>'bar'),'assoc'=>'a_id')); 

[REMIYA]

class Foo extends ActiveRecord {
} 

I was going to ask about this ActiveRecord class you are extending:

Is it some standard library class or your own implementation?

Why the table,primaryKey,field properties are declared static? What is the benefit of this?

[oddz]

I was going to ask about this ActiveRecord class you are extending:

Is it some standard library class or your own implementation?

Part of a library I'm developing.

Why the table,primaryKey,field properties are declared static? What is the benefit of this?

So these properties can accessed outside the class to create configuration objects. The configuration object provides a common public interface composed of getter and setter to access and change the information about the model.

$user = new ActiveRecordModelConfig('User'); // reads user model and creates config
$user->getTable(); // return 'users' 

Here is an example of that interface ( the constants reflect the static property name of the model):

<?php
interface IActiveRecordModelConfig {

    const defaultPrimaryKeyName = 'id';

    const table                    = 'table';
    const fields                 = 'fields';
    const primaryKey             = 'primaryKey';
    const uniqueKeys            = 'uniqueKeys';
    const foreignKeys             = 'foreignKeys';
    const transformations        = 'transformations';
    const dataTypes                = 'dataTypes';
    const requiredFields        = 'requiredFields';
    const defaultValues            = 'defaults';
    const hasOne                 = 'hasOne';
    const hasMany                 = 'hasMany';
    const belongsTo             = 'belongsTo';
    const belongsToAndHasMany     = 'belongsToAndHasMany';    

    public function getClassName();
    public function getTable();
    public function getFields();
    public function getPrimaryKey();
    public function getUniqueKeys();
    public function getForeignKeys();
    public function getTransformations();
    public function getDataTypes();
    public function getRequiredFields();
    public function getDefaultValues();
    public function gethasOne();
    public function getHasMany();
    public function getBelongsTo();
    public function getBelongsToAndHasMany();
    
    public function hasClassName();
    public function hasTable();
    public function hasFields();
    public function hasPrimaryKey();
    public function hasUniqueKeys();
    public function hasForeignKeys();
    public function hasTransformations();
    public function hasDataTypes();
    public function hasRequiredFields();
    public function hasDefaultValues();
    public function hasOne();
    public function hasMany();
    public function hasBelongsTo();
    public function hasBelongsToAndHasMany();
    
    public function getRelatedField(IActiveRecordModelConfig $pConfig);
    public static function getModelConfig($pClassName);
    
}
?>

Anything that implements that interface can essentially be used in generate, select, delete, and save methods… simply put.

[allspiritseve]

This a current problem in my system. It does not support multiple foreign keys that reference the same table/model. Any ideas of how to go about this taking into consideration the below example and problems.

I'm having trouble figuring out what situation would require that... do you have any real world examples? Never really got the whole foobar thing.

I may be misunderstanding your example, but it seems like an association table would take care of your problem, not to mention being more flexible. You currently seem to have a 2-to-many relationship, but the association table would give you a many-to-many relationship.

[oddz]

I'm just trying to decide whether or not its worth the hassle. Considering it breaks the current implementation. I haven't found a need for it yet, that is why I ask.

I do have a example, which I avoided by using a separate table.

project_items
- id
- file (references files(id))
- thumbnail (references files(id))

files
- id
- path

In this case a project_item can have 2 files which breaks the system because dependencies are resolved to the model name. Furthermore, its always assumed that a dependency can only resolve to one field. However, in this case it can resolve to two file and thumbnail. Now, while this won't "break" the system only one field will ever be resolved to. In this case the association would never resolve to thumbnail since file preceded it.

$project_item->file (file instance => file)

[allspiritseve]

I'm just trying to decide whether or not its worth the hassle. Considering it breaks the current implementation. I haven't found a need for it yet, that is why I ask.

I do have a example, which I avoided by using a separate table.

project_items
- id
- file (references files(id))
- thumbnail (references files(id))

files
- id
- path

In this case a project_item can have 2 files which breaks the system because dependencies are resolved to the model name. Furthermore, its always assumed that a dependency can only resolve to one field. However, in this case it can resolve to two file and thumbnail. Now, while this won't "break" the system only one field will ever be resolved to. In this case the association would never resolve to thumbnail since file preceded it.

$project_item->file (file instance => file)

In that case, if there is a semantic difference between items, I'd say $project_item->file and $project_item->thumbnail would be the best approach. You may need both, and there's no reason to call a thumbnail a file just because the thumbnail links to a files table. It's like using a field 'author_id' when you have a 'users' table... author is semantically correct for an article or a blog post, but the actual entity is a user.

[oddz]

The problem with that is that the actual field name is the value of the field. So the thumbnail property of the project_item instance would be a integer.

[oddz]

The other thing I've been playing with recently is the ability to use the root object as a gateway to delete associated objects.

$thread = 
Thread::find(
  ,'one'
  ,array(
    'id'=>8
    ,'include'=>'posts'
  )
  ,array(
    'condition'=>array(
      's'=>array('Post.id = ? OR Post.id = ? OR Post.id = ?',8,90,2)
    )
   ,'conditionMap'=>'{s}'
  )
); 

The above code would return a thread object. The thread object would then have a property posts that is a array. That array though would only contain posts that are related and have a id of 8,90 or 2 as per the condition in the find config.

Now I was thinking it would be useful to implement a way to remove those items through the root (thread) object. Perhaps something like:

$thread->remove(); // deletes dependencies that are apart of object

So in this case projects 8,90 and 2 would be deleted along with all their dependencies and so one until the leaf is reached. However, the thread would stay in tact.

I was also, thinking that it would be neat to include a method that reversed this. Where all the posts would be deleted besides those that have been pulled into memory.

Just some thinking out loud.

[oddz]

In that case, if there is a semantic difference between items, I'd say $project_item->file and $project_item->thumbnail would be the best approach. You may need both, and there's no reason to call a thumbnail a file just because the thumbnail links to a files table. It's like using a field 'author_id' when you have a 'users' table... author is semantically correct for an article or a blog post, but the actual entity is a user.

I can't believe I hadn't thought about this sooner. The answer is really simple actually:

<?php
class File {

    public static $table = 'files';

}

class Thumbnail extends File {

}


echo Thumbnail::$table;
?>

Now a thumbnail shares the same schema, but is a whole other entity. Which makes it possible because everything is related via the model name not the table name. So now a model can have a thumbnail and a file even though they represent the same table.

[REMIYA]

Now I see your logic for making these methods static.

Anything that implements that interface can essentially be used in generate, select, delete, and save methods… simply put.

I think this approach is a bit overburdening.

Because I like the KISS principle, I have made everything even more simplified. Using set and get methods, and embedding the table creation in the database object.

A simple example:

// To create the USER table
if(User::table_exists()==false)User::table_create();

$user = new User(id);

// Create a new USER
if($user->exists()==false)user->create($data);

// Get a property
$user->get("NAME");

// Set a property
$user->set("NAME","John");

// Save, explicitly, otherwise it is done automatically
$user->save(); 

[oddz]

class ActiveRecord {

       public static $table = '';

    public static function find() {
    
        echo self::$table;    
    
    }

}


class User extends ActiveRecord {

    public static $table = 'users';

}

User::find(); // returns ''