SQLite Trigger, weergaven en index met voorbeeld
Marcus Allen
Bij het dagelijks gebruik van SQLite, heeft u enkele administratieve hulpmiddelen voor uw database nodig. U kunt ze ook gebruiken om het uitvoeren van query's in de database efficiënter te maken door indexen te maken, of om deze beter herbruikbaar te maken door weergaven te maken.
SQLite Bekijk
Weergaven lijken sterk op tabellen. Maar Views zijn logische tabellen; ze worden niet fysiek opgeslagen zoals tabellen. Een weergave bestaat uit een select-instructie.
U kunt een weergave definiëren voor uw complexe query's en u kunt deze query's opnieuw gebruiken wanneer u maar wilt door de weergave rechtstreeks aan te roepen in plaats van de query's opnieuw te schrijven.
CREATE VIEW-instructie
Om een weergave voor een database te maken, kunt u de instructie CREATE VIEW gebruiken, gevolgd door de weergavenaam, en daarna de gewenste query plaatsen.
Voorbeeld: In het volgende voorbeeld maken we een weergave met de naam "AlleStudentenBekijk” in de voorbeelddatabase “ZelfstudiesSampleDB.db"als volgt:
Stap 1) Open Deze computer en navigeer naar de volgende map:C:\sqlite” en open vervolgens “sqlite3.exe"
Stap 2) Open de databank “ZelfstudiesSampleDB.db" door het volgende commando:
Stap 3) Hieronder volgt een basis-syntaxis van de sqlite3-opdracht om de weergave te maken
CREATE VIEW AllStudentsView
AS
SELECT
s.StudentId,
s.StudentName,
s.DateOfBirth,
d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Er mag geen uitvoer van de opdracht als volgt zijn:
Stap 4) Om er zeker van te zijn dat de weergave wordt gemaakt, kunt u de lijst met weergaven in de database selecteren door de volgende opdracht uit te voeren:
SELECT name FROM sqlite_master WHERE type = 'view';
Je zou het uitzicht moeten zien “AlleStudentenBekijk” wordt geretourneerd:
Stap 5) Nu onze weergave is gemaakt, kunt u deze als een normale tabel gebruiken, ongeveer als volgt:
SELECT * FROM AllStudentsView;
Met deze opdracht wordt de weergave 'AllStudents' opgevraagd en worden alle rijen geselecteerd, zoals weergegeven in de volgende schermafbeelding:
Tijdelijke weergaven
Tijdelijke weergaven zijn tijdelijk voor de huidige databaseverbinding die is gebruikt om deze te maken. Als u vervolgens de databaseverbinding sluit, worden alle tijdelijke weergaven automatisch verwijderd. Tijdelijke weergaven worden gemaakt met een van de volgende opdrachten:
- MAAK TEMPWEERGAVE, of
- CREËER TIJDELIJK UITZICHT.
Tijdelijke weergaven zijn handig als u tijdelijk wat bewerkingen wilt uitvoeren en het geen permanente weergave hoeft te zijn. U maakt dus gewoon een tijdelijke weergave en voert vervolgens uw verwerking uit met behulp van die weergave. Later wanneer u de verbinding met de database verbreekt, wordt deze automatisch verwijderd.
Voorbeeld:
In het volgende voorbeeld openen we een databaseverbinding en maken we vervolgens een tijdelijke weergave.
Daarna sluiten we die verbinding af en kijken we of de tijdelijke weergave nog bestaat of niet.
Stap 1) Open sqlite3.exe vanuit de map “C:\sqlite’, zoals eerder uitgelegd.
Stap 2) Open een verbinding met de database “ZelfstudiesSampleDB.db" door de volgende opdracht uit te voeren:
.open TutorialsSampleDB.db
Stap 3) Schrijf de volgende opdracht die een tijdelijke weergave zal creëren:AlleStudentenTijdelijkBekijk"
CREATE TEMP VIEW AllStudentsTempView
AS
SELECT
s.StudentId,
s.StudentName,
s.DateOfBirth,
d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Stap 4) Zorg ervoor dat de temperatuurweergave “AlleStudentenTijdelijkBekijk” wordt gemaakt door de volgende opdracht uit te voeren:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Stap 5) Sluit sqlite3.exe en open het opnieuw.
Stap 6) Open een verbinding met de database “ZelfstudiesSampleDB.db" door het volgende commando:
.open TutorialsSampleDB.db
Stap 7) Voer de volgende opdracht uit om de lijst met tijdelijke weergaven op te halen die in de database zijn gemaakt:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
U zou geen output moeten zien, omdat de temp view die we hebben gemaakt, is verwijderd toen we de databaseverbinding in de vorige stap sloten. Anders zou u, zolang u de verbinding met de database open houdt, de temporary view met data kunnen zien.
Opmerkingen:
- U kunt de instructies INSERT, DELETE of UPDATE niet gebruiken met views, u kunt alleen de opdracht “select from views” gebruiken, zoals weergegeven in stap 5 in het CREATE View-voorbeeld.
- Om een VIEW te verwijderen, kunt u de instructie “DROP VIEW” gebruiken:
DROP VIEW AllStudentsView;
Om er zeker van te zijn dat de weergave wordt verwijderd, kunt u de volgende opdracht uitvoeren. Deze geeft u een lijst met weergaven in de database:
SELECT name FROM sqlite_master WHERE type = 'view';
Er worden geen weergaven geretourneerd omdat de weergave is verwijderd, zoals hieronder:
SQLite Index
Als u een boek heeft en u wilt zoeken op een trefwoord voor dat boek. U zoekt op dat trefwoord in de index van het boek. Vervolgens navigeert u naar het paginanummer voor dat trefwoord om meer informatie over dat trefwoord te lezen.
Als er echter geen index of paginanummers op dat boek staan, scant u het hele boek van begin tot eind totdat u het trefwoord vindt waarnaar u op zoek bent. En dit is erg moeilijk, vooral als u een index hebt en een erg langzaam proces om naar een trefwoord te zoeken.
Indexen binnen SQLite (en hetzelfde concept geldt voor andere databasebeheersystemen ook) werkt op dezelfde manier als de indexen achterin de boeken.
Wanneer u zoekt naar enkele rijen in een SQLite tabel met zoekcriteria, SQLite zal in alle rijen van de tabel zoeken totdat het de rijen vindt waarnaar u zoekt die aan de zoekcriteria voldoen. En dat proces wordt erg traag als je grotere tafels hebt.
Indexen versnellen zoekopdrachten naar gegevens en helpen bij het ophalen van gegevens uit tabellen. Indexen worden gedefinieerd in de tabelkolommen.
Prestaties verbeteren met indexen:
Indexen kunnen de prestaties van het zoeken naar gegevens in een tabel verbeteren. Wanneer u een index op een kolom maakt, SQLite zal een gegevensstructuur voor die index creëren waarbij elke veldwaarde een verwijzing heeft naar de hele rij waar de waarde thuishoort.
Als u vervolgens een query met een zoekvoorwaarde uitvoert op een kolom die deel uitmaakt van een index, SQLite zal eerst de waarde op de index opzoeken. SQLite zal er niet de hele tabel op scannen. Vervolgens wordt de locatie gelezen waar de waarde voor de tabelrij verwijst. SQLite zal de rij op die locatie lokaliseren en ophalen.
Als de kolom waarnaar u zoekt echter geen deel uitmaakt van een index, SQLite voert een scan uit voor de kolomwaarden om de gegevens te vinden waarnaar u op zoek bent. Als er geen index is, zal dit doorgaans een langzamer proces zijn.
Stel je een boek voor zonder index en je moet naar een specifiek woord zoeken. U scant het hele boek, van de eerste tot de laatste pagina, op zoek naar dat woord. Als u echter een index van dat boek heeft, zoekt u eerst naar het woord erop. Zoek het paginanummer op waar het zich bevindt en navigeer er vervolgens naartoe. Dat zal veel sneller zijn dan het hele boek van kaft tot kaft scannen.
SQLite CREËER INDEX
Om een index op een kolom te maken, gebruikt u het commando CREATE INDEX. En je moet het als volgt definiëren:
- U moet de naam van de index opgeven na de opdracht CREATE INDEX.
- Na de naam van de index moet u het trefwoord “ON” plaatsen, gevolgd door de tabelnaam waarin de index zal worden aangemaakt.
- Vervolgens de lijst met kolomnamen die voor de index worden gebruikt.
- U kunt een van de volgende trefwoorden "ASC" of "DESC" gebruiken na een kolomnaam om een sorteervolgorde op te geven die wordt gebruikt om de indexgegevens te ordenen.
Voorbeeld:
In het volgende voorbeeld maken we een index “StudentenNaamIndex” op de studententafel in de “Leerlingen” database als volgt:
Stap 1) Navigeer naar de map “C:\sqlite’, zoals eerder uitgelegd.
Stap 2) Open sqlite3.exe.
Stap 3) Open de databank “ZelfstudiesSampleDB.db" door het volgende commando:
.open TutorialsSampleDB.db
Stap 4) Maak een nieuwe index aan “StudentNaamIndex"met behulp van de volgende opdracht:
CREATE INDEX StudentNameIndex ON Students(StudentName);
U zou hiervoor geen uitvoer moeten zien:
Stap 5) Om er zeker van te zijn dat de index is gemaakt, kunt u de volgende query uitvoeren. Deze geeft u een lijst met indexen die zijn gemaakt in de tabel Studenten:
PRAGMA index_list(Students);
Je zou de index moeten zien die we zojuist hebben gemaakt:
Opmerkingen:
- Indexen kunnen niet alleen op basis van kolommen worden gemaakt, maar ook op basis van expressies. Iets zoals dit:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);
De “OrderTotalIndex” zal gebaseerd zijn op de OrderId-kolom en ook op de vermenigvuldiging van de kolomwaarde Hoeveelheid en de kolomwaarde Prijs. Elke zoekopdracht naar “OrderId” en “Quantity*Price” zal dus efficiënt zijn, omdat de zoekopdracht de index gebruikt.
- Als u een WHERE-component in de CREATE INDEX-instructie hebt opgegeven, is de index een gedeeltelijke index. In dit geval staan er alleen vermeldingen in de index voor de rijen die voldoen aan de voorwaarden in de WHERE-component. Bijvoorbeeld in de volgende index:
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price) WHERE Quantity > 10000;
(In het bovenstaande voorbeeld zal de index een gedeeltelijke index zijn omdat er een WHERE-clausule is opgegeven. In dit geval wordt de index alleen toegepast op bestellingen met een hoeveelheidswaarde groter dan 10000. Houd er rekening mee dat deze index een gedeeltelijke index wordt genoemd. index vanwege de WHERE-clausule, niet de expressie die erop wordt gebruikt. U kunt de expressies echter wel gebruiken met normale indexen.)
- U kunt de instructie CREATE UNIQUE INDEX gebruiken in plaats van de CREATE INDEX om dubbele vermeldingen voor de kolommen te voorkomen, waardoor alle waarden voor de geïndexeerde kolom uniek zullen zijn.
- Om een index te verwijderen, gebruikt u de opdracht DROP INDEX gevolgd door de indexnaam die u wilt verwijderen.
SQLite Trigger
Inleiding tot SQLite Trigger
Triggers zijn automatische vooraf gedefinieerde bewerkingen die worden uitgevoerd wanneer een specifieke actie plaatsvindt op een databasetabel. Een trigger kan worden gedefinieerd om te worden geactiveerd wanneer een van de volgende acties plaatsvindt op een tabel:
- INSERT in een tabel.
- VERWIJDER rijen uit een tabel.
- UPDATE een van de tabelkolommen.
SQLite ondersteunt de trigger FOR EACH ROW, zodat de vooraf gedefinieerde bewerkingen in de trigger worden uitgevoerd voor alle rijen die betrokken zijn bij de acties die in de tabel zijn uitgevoerd (ongeacht of het invoegen, verwijderen of bijwerken betreft).
SQLite MAAK TRIGGER
Om een nieuwe TRIGGER te maken, kunt u de instructie CREATE TRIGGER als volgt gebruiken:
- Na CREATE TRIGGER moet u een triggernaam opgeven.
- Na de triggernaam moet u opgeven wanneer de triggernaam precies moet worden uitgevoerd. Je hebt drie opties:
- BEFORE – de trigger wordt uitgevoerd vóór de opgegeven INSERT-, UPDATE- of verwijderinstructie.
- Na – de trigger wordt uitgevoerd na de opgegeven INSERT-, UPDATE- of verwijderinstructie.
- INSTEAD OF – Het vervangt de actie die heeft plaatsgevonden en die de trigger heeft geactiveerd, met de instructie die is opgegeven in de TRIGGER. INSTEAD OF trigger is niet van toepassing op tabellen, alleen op weergaven.
Triggers worden alleen geactiveerd (afgevuurd), afhankelijk van het type instructie dat is opgegeven bij de opdracht create trigger. Bijvoorbeeld:
- De BEFORE INSERT-trigger wordt vóór elke insert-instructie geactiveerd (afgevuurd).
- De AFTER UPDATE-trigger wordt geactiveerd (geactiveerd) na elke update-instructie, ... enzovoort.
Binnen de trigger kunt u verwijzen naar de nieuw ingevoegde waarden met behulp van het trefwoord "new". U kunt ook verwijzen naar de verwijderde of bijgewerkte waarden met behulp van het trefwoord old. Als volgt:
- Binnen INSERT-triggers – er kan een nieuw trefwoord worden gebruikt.
- Binnen UPDATE-triggers kunnen nieuwe en oude trefwoorden worden gebruikt.
- Binnen DELETE-triggers kan een oud trefwoord worden gebruikt.
Voorbeeld
In het volgende zullen we een trigger creëren die wordt geactiveerd voordat een nieuwe student wordt toegevoegd aan de "Leerlingen" tafel.
Het logt de nieuw ingevoerde leerling in de tabel “StudentenLog" met een automatische tijdstempel voor de huidige datum en tijd waarop de insert-instructie plaatsvond. Als volgt:
Stap 1) Navigeer naar de map “C:\sqliteen voer sqlite3.exe uit.
Stap 2) Open de databank “ZelfstudiesSampleDB.db" door de volgende opdracht uit te voeren:
.open TutorialsSampleDB.db
Stap 3) creëer de trigger “InsertIntoStudentTriggerDoor de volgende opdracht uit te voeren:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
De functie "datum Tijd()" geeft u de huidige datum en tijdstempel waarop de invoeginstructie plaatsvond. Zodat we de invoegtransactie kunnen loggen met automatische tijdstempels die aan elke transactie worden toegevoegd.
De opdracht zou met succes moeten worden uitgevoerd en u krijgt geen uitvoer:
De trekker "InsertIntoStudentTrigger' wordt geactiveerd elke keer dat u een nieuwe leerling in de leerlingentabel plaatst. De "nieuweHet trefwoord verwijst naar de waarden die worden ingevoegd. Bijvoorbeeld de “nieuw.StudentId” is de student-ID die wordt ingevoegd.
Nu gaan we testen hoe de trigger zich gedraagt als we een nieuwe leerling invoegen.
Stap 4) Schrijf de volgende opdracht om een nieuwe student in de tabel 'studenten' in te voegen:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Stap 5) Schrijf de volgende opdracht die alle rijen uit de "StudentenLog" tafel:
SELECT * FROM StudentsLog;
Er zou een nieuwe rij moeten verschijnen voor de nieuwe leerling die we zojuist hebben ingevoegd:
Deze rij werd door de trigger ingevoegd voordat de nieuwe leerling met id 11 werd ingevoegd.
In dit voorbeeld gebruikten we de trigger “ InsertIntoStudentTrigger ” die we hebben gemaakt om eventuele invoegtransacties in de tabel te loggen “StudentenLogautomatisch. Op dezelfde manier kunt u elke update registreren of verklaringen verwijderen.
Onbedoelde updates voorkomen met triggers:
Met behulp van BEFORE UPDATE-triggers voor een tabel kunt u de update-instructies voor een kolom op basis van een expressie voorkomen.
Voorbeeld
In het volgende voorbeeld voorkomen we dat een update-instructie de kolom ‘studentname’ in de tabel Students bijwerkt:
Stap 1) Navigeer naar de map “C:\sqliteen voer sqlite3.exe uit.
Stap 2) Open de databank “ZelfstudiesSampleDB.db" door de volgende opdracht uit te voeren:
.open TutorialsSampleDB.db
Stap 3) Maak een nieuwe trigger “voorkomenUpdateStudentName" op de tafel "Leerlingen"door de volgende opdracht uit te voeren
CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'You cannot update studentname');
END;
De "VERHOGEN" commando zal een fout opleveren met een foutmelding " Je kunt de studentnaam niet bijwerken “, en dan zal het voorkomen dat de update-instructie wordt uitgevoerd.
Nu zullen we verifiëren dat de trigger goed werkt en dat deze elke update voor de kolom studentnaam verhindert.
Stap 4) Voer de volgende update-opdracht uit, die de naam van de student zal bijwerken:krik" zijn "Jack1'.
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
U zou de foutmelding moeten krijgen die we bij de trigger hebben gespecificeerd, waarin staat dat “Je kunt de studentnaam niet bijwerken"als volgt:
Stap 5) Voer de volgende opdracht uit om de lijst met studentennamen uit de tabel 'studenten' te selecteren.
SELECT StudentName FROM Students;
Je zou moeten zien dat de studentennaam “Jack” nog steeds hetzelfde is en niet verandert:
Samenvatting
Views, indexen en triggers zijn zeer krachtige hulpmiddelen voor het beheren van een SQLite databank. U kunt de bewerkingen voor het wijzigen van gegevens bijhouden wanneer ze in een tabel plaatsvinden. U kunt ook de bewerking voor het ophalen van databasegegevens optimaliseren door indexen te maken.
