java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportIJ.sql

--
--   Licensed to the Apache Software Foundation (ASF) under one or more
--   contributor license agreements.  See the NOTICE file distributed with
--   this work for additional information regarding copyright ownership.
--   The ASF licenses this file to You under the Apache License, Version 2.0
--   (the "License"); you may not use this file except in compliance with
--   the License.  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
--
-- test for import export thru command line interface

-- first test basic import functionality
-- ascii delimited default format
drop table T1;
create table T1 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			Address2 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Country char(10),
			Phone1  char(20),
			Phone2  char(20),
			email   char(30),
			web     char(30),
			Fname	char(30),
			Lname	char(30),
			Comment	char(30),
			AccDate	char(30),
			Payment	decimal(8,2),
			Balance decimal(8,2));

create index T1_IndexBalance on T1 (Balance, Account, Company);
create index T1_IndexFname on T1 (Fname, Account);
create index T1_IndexLname on T1 (Lname, Account);

-- second line of data file is not complete - should give error message re EOF
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/TwoLineBadEOF.dat' , 
                                    null, null, null, 0) ;

-- should work, default format (i.e. field sep , column delimiter ".)
-- (last two lines have extra white space (tabs, spaces) which should not matter).
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T1' , 'extin/AccountData_defaultformat.dat' , 
                                    null, null, null, 0) ;

values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T1'));

-- Delimited with a different char but using default import format
drop table T2;

autocommit off;
create table T2 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			Address2 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Country char(10),
			Phone1  char(20),
			Phone2  char(20),
			email   char(30),
			web     char(30),
			Fname	char(30),
			Lname	char(30),
			Comment	char(30),
			AccDate	char(30),
			Payment	decimal(8,2),
			Balance decimal(8,2));

create index T2_IndexBalance on T2 (Balance, Account, Company);
create index T2_IndexFname on T2 (Fname, Account);
create index T2_IndexLname on T2 (Lname, Account);
commit;
--this one should fail as we're still using the default import format
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T2' , 'extin/AccountData_format1.dat' , 
                                    null, null, null, 0) ;

values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T2'));
-- so following should only commit an empty table
commit;

-- But if we use correct specification?
call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'T2' , 'extin/AccountData_format1.dat' , 
				   'q', '"', 'ASCII', 0) ;

values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T2'));
select count(*) from T2;
rollback;

-- test remapping
drop table T3;
create table T3 (	Lname	char(30),
			Fname	char(30),
			Account	int not null primary key,
			Company varchar(35),
			Payment	decimal(8,2),
			Balance decimal(8,2));

create index T3_indexBalance on T3 (Balance, Company, Account);
create index T3_indexPayment on T3 (Payment, Company, Account);

--incorrect mapping of file to table ; should give error and rollback
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T3' , 
                                  null , '16, 15, 1, 4, 19, 200' ,
				  'extin/AccountData_defaultformat.dat' , 
				  null, null, null, 0) ;
select count(*) from T3;

drop table T4;
create table T4 (	Lname	char(30),
			Fname	char(30),
			Account	int not null primary key,
			Company varchar(35),
			Payment	decimal(8,2),
			Balance decimal(8,2));

create index T4_indexBalance on T4 (Balance, Company, Account);
create index T4_indexPayment on T4 (Payment, Company, Account);
-- correctly remapped
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'T4' ,  
                                  null , '16, 15, 1, 4, 19, 20' ,
				  'extin/AccountData_defaultformat.dat' ,
				   null, null, null, 0) ;
values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T4'));
commit;

-- now check results
select count(*) from T1;
select count(*) from T2;
select count(*) from T3;
select count(*) from T4;

select * from T1 where State = 'CA';
select * from T2 where State = 'CA';
select * from T4 where Fname = 'Georgiana' or Fname = 'Michael';

select Balance, Account, Company from T1 order by Balance;
select Balance, Account, Company from T2 order by Balance;
select Balance, Account, Company from T4 order by Balance;

--- now check other input formats
-- this is sample data with RecordSeperator=',', FieldStartDelimiter=(, FieldEndDelimiter=),FieldSeperator=TAB
-- which the import can't handle (not the RecordSeparator, and there can be only one
-- fieldDelimitor character). 
-- The error XIE0R and 22018 are returned if the 1 line file is largish  
autocommit on;
drop table Alt1;
create table Alt1 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Payment	decimal(8,2),
			Balance decimal(8,2));

call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT1' , 'extin/UnsupportedFormat1.dat' , 
                                      null, null, null, 0) ;

select count(*) from Alt1;

-- But error 38000 and 42X04 are returned if the 1 line file is smaller  
drop table Alt2;
create table Alt2 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Payment	decimal(8,2),
			Balance decimal(8,2));

call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT2' , 'extin/UnsupportedFormat2.dat' , 
                                      null, null, null, 0) ;

select count(*) from Alt2;

-- this is sample data with some null (missing) fields

drop table Alt3;
create table Alt3 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			Address2 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Country char(10),
			Phone1  char(20),
			Phone2  char(20),
			email   char(30),
			web     char(30),
			Fname	char(30),
			Lname	char(30),
			Comment	char(30),
			AccDate	char(30),
			Payment	decimal(8,2),
			Balance decimal(8,2));


call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT3' , 'extin/AccountData_NullFields.dat' , 
                                      null, null, null, 0) ;

select count(*) from Alt3;

--test more remapping - size matters not
create table Alt4(column1 varchar(1000) , column3 varchar(1000) , column9 varchar(1000));

call SYSCS_UTIL.SYSCS_IMPORT_DATA (null, 'ALT4' , 
   				   null , '2,3,9',
				   'extin/AccountData_defaultformat.dat' , 
				   null, null, null, 0) ;
select * from Alt4;

--- Format with | as column separator and '' as delimiter. Also remapping
drop table Alt5;
create table Alt5 (
	Id	int,
	Name	varchar(40),
	Title	varchar(40),
	Company	varchar(50),
	Address	varchar(80),
	City	varchar(30),
	State	varchar(30),
	Zip	varchar(30),
	Country varchar(30),
	phone1	varchar(50),
	phone2	varchar(30),
	email	varchar(80),
	web	varchar(50));

call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'ALT5' ,
                                  null , '1,2,3,4,5,7,8,9,10,11,12,13,14', 
	 		          'extin/AccountData_format2.dat' , 
				  '|', '''', 'ASCII', 0) ;

	
select Company, Country from Alt5 where country not like 'U%S%A%' and country is not null;

rollback;

-- test remapping out of sequence
drop table Alt6;
create table Alt6 (
	Fname varchar(30),
	Lname varchar(30),
	email varchar(40),
	phone varchar(30));

call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'ALT6' , 
		                  null , '15, 16, 13, 11',
			          'extin/AccountData_format2.dat' , 
				  '|', '''', 'ASCII', 0) ;
select count(*) from Alt6;

rollback;

-- import fails if data has delimiter character within field. 
drop table Alt7;
create table Alt7 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			Address2 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Country char(10),
			Phone1  char(20),
			Phone2  char(20),
			email   char(30),
			web     char(30),
			Fname	char(30),
			Lname	char(30),
			Comment	char(30),
			AccDate	char(30),
			Payment	decimal(8,2),
			Balance decimal(8,2));

call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT7' ,
			          'extin/AccountData_format2oops.dat' , 
				  '|', '''', 'ASCII', 0) ;

select count(*) from Alt8;
rollback;

-- if there's no end of record (CR/LF) after 1st record, rest gets ignored
drop table Alt8;
create table Alt8 (	Account	int,
			Name    char(30),
			Jobdesc char(40),
			Company varchar(35),
			Address1 varchar(40),
			Address2 varchar(40),
			City	varchar(20),
			State	char(5),
			Zip	char(10),
			Country char(10),
			Phone1  char(20),
			Phone2  char(20),
			email   char(30),
			web     char(30),
			Fname	char(30),
			Lname	char(30),
			Comment	char(30),
			AccDate	char(30),
			Payment	decimal(8,2),
			Balance decimal(8,2));

call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ALT8' , 'extin/NoEOR.dat' , 
                                      null, null, null, 0) ;

select count(*) from Alt8;
rollback;

autocommit off;

-- import fails if the table has more columns than named in import statement
drop table HouseHoldItem;
create table HouseHoldItem(
	Category	int,
	RoomId		int,
	Description	varchar(255),
	Model		varchar(50),
	ModelId		varchar(50),
	SerialNumber	varchar(50),
	DayPurchase	date,
	PurchasePrice	decimal(8,2),
	Insured		decimal(8,2),
	Note		varchar(512));
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HOUSEHOLDITEM' , 
				  null , '2,3,4,5,6,7,8,11,12', 
				  'extin/Access1.txt' , 
				  null, null, null, 0) ;
select * from HouseHoldItem;
rollback;

-- import fails if datatype & format of data are not compatible
create table HouseHoldItem(
	Category	int,
	RoomId		int,
	Description	varchar(255),
	Model		varchar(50),
	ModelId		varchar(50),
	SerialNumber	varchar(50),
	DayPurchase	date,
	PurchasePrice	decimal(8,2),
	Insured		smallint,
	Note		varchar(512));
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HOUSEHOLDITEM' , 
				  null , '2,3,4,5,6,7,8,11,12,10', 
				  'extin/Access1.txt' , 
				  null, null, null, 0) ;
select * from HouseHoldItem;
rollback;

-- import will succeed with default format settings whether int, date, 
-- or time fields have quotes in the import file or not
create table HouseHoldItem(
	Category	int,
	RoomId		int,
	Description	varchar(255),
	Model		varchar(50),
	ModelId		varchar(50),
	SerialNumber	varchar(50),
	DayPurchase	date,
	PurchasePrice	decimal(8,2),
	Insured		decimal(8,2),
	Note		varchar(512));
call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'HOUSEHOLDITEM' , 
				  null , '2,3,4,5,6,7,8,11,12,10', 
				  'extin/Access1.txt' , 
				  null, null, null, 0) ;
select * from HouseHoldItem;
rollback;


----
---- test export 
----

call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'T1' , 'extinout/t1.dump' , 
                                    '|','''', 'ASCII') ;

