Pagina1 van 1

Lazarus – Starten met Lazarus Free Pascal en SQLite

Lazarus – Starten met Lazarus Free Pascal en SQLite
   17

Lazarus, een op Free Pascal gebaseerd gratis en open source ontwikkelomgeving die veel weg heeft van Delphi, en cross-platform applicatie ontwikkeling toestaat, bestaat al een tijdje en begint meer en meer volwassen te worden. Zoals je misschien al weet heb ik het al een aantal keren gebruikt voor mijn eigene experimenten zoals bijvoorbeeld Name My TV Series om applicaties te ontwikkelen voor meerder platformen.

In dit artikel laat ik je zien hoe je SQLite kunt gebruiken in jouw eigen Lazarus applicaties. SQLite is een open source embedded SQL engine waarmee je effectief applicatie data kunt beheren middels SQL statements.

Voor dit artikel is een beetje SQL, Lazarus Pascal en databases handig.




Overzicht

Over SQLite

SQLite is een van de meest gebruikte embedded SQL engines in de wereld. Zonder het te weten gebruik je het waarschijnlijk vandaag in een van jouw apparaten thuis. Bekende bedrijven zoals Apple, Adobe, Microsoft, Skype, Mozilla, en Google gebruiken SQLite voor hun producten. Airbus gebruikt het zelfs in vliegtuigen (voor meer “beroemde” toepassingen zie: Well-Known SQLite users).

SQLite

SQLite

Typisch gebruik van SQLite zien we wanneer men een programma schrijft waarbij een redelijke hoeveelheid data opgeslagen dient te worden welke men met SQL statements wil uitlezen of bewerken. Vaak betreft het dan een relatief kleine database welke steeds maar door 1 gebruiker tegelijk benaderd wordt. Dit in contrast tot grotere database engines zoals MySQL (mijn favoriet) of FireBird. Beiden zijn overigens ook beschikbaar als een embedded versie maar vaak meer complex in gebruik, gebruiken vaak meer resources en zijn vaak niet altijd even eenvoudig voor de hand voor andere platformen.

SQLite kunnen we overigens op een enorme hoeveelheid verschillende platformen vinden; Android, Apple MacOS X, Apple iOS, Microsoft Windows, Linux, etc.

Ik heb in de loop van de jaren verschillende embedded database engines geprobeerd en kan alleen maar tot de conclusie komen dat SQLite de winnaar is als het aankomt op data opslag en SQL benadering voor een kleine compacte database op meerder platformen.

Je moet wel een paar dingen in de gaten houden.

Als eerste slaat SQLite alles op in een enkel bestand en is echt bedoelt voor single user gebruik – dus slechts een gebruiker (en 1 programma) hebben toegang tot de data.

Je moet, in tegenstelling tot de meer geavanceerde databases, ook niet vergeten dat ja iedere transactie handmatig moet “comitten” (toepassen) in jouw programma.

Installatie of distributie van SQLite met jouw Lazarus applicaties

SQLite moet eerst geïnstalleerd. Installeren is misschien iets te veel, SQLite betreft namelijk slechts een enkel bestand dat je mee moet kopiëren met jouw programma als we aan distributie gaan denken.

De eerste stappen m met SQLite aan de slag te gaan is net even anders voor ieder besturingssysteem, en ik moet toegeven dat het even puzzelen was voor ik het zelf allemaal uitgevonden had – vandaar dit artikel.

Starten met SQLite onder MacOS X

MacOS X komt standaard al met SQLite geïnstalleerd omdat Apple SQLite gebruikt voor programma’s zoals email, Safari, etc. Dus SQLite kan op iedere Mac gevonden worden.

Je hoeft alleen maar het TSQLite3Connection component van de SQLdb tab op je jouw hoofd-form te plaatsen.

Misschien niet echt nodig, maar het pad voor de library instellen kan zinvol zijn:


1
2
3
4
procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLiteLibraryName:='/usr/lib/libsqlite3.dylib';
end;

 

Lazarus en SQLite onder MacOSX - Het TSQLite3Connection component

Lazarus en SQLite onder MacOSX – Het TSQLite3Connection component

Merk op : De standaard locatie van de SQLite bibliotheek onder MacOS X is /usr/lib/sqlite3 en dit is wat Lazarus standaard gebruikt.

Starten met SQLite onder Linux (Ubuntu/Raspbian)

Ik gebruik (op dit moment) Ubuntu op een Virtuele Machine en heb gemerkt dat de volgende stappen het eenvoudigste zijn.

Op de computer waar je jouw programma op ontwikkelt (uitgaande van Lazarus 1.0.x of nieuwer) installeren we SQLite3 eerst met apt-get.


sudo apt-get install sqlite3 libsqlite3-dev

Als je dit gedaan hebt, vindt je de SQLite bibliotheek hier: /usr/lib/i386-linux-gnu/libsqlite3.so.0 (Note: for Raspberry Pi 2 model B /usr/lib/arm-linux-gnueabihf/libsqlite3.so.0)

Tip : Mocht je het hier niet vinden, probeer dan het “locate” statement om “libsqlite3.so.0” te vinden.

