Forum OpenACS Development: Too lazy to code

Collapse
Posted by Tom Jackson on

After the beta release of OACS4 this weekend, I am going to release a package I call query-writer. I am having trouble explaining the benefits of the package, thus this post, in hopes I'll find the right words or examples eventually.

This package started out as just a few procs which I have previously described: https://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0002Jz

The main focus of these procs was to ease the porting time for oracle pl calls. It handled overloaded functions, and the ordering of the function attributes as well as replacing missing attributes with the default values.

This seems to work fairly well, but I was just too lazy to want to write the initial oracle file as well as the ported version xql files for simple inserts, updates and deletes.

What the query-writer package does it to handle inserts, updates and deletes of registered parts of the data model. The package relies on specially named form variables for url submission, or on a simple api for tcl code access.

First the url based system. A single user file called qw.tcl is used to process all forms. A single form submission can handle any number of inserts, updates or deletes of registered objects. Simple objects which have attributes from only one table can be handled without pl functions. Complex objects, such as those referencing acs_objects, require pl functions. Inserts work fine for objects with a composite primary key, but deletes of this type are not supported, and updates are not simple.

The format of the form variable names is easiest explained by example:

new.product.name.0 new.product.description.0 set.product.name.1234 set.product.descripiton.1234 del.product.1234

All of these could be on one form and would work to create a new product with the given name and description, set the product name and description of product with id 1234 to the values, and delete product with id 1234.

Any filters registered for the attribute are run, and violations are reported in the same way as with ad_page_contract.

Without some form of permission checking, this setup would be a real disaster. Permission checking is performed, if requested, on objects which use the acs_objects data model. In addition, a simple group system is used, completely separate from the acs groups data model. There are two initial groups: admin and default. What is grouped, is access to the attributes, and to operations on the attributes. Additional groups can be created to suit the developer's needs. Essentially what this allows is for one query processing page to control a very fine grained access to attributes. The developer can write any procedure desired to set the effective group of a user. The current procedure assigns site wide admins to the admin group, and every one else to the default group. Besides control of access to attributes, access to certain values of an attribute can also be specified. So, for instance, if you have an attribute such as order_state, you might allow users to set the order state to only a few of the possible values.

Not all values that need to be inserted into the database should pass through the html form. To get around this problem, additional attributes, and tcl code which would set the attribute can be specified. For instance, the context_id is often set to something useful for permissions. If you wanted to set it to the user_id you could use:

context_id;upvar user_id context_id for that object. Any number of such pairs can be included. The second of each pair is eval'd.
Collapse
Posted by Tom Jackson on

Although the url method is useful, there will arise situations where more custom code is required. The internal api for the query-writer is very simple. To use the api, you have to construct an array holding the attributes:

set myarray(name) "Product1" set myarray(description) "One great product!"

Then you can call the qw_new proc:

qw_new product myarray

This api does not perform filtering or permission checking, but it still performs any extra eval's specified for the object. Updates and deletes are just as easy:

qw_set $obj_id product myarray qw_del $obj_id product

The query-writer package has a complete admin ui for adding objects, attributes, groups, functions, assigning attribute access permissions and dumping data for a particular package of objects. It also has numerous examples of use: some of the query-writer object tables use the qw.tcl ui. All database values used in the query-writer are loaded into nsv arrays. The operation of the query-writer does not require any additional database activity above the inserts, updates or deletes being performed.

Currently lacking is a helpful form variable holding the current value of the attribute. These will start with 'cur'. This will help weed out unchanged values in a form for updates. Also missing is after a delete, any corresponding set array should be deleted as well: you cannot update what doesn't exist. I stubbed into the data model additional permission checking, but it is not supported yet.

The current setup only works for PostgreSQL. However, there are six procs which handle the formatting, dml and pl for new,set,del. These are carefully separated. The names are specified by the developer, so to port the query-writer to oracle, you need to write these six small procs and then specify the names you use in the datamodel that holds these names. You do not have to edit any of the query-writer code. I estimate this will take no more than six hours.

Collapse
Posted by Lars Pind on
Tom,

I'm not quite clear on the practical steps involved in using this thing:

You write and load your data model.

Then you define your data model all over again using the query writer admin UI (plus some more stuff you define there).

Then /qw/qw.tcl will immediately start doing the right thing (thanks to nsv_arrays being set).

Is that it?

How do we know/ensure where the /qw/qw.tcl file is mounted?

Collapse
Posted by Tom Jackson on

