Discussion :

[castorcharly]

Bonjour,

J'utilise Ibexpert (pro version acquise avec licence) pour faire une PS qui fait un simple drop table et à la compilation,
j'ai une erreur commande drop token unknow !

C'est mon ibexpert qui a un bug ou bien il n'est pas permis de faire un drop table dans une PS ?

[SergioMaster]

Bonjour,

je crois que DROP ne fait pas partie du PSQL (Procédural SQL) uniquement du DSQL (Design SQL)

[castorcharly]

Ah, je vais regarder dans la doc, merci sergioMaster je n'avais pas envisagé cette restriction.

[castorcharly]

Il semble que DROP ne soit valide qu'en DSQL et ISQL mais pas en PSQL.
Je vais faire autrement,

merci

[Artemus24]

Salut à tous.

Pourquoi ne pas utiliser une table temporaire ?

@+

[castorcharly]

Merci de répondre,

mais quel rapport entre faire un DROP Table dans une PS et une table temporaire ?

[Artemus24]

Salut castorcharly.

Une table est quelque chose de statique dans une base de données.
Il n'y a aucun intérêt à faire une suppression de tables dans une procédure stockées.
La procédure stockée n'est pas destinée à cet usage.

Si vous désirez faire une suppression de tables, genre faire du nettoyage, autant le faire dans un script SQL.
Et de le déclencher avec un planificateur de tâche si vous êtes sous windows, ou bien avec la crontab.

Inversement, il existe les tables temporaires, qui ont une durée de vie limité dans le temps.
Il n'est pas nécessaire de les supprimer effectivement.
Car à la fin de la transaction, elles sont automatiquement supprimées !

@+

[SergioMaster]

Inversement, il existe les tables temporaires, qui ont une durée de vie limité dans le temps.
Il n'est pas nécessaire de les supprimer effectivement.
Car à la fin de la transaction, elles sont automatiquement supprimées !

De quelles tables temporaires parles-tu ? car pour une GLOBAL TEMPORARY TABLE même si les données sont effacées les métadatas elles restent (du moins dans les versions que je maitrise) y a t-il eu des ajouts/améliorations avec la versions 3 ?

maintenant, je n'ai pas vérifié, mais qu'en serait-il en utilisant ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DELETE from rdb$relations where rdb$relation_name = 'TABLEASUPPRIMER'