De volgende stap, en er zullen vast betere methoden bestaan, is het kopiëren van de bibliotheek naar de directory van mijn Lazarus project. Dit doe ik altijd om er zeker van de te zijn dat de juist bibliotheek gebruikt wordt en als voorbereiding voor het maken van een zip bestand voor distributie van mijn nieuwe programma op een later moment.

In onderstaand voorbeeld hebben we ons project opgeslagen in ~/Desktop/MyProject en in deze stap zorgen we er ook voor dat de naam van de bibliotheek libsqlite3.so wordt.

Vervolgens willen we natuurlijk ook dat Lazarus SQLite kan vinden in design time en we maken hiervoor een soft link (of te wel: Symbolic link) in de /usr/lib/ directory waar we het ook ‘libsqlite3.so’ gaan noemen omdat dit het bestand is waar Lazarus automatisch naar zoekt.

Merk op : In een aantal forums spreekt men over ‘/usr/lib64’ voor bepaalde 64-bit systemen, maar ik kan dit niet bevestigen.

Merk op : libsqlite3.so.0 is overigens een symbolic link naar het werkelijke bestand, vaak met een versie nummer er achter. B.v. libsqlite3.so.0.8.6. Als we dit bestand kopiëren, dan kopiëren we niet de link maar het werkelijke bestand.
Let onder Raspbian op dat het pad natuurlijk anders is, zie hierboven.


1
2
3
4
cd ~/Desktop/MyProject
cp /usr/lib/i386-linux-gnu/libsqlite3.so.0 ./libsqlite3.so

sudo ln -s /usr/lib/i386-linux-gnu/libsqlite3.so.0 /usr/lib/libsqlite3.so

Als we de bestanden gekopieerd hebben, kunnen we het TSQLite3Connection component van de SQLdb tab op ons hoof form plaatsen.

Lazarus en SQLite onder Linux (Ubuntu) - Het TSQLite3Connection component

Lazarus en SQLite onder Linux (Ubuntu) – Het TSQLite3Connection component

Nu moeten we natuurlijk niet vergeten in onze Lazarus code dat het de bibliotheek in onze project directory moeten gaan gebruiken. We doen dit in het onCreate event van het hoofd form als volgt:


1
2
3
4
procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLiteLibraryName:='./libsqlite3.so.0';
end;

Startent met SQLite onder Windows

Het gebruik van SQLite3 onder Windows is een kwestie van de juiste DLL kopiëren naar de project directory.

Je kunt de meest actuele DLL downloaden van de SQLite Download Pagina onder het kopje Precompiled Binaries for Windows (op dit moment, dat ik dit artikel schrijf, is het tweede bestand dat met ‘sqlite-dll’ begint, het bestand dat we moeten hebben).

Na het downloaden en unzippen kopiëren we sqlite3.dll  in onze project directory.

Om er voor te zorgen dat Lazarus het kan vinden gedurende design time, moeten we het sqlite3.dll bestand ook naar c:\windows\system  kopiëren.

Hierna kunnen we het TSQLite3Connection component van de SQLdb tab op het hoofd form plaatsen.

Lazarus en SQLite onder Windows - Het TSQLite3Connection component

Lazarus en SQLite onder Windows – Het TSQLite3Connection component

Net als onder Linux moeten we ons programma natuurlijk wel vertellen dat het de DLL in onze project directory moet gebruiken en we doen dat weer in het onCreate event van ons hoofd form:


1
2
3
4
procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLiteLibraryName:='sqlite3.dll';
end;

Cross-Platform Library opgeven

Uiteraard, als je een applicatie voor meerdere platformen schrijft, gebruik dan zoiets als dit:


1
2
3
4
5
6
7
8
9
10
11
12
{$IFDEF UNIX} // Linux
  {$IFNDEF DARWIN}
    SQLiteDefaultLibrary := 'libsqlite3.so';
  {$ENDIF}
  {$IFDEF DARWIN}
    SQLiteLibraryName:='/usr/lib/libsqlite3.dylib';
  {$ENDIF}
{$ENDIF}

{$IFDEF WINDOWS} // Windows
  SQLiteDefaultLibrary := 'sqlite3.dll';
{$ENDIF}

 

Het Database bestand …

Zoals eerder vermeld: SQLite gebruikt slecht een enkel bestand om de database in op te slaan en als je een nieuw project begint zul je dit bestand waarschijnlijk nog niet hebben …

Het mooie van het TSQLite3Connection component is dat als we een verbinding met het bestand willen opzetten (SQLite3Connection.Open), en het bestand nog niet bestaat, dat SQLite het bestand automatisch zal aanmaken.
Het nieuwe bestand is natuurlijk geheel leeg en we moeten natuurlijk nog steeds de tabellen en zo aanmaken (met SQLite3Connection.ExecuteDirect(SQLStatements); ) .

Er is echter ook een groot aantal hulpmiddelen beschikbaar voor het beheer van zo’n bestand.
Voor een overzicht van beschikbaar programma’s hier voor zie de SQLite Management Tools Pagina.
Er is een redelijke keuze en een aantal zijn gratis, en een aantal zijn commercieel, al dan niet met een gratis versie of een uitprobeer versie.

Valentina Studio – Cross platform en gratis

Valentina Studio vindt ik zelf een goede SQLite editor en is beschikbaar voor MacOS X, Linux en Windows in een commerciële versie en een gratis versie. In de gratis versie vond ik alleen maar het nadeel dat de SQL die je genereert met de visuele query bouwer niet kunt kopiëren. Je kunt Valentina downloaden van de Valentina Studio Homepage or additionally for Mac users in the Mac App Store..

Eenmaal geïnstalleerd en gestart voor de eerste keer, zal het om een registratie nummer vragen. Het is gratis en kost maar een paar klikken om een registratie nummer te krijgen.

Starten met SQLite in jouw programma

Ik ben vast niet de beste programmeur op de wereld en iedereen heeft zijn of haar eigen methode om te beginnen met een database. De volgende stappen zijn mijn normale proces:

  1. Bepaalde de data behoefte en ontwerp tabellen hiervoor.
  2. Maak een SQLite database met Valentina.
  3. Maak de benodigde tabellen (op basis van stap 1, in de database van stap 2).
  4. Bouw de Lazarus applicatie – met eventuele kleine wijzigingen in de database.
  5. Dump SQL statements voor het aanmaken van tabellen in de toekomst.
  6. Voeg de SQL van stap 5 toe aan het Lazarus programma zodat het de database kan aanmaken, mocht het nog niet bestaan.

Stap 1 – Data Behoefte en Tabellen ontwerpen

Dit is een stap die iedereen eigenlijk wel neemt: Het ontwerpen van tabellen voor de data behoeftes van jouw programma. Dit eerste ontwerp optimaliseer ik vaak als ik aan het programmeren ben in Lazarus.

Stap 2  – Aanmaken van een Database met Tabellen.

Over het algemeen gebruik ik hiervoor Valentina Studio.

Na downloaden, installeren en registreren van Valentina Studio, klik je in het menu “File New Database” en kies je “SQLite files” van het venster dat zich opent, gevolgd door een klik op de “OK” knop. Een nieuw venster opent zicht waar je bestandsnaam en bestandslocatie opgeeft.

Allemaal erg voor de hand liggend maar het kan zijn dat Valentina klaagt over de locatie omdat Valentine beperkt waar je database bestanden kunt aanmaken. Dit is overigens geen probleem hoor. Klik op de “Edit Preferences” knop en voeg de nieuwe locatie toe als een toegestane locatie.

Nu dat de database bestaat, kunnen we tabellen en zo gaan aanmaken.

Stap 3 – Het schrijven van jouw Lazarus programma

Als je begint met het schrijven van jouw Lazarus programma, MOET je Valentina sluiten! Zoals eerder vermeld: SQLite staat slecht 1 gebruiker/programma tegelijk toe voor wat betreft toegang tot de data en je kunt dus niet veel in jouw Lazarus programma doen als Valentina de database nog steeds gebruikt.

Dit is overigens ook het geval als je de database verbinding in design time aan hebt staan.
Als je jouw programma test zorg er dan voor dat de “Connected” property van TSQLite3Connection op “FALSE” staat.

Dit wil dus ook zeggen dat als jouw programma start, het de database verbinding moet openen voor je het kunt gebruiken, bijvoorbeeld in de onCreate event van het hoofd form. Onderstaand voorbeeld voor Windows, zie eerdere tekst voor wat betreft de SQLiteLibraryName variable die je moet opgeven voor Windows en Linux, maar overbodig is voor MacOS X.


1
2
3
4
5
procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLiteLibraryName:='sqlite3.dll';
  SQLite3Connection1.Open;
end;

We moeten ook een TSQLTransaction component op ons TForm plaatsen (staat ook op de SQLdb componenten tab) welke we met TSQLite3Connection component verbinden door de “Database” property op de TSQLite3Connection te zetten die we eerder geplaatst hadden.

Nu kunnen we de gebruikelijke Queries en Tabellen toevoegen (beiden gebruiken het TSQLQuery component) – voor tabellen moet je ook de “SQL” property invullen met een simpele query, bijvoorbeeld:


SELECT * FROM movies ORDER BY filename;

Vergeet niet dat data-aware componenten ook een TDataSource nodig hebben voor iedere Query en tabel, welke je kunt vinden op de “Data Access” componenten tab.

Stap 4 – Dump SQL voor de database tabellen

In deze stap moet jouw Lazarus programma al zo goed als klaar zijn.

Ik wil altijd graag data programma’s die ik schrijf een database aanmaken in het geval het database bestand niet niet bestaat. Dit maakt niet alleen de distributie kleiner (sneller downloaden) maar voorkomt ook problemen mocht het database bestand om de een of andere reden gewist zijn.

Valentina is hier een handig hulpmiddel voor: klik met rechts op de gewenste database in Valentina en kies “Create Dump” uit het menu.

In het venster dat zich opent, kies je “SQL” als “Dump kind“, en klik je op de “Next” knop.
In het volgende venster kies je “Structure only“, of “Structure and Records” als je de data ook nodig hebt, en klik je weer op de “Next” knop.
In het nu volgende venster selecteer ik NIETS en we klikken weer op “Next“.
Uiteindelijk krijgen we een venster waar we bestandsnaam en locatie op geven voor de SQL dump.

Het resulterende bestand kan nu met een tekst editor geopend worden zodat we de SQL code in ons Lazarus programma kunnen kopiëren.

