5dBRrE1q

1/* Load modules */
2let sqlite3 = require('sqlite3').verbose();
3const fs = require('fs');
4const csv = require('csv');
5const parse = require('csv-parse');
6const path = require('path');
7
8/*
9 * Database configuration
10 */
11
12/* Load database file (Creates file if not exists) */
13const db = new sqlite3.Database('database.db');
14//const db = new sqlite3.Database(':memory:');
15
16//Activation des contraintes d'intégrté
17db.get("PRAGMA foreign_keys = ON")
18
19
20
21const createInstallation = function() {
22    return new Promise(function (resolve, reject) {
23        const sqlRequest = "CREATE TABLE IF NOT EXISTS installation (" +
24            "numero_de_l_installation TEXT NOT NULL, " +
25            "nom_usuel_de_l_installation TEXT NOT NULL, " +
26            "code_postal TEXT NOT NULL, " +
27            "nom_de_la_commune TEXT NOT NULL, " +
28            "localisation TEXT NOT NULL, " +
29            "accessibilite_handicapes_a_mobilite_reduite TEXT NOT NULL, " +
30            "PRIMARY KEY (numero_de_l_installation))";
31
32        db.run(sqlRequest,[], (err) => {
33
34            if (err) {
35                console.log(err);
36                reject(err);
37            }
38            else {
39                console.log("Installation créée");
40                resolve(this);
41            }
42        });
43
44    })
45};
46
47const createEquipement = function() {
48    return new Promise(function (resolve, reject) {
49        const sqlRequest = "CREATE TABLE IF NOT EXISTS equipement (" +
50            "numero_de_la_fiche_equipement TEXT NOT NULL," +
51            "numero_de_l_installation TEXT NOT NULL," +
52            "PRIMARY KEY (numero_de_la_fiche_equipement)," +
53            "FOREIGN KEY (numero_de_l_installation) REFERENCES installation(numero_de_l_installation))";
54
55        db.run(sqlRequest, [], (err) => {
56
57            if (err) {
58                console.log(err);
59                reject(err);
60            }
61            else {
62                console.log("Equipement créée");
63                resolve(this);
64            }
65
66        });
67
68    });
69};
70const createActivite = function () {
71    return new Promise(function (resolve, reject) {
72        const sqlRequest = "CREATE TABLE IF NOT EXISTS activite (" +
73            "activite_code TEXT NOT NULL," +
74            "activite_libelle TEXT NOT NULL," +
75            "numero_de_la_fiche_equipement TEXT NOT NULL," +
76            "PRIMARY KEY (activite_code, numero_de_la_fiche_equipement)," +
77            "FOREIGN KEY (numero_de_la_fiche_equipement) REFERENCES equipement(numero_de_la_fiche_equipement))";
78        db.run(sqlRequest, [], (err) => {
79
80            if (err) {
81                console.log(err);
82                reject(err);
83            }
84            else {
85                console.log("Activité créée");
86                resolve(this);
87            }
88        });
89
90    });
91};
92
93const populateInstallation =  function() {
94    return new Promise(function (resolve, reject) {
95        const fileName = 'data/234400034_004-010_fiches-installations-rpdl_extra_small.csv';
96        const stream = fs.createReadStream(fileName, {encoding: 'utf8'});
97
98
99        const parser = parse({
100            delimiter: ';',
101            columns: header =>
102                header.map( column => column.normalize('NFD').
103                    replace(/[\u0300-\u036f]/g, "").
104                    replace(/[^a-z0-9]/gmi, "_").
105                    replace(/\s+/g, '_').
106                    toLowerCase())
107        });
108
109
110        parser.on('readable', function () {
111            let row;
112
113            while (row = this.read()) {
114                    const sqlRequest = "INSERT OR IGNORE into installation (numero_de_l_installation, nom_usuel_de_l_installation, code_postal, nom_de_la_commune, localisation, accessibilite_handicapes_a_mobilite_reduite) " +
115                        "VALUES ($noDeLInstallation, $nomUsuelDeLInstallation, $codePostal, $nomDeLaCommune, $localisation, $accessibilite_handicapes_a_mobilite_reduite)";
116                    const sqlParams = {
117                        $noDeLInstallation: row.numero_de_l_installation,
118                        $nomUsuelDeLInstallation: row.nom_usuel_de_l_installation,
119                        $codePostal: String(row.code_postal),
120                        $nomDeLaCommune: String(row.nom_de_la_commune),
121                        $localisation: String(row.localisation),
122                        $accessibilite_handicapes_a_mobilite_reduite: String(row.accessibilite_handicapes_a_mobilite_reduite)
123                    };
124
125
126                    db.run(sqlRequest, sqlParams, function (err) {
127                        if (err)
128                            console.log(err);
129                    });
130
131            }
132        });
133
134
135        stream.pipe(parser);
136
137        parser.on('finish', function ()  {
138            console.log("Installations importées");
139            resolve(this);
140
141        });
142
143        parser.on("error", (err) =>{
144            console.log(err);
145            reject(err);
146        });
147
148    })
149}
150
151const populateEquipement =  function() {
152    return new Promise(function (resolve, reject) {
153        const fileName = 'data/234400034_004-011_fiches-equipements-rpdl_extra_small.csv';
154        const stream = fs.createReadStream(fileName, {encoding: 'utf8'});
155
156        const parser = parse({
157            delimiter: ';',
158            columns: header =>
159                header.map( column => column.normalize('NFD').
160                replace(/[\u0300-\u036f]/g, "").
161                replace(/[^a-z0-9]/gmi, "_").
162                replace(/\s+/g, '_').
163                toLowerCase())
164        });
165
166        parser.on('readable', function () {
167            let row;
168
169            while (row = this.read()) {
170                const sqlRequest = "INSERT OR IGNORE into equipement (numero_de_la_fiche_equipement, numero_de_l_installation) " +
171                    "VALUES ($numeroDeLaFicheEquipement, $numeroDeLInstallation)";
172                const sqlParams = {
173                    $numeroDeLaFicheEquipement: row.numero_de_la_fiche_equipement,
174                    $numeroDeLInstallation: row.numero_de_l_installation
175
176
177                };
178
179                db.run(sqlRequest, sqlParams, function (err) {
180                    if (err)
181                        console.log(err);
182                });
183            }
184        });
185
186
187        stream.pipe(parser);
188
189        parser.on('finish', function ()  {
190            console.log("Equipements importés");
191            resolve(this);
192
193        });
194
195        parser.on("error", (err) =>{
196            console.log(err);
197            reject(err);
198        });
199
200    })
201}
202
203const populateActivite =  function() {
204    return new Promise(function (resolve, reject) {
205        const fileName = 'data/234400034_004-009_activites-des-fiches-equipements-rpdl_extra_small.csv';
206        const stream = fs.createReadStream(fileName, {encoding: 'utf8'});
207
208        const parser = parse({
209            delimiter: ';',
210            columns: header =>
211                header.map( column => column.normalize('NFD').
212                replace(/[\u0300-\u036f]/g, "").
213                replace(/[^a-z0-9]/gmi, "_").
214                replace(/\s+/g, '_').
215                toLowerCase())
216        });
217
218        parser.on('readable', function () {
219            let row;
220
221            while (row = this.read()) {
222                const sqlRequest = "INSERT OR IGNORE into activite(activite_code, activite_libelle, numero_de_la_fiche_equipement) " +
223                    "VALUES ($activiteCode, $activiteLibelle, $numeroDeLaFicheEquipement)";
224                const sqlParams = {
225                    $activiteCode: row.activite_code,
226                    $activiteLibelle: row.activite_libelle,
227                    $numeroDeLaFicheEquipement : row.numero_de_la_fiche_equipement
228                };
229
230
231               db.run(sqlRequest, sqlParams, function (err) {
232                    if (err) {
233                        console.log(err);
234                        console.log(sqlRequest, sqlParams.$activiteCode, sqlParams.$activiteLibelle, sqlParams.$numeroDeLaFicheEquipement);
235                    }
236                });
237            }
238        });
239
240
241        stream.pipe(parser);
242
243        parser.on('finish', function ()  {
244            console.log("Activités importées");
245            resolve(this);
246
247        });
248
249        parser.on("error", (err) =>{
250            console.log(err);
251            reject(err);
252        });
253
254    })
255}
256const init = function() {
257    db.serialize(() => {
258        console.log("Création des tables et importation des données");
259        createInstallation().
260        then(
261            ()=>createEquipement()
262        ).then(
263            ()=>createActivite()
264        ).then(
265            ()=>populateInstallation()
266        ).then(
267            ()=>populateEquipement()
268        ).then(
269            ()=>populateActivite()
270        ).catch((err)=>console.log(err));
271    });
272
273
274};
275/* Init car and driver tables if they don't exist */
276
277
278
279
280module.exports = {
281    init: init,
282    db: db
283};