I will outline the basic steps here to using the query writer. Since it now writes up the pg create script functions and the drop script, I think you can possibly ignore the pkbuilder package. That package will create both oracle and pg packages, but I think the query writer will handle things like function overloading, which is needed in pg when you have more than 16 attributes.

  • Install query-writer version at lease 0.5.1. I manually loaded the data model with i query-writer-create.sql, then I used the apm:
    • Select the Install packages link on the main APM page.
    • Select only the query-writer package.
    • Click on the Next --> button.
    • Unselect the load checkbox next to the data model.
    • Click on the Install Packages button.
    • That should be it, you might have to restart the server to load the bootstrap data.
  • Use the site-map to mount the query-writer package at /qw.
  • Maybe you have to restart after it is mounted, I'm not sure if you need to do this however.
  • Visit the main admin page at /qw/admin
  • Click on the Objects, then the Add/Select Object links.
  • Scroll down the page an see if the query-writer objects are listed in the Current Objects table.
  • Assuming these are there, proceeed, otherwise try to figure out why the bootstrap data didn't load.
  • Create an object by filling out the form at the top of the page. An object is the container for all the attributes in the main table plus any additional attributes for the acs object or other stuff you want to pass to a function.
    • The object_id could be different from the object. But usually it will be the same. For pg functions that end up looking like 'xy__new', the object is 'xy', but the object_id could be anything.
    • The Object Table is the main table, and the Key is the primary key for this table.
    • Eval After is a little complicated. Usually you don't pass every attribute using a form. Sometimes extra attributes need to be set based on the current submission environment. For instance, I usually set the context_id to something useful. Examples of setting the context_id :
             context_id;upvar user_id context_id
             context_id;set context_id [ms_get_customer_id]
             

      An example in query writer using a the db api to select an sequence for insert:

              fn_id;set fn_id [db_string fn_nextval "select  
              nextval('qw_fn_sequence')"]
             
    • Please ignore the perm checks, these are now handled with a new perm check filter. The filter names are 'admin_pemission' , 'write_permission', and 'read_permission', and you can add these to the object attribute filters later on.
    • Right now the operations are set at 'new;set;del', I haven't added any other operations yet. This is the starting name of the form variables, and the names of the arrays used in qw.tcl.
    • The new, set and del function names should correspond to what you wish to call these. I use object__new, object__set_attrs, and object__delete. These should not be in this table, but they are. They are needed, and they do have to match the names of your functions used later on.
    • The permission_p radio allow you to turn the permission_p checking off. Objects don't have to be acs objects, or developers might write a filter to do permission checking in a different way. Don't use permission_p is you have figured out a better way to check this.
    • Click on the Add Object button.
  • Add attributes to the object. Once the object is created, select it from the Current Objects table. There are three links at the top of the 'one' page for the object. The first allows you to add attributes.
    • Usually the attribute and the attribute Id will be the same. If you want to obscure the backend datamodel from the user, you can choose different names for each. Of course, the attribute has to match the backend data model.
    • Datatype. Select one, unfortunately I left out 'text'. If you need the text, type, edit the form. This is a general datatype like what gets fed to a function.
    • Filters, these are the filters just like in ad_page_contract. Unfortunately, or fortunately, not all filters are the same. You cannot use trim, notnull, or some of the other filters. However, all values are trimmed at the array level, so this gets done anyway. The notnull filter is useless with query writer anyway. I use integer on all integer values.
    • Default value is usually set to any default in the table definition. This is not used directly, when you define your functions, each one can have a different default for the same attribute.
    • The maximum length is used for char and varchar attribute types. A separate filter is run if this is set. This avoids many database rejections.
    • Description, is usually limited to a very brief human readable name for the attribute.
    • Help Text further describes the attribute.
    • Click on the Add Attribute button.
Collapse
Posted by Tom Jackson on

Okay, so continuing with the query writer. Assuming all the attributes have been added for an object, visit the one object page and choose the second link, which allows you to build up a function using the attributes. (/qw/admin/objects/one?object_id=xy).

First note that if you don't want to use functions to insert, update or delete, you don't have to, 'IF' you use a single table to hold the object data (no acs_object) and that table has a single attribute as the primary key column. If you don't have functions defined, query-writer will use regular dml to do the work.

You add a new function by giving it a name a description and selecting the type: new, set or del.

Once you have added the function, you can click on the link in the table under 'Function Name'. A table listing all the attributes is divided into two sections. Initially all the attributes are in one section. To add an attribute to a function, you need to choose a default value and an order. Once you add an attribute you can update either the order or the default value, or you can remove it from the function definition. Attributes that are part of the definition show up in the top part of the table.

Default values can be different for the same attribute used in more than one function. An example of this is the primary key, or object_id attribute. The new function usually defaults to NULL, whereas there is no default value in the set function. If no default is provided, it means that a non null value is required. In the set function, the primary key attribute has not default value, all the other attributes have a null default. The set function only updates passed in attributes that are not null. Fortunately in postgresql, the empty string is not equal to null, so inputs that clear a form entry field are set to the empty string. This leaves the issue of how to set a field to null once it has contained something not null. A future version of query-writer will impliment the rst function which will reset the database value to some specific preset value. If that is not enough, I guess a nul function will have to be implimented.

Once all the functions are defined, you can print out the function bodies, and the drop script for the object.

On the /qw/admin/objects/ page, in the Current Objects table, the final column has a link to the pg-create script. The top part of the script will be appended to your object-create.sql file and the bottom part will become your object-drop.sql file. Sorry, right now you don't get a create table script.

Even though you now will have a working package, you still will not be able to use the qw.tcl file. You have to setup group permissions for the attributes and objects. Fortunately this is relatively easy.

The main admin page /qw/admin has a link to groups. One group comes built in to query writer: the admin group. If you want a special group for your package you can create a new group. There might be another special group called Default. These groups have nothing to do with acs permissions. A user can only be in one group for a given page request. The developer must write a script to define which group a user is in. As a guide, the admin group should be able to do anything, while the default group should be allowed to do only what the UI will allow, the expected use of the package. The main purpose of the groups is to allow developers to provide different features to different groups while having a single ui and backend. The idea is to provide a list of booleans for each group. The ui can use these booleans in the template if tags to 'white out' un supported values, or attributes. I still need to write the procedure to write these booleans, but they will be similar in form to the form variable names, so if you had a input variable named new.product.state.0, and the possible product states were 1-5, you would have booleans named product_state_1_p, product_state_2_p, etc. The backend already blocks hacking attempts using similar values. If you add a new group, choose some odd number that might be easy to substitute in the bootstrap file.

Assuming all your package tables begin with some nice distinguishing prefix like 'qw_', you can dump all this data into a bootstrap file so that you can package it up and give a copy to all your friends. Visit the /qw/admin/dump/ page and enter your prefix. You have to copy this to some convenient location so your startup script can easily find it. There is an example in the query-writer-init.tcl file.

Collapse
Posted by Jon Griffin on
I guess I missed something, where can I download this?
Collapse
Posted by Tom Jackson on

Jon, you can get it here: http://zmbh.com/query-writer/

Collapse
Posted by Jon Griffin on
I don't see a way in the admin to delete an attribute that I decided I don't want.
Collapse
Posted by Tom Jackson on

Yeah, I need to add that, I think the reason I initially left it off was that I wanted to use the qw.tcl file to delete attributes, and I hadn't gotten that far yet. There is no operational downside, but I'll fix it tonight.

Collapse
Posted by Tom Jackson on

Okay the new version is available that allows you to delete attributes. You can just copy over the add.adp file and add the delete.tcl file 😉

Collapse
Posted by Jon Griffin on
Since I am being nit-picky ;)

  • The navigation is messed up, i.e. it is somewhat cumbersome to not have the nav bar work for many links.
  • Is there a way to add pretty_name & pretty plural to acs_object__create_type?
