"Automatic" creation of searchable documents from Oracle DB tables
Hi Kapil, (and fellow Zopatistas) Well, it's been difficult, but I finally feel as if I'm on the verge or understanding this a bit better.. Thank you for taking the time to explain this. I would think, it's a fairly simple thing I'm trying to do. Hehehe.. Famous last words.. huh? So, anyway, I've been staying up till 2 am almost every night trying to absorb as much of this as I can, but I have an (extensive) web development, but not a programmer's background, and I'm very much a Zope newbie, so I need to ask you some more questions.. I hope you don't mind.. You can get a feel for what I am talking about by looking at any of the programs or workshops on the website I manage, www.msri.org. Currently, I'm doing this site by rendering these fields of info through templates into static pages in Frontier, but I'd like to transition to Zope. (Its the old webmaster's dream of the "self-maintaining site" *LOL*) So here's where I'm at so far.. I have built an Oracle 7 database to hold the event info.. Everything fine there, everything works.. now for the fun stuff.. I have several tables in this "event" section of the database, programs, workshops and talks. (there actually is one other table for other kinds of events) Each kind of event is treated somewhat differently in the schema. It's a very simple setup with nothing exotic going on.. Logically, a given workshop is usually tied to a program..and a given talk is usually tied to a parent workshop. I have been successful in setting up the basic web-database interaction. I made the basic insert and query forms that are described in the ZSQL Users Guide, and as I said in my previous posting my next goal is to be able to create separate documents from the data in the individual records that are inserted into the tables.. I'd like to be able to search them along with the other Zope documents on the site, at once, (using a ZCatalog..hopefully) I also need to be able to quickly build several different kinds of automatically-maintained lists from SQL queries that home in on various subsets of the event data. (Ultimately, this system will be expanded to handle "people" data, which will involve some very large lists of people spanning over 20 years, so this system needs to have decent performance with large datasets...But, even then, the result set of a given query will always be quite small..) Starting with (on the insert record side) I have an SQL Method for each event type that describes the permissable arguments, and the code describing the multi-field insert. (I have a sequence and trigger already working on the database side of things that implements a unique ID -the primary key- for each new program, so the ID is not one of the arguments passed through the ZSQL Method..should I change this? - I could do this some other way..) I also have another SQL method for each type of event that does a select * from <event_type> so that I can implement pulldowns in the select form for the child workshops of a program. (This is really nice and is one of the reasons I think Zope will be great for building a highly useable web interface to this database, widgets like this make it easy to keep referential integrity..) Then, one (in the case of the event types without possible "parents") or both of the SQL Methods are called in an DTML Document input form. When the form is submitted a DTML Method is triggered, which right now only shows an acknowledging thank you to the person who submits the information. So, if I understand you correctly, **I should then use that action to create a class that is tied to the primary key of the database table. Is there an example somewhere of this RDBMS table-keyed "automatic" creation of ZClasses or DTML documents?* I think ZClasses would be preferable because there are several kinds of event pages I'd like to generate for each kind of event.. (for example, sometimes we have short-term housing information we want to show, and a few weeks before a given event, as more information becomes available, I'd like to be able to have it added .. For example, as an event time nears, I almost always have to put up schedules of the talks in a given workshop..) Anyway, if you have the time and can think of any handy pointers to documentation or relevant examples, Id appreciate it.. But even if not, you already have given me a good start.. It shouldn't be very hard, but even though I've been putting in a lot of time searching, finding information has been difficult for me.. The closest I could find to what I want to do is the (very) few snippets of information available about "pluggable brains". But there isn't even a HOWTO on this, a question that should be a common question.. If anyone has any pointers for a beginner. I'd be really grateful.. Thank you.. Chris I really appreciate your help.. Chris cbeaumon@msri.org
Some considerations.
1. Duplicating data is bad.
2. Let the RDMBS do as much of the processing as possible.
A possible solution (TMTOWTDI):
create a dtml method 'create event'. its basically an encapsulation to simulate triggers. in this method make your call to the insertion of the data into the sql db. after that call you can create a ZClass or DTML document with the property that stores the primary key of the data record in the DB (if you do it as a dtml doc, you either want to clone, or pass its body, check out the ZQR). this is your basic association of your objects and the sql data. in a dtml method that lives above your objects have a body that basically calls a retrieval sql on the db which takes an arg for the id. The body of your dtml doc/ZClass(index_html) calls this method for presentation of its data passing its property as an arg. you can arrange your objects in whatever heirarchy you want as they will acquire the dtml method and the sql methods for presentation (or put it in the zclass). For deletion create a similiar trigger encapsulation method, delete row, delete object.
caveat. DON't use this to display lots of events on a page (hideous performance). you're much better off going to the db directly and grabbing all the results you need perhaps with python brain for extra presentation info.
Cheers
Kapil
On Sun, 27 Aug 2000, Chris Beaumont wrote:
Then, one (in the case of the event types without possible "parents") or both of the SQL Methods are called in an DTML Document input form. When the form is submitted a DTML Method is triggered, which right now only shows an acknowledging thank you to the person who submits the information.
So, if I understand you correctly, **I should then use that action to create a class that is tied to the primary key of the database table. Is there an example somewhere of this RDBMS table-keyed "automatic" creation of ZClasses or DTML documents?* [...] Anyway, if you have the time and can think of any handy pointers to documentation or relevant examples, Id appreciate it.. But even if not, you already have given me a good start.. It shouldn't be very
I'm still not clear on why you want to instantiate objects instead of just dynamically generating the pages out of the database. But if instantiating ZClasses when the action method is called is what you want to do, check out the HowTo Adding ZClass Instances Programatically: http://www.zope.org/Members/tazzzzz/addZClasses --RDM
Chris Beaumont wrote:
Part 1.1Type: Plain Text (text/plain)
Hi Kapil, (and fellow Zopatistas) Well, it's been difficult, but I finally feel as if I'm on the verge or understanding this a bit better.. Thank you for taking the time to explain this. I would think, it's a fairly simple thing I'm trying to do. Hehehe.. Famous last words.. huh? ######### #Kapil ########### i say it all the time:) before i go on i should probably say that i'm not sure about the benefits of using zclass presentation of sql data in this case. you're trying to enforce a one-to-one object to event ratio but to what benefit, the only one thats readily apparent to me are the urls. The functionality for display could easily be done with a few pages and some sql methods in instead. ######### So, anyway, I've been staying up till 2 am almost every night trying to absorb as much of this as I can, but I have an (extensive) web development, but not a programmer's background, and I'm very much a Zope newbie, so I need to ask you some more questions.. I hope you don't mind.. You can get a feel for what I am talking about by looking at any of the programs or workshops on the website I manage, www.msri.org. Currently, I'm doing this site by rendering these fields of info through templates into static pages in Frontier, but I'd like to transition to Zope. (Its the old webmaster's dream of the "self-maintaining site" *LOL*) So here's where I'm at so far.. I have built an Oracle 7 database to hold the event info.. Everything fine there, everything works.. now for the fun stuff.. I have several tables in this "event" section of the database, programs, workshops and talks. (there actually is one other table for other kinds of events) Each kind of event is treated somewhat differently in the schema. It's a very simple setup with nothing exotic going on.. Logically, a given workshop is usually tied to a program..and a given talk is usually tied to a parent workshop. ############### ################# I have been successful in setting up the basic web-database interaction. I made the basic insert and query forms that are described in the ZSQL Users Guide, and as I said in my previous posting my next goal is to be able to create separate documents from the data in the individual records that are inserted into the tables.. I'd like to be able to search them along with the other Zope documents on the site, at once, (using a ZCatalog..hopefully) ##################### ZCatalog searches of the RDBMS are generally not possible or recommended(size concerns). It might be possible catalog the objects based on an sql method within the zclass that takes no args. Search the mailing lists for more on this. doing this would probably require doing this as a base python product with a zclass inheriting from it, so you can create the sql method with the proper syntax without an arg. Another possibility since you're using oracle is intermedia/context indexing, although i'm not sure if it exists in oracle 7, and its a pain to setup (see technet.oracle.com and www.arsdigita.com/bboard for help). ######################## I also need to be able to quickly build several different kinds of automatically-maintained lists from SQL queries that home in on various subsets of the event data. (Ultimately, this system will be expanded to handle "people" data, which will involve some very large lists of people spanning over 20 years, so this system needs to have decent performance with large datasets...But, even then, the result set of a given query will always be quite small..) ######### if you're trying to change your zope object heirarchy based on these lists you should just do the whole thing in sql which is designed for relations. IMO object dbs are designed for interaction. ######### Starting with (on the insert record side) I have an SQL Method for each event type that describes the permissable arguments, and the code describing the multi-field insert. (I have a sequence and trigger already working on the database side of things that implements a unique ID -the primary key- for each new program, so the ID is not one of the arguments passed through the ZSQL Method..should I change this? - I could do this some other way..) ########################### keep your sequence. in your insert method do an sql delimter and select the id of the inserted record, assign this as either a property or the id of the zclass. the property might be better if you want to have a url which has some semantic sense to it. ########################### I also have another SQL method for each type of event that does a select * from <event_type> so that I can implement pulldowns in the select form for the child workshops of a program. (This is really nice and is one of the reasons I think Zope will be great for building a highly useable web interface to this database, widgets like this make it easy to keep referential integrity..) Then, one (in the case of the event types without possible "parents") or both of the SQL Methods are called in an DTML Document input form. When the form is submitted a DTML Method is triggered, which right now only shows an acknowledging thank you to the person who submits the information. So, if I understand you correctly, **I should then use that action to create a class that is tied to the primary key of the database table. Is there an example somewhere of this RDBMS table-keyed "automatic" creation of ZClasses or DTML documents?* ######################## when the form is submitted to the a dtml method. that method should call the sql method get the result back from it which is the id of the record and create the zclass and change its properties to reflect the proper zclass. there are how tos on programmatic insertion of ZClasses, and ZClass properties ######################### I think ZClasses would be preferable because there are several kinds of event pages I'd like to generate for each kind of event.. (for example, sometimes we have short-term housing information we want to show, and a few weeks before a given event, as more information becomes available, I'd like to be able to have it added .. For example, as an event time nears, I almost always have to put up schedules of the talks in a given workshop..) Anyway, if you have the time and can think of any handy pointers to documentation or relevant examples, Id appreciate it.. But even if not, you already have given me a good start.. It shouldn't be very hard, but even though I've been putting in a lot of time searching, finding information has been difficult for me.. The closest I could find to what I want to do is the (very) few snippets of information available about "pluggable brains". But there isn't even a HOWTO on this, a question that should be a common question.. If anyone has any pointers for a beginner. I'd be really grateful.. ########################### with regards to searching for info. the google search on zope.org is the probably the fastest mechanism to finding the info you want (accessible from the search link on the top of the page) unless you certain of some benefits of doing this with ZClasses wrapping your db records i would suggest reading up on some sql and doing the site with sql. Good luck Kapil ###########################
participants (3)
-
Chris Beaumont -
Kapil Thangavelu -
R. David Murray