Shirly Radco has uploaded a new change for review.
Change subject: history: added views and table for users data
......................................................................
history: added views and table for users data
Added users_history table to history db,
added user_id to vm_samples_history,
vm_hourly_history and vm_daily_history.
Added views v3_5_history_users
and v3_5_latest_users and added user_id
to v3_5_statistics_vms_resources_usage_samples,
v3_5_statistics_vms_resources_usage_hourly
and v3_5_statistics_vms_resources_usage_daily.
Change-Id: Id236a3112947e00a58175f0361d44afda692b894
Signed-off-by: Shirly Radco <sra...@redhat.com>
---
M packaging/dbscripts/create_views_3_5.sql
A packaging/dbscripts/upgrade/03_05_0020_add_users_history_table.sql
2 files changed, 72 insertions(+), 0 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-dwh refs/changes/38/27338/1
diff --git a/packaging/dbscripts/create_views_3_5.sql
b/packaging/dbscripts/create_views_3_5.sql
index c2b2a4c..5afd242 100644
--- a/packaging/dbscripts/create_views_3_5.sql
+++ b/packaging/dbscripts/create_views_3_5.sql
@@ -513,6 +513,7 @@
a.vm_ip as vm_ip,
a.vm_client_ip,
a.currently_running_on_host as currently_running_on_host,
+ a.current_user_id as current_user_id,
a.current_user_name as current_user_name,
a.user_logged_in_to_guest,
b.disks_usage as disks_usage,
@@ -540,6 +541,7 @@
a.max_system_cpu_usage_percent as max_system_cpu_usage_percent,
a.vm_ip as vm_ip,
a.currently_running_on_host as currently_running_on_host,
+ a.current_user_id as current_user_id,
a.current_user_name as current_user_name,
b.disks_usage as disks_usage,
a.vm_configuration_version as vm_configuration_version,
@@ -566,6 +568,7 @@
a.max_system_cpu_usage_percent as max_system_cpu_usage_percent,
a.vm_ip as vm_ip,
a.currently_running_on_host as currently_running_on_host,
+ a.current_user_id as current_user_id,
a.current_user_name as current_user_name,
b.disks_usage as disks_usage,
a.vm_configuration_version as vm_configuration_version,
@@ -578,6 +581,7 @@
AS
SELECT history_id,
history_datetime,
+ user_id,
user_name,
vm_id,
session_time_in_minutes,
@@ -601,6 +605,7 @@
AS
SELECT history_id,
history_datetime,
+ user_id,
user_name,
vm_id,
session_time_in_minutes,
@@ -836,6 +841,48 @@
WHERE history_id in (SELECT max(a.history_id) FROM vm_device_history as a
GROUP BY a.vm_id, a.device_id)
and delete_date IS NULL;
+CREATE OR REPLACE VIEW v3_5_history_users
+ AS
+SELECT
+ history_id,
+ history_datetime,
+ user_id,
+ first_name,
+ last_name,
+ domain,
+ username,
+ department,
+ user_role_title,
+ email,
+ external_id,
+ active,
+ create_date,
+ update_date,
+ delete_date
+FROM users_history;
+
+CREATE OR REPLACE VIEW v3_5_latest_users
+ AS
+SELECT
+ history_id,
+ history_datetime,
+ user_id,
+ first_name,
+ last_name,
+ domain,
+ username,
+ department,
+ user_role_title,
+ email,
+ external_id,
+ active,
+ create_date,
+ update_date,
+ delete_date
+FROM users_history
+WHERE history_id in (SELECT max(a.history_id) FROM users_history as a GROUP BY
a.user_id)
+ and delete_date IS NULL;
+
CREATE OR REPLACE VIEW v3_5_tags_relations_history
AS
SELECT history_id as history_id,
diff --git a/packaging/dbscripts/upgrade/03_05_0020_add_users_history_table.sql
b/packaging/dbscripts/upgrade/03_05_0020_add_users_history_table.sql
new file mode 100644
index 0000000..0a13f09
--- /dev/null
+++ b/packaging/dbscripts/upgrade/03_05_0020_add_users_history_table.sql
@@ -0,0 +1,25 @@
+CREATE TABLE users_history
+(
+ history_id INTEGER DEFAULT NEXTVAL('configuration_seq') primary key NOT NULL,
+ history_datetime DATE NOT NULL,
+ user_id uuid NOT NULL,
+ first_name character varying(255),
+ last_name character varying(255),
+ domain character varying(255) NOT NULL,
+ username character varying(255) NOT NULL,
+ department character varying(255),
+ user_role_title character varying(255),
+ email character varying(255),
+ external_id bytea NOT NULL DEFAULT ''::bytea,
+ active boolean NOT NULL DEFAULT false,
+ create_date TIMESTAMP WITH TIME ZONE NOT NULL,
+ update_date TIMESTAMP WITH TIME ZONE,
+ delete_date TIMESTAMP WITH TIME ZONE
+) WITH OIDS;
+
+CREATE INDEX users_history_user_id_idx ON users_history(user_id);
+
+SELECT fn_db_add_column('vm_samples_history', 'current_user_id', 'UUID NOT
NULL');
+SELECT fn_db_add_column('vm_hourly_history', 'current_user_id', 'UUID NOT
NULL');
+SELECT fn_db_add_column('vm_daily_history', 'current_user_id', 'UUID NOT
NULL');
+
--
To view, visit http://gerrit.ovirt.org/27338
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id236a3112947e00a58175f0361d44afda692b894
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-dwh
Gerrit-Branch: master
Gerrit-Owner: Shirly Radco <sra...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
