Bonjour à tous
Ma base de données MySQL contient une table "Addresses". Chaque adresse a un champ "zip" (code postal) et un champ "region"
Les "régions" qui m'intéressent sont:
1) paris
2) ile de france
3) autres régions
Si l'adresse n'est pas en France, le champ "region" est NULL
Actuellement la valeur de "region" est calculée en PHP avant la création ou la mise à jour de la table Address à partir du champ "zip".
J'ai cru comprendre que l'on pouvait faire ça automatiquement dans MySQL, mais je n'ai pas trouvé comment faire.
Auriez vous des informations à me communiquer?
Merci de votre aide
Modérateur
Et l'eau,

Ton explication n'est pas très claire. Peux tu indiquer sur le forum la structure de ta table ou tes tables ?

Sinon, ceci (peut être ?)

SELECT 
    un_champ, 
    un_autre_champ,
    etc 
FROM 
    adresses 
WHERE 
    region IN ('Paris', 'ile de france', 'autres régions')

Modifié par niuxe (19 Jun 2020 - 13:17)
Structure de la table

CREATE TABLE `Addresses` (
  `id` int(32) NOT NULL,
  `addressID` varchar(128) NOT NULL,
  `street` varchar(1024) DEFAULT NULL,
  `zip` varchar(64) DEFAULT NULL,
  `town` varchar(128) DEFAULT NULL,
  `country` varchar(128) DEFAULT NULL,
  `regionID` varchar(128)  DEFAULT NULL
) 

Le champ 'regionID' vaut (paris, idf, other, abroad)
Il est calculé par PHP avant la création ou la mise à jour de la table par

if($this -> country == 'France') {
  $dpt = substr($this -> zip, 0, 2);
  switch($dpt) {
    case '75':
      $this -> regionID = 'paris';
      break;
    case '77': case '78': case '91': case '92': case '93': case '94': case '95':
      $this -> regionID = 'idf';
      break;
    default:
      $this -> regionID = 'other';
      break;
  }
}
else $this -> regionID = 'abroad';

Ma question c'est
Je crois qu'il existe un moyen (mais ce n'est peut-être qu'une impression) de faire en sorte qu'on n'ait pas besoin de calculer le champ "regionID" mais qu'il soit automatiquement calculé par le serveur de BDD.
Modifié par PapyJP (19 Jun 2020 - 16:20)
Modérateur
Pour un select :

SELECT 
    street,
    zip,
    SUBSTR(zip, 0,3) AS dpt,
    town,
    country, 
    CASE 
        WHEN SUBSTR(zip, 0,3) = '75' THEN 'paris' 
        WHEN  SUBSTR(zip, 0,3) = '77' 
		OR 
			SUBSTR(zip, 0,3) = '78' 
		OR 
			SUBSTR(zip, 0,3) BETWEEN '91' AND '95' 
		THEN 'idf'  
        ELSE 'other'
    END
FROM 
    adresses;


pour un update :

UPDATE  
    adresses 
SET 
    regionid= (
    CASE 
        WHEN SUBSTR(zip, 0,3) = '75' THEN 'paris' 
        WHEN SUBSTR(zip, 0,3) = '77' 
		OR 
			SUBSTR(zip, 0,3) = '78' 
		OR 
			SUBSTR(zip, 0,3) BETWEEN '91' AND '95' 
		THEN 'idf'  
        ELSE 'other'
    END
);


Attention à l'update, je n'ai pas mis de clause WHERE. De toute manière, la règle est simple : avant de faire un update ou un delete, on fait toujours un select afin de vérifier ce que l'on va supprimer ou mettre à jour

