Manage Existing Schemas

Manage Existing Schemas
lac42
With a managed data server and a data source for a managed database added to the data server, you can manage the tables, fields, and relationships in the data source. You can manage the content in a data source for a managed database using API Creator or Data Explorer.
In API Creator, you manage the content in a data source for a managed database using one of the following methods:
  • By adding tables, columns, and relationships.
    This method is part of the 
    App-first
     approach to creating APIs.
    For more information about the approaches to creating APIs, see Creating APIs.
  • By converting a model, such as Swagger, to schema format and then creating your schema and API by importing your data model.
    This method is part of the 
    Model-first
     approach to creating APIs.
    For more information about how to convert a model to schema format, see Swagger to Schema.
  • By using the 
    @tables
    , the 
    @columns
    , or the 
    @relationships
     system REST endpoints.
    For more information about how to manage the content in a data source for a managed database using a system REST endpoint, see System REST Endpoints.
You can call tables by way of GET operations. If your database supports it and you have added virtual primary keys to your table, you can also call tables using POST, PUT, and DELETE requests.
Synchronize your schema changes with your repository by reloading the schema.
For more information about how to reload the schema, see Database Administration.
The following procedures describe how to manage the content using API Creator by adding tables and columns.
For more information about how to add relationships using API Creator, see Database Relationships.
In this article:
2
Convert a Data Source to be a Data Source for a Managed Database
You can manage the tables, fields, and relationships in the data source using API Creator only if the data source is a data source for a managed database. If the data source is not a data source for a managed database, you can convert it to be a data source for a managed database.
Follow these steps:
  1. Verify that you are connected to a data source that you can manage using 
    Layer7 Live API Creator
    . You can manage the following data source types in 
    Layer7 Live API Creator
    : MySQL, PostgreSQL, SQL Server, Oracle, and Derby.
  2. Verify that you have the Data designer role.
    For more information about this role, see Database Creation.
  3. Allow users with the appropriate credentials to edit the data source for this database. Complete the following steps:
    1. In the Create section, click
      Data Sources
      .
      The Connection tab displays.
    2. Select the
      Schema is editable
      checkbox, and then click
      Save
      .
    For more information about the fields on the Connection tab, see Database Connectivity.
The data source is converted to be a data source for a managed database.
Manage your Tables
Tables are the endpoints for each base entity (table or view).
Prerequisites:
  • You have allowed users with the appropriate credentials to edit the data source in which you want to manage tables.
  • If your enterprise database administrator (DBA) has adopted practices that limit your ability to create schemas, you have allowed users to create schemas in the database. You allow TeamSpace users to create schemas by connecting to the database that your DBA has created using the SQL user account with the appropriate credentials to edit the schema, and then selecting the 
    Schema is editable
    checkbox on the Data Sources, Connection tab.
    For more information about how to connect to databases and about this checkbox, see Database Connectivity.
You can do the following to manage your tables:
  • Add tables to your data source.
  • Edit a table name in your data source.
  • Delete a table in your data source.
You can manage your tables using the following methods:
  • Using API Creator.
  • By way of the 
    @tables
     managed data REST endpoint requests.
    For more information about how to manage your tables using managed data REST endpoint requests, see System REST Endpoints.
  • Using Data Explorer.
    For more information about how to manage your tables using Data Explorer, see Manage Tables in Managed Databases.
The following procedures describe how to manage your tables using API Creator.
Add a Table to your Data Source
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default. Your table endpoints are displayed on this tab.
  2. Click 
    Add
     in the 
    Tables
     listbox.
    The Add Table window opens.
  3. Complete the following fields, and then click 
    Add New Table
    :
    Data source
    The data source connection to which you want to add the table.
    Table name
    The name for your table.
The table is added to the data source.
Edit a Table Name in your Data Source
  1. On the Tables tab, click the table endpoints that you want to edit the name in the
    Tables
    listbox, and then click
    Update
    .
    The
    Update Table
    <data source connection:table name>
     window opens.
  2. Enter a name for the table in the 
    New name
     field, and then click 
    Update Table
    .
The changes to the table name are saved.
Delete Tables in your Data Source
On the Tables tab, select the table that you want to delete in the 
Tables
 listbox, and then click 
Delete
.
Manage your Columns
Prerequisites:
  • The data source in which you want to add, update, or delete a column is marked as editable.
  • If your enterprise database administrator (DBA) has adopted practices that limit your ability to create schemas, you have allowed TeamSpace users to create schemas in the database. You allow TeamSpace users to create schemas by connecting to the database that your DBA has created using the SQL user account with the appropriate credentials to edit the schema, and then selecting the 
    Schema is editable
     checkbox on the Data Sources, Connection tab.
    For more information about how to connect to databases and about this checkbox, see Database Connectivity.
