Page 3 of 4

Re: Ideas for lightweight ORM implementation

Posted: Wed Nov 18, 2009 4:31 pm
by inghamn
For the collection classes, it meant I no longer had to keep track of my joins and bound parameters myself. I was having to do that in order to pass valid prepared queries to my PDOResultIterator. It meant when writing List classes, I had to be careful to keep the bound parameters in the same order I was adding the options.

Now, with using ZendDB, I can add these things ad hoc to a Zend_DB_Select object, and let it handle keeping everything straight. This is, of course on top of letting Zend_Db handle the variations in the databases, which was the big reason I tried this out.

Original UserList using PDO.

Code: Select all

 
/**
 * A collection class for User objects
 *
 * This class creates a select statement, only selecting the ID from each row
 * PDOResultIterator handles iterating and paginating those results.
 * As the results are iterated over, PDOResultIterator will pass each desired
 * ID back to this class's loadResult() which will be responsible for hydrating
 * each User object
 *
 * Beyond the basic $fields handled, you will need to write your own handling
 * of whatever extra $fields you need
 *
 * The PDOResultIterator uses prepared queries; it is recommended to use bound
 * parameters for each of the options you handle
 *
 * @copyright 2006-2008 City of Bloomington, Indiana
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
 * @author Cliff Ingham <inghamn@bloomington.in.gov>
 */
class UserList extends PDOResultIterator
{
    /**
     * @param array $fields
     */
    public function __construct($fields=null)
    {
        $this->select = 'select users.id as id from users';
        if (is_array($fields)) $this->find($fields);
    }
 
    /**
     * @param array $fields
     * @param string $sort
     * @param string $limit
     * @param string $groupBy
     */
    public function find($fields=null,$sort='username',$limit=null,$groupBy=null)
    {
        $this->sort = $sort;
        $this->limit = $limit;
        $this->groupBy = $groupBy;
        $this->joins = '';
 
        $options = array();
        $parameters = array();
        if (isset($fields['id'])) {
            $options[] = 'id=:id';
            $parameters[':id'] = $fields['id'];
        }
        if (isset($fields['username'])) {
            $options[] = 'username=:username';
            $parameters[':username'] = $fields['username'];
        }
        if (isset($fields['password'])) {
            $options[] = 'password=:password';
            $parameters[':password'] = $fields['password'];
        }
        if (isset($fields['authenticationMethod'])) {
            $options[] = 'authenticationMethod=:authenticationMethod';
            $parameters[':authenticationMethod'] = $fields['authenticationMethod'];
        }
 
        // Finding on fields from other tables required joining those tables.
        // You can add fields from other tables to $options by adding the join SQL
        // to $this->joins here
        $joins = array();
 
        if (isset($fields['firstname'])) {
            $joins['peopleJoin'] = 'left join people on users.id=people.user_id';
            $options[] = 'firstname=:firstname';
            $parameters[':firstname'] = $fields['firstname'];
        }
        if (isset($fields['lastname'])) {
            $joins['peopleJoin'] = 'left join people on users.id=people.user_id';
            $options[] = 'lastname=:lastname';
            $parameters[':lastname'] = $fields['lastname'];
        }
        if (isset($fields['email'])) {
            $joins['peopleJoin'] = 'left join people on users.id=people.user_id';
            $options[] = 'email=:email';
            $parameters[':email'] = $fields['email'];
        }
        if (isset($fields['role'])) {
            $joins['roleJoin'] = 'left join user_roles on users.id=user_id left join roles on role_id=roles.id';
            $options[] = 'role=:role';
            $parameters[':role'] = $fields['role'];
        }
 
        $this->joins = implode(' ',$joins);
        $this->populateList($options,$parameters);
    }
 
    /**
     * @param mixed $key
     */
    protected function loadResult($key)
    {
        return new User($this->list[$key]);
    }
}
 
New UserList using Zend_Db

Code: Select all

 
/**
 * A collection class for User objects
 *
 * This class creates a zend_db select statement.
 * ZendDbResultIterator handles iterating and paginating those results.
 * As the results are iterated over, ZendDbResultIterator will pass each desired
 * row back to this class's loadResult() which will be responsible for hydrating
 * each User object
 *
 * Beyond the basic $fields handled, you will need to write your own handling
 * of whatever extra $fields you need
 *
 * @copyright 2009 City of Bloomington, Indiana
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
 * @author Cliff Ingham <inghamn@bloomington.in.gov>
 */
