I opfølgning på blogposten omkring de fysiske omgivelser ved en Oracle Database, vil jeg i denne, komme nærmere ind på hvordan vi kan arbejde med Oracle databasen. I kommunikation med Oracle databasen, er det typisk at "snakke" SQL, Structured Query Language, der er det nok mest populære sprog til at oprette, ændre, hente og slette data i relationelle databaser (relational database management systems, RDBMS). 

Oracle Databaser - Del I, General Database Struktur: memory, prosesser, filer

Historie

I Juni 1970 skrev og publiserede Dr. Edgar F. Codd en artikel; "A Relational Model of Data for Large Shared Data Banks" i Association for Computing Machinery (ACM) journal, Communications of the ACM. Dr. Codd's artikel blev vidt accepteret som den definition for relationel database management systemer vi kender i dag (RDBMS el. RDMS).

Gennem 1970'erne arbejde en gruppe på IBM's San Jose udviklings center på et database system "System R" der i stor grad baserede sig på, men ikke var fuldstændig "tro" mod Codd's model. Structured English Query Language ("SEQUEL") var designet til at manipulerere og hente data fra System R. Akronymet SEQUEL blev senere til SQL. Grunden skulle findes i at SEQUEL var trademark for Hawker-Siddeley - et flyselskab i England. Selvom meget af SEQUEL baserede sig på Dr. Codd's model, var det Donald D. Chamberlin og Raymond F. Boyce fra IBM der forfattede SEQUEL, og det var deres koncepter der blev publiseret for at øge interessen omkring SQL.

Den første ikke kommercielle, relationelle, ikke-SQL database, INGRES, blev udviklet i '74 ved U.C Berkely.

I '78, blev der metodisk testet på systemet på kunde sites. Det demonstrerede hvor praktisk og brugbart systemet egentlig var. Denne testing gik hen og blev en kæmpe success for IBM. Som et resultat heraf begyndte IBM at udvikle produkter der implementerede SQL baseret på deres System R prototype inklusiv System/38 (introduceret i '78 - på markedet i '79), SQL/DS (introduceret i '81) og endelig DB2 (i 1983).

På samme tidspunkt så firmaet Relational Software inc (nu Oracle corporation) mulighederne i koncepterne beskrevet at Chamberlin og Boyce, og udviklede deres egen version af en RDBMS til marinen, CIA og andre. I sommeren 1979 introducerede Relational Software Inc. Oracle V2 (Version 2) for VAX computere som den første kommercielle implementering af SQL. Det er ofte fejl citeret forskellige steder, at Oracle slog IBM på markedet med 2 år - fakta er at de kun var få uger før frigivelse af IBM's System/38.

Standarlisering

SQL blev en ANSI (American National Standards Institute) i 1986 og ISO (International Organization for Standardization) i 1987. ANSI har offentliggjort at den korrekte udtale form for SQL er;  [S.KJU.L] - det på trods udtaler mange database professionelle SQL som SEQUEL.

SQL standarden har gennemgået et antal revisioner; 

År Navn Alias Kommentar
1986 SQL-86 SQL-87 Publiseret af ANSI
1989 SQL-89   Lille revision.
1992 SQL-92 SQL2 Stor revision.
1999 SQL:1999 SQL3

Tilføjelse af rekursive forespørgsler, triggers, ikke skalerbare typer og objekt orienterede features.
2003 SQL:2003   XML features, standarliserede sequences, Kolonner med autogenererede værdier.

Scope

Selvom SQL er defineret ANSI og ISO, er der mange udvidelser, og variationer til gældende version. Mange udvidelser er af patentert natur, f.eks Oracle's PL/SQL eller Sybase, IBM's SQL PL (SQL procedural language), og Microsoft's Transact-SQL. Det er også ikke helt uanmindeligt at udelade support for standard features i sproget, f.eks DATE og TIME datatyperne, foretrækkes en variant af selskabets egen. Som resultat og i kontrast til f.eks ANSI C eller ANSI fortran, der kan porteres fra platform til platform uden de store strukturelle ændringer - er der typisk en del arbejde med at flytte SQL mellem database systemer. Der er flere grunde hertil:

  • størrelsen og komplexiteten på SQL standarden gør at mange databaser ikke implementerer hele standarden.
  • standarden specificerer ikke hvorledes databasen skal opføre sig på mange vigtige områder, f.eks indexes - dette overlades til implementøren at regne ud hvorledes det bør gøres.
  • SQL standarden specificerer præsist hvorledes den bør implementeres i et database system. Standard specifikationerne vedr semantik i sprog kontruktioner er ikke helt så definerede og leder mange gange til misforståelser..
  • nogle mener at manglen på kompatibilitet mellem database systemer er bevidst for at sikre en leverandør lock-in..

