xcc.es
[READ-ONLY] Mirror of https://github.com/mrgnw/ananas. learn multiple languages at once
ananas.xcc.es
1-- SQLite schema for countries data
2CREATE TABLE IF NOT EXISTS countries (
3 id TEXT PRIMARY KEY, -- Wikidata ID
4 name TEXT NOT NULL, -- Country name in English
5 code TEXT NOT NULL, -- ISO 3166-1 alpha-2 code
6 flag TEXT NOT NULL, -- Emoji flag
7 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
8 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
9);
10
11CREATE TABLE IF NOT EXISTS country_languages (
12 country_id TEXT NOT NULL,
13 language_id TEXT NOT NULL,
14 speakers INTEGER, -- Number of speakers in thousands
15 is_official BOOLEAN, -- Whether it's an official language
16 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
17 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
18 PRIMARY KEY (country_id, language_id),
19 FOREIGN KEY (country_id) REFERENCES countries(id),
20 FOREIGN KEY (language_id) REFERENCES languages(iso)
21);
22
23-- Indexes
24CREATE INDEX IF NOT EXISTS idx_countries_code ON countries(code);
25CREATE INDEX IF NOT EXISTS idx_country_languages_language ON country_languages(language_id);
26
27-- Views
28CREATE VIEW IF NOT EXISTS v_country_languages AS
29SELECT
30 c.name as country_name,
31 c.code as country_code,
32 c.flag as country_flag,
33 l.langLabel as language_name,
34 l.iso as language_code,
35 cl.speakers as speakers_k,
36 cl.is_official
37FROM countries c
38JOIN country_languages cl ON c.id = cl.country_id
39JOIN languages l ON cl.language_id = l.iso
40ORDER BY c.name, cl.speakers DESC NULLS LAST;