SAP HANA 6. 创建schema、table、view、sequence、role

1、创建schema（WORKSHOPA_00.hdbschema）

代码：

schema_name="WORKSHOPA_00";

 

2、创建table（header.hdbtable、item.hdbtable）

Header.hdbtable代码：

table.schemaName ="WORKSHOPA_00";
table.tableType =COLUMNSTORE;
table.description ="Workshop Order Header";
table.columns = [
{name ="OrderId"; sqlType = NVARCHAR; nullable = false; length = 10; comment= "Order ID"; },
{name ="CreatedBy"; sqlType = NVARCHAR; nullable = false; length = 10;comment = "Created By"; },
{name ="CreatedAt"; sqlType = DATE; nullable = false; comment ="Created At Date and Time"; },
{name ="Currency"; sqlType = NVARCHAR; nullable = false; length = 5; comment= "Currency Code"; },
{name ="GrossAmount"; sqlType = DECIMAL; nullable = false; precision = 15;scale = 2; defaultValue = "0";
comment ="Total Gross Amount"; }
];
table.primaryKey.pkcolumns= ["OrderId"];

 

Item.hdbtable代码：

table.schemaName ="WORKSHOPA_00";
table.tableType =COLUMNSTORE;
table.description ="Workshop Order Item";
table.columns = [
{name ="OrderId"; sqlType = NVARCHAR; nullable = false; length = 10; comment=
"OrderID"; },
{name ="OrderItem"; sqlType = NVARCHAR; nullable = false; length = 10;comment =
"OrderItem"; },
{name ="ProductId"; sqlType = NVARCHAR; nullable = false; length = 10;comment =
"ProductID"; },
{name ="Quantity"; sqlType = DECIMAL; nullable = false; precision = 13;scale = 3;
defaultValue ="0";
comment ="Quantity"; },
{name ="QuantityUnit"; sqlType = NVARCHAR; nullable = false; length = 3;comment =
"QuantityUnit"; },
{name ="DeliveryDate"; sqlType = DATE; nullable = true; comment =
"ScheduledDelivery Date"; }];
table.primaryKey.pkcolumns= ["OrderId","OrderItem"];

 

3、创建sequence（orderid.hdbsequence）

代码：

schema="WORKSHOPA_00";
start_with=2000000000;
depends_on_table="workshop.sessiona.00.data::header";

 

4、创建view（ordersExt.hdbview）

代码：

schema="WORKSHOPA_00";
query="selectT0.\"OrderId\",
T1.\"OrderItem\",
T0.\"CreatedBy\",
T0.\"CreatedAt\",
T1.\"ProductId\",
T1.\"Quantity\",
T1.\"QuantityUnit\"
from\"WORKSHOPA_00\".\"WorkShop.sessiona.00.data::header\" T0
left outer join\"WORKSHOPA_00\".\"WorkShop.sessiona.00.data::item\" T1
onT0.\"OrderId\" = T1.\"OrderId\"
order byT0.\"OrderId\" ASC";
depends_on_table=["WorkShop.sessiona.00.data::header","WorkShop.sessiona.00.data::item"];

 

activate过程中发生错误如下：

经检查是package大小写问题，因建package时WorkShop命名中有大写，workshop.session<session>.<group>大小写调整后，程序运行成功。

 

5、创建role（workshopUser.role、workshopAdmin.role）

workshopUser.role代码：

roleWorkShop.sessiona.00.data::workshopUser {
catalogschema "WORKSHOPA_00": SELECT;
    application privilege:WorkShop.sessiona.00::Basic;
 
}

 

workshopAdmin.role代码：

roleWorkShop.sessiona.00.data::workshopAdmin
extends roleWorkShop.sessiona.00.data::workshopUser
{
catalogschema "WORKSHOPA_00": SELECT, INSERT, UPDATE, DELETE, DROP;
    application privilege:WorkShop.sessiona.00::Admin;
 
}

 

6、将role权限授权给username

SQL语句：

call "_SYS_REPO"."GRANT_ACTIVATED_ROLE"('WorkShop.sessiona.00.data::workshopAdmin','SYSTEM');

注意：username必须为已经存在的用户，不能自动创建。