Chaque fois, je me trompe dans le between. Je ne sais plus s'il prend les critères désignés ou seulement ceux à l'intérieur. Il me semble que oui
Modifié par niuxe (19 Jun 2020 - 17:52)
Merci de ta réponse.
Ce que tu me dis veut sans doute dire que j'avais compris de travers.
Dans ton SELECT on part d'une table qui n'a pas de champ "regionID" et on le met dans la réponse.
Dans ton UPDATE, tu pars d'une table qui a un champ "regionID" et tu le remplis à chaque UPDATE.
Mais ça veut dire que toute commande UPDATE doit contenir ce code, c'est à dire que tu fais en SQL ce que je fais en PHP.
Ce que j'avais cru comprendre c'est qu'on pouvait mettre quelque part dans la dabatase une fonction enregistrée qui faisait ça sans qu'on ait besoin de l'appeler spécifiquement à chaque UPDATE.
Il y a un mécanisme de "trigger" qui doit permettre de faire ça, mais ça ne doit pas exister dans MySQL
Modérateur
PapyJP a écrit :

Ce que tu me dis veut sans doute dire que j'avais compris de travers.

oui je pense
PapyJP a écrit :

Dans ton SELECT on part d'une table qui n'a pas de champ "regionID" et on le met dans la réponse.

oui. exemple le champ créé à la volée : dpt
PapyJP a écrit :

Dans ton UPDATE, tu pars d'une table qui a un champ "regionID" et tu le remplis à chaque UPDATE.
Mais ça veut dire que toute commande UPDATE doit contenir ce code, c'est à dire que tu fais en SQL ce que je fais en PHP.

oui et c'est nettement plus rapide puisque php/python/ruby/etc. et apache/nginx n’interprètent pas.

PapyJP a écrit :

Ce que j'avais cru comprendre c'est qu'on pouvait mettre quelque part dans la dabatase une fonction enregistrée qui faisait ça sans qu'on ait besoin de l'appeler spécifiquement à chaque UPDATE.
Il y a un mécanisme de "trigger" qui doit permettre de faire ça, mais ça ne doit pas exister dans MySQL

Que ce soit les trigger ou procédure stocké, je ne connais pas trop puisque j'en ai jamais eu besoin. Là de toute évidence ta table n'est pas normalisée. Il est toujours préférable d'avoir peu de champ et beaucoup de lignes que l'inverse.

Après, je ne sais pas ce que tu veux faire : select ou update ? Si c'est un select, regarde les VIEW Smiley cligne


CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition; 

Modifié par niuxe (19 Jun 2020 - 18:05)
Cette question est la conséquence de la discussion https://forum.alsacreations.com/list-1-Questions-generales-et-questions-de-debutants.html
Il me semblait utile d'avoir un champ "région" pour classer les lieux de répétition, concerts, etc. au même titre que la dernière date où on a utilisé ce lieu et autres "champs intermédiaires".
On peut certes compliquer la requête SELECT, mais il est plus simple d'avoir un champ tout fait. Créer ou mettre à jour une entrée dans la database se fait relativement rarement par rapport à la consultation de la table. Mais dupliquer des valeurs est une source de bugs qui mettent en danger l'intégrité de la base.
Si on ne peut pas disposer d'un mécanisme de trigger qui assure que ces "champs intermédiaires" sont cohérents, il ne reste, comme tu le proposes, qu'à complexifier le sql et conséquemment à ralentir la consultation de la base.
Je voulais y voir clair, car cela a un impact important sur la façon de concevoir l'application.
Modérateur
PapyJP a écrit :
Cette question est la conséquence de la discussion https://forum.alsacreations.com/list-1-Questions-generales-et-questions-de-debutants.html

Heu oui.... Smiley rolleyes laquelle ?

PapyJP a écrit :

Il me semblait utile d'avoir un champ "région" pour classer les lieux de répétition, concerts, etc. au même titre que la dernière date où on a utilisé ce lieu et autres "champs intermédiaires".
On peut certes compliquer la requête SELECT, mais il est plus simple d'avoir un champ tout fait.


Comme je t'ai dit, ta table n'est pas normalisée et il y aura des conséquences désastreuses d'où ma requête avec le CASE

