Hello and welcome to dbiScript!

dbiScript is the world's first Relational Interface Development Language (RIDL). dbiScript is both a programming language and a Rapid Application Development (RAD) tool in that it enables you, the developer, to rapidly create a full-blown database application. It does this by creating a browser-based interface to your database on-the-fly from instructions you write in the dbiScript language. A simple text editor is all you need to write your dbiScript code. This guide provides detailed instructions on how to set up your development environment and how to write your dbiScript code.

Sample Applications

The download (3.9 MB) contains two sample dbiScript sample applications including databases and easy setup instructions. The Clinic sample application includes databases for Oracle, MySQL, SQL Server, DB2, PostgreSQL, SQL Anywhere, and MS Access. The Northwind application includes an MS Access database.

Relational Interface

In relational database theory, a relationship between two tables is created by including a column (field) within the "child" table to store the primary key (or other candidate key) of its "parent" table. This column is referred to as the foreign key of the parent table.

In a relational interface, the organization of the graphical user interface (GUI) reflects the established relationships within the database. A relational interface development language is a programming language that enables the programmer to create a relational interface.

GUI Organization

The graphical user interface which dbiScript creates is organized into levels. Each level consists of a single page frame. The tables which are accessible at the page frame's level are organized into the tabs of the page frame. The tables included in the topmost level are completely independent. Tables in subsequent levels are dependent on the tables of the previous levels. The placement of a table within the GUI is dependent upon the type of relationship established with its parent(s) as defined in in the figure below. Child tables of relationship types S3 – S8, M3 and M5 are directly accessible via the tabs of the page frame whereas child tables of relationship types S1 and S2 are accessible through their parent row. Child rows of relationship type M1 are accessible through the parent row of the last parent to be selected. Selecting a row in any table will display beneath that row any existing child rows of relationship types S1 and S2 along with any existing child rows of type M1 where the other parent rows have been selected in a higher level. Child rows of relationship types S1 and S2 are edited in conjunction with their parent row whereas child rows of relationship type M1 can be edited independently or in conjunction with the last parent row to be selected.

Tables are composed of inline fields and / or drilldown fields. Inline fields are displayed in the table's inline rows while the drilldown fields are displayed in the drilldown row which only appears beneath an inline row when it's selected or in edit mode. Independent tables and child tables of relationship types S3 – S8, M3 and M5 are required to have at least one inline field and can optionally have drilldown fields. Child tables of S1, S2 and M1 relationships have only drilldown fields.

Compatibility

Browser Compatibility

dbiScript is compatible with these browsers:

  • Google Chrome
  • Apple Safari
  • Mozilla Firefox
  • Opera
  • Microsoft Internet Explorer 9+

Photo Uploads

dbiScript generally behaves identically in all supported browsers The most significant difference between the browsers is in the handling of file uploads. If your application uses the dbiScript photo album (see .appendPhotoAlbum), the photo uploading experience is dependent on the browser's file upload capabilities.

Google Chrome, Apple Safari, Mozilla Firefox and Internet Explorer 10+ all support the ability to drag & drop files into the browser. This means the user can select a group of files via the OS-based file manager (i.e. Windows Explorer, Apple Finder) and then drag & drop them to the dbiScript photo upload window. Most users will find this to be the easiest path to uploading the files they want. Another advantage of the drag & drop support is that files on the OS desktop itself can be directly moved to the dbiScript photo upload.

For users more comfortable with the browser's built-in file selection tool, Google Chrome, Apple Safari, Mozilla Firefox, Opera and Internet Explorer 10+ all support the ability to select multiple files.

Internet Explorer 9 neither supports drag & drop nor does it support multiple file selections. Users running dbiScript on Internet Explorer 9 will need to select files one at time for upload.

Database Compatibility

dbiScript is compatible with these databases:

  • MySQL
  • Oracle
  • IBM DB2
  • Microsoft SQL Server
  • Microsoft Access
  • PostgreSQL
  • Sybase SQL Anywhere

Theory

Entity-Relationships

In database theory there are 16 different types of relationships which can be established between tables. dbiScript supports all relationship types in which the child table has only one parent table. In the case of a child table with multiple parent tables, dbiScript supports the three relationship types which do not specify a minimum number of child rows for each unique combination of parent rows.

Relationship types S2, S4, S6, S7 and S8 are supported by ensuring that new parent rows are not created without also creating the specified minimum number of required child rows.

