Working with Existing Database Tables¶
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 withfind /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 ascsdl-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, theSQL.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 thecsdl-to-sql
tool to support change tracking. -
The
SQL.DeltaView
column indicates the name of a SQL view that will be generated by thecsdl-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 release, the -track:changes
and -track:downloads
options can only be used with hana
or mssql
.
Note
For production deployment, the generated OData service needs to establish JDBC connectivity to the existing database. If the OData service runs in SAP Business Technology Platform, SAP Cloud Connector may be required. Since SAP Cloud 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.
Tip
Consider using Java package naming conventions or .NET Namespace Conventions.
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 withfind /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.