rel-eng/packages/spacewalk-schema
| 2
schema/spacewalk/common/tables/log.sql
| 4
schema/spacewalk/oracle/packages/logging.pkb
| 156 +++---
schema/spacewalk/oracle/packages/logging.pks
| 3
schema/spacewalk/postgres/packages/logging.pkb
| 98 ++--
schema/spacewalk/postgres/packages/logging.pks
| 2
schema/spacewalk/spacewalk-schema.spec
| 10
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle
| 4
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
| 4
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle
| 1
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql
| 5
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
| 25 +
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
| 2
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
| 147 ++++++
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
| 236 ++++++++++
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql
| 2
16 files changed, 602 insertions(+), 99 deletions(-)
New commits:
commit 7ae88a56be7af25fb89b2916974c844aa2d3823a
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Wed Jul 24 12:14:23 2013 +0200
Automatic commit of package [spacewalk-schema] release [2.1.2-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema
index 1a3cde3..feca68d 100644
--- a/rel-eng/packages/spacewalk-schema
+++ b/rel-eng/packages/spacewalk-schema
@@ -1 +1 @@
-2.1.1-1 schema/spacewalk/
+2.1.2-1 schema/spacewalk/
diff --git a/schema/spacewalk/spacewalk-schema.spec
b/schema/spacewalk/spacewalk-schema.spec
index f8ade67..cbdb6db 100644
--- a/schema/spacewalk/spacewalk-schema.spec
+++ b/schema/spacewalk/spacewalk-schema.spec
@@ -4,7 +4,7 @@ Name: spacewalk-schema
Group: Applications/Internet
Summary: Oracle SQL schema for Spacewalk server
-Version: 2.1.1
+Version: 2.1.2
Release: 1%{?dist}
Source0: %{name}-%{version}.tar.gz
@@ -71,6 +71,14 @@ rm -rf $RPM_BUILD_ROOT
%{_mandir}/man1/spacewalk-sql*
%changelog
+* Wed Jul 24 2013 Tomas Lestach <tlestach(a)redhat.com> 2.1.2-1
+- fix oracle equivalent source sha1
+- fix_f624b843
+- extract logging.recreate_trigger from logging.enable_logging
+- replace integer with number/ric in logging package
+- change log_id type on logged tables
+- change log table integer -> number
+
* Tue Jul 23 2013 Michael Mraka <michael.mraka(a)redhat.com> 2.1.1-1
- 876019 - schema upgrade scripts
- made serverchannels cursor properly parametrized
commit ed94fcc2c0f301baf3b4915936d192bcf06585be
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Wed Jul 24 12:13:54 2013 +0200
fix oracle equivalent source sha1
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql
index c02ee78..1ec95e3 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 b34e5effdc23dd2bc7bb9c805c71a0396d947848
+-- oracle equivalent source sha1 49a42a42768d83a188a939412c353bf6de4013ab
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
commit 6bc65e46f0b6693764c0562f30a2dabb69d3e1d7
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Wed Jul 24 12:12:05 2013 +0200
fix_f624b843
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
index 3c5f689..b207494 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 db59999d5662a6f91b00e119e676577fc054e02e
+-- oracle equivalent source sha1 4ef54d7b4132976de79f3b6a202ab85b6fe507ed
ALTER TABLE log ALTER id TYPE numeric;
ALTER TABLE log ALTER user_id TYPE numeric;
commit 42ab5d86f1679b65ae322f892c1209d33316a6c1
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Wed Jul 24 09:13:44 2013 +0200
extract logging.recreate_trigger from logging.enable_logging
+ fix lower issues on PG
diff --git a/schema/spacewalk/oracle/packages/logging.pkb
b/schema/spacewalk/oracle/packages/logging.pkb
index 797c203..78566a7 100644
--- a/schema/spacewalk/oracle/packages/logging.pkb
+++ b/schema/spacewalk/oracle/packages/logging.pkb
@@ -18,6 +18,7 @@ is
the_log_id number;
the_user_id number;
the_stamp timestamp with local time zone;
+
procedure clear_log_id
is
begin
@@ -25,6 +26,7 @@ is
the_user_id := null;
the_stamp := current_timestamp;
end clear_log_id;
+
procedure set_log_auth(user_id in number)
is
begin
@@ -34,6 +36,7 @@ is
the_user_id := user_id;
the_stamp := current_timestamp;
end set_log_auth;
+
function get_log_id return number
is
begin
@@ -48,72 +51,97 @@ is
return the_log_id;
end get_log_id;
-procedure enable_logging(table_name_in in varchar)
-is
- pk_column varchar(512);
- ddl_columns varchar(4000);
- the_insert varchar(4000);
- already_not_null exception;
- pragma exception_init(already_not_null, -1442);
-begin
- select column_name into pk_column
- from user_cons_columns
- where constraint_name = (
- select constraint_name
- from user_constraints
- where table_name = upper(table_name_in)
- and constraint_type = 'P'
- and owner = user
- )
- and owner = user ;
- ddl_columns := '';
- for rec in (
- select column_name
- from user_tab_columns
- where table_name = upper(table_name_in)
- and column_name not in ( pk_column, 'CREATED', 'MODIFIED' )
- order by column_id
- ) loop
- ddl_columns := ddl_columns || ', ' || rec.column_name;
- end loop;
+ function get_pk_column(table_name_in in varchar) return varchar
+ is
+ pk_column varchar(512);
+ begin
+ select column_name into pk_column
+ from user_cons_columns
+ where constraint_name = (
+ select constraint_name
+ from user_constraints
+ where table_name = upper(table_name_in)
+ and constraint_type = 'P'
+ and owner = user
+ )
+ and owner = user ;
+ return pk_column;
+ end get_pk_column;
- execute immediate 'create table ' || table_name_in || '_log
- as select ' || pk_column || ', logging.get_log_id() as log_id,
''A'' as action' || ddl_columns
- || ' from ' || table_name_in;
+ function get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) return
varchar
+ is
+ ddl_columns varchar(4000);
+ begin
+ ddl_columns := '';
+ for rec in (
+ select column_name
+ from user_tab_columns
+ where table_name = upper(table_name_in)
+ and column_name not in ( pk_column_in, 'CREATED',
'MODIFIED' )
+ order by column_id
+ ) loop
+ ddl_columns := ddl_columns || ', ' || rec.column_name;
+ end loop;
+ return ddl_columns;
+ end get_ddl_columns;
+
+ procedure recreate_trigger(table_name_in in varchar)
+ is
+ pk_column varchar(512);
+ ddl_columns varchar(4000);
+ the_insert varchar(4000);
+ begin
+ pk_column := get_pk_column(table_name_in);
+ ddl_columns := get_ddl_columns(table_name_in, pk_column);
+ the_insert := 'insert into ' || table_name_in || '_log (log_id,
action, ' || pk_column || ddl_columns ||')
+ values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', :old.') || ');';
+ execute immediate 'create or replace trigger ' || table_name_in ||
'_log_trig
+ after insert or update or delete on ' || table_name_in || '
+ for each row
+ declare
+ log_id_v number;
+ tg_op char(1);
+ begin
+ log_id_v := logging.get_log_id();
+ if updating then
+ if :old.' || pk_column || ' <> :new.' || pk_column
|| ' then raise_application_error(-20298, ''Cannot update column ' ||
table_name_in || '.' || pk_column || '.''); end if;
+ tg_op := ''U'';
+ end if;
+ if deleting then
+ tg_op := ''D'';
+ ' || the_insert || '
+ else
+ if inserting then tg_op := ''I''; end if;
+ ' || replace(the_insert, ':old.', ':new.') ||
'
+ end if;
+ end;
+ ';
+ end recreate_trigger;
+
+ procedure enable_logging(table_name_in in varchar)
+ is
+ pk_column varchar(512);
+ ddl_columns varchar(4000);
+ already_not_null exception;
+ pragma exception_init(already_not_null, -1442);
begin
- execute immediate 'alter table ' || table_name_in || '_log modify
' || pk_column || ' not null';
- exception when already_not_null then
- null;
- end;
- execute immediate 'create index ' || table_name_in || '_log_idx on '
|| table_name_in || '_log(' || pk_column || ')';
- execute immediate 'alter table ' || table_name_in || '_log modify log_id
not null';
- execute immediate 'alter table ' || table_name_in || '_log add foreign
key (log_id) references log(id)';
- execute immediate 'alter table ' || table_name_in || '_log modify action
not null';
+ pk_column := get_pk_column(table_name_in);
+ ddl_columns := get_ddl_columns(table_name_in, pk_column);
- the_insert := 'insert into ' || table_name_in || '_log (log_id, action,
' || pk_column || ddl_columns ||')
- values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', :old.') || ');';
- execute immediate 'create or replace trigger ' || table_name_in ||
'_log_trig
- after insert or update or delete on ' || table_name_in || '
- for each row
- declare
- log_id_v number;
- tg_op char(1);
+ execute immediate 'create table ' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id() as log_id,
''A'' as action' || ddl_columns
+ || ' from ' || table_name_in;
begin
- log_id_v := logging.get_log_id();
- if updating then
- if :old.' || pk_column || ' <> :new.' || pk_column ||
' then raise_application_error(-20298, ''Cannot update column ' ||
table_name_in || '.' || pk_column || '.''); end if;
- tg_op := ''U'';
- end if;
- if deleting then
- tg_op := ''D'';
- ' || the_insert || '
- else
- if inserting then tg_op := ''I''; end if;
- ' || replace(the_insert, ':old.', ':new.') || '
- end if;
+ execute immediate 'alter table ' || table_name_in || '_log modify
' || pk_column || ' not null';
+ exception when already_not_null then
+ null;
end;
- ';
-end enable_logging;
+ execute immediate 'create index ' || table_name_in || '_log_idx on
' || table_name_in || '_log(' || pk_column || ')';
+ execute immediate 'alter table ' || table_name_in || '_log modify
log_id not null';
+ execute immediate 'alter table ' || table_name_in || '_log add
foreign key (log_id) references log(id)';
+ execute immediate 'alter table ' || table_name_in || '_log modify
action not null';
+ recreate_trigger(table_name_in);
+ end enable_logging;
end logging;
/
show errors
diff --git a/schema/spacewalk/oracle/packages/logging.pks
b/schema/spacewalk/oracle/packages/logging.pks
index 7dac79c..adcd248 100644
--- a/schema/spacewalk/oracle/packages/logging.pks
+++ b/schema/spacewalk/oracle/packages/logging.pks
@@ -18,6 +18,7 @@ is
procedure clear_log_id;
procedure set_log_auth(user_id in number);
function get_log_id return number;
+ procedure recreate_trigger(table_name_in in varchar);
procedure enable_logging(table_name_in in varchar);
end logging;
/
diff --git a/schema/spacewalk/postgres/packages/logging.pkb
b/schema/spacewalk/postgres/packages/logging.pkb
index 4304e46..084b3a5 100644
--- a/schema/spacewalk/postgres/packages/logging.pkb
+++ b/schema/spacewalk/postgres/packages/logging.pkb
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e
+-- oracle equivalent source sha1 386fd52474c4e2ea59ec9a9fc6271bc3a1126adb
--
-- Copyright (c) 2013 Red Hat, Inc.
--
@@ -106,21 +106,18 @@ begin
end;
$$ language plpgsql set search_path from current;
-create or replace function enable_logging(table_name_in in varchar)
-returns void
+create or replace function _get_primary_key_column(table_name_in in varchar)
+returns varchar
as
$$
declare
pk_column varchar;
- ddl_columns varchar;
- the_insert varchar;
- rec record;
begin
select constraint_column_usage.column_name into strict pk_column
from information_schema.table_constraints,
information_schema.constraint_column_usage
where constraint_column_usage.table_catalog = current_catalog
and constraint_column_usage.table_schema = 'public'
- and constraint_column_usage.table_name = table_name_in
+ and constraint_column_usage.table_name = lower(table_name_in)
and constraint_column_usage.constraint_catalog = current_catalog
and constraint_column_usage.constraint_schema = 'public'
and constraint_column_usage.table_catalog = table_constraints.constraint_catalog
@@ -130,27 +127,47 @@ begin
and constraint_column_usage.constraint_catalog =
table_constraints.constraint_catalog
and constraint_column_usage.constraint_name = table_constraints.constraint_name
and table_constraints.constraint_type = 'PRIMARY KEY';
+ return pk_column;
+end;
+$$
+language plpgsql set search_path from current;
+
+create or replace function _get_ddl_columns(table_name_in in varchar, pk_column_in in
varchar)
+returns varchar
+as
+$$
+declare
+ ddl_columns varchar;
+ rec record;
+begin
ddl_columns := '';
for rec in (
select column_name
from information_schema.columns
where table_catalog = current_catalog
and table_schema = 'public'
- and table_name = table_name_in
- and column_name not in ( pk_column, 'created', 'modified' )
+ and table_name = lower(table_name_in)
+ and column_name not in (pk_column_in, 'created', 'modified'
)
) loop
ddl_columns := ddl_columns || ', ' || rec.column_name;
end loop;
+ return ddl_columns;
+end;
+$$
+language plpgsql set search_path from current;
- execute 'create table public.' || table_name_in || '_log
- as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
- || ' from ' || table_name_in;
- execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
- execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
- execute 'alter table public.' || table_name_in || '_log alter log_id set
not null';
- execute 'alter table public.' || table_name_in || '_log add foreign key
(log_id) references log(id)';
- execute 'alter table public.' || table_name_in || '_log alter action set
not null';
+create or replace function recreate_trigger(table_name_in in varchar)
+returns void
+as
+$$
+declare
+ pk_column varchar;
+ ddl_columns varchar;
+ the_insert varchar;
+begin
+ pk_column := _get_primary_key_column(table_name_in);
+ ddl_columns := _get_ddl_columns(table_name_in, pk_column);
the_insert := 'insert into public.' || table_name_in || '_log (log_id,
action, ' || pk_column || ddl_columns ||')
values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', old.') || ');';
execute 'create or replace function public.' || table_name_in ||
'_log_trig_fun() returns trigger as
@@ -172,6 +189,7 @@ begin
''
language plpgsql;';
+ execute 'drop trigger if exists ' || table_name_in || '_log_trig' ||
' on ' || table_name_in;
execute 'create trigger ' || table_name_in || '_log_trig
after insert or update or delete on public.' || table_name_in || '
for each row
@@ -179,6 +197,30 @@ begin
end;
$$
-language plpgsql;
+language plpgsql set search_path from current;
+
+create or replace function enable_logging(table_name_in in varchar)
+returns void
+as
+$$
+declare
+ pk_column varchar;
+ ddl_columns varchar;
+begin
+ pk_column := _get_primary_key_column(table_name_in);
+ ddl_columns := _get_ddl_columns(table_name_in, pk_column);
+
+ execute 'create table public.' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
+ || ' from ' || table_name_in;
+ execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
+ execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
+ execute 'alter table public.' || table_name_in || '_log alter log_id set
not null';
+ execute 'alter table public.' || table_name_in || '_log add foreign key
(log_id) references log(id)';
+ execute 'alter table public.' || table_name_in || '_log alter action set
not null';
+ perform recreate_trigger(table_name_in);
+end;
+$$
+language plpgsql set search_path from current;
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('logging')+1) ) where name = 'search_path';
diff --git a/schema/spacewalk/postgres/packages/logging.pks
b/schema/spacewalk/postgres/packages/logging.pks
index fcbb09a..8076191 100644
--- a/schema/spacewalk/postgres/packages/logging.pks
+++ b/schema/spacewalk/postgres/packages/logging.pks
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7
+-- oracle equivalent source sha1 bf830c8fe841b043caab71aa49bef3903c5df39b
--
-- Copyright (c) 2013 Red Hat, Inc.
--
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
index 7dac79c..adcd248 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
@@ -18,6 +18,7 @@ is
procedure clear_log_id;
procedure set_log_auth(user_id in number);
function get_log_id return number;
+ procedure recreate_trigger(table_name_in in varchar);
procedure enable_logging(table_name_in in varchar);
end logging;
/
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
index 73c181c..c1ac623 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
@@ -1,2 +1,2 @@
--- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7
+-- oracle equivalent source sha1 bf830c8fe841b043caab71aa49bef3903c5df39b
-- This file has intentionally been left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
index 797c203..78566a7 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
@@ -18,6 +18,7 @@ is
the_log_id number;
the_user_id number;
the_stamp timestamp with local time zone;
+
procedure clear_log_id
is
begin
@@ -25,6 +26,7 @@ is
the_user_id := null;
the_stamp := current_timestamp;
end clear_log_id;
+
procedure set_log_auth(user_id in number)
is
begin
@@ -34,6 +36,7 @@ is
the_user_id := user_id;
the_stamp := current_timestamp;
end set_log_auth;
+
function get_log_id return number
is
begin
@@ -48,72 +51,97 @@ is
return the_log_id;
end get_log_id;
-procedure enable_logging(table_name_in in varchar)
-is
- pk_column varchar(512);
- ddl_columns varchar(4000);
- the_insert varchar(4000);
- already_not_null exception;
- pragma exception_init(already_not_null, -1442);
-begin
- select column_name into pk_column
- from user_cons_columns
- where constraint_name = (
- select constraint_name
- from user_constraints
- where table_name = upper(table_name_in)
- and constraint_type = 'P'
- and owner = user
- )
- and owner = user ;
- ddl_columns := '';
- for rec in (
- select column_name
- from user_tab_columns
- where table_name = upper(table_name_in)
- and column_name not in ( pk_column, 'CREATED', 'MODIFIED' )
- order by column_id
- ) loop
- ddl_columns := ddl_columns || ', ' || rec.column_name;
- end loop;
+ function get_pk_column(table_name_in in varchar) return varchar
+ is
+ pk_column varchar(512);
+ begin
+ select column_name into pk_column
+ from user_cons_columns
+ where constraint_name = (
+ select constraint_name
+ from user_constraints
+ where table_name = upper(table_name_in)
+ and constraint_type = 'P'
+ and owner = user
+ )
+ and owner = user ;
+ return pk_column;
+ end get_pk_column;
- execute immediate 'create table ' || table_name_in || '_log
- as select ' || pk_column || ', logging.get_log_id() as log_id,
''A'' as action' || ddl_columns
- || ' from ' || table_name_in;
+ function get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) return
varchar
+ is
+ ddl_columns varchar(4000);
+ begin
+ ddl_columns := '';
+ for rec in (
+ select column_name
+ from user_tab_columns
+ where table_name = upper(table_name_in)
+ and column_name not in ( pk_column_in, 'CREATED',
'MODIFIED' )
+ order by column_id
+ ) loop
+ ddl_columns := ddl_columns || ', ' || rec.column_name;
+ end loop;
+ return ddl_columns;
+ end get_ddl_columns;
+
+ procedure recreate_trigger(table_name_in in varchar)
+ is
+ pk_column varchar(512);
+ ddl_columns varchar(4000);
+ the_insert varchar(4000);
+ begin
+ pk_column := get_pk_column(table_name_in);
+ ddl_columns := get_ddl_columns(table_name_in, pk_column);
+ the_insert := 'insert into ' || table_name_in || '_log (log_id,
action, ' || pk_column || ddl_columns ||')
+ values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', :old.') || ');';
+ execute immediate 'create or replace trigger ' || table_name_in ||
'_log_trig
+ after insert or update or delete on ' || table_name_in || '
+ for each row
+ declare
+ log_id_v number;
+ tg_op char(1);
+ begin
+ log_id_v := logging.get_log_id();
+ if updating then
+ if :old.' || pk_column || ' <> :new.' || pk_column
|| ' then raise_application_error(-20298, ''Cannot update column ' ||
table_name_in || '.' || pk_column || '.''); end if;
+ tg_op := ''U'';
+ end if;
+ if deleting then
+ tg_op := ''D'';
+ ' || the_insert || '
+ else
+ if inserting then tg_op := ''I''; end if;
+ ' || replace(the_insert, ':old.', ':new.') ||
'
+ end if;
+ end;
+ ';
+ end recreate_trigger;
+
+ procedure enable_logging(table_name_in in varchar)
+ is
+ pk_column varchar(512);
+ ddl_columns varchar(4000);
+ already_not_null exception;
+ pragma exception_init(already_not_null, -1442);
begin
- execute immediate 'alter table ' || table_name_in || '_log modify
' || pk_column || ' not null';
- exception when already_not_null then
- null;
- end;
- execute immediate 'create index ' || table_name_in || '_log_idx on '
|| table_name_in || '_log(' || pk_column || ')';
- execute immediate 'alter table ' || table_name_in || '_log modify log_id
not null';
- execute immediate 'alter table ' || table_name_in || '_log add foreign
key (log_id) references log(id)';
- execute immediate 'alter table ' || table_name_in || '_log modify action
not null';
+ pk_column := get_pk_column(table_name_in);
+ ddl_columns := get_ddl_columns(table_name_in, pk_column);
- the_insert := 'insert into ' || table_name_in || '_log (log_id, action,
' || pk_column || ddl_columns ||')
- values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', :old.') || ');';
- execute immediate 'create or replace trigger ' || table_name_in ||
'_log_trig
- after insert or update or delete on ' || table_name_in || '
- for each row
- declare
- log_id_v number;
- tg_op char(1);
+ execute immediate 'create table ' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id() as log_id,
''A'' as action' || ddl_columns
+ || ' from ' || table_name_in;
begin
- log_id_v := logging.get_log_id();
- if updating then
- if :old.' || pk_column || ' <> :new.' || pk_column ||
' then raise_application_error(-20298, ''Cannot update column ' ||
table_name_in || '.' || pk_column || '.''); end if;
- tg_op := ''U'';
- end if;
- if deleting then
- tg_op := ''D'';
- ' || the_insert || '
- else
- if inserting then tg_op := ''I''; end if;
- ' || replace(the_insert, ':old.', ':new.') || '
- end if;
+ execute immediate 'alter table ' || table_name_in || '_log modify
' || pk_column || ' not null';
+ exception when already_not_null then
+ null;
end;
- ';
-end enable_logging;
+ execute immediate 'create index ' || table_name_in || '_log_idx on
' || table_name_in || '_log(' || pk_column || ')';
+ execute immediate 'alter table ' || table_name_in || '_log modify
log_id not null';
+ execute immediate 'alter table ' || table_name_in || '_log add
foreign key (log_id) references log(id)';
+ execute immediate 'alter table ' || table_name_in || '_log modify
action not null';
+ recreate_trigger(table_name_in);
+ end enable_logging;
end logging;
/
show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
index 409f761..80a0a31 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e
+-- oracle equivalent source sha1 386fd52474c4e2ea59ec9a9fc6271bc3a1126adb
--
-- Copyright (c) 2013 Red Hat, Inc.
--
@@ -19,12 +19,12 @@
update pg_settings set setting = 'logging,' || setting where name =
'search_path';
drop function clear_log_id();
-drop function _set_log_auth(user_id in numeric, stamp in varchar);
-drop function set_log_auth(user_id in numeric);
+drop function _set_log_auth(user_id in integer, stamp in varchar);
+drop function set_log_auth(user_id in integer);
drop function _get_log_auth();
drop function _get_log_stamp();
drop function _get_log_id();
-drop function _set_log_id(log_id in numeric);
+drop function _set_log_id(log_id in integer);
drop function get_log_id();
drop function enable_logging(table_name_in in varchar);
@@ -116,21 +116,18 @@ begin
end;
$$ language plpgsql set search_path from current;
-create or replace function enable_logging(table_name_in in varchar)
-returns void
+create or replace function _get_primary_key_column(table_name_in in varchar)
+returns varchar
as
$$
declare
pk_column varchar;
- ddl_columns varchar;
- the_insert varchar;
- rec record;
begin
select constraint_column_usage.column_name into strict pk_column
from information_schema.table_constraints,
information_schema.constraint_column_usage
where constraint_column_usage.table_catalog = current_catalog
and constraint_column_usage.table_schema = 'public'
- and constraint_column_usage.table_name = table_name_in
+ and constraint_column_usage.table_name = lower(table_name_in)
and constraint_column_usage.constraint_catalog = current_catalog
and constraint_column_usage.constraint_schema = 'public'
and constraint_column_usage.table_catalog = table_constraints.constraint_catalog
@@ -140,27 +137,47 @@ begin
and constraint_column_usage.constraint_catalog =
table_constraints.constraint_catalog
and constraint_column_usage.constraint_name = table_constraints.constraint_name
and table_constraints.constraint_type = 'PRIMARY KEY';
+ return pk_column;
+end;
+$$
+language plpgsql set search_path from current;
+
+create or replace function _get_ddl_columns(table_name_in in varchar, pk_column_in in
varchar)
+returns varchar
+as
+$$
+declare
+ ddl_columns varchar;
+ rec record;
+begin
ddl_columns := '';
for rec in (
select column_name
from information_schema.columns
where table_catalog = current_catalog
and table_schema = 'public'
- and table_name = table_name_in
- and column_name not in ( pk_column, 'created', 'modified' )
+ and table_name = lower(table_name_in)
+ and column_name not in (pk_column_in, 'created', 'modified'
)
) loop
ddl_columns := ddl_columns || ', ' || rec.column_name;
end loop;
+ return ddl_columns;
+end;
+$$
+language plpgsql set search_path from current;
- execute 'create table public.' || table_name_in || '_log
- as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
- || ' from ' || table_name_in;
- execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
- execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
- execute 'alter table public.' || table_name_in || '_log alter log_id set
not null';
- execute 'alter table public.' || table_name_in || '_log add foreign key
(log_id) references log(id)';
- execute 'alter table public.' || table_name_in || '_log alter action set
not null';
+create or replace function recreate_trigger(table_name_in in varchar)
+returns void
+as
+$$
+declare
+ pk_column varchar;
+ ddl_columns varchar;
+ the_insert varchar;
+begin
+ pk_column := _get_primary_key_column(table_name_in);
+ ddl_columns := _get_ddl_columns(table_name_in, pk_column);
the_insert := 'insert into public.' || table_name_in || '_log (log_id,
action, ' || pk_column || ddl_columns ||')
values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', old.') || ');';
execute 'create or replace function public.' || table_name_in ||
'_log_trig_fun() returns trigger as
@@ -182,6 +199,7 @@ begin
''
language plpgsql;';
+ execute 'drop trigger if exists ' || table_name_in || '_log_trig' ||
' on ' || table_name_in;
execute 'create trigger ' || table_name_in || '_log_trig
after insert or update or delete on public.' || table_name_in || '
for each row
@@ -189,6 +207,30 @@ begin
end;
$$
-language plpgsql;
+language plpgsql set search_path from current;
+
+create or replace function enable_logging(table_name_in in varchar)
+returns void
+as
+$$
+declare
+ pk_column varchar;
+ ddl_columns varchar;
+begin
+ pk_column := _get_primary_key_column(table_name_in);
+ ddl_columns := _get_ddl_columns(table_name_in, pk_column);
+
+ execute 'create table public.' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
+ || ' from ' || table_name_in;
+ execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
+ execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
+ execute 'alter table public.' || table_name_in || '_log alter log_id set
not null';
+ execute 'alter table public.' || table_name_in || '_log add foreign key
(log_id) references log(id)';
+ execute 'alter table public.' || table_name_in || '_log alter action set
not null';
+ perform recreate_trigger(table_name_in);
+end;
+$$
+language plpgsql set search_path from current;
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('logging')+1) ) where name = 'search_path';
commit 6dc9f81c3ba4dd8b6fcf7994b66108857a9e5f22
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Tue Jul 23 11:55:30 2013 +0200
replace integer with number/ric in logging package
diff --git a/schema/spacewalk/oracle/packages/logging.pkb
b/schema/spacewalk/oracle/packages/logging.pkb
index 946e0d1..797c203 100644
--- a/schema/spacewalk/oracle/packages/logging.pkb
+++ b/schema/spacewalk/oracle/packages/logging.pkb
@@ -15,8 +15,8 @@
create or replace package body logging
is
- the_log_id integer;
- the_user_id integer;
+ the_log_id number;
+ the_user_id number;
the_stamp timestamp with local time zone;
procedure clear_log_id
is
@@ -25,7 +25,7 @@ is
the_user_id := null;
the_stamp := current_timestamp;
end clear_log_id;
- procedure set_log_auth(user_id in integer)
+ procedure set_log_auth(user_id in number)
is
begin
if the_stamp is null then
@@ -96,7 +96,7 @@ begin
after insert or update or delete on ' || table_name_in || '
for each row
declare
- log_id_v integer;
+ log_id_v number;
tg_op char(1);
begin
log_id_v := logging.get_log_id();
diff --git a/schema/spacewalk/oracle/packages/logging.pks
b/schema/spacewalk/oracle/packages/logging.pks
index 1a324ef..7dac79c 100644
--- a/schema/spacewalk/oracle/packages/logging.pks
+++ b/schema/spacewalk/oracle/packages/logging.pks
@@ -16,7 +16,7 @@
create or replace package logging
is
procedure clear_log_id;
- procedure set_log_auth(user_id in integer);
+ procedure set_log_auth(user_id in number);
function get_log_id return number;
procedure enable_logging(table_name_in in varchar);
end logging;
diff --git a/schema/spacewalk/postgres/packages/logging.pkb
b/schema/spacewalk/postgres/packages/logging.pkb
index 0697c41..4304e46 100644
--- a/schema/spacewalk/postgres/packages/logging.pkb
+++ b/schema/spacewalk/postgres/packages/logging.pkb
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 5cac57e2eb784ae8a4f66d945ca38110ce6497b4
+-- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e
--
-- Copyright (c) 2013 Red Hat, Inc.
--
@@ -31,7 +31,7 @@ $$
return
$$ language pltclu set search_path from current;
-create or replace function _set_log_auth(user_id in integer, stamp in varchar)
+create or replace function _set_log_auth(user_id in numeric, stamp in varchar)
returns void
as
$$
@@ -41,7 +41,7 @@ $$
set the_stamp $2
$$ language pltclu set search_path from current;
-create or replace function set_log_auth(user_id in integer)
+create or replace function set_log_auth(user_id in numeric)
returns void
as
$$
@@ -51,7 +51,7 @@ end
$$ language plpgsql set search_path from current;
create or replace function _get_log_auth()
-returns integer
+returns numeric
as
$$
global the_user_id
@@ -67,14 +67,14 @@ $$
$$ language pltclu set search_path from current;
create or replace function _get_log_id()
-returns integer
+returns numeric
as
$$
global the_log_id
return $the_log_id
$$ language pltclu set search_path from current;
-create or replace function _set_log_id(log_id in integer)
+create or replace function _set_log_id(log_id in numeric)
returns void
as
$$
@@ -84,12 +84,12 @@ $$
$$ language pltclu set search_path from current;
create or replace function get_log_id()
-returns integer
+returns numeric
as
$$
declare
- the_log_id integer;
- the_user_id integer;
+ the_log_id numeric;
+ the_user_id numeric;
the_stamp varchar;
begin
the_log_id := _get_log_id();
@@ -100,7 +100,7 @@ begin
insert into public.log (id, stamp, user_id)
values (the_log_id,
case when the_stamp = '' then current_timestamp else the_stamp::timestamp
with time zone end,
- case when the_user_id = '0' then null else the_user_id::integer end);
+ case when the_user_id = '0' then null else the_user_id::numeric end);
perform _set_log_id(the_log_id);
return the_log_id;
end;
@@ -143,7 +143,7 @@ begin
end loop;
execute 'create table public.' || table_name_in || '_log
- as select ' || pk_column || ', logging.get_log_id()::integer as log_id,
''A''::char as action' || ddl_columns
+ as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
|| ' from ' || table_name_in;
execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
@@ -156,7 +156,7 @@ begin
execute 'create or replace function public.' || table_name_in ||
'_log_trig_fun() returns trigger as
''
declare
- log_id_v integer;
+ log_id_v numeric;
begin
log_id_v := logging.get_log_id();
if tg_op = ''''UPDATE'''' then
diff --git a/schema/spacewalk/postgres/packages/logging.pks
b/schema/spacewalk/postgres/packages/logging.pks
index 7385974..fcbb09a 100644
--- a/schema/spacewalk/postgres/packages/logging.pks
+++ b/schema/spacewalk/postgres/packages/logging.pks
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 5f0b00361ac0a36d5ec4c2ddae6d0f7de583407d
+-- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7
--
-- Copyright (c) 2013 Red Hat, Inc.
--
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
new file mode 100644
index 0000000..7dac79c
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle
@@ -0,0 +1,24 @@
+--
+-- Copyright (c) 2013 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+create or replace package logging
+is
+ procedure clear_log_id;
+ procedure set_log_auth(user_id in number);
+ function get_log_id return number;
+ procedure enable_logging(table_name_in in varchar);
+end logging;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
new file mode 100644
index 0000000..73c181c
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7
+-- This file has intentionally been left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
new file mode 100644
index 0000000..797c203
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle
@@ -0,0 +1,119 @@
+--
+-- Copyright (c) 2013 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+create or replace package body logging
+is
+ the_log_id number;
+ the_user_id number;
+ the_stamp timestamp with local time zone;
+ procedure clear_log_id
+ is
+ begin
+ the_log_id := null;
+ the_user_id := null;
+ the_stamp := current_timestamp;
+ end clear_log_id;
+ procedure set_log_auth(user_id in number)
+ is
+ begin
+ if the_stamp is null then
+ raise_application_error(-20299, 'Call set_log_auth need to follow
clear_log_id.');
+ end if;
+ the_user_id := user_id;
+ the_stamp := current_timestamp;
+ end set_log_auth;
+ function get_log_id return number
+ is
+ begin
+ if the_stamp is null then
+ raise_application_error(-20297, 'Call get_log_id need to follow
set_log_auth.');
+ end if;
+ if the_log_id is null then
+ insert into log (id, stamp, user_id)
+ values (log_seq.nextval, the_stamp, the_user_id)
+ returning id into the_log_id;
+ end if;
+ return the_log_id;
+ end get_log_id;
+
+procedure enable_logging(table_name_in in varchar)
+is
+ pk_column varchar(512);
+ ddl_columns varchar(4000);
+ the_insert varchar(4000);
+ already_not_null exception;
+ pragma exception_init(already_not_null, -1442);
+begin
+ select column_name into pk_column
+ from user_cons_columns
+ where constraint_name = (
+ select constraint_name
+ from user_constraints
+ where table_name = upper(table_name_in)
+ and constraint_type = 'P'
+ and owner = user
+ )
+ and owner = user ;
+ ddl_columns := '';
+ for rec in (
+ select column_name
+ from user_tab_columns
+ where table_name = upper(table_name_in)
+ and column_name not in ( pk_column, 'CREATED', 'MODIFIED' )
+ order by column_id
+ ) loop
+ ddl_columns := ddl_columns || ', ' || rec.column_name;
+ end loop;
+
+ execute immediate 'create table ' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id() as log_id,
''A'' as action' || ddl_columns
+ || ' from ' || table_name_in;
+ begin
+ execute immediate 'alter table ' || table_name_in || '_log modify
' || pk_column || ' not null';
+ exception when already_not_null then
+ null;
+ end;
+ execute immediate 'create index ' || table_name_in || '_log_idx on '
|| table_name_in || '_log(' || pk_column || ')';
+ execute immediate 'alter table ' || table_name_in || '_log modify log_id
not null';
+ execute immediate 'alter table ' || table_name_in || '_log add foreign
key (log_id) references log(id)';
+ execute immediate 'alter table ' || table_name_in || '_log modify action
not null';
+
+ the_insert := 'insert into ' || table_name_in || '_log (log_id, action,
' || pk_column || ddl_columns ||')
+ values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', :old.') || ');';
+ execute immediate 'create or replace trigger ' || table_name_in ||
'_log_trig
+ after insert or update or delete on ' || table_name_in || '
+ for each row
+ declare
+ log_id_v number;
+ tg_op char(1);
+ begin
+ log_id_v := logging.get_log_id();
+ if updating then
+ if :old.' || pk_column || ' <> :new.' || pk_column ||
' then raise_application_error(-20298, ''Cannot update column ' ||
table_name_in || '.' || pk_column || '.''); end if;
+ tg_op := ''U'';
+ end if;
+ if deleting then
+ tg_op := ''D'';
+ ' || the_insert || '
+ else
+ if inserting then tg_op := ''I''; end if;
+ ' || replace(the_insert, ':old.', ':new.') || '
+ end if;
+ end;
+ ';
+end enable_logging;
+end logging;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
new file mode 100644
index 0000000..409f761
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql
@@ -0,0 +1,194 @@
+-- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e
+--
+-- Copyright (c) 2013 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+-- create schema logging;
+
+update pg_settings set setting = 'logging,' || setting where name =
'search_path';
+
+drop function clear_log_id();
+drop function _set_log_auth(user_id in numeric, stamp in varchar);
+drop function set_log_auth(user_id in numeric);
+drop function _get_log_auth();
+drop function _get_log_stamp();
+drop function _get_log_id();
+drop function _set_log_id(log_id in numeric);
+drop function get_log_id();
+drop function enable_logging(table_name_in in varchar);
+
+create or replace function clear_log_id()
+returns void
+as
+$$
+ global the_log_id
+ set the_log_id 0
+ global the_user_id
+ set the_user_id 0
+ global the_stamp
+ set the_stamp ""
+ return
+$$ language pltclu set search_path from current;
+
+create or replace function _set_log_auth(user_id in numeric, stamp in varchar)
+returns void
+as
+$$
+ global the_user_id
+ set the_user_id $1
+ global the_stamp
+ set the_stamp $2
+$$ language pltclu set search_path from current;
+
+create or replace function set_log_auth(user_id in numeric)
+returns void
+as
+$$
+begin
+ perform _set_log_auth(user_id, current_timestamp::varchar);
+end
+$$ language plpgsql set search_path from current;
+
+create or replace function _get_log_auth()
+returns numeric
+as
+$$
+ global the_user_id
+ return $the_user_id
+$$ language pltclu set search_path from current;
+
+create or replace function _get_log_stamp()
+returns varchar
+as
+$$
+ global the_stamp
+ return $the_stamp
+$$ language pltclu set search_path from current;
+
+create or replace function _get_log_id()
+returns numeric
+as
+$$
+ global the_log_id
+ return $the_log_id
+$$ language pltclu set search_path from current;
+
+create or replace function _set_log_id(log_id in numeric)
+returns void
+as
+$$
+ global the_log_id
+ set the_log_id $1
+ return
+$$ language pltclu set search_path from current;
+
+create or replace function get_log_id()
+returns numeric
+as
+$$
+declare
+ the_log_id numeric;
+ the_user_id numeric;
+ the_stamp varchar;
+begin
+ the_log_id := _get_log_id();
+ if the_log_id is not null and the_log_id > 0 then return the_log_id ; end if;
+ the_log_id := nextval('log_seq');
+ the_user_id := _get_log_auth();
+ the_stamp := _get_log_stamp();
+ insert into public.log (id, stamp, user_id)
+ values (the_log_id,
+ case when the_stamp = '' then current_timestamp else the_stamp::timestamp
with time zone end,
+ case when the_user_id = '0' then null else the_user_id::numeric end);
+ perform _set_log_id(the_log_id);
+ return the_log_id;
+end;
+$$ language plpgsql set search_path from current;
+
+create or replace function enable_logging(table_name_in in varchar)
+returns void
+as
+$$
+declare
+ pk_column varchar;
+ ddl_columns varchar;
+ the_insert varchar;
+ rec record;
+begin
+ select constraint_column_usage.column_name into strict pk_column
+ from information_schema.table_constraints,
information_schema.constraint_column_usage
+ where constraint_column_usage.table_catalog = current_catalog
+ and constraint_column_usage.table_schema = 'public'
+ and constraint_column_usage.table_name = table_name_in
+ and constraint_column_usage.constraint_catalog = current_catalog
+ and constraint_column_usage.constraint_schema = 'public'
+ and constraint_column_usage.table_catalog = table_constraints.constraint_catalog
+ and constraint_column_usage.table_catalog = table_constraints.table_catalog
+ and constraint_column_usage.table_schema = table_constraints.constraint_schema
+ and constraint_column_usage.table_schema = table_constraints.table_schema
+ and constraint_column_usage.constraint_catalog =
table_constraints.constraint_catalog
+ and constraint_column_usage.constraint_name = table_constraints.constraint_name
+ and table_constraints.constraint_type = 'PRIMARY KEY';
+ ddl_columns := '';
+ for rec in (
+ select column_name
+ from information_schema.columns
+ where table_catalog = current_catalog
+ and table_schema = 'public'
+ and table_name = table_name_in
+ and column_name not in ( pk_column, 'created', 'modified' )
+ ) loop
+ ddl_columns := ddl_columns || ', ' || rec.column_name;
+ end loop;
+
+ execute 'create table public.' || table_name_in || '_log
+ as select ' || pk_column || ', logging.get_log_id()::numeric as log_id,
''A''::char as action' || ddl_columns
+ || ' from ' || table_name_in;
+ execute 'alter table public.' || table_name_in || '_log alter ' ||
pk_column || ' set not null';
+ execute 'create index ' || table_name_in || '_log_idx on public.' ||
table_name_in || '_log(' || pk_column || ')';
+ execute 'alter table public.' || table_name_in || '_log alter log_id set
not null';
+ execute 'alter table public.' || table_name_in || '_log add foreign key
(log_id) references log(id)';
+ execute 'alter table public.' || table_name_in || '_log alter action set
not null';
+
+ the_insert := 'insert into public.' || table_name_in || '_log (log_id,
action, ' || pk_column || ddl_columns ||')
+ values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' ||
pk_column || ddl_columns, ', ', ', old.') || ');';
+ execute 'create or replace function public.' || table_name_in ||
'_log_trig_fun() returns trigger as
+ ''
+ declare
+ log_id_v numeric;
+ begin
+ log_id_v := logging.get_log_id();
+ if tg_op = ''''UPDATE'''' then
+ if old.' || pk_column || ' <> new.' || pk_column ||
' then raise exception ''''Cannot update column ' || table_name_in
|| '.' || pk_column || '.''''; end if;
+ end if;
+ if tg_op = ''''DELETE'''' then
+ ' || the_insert || '
+ else
+ ' || replace(the_insert, 'old.', 'new.') || '
+ end if;
+ return new;
+ end;
+ ''
+ language plpgsql;';
+
+ execute 'create trigger ' || table_name_in || '_log_trig
+ after insert or update or delete on public.' || table_name_in || '
+ for each row
+ execute procedure ' || table_name_in || '_log_trig_fun()';
+
+end;
+$$
+language plpgsql;
+
+update pg_settings set setting = overlay( setting placing '' from 1 for
(length('logging')+1) ) where name = 'search_path';
commit 19766208b7c491c1e5db65a3f990d37ccef9f68c
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Tue Jul 23 11:45:54 2013 +0200
change log_id type on logged tables
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle
new file mode 100644
index 0000000..b7b00b1
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle
@@ -0,0 +1 @@
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql
new file mode 100644
index 0000000..b757a3c
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql
@@ -0,0 +1,5 @@
+-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+
+ALTER TABLE web_contact_log ALTER log_id TYPE numeric;
+ALTER TABLE rhnServer_log ALTER log_id TYPE numeric;
+ALTER TABLE rhnServerGroup_log ALTER log_id TYPE numeric;
commit f624b843fa9577edbb138b64e614544248edb7e6
Author: Tomas Lestach <tlestach(a)redhat.com>
Date: Tue Jul 23 11:25:45 2013 +0200
change log table integer -> number
diff --git a/schema/spacewalk/common/tables/log.sql
b/schema/spacewalk/common/tables/log.sql
index 13d2d20..e2b4469 100644
--- a/schema/spacewalk/common/tables/log.sql
+++ b/schema/spacewalk/common/tables/log.sql
@@ -16,9 +16,9 @@
CREATE TABLE log
(
- id INTEGER NOT NULL PRIMARY KEY,
+ id NUMBER NOT NULL PRIMARY KEY,
stamp TIMESTAMP WITH TIME ZONE,
- user_id INTEGER REFERENCES web_contact_all(id)
+ user_id NUMBER REFERENCES web_contact_all(id)
)
ENABLE ROW MOVEMENT
;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle
new file mode 100644
index 0000000..a179a5c
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle
@@ -0,0 +1,4 @@
+ALTER TABLE log MODIFY (
+ id number,
+ user_id number
+);
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
new file mode 100644
index 0000000..3c5f689
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql
@@ -0,0 +1,4 @@
+-- oracle equivalent source sha1 db59999d5662a6f91b00e119e676577fc054e02e
+
+ALTER TABLE log ALTER id TYPE numeric;
+ALTER TABLE log ALTER user_id TYPE numeric;