view release on metacpan or  search on metacpan

scripts/cldr-schema.sql  view on Meta::CPAN

CREATE UNIQUE INDEX idx_unit_prefixes_unique ON unit_prefixes(unit_id);

-- Source: supplemental/units.xml/supplementalData/unitConstants/unitConstant
CREATE TABLE unit_constants (
     unit_constant_id   INTEGER
    -- Example: lb_to_kg, ft2_to_m2
    ,constant           VARCHAR(10) NOT NULL COLLATE NOCASE
    -- Example: gravity, PI, G, ft_to_m*ft_to_m
    ,expression         TEXT NOT NULL COLLATE NOCASE
    -- The computed value from the expression
    ,value              DECIMAL NOT NULL
    ,description        TEXT
    -- Example: approximate
    ,status             VARCHAR(20)
    ,PRIMARY KEY(unit_constant_id)
    ,CHECK( constant REGEXP '^[a-zA-Z](?:[a-zA-Z0-9]+(?:\_[a-zA-Z0-9]+)*)?$' )
    ,CHECK( status REGEXP '^[a-zA-Z][a-zA-Z0-9\_]+$' )
);
CREATE UNIQUE INDEX idx_unit_constants_unique ON unit_constants(constant);

-- Source: supplemental/units.xml/supplementalData/unitQuantities/unitQuantity
CREATE TABLE unit_quantities (
     unit_quantity_id   INTEGER
    -- Example: kilogram-square-meter-per-square-second-square-ampere
    ,base_unit          VARCHAR(70) NOT NULL COLLATE NOCASE
    -- Example: electric-resistance
    ,quantity           VARCHAR(30) NOT NULL COLLATE NOCASE
    -- Example: simple
    ,status             VARCHAR(20)
    -- Possibly some circumstantial comments
    ,comment            TEXT
    ,PRIMARY KEY(unit_quantity_id)
    ,CHECK( base_unit REGEXP '^[a-zA-Z][a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( status REGEXP '^[a-zA-Z][a-zA-Z0-9\_]+$' )
);
CREATE UNIQUE INDEX idx_unit_quantities_unique ON unit_quantities(base_unit);

-- Source: supplemental/units.xml/supplementalData/convertUnits/convertUnit
CREATE TABLE unit_conversions (
     unit_conversion_id INTEGER
    -- Example: candela, earth-mass, etc
    ,source             VARCHAR(70) NOT NULL COLLATE NOCASE
    ,base_unit          VARCHAR(70) NOT NULL COLLATE NOCASE
    -- Optional. There may not be an expression, but insteance a one-to-one value
    -- Example: 1000*item_per_mole/glucose_molar_mass
    ,expression         TEXT NULL COLLATE NOCASE
    -- If expression is NULL, this too, would be NULL
    ,factor             DECIMAL NULL
    -- ["si_acceptable", "metric", "prefixable"]
    ,systems            TEXT[]
    -- luminous-intensity, mass, volume, area, length, time, year-duration, electric-current, temperature, angle, substance-amount, portion, digital, graphics, typewidth, frequency, force, pressure, pressure-per-length, energy, th, power, voltage, el...
    ,category           VARCHAR(20)
    ,PRIMARY KEY(unit_conversion_id)
    ,CHECK( category REGEXP '^[a-zA-Z][a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,FOREIGN KEY(base_unit) REFERENCES unit_quantities(base_unit) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE UNIQUE INDEX idx_unit_conversions_unique ON unit_conversions(source, base_unit);

-- Source: supplemental/units.xml/supplementalData/unitPreferenceData/unitPreferences/unitPreference
-- <https://cldr-smoke.unicode.org/spec/main/ldml/tr35-info.html#Unit_Preferences_Data>
CREATE TABLE unit_prefs (
     unit_pref_id       INTEGER
    -- Example: cubic-centimeter. One would expect this is a foreign key to unit_quantities, but it is not unfortunately
    ,unit_id            VARCHAR(70) NOT NULL COLLATE NOCASE
    ,territory          VARCHAR(3) NOT NULL
    -- Example: area, concentration, consumption, duration, energy, length, power, pressure, speed, temperature, volume, year-duration
    ,category           VARCHAR(20)
    -- Example: default, geograph, land, floor, blood-glucose, etc
    ,usage              VARCHAR(20)
    -- Example: 2.5
    ,geq                DECIMAL
    -- Example: precision-increment/50
    -- <https://github.com/unicode-org/icu/blob/main/docs/userguide/format_parse/numbers/skeletons.md#precision>
    ,skeleton           VARCHAR(70)
    ,PRIMARY KEY(unit_pref_id)
    ,CHECK( unit_id REGEXP '^[a-zA-Z][a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( category REGEXP '^[a-zA-Z]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( usage REGEXP '^[a-zA-Z]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( skeleton REGEXP '^[a-zA-Z]+(?:\-[a-zA-Z0-9\/]+)*$' )
    ,FOREIGN KEY(territory) REFERENCES territories(territory) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE UNIQUE INDEX idx_unit_prefs_unique ON unit_prefs(category, usage, unit_id, territory, geq);

-- Source: supplemental/units.xml/supplementalData/metadata/alias/unitAlias
CREATE TABLE unit_aliases (
     unit_alias_id      INTEGER
    ,alias              VARCHAR(70) NOT NULL COLLATE NOCASE
    ,target             VARCHAR(70) NOT NULL COLLATE NOCASE
    ,reason             VARCHAR(20)
    ,PRIMARY KEY(unit_alias_id)
    ,CHECK( alias REGEXP '^[a-zA-Z][a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    -- target could be '1e6', so it does not necessarily start with alphabetical characters
    ,CHECK( target REGEXP '^[a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( reason REGEXP '^[a-zA-Z][a-zA-Z0-9]+$' )
);
CREATE UNIQUE INDEX idx_unit_aliases_unique ON unit_aliases(alias);

-- Source: main/*.xml/ldml/units/unitLength/compoundUnit
-- Source: main/*.xml/ldml/units/unitLength/unit
-- Source: main/*.xml/ldml/units/unitLength/coordinateUnit
CREATE TABLE units_l10n (
     units_l10n_id      INTEGER
    ,locale             VARCHAR(20) NOT NULL COLLATE NOCASE
    -- Example: long, short, narrow
    ,format_length      VARCHAR(10) NOT NULL COLLATE NOCASE
    -- Example: compound, regular
    ,unit_type          VARCHAR(20) NOT NULL COLLATE NOCASE
    -- Example: 10p-1, torque-newton-meter
    ,unit_id            VARCHAR(70) NOT NULL COLLATE NOCASE
    ,unit_pattern       VARCHAR(70) NOT NULL COLLATE NOCASE
    -- regular, prefix, per-unit
    ,pattern_type       VARCHAR(10) COLLATE NOCASE
    -- An optional locale display name
    ,locale_name        TEXT
    ,count              VARCHAR(10) COLLATE NOCASE
    -- Example: masculine, feminine, neuter, inanimate, common
    ,gender             VARCHAR(10) DEFAULT 'masculine' COLLATE NOCASE
    -- Example: nominative, accusative, genitive, dative
    ,gram_case          VARCHAR(10) COLLATE NOCASE
    ,PRIMARY KEY(units_l10n_id)
    ,CHECK( format_length REGEXP '^[a-zA-Z][a-zA-Z0-9]+$' )
    ,CHECK( unit_type REGEXP '^[a-zA-Z][a-zA-Z0-9]+$' )
    ,CHECK( unit_id REGEXP '^[a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( pattern_type REGEXP '^[a-zA-Z][a-zA-Z0-9]+(?:\-[a-zA-Z0-9]+)*$' )
    ,CHECK( count REGEXP '^[a-zA-Z][a-zA-Z0-9]+$' )
    ,CHECK( gender REGEXP '^[a-zA-Z][a-zA-Z0-9]+$' )
    ,FOREIGN KEY(locale) REFERENCES locales(locale) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE UNIQUE INDEX idx_units_l10n_unique ON units_l10n(locale, format_length, unit_type, unit_id, IFNULL(count, ''), IFNULL(gender, ''), IFNULL(gram_case, ''));

-- Source: main/*.xml/ldml/numbers/defaultNumberingSystem
-- Source: main/*.xml/ldml/numbers/otherNumberingSystems/*[local-name()="native" or local-name()="traditional" or local-name()="finance"]
-- <https://unicode.org/reports/tr35/tr35-numbers.html#defaultNumberingSystem>
-- <https://unicode.org/reports/tr35/tr35-numbers.html#otherNumberingSystems>
CREATE TABLE locale_number_systems (
     locale_num_sys_id  INTEGER
    ,locale             VARCHAR(20) NOT NULL COLLATE NOCASE
    -- The default numbering system for this locale
    ,number_system      VARCHAR(10)
    ,native             VARCHAR(10)
    ,traditional        VARCHAR(10)
    ,finance            VARCHAR(10)