Databases and Zope
It's obvious how we can use ZSQL methods and DTML to perform the same query each time. But if we want to modify our basic query each time it is run, we will need to define one or more arguments.
For example, if we want to retrieve information about someone based on their last name (or a portion thereof, using SQL regular expressions), we will want to define the following sort of ZSQL method:
SELECT first_name, last_name, phone_number, fax_number, cell_number FROM AddressBook WHERE last_name LIKE ORDER BY last_name, first_name
In DTML, we can replace the XXXXXX with the <dtml-sqlvar> tag, which automatically handles quoting for us. We must name the SQL variable that we are using, as well as indicate its type:
SELECT first_name, last_name, phone_number, fax_number, cell_number FROM AddressBook WHERE last_name LIKE <dtml-sqlvar name_sqlregexp type="string"> ORDER BY last_name, first_nameIn order for the above ZSQL method to work, we must name an argument (name_sqlregexp) in the appropriate text box when creating our method. Zope will take the value of that variable, place it inside of our query and retrieve the results.
We can put even more of the burden on Zope if we use a <dtml-sqltest> tag, which operates similarly to <dtml-sqlvar>:
SELECT first_name, last_name, phone_number, fax_number, cell_number FROM AddressBook WHERE <dtml-sqltest name_sqlregexp op="like" type="string"> ORDER BY last_name, first_name
If we have stored the above query in a ZSQL method named select_by_last_name, then Zope can automatically produce skeleton DTML documents that allow users to enter search terms and see results. To do this, simply choose the “Z Search Interface” product from the “add product” list. You will be able to choose from all of the searchable objects on the system, including the ZSQL method that we just created (select_by_last_name). Choose this, and give the report an ID (I used search_by_last_name). Also give a name to the “input ID”, which is a fancy term for the HTML form that will be used to send input to search_by_last_name. (I named it search_by_last_name_form.) In modern versions of Zope, you also must indicate whether you want the system to create DTML methods or page templates; we want the former.
Clicking on Add creates two new DTML methods in the current folder, corresponding to the names that you entered in the form. Clicking on the “input ID” URL will present a simple HTML form into which you can enter an SQL regular expression. Clicking on the submit button will send your query to the search_by_last_name DTML method, which will in turn invoke our ZSQL method (select_by_last_name), which will then pass along our query to PostgreSQL. PostgreSQL returns results to select_by_last_name, which returns a result set to search_by_last_name, which then displays them in our web browser.
You can, of course, modify the DTML methods that are created to match the style of your own site. You also can copy the DTML that Zope created automatically into your own DTML pages, using them as examples of how to create your own database queries.
The only major task left is the implementation of an INPUT query, which adds items into the database. Luckily, this is rather easy: we create a ZSQL method that inserts a row into the database. Then we create a DTML document that submits its HTML form elements to another DTML document. This second document invokes <dtml-call> to our ZSQL method. Voilà--our record is inserted into the database.
Listing 3 shows the ZSQL method that we need, which I named insert_address_data. Now we'll create a simple DTML document, which will contain an HTML form (see Listing 4).
Finally, we create the DTML document insert_address that receives input from insert_address_form and passes its arguments along to the ZSQL method insert_address_data:
<dtml-var standard_html_header> <h2><dtml-var title_or_id></h2> <dtml-try> <dtml-call insert_address_data> <dtml-except> <p>Sorry, but the INSERT didn't work.</p> <dtml-else> <p>Successfully inserted! </dtml-try> <dtml-var standard_html_footer>
Users can now insert information into our PostgreSQL table using an HTML form pointed at insert_address_form, and they can retrieve it using search_by_last_name_form. It's rather impressive that we can do so much in so few files—and even more so that we didn't have to touch a text editor once in order to get this to work, but that it could all be done using nothing more than our web browser.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Back to Backups
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- A New Version of Rust Hits the Streets
- Google's Abacus Project: It's All about Trust
- Secure Desktops with Qubes: Introduction
- Seeing Red and Getting Sleep
- Fancy Tricks for Changing Numeric Base
- Secure Desktops with Qubes: Installation
- Working with Command Arguments
- CentOS 6.8 Released
Until recently, IBM’s Power Platform was looked upon as being the system that hosted IBM’s flavor of UNIX and proprietary operating system called IBM i. These servers often are found in medium-size businesses running ERP, CRM and financials for on-premise customers. By enabling the Power platform to run the Linux OS, IBM now has positioned Power to be the platform of choice for those already running Linux that are facing scalability issues, especially customers looking at analytics, big data or cloud computing.
￼Running Linux on IBM’s Power hardware offers some obvious benefits, including improved processing speed and memory bandwidth, inherent security, and simpler deployment and management. But if you look beyond the impressive architecture, you’ll also find an open ecosystem that has given rise to a strong, innovative community, as well as an inventory of system and network management applications that really help leverage the benefits offered by running Linux on Power.Get the Guide