Iškilo būtinybė uždėti limitą duombazės dydžiui. Pats MySQL tokio funkcionalumo neturi. Ieškojau kaip išspręsti šią problemą… Mano reikalavimai sprendimui tokie:

• reikalavimų minimumas; portabilumas - kad nereikėtų įdiegti papildomos programinės įrangos, kad veiktų kuo įvairesnėse aplinkose, nereikalautų didelio paruošimo bei palaikymo;
• resursų taupymas

Kaip gali būti realizuota tokia funkcija? Atrodo pakankamai paprastai - tereikia žinoti duombazės dydį (esamą.. bet geriau tą, kuris būtų po užklausos įvykdymo) ir jei tas dydis viršija mūsų nustatytą limitą tai atitinkamai arba leidžiama įvykdyti užklausą arba ne.

Bet yra ir kitas būdas. Žinome, kad MySQL kiekvieną duombazę saugo atskirame kataloge. Taigi tereiktų failų sistemos lygyje nustatyti apribojimą tam katalogui, kad nesipūstų per daug. GNU/Linux sistemoje yra tokia galimybė nustatyti limitą konkrečiam naudotojo namų katalogui. Taigi:

• sukuriam naują vartotoją (tebūnie ‘manovardas’) serveryje ir nustatom limitą jo namų katalogui.
• sukuriam naują duombazę:

  CREATE TABLE manodb

• sustabdom MySQL servisą - nebūtina, bet labai rekomenduojama!
• perkeliam duombazės katalogą pas naudotoją “į namus”:

  mv /var/lib/mysql/manodb /home/manovardas

  (gal reikia “-R” rakto, nepamenu)

• sukuriam nuorodą:

  ln -s /home/manovardas/manodb /var/lib/mysql/manodb

• paleidžiam MySQL servisą.

Viskas. Ar šis sprendimas tinka visada? Nemanau. Pavyzdžiui, mes nežinom, kas bus, kai duombazė pasieks tą nustatytą limitą? Ar mums vykdant “insert” užklausą bus išmesta klaida? Ar viskas užstrigs? Servisas nulūš? Internete radau kažkieno pasakymą, kad tokiu atveju lūžta einamoji MySQL serviso gija. Visgi man sunku tuo patikėt - kai man pačiam buvo pasibaigus vieta particijoj ir įterpinėjau duomenis tai tiesiog mano užklausa užšaldavo. Kiti susijungimai toliau leisdavo vykdyt SELECT užklausas. Sako, tyla gera byla, bet mūsų atveju geriau būtų klaida :) dabar sėdi naudotojas ir nežino kas vyksta. Kita problema - reikia turėti teises į failų sistemą. Dar reikia kurti papildomą sistemos naudotoją… taigi, man šis sprendimas netiko, todėl ieškojau kaip realizuot pirmu minėtu metodu (patikrinant DB dydį).

Duombazės dydis - kaip jį sužinoti?

Turbūt paprasčiausia būtų pažiūrėt, kiek užima duombazės failai. Bet ne visada galim turėt galimybę tai atlikt. Pavyzdžiui, jei jungiamės prie nutolusio MySQL serverio. Arba net jei tai vietinis serveris, vis tiek galime neturėti teisių tokiam veiksmui.

Suskaičiuoti visus įrašus… mm.. būtų galima bandyt, jei kiekvienas įrašas užimtų tiek pat vietos - tada apskaičiuotume įrašo dydį, sudaugintume su eilučių skaičiumi lentelėje ir tai pakartotume visoms lentelėms. Bet, deja, ne visi įrašai yra fiksuoto dydžio - kaltininkai čia būtų VARCHAR (ir kt.) laukai. Taigi, pamirštam.

Atrodo yra paprastesnis būdas. Tai, ko mums reikia, grąžina užklausa:

SHOW TABLE STATUS;

Kad nesikartočiau, ką jau kažkas parašė, siūlau tiesiog užeit šiuo adresu:
http://www.drquincy.com/resources/tutorials/webserverside/getthesizeofamysqldatabasewithphp/

Limitai

Žinom duombazės dydį - kas dabar? Sakykim, mūsų sistema, kuri naudos duombazę, yra svetainė rašoma su PHP. Tai nejau prieš kiekvieną užklausą vykdysim tą kodą, kuris tikrina DB dydį? Jei mums reikės padaryt 10 000 įterpimų, tai tiek kartų tikrinti būtų neracionalu. Ypač jei DB klientas ir DB serveris yra nutolę. Kita problema - gal mes norime kad ne tik php skriptas įterpinėjant duomenis neperžengtų ribos, bet kad niekaip nebūtų įmanoma apeiti limito (net tiesiai prisijungus iš konsolės)? Tada reiktų tikrinimą kažkaip “įklijuoti” į patį MySQL. Taip ir padarom:

