ODataDB Application Settings

ODataDB Application Settings

Table of Contents

Introduction

ODataDB reads the configuration from the JSON configuration files like appsettings.json and appsettings.Production.json.

The appsettings.json file contains settings for all environments. The appsettings.Production.json file contains settings for the Production environment.

The appsettings.json file can be empty with the {} content.

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

See details at Configuration in ASP.NET Core.

Configuration Sample

Below is a sample of the 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 allows enabling host filtering.
See details at Host filtering with ASP.NET Core Kestrel web server.
Use * to serve endpoints for all hosts.
Kestrel
This optional section allows configuring the Kestrel web server.
For example, use this section to define specific endpoint SSL certificates.
See details at Kestrel web server implementation in ASP.NET Core.
Logging
This optional section allows configuring logging features.
See details at Logging Configuration.
ODataDB
This section contains ODataDB settings.
ODataDBW
This section overrides ODataDB settings for the console version built for .NET Framework.
It can contain the WebRoot, IntegratedSecurityEnabled, and StopEnabled settings only.
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: jwt and basic.
The default value is jwt that also allows using the basic authentication.
WebRoot
This value defines the folder with the website contents.
The default value is wwwroot.
ApiRoot
This value defines the API root, like v4 in the /v4/mssql/ endpoint.
The default value is v4.
AppRoot
This value defines the console application management root, like app in the /app/stop URL.
Supported POST commands: stop, hide, and show.
To enable commands, set StopEnabled to true.
AuthRoot
This value defines the root of JWT operations, like auth in the /auth/mssql/login URL.
Supported POST commands: login, logout, refresh, and getToken.
HomeRoot
This value defines the root of home redirect endpoints, like home in the /home/mssql/ endpoint.
ODataDB redirects such URLs to home pages set with the Home value or to the service document if the page is not set.
The default value is home.
EditRoots
This section defines edit roots, like edit in the /edit/mssql/ endpoint, and used HTML pages.
For example above, 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 used HTML pages.
For example above, ODataDB returns the login.htm page content for the /login/mssql/ endpoint.
These endpoints do not require the initial authentication.
The default root is login and the default page is login.htm.
ApplicationPathBase
This value defines the application path base.
Usually, you do not need to change this value.
See details at UsePathBaseExtensions.UsePathBase.
ConnectionSegment
This value defines the order of the API root and connection name segments.
By default, ODataDB allows using endpoints like /v4/mssql/ and /mssql/v4/.
You may set 2 to allow only endpoints with the connection in the second segment like /v4/mssql/ and 1 to allow only the connection in the first segment like /mssql/v4/.
The default value is 0.
DefaultPort
This value defines the default port used when the service URLs are not specified in the configuration, environment variables, or command-line arguments.
The default value is 5002.
DefaultLanguageFolder
This value defines the default language folder to find files when there is no folder of the desired language specified in the URL.
For example above, ODataDB will return pages from the en-us subfolder.
The default value is en-us.
AccessTokenExpirationInMinutes
This value defines a number of minutes of the access token expiration.
The default value is 10 minutes.
RefreshTokenExpirationInMinutes
This value defines a number of minutes of the refresh token expiration.
The default value is 20160 minutes (14 days).
MaxPageSize
This value defines the maximum number of records returned in the response.
UppercaseNamesInLowercase
This value enables converting uppercase object and column names to lowercase.
ODataDB leaves mixed-case names as is.
The default value is true.
DefaultContainerName
This value defines a name of the default entity container and its schema.
The default entity container can be omitted in the URLs.
For the example above, endpoints like /v4/mssql/ and /v4/mssql/default/ use the same model.
The default value is default.
DisableMetadataCache
This value allows disabling the metadata model cache.
Developers can use it to load the model from a database every time during the development phase.
Note that you can use the URL system parameter $reloadMetadata=true or press Ctrl and click the Reload button instead.
The default value is false.
DisableEditPageCache
This value allows disabling the edit page cache.
Developers can use it when developing edit pages.
The default value is false.
IncludeAnnotations
This value enables metadata annotations.
The built-in ODataDB client does not depend on this option.
The default value is true.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects of the SaveToDB Framework.
The ODataDB section contains the default value. You may rewrite it in the connection settings.
The default value 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 such procedures internally to support POST, PUT, and DELETE operations.
You may publish such procedures to call them via POST requests.
The ODataDB section contains the default value. You may rewrite it in the connection settings.
The default value is true.
EnableCodeBrowser
This value enables getting object definitions using the /$definition URL segment.
A user must have the VIEW DEFINITION permission to get the definition.
The default value is false.
BinaryAsHex
This value enables serializing binary data as hex strings by default contrary to base64.
Note that you can use the URL system parameter $binaryAsHex=true instead.
The default value is false.
BigNumbersAsString
This value enables serializing big numbers that lose precision in JavaScript as strings.
Note that you can use the URL system parameter $bigNumbersAsString=true instead.
The default value 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.
So, you may set prefixes directly.
The example above contains 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.
So, you may set suffixes directly.
The example above contains suffixes used in SaveToDB and ODataDB examples.
LanguageParameters
This comma-separated value defines language context parameter names.
ODataDB does not publish such parameters in the model and passes language values automatically.
ODataDB detects the language by the URL segments like /en/, /en-us/, /en-gb/, or /zh-cn/.
ODataDB detects 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 contains language-context parameters used by the SaveToDB add-in.
TraceSQL
This value enables SQL command tracing.
The default value 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 may use these commands when integrating ODataDB into your desktop apps.
You may change the /app root using the AppRoot setting.
The default value is false.
IntegratedSecurityEnabled
This value enables integrated security in the console version built with .NET Framework.
Usually, this version serves the localhost only and started under the current user account.
So, it's safe to allow the integrated security to connect to databases.
The default value is false.

