Firebird SQL
CREATION D'UNE BASE
isql -s 1 (met en dialect 1 isql)
SQL> CREATE DATABASE "C:\mabase.fdb" page_size 8192 user 'SYSDBA' password 'masterkey';
BACKUP D'UNE BASE
gbak -user SYSDBA -pass masterkey -b -t 192.168.0.1:c:/bases/mabase.fdb /home/firebird/mabase.fbk
RESTORE D'UNE BASE
gbak -user SYSDBA -pass masterkey -c -v /home/firebird/mabase.fbk 192.168.0.1:c:/bases/manouvellebase.fdb
RESTORE D'UNE BASE AVEC DES PROBLEMES DE DONNEES
GBAK -create -V -ignore -garbage -R -O -user sysdba -password masterkey /home/firebird/mabase.fbk 192.168.0.1:c:/bases/manouvellebase.fdb
GET VERSION FIREBIRD
SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database
CHANGEMENT DE DIALECT
gfix C:\mabase.fdb -user SYSDBA -password masterkey -sql_dialect 1
AJOUT D'UN UTILISATEUR
server:~# gsec
GSEC> add MONUTILISATEUR -pw MON_MOT_DE_PASSE
GSEC> q
CHANGEMENT DU MOT DE PASSE SYSDBA
gsec -user SYSDBA -password masterkey -modify SYSDBA -pw NOUVEAU_MOT_DE_PASSE
SELECT EN LIGNE DE COMMANDE
echo "select * from ma_table;" | isql-fb -user sysdba -pass masterkey C:\mabase.fdb
FONCTION EXTRACT FROM DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEKDAY, YEARDAY
SELECT * FROM MA_TABLE
WHERE extract(hour from champs_date)=8
AND extract(minute from champs_date)<30
CALCULS DE DATE
dateadd (28 day to CURRENT_TIMESTAMP)
dateadd (-6 hour to CURRENT_TIMESTAMP)
FONCTION CASE EN SQL
SELECT NO_CLIENT,
SUM(CASE TYPE_DOCUMENT
WHEN '1' THEN MONTANT
WHEN '2' THEN -MONTANT
END) AS CA
FROM MA_TABLE
SELECT DANS LE SELECT
SELECT MT.NO_ARTICLE,
(SELECT A.LIBELLE FROM ARTICLE A WHERE A.NO_ARTICLE=MT.NO_ARTICLE) AS LIBELLE
FROM MA_TABLE MT
SELECT SUR LE GENERATEUR PR RECUP SA VALEUR
SELECT GEN_ID(MON_GENERATEUR,1) FROM RDB$DATABASE;
RECHERCHER DANS TOUTES LES SOURCES DE TOUS LES TRIGGERS
SELECT R.* FROM RDB$TRIGGERS R
WHERE R.RDB$TRIGGER_SOURCE containing 'YOUR SEARCH'
Liste des champs par table
SELECT F.RDB$RELATION_NAME, F.RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS F
JOIN RDB$RELATIONS R ON F.RDB$RELATION_NAME = R.RDB$RELATION_NAME
AND R.RDB$VIEW_BLR IS NULL
AND (R.RDB$SYSTEM_FLAG IS NULL OR R.RDB$SYSTEM_FLAG = 0)
ORDER BY 1, F.RDB$FIELD_POSITION;
LISTE DES CHAMPS PAR DOMAINE
SELECT RDB$RELATION_NAME,
RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS
WHERE RDB$FIELD_SOURCE = :DOMAINE_RECHERCHE;
INSERT EN FONCTION DEPUIS UN SELECT
INSERT INTO TABLE1
SELECT ID, 'BRL', 'BRAZILIAN REAL', CHPS2, CHPS3, CHPS4
FROM TABLE2 T2
WHERE NOT EXISTS(SELECT ID FROM TABLE1 T1 WHERE T1.ID=T2.ID);
DESACTIVER/ACTIVER UN TRIGGER
ALTER TRIGGER trigger_name INACTIVE;
ALTER TRIGGER trigger_name ACTIVE;
CHANGEMENT DE TYPE D UNE COLONNE
ALTER TABLE MA_TABLE ALTER EMAIL TYPE VARCHAR(60);
CHANGEMENT DE NOM D UNE COLONNE
ALTER TABLE MA_TABLE ALTER COLONNE1 TO COLONNE2;
CHANGEMENT DE PLACE D UNE COLONNE
ALTER TABLE MA_TABLE ALTER COLONNE1 POSITION 999;
LEFT JOIN AVEC PROCEDURE STOCKEE
SELECT T1.NO_ARTICLE, T1.MONTANT, P1.TARIF
FROM TABLE1 T1
LEFT JOIN PROCEDURE1 (ARGUMENT1,ARGUMENT2,ARGUMENT3) P1 ON (T1.NO_DOSSIER=P1.NO_DOSSIER AND T1.NO_ARTICLE=P1.NO_ARTICLE)
QUI UTILISE MA PROCEDURE STOCKEE ?
SELECT M.*, MT.MON$USER FROM MON$STATEMENTS M
LEFT JOIN MON$ATTACHMENTS MT ON MT.MON$ATTACHMENT_ID=M.MON$ATTACHMENT_ID
LEFT JOIN UTILISATEUR U ON U.INTERBASE_NAME=MT.MON$USER
WHERE M.MON$SQL_TEXT CONTAINING 'LENOM_DE_LA_PROCEDURE'
CHERCHER UN MOT CLE DANS UNE PROCEDURE STOCKEE
SELECT R.* FROM RDB$PROCEDURES R
WHERE RDB$PROCEDURE_SOURCE containing :MOTCLERECHERCHE
WHERE AVEC DATE - 3 ANS
SELECT * FROM MA_TABLE
WHERE DATE_DOCUMENT>=ADDYEAR(STRIPTIME("NOW"),-3)
ORDER BY DATE_DOCUMENT
WHERE AVEC UN IIF
SELECT T.* FROM TABLE T
WHERE T.CHAMPS1=:CHAMPS1
AND IIF(:CODE_UNIQUE<>-1, T.CODE_UNIQUE, -1)=:CODE_UNIQUE
HAVING COUNT
SELECT NO_CLIENT,COUNT(*)
FROM MA_TABLE
GROUP BY NO_CLIENT
HAVING COUNT(*)>1
UTILISATION DES VUES
WITH CLIENTS_XXX AS (
SELECT distinct(C.NO_TVA) as NO_TVA, count(*) AS NB
FROM CLIENT C
WHERE C.KEY_PRIMARY=700
GROUP BY C.NO_TVA
HAVING COUNT(*) > 1
ORDER BY 2
)
SELECT CX.NO_TVA, C1.NOM_CLI FROM CLIENTS_XXX CX
LEFT JOIN CLIENT C1 ON (C1.KEY_PRIMARY=700 AND C1.NO_TVA=CX.NO_TVA)
GRANT POUR LES PROCEDURES STOCKEES
GRANT EXECUTE ON PROCEDURE MA_PROCEDURE_STOCKEE TO PUBLIC;
ALTER DOMAIN
ALTER DOMAIN TTEL TYPE VARCHAR(30);
GRANT POUR LES TABLES
GRANT SELECT, DELETE, INSERT, UPDATE ON MA_TABLE TO MON_UTILISATEUR;
SUPPRIMER UN CHAMPS
ALTER TABLE MA_TABLE DROP MON_CHAMPS_A_SUPPRIMER;
SUPPRIMER UNE FONCTION D'UN UDF
DROP EXTERNAL FUNCTION
RENVOI LA POSITION DES CHAMPS D'UNE TABLE
SELECT 'ALTER TABLE ' || TRIM(F.RDB$RELATION_NAME) || ' ALTER COLUMN ' || TRIM(F.RDB$FIELD_NAME) || ' POSITION ' || CAST(F.RDB$FIELD_POSITION + 1 AS INTEGER) || ';'
FROM RDB$RELATION_FIELDS F
JOIN RDB$RELATIONS R ON F.RDB$RELATION_NAME = R.RDB$RELATION_NAME
AND R.RDB$VIEW_BLR IS NULL
AND (R.RDB$SYSTEM_FLAG IS NULL OR R.RDB$SYSTEM_FLAG = 0)
WHERE F.RDB$RELATION_NAME IN (:TABLE1, :TABLE2, :TABLE3, :TABLE999)
ORDER BY F.RDB$RELATION_NAME, F.RDB$FIELD_POSITION;
CHERCHER TOUS LES CHAMPS D'UN MEME NOM
SELECT R.* FROM RDB$RELATION_FIELDS R
WHERE UPPER(R.RDB$FIELD_NAME)= UPPER(:MONCHAMPRECHERCHE)
CHERCHER TOUTES LES PRIMARY KEY
SELECT RDB$INDICES.RDB$INDEX_NAME, RDB$INDEX_SEGMENTS.RDB$FIELD_NAME, RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME
FROM RDB$INDICES
LEFT JOIN RDB$INDEX_SEGMENTS ON RDB$INDEX_SEGMENTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME
WHERE
RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
UTILISATION DU TRIM
TRIM( ASCII_CHAR(13) || ASCII_CHAR(10) from NOM_DU_CHAMPS)
BOUCLER SUR UN SELECT SANS PROC STOCKEE
EXECUTE BLOCK AS
declare variable oldid integer;
declare variable newid integer;
BEGIN
newid = 10;
update TABLE set id = id+2000;
for select id from TABLE order by ID into :oldid do
begin
update TABLE set id = :newid where id = :oldid;
newid = newid+1;
end
END
GFIX: Ne pas oublier de faire une copie !!!!
Source : https://www.ibphoenix.com/resources/documents/how_to/doc_5#how-to-analyse-and-repair-a-corrupted-database gfix -shut full -force 0 -user SYSDBA -password masterkey chemin_de_la_base.fdb1. Chercher si la base est corrompue
gfix -v -full -user SYSDBA -password masterkey base.fdb
2. Mend pour fixer la corruption
gfix -mend -user SYSDBA -password masterkey base.fdb
3. Activez le sweeping automatique pour nettoyer
gfix -sweep -user SYSDBA -password masterkey base.fdb
4. Revalider après le mend
gfix -v -full -user SYSDBA -password masterkey base.fdb