• sukuriam funkciją (kažkas tokio kaip STORED PROCEDURE, tik grąžins skaičių) kuri nustato DB dydį
• ant kiekvienos lentelės sukuriam trigerius, kurie suveiktų, pavyzdžiui, prieš įterpimą; trigeris turėtų patikrinti ar galima įterpti, ar ne.

Atrodo gražu, bet viena problemėlė. O kaip mes trigeryje pasakysim “žinai, nebedarom įterpimo nes pasiektas limitas”? MySQL neturi tokios galimybės kaip kad “raise_error” ant Microsoft SQL Server. Sako, kad nuo MySQL 5.2 gal jau bus “signalai”, kurie leis padaryt tai, ko mums reikia.. bet mums reikia dabar. Visgi yra sprendimas:
http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/
Siūlo tiesiog sukelti bet kokią kitą klaidos situaciją, pvz “unknown column”.

Beliko viską realizuot. Kadangi aš beveik nesu rašęs MySQL saugomų procedūrų (”STORED PROCEDURE“), funkcijų tai neišsiverčiau be MySQL dokumentacijos ir kodo pavyzdžių, rastų internete. Pavyzdžiui, štai kaip reiktų prasukt ciklą pasinaudojant kursoriais:
http://dev.mysql.com/doc/refman/5.0/en/cursors.html
Man šitas ciklas pasirodė keistokas, todėl dariau taip kaip parodyta čia:
http://www.futhark.ch/mysql/130.html

Viskas, daugiau paaiškinimų nebebus.

Funkcijos, nustatančios DB dydį, kodas:

delimiter //
CREATE FUNCTION dbsize () RETURNS integer
READS SQL DATA
BEGIN
  DECLARE size integer DEFAULT 0;
  DECLARE c1, c2, c3, c4, c5, c6,
    datalength, c8, indexlength,
    c10, c11, c12, c13, c14, c15, c16,
		c17, c18 integer;
  DECLARE done BOOLEAN DEFAULT FALSE;
 
  DECLARE curr CURSOR FOR SHOW TABLE STATUS;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
 
  OPEN curr;
 
  loop1: LOOP
 
    FETCH curr INTO c1, c2, c3, c4, c5, c6, datalength, c8, indexlength, c10, c11, c12, c13, c14, c15, c16, c17, c18;
    IF done THEN LEAVE loop1; END IF;
    SET size := size + datalength + indexlength;
 
  END LOOP loop1;
 
  CLOSE curr;
 
  RETURN size;
 
END;
//
delimiter ;

Trigerio kodas:

delimiter //
CREATE TRIGGER t1bi
BEFORE INSERT ON t1
  FOR EACH ROW
BEGIN
  SET @dummy = 0;
  IF dbsize() > 20000 THEN
    SELECT Database_size_limit_reached INTO @dummy FROM t1;
  END IF;
END;
//
delimiter ;

Čia:

• 20000 - skaičius 20000 rodo baitų kiekį, kurio negali peržengti duombazės dydis.
• t1 - lentelė, kuriai kuriamas trigeris; reikėtų sukurti kiekvienai lentelei.
• t1bi - kuriamo trigerio vardas (lentelės vardas plius pirmosios raidės iš “BEFORE INSERT”)

Dabar beliko sutvarkyt leidimus (angl. permissions). Naudotojui leisti tik SELECT, INSERT, UPDATE, DELETE, bet neleisti kurti/trinti (būtent trinti) funkcijų ir trigerių.

Išvados

Man šis sprendimas labai tinka nes:

• nėra jokių papildomų reikalavimų, nereikia nieko papildomai įdiegti, kurti papildomų lentelių. Na gerai, yra reikalavimai - būtinas trigerių palaikymas bei lentelėse negali būti stulpelis pavadinimu “Database_size_limit_reached” :)
• nors tenka kiekvienu įterpimu iš naujo patikrinti DB dydį, bet tai sąlyginai mažai resursų reikalaujantis procesas (pas mane tik ~10 lentelių o ne 200+), ir pasitelkus “STORED PROCEDURES” tikrinimas vyksta ne pas klientą o pačioje DBVS, nenaudojamas tinklo srautas.

Panašūs straipsniai

• SQL pagrindai (3)
• Savaitgalio skaitiniai #32 (0)
• Savaitgalio skaitiniai #27 (0)
• Savaitgalio skaitiniai #23 (0)
• Savaitgalio skaitinai #34 (1)

Žymėti: kaip.tik.ten | del.icio.us | topix.lt | žynios.lt