mirror of
https://github.com/FirebirdSQL/firebird-qa.git
synced 2025-01-22 13:33:07 +01:00
2 lines
360 KiB
MySQL
2 lines
360 KiB
MySQL
|
set bail on; set autoddl off; set list on; select 'oltp30_DDL.sql start' as msg, current_timestamp from rdb$database; set list off; commit; set term ^; execute block as begin begin execute statement 'recreate exception ex_exclusive_required ''At least one concurrent connection detected.'''; when any do begin end end begin execute statement 'recreate exception ex_not_suitable_fb_version ''This script requires at least Firebird 3.x version'''; when any do begin end end end ^ set term ;^ commit; set term ^; execute block as begin if ( rdb$get_context('SYSTEM','ENGINE_VERSION') starting with '2.' ) then begin exception ex_not_suitable_fb_version; end if ( exists( select * from mon$attachments a where a.mon$attachment_id<>current_connection and a.mon$remote_protocol is not null ) ) then begin exception ex_exclusive_required; end end ^ set term ;^ commit; set term ^; execute block as declare stt varchar(8190); declare ref_name varchar(31); declare tab_name varchar(31); declare view_ddl varchar(8190); declare c_view cursor for ( with a as( select rf.rdb$relation_name view_name, rf.rdb$field_position fld_pos, trim(rf.rdb$field_name) fld_name from rdb$relation_fields rf join rdb$relations rr on rf.rdb$relation_name=rr.rdb$relation_name where coalesce(rf.rdb$system_flag,0)=0 and coalesce(rr.rdb$system_flag,0)=0 and rr.rdb$relation_type=1 ) select view_name, cast( 'create or alter view '||trim(view_name)||' as select ' ||list( fld_pos||' '||trim(lower( fld_name )) ) ||' from rdb$database' as varchar(8190) ) view_ddl from a group by view_name ); begin open c_view; while (1=1) do begin fetch c_view into tab_name, stt; if (row_count = 0) then leave; execute statement (:stt); end close c_view; end^ set term ;^ commit; set term ^; execute block as declare stt varchar(512); declare ref_name varchar(31); declare tab_name varchar(31); declare c_trig cursor for (select rt.rdb$trigger_name from rdb$triggers rt where coalesce(rt.rdb$system_flag,0)=0 ); declare c_view cursor for (select rr.rdb$relation_name from rdb$relations rr where rr.rdb$relation_type=1 and coalesce(rr.rdb$system_flag,0)=0 ); declare c_func cursor for (select rf.rdb$function_name from rdb$functions rf where coalesce(rf.rdb$system_flag,0)=0 ); declare c_proc cursor for (select rp.rdb$procedure_name from rdb$procedures rp where coalesce(rp.rdb$system_flag,0)=0 ); declare c_excp cursor for (select re.rdb$exception_name from rdb$exceptions re where coalesce(re.rdb$system_flag,0)=0 ); declare c_fk cursor for (select rc.rdb$constraint_name, rc.rdb$relation_name from rdb$relation_constraints rc where rc.rdb$constraint_type ='FOREIGN KEY' ); declare c_tabs cursor for (select rr.rdb$relation_name from rdb$relations rr where rr.rdb$relation_type in(0,4,5) and coalesce(rr.rdb$system_flag,0)=0 ); declare c_doms cursor for (select rf.rdb$field_name from rdb$fields rf where coalesce(rf.rdb$system_flag,0)=0 and rf.rdb$field_name not starting with 'RDB$' ); declare c_coll cursor for (select rc.rdb$collation_name from rdb$collations rc where coalesce(rc.rdb$system_flag,0)=0 ); declare c_gens cursor for (select rg.rdb$generator_name from rdb$generators rg where coalesce(rg.rdb$system_flag,0)=0 ); declare c_role cursor for (select rr.rdb$role_name from rdb$roles rr where coalesce(rr.rdb$system_flag,0)=0 ); begin open c_trig; while (1=1) do begin fetch c_trig into stt; if (row_count = 0) then leave; stt = 'drop trigger '||stt; execute statement (:stt); end close c_trig; open c_func; while (1=1) do begin fetch c_func into stt; if (row_count = 0) then leave; stt = 'create or alter function '||stt||' returns int as begin return 1; end'; execute statement (:stt); end close c_func; open c_proc; while (1=1) do begin fetch c_proc into stt; if (row_count = 0) then leave; stt = 'create or alter procedure '||stt||' as begin end'; execute statement (:stt); end close c_proc; open c_view; while (1=1) do begin fetch c_view into stt; if (row_count = 0) then leave; stt = 'drop view '||stt; execute statement (:stt); end close c_view; open c_func; while (1=1) do begin fetch c_func into stt; if (row_count =
|