Saglabātās procedūras

SQLs, protams, ir laba, spēcīga un spējīga datu apstrādes valoda, tomēr reižu reizēm ar to nepietiek. Tiesa gan pirms ķerties pie kaut kā vairāk noteikti ir jāpārliecinās, ka ar SQL vien nepietiek, atceroties, piemēram, tādas lietas kā grupēšanas, apakšvaicājumus, savienojumus, analītiskās funkcijas, model klauzas u.c. iespējas. Sīkāk par to, kādā kārtībā vajadzētu mēģināt risināt problēmas, rakstīju pagājušoreiz.

Tomēr, ja biznesa loģika un realizējamie algoritmi ir pārāk sarežģīti, tad nākas ķerties pie kaut kā sarežģītāka un ar lielākām iespējām un tās ir iebūvētās (reizēm laikam saka arī iegultās vai barbarisms storētās, no angļu stored) procedūras un funkcijas. Lielāka daļa DBVS (tālāk būs viens un tas pats piemērs Oracle, SQL Server un MySQL), kas cenšas attīstīt un piedāvāt lielāku funkcionalitāti, ir izveidojušas vai paņēmušas jau esošu programmēšanas valodu, kurā tad šīs procedūras un funkcijas var veidot. Būtiski ir tas, ka šīs procedurālās vienības tiek glabātas datubāzē. Tā ir pirmā un pati būtiskākā atšķirība, kas tās atšķir no jebkuras citas procedūras vai funkcijas, kas izveidotas un parasti atrodas uz klienta (klienta-servera sistēmā) vai lietojumprogrammu servera (application server) trīs līmeņu sistēmā. Tas faktiski arī nosaka šo procedurālo vienību labās un sliktās īpašības salīdzinājumā ar parastām procedūrām un funkcijām.

Procedūru un funkciju lietošanas mehānisms ir šāds:

1. Vispirms procedūra vai funkcija tiek izveidota, saglabāta un nokompilēta datubāzē. To dara līdzīgi kā ar jebkuru citu datubāzes objektu, izmantojot CREATE PROCEDURE vai CREATE FUNCTION SQL DDL teikumu. Šai brīdī procedūra vai funkcija tiek tikai izveidota, tā vēl netiek pildīta. Šai brīdī parasti DBVS veic arī zināmas pārbaudes, vai procedūra/funkcija ir korekta sintaktiski, vai ir pieejami tajā minētie objekti u.c.
2. Pēc tam, kad procedūra vai funkcija ir veiksmīgi izveidota un bez kļūdām nokompilēta, to var izpildīt (execute). Katrā DBVS to dara mazliet atsķirīgi, bet šis ir tas brīdis, kad visa procedūrā vai funkcijā iekodētā loģika tiešām tiek izpildīta un iegūts (cerams vēlamais 😉 ) rezultāts.

Tālāk neliels iebūvētās procedūras piemērs visās trīs tradicionāli manis aprunātajās DBVS. Pieņemsim, ka mums ir lietotāju sesiju tabula act_user_sessions, kurā tiek glabātas aktīvās lietojumprogrammu sesijas. Procedūra kill_inact_sessions izdzēsīs tās sesijas, kurās pēdējā darbība veikta senāk, nekā parametrā norādītais minūšu skaits. Ja parametrs nav padots, tad tam piekārto noklusēto vērtību. Tabula – šis piemērs der Oracle. SQL Server un MySQL DATE būtu jāaizvieto ar DATETIME.

CREATE TABLE act_user_sessions (
  sess_id VARCHAR(32) NOT NULL,
  user_id INTEGER NOT NULL,
  last_action_time DATE);

ALTER TABLE act_user_sessions ADD CONSTRAINT sess_pk PRIMARY KEY(sess_id);

Piezīme: šeit uzskatāmā veidā ir redzams, ka datubāzu neatkarība – tas ir apmēram kā filozofu akmens meklējumi. Jo diemžēl Oraclē DATE nozīmē datums + laiks, SQL Serverī un MySQL tikai datums. Savukārt TIMESTAMP datu tipu nav iespējams izmantot, jo SQL Serverī, tas patiesībā ir sinonīms ROWVERSION datu tipam, kam ar datumu un laiku nav nekāda sakara. Vai nav jauki, ko?

