PostgreSQL Schema Management Basics

Vraagt u zich af wat Postgresql schema’s zijn en waarom ze belangrijk zijn en hoe u schema’s kunt gebruiken om uw database-implementaties robuuster en onderhoudbaarder te maken? Dit artikel zal de basisprincipes van schema’s in Postgresql introduceren en je laten zien hoe je ze kunt maken met een aantal basisvoorbeelden. Toekomstige artikelen zullen dieper ingaan op voorbeelden van hoe je schema’s kunt beveiligen en gebruiken voor echte toepassingen.

Ten eerste, om mogelijke terminologische verwarring op te helderen, laten we begrijpen dat in de Postgresql wereld, de term “schema” misschien wat ongelukkig overbelast is. In de ruimere context van relationele databasebeheersystemen (RDBMS) kan de term “schema” worden begrepen als een verwijzing naar het algemene logische of fysische ontwerp van de database, m.a.w., de definitie van alle tabellen, kolommen, views, en andere objecten die de database-definitie vormen. In die bredere context kan een schema worden uitgedrukt in een entity-relationship (ER) diagram of een script van data definition language (DDL) statements die worden gebruikt om de applicatiedatabase te instantiëren.

In de Postgresql wereld kan de term “schema” misschien beter worden opgevat als een “namespace”. In feite, in de Postgresql systeemtabellen, worden schema’s opgenomen in tabel kolommen genaamd “name space”, die, IMHO, is meer accurate terminologie. In de praktijk, wanneer ik “schema” zie in de context van Postgresql, herinterpreteer ik het stilletjes als “name space”.

Maar je kunt je afvragen: “Wat is een name space?” In het algemeen is een naamruimte een vrij flexibele manier om informatie te organiseren en te identificeren op naam. Stel u bijvoorbeeld twee naburige huishoudens voor, de Smiths, Alice en Bob, en de Jones, Bob en Cathy (zie figuur 1). Als we alleen voornamen zouden gebruiken, zou het verwarrend kunnen zijn welke persoon we bedoelden als we het over Bob hadden. Maar door de achternaam, Smith of Jones, toe te voegen, identificeren we op een unieke manier welke persoon we bedoelen.

Vaak worden naamruimten georganiseerd in een geneste hiërarchie. Hierdoor kunnen enorme hoeveelheden informatie efficiënt worden geclassificeerd in een zeer fijnmazige structuur, zoals bijvoorbeeld het internetdomeinnaamsysteem. Op het hoogste niveau definiëren “.com”, “.net”, “.org”, “.edu”, enz. brede naamruimten waarbinnen namen voor specifieke entiteiten zijn geregistreerd, zodat bijvoorbeeld “severalnines.com” en “postgresql.org” op unieke wijze zijn gedefinieerd. Maar onder elk van deze zijn er een aantal gemeenschappelijke sub-domeinen zoals “www”, “mail”, en “ftp”, bijvoorbeeld, die op zichzelf duplicatief zijn, maar binnen de respectieve naamruimten uniek zijn.

Postgresql schema’s dienen dit zelfde doel van organiseren en identificeren, echter, in tegenstelling tot het tweede voorbeeld hierboven, kunnen Postgresql schema’s niet in een hiërarchie worden genest. Hoewel een database vele schema’s kan bevatten, is er altijd maar één niveau en dus moeten schemanamen binnen een database uniek zijn. Ook moet elke database ten minste één schema bevatten. Telkens wanneer een nieuwe database wordt aangemaakt, wordt een standaardschema met de naam “public” aangemaakt. De inhoud van een schema omvat alle andere database-objecten zoals tabellen, views, stored procedures, triggers, enz. Ter visualisatie, zie figuur 2, die een matroesjka pop-achtige nesting toont die laat zien waar schema’s passen in de structuur van een Postgresql database.

Naast het eenvoudig organiseren van database objecten in logische groepen om ze beter beheersbaar te maken, dienen schema’s het praktische doel van het vermijden van naambotsingen. Een van de operationele paradigma’s is het definiëren van een schema voor elke databasegebruiker om een zekere mate van isolatie te bieden, een ruimte waarin gebruikers hun eigen tabellen en views kunnen definiëren zonder elkaar te hinderen. Een andere benadering is het installeren van tools van derden of databankuitbreidingen in individuele schema’s om alle gerelateerde componenten logisch bij elkaar te houden. Een later artikel in deze serie zal een nieuwe benadering van robuust applicatie-ontwerp beschrijven, waarbij schema’s worden gebruikt als een middel van indirection om de blootstelling van het fysieke database-ontwerp te beperken en in plaats daarvan een gebruikersinterface te presenteren die synthetische sleutels oplost en onderhoud op lange termijn en configuratiebeheer vergemakkelijkt naarmate de systeemvereisten evolueren.

Laten we wat code doen!

Het eenvoudigste commando om een schema in een database te maken is

CREATE SCHEMA hollywood;

Dit commando vereist create privileges in de database, en het nieuw gemaakte schema “hollywood” zal eigendom zijn van de gebruiker die het commando uitvoert. Een complexere opdracht kan optionele elementen bevatten die een andere eigenaar specificeren, en kan zelfs DDL-statements bevatten die databankobjecten binnen het schema instantiëren, allemaal in één opdracht!

