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-csdltool 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-sqltool 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 Creating an OData Service.
The sql-to-csdl Tool¶
Find the sql-to-csdl script in the same folder as the csdl-to-war script.
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 Terminal 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.Importedannotation 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.Tableannotation 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.Columnannotation 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.ColumnTypeannotation 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.GeneratedKeyannotation indicates that database-level key generation should be used (with SQL identity/sequence mechanisms). If a database sequence is used, theSQL.KeySequenceannotation is also provided to indicate the name of a database sequence. -
The
SQL.RowVersionannotation indicates the name of an integer-typed row version column used for optimistic concurrency control. -
The
SQL.TrackingTablecolumn indicates the name of a SQL table that will be generated by thecsdl-to-sqltool to support change tracking. -
The
SQL.DeltaViewcolumn indicates the name of a SQL view that will be generated by thecsdl-to-sqltool 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¶
Find the csdl-to-sql script in the same folder as the csdl-to-war script.
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 Terminal 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.