Se connecter
Se connecter
Mot de passe oublié ?
ou
Créer un compte
ou
Se connecter avec Facebook
Notifications
< Retour vers Bidouille & Développement Informatique
Agrandir
Bidouille & Développement Informatique
Les Mains dans le Cambouis
Bidouille & Développement Informatique
Actu 1 1
Articles
Médias 33 33
Tutoriels
Forums 326 326

Le pub des programmeurs

  • 1 927 réponses
  • 117 participants
  • 123 832 vues
  • 130 followers
Sujet de la discussion Le pub des programmeurs
Salut :coucou: y a des programeurs sur AF si oui vous bossez sous quoi ?
Afficher le sujet de la discussion
1891
Ah pardon moi je parlais plutôt des donnés style date de début de relation, type de relation (ami, famille, collègue) plus tout les détails que tu peut être amener à ajouter.
1892
Citation de Truelle :
Ah pardon moi je parlais plutôt des donnés style date de début de relation, type de relation (ami, famille, collègue) plus tout les détails que tu peut être amener à ajouter.

Effectivement, dans ce cas, mieux vaut avoir une table dediee, ou alors un dictionnaire contact: connectionid qui pointe vers une autre table qui pourra avoir ces infos.

http://blog.audio-tk.com/

Audio Toolkit: http://www.audio-tk.com/

1893
Citation de Truelle :
Après quelques petites recherche en me basant sur le post du blog aliabacloud, je suis arrivé à un truc complètement différent:
...


D'accord sur le principe, mais une table de liaison à 3 colonnes (ID, A_ID, B_ID) marche tout aussi bien.
Pour l'unicité, tu pourrais passer par une procédure stockée pour réaliser l'insertion d'une relationship. Je ne suis pas sûr que tu puisses le faire simplement avec des constraints. Ou alors ça risque de compliquer ton design.
1894
@props:

Je viens de trouver ca pour une table du type relationships(ID, A_ID, B_ID):
https://dba.stackexchange.com/questions/202570/combine-indexes-to-optimize-or-queries
https://www.postgresql.org/docs/current/bloom.html

Du coup je pourrais utiliser des foreignkey pour A_ID, B_ID.
Et plus de table "relationships_informations".

Edit: Ouai nan en fait il peut y avoir des faux positif et c'est surtout utilisé quand on teste beaucoup de colonnes.

Tout connement je viens de voir dans le même post qu'on peut faire un simple 'WHERE id IN (a_id, b_id)'. :facepalm:

[ Dernière édition du message le 21/02/2020 à 20:39:56 ]

1895
Du coup retour à la case départ avec l'idée de Duch et Props:

-- # ENUMS
-- #######
-- Relation Status:
-- - P = Pending
-- - A = Accepted
-- - D = Declined
-- - B = Blocked
CREATE TYPE relation_status AS ENUM('P', 'A', 'D', 'B');


-- # FUNCTIONS
-- ###########
CREATE FUNCTION reverse_relationship_exists(account_id_a INTEGER, account_id_b INTEGER)
RETURNS BOOLEAN AS $$
BEGIN 
    RETURN EXISTS(
        SELECT
            1
        FROM relationships
        WHERE
          account_a_id = account_id_b
          AND account_b_id = account_id_a
    );
END;
$$
LANGUAGE plpgsql;


-- # TABLES
-- ########
CREATE TABLE accounts(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);

CREATE TABLE relationships(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    account_a_id INTEGER NOT NULL REFERENCES accounts (id),
    account_b_id INTEGER NOT NULL REFERENCES accounts (id),
    CONSTRAINT accounts_are_differents CHECK (account_a_id != account_b_id),
    CHECK (NOT reverse_relationship_exists(account_a_id, account_b_id)),
    UNIQUE (account_a_id, account_b_id),

    -- Informations
    status relation_status NOT NULL,
    last_action_account_id INTEGER NOT NULL REFERENCES accounts (id),
    CONSTRAINT last_action_done_by_one_of_two_users CHECK(last_action_account_id IN (
        account_a_id, account_b_id
    ))
);

Du coup j'imagine qu'il faut créer un index pour chaques colonnes account_a_id, account_b_id.

CREATE INDEX a_id ON relationships(account_a_id);
CREATE INDEX b_id ON relationships(account_b_id);

Ou créer un index unique et supprimer le ligne "UNIQUE(account_a_id, account_b_id)":
CREATE UNIQUE INDEX a_id ON relationships(account_a_id, account_b_id);

[ Dernière édition du message le 21/02/2020 à 21:27:40 ]

1896
Puis-je vous inviter à une petite reflexion sur les formes normales d'une base de données relationnelle?
Je voudrais insister sur le design des tables.

L'élément le plus simple doit avoir une clé unique (primary key)

ex: "account" avec un identifiant ( id géneré, N° de passeport, etc) qui le rend unique:
- id_acc

Toutes les informations "uniques" qui se rapportent à cet objet peuvent, doivent être inclues dans cette table:

- nom
- prénom
- date de naissance
- nom du père
- ....

Donc, une autre table "Relation" qui identifie une relation entre deux "account" avec un identifiant propre :
- id_relation (primary key de forme atomique)
et toutes les infos qui caractérisent cette relation entre deux "accounts". Ici, pas d'information relative à l'account, juste un objet "relation"
- description ( frére, soeur, père, mère, patron, directeur, employé,...)
- ...

Une troisième table "relation_account":
Qui reflète les relations entre accounts avec une clé unique aussi (primary key)
PK = "id_account_A , id_account B, id_rel"

Les données supplémentaires de la relation:
- un statut ( active, pending, ...)
- date de création
- id_rel_inv

