franGiPane - architecture de la base de données (discussion)

Cette page contient les discussions/réflexions concernant SqlSchema.

Contraintes de validité des données

Jeu de test ?

Champs protégés en écriture

Certains champs doivent être protégés en écriture :

• dns_records.{hostname,type,domain_id}
• tous ceux qui correspondent à qqch dans le système de fichiers :
  • domains.name
  • web_sites.{name,dns_record_id}
• web_masters.{login,web_site_id}
• PRIMARY KEY et FOREIGN KEY
• mail_addresses.{username,domain}

TODO : reste à le faire pour la fin du schéma (fait pour domains, dns_records, web_sites, web_masters, mail_*)

NB: on veut pouvoir changer domains.type, les méthodes devront gérer les complexes implications de la chose. Il s'agit d'une migration, quand même.

Valeurs spéciales à interdire

• web_masters.login = logins shell existants sur la machine : mettre le check dans la méthode de création de webmaster, et faire gaffe lorsqu'on crée un nouveau login shell
• - dans web_masters.login ?
• web_masters.login ne peut commencer par un chiffre
• interdire les sous-domains gérés hors-fGP, s'il y en a (wiki.boum.org, etc.) ; m'enfin, en vrai, mieux vaut les mettre ds fGP, hein.
• adresses mail : pour boum.org, celles gérées hors-fGP (càd, au moins, ceux de /etc/aliases)
• autres: admin, root, etc. TODO

Fait pour l'existant, à faire pour les nouvelles tables

• gaffe à la casse !
• domains valides et en minuscules
• NULL autorisé ou pas
• tester nullité des arguments des functions de check, car NULL comparé à n'importe quoi renvoie true, argh.

Astuce : dans une fonction SQL, à l'intérieur d'une regexp, dc entre '' et '', pour escaper un caractère spécial (comme un bête point) : \\\\.

Checks spécifiques à voir

• adresses IP (is_a_valid_ip) : limiter à une/des classes d'IP ?
• compléter la liste des TLDs qu'on connait
• finir is_a_valid_dns_content (PTR)

Et si besoin de trucs un peu plus subtils...

Triggers

• file:///usr/share/doc/postgresql-doc/html/sql-createtrigger.html
• file:///usr/share/doc/postgresql-doc/html/trigger-example.html

Un index peut servir à ajouter des contraintes

• file:///usr/share/doc/postgresql-doc/html/indexes-expressional.html

Contraintes de table référençant d'autres tables

Gaffe, les contraintes sur une table ne sont vérifiées que lorsqu'un enregistrement y est inséré ou modifié ; or nous avons des check() référençant une autre table, ce que PostgreSQL n'autorise normalement pas, mais vu que nous utilisons des fonctions dans nos check(), tout est possible. Ça implique lorsqu'un enregistrement référencé par ce type de contraintes est modifié, ces contraintes ne sont pas vérifiées.

Par exemple, si on modifie la table domains, les contraintes de la table dns_records dépendant de la table domains ne sont pas vérifiées ; les champs ainsi référencés sont :

• domains.type : est_un_domaine_externe et ce qui l'utilise (i.e. is_a_valid_dns_content et est_un_mx_valide)
• dns_records.{type,domain_id} : dns_record_ok_for_website
• domains.fqdn : dns_record_ok_for_website
• dns_records.content : dns_record_ok_for_website

Comment force-t-on ces contraintes, alors ? Ou mieux, comment fait-on pour s'en passer ?

• domains.fqdn : la RULE forbidden_updates empêche qu'il soit modifié
• dns_records.{type,domain_id} : la RULE forbidden_updates empêche qu'ils soient modifiés.
• domains.type
  • is_a_valid_dns_content : pour ne plus utiliser est_un_domaine_externe, on n'exclut pas des vérifications les enregistrements DNS externes. Yaka y mettre un content valide, na.
  • est_un_mx_valide : vu que c'est la seule contrainte restante sur dns_records référençant domains, on la bouge dans les méthodes, tant pis ; TODO dans les méthodes.
• dns_records.content (dns_record_ok_for_website) ; TODO : dans les méthodes qui modifient dns_records.content, demander une confirmation, en listant les sites qui ne seront plus accessibles (et qu'il faudrait p't'être passer en status='off' à ce moment là, d'ailleurs) ; faut pas l'empêcher complètement, ça peut être nécessaire pour une migration

Indexes

Notes générales

Fait

Ddonnées utilisées par les vues :

• dns.domains
• dns.records
• web.sites & web.sites_on
• nss.ftp_passwd & nss.ftp_group

TODO : à revoir... et en particulier, filtrer nos indexes sur status='on' ?

TODO

NB: The query planner can use a multicolumn index for queries that involve the leftmost column in the index definition plus any number of columns listed to the right of it, without a gap. (11.3) P't'être on peut en économiser qques-un, donc.

NB: index partiel = perf++ : file:///usr/share/doc/postgresql-doc/html/indexes-partial.html

Unicité des adresses mail

Le schéma SQL empêche de créer, deux entités mail/alias/liste ayant la même adresse mail, en prenant en compte les aliases spéciaux de Mailman (-request, etc.).

Ceci, grâce à des contraintes (unique_mail_address) ajoutées sur les tables concernées (mail_boxes, mail_aliases, lists), contraintes qui rendent ces tables définies cycliquement. Les modifications des champs référencés par ces contraintes sont donc interdites, pour les raisons expliquées dans "Contraintes de table référençant d'autres tables".