Grundstruktur - Repræsentation af data i rækker

Før vi kigger på DML og DDL - kan man sige at vi i "den virkelige" verden,- gemmer vores data i databaser. F.eks er hver kunde, tids forbrug, projekt beskrivelser m.v gemt hos mig, som en fuldstændig log over hvad der er sket hvornår, hos hvem og hvor lang tid det har taget. Kunden repræsenteres af en række i databasen, ligeledes projektet, tiden m.v. For at kunne muliggøre disse oprationer der er involveres med oprettelse/veligehold af data benytter jeg SQL. Nogen gange henter jeg bare data - for at lave en rapport f.eks til kunden over tidsforbrug for en given måned. Andre gange opretter jeg data, f.eks

"Mandag d. 15/maj/2006, 09:00-12:00, Arbejde på en bookmark funktionalitet i test miljøet PORTST. Udvikling af set bookmark,
test af funktionalitet. 3 timer."
"Mandag d. 15/maj/2006, 12:30-17:45, Nedbrud i produktion - recover fra backup. Tilstede PDU, KBI, HLA"

Til det benytter jeg variationer af SQL - kategoriseret i hovedgrupperne DML og DDL.

Hentning af data

Den oftest brugte operation i database sammenhænge er hentning af data.

  • SELECT benyttes til at hente 0 eller flere rækker fra en eller flere tabeller i databasen. I de fleste applikationer, er SELECT den mest benyttede DML kommando. Når man skriver en SELECT forespørgsel, specificere brugeren det ønskede resultat sæt, men de beskriver ikke hvilke fysiske operationer der skal udføre for at få fat i data. Oversættelse af forespørgslen til en effektiv query plan bliver overladt til database systemmet, mere specifikt til query optimizer'en.
    • Almindelige keywords der relatere sig til SELECT inkluderer:
      • FROM der fortæller, fra hvilke tabel eller tabeller data skal hentes fra. Ligeledes ved flere tabeller beskrives hvorledes join skal udføres. 
      • WHERE er brugt til at identificere hvilke rækker der skal hentes.
      • GROUP BY benyttes til grupperinger f.eks. HENT alle nøgletab fra tabellen REGNSKAB grupperet på Afdelingsnavn. SELECT afdelingsnavn, sum(udbetalinger) FROM REGNSKAB.
      • HAVING benyttes ved at en forespørgsel der har en GROUP BY eller hvor SELECT indeholder aggregates, hvor HAVING opfører sig meget som vi kender WHERE. Til forskel arbejder HAVING på GROUP BY resultater.
  • ORDER BY benyttes til at bestemme hvorledes et resultat sæt skal sorteres. 
Eksempel 1:
  SELECT * FROM boger WHERE pris > 100.00
  ORDER BY titel

Eksemplet ovenfor viser at vi skal hente data fra tabellen "boger" hvor prisen er > 100.00. Resultatsættet skal sorteres udfra titel alphanumerisk. (*) betyder ALLE rækker skal hentes der evalueres til true dvs alle titler hvor pris > 100. Alternativt til dette kan der specificeres hvilke kolonner man ønsker at se.

Eksempel 2:
   SELECT bk.title, count(*) AS Forfatter
   FROM boger AS bk, boger_forfatter AS ba
   WHERE bk.bog_number = ba.bog_number
   GROUP BY bk.titel

Eksempel 2 viser hvordan man henter data fra flere tabeller (kaldet et join), samt hvorledes resultater kan grupperes. "bk" og "ba" er det man kander "table aliases" og hjælper til at undgå ambiguasitet ved joins. "AS" er frivillig om man vil benytte i mange SQL versioner. Eksemplet henter data hvor vi vil se hvormange forfattere der er pr. bog.

 Titel                                  Forfatter
 ----------------------                 ---------
 Herligheden ved SQL                            1
 Faldgrupper i SQL                              1
 Hvordan jeg blev et bedre SQL menneske         1

Data manipulering

