Sample for integrating drafts & submissions component with database sample-for-integrating-drafts-submissions-component-with-database

Sample overview sample-overview

AEM Forms portal drafts and submissions component allows users to save their forms as drafts and submit later from any device. Also, users can view their submitted forms on portal. To enable this functionality, AEM Forms provides data and metadata services to store the data filled in by a user in the form and the form metadata associated with drafts and submitted forms. This data is stored in the CRX repository, by default. However, as users interact with forms through AEM publish instance, which is generally outside the enterprise firewall, organizations may want to customize data storage for it to be more secure and reliable.

The sample, discussed in this document, is a reference implementation of customized data and metadata services to integrate drafts and submissions component with a database. The database used in the sample implementation is MySQL 5.6.24. However, you can integrate the drafts and submissions component with any database of your choice.

NOTE
  • The examples and configurations explained in this document are according to MySQL 5.6.24 and you must substitute them appropriately for your database system.
  • Ensure that you have installed latest version of AEM Forms add-on package. For the list of available packages, see the AEM Forms releases article.
  • The sample package works only with Adaptive Forms submit actions.

Set up and configure the sample set-up-and-configure-the-sample

Perform the following steps, on all the author and publish instances, to install and configure the sample :

  1. Download the following aem-fp-db-integration-sample-pkg-6.1.2.zip package to your file system.

    Sample package for database integration

    Get File

  2. Go to AEM package manager at https://[host]:[port]/crx/packmgr/.

  3. Click Upload Package.

  4. Browse to select the aem-fp-db-integration-sample-pkg-6.1.2.zip package and click OK.

  5. Click Install to next to the package to install the package.

  6. Go to AEM Web Console Configuration
    page at https://[host]:[port]/system/console/configMgr.

  7. Click to open Forms Portal Draft and Submission Configuration in edit mode.

  8. Specify the values for properties as described in the following table:

    table 0-row-3 1-row-3 2-row-3 3-row-3 4-row-3 5-row-3 6-row-3
    Property Description Value
    Forms Portal Draft Data Service Identifier for draft data service formsportal.sampledataservice
    Forms Portal Draft Metadata Service Identifier for draft metadata service formsportal.samplemetadataservice
    Forms Portal Submit Data Service Identifier for submit data service formsportal.sampledataservice
    Forms Portal Submit Metadata Service Identifier for submit metadata service formsportal.samplemetadataservice
    Forms Portal Pending Sign Data Service Identifier for Pending Sign data service formsportal.sampledataservice
    Forms Portal Pending Sign Metadata Service Identifier for Pending Sign metadata service formsportal.samplemetadataservice
    note note
    NOTE
    The services are resolved by their names mentioned as value for the aem.formsportal.impl.prop key as follows:
    code language-java
    @Service(value = {SubmitDataService.class, DraftDataService.class})
    @Property(name = "aem.formsportal.impl.prop", value = "formsportal.sampledataservice")
    @Service(value = { SubmitMetadataService.class, DraftMetadataService.class })
    @Property(name = "aem.formsportal.impl.prop", value = "formsportal.samplemetadataservice")
    

    You can change names of the data and metadata tables.

    To provide a different name for the metadata table:

    • In the Web Console Configuration, find and click Forms Portal Metadata Service Sample Implementation. You can change the values of data source, metadata/additional metadata table name.

    To provide a different name for the data table:

    • In the Web Console Configuration, find and click Forms Portal Data Service Sample Implementation. You can change the values of data source and data table name.
    note note
    NOTE
    If you change the table names, provide them in the Form Portal configuration.
  9. Leave other configurations as is and click Save.

  10. The database connection can be done via Apache Sling Connection Pooled Data Source.

  11. For Apache Sling connection, find and click to open Apache Sling Connection Pooled DataSource in edit mode in the Web Console Configuration. Specify the values for properties as described in the following table:

Property
Value
Datasource name

A datasource name for filtering drivers from the data source pool

Note: The sample implementation uses FormsPortal as the datasource name.

