This is documentation for Apprenda 7 and 8.
Documentation for newer version is available at https://new.docs.apprenda.com.

Working with Guest Application Data

The Platform has the ability to handle all deployment responsibilities of your applications' SQL Server or Oracle storage partitions when they are hosted on Platform. As of Platform version 8.2.0, you have two database deployment strategies for how the Platform can manage and provision your application data. The data tier can either be deployed using application/tenant provisioning scripts or a Data Tier plugin. If you are using a Platform version before 8.2.0, you can only use scripts to provision applications.

This page outlines some scripting conventions and considerations for designing your application's database/schema/PDB  to keep in mind when developing an application that needs a data tier. Before building a data tier, you should understand how the Platform deploys databases.

Note: utf-8 encoding is required for all persistence scripts, application and add-on manifests, CLI installer input files, and Apprenda mock files.

Choose a Database Provisioning Strategy

With the introduction of Data Tier plugins in Platform version 8.2.0, developers now have a choice in the provisioning strategy to use to configure the data tier of their applications. During the first deployment of their application, a developer can either include SQL scripts that create and patch the database in the application archive (the traditional database creation method) or use the Data Tier plugin to create data for their application.

A single application can only use one of the two methods, and once a strategy (or method) is chosen for the first version of the application, all subsequent patched versions of the application must continue using the same method of configuring the data tier.

For more information on using up Data Tier plugins, see the Data Tier plugin topic.

Note: Data Tier plugins only support SQL databases. If your application requires an on Platform Oracle database, you must use provisioning scripts. Additionally, if you are using a Platform version before 8.2.0, you must use provisioning scripts for on Platform databases.

Application and Tenant Provisioning Scripts 

All tables, views, stored procedures, and other structural artifacts for a guest application storage partitions must be outlined in an application provisioning script.  If needed, a separate Tenant provisioning script can be provided to define data that will be inserted into each storage partition upon initial creation.

Application and Tenant provisioning scripts should be included in the scripts folder of an Apprenda Application archive; only one of each type of script may be included.  Any scripting file extension or file name can be used so long as the archive also includes a Deployment Manifest that clearly specifies the files that should serve as the application and Tenant provisioning scripts. In the Deployment Manifest you must use the full file name with file extension, and the path to each file should be indicated relative to the "Scripts" folder in your archive. In addition, you should specify the type of DBMS (Oracle or MS SQL Server).

Sample DeploymentManifest.xml for data storage

<?xml version="1.0" encoding="utf-8"?>
<appManifest xmlns="http://schemas.apprenda.com/DeploymentManifest"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://schemas.apprenda.com/DeploymentManifest http://apprenda.com/schemas/platform/6.5/DeploymentManifest.xsd">

<!--Valid values for persistence dbms: "mssql", "oracle11g", "oracle12c", "oracle" (equivalent to "oracle11g")-->
<!--The values for "application scriptPath" and "tenant scriptPath" must be relative to the Scripts folder in your archive.-->
 
  <persistence strategy="IsolatedDB" dbms="oracle11g" >
    <provisioning>
      <application scriptPath="MyAwesomeScript.sql" />
      <tenant scriptPath="MyOtherAwesomeScript.sql" />
    </provisioning>
  </persistence>

</appManifest>

While the best practice is to specify the DBMS and scripts in a Deployment Manifest, the Platform will attempt to detect application and Tenant provisioning scripts included in the scripts folder of an Apprenda Application archive under the following conditions:

  • If "mssql" is specified for the DBMS but no scripts are specified, the Platform will automatically recognize a file named "ApplicationProvisioning_Script.sql" as the application provisioning script; it will also automatically recognize a file named "TenantProvisioning_Script.sql" as the Tenant provisioning script.  
  • If "oracle" is specified for the DBMS but no scripts are specified, the Platform will automatically recognize a file named "ApplicationProvisioning_Script.pls" as the application provisioning script; it will recognize a file named "TenantProvisioning_Script.pls" as the Tenant provisioning script. 
  • If no DBMS is specified and no scripts are specified:
    •  If a file named "ApplicationProvisioning_Script.pls" is included, the Platform will recognize this file as the application provisioning script and will automatically set the DBMS selection to "oracle.; it will then recognize only a file named "TenantProvisioning_Script.pls" as the Tenant provisioning script. 
    • Otherwise, by default the Platform will automatically set the DBMS selection to "mssql". If a file named "ApplicationProvisioning_Script.sql" is included, the Platform will recognize this file as the application provisioning script and will set the DBMS selection to "mssql" ; it will then recognize only a file named "TenantProvisioning_Script.sql" as the Tenant provisioning script.  
  • If no DBMS is specified but scripts are specified:
    • If the script specified as the application provisioning script has the file extension PLS, the DBMS will be set to "oracle."
    • Otherwise, by default the Platform will set the DBMS selection to "mssql."