I am enjoying not writing code, I will maybe add the create db stuff if I have time and get a handle on what you are doing. I would love to just pick a table and have the basic parts filled in.
Collapse
Posted by Tom Jackson on

I think the pretty_name and pretty_plural would go into the qw_objects table, but I just wrote the pg-create script this weekend and I was happy that I only had to add the datatype field to the previous data model to get that script to work. There are many deficiencies in the current setup. In truth, you should really only have to add attributes as referring to a real table attribute. From this attribute reference, you should be able to figure out lots of things.

Navigation is screwed up because I added it before the need for all the links evaporated. I have to over compensate here. I usually lose files because they are not linked to anything. This is still the case. The /qw/admin/nsv/nsv file is not linked to anything, and is somewhat useful for debugging. I don't think the tools directory is linked either.

Collapse
Posted by Ola Hansson on
Tom,

I've been trying out your query-writer package a little, but not long enough to understand it in detail. It looks promising, though.

The question I'm asking myself at this point is whether or not it is a tool I want to build my future packages with. Packages that I want to have a chance to submit as "regular" OpenACS packages.

The way I understand it, a package that has been developed by using the query-writer will depend on the query-writer being installed (and bootstrapped with qw-dumps from my package) on any system that wants to run the new package. Is that correct?

If so, qw-made packages won't run on a basic install of OpenACS as of today. In that case I reckon developers will be reluctant to write packages using qw until/unless it is added as a core OpenACS package.

