6. Προχωρημένες λειτουργίες

Η PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) είναι μία διαδικαστική γλώσσα που υποστηρίζεται από το λογισμικό σύστημα διαχείρισης βάσεων δεδομένων PostgreSQL. Η PL/pgSQL είναι η μόνη διαδικαστική γλώσσα εγκατεστημένη εξορισμού στο λογισμικό PostgreSQL αντίθετα με άλλες διαδικαστικές γλώσσες που είναι συμβατές μαζί του. Τέτοιες είναι οι: PL/Java, PL/Perl, plPHP, PL/Python, PL/R, PL/Ruby, PL/sh, και PL/Tcl.

Η διαδικαστική γλώσσα PL/pgSQL μοιάζει αρκετά με την αντίστοιχη γλώσσα PL/SQL που έχει αναπτυχθεί για το διαδεδομένο εμπορικό λογισμικό σύστημα διαχείρισης βάσεων δεδομένων Oracle. Σύμφωνα με τους δημιουργούς της η PL/pgSQL δημιουργήθηκε με σκοπό: Να επιτρέπει την δημιουργία συναρτήσεων και σκανδαλιστών, Να προσθέσει δομές ελέγχου στην γλώσσα SQL Να επιτρέπει πολύπλοκους υπολογισμούς Να επιτρέπει την κληρονομικότητα ορισμένων από τον χρήστη συναρτήσεων, τύπων και τελεστών Να μπορεί να οριστεί έτσι ώστε να διαχειρίζεται κάποιον δικτυακό εξυπηρετητή Να είναι εύχρηστη

Οι συναρτήσεις που δημιουργούνται με την γλώσσα PL/pgSQL μπορούν να χρησιμοποιηθούν οπουδήποτε υπάρχει περιθώριο για ενσωματωμένες συναρτήσεις (built-in functions). Για παράδειγμα είναι δυνατή η δημιουργία σύνθετων υπολογιστικών συναρτήσεων υπό όρους (conditional computation functions) που αργότερα θα χρησιμοποιηθούν για τον ορισμό τελεστών (operators) ή σε ευρετήρια (index expressions).

Η PL/pgSQL είναι μία πλήρης γλώσσα προγραμματισμού, επιτρέπει πολύ περισσότερο διαδικαστικό έλεγχο από την γλώσσα SQL, και περιλαμβάνει την δυνατότητα για χρήση βρόχων και άλλων δομών ελέγχου. Οι λειτουργίες που δημιουργούνται στην γλώσσα PL/pgSQL μπορούν να κληθούν μέσω εντολών SQL ή να ενεργοποιηθούν μέσω σκανδαλιστών.

Η γλώσσα PL/pgSQL επιτρέπει την ομαδοποίηση υπολογισμών και ερωτημάτων στον εξυπηρετητή και ως εκ τούτου παρέχει τις δυνατότητες μίας διαδικαστικής γλώσσας με την ευκολία της SQL αλλά με δυνατότητα ελαττωμένης επιβάρυνσης διαδικτυακής επικοινωνίας μεταξύ χρήστη και εξυπηρετητή (client/server communication overhead). Συνολικά: Επιπλέον ερωτήσεις και αποκρίσεις μεταξύ χρήστη και εξυπηρετητή ελαχιστοποιούνται Ενδιάμεσα αποτελέσματα που ο χρήστης δεν χρειάζεται καθαυτά αλλά έχουν σημασία για επόμενα ερωτήματα δεν χρειάζεται να επεξεργαστούν από τον χρήστη και να μεταφερθούν ανάμεσα σε αυτόν και τον εξυπηρετητή Πολλαπλοί κύκλοι επεξεργασίας και υποβολής ερωτημάτων μπορούν να αποφευχθούν Τα παραπάνω συμβάλλουν στην δραματική βελτίωση της υπολογιστικής απόδοσης και ταχύτητας σε σχέση με ένα σύστημα που δεν αποθηκεύει συναρτήσεις (functions). Επιπλέον η γλώσσα PL/pgSQL μπορεί να συμπεριλάβει όλους τους τύπους δεδομένων, τελεστές και λειτουργίες της γλώσσας SQL.

Στην συνέχεια του οδηγού αξιοποιούνται τα στοιχεία ελέγχου που προσφέρει η PL/pgSQL όπως οι σκανδαλιστές και οι συναρτήσεις. Έτσι συντάσσονται στοιχεία ελέγχου που θα αποθηκευτούν στο σύστημα διαχείρισης βάσεων δεδομένων και θα κληθούν από τον διαχειριστή της βάσης δεδομένων ή να εκτελούνται από σκανδαλιστές που παρακολουθούν βασικές λειτουργίες του συστήματος διαχείρισης της χωρικής βάσης δεδομένων.