Considerations for MS SQL Server Databases

The following should be taken into consideration for guest application databases (also called "storage partitions" in Apprenda) hosted on MS SQL Server:

  • In your application provisioning script, you may use any standard data type. Custom data types, however, are not supported at this time. As with non-Apprenda applications, Developers should be aware of any server differences that may exist in the local environment vs. the Apprenda instance and not inadvertently reference a data type that is not supported in the Apprenda instance, or Apprenda will fail to deploy the database. 

  • Apprenda supports nearly all database components, including but not limited to tables, views, stored procedures (CLR and T-SQL), foreign keys, and functions. Support for indexed views, however, is limited to the isolated data model; at this time indexed views are not supported for commingled databases.

  • Apprenda manages deployment of one database definition per application, but does support multiple schemas so long as a default schema is specified in the Deployment Manifest.

  • The database schema is to be written in a Tenant-unaware fashion, as Apprenda provides this construct for your application automatically via its API calls.

  • The following limitations apply to the provider partition, which is accessed via the ProviderContext and allows queries to be performed on data across all Tenants (it should be noted that these limitations do not apply within the TenantContext):

    • You cannot have a view that joins more than one physical table and uses SELECT * notation. The reason for this is that both tables from which the selection is made will have a special tenant_id column of the same name that is created by Apprenda during deployment of your database.  Querying this view will result in the desired Tenant view, but will lack any additional Provider support. 

    • Conversely, views that explicitly list their columns (i.e. do not use SELECT * notation) will never include the tenant_id column in its output. This makes it impossible to use views and know which rows belong to which Tenant. This is because Apprenda does not rewrite the view to automatically include the column in the view results.

Shared Tables

It is possible to denote that certain tables are shared, meaning that one copy of the data is intended to be used by all Tenants of the application. All data rows are visible to all Tenants, and all updates to these tables affect all queries by other Tenants. To declare a table as shared, denote it as such in the DeploymentManifest.xml file included with your application archive, as shown in the following example.

DeploymentManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<appManifest xmlns="http://schemas.apprenda.com/DeploymentManifest"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://schemas.apprenda.com/DeploymentManifest http://apprenda.com/schemas/platform/6.0/DeploymentManifest.xsd">

  <persistence>
    <tableOptions>
    <tableOption name="Tag" schema="dbo" option="AllTenants" />
    </tableOptions>
  </persistence>
     
</appManifest>

 

For more information on the DeploymentManifest.xml file, please see this documentation.

Data belonging to shared tables is placed in a separate database from other Tenant-specific data. Because of this deployment model, there are two items to be aware of:

  • Foreign keys referencing data in a shared table from a non-shared table are not supported. Apprenda will not create these foreign keys when deploying your database even if your application provisioning script contains such a key.
  • Before running your query that accesses a shared table, you must enable your SQL connection to cross machine boundaries since it is possible that Apprenda will deploy your shared table database to a separate server from some of your Tenant's data. To do this, run the following SQL statement: SET XACT_ABORT ON. See the MSDN article for this command for additional information about working with a SQL connection that may access multiple databases.

Considerations for Oracle 11g Schemas

Note: Oracle 11g is not supported on Platform version 8.1.0 and later.

