ad4462ef8a
This commit introduces a major architectural overhaul, migrating the data backend from a flat JSON file to a relational MySQL database. - Replaced multiple scraping scripts with a unified `main.py` that handles crawling, parsing, and database synchronization. - Introduced `mysql_helper.py` for robust database interaction with a connection pool. - Added `queries.sql` defining the new database schema for assets, categories, tags, and changelogs. - Removed all obsolete frontend code (v1, v2) and old scraping scripts. This change provides a more scalable and maintainable foundation for managing asset data.
73 lines
2.1 KiB
SQL
73 lines
2.1 KiB
SQL
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS category;
|
|
DROP TABLE IF EXISTS series;
|
|
DROP TABLE IF EXISTS tag;
|
|
DROP TABLE IF EXISTS asset_pack;
|
|
DROP TABLE IF EXISTS asset_pack_image;
|
|
DROP TABLE IF EXISTS asset_pack_tag;
|
|
DROP TABLE IF EXISTS update_log;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
CREATE TABLE category (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(100) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE series (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(100) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE tag (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(100) NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE asset_pack (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL UNIQUE,
|
|
category_id INT NOT NULL,
|
|
series_id INT,
|
|
discovered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
released_at DATE,
|
|
updated_at DATE,
|
|
base_asset_path VARCHAR(255),
|
|
orig_page_link VARCHAR(255),
|
|
FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
INDEX idx_name (name),
|
|
INDEX idx_category (category_id),
|
|
INDEX idx_series (series_id)
|
|
);
|
|
|
|
CREATE TABLE asset_pack_image (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
asset_pack_id INT NOT NULL,
|
|
image_file_name VARCHAR(255),
|
|
orig_file_link VARCHAR(255),
|
|
FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
|
|
INDEX idx_asset_pack_id (asset_pack_id)
|
|
);
|
|
|
|
CREATE TABLE asset_pack_tag (
|
|
asset_pack_id INT NOT NULL,
|
|
tag_id INT NOT NULL,
|
|
PRIMARY KEY (asset_pack_id, tag_id),
|
|
FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE update_log (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
asset_pack_id INT NOT NULL,
|
|
released_date DATE NOT NULL,
|
|
version VARCHAR(20),
|
|
description VARCHAR(500),
|
|
files_count INT UNSIGNED DEFAULT 0,
|
|
feat_animations TINYINT(1) DEFAULT 0,
|
|
feat_variations TINYINT(1) DEFAULT 0,
|
|
zip_file_name VARCHAR(255),
|
|
orig_download_link VARCHAR(255),
|
|
FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
|
|
INDEX idx_released_date (released_date)
|
|
); |