Lesson 4. Second step—working with databases

Lesson 4. Second step-working with databases

First of all, you shouldn't be scared of the title, even if you have never dealt with databases (further referred to as DB). You cannot do without them if you want to build a flexible, easy-to-tune-up site. By refusing to work with databases you don't make your life easier but limit yourself, since databases provide many useful opportunities. Trying to build a professional site without DB is like fishing without a fishing-rod: you surely can catch a fish with your own hands, but why complicate your life? In short, if you have never dealt with DB, you'd better start it as soon as possible and use it in all your projects. OK, let's get off this little propaganda and presume you now fully realize the necessity of working with DB.

Working with DB in Parser is easy. Parser has a good system of interacting with various DBMS (Database Management Systems), such as MySQL, Oracle, PgSQL or any ODBC-based DBMS (that is MS SQL, MS Access, etc.). Since Parser is an open-source project, one can add support for any DBMS by creating appropriate driver). To work with DB, you don't have to possess any additional skills in Parser. All you need to do is connect to a DBMS and use SQL queries that this DBMS supports. Parser may only replace apostrophes for a relevant construction (that depends on DB type) as a "fool-proof," while the rest will be transferred as-is.

There is also a special construction used for long string literals. Oracle, PgSQL and, perhaps, some servers, drivers to which may be created in the future, cannot handle long strings properly. If a string input, which is transferred, for example, from form to database, is more than 2000 [Oracle 7.x] or 4000 [Oracle 8.x] characters long, the server will report an error like "literal is too long." If you try to cheat by combining "2000 characters" + "2000 characters" there will be another error like "sum is too great." To store such constructions, we usually use data type CLOB [Oracle] and OID [PgSQL] and, to make SQL commands simplest, we should add a control comment which will be properly interpreted by a driver of SQL server:

insert into news text values (/**text**/'$form:text')

Word text in construction /**text**/ is the name of a column to which we input the string that follows. There must be NO spaces inside it!

Of course, we will not try to cover in one lesson each and every opportunity Parser provides for working with various types of DBMS. We will choose MySQL as the most widely used and, therefore, included as a usual service by most of the hosting providers. Besides, it is free of charge and easy to master.

What are we going to store in our DB? Most obvious answer is news. The table with news must have the following fields: a unique number of a news article in DB (to be generated automatically by DBMS), date indicating when the news was added to DB (this we need to retrieve news related to a certain date), news header and the text (news itself). Such a structure will be simple but effective.

We also need to decide how the news will get into DB. We can use DBMS command line for this purpose, but it is not at all comfortable. If you are going to build a site for an intranet, you can use popular and simple DBMS Microsoft Access. In this case, familiar interface and copy+paste will suit the purpose well and make you a star among your colleagues for many years. We, however, propose a solution for Internet which is to create a section for administration purposes which will include a page with HTML form to input news articles right in your browser.

That's the task which is to be handled. Let's now see how we'll do it. For this lesson, you must have MySQL DBMS installed (without it the whole lot of code will simply not work).

First of all, we should create a DB p3test, containing sole table news with fields (columns) id, date, header and body:

id
int not null auto_increment primary key

date
date

header
varchar(255)

body
text

Now we create administration section, which will allow us to fill this DB with news articles. We will create directory /admin/ and, inside it, file index.html, in which we put the following:

@greeting[]
News DB management

@body_additional[]
Adding news

@body_main[]
$now[^date::now[]]

<center>
<form method="POST">
<p>
Date: <input name="date" value="${now.year}-${now.month}-${now.day}">
     
Header: <input name="header">
</p>
<p>Body:<br />
<textarea cols="50" name="body" rows="5"></textarea>
</p>
<p>
<input type="submit" value="Add New" name="posted">   
<input type="reset" value="Cancel">
</p>
</form>

#start processing
^if(def $form:date && def $form:header && def $form:body){
   ^connect[$connect_string]{
      ^void:sql{insert into news
         (date, header, body)
      values
         ('$form:date', '$form:header', '$form:body')
      }
      …news added
      }
}{
   …cannot add the news&nbsp^;&#151^;  all form fields must be filled
}
</center>

You also need to add method auto before method main in root-directory auto.p. This method is used to initialize global variables, i.e. the variables which will be accessible everywhere on the site. Within this method we will set DB connect string, which we'll return to a bit later:

@auto[]
$connect_string[mysql://root@localhost/p3test]

As you see, the structure of this page is totally compliant with the general structure of all our pages. Elements greeting, body (both parts of it), footer and header are all there. By the way, do you remember how we make header and footer appear on this page? Yes, we call them from within function main located in root auto.p.

We find unfamiliar constructions only in the main part. Let's examine it. In the beginning we see a usual HTML form with current date included as a default value for field date. This we do to make it comfortable for users. However, the line

${now.year}-${now.month}-${now.day}

seems strange. We use curly brackets here to get a string like "2001-11-06" (this is the format we'll use to store dates in DB). If we don't place curly brackets here, Parser will report an error because it will not understand what to do with this code. In such a construction (without curly brackets), i.e.:

$now.year-$now.month-$now.day

a hyphen will be regarded as a part of the name. Remember that you should separate the name of a variable from a character that follows (dot, hyphen, semicolon, any letter or number, etc., except space character). Thus, if you need a hyphen to immediately follow the variable value, you should write:

${variable_name}-

and you will get:

variable_value-

Please, read the page with name-building rules carefully

We would best solve the problem with date by using here construction ^date.sql-string[]. You can try to do it by yourself using Parser language reference. If you still can't cope with it-don't worry, we'll show you how to do it in the next lesson.

Let's go on. If you have already dealt with HTML forms you know that forms send the data filled in by a visitor to some scripts for further processing. In our case the script for processing data will be the page with the form itself. We will need no additional scripts.

After closing tag </form>, we have data processing block. First, with the help of if, we check whether the form fields are not blank. We might do without it, but we want to make something that will not be a mere exhibit-we want our form to work perfectly in real-world conditions. In order to check, we have to get the values of form fields. In Parser, we do it by simply referring to form fields as to static fields:

$form:field_name

The values thus retrieved we will check (whether they are blank or not) with the help of operator def and logical "AND" (&&). We have also performed such a check in Lesson 3, but we didn't use def, as we checked whether a table was empty or not. As you remember, a table has a numerical value, which is the number of its rows, so any non-empty table is considered definite. Here, however, we must use def the same way we do to check any other object. If a field of our form remained empty when submitted, the value of $form:field_name will be considered undefined. Now, that we are sure that all the fields are filled in, we must store them in DB. We do it by first connecting to DB and then sending an SQL query that will put the data into table. Here is how we do it:

^connect[$connect_string]{
   ^void:sql{insert into news
      (date, header, body)
   values
      ('$form:date', '$form:header', '$form:body')
   }
   …news added
}

The most comfortable thing in Parser is that, except in some rare cases, you don't have to learn any constructions to work with DB except those required by DBMS itself. Database session is contained within operator connect which has the syntax:

^connect[protocol://connect string]{methods working with SQL queries}

For MySQL it will look like:

^connect[mysql://username:password@host/data_base]{…}

where curly brackets contain methods working with SQL queries. A query may return some data or nothing (in our case, for example, we just add a new entry to DB and don't request any data). In Parser we use different constructions for these two types of queries. In our case, the query is written like this:

^void:sql{insert into news
      (date, header, body)
   values
      ('$form:date', '$form:header', '$form:body')
}

By the way, this is a static method of class void (remember the semicolon?).

The uncolored part of this construction is SQL commands. Everything is easy here. If you know SQL, you will need nothing else but if you don't, we would again strongly recommend you to study it, as the benefits of using SQL are numerous.

Do appreciate how simply and gracefully Parser interacts with DB! It provides a comprehensible access to DBMS and (except in some rare cases) requires no additional knowledge. As you see, we also can add data from our form to SQL queries using Parser constructions. The opportunities provided by this symbiosis are unlimited. DBMS handles the problems connected with data processing (as it is designed for this very purpose and suits it quite well), and we just use the results. The situation is just the same with any DBMS that you may deal with.

Now we have a form allowing us to add records to our DB. Add several records to it. Now we're going to retrieve them. Before we do it, we need to complete function calendar, which we created in previous lesson. We should place links on dates so that the date could be passed to our script as a form field. Such a link will then direct a user to news archive and retrieve news for the chosen date. Such an enhancement is not a hard task; we'll just have to add some HTML to /news/auto.p. Within operator if we will surround $days.$week_day with the anchor tags like this:

<a href="/news/?day=$days.$week_day">$days.$week_day</a>

As a result, visitors will be able to use our calendar as a menu and select news related to a certain date.

Let's now deal with /news/index.html. We add to it the code:

@greeting[]
News page, Keep up to date!

@body_additional[]
<center>News Archive for Current Month:</center>
<br />
^calendar[]

@body_main[]
$now[^date::now[]]
<b><h1>NEWS</h1></b>
$day(^if(def $form:day){
   $form:day
}{
   $now.day
})
^connect[$connect_string]{
   $news[^table::sql{select
       date, header, body
   from
      news
   where
      date='${now.year}-${now.month}-$day'
   }]
   ^if($news){
      ^news.menu{
         <b>$news.date-$news.header</b><br />
         ^untaint{$news.body}<br />
      }[<br />]
   }{
      Sorry, no news for selected period.
   }
}

The structure is usual. In additional part of body we place calendar by calling ^calendar[] (remember: this function is defined in /news/auto.p). Information part of the page is based on data retrieved from news database and related to the date user selected by clicking on respective link in our calendar (where-part of SQL query). This is a second type of SQL query, which we use to retrieve data. Note that our query will result in table which we'll use further on. We therefore need to create an object of class table.

Let's get to know another constructor of class table, which is based on SQL query. Its logic is similar to that of ^table::load[]. The difference is that the source of data here is not a text file (such as we used to create navigation menu) but SQL query result, i.e. data retrieved from DB:

$variable[^table::sql{SQL query}]

You can use this constructor only within operator ^connect[], that is when you have connection with DB open, because SQL queries processing is handled by DBMS itself. The returned result will be a table, where column names will be the same as the headers returned by SQL server as answer to the query.

A short digression: We recommend that you avoid constructions like select * from ... because an outsider, who doesn't know the structure of the table addressed, will not understand what data will be returned by DB. Such a construction can be used only when you test the script, but in final version, instead of select *, you should always indicate exact names of table's fields which you want to be returned.

The rest of the code must be clear now: if checks whether the form field day (i.e. $form:day-the day user selected from calendar generated by function calendar) is defined (def). We do it to figure out whether the user has already chosen a day from calendar or has just come to news section following a link in navigation menu on some other page. If $form:day is defined we just make it the value of variable day. Otherwise, the value of variable day will be today. Then we connect to DB the same way we did when adding new records, create table news and fill it with the news related to requested day (SQL-query result). After that, we use method menu to go through the table row by row and output the news by referring to the content of its fields. Everything is now clear except one additional operator used for a specific way of outputting the text of the news:

^untaint{$news.body}

Here, you would better put aside the lesson for awhile and read the section on operators taint and untaint to study the work of these operators closely. These are very important operators and you will most probably need to use them quite often. Besides, a great deal of data processing is handled by Parser itself, behind the curtain. This work isn't seen, but it's important that you understand its logic.

Have you read it? Let's go further, then. Why do we need untaint here? We have a form to manage news records and we want to allow using HTML tags in our articles. It is prohibited by default, because some malicious user can put some JavaScript on your page (which could, for example, redirect user's browser to some other page). How will we do it? We will just mark this text as trustworthy by using operator untaint:

^untaint{text of news article}

In our case, as we don't specify the first parameter, the text will be untainted [as-is] (by default). That means the data will be output as it is in DB.

At last we can relax a little: news section is now complete. We can add news and retrieve news related to the date specified by user. Of course, we can improve some little things in our calendar. For example, we can make it leave the days-to-come without links (since we can view only the news for past and present, not for the future), to indicate chosen date in page header, or provide the opportunity to retrieve news of past months (presently, we have only the current month available). This, however, you can do by yourself. The knowledge you got in the previous lessons is quite enough to put these and other ideas, which you may have, into practice. Use your creativity!

Let's sum it up,

What have we done?
We have built administration section to add news articles, enhanced the function responsible for making up a calendar for the current month, filled news section with data retrieved from DB either based on user's date selection or the current date.

What have we learnt?

·	the way Parser interacts with MySQL DBMS;

·	two different ways of sending SQL queries (static method sql of class void and constructor sql of class table);

·	operator untaint.

What should we remember?
To work with DB in Parser is easy and clear, all you need to know is the constructions used by DBMS itself. Don't deprive yourself of using databases in your work.

What's next?
Now, as the news section is complete, we are going to make a guestbook to keep track of our site's rating and see whether the site needs certain enhancements.

Last updated: 21.09.2007

id	int not null auto_increment primary key
date	date
header	varchar(255)
body	text