par , 07/06/2017 à 19h03 (3182 Affichages)
Utilité : Afin de lire les données d'une base sqlite et de sélectionner des données directement en plsql.
J'utilisais le plugin Firefox "SQlite Manager", mais le besoin de mettre à jour ma base Oracle nécessitait des exports et des transformations sur le fichier d'export.
Certaines informations ne sont pas très clairement documentées.
Sources : https://sqlite.org/fileformat.html et http://forensicsfromthesausagefactor...re-within.html
Un fichier sqlite.db est assez simple à lire :
les 100 premiers caractères correspondent à la description de la base (taille de page, etc..)
Le fichier est découpé en pages qui ont toutes la même taille.
Chaque page correspond à un seul objet (une seule table ou un seul index)
Une page contient soit un ensemble de pointeurs vers d'autres pages, soit les lignes de la table.
Le catalogue des tables,index, etc.. de la base est donné dans la première page (table nommée sqlite_master).
Je n'ai créé qu'une récupération des données des tables. A ce que j'ai lu, certaines tables sont créées sans rowid et ne sont que des indexes.
Installation
1 : Créer une table qui va contenir la liste des objets de la base sqlite (master_table) qui permet d'associer une Table à un n° de page.
1
2
3
4
5
6
7
8
9
| CREATE GLOBAL TEMPORARY TABLE GTT_SQLITE_MASTER
( IDROW NUMBER NOT NULL,
TYPE VARCHAR2(30), -- table, index, trigger
NAME VARCHAR2(255), -- nom sqlite
TABLENAME VARCHAR2(128), -- nom de la table
ROOTPAGE NUMBER, -- Page Initiale de la table
SQLTEXT VARCHAR2(4000) -- Ordre sql de création
)
ON COMMIT PRESERVE ROWS; |
2 : Compiler le package sqlitedb_pkg.sql
Utilisation
Récupérer une base de données Sqlite et la mettre dans une variable de type BLOB.
Dans les exemples suivants, le fichier sqlite.db a été inséré dans une table : WSQLITEDB (NOM VARCHAR2(128) NOT NULL, DB BLOB)
- Toujours à faire en premier : Récupérer le catalogue des tables de la db sqlite
La table est temporaire et garde les lignes au commit. Donc il ne faut le refaire que si on ouvre une nouvelle base sqlite ou si on a fermé sa session.
1
2
3
4
5
6
| DECLARE
vdb BLOB;
BEGIN
SELECT db INTO vdb FROM WSQLITEDB WHERE nom = 'McM'; -- Récupère la db sqlite
SQLDB_PKG.P_LIST_OBJECTS(vdb);
END; |
Voir la liste des tables :
SELECT * FROM GTT_SQLITE_MASTER WHERE TYPE = 'table' ORDER BY tablename
- Function qui renvoie le nombre de lignes d'une table
FUNCTION F_NBLIG (pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2) RETURN NUMBER;
dbms_output.put_line('Nb de ligne de TARTICLE :'|| sqlitedb_pkg.F_NBLIG(vdb, 'TARTICLE'));
Les dates Sqlite sont au format Varchar : '2017-02-22 19:18:52.0'
La fonction F_DATE renvoie la date (niveau seconde, sans le .0)
FUNCTION F_DATE(p_chaine IN VARCHAR2) RETURN DATE;
- Procédure de récupération des lignes d'une table
P_GETROWS(pDB IN OUT NOCOPY BLOB, p_nomtable IN VARCHAR2);
Récupère les enregistrements de la table
Les données sont dans les 3 tableaux du package sqlitedb_pkg : lnum, lchar, lblob
La définition de chaque donnée est dans le tableau ldef du package (contient 'NUM', 'CHAR', 'BLOB')
L'index de ldef est à utiliser sur les tableaux lnum, lchar, lblob suivant ce que contient ldef
ex ldef(5) = 'CHAR' => donnée dans lchar(5)
ldef(8) = 'NUM' => donnée dans lnum(8)
ldef(6) = 'BLOB' => données dans lblob(6)
La variable sqlitedb_pkg.nbcols contient le nombre de colonnes
Attention par défaut on récupère aussi la colonne interne ROWID de sqldb qu'on met en première colonne. Désactivable par la variable sqlitedb_pkg.vgetrowid
S'il n'y a pas de ligne, la variable nbcols = 0
Les tableaux contiennent les données de toutes les lignes de la table. Exemple une table avec 3 colonnes ( nom VARCHAR, dtenaiss VARCHAR, photo BLOB).
Le table contient 2 lignes.
Si sqlitedb_pkg.vgetrowid = TRUE et lancement de P_GETROWS
ldef(1) = 'NUM' ; ldef(2) = 'CHAR' ; ldef(3) = 'CHAR' ; ldef(4) = 'BLOB'; ldef(5) = 'NUM' ; ldef(6) = 'CHAR' ; ldef(7) = 'CHAR' ; ldef(8) = 'BLOB'
sqlitedb_pkg.nbcols = 4
Les index 1-4 = Première ligne : Lnum(1) =rowid ; Lchar(2)=nom ; Lchar(3)=dtenaiss ; Lblob(4)=photo
Les index 5-8 = Seconde ligne : Lnum(5) =rowid ; Lchar(6)=nom ; Lchar(7)=dtenaiss ; Lblob(8)=photo
Si sqlitedb_pkg.vgetrowid = FALSE et lancement de P_GETROWS
ldef(1) = 'CHAR' ; ldef(2) = 'CHAR' ; ldef(3) = 'BLOB'; ldef(4) = 'CHAR' ; ldef(5) = 'CHAR' ; ldef(6) = 'BLOB'
sqlitedb_pkg.nbcols = 3
Les index 1-3 = Première ligne : Lchar(1)=nom ; Lchar(2)=dtenaiss ; Lblob(3)=photo
Les index 4-6 = Seconde ligne : Lchar(4)=nom ; Lchar(5)=dtenaiss ; Lblob(6)=photo
Exemple concret
Récupérer les lignes de TARTICLE (sqlite) qu'on va insérer dans la table TEMP_ARTICLE (oracle)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| 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;
END; |
Si j'ai besoin de récupérer les données d'une autre table, pas besoin de refaire un P_LIST_OBJECTS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| DECLARE
vdb BLOB; v NUMBER;
BEGIN
SELECT db INTO vdb FROM wsqlitedb WHERE nom= 'McM';
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; |
Quelques spécificités
Les bases sqlite sont codées en UTF8 ou UTF16. La variable de package vcharset contient UTF8, UTF16le ou UTF16be
Les accents et symboles € étaient mal récupérés donc j'ai mis un CONVERT vers la fin de la procédure SQLDB avec le charset de mon serveur ('WE8MSWIN1252') dans la variable de package vconvert.
Les données NULL n'ont pas de type, donc je mets les 3 tableaux lnum, lchar et lblob à NULL pour cette donnée, ne sachant pas si c'est un NUM, VARCHAR ou BLOB.
Les nombres à virgule sont décodés avec UTL_RAW.cast_to_binary_double, il faut visiblement arrondir.
Limitations
Je n'ai pas encore testé de lire des tables avec de très nombreuses lignes (je ne sais pas comment est codé la rootpage si le nombre de sous-pages ne rentre pas dans l'espace de la rootpage).
N'hésitez pas à commenter.
Consulter le profil
Marquer ce blog comme lu
