#!./pgbash
source ./Test.conf
source ./pgbashrc
#---------------------------------------------------------------
#   sql.bsh
#---------------------------------------------------------------
#
connect to template1 user $USER $PASSWD;

set option_quiet=ON;
drop database $DB1;
drop database $DB2;
set option_quiet=OFF;

create database $DB1;
create database $DB2;
disconnect template1;
#
echo "****************** command test ******************" 
echo " " 
#
set OPTION_QUIET;
connect to $DB1 as db1 user $USER $PASSWD;
connect to $DB2 as db2 user $USER $PASSWD;
set OPTION_QUIET=OFF;

exec_sql -m
#
#   db1 Database
#
set connection db1;

set option_quiet;
drop table $TABLE1;
drop sequence pgbash_seq_table;
set option quiet=f;

create table $TABLE1( 
   code		int, 		# User Code
   name		varchar(32), 	# User Name
   address	varchar(64) 	# User's Address
);

create index $INDEX1 on $TABLE1(code);
create view $VIEW1 as select * from $TABLE1;
create sequence $SEQ1;
grant all privileges on $TABLE1 to public;

#
#   db2 Database
#
set connection db2;

set option_quiet;
drop table $TABLE2;
drop index &INDEX2;
set option_quiet=Flase;

create table $TABLE2( 
   code		int, 
   name		varchar(32), 
   address	varchar(64) 
);

create index $INDEX2 on $TABLE2(code);

#
#  display database information
#
echo " "
set OPTION_FRAME=ON;
#
##_list_usernames
##_list_databases

set connection db1;
_list_tables
_list_indexes
_list_views
_list_sequences
_list_permissions

set connection db2;
_list_tables
_list_indexes
#
#
set connection db1;
_list_relations
_list_relation $TABLE1
_list_relation $INDEX1
_list_relation $VIEW1
_list_relation $SEQ1
#
set connection db2;
_list_relations
_list_relation $TABLE2
_list_relation $INDEX2
#
# large object
#
_lo_import ShellScript/command.bsh 'large data 1'
_lo_import ShellScript/connect.bsh 'large data 2'
_lo_import ShellScript/sql.bsh     'large data 3'
begin;
declare cur cursor for select description 
	from pgbash_description order by objoid;
while (( 1 )); do
   fetch in cur into :description;
   if (( SQLCODE != SQL_OK )); then
      break
   fi
   echo "$description"
done
end;
#
select objoid into :objoid 
	from pgbash_description 
	where description='large data 3';
rm -f "Results/large_data_3"
_lo_export $objoid "Results/large_data_3"
_lo_unlink $objoid
#
select objoid into :objoid 
	from pgbash_description 
	where description='large data 1';
rm -f "Results/large_data_1"
_lo_export $objoid "Results/large_data_1"
_lo_unlink $objoid
#
select objoid into :objoid 
	from pgbash_description 
	where description='large data 2';
rm -f "Results/large_data_12"
_lo_export $objoid "Results/large_data_2"
_lo_unlink $objoid
#
disconnect all;
#
#*****************************************
connect to template1 user $USER $PASSWD;
drop database $DB1;
drop database $DB2;
disconnect all;
#