Προκειμένου να κάνουμε ανάγλυφο τον τρόπο λειτουργίας των στοιχείων ελέγχου που δομούνται με την διαδικαστική γλώσσα PL/pgSQL θα ξεκινήσουμε με ένα παράδειγμα. Θα υποθέσουμε ότι το σύστημα που δημιουργήσαμε μέχρι τώρα χρησιμοποιείται από την υπηρεσία που μεταφέρει στο νοσοκομείο τα θύματα ατυχημάτων (πχ ΕΚΑΒ). Σε αυτό το παράδειγμα ο υπάλληλος που δέχεται τα τηλεφωνήματα εισάγει την τοποθεσία των ατυχημάτων στο σύστημα και επικοινωνεί με το πλήρωμα του ασθενοφόρου που βρίσκεται σταθμευμένο στην πλησιέστερη θέση. Στην πραγματικότητα τα ασθενοφόρα βρίσκονται σταθμευμένα σε διάφορα σημεία στην πόλη. Εδώ ας υποθέσουμε ότι τα ασθενοφόρα βρίσκονται σταθμευμένα στα νοσοκομεία. Συνεπώς ο υπάλληλος που δέχεται τα τηλεφωνήματα πρέπει να γνωρίζει άμεσα ποιο είναι το πλησιέστερο νοσοκομείο σε κάθε περιστατικό. Στο παράδειγμα που ακολουθεί θα αναπτύξουμε έναν μηχανισμό που θα παρέχει στον υπάλληλο αυτή την πληροφορία. Επιπλέον ο υπάλληλος θα πρέπει να γνωρίζει πόσο μακρυά από το σημείο στάθμευσης του ασθενοφόρου είναι το συμβάν. Ο σκανδαλιστής και η συνάρτηση σκανδαλιστή που θα δημιουργήσουμε εδώ θα ενημερώνουν αυτόματα κάθε εγγραφή ενός πίνακα που αφορά σε περιστατικά για το πλησιέστερο (σε κάθε περιστατικό) νοσοκομείο και την απόσταση από αυτό.

Προκειμένου να εισάγουμε την έννοια των περιστατικών στο σύστημα μας θα δημιουργήσουμε έναν πίνακα. Τον πίνακα αυτό θα ονομάσουμε incidents. Σε αυτό τον πίνακα θα εισάγουμε τα ακόλουθα στοιχεία: α/α περιστατικού χρονική στιγμή που το περιστατικό εισήχθη στο σύστημα Όνομα πλησιέστερου νοσοκομείου απόσταση από το πλησιέστερο νοσοκομείο θέση περιστατικού

Θα δημιουργήσουμε έναν νέο πίνακα με αυτά τα γνωρίσματα με την ακόλουθη εντολή:

CREATE TABLE health_services.incidents

(gid Integer PRIMARY KEY,
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
near_hosp_name character varying(200),
dist NUMERIC);

SELECT AddGeometryColumn(’health_services’,’incidents’,’the_geom’,’4121’, ‘POINT’,2);

Το σύστημα θα λαμβάνει πληροφορίες για νέα συμβάντα μέσω ενός συστήματος πελάτη (λχ μίας φόρμας) με εντολές SQL όπως η ακόλουθη:

INSERT INTO health_services.incidents VALUES (Α/Α,CURRENT_TIMESTAMP, 9999, 9999, GeomFromText(’POINT( Χ Υ )’,4121));

Ο υπάλληλος αρχικά θα γνωρίζει μόνο την τοποθεσία όπου πρέπει να πάει το ασθενοφόρο. Η τιμή της χρονικής στιγμής που εισάγεται η εγγραφή στο σύστημα συμπληρώνεται αυτόματα από την βάση δεδομένων με την χρήση της λειτουργίας CURRENT_TIMESTAMP. Οι τιμές του πλησιέστερου νοσοκομείου και της απόστασης από αυτό θα συμπληρώνονται με 9999 (που υποδεικνύει ότι η τιμή του συγκεκριμένου πεδίου είναι άγνωστη στον χρήστη) και θα συμπληρώνονται αυτόματα από την βάση δεδομένων με την συνάρτηση που θα φτιάξουμε στην συνέχεια.

Η συνάρτηση που θα φτιάξουμε θα λειτουργεί με τα εξής απλά βήματα:

Αρχικά η συνάρτηση θα πρέπει να διαβάζει τον πίνακα περιστατικών και να απομονώνει το περιστατικό για το οποίο πρέπει να γίνει ο υπολογισμός. Επειδή η συνάρτηση θα ενεργοποιείται κάθε φορά που εισάγεται νέο στοιχείο στον πίνακα των περιστατικών (αυτό θα εξηγηθεί στην επόμενη παράγραφο) τότε το περιστατικό για το οποίο θα γίνεται ο υπολογισμός θα είναι το πλέον πρόσφατο περιστατικό. Το περιστατικό θα ταυτοποιείται με τον αύξοντα αριθμό του. Συνολικά λοιπόν σε αυτό το βήμα η συνάρτηση θα βρίσκει τον αύξοντα αριθμό του πιο πρόσφατου περιστατικού και θα τον κρατά στην μνήμη.

