Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D025B9FC0C1 for ; Fri, 8 Jun 2007 14:52:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 86348-07 for ; Fri, 8 Jun 2007 14:52:18 -0300 (ADT) X-Greylist: delayed 00:31:20.307176 by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id A25899FC283 for ; Fri, 8 Jun 2007 14:52:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from sf2a38.a2hosting.com (periship.com [69.39.89.38]) by svr4.postgresql.org (Postfix) with ESMTP id B705E5AF87E for ; Fri, 8 Jun 2007 17:20:52 +0000 (GMT) Received: from dsl093-232-194.hfd1.dsl.speakeasy.net ([66.93.232.194] helo=RayNew) by sf2a38.a2hosting.com with esmtpa (Exim 4.66) (envelope-from ) id 1Hwi8n-0007Xt-CU; Fri, 08 Jun 2007 13:20:49 -0400 From: "Ray Garrison" To: "'Colin Ross'" , Subject: Re: Application Design: Where to implement historical revisions of objects Date: Fri, 8 Jun 2007 13:17:05 -0400 MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_016C_01C7A9CF.4FF6D790" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AceddrpCuFH+trZlQ72vCAh7dXOe9wMeU1pg X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 In-Reply-To: X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - sf2a38.a2hosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - periship.com X-Source: X-Source-Args: X-Source-Dir: Message-Id: <20070608172052.B705E5AF87E@svr4.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.137 tagged_above=0 required=5 tests=BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/3 X-Sequence-Number: 3805 This is a multi-part message in MIME format. ------=_NextPart_000_016C_01C7A9CF.4FF6D790 Content-Type: multipart/alternative; boundary="----=_NextPart_001_016D_01C7A9CF.4FF6D790" ------=_NextPart_001_016D_01C7A9CF.4FF6D790 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit There is no question in my mind that this kind of auditing must be done at the database level. Otherwise, any time you access the underlying database directly and update any tables monitored by the audit log you break your audit trail. I've heard arguments made that access to the database should be restricted to the application front end only, thus negating the possibility of someone directly updating the data and circumventing the audit process, but in 25 years of IT experience I have *NEVER* *ONCE* seen an environment where that was practical 100% of the time. Ray Garrison, CIO 420 East Main Street Bld 2, Suite 2 Branford, CT 06405 203.315.8637 Office 203.315.0429 Fax www.periship.com _____ From: Colin Ross [mailto:colinross@gmail.com] Sent: Wednesday, May 23, 2007 4:11 PM To: pgsql-php@postgresql.org Subject: [PHP] Application Design: Where to implement historical revisions of objects Summary: In a situation where the business model dictates that a certain entity or class of entities should support the viewing of and differencing of historical revisions of the data (read properties) contained in the entity should be supported. Should the logic for the creation and viewing of this historical information be implemented in the application itself In Practice: An application has a model class of "PageContent" which represents the content that is shown on a certain page. Users of the system are authenticated, then able to edit the content. From an application design point of view, where should the logic and underlying system be for the management of historical revisions. My specifics include using PHP (5) and Postgre ( 7.4). I am using the Table/Row gateways provided by the Zend Framework as a base for my model objects. I don't have a large need to remain rdbms-neutral and am fine with a solution the "locks me in" to using postgre as this will eventually be a hosted application in a controlled environment. Option 1 (Implemented in the database/persistence layer): This would follow the path of logic that stems from seeing the database as not just a dumb container-- but as the manager and more of a rich container if you will. When a user updates a certain row (represented as an entity in the application), the database manages the auditing of the historical data via triggers (ON UPDATE,ON DELETE) using an audit table with a similar DDL of the base table, with the addition of audit-specific fields (like revision id/version/etc). The 'current' version of the data always remains in the base table, and the application, in its basic functionality, remains unaware of such functionality, except with the ability to query the audit table. Option 2 (Implemented in the Application Layer): This would see the database as a dumb container, and the model classes on the application side of 'the boundary' would implement the logic of saving the revision is a separate table (or potentially in the same table). All the logic for automagically creating a new revision record on update/delete/etc. would be handled in the application layer. Questions: Which would you choose and why? Outside of (and even including) portability complaints for the application? From a best practices / theory point-of-view which is best? Why? Or is the truly a religious debate with no real right or wrong? Colin Ross I have posted this on the zf-general (ZendFramework) and pgsql-php (Postrge-php) mailing lists to see the differences in views from the two crowds, abit separately to avoid confusing cross-posting between communities. ------=_NextPart_001_016D_01C7A9CF.4FF6D790 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