Connection Settings

ProviderName
This required value defines a provider name.
ConnectionString
This required value defines a connection string.
Use a real username and password or the user and pass placeholders.
In the last case, ODataDB requires a username and password when a user connects to the endpoint and replaces placeholders with the actual values.
AllowDatabaseChange
This value allows changing a connection database via the URL in the form like <connection>:<database>.
For example, a user can use the URL like /edit/mssql:AzureDemo100/ to connect to the AzureDemo100 database.
The default value is true for the master, postgres and mysql databases and is false for others.
AllowServerChange
This value allows changing a connection server and database via the URL in the form like <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL like /edit/mssql:mssql.savetodb.com,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server.
The default value is false.
AllowPortChange
This value allows changing a connection port via the URL in the form like <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL like /edit/mssql:mssql.savetodb.com,1433,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server on port 1433.
The default value is false.
Title
This value defines a connection title shown to end users.
The default value is the connection name.
Offline
This value allows disabling the endpoint.
ODataDB immediately returns an offline message without trying to connect to the database.
The default value is false.
Home
This value allows setting the homepage of the connection.
ODataDB redirects to the homepage when a user clicks the Home button.
To open the default page, hold the Ctrl key when clicking the Home button.
You may specify the file name only and place language-specific pages into folders like en-us and zh-hans. ODataDB will return the page depending on the user 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 the view objects only in the service document.
Use this feature to configure the entity container and keep the service root clear.
The default value is null that advertises all select-nature database objects available to the user.
IncludeSchemas
This space-separated value defines an explicit list of schemas to include into the model.
If the value is empty, the model includes the only database for MySQL and all schemas except for specified in the ExcludeSchemas value for other servers.
You may specify * to include all schemas for MySQL.
ExcludeSchemas
This space-separated value defines a list of schemas to not include into the model.
HideSchemas
This space-separated value defines a list of schemas to not advertize the schema objects into in the service document.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects of the SaveToDB Framework.
This value overrides the default value 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 such procedures internally to support POST, PUT, and DELETE operations.
You may publish such procedures to call them via POST requests.
This value overrides the default value defined in the ODataDB section.
OmitSchemasInNames
This space-separated value defines a list of schemas omitted in the 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 the value is empty, ODataDB omits the schema if a database contains a single schema not including the xls schema.
To disable this feature, specify a dummy schema like none.
ApiNameReplacements
This section defines strings to replace in the generated object names.
In the example above, ODataDB replaces usp_buyer_ and usp_seller_ with empty strings.
So, for example, names like usp_buyer_purchases and usp_seller_orders are published as purchases and orders.
SignIn
This value defines a procedure to authenticate users.
ODataDB executes this procedure using the credentials of the auth role of the RoleUsers section.
The procedure must have at least two parameters, for username and password.
Also, the procedure may have parameters defined in the AuthContextValues section.
The procedure must return at least one field defined in the AuthContextParams value, usually a user id.
Also, the procedure may return the role and message fields defined in the SignInRoleField and SignInMessageField values.
If the procedure returns an error, ODataDB tries to authenticate the user using the database login and password.
SignUp
This value defines a procedure to sign-up new users.
Features and requirements are the same as of SignIn.
SignInRoleField
This value defines the name of the result field with the signed user role.
ODataDB uses the role value to load settings from the RoleUsers section.
The default value is role.
SignInMessageField
This value defines the name of the result field with the error message.
If the value is not null, ODataDB raises an exception and returns the message to the user.
The default value is message.
AuthContextValues
This section defines context parameter names and values.
ODataDB does not publish such 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 has the auth_account_id parameter that can be used in stored procedures to process data of the account 1.
AuthContextParams
This value defines parameters populated from the SingIn and SignUp procedures.
ODataDB does not publish such parameters in the model but passes the values to all procedures that have these parameters.
Usually, this is an internal user id.
However, you may define and return any number of parameters.
For example above, the application has two parameters: auth_user_id and auth_seller_id. And procedures may use one of them or both.
RoleUsers
This section contains settings for user roles.
The section must have definitions at least for two built-in roles: auth and default.
ODataDB uses the auth role credentials to execute the SignIn and SignUp procedures.
ODataDB uses the default role credentials to serve authenticated user requests when the SignIn and SignUp procedures do not return the role or the role is not found in the RoleUsers section.
Each role must have at least two values: Username and Password.
Also, the role may contain the QueryList value that defines a view that selects objects to advertize in the service document.
The example above, contains two additional roles: buyer and seller. This allows having different models for each user role.