In order to support relationship types M2, M4. M6, M7 and M8, any new row created in parent table A would require a new child row (X new child rows for cases M7 and M8) in table C for every existing row in parent table B. All the new required C rows would have to be created before the new A row could be saved. These theoretical relationships are not practical and are therefore not supported in dbiScript.

The 16 Types Of Theoretical Relationships In A Relational Database

The 16 Types Of Theoretical Relationships In A Relational Database

Relationship Types

Single-Parent Relationships

The child table in an S1 – S8 relation has a single parent table.

S1

Each row in the parent table has 0 or 1 row in the child table.

Tables with this type of relationship can be used to create optional groups of data within rows.

S2

Each parent row has 1 row in the child table.

Tables with this type of relationship can be used to create physically separate groups of data within rows.

S3

Each parent row has 0 or more rows in the child table.

S4

Each parent row has 1 or more rows in the child table.

S5

Each parent row has between 0 and Y rows in the child table, where Y > 1.

S6

Each parent row has between 1 and Y rows in the child table, where Y > 1.

S7

Each parent row has between X and Y rows in the child table, where X > 1 and Y >= X.

S8

Each parent row has X or more rows in the child table, where X > 1.

Multiple-Parent Relationships

The child table in an M1 – M8 relationship has two or more parent tables.

M1

Each unique combination of parent rows has 0 or 1 rows in the child table.

M2

Each unique combination of parent rows has 1 row in the child table. This type of relationship is purely theoretical and not supported in dbiScript.

M3

Each unique combination of parent rows has 0 or more rows in the child table.

M4

Each unique combination of parent rows has 1 or more rows in the child table. This type of relationship is purely theoretical and not supported in dbiScript.

M5

Each unique combination of parent rows has between 0 and Y rows in the child table, where Y > 1.

M6

Each unique combination of parent rows has between 1 and Y rows in the child table, where Y > 1. This type of relationship is purely theoretical and not supported in dbiScript.

M7

Each unique combination of parent rows has between X and Y rows in the child table, where X > 1 and Y >= X. This type of relationship is purely theoretical and not supported in dbiScript.

M8

Each unique combination of parent rows has X or more rows in the child table, where X > 1. This type of relationship is purely theoretical and not supported in dbiScript.

Nomenclature

For the purposes of this documentation, an S1 table is defined as the child table of an S1 relationship, and so forth.

Fields

From Wikipedia:

In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed.

The term field is often used interchangeably with column, although many consider it more correct to use field (or field value) to refer specifically to the single item that exists at the intersection between one row and one column.

In dbiScript, a data table's inline rows are organized into columns of data. However, since each data table may include drilldown rows where the data are not organized into columns, the term "column" would not be an accurate term to use for the constituent parts of a dbiScript row. For this reason, a dbiScript field refers to the GUI representation of each database column. Within this documentation, "column" refers to a database column, "field value" refers to the single item that exists at the intersection between one row and one column and "field" refers to a dbiScript field.

Primary Keys

All tables in a dbiScript application must have a primary key consisting of only one field.

A surrogate primary key in the form of an auto-generated integer column is required for most tables. S1 and S2 tables are the only exception to this rule; the primary key of these types of tables should be the foreign key, although the surrogate key is permissible.

To ensure normalization, dbiScript also requires at least one unique simple or composite key in each table.

Conversion from Candidate Keys to Surrogate Keys

  • Always backup your database before issuing ALTER or UPDATE SQL

If you're working with an existing database that does not use auto-generated numeric columns for primary keys, it is a straightforward process to convert the database. As an example, we'll consider the case of a database with a customer table and purchase table which are related as illustrated below.

Customer - Purchase Entity-Relationship Diagram

Customer - Purchase Entity-Relationship Diagram

Existing Database – Candidate Keys

customer
ssnlnamefnamemi
595-12-5274MurphyDennisM
734-08-9267O'BrienJohnK
773-82-0167FlanneryHelenJ
purchase
ssntrandatestatus
595-12-52742009-03-270
595-12-52742009-06-291
773-82-01672009-04-081

dbiScript-Ready Database – Surrogate Keys

customer
pidssnlnamefnamemi
1595-12-5274MurphyDennisM
2734-08-9267O'BrienJohnK
3773-82-0167FlanneryHelenJ
purchase
pidcustomeridtrandatestatus
112009-03-270
212009-06-291
332009-04-081

