-
Notifications
You must be signed in to change notification settings - Fork 2
/
02_db_inmobiliaria_DDL_LOGS.sql
148 lines (90 loc) · 3.84 KB
/
02_db_inmobiliaria_DDL_LOGS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
/* ----------------------------
* ------ INMOBILIARIA---------
* ----------------------------
*
*
* ========= DDL LOGS FUNCTIONS =============
*/
drop table if exists logs_inserts cascade;
drop table if exists logs_updates cascade;
drop table if exists logs_deletes cascade;
-- Todos lo id PK auto_increment
drop sequence if exists id_sec_logs_ins cascade;
drop sequence if exists id_sec_logs_upd cascade;
drop sequence if exists id_sec_logs_del cascade;
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- Módulo de PostgreSql para uuid
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- ======= TABLA LOGS INSERTS ===========
create table logs_inserts(
id int primary key,
id_registro int not null,
uuid_registro uuid default uuid_generate_v4 (),-- 32 digitos hex
nombre_tabla varchar(30) not null,
accion varchar(30) not null,
fecha date default current_date,
hora time default current_time,
usuario varchar(50) default current_user, -- lo mismo que current_role
usuario_sesion varchar(50) default session_user,
db varchar(50) default current_catalog,
db_version varchar(100) default version()
);
-- ======= Restricciones Tabla logs_inserts ===========
-- UNIQUE ID
alter table logs_inserts
add constraint UNIQUE_logs_inserts_id
unique(id);
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- ======= TABLA LOGS UPDATES ===========
create table logs_updates(
id int primary key,
id_registro int not null,
uuid_registro uuid default uuid_generate_v4 (),-- 32 digitos hex
nombre_tabla varchar(30) not null,
campo_tabla varchar(50) not null,
accion varchar(30) not null,
fecha date default current_date,
hora time default current_time,
usuario varchar(50) default current_user, -- lo mismo que current_role
usuario_sesion varchar(50) default session_user,
db varchar(50) default current_catalog,
db_version varchar(100) default version()
);
-- ======= Restricciones Tabla logs_updates ===========
-- UNIQUE ID
alter table logs_updates
add constraint UNIQUE_logs_updates_id
unique(id);
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- ======= TABLA LOGS DELETES ===========
create table logs_deletes(
id int primary key,
id_registro int not null,
uuid_registro uuid default uuid_generate_v4 (),-- 32 digitos hex
nombre_tabla varchar(30) not null,
campo_tabla varchar(50) not null,
accion varchar(30) not null,
fecha date default current_date,
hora time default current_time,
usuario varchar(50) default current_user, -- lo mismo que current_role
usuario_sesion varchar(50) default session_user,
db varchar(50) default current_catalog,
db_version varchar(100) default version()
);
-- ======= Restricciones Tabla logs_deletes ===========
-- UNIQUE ID
alter table logs_deletes
add constraint UNIQUE_logs_deletes_id
unique(id);
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- ======== TODOS LOS ID´S PK DE LAS TABLAS COMO AUTO_INCREMENT =======
create sequence id_sec_logs_ins;
create sequence id_sec_logs_upd;
create sequence id_sec_logs_del;
alter table logs_inserts alter id set default nextval('id_sec_logs_ins');
alter table logs_updates alter id set default nextval('id_sec_logs_upd');
alter table logs_deletes alter id set default nextval('id_sec_logs_del');