WebCie Databases

From A-Eskwiki
Jump to: navigation, search

De WebCie runt een paar databases waarin alle websitedata (dus ook ledendata, en transactiedata) staat. Op de vm-www, vm-www-debug en de vm-www-inwerking draaien allemaal een databaseserver en dezelfde databasestructuur (modulo updatevolgorde).

Gebruik in de code

De beste manier om kwerries op de databaas te doen is met QueryBuilder. Dit is een manier om volautomatisch SQL te genereren vanuit object-georienteerde code, zonder dat je bang hoeft te zijn voor injectie enzo.

De belangrijkste database is whoswho4, waar alle WhosWho4-objecten in staan. Dan zijn er nog benamite voor Benamite en boeken voor Bookweb.

Hoe een db-connectie opgezet wordt

In principe zouden dingen als databaseconnectie enzo al geregeld moeten zijn als je controllerfunctie aangeroepen wordt, maak hier dus alleen zorgen over als je ergens in space bezig bent.

Eerst wordt door space het bestand secret.php geladen, waarin gebruikersnaam en wachtwoord voor alle databaseusers staan. Op basis van de functie getLevel worden drie globalen $WSW4DB, $BWDB en $BMDB gemaakt door middel van dbpdo. Hun memberfunctie q(...) verstuurt een kwerrie naar de desbetreffende databaas.

Dit zorgt er ook voor dat inloggen en uitloggen (en eigenlijk alles wat de auth van de gebruiker aanpast) een refresh vereist voordat alles goed weergegeven kan worden!

Debugdatabase

Op de vm-www-debug wordt elke nacht een kopietje gemaakt van de livedatabases om op te kunnen debuggen. Deze db's heten test_$BLA waar $BLA staat voor de naam van de originele database.

Om precies te zijn, elke dag wordt scripts/cron/copy_db_to_test_db.sh boeken whoswho4 aangeroepen op de live-server (zolang bookweb nog wordt gebruikt met boeken). Elke maandag ochtend wordt de tabel benamite gekopieerd naar de debug database. Dit valt terug te vinden in de crontab onder scripts/cron. De aanroep is ssh root@vm-www-debug -i ~/.ssh/debug test_$db. Zoals je misschien vermoedt, is er geen commando genaamd test_$db op de vm-www-debug. In plaats daarvan staat in ~/.ssh/authorized_keys van root@vm-www-debug ingesteld dat bij ssh'en het commando mysql aangeroepen wordt in plaats van wat je van plan bent. Bij navraag bij degene die deze soepmes begaan heeft, antwoordde die: "zo is natoer".

Nieuwe database aanmaken

