@echo off
rem **************************************************************************/
rem *                                                                        */
rem *  Licensed Materials - Property of IBM                                  */
rem *                                                                        */
rem *  "Restricted Materials of IBM"                                         */
rem *                                                                        */
rem *  IBM Informix Dynamic Server                                           */
rem *  Copyright IBM Corporation 2007, 2014                                  */
rem *                                                                        */
rem **************************************************************************/

if %1test==/?test goto usage
if %1test==--test goto usage
if %1test==-?test goto usage
if %1test==-helptest goto usage

if "%INFORMIXDIR%notset" == "notset" goto error_infxdir
if "%INFORMIXSERVER%notset" == "notset" goto error_infxserver

if not exist %INFORMIXDIR%\demo\dbaccess\demo_ids goto error_demodir

set CLIENT_LOCALE_SAV=%CLIENT_LOCALE%

set GL_DATE_SAV=%GL_DATE%
set DBMONEY_SAV=%DBMONEY%
set CREATE_SAV=%CREATE%
set DB_SAV=%DB%
set IN_SAV=%IN%
set NOTS=0
set DBSPACE_SAV=%DBSPACE%
set WLOG_SAV=%WLOG%

set GL_DATE=%%m/%%d/%%Y
set DBMONEY=$.
set CREATE=CREATE DATABASE
set CREATECONTAINER=
set DB=stores_demo
set IN=
set DBSPACE=
set WLOG=

REM evaluate command line arguments
if %1test==test goto startdb
if %1test==-notstest goto nots1
if %1test==-logtest goto log1
if %1test==-dbspacetest goto dbspace1
set DB=%1
goto test2

:nots1
set nots=1
goto test2

:log1
set WLOG=WITH LOG
goto test2

:dbspace1
set IN=in
set DBSPACE=%2

:test2
if %2test==test goto startdb
if %2test==-notstest goto nots2
if %2test==-logtest goto log2
if %2test==-dbspacetest goto dbspace2
if NOT %1test==-dbspacetest SET DB=%2
goto test3

:nots2
set nots=1
goto test3

:log2
set WLOG=WITH LOG
goto test3

:dbspace2
set IN=in
set DBSPACE=%3

:test3
if %3test==test goto startdb
if %3test==-notstest goto nots3
if %3test==-logtest goto log3
if %3test==-dbspacetest goto dbspace3
if NOT %2test==-dbspacetest SET DB=%3
goto test4

:nots3
set nots=1
goto test4

:log3
set WLOG=WITH LOG
goto test4

:dbspace3
set IN=in
set DBSPACE=%4

:test4
if %4test==test goto startdb
if %4test==-notstest goto nots4
if %4test==-logtest goto log4
if %4test==-dbspacetest goto dbspace4
if NOT %3test==-dbspacetest SET DB=%4
goto test5

:nots4
set nots=1
goto test5

:log4
set WLOG=WITH LOG
goto test5

:dbspace4
set IN=in
set DBSPACE=%5

:test5
if %5test==test goto startdb
if %5test==-notstest goto nots5
if %5test==-logtest goto log5
if NOT %4test==-dbspacetest SET DB=%5
goto startdb:

:nots5
set nots=1
goto startdb

:log5
set WLOG=WITH LOG

:startdb

REM cd %INFORMIXDIR%\demo\dbaccess\demo_ids

call %INFORMIXDIR%\%INFORMIXSERVER%.cmd
if /i "%DB_LOCALE%notset" == "notset" set DB_LOCALE=EN_US.8859-1
if /i "%DB_LOCALE%" == "EN_US.C" set DB_LOCALE=EN_US.8859-1
if /i "%DB_LOCALE%" == "EN_US.POSIX" set DB_LOCALE=EN_US.8859-1
if /i "%DB_LOCALE%" == "EN_US.Latin-1" set DB_LOCALE=EN_US.8859-1
if /i "%DB_LOCALE%" == "EN_US.819" set DB_LOCALE=EN_US.8859-1
if /i not "%DB_LOCALE%" == "EN_US.8859-1" echo Setting DB_LOCALE to EN_US.8859-1
set DB_LOCALE=EN_US.8859-1
set CLIENT_LOCALE=EN_US.CP1252
pushd %INFORMIXDIR%\demo\dbaccess\demo_ids