The following should be taken into consideration for guest application schemas (also called "storage partitions" in Apprenda) hosted on Oracle 11g:

  • In your application provisioning script, you may use any standard data type. Custom data types are also supported. As with non-Apprenda applications, Developers should be aware of any server differences that may exist in the local environment vs. the Apprenda instance and not inadvertently reference a data type that is not supported in the Apprenda instance, or Apprenda will fail to deploy the database. 

  • Apprenda supports nearly all database components, including but not limited to tables, views, stored procedures, foreign keys, and functions. Materialized views are also supported.

  • Apprenda manages deployment of one schema definition per application.

  • The schema is to be written in a Tenant-unaware fashion, as Apprenda provides this construct for your application automatically via its API calls.

  • Developers will not be able to make use of tablespaces unless they are explicitly provided by your Platform Operator/DBA.  If, for instance, a single tablespace is needed per schema (or even multiple tablespaces for a given schema), this would require that said tablespaces be hardcoded AND already exist on a tagged, pre-prepared machine.

  • Because there are no components built into the .NET framework for working with Oracle, please see the  Building Apprenda Applications that Connect to Oracle topic for tools and processes necessary to building a .NET app that uses Oracle.

Considerations for Oracle 12c PDBs

The following should be taken into consideration for guest application PDBs (also called "storage partitions" in Apprenda) hosted on Oracle 12c:

  • Apprenda creates one PDB for each single-tenant application and each tenant in a multi-tenant application.

  • Each PDB will be created with a DEFAULT_SCHEMA schema/user to house objects by default and a TENANT schema/user for use by the application at runtime. As such each application appears to live in an oracle database by itself. The application scripts have the ability to leverage most if not all features of the database, with few limitations, including but not limited to tables, views (both normal & materialized), stored procedures, tablespaces, foreign keys, and functions.

  • ​Known limitations/concerns:

    • Because multiple PDBs are expected to coexist on a given CDB, tablespaces should be created without filename references unless *great* care and action is taken to avoid collisions. Specifically, 12c creates all filename-specified tablespaces as relative to the singular PDB.  This means that referencing a static path or name will create collisions.

    • Although Apprenda does not restrain you from creating additional schemas, managing their security is up to the application.

    • Oracle 'flows' all common users on a server into every PDB on that server and those users have tablespaces requirements. Because of this, care should be taken to provide all tablespaces for all known common users across the platform, in case the PDB is moved.

  • Because there are no components built into the .NET framework for working with Oracle, please see the Building Apprenda Applications that Connect to Oracle topic for tools and processes necessary to building a .NET app that uses Oracle.

User Grants 

For Oracle 12c, your Platform Operator has the option to enable or disable automatic grant creation for the TENANT user for PDBs hosted on the Apprenda Platform. Your Platform Operator may also copy/move an Oracle 12c PDB to an off-Platform Oracle server (or, in some cases, recreate the PDB on an off-Platform Oracle server), in which case no grants will be automatically created. 

If your Platform Operator has disabled automatic grant creation, or if you have been informed that your PDB will be moved to an off-Platform Oracle server, you must explicitly grant permissions to the TENANT user for each object the client may access. An example of how this can be done by including a section in your application provisioning script is as follows (where the "grant" line has been added for the existing "create" line for the "MY_TABLE" object):

create table MY_TABLE...;
grant select, insert, update, delete on MY_TABLE to TENANT;

It should be noted that in some cases your DBA or Platform Operator may require that the names of the users created to access a PDB be changed from the Platform defaults when the PDB is moved to or recreated off-Platform. In such cases, your DBA or Platform Operator should provide you with an alternate syntax for creating grants in your application provisioning script.

Rollback Scripts

When an application with a database component is patched, developers indicate changes to the storage partition through patching scripts included in the patching archive.

For applications that use Oracle 11g or SQL Server, if promotion of a patched version of the application fails, the Apprenda Platform manages the "rollback" of application data to the initial, pre-patched state.

The Platform does not manage rollback for Oracle 12c, but instead leaves rollback management up to developers, who are required to provide rollback scripts (restore-based rollbacks are not supported). In the case of failed promotion, rollback scripts must undo the changes made to application data by the patching scripts.

Developers are required to provide rollback scripts in the Scripts folder within the Persistence folder in the patching archive. The path to the rollback scripts (relative to the root of the archive) must be specified in the DeploymentManifest.xml file included with the patching archive:

Sample Patching Deployment Manifest.xml for Oracle12c

