Value Lists with ODataDB

ODataDB supports several configurations:

ODataDB automatically creates the necessary OData objects.

The built-in JavaScript client and SaveToDB 8+ support these features out of the box.

Consider a database with the following tables:

Database Diagram of Sample 02 - Advanced Features

The s02.cashbook entity type is declared as follows:

<EntityType Name="cashbook">
    <Key>
        <PropertyRef Name="id"/>
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false">
        <Annotation Term="Core.Permission" EnumMember="Core.Permission/Read"/>
    </Property>
    <Property Name="date" Type="Edm.Date" Nullable="false"/>
    <Property Name="account_id" Type="Edm.Int32" Nullable="false"/>
    <Property Name="item_id" Type="Edm.Int32"/>
    <Property Name="company_id" Type="Edm.Int32"/>
    <Property Name="debit" Type="Edm.Decimal"/>
    <Property Name="credit" Type="Edm.Decimal"/>
    <NavigationProperty Name="account" Type="s02.accounts" Partner="cashbooks">
        <ReferentialConstraint Property="account_id" ReferencedProperty="id"/>
    </NavigationProperty>
    <NavigationProperty Name="company" Type="s02.companies" Partner="cashbooks">
        <ReferentialConstraint Property="company_id" ReferencedProperty="id"/>
    </NavigationProperty>
    <NavigationProperty Name="item" Type="s02.items" Partner="cashbooks">
        <ReferentialConstraint Property="item_id" ReferencedProperty="id"/>
    </NavigationProperty>
</EntityType>

Client applications can use NavigationProperties to create lists.

Assuming the xls.handlers table has the following configuration:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
s02	cashbook	account_id	ValidationList	s02	accounts	TABLE	id, +name
s02	cashbook	item_id	ValidationList	s02	items	TABLE	id, +name
s02	cashbook	company_id	ValidationList	s02	companies	TABLE	id, +name

The s02.cashbook entity type will have the following declarations for the account_id, item_id, and company_id columns:

<EntityType Name="cashbook">
    ...
    <Property Name="account_id" Type="Edm.Int32" Nullable="false">
        <Annotation Term="ODataDB.ValueList" Path="default.default/accounts_select_id_name_order_by_name1"/>
    </Property>
    <Property Name="item_id" Type="Edm.Int32">
        <Annotation Term="ODataDB.ValueList" Path="default.default/items_select_id_name_order_by_name1"/>
    </Property>
    <Property Name="company_id" Type="Edm.Int32">
        <Annotation Term="ODataDB.ValueList" Path="default.default/companies_select_id_name_order_by_name1"/>
    </Property>
    ...
</EntityType>

The ODataDB.ValueList annotations point to the declared FunctionImports in the default EntityContainer:

<FunctionImport Name="s02_accounts_select_id_name_order_by_name1" Function="s02.accounts_select_id_name_order_by_name1" EntitySet="s02_accounts"/>
<FunctionImport Name="s02_items_select_id_name_order_by_name1" Function="s02.items_select_id_name_order_by_name1" EntitySet="s02_items"/>
<FunctionImport Name="s02_companies_select_id_name_order_by_name1" Function="s02.companies_select_id_name_order_by_name1" EntitySet="s02_companies"/>

Here are the declared functions in the s02 schema:

<Function Name="accounts_select_id_name_order_by_name1">
    <ReturnType Type="Collection(s02.accounts)"/>
</Function>
<Function Name="items_select_id_name_order_by_name1">
    <ReturnType Type="Collection(s02.items)"/>
</Function>
<Function Name="companies_select_id_name_order_by_name1">
    <ReturnType Type="Collection(s02.companies)"/>
</Function>

Below is a sample configuration for the s02.usp_cashbook procedure, which selects data from the s02.cashbook table and has three parameters: account_id, item_id, and company_id.

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
s02	usp_cashbook	account_id	ParameterValues	s02	accounts	TABLE	id, +name
s02	usp_cashbook	item_id	ParameterValues	s02	items	TABLE	id, +name
s02	usp_cashbook	company_id	ParameterValues	s02	companies	TABLE	id, +name

Here’s the function declaration:

<EntityType Name="usp_cashbook_result" BaseType="s02.cashbook" OpenType="true"/>
<Function Name="usp_cashbook">
    <Parameter Name="account_id" Type="Edm.Int32">
        <Annotation Term="ODataDB.ValueList" Path="default.default/accounts_select_id_name_order_by_name"/>
    </Parameter>
    <Parameter Name="item_id" Type="Edm.Int32">
        <Annotation Term="ODataDB.ValueList" Path="default.default/items_select_id_name_order_by_name"/>
    </Parameter>
    <Parameter Name="company_id" Type="Edm.Int32">
        <Annotation Term="ODataDB.ValueList" Path="default.default/companies_select_id_name_order_by_name"/>
    </Parameter>
    <ReturnType Type="Collection(s02.usp_cashbook_result)"/>
</Function>

This also uses the ODataDB.ValueList annotation to link functions for retrieving parameter values.

You can explore the complete model online:

https://odatadb.savetodb.com/v4/mssql-023/default/en-us/$metadata