· 5 years ago · Mar 19, 2020, 08:26 AM
1const sqlite3 = require("sqlite3").verbose();
2const fs = require("fs");
3const parse = require("csv-parse");
4
5const dbName = "beerdb.db"; // dbName = ':memory:'; //Pour travailler en mémoirec
6//const dbName = ':memory:';
7
8const createDataBase = () =>
9 new Promise((resolve, reject) => {
10 const db = new sqlite3.Database(dbName, err => {
11 if (err) {
12 reject(err);
13 }
14 });
15 db.get("PRAGMA foreign_keys = ON");
16 console.log(`base de données ${dbName}`);
17
18 resolve(db);
19 });
20
21const createTable = db =>
22 new Promise((resolve, reject) => {
23 const sqlCategorie =
24 "CREATE TABLE IF NOT EXISTS categorie (" +
25 "id INTEGER NOT NULL," +
26 "cat_name TEXT NOT NULL," +
27 "last_mod NUMERIC NOT NULL," +
28 "PRIMARY KEY(id))";
29 const sqlStyle =
30 "CREATE TABLE IF NOT EXISTS style (" +
31 "id INTEGER NOT NULL," +
32 "cat_id INTEGER NOT NULL," +
33 "style_name TEXT NOT NULL," +
34 "last_mod NUMERIC NOT NULL," +
35 "PRIMARY Key(id)," +
36 "FOREIGN KEY (cat_id) REFERENCES categorie(id)" +
37 ")";
38 const sqlBrewery =
39 "CREATE TABLE IF NOT EXISTS brewery (" +
40 "id INTEGER NOT NULL," +
41 "breweries TEXT NOT NULL," +
42 "address1 TEXT," +
43 "address2 TEXT," +
44 "city TEXT," +
45 "state TEXT," +
46 "code TEXT," +
47 "country TEXT," +
48 "phone TEXT," +
49 "website TEXT," +
50 "filepath TEXT," +
51 "descript TEXT," +
52 "last_mod NUMERIC NOT NULL," +
53 "coordinates TEXT NOT NULL," +
54 "PRIMARY Key(id))";
55
56 const geoCode =
57 "CREATE TABLE IF NOT EXISTS geocode (" +
58 "id INTEGER NOT NULL," +
59 "brewery_id INTEGER NOT NULL," +
60 "latitude NUMERIC NOT NULL," +
61 "longitude NUMERIC NOT NULL," +
62 "accuracy TEXT NOT NULL," +
63 "coordinates TEXT NOT NULL," +
64 "PRIMARY Key(id)," +
65 "FOREIGN KEY (brewery_id) REFERENCES brewery(id))";
66 const beer =
67 "CREATE TABLE IF NOT EXISTS beer (" +
68 "name TEXT NOT NULL," +
69 "id INTEGER NOT NULL," +
70 "brewery_id INTEGER NOT NULL," +
71 "cat_id INTEGER," +
72 "style_id INTEGER," +
73 "alcohol_by_volume NUMERIC NOT NULL," +
74 "international_bitterness_units INTEGER NOT NULL," +
75 "standard_reference_method INTEGER NOT NULL," +
76 "universal_product_code INTEGER NOT NULL," +
77 "filepath TEXT," +
78 "description TEXT," +
79 "add_user TEXT," +
80 "last_mod NUMERIC NOT NULL," +
81 "style TEXT," +
82 "category TEXT," +
83 "brewer TEXT," +
84 "address TEXT," +
85 "city TEXT," +
86 "state TEXT," +
87 "country TEXT," +
88 "coordinates TEXT," +
89 "website TEXT," +
90 "PRIMARY Key(id)," +
91 "FOREIGN KEY (brewery_id) REFERENCES brewery(id)," +
92 "FOREIGN KEY (style_id) REFERENCES style(id)," +
93 "FOREIGN KEY (cat_id) REFERENCES categorie(id))";
94
95 db.serialize(() => {
96 db.run(sqlCategorie, [], err => {
97 if (err) {
98 reject(err);
99 } else console.log("Categorie créée");
100 })
101 .run(sqlStyle, [], err => {
102 if (err) {
103 reject(err);
104 } else console.log("Style créée");
105 })
106 .run(sqlBrewery, [], err => {
107 if (err) {
108 reject(err);
109 } else console.log("Brewery créée");
110 })
111 .run(geoCode, [], err => {
112 if (err) {
113 reject(err);
114 } else console.log("Geocode crcréée");
115 })
116 .run(beer, [], err => {
117 if (err) {
118 reject(err);
119 } else {
120 console.log("Beer créée");
121 resolve(db);
122 }
123 });
124 });
125 });
126
127const populateCategorie = db =>
128 new Promise((resolve, reject) => {
129 const fileName = "./src/data/open-beer-database-categories.csv";
130 const stream = fs.createReadStream(fileName, { encoding: "utf8" });
131
132 const parser = parse({
133 delimiter: ";",
134 columns: header =>
135 header.map(column =>
136 column
137 .normalize("NFD")
138 .replace(/[\u0300-\u036f]/g, "")
139 .replace(/[^a-z0-9]/gim, "_")
140 .replace(/\s+/g, "_")
141 .toLowerCase()
142 )
143 });
144
145 parser.on("readable", function() {
146 let row;
147
148 while ((row = this.read())) {
149 const sqlRequest =
150 "INSERT OR IGNORE into categorie(" +
151 "id,cat_name,last_mod) " +
152 "VALUES ($id,$catName,$lastMod)";
153 const sqlParams = {
154 $id: row.id,
155 $catName: row.cat_name,
156 $lastMod: row.last_mod
157 };
158
159 db.run(sqlRequest, sqlParams, function(err) {
160 if (err) {
161 console.log("categorie", err, sqlParams, sqlRequest);
162 }
163 });
164 }
165 });
166
167 stream.pipe(parser);
168
169 parser.on("finish", function() {
170 console.log("categorie populate");
171 resolve(db);
172 });
173
174 parser.on("error", err => {
175 console.log(err);
176 reject(err);
177 });
178 });
179
180const populateStyle = db =>
181 new Promise((resolve, reject) => {
182 const fileName = "./src/data/open-beer-database-styles.csv";
183 const stream = fs.createReadStream(fileName, { encoding: "utf8" });
184
185 const parser = parse({
186 delimiter: ";",
187 columns: header =>
188 header.map(column =>
189 column
190 .normalize("NFD")
191 .replace(/[\u0300-\u036f]/g, "")
192 .replace(/[^a-z0-9]/gim, "_")
193 .replace(/\s+/g, "_")
194 .toLowerCase()
195 )
196 });
197
198 parser.on("readable", function() {
199 let row;
200
201 while ((row = this.read())) {
202 const sqlRequest =
203 "INSERT OR IGNORE into style(" +
204 "id,cat_id,style_name,last_mod) " +
205 "VALUES ($id,$catId,$styleName,$lastMod)";
206 const sqlParams = {
207 $id: row.id,
208 $catId: row.cat_id,
209 $styleName: row.style_name,
210 $lastMod: row.last_mod
211 };
212
213 db.run(sqlRequest, sqlParams, function(err) {
214 if (err) {
215 console.log("style", err, sqlParams, sqlRequest);
216 }
217 });
218 }
219 });
220
221 stream.pipe(parser);
222
223 parser.on("finish", function() {
224 console.log("style populate");
225 resolve(db);
226 });
227
228 parser.on("error", err => {
229 console.log(err);
230 reject(err);
231 });
232 });
233
234const populateBewery = db =>
235 new Promise((resolve, reject) => {
236 const fileName = "./src/data/open-beer-database-breweries.csv";
237 const stream = fs.createReadStream(fileName, { encoding: "utf8" });
238
239 const parser = parse({
240 delimiter: ";",
241 columns: header =>
242 header.map(column =>
243 column
244 .normalize("NFD")
245 .replace(/[\u0300-\u036f]/g, "")
246 .replace(/[^a-z0-9]/gim, "_")
247 .replace(/\s+/g, "_")
248 .toLowerCase()
249 )
250 });
251
252 parser.on("readable", function() {
253 let row;
254
255 while ((row = this.read())) {
256 const sqlRequest =
257 "INSERT OR IGNORE into brewery(" +
258 "id,breweries,address1,address2,city,state,code,country,phone,website,filepath,descript,last_mod,coordinates) " +
259 "VALUES ($id,$breweries,$address1,$address2,$city,$state,$code,$country,$phone,$website,$filepath,$descript,$lastMod,$coordinates)";
260
261 const sqlParams = {
262 $id: row.id,
263 $breweries: row.breweries,
264 $address1: row.address1,
265 $address2: row.address2,
266 $city: row.city,
267 $state: row.state,
268 $code: row.code,
269 $country: row.country,
270 $phone: row.phone,
271 $website: row.website,
272 $filepath: row.filepath,
273 $descript: row.descript,
274 $lastMod: row.last_mod,
275 $coordinates: row.coordinates
276 };
277
278 db.run(sqlRequest, sqlParams, function(err) {
279 if (err) {
280 console.log("brewery", err, sqlParams, sqlRequest);
281 }
282 });
283 }
284 });
285
286 stream.pipe(parser);
287
288 parser.on("finish", function() {
289 console.log("brewery populate");
290 resolve(db);
291 });
292
293 parser.on("error", err => {
294 console.log(err);
295 reject(err);
296 });
297 });
298
299const populateGeocode = db =>
300 new Promise((resolve, reject) => {
301 const fileName = "./src/data/open-beer-database-breweries-geocode.csv";
302 const stream = fs.createReadStream(fileName, { encoding: "utf8" });
303
304 const parser = parse({
305 delimiter: ";",
306 columns: header =>
307 header.map(column =>
308 column
309 .normalize("NFD")
310 .replace(/[\u0300-\u036f]/g, "")
311 .replace(/[^a-z0-9]/gim, "_")
312 .replace(/\s+/g, "_")
313 .toLowerCase()
314 )
315 });
316
317 parser.on("readable", function() {
318 let row;
319
320 while ((row = this.read())) {
321 //id;brewery_id;latitude;longitude;accuracy;coordinates
322 const sqlRequest =
323 "INSERT OR IGNORE into geocode(" +
324 "id,brewery_id,latitude,longitude,accuracy,coordinates) " +
325 "VALUES ($id,$breweryId,$latitude,$longitude,$accuracy,$coordinates)";
326
327 const sqlParams = {
328 $id: row.id,
329 $breweryId: row.brewery_id,
330 $latitude: row.latitude,
331 $longitude: row.longitude,
332 $accuracy: row.accuracy,
333 $coordinates: row.coordinates
334 };
335
336 db.run(sqlRequest, sqlParams, function(err) {
337 if (err) {
338 console.log("geocode", err, sqlParams, sqlRequest);
339 }
340 });
341 }
342 });
343
344 stream.pipe(parser);
345
346 parser.on("finish", function() {
347 console.log("geocode populate");
348 resolve(db);
349 });
350
351 parser.on("error", err => {
352 console.log(err);
353 reject(err);
354 });
355 });
356
357const populateBeer = db =>
358 new Promise((resolve, reject) => {
359 const fileName = "./src/data/open-beer-database_small_500.csv";
360 const stream = fs.createReadStream(fileName, { encoding: "utf8" });
361
362 const parser = parse({
363 delimiter: ";",
364 columns: header =>
365 header.map(column =>
366 column
367 .normalize("NFD")
368 .replace(/[\u0300-\u036f]/g, "")
369 .replace(/[^a-z0-9]/gim, "_")
370 .replace(/\s+/g, "_")
371 .toLowerCase()
372 )
373 });
374
375 parser.on("readable", function() {
376 let row;
377
378 while ((row = this.read())) {
379 //id;brewery_id;latitude;longitude;accuracy;coordinates
380 const sqlRequest =
381 "INSERT OR IGNORE into beer (" +
382 "name,id,brewery_id,cat_id,style_id,alcohol_by_volume,international_bitterness_units,standard_reference_method,universal_product_code,universal_product_code,description,add_user,last_mod,style,category,brewer,address,city,state,country,coordinates,website) " +
383 "VALUES ($name,$id,$breweryId,$catId,$styleId,$alcoholByVolume,$internationalBitternessUnits,$standardReferenceMethod,$universalProductCode,$filepath,$description,$addUser,$lastMod,$style,$category,$brewer,$address,$city,$state,$country,$coordinates,$website)";
384 if (row.cat_id == -1) row.cat_id = null;
385 if (row.style_id == -1) row.style_id = null;
386
387 const sqlParams = {
388 $name: row.name,
389 $id: row.id,
390 $breweryId: row.brewery_id,
391 $catId: row.cat_id,
392 $styleId: row.style_id,
393 $alcoholByVolume: row.alcohol_by_volume,
394 $internationalBitternessUnits: row.international_bitterness_units,
395 $standardReferenceMethod: row.standard_reference_method,
396 $universalProductCode: row.universal_product_code,
397 $filepath: row.universal_product_code,
398 $description: row.description,
399 $addUser: row.add_user,
400 $lastMod: row.last_mod,
401 $style: row.style,
402 $category: row.category,
403 $brewer: row.brewer,
404 $address: row.address,
405 $city: row.city,
406 $state: row.state,
407 $country: row.country,
408 $coordinates: row.coordinates,
409 $website: row.website
410 };
411
412 db.run(sqlRequest, sqlParams, function(err) {
413 if (err) {
414 console.log("beer", err, sqlParams, sqlRequest);
415 }
416 });
417 }
418 });
419
420 stream.pipe(parser);
421
422 parser.on("finish", function() {
423 console.log("beer populate");
424 resolve(db);
425 });
426
427 parser.on("error", err => {
428 console.log(err);
429 reject(err);
430 });
431 });
432
433const init = new Promise((resolve, reject) => {
434 createDataBase()
435 .then(db => createTable(db))
436 .then(db => populateCategorie(db))
437 .then(db => populateStyle(db))
438 .then(db => populateBewery(db))
439 .then(db => populateGeocode(db))
440 .then(db => populateBeer(db))
441 .then(db => {
442 module.exports.db = db;
443 resolve(db);
444 });
445});
446
447module.exports.init = init;