Skip to content

Working with Existing Database Tables

Caution

The following sections describe a Preview Feature in this release. It should not be used at this time for production deployment.

Command-Line Tools

Two additional tools support the modeling and generation of an OData service that exposes existing database tables as OData entity sets, with or without OData change tracking or download tracking.

  • The sql-to-csdl tool generates an OData CSDL XML metadata file for the service from a SQL data model which defines the existing database tables.

    • The generated CSDL definitions can be edited to alter the naming of the imported entity types and properties if the automatically derived names do not conform to the desired naming conventions.

    • Additional CSDL definitions can be added which are not required for access to the existing database tables, but are required to support client applications. For example, Filter Entities for use with Download Queries.

  • The csdl-to-sql tool generates SQL definitions for any additional tables, triggers and views that are required to support the OData service.

    • Additional tables, triggers and views will extend the existing database schema without changing the SQL definitions of existing tables.

    • The SQL database administrator must apply the generated SQL definitions to the existing database.

For offline scenarios, enabling change/download tracking for existing database tables is likely to be more efficient than using a cache database, as it avoids the need to periodically copy back-end data from existing database tables into cache database tables. It also ensures that offline-enabled clients can download the most up-to-date information when they synchronize. Some additional database workload should be expected due to extra database triggers and client download activity.

After using the tools, please proceed with setting up Visual Studio Code Commands and Visual Studio Code Tasks, followed by Generating an OData Service and finally Deploying the Generated Service.

The sql-to-csdl Tool

The sql-to-csdl command-line tool is delivered as a Windows batch file (sql-to-csdl.bat) and as a Mac/Linux shell script (sql-to-csdl.sh) in the resources\server-odata-sdk\bin folder within the Visual Studio Code extension for Mobile Back-End Tools (vsc-extension-mbt).

  • Windows: %USERPROFILE%\.vscode\extensions\sapse.vsc-extension-mbt-X.Y.Z\resources\server-odata-sdk\bin\sql-to-csdl.bat

  • Mac/Linux: $HOME/.vscode/extensions/sapse.vsc-extension-mbt-X.Y.Z/resources/server-odata-sdk/bin/sql-to-csdl.sh

  • SAP Business Application Studio (in a Terminal window): locate with find /tmp/vscode-unpacked -name sql-to-csdl.sh -print

The command-line tool accepts a SQL data model as input, and generates an OData CSDL XML metadata file as output.

Note

The SQL data model input file should be obtained from the SQL database administrator. It should include the create table statements for the existing database tables, the create sequence statements for any sequences used for key generation, and any alter table statements that define foreign key constraints.

Command-Line Tool Usage: sql-to-csdl

The minimum required command-line tool parameters are a SQL data model input file name and an OData CSDL XML output file name.

Mac/Linux shell example:

sql-to-csdl.sh ~/test/example.sql ~/projects/myapp/metadata.csdl.xml -track:changes

Windows Command Prompt example:

sql-to-csdl.bat c:\test\example.sql c:\projects\myapp\metadata.csdl.xml -track:changes

The above example command generates an OData CSDL XML metadata file from a SQL data model, enabled for OData change tracking.

The generated CSDL file includes OData annotations to map OData model elements back to the original SQL definitions. These annotations should be preserved, even if the CSDL element names (e.g. entity type and property names) are altered.

  • The SQL.Imported annotation marks an entity type as having been imported from an existing database table. This annotation should be preserved so that other tools (such as csdl-to-sql) will not attempt to generate a new table for the entity type.

  • The SQL.Table annotation preserves the original SQL table name corresponding to an OData entity type. Editing the entity type name is supported, but the annotation should be preserved without changes.

  • The SQL.Column annotation preserves the original SQL column name corresponding to an OData entity property. Editing the entity property name is supported, but the annotation should be preserved without changes.

  • The SQL.ColumnType annotation preserves the original SQL column type corresponding to an OData entity property. Editing the entity property type is discouraged, and the annotation should be preserved without changes.

  • The SQL.GeneratedKey annotation indicates that database-level key generation should be used (with SQL identity/sequence mechanisms). If a database sequence is used, the SQL.KeySequence annotation is also provided to indicate the name of a database sequence.

  • The SQL.RowVersion annotation indicates the name of an integer-typed row version column used for optimistic concurrency control.

  • The SQL.TrackingTable column indicates the name of a SQL table that will be generated by the csdl-to-sql tool to support change tracking.

  • The SQL.DeltaView column indicates the name of a SQL view that will be generated by the csdl-to-sql tool to support change tracking.

Command-Line Tool Options: sql-to-csdl

The -database:type option should be specified for non-HANA databases. Other options should be specified as needed.


Option: -container <entity-container>

Indicates the OData EntityContainer Name. The name must not contain a period ('.').

If not provided, it defaults to EntityContainer.

Tip

Provide a name that describes the service, such as BankService, ShopService etc.


Option: -database:type <type-name>

Identifies the type of SQL database.

Legal values are: ase (SAP ASE), derby (Apache Derby), h2 (H2 database), hana (SAP HANA), mssql (Azure SQL or Microsoft SQL Server), oracle (Oracle Database), pgsql (PostgreSQL) or sqlany (SAP SQL Anywhere).

If not provided, it defaults to hana.

Caution

For this preview release, the -track:changes and -track:downloads options can only be used with hana.

Note

For production deployment, the generated OData service needs to establish JDBC connectivity to the existing database. If the OData service runs in SAP Cloud Platform, SAP Cloud Platform Connector may be required. Since SAP Cloud Platform Connector JDBC connectivity is only available for SAP HANA, if the existing database type is not HANA it will be necessary to run the generated OData service on-premise. See Deploying the Generated Service for more details.


Option: -generated <prefix-or-suffix>

Indicates the prefix or suffix naming pattern used in the database to identify the sequence name associated with a table name (for primary key generation).

If not provided, it defaults to suffix:_seq,suffix:_id_seq,suffix:_sequence.


Option: -namespace <schema-namespace>

Indicates the OData Schema Namespace. The namespace may contain a period ('.').

If not provided, it is derived from the SQL input file name.


Option: -odata:version <odata-version>

Specifies the target OData version (2.0 or 4.0).

It not provided, it defaults to 4.0.


Option: -overwrite

Overwrite the XML output file, even if it is newer than the SQL input file.

By default (when the -overwrite option is not specified), if the output file is newer than the input file, the tool will report an error. This avoids accidentally overwriting an XML file that was edited after it was generated.


Option: -row:version <column-name>

Indicates a database column name, which if found in a table will result in the SQL.RowVersion annotation being included in the generated entity type to enable optimistic concurrency control via the OData ETag mechanism.

Note: the indicated column will not result in a corresponding OData Property in the entity type.

If not provided, optimistic concurrency control will not be enabled for any entity types.


Option: -table:prefix <table-prefix>

Indicates a name prefix found in SQL table names which will be removed when OData entity type names are generated from the table names.

By default, no table prefix is assumed.


Option: -table:suffix <table-suffix>

Indicates a name suffix found in SQL table names which will be removed when OData entity type names are generated from the table names.

By default, no table suffix is assumed.


Option: -track:changes

Indicates that OData change tracking will be enabled via a SQL.TrackChanges annotation in the EntityContainer.


Option: -track:downloads

Indicates that OData download tracking will be enabled via a SQL.TrackDownloads annotation in the EntityContainer


The csdl-to-sql Tool

The csdl-to-sql command-line tool is delivered as a Windows batch file (csdl-to-sql.bat) and as a Mac/Linux shell script (csdl-to-sql.sh) in the resources\server-odata-sdk\bin folder within the Visual Studio Code extension for Mobile Back-End Tools (vsc-extension-mbt).

  • Windows: %USERPROFILE%\.vscode\extensions\sapse.vsc-extension-mbt-X.Y.Z\resources\server-odata-sdk\bin\csdl-to-sql.bat

  • Mac/Linux: $HOME/.vscode/extensions/sapse.vsc-extension-mbt-X.Y.Z/resources/server-odata-sdk/bin/csdl-to-sql.sh

  • SAP Business Application Studio (in a Terminal window): locate with find /tmp/vscode-unpacked -name csdl-to-sql.sh -print

The command-line tool accepts an OData CSDL XML metadata file as input, and generates a SQL data model as output.

Note

The SQL data model output file includes any additional tables, triggers or views that are required to support the OData service. It should be reviewed by the SQL database administrator, and then applied to the existing database.

Command-Line Tool Usage: csdl-to-sql

The minimum required command-line tool parameters are an OData CSDL XML input file name and a SQL data model output file name.

Mac/Linux shell example:

csdl-to-sql.sh ~/projects/myapp/metadata.csdl.xml ~/test/example_delta.sql

Windows Command Prompt example:

csdl-to-sql.bat c:\projects\myapp\metadata.csdl.xml c:\test\example_delta.sql

The above example command generates a SQL data model from an OData CSDL XML metadata file.

The generated SQL file includes any additional tables, triggers or views that are required to support the OData service.

Command-Line Tool Options: csdl-to-sql


Option: -overwrite

Overwrite the SQL output file, even if it is newer than the XML input file.

By default (when the -overwrite option is not specified), if the output file is newer than the input file, the tool will report an error. This avoids accidentally overwriting a SQL file that was edited after it was generated.


Last update: August 12, 2020