SAP HANA SPS 09: New Developer Features; Core Data Services

This blog is part of the larger series on all new developer features in SAP HANA SPS 09:http://scn.sap.com/community/developer-center/hana/blog/2014/12/02/sap-hana-sps-09-new-developer-features

Core data services (CDS) is an infrastructure for defining and consuming semantically rich data models in SAP HANA. Using a a data definition language (DDL), a query language (QL), and an expression language (EL), CDS is envisioned to encompass write operations, transaction semantics, constraints, and more.

A first step toward this ultimate vision for CDS was the introduction of the hdbdd development object in SPS 06. This new development object utilized the Data Definition Language of CDS to define tables and structures. It can therefore be consider an alternative to hdbtable and hdbstructure.

In SPS 09 we continue to build on the foundation of CDS and the HDBDD development artifact.  We introduce important new syntax as well as significant enhancements to the lifecycle management of these development objects.

Lifecycle Management

One of the biggest requests for CDS/HDBDD was to improve the lifecycle management capabilities.  Before SPS 09 if you made most changes to an entity definition for a table which had data in it, you received an activation error. The developer was responsible for migrated data out of the existing table, dropping it, recreating with the new structure and then migrating the data back. This process was not only cumbersome and error prone but also made transporting such changes automatically to downstream systems nearly impossible.

In SPS 09 we add an automatic migration process to activation of the HDBDD artifact. This supports a variety of scenarios.

• Conversion from the same named hdbtable artifact
• Change of key / data type
• Removal of columns

In these situations, data is moved transparently to a shadow table.  The source table is then dropped/adjusted and the data is migrated back. This allows for structural changes that in the past would have required significant developer work to preserve the data.  Even better is that this process happens automatically upon activation, therefore transporting content via Delivery Units will automatically trigger the migration.

Multi-File Support

Another very common request was for multiple-file support in HDBDD. Before SPS 09 you often were force into very large monolithic HDBDD artifacts if you wanted to share association or type definitions between entities or views. It also meant no way to defined global types that could be reused across the system.

SPS 09 solves all of these problems by adding the ability to import from one or more existing HDBDD files.  All the entity definitions, types, etc then become visible to the destination HDBDD artifact.

It is possible to refer to an artifact that is defined in another HDBDD file (“external” artifact). Each external artifact must explicitly be made accessible by a using declaration. The using declaration introduces a local name as an alias for the external artifact, which is identified by a fully qualified name. The external artifact can be either a single object (type, entity, view) or a context. The using declarations are located in the header of the file between the namespace declaration and the beginning of the top level artifact.

For example you might create some reusable types in a central HDBDD:

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context ContextA {
  type T1 : Integer;
  context ContextAI {
    type T2 : String(20);
    type T3 {
      a : Integer;
      b : String(88);
    };
  };
};

You can then import and reference these types in a separate HDBDD artifact.

namespace playground.sp9.cds;
using playground.sp9.cds::ContextA.T1;
using playground.sp9.cds::ContextA.ContextAI as ic;
using playground.sp9.cds::ContextA.ContextAI.T3 as ict3;
@Schema: 'SP9DEMO'
context ContextB {
  type T10 {
    a : T1;               // Integer
    b : ic.T2;            // String(20)
    c : ic.T3;            // structured
    d : type of ic.T3.b;  // String(88)
    e : ict3;             // structured
  };
  context ContextBI {
    type T1 : String(7);  // hides the T1 coming from the first using declaration
    type T2 : T1;         // String(7)
  };
};

Enumerations

Another new syntax feature in SPS 09 is the introduction of enumerations.  That’s a type which declares several possible values or domains.  However Enumerations can only be used within Annotations. The definition of annotations is very similar to type definitions. Annotation definitions can be either located inside a context, or an annotation definition can be the single top level artifact in a CDS file.  However annotations do NOT generate catalog artifacts.

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context enumerations {
    type Color : String(10) enum { red = 'FF0000'; g = '00FF00'; b = '0000FF'; };
annotation MyAnnotation {
  a : Integer;
  b : String(20);
  c : Color;
  d : Boolean;
};
};

New Types

One of the other common requests was for the CDS syntax to support all the HANA data types.  With SPS 09 we extend the supported types, including the GeoSpatial types.  Please note that the support the GeoSpatial types ST_POINT and ST_GEOMETRY is still limited.  These types can only be used for the definition of elements in types and entities.  It is not possible to define a CDS view that selects such an element from an CDS Entity.

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context NewTypes {
@nokey
entity SomeTypes {
  a : hana.ALPHANUM(10);
  b : hana.SMALLINT;
  c : hana.TINYINT;
  d : hana.SMALLDECIMAL;
  e : hana.REAL;
  h : hana.VARCHAR(10);
  i : hana.CLOB;
  j : hana.BINARY(10);
  k : hana.ST_POINT;
  l : hana.ST_GEOMETRY;
};
 
};

