Forum OpenACS Development: xotcl object and adp pages

Collapse
Posted by Andrei Mitran on
I am exploring using xotcl to help improve code quality in an application that I am working on.

Instead of using a basic db_1row to fetch data from a database I decided to encapsulate it using an xotcl class. The primary diver was avoiding a bunch of 1rows that potentially have the same columns and end up stepping on each other in a script. This works fine except for a couple of small issues.

1: I couldn't figure out how to get the db_1row to dynamically create the object slots without a "my instvar some_var" in the method. This works but ends up as more code and a source for errors then just using the 1row. I came across -objscope but never could get it to work as I (foolishly?) expected. I failed to find sample code. Any examples anyone could point me to would be appreciated.

2: In the adp file displaying the value of a slot is not elegant. < % set some_var [some_instance set some_instvar] % > @some_var@ I could do it in the tcl file - but they why bother with xotcl - just use db_1row, be careful and use select x as y where there are conflicts.
Suggestions would be appreciated.

3: db_multirow - I could create a collection of instances and be more object oriented and elegant or create an instance with a multirow stuffed in it Insights on these alternatives would be appreciated. Some context - the data comes from multiple databases (one is even a sql server database - ugh). Some tables are large - 10's of millions of rows. Fetching without an appropriate "where" in the sql won't work. Most queries involve many joins - some as bad as 30 tables. While exploring ideas, I have cooked up generic table loaders with a few lines of xotcl code - dynamic OO is taking a bit of effort to comprehend - but I was pleasantly surprised at how much can be accomplished with so little code when you can add instance variables dynamically.

Collapse
Posted by Gustaf Neumann on
Dear Andrei,

Are you aware of the interface between xotcl and the acs object system?

You might check out the following links:

Some answer to your questions:

  1. How to fetch sql content into a single object:

    xotcl-core defines already for all objects a method db_1row. By using this method, the variables returned by the sql query are set a instance variables in the used object.

    Example (try it e.g. in the developer support in the ds/shell):

       Object o
       o db_1row . {select * from acs_objects where object_id = 0}
       o set title
    
  2. How to output variables easy in adp-files:

    Well, adp is defined to work within a single namespace. So it has no predefined support for xotcl instance variables. However, it is not very complicated to extend the adp-compiler to recognize it. This way, one could resolve instance variables in ADP similarly to array variables with the syntax @table.entry@ e.g. with the new syntax @object->instance_variable@. But that is not standard. For now, you have to export the variables to the appropriate scope. For the example above for a single variable one can use

       o instvar title
    
    or for all instvars of the object
       eval o instvar [o info vars]
    
  3. How to fetch multiple tuples into multiple objects:

    xotcl-core defines instantiate_objects, which accepts an arbitrary SQL query and returns an ordered composite with the results OrderedComposite. Example (try it e.g. in the developer support in the ds/shell):

       # fetch results into an ordered composite
       set s [::xo::db::Object instantiate_objects -sql {select * from acs_objects limit 10}]
    
       # iterate over the ordered composite
       foreach x [$s children] { append _ [$x set object_id] \n}
    
       # show result
       set _
    
Hope this helps,

Best regards
-gustaf neumann

Collapse
Posted by Andrei Mitran on
Gustaf,

1: Many thanks for responding. Yes I am aware of how xotcl db_1row works for acs_objects. It is very nice and thanks for all the great work that you have put into it! It works great for tables I have control over. Unfortunately the application(s) that I am working on has nearly 2,000 tables across 5 databases. None of which are or can be acs_objects. Some of these tables are large 10's of millions of rows and I need sub second response for the queries. Raw sql performance is mostly good. As an old guy I continue to be amazed at how good most of the major relation database systems work if you have a decent design with good hardware (In my case 10's of GB of RAM, lots of processors, many independent spindles for data, indexes, txn-logs with large numbers of raided drives for each etc.) and you use indexes wisely.

I ended up using the information schema (data dictionaries) to figure out the columns and used a loop to create the instance variables. Not ideal – it would have been nicer to create the instance vars automatically from the select list of the query. Where there was a large number of columns (200+ and some of the columns are big 5k varchars) I resorted to using the select list itself or hand coding the instvar creation for specific problem queries. I was thinking of writing my own version of db_1row and db_0or1row called db_xo_1row to deal with this better.

2: Yes – extending the adp compiler is probably the best way to go. I am mostly using json/jquery/ext3 these days so I just have the class spitting out the appropriate json – avoiding the problem. If I can get a break from my day job - I might extend the adp-compiler and contribute it back if anyone else was interested – it would be handy for retrofitting some older code.

In general, using xotcl is helping to structure the code better and it is reducing the amount of code and queries needed.

I have another question: If I don't remember to destroy the instances created for a page it is not clear to me if I will have memory leaks.

Thanks for the help,

Andrei

Collapse
Posted by Gustaf Neumann on
Dear Andrei,

The method db_1row defined by xotcl-core works for all SQL queries, not only for queries on acs-objects. For example, acs_attributes are not acs objects.

   Object o -destroy_on_cleanup
   o db_1row . {select * from acs_attributes limit 1}
   o info vars
The same way you can use the tables of your own data model, use arbitrary SQL queries with joins, etc.. The same is also true for instantiate_objects. For both methods the instance variables are created as needed by the results of the SQL queries.

If you want to create XOTcl classes from the data dictionary - which might be useful or not - it requires more work and more detailed knowledge about the available schema definition. xotcl-core is able to automatically derive XOTcl classes from the schema definition of the OpenACS meta data.

If i can be of any help for the adp integration, or if the are some results that you can share, let me know.

Concerning object/class creation/deletion: Certain objects/class should persist in memory after a connection thread, some of these not. The objects/classes defined in the -procs.tcl files should certainly persist.

The situation with XOTcl objects and classes (which are technically Tcl commands) is pretty much the same as for other Tcl commands (e.g. the one generated by tdom). In general, temporary objects/classes can be destroyed using "manually" with the destroy method, or - the recommended approach - with the method destroy_on_cleanup. Most of the object creating commands in the xotcl-core SQL interface use destroy_on_cleanup. This is described in the API definition.

In the example above, the objects created by instantiate_objects are automatically cleaned up. Object o from this posting is automatically cleaned up, while the version of the first posting is not.