Wikipedia:FMPortal/sql

Kohteesta Wikipedia
Siirry navigaatioon Siirry hakuun

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

Main tables[muokkaa wikitekstiä]

FMDataSources[muokkaa wikitekstiä]

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 wikitekstiä]

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 wikitekstiä]

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 wikitekstiä]

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 wikitekstiä]

FMSourceLocalization[muokkaa wikitekstiä]

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 wikitekstiä]

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 wikitekstiä]

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