View Syntax

The View Syntax receives several new features as well.  For example we can now specify the OrderBy clause in the view definition:

//Order By
view EmployeesView as select from Employee
{
  orgUnit,
  salary
} order by salary desc;

We can also use the CASE syntax within the view definition:

//Case
entity MyEntity {
key id : Integer;
     a : Integer;
     color : String(1);
};
 
view MyView as select from MyEntity {
    id,
    case color 
        when 'R' then 'red'
        when 'G' then 'green'
        when 'B' then 'blue'
        else 'black'
    end as color,
    case when a < 10 then 'small'
        when 10 <= a and a < 100 then 'medium'
        else 'large'
    end as size
};

Unmanaged Associations

Associations in general are one of the most powerful features of the CDS syntax. They move the relationship definition out of the view/join and into the source entity definition for better re-usability/maintenance and simpler overall syntax.

An unmanaged associations is based on existing elements of the source and target entity. No fields are generated. In the ON condition, only elements of the source or the target entity can be used; it is not possible to use other associations. The ON condition may contain any kind of expression – all expressions supported in views should also work in the ON condition of an unmanaged association. The names in the ON condition are resolved in the scope of the source entity. Elements of the target entity are accessed via the association itself.

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context unmanagedAssociations {
entity Employee {
  key id   : String(256);
  officeId : Integer;
};
entity Room {
  key id : Integer;
  inhabitants : Association[*] to Employee on inhabitants.officeId = id;
};
 
entity Thing {
  key id   : Integer;
  parentId : Integer;
  parent   : Association[1] to Thing on parent.id = parentId;
  children : Association[*] to Thing on children.parentId = id;
};
 
};

Backlink

Unmanaged associations also make several previously unsupported features possible.  For example the backlink. The CDS specification defines backlink associations. This kind of association is not yet supported. But you can (with a little extra effort) achieve the same effect by using unmanaged associations.

According to the CDS specification, it shall be possible to define a header/item relationship like this:

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context backlink {
//Backlink syntax not yet supported
//entity Header {
//  key id : Integer;
//  items : Association[*] to Item via backlink header;
//  description : String(120);
//};
//
//entity Item {
//  key header : Association[1] to Header;
//  key id : Integer;
//  description : String(120);
//};
//
//Workaround using unmanaged associations
entity Header {
  key id : Integer;
  items : Association[*] to Item on items.headerId = id;
  description : String(120);
};
entity Item {
  key headerId : Integer;
  key id : Integer;
  header : Association[1] to Header on header.id = headerId;
  description : String(120);
};
};

Many to Many

Similarly Many to Many can also be simplified using unmanaged associations. CDS defines mediated m-to-n associations, which can be defined using the “via entity” keyword. These kinds of associations are not yet supported directly in HDBDD. But using unmanaged associations, there is a workaround.

Example: model of employees and projects, employee can be assigned to any number of projects. The link table must be modelled explicitly.

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context manyToMany {
entity Employee {
  key id : Integer;
  name : String(80);
  projectLinks : Association[*] to E2P on projectLinks.e_id = id;
};
entity Project {
  key id : Integer;
  name   : String(80);
  employeeLinks : Association[*] to E2P on employeeLinks.p_id = id;
};
entity E2P { 
  key e_id : Integer;
  key p_id : Integer;
  projects  : Association[*] to Project  on projects.id  = p_id;
  employees : Association[*] to Employee on employees.id = e_id;
}; 
view EmployeesWithProjects as select from Employee {
     name                       as EmployeeName,
     projectLinks.projects.id   as projectId,
     projectLinks.projects.name as projectName
  };
   
view ProjectsWithEmployees as select from Project {
     name                         as projectName,
     employeeLinks.employees.id   as EmployeeId,
     employeeLinks.employees.name as EmployeeName
};
 
};

Full Text Index

Last but not least we now have syntax to define full text Index within the CDS/HDBDD artifact.

namespace playground.sp9.cds;
@Schema: 'SP9DEMO'
context fullText {
entity Header {
  key id : Integer;
@SearchIndex.text: { enabled: true }
@SearchIndex.fuzzy: { enabled: true }
  description : String(120);
};
};