Dati – te pat var iztikt ar vienu insert teikumu.

INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
VALUES ('6AA6DAFBBAB744A6BC798B0932325768', 1, current_timestamp);

Oracle piemērs

Procedūra Oraclei izskatās šādi:

CREATE OR REPLACE PROCEDURE kill_inact_sessions (
  -- atļautais bezdarbības minūšu skaits noklusēti 15
  idle_time IN NUMBER DEFAULT 15)
IS
BEGIN
  -- dzēš aktīvās sesijas, kuru bezdarbības minūšu skaits pārsniedz parametrā   padoto
  DELETE act_user_sessions
  WHERE sysdate - last_action_time >= 1*idle_time/(24 * 60);  COMMIT;
END;

Lai izsauktu procedūru SQL*Plusā vai kādā citā vizuālā rīkā var izmantot anonīmo bloku.

BEGIN
  kill_inact_sessions;
END;

Lūk piemērs. Vispirms pievienojam ierakstu tabulai. Tad izsaucam procedūru. Redzam, ka ieraksts vēl joprojām tabulā ir. Tad mazliet pagaidam un pievienojam otru ierakstu. Tagad redzam, ka tabulā ir jau divi ieraksti. Izsaucam vēlreiz procedūru, šoreiz padodot parametru 1, tas ir dzēšam visus ierakstus, kas ir vismaz minūti veci. Pēc tam redzam, ka tabulā palicis tikai 1 ieraksts.

SQL> INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
  2  VALUES ('6AA6DAFBBAB744A6BC798B0932325768', 1, current_timestamp);

1 row created.
SQL> BEGIN
  2    kill_inact_sessions;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT last_action_time, sysdate FROM act_user_sessions;

LAST_ACTION_TIME    SYSDATE
------------------- -------------------
2011.02.08 18:44:31 2011.02.08 18:45:56

SQL> INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
  2  VALUES ('6AA6DAFBBAB744A6BC798B0932325762', 2, current_timestamp);

1 row created.

SQL> SELECT last_action_time, sysdate FROM act_user_sessions;

LAST_ACTION_TIME    SYSDATE
------------------- -------------------
2011.02.08 18:44:31 2011.02.08 18:48:12
2011.02.08 18:47:57 2011.02.08 18:48:12

SQL> BEGIN
  2    kill_inact_sessions(1);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT last_action_time, sysdate FROM act_user_sessions;

LAST_ACTION_TIME    SYSDATE
------------------- -------------------
2011.02.08 18:47:57 2011.02.08 18:48:26

SQL Server piemērs

Procedūra šeit izskatās diezgan līdzīga. Ir, protams, sintakses atšķirības parametra definēšanā, arī SQL teikumā, taču nekā principiāli atšķirīga, protams, nav.

CREATE PROC [dbo].[kill_inact_sessions]
  -- atļautais bezdarbības minūšu skaits noklusēti 15
   @idle_time integer = 15
AS
BEGIN
  -- dzēš aktīvās sesijas, kuru bezdarbības minūšu skaits pārsniedz parametrā padoto
  DELETE act_user_sessions
  WHERE DATEDIFF(MINUTE, last_action_time, CURRENT_TIMESTAMP) >= @idle_timeEND
GO

SQL procedūras izsaukums izskatās šādi:

EXECUTE [dbo].[kill_inact_sessions]
GO

Atkārtojam to pašu piemēru šeit. Tātad izpildāmās konstrukcijas izskatās šādi. Pievēršam uzmanību, ka pirmajā reizē procedūra tiek izsaukta bez parametra, otrajā reizē, tai ir parametrs 1. Tas pirmajā brīdī varētu arī paslīdēt garām, jo parametrs nav iekavās.:

INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
VALUES ('6AA6DAFBBAB744A6BC798B0932325768', 1, current_timestamp);

(1 row(s) affected)

EXECUTE [dbo].[kill_inact_sessions]
GO

(0 row(s) affected)

SELECT last_action_time, current_timestamp FROM act_user_sessions;

last_action_time
----------------------- -----------------------
2011-02-09 01:38:07.087 2011-02-09 01:38:07.107

(1 row(s) affected)

INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
VALUES ('6AA6DAFBBAB744A6BC798B0932325762', 2, current_timestamp);

(1 row(s) affected)

SELECT last_action_time, current_timestamp FROM act_user_sessions;

last_action_time
----------------------- -----------------------
2011-02-09 01:41:17.413 2011-02-09 01:41:17.413
2011-02-09 01:38:07.087 2011-02-09 01:41:17.413

(2 row(s) affected)

EXECUTE [dbo].[kill_inact_sessions] 1
GO

(1 row(s) affected)

SELECT last_action_time, current_timestamp FROM act_user_sessions;

last_action_time
----------------------- -----------------------
2011-02-09 01:41:17.413 2011-02-09 01:41:17.433

(1 row(s) affected)

MySQL piemērs

Sākot ar 5.0 versiju arī MySQL nodrošina procedūras, funkcijas un trigerus. MySQL dokumentācijā teikts, ka to veidošana notiekot atbilstoši SQL:2003 sintaksei, kuru lietojot arī IBM DB2. Nekad neesmu neko darījis iekš DB2, bet iespējams, ka tādā gadījumā sintaktiski starp MySQL un DB2 ir ļoti maz atšķirību.

Jāatzīst, ka šī bija mana pirmā procedūra MySQLā, līdz ar to tās izveide prasīja diezgan neadekvāti ilgu laiku, kuras laikā es noskaidroju pāris tīri MySQL specifiskas lietas:

• Tā kā procedūrās var būt un parasti ir vairāki SQL teikumi, tad speciāli ir jānorāda atdalītājsimbols, ar ko beigsies procedūras definīcija. Kāpēc MySL izstrādātājiem patīk sāpēs un čakars un kāpēc viņi nevarēja kādu izvēlēties automātiski līdzīgi kā Oraclē, nav īsti skaidrs, iespējams vēsturiskas problēmas. Jo katru reizi definēt un pārdefinēt ir ļoti nepatīkami.
• Izrādās, ka MySQLā ar ALTER PROCEDURE SQL teikumu nav iespējams mainīt procedūras parametrus un pašu ķermeni, tā vietā procedūra jānodzēš (DROP) un jāveido no jauna (CREATE). Vispār tas ir ļoti slikti (iemesls sīkāk paskaidrots zemāk), bet visdrīzāk to var izskaidrot ar tradicionālo bērnu slimību – vēl nav paspējuši uztaisīt.
• Ieejas parametriem nav iespējams dot noklusētās vērtības – arī droši vien tā pati bērnu slimība.

Tas pats piemērs MySQLā izskatītos tā kā zemāk. Vispirms definējam atdalītājsimbolu //, tad dzēšam procedūru, tad veidojam no jauna, tad definējam atdalītājsimbolu atkal kā normālo semikolu, tad visbeidzot izsaucam procedūru.

delimiter //
DROP PROCEDURE kill_inact_sessions //
CREATE PROCEDURE kill_inact_sessions (
  -- atlautais bezdarbibas minusu skaits
  IN idle_time INT)
BEGIN
  -- ja padotais minūšu skaits ir null, tad piešķir tam noklusēto vērtību
  IF idle_time IS NULL
  THEN SET idle_time = 15;
  END IF;
  DELETE FROM act_user_sessions
  WHERE time_to_sec(timediff(sysdate(), last_action_time)) > idle_time*60;
END//
delimiter ;

CALL kill_inact_sessions(null);

Tagad tā pati tradicionālā darbību secība, kas iepriekš. Tā kā procedūrām nav noklusēto parametru, tad tā jāizsauc ar vērtību NULL, kas pašā procedūrā tiek pārbaudīta un tad nepieciešamības gadījumā piešķirta noklusētā vērtība 15. Citādi izsaukums ne ar ko īpaši neatšķiras no iepriekšējiem diviem piemēriem.