PapyJP a écrit :

Créer ou mettre à jour une entrée dans la database se fait relativement rarement par rapport à la consultation de la table.


Pas obligatoirement

PapyJP a écrit :

Mais dupliquer des valeurs est une source de bugs qui mettent en danger l'intégrité de la base.


Oh que oui !

PapyJP a écrit :

Si on ne peut pas disposer d'un mécanisme de trigger qui assure que ces "champs intermédiaires" sont cohérents, il ne reste, comme tu le proposes, qu'à complexifier le sql et conséquemment à ralentir la consultation de la base.


À priori, tu n'as pas besoin d'un mécanisme de trigger. Que fait ton code php ?

PapyJP a écrit :

Je voulais y voir clair, car cela a un impact important sur la façon de concevoir l'application.


parlez vous merise ?
Merise 60 AFFAIRES CLASSÉES

edit: tout à l'heure, j'ai vu cette erreur et j'ai oublié de la relever :

CREATE TABLE `Addresses` (
....
  `street` varchar(1024) DEFAULT NULL,
....
) 


Un varchar ne peut excéder 255 caractères. Il me semble de mémoire que default null n'est pas nécessaire. Au mieux, NULL suffit
Modifié par niuxe (19 Jun 2020 - 20:01)
Modérateur
PapyJP a écrit :
Qu’entends-tu par "normaliser" une base?


Que tu aies :
- id unique (tiens... un oxymore)
- qu'un id ne soit pas composite (je le vois souvent. Or c'est une hérésie... Edgar Codd sauterait au plafond voyant ça)
- que tes champs soient atomiques
- que tu aies des index bien placés
- etc.
- etc.

relis bien mon message précédent, j'ai ajouté une note
Modifié par niuxe (19 Jun 2020 - 19:33)
niuxe a écrit :
Que fait ton code php ?

Rien d'autre qu'à gérer et afficher les données de la base.
Depuis 21 ans, la "base" est constituée de 3 fichiers Excel exportés sous la forme de fichiers xml sur le site, le code PHP ne sert qu'à afficher les données.
Mais ça veut dire que seule la personne qui possède le PC où se trouve les fichiers Excel (moi-même) peut administrer les données.
La grosse difficulté dans le design de l'interface de gestion des données c'est qu'il y a des tas de choses dans Excel qu'il faudrait pour bien faire que je programme en PHP, c'est une tâche a priori trop lourde pour que le puisse le faire rapidement.
J'ai essayé de faire une interface qui travaille sur les fichiers xml, et on a vu que ça n'était pas approprié.
J'ai donc mis les données dans des tables SQL, ce qui n'est pas très difficile puisque ce n'est que la reprise d'un design qui marche depuis 21 ans.
Modifié par PapyJP (19 Jun 2020 - 19:44)
niuxe a écrit :


Que tu aies :
- id unique (tiens... un oxymore)
- qu'un id ne soit pas composite (je le vois souvent. Or c'est une hérésie... Edgar Codd sauterait au plafond voyant ça)
- que tes champs soient atomiques
- que tu aies des index bien placés
- etc.
- etc.

relis bien mon message précédent, j'ai ajouté une note

Pour moi c'est le cas, mais personne n'est parfait.
A propos des varchar la doc dit:
"Avant Mysql version 5.0.3, le type de données Varchar peut stocker 255 caractères, mais à partir de la version 5.0.3, il peut contenir 65 535 caractères. MAIS il a une limitation de taille maximale de 65 535 octets. Cela signifie que toutes les colonnes ne doivent pas dépasser 65 535 octets."

