Wikipedia:FMPortal/sql

FMPortal SQL tables accessible (read-only) for every toolserver user under host sql, database u_agony_fmportal_p.

Main tables muokkaa

FMDataSources muokkaa

CREATE TABLE FMDataSources (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	Country VARCHAR(255) NOT NULL,
	SourceDate DATE NOT NULL,
	SourceName TINYTEXT NOT NULL,
	Url TINYTEXT NOT NULL
);

Id: Primary key, SourceId used in other tables to point this key
Country: Indexed name of country
SourceDate: Source date (YYYY-MM-DD)
SourceName: Source name
Url: Source Url

FMMunicipalities muokkaa

CREATE TABLE FMMunicipalities (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	Country VARCHAR(255) NOT NULL,
	Active DATE NULL,
	Former DATE NULL,
	Municipality TINYTEXT NOT NULL,
	NoSequence TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
	INDEX(Country)
);

Id: Primary key, MunicipalityId used in other tables to point this key
Country: Indexed name of country
Active: Date value when municipality began active. These municipalities are automatically added for template output after activation
Former: Date value when municipality had former municipality status. These municipalities are automatically removed from template output after former date.
Municipality: Municipality name
NoSequence: Flag to mark cases that should not be included for sequence templates

FMTemplates muokkaa

CREATE TABLE FMTemplates (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	SourceId INT UNSIGNED NOT NULL,
	SequenceNumber TINYINT UNSIGNED NOT NULL DEFAULT 0,
	Country VARCHAR(255) NOT NULL,
	Template TINYTEXT NOT NULL,
	TemplateHeader TEXT NOT NULL,
	TemplateFooter TEXT NOT NULL,
	INDEX(SourceId)
);

Id: Primary key, TemplateId used in other tables to point this key
SourceId: Indexed source relation
SequenceNumber: 1 when template list sequence number instead of value itself
Country: Indexed name of country
Template: Internal name for template
TemplateHeader: Default template header
TemplateFooter: Default template footer

FMData muokkaa

CREATE TABLE FMData (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	MunicipalityId INT UNSIGNED NOT NULL,
	TemplateId INT UNSIGNED NOT NULL,
	DataValue BIGINT NOT NULL,
	INDEX(MunicipalityId),
	INDEX(TemplateId)
);

Id: Primary key, DataId used in other tables to point this key
MunicipalityId: Indexed municipality relation
TemplateId: Indexed template relation
DataValue: Integer value of data (example 1234.56 is marked as 123456 and 123 is marked as 12300)

Localization tables muokkaa

FMSourceLocalization muokkaa

CREATE TABLE FMSourceLocalization (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	SourceId INT UNSIGNED NOT NULL,
	Lang VARCHAR(32) NOT NULL,
	SourceName TINYTEXT NOT NULL,
	INDEX(SourceId),
	INDEX(Lang, SourceId)
);

Id: Primary key, SourceLocalizationId used in other tables to point this key
SourceId: Indexed source relation
Language: Localized language code (=interwiki)
Source: Localized name for source

FMMunicipalityLocalization muokkaa

CREATE TABLE FMMunicipalityLocalization (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	MunicipalityId INT UNSIGNED NOT NULL,
	ImportOnly TINYINT UNSIGNED NOT NULL DEFAULT 0,
	Lang VARCHAR(32) NOT NULL,
	Municipality TINYTEXT NOT NULL,
	INDEX(MunicipalityId),
	UNIQUE(MunicipalityId, Lang)
);

Id: Primary key, MunicipalityLocalizationId used in other tables to point this key
MunicipalityId: Indexed municipality relation
ImportOnly: 1 when used only when importing data
Language: Localized language code (=interwiki)
Municipality: Localized name for municipality

FMTemplateLocalization muokkaa

CREATE TABLE FMTemplateLocalization (
	Id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	TemplateId INT UNSIGNED NOT NULL,
	Lang VARCHAR(32) NOT NULL,
	Template TINYTEXT NOT NULL,
	TemplateHeader TEXT NULL,
	TemplateFooter TEXT NULL,
	INDEX(TemplateId),
	INDEX(Lang, TemplateId)
);

Id: Primary key, TemplateLocalizationId used in other tables to point this key
TemplateId: Indexed template relation
Language: Localized language code (=interwiki)
Template: Localized name for template
TemplateHeader: Localized template header
TemplateFooter: Localized template footer