[Zope] skinscript and ZSQL

[Steve Spicklemire]

Hi Aaron,

   Since we communicated privately and I now have a much better
understanding of your problem, I realized that my last message to the
list was unclear so I thought I'd take a moment to try to clarify the
'gotchas' that got you, as I see them, so that other folks might not
so easily fall into the same problems. 

   Since you started with "DumbZPatternsExample":http://www.zope.org/Members/sspickle/DumbZPatternsExample
others might be able to follow along better if they refer to that 
example.

Situation: 

You're creating a class that is used as a 'stand-in' for
objects stored in an SQL database. You're using ZPatterns to implement
this stand-in-ability.

These objects have the following attributes (these also correspond to
column names for the table that lives in the SQL database, in your
case, gadfly.)

client_name, email, rate, last_payment, primary, and services.

client_name is used as the 'id' of the object when it is
instantiated in Zope.

In order to accomplish this you:

1) Create a ZClass (ClientObject) to act as the stand-in. 
 1a) set up methods of the ZClass 

  editInstance: self.propertysheets.Basic.manage_changeProperties(REQUEST)
  editInstanceForm: (form to edit the properties of an instance)
  editInstanceInterface: (UI method that calls editInstance to get modification done)
  index_html: (display this instance)

 1b) set up propertysheet for ZClass
  add properties: email, rate, last_payment, primary and services.
   do *not* set up a property for client_name since it is really
   going to be the 'id', and also the property that the rack uses to see
   if the object was found in the database. 

   [ If 'client_name' were propertysheet-property then the Rack could
   create a virtual instance, and then attemt to access the property
   'client_name'. This would then fire the trigger that queries the
   database, which would fail (leaving the virtual instance
   unchanged).  Since the virtual instance is an instance of your
   ZClass even though the query failed the Rack would *still* get a
   valid value for the property from the ZClass property sheet!
   This is why you were getting the 'already exists' error in
   the first place. Taking the 'client_name' property out of the
   ZClass fixes this problem. ]

2) Create a Specialist with a Rack that manages the ZClass instances
   that represent the objects from your database.

  2a) In the Rack set the Storage tab so that it uses ClientObject
   as the class of objects it manages, and choose "loaded by accessing attribute"
   rather than "stored persistently". Set the attribute to 'client_name'.

  2b) In the "Data PlugIns" tab clear out the persistent PlugIns and
   replace them with a single SkinScript method with the following
   lines:
   
   ------------------------------------------------------------

    WHEN OBJECT ADDED CALL sqlInsertProduct(client_name=self.id, 
    email=self.email, rate=self.rate, last_payment=self.last_payment, 
    primary=self.primary, services=self.services)
    
    WHEN OBJECT CHANGED STORE email, rate, last_payment, primary, services 
    USING sqlUpdateProduct(client_name=self.id, email=self.email, rate=self.rate, 
    last_payment=self.last_payment, primary=self.primary, services=self.services)
    
    WHEN OBJECT DELETED CALL sqlDeleteProduct(client_name=self.id)
    
    WITH QUERY lookup_product(client_name=self.id) COMPUTE 
    client_name, email,rate,last_payment,primary,services

    ------------------------------------------------------------

    (The 'STORE ..' part on the WHEN OBJECT CHANGED line is
    important to ensure that the Rack knows that these attributes
    are being stored with this command.)
    
  2c) Add ZSQL Methods, Python Methods/Scripts and DTML methods to the
    Speciliast so that it can carry out it's program.  Everything
    *but* the SQL methods are covered in the previously mentioned
    example. Here I'll only describe the ZSQL Methods used by the
    SkinScript:

    ZSQL Methods:
    
    sqlInsertProduct, args: 
    
    client_name email rate last_payment primary services
    
    code:
    
    INSERT INTO clienttracker_table
    (client_name, email, rate, last_payment, primary, services)
    VALUES
    (
    <dtml-sqlvar client_name type=string>,
    <dtml-sqlvar email type=string>,
    <dtml-sqlvar rate type=string>,
    <dtml-sqlvar last_payment type=string>, 
    <dtml-sqlvar primary type=string>, 
    <dtml-sqlvar services type=string> 
    )
    
    
    sqlUpdateProduct, args:
    
    client_name email rate last_payment primary services
    
    code:
    
    UPDATE clienttracker_table SET
    email=<dtml-sqlvar email type=string>, 
    rate=<dtml-sqlvar rate type=string>, 
    last_payment=<dtml-sqlvar last_payment type=string>, 
    primary=<dtml-sqlvar primary type=string>, 
    services=<dtml-sqlvar services type=string>
    where client_name=<dtml-sqlvar client_name type=string>
    
    sqlDeleteProduct, args:
    
    client_name
    
    code:
    
    DELETE FROM clienttracker_table WHERE 
    client_name = <dtml-sqlvar client_name type=string>
    
    lookup_product, args:
    
    client_name 
    
    code:
    
    select * from clienttracker_table where
    <dtml-sqltest client_name type=string>

  3) Since the items are no longer stored persistently, you need
    to change index_html to use a ZSQL Method to get the object
    ids, rather than defaultRack.getPersistentItemIDs(). In a 'real'
    situation this would probably be tied to some sort of search
    interface.

Good Luck!
-steve

    
>>>>> "AP" == Aaron Payne <aaron@aaronpayne.com> writes:

    AP> Hi all,

    AP> I'm trying to use Gadfly as storage for a Zpatterns rack.
    AP> With the following skinscript trigger I can view records in
    AP> the database.  But I can't insert a new one.

    AP> WHEN OBJECT ADDED CALL sqlInsertProduct()

    AP> WITH QUERY lookup_product(client_name=self.id) COMPUTE
    AP> client_name,email,rate,last_payment,primary,services

    AP> Where:

    AP> sqlInsertProduct is a zsql method.  args: client_name email
    AP> rate last_payment primary services Query template: INSERT INTO
    AP> clienttracker_table (client_name, email, rate, last_payment,
    AP> primary, services) VALUES ( '<dtml-var client_name>',
    AP> '<dtml-var email>', '<dtml-var rate>', '<dtml-var
    AP> last_payment>', '<dtml-var primary>', '<dtml-var services>' )

    AP> lookup_product is a zsql method.  args: client_name Query
    AP> template: select * from clienttracker_table where
    AP> <dtml-sqltest client_name type=string>


    AP> client_name,email,rate,last_payment,primary,services are
    AP> fields of the table clienttracker_table and properties of the
    AP> dataskin.

    AP> If I remove "=self.id" from the trigger I am able to add a
    AP> record to the db through the trigger.  Without "=self.id" in
    AP> the trigger I am unable to view the records in the db.


    AP> I noticed in a post today that said

    AP> ... you should probably choose some attribute other than 'id'
    AP> as the attribute to check for your DataSkin. The 'id'
    AP> attribute is always there...

    AP> Objects are loaded by accessing attribute client_name and I
    AP> still get the "object already exists" error.

    AP> Questions: -Should I just create the record directly with the
    AP> zsql method?  That would defeat the purpose of using
    AP> ZPatterns.  -How do I eliminate the "already exists" error

    AP> Zope version: Zope 2.2.1 Python version: 1.5.2 System
    AP> Platform: freebsd4

    AP> -Aaron


    AP> _______________________________________________ Zope maillist
    AP> - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope **
    AP> No cross posts or HTML encoding!  ** (Related lists -
    AP> http://lists.zope.org/mailman/listinfo/zope-announce
    AP> http://lists.zope.org/mailman/listinfo/zope-dev )