[Edit] après essai cela ne suffit pas, si la table semble disparaitre les champs sont toujours là RDB$RELATION_FIELDS, les indexs aussi RDB$INDICES il faudrait donc faire plus de DELETE (à minima sur ces deux tables, mais il y en a certainement d'autres impliquées aussi

[Artemus24]

Salut SergioMaster.

De quelles tables temporaires parles-tu ?

Je parle de ce genre de fonctionnement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
SET TRANSACTION;
Rolling back work.
 
-- ===========================
-- Création de la table 'test'
-- ===========================
 
CREATE GLOBAL TEMPORARY TABLE test
(  id   INTEGER       NOT NULL PRIMARY KEY,
   val  VARCHAR(255)  NOT NULL
) on commit delete rows;
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
insert into test (id,val) values (1, 'un');
insert into test (id,val) values (2, 'deux');
insert into test (id,val) values (3, 'trois');
insert into test (id,val) values (4, 'quatre');
insert into test (id,val) values (5, 'cinq');
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test;
 
          ID VAL
============ ===============================================================================
           1 un
           2 deux
           3 trois
           4 quatre
           5 cinq
 
 
commit;
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test;
 
commit;
 
-- ============
-- Informations
-- ============
 
select * from rdb$relations       where rdb$relation_name = 'TEST';
 
     RDB$VIEW_BLR   RDB$VIEW_SOURCE   RDB$DESCRIPTION RDB$RELATION_ID RDB$SYSTEM_FLAG RDB$DBKEY_LENGTH RDB$FORMAT RDB$FIELD_ID RDB$RELATION_NAME               RDB$SECURITY_CLASS              RDB$EXTERNAL_FILE                                                                                                                                                                                                                                                     RDB$RUNTIME RDB$EXTERNAL_DESCRIPTION RDB$OWNER_NAME                  RDB$DEFAULT_CLASS               RDB$FLAGS RDB$RELATION_TYPE
================= ================= ================= =============== =============== ================ ========== ============ =============================== =============================== =============================================================================== ================= ======================== =============================== =============================== ========= =================
           <null>            <null>            <null>             128               0                8          1            2 TEST                            SQL$414                         <null>                                                                                                                                                                                                                                                                      6:2cd                   <null> PATRON                          SQL$DEFAULT51                           1                 5
==============================================================================
RDB$RUNTIME:
BLOB display set to subtype 1. This BLOB: subtype = 5
==============================================================================
 
select * from rdb$relation_fields where rdb$relation_name = 'TEST';
 
RDB$FIELD_NAME                  RDB$RELATION_NAME               RDB$FIELD_SOURCE                RDB$QUERY_NAME                  RDB$BASE_FIELD                  RDB$EDIT_STRING                                                                                                                 RDB$FIELD_POSITION  RDB$QUERY_HEADER RDB$UPDATE_FLAG RDB$FIELD_ID RDB$VIEW_CONTEXT   RDB$DESCRIPTION RDB$DEFAULT_VALUE RDB$SYSTEM_FLAG RDB$SECURITY_CLASS              RDB$COMPLEX_NAME                RDB$NULL_FLAG RDB$DEFAULT_SOURCE RDB$COLLATION_ID RDB$GENERATOR_NAME              RDB$IDENTITY_TYPE
=============================== =============================== =============================== =============================== =============================== =============================================================================== ================== ================= =============== ============ ================ ================= ================= =============== =============================== =============================== ============= ================== ================ =============================== =================
ID                              TEST                            RDB$1                           <null>                          <null>                          <null>                                                                                                                                           0            <null>               1            0           <null>            <null>            <null>               0 <null>                          <null>                                      1             <null>           <null> <null>                                     <null>
VAL                             TEST                            RDB$2                           <null>                          <null>                          <null>                                                                                                                                           1            <null>               1            1           <null>            <null>            <null>               0 <null>                          <null>                                      1             <null>           <null> <null>                                     <null>
 
 
-- ========================
-- Suppression table 'test'
-- ========================
 
drop table test;
 
commit;
 
-- ============
-- Informations
-- ============
 
select * from rdb$relations       where rdb$relation_name = 'TEST';
select * from rdb$relation_fields where rdb$relation_name = 'TEST';
 
quit;
 
Appuyez sur une touche pour continuer...

Création d'une table temporaire, avec "on commit delete rows". Après le "commit", le contenu de la table a été détruit.
Pour faire disparaître la table, il faut faire un "drop table test". Ne pas oublier le "commit" juste après.

Faire un delete sur une des tables du noyau de FireBird n'est pas une façon correcte pour supprimer une table.

Précédemment, j'ai dit que l'on ne peut pas directement, faire un "drop table temp" dans la procédure stockée.
Mais on peut contourner le problème, en faisant un "execute statement", comme dans l'exemple ci-après :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- ===========================
-- Création de la table 'temp'
-- ===========================
 
CREATE TABLE temp
(  id   INTEGER       NOT NULL PRIMARY KEY,
   lib  VARCHAR(255)  NOT NULL
);
 
-- =====================
-- Insertion dans 'temp'
-- =====================
 
insert into temp (id, lib) values (1, 'rouge');
insert into temp (id, lib) values (2, 'jaune');
insert into temp (id, lib) values (3, 'bleu');
insert into temp (id, lib) values (4, 'orange');
insert into temp (id, lib) values (5, 'vert');
 
-- ================
-- Vidage de 'temp'
-- ================
 
select * from temp;
 
          ID LIB
============ ===============================================================================
           1 rouge
           2 jaune
           3 bleu
           4 orange
           5 vert
 
 
commit;
 
-- ================
-- Vidage de 'temp'
-- ================
 
select * from temp;
 
          ID LIB
============ ===============================================================================
           1 rouge
           2 jaune
           3 bleu
           4 orange
           5 vert
 
 
commit;
 
-- ==================
-- Création Procédure
-- ==================
 
SET TERM #;
 
CREATE PROCEDURE proc ()
AS
BEGIN
  execute statement 'drop table temp;';
END#
 
SET TERM ;#
 
-- =========
-- exécution
-- =========
 
execute procedure proc;
 
commit;
 
-- ================
-- Vidage de 'temp'
-- ================
 
select * from temp;
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -204
-Table unknown
-TEMP
-At line 1, column 15
At line 61 in file Base_2.sql
 
quit;
 
Appuyez sur une touche pour continuer...

La table n'est pas temporaire et le "commit" n'a aucune influence comme précédemment avec la table temporaire.
Dans la procédure stockée, je fais bien un "drop table" et cela fonctionne ! C'est une façon détourner de contrer l'interdit.
Mais franchement, je n'aime pas du tout cette façon de faire.

Comme je l'ai dit, je préfère un script contenant les "drop table", déclenché par la crontab ou le planificateur des tâches.

@+

[castorcharly]

Artemus,

Je n'ai pas encore utilisé ce genre de structure de table, il faudrait que j'y vois un interet et aussi les performances.
La structure et la mise en oeuvre du script me paraissent bien compliqué, dès la lecture de ton exemple.

Pour ce qui concerne le fait de détruire une table, c'est très simple, c'est bien plus rapide que de faire un delete de toutes les lignes.
Alors qu'un drop et un recréate est très simple à mettre en oeuvre, très peu gourmand en ressource
et je ne vois pas où est la réserve que tu indiques sur le fait de pratiquer un DROP d'une table par rapport au noyau de FB.

L'avantage de le faire dans une PS est que c'est centralisé dans la structure, que tout se passe en local:
-le contrôle si la table existe bien,
-le drop
-puis le re create de la table en question et de ses propriétés particulières, (Index, Triggers etc...).

J'ai donc, vu ce que m'a signalé SergioMaster sur l'incompatibilité de la commande DROP en PS, utilisé un execute statement dans ma PS
pour faire une commande centralisé, qui fonctionne très bien depuis ce petit changement.

La suppression de la table contenant plusieurs centaine de milliers de ligne avec champ blob volumineux est très rapide,
ce que je pratique depuis des années sur des centaines de bases de données, sans aucun inconvénient.

Jusqu'a present, je lançais ceci en dehors de FB par commande via mes services, je trouvais que l'intégrer dans une PS était plus efficace car je gère
quelques dizaines de bases strictement identique en structure.

Je regarderai de plus près ce genre de structure de table, pour voir ce qu'elle peut apporter dans les applications que je développe.

Merci pour tes exemples très clairs.

[SergioMaster]

Bonjour,
je n'avais pas penser au execute statement

Faire un delete sur une des tables du noyau de FireBird n'est pas une façon correcte pour supprimer une table.

et oui, il est hyper casse-g...e de modifier les données des tables Système

pour en revenir aux GTT

Je n'ai pas encore utilisé ce genre de structure de table, il faudrait que j'y vois un intérêt et aussi les performances.

il y a plusieurs possibilités intéressantes avec elles

soit une GTT ON COMMIT DELETE ROWS la table est vidée à la fermeture de la transaction , la structure reste . Bon c'est pas mal mais cette histoire de transaction ça peut être gênant sauf si utilisé à l'intérieur d'une procédure

prenons maintenant un GTT ON COMMIT PRESERVE ROWS les enregistrement sont gardés le temps de la connexion. Celle-ci j'utilise beaucoup maintenant à la place de fichiers temporaires externes.

un exemple concret : je travaille dans le secteur industrie textile/chaussures ("la mode") une fonctionnalité d'un de mes programmes est d'imprimer des étiquettes sur des planches.
succinctement : soit un fichier MODELECHAUSSURE(NOM,POINTURE1,POINTURE2,POINTURE3,POINTURE4 ..... POINTURE24)
// APPARTE -----------------------------------------------------------------------
OUI, la structure de la base pourrait être
MODELE(NOM)
MODELEPOINTURE(NOM,PT)
je ne vous raconte pas le nombre de lignes que cela ferait sur plusieurs saisons (2 par an) à raison d'une 100 de modeles, 10 à 20 coloris et sur 1 à 36 pointures
si à ces tables on ajoute les commandes, les expéditions, les stocks, les lancements détaillés à la pointure
Je me suis dit qu'un jour je devrai essayer les tableaux dans les tables mais il faudrait que j'écrive des fonctions de SOMMEDETABLEAU etc...
et au niveau programmation je n'avais rien de simple pour traiter ces dits tableaux
// FIN APPARTE -----------------------------------------------------------------------

il me faut éditer une ou plusieurs étiquettes par pointure, et pour corser la première planche a déjà été utilisée en partie (l'utilisateur indique sur une représentation les étiquettes utilisées de la première planche).

Avant (début 2000, D3), sur le poste je créais un fichier temporaire (Paradox sous D3, SQlite, Firebird ou autre ensuite)
- l'inconvénient de Firebird, une installation serveur ou embedded sur chaque poste, pb en cas de double utilisation sur le poste
- l'inconvénient de Paradox (outre l'utilisation du BDE), un planton et le fichier n'est pas supprimé donc risque de traces
- moins de problèmes avec SQlite sauf en cas de double utilisation du programme sur le PC
et faites confiance aux utilisateurs pour les doubles ou plus utilisations

avec une GTT
la structure de ma table GTT, fixe : ETIQUETTE(numeroetiquette,nom,pointure) avec un clé primaire sur numeroetiquette assez facile à remplir via une procédure ou par programme. Une fois l'édition faite un DELETE from ETIQUETTE vide la table. Cependant quand j'écris vide la table c'est partiellement faux car si j'ai deux utilisateurs sur des PC différents ou encore deux fois le même programme qui tourne sur le PC seules les étiquettes du programme seront effacées (pas celles de l'autre utilisateur ou de l'autre programme)
les perfs ? identiques à l'utilisation d'une table "normale"

bref, on a affaires à une table "normale", multi utilisateurs mais seules les données de l'utilisateur sont visible par ce dernier un peu comme si on avait rajouté une identification par connexion dans la table et que les accès aux données étaient faites au travers de ce filtre (WHERE IDENTIFICATION=CONNEXIONUTILISATEUR)
mais tout ça sans avoir à le gérer !

[castorcharly]

SergioMaster,

Merci pour cet exemple concret d'utilisation des GTT, ça peut sûrement me servir dans un cas spécifique,
mais pas dans le cas présent où les lignes sont stockées pour consultation pendant un temps déterminé (en jours, semaine ou mois),
consultable par des centaines de connexions différentes(non simultanées, seulement une centaine en simultané).

Pour le coté de modifier la structure, la pratique ne m'a jamais montré de danger et comme indiqué
je l'utilise depuis très longtemps sans aucun soucis. J'ai des bases qui tournent depuis plus de 17 ans
qui possèdent ce type de manip et qui n'ont subit pour maintenance que du backup/restore régulier
et une ou deux MàJ vers une version plus récente (1.5 vers 2 puis 2.5 actuelle),
avec des volumes de plusieurs giga en mono fichier ou de plusieurs Tera en multi fichier.
Rien qu'en insert c'est de l'ordre de 100 000 lignes/jours, avec blob/base sur une trentaine de base.

En tout cas, merci à toi et Artemus de vous être penché sur mon petit problème.

[Artemus24]

Salut castorcharly.

Je n'ai pas encore utilisé ce genre de structure de table, il faudrait que j'y vois un interet et aussi les performances.

Une table temporaire se gère comme une table normal. L'intérêt dépend de ce que vous cherchez à faire.
Par exemple, vous faites une extraction et que celle-ci est fréquemment utilisée dans votre application.
Ou encore une table de travail pour faire de la mise en forme avant de créer un fichier excel.
Question performance, c'est pareil qu'une table normale.
--> https://ib-aid.com/en/articles/45-wa...bird-database/
Regardez le point 17

La structure et la mise en oeuvre du script me paraissent bien compliqué, dès la lecture de ton exemple.

Quel exemple ?

je ne vois pas où est la réserve que tu indiques sur le fait de pratiquer un DROP d'une table par rapport au noyau de FB.

Je ne parlais pas du "drop" mais du "delete" dans le noyau de FB.

Pour ce qui concerne le fait de détruire une table, c'est très simple, c'est bien plus rapide que de faire un delete de toutes les lignes.

Il y a de l'eau dans le gaz !

Le delete donné en exemple par SergioMaster ne détruit pas les lignes contenues dans votre table, mais détruit la référence de ta table dans le metamodèle de FB.
Et personne n'a indiqué qu'il fallait préconnier un delete pour détruire le contenu des lignes dans une table.

Ensuite sous MySql, il existe le "truncate table" qui permet de remettre à zéro le contenu d'une table. C'est l'équivalent d'un delete mais en bien plus rapide.
L'équivalent de cette astuce sous FireBird, est de faire un "recreate table".

Alors qu'un drop et un recréate est très simple à mettre en oeuvre, très peu gourmand en ressource

Le "drop" est inutile si vous faites un "recreate" puisque le "recreate" le fait déjà !

L'avantage de le faire dans une PS est que c'est centralisé dans la structure, que tout se passe en local:

Une procédure stockées ne sert à pas à faire du DDL (Define Data Language), c'est-à-dire à créer, modifier ou supprimer la structure de vos données.
On fait avec du DML (Data Managment Language), c'est-à-dire des extractions, insertions, modifications et suppression de lignes dans vos tables.

Donc vous détournez de son usage premier les procédures stockées pour faire des choses qui n'ont pas été prévues dès le départ.
Encore une fois, c'est le rôle des scripts de faire de l'administration et non aux procédures stockées. Pourquoi ?
Car c'est bien plus rapide de le faire en utilisant les outils de FireBird, au lieu de les réinventer !

La suppression de la table contenant plusieurs centaine de milliers de ligne avec champ blob volumineux est très rapide,
ce que je pratique depuis des années sur des centaines de bases de données, sans aucun inconvénient.

Je ne dis pas le contraire, mais il y a une façon correcte de le faire.

Jusqu'à present, je lançais ceci en dehors de FB par commande via mes services, je trouvais que l'intégrer dans une PS était plus efficace car je gère quelques dizaines de bases strictement identique en structure.

En quoi est-ce plus efficace de l'intégrer dans une procédure stockée ?

Et que faites-vous après avoir recréé vos tables ? Sont-elles vides ?
Comme je le suppose, vous désirez purger les données les plus anciennes.
C'est avec l'utilitaire "gbak" que l'on fait ce genre de manipulation car c'est bien plus performant.
Je pense que vous ne tenez pas compte de l'aspect administration de vos bases de données.
Un des aspects qui n'existe pas sous FireBird < 3.0.0. est la notion de partitionnement des tables.
Je ne me suis pas encore intéressé à cet aspect de FireBird 3.0.0.

je n'avais pas penser au "execute statement"

Sur le moment, moi non plus. C'est en faisant quelques recherches que je me suis aperçu qu'on pouvait le faire.

@ SergioMaster : en général, j'utilise très peu les tables temporaires, sauf pour optimiser les requêtes dont la sous-reqête est fréquemment appelé dans une application.
C'est-à-dire eviter d'extraire à nouveau ce qui a déjà été fait au préalable dans une étape précédente de l'application.
C'est comme qui dirait, une façon de découper les traitements afin de les optimiser.

Ou encore pour faire de la mise en forme dans le cas de la création de fichier Excel.

Intéressant cette façon particulière d'utiliser les tables temporaires multi-utilisateurs.

@+