Manage Existing Schemas

You can manage the content (tables, fields, and relationships) in a data source by manipulating the data schema, using API Creator or Data Explorer.
You can manage the content (tables, fields, and relationships) in MySQL, PostgreSQL, SQL Server, Oracle, and Derby data sources by manipulating the data schema, using API Creator or Data Explorer. The data source must be marked as editable. The following procedures describe how to manage the content using API Creator.
In API Creator, you manage the schema content in a data source for a database using one of the following methods:
  • By adding tables, columns, and relationships.
  • By using the
    @tables
    , the
    @columns
    , or the
    @relationships
    system REST endpoints.
    For more information about how to manage the content using a system REST endpoint, see System REST Endpoints.
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
Mark a Data Source as Editable
Prerequisite:
You have Data designer role.
For more information about this role, see Database Creation.
Follow these 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 for a database is marked as editable.
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:
The following procedures describe how to manage your tables using API Creator.
Add a Table to your Data Source
  1. 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:
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
You can call tables by way of GET operations. 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