Quick search:

Some code for an update to phpPeanuts. (Sql Update / readonly columns removal)

2005-08-12 17:19:44

I've probably gone about this in a much more complicated way then necessary, but the functionality I've added is really important to me.  A technique I often use in UI design is to do an outer join between a table that has a fully populated column (such as a list of all the dates in a month), and a table that is sparsely poplulated which might be holidays during the month.  Then, I create an outer join that lets me show the holidays listed amongst all the other days.  With mysql and PHPPeanuts, this is no problem.  The problem comes with mysql 5.0 and how it handles updates to views.  It will let you update a view as long as you are only updating columns in one table.  The problem with the current version of phpPeanuts is that even if a column is marked readonly by something like "$this->addFieldProp('city', 'string',true);", the sql Update command still fires on that column.

So, not knowing the framework well at all (or PHP for that matter), I've successfully made changes to two files in the framework.  

PntDBObject.php - modified the save() function to first iterate through the columns of the table being updated and make a list of columns that are readonly.  Then, pass this list to the function setQueryToSaveObject_table_fieldMap in the file classPntQueryHandler.php as a new last parameter.

classPntQueryHandler.php - Added an extra parameter, &$fieldMapReadOnly, which is a list of column names not to update.  Then, added some code to exclude these columns from the generated sql update command.

I've put my initials PGK near all my changes so they are easy to find.  I'm hoping these changes make sense and would very much appreciate if you would include the functionality in upcoming php releases.  If there is a trivial way to do what I did, I'd be much happier with that instead.  I will assign this to a learning experience rather than useful work.  The other thing I set out to do but ran out of steam was to try and figure out if data actually changed.  If it did not, then don't generate the update.  This would actually avoid the necessity to search for readonly fields.  Maybe if I knew what I was doing, that might have been easier.

So, I've uploaded the two files in a zip on my server.


Thanks for reading.

Peter Kellner
2005-08-13 02:51:21
First bug.   classPntQueryHandler.php.

line 260 should be:  if (isset($fieldMapReadOnly) )
2005-08-13 11:05:49

Thanks for your contribution. At first sight i don't think there is a trivial way to do what you did. OTOH, you do not necessarily need to change the framework to do it. You could have put your version of save() in your domain class, or in a superclass and subclass all your domain classes from it. You could also have overridden setQueryToSaveObject_table_fieldMap on QueryHandler (in your classes root folder), but i doubt if that is necessary, i think supplying a fieldMap that does not contain the fields that are readonly in your view will do the trick.

Nevertheless, your idea of adding the option of having fieldProperties that are loaded from the database but not saved may be worth adding to phpPeanuts one way or another. But i do I need some time to look into this and think about it. I'll get back on it before the next release (1.2 alpha 2 - it will not take a long time).

Greetings, thanks,

Henk verhoeven.
2005-08-14 19:05:48
Hi Peter,

I myself would probably use an in-memory solution for the concrete example of showing the holidays listed amongst all the other days (using transient objects for representing the days of the month). But of course that does not say anything about the abstract case you are making about the use of database views.

One problem though is that in some cases i do want readonly fields to be saved into the database. In such cases the readonly field is usually holding derived information, that i want to be put into the database so that i can search for it. If the derived information is shown in the user interface, but not to be edited, i use a readonly fieldproperty that is saved into the database.

Theoretically, propertydescriptors specify the interface of an object. So if a property is described readonly, the property should not be set. In other words, its setter method should not be called, neither should its propertydescriptors _setValue_for method. Because of encapsulation this implies the property can also not be edited by the user (at least not directly). It does not necessarily imply the properties field value can not be saved into the database: setting the field is allowed for methods of the object itself and maybe that value needs to be saved too.

Persistency of a property is specified by its propertydescriptor being set to be persistent or not.  I agree this does not take the option of retrieving but not saving the property value into account. Maybe the persistency of a property shoudl not have been specified by true or false, but by constants describing at least three possible states: PNT_NOT, PNT_READ_ONLY, PNT_READ_WRITE. Maybe PNT_WRITE_ONLY will also prove usefull at some point. This is one of our options: allow these constants als values for PntPropertyDescriptor>>persistent and adapt the persistency framework accordingly. This can be done in a compatible way, such as defining true to be the equivalent of PNT_READ_WRITE. Existing applications will then remain working as they did before.

A second option could be to add a setting to PntDbClassDescriptor so that you can define for a class and its subclasses that readonly properties are not to be saved into the database. This would be close to your solution, except for the option for the developer to choose between your solution and the old situation.

A third option could be to let the choice to the developer to override the save method and make things work differntly. This would basically be equivvalent to the current situation. However, not saving read only properties could be facilitated by an extra parameter for the PntClassDescriptor::getFieldMapForTable method to specify wheather you want readonly properties to be mapped.

I think the first solution is most consistent and most flexible.  The disadvantage of PntPropertyDescriptor>>persistent to be multi typed (allowing both boolean and constant arguments) does not seem a big problem in practice. The main disadvantage, wich it shares with the second option, is that the field mapping and column prefixing interfaces will have to be checked for implications of the new persistency options. I don't think that is a big problem either. It would be handy to add a parameter $forSave=false to  PntClassDescriptor::getFieldMapForTable.

Anyone who disagrees or has comments on this, please react.


Henk Verhoeven.

Addition made later:

Maybe the following option should be considered too: Do precisely what Peter suggested: make readonly field properties be read from the database but but never saved. Developers that need to write the value of a readonly property to the database can make a readonly property that is derived from a normal fieldproperty that is set to be invisible. This will keep the framework itself simpeler. But there will be some maintenance on existing applications...

Post Edited (08-14-05 21:04)
2005-08-14 19:42:49
I agree that the first method you list is best.  In general, I'm not a big fan of lots of inheritance because it is to easy to get confused as to what is happening.  Adding another layer to be used sometimes,  only would cause confusion.  (though in many cases inheritance can be very powerful)

BTW, if you get time, I'd love to see an example of how you would implement an in memory solution for showing holidays among other days.  That would be helpful for me better understanding how to use the framework.  I really like learning from examples.  Example 14?

Take Care,

2005-08-14 21:59:02

Making that in memory solution would be interesting, i don't have it all worked out. My initial idea involves a static method on class Holiday for SELECTing all holidays between two dates, put them into an associative array using dates as keys. A static method on class Day would then cal this method keep the result in a variable, run through all dates between the two dates, generate a transient object for each day, use isSet to check in the array and set a reference in the day to the holiday if it exits. The associative array's hashing algorithm will make the lookup perform pritty well. The rest would be adding derived properties to  complete the UI. A specific page will be needed to get all days of a month from the static method of Day and show them to the user. Of course you can click on a day to het an EditDetailsPage. That page may use the same mechanism as is used to retrieve instances of the class Gender (example 3), but using the date as id. There is some extra work if you want so be able to edit and save holiday data from the page through the Day object into a Holiday record.

However, this is a first idea, there may be some issues, you never know what you run into. In the end there's allways a possibility that a drastic refactoring is required...  Anyhow, i doubt if i will have time for building an example this month: updating the examples to 1.2 is taking more time then i thought. Then all of the changes in 1.2 need to be reflected in the documentation. There may be some contributions for 1.2 beta from an other developer, i did not yet ask for that (but i will). Those contributions may take time too. There will also be some work on making an upgrade release. And there's a chance of getting commercial work too.  So i am afraid the above idea-writing will have to do for a while.


Henk Verhoeven.
2005-08-17 18:36:41
Just downloaded and installed the alpha 2.  I finally put it, along with my project under SubVersion control so I can check out all the changes.


Thanks very much for the readonly addition.

$this->addFieldProp('monthyearvalue_readonly', 'date', true,null,null,null,null,null,PNT_READ_ONLY);

It does exactly what I need and I don't have to change the framework.

The only other piece of code I still use is I stuck into the _runQuery some code to time it and log the run sql to a database.  Not sure this is for everyone but I always like to see what gets sent to the database and how long it took, even when it is successful.   It's trivial code but I stuck it to the bottom of this in case anyone else wants to see it.

Also, I don't see how it happened, but I had a vertical spacing problem with my funky buttons getting cramped before.  That seems fixed now to though I didn't see an changes in vertical stuff.  Just horizontal.   Not complaining though.



global $queryCount;
$this->error = null;

$timeStart = microtime(true);

$this->result =& mysql_query($this->query);
if ($this->result) {
    if (strtolower(substr(trim($this->query),0,6))=="select") {            
        $this->aantalRecords = mysql_num_rows($this->result);
    } else {
        if (strtolower(substr(trim($this->query),0,11))=="insert into") {            
            $this->insertId = mysql_insert_id();
} else {
    $this->error = $error."<BR>$this->query<BR>".mysql_error();

$timeEnd = microtime(true);
$timeElapsed = $timeEnd - $timeStart;
$sqlLogString = $this->convertToSql($this->query);
$logSql = "insert into sqllog (queryString,timeOfQuery,elapsedQueryTime) values (" . $sqlLogString . ", now(),". $timeElapsed ." )";
Add a Reply
Loading form, please wait
The website will not send you an e-mail when a reply is added to this topic

Back to Topics List