PostgreSQL Schema Management Basics

Funderar du på vad Postgresql-scheman är, varför de är viktiga och hur du kan använda scheman för att göra dina databasimplementationer mer robusta och underhållbara? Den här artikeln kommer att presentera grunderna för scheman i Postgresql och visa dig hur du skapar dem med några grundläggande exempel. Kommande artiklar kommer att fördjupa sig i exempel på hur man kan säkra och använda scheman för verkliga tillämpningar.

För det första, för att reda ut eventuell terminologisk förvirring, låt oss förstå att i Postgresql-världen är begreppet ”schema” kanske lite olyckligt överbelastat. I det bredare sammanhanget av relationella databashanteringssystem (RDBMS) kan termen ”schema” förstås som den övergripande logiska eller fysiska utformningen av databasen, dvs. definitionen av alla tabeller, kolumner, vyer och andra objekt som utgör databasdefinitionen. I det bredare sammanhanget kan ett schema uttryckas i ett ER-diagram (entitet-relation) eller i ett skript med DDL-angivelser (data definition language) som används för att instantiera applikationsdatabasen.

I Postgresql-världen kan begreppet ”schema” kanske bättre förstås som ett ”namnområde”. I Postgresql-systemtabellerna registreras scheman faktiskt i tabellkolumner som kallas ”name space”, vilket enligt mig är en mer korrekt terminologi. I praktiken tolkar jag varje gång jag ser ”schema” i Postgresql-sammanhang tyst om det till ”namnutrymme”.

Men du kanske frågar dig: ”Vad är ett namnutrymme?”. Generellt sett är ett namnområde ett ganska flexibelt sätt att organisera och identifiera information med hjälp av namn. Tänk dig till exempel två grannhushåll, Smiths, Alice och Bob, och Jones, Bob och Cathy (se figur 1). Om vi endast använde förnamn skulle det kunna bli förvirrande om vilken person vi menade när vi talade om Bob. Men genom att lägga till efternamnet, Smith eller Jones, identifierar vi entydigt vilken person vi menar.

Ofta är namnrymderier organiserade i en nischad hierarki. Detta möjliggör en effektiv klassificering av stora mängder information i en mycket finkornig struktur, som till exempel internetdomännamnssystemet. På den högsta nivån definierar ”.com”, ”.net”, ”.org”, ”.edu” osv. breda namnrymder inom vilka namn för specifika enheter registreras, så att till exempel ”severalnines.com” och ”postgresql.org” är unikt definierade. Men under var och en av dessa finns det ett antal gemensamma underdomäner som till exempel ”www”, ”mail” och ”ftp”, som i sig själva är duplicerande, men som inom respektive namnrymd är unika.

Postgresql-scheman tjänar samma syfte att organisera och identifiera, men till skillnad från det andra exemplet ovan kan Postgresql-scheman inte nästlas in i en hierarki. Även om en databas kan innehålla många scheman finns det alltid bara en nivå och därför måste schemanamnen vara unika inom en databas. Dessutom måste varje databas innehålla minst ett schema. När en ny databas installeras skapas ett standardschema med namnet ”public”. Innehållet i ett schema omfattar alla andra databasobjekt som tabeller, vyer, lagrade procedurer, triggers osv. För att visualisera detta hänvisas till figur 2, som visar en matrjosjka-docka-liknande nesting som visar var scheman passar in i strukturen för en Postgresql-databas.

Förutom att helt enkelt organisera databasobjekten i logiska grupper för att göra dem mer lätthanterliga, tjänar scheman det praktiska syftet att undvika namnkollisioner. Ett operativt paradigm innebär att man definierar ett schema för varje databasanvändare för att ge en viss grad av isolering, ett utrymme där användarna kan definiera sina egna tabeller och vyer utan att störa varandra. Ett annat tillvägagångssätt är att installera verktyg från tredje part eller databastillägg i enskilda scheman för att hålla alla relaterade komponenter logiskt samman. En senare artikel i den här serien kommer att beskriva ett nytt tillvägagångssätt för robust applikationsdesign, där man använder scheman som ett sätt att begränsa exponeringen av databasens fysiska design och i stället presentera ett användargränssnitt som löser syntetiska nycklar och underlättar långsiktigt underhåll och konfigurationshantering allteftersom systemkraven utvecklas.

Vi gör lite kod!

Det enklaste kommandot för att skapa ett schema i en databas är

CREATE SCHEMA hollywood;

Detta kommando kräver create-privilegier i databasen, och det nyskapade schemat ”hollywood” kommer att ägas av användaren som åberopar kommandot. Ett mer komplext upprop kan innehålla valfria element som anger en annan ägare och kan till och med innehålla DDL-anvisningar som instansierar databasobjekt inom schemat, allt i ett och samma kommando!

