Created
January 13, 2016 20:35
-
-
Save DamianReeves/3cf07240a3c69c3c1f68 to your computer and use it in GitHub Desktop.
Even Oracle schema template
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
declare | |
table_count integer; | |
begin | |
select count (object_id) into table_count from user_objects where exists ( | |
select object_name from user_objects where (object_name = upper('{0}') and object_type = 'TABLE')); | |
if table_count = 0 then | |
dbms_output.put_line('Creating the {0} table'); | |
execute immediate ( | |
'create table {0} ( | |
GlobalSequence number not null, | |
EventID varchar2(36) not null, | |
StreamHash raw(20) not null, | |
StreamName varchar2(200) not null, | |
EventType varchar2(50) not null, | |
UtcTimeStamp timestamp not null, | |
Metadata blob, | |
Payload blob not null, | |
PayloadFormat number not null, | |
constraint PK_{0} primary key (GlobalSequence))'); | |
execute immediate ('create index IX_{0}_ on {0} (StreamHash)'); | |
execute immediate ('create sequence {0}_GlobSeq'); | |
execute immediate (' | |
create or replace trigger {0}_GlobSeq_Trig | |
before insert on {0} | |
for each row | |
begin | |
select {0}_GlobSeq.nextval | |
into :new.GlobalSequence | |
from dual; | |
end;'); | |
else | |
dbms_output.put_line ('The {0} table already exists in the database.'); | |
end if; | |
select count (object_id) into table_count from user_objects where exists ( | |
select object_name from user_objects where (object_name = upper('{1}') and object_type = 'TABLE')); | |
if table_count = 0 then | |
dbms_output.put_line('Creating the {1} table'); | |
execute immediate ( | |
'create table {1} ( | |
ProjectionStreamHash raw(20) not null, | |
ProjectionStreamSequence number(19) not null, | |
GlobalSequence number(19) not null, | |
constraint PK_{1} primary key (ProjectionStreamHash, ProjectionStreamSequence) | |
)'); | |
else | |
dbms_output.put_line ('The {1} table already exists in the database.'); | |
end if; | |
select count (object_id) into table_count from user_objects where exists ( | |
select object_name from user_objects where (object_name = upper('{2}') and object_type = 'TABLE')); | |
if table_count = 0 then | |
dbms_output.put_line('Creating the {2} table'); | |
execute immediate ( | |
'create table {2} ( | |
ProjectionStreamHash raw(20) not null primary key, | |
LastGlobalSequence number(19) not null | |
)'); | |
else | |
dbms_output.put_line ('The {2} table already exists in the database.'); | |
end if; | |
exception when others then dbms_output.put_line('An unexpected exception has occured. Please re-evaluate the PL/SQL script'); | |
end; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment