Toon Van de Putte: Krijg meer inzicht in je data met SQLite en Sequel Pro
Goed, ik ga je het vertellen. Mijn stem klinkt een beetje gisteren feestje gedaan. Dus ik ga iets vertellen over SQLite en SQLPro en hoe dat gaat kunnen gebruiken om de data die in uw site zit of die in uw site moet terechtkomen of die van een andere site komt, zodat je daar meer inzichten in kunt krijgen. Heel veel analyse voor dat soort dingen gebeurt in Excel spreadsheets die aan elkaar gaan worden op allerlei manieren. Dat is superhandig voor data die heel makkelijk in twee dimensies te vatten is, maar dat is niet altijd. Je moet soms verbanden liggen en dingen met elkaar vergelijken en dan wordt dat heel snel heel vermoeiend. Er zijn ook tools in WordPress-plugins die je zelf kunt gaan bouwen waarbij je complexe data-relaties en grafieken genereert, maar dat is soms niet handig als je bijvoorbeeld alleen bij het opzetten van de site dat nodig hebt en daarna nooit meer, een beetje verloren moeite of als je hem er heel af en toe nodig hebt. Dus in dat gat zie ik dan SQLPro en SQLite en dan een extra toolsheer daarvoor kunt gebruiken om die SQLite sichtbaar te maken. Dat zijn allemaal open source tools. Ik zal beginnen met SQLPro. Dat is eigenlijk veel mensen kennen waarschijnlijk PHP MyAdmin, wat een webhost het manier is om mijn SQL data meest te beheren en dingen te halen. SQLPro is min of mere hetzelfde, maar aan puut desktop. Dat is een macOS-applicatie, dus helaas niet op Windows. En ook niet op Linux, denk ik. Dus je connecteert op puut database. Op tal van het manier, dat kan gewoon lokaal zijn, dat kan via een SSH-tunnel zijn, dat kan van alles zijn. En dan kun je bijvoorbeeld al hier, ik heb hier een WordPress database en ik zit in post meta en ik kan er al gaan filteren op een bepaalde meta-key en daar kan ik misschien al dingen van halen. Ik kan hier ook rechtstreeks een CSV gaan exporteren. Ik zal eerst even... Je kunt ook gewoon queries doen, zoals dat in PHP MyAdmin ook gaat. Die zijn color coded. Het toffe eraan is dat je die queries kunt opslaan. Lokaal voor die specifieke databaseconnectie of global in heel uw SQLPro-config. Dus dan kunnen jullie later ook terug gaan doen. Zelf praktisch.  Zonder dat je dus PHP MyAdmin op je server moet gaan zetten of het moet worden. Exporteren, heel handig uiteraard. Je kunt een CSV exporteren van de bepaalde query. Dus van alleen die column in die bepaalde relatie afhankelijk van uw SQL kennis kan heel ver gaan. En je kunt ook een SQL-dump doen. Dit is zo'n CSV dat je kunt exporteren bijvoorbeeld. Uw SQL-dump, daar kiest je dus alle tabellen of structure content dat je de drop table syntax erbij wilt. Je kunt allemaal kiezen. Dit is natuurlijk wel, als je hele WordPress database en zoals in mijn geval als een database met wel wat in en je doet heel die een dump hier, dat gaat heel lang duren. Dat doet uiteraard beter op je server en dan gewoon het foutje downloaden. Maar voor 1 of 2 tabellen werkt dat prima. En ook voor de structure is dat heel handig dat je een database structure moet exporteren. Voila. Dit vind ik het tofste van de twee SQLPro's een handige tool. Maar SQL Lite is SQL Lite een DB browser. De URL staan daaronder. Ik noem dat Excel on steroids. SQL Lite. SQL Lite is wat ze zeggen zelf, de meest gebruikte SQL database ter wereld. Dat zit in heel veel desktop applicaties. In Firefox gebruikt voor caching, Dropbox gebruikt daar allerlei configuratie op te slaan. Zijn allemaal SQL Lite databases. Tof er aan is, dat is filebase. Dus dat is één file, dat is uw database met alles in. Queries, data, views, indexen, alles wat je wilt. Het is vrij standaard compliant SQL. Als je SQL kent, kun je dat daar gebruiken. En de queries die je daar hebt geschreven, kun je ook in andere omgevingen gaan hergebruiken zonder al te veel bijzingen. Er zijn heel veel libraries voor. Python, PHP, dat bestaat allemaal. Dus je kunt daar alle kanten op. Als je dat fileje hebt, kunnen we dat allerlei manieren gaan aanspreken. En het is open source, future proof. Als je dat nu gebruikt, gaat dat weer in een vijf jaar waarschijnlijk nog open krijgen. Het is geen technologie die zomaar gaat verzwijnen. Het is SQL Lite. Het is geen full-fledged database. Het nadeel is, je hebt maar één user eigenlijk. Je connecteert erop, gedootingen en uw database is locked. Terwijl je dat doet, kan niemand anders iets doen.  Wat dat perfect is voor desktopapplicaties, maar iets minder voor een webapplicatie. Je hebt geen netwerk toegang zonder dat je daar extra shenanigans bijstekent. En je kunt dat ook niet tune op performance ofzo. Dat werkt niet. Je kunt dat niet optimaliseren. Dus voor een webapplicatie met 10.000 users, geen goede idee. Dan SQLite Browser. Ze noemen dat DB Browser het meest plachelijke naam voor een applicatie ooit, als je dan moet gaan googelen. Maar dat is dus een visueel omgeving waarin je dan dat soort databases kunt gaan beheren. Dat maakt het heel toegankelijk. Het is beeld voor developers en gewone gebruikers, wel iets waar de geavanceerde Excel gebruikers, dus iemand die toch weet wat dat aan het doen is. Maar je moet dus geen code kunnen schrijven om zo'n SQLite database te kunnen benaderen. Het voorbeeld dat ik gebruik is de leraarkaart. Dit is een leraarkaart voordeel. Daar heeft dus een naam en een locatie enzo. Ik wil weten hoe die verdeling zit doorheen de verschillende geografische regio's van België. Ik gebruik daarvoor de NIS-codes. Dat is iets anders dan postcodes. Het eerste cijfer van de NIS-codes is de provincie tussen het Arlande Cement en de gemeente. Dat betekent dat je basis van de NIS-codes perfect kunt zeggen in die gemeente liggen in die provincies. Die NIS-codes zitten niet in mijn WordPress-database. Maar ik heb wel een tabel gevonden waarin ik die allemaal vind. Ergens online, dat is met Franstale gemeenten, dat maakt niet uit. Maar ik heb dus verschillende kolommen met al die postcodes. Ik heb dat opgesplitst. Ik kan nu proberen van in Excel te gaan koppelen aan mijn website-daten. Maar ik ga dat met SQLite doen. Dit is dbbrowser, met een nieuwe database die volledig leeg is. En dan kunnen we hier bijvoorbeeld als AMOD gaan importeren. Dat is een CSV. Dus ik kies met een CSV file. En die wordt dan eigenlijk op basis van de kolommen dat daarin zit, wordt er een tabel aangemaakt. Krijg een preview. Hop, import complete. En ik heb al mijn kolommen en ik heb al mijn data. Klaar. En ik kan ook al direct gaan filteren als ik wil. Dan ga ik de NIS-codes importeren. Dus ik heb dat Excel-lijstje van de juist, waarin al die koles opgesplitst zijn. En dan heb ik dat op dezelfde manier beschikbaar.  Nu hebben we natuurlijk al die verschillende NIS-codes in verschillende kolommen. Om dat overzichtlijker te maken, willen we NIS-codes, postcodes, 2 kolommen alles onder elkaar. En dat kan ik doen. Ik heb hier een SQL statement. Ik heb al die verschillende postcodes ge-selecteerd, allemaal onder elkaar gezet en gecombineerd. Voilà. Dus ik kies kolom postcode 1, postcode 2, postcode 3, ik noem die allemaal naar postcode en ik zit die allemaal onder elkaar. En ik kan er gewoon één statement create view voorzetten. Je moet hier een name geven, uiteraard. Ja, het is niet zo heel leesbaar, maar wat dat kan doen, is hopelijk min of meer leesbaar. Voilà. Als ik daar run, dan is die view gemaakt. Je krijgt van onderhoek in je console, krijg je ook feedback. Ik heb in de view een soort virtuele tabel met al die gegeven zin. En dit kunnen bijvoorbeeld al, als developer, zou de dit al kunnen geven aan iemand en die kan daar dan verder zelf mee aan de slag. Als die zelf die dingen kan filteren of daar een CSV van kan exporteren, dan kan die daar zelf mee beginnen werken. Dus ik kan dat zeggen. Ik zal je een database maken met de view zien. Ik stuur een SQLite file door en als die dit open source portable tooltje heeft, dan kan die dat installeren. Kan die dat zelf gaan bekijken. En nu willen we dat ze wat laatstdag gaan doen, is de totale per regio gaan berekenen. Dus hier heb ik een lijstje van automatisch eigenlijk alle totale per in België provincie Antwerpen, West-Vlaanderen, Oost-Vlaanderen, Brussel enzovoort. Dus dat is een query die redelijk complex. Maar als je dit wilt raadplegen, hoefde dat dus niet te kennen, dan kun je gewoon dat file delen. Dus ik kan daar ook een view van. Ik kan even skipen naar het volgende. Stel dat we nu merken, we hebben ontbreken de postcodes. Ik heb hier postcodes... Voilà. Ik zie hier van boven allemaal postcodes die niet bestaan. Dus waar dat blijkbaar geen connectie voor gevonden is. Bijvoorbeeld 2140 daar. Ik heb... Ik wou het niet live doen. Voilà. 2140 is een ontbrekende postcode. Ik weet toevallig dat dat borger oud is. Dus die moet bij Antwerpen gezet worden. Dus dan ga ik kijken. Ik heb hier nu 244 in Antwerpen. En ik kan nu gaan kijken naar mijn NIS-codes. 2140. 