REM drop the existing %DB% database
echo Dropping existing %DB% database...
echo DROP DATABASE %DB% | dbaccess -

if /i "%nots%" == "0" (
  if /i "%WLOG%test" == "test" set WLOG=WITH BUFFERED LOG)

REM  create the database
echo Creating %DB% database...
echo %CREATE% %DB% %IN% %DBSPACE% %WLOG% | dbaccess -                   
if not errorlevel 0 goto error_cdb

REM create external table
echo preparing external table command...
echo create external table ext_customer sameas  customer > %INFORMIXDIR%\demo\dbaccess\demo_ids\c_ext_table.sql
echo    using (datafiles ('disk:%INFORMIXDIR%\demo\dbaccess\demo_ids\ext_customer.unl'), >> %INFORMIXDIR%\demo\dbaccess\demo_ids\c_ext_table.sql
echo    numrows 3 >> %INFORMIXDIR%\demo\dbaccess\demo_ids\c_ext_table.sql
echo    ); >> %INFORMIXDIR%\demo\dbaccess\demo_ids\c_ext_table.sql

REM create the tables
echo Creating tables...
dbaccess %DB% %INFORMIXDIR%\demo\dbaccess\demo_ids\c_tables.sql                 
if not errorlevel 0 goto error_ctables
if %nots% == 0 (
dbaccess %DB% %INFORMIXDIR%\demo\dbaccess\demo_ids\c_meter.sql                 
if not errorlevel 0 goto error_ctables)

REM Create the container and virtual tables
if /i "%nots%" == "1" goto continue1
if /i %DBSPACE%test == test set DBSPACE=rootdbs