<?xml version="1.0" encoding="utf-8"?>
<appManifest xmlns="http://schemas.apprenda.com/DeploymentManifest"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://schemas.apprenda.com/DeploymentManifest http://apprenda.com/schemas/platform/6.0/DeploymentManifest.xsd">
 
  <persistence strategy="IsolatedDB" dbms="oracle12c" >
  </persistence>

  <persistencePatches>
    <persistencePatch path="\persistence\Scripts\truncate.sql" type="DataScript"/>
	<persistencePatch path="\persistence\Scripts\13-createpackage.sql" type="SchemaScript"/>
  </persistencePatches>

  <rollbackScripts>
    <rollbackScript path="\persistence\Scripts\rollback_script.sql" />
  </rollbackScripts>

</appManifest>

Considerations for rollback scripts:

  • Multiple rollback scripts can be specified; they will be executed in the order indicated in the Deployment Manifest.
  • Rollback scripts which result in an error will result in one or more tenants being placed in maintenance mode for the application.
  • At least one rollback script should 'test' the state of the rollback to verify that rollback was successful (and, if not throw => maintenance mode).

Establishing a SQL Server or Oracle Connection in Your .NET Application Code

There are two mechanisms by which you can access application data for multi-tenant applications, as well as an additional method available for single-tenant applications only.

1. Look up the connection string from the Apprenda API, and use it to create a standard SqlConnection 

Note: This is applicable to multi-tenant applications only.

The following example returns a new SQL Server database connection via the Apprenda .NET Guest App API:

C# (SQL Server example)

public IDbConnection GetConnection()
{
    SqlConnection connection;
    if (SessionContext.DominantScope is IProviderContext)
    {
        connection = new SqlConnection(ProviderContext.Current.ConnectionString);
    }
    else
    {
        connection = new SqlConnection(TenantContext.Current.ConnectionString);
    }
    connection.Open();
    return connection;
}

The TenantContext.Current.ConnectionString endpoint can also be used to return an Oracle connection; the ProviderContext.Current.ConnectionString endpoint, however, is applicable to MS SQL Server only.

 

2. Use Apprenda's provided data client (which implements IDbConnection) in conjunction with System.Data.Common.DbProviderFactories

Note: This is applicable to multi-tenant applications only.

The following example shows how to configure your user interface, WCF service,or Windows Service to use Apprenda's data client, and instantiate a connection via the DbProviderFactory class included in the .NET Framework.  The Apprenda SQL Client retrieves the correct connection string for the current context and then uses the framework-supplied System.Data.SqlClient internally.

App.config example for SQL Server

<configuration>
  <connectionStrings>
    <add name="EchoAppDatabase" providerName="SaaSGrid.Data.SqlClient" 
       connectionString="Dynamically set by Apprenda" />
    </connectionStrings>
    . . .
<configuration>

The following example shows how to configure your user interface, WCF service,or Windows Service to use Apprenda's data client, and instantiate a connection via ODP.NET (a .NET driver provided by Oracle). 

App.config example for Oracle

<configuration>
  <connectionStrings>
    <add name="EchoAppDatabase" providerName="Oracle.DataAccess.Client" 
       connectionString="Dynamically set by Apprenda" />
    </connectionStrings>
    . . .
<configuration>
    . . .
  <system.data/>
    <DbProviderFactories/>
      <remove invariant="Oracle.DataAccess.Client" //>
      <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" //>
    </DbProviderFactories/>
  </system.data/>

The following exemplifies how to use the retrieved connection string in your C# code:

C#

DbProviderFactory providerFactory =
    DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["EchoAppDatabase"].ProviderName);
using (DbConnection connection = providerFactory.CreateConnection())
{
   // Do something here 
}

 

3. Use the standard System.Data.SqlClient in conjunction with the APPLICATIONCONNECTIONSTRING app config token 

Note: This is applicable to single-tenant applications only, and relies strictly on token replacement at component deploy time.

The following example shows how to configure your user interface, WCF service, or Windows Service to use the framework-supplied SQL Client in conjunction with the APPLICATIONCONNECTIONSTRING app config token.  It should be noted that this approach is only applicable to single-tenant applications.  It is also important to understand that app config tokens are replaced at component deploy time, so if your database/schema is relocated to another SQL Server instance or Oracle host, the connection string in any existing application workloads will then be incorrect (and should be redeployed).

App.config

<configuration>
  <connectionStrings>
    <add name="EchoAppDatabase" providerName="{YourProviderHere}" 
       connectionString="$#APPLICATIONCONNECTIONSTRING#$" />
    </connectionStrings>
    . . .
<configuration>

C#

  
var connectionString = ConfigurationManager.ConnectionStrings["EchoAppDatabase"];
// Open and use your connection as needed

 

Data and Local .NET Development

When running your application on your Developer workstation via Visual Studio, you'll want to "point" the application to a database explicitly so that local Developer testing can take place. Fortunately, Apprenda provides an easy approach to do this regardless of which approach you took to create your Apprenda connection.

If you are creating your connection via a connection string provided by the Apprenda API, you can simply specify a connection string in your user interface's Apprenda.mock.xml file so that the Local API can return it. This is demonstrated in the following example:

Apprenda.mock.xml

<?xml version="1.0" encoding="utf-8"?>
<apprendaLocal xmlns="http://schemas.apprenda.com/LocalConfig"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://schemas.apprenda.com/LocalConfig https://apprenda.com/schemas/platform/6.5/LocalApprendaConfig.xsd"
               applicationId="1389AB65-76F6-49bb-BE01-96FD62124653"
               providerId="C80D19C1-5FA1-48f7-B5D3-C4ADE90C8748"
               connectionString="Server=server; Database=EchoApp; Trusted_Connection=True;">
</apprendaLocal> 

 

Alternately, if you are using a configuration file to store the connection information, you can use Apprenda's conditional configuration system to specify a separate connection string that will be used until your application is deployed on the live Apprenda instance. 

App.config example for SQL Server

<connectionStrings>
     <!--Apprenda Local Configuration--> 
     <add name="EchoAppDatabase" providerName="System.Data.SqlClient"
          connectionString="Server=server; Database=EchoApp; Trusted_Connection=True;"/>
     <!--End Apprenda Local Configuration-->

     <!--Apprenda Live Configuration
     <add name="EchoAppDatabase" providerName="SaaSGrid.Data.SqlClient" 
          connectionString="Dynamically set by Apprenda"/>
     End Apprenda Live Configuration-->
</connectionStrings>

 

View the Conditional Configuration and Tokens article for more information about this capability in Apprenda.

Using an ORM

To use an Object-Relation Mapper such as NHibernate, it is usually necessary to create a custom connection provider that then uses one of the approaches outlined above in order to look up the appropriate connection string that will be supplied by Apprenda at deployment time. Refer to the Tradr example application that is included in the Developer Portal's Resources page for an example of how to do this with NHibernate. 

Writing Your Application Code for Maximum Flexibility

Apprenda recommends writing all database calls against the generic IDBConnection class as opposed to SqlConnection directly. This provides maximum flexibility for the deployment of your application to alternate databases that may be natively supported by Apprenda (such as Oracle), and most applications derive no benefit from using the specific SqlConnection class.

Establishing a SQL Server Database or Oracle Schema Connection in Your Java Application

There are two ways to connect to your application's database/schema from Java Web Application components.

1. Use one of the existing DataSource implementations provided by the Apprenda Datasource component:

Sample Spring context.xml

<context ..>
....
<bean id="apprendaDataSource" class="com.apprenda.guest.data.ApprendaSimpleDataSource">
    <property name="driverClass" value="net.sourceforge.jtds.jdbc.Driver" />
</bean>
...
</context>

The DataSource implementations can be used as-is or as the starting point for your own custom implementation.

​2. Use the Java Guest App API directly

This method involves creating the JDBC connection using the JDBC APIs to connect to and query the database.

Sample Java snipped to retrieve a SQL Server database connection

GuestAppContext guestCtx = ApprendaGuestApp.getContext();
Connection conn = null
if (guestCtx.isEnabled()) {
    // NOTE : This call will throw a GuestApplicationException (a runtime exception) if the application does not have a proper single or multi-tenant context
    // the driver could catch and record it
    // This actually gets called once and fails on hibernate initialization
    ConnectionConfig config = guestCtx.getTenant().getConnectionConfig();
    jdbcUrl = "jdbc:sqlserver://%s:%s/databaseName=%s".format(config.getServer(), config.getPort(),config.getDatabaseName()) ;
    user = config.getUsername();
    pwd = config.getPassword();
    connection = DriverManager.getConnection(jdbcUrl, user, pwd);
}

Additional information for using the Guest App API is available in the ConnectionConfig API documentation