Step by Step SQL

Oracle

ALTER TABLE customer ADD pid INT
UPDATE customer SET pid = ROWNUM
ALTER TABLE customer DROP PRIMARY KEY
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY (pid)
ALTER TABLE purchase ADD pid INT
ALTER TABLE purchase ADD customerid INT
UPDATE purchase SET pid = ROWNUM
UPDATE purchase SET customerid = (SELECT pid FROM customer WHERE customer.ssn = purchase.ssn)
ALTER TABLE purchase DROP PRIMARY KEY
ALTER TABLE purchase ADD CONSTRAINT order_pk PRIMARY KEY (pid)
ALTER TABLE purchase DROP COLUMN ssn

DB2

ALTER TABLE customer ADD pid INT NOT NULL WITH DEFAULT 0
ALTER TABLE customer ALTER pid DROP DEFAULT SET GENERATED ALWAYS AS IDENTITY
UPDATE customer SET pid = DEFAULT
ALTER TABLE customer DROP PRIMARY KEY
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY (pid)
ALTER TABLE purchase ADD pid INT NOT NULL WITH DEFAULT 0
ALTER TABLE purchase ALTER pid DROP DEFAULT SET GENERATED ALWAYS AS IDENTITY
UPDATE purchase SET pid = DEFAULT
ALTER TABLE purchase ADD customerid INT
UPDATE purchase SET customerid = (SELECT pid FROM customer WHERE customer.ssn = purchase.ssn)
ALTER TABLE purchase DROP PRIMARY KEY
ALTER TABLE purchase ADD CONSTRAINT order_pk PRIMARY KEY (pid)
ALTER TABLE purchase DROP COLUMN ssn

SQL Server

SQL Server does not support the ALTER TABLE DROP PRIMARY KEY syntax. We'll create a stored procedure to do this work.

CREATE PROCEDURE dbo.DropPrimaryKey
@tableName VARCHAR(255)
AS
DECLARE @pkName VARCHAR(255)
SET @pkName = (SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID(N'[dbo].['+@tableName+N']'))
EXEC('ALTER TABLE ['+@tableName+'] DROP CONSTRAINT ['+@pkName+']')
GO
dbo.DropPrimaryKey('customer')
ALTER TABLE customer ADD pid BIGINT IDENTITY PRIMARY KEY
dbo.DropPrimaryKey('purchase')
ALTER TABLE purchase ADD pid BIGINT IDENTITY PRIMARY KEY
ALTER TABLE purchase ADD customerid INT
UPDATE purchase SET customerid = customer.pid FROM customer INNER JOIN purchase ON customer.ssn = purchase.ssn
ALTER TABLE purchase DROP COLUMN ssn

Access

In the table design view, right-click in the ssn row and select Insert Rows.

Inserting a New Row in Access

Inserting a New Row in Access

Enter pid as the Field Name, select AutoNumber as the Data Type then right-click in the pid row, select Primary Key and save your changes.

Setting the Primary Key in Access

Setting the Primary Key in Access

Repeat the process for the purchase table, also adding the customerid column as data type Number.

Start a new query, right-click anywhere on the background and select SQL View.

Creating an Update Query in Access

Creating an Update Query in Access

Type in and run the following SQL:

UPDATE purchase a INNER JOIN customer b ON a.ssn = b.ssn SET a.customerid = b.pid

Remove the ssn column from purchase table.

MySQL

ALTER TABLE customer DROP PRIMARY KEY
ALTER TABLE customer ADD pid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE purchase DROP PRIMARY KEY
ALTER TABLE purchase ADD pid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE purchase ADD customerid BIGINT
UPDATE purchase SET customerid = (SELECT pid FROM customer WHERE customer.ssn = purchase.ssn)
ALTER TABLE purchase DROP COLUMN ssn

PostgreSQL

PostgreSQL does not support the ALTER TABLE DROP PRIMARY KEY syntax. We'll create a stored procedure to do this work.