class UserList extends ZendDbResultIterator
{
    private $columns = array('id','person_id','username','password','authenticationmethod');
 
    /**
     * Creates a basic select statement for the collection.
     *
     * Populates the collection if you pass in $fields
     * Setting itemsPerPage turns on pagination mode
     * In pagination mode, this will only load the results for one page
     *
     * @param array $fields
     * @param int $itemsPerPage Turns on Pagination
     * @param int $currentPage
     */
    public function __construct($fields=null,$itemsPerPage=null,$currentPage=null)
    {
        parent::__construct($itemsPerPage,$currentPage);
 
        if (is_array($fields)) {
            $this->find($fields);
        }
    }
 
    /**
     * Populates the collection
     *
     * @param array $fields
     * @param string|array $order Multi-column sort should be given as an array
     * @param int $limit
     * @param string|array $groupBy Multi-column group by should be given as an array
     */
    public function find($fields=null,$order='username',$limit=null,$groupBy=null)
    {
        $this->select->from(array('u'=>'users'));
 
        // Finding on fields from the Users table is handled here
        if (count($fields)) {
            foreach ($fields as $key=>$value) {
                if (in_array($key,$this->columns)) {
                    $this->select->where("u.$key=?",$value);
                }
            }
        }
 
        // Finding on fields from other tables requires joining those tables.
        // You can handle fields from other tables by adding the joins here
        // If you add more joins you probably want to make sure that the
        // above foreach only handles fields from the users table.
        $joins = array();
 
        // Firstname, lastname, and email come from the People table
        if (isset($fields['firstname'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.firstname=?',$fields['firstname']);
        }
        if (isset($fields['lastname'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.lastname=?',$fields['lastname']);
        }
        if (isset($fields['email'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.email=?',$fields['email']);
        }
 
        // To get the Role, we have to join the user_roles and roles tables
        if (isset($fields['role'])) {
            $joins['ur'] = array('table'=>'user_roles','condition'=>'u.id=ur.user_id');
            $joins['r'] = array('table'=>'roles','condition'=>'ur.role_id=r.id');
            $this->select->where('r.name=?',$fields['role']);
        }
 
        // Add all the joins we've created to the select
        foreach ($joins as $key=>$join) {
            $this->select->joinLeft(array($key=>$join['table']),$join['condition']);
        }
 
 
 
        $this->select->order($order);
        if ($limit) {
            $this->select->limit($limit);
        }
        if ($groupBy) {
            $this->select->group($groupBy);
        }
        $this->populateList();
    }
 
    /**
     * Hydrates all the objects from a database result set
     *
     * This is a callback function, called from ZendDbResultIterator.  It is
     * called once per row of the result.
     *
     * @param int $key The index of the result row to load
     * @return User
     */
    protected function loadResult($key)
    {
        return new User($this->result[$key]);
    }
}
 

Re: Ideas for lightweight ORM implementation

Posted: Wed Nov 18, 2009 5:03 pm
by inghamn
Because this latest iteration relies heavily on Zend Framework stuff, I don't think I can really call it "lightweight" anymore.

Although, once I knew what I was doing with Zend_Db, I was able to update the code generators in my scaffolding, point it at the (insanely-laid-out) oracle database, and generate these classes by reverse engineering the database. Zend_Db had built in functions that helped out with the cross database problems here. I was having to build to support Oracle 8, Oracle 10, and MySQL, with the possibility of moving to MS SQL Server next year.

$zend_db->listTables()
$zend_db->describeTable($tableName)

All in all, I originally thought this project would take me 2 months. It ended up taking me 4.5 months. But I don't know if I could have done the cross database support without Zend_db. Well, maybe I could have, but it would have taken longer to learn all the variations and write my own SQL string libraries.

Using Zend_Db allowed me to keep building stuff in the development style that makes sense to me (AR + Collections). Although over the holidays, I'm going to be trying out Doctrine 2.0. I know it's alpha, but it's looking much more usable than the 1.5 version. And namespace support is nice.

I'm starting to wonder how long I can keep up the pretense, though, of my own framework. I guess I've still got my own PageControllers + Templates system to be proud of. Even if I do end up swapping out the entire model layer.

At some point I have to wonder if I've jumped the shark. I look at what I've got in my framework, and there's really not much there. I say it's small, but only because I'm starting to lean heavily on these larger frameworks. And all along I've been trying to out-simplify ZF and CI and all these other frameworks.

But yeah, my stuff is simple.....right....?

Re: Ideas for lightweight ORM implementation

Posted: Mon Nov 30, 2009 9:39 pm
by alex.barylski
I have just finished reading the chapter in PoEAA for the 1000th time and I`m still confused as I was when I first read it. :P

Anyways, I`d like to just ask, what exactly is an ORM and answer with a few expected features:

- Map objects to tables
- Perform cascading operations (deletes)
- Simplify moderate SELECT queries by pulling on associations for you

I have a page object and that page object relies on a hasMany relationship with meta data and a hasOne relationship with page data I would ideally like to have an ORM system perform that operation for me while allowing me to tweak the query with a custom WHERE clause of some sort.

I`m currently in the process of implementing a basic mapper on top of my existing PDO Wrapper layer and I am trying to anticipate some implications of going with one approach over an other, so this thread has been useful to me. :)

I really hate the idea of having to inherit my model class from any ORM or any data mapper though...thats a big no-no for me. For one it doesn`t make sense, as a model (in my experience) is typically a composition of various data sources, etc...not just a database ORM. Two my models occassionally need to derive from base platform models, etc. This is definitely a design issue which I will avoid in my own implementation.

Other than the features listed above, what else is expected from an ORM -- other than the obvious questions like easy to use (which is very subjective anyways)...

Cheers,
Alex

Re: Ideas for lightweight ORM implementation

Posted: Mon Nov 30, 2009 10:38 pm
by allspiritseve
PCSpectra wrote: - Map objects to tables
People always seem to take this phrase as meaning that any system that takes tables and gives you objects to work with is an ORM. However, I think a key component of an ORM is that it can handle discrepancies between your object model and your database schema.
PCSpectra wrote:I`m currently in the process of implementing a basic mapper on top of my existing PDO Wrapper layer and I am trying to anticipate some implications of going with one approach over an other, so this thread has been useful to me. :)
Good, I wish this topic were discussed more often.
PCSpectra wrote:I really hate the idea of having to inherit my model class from any ORM or any data mapper though...thats a big no-no for me.
Agreed, I dislike that as well. In fact, that is a core feature of the Skeleton ORM: no need to extend a class or lose control of your DB schema just to make our mapping job easier.
PCSpectra wrote:Other than the features listed above, what else is expected from an ORM -- other than the obvious questions like easy to use (which is very subjective anyways)...
I'm not sure which section it is in PoEAA (OR structural patterns maybe?), but Fowler talks about such patterns as eager loading, lazy loading, and batch lazy loading. If an ORM is going to handle object relations, those patterns are a necessity. Then, if you are implementing lazy loading, you probably need an identity map to avoid circular references.

I would also argue that an important feature is the ability to override the automatic SQL generation and write your own optimized SQL, while still having the full ORM mapping capabilities available to use (I don't know how other ORMs work, but ours has naturally gravitated to having two separate functions: mapping and sql generation).

Hope that helps... I'm by no means an expert but I've learned a lot from working on the Skeleton ORM with Arborint.

Re: Ideas for lightweight ORM implementation

Posted: Mon Nov 30, 2009 11:36 pm
by alex.barylski
People always seem to take this phrase as meaning that any system that takes tables and gives you objects to work with is an ORM. However, I think a key component of an ORM is that it can handle discrepancies between your object model and your database schema.
Haha...you took me to literally. I agree that is the intent of ORM, to manage (shadow data) the descrepancies between objects and tables. For instance, if I were to use ORM something like this:

Code: Select all

class Page{
  $title = '';
  $meta = new Meta(); // ORM object
}
$meta would map to the meta table but notice the lack of FKID's - it's an object which is resolved/translate into proper foriegn key lookup and result in a collection of meta results associated with the Page object. Horrible example I'm just using it to demonstrate how I feel about ORM and supporting the impedance mis-match (if that is correct terminology). :)

That being said, how do you interpret what Fowler says about data mapper? Seems there is frequently a push to stay away from the 1:1 mapping between table and object.
I'm not sure which section it is in PoEAA (OR structural patterns maybe?), but Fowler talks about such patterns as eager loading, lazy loading, and batch lazy loading. If an ORM is going to handle object relations, those patterns are a necessity. Then, if you are implementing lazy loading, you probably need an identity map to avoid circular references.
I was hoping for higher level features, not so much architectural/implementation requirements, but still appreciated. :)

That being said, can you think of any high level features which I may have missed out on in my list above? Cascading deletes seems an obvious one, but wouldn't ORM be redundant then on RDBMS that support forign key constraints...not really asking as I know how I feel about that (I side with ORM still) just throwing it out there. :)

Cheers,
Alex

Re: Ideas for lightweight ORM implementation

Posted: Tue Dec 01, 2009 12:00 am
by allspiritseve
PCSpectra wrote:Haha...you took me to literally. I agree that is the intent of ORM, to manage (shadow data) the descrepancies between objects and tables.
I don't mean just shadow data, but columns that have been renamed, columns that map to multiple properties, properties that map to multiple columns, and so on. There are often scenarios where the DB is old or crusty (or maybe just optimized by a db expert, and doesn't fit with the object model you've written). You could change the db (but that often isn't possible) and you could change your objects, but often they are used in many different parts of your application and simply mapping the unchangeable to the changed data can be extremely useful.

Also, I didn't mean to imply that you were interpreting "map objects to tables" in such a generic way, but I wanted to elaborate on that because I think that is a crucial and often misunderstood aspect of an ORM.
PCSpectra wrote:That being said, how do you interpret what Fowler says about data mapper? Seems there is frequently a push to stay away from the 1:1 mapping between table and object.
He's not pushing away from 1:1 mapping-- he's saying that a DataMapper isn't the right tool for the job if you have a 1:1 mapping. An ActiveRecord object or a Table Data Gateway are much simpler patterns that handle 1:1 mapping, because they don't have to deal with all the crap I mentioned above.
PCSpectra wrote:I was hoping for higher level features, not so much architectural/implementation requirements, but still appreciated. :)
Hmmm... can you explain what you are looking for when you say "higher level features"? Just about everything I mentioned are features that the end user should have at their disposal.

Re: Ideas for lightweight ORM implementation

Posted: Tue Dec 01, 2009 8:44 am
by alex.barylski
He's not pushing away from 1:1 mapping-- he's saying that a DataMapper isn't the right tool for the job if you have a 1:1 mapping
Maybe eh? Can you cite any singlae statement in chapter 3 where he discusses this? It's very possible I simply read it so many times my initial understaning is what stuck and the rest re-asserted that understanding, which is a common flaw of mine.
An ActiveRecord object or a Table Data Gateway are much simpler patterns that handle 1:1 mapping, because they don't have to deal with all the crap I mentioned above.
TDG/RDG are simple objects which map 1:1 that much I understand (active record being a RDG with custom business logic implemented). As a gateway for SQL I would expect to see explicit SQL in these layers, almost providing a DAL (data access layer) for which the models might use to decouple the model from any data source.

So what would you say the difference between data mapper and ORM? Data mapper to me, still means 1:1, at least the term 'mapper' itself implies mapping one entity to another, whether thats is objects to XML (ie: DOM), etc.
Hmmm... can you explain what you are looking for when you say "higher level features"? Just about everything I mentioned are features that the end user should have at their disposal.
- Cascading deletes

Cheers,
ALex

Re: Ideas for lightweight ORM implementation

Posted: Tue Dec 01, 2009 1:24 pm
by Christopher
PCSpectra wrote:Maybe eh? Can you cite any singlae statement in chapter 3 where he discusses this? It's very possible I simply read it so many times my initial understaning is what stuck and the rest re-asserted that understanding, which is a common flaw of mine.
He says in Chapter 3 that if you have a "close correspondence between classes and tables" then ActiveRecord is easier. If you have "something more complicated" implying less 1:1 then DataMapper. He also several places warns about the complexity of implementing DataMapper and says pretty clearly that if you don't need it to save yourself the trouble. And if you do need the complexity think of buying rather than building.
PCSpectra wrote:TDG/RDG are simple objects which map 1:1 that much I understand (active record being a RDG with custom business logic implemented). As a gateway for SQL I would expect to see explicit SQL in these layers, almost providing a DAL (data access layer) for which the models might use to decouple the model from any data source.
I don't think you would see "explicit SQL", meaning to me programmer coded SQL. You would see SQL generation.
PCSpectra wrote:So what would you say the difference between data mapper and ORM? Data mapper to me, still means 1:1, at least the term 'mapper' itself implies mapping one entity to another, whether thats is objects to XML (ie: DOM), etc.
DataMapper is one of many ORM patterns. You are playing with words by saying that it "still means 1:1, at least the term 'mapper' itself implies mapping one entity to another." The relation is between the data table schema and the in-memory representation. Pure 1:1 means they are the same. A DataMapper moves data between one or more data table schema and a potentially totally different in-memory representation.
PCSpectra wrote:- Cascading deletes
I think that may be a more basic functionality. It is the Identity, Key, Locking, etc. functionality that increases complexity to me.

Re: Ideas for lightweight ORM implementation

Posted: Tue Dec 01, 2009 7:31 pm
by alex.barylski
DataMapper is one of many ORM patterns. You are playing with words by saying that it "still means 1:1, at least the term 'mapper' itself implies mapping one entity to another."
Hmmm...yes true enough...mapper could mean 1:1 or otherwise. That is actually how I have always understood any mapper to work, almost like an adapter, in that it translates from one schema, protocol, etc to another, whether that is 1:1 is irrelevant. That last part is what you are saying, correct?
I think that may be a more basic functionality. It is the Identity, Key, Locking, etc
The sell-able feature set of a ORM. Does it change the schema dynamically to reflect/meet the requirements of new/removed properties of the objects which are mapped, cascading deletes, foriegn key constraints, etc.

Locking just caught my attention...what do you mean by this? Skeleton ORM supports row/table locking when using the ORM? I thought about something like this, similar to transactions, I think I'd rather leave that to the RDBMS, personally. If someone requires that advanced functionality that badly they should probably be running with the proper RDBMS instead of something like SQLite, etc. Beside that functionality is so complex and difficult to get right and yet mission critical in the situations it is intended to address, to me it just made sense to scrap that idea.

Of course, locking might mean something totally different to you, which I'm interested in hearing.

I'm also curious to know when you experienced the need to implement identity maps in Skeleton ORM. Admittedly I have not yet implemented much other than a simple data mapper (which can optionally have 1:1 mapping with tables or not) but I have yet to encounter many situations where I needed to cache objects in a map lookup.

Cheers,
Alex

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 1:52 am
by Christopher
PCSpectra wrote:Hmmm...yes true enough...mapper could mean 1:1 or otherwise. That is actually how I have always understood any mapper to work, almost like an adapter, in that it translates from one schema, protocol, etc to another, whether that is 1:1 is irrelevant. That last part is what you are saying, correct?
Yes.
PCSpectra wrote:The sell-able feature set of a ORM. Does it change the schema dynamically to reflect/meet the requirements of new/removed properties of the objects which are mapped, cascading deletes, foriegn key constraints, etc.
The sell-able feature of an ORM is whether is allowed me to solve my complicated problem.
PCSpectra wrote:Locking just caught my attention...what do you mean by this? Skeleton ORM supports row/table locking when using the ORM? I thought about something like this, similar to transactions, I think I'd rather leave that to the RDBMS, personally. If someone requires that advanced functionality that badly they should probably be running with the proper RDBMS instead of something like SQLite, etc. Beside that functionality is so complex and difficult to get right and yet mission critical in the situations it is intended to address, to me it just made sense to scrap that idea.
The locking I am talking about is not row/table locking. It is the ability to lock the data associated with some Identity/key so that another process cannot access until it is free.
PCSpectra wrote:I'm also curious to know when you experienced the need to implement identity maps in Skeleton ORM. Admittedly I have not yet implemented much other than a simple data mapper (which can optionally have 1:1 mapping with tables or not) but I have yet to encounter many situations where I needed to cache objects in a map lookup.
Identity Map is easy to implement. Harder is to decide how many maps and what the unique identifier is. Once you sort those out it is straightforward. It is probably not just one implementation to cover all the possibilities. You should start contributing to the code. ;)

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 6:54 pm
by alex.barylski
The locking I am talking about is not row/table locking. It is the ability to lock the data associated with some Identity/key so that another process cannot access until it is free.
I'm not sure I see the point. Locking same instance, in-memory access? How and why would this be an issue in a single-threaded application environment like PHP typically runs? At least I interpret this as some kind of thread-safety mechanism??? Unless you mean setting a flag in the record to prevent other SQL access to the record, in which case, isn't that row level locking?
Identity Map is easy to implement. Harder is to decide how many maps and what the unique identifier is. Once you sort those out it is straightforward. It is probably not just one implementation to cover all the possibilities. You should start contributing to the code
Can you not explicitly state the unique identifier? In my case the mapper is capable of determining the primary key of a table to which it maps, if this is desired by the developer. However I typically prefer to specify what the 'unique' keys are myself (which reminds me -- I currently call them 'finder' keys but I like the term 'unique' keys much better).

Cheers,
Alex

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 7:28 pm
by Christopher
PCSpectra wrote:I'm not sure I see the point. Locking same instance, in-memory access? How and why would this be an issue in a single-threaded application environment like PHP typically runs? At least I interpret this as some kind of thread-safety mechanism??? Unless you mean setting a flag in the record to prevent other SQL access to the record, in which case, isn't that row level locking?
The thing you want to lock is the object. It may be data from several tables. The lock would only provide locking when accessing the data through the ORM.
PCSpectra wrote:Can you not explicitly state the unique identifier? In my case the mapper is capable of determining the primary key of a table to which it maps, if this is desired by the developer. However I typically prefer to specify what the 'unique' keys are myself (which reminds me -- I currently call them 'finder' keys but I like the term 'unique' keys much better).
A single PK is not sufficient for all cases.

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 7:43 pm
by alex.barylski
The thing you want to lock is the object. It may be data from several tables. The lock would only provide locking when accessing the data through the ORM.
I figured as much, but when in PHP would such a requirement ever arise in a single threaded application? I can almost see this becoming a issue in itself, with programmers locking a object and forgetting to unlock, not really understanding the mechanics behind such a complex feature.

I am sure you have valid reasons, but I am curious to hear real life (or even phantom) cases of where this would come in handy.
A single PK is not sufficient for all cases.
No, I agree.

For instance, my mapper was at one time restricted to single keys, until I reimplemented my authentication component and realized that I could save myself some work (and probably clock cycles) if my mapper class was capable of using two fields for a lookup and they were not primary keys but any field I required. This enhancement has served me well in dozens of instances since then.

But I can still explicitly set the keys required in 100% of the cases I have worked on. Although the reason I am re-investigating my own data mapper (and more particularly ORM) is my growing interest in developing `business objects` and automatic form generation, etc. In these cases it is obviously impossible to know what the unique key(s) are supposed to be, although primary key can be determined.

Cheers,
Alex

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 8:57 pm
by Christopher
PCSpectra wrote:I am sure you have valid reasons, but I am curious to hear real life (or even phantom) cases of where this would come in handy.
There are many real-life cases, for example editing any data via a web interface. Since multiple people can edit the data there is the possibility of overlapping reads and write the entity needs to be locked. And it is often the case that multiple table need to be updated. I use a Offline Lock in an cron based email queue. A job runs every five minutes and attempts to send a specified number of emails. But it may take longer than five minutes to send all the emails, so another job may start before the previous one ends. So each job locks a block of records.

Re: Ideas for lightweight ORM implementation

Posted: Wed Dec 02, 2009 9:33 pm
by alex.barylski
There are many real-life cases, for example editing any data via a web interface. Since multiple people can edit the data there is the possibility of overlapping reads and write the entity needs to be locked. And it is often the case that multiple table need to be updated. I use a Offline Lock in an cron based email queue. A job runs every five minutes and attempts to send a specified number of emails. But it may take longer than five minutes to send all the emails, so another job may start before the previous one ends. So each job locks a block of records.
OK how does object level locking differ from row-level locking here? Row-level locking lasts for the duration of the script execution I imagine, whereas I supposed one could implement object locking and persist the lock in the database, but that requires storing meta data somewhere.

I'm not sure I would personally want that implemented at the PHP level (or at least ORM), for starters it's not consistent across the board. Yes someone might edit a record which I am also working on (I think this is a basic compromise most developers are willing to accept and thus clients are assumed to as well -- but anyways) however there is nothing to stop say phpMyAdmin from circumventing this lock, correct?

If you need to prevent concurrency such as the case in mission critical software: PHP on the web is *maybe* not the right tool for the job.

I would argue this feature is somewhat 'rocket' like in intent, in that you need an all or nothing solution, not something half-fast. I wouldd rather find some alternative solution, such as establish a persistent connection, store the resource in shared memory and use transactions and row-level locking to truly enforce this policy.

Just an opinion of course :)

Cheers,
Alex