JDBC driver class
com.mysql.jdbc.Driver
JDBC connection URI
jdbc//[host]:[port]/[schema_name]
Username
A username to authenticate and perform actions on database tables
Password
Password associated with the username
Transaction Isolation
READ_COMMITTED
Max Active Connections
1000
Max Idle Connections
100
Min Idle Connections
10
Initial Size
10
Max Wait
100000
Test on Borrow
Checked
Test while Idle
Checked
Validation Query
Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server) (validationQuery)
Validation Query timeout
10000
NOTE
  • The JDBC driver for MySQL is not provided with the sample. Ensure that you have provisioned for it and provide the required information to configure the JDBC connection pool.
  • Point your author and publish instances to use same database. Value of the JDBC connection URI field must be same for all the author and publish instances.
  1. Leave other configurations as is and click Save.

  2. If you already have a table in the database schema, skip to the next step.

    Otherwise, if you do not already have a table in the database schema, execute the following SQL statements to create separate tables for data, metadata, and additional metadata in the database schema:

    note note
    NOTE
    You do not require different databases for author and publish instances. Use same database on all the author and publish instances.

    SQL statement for data table

    code language-sql
    CREATE TABLE `data` (
    `owner` varchar(255) DEFAULT NULL,
    `data` longblob,
    `metadataId` varchar(45) DEFAULT NULL,
    `id` varchar(45) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    SQL statement for metadata table

    code language-sql
    CREATE TABLE `metadata` (
    `formPath` varchar(1000) DEFAULT NULL,
    `formType` varchar(100) DEFAULT NULL,
    `description` text,
    `formName` varchar(255) DEFAULT NULL,
    `owner` varchar(255) DEFAULT NULL,
    `enableAnonymousSave` varchar(45) DEFAULT NULL,
    `renderPath` varchar(1000) DEFAULT NULL,
    `nodeType` varchar(45) DEFAULT NULL,
    `charset` varchar(45) DEFAULT NULL,
    `userdataID` varchar(45) DEFAULT NULL,
    `status` varchar(45) DEFAULT NULL,
    `formmodel` varchar(45) DEFAULT NULL,
    `markedForDeletion` varchar(45) DEFAULT NULL,
    `showDorClass` varchar(255) DEFAULT NULL,
    `sling:resourceType` varchar(1000) DEFAULT NULL,
    `attachmentList` longtext,
    `draftID` varchar(45) DEFAULT NULL,
    `submitID` varchar(45) DEFAULT NULL,
    `id` varchar(60) NOT NULL,
    `profile` varchar(255) DEFAULT NULL,
    `submitUrl` varchar(1000) DEFAULT NULL,
    `xdpRef` varchar(1000) DEFAULT NULL,
    `agreementId` varchar(255) DEFAULT NULL,
    `nextSigners` varchar(255) DEFAULT NULL,
    `eSignStatus` varchar(45) DEFAULT NULL,
    `pendingSignID` varchar(45) DEFAULT NULL,
    `agreementDataId` varchar(255) DEFAULT NULL,
    `enablePortalSubmit` varchar(45) DEFAULT NULL,
    `submitType` varchar(45) DEFAULT NULL,
    `dataType` varchar(45) DEFAULT NULL,
    `jcr:lastModified` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ID_UNIQUE` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    SQL statement for additionalmetadatatable

    code language-sql
    CREATE TABLE `additionalmetadatatable` (
    `value` text,
    `key` varchar(255) NOT NULL,
    `id` varchar(60) NOT NULL,
    PRIMARY KEY (`id`,`key`),
    CONSTRAINT ‘additionalmetadatatable_fk’ FOREIGN KEY (`id`) REFERENCES `metadata` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    SQL statement for comment table

    code language-sql
    CREATE TABLE `commenttable` (
    `commentId` varchar(255) DEFAULT NULL,
    `comment` text DEFAULT NULL,
    `ID` varchar(255) DEFAULT NULL,
    `commentowner` varchar(255) DEFAULT NULL,
    `time` varchar(255) DEFAULT NULL);
    
  3. If you already have the tables (data, metadata, and additionalmetadatatable) in the database schema, execute the following alter table queries:

    SQL statement for altering the data table

    code language-sql
    ALTER TABLE `data` CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
    

    SQL statement for altering the metadata table

    code language-sql
    ALTER TABLE metadata add markedForDeletion varchar(45) DEFAULT NULL
    
    note note
    NOTE
    The ALTER TABLE metadata add query fails if you have already run it and the markedforDeletion column is present in the table.
    code language-sql
    ALTER TABLE metadata add agreementId varchar(255) DEFAULT NULL,
    add nextSigners varchar(255) DEFAULT NULL,
    add eSignStatus varchar(45) DEFAULT NULL,
    add pendingSignID varchar(45) DEFAULT NULL,
    add agreementDataId varchar(255) DEFAULT NULL,
    add enablePortalSubmit varchar(45) DEFAULT NULL,
    add submitType varchar(45) DEFAULT NULL,
    add dataType varchar(45) DEFAULT NULL;
    
    code language-sql
    ALTER TABLE `metadata` CHANGE `formPath` `formPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `formType` `formType` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `description` `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `formName` `formName` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `renderPath` `renderPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `showDorClass` `showDorClass` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `sling:resourceType` `sling:resourceType` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `profile` `profile` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `submitUrl` `submitUrl` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `xdpRef` `xdpRef` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
    

    SQL statement for altering the additionalmetadatatable table

    code language-sql
    ALTER TABLE `additionalmetadatatable` CHANGE `value` `value` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `key` `key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
    

The sample implementation is now configured, which you can use to list your drafts and submissions while storing all data and metadata in a database. Let’s now see how data and metadata services are configured in the sample.

Install mysql-connector-java-5.1.39-bin.jar file install-mysql-connector-java-bin-jar-file

Perform the following steps,on all the author and publish instances, to install the mysql-connector-java-5.1.39-bin.jar file:

  1. Navigate to https://'[server]:[port]'/system/console/depfinder and search for com.mysql.jdbc package.

  2. In the Exported by column, check if the package is exported by any bundle.

    Proceed if the package is not exported by any bundle.

  3. Navigate to https://'[server]:[port]'/system/console/bundles and click Install/Update.

  4. Click Choose File and browse to select the mysql-connector-java-5.1.39-bin.jar file. Also, select Start Bundle and Refresh Packages checkboxes.

  5. Click Install or Update. Once complete, restart the server.

  6. (Windows only) Turn off the system firewall for your operating system.

Sample code for forms portal data and metadata service sample-code-for-forms-portal-data-and-metadata-service

The following zip contains FormsPortalSampleDataServiceImpl and FormsPortalSampleMetadataServiceImpl (implementation classes) for data and metadata service interfaces. Additionally, it contains all the classes required for compilation of above mentioned implementation classes.

Get File

Verify length of the file name verify-length-of-the-file-name

Database implementation of Forms Portal uses additional metadata table. The table has a composite primary key based on Key and id columns of the table. MySQL allows primary keys up to the length of 255 characters. You can use the following client-side validation script to verify the length of filename attached to the file widget. The validation is run when a file is attached. The script provided in the following procedure displays a message, when the filename is larger than 150 (including extension). You can modify the script to check it for a different number of characters.

Perform the following steps to create a client library and use the script:

  1. Log in to CRXDE and navigate to /etc/clientlibs/

  2. Create a node of type cq:ClientLibraryFolder and provide name of the node. For example, validation.

    Click Save All.

  3. Right-click the node, click create new file, and create a file with extension .txt. For example, js.txtAdd the following code to the newly created .txt file and click Save All.

    code language-javascript
    #base=util
     util.js
    

    In the above code, util is the name of the folder and util.js name of the file in the util folder. The util folder and util.js file are created in suceeeding steps.

  4. Right-click the cq:ClientLibraryFolder node created in step 2, select Create > Create Folder. Create a folder named util. Click Save All. Right-click the util folder, select Create > Create File. Create a file named util.js. Click Save All.

  5. Add the following code to util.js file and click Save All. The code validate length of the file name.

    code language-javascript
    /*
     * ADOBE CONFIDENTIAL
     * ___________________
     *
     * Copyright 2016 Adobe Systems Incorporated
     * All Rights Reserved.
     *
     * NOTICE:  All information contained herein is, and remains
     * the property of Adobe Systems Incorporated and its suppliers,
     * if any.  The intellectual and technical concepts contained
     * herein are proprietary to Adobe Systems Incorporated and its
     * suppliers and may be covered by U.S. and Foreign Patents,
     * patents in process, and are protected by trade secret or copyright law.
     * Dissemination of this information or reproduction of this material
     * is strictly forbidden unless prior written permission is obtained
     * from Adobe Systems Incorporated.
     *
     */
    (function () {
        var connectWithGuideBridge = function (gb) {
            gb.connect(function () {
                //For first time load
                window.guideBridge.on("elementValueChanged" , function(event, payload) {
            var component = payload.target; // Field whose value has changed
                    if(component.name == 'fileAttachment' && component.parent) {
                        var fileItems = $('#'+payload.target.parent.id).find(".guide-fu-fileItem");
                        for (i = 0;i<fileItems.length;i++) {
                            var filename = $(fileItems[i]).find(".guide-fu-fileName").text();
                            //check whether it is previously attached file or a newly  attached one
                            if(filename.length > 150 && filename.indexOf("fp.attach.jsp") < 0) {
                                window.alert("filename is larger than 150 : "+filename);
                                 $(fileItems[i]).find(".guide-fu-fileClose.close").click();
                            }
                        }
                    }
    
       });
            });
        };
    
        if (window.guideBridge) {
            connectWithGuideBridge(window.guideBridge);
        } else {
            window.addEventListener("bridgeInitializeStart", function (event) {
                connectWithGuideBridge(event.detail.guideBridge);
            });
        }
    })();
    
    note note
    NOTE
    The script is for out of the box (OOTB) attachment widget component. If you have customized the OOTB attachment widget then change the above script to incorporate respective changes.
  6. Add the following property to the folder created in step 2 and click Save All.

    • Name: categories

    • Type: String

    • Value: fp.validation

    • multi option: Enabled

  7. Navigate to /libs/fd/af/runtime/clientlibs/guideRuntimeand append the fp.validation value to the embed property.

  8. Navigate to /libs/fd/af/runtime/clientlibs/guideRuntimeWithXFA and append the fp.validation value to embed property.

    note note
    NOTE
    If you are using custom client libraries instead of of the guideRuntime and guideRuntimeWithXfa client libraries, use the category name to embed the client library created in this procedure to your custom libraries loaded at runtime.
  9. Click Save All. Now, when the filename is larger than 150 (including extension) characters a message is displayed.

recommendation-more-help
19ffd973-7af2-44d0-84b5-d547b0dffee2