CREATE FUNCTION DropPrimaryKey(tableName VARCHAR(255)) RETURNS int
AS
'DECLARE pkName information_schema.table_constraints.constraint_name%TYPE;
BEGIN
SELECT constraint_name INTO pkName FROM information_schema.table_constraints WHERE table_name = tableName and constraint_type = ''PRIMARY KEY'';
EXECUTE ''ALTER TABLE "''||tableName||''" DROP CONSTRAINT "''||pkName||''"'';
RETURN 0;
END;' LANGUAGE plpgsql;
SELECT DropPrimaryKey('customer')
ALTER TABLE customer ADD pid BIGSERIAL PRIMARY KEY
SELECT DropPrimaryKey('purchase')
ALTER TABLE purchase ADD pid BIGSERIAL PRIMARY KEY
ALTER TABLE purchase ADD customerid BIGINT
UPDATE purchase SET customerid = (SELECT pid FROM customer WHERE customer.ssn = purchase.ssn)
ALTER TABLE purchase DROP COLUMN ssn

SQL Anywhere

ALTER TABLE customer DROP PRIMARY KEY
ALTER TABLE customer ADD pid UNSIGNED BIGINT IDENTITY NOT NULL
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY (pid)
ALTER TABLE purchase DROP PRIMARY KEY
ALTER TABLE purchase ADD pid UNSIGNED BIGINT IDENTITY NOT NULL
ALTER TABLE purchase ADD CONSTRAINT purchase_pk PRIMARY KEY (pid)
ALTER TABLE purchase ADD customerid BIGINT
UPDATE purchase SET customerid = (SELECT pid FROM customer WHERE customer.ssn = purchase.ssn)
ALTER TABLE purchase DROP ssn

dbiScript Application Development

Naming Conventions

Table Names

It is suggested that you use the singular form when naming your tables (i.e. customer instead of customers).

Table names are case insensitive. If your database supports case sensitive table names, you will need to ensure no two tables have the same name when compared case insensitively.

Primary Keys

It is suggested that you use 'pid' (short for primary id) as the name of your primary key in each table. If you follow this naming convention, within dbiScript you do not need to specify the name of the primary key of each table as the default is 'pid'. If you prefer another name, you can specify a default name or individually specify each table's primary key.

Foreign Keys

It is suggested that you use the parent table's name followed by 'id' as the name of each foreign key. For example, in the purchase table of database discussed earlier we used customerid as the foreign key to customer.

S1 and S2 Tables

In S1 and S2 tables, there is one foreign key which can also be the primary key. It is suggested that the foreign key naming convention be used for this field. If the primary key is the foreign key, it is not necessary to specify the .primaryKey table property (primary key field name) – dbiScript will set it to the foreign key.

Variable Names

  • Use only variable names that begin with a lowercase letter.

All one, two and three letter variable names which begin with an uppercase letter are reserved for use by dbiScript. As dbiScript is written in JavaScript, it is not possible to prevent reuse of a variable; using a reserved variable name in your script will result in difficult to debug errors.

While variable names which begin with an uppercase letter and are four or more letters long are not reserved, it is suggested that the convention of using only variable names which begin with a lowercase letter be used throughout your dbiScript application.

Reserved Browser Functions

The following functions are reserved by dbiScript:

  • window.ondragover
  • window.ondrop
  • window.onerror
  • window.onkeydown (all browsers except Internet Explorer)
  • document.onkeydown (Internet Explorer only)
  • document.onclick
  • document.onmousedown
  • document.oncontextmenu

The values returned by these functions are important for the proper operation of dbiScript. Do not overwrite these functions in your application. If your application requires functionality for any of these methods, use the JavaScript addEventListener method to attach the required functionality.

Audit Columns

Whenever the database is rebuilt, the audit columns will automatically be added to any database tables which are missing them, unless the inherited [table element].dbiScriptProperties.auditColumns.create is false. The names and data types of the audit columns are listed in the table below.

Whenever a row is created or updated, dbiScript will automatically maintain the audit fields, if they exist.

Data ProviderDescriptionColumn NameData Type
Access
MySQL
SQL Server
Date and time the row was created.create_datetimeDATETIME
ID of the user who created the row.create_useridVARCHAR(50)
Date and time the row was last modified.update_datetimeDATETIME
ID of the user who made the last modification.update_useridVARCHAR(50)
DB2
SQL Anywhere
PostgreSQL
Date and time the row was created.create_timestampTIMESTAMP
ID of the user who created the row.create_useridVARCHAR(50)
Date and time the row was last modified.update_timestampTIMESTAMP
ID of the user who made the last modification.update_useridVARCHAR(50)
OracleDate and time the row was created.create_timestampTIMESTAMP
ID of the user who created the row.create_useridVARCHAR2(50)
Date and time the row was last modified.update_timestampTIMESTAMP
ID of the user who made the last modification.update_useridVARCHAR2(50)