Αυτό το βήμα μπορεί να υλοποιηθεί με το εξής απλό ερώτημα SQL:

LATEST_INCIDENT_ID = (SELECT gid

			FROM health_services.incidents
			ORDER BY time desc
			limit 1);   

Στο επόμενο βήμα ή συνάρτηση θα υπολογίζει ποιο είναι το πλέον κοντινό νοσοκομείο στην θέση του περιστατικού που προσδιορίστηκε στο παραπάνω βήμα. Αυτό θα γίνει με χρήση του τελεστή DISTANCE που περιγράψαμε νωρίτερα:

NEAREST_HOSP = (SELECT hospital FROM

		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1); 

Στην συνέχεια με την χρήση του ίδιου ερωτήματος θα απομονώνεται η απόσταση από το πλησιέστερο νοσοκομείο:

HOSP_DIST = (SELECT DISTANCE

		FROM 
		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1);

Αφού η συνάρτηση έχει προσδιορίσει αυτές τις παραμέτρους τότε μπορεί να εισάγει τις τιμές τους στα ανάλογα πεδία της πιο πρόσφατης εγγραφής. Αυτό γίνεται επίσης με εντολές SQL:

UPDATE health_services.incidents SET near_hosp_name = NEAREST_HOSP, dist = HOSP_DIST WHERE GID = LATEST_INCIDENT_ID;

Αν όλα αυτά τα βήματα τα συνθέσουμε σε μία εντολή δημιουργίας συνάρτησης σε γλώσσα PL/pgSQL τότε αυτή θα μοιάζει ώς εξής:

CREATE OR REPLACE FUNCTION FIND_NEAREST_HOSPITAL() RETURNS trigger AS '

DECLARE LATEST_INCIDENT_ID INTEGER; NEAREST_HOSP character varying(200); HOSP_DIST NUMERIC;

BEGIN

LATEST_INCIDENT_ID = (SELECT gid

		FROM health_services.incidents
		ORDER BY time desc
		limit 1);

NEAREST_HOSP = (SELECT hospital FROM

		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1);

HOSP_DIST = (SELECT DISTANCE

		FROM 
		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1);

UPDATE health_services.incidents SET near_hosp_name = NEAREST_HOSP, dist = HOSP_DIST WHERE GID = LATEST_INCIDENT_ID;

RETURN new; END ‘Language plpgsql;

Προκειμένου να δημιουργήσουμε αυτή την συνάρτηση εκτελούμε το παραπάνω κομμάτι κώδικά στο παράθυρο ερωτημάτων του pgAdminIII. Αν η δημιουργία της συνάρτησης λειτουργήσει όπως πρέπει τότε λαμβάνουμε το παρακάτω μήνυμα:

“Query returned successfully with no result in ... ms”

Πέρα από τα ερωτήματα SQL που διατυπώσαμε νωρίτερα έχουμε εισάγει και κάποια επιπλέον στοιχεία στον κώδικά μας:

“CREATE OR REPLACE FUNCTION FIND_NEAREST_HOSPITAL() RETURNS trigger AS ' ”

Ξεκινούμε την δημιουργία της συνάρτησης, δηλώνουμε το όνομα της, προσδιορίζουμε ότι θα χρησιμοποιηθεί μαζί με σκανδαλιστή (βλ. Επόμενη παράγραφο) και ξεκινάμε το τμήμα κώδικα που προσδιορίζει την συνάρτηση.

Αρχικά, δηλώνουμε τρεις παραμέτρους που θα χρησιμοποιεί η συνάρτηση.

DECLARE LATEST_INCIDENT_ID INTEGER; NEAREST_HOSP character varying(200); HOSP_DIST NUMERIC;

Μόλις εκτελεσθεί η συνάρτηση οι τρεις παράμετροι παίρνουν τιμές.

BEGIN

LATEST_INCIDENT_ID = (SELECT gid

			FROM health_services.incidents
			ORDER BY time desc
			limit 1);

NEAREST_HOSP = (SELECT hospital FROM

		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1);

HOSP_DIST = (SELECT DISTANCE

		FROM 
		(SELECT name AS Hospital,  DISTANCE(H.the_geom, I.the_geom) AS distance
		FROM health_services.hospitals H, health_services.incidents I
		WHERE I.gid = LATEST_INCIDENT_ID
		ORDER BY DISTANCE asc) AS NEARBY_HOSPITALS
		limit 1);

