Forum OpenACS Development: A query writing tool.

Collapse
Posted by Tom Jackson on

I haven't even ported one package and I am already annoyed at some of the problems due to pg's lack of named attributes and 16 attribute limit.

So I am working on a helper procedure that will return a properly formatted attribute list, given a subset of attributes.

I have a demonstration, with more information at http://zmbh.com/discussion/query/

I am interested to know if this might be generally useful for OpenACS, or if I should just develop it for my own use.

I think the main use of these procedures would be for the developer who might want to add new pages to OpenACS without constantly refering to the data model.

Collapse
Posted by Don Baccus on
It's an interesting idea.  Steve Woodcock's been working on the code in acs-subsite to instantiate or create packages, which depends on the fact that Oracle has all the information you need about function parameters stuffed into user-accessible tables.  PG doesn't (in part because there are no default values, we've just commented them in by convention).

So we're expanding the metadata datamodel so it will hold the necessary attribute information (the package instantiation code creates a call to the package's "new" routine so needs to know which parameters to provide, their order, etc).

How would your idea work with the query dispatcher, though?  I guess people could put the call right in the query file but that's a bit odd ...

Collapse
Posted by Tom Jackson on

I was thinking it might be useful, either as a one-time-use generator, or in the .xql file. The .xql is what gets edited anyway if a package is converted from Oracle. If a tcl proc was inserted there instead, then package maintainance would be more centralized.

I looked around and noticed that some .xql files already have tcl code togeather with other sql fragments.

One thing I don't quite get with ACS is that there is no real standard for updating data, or checking that an object is of the right type before being deleted, or resetting data to it's default value.

For instance, if you create a user and then change the user's password, you should be able to do that through the same package used to create the user. Instead, you have to know what table the password is in and update it there.

Collapse
Posted by Tom Jackson on

I thought I had mentioned my Package Builder here, but I guess I haven't. You enter your data model and it writes the pl/sql and now pl/pgsql for a basic package. The pg part isn't quite done, since I stopped to work on the query writer, but it does create the new and delete functions.

Anyone interested can try it out at: http://choicedev.zmbh.com:8300/pkbuilder/ To save everyone the need to setup an account just to look around the test user email is 'test@zmbh.com' and password is 'test'. I added the notes package, which is small, but only took about 5 minutes. Missing is a general function builder, but it should be easy enough to put in. This same data could be used to write the qd_add_package and qd_add_function calls.

Collapse
Posted by Don Baccus on
Steve Woodcock's on holiday for five days, but when he returns you should definitely coordinate with him.  Your package builder, for instance, can build up all of the metadata needed by his package instantiation code.  He's not quite done with his changes, but you can look at what he's done thus far.  Also a "package creator" exists in ACS 4, he's just beginning to look at it.  I haven't, yet, maybe I  will find some time later today (though I've got some APM and site map loose-ends to take care of first).

Another thing I've noticed with packages is that various programmers were lazy and didn't bother to create all of the metadata that tracks  attributes and the like.  Some packages are complete in this regard, but most aren't.  A package builder could be a big help here, removing  a bunch of the tedium involved with the task.

