Old vs New: Approaches in Managing PostgreSQL Schemas

At forstå det grundlæggende i skemahåndtering er afgørende for opbygning og vedligeholdelse af en effektiv PostgreSQL-database. I denne artikel vil vi se på den traditionelle måde at administrere et Postgres-skema på og på en nyere, mere effektiv måde at gøre det visuelt på, uden at du behøver at skrive en linje kode.

Hvad er et PostgreSQL-skema?

Først skal vi for at lægge grundlaget for artiklen afklare noget terminologi. I Postgres omtales skemaet også som et namespace. Navneområdet kan være forbundet med et familienavn. Det bruges til at identificere og skelne mellem visse objekter i databasen (tabeller, visninger, kolonner osv.). Det er ikke tilladt at oprette to tabeller med det samme navn i ét skema, men du kan gøre det i to forskellige skemaer. Vi kan f.eks. have to tabeller, der begge hedder table1, som findes i public- og postgres-skemaer.

Hvorfor bruge skemaer?

Skemaer er meget nyttige til at organisere databaseobjekter i logiske grupper og undgå navnekollisioner. Udover dette bruges skemaer ofte for at give forskellige brugere mulighed for at arbejde med databasen uden at forstyrre hinanden. Et almindeligt eksempel er, når hver databasebruger arbejder på sit eget skema, uden at forstyrre andre brugere og uden at undgå konflikter.

Den klassiske måde at administrere PostgreSQL-skemaer på

Alle nedenstående forespørgsler vil blive udført inde fra PostgreSQL-shell’en.

Opretning af et skema

Når du opretter en ny database i Postgres, er standardskemaet public. Et nyt skema kan oprettes ved at udføre den næste forespørgsel:

CREATE SCHEMA schema_1;

Hvor der tilføjes nogle tabeller til den, vil jeg forklare to vigtige begreber: kvalificerede og ukvalificerede navne.

• Et kvalificeret navn er skemanavnet og tabelnavnet adskilt af en prik. Dette vil angive det skema, som vi ønsker at oprette vores tabel i:

xxxxxxxxxx

CREATE TABLE schema_name.table_name (...);

• Et ukvalificeret navn består kun af tabellens navn. Dette vil oprette tabellen i den valgte database, der som standard er offentlig. Dette kan ændres via search_path, men det vil vi beskrive nærmere senere. Et eksempel på ukvalificeret navngivning er:

xxxxxxxxxx

CREATE TABLE table_name (...);

Spalterne i tabellerne vil blive defineret inden for parenteserne fra ovenstående forespørgsler (…).

For at oprette en ny tabel i vores nye skema, vil vi udføre:

xxxxxxxxxx

CREATE TABLE schema_1.persons (name text, age int);

For at droppe skemaet, har vi to muligheder. Hvis skemaet er tomt (indeholder ikke nogen tabel, visning eller andre objekter), kan vi udføre:

xxxxxxxxxx

DROP SCHEMA schema_1;

Hvis skemaet indeholder databaseobjekter, indsætter vi kaskade-kommandoen:

xxxxxxxxxx

DROP SCHEMA schema_1 CASCADE;

I PostgreSQL er det også muligt at oprette et skema, der ejes af en anden bruger, med:

xxxxxxxxxx

CREATE SCHEMA schema_name AUTHORIZATION username;

Søgningssti

Ved udførelse af en kommando med et ukvalificeret navn, følger Postgres en søgesti for at bestemme, hvilke skemaer der skal bruges. Som standard er søgestien indstillet til det offentlige skema. Hvis du vil se det, skal du udføre:

xxxxxxxxxx

SHOW search_path;

Hvis der ikke er ændret noget i din database, denne forespørgsel bør give det næste resultat:

xxxxxxxxxx

 search_path

--------------

 "$user",public

Søge_stien kan ændres, så systemet automatisk vælger et andet skema, hvis du bruger et ukvalificeret navn. Det første skema i søgestien kaldes aktuelt skema. Jeg vil f.eks. indstille schema_1 som det aktuelle skema:

xxxxxxxxxx

SET search_path TO schema_1,public;

Den næste forespørgsel vil bruge et ukvalificeret navn til at oprette en tabel. Dette vil automatisk oprette det i skema_1:

xxxxxxxxxx

CREATE TABLE address (city text, street text, number int); 

Den nye vej: Administrer uden kode!

Der er en enklere måde at udføre alle skemaadministrationsopgaver på, uden at du behøver at skrive en eneste linje kode. Ved hjælp af DbSchema kan du udføre alle ovenstående forespørgsler fra en intuitiv GUI med blot et par klik. Det tager kun et par sekunder at oprette forbindelse til databasen. Fra starten kan du vælge hvilket skema du vil arbejde på.

Det valgte skema eller de valgte skemaer vil blive reverse-engineered af DbSchema og vist i layoutet.

For at oprette et nyt skema skal du blot højreklikke på skemamappen i menuen til venstre og vælge Opret skema.

For at oprette en ny tabel i skemaet skal du højreklikke på layoutet og vælge Opret tabel.

Skemaet kan slettes ved at højreklikke på dets navn i menuen til venstre.

For at tilføje et andet skema fra databasen skal du vælge Refresh From Database (Opdater fra database).

Med DbSchema behøver du ikke at bruge syntaksen show_path, fordi du kan oprette tabellerne direkte i layoutet. Et layout kan sammenlignes med et tegnebræt, hvor du kan tilføje tabellerne og redigere dem. Hvert layout har ét skema tilknyttet, så hvis du er på layoutet schema_1, vil tabellerne automatisk blive oprettet der.

Arbejde offline

DbSchema gemmer et lokalt aftryk af skemaet i en lokal projektfil. Det betyder, at projektfilen kan åbnes uden databaseforbindelse (offline). Mens du er offline, kan du udføre alle de handlinger, der er præsenteret ovenfor, og meget mere, men uden data. Når du har genetableret forbindelsen til databasen, kan du sammenligne projektfilen med databasen og vælge, hvilke handlinger du vil beholde eller droppe.

Det samme kan gøres mellem to forskellige versioner af den samme projektfil. Hvis du f.eks. arbejder i et team, kan det være tilfældet, at der er flere skemaer (produktion, test, udvikling) med hver sin projektfil. Hvis en ændring dukker op i udvikling, og du ønsker at implementere den over de to andre skemaer, kan du blot sammenligne og synkronisere de to projektfiler.

Konklusion

Forståelse af de ovenfor nævnte begreber vil hjælpe dig til nemt at administrere dine PostgreSQL-skemaer. Brug af en visuel designer som DbSchema vil gøre dit arbejde endnu lettere ved at gøre det hele visuelt, uden at du behøver at skrive en eneste linje kode.