Monthly Usage Schema

usage_schema_v1.sql

DROP TABLE IF EXISTS metric;
DROP TABLE IF EXISTS metric_type;
DROP TABLE IF EXISTS uom;
DROP TABLE IF EXISTS resource;
DROP TABLE IF EXISTS usage_tenant;
DROP TABLE IF EXISTS job_run;
 
CREATE TABLE usage_tenant (
	usage_tenant_id INT(10) NOT NULL AUTO_INCREMENT,
	source_tenant_id VARCHAR(36) NOT NULL,
	CONSTRAINT pk_usage_tenant PRIMARY KEY (usage_tenant_id)
);
ALTER TABLE usage_tenant ADD CONSTRAINT uc_usage_tnt_src UNIQUE(source_tenant_id);
 
CREATE TABLE resource (
	resource_id INT(10) NOT NULL AUTO_INCREMENT,
	source_resource_id VARCHAR(36) NOT NULL,
	usage_tenant_id INT(10) NOT NULL,
	CONSTRAINT pk_resource PRIMARY KEY (resource_id)
);
ALTER TABLE resource ADD CONSTRAINT fk_res_usage_tnt FOREIGN KEY (usage_tenant_id) REFERENCES usage_tenant(usage_tenant_id);
 
CREATE TABLE uom (
	uom_id INT(10) NOT NULL AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
	representation VARCHAR(128) NOT NULL,
	CONSTRAINT pk_uom PRIMARY KEY (uom_id)
);
ALTER TABLE uom ADD CONSTRAINT uc_uom_name UNIQUE(name);
 
CREATE TABLE metric_type (
	metric_type_id INT(10) NOT NULL AUTO_INCREMENT,
	uom_id INT(10) DEFAULT NULL,
	collect_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT pk_metric_type PRIMARY KEY (metric_type_id)
);
ALTER TABLE metric_type ADD CONSTRAINT uc_metric_type_name UNIQUE(name);
ALTER TABLE metric_type ADD CONSTRAINT fk_metric_type_uom FOREIGN KEY (uom_id) REFERENCES uom(uom_id);
 
CREATE TABLE metric (
	metric_id INT(10) NOT NULL AUTO_INCREMENT,
	resource_id INT(10) NOT NULL,
	metric_type_id INT(10) NOT NULL,
	CONSTRAINT pk_metric PRIMARY KEY (metric_id)
);
ALTER TABLE metric ADD CONSTRAINT fk_metric_resource FOREIGN KEY (resource_id) REFERENCES resource(resource_id);
ALTER TABLE metric ADD CONSTRAINT fk_metric_type FOREIGN KEY (metric_type_id) REFERENCES metric_type(metric_type_id);
 
CREATE TABLE job_run (
	job_run_id INT(10) NOT NULL AUTO_INCREMENT,
	job_type VARCHAR(255) NOT NULL,
	status VARCHAR(256),
	execution_message VARCHAR(1024),
	start_time DATETIME NOT NULL,
	end_time DATETIME,
	CONSTRAINT pk_job_run PRIMARY KEY (job_run_id)
);
 
-- REFERENCE DATA :: uom
INSERT INTO uom(uom_id, name, representation) values(1,'Hour', 'Hr');
INSERT INTO uom(uom_id, name, representation) values(2,'Gigabyte', 'GB');
INSERT INTO uom(uom_id, name, representation) values(3,'Megaherz', 'MHz');
 
-- REFERENCE DATA :: metric_type
INSERT INTO metric_type(name,	uom_id) values('Data Center', null);
INSERT INTO metric_type(name,	uom_id) values('Cluster', null);
INSERT INTO metric_type(name,	uom_id) values('Host', null);
INSERT INTO metric_type(name,	uom_id) values('VM_Name', null);
INSERT INTO metric_type(name,	uom_id) values('Vcpu', null);
INSERT INTO metric_type(name,	uom_id) values('cpu_alloc', 3);
INSERT INTO metric_type(name,	uom_id) values('cpuused', null);
INSERT INTO metric_type(name,	uom_id) values('mem_alloc', 2);
INSERT INTO metric_type(name,	uom_id) values('memused', 2);
INSERT INTO metric_type(name,	uom_id) values('diskalloc', 2);
INSERT INTO metric_type(name,	uom_id) values('diskused', 2);
INSERT INTO metric_type(name,	uom_id) values('ip address', null);
INSERT INTO metric_type(name,	uom_id) values('VM OS', null);
INSERT INTO metric_type(name,	uom_id) values('Total VM running hours', 1);
INSERT INTO metric_type(name,	uom_id) values('Allocation Date', null);
INSERT INTO metric_type(name,	uom_id) values('Decommission Date', null);