set CREATECONTAINER= '%DBSPACE%', 'meter_data', 100, 50^)
echo ^
EXECUTE PROCEDURE TSContainerCreate('raw_container', %CREATECONTAINER% ; ^
EXECUTE PROCEDURE TSContainerCreate('mult_container', %CREATECONTAINER% ; ^
EXECUTE PROCEDURE TSCreateVirtualTab('ts_data_v', 'ts_data', ^
'origin(2010-11-10 00:00:00.00000),calendar(cal15min),container(raw_container),threshold(0),regular', 0, 'raw_reads'); ^
EXECUTE PROCEDURE TSCreateVirtualTab('ts_data_multiplier_v', 'ts_data', ^
'origin(2010-11-10 00:00:00.00000),calendar(cal15min),container(mult_container),threshold(0),irregular', 0, 'multiplier'); ^
 | dbaccess %DB%
:continue1

REM create external table
echo Creating external table...
dbaccess %DB% %INFORMIXDIR%\demo\dbaccess\demo_ids\c_ext_table.sql                 
if not errorlevel 0 goto error_ctables

REM load data
echo Loading data...
dbaccess %DB% %INFORMIXDIR%\demo\dbaccess\demo_ids\c_load.sql                   
if not errorlevel 0 goto error_load
if %nots% == 0 echo ^
CREATE TABLE ts_data_location ( ^
	loc_esi_id	char(20) NOT NULL PRIMARY KEY, ^
	longlat		st_point ^
) LOCK MODE ROW; ^
LOAD FROM '%INFORMIXDIR%\demo\dbaccess\demo_ids\ts_data.unl' ^
     INSERT INTO ts_data; ^
LOAD FROM '%INFORMIXDIR%\demo\dbaccess\demo_ids\ts_data_location.unl' ^
     INSERT INTO ts_data_location; ^
LOAD FROM '%INFORMIXDIR%\demo\dbaccess\demo_ids\customer_ts_data.unl' ^
     INSERT INTO customer_ts_data; ^
CREATE INDEX ts_data_location_spix ^
	ON ts_data_location(longlat ST_Geometry_ops) USING RTREE; ^
 | dbaccess %DB% 
if not errorlevel 0 goto error_load
rem Final data to load..
rem
echo set lock mode to wait; > %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo update statistics; >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo grant resource to public; >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo execute procedure ifx_allow_newline('t'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo CREATE TABLE tab (col2 lvarchar); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo INSERT INTO tab VALUES ('^<personnel^>^<person id="Jason.Ma"^>^<name^>^<family^>Ma^</family^>^<given^>Jason^</given^>^</name^>^</person^>^</personnel^>'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo CREATE TABLE warehouses ( >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   warehouse_name  lvarchar, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   warehouse_id  int, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   warehouse_spec lvarchar >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo INSERT INTO warehouses values('Liverpool, England', 1, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo '^<?xml version="1.0" encoding="iso-8859-1"?^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<!-- @version: --^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<Warehouse^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   ^<Docks^>2^</Docks^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^</Warehouse^>' >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo INSERT INTO warehouses values('Taipei, Taiwan', 2, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo '^<?xml version="1.0" encoding="iso-8859-1"?^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<!-- @version: --^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<Warehouse^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   ^<Docks^>1^</Docks^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^</Warehouse^>' >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo INSERT INTO warehouses values('Buenos Aires, Argentina', 3, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo '^<?xml version="1.0" encoding="iso-8859-1"?^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<!-- @version: --^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<Warehouse^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   ^<Docks^>4^</Docks^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^</Warehouse^>' >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo INSERT INTO warehouses values('Seattle, USA', 4, >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo '^<?xml version="1.0" encoding="iso-8859-1"?^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<!-- @version: --^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^<Warehouse^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo   ^<Docks^>3^</Docks^> >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ^</Warehouse^>' >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo ); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo create table classes(classid int, class int, subject char(32)); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo insert into classes values(1, 125, 'Chemistry'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo insert into classes values(2, 250, 'Physics'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo insert into classes values(3, 375, 'Mathematics'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo insert into classes values(4, 500, 'Biology'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo create ROW TYPE address_t (address1 char(64), city char(32), state char(32), zipcode int); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo create table employee(givenname char(32), familyname char(32), address address_t, phone char(32)); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo insert into employee values('Roy', 'Conner', ROW('123, First street', 'Denver', 'CO', 80111)::address_t, '303-555-1212'); >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo update statistics; >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo. >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
echo grant resource to public; >> %TEMP%\c_tabxml_%INFORMIXSERVER%.sql
dbaccess %DB% %TEMP%\c_tabxml_%INFORMIXSERVER%.sql

if not errorlevel 0 goto error_load
goto SUCCESS

:usage
echo "Usage:  dbaccessdemo [-nots] [-log] [-dbspace <dbspace name>] [<database name>]"
goto end

:error_infxdir
echo INFORMIXDIR environment variable not set.
goto end

:error_infxserver
echo INFORMIXSERVER environment variable not set
goto end

:error_cdb
echo Database creation failed.
goto end

:error_ctables
echo Tables creation failed.
goto end

:error_load
echo Load data failed.
goto end 

:error_demodir
echo Directory %INFORMIXDIR%\demo\dbaccess\demo_ids not found.
goto end

:SUCCESS
echo The %DB% database created successfully.
echo The demo files can be found in %INFORMIXDIR%\demo\dbaccess\demo_ids

:end
set GL_DATE=%GL_DATE_SAV%
set DBMONEY=%DBMONEY_SAV%
set CREATE=%CREATE_SAV%
set DB=%DB_SAV%
set IN=%IN_SAV%
set DBSPACE=%DBSPACE_SAV%
set WLOG=%WLOG_SAV%
set CLIENT_LOCALE=%CLIENT_LOCALE_SAV%

set GL_DATE_SAV=
set DBMONEY_SAV=
set CREATE_SAV=
set DB_SAV=
set IN_SAV=
set DBSPACE_SAV=
set WLOG_SAV=
set CLIENT_LOCALE_SAV=

echo.

popd