create table  imp_temp(column2 varchar(200), 
                  column3 varchar(200), 
                  column4 varchar(200), 
                  column5 varchar(200),
                  column6 varchar(200));

call SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'IMP_TEMP' ,null, '2, 3, 4, 5, 6',
                                    'extinout/t1.dump', '|', '''', 'ASCII', 0) ;

select * from imp_temp ;
drop table imp_temp;

-- test case for derby-1854/derby-1641
-- perform import into a table that has same column 
-- as a primary key and a foreign key (ADMINS table).  

create table users (
 user_id int not null generated by default as identity,
 user_login varchar(255) not null,
 primary key (user_id));

create table admins (
 user_id int not null,
 primary key (user_id),
 constraint admin_uid_fk foreign key (user_id) references users (user_id));
 
insert into users (user_login) values('test1');
insert into users (user_login) values('test2');

call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select user_id from users' , 
                    'extinout/users_id.dat', null , null , null ) ;
call syscs_util.syscs_import_table( null, 'ADMINS', 
                    'extinout/users_id.dat', null, null, null,1); 
select * from admins; 
select * from users;
-- do consistency check on the table.
values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
drop table admins;
drop table users;
-- end derby-1854/derby-1641 test case. 

--
-- begin test case for derby-2193:
--
-- Field comprised of all blank space should become a null
--

create table derby_2193_tab
(
    a  varchar( 50 ),
    b  varchar( 50 )
);

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE 
( null, 'DERBY_2193_TAB', 'extin/derby-2193.txt', null, null, null, 0 );
select * from derby_2193_tab;
select b, length(b) from derby_2193_tab;

--
-- Errors should contain identifying line numbers
--
create table derby_2193_lineNumber
(
    a  int,
    b  int
);

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE 
( null, 'DERBY_2193_LINENUMBER', 'extin/derby-2193-linenumber.txt', null, null, null, 0 );
select * from derby_2193_lineNumber;

--
-- end test case for derby-2193:
--

--
-- begin test case for derby-2925:
--
-- Prevent export from overwriting existing files 
--

create table derby_2925_tab
(
    a  varchar( 50 ),
    b  varchar( 50 )
);

--
-- Testing SYSCS_UTIL.SYSCS_EXPORT_TABLE
--

CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE
( null, 'DERBY_2925_TAB', 'extout/derby-2925.txt', null, null, null);
--
-- Errors should should happen in the second
-- call to SYSCS_UTIL.SYSCS_EXPORT_TABLE
-- since extout/derby-2925.txt already exists. 
--
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE
( null, 'DERBY_2925_TAB', 'extout/derby-2925.txt', null, null, null);

--
-- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY
--

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY
('select * from DERBY_2925_TAB', 'extout/derby-2925-query.dat', null , null , null ) ;
--
-- Errors should should happen in the second
-- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY
-- since extout/derby-2925-query.dat already exists.
--
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY
('select * from DERBY_2925_TAB', 'extout/derby-2925-query.dat', null , null , null ) ;

--
-- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
--

create table derby_2925_lob
(
	id 	int,
        name 	varchar(30),
        content clob, 
        pic 	blob 
);

--
-- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
-- where data file exists.
--

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data.dat', '\' ,'|','UTF-16','extout/derby-2925_lobs.dat');
--
-- Errors should should happen in the second
-- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
-- since extout/derby-2925_data.dat already exists.
--
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data.dat', '\' ,'|','UTF-16','extout/derby-2925_lobs.dat');

--
-- Testing SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
-- where lob file exists.
--
-- Errors should should happen in the 
-- call to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
-- since extout/derby-2925_lobs.dat already exists.
--

CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
('SELECT * FROM DERBY_2925_LOB','extout/derby-2925_data1.dat', '\' ,'|','UTF-16','extout/derby-2925_lobs.dat');

--
-- end test case for derby-2925: