Aan de slag met SQLite3 – Basiscommando’s

SQLite is een C bibliotheek die een SQL database engine implementeert. Het is een Relationeel Database Management Systeem (of RDBMS). De meeste SQL databases werken met het client/server model. Neem MySQL als voorbeeld. Om gegevens in een MySQL-database in te voeren en te ontvangen, moet u een verzoek naar de MySQL-server sturen, die u na ontvangst het juiste antwoord zal geven. In tegenstelling tot MySQL worden SQLite-databases rechtstreeks vanaf de schijf aangestuurd. Het is niet nodig om verzoeken naar de server te sturen.

Installatie

We zullen onze database bedienen met behulp van de sqlite31 Command Line Interface(CLI). We werken onder het Linux-platform. sqlite3 CLI voor MAC OS en Windows zijn precies hetzelfde, maar omwille van de consistentie is het raadzaam om Homestead Improved te installeren – een lichtgewicht VM die in 5 minuten kan worden uitgevoerd en die je computer vrijhoudt van extra onnodige software.

Om het op Homestead Improved (of een ander Debian gebaseerd OS zoals Ubuntu) te installeren, voert u het volgende uit:

sudo apt-get install sqlite3 libsqlite3-dev

Dit installeert sqlite3. Om het op andere platformen te installeren, volg hun officiële instructies. Nadat het geïnstalleerd is kunnen we de sessie starten. Open de Terminal/Command Line en voer het commando sqlite3 in. U moet iets van deze strekking zien :

De tweede regel vertelt u dat u .help moet invoeren voor instructies. Er wacht een prompt op u om een commando te typen. Dus ga je gang en voer .help in. Dit geeft je een lijst van Meta Commando’s en hun beschrijvingen.

Meta Commando’s

Meta Commando’s worden gebruikt om uitvoeropmaak voor tabellen te definiëren, databases te onderzoeken en voor andere administratieve operaties. Ze beginnen altijd met een punt. Zelfs .help is een meta commando. U kunt de lijst doornemen. Hier zijn er een paar die vaak van pas zullen komen:

Commando	Beschrijving
.show	Toont huidige instellingen voor diverse parameters
.databases	Stelt databasenamen en bestanden voor
.quit	Start sqlite3-programma
.tables	Toon huidige tabellen
.schema	Toon schema van tabel
.header	Toon of verberg de koptekst van de uitvoertabel
.mode	Selecteer de modus voor de uitvoertabel
.dump	Dump database in SQL tekst formaat

Standaard Commando’s

Laten we de standaard commando’s in sqlite3 eens doornemen. Meta commando’s worden gegeven om een database te onderzoeken. Standaard SQL commando’s worden gebruikt om een database te bewerken. Standaardcommando’s kunnen in drie groepen worden ingedeeld:

• Data Definition Language: Deze biedt de opslagstructuur en methoden om toegang te krijgen tot gegevens uit het databasesysteem.

  • CREATE
  • ALTER
  • DROP

• Data Manipulation Language: Hiermee kunnen gebruikers gegevens manipuleren (toevoegen/wijzigen/verwijderen).

  • INSERT
  • UPDATE
  • DELETE
• Data Query Language: Hiermee kunnen gebruikers de gewenste gegevens uit de database ophalen.
  • SELECT

Note: SQLite begrijpt nog veel meer standaardcommando’s, waarvan u hier een lijst kunt lezen. Aangezien deze tutorial een introductie op beginnersniveau is, behandelen we alleen de hierboven genoemde commando’s.

SQLite databases zijn cross-platform portable bestanden. Ze kunnen worden opgeslagen op verschillende opslagapparaten en kunnen worden overgebracht naar verschillende computers.

We zullen leren werken met sqlite3 met onze Comment Section database. Bijna elke website heeft tegenwoordig een commentaarsectie. Om een reactie te plaatsen, moet de gebruiker de volgende gegevens invoeren:

• Naam
• Email
• Website
• Commentaar

Van deze vier is alleen de URL van de website optioneel. We moeten ook een kolom definiëren die de commentaren nummert. Laten we die post_id noemen.

We kunnen de datatypes van de kolommen (attributen) als volgt definiëren:

Attribute	Datatype Required
post_id	INTEGER
naam	TEXT
email	TEXT
website_url	TEXT
commentaar	TEXT

U kunt de documentatie bekijken voor de verschillende Datatypes en Storage Classes die in SQLite3 beschikbaar zijn.

SQLite3 maakt gebruik van Manifest Typing2. De meeste andere database-engines, waaronder MySQL, gebruiken Static Typing.

Nu gaan we een database maken. Als u nog steeds in het sqlite3 programma zit, sluit het dan af door .quit in te voeren op de sqlite prompt. Geef dan het commando:

sqlite3 comment_section.db

Dit zal een database bestand comment_section.db aanmaken in de huidige werkdirectory.

Note: Als er geen bestandsnaam volgt op het commando, maakt sqlite3 automatisch een tijdelijke database aan. Dit is wat er gebeurde toen we het enkele commando sqlite3 voor de eerste keer invoerden.

CREATE TABLE

Om de commentaren op te slaan moeten we een tabel definiëren. Laten we hem noemen. Om hem te maken, voert u het volgende statement in:

CREATE TABLE comments ( post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL );

NOT NULL zorgt ervoor dat de bepaalde waarde in een record niet leeg wordt gelaten. PRIMARY KEY3 en AUTOINCREMENT4 werken het attribuut post_id uit.

Om te controleren of de tabel is aangemaakt, geeft u het meta commando .tables. Het zal de naam van de tabel weergeven .

Note: om het schema van de tabel te krijgen voert u .schema comments

Nu de tabel is gemaakt, moeten we gegevens invoeren.

INSERT ROWS

Voorstel dat een gebruiker een opmerking heeft ingevoerd met de volgende gegevens:

Name : Shivam MamgainEmail : [email protected] : shivammg.blogspot.comComment : Great tutorial for beginners. 

Om deze in te voegen, gebruiken we het INSERT commando.

INSERT INTO comments ( name, email, website_url, comment )VALUES ( 'Shivam Mamgain', '[email protected]','shivammg.blogspot.com', 'Great tutorial for beginners.' );

Ik heb de waarde voor post_id niet opgegeven, ook al is deze gedefinieerd als NOT NULL. Het is namelijk niet nodig omdat het een AUTOINCREMENT veld is.

Voeg nog wat rijen in de tabel in om te oefenen.

SELECT

We hebben geleerd om gegevens in de tabel in te voegen. Om gegevens op te halen gebruiken we het SELECT commando.

SELECT post_id, name, email, website_url, commentFROM comments;

Het bovenstaande statement kan ook geschreven worden als:

SELECT *FROM comments;

Dit zal alle rijen uit de tabel ophalen. De resulterende tabel kan er ondoorzichtig uitzien zonder headers en een goede scheiding tussen kolommen. Om dit te verhelpen, moeten we enkele parameters wijzigen.

Om kolomattributen weer te geven, voert u .show

Om kolomattributen weer te geven, voert u .headers ON in.
Om rijen in kolomstijl weer te geven, typ .mode column.
Voer het SELECT statement opnieuw in.

Note: Kies een weergave die bij u past. Voer .help in en zoek naar .mode. U krijgt de verschillende waarden te zien die .mode kan hebben.

UPDATE

Voorstel dat e-mail voor ‘Shivam Mamgain’ is gewijzigd in ‘[email protected]’ . We moeten de rij bijwerken. Voer het volgende statement uit:

UPDATE commentsSET email = '[email protected]'WHERE name = 'Shivam Mamgain';

Dit verandert het email attribuut voor de naam ‘Shivam Mamgain’ in ‘[email protected]’.

Note: Het naam attribuut is niet uniek, dus het bovenstaande statement kan meer dan één rij beïnvloeden. Voor elke naam = ‘Shivam Mamgain’ zal de email worden ingesteld op ‘[email protected]’. Om een bepaalde rij bij te werken gebruik je het post_id attribuut. Het is gedefinieerd als een PRIMARY KEY en zal altijd uniek zijn.

DELETE

Om rij(en) uit de tabel te verwijderen, kunnen we de WHERE voorwaarde gebruiken, net als in UPDATE.

Voorstel dat we een rij met post_id 9 moeten verwijderen. We kunnen het commando invoeren:

DELETE FROM commentsWHERE post_id = 9;

We moeten alle commentaren verwijderen die zijn geplaatst door ‘Bart Simpson’ en ‘Homer Simpson’. We kunnen de volgende query uitvoeren:

DELETE FROM commentsWHERE name = 'Bart Simpson' OR name = 'Homer Simpson';

ALTER

Nieuwe kolommen kunnen aan een tabel worden toegevoegd met ALTER. Op het web gebruiken de meeste mensen niet noodzakelijk hun echte naam. Ze werken meestal onder een alias, een gebruikersnaam. We moeten een kolom gebruikersnaam aan onze tabel toevoegen. De onderstaande query volbrengt deze taak:

ALTER TABLE commentsADD COLUMN username TEXT;

Dit maakt een kolom gebruikersnaam aan in en stelt het datatype in op TEXT. De waarde van de gebruikersnaam voor reeds ingevoegde rijen zal op NULL worden gezet.

Het ALTER commando wordt ook gebruikt om tabellen te hernoemen. Laten we de tabel veranderen in Coms.

ALTER TABLE commentsRENAME TO Coms;

DROP

Het verwijderen van een tabel betekent het verwijderen van de gehele tabel. De Coms tabel kan met de volgende query worden verwijderd:

DROP TABLE Coms;

Conclusie

SQLite3 biedt vele voordelen boven andere relationele databases. Enkele van zijn onderscheidende kenmerken kunt u hier lezen. De meeste PHP frameworks en vele andere web frameworks waaronder Django, Ruby on Rails en web2py hebben SQLite3 als hun standaard database. Omdat het zo licht is, verdient het de voorkeur voor lokale opslag in webbrowsers. Het wordt ook gebruikt als client-opslag voor veel besturingssystemen, waaronder Android en Windows Phone 8. Het is een van de meest gebruikte database engines.

In de tutorial hebben we interactie gehad met een SQLite database systeem. Er zijn GUI applicaties om hetzelfde te doen, zonder dat je commando’s hoeft te leren. Twee van dergelijke toepassingen zijn de DB Browser voor SQLite en SQLiteStudio. U kunt SQL ook online oefenen op SQL Fiddle.

Deze gids heeft de basisbeginselen behandeld om u op weg te helpen met SQLite3. Ik hoop dat het u zal verleiden om verder te gaan. U kunt het eenvoudig integreren met PHP met behulp van de gemeenschappelijke PDO-klasse.