DML eller Data Manipulation Language benyttes til tilføjelser, opdateringer samt sletninger i databasen. Når du via et værktøj (SQL*Plus, SQL*Developer eller via en webside med adgang til databasen) er forbundet - arbejder du på din egen private version af data, tabeller, rækker indtil du fortæller værktøjet/toolet at du er færdig eller fortryder dine ændringer - eller simplethen at din PC bryder ned. SELECT hører også under DML, selvom det ikke modificerer data i databasen men bare henter data.

  • INSERT benyttes til at indsætte poster/rækker i en eksisterende tabel.
  • UPDATE benyttes til at opdaterer data i eksisterende rækker i en eksisterende tabel..
  • MERGE benyttes til at kombinerer data fra flere tabeller. Det er et mix af INSERT og UPDATE. Før SQL:2003 standarden havde nogle database leverandører implementeret en lignende funktionalitet og blev ofte kaldt "upsert".
  • TRUNCATE sletter alle data fra en tabel (ikke-standard, men en fælles SQL kommando - hurtigt også da der ikke logges sletninger i redo loggen).
  • DELETE sletter data fra en tabel. 
Eksempel:
INSERT INTO my_table (field1, field2, field3) VALUES ('test', 'N', NULL);
UPDATE my_table SET field1 = 'updated value' WHERE field2 = 'N';
DELETE FROM my_table WHERE field2 = 'N';

Data definition

Den anden SQL gruppering er Data Definition Language (DDL). DDL tillader brugeren at definere tabeller, indexer eller modifisere disse. De fleste leverandører har patenterede extensions til deres version af DDL, der håndterer kontrol over ikke standard features i databasen. Når du udfører et DDL udtryk er effekten umidelbar - dvs du skal ikke skrive COMMIT.

De simpleste DDL er CREATE or DROP kommandoerne.

  • CREATE forudsager et objekt oprettes i databasen. F.eks en tabel, et index m.v. Efter udført CREATE skal der ikke explicit udføres COMMIT idet dette foretages implicit.
  • DROP Forudsager et objekt i databasen droppes. Normalt er denne handling ireversibel. 
Eksempel:
CREATE TABLE my_table (
my_field1   INT          UNSIGNED,
my_field2   VARCHAR (50),
my_field3   DATE         NOT NULL,
PRIMARY KEY (my_field1, my_field2) 
) 

Efter oprettelse af en tabel kan det være nødvendigt at lave en ændring; f.eks tilføjelse af en kolonne, oprettelse af constraints m.v

ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);

Constraints i en Oracle database - er vel egentlig "business rules". F.eks hvis vi havde 2 tabeller; afdeling og medarbejder. Hver gang en medarbejder ansættes i en denne virksomhed - skal denne tilknyttes en afdeling. Før i tiden lagde vi den slags check i koden - men hvorfor nu gøre det når vi kan få databasen til at overholde disse regler ?

Man kunne forestille sig;

CREATE TABLE afdeling ( afdelingsnavn VARCHAR2(100) );

Til en tabel, definerer man også datatyper i dette tilfælde en tekststreng med max indhold pr. række på 100 karakterer.

ALTER TABLE afdeling ADD CONSTRAINT afdeling_afdnavn_pk PRIMARY KEY (afdelingsnavn);

Jeg tilføjer et PRIMARY KEY constraint der er en sammensætning af UNIQUE samt NOT NULL, med andre ord indsættes der i denne tabel skal det være med en værdi der ikke tidligere er indsat.

INSERT INTO afdeling (afdelingsnavn) VALUES ('Teknik');
INSERT INTO afdeling (afdelingsnavn) VALUES ('MARKETING');
INSERT INTO afdeling (afdelingsnavn) VALUES ('Salg');

Lige for at illustrere vores UPDATE igen - har jeg med vilje indsat afdelingen marketing med store bokstaver, det retter vi lige.

UPDATE afdeling SET afdelingsnavn = 'Marketing' WHERE afdelingnavn = 'MARKETING';

Nu er jeg klar med COMMIT til at gemme mine data så andre end mig kan se ændringerne.

COMMIT;

Så prøver jeg lige at hente data for at se om de står rigtigt

SELECT * FROM afdeling

CREATE TABLE medarbejder ( medarbejdernavn VARCHAR2(100), afdelingsnavn VARCHAR2(100) );

Jeg laver tabellen medarbejder og tilføjer en FOREIGN KEY. En foreign peger altid på en PRIMARY KEY enten i samme tabel eller i en anden tabel.

ALTER TABLE medarbejder ADD CONSTRAINT medarbejder_afdnavn_fk FOREIGN KEY (afdelingsnavn) REFERENCES afdeling (afdelingsnavn);

INSERT INTO medarbejder (medarbejdernavn, afdelingsnavn) VALUES ('kbirch', 'Teknik');
INSERT INTO medarbejder (medarbejdernavn, afdelingsnavn) VALUES ('kbirch', 'Teknikken_der_ikke_eksisterer');

Den første insert går godt men den anden fejler.