Hmm. I guess Oracle support will be required for this to happen...

Nonetheless, I think the qw comes in handy even when it's just used as a generator of create/drop scripts for ones objects - it's a good way to enforce consistency among the .sql files (and developers) IMO.

I take it you have developed packages of your own (merchant-system?) with the qw. Do you have plans to release such a package so that others may study how it uses the qw? Also, have you noticed any major negative side effects performance or otherwise?

BTW, I've hacked "www/admin/objects/pg-create.tcl" so that it outputs the table creation and I fixed the indentations (using forums as model) and a few other things. I can email you the whole file or a patch if you want, after I've tested it a bit further.

Cheers,

Collapse
Posted by Tom Jackson on
The way I understand it, a package that has been developed by using the query-writer will depend on the query-writer being installed (and bootstrapped with qw-dumps from my package) on any system that wants to run the new package. Is that correct?

Yes, correct. What it should mean is that users install query-writer like a regular package using the package manager. You must have done this. :)

If so, qw-made packages won't run on a basic install of OpenACS as of today. In that case I reckon developers will be reluctant to write packages using qw until/unless it is added as a core OpenACS package. Hmm. I guess Oracle support will be required for this to happen...

Yes, I have been talking with Don about doing this. It apparently needs Oracle support, so query-writer needs to be ported to Oracle. That should be easy for insert/update/delete queries, as the query-writer uses itself in most places, but select queries need porting using .xql files. However, there are packages in the system that haven't been ported one way or the other, and some that have, have bugs. This is one package that must be ported perfectly, otherwise the benefits of using it go way down.

I don't think it should be a core package any time soon, just available from cvs so it is easy to develop to completion. When, or if it goes in to cvs isn't my decision.

Nonetheless, I think the qw comes in handy even when it's just used as a generator of create/drop scripts for ones objects - it's a good way to enforce consistency among the .sql files (and developers) IMO. I take it you have developed packages of your own (merchant-system?) with the qw. Do you have plans to release such a package so that others may study how it uses the qw? Also, have you noticed any major negative side effects performance or otherwise?

If you need a copy of merchant-system, I can give you a link. It works for my client, but needs changes to remove vendor specific info. It is a good place to look to find out how the query-writer can be used. Merchant-system is now pushing 450+ files, without any query processing files, imagine if I had .xql files everywhere!

Query-writer is especially helpful if you have a large data model, but it also helps administer access to attributes, and even to the values certain table attributes can be set to by a particular user. The original query-writer was written for an Oracle system, but only the query processing part, it didn't need to know about overloaded functions, default values or attribute order. It also didn't have a tcl api that could be used, only the http post method was supported.

What is left out, and needs to be added in is a set of *_p tcl vars set to 0 or 1. They take the form: object_attribute_value_group_p, or something like that. group being the qw_group, not an OACS group. They are useful in writing adp templates that keep in sync with attribute values that are allowed in a certain situation, so select boxes can only contain values the user can set, or input boxes for attributes can be set to not show up if a user can't access that attribute. Also, I had a confirm page which weeded out vars which didn't change on an update form. This used a similar set of form vars that started with 'cur.' Right now, every var is updated each time. Another thing to consider is the method of updating the database. Every non-null attribute is updated separately, instead of togeather as in a simple dml statement. However, I have not noticed any performance problems with query-writer. Most of the functions are performed on nsv_ arrays, and performance can be increased by adding more nsv buckets.

Before porting to Oracle, I think the query-writer needs to become more feature complete. Otherwise the job will be much greater. However, the port of query-writer will greatly speed porting any package using query-writer. That is the big benefit, all you will have to port will be select statements.

BTW, I've hacked "www/admin/objects/pg-create.tcl" so that it outputs the table creation and I fixed the indentations (using forums as model) and a few other things. I can email you the whole file or a patch if you want, after I've tested it a bit further.

That sounds great! You can send me a copy when you get time.

Collapse
Posted by Ola Hansson on
I don't think it should be a core package any time soon, just available from cvs so it is easy to develop to completion. When, or if it goes in to cvs isn't my decision.

Having it available from cvs somewhere on openacs.org so that interested parties could develop on it would be really cool.

It would be interesting to hear what Don and others think about the general usefulness of query-writer and its chances of getting "blessed". And yes, I would very much like to get a link to the merchant-system. Thanks.