mysql> INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
    -> VALUES ('6AA6DAFBBAB744A6BC798B0932325768', 1, current_timestamp);
Query OK, 1 row affected (0.03 sec)

mysql> CALL kill_inact_sessions(null);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT last_action_time, current_timestamp FROM act_user_sessions;
+---------------------+---------------------+
| last_action_time    | current_timestamp   |
+---------------------+---------------------+
| 2011-02-12 12:39:02 | 2011-02-12 12:39:27 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO act_user_sessions (sess_id, user_id, last_action_time)
    -> VALUES ('6AA6DAFBBAB744A6BC798B0932325762', 2, current_timestamp);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT last_action_time, current_timestamp FROM act_user_sessions;
+---------------------+---------------------+
| last_action_time    | current_timestamp   |
+---------------------+---------------------+
| 2011-02-12 12:39:36 | 2011-02-12 12:39:43 |
| 2011-02-12 12:39:02 | 2011-02-12 12:39:43 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> CALL kill_inact_sessions(1);
Query OK, 1 row affected (0.14 sec)

mysql> SELECT last_action_time, current_timestamp FROM act_user_sessions;
+---------------------+---------------------+
| last_action_time    | current_timestamp   |
+---------------------+---------------------+
| 2011-02-12 12:39:36 | 2011-02-12 12:40:14 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Kopsavilkums

Kā redzams saglabāto procedūru izveide nav gluži Ķīnas ābece vai kosmosa kuģa būve. Process ir pietiekami vienkāršs, galvenais jāatceras, ka vispirms ir izveide un tad izsaukums.

Par procedūru izveidi runājot jāsaka, ka man personīgi vislabāk patīk Oracle variants, kur procedūru var izveidot vai pārrakstīt reizē rakstot CREATE OR REPLACE. SQL Serverī var vismaz rakstīt ALTER, bet MySQL to praktiski nevar. Kāpēc tas ir tik svarīgi? Tāpēc, ka pārrakstot procedūru, tai saglabājas visas piešķirtās tiesības un potenciāli mazāk invalidējas (maina statusu un tās nepieciešama pārkompilēt) citas atkarīgās procedūras. Procedūru dzēšot un izveidojot no jauna visas tiesības ir jāpiešķir no jauna un tas var būt ļoooooti netriviāls uzdevums.

Tagad daži vārdi par to labajām un sliktajām īpašībām. Vispirms galvenās labās īpašības:

• Samazināta tīkla noslodze. Viss darbs, ko SQL procedūra dara norisinās vienuviet – datubāzē, maksimāli tuvu datiem. Neizmantojot saglabātās procedūras ir atsevišķi SQL teikumi, kuros maksimāli optimizētā gadījumā atlasa datus, pārsūta uz klientu apstrādā un aizsūta atpakaļ. Daudz tipiskāk ir vēl krietni sliktāki gadījumi, kad uz klientu aizsūta tūkstoš ierakstu, tad 90% izmet un tikai 10% izmanto. Vai vēl trakāk – apstrādā ierakstu pa ierakstam ntās reizes mētājot pa tīklu vienādi otrādi pa vienam ierakstam.
• Var maksimāli izmantot konkrētās DBVS sniegtās iespējas. Tās mēdz būt unikālas vienas DBVS ietvaros (parasti gan var atrast ko līdzīgu citur), bet tai pašā laikā dot maksimāli ātrāko rezultātu.
• Ir standarta saskarne (parametri) un to var izsaukt no N dažādām aplikācijām vienmēr esot ar pārliecību, procedūra darbosies prognozējami un vienādiem parametriem rezultāts būs vienāds neatkarīgi no izsaukuma vietas.
• Paaugstināta drošība – aplikācijām nav jādod tiesības modificēt un lasīt datus pa tiešo, to var darīt izmantojot procedūras, tādējādi nodrošinot papildus kontroli pār aplikācijām.
• Uzlabota ātrdarbība. Parasti saglabātās procedūras ir vieglāk skaņot un panākt to ātrāku darbību salīdzinot ar SQL un procedurālo teikumu apvienojumu kādā citā vidē.
• Potenciālās izmaiņas  ir tikai vienā vietā. Ja izmanto trīs līmeņu arhitektūru, tad, visdrīzāk, ieguvums nav tik liels, savukārt klienta-servera aplikācijas gadījumā, nav jāpārinstalē N klienti.
• Ir lielāka varbūtība, ka saglabātās procedūras izstrādā cilvēks, kam ir pieredze darbā ar datubāzēm, nevis tikai priekšgala (front-end) programmatūras veidošanā. Līdz ar to pastāv cerība, ka datubāzē šādā gadījumā neizvērtīsies par datu miskasti, kā tas diemžēl mēdz būt, ja cilvēki tikai ar lietotāja saskarnes programmēšanas zināšanām ķeras pie datubāzēm un SQL.