Det er fordi vi i vores opsætning med primary key/foreign key netop siger at indsættelser i medarbejder tabellen er under forudsætning at der eksisterer et afdelings navn (primary key) i addelings tabellen.

Det betyder at jeg som udvikler, blot behøver at koncentrere mig om INSERT og eventuelle fejlkoder - og ikke først lave et manuelt check før indsættelse.

Læg også mærke til fejlbeskeden ved reference til constraint'et ORABLOG.MEDARBEJDER.AFDNAVN_FK. Havde jeg ikke navngivet mine constraints ville der have stået SYS_0000 - der giver et hurtigere billede af problemmet hvis man som standard vælger at navngive sine constraints. Ligeledes er det lettere at finde frem i USER_CONSTRAINTS (data dictionary) for nærmere undersøgelse. Jeg har i dette til navngivet constrain'tet så det fortæller mig noget om; Tabelnavn, hvilken kolonne og typen af constraint.

Yderligere den første insert går jo godt - hvad når der fejles laves der ROLLBACK på fejlende udtryk eller siden sidste COMMIT el. ROLLBACK ? Der rulles kun tilbage for det udtryk der fejler. Der er dog muligheder som vi ser på senere med SET TRANSACTION.

Data control

Den tredie SQL gruppering er Data Control Language (DCL). DCL håndterer authorisations aspekter af data og tillader at brugeren kan kontrollere hvem der har adgang til at se, og manipulerer med data i databasen.

Der findes 2 typer nøgleord i forbindelse med DCL:

  • GRANT — Tillader en eller flere brugere at udføre en operation eller et sæt af operationer på et objekt.
  • REVOKE — Fjerner eller begrænser en bruger at udføre en operation eller et sæt opereationer på et objekt. 
Eksempel:
GRANT SELECT, UPDATE ON my_table TO some_user, another_user

Man skelner mellem 2 typer rettigheder; til objekter og til system. Eksempel på system er GRANT CREATE TABLE, GRANT CONNECT m.v. Ligeledes kan GRANT for henholdsvis objekter og system gives med el. WITH GRANT OPTION og WITH ADMIN OPTION. WITH GRANT OPTION på objekter skal man være opmærksom på REVOKE CASCADE effekten.

Lad os se på et eksempel. Jeg vil gerne give brugeren KBIRCH adgang til at læse data i min afdelings tabel (jeg er selv brugeren ORABLOG), dette gøres således

GRANT SELECT ON afdeling TO kbirch

Jeg vil også give læse adgang til afdeling og videregive muligheden for at KBIRCH selv kan give disse rettigheder videre. Det gøres således

GRANT SELECT ON afdeling TO kbirch WITH GRANT OPTION

Lad os forbinde os som kbirch og teste

CONNECT kbirch/kbirch

SELECT * FROM orablog.afdeling

Det går jo ganske fint. Hvis jeg vil undgå at prefixe tabellen med ejer.tabelnavn skal der oprettes et SYNONYM. Men lad os give rettighederne videre til RGOTZE, dog uden GRANT OPTION

 

GRANT SELECT ON orablog.afdeling TO rgotze

Vi forbinder os som RGOTZE

 

CONNECT rgotze/rgotze

SELECT * FROM orablog.afdeling

Det går sørme også godt. Nu vender vi tilbage til ORABLOG og tager rettighederne igen. KBIRCH har alligevel ikke fortjent det.

CONNECT orablog/orablog

REVOKE select ON afdeling FROM kbirch

Lad os vende tilbage til RGOTZE der også havde læseadgang.

CONNECT rgotze/rgotze

SELECT * FROM orablog.afdeling

Ups nu kan jeg ikke læse afdelingstabellen længere. Dvs. REVOKES rettighederne fra en bruger der har rettighed med GRANT OPTION, der vidergiver disse rettigheder, og REVOKES disse går det udover alle der efterfølgende har fået dem

Som i sikkert kan forestille jer, er det lidt af en administration, hvis man skulle sidde og fedte med alle disse objekter og rettigheder. De fleste i daglige produktions, test og udvikligs miljøer, gør derfor også brug af ROLLER.

Andet

ANSI-standard SQL supportere -- som en enkelt linie kommentar og /* */ for flere linie udkommentereing.

Eksempel:
SELECT * FROM lager -- Hent alle data fra tabellen lager.
/* 
Hentning af data fra lager tabellen 
Version 1.0 15 Maj 2006, Kbirch
*/
SELECT * FROM lager

Yderligere informationer

Oracle Databaser - Del I, General Database Struktur: memory, prosesser, filer