Directory Structure

It is suggested that you follow the directory structure illustrated below when developing in dbiScript. Place the Cyberaxiom.dbiScript.dll file in the bin folder and the dbiScript.js file in the apps folder.

Recommended dbiScript Directory Structure

Recommended dbiScript Directory Structure

The application folders (app1, etc.) should be renamed. Use names which are more meaningful to your specific dbiScript application.

License Location

Your application's license must be stored in its application folder. For example, given the directory structure illustrated above, the license for app1 would be stored in the app1 folder. A license is not required until you are ready to deploy your dbiScript application. To purchase a license for your application, please visit dbiScript.com/license.htm.

HTML Structure

Create a text file named index.htm in your application folder. Paste the following HTML into that file:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>My dbiScript Application</title>
<link rel="shortcut icon" href="../images/favicon.ico">
<style>
body, td, input, select, textarea {font: 10pt Verdana, Arial, Helvetica, sans-serif}
</style>
</head>
<body style='background:url(../images/bg0.gif)'>
</body>
<script type="text/javascript">

function dbiScriptApplication(){

app.dbiScriptProperties.dataConfig = "../data/dbiScript.cfg"

// you must specify dataConfig – see page 25 for more information.
// continue your dbiScript code here

}

</script>
<script src="../dbiScript.js" type="text/javascript"></script>
</html>

Alternatively, you can place your dbiScript code in a separate file and link to that file in the first script tag.

Application Name

The application name is inherited from the title of your application's html document (i.e. "My dbiScript Application" in the above HTML document). The application name is displayed at the top of the login page and should be kept short. The maximum length of the application name is 50 characters.

Security Concerns

The data folder is the recommended location of your dbiScript configuration file, database file (for MS Access and Sybase SQL Anywhere databases) and also the parent folder for all your application's images (via [dataTable object].appendPhotoAlbum) and files (via [dataTable object].appendFileVault).

Securing the Data Folder via Windows Security Settings

The configuration file contains sensitive information and as such it must not be accessible via the internet. By default, IIS prevents internet access to many sensitive files (i.e. ".cfg", ".db" & ".mdb" files), however it is strongly recommended to ensure that internet users do not have direct access to these files by properly configuring Windows Security Permissions on the data folder for the IUSR account. Additionally, the data folder requires modify & write permissions for the Windows .NET user account (IIS_IUSRS) when the folder contains a database for either Microsoft Access or Sybase SQL Anywhere or when the folder is specified as location for your application's images and files (for PhotoAlbum and FileVault fields).

Follow these instructions to properly configure permissions for the data folder:

  1. Navigate to the data folder in either Windows Explorer or Internet Information Services (IIS).
  2. Right-click the data folder and select Properties (in Windows Explorer) or Edit Permissions… (in IIS).
  3. Click the Security tab.
  4. Click the Edit… button.
  5. If IUSR is not in the list, click the Add… button, type IUSR into the box and then click the OK button.
  6. Select the IUSR user.
  7. Check the Read & Execute checkbox in the Deny column.
  8. Ensure that Windows automatically (in the last step) checked the List folder contents and Read checkboxes in the Deny column.
  9. If IIS_IUSRS is not in the list, click the Add… button, type IIS_IUSRS into the box and then click the OK button.
  10. Select the IIS_IUSRS user.
  11. Check the Modify checkbox in the Allow column.
  12. Ensure that Windows automatically (in the last step) checked the Write checkbox in the Allow column.
  13. Click the OK button to save the changes.

The following images illustrate the proper permissions settings of the IUSR and IIS_IUSRS accounts:

Recommended Windows Security Permissions for the Data Folder

Recommended Windows Security Permissions for the Data FolderRecommended Windows Security Permissions for the Data Folder

Securing the Data Folder via web.config

Alternatively, the data folder may be secured by modifying your site's web.config file. There are several ways to accomplish this. The two options listed below produce similar results.

Register an HttpForbiddenHandler

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<handlers>
<add name="Deny" verb="*" path="data/*" type="System.Web.HttpForbiddenHandler" />
</handlers>
</system.webServer>
</configuration>
This option will result in a 403 (Forbidden) status code.

