127 lines
4.0 KiB
JavaScript
127 lines
4.0 KiB
JavaScript
// Injests from ./input/* into ./output/tabs.db
|
|
|
|
const sqlite3 = require('sqlite3');
|
|
const sqlite = require('sqlite');
|
|
|
|
const fs = require('fs/promises');
|
|
|
|
(async () => {
|
|
const db = await sqlite.open({
|
|
driver: sqlite3.Database,
|
|
filename: './output/tabs-no-text.db'
|
|
});
|
|
|
|
await db.run(`
|
|
CREATE TABLE IF NOT EXISTS artists (
|
|
scrape_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
|
|
, id INTEGER
|
|
, name TEXT
|
|
, tabscount INTEGER
|
|
, artist_url TEXT
|
|
, tabs_last_update_timestamp INTEGER
|
|
)
|
|
`);
|
|
|
|
await db.run(`
|
|
CREATE TABLE IF NOT EXISTS tabs (
|
|
scrape_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
|
|
, artist_scrape_id INTEGER NOT NULL
|
|
, id INTEGER
|
|
, song_id INTEGER
|
|
, song_name TEXT
|
|
, artist_id INTEGER
|
|
, artist_name INTEGER
|
|
, type TEXT
|
|
, part TEXT
|
|
, version INTEGER
|
|
, votes INTEGER
|
|
, rating NUMERIC
|
|
, date TEXT
|
|
, status TEXT
|
|
, preset_id INTEGER
|
|
, tab_access_type TEXT
|
|
, tp_version INTEGER
|
|
, tonality_name TEXT
|
|
, version_description TEXT
|
|
, verified INTEGER
|
|
, artist_url TEXT
|
|
, tab_url TEXT
|
|
, tab_text TEXT
|
|
, difficulty TEXT
|
|
, tuning TEXT
|
|
, type_name TEXT
|
|
, FOREIGN KEY (artist_scrape_id) REFERENCES artists(scrape_id)
|
|
)
|
|
`);
|
|
|
|
// Clear out the database
|
|
await db.run('DELETE FROM tabs');
|
|
await db.run('DELETE FROM artists');
|
|
|
|
const files = await fs.readdir('./input/');
|
|
|
|
const stmtAddArtist = await db.prepare(`
|
|
INSERT INTO artists (
|
|
id, name, tabscount, artist_url, tabs_last_update_timestamp
|
|
) VALUES (
|
|
?1, ?2, ?3, ?4, ?5
|
|
)
|
|
`);
|
|
const stmtAddTab = await db.prepare(`
|
|
INSERT INTO tabs (
|
|
artist_scrape_id
|
|
, id, song_id, song_name, artist_id, artist_name, type, part
|
|
, version, votes, rating, date, status, preset_id, tab_access_type, tp_version, tonality_name
|
|
, version_description, verified, artist_url, tab_url, tab_text, difficulty, tuning, type_name
|
|
) VALUES (
|
|
?1
|
|
, ?2, ?3, ?4, ?5, ?6, ?7, ?8
|
|
, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17
|
|
, ?18, ?19, ?20, ?21, ?22, ?23, ?24, ?25
|
|
)
|
|
`);
|
|
|
|
function addArtist(id, name, tabscount, artist_url, tabs_last_update_timestamp) {
|
|
return stmtAddArtist.run([id, name, tabscount, artist_url, tabs_last_update_timestamp]);
|
|
}
|
|
|
|
function addTab(
|
|
artist_scrape_id, id, song_id, song_name, artist_id, artist_name, type, part, version, votes,
|
|
rating, date, status, preset_id, tab_access_type, tp_version, tonality_name, version_description,
|
|
verified, artist_url, tab_url, tab_text, difficulty, tuning, type_name
|
|
) {
|
|
return stmtAddTab.run([
|
|
artist_scrape_id, id, song_id, song_name, artist_id, artist_name, type, part, version, votes,
|
|
rating, date, status, preset_id, tab_access_type, tp_version, tonality_name, version_description,
|
|
verified, artist_url, tab_url, tab_text, difficulty, tuning, type_name
|
|
]);
|
|
}
|
|
|
|
for (let file of files) {
|
|
if (!file.endsWith('.json')) continue; // skip the .keep file
|
|
console.log('reading ./input/' + file);
|
|
let dataJSON = await fs.readFile('./input/' + file);
|
|
let data = JSON.parse(dataJSON);
|
|
let artistIndex = 0;
|
|
for (let artist of data) {
|
|
console.log(`adding artist (${artistIndex+1}/${data.length}, ${artist.tabs.length} tabs): ${artist.name}`)
|
|
let artistResult = await addArtist(artist.id, artist.name, artist.tabscount, artist.artist_url, artist.tabs_last_update_timestamp);
|
|
let artistScrapeId = artistResult.lastID;
|
|
for (let tab of artist.tabs) {
|
|
addTab(
|
|
artistScrapeId,
|
|
tab.id, tab.song_id, tab.song_name, tab.artist_id, tab.artist_name, tab.type, tab.part, tab.version, tab.votes,
|
|
tab.rating, tab.date, tab.status, tab.preset_id, tab.tab_access_type, tab.tp_version, tab.tonality_name, tab.version_description,
|
|
tab.verified, tab.artist_url, tab.tab_url, null, tab.difficulty, tab.tuning, tab.type_name
|
|
);
|
|
}
|
|
artistIndex += 1;
|
|
}
|
|
}
|
|
|
|
await stmtAddTab.finalize();
|
|
await stmtAddArtist.finalize();
|
|
|
|
await db.close();
|
|
})();
|