La clé primaire (PK) pourrait aussi être un "timestamp" ou une id unique, si plusieures relations identiques doivent se côtoyer.

Un trigger peut également générer une relation inverse dans la table "relation_account" à la création d'une entrée dans la table "account".

Bien qu'en ajoutant une clé "étrangère" (Foreing Key) avec l'identifiant de la relation inverse dans la table "relation", on peut évité cette étape. Il suffirait dans la requête SQL de lire la relation à l'envers en utilisant la FK pour retrouver la relation inverse.
genre :

Relation normale:

select id_account A, id_B, id_relation, A.nom, A.prenom, B.nom, B.prénom, type_relation, date_creation, status,....
from account_relation, relation, account A, account B
where id_account_A = A.id_acc
and id_account_b = B.id_acc
and id_relation = id_rel

Ou si relation inverse

[b/]and id_relation = id_rel_inverse[/b]

Tout ceci peut-être renforcé par des contraintes d'intégrité référentielles pour évité des non-sens et des liens "account_relation" n'ayant pas/ plus de clé consistantes ou executé de update en cascade.

On peut bien-sûr ajouter des clauses de tri (order by, group by) et de sélection (where, where in et like) pour affiner la recherche.

Si des problèmes de performance apparaîssent:

- n'hésitez pas à reccourrir au mode "explain " pour analyser vos querie et déterminer l'ajout d'un index pertinent sur l'un ou l'autre champs/groupe de champs de vos tables
- repartir vos tables/index sur des "locations" (tablespaces) différentes.

:clin:
AL1
1897
Ouai merci pour l'explain, connaissait pas, du coup j'ai maté une vidéo du pgday qui en parlait et lu un peu la doc.

Sinon j'ai passé du temps à trouver des solutions plutôt compliqué mais finalement est-ce qu'il serait pas plus simple que je fasse une vue sur ça:
select user_a, user_b from relationships union select user_b, user_a from relationships;

Plus de relation inverse.
Du coup je peux mettre les infos dans cette tables .
Les recherches sont aussi simple.
Un index sur user_a, un autre sur user_b. Les deux en clé primaire.

Et si j'utilise cette vue je ne peux pas créer de doublon inverse puisque la même rangé apparaît dans les deux sens. (Sauf si je fais un insert de (1,2),(2,1) puisqu'il se baserait sur le même résultat je pense, m'enfin un check ça coûte rien).

Après si je fais une vue là dessus et que je fais un select sur cette vue, est-ce qu'elle calcule la totalité des deux select avant de les renvoyer pour faire un select/insert/update/delete et du coup serait une grosse consommatrice de cpu/mémoire à chaque utilisation?

[ Dernière édition du message le 23/02/2020 à 23:58:21 ]

1898
Citation :
Un index sur user_a, un autre sur user_b. Les deux en clé primaire.


Une clé primaire est par définition un index.
L'ajout d'index sur des éléments de clé est souvent contre-productif, a essayer avec un "explain" :clin:
Citation :
select user_a, user_b from relationships union select user_b, user_a from relationships;

L'utilisation d'une vue n'est pas une mauvaise idée.
Mais une vue n'est jamais qu'un "select" formalisé. Pour les insert/update avec une vue, cela impose les mếmes contraintes que pour des insert/update sur les tables. Tous les champs obligatoires doivent être remplis.
Ici, cela ne donnera rien de mieux que deux fois la table relationship dans une table temporaire ce qui peut être gourmand en memoire/table temporaire.
Un select avec une clause where bien torchée me semble plus efficace (index scan plutôt que hash join) qu'une vue

select A.user_A, A.user_B from relation_ship A
where A.user_A = 'misterX'
union
select B.user_A, B.user_B from relation_ship B
where A.user_A = B.user_B


Citation :
Après si je fais une vue là dessus et que je fais un select sur cette vue, est-ce qu'elle calcule la totalité des deux select avant de les renvoyer pour faire un select/insert/update/delete et du coup serait une grosse consommatrice de cpu/mémoire à chaque utilisation?



Une "vue" crée une table temporaire avec les résultats du select qui la définit. Elle va occuper de la place en mémoire et forcément utiliser du CPU voire de l'espace disque . Elle aura le même coût qu'un select normal, d'où l'intérêt de bien l'écrire. Pour des résultats importants ( genre select * et des "hash join") sur des grosses tables, cela mets rapidement des machines à genoux -> calibrage des tablespace temporaires très important!.
Les delete sont uniquement des radiations d'index dans un premier temps. Les réorganisations/truncate (vacuum) feront le nettoyage à postériori.,
AL1

[ Dernière édition du message le 24/02/2020 à 10:04:33 ]

1899
Citation :
Une clé primaire est par définition un index.
L'ajout d'index sur des éléments de clé est souvent contre-productif, a essayer avec un "explain" https://static.audiofanzine.com/images/audiofanzine/interface/smileys/icon_wink.gif


Yep mais du coup si je fais une recherche uniquement sur une des deux colonnes elles doivent bien être indexé séparément également ?
1900
Citation :
Yep mais du coup si je fais une recherche uniquement sur une des deux colonnes elles doivent bien être indexé séparément également ?


Quelque soit la base de données utilisée (Postgresql, MySQL/MariaDB, Oracle, DB2,...), il y a derrière des algorythmes qui influencent le choix de la méthode d'accès. Ils tiennent compte de la taille des tables, des index et de l'usage de la machine (nombre et vitesse des CPU, mémoire disponible,...)

Dans ce cas précis, il y a fort à parier que tu aura un index-scan sur la première zone suivi d'un hash-join (recherche en séquence) sur la table pour la seconde zone de la clé.

C'est le principe d'une fonction 'like'.
AL1