CSRD-CustomAchievements/configs/sql-init-scripts/sqlite/custom_achievements.sql

/* run with sqlite3 -init "configs/sql-init-scripts/sqlite/custom_achievements.sql" "data/sqlite3/custom-achievements.sq3" */

/* list of achievements */
CREATE TABLE achievements (
	achievement_id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	displaytype INTEGER,
	visible BOOLEAN DEFAULT TRUE, /* determines whether or not it's rendered on an achievement page */
	CONSTRAINT achievement_name UNIQUE(name)
);

/* per-account achievement status */
CREATE TABLE achievement_status (
	achievement_id INTEGER,
	steamid3 INTEGER NOT NULL,
	achieved INTEGER DEFAULT 0, /* should be time achieved */
	metadata TEXT,
	CONSTRAINT achievement_user UNIQUE(achievement_id, steamid3)
);

/* localized achievement name and descriptions for display */
/* also to be used in CallOnCustomAchievementAwarded() to print to chat */
CREATE TABLE achievement_languages (
	achievement_name TEXT NOT NULL,
	language_shortcode TEXT NOT NULL,
	achievement_local_name TEXT NOT NULL,
	achievement_local_description TEXT,
	CONSTRAINT achievement_in_language UNIQUE(achievement_name, language_shortcode)
);

/* automatically create language placeholder entries */
CREATE TRIGGER create_achievement_localizations AFTER INSERT ON achievements BEGIN
	INSERT INTO achievement_languages (achievement_name, language_shortcode,
			achievement_local_name, achievement_local_description) VALUES
			(NEW.name, 'en', NEW.name, NEW.name || '_description');  
END;