Specify a <location> Element

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<location path="data">
<system.webServer>
<security>
<authorization>
<remove users="*" roles="" verbs="" />
<add accessType="Allow" roles="Administrators" />
</authorization>
</security>
</system.webServer>
</location>
</configuration>

This option will result in a 401 (Unauthorized) status code. Users will be given the opportunity to enter valid credentials. This may be desirable in certain circumstances, however it is generally preferable to provide authorized access to files in the data directory through other methods.

Securing the Application License

It is recommended that your dbiScript license also be secured. The application license is a file named "license.htm" placed in the application folder.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<handlers>
<add name="Deny1" verb="*" path="data/*" type="System.Web.HttpForbiddenHandler" />
<add name="Deny2" verb="*" path="apps/myapp/license.htm" type="System.Web.HttpForbiddenHandler" />
</handlers>
</system.webServer>
</configuration>

Testing Security Settings

It is critical to test your security settings before deploying your application. Enter the URL of your configuration file into your browser and ensure that you receive an error page. If the file is downloaded then you need to adjust your security settings.

  • Ensure that your configuration file cannot be downloaded directly through the browser.

Data Configuration File

The data configuration file is a small text file that specifies the type of database, connection information and other low-level dbiScript settings.

Provider-Specific Examples

Create a text file in the dataConfig folder. The suggested naming convention is [application name].cfg, e.g., app1.cfg. Paste the text from the appropriate example into the file, replace the colored text with the appropriate information for your database and save the file. Within the configuration file, the [ ' ] (apostrophe) character is reserved for comments – if an apostrophe appears in a line of the file, everything after the apostrophe on that line will be ignored. Note that the listed connection strings are intended to provide basic connectivity and are by no means an exhaustive discussion of connection strings. For more information on connection strings, consult your database documentation or Google "oracle connection string" (for example).

The configuration files do not support line continuations. In the examples provided below, some of the lines have been formatted onto multiple lines on order to fit in this document. Within your configuration file, each parameter setting should appear on a single line.

Access

Access 2007 & Access 2010

dbProvider = MSAccess
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=../data/dbname.mdb"

When specifying a path for Access 2007 or Access 2003 either "/" or "\" can be used as the directory separator. Since the text of the dataConfig is interpreted as a Visual Basic string, the backslash ("\") character can be specified without the extra "\" needed when including a backslash in a JavaScript string.

The specified path can be either absolute (c:/etc) or relative (../etc). Relative paths are relative to the location of the dbiScript.js file. The illustrated "../data/" directory follows the recommended directory structure.

Access 2003

dbProvider = MSAccess
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=../data/dbname.accdb"

DB2

dbProvider = DB2
connectionString = "Server=serverAddress:portNumber; Database=dbname; UID=userid; PWD=password"

MySQL

dbProvider = MySQL
connectionString = "Data Source= serverAddress; Database=dbname; User Id=userid; Password=password"

The fully-managed .NET driver for MySQL can be found at dev.mysql.com/downloads/connector/net/.

Oracle

dbProvider = Oracle
connectionString = "Data Source= serverAddress; User Id=userid; Password=password"

PostgreSQL

dbProvider = PostgreSQL
connectionString = " Server= serverAddress; Port=5432; Database=dbname; UID=userid; PWD=password"

The fully-managed .NET driver for PostgreSQL can be found at npgsql.projects.pgfoundry.org/.

SQL Anywhere

dbProvider = SQLAnywhere
connectionString = "DBF=../data/dbname.db; UID=userid; PWD=password"

The specified path can be either absolute (c:/etc) or relative (../etc). Relative paths are relative to the location of the dbiScript.js file. The illustrated "../data/" directory follows the recommended directory structure.

SQL Server

dbProvider = SQLServer
connectionString = "Server=serverAddress; Initial Catalog=catalogname; User Id=userid; Password=password"

Server authentication must be set to "SQL Server and Windows Authentication mode". The following MSDN article provides instructions on how to change the SQL Server authentication mode: msdn.microsoft.com/en-us/library/ms188670.aspx.

Connection Strings

The basic formatting rules for connection strings are (Lurade):

  • All blank characters, except those placed within a value or within quotation marks, are ignored
  • Blank characters will though affect connection pooling mechanism, pooled connections must have the EXACT same connection string
  • If a semicolon (;) is part of a value it must be delimited by quotation marks (")
  • Use a single-quote (') if the value begins with a double-quote (")
  • Conversely, use the double quote (") if the value begins with a single quote (')
  • No escape sequences are supported
  • The value type is NOT relevant
  • Names are case iNsEnSiTiVe
  • If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the LAST occurrence is used
  • But!... if the PROVIDER keyword occurs multiple times in the string, the FIRST occurrence is used.
  • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.

An excellent resource on Connection Strings is provided by ConnectionStrings.com

Development Mode

In application development mode, the application login is suppressed. Development mode is automatically enabled if there are no rows in the dbiUserAccounts table or if there are no users with administrative rights.

Additionally, database metadata is not cached while development mode is enabled. In normal operation (non-development mode), database metadata is cached to increase performance.

  • In normal operation (non-development mode) database metadata is cached and any changes to the structure of your database will not be visible to your dbiScript application until the webserver is reset.

To manually enable application development mode, add the following line anywhere in the configuration file:

enableDevelopmentMode = True
  • Do not deploy your application with enableDevelopmentMode = True in your configuration file.

Development mode should only be enabled during application development. For security purposes, either remove the enableDevelopmentMode = True line from your configuration file or set enableDevelopmentMode to False prior to deployment.

Development access is limited to a single user. If two or more users attempt to access the application while in development mode, the earlier users will be logged out.

Disable Cache

It is critical to disable the cache if any third-party applications are configured to update your database.

Likewise, when deployed in a multi-server environment, cache must be disabled. In a single-server environment, internal caching of the data improves performance. In multi-server environments, a data row cached on server A will become outdated when the same row is updated by server B.

If your dbiScript application resides on a single server and no third-party applications will update the database then leave the cache enabled. The cache provides a noticeable performance boost.

  • It is critical to disable the cache in multi-server / third-party update environments.

To disable the cache, add the following line anywhere in the configuration file:

disableCache = True

Provider Factory

If you wish to use a non-standard provider factory, add the following line anywhere in the configuration file:

providerFactory = "MyProviderFactory"

Query String Login

To enable login via query string, add the following line anywhere in the configuration file:

enableQueryStringLogin = True
  • Enabling login via query string is a major security risk. Use at your own risk.

This feature is discussed in depth in the next chapter.

Rebuild Database

If you are building your database through dbiScript, permission to rebuild the database must be granted in the configuration file. To grant rebuild permission, add the following line anywhere in the configuration file:

allowRebuildDatabase = True
  • Do not deploy your application with allowRebuildDatabase = True in your configuration file.

Permission to rebuild the database should only be granted during application development. For security purposes, either remove the allowRebuildDatabase = True line from your configuration file or set allowRebuildDatabase to False prior to deployment.

Query String Fields

userid and password

The ability to login via query string is primarily provided to enable dbiScript applications to be embedded in a web portal, typically through the use of an iframe.

To login via query string, set the query string fields "userid" and "password" in the URL, i.e.:

http://www.mydomain.com/dbiScript/apps/clinic/?userid=myuserid&password=mypassword

When using an iframe, the src attribute of the iframe would be set to this URL, i.e.:

<iframe src="http://www.mydomain.com/dbiScript/apps/clinic/?userid=myuserid&password=mypassword"></iframe>

Specifying login credentials in plain text within the URL is a major security risk and is generally not recommended. This functionality is provided exclusively for those environments where the security risk is acceptable. Use of this feature is entirely at your own risk and must be developer-enabled within the application configuration file (see Query String Login).

  • Enabling login via query string poses a security threat. Use at your own risk.

The use of all other query string fields does not incur a security risk. The following fields can be utilized without enabling query string logins in the configuration file.

initialTable

If initialTable is specified, the active tab of the top-level page frame is set to the specified table. This setting is case sensitive.

Example URL: "http://www.mydomain.com/dbiScript/apps/clinic/?initialTable=patient"

initialFilter

If initialFilter is specified, the rows of the active table of the top-level page frame are filtered by the specified value. The specified value must be of the form [field name]=[value]. Multiple values may be specified for this field.

Example URL: "http://www.mydomain.com/dbiScript/apps/clinic/?initialFilter=fname=brian&initialFilter=lname=boyd"

Note that two initialFilter values have been specified in this example.

initialFilterMatchCase

By default, the filter is case-insensitive. For a case-sensitive filter, set this field to True.

Example URL: "http://www.mydomain.com/dbiScript/apps/clinic/?initialFIlter=fname=Brian&initialFilterMatchCase=True"