Voir le flux RSS

Astuces Dev Oracle

Sqlite db Reader en PLSQL

Noter ce billet
par
McM
, 07/06/2017 à 18h03 (104 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.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
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.
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
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 :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
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;
Code : Sélectionner tout - Visualiser dans une fenêtre à part
dbms_output.put_line('Nb de ligne de TARTICLE :'|| sqlitedb_pkg.F_NBLIG(vdb, 'TARTICLE'));

  • Fonction pour les dates

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)

Code sql : 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
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
Code sql : 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
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.

Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Viadeo Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Twitter Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Google Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Facebook Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Digg Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Delicious Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog MySpace Envoyer le billet « Sqlite db Reader en PLSQL » dans le blog Yahoo

Catégories
Programmation

Commentaires