(De bedoeling is dat we het bij de huidige db's houden, maar misschien is er in de toekomst een goede reden!)

Als je een nieuwe WebCie-database wilt aanmaken, moet je ook het backupscript aanpassen. Daarin staan namelijk alle databases die gebackupt worden. Vergeet ook niet verbindingen te openen met de nieuwe db in space.

Databasestructuur aanpassen

De databasestructuur wordt in de repository opgeslagen door middel van Alembic. Deze bewaart de geschiedenis in de vorm van een gerichte graaf van revisie, een soort git maar dan voor databasestructuur. In tegenstelling tot git, begin je met de revisie een naam geven, en pas je daarna een bestand aan.

Voorbeeld: een veld toevoegen aan een tabel

Stel dat je in het Persoon-object een veld 'naamHuisdier' wilt toevoegen. De makkelijkste manier is om de dia aan te passen en dan in je terminal uit te voeren: manage revision 'veld naamHuisdier toevoegen in Persoon'. Dit zou volautomatisch een databaserevisie moeten maken en openen in je favoriete editor. (PRO-TIP: opent niet je favoriete editor? Stel $EDITOR in!) Vervolgens kun je de migratie naar hartelust aanpassen.

Ben je tevreden met de wijzigingen, dan sla je het bestand op, en doe je op de command line alembic upgrade head. Dit past je wijzigingen toe op de debugdatabase. Nu kun je de nieuwe databasestructuur gaan testen. LET OP: als je je revisie aan wil passen, doe eerst alembic downgrade -1 om die ongedaan te maken.

Na verloop van tijd is je feature hopelijk naar de livesite gemerged. Het livezetten gaat hetzelfde: doe op de vm-www weer alembic upgrade head (vergeet niet eerst source venv/bin/activate te doen om de commando's beschikbaar te maken!) en alle wijzigingen worden automatisch doorgevoerd. Als je wijzigingen op de livesite doorgevoerd hebt, moet je de Databasestructuur committen.

Hetzelfde, maar dan handmatig

In het handmatige geval doe je als eerste stap in de command line alembic revision -m 'veld naamHuisdier toevoegen in Persoon'. Alembic maakt dan een bestand aan in alembic/versions/ met een of andere naam. Open dat bestand in je favoriete teksteditor (PRO-TIP: stel $EDITOR in en doe alembic edit head). Nu kun je in het bestand operaties gaan toevoegen, zodat je bestand er ongeveer uitziet als:

# ... metadata voor Alembic

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('Persoon', sa.Column('naamHuisdier', sa.String))

def downgrade():
    op.drop_column('Persoon', 'naamHuisdier')

Het is belangrijk om upgrade en downgrade elkaars inverses te maken (op de structuur, want data kun je misschien wel kwijtraken), zodat het schakelen tussen branches goedgaat.

Het verdere verloop is net als bij automatische migraties.

In de database graven

Wil je iets in de database doen dat niet kan met de site? Dan kun je op [1] (of als je echt PHPMyAdmin nodig hebt [2]) (debugdb) en [3] (livedb) inloggen op localhost met gebruikersnaam root en het databasewachtwoord. Dit wachtwoord vind je in het bestand .my.cnf in de homedir van gebruiker webcie.

Migratie naar PostgreSQL

MySQL is stiekem best wel slecht, dus we willen graag in plaats daarvan PostgreSQL gebruiken. De migratie gaat ongeveer als volgt.

SSH naar de VM waar de oorspronkelijke database opstaat. Dan doe je ongeveer de volgende stappen (dit is voor de vm-www-debug). Eerst installeren we Postgres:

wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm
rpm -Uvh pgdg-centos96-9.6-3.noarch.rpm
sudo yum update                                                                
sudo yum install postgresql96-server postgresql96-contrib                      
sudo service postgresql-9.6 initdb                                             
sudo service postgresql-9.6 start                                              
sudo chkconfig postgresql-9.6 on                                               
sudo -i                                                                        
su postgres                                                                    
psql

Doe nu CREATE USER voor alle users in je secret.php.

Dan zijn we klaar om de koppeling met PHP te maken:

sudo yum install php-pgsql                                                        
sudo service httpd restart

Pas /var/lib/pgsql/9.6/data/pg_hba.conf aan zodat je auth method md5 gebruikt. (De bestanden /usr/pgsql/* slaan nergens op, negeer deze.)

sudo service postgresql-9.6 restart

Je zou nu moeten kunnen inloggen met psql --user $BLA -d postgres en ook op adminer (maar let op dat je een database kiest die bestaat).

Data migreren

Ruim eerst je DB een beetje op! (oftewel zorg dat je foreign keys kloppen, dat er geen datums zijn met jaar 0000, maand 00 of dag 00 enzo)

Dit is een volledige migratie, inclusief dbstructuur. Gebruik andere opties voor mysqldump om alleen data te verhuizen.

mysqldump --compatible=postgresql --user=root --password --default-character-set=utf8 test_whoswho4 > export.sql
python2.7 docs/db_converter.py export.sql pg-export.sql                    
psql -d test_whoswho4 -U root -f pg-export.sql

PRO-TIP: pas de relaties eerst handmatig aan, want die zijn niet echt goed over te zetten.

Vervolgens moeten we handmatig alle tabellen die in MySQL een autoincrement hadden, maar waar die kolom niet id heet, in PostgreSQL een sequencenummer geven. Dat ziet er ongeveer zo uit:

CREATE SEQUENCE IOUBon_bonID_seq;
SELECT setval('IOUBon_bonID_seq', max("bonID")) FROM "IOUBon";
ALTER TABLE "IOUBon" ALTER COLUMN "bonID" SET DEFAULT nextval('IOUBon_bonID_seq');

Heb je dat allemaal gedaan, dan moet je ook voor alle databaseusers rechten geven:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO god;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ingelogd;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dibstablet;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO mollie;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO gast;

Nu moeten we ook nog de rechten overnemen. In MySQL doe je SHOW GRANTS FOR '$USER'@'localhost' voor alle users in de db-wachtwoorden, pas de output hiervan aan tot PostgreSQL-syntax en voer die uit. (Dit is een hoop werk!)