A propos de "DEFAUT NULL" c'est phpMySql qui génère ça, moi j'ai simplement dit que je considérais que ce champ pouvait être null et n'avait pas de valeur par défaut.
Modifié par PapyJP (19 Jun 2020 - 21:30)
Modérateur
ok, j'ai compris pourquoi ta table n'est pas normalisée. (un fichier plat, il faut s'en méfiait. Parce que ce sont des données plates... ) Or à l'insertion des données, tu n'as pas fait la préparation en amont. Merise ....

Donc la solution que je puisse te proposer, c'est bien de créer une vue que tu amélioreras ( voir ma réponse précédente ). Ensuite l'appeler via php.

PapyJP a écrit :

mais personne n'est parfait.

+1
PapyJP a écrit :

A propos des varchar la doc dit:
"Avant Mysql version 5.0.3, le type de données Varchar peut stocker 255 caractères, mais à partir de la version 5.0.3, il peut contenir 65 535 caractères. MAIS il a une limitation de taille maximale de 65 535 octets. Cela signifie que toutes les colonnes ne doivent pas dépasser 65 535 octets."


C'est la définition du type de champ TEXT Smiley cligne
Modifié par niuxe (19 Jun 2020 - 20:18)
Je ne comprends pas très bien de quoi tu parles. Je regarderai demain. Je pense qu’un schéma de données qui marche sans problème depuis 21 ans ne peut pas être totalement à jeter.
Ce qui me pose un problème c’est de devoir utiliser une base de données SQL pour y stocker des objets. Il se trouve que j’ai travaillé sur ce sujet dans les années 90, il y avait des travaux très intéressants sur les bases de données objet, mais elles ont été anéanties par qui nous savons.
Modérateur
PapyJP a écrit :
Je ne comprends pas très bien de quoi tu parles. Je regarderai demain. Je pense qu’un schéma de données qui marche sans problème depuis 21 ans ne peut pas être totalement à jeter.


Vraisemblablement, ton schema est équivalent à ton fichier plat :
| id | addressID | street | zip | town | country | regionID |

Or, déjà on peut scinder cette table en 3 ou en 4. Ton schema peut fonctionner pendant plusieurs années. Mais tes requêtes seront moins performantes et pour étendre ton schéma sans avoir de doublons, bonne chance.

PapyJP a écrit :

Ce qui me pose un problème c’est de devoir utiliser une base de données SQL pour y stocker des objets.

Je vois pas de quoi tu parles. Quel objet ?
PapyJP a écrit :

Il se trouve que j’ai travaillé sur ce sujet dans les années 90, il y avait des travaux très intéressants sur les bases de données objet, mais elles ont été anéanties par qui nous savons.


Merise existe depuis les années 70-80. Tout comme le SQL. Edgar Codd et son acolyte dont j'ai oublié le nom ont travaillé pour sortir quelque chose de cohérent et surtout efficace. Il faut comprendre que dans les années 70, le SQL n'était pas du tout prédominant. Le nom du langage ne me revient pas. D'ailleurs, il était largement utilisé dans les databases de cette époque (exemple : Ingres papa de Postgresql).

Bob Miner, Larry Ellison et Ed Oates ont vu les travaux d'Edgar et ont décidé de révolutionner le secteur.... (Ils l'ont révolutionné) Si tu fais allusion à Bill Gates, bah non, il n' pas fait grand chose. SQLServer n'est qu'une database à la sauce microsoft (donc un sql légèrement différent du standard).
Modifié par niuxe (20 Jun 2020 - 18:30)
J’ai eu le désagréable privilège de travailler sous les ordres d’un des concepteurs de Merise qui publiait des bouquins à ce sujet. Le Merise de l’époque conduisait à des modèles de données fondés sur des processus figés. Quelques modifications des processus suffisaient à casser le modèle.
J’espère que les évolutions de Merise ont permis de changer cela. Mais je n’ai aucune envie de me plonger à nouveau dans ce marécage.
Je n’avais pas non plus envie de faire du SQL, mais j’y suis contraint. Pour autant que je comprenne SQL conduit à éclater les données, choses complètement contraire à ma façon de travailler depuis 50 ans. Que cela conduise à un modèle de données hérétique à tes yeux ne m’étonne pas.