namespace test.tom;

using test.tom::CDS_TEST_TYPE.test_type;

@Schema: 'TEST'

context CDS_TEST {

    //单行注释

    /*

    多行注释

    */

};

context CDS_TEST_TYPE {

    type test_type : Integer;

    type test_struct_type{

         test_type1 : String(20);

         test_type2 : String(30);

    };

};

    /*定义类型*/

    //单个类型

    type t1_char200 : String(200);

    //类型集

    type t1_collection{

    field1 : Integer;

    field2 : String(20);

    }

    /*嵌套Context*/

    context MyContext1{

    //定义type

    type t2_char10 : String(10);

    //使用上层单个type

    type t2_sin_type : t1_char200;

    //使用上层类型集中type

    type t2_col_type : type of t1_collection.field2;

    }

 /*定义entity(table)*/

 context MyContext2{

    //定义user table

    entity MyUser{

             key ID : Integer; //key设置为键值

             Name : String(100) not null; //不能为空

             Address : String(200) default 'CN'; //设置默认值

             Account : Decimal(15,2);

             Telephone : String(12);

         };

    //定义默认值测试table

    entity MyDefault{

         //generated [always | by default] as identity

         //Defining an element with IDENTITY is not supported in XS Classic

         //定义自增

         //key id : Integer generated always as identity(start with 1 increment by 1);

             field1 :  Integer       default -42;

             field2 :  Integer64     default 9223372036854775807;

             field3 :  Decimal(5,3)  default 12.345;

             field4 :  BinaryFloat   default 123.456e-1;

             field5 :  LocalDate     default date'2013-04-29';

             field6 :  LocalTime     default time'17:04:03';

             field7 :  UTCDateTime   default timestamp'2013-05-01 01:02:03';

             field8 :  UTCTimestamp  default timestamp'2013-05-01 01:02:03';

             //field9 :  Binary(32)    default x'0102030405060708090a0b0c0d0e0123[...]';

             field10 : String(10)    default 'foo';

         //generated always as <expression>

         //Generated calculated elements not supported in XS Classic

         //定义公式

         //field11 : Decimal(10,3) generated always as field1 + field3;

    };

    //定义table

    //通过批注等配置存储方式，index

    @Catalog:{

        tableType : #COLUMN,

             index : [

                  { name:'Index1', order:#DESC, unique:true,  elementNames:['ID'] },

                  { name:'Index2', order:#DESC, unique:false, elementNames:['name'] }

                    ]

         }

    entity Books{

         key ID : Integer;

         name : String(20);

         Editor: String(100);

    };

    //通过technical设置存储方式，index

    entity Author{

         key ID : Integer;

         name : String(20);

         Address : String(100);

    } technical configuration {

              row store;

              index MyIndex1 on (name) asc;

              unique index MyIndex2 on (ID,name) desc;

    };
}

    //定义table

    //定义Annotation关系，类似于Table直接外键。

    context SimpleAssociations{

             type StreetAddress{

                 name : String(80);

                 number : Integer;

             };

             type CountryAddress{

                 name : String(80);

                 code : String(3);

             };

             entity Address{

                 key id : Integer;

                 street : StreetAddress;

                 zipCode : Integer;

                 city : String(80);

                 country : CountryAddress;

                 type : String(10); // home, office

             };

             entity Person{

                 key id : Integer;

                 // address1,2,3 are to-one associations

                 address1 : Association to Address;

                 address2 : Association to Address { id };

                 address3 : Association[1] to Address { zipCode, street, country };

                 // address4,5,6 are to-many associations

                 address4 : Association[0..*] to Address { zipCode };

                 address5 : Association[*] to Address { street.name };

                 address6 : Association[*] to Address { street.name AS streetName,country.name AS countryName };

                 //对订单关系

                 orders : Association[*] to SalesOrder on orders.Person_id = id;

             };

             //订单和人员，Item之间关系

             entity SalesOrder{

             key Order_id : Integer;

             key Item_id : Integer;

             key Person_id : Integer;

             count : Decimal(18,2);

             //对person,item关系,1個張訂單對應一個人，一個訂單對應多個訂單Item

             person : Association[1] to Person on person.id = Person_id;

             item : Association[*] to Item on item.id = Item_id;

             };

             //订单item

             entity Item{

             key id : Integer;

             name : String(20);

             //对应SalesOrder关系

             orders : Association[1] to SalesOrder on orders.Item_id = id;

             };

         }

/*定义View*/

Context MyContext3{

    //const定义常量

    const number1 : Integer = 4;

    entity test{

         name : String(20);

         a : Integer;

         b : Integer;

    };

    entity test1{

         name : String(20);

         a : Decimal(10,2);

         b : Integer;

    }

    　　 //定义View,查询MyContext2.SimpleAssociations路径,括号中定义查询字段

        view AddressView as select from MyContext2.SimpleAssociations.Address{

             id,

             street.name,

             street.number

         };

         //表达式,计算+，-，*，/,||连接操作,case when操作

         view CalView as select from test{

             a + b AS data_sum,

             a - b AS data_sub,

             a * 1 AS data_mul,

             a / 1 AS data_div,

             -1 AS data_neg,

             a || b AS data_con,

             CASE WHEN a > 10 THEN 'large'

                  WHEN a > 20 THEN 'l large'

             ELSE 'none'

             END AS data_case

         };

         //统计function

         //AVG,COUNT,MIN,MAX,SUM,STDDEV,VAR

         //where条件

         //group by分组

         //having 统计function条件

         //order by 排序，

         //limit xx offset xx限制条数

         view FuncView as select from test{

             name,

             AVG(a) AS data_avg,

             COUNT(*) AS data_count,

             MIN(a) AS data_min,

             MAX(a) AS data_max,

             SUM(a) AS data_sum,

             STDDEV(a) AS data_stddev,

             VAR(a) AS data_var

         }where name in('tom','jim','tian')

         group by name  //分组

         having AVG(a) > 100

         order by name limit 1 offset 30

         ;

         //UNION操作

         view UnionView as select from test{name,a,b}

                            union

                            select from test1{name,a,b};

         //Join操作

         //[ INNER ] JOIN

         //LEFT [ OUTER ] JOIN

         //RIGHT [ OUTER ] JOIN

         //FULL [ OUTER ] JOIN

         //CROSS JOIN

         view JoinView as select from test join test1 on test.name = test1.name{

             test.name,

             test.a + test1.a as a,

             test.b + test1.b as b

         };

         //top 前几条记录，distinct去重

         view topView as select from test top 10 {

             name,

             a,

             b

         };

         view distinctView as select from test distinct{

             name

         };

         //定义具有参数的View

         //parameter names :只能由大写字母，数字，下划线组成，不能数字开头

         //Views with parameters are not supported in XS Classic

         /*

         view paramView with parameters PAR1 : Integer,

                                          PAR2 : String(20)

         as select from test{

             name,

             a,

             b + $parameters.PAR1 AS b

         }where name = $parameters.PAR2;

         */

         //查询该Schema下实体Table;

         //不能多个Schema Table 混合同时访问；

         //@Schema批注指定Schema

         view tableView as select from TEST_SFLIGHT distinct{

             CARRID,

             CONNID

         };

         /*

         view tableView1 as select from SFLIGHT distinct{

             CARRID,

             CONNID

         };

         */

}