There is no question in my mind = that this kind of auditing must be done at the database level.  Otherwise, = any time you access the underlying database directly and update any tables = monitored by the audit log you break your audit trail.

 

I’ve heard arguments made = that access to the database should be restricted to the application front end = only, thus negating the possibility of someone directly updating the data and circumventing the audit process, but in 25 years of IT experience I have = *NEVER* *ONCE* seen an environment where that was practical 100% of the = time.

 

 

Ray Garrison, = CIO

420 East Main = Street

Bld 2, = Suite = 2

Branford<= /font>, CT 06405

 

203.315.8637<= span style=3D'font-weight:bold'> = Office

203.315.0429 Fax

www.periship.com=


From: Colin = Ross [mailto:colinross@gmail.com]
Sent: Wednesday, May 23, = 2007 4:11 PM
To: = pgsql-php@postgresql.org
Subject: [PHP] = Application Design: Where to implement historical revisions of = objects

 

Summary: =
In a situation where the business model dictates that a certain entity = or class of entities should support the viewing of and differencing of historical revisions of the data (read properties) contained in the entity should = be supported. Should the logic for the creation and viewing of this = historical information be implemented in the application itself

In Practice:
An application has a model class of "PageContent" = which represents the content that is shown on a certain page. Users of the = system are authenticated, then able to edit the content. From an application design point of view, = where should the logic and underlying system be for the management of = historical revisions.

My specifics include using PHP (5) and Postgre ( 7.4). I am using the = Table/Row gateways provided by the Zend Framework as a base for my model = objects.
I don't have a large need to remain rdbms-neutral and am fine with a = solution the "locks me in" to using postgre as this will eventually be = a hosted application in a controlled environment.

Option 1 (Implemented in the database/persistence layer):
   This would follow the path of logic that stems = from seeing the database as not just a  dumb container-- but as the = manager and more of a rich container if you will. When a user updates a certain row (represented as an entity in the application), the database manages the auditing of the historical data via triggers (ON UPDATE,ON DELETE) using = an audit table with a similar DDL of the base table, with the addition of audit-specific fields (like revision id/version/etc).  The = 'current' version of the data always remains in the base table, and the = application, in its basic functionality, remains unaware of such functionality, except = with the ability to query the audit table.

Option 2 (Implemented in the = Application Layer):
 
This would see the database as a dumb container, and = the model classes on the application side of 'the boundary' would implement = the logic of saving the revision is a separate table (or potentially in the = same table). All the logic for automagically creating a new revision record = on update/delete/etc. would be handled in the application layer.

Questions:
Which would you choose and why?  Outside of (and even including) portability complaints for the application?  From a best practices = / theory point-of-view which is best? Why?  Or is the truly a = religious debate with no real right or wrong?

Colin Ross

I have posted this on the zf-general (ZendFramework) and pgsql-php (Postrge-php) mailing lists to see the differences in views from the two crowds, abit separately to avoid confusing cross-posting between = communities.

------=_NextPart_001_016D_01C7A9CF.4FF6D790-- ------=_NextPart_000_016C_01C7A9CF.4FF6D790 Content-Type: image/jpeg; name="image002.jpg" Content-Transfer-Encoding: base64 Content-ID: /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0a HBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIy MjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCAA1AIQDASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD36iiv Fbn40eI5PE2raPo/g1tSbT7iSJjBI7NtVyoYgKcZxQB7VRXj+lfHFoNag0zxf4autCadgEnkLbRn uwZQce4zXsAIIyDkGgAooooAKKK5rXvGuiaHr2m6Df3MsV7qZCwhIyQMttGT2yeBQBtyXZLmO2hM 7g4JB2op92/oM1GYtTk+9c28PtHEXP5k/wBK5Lx/8R7TwKLPTrTT5NR1e7H+j2UXGFzgE4BPJ4AA ycGuUT4veLtIuoH8U+Bp7SwmcJ58YddmTgZ3ZH4Eiq5rbIR6t9k1IcrqgPs1upH6EU0z6rbczW0N 1GOptyVcf8Bbg/nXNfEDx5N4LvNBghsI7oapcmBi8hXyxlRkYBz979K7enz90FivaXsF9GXgfO07 XVgQyH0IPINWKxPEE9pottJ4huLg20Vmm65ZU3ebF/dI7nOMf/Xqbw54h0/xVodvrGlyO9pPkLvX awIJBBH1FKSXQEatFFFSMKKKKACvnz4deINI8P8AxZ8cS6vqVtYxy3Eqo08gUMfOY4Ga+g6+c/A3 hLQ/FvxX8bW+uWIu4oLmV41MjJtJmYZ+UigC18b/ABfoHizTdK0PQLiPVNSN2HVrYbgoKldoPcsS OB6fSvTNf8aaf8OfCmlJq5kudQaCOCK1h5kmdVAY+wz39+9aWheAPCvhq5FzpOiW1vcAYExBdx9G Ykj8K8o8ff2tcftC6LDYvYrcpaKbL+0Qxg3YkOcLznOce4FAHR2/xpks762TxR4R1PQrK6YLFeTZ ZBn+8Cq/pk+1db498cQeBvDkWsvZtfRyzrCqRyhfvAkNnB44/WuS8T+FPiX4u0GfRtTuvCn2aYqS 0ccwdSpBBUnODx+RNY3xe0280b4H6Hpl/Mk11aXEEMkkZJVtqOBjPPTFAG3r3xutdJmVrLw/fajY xhRd3sRxFE5GSgbaQxGcHkc1p6/4y0eDxh4Rtbrw79ou9TVZLW5uFVZLTeQOhBIPPODXV+GtNtLH whpdhBAi2yWka+XtBByoJyO+SST9a8x+Jf8AyWvwB/10H/owUAVPitHqnhD4maN4/hsje6dDEIJl A/1Z+YEE/wAOQ/B9RXpnhjxl4c8eaYzadcRz5X9/ZzKPMT2ZD1HuMitx5LO8kuLCRoZmVB51u2G+ Vs43KexwfyNeDfFLwPB8O7iz8beEpjp7pcqklsrfICckFR/dOCCvTmgDY+Ps8VrfeDbiZtkUV87u 2M4UGMk/lWjqPxpubCNNTbwVq/8Awj7uFXUZPk3AnhgpXGD2yRmsb4y3S6ovw9u3iAW6uFkaNhkA N5Rwfzr0r4jxo/w18RIygqLCUgEcAhcj9QKAJNW8S6W/w+uvEaQLqOmNZG48lgAJUxypBB+hBFZO l+NNPtfhIviux0UWtjBCzpp8TKoULIVwCBgevSuP8PszfstXO45xY3QH08x6j07/AJNUl/69Jf8A 0eaALMHx8eW2W7PgjV/sZGfPjbcuO5B2gH8605vFI+IFraX/AIZnvpLaFsXFtA/lzRSc/fAYdRja 2SoIOa2vg/z8KNB/65P/AOjGriPB8UOnftIeJLLSQEsXtmaaOMYRXxGx47Ycn6ZNAHtFis6WFst0 265ESiU+r4Gf1zRU9FABXnfgj4f6j4Y8c+JNduru1lt9Vkd4ki3bkBkLDdkY6HtXolFABXDfEb4c weOba1uILtrDWLI7ra7UHjnO1sc4zyCOQa7migDxxPB3xhuohY3njezhtPumaEZlI+oQHP8AwIVv eNvhzfeIfhzpvhiw1GNprOSItc3hb94FVlJOATkk5r0WigCvp9u1pptrbOQzQwpGxHQkACuK8W+B b/xB8QPDPiC3uraO20pw0scm7e/z7vlwMfnXe0UAeceOfh1rOteJIvEvhnxFJpWrRwCBlbIjdQSR yPr0IIrnR8KfGfivUbRvHvieK6062feLW1/j/JVAz0zgnFe00UAcB8Q/AN74tuvDj6dPa20WlXHm uku4ZXKYC4B7LXUeKdJm13wpquk28iRzXlq8KPJnapYYBOO1a9FAHn2l+AtQsfg5N4NkurZr2SCa ITLu8sF3Zh2z39KytY8O3PhP9njUNEvJopp7a1cM8Odp3S7hjIB6GvVq434r/wDJLfEH/Xt/7MKA PMvAuifFC58Bab/wj/iXTrfSriNvLSaP95ANzAgHYe+T1716R8PPh1b+B7a6nmu2v9XvTuurtx15 ztGecZJJJ5JpPg9/ySjQf+uT/wDoxq7igAooooAKKKKACiiigAooooAKKKKACiiigAooooAKZLFH PE0U0aSRsMMjqCD9QaKKACKGK3iWKGNI41+6iKAB9AKfRRQAUUUUAf/Z ------=_NextPart_000_016C_01C7A9CF.4FF6D790--