Yaniv Dary has uploaded a new change for review.
Change subject: history: added user tables
......................................................................
history: added user tables
Change-Id: Ife90396992238b9919bcf6377825c12a17082871
Signed-off-by: Yaniv Dary <yd...@redhat.com>
---
M data-warehouse/historydbscripts_postgres/create_functions.sql
M data-warehouse/historydbscripts_postgres/create_views_3_1.sql
M data-warehouse/historydbscripts_postgres/create_views_3_2.sql
A
data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql
4 files changed, 70 insertions(+), 14 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-dwh refs/changes/32/9332/1
diff --git a/data-warehouse/historydbscripts_postgres/create_functions.sql
b/data-warehouse/historydbscripts_postgres/create_functions.sql
index 84e41a5..b78ce69 100644
--- a/data-warehouse/historydbscripts_postgres/create_functions.sql
+++ b/data-warehouse/historydbscripts_postgres/create_functions.sql
@@ -40,8 +40,8 @@
RETURN NULL;
end if;
select parent_id INTO ParentID FROM tag_relations_history WHERE entity_id =
currentTagID and history_id in (SELECT max(a.history_id)
- FROM
tag_relations_history a
- WHERE
a.entity_id = currentTagID);
+ FROM
tag_relations_history a
+ WHERE a.entity_id =
currentTagID);
IF runNumber = 0 then
RETURN coalesce(GetPathIDs(ParentID, runNumber + 1),'/');
ELSE
diff --git a/data-warehouse/historydbscripts_postgres/create_views_3_1.sql
b/data-warehouse/historydbscripts_postgres/create_views_3_1.sql
index bd05094..5fa71e2 100644
--- a/data-warehouse/historydbscripts_postgres/create_views_3_1.sql
+++ b/data-warehouse/historydbscripts_postgres/create_views_3_1.sql
@@ -635,9 +635,9 @@
SELECT
history_id as history_id,
vm_disk_id as vm_disk_id,
- CASE
- WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
- ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
+ CASE
+ WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
+ ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
END as vm_disk_name,
vm_disk_description as vm_disk_description,
image_id as image_id,
@@ -657,9 +657,9 @@
SELECT
history_id as history_id,
vm_disk_id as vm_disk_id,
- CASE
- WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
- ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
+ CASE
+ WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
+ ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
END as vm_disk_name,
vm_disk_description as vm_disk_description,
image_id as image_id,
diff --git a/data-warehouse/historydbscripts_postgres/create_views_3_2.sql
b/data-warehouse/historydbscripts_postgres/create_views_3_2.sql
index 713fbb3..0134911 100644
--- a/data-warehouse/historydbscripts_postgres/create_views_3_2.sql
+++ b/data-warehouse/historydbscripts_postgres/create_views_3_2.sql
@@ -635,9 +635,9 @@
SELECT
history_id as history_id,
vm_disk_id as vm_disk_id,
- CASE
- WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
- ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
+ CASE
+ WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
+ ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
END as vm_disk_name,
vm_disk_description as vm_disk_description,
image_id as image_id,
@@ -657,9 +657,9 @@
SELECT
history_id as history_id,
vm_disk_id as vm_disk_id,
- CASE
- WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
- ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
+ CASE
+ WHEN vm_disk_name IS NOT NULL THEN vm_disk_name
+ ELSE 'disk ' || cast(vm_internal_drive_mapping as varchar)
END as vm_disk_name,
vm_disk_description as vm_disk_description,
image_id as image_id,
diff --git
a/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql
b/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql
new file mode 100644
index 0000000..fb126c2
--- /dev/null
+++
b/data-warehouse/historydbscripts_postgres/upgrade/03_02_0010_add_vm_users_usage_tables.sql
@@ -0,0 +1,56 @@
+CREATE SEQUENCE statistics_vms_users_usage_hourly_seq INCREMENT BY 1 START
WITH 1;
+CREATE TABLE statistics_vms_users_usage_hourly
+(
+ history_id INTEGER DEFAULT NEXTVAL('statistics_vms_users_usage_hourly_seq')
primary key NOT NULL,
+ history_datetime TIMESTAMP WITH TIME ZONE NOT NULL,
+ user_name VARCHAR(255),
+ vm_id UUID NOT NULL,
+ session_time_in_status DECIMAL(7,2) NOT NULL DEFAULT 1,
+ cpu_usage_percent SMALLINT DEFAULT 0,
+ max_cpu_usage SMALLINT,
+ memory_usage_percent SMALLINT DEFAULT 0,
+ max_memory_usage SMALLINT,
+ user_cpu_usage_percent SMALLINT DEFAULT 0,
+ max_user_cpu_usage_percent SMALLINT DEFAULT 0,
+ system_cpu_usage_percent SMALLINT DEFAULT 0,
+ max_system_cpu_usage_percent SMALLINT DEFAULT 0,
+ vm_last_up_time TIMESTAMP WITH TIME ZONE,
+ vm_last_boot_time TIMESTAMP WITH TIME ZONE,
+ vm_ip VARCHAR(255),
+ currently_running_on_host UUID,
+ vm_configuration_version INTEGER REFERENCES vm_configuration (history_id),
+ current_host_configuration_version INTEGER REFERENCES host_configuration
(history_id)
+) WITH OIDS;
+
+CREATE INDEX IDX_vm_users_usage_history_datetime_hourly ON
statistics_vms_users_usage_hourly (history_datetime);
+CREATE INDEX IDX_vm_users_usage_configuration_version_hourly ON
statistics_vms_users_usage_hourly (vm_configuration_version);
+CREATE INDEX IDX_vm_users_usage_current_host_configuration_hourly ON
statistics_vms_users_usage_hourly (current_host_configuration_version);
+
+CREATE SEQUENCE statistics_vms_users_usage_daily_seq INCREMENT BY 1 START WITH
1;
+CREATE TABLE statistics_vms_users_usage_daily
+(
+ history_id INTEGER DEFAULT NEXTVAL('statistics_vms_users_usage_daily_seq')
primary key NOT NULL,
+ history_datetime TIMESTAMP WITH TIME ZONE NOT NULL,
+ user_name VARCHAR(255),
+ vm_id UUID NOT NULL,
+ session_time_in_status DECIMAL(7,2) NOT NULL DEFAULT 1,
+ cpu_usage_percent SMALLINT DEFAULT 0,
+ max_cpu_usage SMALLINT,
+ memory_usage_percent SMALLINT DEFAULT 0,
+ max_memory_usage SMALLINT,
+ user_cpu_usage_percent SMALLINT DEFAULT 0,
+ max_user_cpu_usage_percent SMALLINT DEFAULT 0,
+ system_cpu_usage_percent SMALLINT DEFAULT 0,
+ max_system_cpu_usage_percent SMALLINT DEFAULT 0,
+ vm_last_up_time TIMESTAMP WITH TIME ZONE,
+ vm_last_boot_time TIMESTAMP WITH TIME ZONE,
+ vm_ip VARCHAR(255),
+ currently_running_on_host UUID,
+ vm_configuration_version INTEGER REFERENCES vm_configuration (history_id),
+ current_host_configuration_version INTEGER REFERENCES host_configuration
(history_id)
+) WITH OIDS;
+
+CREATE INDEX IDX_vm_users_usage_history_datetime_daily ON
statistics_vms_users_usage_daily (history_datetime);
+CREATE INDEX IDX_vm_users_usage_configuration_version_daily ON
statistics_vms_users_usage_daily (vm_configuration_version);
+CREATE INDEX IDX_vm_users_usage_current_host_configuration_daily ON
statistics_vms_users_usage_daily (current_host_configuration_version);
+
--
To view, visit http://gerrit.ovirt.org/9332
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ife90396992238b9919bcf6377825c12a17082871
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-dwh
Gerrit-Branch: master
Gerrit-Owner: Yaniv Dary <yd...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
