CREATE OR REPLACE PACKAGE SQLITEDB_PKG IS -- V1.0 McM le 24.05.2017 -- v1.1 McM le 04.02.2020 Erreur sur Chaine Vide (N=13) + Nombres négatifs (complément à 2 oublié) -- CREATE GLOBAL TEMPORARY TABLE GTT_SQLITE_MASTER (idrow NUMBER NOT NULL, TYPE VARCHAR2(30), NAME VARCHAR2(255), tablename VARCHAR2(128), rootpage NUMBER, sqltext VARCHAR2(4000)) -- ON COMMIT PRESERVE ROWS; TYPE TABCELLS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; lnum TABCELLS; nbcols NUMBER; TYPE TABCHAR IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; lchar TABCHAR; ldef TABCHAR; TYPE TABBLOB IS TABLE OF BLOB INDEX BY BINARY_INTEGER; lblob TABBLOB; vpagesize NUMBER; vcharset VARCHAR2(10); vconvert VARCHAR2(20) := 'WE8MSWIN1252'; -- Conversion des caractères dans ce charset vgetrowid BOOLEAN := TRUE; -- Faut-il récupérer les rowid sqlite PROCEDURE P_LIST_OBJECTS (pDB IN OUT NOCOPY BLOB); /* P_LIST_OBJECTS : Permet d'insérer dans la table GTT_SQLITE_MASTER le catalogue de la base (Données Master_table). Permet de faire le lien NOMTABLE - Page Interne Obligatoire. A faire en PREMIER DECLARE vdb BLOB; BEGIN SELECT db INTO vdb FROM WSQLITEDB WHERE nom = 'McM'; -- Récupère une DB SQLITE préalablement insérée dans WSQLITEDB sqlitedb_pkg.p_list_objects(vdb); END; Exemple après lancement SELECT tablename, rootpage FROM GTT_SQLITE_MASTER WHERE TYPE = 'table' ORDER BY 1 */ FUNCTION F_NBLIG (pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2) RETURN NUMBER; /* Retourne le nombre d'enregistrements de la table dbms_output.put_line(sqlitedb_pkg.F_NBLIG(vdb, 'MOB_PARTICLE')); */ FUNCTION F_DATE(p_chaine IN VARCHAR2) RETURN DATE; /* Les dates Sqlitedb sont au format Chaine : 2017-02-22 19:18:52.0 Fonction qui renvoit la date de cette chaine. */ PROCEDURE P_GETROWS(pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2); /* Retourne les enregistrements de la table Les données sont dans les tableaux sqlitedb_pkg.lnum, lchar, lblob La définition de chaque donnée est dans sqlitedb_pkg.ldef (NUM, CHAR, BLOB) L'index de ldef est à utiliser sur les tableaux lnum, lchar, lblob . ex ldef(5) = 'CHAR' => donnée dans lchar(5) Attention, toutes les tables ont un ROWID en première colonne Les dates sont au format CHAR. Voir fonction F_DATE. Exemple : Récupérer les lignes de TARTICLE et TSTOCK DECLARE vdb BLOB; v NUMBER; BEGIN SELECT db INTO vdb FROM wsqlitedb WHERE nom= 'McM'; sqlitedb_pkg.vgetrowid := TRUE; sqlitedb_pkg.p_list_objects(vdb); sqlitedb_pkg.vgetrowid := FALSE; -- Je ne veux pas récupérer les rowid des lignes sqlitedb_pkg.P_GETROWS(vdb, 'TARTICLE'); -- On récupère les données de TARTICLE IF sqlitedb_pkg.nbcols > 0 -- Evite de planter si la table est vide. THEN FOR i IN 1.. (sqlitedb_pkg.ldef.COUNT / sqlitedb_pkg.nbcols) LOOP v := (i-1) * sqlitedb_pkg.nbcols + 1; INSERT INTO TEMP_ARTICLE(IDART, LIBART, DATE_CREA) VALUES(sqlitedb_pkg.lnum(v), sqlitedb_pkg.lchar(v+1), sqlitedb_pkg.f_date(sqlitedb_pkg.lchar(v+2))); END LOOP; END IF; sqlitedb_pkg.P_GETROWS(vdb, 'TSTOCK'); IF sqlitedb_pkg.nbcols > 0 -- Evite de planter si la table est vide. THEN FOR i IN 1.. (sqlitedb_pkg.ldef.COUNT / sqlitedb_pkg.nbcols) LOOP v := (i-1) * sqlitedb_pkg.nbcols + 1; INSERT INTO TEMP_STOCK(IDART, QTE) VALUES(sqlitedb_pkg.lnum(v), sqlitedb_pkg.lnum(v+1)); END LOOP; END IF; END */ END; / CREATE OR REPLACE PACKAGE BODY SQLDB_PKG AS PROCEDURE P_LOG(v_chaine IN VARCHAR2, p_force IN VARCHAR2 DEFAULT NULL) IS BEGIN IF g_debug = 'O' OR p_force = 'O' THEN DBMS_OUTPUT.PUT_LINE(v_chaine); END IF; END; -- ++/*///////////////////////////////////++++++++++++++++++++++++ PROCEDURE VARINT (p_raw IN RAW, p_val OUT NUMBER, p_bytes OUT NUMBER) IS -- Renvoie la valeur en VARINT et le nb d'octets utilisés (pour les décalages suivants) var NUMBER; i NUMBER := 0; nb NUMBER := 0; BEGIN LOOP var := TO_NUMBER(SUBSTR(p_raw,2*i+1,2), 'XX'); P_LOG('Varint :'|| SUBSTR(p_raw,2*i+1,2) ||' ('|| var ||')'); IF var >= 128 THEN i := i + 1; nb := nb * 128 + var - 128; ELSE i := i + 1; p_val := nb * 128 + var; p_bytes := i; -- nb de bytes utilisés EXIT; END IF; END LOOP; END; --- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ FUNCTION F_ROOTPAGE(p_nomtable IN VARCHAR2) RETURN NUMBER IS v_page NUMBER; BEGIN SELECT rootpage INTO v_page FROM GTT_SQLITE_MASTER WHERE NAME = p_nomtable AND TYPE = 'table'; RETURN v_page; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20005,'Aucune table '|| p_nomtable ||' dans le catalogue. Penser à faire un P_LIST_OBJECTS en premier'); END; ----------- ++++++++++++++++++++++++++++++ FUNCTION F_DATE(p_chaine IN VARCHAR2) RETURN DATE IS BEGIN -- 2017-02-22 19:18:52.0 RETURN TO_DATE(SUBSTR(p_chaine, 1, INSTR(p_chaine,'.')-1), 'RRRR-MM-DD HH24:MI:SS'); END; --- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROCEDURE SQLDB (pDB IN OUT NOCOPY BLOB, p_action IN VARCHAR2, p_page IN NUMBER DEFAULT 1) IS -- p_action : -- D : Display technique -- A : Récup des Cells et Affichage des données -- G : (Get Rows) Récup des données vtotpage NUMBER; tpage VARCHAR2(2); vraw RAW(32766); vheader RAW(200); vlen NUMBER; v NUMBER; vnb NUMBER; vrowid NUMBER; vpayload NUMBER; v_offset NUMBER; vnbh NUMBER; vPageOverflow NUMBER; lrec TABCELLS; lcell TABCELLS; vlob BLOB; vislob NUMBER; vlob2 BLOB; k NUMBER := 0; vnbcells NUMBER; l NUMBER; BEGIN lrec.DELETE; lcell.DELETE; -- Pour mettre les données des cells IF p_action = 'G' THEN k := ldef.COUNT; ELSE ldef.DELETE; lnum.DELETE; lchar.DELETE; lblob.DELETE; k := 0; END IF; nbcols := 0; vislob := 0; vpagesize := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, 17),'XXXX'); vtotpage := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, 29),'XXXXXXXX'); P_LOG('Pagesize:'|| vpagesize ||' Page ' || p_page ||' / '|| vtotpage); tpage := dbms_lob.SUBSTR(pDB, 1, CASE WHEN p_page = 1 THEN 101 ELSE (p_page -1)* vpagesize + 1 END); P_LOG('TYPE:'||tpage ||' ' || CASE WHEN tpage = '02' THEN 'interior INDEX b-tree page' WHEN tpage = '05' THEN 'interior TABLE b-tree page' WHEN tpage = '0A' THEN 'leaf INDEX b-tree page' WHEN tpage = '0D' THEN 'leaf TABLE b-tree page' ELSE 'Non défini' END); IF p_action = 'D' THEN IF p_page = 1 THEN v_offset := 101; ELSE v_offset := (p_page -1) * vpagesize + 1; END IF; P_LOG('First Freeblock:'|| TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset+1),'XXXX'), 'O'); P_LOG('NB Cells:'|| TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset+3),'XXXX'), 'O'); P_LOG('Start Cells Content Area 0=65536:'|| dbms_lob.SUBSTR(pDB, 2, v_offset+5), 'O'); P_LOG('nbFrag Cells Content Area :'|| TO_NUMBER(dbms_lob.SUBSTR(pDB, 1, v_offset+7),'XX'), 'O'); IF tpage IN ('02', '05') THEN P_LOG('RIGHT-most pointer :'|| dbms_lob.SUBSTR(pDB, 4, v_offset+8), 'O'); END IF; ELSE -- Lecture des Offset des Cells v_offset := (p_page -1) * vpagesize + CASE WHEN p_page = 1 THEN 101 ELSE 1 END; P_LOG(dbms_lob.SUBSTR(pDB, 20, v_offset +3)); -- P_LOG('nbFrag Cells Content Area :'|| TO_NUMBER(dbms_lob.SUBSTR(pDB, 1, v_offset+7),'XX'), 'O'); -- Nb de cells vnbcells := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset+3),'XXXX'); P_LOG('NB cells:'|| vnbcells); v_offset := v_offset + CASE WHEN tpage IN ('02', '05') THEN 12 ELSE 8 END; FOR i IN 1 .. vnbcells LOOP lcell(i) := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset), 'XXXX'); v_offset := v_offset + 2; END LOOP; IF lcell.LAST IS NOT NULL THEN FOR i IN lcell.FIRST..lcell.LAST LOOP P_LOG('Cellule N° '|| i ||' : '|| TO_CHAR(lcell(i),'FM000X') ); v_offset := (p_page -1) * vpagesize + lcell(i) +1; IF tpage = '0D' THEN -- Table B-Tree Leaf Cell (header 0x0d) : VARINT(payload), VARINT(rowid), payload(hors-dépassement),4octets:PageNumberOverflow[Null si pas de overflow] vraw := dbms_lob.SUBSTR(pDB, 20, v_offset); VARINT(vraw, v, vnb); -- Récupère la donnée de la cell P_LOG('Payload:'|| vraw ||' : '|| v ||' '|| vnb); vpayload := v; v_offset := v_offset + vnb; VARINT(SUBSTR(vraw, 2*vnb+1), vrowid, vnb); v_offset := v_offset + vnb; P_LOG('Len Payload :'|| vpayload ||' offset :'||v_offset ||' offsetpage='|| MOD(v_offset, vpagesize) ||' fin:'|| (vpayload+MOD(v_offset, vpagesize) - 1) ||' >? '|| vpagesize); -- Si les datas dépassent les 32 000 cat, on passe en BLOB -- Je considère que les pagesizes font moins de 32000 et qu'on est automatiquement en overflow IF vpayload > 32000 THEN vislob := 1; -- On va gérer les datas en BLOB DBMS_LOB.createtemporary(lob_loc => vlob, CACHE => TRUE); vPageOverflow := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, p_page * vpagesize - 3), 'XXXXXXXX'); vlen := vpagesize - MOD(v_offset, vpagesize) - 3; WHILE vpayload > 0 LOOP -- Page Overflow : 4 derniers Bytes de la page. P_LOG( 'PageOverflow:'|| TO_CHAR(vPageOverflow,'FM0000000X')); DBMS_LOB.COPY(dest_lob => vlob, src_lob => pDB, amount => vlen, dest_offset => DBMS_LOB.GETLENGTH(vlob)+1, src_offset => v_offset); --UTL_RAW.CONCAT(vraw, dbms_lob.SUBSTR(pDB, vlen, v_offset)); vpayload := vpayload - vlen; P_LOG('LOB ('|| DBMS_LOB.GETLENGTH(vlob)||') récup de :'|| vlen ); v_offset := (vPageOverflow -1) * vpagesize + 1; vPageOverflow := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, v_offset), 'XXXXXXXX'); v_offset := v_offset + 4; vlen := LEAST(vpayload, vpagesize - 4); P_LOG('Reste :'|| vpayload ||' len:'|| vlen); END LOOP; -- On récupère pour les headers vraw := DBMS_LOB.SUBSTR(vlob, 2000, 1); -- Si les datas dépassent la fin de page, on est en overflow. ELSIF vpayload + MOD(v_offset, vpagesize) - 1 > vpagesize THEN vraw := ''; vPageOverflow := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, p_page * vpagesize - 3), 'XXXXXXXX'); vlen := vpagesize - MOD(v_offset, vpagesize) - 3; WHILE vpayload > 0 LOOP -- Page Overflow : 4 derniers Bytes de la page. P_LOG( 'PageOverflow:'|| TO_CHAR(vPageOverflow,'FM0000000X')); vraw := UTL_RAW.CONCAT(vraw, dbms_lob.SUBSTR(pDB, vlen, v_offset)); vpayload := vpayload - vlen; P_LOG('Raw('|| LENGTH(vraw)||') récup de :'|| vlen ||' = '||SUBSTR(vraw,0,10) || '...'|| SUBSTR(vraw, LENGTH(vraw)-10)); v_offset := (vPageOverflow -1) * vpagesize + 1; vPageOverflow := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, v_offset), 'XXXXXXXX'); v_offset := v_offset + 4; vlen := LEAST(vpayload, vpagesize - 4); P_LOG('Reste :'|| vpayload ||' len:'|| vlen); END LOOP; ELSE vraw := dbms_lob.SUBSTR(pDB, vpayload, v_offset); END IF; -- P_LOG(vraw ||' : '|| v ||' '|| vnb); VARINT(vraw, vnbh, vnb); -- Nb de bytes de Header vheader := SUBSTR(vraw, 3, 2*vnbh - 2); -- contient les def des colonnes IF vislob = 1 THEN v_offset := vnbh + 1; -- offset des datas. ELSE vraw := SUBSTR(vraw, 2*vnbh+1); -- Contient les datas v_offset := 1; END IF; P_LOG('Header:'|| vheader ||' ('|| vnbh ||')'); P_LOG('Rowid : '|| vrowid); IF vislob = 1 THEN P_LOG('Datas BLOB |'|| DBMS_LOB.GETLENGTH(vlob) ||'] '|| DBMS_LOB.SUBSTR(vlob, 10, 1)); ELSE P_LOG('Datas ('|| LENGTH(vraw) ||') :'|| vraw); END IF; nbcols := 0; -- On insère le ROWID IF vgetrowid THEN k := k + 1; ldef(k) := 'NUM'; lnum(k) := vrowid; nbcols := 1; END IF; FOR i IN 1..vnbh LOOP nbcols := nbcols + 1; P_LOG(vheader); VARINT(vheader, v, vnb); lrec(i) := v; vheader := SUBSTR(vheader, 2*vnb+1); EXIT WHEN vheader IS NULL; END LOOP; FOR i IN lrec.FIRST .. lrec.LAST LOOP /*0 0 Value is a NULL. 1 1 Value is an 8-bit twos-complement integer. 2 2 Value is a big-endian 16-bit twos-complement integer. 3 3 Value is a big-endian 24-bit twos-complement integer. 4 4 Value is a big-endian 32-bit twos-complement integer. 5 6 Value is a big-endian 48-bit twos-complement integer. 6 8 Value is a big-endian 64-bit twos-complement integer. 7 8 Value is a big-endian IEEE 754-2008 64-bit floating point number. 8 0 Value is the integer 0. (Only available for schema format 4 and higher.) 9 0 Value is the integer 1. (Only available for schema format 4 and higher.) 10,11 Not used. Reserved for expansion. N=12 and even (N-12)/2 Value is a BLOB that is (N-12)/2 bytes in length. N=13 and odd (N-13)/2 Value is a string in the text encoding and (N-13)/2 bytes in length. The nul terminator is not stored. */ P_LOG(i||':'|| lrec(i) ||' ' || k ||' '); IF lrec(i) = 0 THEN P_LOG('NULL'); k := k + 1; ldef(k) := 'NUM'; lnum(k) := NULL; lchar(k) := NULL; lblob(k) := NULL; -- On met Num ou Char pour simplifier le catalogue (et éviter le no_data_found en récupération ELSIF lrec(i) BETWEEN 1 AND 4 THEN k := k + 1; ldef(k) := 'NUM'; IF vislob = 1 THEN P_LOG('Integer ['|| lrec(i) ||'] => ' || TO_NUMBER(DBMS_LOB.SUBSTR(vlob, lrec(i), v_offset), LPAD('X',2* lrec(i), 'X')) ||' [compl2]'); lnum(k) := TO_NUMBER(DBMS_LOB.SUBSTR(vlob, lrec(i), v_offset), LPAD('X',2* lrec(i), 'X')); v_offset := v_offset + lrec(i); ELSE P_LOG('Integer ['|| lrec(i) ||'] => ' || TO_NUMBER(SUBSTR(vraw, v_offset, 2* lrec(i)), LPAD('X',2* lrec(i), 'X')) ||' [compl2]'); lnum(k) := TO_NUMBER(SUBSTR(vraw, v_offset, 2* lrec(i)), LPAD('X',2* lrec(i), 'X')); v_offset := v_offset + 2*lrec(i); END IF; -- On fait le complément à 2 IF lnum(k) >= POWER(2, 8 * lrec(i))/2 THEN lnum(k) := lnum(k) - POWER(2, 8 * lrec(i)); END IF; ELSIF lrec(i) = 5 THEN k := k + 1; ldef(k) := 'NUM'; IF vislob = 1 THEN P_LOG(k ||' Integer [6] => ' || TO_NUMBER(DBMS_LOB.SUBSTR(vlob, 6, v_offset), LPAD('X',2*6, 'X') ) ||' [compl2]'); lnum(k) := TO_NUMBER(DBMS_LOB.SUBSTR(vlob, 6, v_offset), LPAD('X',2*6, 'X') ); v_offset := v_offset + 6; ELSE P_LOG(k ||' Integer [6] => ' || TO_NUMBER(SUBSTR(vraw, v_offset, 2*6), LPAD('X',2*6, 'X') ) ||' [compl2]'); lnum(k) := TO_NUMBER(SUBSTR(vraw, v_offset, 2*6), LPAD('X',2*6, 'X') ); v_offset := v_offset + 2*6; END IF; -- On fait le complément à 2 IF lnum(k) >= POWER(2, 48)/2 THEN lnum(k) := lnum(k) - POWER(2, 48); END IF; ELSIF lrec(i) = 6 THEN k := k + 1; ldef(k) := 'NUM'; IF vislob = 1 THEN P_LOG(k ||' Integer [8] => ' || TO_NUMBER(DBMS_LOB.SUBSTR(vlob, 8, v_offset), LPAD('X',2*8, 'X') ) ||' [compl2]'); lnum(k) := TO_NUMBER(DBMS_LOB.SUBSTR(vlob, 8, v_offset), LPAD('X',2*8, 'X') ); v_offset := v_offset + 8; ELSE P_LOG(k ||' Integer [8] => ' ||TO_NUMBER(SUBSTR(vraw, v_offset, 2*8), LPAD('X',2*8, 'X') ) ||' [compl2]'); lnum(k) := TO_NUMBER(SUBSTR(vraw, v_offset, 2*8), LPAD('X',2*8, 'X') ); v_offset := v_offset + 2*8; END IF; -- On fait le complément à 2 IF lnum(k) >= POWER(2, 64)/2 THEN lnum(k) := lnum(k) - POWER(2, 64); END IF; ELSIF lrec(i) = 7 THEN k := k + 1; ldef(k) := 'NUM'; IF vislob = 1 THEN P_LOG(k ||' Float [8] => ' || UTL_RAW.cast_to_binary_double(DBMS_LOB.SUBSTR(vlob, 8, v_offset))); lnum(k) := UTL_RAW.cast_to_binary_double(DBMS_LOB.SUBSTR(vlob, 8, v_offset)); v_offset := v_offset + 8; ELSE P_LOG(k ||' Float [8] => ' || UTL_RAW.cast_to_binary_double(UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, 8))); lnum(k) := UTL_RAW.cast_to_binary_double(UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, 8)); v_offset := v_offset + 2*8; END IF; ELSIF lrec(i) = 8 THEN k := k + 1; ldef(k) := 'NUM'; lnum(k) := 0; P_LOG(k ||' Integer 0'); ELSIF lrec(i) = 9 THEN k := k + 1; ldef(k) := 'NUM'; lnum(k) := 1; P_LOG(k ||' Integer 1'); ELSIF lrec(i) > 11 AND MOD(lrec(i),2) = 0 THEN k := k + 1; ldef(k) := 'BLOB'; IF lrec(i) = 12 THEN P_LOG(k ||' Blob [0] offset :'|| v_offset ||' = Null'); lblob(k) := NULL; ELSIF vislob = 1 THEN P_LOG(k ||' Blob ['|| ((lrec(i) - 12) / 2) ||'] offset :'|| v_offset ||' = '|| DBMS_LOB.SUBSTR(vlob, 10, v_offset) ||'...'); -- Il y a peut-être plus simple que de créer un second LOB. (copier dans vblob(k) ne fonctionne pas). DBMS_LOB.createtemporary(lob_loc => vlob2, CACHE => TRUE); DBMS_LOB.COPY(dest_lob => vlob2, src_lob => vlob, amount => (lrec(i) - 12) / 2, dest_offset => 1, src_offset => v_offset); lblob(k) := vlob2; DBMS_LOB.FREETEMPORARY(vlob2); v_offset := v_offset + (lrec(i) - 12) / 2 ; ELSE P_LOG(k ||' Blob ['|| ((lrec(i) - 12) / 2) ||'] offset :'|| v_offset); lblob(k) := UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, (lrec(i) - 12) / 2); v_offset := v_offset + (lrec(i) - 12) ; END IF; ELSIF lrec(i) > 11 AND MOD(lrec(i),2) = 1 THEN k := k + 1; ldef(k) := 'CHAR'; IF lrec(i) = 13 THEN P_LOG(k ||' Varchar2 [0] offset :'|| v_offset ||' = Null'); lchar(k) := NULL; ELSIF vislob = 1 THEN P_LOG(k ||' Varchar2 ['|| ((lrec(i) - 13) / 2) ||'] => ' || DBMS_LOB.SUBSTR(vlob, (lrec(i) - 13) / 2, v_offset)); lchar(k) := DBMS_LOB.SUBSTR(vlob, (lrec(i) - 13) / 2, v_offset); v_offset := v_offset + (lrec(i) - 13) / 2; ELSE P_LOG(k ||' Varchar2 ['|| ((lrec(i) - 13) / 2) ||'] => ' || UTL_RAW.cast_to_varchar2(UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, (lrec(i) - 13) / 2 ) )); -- Pour gérer les accents et les symboles Euros, il faut transformer le charset de la sqlitedb (UTF8) en charset de la base. IF vconvert IS NOT NULL THEN lchar(k) := CONVERT(UTL_RAW.cast_to_varchar2(UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, (lrec(i) - 13) / 2 )), vconvert, vcharset); ELSE lchar(k) := UTL_RAW.cast_to_varchar2(UTL_RAW.SUBSTR(vraw, (v_offset+1)/2, (lrec(i) - 13) / 2 )); END IF; v_offset := v_offset + (lrec(i) - 13); END IF; END IF; END LOOP; IF vislob = 1 THEN DBMS_LOB.FREETEMPORARY(vlob); vislob := 0;END IF; ELSIF tpage = '05' THEN -- Table B-Tree Interior Cell (header 0x05): 4octets (big-endian) page number du left-child pointer | A VARINT : integer key vraw := dbms_lob.SUBSTR(pDB, 8, v_offset); P_LOG(vraw); vnb := UTL_RAW.CAST_TO_BINARY_INTEGER(SUBSTR(vraw,1,8)); P_LOG('Page Num:'||vnb); SQLDB (pDB, 'A', vnb); v_offset := v_offset + 4; VARINT(SUBSTR(vraw, 8+1), vrowid, vnb); P_LOG('Rowid:'||vrowid); END IF; END LOOP; -- Fin boucle des cells -- On affiche ligne par ligne IF p_action = 'A' THEN FOR i IN 1.. (ldef.COUNT / nbcols) LOOP P_LOG('AFFICHAGE de '|| i ||' / '|| ldef.COUNT ||' nbcol:'|| nbcols, 'O'); FOR j IN 1 .. nbcols LOOP v := (i-1) * nbcols + j; P_LOG(v); IF ldef(v) = 'NUM' THEN P_LOG(CHR(9)||'N '|| lnum(v), 'O'); ELSIF ldef(v) = 'CHAR' THEN P_LOG(CHR(9)||'C '|| lchar(v),'O'); -- ELSIF ldef(v) = 'BLOB' THEN P_LOG(CHR(9)||'B '|| UTL_RAW.CAST_TO_VARCHAR2(lblob(v)), 'O'); ELSIF ldef(v) = 'BLOB' THEN P_LOG(CHR(9)||'Blob ['|| LENGTH(lblob(v)) ||']', 'O'); END IF; END LOOP; END LOOP; END IF; END IF; -- lcell.LAST IS NOT NULL END IF; -- Fin p_action END SQLDB; ----++++++++++++++++++++ ++++++++++++++++++ +++++++++++++++++++++++ PROCEDURE P_GETROWS(pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2) -- Récupère les lignes d'une TABLE. -- Si nomtable est NULL => récupère le catalogue des tables de la base -- /!\ : Première colonne est toujours un ROWID IS v_page NUMBER; v_offset NUMBER; tpage VARCHAR2(2); vnb NUMBER; vlastpage NUMBER; TYPE TABCELLS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; lcell TABCELLS; vraw RAW(4000); BEGIN lcell.DELETE; ldef.DELETE; lnum.DELETE; lchar.DELETE; lblob.DELETE; IF p_nomtable IS NOT NULL THEN -- On récupère la RootPage de la table v_page := F_ROOTPAGE(p_nomtable); ELSE v_page := 1; END IF; IF v_page = 1 THEN v_offset := 101; ELSE v_offset := (v_page -1) * vpagesize + 1; END IF; P_LOG('Page '|| v_page ||' PageSize : '|| vpagesize); tpage := dbms_lob.SUBSTR(pDB, 1, v_offset); IF tpage = '05' THEN -- Nb de cells vnb := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset+3),'XXXX'); P_LOG('NB cells:'|| vnb); vlastpage := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, v_offset+8), 'XXXXXXXX'); P_LOG('RIGHT-most pointer :'|| vlastpage); v_offset := v_offset + 12; -- On récupère les offset des données des cells de la page FOR i IN 1 .. vnb LOOP lcell(i) := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset), 'XXXX'); v_offset := v_offset + 2; END LOOP; IF lcell.LAST IS NOT NULL THEN FOR i IN lcell.FIRST..lcell.LAST LOOP P_LOG('Page '|| v_page ||' Cellule N° '|| i ||' : '|| TO_CHAR(lcell(i),'FM000X') ); v_offset := (v_page -1) * vpagesize + lcell(i) + 1; -- Table B-Tree Interior Cell (header 0x05): 4octets (big-endian) page number du left-child pointer | A VARINT : integer key vraw := dbms_lob.SUBSTR(pDB, 8, v_offset); P_LOG(vraw); vnb := UTL_RAW.CAST_TO_BINARY_INTEGER(SUBSTR(vraw,1,8)); P_LOG('Page Num:'||vnb); -- On Récupère ce qu'il y a dans la page SQLDB (pDB, 'G', vnb); --v_offset := v_offset + 4; VARINT(SUBSTR(vraw, 8+1), vrowid, vnb); P_LOG('Rowid:'||vrowid); END LOOP; -- Fin boucle des cells END IF; -- lcell.LAST IS NOT NULL IF vlastpage > 1 -- Je ne suis pas sûr de ce qu'il faut faire comme test. THEN P_LOG('Page '|| v_page ||' Last Page ['|| vlastpage ||']'); -- On Récupère ce qu'il y a dans la page SQLDB (pDB, 'G', vlastpage); END IF; ELSIF tpage = '0D' THEN SQLDB (pDB, 'G', v_page); END IF; END P_GETROWS; ---- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ PROCEDURE P_LIST_OBJECTS (pDB IN OUT NOCOPY BLOB) IS v NUMBER; BEGIN IF NOT vgetrowid THEN RAISE_APPLICATION_ERROR(-20001, 'La variable vgetrowid doit être à TRUE pour cette opération'); END IF; DELETE GTT_SQLITE_MASTER; -- On mémorise les données utiles en variable de package. vpagesize := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, 17),'XXXX'); vcharset := CASE TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, 57),'XXXXXXXX') WHEN 1 THEN 'UTF8' WHEN 2 THEN 'UTF16le' WHEN 3 THEN 'UTF16be' END; P_LOG('Pagesize:'|| vpagesize ||' Charset :'|| vcharset); P_GETROWS(pDB => pDB, p_nomtable => ''); -- On Insère tout ce que l'on a récupéré FOR i IN 1.. (ldef.COUNT / nbcols) LOOP v := (i-1) * nbcols + 1; INSERT INTO GTT_SQLITE_MASTER(IDROW, TYPE, NAME, TABLENAME, ROOTPAGE, SQLTEXT) VALUES (lnum(v), lchar(v+1), lchar(v+2), lchar(v+3), lnum(v+4), lchar(v+5)); END LOOP; END P_LIST_OBJECTS; ---- ++++++++++++++++++++++++++++++++++++++++++++++++++ FUNCTION F_NBLIG_PAGE (pDB IN OUT NOCOPY BLOB, p_page IN NUMBER) RETURN NUMBER IS /* Fonction Interne appelée par F_NBLIG*/ vnblig NUMBER := 0; vnbcells NUMBER; vlastpage NUMBER; v_offset NUMBER; tpage VARCHAR2(2); lcell TABCELLS; vraw RAW(32766); vnb NUMBER; BEGIN lcell.DELETE; P_LOG('Page ' || p_page); tpage := dbms_lob.SUBSTR(pDB, 1, CASE WHEN p_page = 1 THEN 101 ELSE (p_page -1)* vpagesize + 1 END); -- Lecture des Offset des Cells IF p_page = 1 THEN v_offset := 101; ELSE v_offset := 1; END IF; v_offset := (p_page -1) * vpagesize + v_offset; vnbcells := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset+3),'XXXX'); P_LOG('NbCells:'|| vnbcells); vlastpage := TO_NUMBER(dbms_lob.SUBSTR(pDB, 4, v_offset+8), 'XXXXXXXX'); P_LOG('RIGHT-most pointer :'|| vlastpage); -- Nb de cells IF tpage = '0D' THEN RETURN vnbcells; ELSIF tpage = '05' THEN -- On va lire l'arbre des pages 0D v_offset := v_offset + 12; P_LOG('offset :'|| v_offset); FOR i IN 1 .. vnbcells LOOP lcell(i) := TO_NUMBER(dbms_lob.SUBSTR(pDB, 2, v_offset), 'XXXX'); v_offset := v_offset + 2; END LOOP; IF lcell.LAST IS NOT NULL THEN FOR i IN lcell.FIRST..lcell.LAST LOOP P_LOG('Cellule N° '|| i ||' : '|| lcell(i)); v_offset := (p_page -1) * vpagesize + lcell(i) + 1; -- Table B-Tree Interior Cell (header 0x05): 4octets (big-endian) page number du left-child pointer | A VARINT : integer key vraw := dbms_lob.SUBSTR(pDB, 8, v_offset); P_LOG(vraw); vnb := UTL_RAW.CAST_TO_BINARY_INTEGER(SUBSTR(vraw,1,8)); -- On récupère le nb de cells (rows) de la page de destination. vnblig := vnblig + F_NBLIG_PAGE (pDB, vnb); P_LOG('Page Num:'||vnb ||' : '|| vnblig); END LOOP; -- Fin boucle des cells END IF; -- lcell.LAST IS NOT NULL IF vlastpage > 1 THEN -- On affiche ce qu'il y a dans la page vnblig := vnblig + F_NBLIG_PAGE (pDB, vlastpage); P_LOG(' Last Page:'||vlastpage ||' : '|| vnblig); END IF; END IF; RETURN vnblig; END F_NBLIG_PAGE; ----------------------------------------------------- FUNCTION F_NBLIG (pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2) RETURN NUMBER IS BEGIN RETURN F_NBLIG_PAGE(pDB, F_ROOTPAGE(p_nomtable)); END F_NBLIG; /* DOC The first 100 bytes of the database file comprise the database file header. The database file header is divided into fields as shown by the table below. All multibyte fields in the database file header are stored with the most significant byte first (big-endian). Database Header Format Offset Size Description 0 16 The header string: "SQLite format 3\000" 16 2 The database page size in bytes. Must be a power of two between 512 and 32768 inclusive, or the value 1 representing a page size of 65536. 18 1 File format write version. 1 for legacy; 2 for WAL. 19 1 File format read version. 1 for legacy; 2 for WAL. 20 1 Bytes of unused "reserved" space at the end of each page. Usually 0. 21 1 Maximum embedded payload fraction. Must be 64. 22 1 Minimum embedded payload fraction. Must be 32. 23 1 Leaf payload fraction. Must be 32. 24 4 File change counter. 28 4 Size of the database file in pages. The "in-header database size". 32 4 Page number of the first freelist trunk page. 36 4 Total number of freelist pages. 40 4 The schema cookie. 44 4 The schema format number. Supported schema formats are 1, 2, 3, and 4. 48 4 Default page cache size. 52 4 The page number of the largest root b-tree page when in auto-vacuum or incremental-vacuum modes, or zero otherwise. 56 4 The database text encoding. A value of 1 means UTF-8. A value of 2 means UTF-16le. A value of 3 means UTF-16be. 60 4 The "user version" as read and set by the user_version pragma. 64 4 True (non-zero) for incremental-vacuum mode. False (zero) otherwise. 68 4 The "Application ID" set by PRAGMA application_id. 72 20 Reserved for expansion. Must be zero. 92 4 The version-valid-for number. 96 4 SQLITE_VERSION_NUMBER A b-tree page is divided into regions in the following order: The 100-byte database file header (found on page 1 only) The 8 or 12 byte b-tree page header The cell pointer array Unallocated space The cell content area The reserved region. The 100-byte database file header is found only on page 1, which is always a table b-tree page. All other b-tree pages in the database file omit this 100-byte header. B-tree Page Header Format Offset Size Description 0 1 The one-byte flag at offset 0 indicating the b-tree page type. A value of 2 (0x02) means the page is an interior index b-tree page. A value of 5 (0x05) means the page is an interior table b-tree page. A value of 10 (0x0a) means the page is a leaf index b-tree page. A value of 13 (0x0d) means the page is a leaf table b-tree page. Any other value for the b-tree page type is an error. 1 2 The two-byte integer at offset 1 gives the start of the first freeblock on the page, or is zero if there are no freeblocks. 3 2 The two-byte integer at offset 3 gives the number of cells on the page. 5 2 The two-byte integer at offset 5 designates the start of the cell content area. A zero value for this integer is interpreted as 65536. 7 1 The one-byte integer at offset 7 gives the number of fragmented free bytes within the cell content area. 8 4 The four-byte page number at offset 8 is the right-most pointer. This value appears in the header of interior b-tree pages only and is omitted from all other pages. */ END;