Het algemene formaat is

CREATE SCHEMA schemaname ]

waarbij “gebruikersnaam” de eigenaar van het schema is en “schema_element” een van bepaalde DDL-opdrachten kan zijn (raadpleeg de Postgresql-documentatie voor meer informatie). Superuser-privileges zijn nodig om de AUTHORIZATION-optie te gebruiken.

Om bijvoorbeeld een schema met de naam “hollywood” te maken met daarin een tabel met de naam “films” en een view met de naam “winnaars”, zou u in één commando kunnen doen

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;

Extra database-objecten kunnen vervolgens direct worden aangemaakt, bijvoorbeeld een extra tabel zou aan het schema worden toegevoegd met

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

Noteer in het bovenstaande voorbeeld het voorvoegsel van de tabelnaam met de schema-naam. Dit is nodig omdat standaard, dat wil zeggen zonder expliciete schema-specificatie, nieuwe database-objecten worden aangemaakt binnen het huidige schema, dat we hierna zullen behandelen.

Houd in gedachten hoe we in het eerste voorbeeld van de naamruimte hierboven twee personen hadden met de naam Bob, en we beschreven hoe we ze konden deconflicteren of onderscheiden door de achternaam op te nemen. Maar binnen elk van de huishoudens Smith en Jones afzonderlijk, begrijpt elke familie dat “Bob” verwijst naar degene die bij dat specifieke huishouden hoort. Dus bijvoorbeeld in de context van elk respectief huishouden, hoeft Alice haar man niet aan te spreken als Bob Jones, en Cathy hoeft haar man niet aan te spreken als Bob Smith: ze kunnen elk gewoon “Bob” zeggen.

Het Postgresql huidige schema is een beetje zoals het huishouden in het bovenstaande voorbeeld. Objecten in het huidige schema kunnen ongekwalificeerd worden gerefereerd, maar het verwijzen naar gelijknamige objecten in andere schema’s vereist het kwalificeren van de naam door de schema naam te prefixen zoals hierboven.

Het huidige schema wordt afgeleid van de “search_path” configuratie parameter. Deze parameter slaat een door komma’s gescheiden lijst van schemanamen op en kan worden onderzocht met het commando

SHOW search_path;

of op een nieuwe waarde worden gezet met

SET search_path TO schema ;

De eerste schemanaam in de lijst is het “huidige schema” en is waar nieuwe objecten worden aangemaakt indien gespecificeerd zonder schema naam kwalificatie.

De door komma’s gescheiden lijst van schemanamen dient ook om de zoekvolgorde te bepalen waarmee het systeem bestaande ongekwalificeerde benoemde objecten lokaliseert. Bijvoorbeeld, terug naar de Smith en Jones buurt, een pakketlevering alleen geadresseerd aan “Bob” zou een bezoek aan elk huishouden vereisen totdat de eerste bewoner met de naam “Bob” is gevonden. Merk op dat dit misschien niet de beoogde ontvanger is. Dezelfde logica geldt voor Postgresql. Het systeem zoekt naar tabellen, views, en andere objecten binnen schema’s in de volgorde van het zoek_pad, en dan wordt het eerst gevonden naam-gelijk object gebruikt. Schema-gekwalificeerde named objecten worden direct gebruikt zonder verwijzing naar het zoek_pad.

In de standaard configuratie, querying de search_path configuratie variabele onthult deze waarde

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

Het systeem interpreteert de eerste waarde hierboven als de huidige ingelogde gebruikersnaam en accommodeert de eerder genoemde use case waar elke gebruiker een gebruiker-naam schema krijgt toegewezen voor een werkruimte gescheiden van andere gebruikers. Als zo’n gebruikers-naam schema niet is aangemaakt, wordt die entry genegeerd en wordt het “public” schema het huidige schema waar nieuwe objecten worden aangemaakt.

Dus, terug naar ons eerdere voorbeeld van het aanmaken van de “hollywood.actors” tabel, als we de tabelnaam niet hadden gekwalificeerd met de schema naam, dan zou de tabel zijn aangemaakt in het public schema. Als we alle objecten binnen een specifiek schema willen maken, dan kan het handig zijn om de search_path variabele in te stellen als

SET search_path TO hollywood,public;

om de shorthand te vergemakkelijken van het typen van ongekwalificeerde namen om database objecten te maken of te benaderen.

Er is ook een systeeminformatie functie die het huidige schema teruggeeft met een query

select current_schema();

In geval van fat-fingering van de spelling, kan de eigenaar van een schema de naam veranderen, mits de gebruiker ook create privileges heeft voor de database, met het

ALTER SCHEMA old_name RENAME TO new_name;

En tenslotte, om een schema uit een database te verwijderen, is er een drop commando

DROP SCHEMA schema_name;

Het DROP commando zal falen als het schema objecten bevat, dus die moeten eerst verwijderd worden, of u kunt optioneel recursief een schema al zijn inhoud verwijderen met de CASCADE optie

DROP SCHEMA schema_name CASCADE;

Deze basisprincipes zullen u op weg helpen met het begrijpen van schema’s!