241 lines
9.3 KiB
JavaScript
241 lines
9.3 KiB
JavaScript
// reads parts-to-include and sets-to-include and based off
|
|
// schema.sql, creates a new database with the data from
|
|
// the files.
|
|
|
|
const fs = require('fs');
|
|
|
|
fs.writeFileSync('./db/dump.sql', '');
|
|
function addLine(line) {
|
|
fs.appendFileSync('./db/dump.sql', line + '\n');
|
|
}
|
|
|
|
// first categories
|
|
addLine('-- tags\n');
|
|
const newcategory = (category, name) => `INSERT INTO tag (id, name) VALUES ('${category}', '${name}');`;
|
|
const categories = fs.readFileSync('db/res/categories.txt').toString().split('\n');
|
|
|
|
for (let i = 0; i < categories.length; i++) {
|
|
const category = categories[i].split('\t');
|
|
const categoryName = (category[1]).replace(/'/g, '');
|
|
console.log(`NEW category ${categoryName}`);
|
|
addLine(newcategory(category[0], categoryName));
|
|
}
|
|
|
|
// then colour type
|
|
addLine('\n-- colour type\n');
|
|
const newColourType = (type, name) => `INSERT INTO colour_type (id, name) VALUES ('${type}', '${name}');`;
|
|
|
|
const lookupTable = {
|
|
0: 'N/A',
|
|
1: 'Solid',
|
|
2: 'Transparent',
|
|
3: 'Chrome',
|
|
4: 'Pearl',
|
|
5: 'Satin',
|
|
6: 'Metallic',
|
|
7: 'Milky',
|
|
8: 'Glitter',
|
|
9: 'Speckle',
|
|
10: 'Modulex',
|
|
};
|
|
|
|
for (let i = 0; i < 11; i++) {
|
|
console.log(`NEW colour type ${i}`);
|
|
addLine(newColourType(i, lookupTable[i]));
|
|
}
|
|
|
|
// then colour
|
|
addLine('\n-- colour\n');
|
|
const newcolour = (id, name, RGB, type) => `INSERT INTO lego_brick_colour (id, name, hexrgb, col_type) VALUES ('${id}', '${name}', '${RGB}', '${type}');`;
|
|
const colours = fs.readFileSync('db/res/colors.txt').toString().split('\n');
|
|
|
|
for (let i = 0; i < colours.length; i++) {
|
|
const colour = colours[i].split('\t');
|
|
const RGB = colour[2];
|
|
// needs to get key from value
|
|
const type = Object.keys(lookupTable).find(key => lookupTable[key] === colour[3]);
|
|
const id = colour[0];
|
|
const name = colour[1];
|
|
console.log(`NEW colour ${name}`);
|
|
addLine(newcolour(id, name, RGB, type));
|
|
}
|
|
|
|
// then bricks
|
|
addLine('\n-- bricks\n');
|
|
const newBrick = (id, name, weight, dx, dy, dz) => `INSERT INTO lego_brick (id, name, weight, dimensions_x, dimensions_y, dimensions_z) VALUES ('${id}', '${name}', '${weight}', '${dx}', '${dy}', '${dz}');`;
|
|
const allBricks = fs.readFileSync('db/res/Parts.txt').toString().split('\n');
|
|
const brickIds = JSON.parse(fs.readFileSync('db/parts-to-include'));
|
|
|
|
for (let i = 0; i < brickIds.length; i++) {
|
|
const brickId = brickIds[i];
|
|
// find ID in allBricks
|
|
const brick = allBricks.find(brick => brick.split('\t')[2] === brickId);
|
|
if (!brick) {
|
|
console.log(`ERROR: brick ${brickId} not found`);
|
|
continue;
|
|
}
|
|
const brickData = brick.split('\t');
|
|
const name = brickData[3].replace(/'/g, '');
|
|
const weight = brickData[4];
|
|
const dx = brickData[5].split('x')[0].trim();
|
|
const dy = brickData[5].split('x')[1].trim();
|
|
const dz = brickData[5].split('x')[2].trim();
|
|
addLine(newBrick(brickId, name, weight, dx, dy, dz));
|
|
console.log(`NEW brick ${i} ${brickId}`);
|
|
}
|
|
|
|
// then sets
|
|
addLine('\n-- sets\n');
|
|
const newSet = (id, name, weight, description, date, dx, dy, dz) => `INSERT INTO lego_set (id, name, description, date_released, weight, dimensions_x, dimensions_y, dimensions_z) VALUES ('${id}', '${name}', '${description}', '${date}', '${weight}', '${dx}', '${dy}', '${dz}');`;
|
|
const allSets = fs.readFileSync('db/res/Sets.txt').toString().split('\n');
|
|
const setIds = JSON.parse(fs.readFileSync('db/sets-to-include'));
|
|
|
|
for (let i = 0; i < setIds.length; i++) {
|
|
const setId = setIds[i];
|
|
// find ID in allBricks
|
|
const set = allSets.find(set => set.split('\t')[2] === setId);
|
|
if (!set) {
|
|
console.log(`ERROR: set ${setId} not found`);
|
|
continue;
|
|
}
|
|
const setData = set.split('\t');
|
|
const name = setData[3].replace(/'/g, '');
|
|
const desc = setData[3].replace(/'/g, '') + ' ' + setData[1].replace(' / ', ', ').replace(/'/g, '');
|
|
const date = setData[4];
|
|
const weight = setData[5];
|
|
const dx = setData[6].split('x')[0].trim();
|
|
const dy = setData[6].split('x')[1].trim();
|
|
const dz = setData[6].split('x')[2].trim();
|
|
addLine(newSet(setId, name, weight, desc, date, dx, dy, dz));
|
|
console.log(`NEW set ${i} ${setId}`);
|
|
}
|
|
|
|
// then brick tags
|
|
addLine('\n-- brick tags\n');
|
|
const newBrickTag = (brickId, tagId) => `INSERT INTO lego_brick_tag (brick_id, tag) VALUES ('${brickId}', '${tagId}');`;
|
|
|
|
for (let i = 0; i < brickIds.length; i++) {
|
|
const brickId = brickIds[i];
|
|
let tagId = allBricks.find(brick => brick.split('\t')[2] === brickId);
|
|
if (!tagId) {
|
|
console.log(`ERROR: brick at ${i} not found`);
|
|
continue;
|
|
}
|
|
tagId = tagId.split('\t')[0];
|
|
addLine(newBrickTag(brickId, tagId));
|
|
console.log(`NEW brick tag ${i} ${brickId}`);
|
|
}
|
|
|
|
// then set tags
|
|
addLine('\n-- set tags\n');
|
|
const newSetTag = (setId, tagId) => `INSERT INTO lego_set_tag (set_id, tag) VALUES ('${setId}', '${tagId}');`;
|
|
|
|
for (let i = 0; i < setIds.length; i++) {
|
|
const setId = setIds[i];
|
|
const set = allSets.find(set => set.split('\t')[2] === setId);
|
|
if (!set) {
|
|
console.log(`ERROR: set ${setId} not found`);
|
|
continue;
|
|
}
|
|
const tagId = set.split('\t')[0];
|
|
addLine(newSetTag(setId, tagId));
|
|
console.log(`NEW set tag ${i} ${setId}`);
|
|
}
|
|
|
|
// then bricks in sets
|
|
addLine('\n-- bricks in sets\n');
|
|
const newBrickInSet = (setId, brickId, quantity) => `INSERT INTO set_descriptor (set_id, brick_id, amount) VALUES ('${setId}', '${brickId}', '${quantity}');`;
|
|
const setDescriptors = JSON.parse(fs.readFileSync('db/res/sets.json'));
|
|
|
|
for (const setId of setIds) {
|
|
const set = setDescriptors[setId];
|
|
|
|
for (const [brickId, quantity] of Object.entries(set)) {
|
|
console.log(`NEW brick in set ${setId} ${brickId}`);
|
|
addLine(newBrickInSet(setId, brickId, quantity));
|
|
}
|
|
}
|
|
|
|
// then make up some random data for brick inventory
|
|
addLine('\n-- brick inventory\n');
|
|
const newBrickInventory = (brickId, stock, price, newPrice) => `INSERT INTO lego_brick_inventory (brick_id, stock, price, new_price, last_updated) VALUES ('${brickId}', '${stock}', '${price}', '${newPrice || ''}', now());`;
|
|
const newBrickInventoryWNULL = (brickId, stock, price) => `INSERT INTO lego_brick_inventory (brick_id, stock, price, last_updated) VALUES ('${brickId}', '${stock}', '${price}', now());`;
|
|
|
|
for (const brickId of brickIds) {
|
|
const brick = allBricks.find(brick => brick.split('\t')[2] === brickId);
|
|
if (!brick) {
|
|
console.log(`ERROR: brick at ${brickId} not found`);
|
|
continue;
|
|
}
|
|
// between 0 and 100000
|
|
const stock = Math.floor(Math.random() * 100000);
|
|
// between 0.09 and 2 as a float
|
|
let price = Math.random() * 4.99 + 0.09;
|
|
// 20% chance of not being null, must be less than price and greater than 0
|
|
let newPrice = Math.random() < 0.2 ? Math.random() * 2.99 + 0.09 : null;
|
|
|
|
// if newPrice is more than price, swap them
|
|
if (newPrice && newPrice > price) {
|
|
const temp = newPrice;
|
|
newPrice = price;
|
|
price = temp;
|
|
}
|
|
|
|
// round to 2 decimal places
|
|
price = Math.round(price * 100) / 100;
|
|
newPrice = Math.round(newPrice * 100) / 100;
|
|
|
|
console.log(`NEW brick inventory ${brickId}`);
|
|
if (newPrice) {
|
|
addLine(newBrickInventory(brickId, stock, price, newPrice));
|
|
} else {
|
|
addLine(newBrickInventoryWNULL(brickId, stock, price));
|
|
}
|
|
}
|
|
|
|
// then make up some random data for set inventory
|
|
addLine('\n-- set inventory\n');
|
|
const newSetInventory = (setId, stock, price, newPrice) => `INSERT INTO lego_set_inventory (set_id, stock, price, new_price, last_updated) VALUES ('${setId}', '${stock}', '${price}', '${newPrice || ''}', now());`;
|
|
const newSetInventoryWNULL = (setId, stock, price) => `INSERT INTO lego_set_inventory (set_id, stock, price, last_updated) VALUES ('${setId}', '${stock}', '${price}', now());`;
|
|
|
|
for (const setId of setIds) {
|
|
const set = allSets.find(set => set.split('\t')[2] === setId);
|
|
if (!set) {
|
|
console.log(`ERROR: set ${setId} not found`);
|
|
continue;
|
|
}
|
|
// between 0 and 100
|
|
const stock = Math.floor(Math.random() * 100);
|
|
// between 10 and 2000 as a float, biased towards numbers from 50 to 200
|
|
let price = Math.random() * 1000 + 50;
|
|
|
|
// 20% chance of not being null, must be less than price and greater than 0
|
|
let newPrice = Math.random() < 0.5 ? Math.random() * 1000 + 50 : null;
|
|
|
|
// if newPrice is more than price, swap them
|
|
if (newPrice && newPrice > price) {
|
|
const temp = newPrice;
|
|
newPrice = price;
|
|
price = temp;
|
|
}
|
|
|
|
// round to 2 decimal places
|
|
price = Math.round(price * 100) / 100;
|
|
newPrice = Math.round(newPrice * 100) / 100;
|
|
|
|
console.log(`NEW set inventory ${setId}`);
|
|
if (newPrice) {
|
|
addLine(newSetInventory(setId, stock, price, newPrice));
|
|
} else {
|
|
addLine(newSetInventoryWNULL(setId, stock, price));
|
|
}
|
|
}
|
|
|
|
// add bonsai tree
|
|
addLine('\n-- bonsai tree\n');
|
|
addLine(newSet('1010', 'Lego Bonsai Tree', 100, 'Lego Bonsai Tree by Matthew Dennis, Rich Boakes and Jacek Kopecky', 2022, 100, 100, 100));
|
|
addLine(newSetTag('1010', '1201'));
|
|
addLine(newSetTag('1010', '323'));
|
|
addLine(newBrickInSet('1010', '95228', 1000));
|
|
addLine(newSetInventory('1010', 5, 1000.69, 50.00));
|