[public/netxms.git] / sql / metadata.in

/*
** Metadata
**
** ex: syntax=sql
*/

INSERT INTO metadata (var_name,var_value)
	VALUES ('SchemaVersion',DB_FORMAT_VERSION);
INSERT INTO metadata (var_name,var_value)
	VALUES ('Syntax',DB_SYNTAX);

INSERT INTO metadata (var_name,var_value)
	VALUES ('IDataTableCreationCommand','CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)');
#if defined(DB_MSSQL)
INSERT INTO metadata (var_name,var_value)
	VALUES ('IDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)');
#else
INSERT INTO metadata (var_name,var_value)
	VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp DESC)');
#endif

INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,UNIQUE(record_id))');
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataTableCreationCommand_1','CREATE TABLE tdata_records_%d (record_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,row_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)');
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataTableCreationCommand_2','CREATE TABLE tdata_rows_%d (row_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)');
#if defined(DB_POSTGRESQL)
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_timestamp_id ON tdata_%d(tdata_timestamp,item_id)');
#elif defined(DB_MSSQL)
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
#else
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
#endif
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataIndexCreationCommand_1','CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)');
INSERT INTO metadata (var_name,var_value)
	VALUES ('TDataIndexCreationCommand_2','CREATE INDEX idx_tdata_rec_%d_id ON tdata_records_%d(record_id)');

INSERT INTO metadata (var_name,var_value)
	VALUES ('LocationHistory','CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timestamp integer not null, end_timestamp integer not null, PRIMARY KEY(start_timestamp))');
Commit	Line	Data
28f5b9a4 VK	1	/*
	2	** Metadata
	3	**
	4	** ex: syntax=sql
	5	*/
	6
	7	INSERT INTO metadata (var_name,var_value)
	8	VALUES ('SchemaVersion',DB_FORMAT_VERSION);
	9	INSERT INTO metadata (var_name,var_value)
	10	VALUES ('Syntax',DB_SYNTAX);
d6124fa0	11
28f5b9a4	12	INSERT INTO metadata (var_name,var_value)
50da2d20	13	VALUES ('IDataTableCreationCommand','CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)');
d140955e	14	#if defined(DB_MSSQL)
28f5b9a4	15	INSERT INTO metadata (var_name,var_value)
4161f620 VK	16	VALUES ('IDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp)');
	17	#else
	18	INSERT INTO metadata (var_name,var_value)
d140955e	19	VALUES ('IDataIndexCreationCommand_0','CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp DESC)');
28f5b9a4	20	#endif
d6124fa0 VK	21
d6124fa0 VK	22	INSERT INTO metadata (var_name,var_value)
1d4f7890	23	VALUES ('TDataTableCreationCommand_0','CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,UNIQUE(record_id))');
22aaa779	24	INSERT INTO metadata (var_name,var_value)
67c7212f	25	VALUES ('TDataTableCreationCommand_1','CREATE TABLE tdata_records_%d (record_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,row_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)');
22aaa779	26	INSERT INTO metadata (var_name,var_value)
1d4f7890	27	VALUES ('TDataTableCreationCommand_2','CREATE TABLE tdata_rows_%d (row_id ' CONCAT SQL_INT64_TEXT CONCAT ' not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)');
d6124fa0 VK	28	#if defined(DB_POSTGRESQL)
	29	INSERT INTO metadata (var_name,var_value)
	30	VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_timestamp_id ON tdata_%d(tdata_timestamp,item_id)');
	31	#elif defined(DB_MSSQL)
	32	INSERT INTO metadata (var_name,var_value)
	33	VALUES ('TDataIndexCreationCommand_0','CREATE CLUSTERED INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
	34	#else
	35	INSERT INTO metadata (var_name,var_value)
	36	VALUES ('TDataIndexCreationCommand_0','CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)');
	37	#endif
22aaa779 VK	38	INSERT INTO metadata (var_name,var_value)
22aaa779 VK	39	VALUES ('TDataIndexCreationCommand_1','CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)');
a9f5aa55 VK	40	INSERT INTO metadata (var_name,var_value)
a9f5aa55 VK	41	VALUES ('TDataIndexCreationCommand_2','CREATE INDEX idx_tdata_rec_%d_id ON tdata_records_%d(record_id)');
4899db4d	42
	43	INSERT INTO metadata (var_name,var_value)
	44	VALUES ('LocationHistory','CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timestamp integer not null, end_timestamp integer not null, PRIMARY KEY(start_timestamp))');