# Script to reproduce PartsVDB
#
# add-the VDB
#
workspace
create-vdb PartsVDB
cd PartsVDB
set-property description 'Sample Parts Supplier example VDB'
#
# =============================================================
# Add PartsSQLServer Source Model
# =============================================================
#
add-model PartsSQLServer
cd PartsSQLServer
#set-property modelType PHYSICAL
#
# PARTS table
#
add-table PARTS
cd PARTS
set-property NAMEINSOURCE '"partssupplier"."dbo"."PARTS"'
add-column PART_ID
cd PART_ID
set-property datatypeName string
set-property datatypeLength 50
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"PART_ID"'
set-property nullable NO_NULLS
cd ..
add-column PART_NAME
cd PART_NAME
set-property datatypeName string
set-property datatypeLength 255
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"PART_NAME"'
cd ..
add-column PART_COLOR
cd PART_COLOR
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"PART_COLOR"'
cd ..
add-column PART_WEIGHT
cd PART_WEIGHT
set-property datatypeName string
set-property datatypeLength 255
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"PART_WEIGHT"'
cd ..
cd ..
#
# SHIP_VIA table
#
add-table SHIP_VIA
cd SHIP_VIA
set-property NAMEINSOURCE '"partssupplier"."dbo"."SHIP_VIA"'
add-column SHIPPER_ID
cd SHIPPER_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE numeric
set-property NAMEINSOURCE '"SHIPPER_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column SHIPPER_NAME
cd SHIPPER_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SHIPPER_NAME"'
cd ..
cd ..
#
# STATUS table
#
add-table STATUS
cd STATUS
set-property NAMEINSOURCE '"partssupplier"."dbo"."STATUS"'
add-column STATUS_ID
cd STATUS_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE numeric
set-property NAMEINSOURCE '"STATUS_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column STATUS_NAME
cd STATUS_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"STATUS_NAME"'
cd ..
cd ..
#
# SUPPLIER table
#
add-table SUPPLIER
cd SUPPLIER
set-property NAMEINSOURCE '"partssupplier"."dbo"."SUPPLIER"'
add-column SUPPLIER_ID
cd SUPPLIER_ID
set-property datatypeName string
set-property datatypeLength 10
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SUPPLIER_ID"'
set-property nullable NO_NULLS
cd ..
add-column SUPPLIER_NAME
cd SUPPLIER_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SUPPLIER_NAME"'
cd ..
add-column SUPPLIER_STATUS
cd SUPPLIER_STATUS
set-property datatypeName bigdecimal
set-property NATIVE_TYPE numeric
set-property NAMEINSOURCE '"SUPPLIER_STATUS"'
set-property FIXED_LENGTH true
cd ..
add-column SUPPLIER_CITY
cd SUPPLIER_CITY
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SUPPLIER_CITY"'
cd ..
add-column SUPPLIER_STATE
cd SUPPLIER_STATE
set-property datatypeName string
set-property datatypeLength 2
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SUPPLIER_STATE"'
cd ..
cd ..
#
# SUPPLIER_PARTS table
#
add-table SUPPLIER_PARTS
cd SUPPLIER_PARTS
set-property NAMEINSOURCE '"partssupplier"."dbo"."SUPPLIER_PARTS"'
add-column SUPPLIER_ID
cd SUPPLIER_ID
set-property datatypeName string
set-property datatypeLength 10
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SUPPLIER_ID"'
set-property nullable NO_NULLS
cd ..
add-column PART_ID
cd PART_ID
set-property datatypeName string
set-property datatypeLength 50
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"PART_ID"'
set-property nullable NO_NULLS
cd ..
add-column QUANTITY
cd QUANTITY
set-property datatypeName bigdecimal
set-property NATIVE_TYPE numeric
set-property NAMEINSOURCE '"QUANTITY"'
set-property FIXED_LENGTH true
cd ..
add-column SHIPPER_ID
cd SHIPPER_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE numeric
set-property NAMEINSOURCE '"SHIPPER_ID"'
set-property FIXED_LENGTH true
cd ..
cd ..
#
# PartsSQLServer VdbModelSource
#
add-source PartsSQLServer
cd PartsSQLServer
set-property sourceJndiName PartsSQLServer
set-property sourceTranslator sqlserver
cd ..
cd ..
#
# =============================================================
# add-PartsOracle Source Model
# =============================================================
#
add-model PartsOracle
cd PartsOracle
#
# PARTS table
#
add-table PARTS
cd PARTS
set-property NAMEINSOURCE '"PARTSSUPPLIER"."PARTS"'
add-column PART_ID
cd PART_ID
set-property datatypeName string
set-property datatypeLength 4
set-property NATIVE_TYPE CHAR
set-property NAMEINSOURCE '"PART_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column PART_NAME
cd PART_NAME
set-property datatypeName string
set-property datatypeLength 255
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"PART_NAME"'
cd ..
add-column PART_COLOR
cd PART_COLOR
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"PART_COLOR"'
cd ..
add-column PART_WEIGHT
cd PART_WEIGHT
set-property datatypeName string
set-property datatypeLength 255
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"PART_WEIGHT"'
cd ..
add-primary-key pk
cd pk
add-column /workspace/PartsVDB/PartsOracle/PARTS/PART_ID
cd ..
cd ..
#
# SHIP_VIA table
#
add-table SHIP_VIA
cd SHIP_VIA
set-property NAMEINSOURCE '"PARTSSUPPLIER"."SHIP_VIA"'
add-column SHIPPER_ID
cd SHIPPER_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE varchar
set-property NAMEINSOURCE '"SHIPPER_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column SHIPPER_NAME
cd SHIPPER_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE NUMBER
set-property NAMEINSOURCE '"SHIPPER_NAME"'
cd ..
add-primary-key pk
cd pk
add-column /workspace/PartsVDB/PartsOracle/SHIP_VIA/SHIPPER_ID
cd ..
cd ..
#
# STATUS table
#
add-table STATUS
cd STATUS
set-property NAMEINSOURCE '"PARTSSUPPLIER"."STATUS"'
add-column STATUS_ID
cd STATUS_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE NUMBER
set-property NAMEINSOURCE '"STATUS_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column STATUS_NAME
cd STATUS_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"STATUS_NAME"'
cd ..
add-primary-key pk
cd pk
add-column /workspace/PartsVDB/PartsOracle/STATUS/STATUS_ID
cd ..
cd ..
#
# SUPPLIER table
#
add-table SUPPLIER
cd SUPPLIER
set-property NAMEINSOURCE '"PARTSSUPPLIER"."SUPPLIER"'
add-column SUPPLIER_ID
cd SUPPLIER_ID
set-property datatypeName string
set-property datatypeLength 10
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"SUPPLIER_ID"'
set-property nullable NO_NULLS
cd ..
add-column SUPPLIER_NAME
cd SUPPLIER_NAME
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"SUPPLIER_NAME"'
cd ..
add-column SUPPLIER_STATUS
cd SUPPLIER_STATUS
set-property datatypeName bigdecimal
set-property NATIVE_TYPE NUMBER
set-property NAMEINSOURCE '"SUPPLIER_STATUS"'
set-property FIXED_LENGTH true
cd ..
add-column SUPPLIER_CITY
cd SUPPLIER_CITY
set-property datatypeName string
set-property datatypeLength 30
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"SUPPLIER_CITY"'
cd ..
add-column SUPPLIER_STATE
cd SUPPLIER_STATE
set-property datatypeName string
set-property datatypeLength 2
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"SUPPLIER_STATE"'
cd ..
add-primary-key pk
cd pk
add-column /workspace/PartsVDB/PartsOracle/SUPPLIER/SUPPLIER_ID
cd ..
cd ..
#
# SUPPLIER_PARTS table
#
add-table SUPPLIER_PARTS
cd SUPPLIER_PARTS
set-property NAMEINSOURCE '"PARTSSUPPLIER"."SUPPLIER_PARTS"'
add-column SUPPLIER_ID
cd SUPPLIER_ID
set-property datatypeName string
set-property datatypeLength 10
set-property NATIVE_TYPE VARCHAR2
set-property NAMEINSOURCE '"SUPPLIER_ID"'
set-property nullable NO_NULLS
cd ..
add-column PART_ID
cd PART_ID
set-property datatypeName string
set-property datatypeLength 4
set-property NATIVE_TYPE CHAR
set-property NAMEINSOURCE '"PART_ID"'
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column QUANTITY
cd QUANTITY
set-property datatypeName bigdecimal
set-property NATIVE_TYPE NUMBER
set-property NAMEINSOURCE '"QUANTITY"'
set-property FIXED_LENGTH true
cd ..
add-column SHIPPER_ID
cd SHIPPER_ID
set-property datatypeName bigdecimal
set-property NATIVE_TYPE NUMBER
set-property NAMEINSOURCE '"SHIPPER_ID"'
set-property FIXED_LENGTH true
cd ..
add-primary-key pk
cd pk
add-column /workspace/PartsVDB/PartsOracle/SUPPLIER_PARTS/PART_ID
cd ..
cd ..
#
# PartsOracle VdbModelSource
#
add-source PartsOracle
cd PartsOracle
set-property sourceJndiName PartsOracle
set-property sourceTranslator custom_oracle
cd ..
cd ..
#
# =============================================================
# add-PartsViews View Model
# =============================================================
#
add-model PartsViews
cd PartsViews
#
# PartSummary view
#
add-view PartSummary
cd PartSummary
add-column PART_ID
cd PART_ID
set-property datatypeName string
set-property datatypeLength 4
set-property nullable NO_NULLS
set-property FIXED_LENGTH true
cd ..
add-column PART_NAME
cd PART_NAME
set-property datatypeName string
set-property datatypeLength 255
cd ..
add-column PART_COLOR
cd PART_COLOR
set-property datatypeName string
set-property datatypeLength 30
cd ..
add-column PART_WEIGHT
cd PART_WEIGHT
set-property datatypeName string
set-property datatypeLength 255
cd ..
add-column SUPPLIER_ID
cd SUPPLIER_ID
set-property datatypeName string
set-property datatypeLength 10
set-property nullable NO_NULLS
cd ..
add-column QUANTITY
cd QUANTITY
set-property datatypeName bigdecimal
set-property FIXED_LENGTH true
cd ..
add-column SHIPPER_ID
cd SHIPPER_ID
set-property datatypeName bigdecimal
set-property FIXED_LENGTH true
cd ..
set-property queryExpression 'SELECT PartsOracle.PARTS.PART_ID, PartsOracle.PARTS.PART_NAME, PartsOracle.PARTS.PART_COLOR, PartsOracle.PARTS.PART_WEIGHT, PartsSQLServer.SUPPLIER_PARTS.SUPPLIER_ID, PartsSQLServer.SUPPLIER_PARTS.QUANTITY, PartsSQLServer.SUPPLIER_PARTS.SHIPPER_ID FROM PartsOracle.PARTS, PartsSQLServer.SUPPLIER_PARTS WHERE PartsSQLServer.SUPPLIER_PARTS.PART_ID = PartsOracle.PARTS.PART_ID'
cd ..
cd ..
#
# =============================================================
# add-custom_oracle VdbTranslator
# =============================================================
#
add-translator custom_oracle oracle
cd custom_oracle
#set-property DatabaseTimeZone CST
#set-property supportsFullOuterJoins false
workspace