8.5 Object-Oriented SQL


Modern object-oriented features are not just a trendy thing; object orientation can be an important help when implementing huge and sophisticated applications. With the help of object orientation, it is possible to use the same functions and attributes in more than just one object by using inheritance. Inheritance means that a child class can inherit attributes from a parent class. Just like PHP, PostgreSQL supports inheritance and that's what this section covers.

8.5.1 Inheritance and Tables

Tables can easily inherit attributes from each other. In tables, attributes are columns. Take a look at an example:

 phpbook=# CREATE TABLE person (name text, birthday date); CREATE 

The table called person contains two columns. In the next step you can define a table for storing information about students. A student has additional information such as the university the student is attending and the subject matter he is studying. To model these circumstances, inheritance can be used:

 phpbook=# CREATE TABLE student (university text, subject text) INHERITS (person); CREATE 

By adding the keyword INHERITS, you can tell PostgreSQL which parent tables must be used. It is important to point out that a table can inherit from more than just one parent. This differs significantly from PHP where a child can only have one parent. Take a look at the columns of student:

 phpbook=# \d student         Table "student"    Column   | Type | Modifiers ------------+------+-----------  name       | text |  birthday   | date |  university | text |  subject    | text | 

The first two columns are taken from the parent table. Let's see what happens when a column is added to the parent table:

 phpbook=# ALTER TABLE person ADD COLUMN gender char(1); ALTER 

The column has been added to the table successfully:

 phpbook=# \d person            Table "person"   Column  |     Type     | Modifiers ----------+--------------+-----------  name     | text         |  birthday | date         |  gender   | character(1) | 

Because student is a child of person, the column can be found in student as well:

 phpbook=# \d student             Table "student"    Column   |     Type     | Modifiers ------------+--------------+-----------  name       | text         |  birthday   | date         |  university | text         |  subject    | text         |  gender     | character(1) | 

Now that you have seen how to create tables, it is time to see how tables can be removed. Let's try to remove the parent table:

 phpbook=# DROP TABLE person; ERROR:  Relation "student" inherits from "person" 

It is not possible to remove a table that still has children. This seems obvious because otherwise all children of the parent table would lose the entire set of columns that can be found in the parent table. To remove a table, it is necessary to remove the children first.

8.5.2 Inserting and Selecting Data

In this section you will see how to insert and retrieve data from tables that are built on inherited columns. The first thing is to insert some data. The first record will be added to the parent table:

 phpbook=# INSERT INTO person VALUES ('Shelley', '1980/03/14', 'f'); INSERT 25428 1 

The second record will be added to the child table:

 phpbook=# INSERT INTO student VALUES ('Carlos', '1972/12/30', 'Technical University of Vienna', 'French', 'm'); INSERT 25429 1 

To see how inheritance works, you can query the parent table:

 phpbook=# SELECT * FROM person;   name   |  birthday  | gender ---------+------------+--------  Shelley | 1980-03-14 | f  Carlos  | 1972-12-30 | m (2 rows) 

Both records you have inserted have been retrieved. When retrieving data from a parent table, PostgreSQL will display all data stored in the child tables as well. However, the result will only contain the columns of the parent table in this case the columns university and subject won't be returned because they can only be found in the child table. Let's query the child:

 phpbook=# SELECT * FROM student;   name  |  birthday  |           university           | subject | gender --------+------------+--------------------------------+---------+--------  Carlos | 12-30-1972 | Technical University of Vienna | French  | m (1 row) 

Only the record that has been added to the child table has been returned. As you can see, the records in the parent table won't be returned.

Tables that inherit columns from other tables can be treated just like "normal" tables. Just as with any other table, it is possible to perform simple join operations. In the next example you can see how to join the parent table with the child table:

 phpbook=# SELECT person.name FROM student, person WHERE student.name=person.name;   name --------  Carlos (1 row) 

In many cases this is not a useful operation, but this example shows the tremendous power of PostgreSQL and what kind of operations can be done with the database.

8.5.3 Rules and Inheritance

Rules and inheritance are two important features of PostgreSQL. In this section, you will find out what happens when both features are used simultaneously and what you have to take care of.

The goal of the next example is to write a rule that inserts logging information into a table. Let's start and create a table called logtab that consists of two columns (if there is already a table in the database that is called logtab, just delete it):

 phpbook=# CREATE TABLE logtab (tstamp timestamp, action text); CREATE phpbook=# \d logtab                   Table "logtab"  Column |            Type             | Modifiers --------+-----------------------------+-----------  tstamp | timestamp(6) with time zone |  action | text                        | 

In the next step a rule can be defined. The rule is executed every time somebody inserts a record into the table called person:

 CREATE RULE rule_insert_person AS ON INSERT         TO person         DO                 INSERT INTO logtab VALUES (now(), 'inserting ...'); 

person is the parent table of student. What happens if a record is inserted into student? Will the rule be inherited by the child table?

 phpbook=# INSERT INTO student VALUES ('Sheila', '1974/12/12', 'PostgreSQL Academy Vienna', 'PostgreSQL', 'f'); INSERT 25437 1 

Let's query the logging table to see if a record has been added to it:

 phpbook=# SELECT * FROM logtab;  tstamp | action --------+-------- (0 rows) 

The table is still empty, so the rule has not been executed. To insert data into logtab when a record is being added to student, a second rule must be defined:

 CREATE RULE rule_insert_student AS ON INSERT         TO student         DO                 INSERT INTO logtab VALUES (now(), 'inserting into student'); 

With the help of this rule, a record in the logging table will be added automatically every time data is added to student:

 phpbook=# INSERT INTO student VALUES ('John', '1982/1/9', 'PostgreSQL Academy Vienna', 'PostgreSQL', 'm'); INSERT 25442 1 

Take a look at the content of the logging table:

 phpbook=# SELECT * FROM logtab;             tstamp             |         action -------------------------------+------------------------  2001-11-29 14:39:40.784463+01 | inserting into student (1 row) 

PostgreSQL does not provide the opportunity for a child class to inherit triggers. A rule can only be defined on one table and will not be valid for child tables. This is an extremely important subject because you have to take it into consideration when designing applications.

In future versions of PostgreSQL, inheriting rules might be possible.

8.5.4 Changing PostgreSQL's Behavior

In this chapter you have already learned that a query automatically returns all values from the current table as well as from all child tables of the table you want to retrieve data from. Sometimes this behavior won't be what you are looking for. In these cases it is necessary to change the way PostgreSQL deals with inheritance.

To influence the behavior, PostgreSQL provides a variable named sql_inheritance, which can be changed at runtime. To retrieve the current value of sql_inheritance, use the SHOW command:

 phpbook=# SHOW sql_inheritance; NOTICE:  sql_inheritance is on SHOW VARIABLE 

By default inheritance is turned on. To change the value temporarily, use the SET command:

 phpbook=# \h SET Command:     SET Description: change a run-time parameter Syntax: SET variable { TO | = } { value | 'value' | DEFAULT } SET TIME ZONE { 'timezone' | LOCAL | DEFAULT } 

Let's change the way inheritance is treated:

 phpbook=# SET sql_inheritance TO off; SET VARIABLE 

In this example the value is set to off and PostgreSQL will now treat all tables as separate and independent tables. If you query student, only three records will be retrieved:

 phpbook=# SELECT * FROM student;   name  |  birthday  |           university           |  subject   | gender --------+------------+--------------------------------+------------+--------  Carlos | 1972-12-30 | Technical University of Vienna | French     | m  Sheila | 1974-12-12 | PostgreSQL Academy Vienna      | PostgreSQL | f  John   | 1982-01-09 | PostgreSQL Academy Vienna      | PostgreSQL | m (3 rows) 

The behavior of PostgreSQL has not changed when querying the child table, but when data from the parent table is selected, it won't contain information taken from child tables any more:

 phpbook=# SELECT * FROM person;   name   |  birthday  | gender ---------+------------+--------  Shelley | 1980-03-14 | f (1 row) 

Only one record has been retrieved.

To change sql_inheritance back to the default value, you can use SET:

 phpbook=# SET sql_inheritance TO DEFAULT; SET VARIABLE 

8.5.5 Serials and Inheritance

For generating primary keys, many people rely on serials. When dealing with inheritance, serials are dangerous in a way because you have to know perfectly well what PostgreSQL does internally otherwise you will have trouble with the primary key because of duplicated entries.

To show you how things work, we have included an example. Let's create a table for storing the ids and the names of plants:

 phpbook=# CREATE TABLE plant (id serial, name text); NOTICE:  CREATE TABLE will create implicit sequence 'plant_id_seq' for SERIAL column 'plant.id' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'plant_id_key' for table 'plant' CREATE 

The first column has been defined as a serial number, so PostgreSQL has automatically generated a sequence. In the next step a second table can be defined that contains a column called id as well:

 phpbook=# CREATE TABLE flower (id serial, color text) INHERITS (plant); NOTICE:  CREATE TABLE will create implicit sequence 'flower_id_seq' for SERIAL column 'flower.id' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'flower_id_key' for table 'flower' NOTICE:  CREATE TABLE: merging attribute "id" with inherited definition CREATE 

An additional sequence will be generated, and PostgreSQL mentions that the two columns having the same name will be merged into one column. This way PostgreSQL will use the sequence called flower_id_seq instead of the one called plant_id_seq to generate the values inserted into the column. To see what this means, insert one record into every table and see what happens:

 phpbook=# INSERT INTO plant (name) VALUES ('birch'); INSERT 25463 1 phpbook=# INSERT INTO flower (name, color) VALUES ('rose', 'red'); INSERT 25464 1 

After inserting the records, you can retrieve them from the table:

 phpbook=# SELECT * FROM plant;  id | name ----+-------   1 | birch   1 | rose (2 rows) 

As you can see, both records have the same id because every sequence defined by the serials has been accessed exactly once. If you add an additional record to table flower, the id of the new record will be 2:

 phpbook=# INSERT INTO flower (name, color) VALUES ('cactus', 'green'); INSERT 25465 1 phpbook=# SELECT * FROM plant;  id |  name ----+--------   1 | birch   1 | rose   2 | cactus (3 rows) 

Because the ids in the column are not unique, the first column cannot be used as the primary key of the table. To get around the problem, you can define triggers. You'll find more information on triggers in Chapter 9, "Embedded Languages."

In the next example you can see how serials can affect child tables. Two tables will be created, but this time the name of the columns containing the sequence won't be the same:

 phpbook=# CREATE TABLE building (bid serial, owner text); NOTICE:  CREATE TABLE will create implicit sequence 'building_bid_seq' for SERIAL column 'building.bid' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'building_bid_key' for table 'building' CREATE 

The column containing the serial is called bid. In the next step an additional table will be created, which contains a sequence as well. This time the column will be called rid:

 phpbook=# CREATE TABLE residential (rid serial, people int) INHERITS (building); NOTICE:  CREATE TABLE will create implicit sequence 'residential_rid_seq' for SERIAL column 'residential.rid' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'residential_rid_ key' for table 'residential' CREATE 

If you look at the data structure of residential, you will see that the default value of two columns is defined as a sequence. This time the two sequences are independent and the sequence of the parent table will also be used by the child table:

 phpbook=# \ d residential                             Table "residential"  Column |  Type   |                        Modifiers --------+---------+---------------------------------------------------------  bid    | integer | not null default nextval('"building_bid_seq"'::text)  owner  | text    |  rid    | integer | not null default nextval('"residential_rid_seq"'::text)  people | integer | Unique keys: residential_rid_key 

Let's insert some values into the child table and see what happens:

 phpbook=# INSERT INTO residential (owner, people) VALUES ('Epi', 3); INSERT 25482 1 phpbook=# INSERT INTO residential (owner, people) VALUES ('Edward', 1); INSERT 25483 1 

After inserting the data, you can retrieve all values from residential:

 phpbook=# SELECT * FROM residential;  bid | owner  | rid | people -----+--------+-----+--------    1 | Epi    |   1 |      3    2 | Edward |   2 |      1 (2 rows) 

As you see, the sequences have been incremented independently.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net