-- quartz-2.2.1 tables. Requiered for clustered deployment and job scheduling
-- Source http://quartz-scheduler.org/downloads/destination?name=quartz-2.2.1-distribution.tar.gz&bucket=tcdistributions&file=quartz-2.2.1-distribution.tar.gz 
-- location: quartz-2.2.1\docs\dbTables\tables_mysql.sql
-- START 
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;


CREATE TABLE QRTZ_JOB_DETAILS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    JOB_CLASS_NAME   VARCHAR(250) NOT NULL,
    IS_DURABLE VARCHAR(1) NOT NULL,
    IS_NONCONCURRENT VARCHAR(1) NOT NULL,
    IS_UPDATE_DATA VARCHAR(1) NOT NULL,
    REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    JOB_NAME  VARCHAR(200) NOT NULL,
    JOB_GROUP VARCHAR(200) NOT NULL,
    DESCRIPTION VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT(13) NULL,
    PREV_FIRE_TIME BIGINT(13) NULL,
    PRIORITY INTEGER NULL,
    TRIGGER_STATE VARCHAR(16) NOT NULL,
    TRIGGER_TYPE VARCHAR(8) NOT NULL,
    START_TIME BIGINT(13) NOT NULL,
    END_TIME BIGINT(13) NULL,
    CALENDAR_NAME VARCHAR(200) NULL,
    MISFIRE_INSTR SMALLINT(2) NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
        REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
);

CREATE TABLE QRTZ_SIMPLE_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    REPEAT_COUNT BIGINT(7) NOT NULL,
    REPEAT_INTERVAL BIGINT(12) NOT NULL,
    TIMES_TRIGGERED BIGINT(10) NOT NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CRON_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    CRON_EXPRESSION VARCHAR(200) NOT NULL,
    TIME_ZONE_ID VARCHAR(80),
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_SIMPROP_TRIGGERS
  (          
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    STR_PROP_1 VARCHAR(512) NULL,
    STR_PROP_2 VARCHAR(512) NULL,
    STR_PROP_3 VARCHAR(512) NULL,
    INT_PROP_1 INT NULL,
    INT_PROP_2 INT NULL,
    LONG_PROP_1 BIGINT NULL,
    LONG_PROP_2 BIGINT NULL,
    DEC_PROP_1 NUMERIC(13,4) NULL,
    DEC_PROP_2 NUMERIC(13,4) NULL,
    BOOL_PROP_1 VARCHAR(1) NULL,
    BOOL_PROP_2 VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) 
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_BLOB_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_CALENDARS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    CALENDAR_NAME  VARCHAR(200) NOT NULL,
    CALENDAR BLOB NOT NULL,
    PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
);

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_GROUP  VARCHAR(200) NOT NULL, 
    PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
);

CREATE TABLE QRTZ_FIRED_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    ENTRY_ID VARCHAR(95) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    FIRED_TIME BIGINT(13) NOT NULL,
    SCHED_TIME BIGINT(13) NOT NULL,
    PRIORITY INTEGER NOT NULL,
    STATE VARCHAR(16) NOT NULL,
    JOB_NAME VARCHAR(200) NULL,
    JOB_GROUP VARCHAR(200) NULL,
    IS_NONCONCURRENT VARCHAR(1) NULL,
    REQUESTS_RECOVERY VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME,ENTRY_ID)
);

CREATE TABLE QRTZ_SCHEDULER_STATE
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    INSTANCE_NAME VARCHAR(200) NOT NULL,
    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
    CHECKIN_INTERVAL BIGINT(13) NOT NULL,
    PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
);

CREATE TABLE QRTZ_LOCKS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    LOCK_NAME  VARCHAR(40) NOT NULL, 
    PRIMARY KEY (SCHED_NAME,LOCK_NAME)
);

-- quartz-2.2.1 tables
-- END