Stap 5 – Code aanmaken voor een missend Database bestand

Nogmaals, ik ben beslist niet de beste programmeur op de wereld, maar dit is wat ik over het algemeen doe.

Als eerste maak ik een procedure die dit allemaal gaat afhandelen – laten we het “StartDatabase” noemen.

In deze procedure gaan we eerst instellen wat de te gebruiken SQLite bibliotheek is (indien nodig).

Vervolgens bepalen we of het database bestand al bestaat en slaan onze bevindingen op in een boolean variable (createTables in ons voorbeeld).

Vervolgens kijken we of de directory voor de database bestaat en maken het aan indien nodig.

Daarna openen we de database verbinding welke verbind met het bestaande bestand of een nieuw bestand aanmaakt (welke leeg is).

Eenmaal verbonden, en als het bestand voorheen niet bestond, voeren we alle SQL queries uit om tabellen en zo aan te maken.
Hiervoor gebruiken we de TSQLite3Connection.ExecuteDirect() functie zodat we geen extra componenten nodig hebben.

Database Locatie 

Je bent natuurlijk geheel vrij in het bepalen van de locatie van jouw database. Als je echter cross-platform werkt, dan moet je wel een paar beperking in de gaten houden.

MacOS X bijvoorbeeld staat het over het algemeen niet toe om bestanden te schrijven in de directory waar het programma staat wat heeft te maken met toegangsrechten en veiligheid.

Andere “bekende” locaties zoals de tijdelijke bestanden directories zijn weer niet erg betrouwbaar of consistent (Windows bijvoorbeeld wil tijdelijke bestanden in allerlei locaties plaatsen, afhankelijk van de Windows versie die je gebruikt).

Lazarus biedt hiervoor een soort oplossing met het cross platform “AppConfigDir” – dit is de locatie waar INI bestanden en registry bestanden opgeslagen worden.
Persoonlijk vindt ik dat een handige en geschikte locatie en deze locatie kan snel bepaald worden met de ingebouwde GetAppConfigDir() functie.

Hieronder zie je een voorbeeld hoe ik dat over het algemeen doe. Voor illustratie doeleinden gebruik ik een simpel tabelletje voor het bijhouden van een film verzameling.

De procedure “StartDatabase” is de eerste procedure die ik aanroep in het onCreate event van mijn hoofd TForm. Hierna kan ik TQSLQuery component(s) en andere database componenten gebruiken en aanroepen.

Het database bestand in dit voorbeeld plaats wordt in de applicatie configuratie directory geplaatst via GetAppConfiDir() – de parameter “false” geeft aan dat het niet de globale directory voor configuratie moet zijn, maar de directory specifiek voor jouw programma. Uiteraard moeten we ook het database bestand een naam geven, ‘mydatabase.db’ in dit geval.

Onder MacOS X vinden we deze directory hier: ~/.config/MyProject/mydatabase.db

Onder Windows XP hier: C:\Documents and Settings\<username>\Local Settings\Application Data\MyProject\mydatabase.db


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
procedure TForm1.StartDatabase;
var createTables:boolean;
begin
  {$IFDEF UNIX}  // Linux
    {$IFNDEF DARWIN}
      SQLiteLibraryName := './libsqlite3.so';
    {$ENDIF}
  {$ENDIF}

  {$IFDEF WINDOWS} // Windows
  SQLiteLibraryName := 'sqlite3.dll';
  {$ENDIF}

  SQLite3Connection.DatabaseName:=GetAppConfigDir(false) + 'mydatabase.db';

  if not DirectoryExists(GetAppConfigDir(false)) then  // Kijk of de directory bestaat
    MkDir(GetAppConfigDir(false));                // Maak het aan indien niet

  createTables := not FileExists(SQLite3Connection.DatabaseName); // geen bestand = maak nieuwe tabellen

  SQLite3Connection.Open;
  SQLTransaction1.Active:=true;

  if createTables then
    begin
      SQLite3Connection.ExecuteDirect('CREATE TABLE "movies"('+
                    ' "bitrate" Numeric,'+
                    ' "duration" DateTime,'+
                    ' "fileextension" Text,'+
                    ' "filename" Text NOT NULL,'+
                    ' "filesize" Numeric,'+
                    ' "filesizetext" Text,'+
                    ' "format_long" Text,'+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "path" Text);');

      SQLite3Connection.ExecuteDirect('CREATE INDEX "movies_filename_idx" ON "movies"( "filename" );');
      SQLite3Connection.ExecuteDirect('CREATE UNIQUE INDEX "movies_id_idx" ON "movies"( "id" );');

      SQLTransaction1.Commit;
    end;
end;

Zoals je ziet: het gebruik van SQLite hoeft niet moeilijk te zijn, zelfs niet als jet het allemaal cross-platform wilt doen.

Tips en Trucs

Na veel spelen met SQLite onder Lazarus, wil ik graag een aantal tips delen waar ik zelf best wel even moeite mee had toen ik er tegen aan liep.

Een TEXT geeft een (memo) weer in een DBGrid

Het kan voorkomen dat een text veld van een tabel of query gezien gaat worden als een ftMemo blob, en een DBGrid geeft dan i.p.v. de werkelijke tekst “(memo)” weer. Hoe lossen we dat op?

