ODataDB Application Settings Guide

ODataDB Application Settings Guide

Table of Contents

Introduction

ODataDB reads configuration from JSON files such as appsettings.json and appsettings.Production.json.

  • The appsettings.json file contains settings for all environments.
  • The appsettings.Production.json file is specific to the Production environment.

The appsettings.json file can be empty, represented as {}.

ODataDB also supports configuration via environment variables and command-line options.

For more details, see Configuration in ASP.NET Core.

Configuration Sample

Here’s a sample configuration file:

{
  "AllowedHosts": "*",
  "Kestrel": {
    "EndPoints": {
      "Http": {
        "Url": "http://localhost:5002"
      }
    }
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "ODataDB": {
    "Auth": "jwt",
    "WebRoot": "wwwroot",
    "ApiRoot": "v4",
    "AppRoot": "app",
    "AuthRoot": "auth",
    "HomeRoot": "home",
    "EditRoots": {
        "edit": "edit.htm"
    },
    "LoginRoots": {
        "login": "login.htm"
    },
    "ApplicationPathBase": null,
    "ConnectionSegment": 0,
    "DefaultPort": 5002,
    "DefaultLanguageFolder": "en-us",
    "AccessTokenExpirationInMinutes": 10,
    "RefreshTokenExpirationInMinutes": 20160,
    "MaxPageSize": 1000,
    "UppercaseNamesInLowercase": true,
    "DefaultContainerName": "default",
    "DisableMetadataCache": false,
    "DisableEditPageCache": false,
    "IncludeAnnotations": true,
    "HideConfigurationObjects": true,
    "HideEditProcedures": true,
    "EnableCodeBrowser": true,
    "BinaryAsHex": false,
    "BigNumbersAsString": false,
    "FunctionPrefixes": "xl_validation_list_, xl_parameter_values_",
    "FunctionSuffixes": "_select",
    "LanguageParameters": "data_language, DataLanguage",
    "TraceSQL": true,
    "StopEnabled": false
  },
  "ODataDBW": {
    "WebRoot": "wwwroot",
    "IntegratedSecurityEnabled": false,
    "StopEnabled": true
  },
  "ConnectionStrings": {
    "mssql": {
      "Title": "SQL Server",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=.\\SQLEXPRESS;Initial Catalog=master;User ID=user;Pwd=pass"
    },
    "pgsql": {
      "Title": "PostgreSQL",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "Npgsql",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=postgres"
    },
    "mysql": {
      "Title": "MySQL",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "MySqlConnector",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=mysql"
    },
    "mssql-023": {
      "Offline": false,
      "Home": "sample02.htm",
      "QueryList": "",
      "IncludeSchemas": "s02 xls",
      "ExcludeSchemas": null,
      "HideSchemas": "xls",
      "HideConfigurationObjects": true,
      "HideEditProcedures": true,
      "OmitSchemasInNames": "s02",
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=mssql.savetodb.com;Initial Catalog=AzureDemo100;User ID=sample02_user3;Pwd=Usr_2011#_Xls4168"
    },
    "marketplace": {
      "Title": "Marketplace",
      "Offline": false,
      "OmitSchemasInNames": "marketplace",
      "ApiNameReplacements": {
        "usp_buyer_": "",
        "usp_seller_": ""
      },
      "ProviderName": "MySqlConnector",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=marketplace",
      "SignIn": "marketplace.usp_sign_in",
      "SignUp": "marketplace.usp_sign_up",
      "SignInRoleField": "role",
      "SignInMessageField": "message",
      "AuthContextValues": {
        "auth_account_id": 1
      },
      "AuthContextParams": "auth_user_id auth_seller_id",
      "RoleUsers": {
        "auth": {
          "Username": "marketplace_auth",
          "Password": "Usr_2011#_Xls4168"
        },
        "default": {
          "QueryList": "marketplace.xl_buyer_views",
          "Username": "marketplace_buyer",
          "Password": "Usr_2011#_Xls4168"
        },
        "buyer": {
          "QueryList": "marketplace.xl_buyer_views",
          "Username": "marketplace_buyer",
          "Password": "Usr_2011#_Xls4168"
        },
        "seller": {
          "QueryList": "marketplace.xl_seller_views",
          "Username": "marketplace_seller",
          "Password": "Usr_2011#_Xls4168"
        }
      }
    }
  }
}

Top-level Keys and Sections

AllowedHosts
This optional value enables host filtering.
See Host filtering with ASP.NET Core Kestrel web server for details.
Use * to serve endpoints for all hosts.
Kestrel
This optional section configures the Kestrel web server.
Use it to define specific endpoint SSL certificates.
See Kestrel web server implementation in ASP.NET Core for more information.
Logging
This optional section configures logging features.
Refer to Logging Configuration for details.
ODataDB
This section contains settings specific to ODataDB.
ODataDBW
This section overrides ODataDB settings for the console version built for .NET Framework.
It can only include WebRoot, IntegratedSecurityEnabled, and StopEnabled settings.
ConnectionStrings
This dictionary defines connection names and related properties, including the required connection strings.
ODataDB uses connection names to configure endpoints.
For example, the sample above defines endpoints like /v4/mssql/, /v4/pgsql/, /v4/mysql/, /v4/mssql-023/, and /v4/marketplace/.

Common ODataDB Settings

Auth
This value defines the authentication schema.
Possible values are jwt and basic.
The default is jwt, which also supports basic authentication.
WebRoot
This value specifies the folder containing website content.
The default is wwwroot.
ApiRoot
This value defines the API root, such as v4 in the /v4/mssql/ endpoint.
The default is v4.
AppRoot
This value defines the console application management root, like app in the /app/stop URL.
Supported POST commands include stop, hide, and show.
To enable these commands, set StopEnabled to true.
AuthRoot
This value defines the root for JWT operations, like auth in the /auth/mssql/login URL.
Supported POST commands include login, logout, refresh, and getToken.
HomeRoot
This value defines the root for home redirect endpoints, like home in the /home/mssql/ endpoint.
ODataDB redirects these URLs to home pages set with the Home value or to the service document if the page isn't set.
The default is home.
EditRoots
This section defines edit roots, like edit in the /edit/mssql/ endpoint, and the associated HTML pages.
For example, ODataDB returns the edit.htm page content for the /edit/mssql/ endpoint.
The default root is edit, and the default page is edit.htm.
LoginRoots
This section defines login roots, like login in the /login/mssql/ endpoint, and the associated HTML pages.
ODataDB returns the login.htm page content for the /login/mssql/ endpoint.
These endpoints do not require initial authentication.
The default root is login, and the default page is login.htm.
ApplicationPathBase
This value defines the application path base.
Usually, you don't need to change this value.
See UsePathBaseExtensions.UsePathBase for more information.
ConnectionSegment
This value defines the order of the API root and connection name segments.
By default, ODataDB allows endpoints like /v4/mssql/ and /mssql/v4/.
Set it to 2 to allow only endpoints with the connection in the second segment (e.g., /v4/mssql/), or to 1 to allow only the connection in the first segment (e.g., /mssql/v4/).
The default value is 0.
DefaultPort
This value defines the default port used when service URLs are not specified in the configuration, environment variables, or command-line arguments.
The default is 5002.
DefaultLanguageFolder
This value defines the default language folder for finding files when no specific language folder is specified in the URL.
For example, ODataDB will return pages from the en-us subfolder.
The default is en-us.
AccessTokenExpirationInMinutes
This value defines the expiration time for access tokens in minutes.
The default is 10 minutes.
RefreshTokenExpirationInMinutes
This value defines the expiration time for refresh tokens in minutes.
The default is 20160 minutes (14 days).
MaxPageSize
This value defines the maximum number of records returned in a response.
UppercaseNamesInLowercase
This value enables converting uppercase object and column names to lowercase.
ODataDB leaves mixed-case names unchanged.
The default is true.
DefaultContainerName
This value defines the name of the default entity container and its schema.
The default entity container can be omitted in URLs.
For example, endpoints like /v4/mssql/ and /v4/mssql/default/ use the same model.
The default is default.
DisableMetadataCache
This value disables the metadata model cache.
Developers can use it to load the model from a database every time during development.
Alternatively, you can use the URL system parameter $reloadMetadata=true or press Ctrl and click the Reload button.
The default is false.
DisableEditPageCache
This value disables the edit page cache.
Developers can use it when developing edit pages.
The default is false.
IncludeAnnotations
This value enables metadata annotations.
The built-in ODataDB client does not depend on this option.
The default is true.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects from the SaveToDB Framework.
The ODataDB section contains the default value, which can be overridden in connection settings.
The default is true.
HideEditProcedures
This value hides edit procedures like usp_cashbook2_insert, usp_cashbook2_update, and usp_cashbook2_delete of the usp_cashbook2 procedure.
ODataDB uses these procedures internally for POST, PUT, and DELETE operations.
You can publish these procedures to call them via POST requests.
The default is true.
EnableCodeBrowser
This value enables retrieving object definitions using the /$definition URL segment.
A user must have the VIEW DEFINITION permission to access the definition.
The default is false.
BinaryAsHex
This value enables serializing binary data as hex strings by default, instead of base64.
You can also use the URL system parameter $binaryAsHex=true.
The default is false.
BigNumbersAsString
This value enables serializing large numbers that lose precision in JavaScript as strings.
You can also use the URL system parameter $bigNumbersAsString=true.
The default is false.
FunctionPrefixes
This comma-separated value defines prefixes of stored procedure names to assign the function type instead of the default action type.
For example, if ODataDB cannot detect the select nature of the xl_list_users procedure, it assigns the action type.
You can set prefixes directly.
The example above includes prefixes used in SaveToDB and ODataDB examples.
FunctionSuffixes
This comma-separated value defines suffixes of stored procedure names to assign the function type instead of the default action type.
For example, if ODataDB cannot detect the select nature of the usp_users_select procedure, it assigns the action type.
You can set suffixes directly.
The example above includes suffixes used in SaveToDB and ODataDB examples.
LanguageParameters
This comma-separated value defines language context parameter names.
ODataDB does not publish these parameters in the model and automatically passes language values.
ODataDB detects the language from URL segments like /en/, /en-us/, /en-gb/, or /zh-cn/.
It uses configured translations in databases and passes existing code language values if possible, or generic code languages if not.
For example, it may pass en for /en-gb/ and zh-hans for /zh-cn/.
The example above includes language-context parameters used by the SaveToDB add-in.
TraceSQL
This value enables SQL command tracing.
The default is false.
StopEnabled
This value allows stopping, showing, or hiding the console application using the /app/stop, /app/show, and /app/hide POST requests.
You can use these commands when integrating ODataDB into your desktop apps.
You can change the /app root using the AppRoot setting.
The default is false.
IntegratedSecurityEnabled
This value enables integrated security in the console version built with .NET Framework.
Typically, this version serves localhost only and runs under the current user account, making it safe to allow integrated security for database connections.
The default is false.

Connection Settings

ProviderName
This required value defines the provider name.
ConnectionString
This required value defines the connection string.
Use real credentials or the user and pass placeholders.
If using placeholders, ODataDB requires a username and password when a user connects to the endpoint, replacing placeholders with actual values.
AllowDatabaseChange
This value allows changing the connection database via the URL in the format <connection>:<database>.
For example, a user can use the URL /edit/mssql:AzureDemo100/ to connect to the AzureDemo100 database.
The default is true for the master, postgres, and mysql databases, and false for others.
AllowServerChange
This value allows changing the connection server and database via the URL in the format <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL /edit/mssql:mssql.savetodb.com,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server.
The default is false.
AllowPortChange
This value allows changing the connection port via the URL in the format <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL /edit/mssql:mssql.savetodb.com,1433,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server on port 1433.
The default is false.
Title
This value defines a connection title displayed to end users.
The default is the connection name.
Offline
This value disables the endpoint.
ODataDB immediately returns an offline message without attempting to connect to the database.
The default is false.
Home
This value sets the homepage of the connection.
ODataDB redirects to this homepage when a user clicks the Home button.
To open the default page, hold the Ctrl key while clicking the Home button.
You can specify only the file name and place language-specific pages in folders like en-us and zh-hans. ODataDB will return the appropriate page based on the user’s language.
In the example above, the connection mssql-023 uses sample02.htm as the homepage.
QueryList
This value defines a view in the SaveToDB QueryList format to advertise view objects only in the service document.
Use this feature to configure the entity container and keep the service root clear.
The default is null, which advertises all select-nature database objects available to the user.
IncludeSchemas
This space-separated value defines an explicit list of schemas to include in the model.
If empty, the model includes only the database for MySQL and all schemas except those specified in the ExcludeSchemas value for other servers.
You can specify * to include all schemas for MySQL.
ExcludeSchemas
This space-separated value defines a list of schemas to exclude from the model.
HideSchemas
This space-separated value defines a list of schemas whose objects will not be advertised in the service document.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects from the SaveToDB Framework.
This value overrides the default defined in the ODataDB section.
HideEditProcedures
This value hides edit procedures like usp_cashbook2_insert, usp_cashbook2_update, and usp_cashbook2_delete of the usp_cashbook2 procedure.
ODataDB uses these procedures internally for POST, PUT, and DELETE operations.
You can publish these procedures to call them via POST requests.
This value overrides the default defined in the ODataDB section.
OmitSchemasInNames
This space-separated value defines a list of schemas omitted from service object names.
For example, database objects like s02.cashbook or s02.usp_cashbook are published by default as s02_cashbook and s02_usp_cashbook.
In the example above, the schema s02 is omitted, so database objects are published as cashbook and usp_cashbook.
If empty, ODataDB omits the schema if a database contains a single schema that does not include the xls schema.
To disable this feature, specify a dummy schema like none.
ApiNameReplacements
This section defines strings to replace in generated object names.
In the example above, ODataDB replaces usp_buyer_ and usp_seller_ with empty strings.
As a result, names like usp_buyer_purchases and usp_seller_orders are published as purchases and orders.
SignIn
This value defines a procedure for authenticating users.
ODataDB executes this procedure using the credentials of the auth role from the RoleUsers section.
The procedure must have at least two parameters for the username and password.
It may also include parameters defined in the AuthContextValues section.
The procedure must return at least one field defined in the AuthContextParams value, typically a user ID.
It may also return the role and message fields defined in the SignInRoleField and SignInMessageField values.
If the procedure returns an error, ODataDB attempts to authenticate the user using the database login and password.
SignUp
This value defines a procedure for signing up new users.
The features and requirements are the same as for SignIn.
SignInRoleField
This value defines the name of the result field containing the signed-in user's role.
ODataDB uses this role value to load settings from the RoleUsers section.
The default is role.
SignInMessageField
This value defines the name of the result field containing the error message.
If not null, ODataDB raises an exception and returns the message to the user.
The default is message.
AuthContextValues
This section defines context parameter names and values.
ODataDB does not publish these parameters in the model but passes the defined values to all procedures that have these parameters.
This feature is useful for multi-tenant applications.
The example above includes the auth_account_id parameter, which can be used in stored procedures to process data for account 1.
AuthContextParams
This value defines parameters populated from the SignIn and SignUp procedures.
ODataDB does not publish these parameters in the model but passes the values to all procedures that have these parameters.
Typically, this is an internal user ID, but you can define and return any number of parameters.
In the example above, the application has two parameters: auth_user_id and auth_seller_id, which procedures may use individually or together.
RoleUsers
This section contains settings for user roles.
It must define at least two built-in roles: auth and default.
ODataDB uses the auth role credentials to execute the SignIn and SignUp procedures.
It uses the default role credentials to serve authenticated user requests when the SignIn and SignUp procedures do not return a role or the role is not found in the RoleUsers section.
Each role must have at least two values: Username and Password.
Additionally, the role may include the QueryList value, which defines a view that selects objects to advertise in the service document.
The example above includes two additional roles: buyer and seller, allowing different models for each user role.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.