Wikipedia:FMPortal/sql

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

Main tables

FMDataSources

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

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

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

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

FMSourceLocalization

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

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

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