Det allmänna formatet är

CREATE SCHEMA schemaname ]

där ”användarnamn” är vem som kommer att äga schemat och ”schema_element” kan vara ett av vissa DDL-kommandon (se Postgresql-dokumentationen för närmare information). Superuser-privilegier krävs för att använda AUTHORIZATION-alternativet.

För att till exempel skapa ett schema som heter ”hollywood” och som innehåller en tabell som heter ”films” och en vy som heter ”winners” i ett enda kommando kan du göra

CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

Utvidare databasobjekt kan skapas direkt, till exempel kan en ytterligare tabell läggas till i schemat med

CREATE TABLE hollywood.actors (name text, dob date, gender text);

Bemärk i exemplet ovan att tabellnamnet föregås av schemanamnet. Detta krävs eftersom nya databasobjekt som standard, det vill säga utan uttrycklig schemaspecifikation, skapas inom det aktuella schemat, vilket vi kommer att ta upp nästa gång.

Har vi i det första exemplet med namnutrymmet ovan två personer som hette Bob och vi beskrev hur man kan särskilja dem genom att inkludera efternamnet. Men inom vart och ett av hushållen Smith och Jones separat förstår varje familj att ”Bob” hänvisar till den som hör till just det hushållet. Så till exempel inom ramen för respektive hushåll behöver Alice inte tilltala sin man som Bob Jones och Cathy behöver inte tilltala sin man som Bob Smith: de kan båda bara säga ”Bob”.

Postgresql:s nuvarande schema är ungefär som hushållet i exemplet ovan. Objekt i det aktuella schemat kan refereras okvalificerat, men för att referera till objekt med liknande namn i andra scheman krävs att namnet kvalificeras genom att schemanamnet prefixeras enligt ovan.

Det aktuella schemat härleds från konfigurationsparametern ”search_path”. Denna parameter lagrar en kommaseparerad lista med schemanamn och kan undersökas med kommandot

SHOW search_path;

eller sättas till ett nytt värde med

SET search_path TO schema ;

Det första schemanamnet i listan är det ”aktuella schemat” och det är där nya objekt skapas om det anges utan kvalificering av schemanamnet.

Den kommaseparerade listan med schemanamn tjänar också till att bestämma den sökordning som systemet använder för att lokalisera befintliga okvalificerade namngivna objekt. Om man till exempel återvänder till grannskapet Smith och Jones, skulle en paketleverans adresserad enbart till ”Bob” kräva att man besöker varje hushåll tills man hittar den första invånaren som heter ”Bob”. Observera att detta kanske inte är den avsedda mottagaren. Samma logik gäller för Postgresql. Systemet söker efter tabeller, vyer och andra objekt i scheman i den ordning som anges i search_path, och sedan används det först funna namnmatchningsobjektet. Schemakvalificerade namngivna objekt används direkt utan hänvisning till search_path.

I standardkonfigurationen avslöjar en sökning i konfigurationsvariabeln search_path detta värde

SHOW search_path; Search_path-------------- "$user", public

Systemet tolkar det första värdet som visas ovan som det aktuella inloggade användarnamnet och tillgodoser det användningsfall som nämndes tidigare, där varje användare tilldelas ett användarnamnsspecifikt schema för ett arbetsområde som är separat från andra användare. Om inget sådant schema med användarnamn har skapats ignoreras den posten och det ”offentliga” schemat blir det aktuella schemat där nya objekt skapas.

Tillbaka till vårt tidigare exempel med att skapa tabellen ”hollywood.actors”, om vi inte hade kvalificerat tabellnamnet med schemanamnet skulle tabellen ha skapats i det offentliga schemat. Om vi räknar med att skapa alla objekt inom ett visst schema kan det vara lämpligt att ställa in variabeln search_path som

SET search_path TO hollywood,public;

för att underlätta förkortningen av att skriva okvalificerade namn för att skapa eller få tillgång till databasobjekt.

Det finns också en systeminformationsfunktion som returnerar det aktuella schemat med en förfrågan

select current_schema();

Ifall det är fråga om att feta stavningen kan ägaren av ett schema ändra namnet, förutsatt att användaren också har create-privilegier för databasen, med hjälp av

ALTER SCHEMA old_name RENAME TO new_name;

Och slutligen för att ta bort ett schema från en databas, finns det ett drop-kommando

DROP SCHEMA schema_name;

DROP-kommandot kommer att misslyckas om schemat innehåller objekt, så de måste tas bort först, eller så kan du välja att rekursivt ta bort ett schema allt dess innehåll med CASCADE-alternativet

DROP SCHEMA schema_name CASCADE;

Dessa grunder kommer att hjälpa dig att komma igång med att förstå scheman!