Collapse
Posted by Don Baccus on
Also, a couple of days ago I received an incomplete package for ACS 4.x labelled "rapid-development", done by aD folks.  I've not looked at it yet, hope to later this week.  I may just shove it into the CVS tree.  You should probably take a look at it to see if there's overlap with your package builder.  If it's complementary perhaps you could stitch together some of their ideas and yours.
Collapse
Posted by Jon Griffin on
Version .2 is broken. Someone tried to fix it :(

.1 works but has a couple of bugs.

All in all usable but not great.

Collapse
Posted by Tom Jackson on

I will look at these other tools. If the rapid-development package is available, can you mail it to me? Or drop a note when it shows up in cvs. My Package Builder was started as a way to generate the package-name-create.sql file. Usually this file contains the data model, acs_object creation and acs_attribute creation code, and the oracle/pg package code. Each user of the Package Builder can create an 'ACS Package'. This is the container for metadata, acs objects, acs attributes and pl functions. What I need to add it the pl Function Builder.

The Function Builder should work using the already entered metadata, from multiple tables, but allow the user to specify the order of the parameters, add/remove declared variables, add to/edit the fuction body. I'm not sure if all of this is easy/possible to integrate into one delivered file. It might be easier to have it create parts and pieces that would be assembled into the final file by hand.

Collapse
Posted by Tom Jackson on

Okay, the aD 'rapid-development' package is located at http://www.arsdigita.com/acs-repository/download-verify?version_id=2104. I have compared it to my Package Builder. Here is a list:

What          rd             PB
Input         metadata       metadata
Functions     new delete     new delete object_p set_attrs reset_attrs
Column order  alphabetic     user set
Attributes    direct supp    not direct 
Table Write   yes!!          not yet
Constraints   yes!!          not complete
DB support    Oracle         Oracle pg
General Funct No             Planned

In short, the aD package looks like it does a complete, but smaller job. You have less control with the aD tool, for some reason columns are ordered alphabetically. It looks like it has good support for writing the tables and constraints. This is exactly what my tool lacks! Also it has built in support for writing attributes. My tool, requires you to enter the few vars needed, but that is easy to fix.

I don't really understand the supertype code. Anyone?

Collapse
Posted by Jon Griffin on
Did you even get the AD version working?

It broke at version .2 for me on multiple servers and clean/upgrade installs.

Collapse
Posted by Don Baccus on
Cool, it looks like there's a lot of overlap but also some complementary stuff, too (as in my comment about perhaps being able to stitch together some of the ideas from their work into your framework).
Collapse
Posted by Tom Jackson on

Jon, no, I never looked at it until today. The extra procedures and functions that I added make my package more useful to me. Neither of these packages allow using more than one table as the basis of a package/function. I need to add this quickly. If the constraint code works, I will be happy to use it. My Package Builder has that stubbed in, but I never found the time to do it.

Don, I will try to use as much of the rapid-development package as possible.

Collapse
Posted by Jon Griffin on
The constraint code works. The package broke in other ways.
Collapse
Posted by Tom Jackson on

I have redone the query writing proc. Now you can call a function by setting the querytext (in the .xql file) to:

   select [qd_write_query_select pb_acs_pkg__new 
     package_name => :package_name 
     owner_id => :user_id 
     context_id => :user_id ]

In this case the qd_write_query_select procedure returns:

pb_acs_pkg__new(null,
:package_name,
null,
null,
:user_id,
'pb_acs_pkg',
now(),
null,
null,
:user_id);  

Any comments would be helpful. The select beginning the querytext seems to be necessary to get the query dispatcher to work correctly.

Collapse
Posted by Don Baccus on
That's pretty cool, Tom ... are you calling a routine to register the information needed first or is it being pulled out of the attributes metadata that is used by the code that Steve wrote to generate "new()"
calls?
Collapse
Posted by Tom Jackson on

What I am using now is an nsv array that is stuffed with the following procs (which are auto generated as comments in the pgplsql data model):

qd_add_package pb_acs_pkg__new package_id package_name title description owner_id object_type creation_date creation_user creation_ip context_id
qd_add_function pb_acs_pkg__new "package_id" "null" "package_name" "" "title" "null" "description" "null" "owner_id" "" "object_type" "'pb_acs_pkg'" "creation_date" "now()" "creation_user" "null" "creation_ip" "null" "context_id" "null" 

To allow for function overloading, you can have as many qd_add_function calls as you want per qd_add_package. The way it works, probably there is a limit of the size of integer allowed in tcl, maybe 32?

qd_add_package assigns a value: 1,2,4,etc. to each attribute. qd_add_function creates a signature which is the total of the attributes. qd_choose_function, which is used by any of the query writing procedures takes the input attributes and calculates their value. Then it looks to see if there is a perfect match to a function, if not, it steps through each added function value and does a bit wise and. The first match is used.

Collapse
Posted by Don Baccus on
OK ... what needs to happen eventually is to have the package loader generate these calls automatically, if folks decide they want this (I like it myself).  That wouldn't be hard to do.
Collapse
Posted by Tom Jackson on

I noticed when I posted the above querytext example, the bboard removed the backslashes that terminated the lines. The backslashes are not that great to have to include. I rewrote the proc so that it can be called (without backslashes) like this:

 select [qd_write_query pb_acs_pkg__new {
     package_name => :package_name 
     owner_id => :user_id 
     context_id => :user_id 
     title => "'This is an example title'" } ]

I also looked in using pg metadata to write the kb_add_function calls. I don't think it will be possible in all cases. There are several reasons. My procedures require default values, which pg doesn't maintain. The defaults in the metadata used in constructing tables do not generally apply to function defaults. One example is the primary key is not nullable, whereas in the new function it is usually null. In the set_attrs function, the primary key is required. Maybe Steve Woodcock has a different way of figuring this out. Where is the stuff he is working on?

Collapse
Posted by Tom Jackson on

I tried to checkout what tables might have metadata suitable for building the function definitions (in acs-subsite). I found a little pgplsql for getting data out of acs_function_args. But I didn't find a table by that name.

To build up the function prototypes for oracle and pg, I created two new packages for the package builder. One is pb_functions, and the other is pb_fn_attrs. Here is the datamodel for the packages:

create table pb_functions (
  function_id integer not null    
    constraint pf_function_id_fk references acs_objects(object_id)
    constraint pf_function_id_pk primary key,
  oracle_pkg_id integer 
    constraint pf_oracle_pkg_id_fk references pb_oracle_pkg,
  function_name varchar(30) not null,
  owner_id  integer 
    constraint pf_owner_id_fk references users(user_id),
  return_type varchar(100) default 'integer' not null,
  return_column integer 
    constraint pf_return_column_fk references pb_columns(column_id)
);
create table pb_fn_attrs (
  fn_attr_id integer not null
   constraint pfa_fn_attr_id_fk references acs_objects(object_id)
   constraint pfa_fn_attr_id_pk primary key,
  function_id integer not null
   constraint pfa_function_id_fk references pb_functions,
  column_id integer 
   constraint pfa_column_id_fk references pb_columns,
  -- Attribute Default Value if null, just enter null, 
  -- otherwise quote chars, but not functions like now()
  default_value varchar(4000),
  -- Attribute type if not from table column
  attr_type varchar(100), 
  -- Order of attribute in input list 
  attr_order integer not null,
  in_out varchar(10) default 'in' not null
);

The immediate use could be in providing a place for function metadata. Second, these packages can be used to group columns from more than one table togeather. To actually write the function, I am proposing that the developer create a tcl/adp page that would take as input the function_id and produce the full package text.

Also, for current function metadata, I wonder if it might be possible to get the data out of Oracle and get it into pg. It couldn't work in all cases, but it might in most.

Collapse
Posted by Don Baccus on
Well, we're not going to force folks to provide Oracle ports of new packages unless they want to (nor PG ports of new Oracle packages etc)  so the sequence isn't necessarily going to be right to do this.

But ... you should really e-mail Steve Woodcock directly, he should be back from holiday now.

Dan's got some hack he used to pull apart PL/SQL packages - that's where all those nice PG functions with default values given as comments come from, and is why so many packages still have the Oracle original embedded as comments.  Steve's original thought was to parse the .sql files when doing package instantiation, but we decided that was too fragile.

But ... ripping up the PL/pgSQL functions to generate the inserts into  metadata tables would be OK.  They could be hand-fixed if necessary.

Of course, at some point we have to ask "which is more work, dealing with positional parameters or doing the work to support a nicer paradigm?"

Collapse
Posted by Marshall Trammell,III on
Should the Ad Rapid Development tool be left as a service or
changed to a package? It generates a lot of code which would be a great help,however, to make it generate the code, I had to move the 'www' directory out to my page root. (I did not know any other way). Is there some other way of running the programs as a service? Because after I change the code in the www directory I will have to move it back to the packages/rapid-development/www directory. Kinda messy & awkward.

It appears to be possible to enhance the program by making it generate a tcl & adp file as well.

Thanks

PS:I have yet to fully test the code that it generates.

Collapse
Posted by Tom Jackson on

You might try my Package Builder at http://choicedev.zmbh.com:8300/, which now generates the pgplsql code for a package. I know the pg pl code works, because I am using it in porting the Package Builder to pg. You can check on the progress of the port at http://oacs4pg.zmbh.com:7777/.

Collapse
Posted by Dave Manginelli on
Tom - what's the status of your Package Builder these days?  I visited the two URLs mentioned in your post, and while I see that the Package Builder is "installed" there, I'm not sure what I can do with it... ?

Don't I need to load it on my own instance of OpenACS to use it?  Is it in CVS yet? Any Docs?

Thanks.

Collapse
Posted by Jeff Putsch on
Where are the qd_* routines defined? If I use the code as generated by the PKbuilder and put the qd_* calls in the files indicated I get errors.

The only place I can find them defined is in the cronjob package. If these routines are to be generally used by non-cronjob packages, shouldn't the qd_* routines be moved into the general query_dispatcher code?

Just a thought...

Collapse
Posted by Marshall Trammell,III on
What is the current status of any query writer tools for acs4?
Collapse
Posted by Tom Jackson on

The current version of the package is at http://multi.zmbh.com:8300/query-writer-0.5.3.apm It has been tested on a large datamodel, but only works with PostgreSQL. There are fewer advantages to using it with Oracle. Currently you can input your datamodel and it will generate pg functions for create/update/delete and object_p. It handles overloaded pg functions, and also works with any table with a single attribute primary key without the pg functions being needed.