Optie 1 (beste) – Pas jouw SQL Query aan

Deze optie probeert een “Memo Blob” te laten overkomen als gewone tekst m.b.v. een zogenaamde “type cast”.
Merk op: het hangt van jouw SQL Database engine af of dit zal werken, maar bij mij werkte het met SQLite, en MySQL, en ik begrijp dat het ook bij PostgreSQL werkt.
Stel dit is jouw Query:


1
2
3
4
5
SELECT
  "Filename",
  "Date"
FROM
  "mytable";

En stel nu dat het veld “Filename” weer wordt gegeven als “(memo)”.
Dan kunnen we het probleem oplossen door het veld als een VARCHAR te “type casten” als volgt:


1
2
3
4
5
SELECT
  CAST( "Filename" AS VARCHAR) AS "Filename",
  "Date"
FROM
  "mytable";

Na dee wijziging zien we dus niet meer “(memo)”, maar de werkelijke tekst.

Optie 2 (als optie 1 faalt) – OnGetText Event

Let op: dit werkt alleen in runtime, dus niet gedurende design time en kost een beetje extra werk.

Als eerste moeten we velden definiëren voor onze TQuery.
Klik de TQuery met rechts aan en kies “Edit Fields”.
In het opkomende venster, klik op de “+” knop en voeg minstens het veld toe dat als “(memo)” wordt weergegeven.

Selecteer het veld dat verkeerd wordt weergegeven en in de object inspector zie je nu dat dit een TMemoField is.
Klik op de “Events” tab in de object inspector en dubbel klik op “OnGetText”.

Voeg in de nieuwe procedure de volgende code toe (aanname: TQuery = qrySomeQuery, veldnaam = “SomeField”, wat dus de TMemoField maakt met de naam “qrySomeQuerySomeField”):


1
2
aText := Sender.AsString;
DisplayText:=true;

Als hele functie zal dit er voor zorgen dat dus niet “(memo)” maar de werkelijke tekst wordt weergegeven:


1
2
3
4
5
procedure TForm1.qrySomeQuerySomeFieldGetText(Sender: TField; var aText: string; DisplayText: Boolean);
begin
  aText := Sender.AsString;
  DisplayText:=true;
end;

Resetten van Auto Increment Velden

Als we een tabel leeg maken of veel records verwijderen en deze tabel heeft Auto Increment velden, dan kan het wenselijk zijn dat de telling correct opnieuw bij 1 begint (of het laagst beschikbare nummer boven nul).

Het volgende SQL statement doet dit voor alle tabellen (helemaal veilig, records worden niet verwijderd of veranderd!):


1
DELETE FROM sqlite_sequence;

Je kunt het ook alleen maar doen voor een specifieke tabel:


1
DELETE FROM sqlite_sequence WHERE name='mijn_tabel';

 

Compact (Vacuum) jouw Database file

Deze is wat lastiger – het schoonmaken en verkleinen van jouw database bestand terwijl jouw programma draait. Met een aangepaste truc die ik in het Lazarus Forum vond.
Normaal krijg je een fout melding (Cannot vacuum from within a transaction) als je het “VACUUM” statement met een TSQLQuery component probeert uit te voeren.

Als eerste: de unit “sqlite3ds” aan de “Uses” clausule toevoegen.

Vervolgens de volgende procedure toevoegen en aanroepen in jouw programma.
Je geeft het de naam van jouw TSQLite3Connection (ik ga er vanuit dat je er maar 1 gebruikt, wat normaal is voor SQLite) en vervolgens sluit het de verbinding, voert een vacuum opdracht uit, en herstelt de verbinding (indien de TSQLite3Connection verbonden was toen je deze functie aanriep).


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
procedure TForm1.SQLiteVacuum(db:TSQLite3Connection);
var tmpDataset   : TSqlite3Dataset;
    wasConnected : boolean;
begin
  wasConnected := db.Connected;

  db.Close;
  repeat
  until not db.Connected;

  tmpDataset := TSqlite3Dataset.Create(nil);
  tmpDataset.FileName:=db.DatabaseName;
  tmpDataset.ExecSQL('VACUUM;');
  tmpDataset.Free;

  db.Connected:=wasConnected;
end;

Tekst sorteren als Nummers

Als we tekst sorteren in SQLite dan kun je tegen het zogenaamde “natural sort” probleem aanlopen.
Met een kleine truc is dit op te lossen voor nummers.
Als voorbeeld had ik een veld “video_mode” met waarden zoals “480p”, “720p”, “1080p” en “2160p”.
Als we normaal “ORDER BY video_mode” gebruiken dan zien we een onverwachte volgorde:

1080p
2160p
480p
720p

Als we het veld, ook al zit er tekst tussen, met een CAST naar INTEGER, in de ORDER BY, doen dan werkt sorteren wel als verwacht:

ORDER BY CAST(video_mode AS INTEGER)
of
ORDER BY CAST(video_mode AS DECIMAL)

Het resultaat:

480p
720p
1080p
2160p

Tekst sorteren, Hoofdletter ongevoelig

Stel we hebben een veld “title”, met de volgende waarden,

title
-----
 c
 a
 D
 Z
 B
 X

dan zal:

SELECT … ORDER BY title ASC;

onverwachts sorteren:

 B
 D
 X
 Z
 a
 c

Echter door de ORDER BY aan te passen naar:

SELECT … ORDER BY title COLLATE NOCASE ASC;

krijgen we de juiste volgorde:

 a
 B
 c
 D
 X
 Z

Meer Informatie

Meer informatie kun je vinden bij de volgende links welke zeker handig zullen zijn als je meer met Lazarus en SQLite wilt gaan doen (Engelstalig):

Ondersteun ons ...


Jouw ondersteuning wordt zeer gewaardeerd, en hoeft zelfs niets te kosten. Bijvoorbeeld door links naar ons te delen op social media, of andere websites.

Andere vormen kunnen ook gratis zijn (b.v. shoppen op Amazon).
Alle opbrengsten worden gebruikt voor web-hosting kosten, project hardware en software, koffie, etc.

Hartelijk dank voor wie al heeft bijgedragen!
Het is altijd geweldig om te zien hoe men mijn artikeltjes en applicaties weet te waarderen.

Merk op dat het klikken op affiliate links een kleine commissie voor ons kunnen genereren - dit wordt zeer gewaardeerd.

Reacties


Er zijn 17 reacties welke je hieronder kunt lezen.
Je kunt jouw eigen opmerkingen plaatsen m.b.v. dit formulier, of een reactie op een bestaande opmerking plaatsen door op de "Beantwoorden" knop te klikken.

  • 3 feb 2014 - 15:28 - Sebastiaan - Auteur: Reactie Link

    Ik heb overal gezocht naar een dergelijke uitleg, en dan vooral de OSX-uitleg i.c.m. sqlite!
    Heel erg bedankt voor deze uitleg. 

    Sebastiaan

    Beantwoorden

    Sebastiaan

    • 3 feb 2014 - 17:26 - hans Reactie Link

      Hi Sebastiaan,

      dank je wel voor de positieve feedback – ik heb het artikel geschreven om precies die reden, ik kon ook geen heldere uitleg vinden voor SQLite en Lazarus under OS X. Mooi om te horen dat je er wat aan had! 

      Groetjes,

      Hans

      Beantwoorden

      hans

  • 7 mrt 2014 - 8:14 - Anton van der Steen - Auteur: Reactie Link

    Een mooie heldere uitleg, echt goed beschreven, dank daar voor.

    Beantwoorden

    Anton van der Steen

    • 7 mrt 2014 - 16:51 - hans Reactie Link

      Dank je wel Anton voor de positieve feedback! 

      Altijd leuk om te horen dat een artikel gewaardeerd wordt 

      Beantwoorden

      hans

  • 18 mei 2015 - 22:30 - hengst Reactie Link

    We zijn al weer verder Hans, jij ook in de States?

    Ik loop nu tegen waarschijnlijk het eenvoudigste aan, maar kom er niet uit.

    De DB vullen gaat goed met de code:

        test :=555;

        test1:=’Nog een goede’;

        test2:=’HaarEnDier’;

        SQLite3Connection1.DatabaseName := ‘C:\demo\’+’LuktHet.db’;

        SQLite3Connection1.Connected := True;

        SQLQuery1.SQL.Clear;

        SQLQuery1.SQL.Add(‘insert into pedicures (NummerP, NaamPedicure, Plaats) values (:test, :test1, :test2)’);

        SQLQuery1.Params.paramByName(’test1′).AsString := test1;

        SQLQuery1.Params.paramByName(’test2′).AsString := test2;

        SQLQuery1.ExecSQL;

        SQLTransAction1.Commit;

        SQLQuery1.Close;  

    Maar nu het teruglezen en in één enkel zichtbaar veld zetten, zoals Label1.Caption. Maar deze code doet het niet:

         SQLite3Connection1.DatabaseName := ‘C:\demo\’+’LuktHet.db’;

         SQLite3Connection1.Connected := True;

         SQLQuery1.SQL.Clear;

         SQLQuery1.SQL.Add(‘SELECT * FROM Pedicures ORDER BY NaamPedicure DESC’);

         Label1.Caption := SQLQuery1.Params.paramByName(‘NaamPedicure’).AsString;

         SQLQuery1.Close;

     Wat doe ik fout?????

    Beantwoorden

    hengst

    • 20 mei 2015 - 22:09 - hans - Auteur: Reactie Link

      Hoi Gerard,

      ik zit midden in het inpak-proces, niet lollig, maar hopelijk ben ik volgende week weer in Nederland. 

      Het eerste wat me opvalt is dat je geen Database-aware componenten gebruikt. Onder de componenten “Data Controls” vindt je tegen hangers van veel gebruikte componenten. B.v. in plaats van TLabel gebruik je TDBText. In plaats van TEdit gebruik je TDBEdit. etc.

      Het gebruik van deze control is aan te bevelen boven de “standaard” controls.

      Sorry dat dit een kort antwoord is … je snapt ‘m al, ik heb het erg druk de komende dagen. Toch hoop ik dat dit je een beetje op weg helpt. 

      Beantwoorden

      hans

  • 22 mei 2015 - 19:54 - hengst Reactie Link

    Hans,

    Eindelijk gelukt en met niet zoveel code.

    Wellicht het volgende in je uitleg meenemen.

    Dus onShow wordt de database gecreëerd de en je kan er gelijk mee werken.

    Wellicht nog niet geheel soepel maar het werkt wel.

    Op Form 1 staan dus TDBEdit1 t/m 6 en de TDBNavegator en Tbutton voor opslaan wijzigingen>>>>>>>

    procedure TForm1.WieWatShow(Sender: TObject);

     var createTables:boolean;

    begin

       SQLiteLibraryName := ‘sqlite3.dll’;

       SQLite3Connection1.DatabaseName:=’F:\ProgDB\’+’LuktHet.db’;

       CreateTables := not FileExists (SQLite3Connection1.DatabaseName);

       SQLite3Connection1.Open;

       SQLTransaction1.Active:=True;

       If createTables then

       begin

         SQLite3Connection1.ExecuteDirect(‘CREATE TABLE “Pedicures”(‘+

     ‘ “NummerP” Integer NOT NULL PRIMARY KEY AUTOINCREMENT ,’+

     ‘ “NaamPedicure” VarChar(25) NOT NULL ,’+

     ‘ “Straat” VarChar(25) NOT NULL ,’+

     ‘ “HuisNummer” Integer ,’+

     ‘ “PostCode” VarChar(25) NOT NULL ,’+

     ‘ “Plaats” VarChar(25) NOT NULL); ‘);

         SQLite3Connection1.ExecuteDirect(‘CREATE INDEX “Pedicures_NaamPedicure_idx” ON “pedicures”( “NaamPedicure” );’);

     

       end;

       SQLTransaction1.Commit;

       Form1. Caption:=’SQLite database with DBNavigator’;

       SQLite3Connection1.DatabaseName:=’F:\ProgDB\’+’LuktHet.db’;

       SQLite3Connection1.Connected:=true;

       SQLTransaction1.Database:=SQLite3Connection1;

       SQLQuery1.Database:=SQLite3Connection1;

       SQLQuery1.SQL.text:=’Select * FROM Pedicures’;

       SQLQuery1.open;

       DataSource1.DataSet:=SQLQuery1;

       DBNavigator1.DataSource:=DataSource1;

       DBEdit1.DataSource:=Datasource1;

       DBEdit2.DataSource:=Datasource1;

       DBEdit3.DataSource:=Datasource1;

       DBEdit4.DataSource:=Datasource1;

       DBEdit5.DataSource:=Datasource1;

       DBEdit6.DataSource:=Datasource1;

       DBEdit1.DataField:=’NummerP’;

       DBEdit2.DataField:=’NaamPedicure’;

       DBEdit3.DataField:=’Straat’;

       DBEdit4.DataField:=’Plaats’;

       DBEdit5.DataField:=’PostCode’;

       DBEdit6.DataField:=’HuisNummer’;

    end;

      

    procedure TForm1.OpslaanClick(Sender: TObject);

    begin

       SQLQuery1.Edit;

       SQLQuery1.UpdateMode:=UpWhereChanged;

       SQLQuery1.ApplyUpdates;

       SQLTransaction1.Commit;

       SQLQuery1.Close;

       SQLQuery1.Open;

    end;    

    Beantwoorden

    hengst

    • 25 mei 2015 - 16:57 - hans - Auteur: Reactie Link

      Hoi Hengst,

      het “onShow” event is misschien niet de beste keuze. Misschien beter om dit te doen in het “onCreate” event van de Form te doen (kan het hier even niet testen). Het hoeft namelijk maar 1x gedaan te worden, en het onShow event kan meerdere keren afgevuurd worden. Misschien een beetje onnodig.

      Toewijzen van DataSource, Dataset en DataField, en instellen van “UpdateMode” en “Caption” doe je normaal gesproken in de Object Inspector en meestal niet in code (er zijn natuurlijk uitzonderingen, maar in een eenvoudige opstelling doe je het dus niet in code).

      Voor de rest ziet het er goed uit (en in code toewijzen maakt het als voorbeeld veel beter leesbaar natuurlijk).

      Beantwoorden

      hans

      • 26 mei 2015 - 17:31 - hengst Reactie Link

        De toewijzing van Datasource, Dataset en Field zijn in code gedaan omdat in gebruik maak van tweemaal Datasource en dus moest de rest ook. Vandaar. Het is wel duidelijker om in code te doen want dan zie je wat je doet.

        Beantwoorden

        hengst

  • 26 mei 2015 - 17:28 - hengst Reactie Link

    Hoi Hans,

    Weer heelhuids terug uit de States.

    Het was een hele klus maar het DB programma draait. Na de start was het datum nog een probleem op zich, maar daar ben ik ook uit. Tevens het maken van realtime statistieken gaat nu goed.

    Zal een stukje algemene code sturen voor de datum transfer tussen excel naar SQLite DB en het gebruik van het DateEdit in Lazarus.
    Er zal best wel een betere methode zijn, maar deze werkt nu heel goed.

    Het tweede stukje over het maken van statistieken.

    Hopelijk kunnen we andere starters ermee helpen.

    Hoor je weer,
    Gerard

     

    Beantwoorden

    hengst

  • 26 mei 2015 - 17:33 - hengst Reactie Link

    De code voor statistieken, waarbij LCount een integer is.

     SQLQuery3.Close;

      SQLQuery3.SQL.text:=’Select Count(*) FROM Klanten WHERE (((date(“now”)-date(geweest)) <= 3) AND ((date(“now”)-date(geweest)) > 1)) ‘;

      SQLQuery3.ExecSQL;

      SQLQuery3.Open;

      LCount:=SQLQuery3.Fields[0].AsInteger;

      Label5.Caption:=’Aantal tussen 1 en 2 jaar geleden geweest : ‘+IntToStr(LCount);

    Beantwoorden

    hengst

  • 26 mei 2015 - 17:38 - hengst Reactie Link

    Datums uit excel naar SQLite DB komen in verschillende formaten binnen:

    #-#-### of ##-#-#### of #-##-#### of ##-##-####.

    Om dat goed te krijgen in SQLite moet je ze steeds omzetten in ##-##-#### (door de functie MaakDatum).

    Dan steeds de volgende code waarbij de DBEdit#.text niet zichtbaar is, kan de de functies van DateEdit in SQLIte gebruiken en direct bewaren.

    Uiteraard kan je wel de TDBNavigator gebruiken en de wijzigingen opslaan.

        DateEdit1.text:=MaakDatum(DBEDit4.Text);

       DateEdit2.Text:=MaakDatum(DBEDit20.Text);     

       DBEdit4.Text:=DateEdit1.Text;

       DBEdit20.Text:=DateEdit2.Text;

       DBEdit4.Update;

       DBEdit20.Update; 

    Hopelijk extra voer voor een artikel.

    Beantwoorden

    hengst

    • 27 mei 2015 - 11:52 - hans - Auteur: Reactie Link

      Dank je wel Hengst!

      Voor wat de datum betreft: probeer zo veel mogelijk met “locale” te werken – dus met standaard functies. Het gerommel met datums (etc) is altijd een ellende, maar standaard funcies zouden dit zoveel mogelijk moeten kunnen afhandelen.

      Sorry voor de korte reactie – nog druk in de verhuis. 

      Beantwoorden

      hans

  • 13 jun 2015 - 21:08 - hengst Reactie Link

    Hoi Hans,

    Alles weer goed en terug uit de States?

    Zit nog steeds te proberen de datums goed te krijgen, wat niet lukt.

    En blijf foutmeldingen krijgen als ik met SQLite van query naar query ga. Lijkt wel of je niet tegelijk 2 queries vanuit 2 forms mag doen.

    Heb je ergens de standaard voor zoiets.

    onCreat database connected en geopend

    on Show form1 query1 van table1, save op button

    on Show form2 query2 van table2, save on exit form als er wijzigingen zijn

    Informatie van 1 en 2 kunnen wijzigingen veroorzaken over en weer.

    query1 zowel TDedits als Grid

    query2 alleen TDedits

    Alle info welkom

    Gerard

    Beantwoorden

    hengst

    • 14 jun 2015 - 12:28 - hans - Auteur: Reactie Link

      Hoi Gerard,

      ik denk dat jouw vragen nu weer een topic in het forum kunnen gebruiken .

      Datums zijn altijd een ellende, vandaar dat ik zo veel mogelijk standaard datum procedures gebruik om ze in het verwachte formaat te krijgen. Maar … ik werk het meeste met TDateTime, omdat query functies daar ook mee overweg kunnen. 

      Wat voor foutmeldingen krijg je?

      Ik zit nog te wachten op m’n spullen – dat gaat nog een week of 3 duren tenzij ik tegen problemen aan loop bij de douane.

      Helaas niet te veel spullen hier en wat issues met Internet, ik moet het via m’n mobiele telefoon doen, want ik moet nog 3 weken wachten op meneer KPN. En zelfs als die er is gaat het niet boven de 3Mb uit komen terwijl ik 10 Mb betaal. Om maar te zwijgen over het ontbreken van HDTV en Interactieve TV en het drastisch inzakken van Internet als ik TV ga kijken. Zou leuk zijn als iemand van de KPN een versterker zou plaatsen zodat ik wat betere snelheden kan krijgen. Of dat meneer Ziggo een kabeltje trekt. Maar ja, tegenwoordig praten we niet meer met mensen maar met regeltjes en computers … Lekker rustig buitenaf wonen heeft dus z’n nadelen.

      Beantwoorden

      hans

    • 14 jun 2015 - 12:29 - hans - Auteur: Reactie Link

      Misschien kan ik eens kijken naar de source om te zien waar het fout gaat (in het forum plaatsen, evt met wachtwoord, of even emailen).

      Beantwoorden

      hans

  • 24 apr 2016 - 0:45 - hans - Auteur: Reactie Link

    UPDATE:

    Ik heb er een paar tips & trucs bij gezet.

    Beantwoorden

    hans



Jouw Opmerking ...

Plaats hier geen grote bestanden (zoals source codes, log files of config files). Gebruik hiervoor het Forum.

Delen:
*
*
Laat me per email weten als er nieuwe reacties zijn.
       Je kunt jouw RSS reader gebruiken om reacties te volgen.


Tweaking4All gebruikt de gratis Gravatar dienst voor Avatar weergave.