Η παράμετρος LATEST_INCIDENT_ID παίρνει την τιμή του πλέον πρόσφατου περιστατικού Η παράμετρος NEAREST_HOSP παίρνει το όνομα του κοντινότερου νοσοκομείου προς το πλέον πρόσφατο συμβάν. Η παράμετρος HOSP_DIST παίρνει την τιμή της απόστασης του πιο πρόσφατου συμβάντος από το κοντινότερο νοσοκομείο.

Η πρώτη από αυτές τις παραμέτρους (LATEST_INCIDENT_ID) χρησιμοποιείται προκειμένου να προσδιοριστούν οι δύο επόμενες (NEAREST_HOSP, HOSP_DIST).

UPDATE health_services.incidents SET near_hosp_name = NEAREST_HOSP, dist = HOSP_DIST WHERE GID = LATEST_INCIDENT_ID;

Οι παράμετροι NEAREST_HOSP και HOSP_DIST εισάγονται στην εγγραφή του πιο πρόσφατου συμβάντος.

RETURN new; END ‘Language plpgsql;

Οι συναρτήσεις στην γλώσσα PL/pgSQL πρέπει να επιστρέφουν μία τιμή που να είναι συμβατή με τον τύπο της συνάρτησης όπως αυτός ορίστηκε στην εντολή CREATE FUNCTION στην αρχή της συνάρτησης. Η new είναι μία τέτοια μεταβλητή συστήματος (βλ. http://www.postgresql.org/ons/8.3/interactive/plpgsql-trigger.html ). Στην συνέχεια ολοκληρώνεται η σειρά ενεργειών της συνάρτησης και δηλώνεται ότι το κομμάτι κώδικα που προηγήθηκε έχει γραφτεί σε γλώσσα Pl/pgSQL.

Στην προηγούμενη παράγραφο δημιουργήσαμε μία συνάρτηση που μπορεί να εκτελεσθεί με έναν σκανδαλιστή. Η υλοποίηση της συνάρτησης έχει ολοκληρωθεί αλλά ο σκανδαλιστής καθαυτός δεν έχει υλοποιηθεί ακόμα. Αυτό που θα κάνει ο σκανδαλιστής είναι να εκτελεί την συνάρτηση μετά από την ικανοποίηση κάποιας ορισμένης από τον χρήστη συνθήκης. Στην περίπτωση μας ο σκανδαλιστής θα ανιχνεύει την εκτέλεση της λειτουργίας INSERT στον πίνακα incidents και θα εκτελεί την συνάρτηση που ορίσαμε.

Ο σκανδαλιστής υλοποιείται με την ακόλουθη εντολή:

CREATE TRIGGER FIND_NEAREST_HOSPITAL AFTER INSERT ON health_services.incidents FOR EACH ROW EXECUTE PROCEDURE FIND_NEAREST_HOSPITAL();

Εκτελούμε την παραπάνω εντολή στο παράθυρο ερωτημάτων του pgAdminIII, όπως κάναμε νωρίτερα με την εντολή δημιουργίας της συνάρτησης.

Στην συνέχεια δοκιμάζουμε την λειτουργία του σκανδαλιστή και της συνάρτησης:

Με τις ακόλουθες εντολές SQL εισάγουμε τρία συμβάντα στον πίνακα incidents. Είναι σημαντικό τα συμβάντα να καταχωρηθούν ένα ένα και όχι όλα μαζί.

INSERT INTO health_services.INCIDENTS VALUES (001,CURRENT_TIMESTAMP, 9999, 9999, GeomFromText(’POINT( 19257758.86538117 4931260.15004108)’,4121));

INSERT INTO health_services.INCIDENTS VALUES (002,CURRENT_TIMESTAMP, 9999, 9999, GeomFromText(’POINT( 19256050.61425355 4924903.86677552)’,4121));

INSERT INTO health_services.INCIDENTS VALUES (003,CURRENT_TIMESTAMP, 9999, 9999, GeomFromText(’POINT( 19249019.42500000 4935754.75000000)’,4121));

Τα συμβάντα έχουν εισαχθεί. Τώρα μπορούμε να ελέγξουμε τα περιεχόμενα του πίνακα incidents. Ας το κάνουμε μέσω του QuantumGIS.

Πράγματι, στον πίνακα του θεματικού επιπέδου των περιστατικών (incidents) έχουν προστεθεί πληροφορίες πέραν αυτών που εισάγαμε με τις παραπάνω εντολές INSERT. Συγκεκριμένα, σε κάθε εγγραφή έχει εισαχθεί το όνομα του πλησιέστερου νοσοκομείου και η απόσταση από αυτό (στήλες “near_hosp_name” και “dist”).