You can do the following to manage your columns:
  • Add a column to a table.
  • Update a column name.
  • Delete a column.
You can manage your columns using the following methods:
  • Using API Creator.
  • By way of the 
    @columns
     and the 
    @relationships
     managed data REST endpoint requests.
    For more information about how to manage your columns using a managed data REST endpoint, see System REST Endpoints.
  • Using Data Explorer.
    For more information about how to manage your columns using Data Explorer, see Manage Tables in Managed Databases.
The following procedures describe how to manage your columns using API Creator.
Add a Column to your Table
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default. Your column endpoints are displayed in the
    Columns
    listbox on this tab.
  2. Click the table to which you want to add a column in the
    Tables
    listbox.
  3. Click 
    Add
     in the 
    Columns
     listbox.
    The
    Add Column to
    <data source connection:table name>
    window opens.
  4. Complete the following fields, and then click 
    Add Column
    :
    Column name
    The name of the column.
    Data type
    The data type for your column.
    Options:
     string, text, int, decimal, date, datetime, and boolean.
    Default:
     string.
    Size
    The column size.
    Default:
     100
    Nullable
    Specifies whether the column can contain a null value.
    Default:
     Selected
The column is added to your table.
Edit a Column Name in your Table
  1. On the Tables tab, click the table endpoints for which you want to edit a column name in the
    Tables
    listbox.
  2. Click the column for which you want to edit the name in the
    Columns
    listbox.
    The
    Update Column in Table 
    <data source connection:table name>
     window opens.
  3. Enter a name for the column in the 
    New Name
     field, and then click 
    Update Column
    .
The changes to the column name are saved.
Delete a Column from your Table
  1. On the Tables tab, select the table from which you want to delete a column in the
    Tables
    listbox.
  2. Click the column that you want to delete the name in the 
    Columns
     listbox, and then click 
    Delete
    .
    The 
    Delete Column from 
    <data source connection:table name>
     window opens.
  3. Click
    Delete Column
    .
The column is deleted from your table.
Manage Virtual Primary Keys in Tables
Some tables do not have defined database primary keys. You can add, update, and delete virtual primary keys to tables. With tables having virtual primary keys defined, you can:
  • (If your database supports it) Call tables using POST, PUT, and DELETE requests.
  • Call functions on tables, views, or resources and pass arguments.
    For more information about how to call functions on tables, views, and resources, see Manage Functions.
Add Virtual Primary Keys to Tables
You can define one or more columns as the virtual primary key for a table.
Follow these steps:
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default. Your tables are displayed. 
  2. Click 
    Add
     in the 
    Keys
     listbox.
    The Add Virtual Primary Key window opens.
  3. For each column that represents the unique row identifier for the table, select 
    Key
    , and then click 
    Add Key
    :
The virtual primary key is added to the table.
Update the Column Used as Virtual Primary Key for a Table
You can change the columns that are used as the virtual primary key for the table.
Prerequisite:
 You have added a virtual primary key to the table.
Follow these steps:
  1. On the Tables tab, select the virtual primary key that you want to change, and then click 
    Update
    .
    The Update Virtual Primary Key window opens.
  2. Change the column that is used as the virtual primary key for the table, and then click 
    Update
    .
The column selection that is used as the virtual primary key for the table is changed.
Delete Virtual Primary Keys from Tables
Prerequisite:
 The virtual primary key that you want to delete has been added to the table.
On the Tables tab, select the virtual primary key that you want to delete, and then click 
Delete
.
Call Tables
The virtual primary keys that you add to tables display in the 
href
 for each row of the table in a GET request. If your database supports it and your table has virtual primary keys defined, you can also call tables using POST, PUT, and DELETE requests.
Prerequisites:
You can call tables using the following named filters and sorts:
  • sysorder
     and 
    userorder
    .
    For more information about named sorts, see Structured Sorts.
  • sysfilter
     and 
    userfilter
    .
    For more information about named filters, see Structured Filters.
You can also supply pagination and supply the optimistic locking value (or override this check by specifying the 
checksum
 value).
For more information about pagination, see Pagination.
If you are connected to a Microsoft SQL Server database, you cannot insert into a table with a virtual primary key added to a column that has the
Identity
property set. Ensure that this table does not include columns that have this property set.
Example
The following example calls the
LineItemJoinProduct
table in the
Demo
database with 
LineItemId
 defined as the virtual primary key using a GET request in the REST Lab:
http://localhost:8080/rest/default/demo/v1/demo:LineItemJoinProduct/1