Protams, saglabātajām procedūrām kā jau katrai lietai šai dzīvē ir arī savi mīnusi. Daži galvenie ir uzskaitīti zemāk:

• Katrai DBVS jāveido savs procedūru komplekts. Ja nu Jūsu sistēmai mārketinga materiālos ir ierakstīts, ka tai ir vienalga kāda DBVS apakšā, tad katrai no tām nākas veidot savu procedūru komplektu. Tas nu tā ir un no tā izbēgt nevar, tas, protams, nozīmē vairāk izmaiņas, testēšanas utt. Tiesa gan tas varētu būt nozīmīgi produktiem, kas tiek pozicionēti plašam lokam. Projektiem, kas orientēti uz kādām specifiskām vajadzībām, tas parasti nav aktuāli, ja vien kāds prasību skribelētājs nav ierakstījis prasību par to, ka jāstrādā uz jebkuras DBVS, tādējādi apliecinot savu nekompetenci šai jautājumā un esot gatavs akceptēt, ka, piemēram, gan Dakārā, gan F1 trasēs kopā startē gan apvidus mašīnas, gan F1 bolīdi. Galu galā taču mašīnas vien ir, kāda jēga kaut ko dalīt?
• Saglabātās procedūras prasa vēl vienas programmēšanas valodas un vides apguvi. Oraclē normāli tas ir PL/SQL, SQL Serverī Transact SQL, MySQLā arī sava valoda. Tiesa gan teorētiski Oraclē to var darīt arī izmantojot Javu un SQL Serverī var rakstīt .NET saglabātās procedūras, taču kaut kā nav radies iespaids, ka šāda prakse būtu ļoti izplatīta.
• Vēl viena papildus programmatūras vide un atsevišķs koda kopums, ko nākas uzturēt. Tas, protams, prasa zināmas pūles un resursus.
• Ja procedūras lieto akli, katrai tabulai definējot savu pievienošanas/koriģēšanas/dzēšanas/atlases procedūru komplektu, tad, protams, šāda pieeja rada nevajadzīgas galvassāpes daudzu uzturamu objektu veidā un nekādā veidā neatvieglo darbu. Protams, ka tik mulķīgi riķoties nevajadzētu un pareizā pieeja būtu tās organizēt pa objektiem, kas nepieciešami no biznesa viedokļa, piemēram, personas ievade/koriģēšana/dzēšana/atlase, ar visiem personas datiem, adresi, informāciju par darba vietu utt, kas DB var tikt glabātas Ntajās tabulās.

Tātad, kā redzat, saglabātajām procedūrām ir savas labās un savas sliktās īpašības. Tāpēc nevajadzētu akli nosvērties vienā vai otrā virzienā pēc principa, ja man ir āmurs, tad visu daru ar āmuru, vai arī, ja skabargas izvilkšanā āmurs neder, tātad tas neder nekam. Ir vērts izsvērt visus par un pret un tad pieņemt lēmumu, vai tās lietot, vai nē.