Views, transaksjoner, integritet

Lagrede spørringer som virtuelle tabeller

En view er en navngitt SELECT. Den oppfører seg som en tabell — du kan velge fra den, joine den, lage views over views — men dataene ligger i de underliggende tabellene. Views er kraftige fordi de gir to ting: (1) gjenbrukbar kompleksitet og (2) en sikkerhetsmekanisme der brukere får se en utsnitt uten å få tilgang til hele basen.

CREATE VIEW faculty AS
  SELECT ID, name, dept_name
  FROM instructor;

-- Brukes som en helt vanlig tabell:
SELECT name FROM faculty WHERE dept_name = 'Biology';

Virtuell vs. materialisert

Hvorfor views?

• Forenkling — pakk en kompleks join inn i ett navn.
• Sikkerhet — gi en bruker tilgang til viewet, ikke basetabellen, så de bare ser delen som er relevant for dem.
• Logisk uavhengighet — endre underliggende skjema, men behold viewets «kontrakt» mot applikasjonen.

Vedlikehold av materialiserte views

Et materialisert view er en kopi som må holdes synkron med basetabellene. Tre strategier — fra dyrt-men-ferskt til billig-men-utdatert:

Valget er en avveining mellom oppdateringskost, lesekost og krav til ferskhet. Materialiserte views er typisk valg når lesetrykket er mye høyere enn skrivetrykket.

Når kan du INSERT/UPDATE/DELETE gjennom et view?

Et view er oppdaterbart hvis SQL kan unikt avgjøre hvilken endring i basetabellene som skal foretas. Det er strenge regler:

• Kun én tabell i FROM (ingen joins).
• Ingen aggregat / GROUP BY / HAVING / DISTINCT (de slår sammen rader — kan ikke inverteres).
• Alle NOT NULL-kolonner uten DEFAULT må være med i SELECT-listen — ellers kan vi ikke INSERT-e.
• Ingen aritmetikk eller subqueries i SELECT-listen.

WITH CHECK OPTION

Et viktig sikkerhetsnett: viewet definerer en filtrert visning, men brukeren kan likevel sette inn rader som havner utenfor viewet — og da forsvinner de fra deres syn. WITH CHECK OPTION hindrer dette.

CREATE VIEW cs_instructor AS
  SELECT *
  FROM instructor
  WHERE dept_name = 'CS'
  WITH CHECK OPTION;

-- Forsøk på å sette inn en Physics-ansatt gjennom dette viewet
-- vil avvises — raden ville ikke vært synlig i viewet etterpå.
INSERT INTO cs_instructor VALUES ('9999', 'X', 'Physics', 50000);
-- ERROR: violates view check option

Reglene som beskytter dataene

Constraints er kontrakter mellom skjemaet og hver INSERT/UPDATE. DBMS-en avviser endringer som bryter dem — uten dem ville dårlig data sive inn umerket.

CREATE TABLE account (
  acct_no   VARCHAR(10) PRIMARY KEY,
  branch    VARCHAR(20) NOT NULL,
  balance   NUMERIC(12, 2) CHECK (balance >= 0),
  customer  VARCHAR(10) NOT NULL,
  FOREIGN KEY (customer) REFERENCES customer (id),
  UNIQUE (branch, customer)
);

Hva skjer med referanser når den refererte raden forsvinner?

Når en rad i den refererte tabellen slettes eller endres, kan referansen i den refererende tabellen behandles på fire måter:

CREATE TABLE course (
  course_id  VARCHAR(7) PRIMARY KEY,
  dept_name  VARCHAR(20),
  FOREIGN KEY (dept_name) REFERENCES department (dept_name)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

Eksemplet: hvis en avdeling slettes, blir kursenes dept_name satt til NULL. Hvis avdelingsnavnet endres, oppdateres alle referanser automatisk.

Egendefinerte regler

CHECK — per rad

CREATE TABLE employee (
  id      INT PRIMARY KEY,
  age     INT CHECK (age BETWEEN 16 AND 100),
  salary  NUMERIC(10, 2),
  bonus   NUMERIC(10, 2),
  CHECK (bonus <= salary / 2)
);

CHECK-er evalueres ved hver INSERT/UPDATE. Begrensning: de gjelder per rad — du kan referere til andre kolonner i samme rad, men ikke andre rader eller andre tabeller (i ANSI-standarden; noen DBMS-er løsner på dette).

ASSERTION — på databasen

Dette er regler som spenner over flere rader/tabeller. Standard SQL definerer CREATE ASSERTION, men det er nesten ingen DBMS som implementerer det — fordi systemet må sjekke regelen ved hver eneste endring som kunne brutt den.

-- ANSI-standard, sjeldent støttet i praksis
CREATE ASSERTION total_credits_check
CHECK (
  NOT EXISTS (
    SELECT ID FROM student
    WHERE tot_cred > 200
  )
);

I praksis må regler på tvers av rader/tabeller implementeres med triggere (se 3D).

BEGIN · COMMIT · ROLLBACK

En transaksjon er en sekvens av SQL-setninger som behandles som én atomær enhet — alt eller ingenting. Den klassiske eksempelet er en bankoverføring: trekk fra konto A og legg til på konto B må enten begge skje, eller ingen.

BEGIN TRANSACTION;

  UPDATE account SET balance = balance - 500
   WHERE acct_no = 'A-101';

  UPDATE account SET balance = balance + 500
   WHERE acct_no = 'B-202';

COMMIT;
-- eller, ved feil:
-- ROLLBACK;

Utsatt sjekk: DEFERRABLE INITIALLY DEFERRED

Noen FK-situasjoner skaper en hønen-og-egget-knipe. Tenk deg person(id, navn, ektefelle) der ektefelle er en FK til samme tabell, og ikke kan være NULL. Tommy og Gina er gift — hvilken rad setter du inn først?

CREATE TABLE ektefelle_par (
  id CHAR(5),
  navn VARCHAR(40),
  ektefelle CHAR(5) NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(ektefelle) REFERENCES ektefelle_par
    DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
  INSERT INTO ektefelle_par VALUES ('A', 'Tommy', 'B');
  INSERT INTO ektefelle_par VALUES ('B', 'Gina',  'A');
COMMIT;   -- FK-sjekken kjøres her, og begge eksisterer nå

Med DEFERRABLE INITIALLY DEFERRED utsetter DBMS-en sjekken til commit-tidspunktet. Inne i transaksjonen kan en FK-rad midlertidig peke på noe som ikke finnes ennå. Default er NOT DEFERRABLE — sjekken skjer ved hver setning.

Få fart på spørringer

En indeks er en datastruktur (typisk B+-tre) som lar DBMS-en finne rader uten å lese hele tabellen. Selve datastrukturen dekkes i kapittel 6 — her dreier det seg om hvordan du definerer dem i SQL.

-- Enkel indeks
CREATE INDEX idx_inst_name ON instructor (name);

-- Sammensatt — rekkefølgen er signifikant!
CREATE INDEX idx_dept_sal ON instructor (dept_name, salary);

-- Unik indeks — håndhever entydighet
CREATE UNIQUE INDEX idx_email ON users (email);

DROP INDEX idx_inst_name;

Sammensatte indekser: hvorfor rekkefølgen betyr noe

En indeks på (dept_name, salary) kan brukes for WHERE dept_name = 'CS', men ikke alene for WHERE salary > 70000. Tenk på det som et sortert leksikon: du finner kjapt alle CS-ansatte, men ikke alle med en gitt lønn — fordi de er sortert etter avdeling først.

Hvem får gjøre hva?

GRANT SELECT, INSERT ON instructor TO alice, bob;

GRANT UPDATE (salary) ON instructor TO hr_app;

GRANT SELECT ON faculty TO public;

REVOKE INSERT ON instructor FROM bob;

Privilegier i SQL-standarden

• SELECT · INSERT · UPDATE · DELETE — på tabell eller view
• REFERENCES — lov til å lage FK-er som peker på tabellen
• ALL PRIVILEGES — alle de over

WITH GRANT OPTION

GRANT SELECT ON instructor TO alice WITH GRANT OPTION;
-- Alice kan nå GRANT SELECT videre til andre.
-- Når du REVOKE-er fra Alice, faller hele kjeden hun ga videre.

Roller

I store systemer blir det uhåndterbart å gi privilegier per bruker. Roller er navngitte samlinger av privilegier. Tildel rollen til brukere, så har de alle privilegiene rollen har — og endringen sprer seg automatisk.

CREATE ROLE teacher;
GRANT SELECT, UPDATE ON grades TO teacher;
GRANT teacher TO alice, bob, carol;

Det du skal kunne

1. Skrive CREATE VIEW og forklare når et view er oppdaterbart.
2. Bruke WITH CHECK OPTION og forklare hva det forhindrer.
3. Liste de fire ON DELETE-handlingene og når CASCADE er passende.
4. Skille CHECK fra ASSERTION og vite hvorfor sistnevnte sjeldent brukes.
5. Skrive en transaksjon med BEGIN/COMMIT/ROLLBACK og forklare ACID.
6. Designe en sammensatt indeks slik at WHERE-klausulen kan bruke den.
7. Bruke GRANT/REVOKE og forklare cascading revoke.

Klar for prosedyrer, triggere og rekursjon? Gå til 3D →