Home → Using LincDoc 3.1+ → Configuring Storage Repositories → Configuring a SQL Repository

12.9. Configuring a SQL Repository

This topic describes how to configure your repository, both using the automated LincDoc capabilities as well as how to manually manipulate mappings and configure the repository database.

Proceed to one of the following sections below for more information:

About the Overall SQL Repository Feature

The main purpose of the SQL Repository is to save the data and its corresponding generated document from your eForm or Document Package to a database, allowing you to retrieve the information (or generated document) at a later time as well as use other LincDoc features (such as search and reporting). Data from each field in your eForm or Document Package is sent to a specific column in a database table, as defined by the mappings within your repository's configuration (described throughout this topic). The generated document is also stored in the database.

About Editing a Repository and Enabling the Changes

The editing and configuration of a repository is done using the Configure dialog box, as described in the remainder of this topic. However, it is important to understand the underlying behavior of this process. Although the Configure dialog box may show you completed field mappings and database tables, none of these changes are actually implemented (pushed to the underlying database) until you execute the appropriate DDL statements. The system is careful to point out when exactly these statements are executed, and it will guide you on when/how to execute them. Further, you must save the mappings that are a part of the eForm's (or Document Package's) stored configuration (i.e., the normal pressing of the save button to save changes made to the eForm/Document Package). All of the steps necessary to complete the configuration process are described in this topic.

Understanding the "Warning" Icon

The Configure dialog box displays a "warning" icon (an exclamation point inside a red triangle) whenever there is some part of the mapping that needs attention. Clicking on this icon will show you a list of possible solutions and/or suggestions on how to fix the issue.

Tip: If the icon shown below is present in the lower right corner of the Configure dialog box, there are issues that may need your attention, such as missing mappings or non-created tables.

Tip: In some situations, it is safe to ignore this icon. For example, if there is an unmapped field being collected in the eForm or Document Package that you do not care to save to the repository's database.

About the Typical Mapping Process

A brief overview of the tasks involved with configuring a SQL Repository is described below.

The exact tasks you will use depend on your automatic mapping choices, your desire to manually map fields, how many changes you make to your eForm or Document Package, and the amount of database table customization you wish to undertake.

  1. Access your repository and select a mapping option, running the spectrum from completely automatic (allowing LincDoc to do all of the work) to no mappings (all done manually). Using the fully automatic option may require no additional configurations, depending on the complexity of your eForm or Document Package.
  2. Review the mappings (both standard and multi-value mappings, if present).
  3. Edit the mappings, as needed, to fully map all fields to their corresponding repository database columns.
  4. If necessary, edit the database tables themselves.
  5. Create the tables once you are satisfied with the mappings and table definitions.
  6. Test the repository mappings and tables, and fix any issues that are discovered.
  7. Set other repository options (regular options and security options).
  8. Save all of your changes.

Selecting an Initial Mapping Option

Once you add a repository to your model and connect to your database, you need to map your form fields to repository (database) fields. The first time you access your repository, you are presented with three options to help facilitate this mapping, based on your current needs and the amount of customization you desire.

  1. Verify that you have created your repository and connected to the associated database.
  2. On the Repositories tab, click the configuration button that corresponds to the repository you want to configure.

    The Auto-map? dialog box appears.
     
  3. Choose one of the following options:
    • Save data automatically without mapping. This option does not map any fields or create any tables. Instead or storing your form data in specific database columns, your data is saved in a single, large XML string inside a pre-created table. Saving into a single string limits other LincDoc functionality, such as searching and reporting.

      Tip: This option is useful during testing, when your form has not been finalized, you are still adding, removing fields, or changing field types. Once your form is finalized, you should map the form's fields to database columns. It is also ideal if you want minimal involvement with the repository configuration process.

    • Auto-map unmapped fields. This option automatically maps all fields but does not create any tables. It merely shows the table(s) it will create, should you later use the Configure dialog's feature to do so.
    • Auto-map unmapped fields and create tables. This option both automatically maps all fields and executes all the necessary DDL statements to create the necessary table(s).
      Tip: You should use this option if and only if ALL of the following conditions are true:
      • All field types have already been assigned
      • For all text fields, the max lengths have been set.
      • You have set up all of the desired searchable fields on the Search tab.

      If any of the above items are altered after this step, and you have saved one or more forms into this mapping, you must take appropriate steps to re-map the field(s). This scenario almost always requiring manual intervention.

      Important: If you do not care about losing data and documents for previously generated forms, you can always choose the option to delete the existing table(s) associated with your eForm, and then auto-map and create everything again. Use caution if you choose this route. For more information, see the remainder of this topic below.

  4. Proceed to Reviewing Existing Mappings below for more information on the Configure dialog box, which appears once you select one of the above options.

Reviewing Existing Mappings

Once you specify a mapping option as described in  Selecting a Mapping Option above, or if you access your repository after creating the initial mappings, you can view your current mappings using the Configure dialog box.

Proceed to one of the following sections below:

Viewing Your Standard Mappings

Your currently defined, standard repository mappings can be viewed using the Configure dialog box's mapping tab, as shown below.

 

The left side of the dialog box shows the fields in your current eForm or Document Package (the Fields/sections entry), the type of data stored in the field (the type entry), and the database column to which the field is currently mapped, if any (the Column entry). The right side of the dialog box shows the database tables themselves, and provides the ability to manipulate these tables and their columns, if desired. Whenever possible, when tables are created automatically, LincDoc will attempt to use the LincDoc field names when defining the table column names.

You may also see the following items on your Configure dialog box (not all items will appear in all repositories):

Viewing Your Multi-Value Mappings

If your eForm or Document Package uses multi-value fields, the mappings for these fields appear and are configured in their own tab on the Configure dialog box, separate from regular fields on the mapping tab. Each multi-value table in your eForm or Document Package will use a separate tab.

In the following example, one additional table for multi-value fields is present in the form, so only one additional tab is present.

 

An additional table is used because LincDoc doesn't know how many entries will be made to the multi-value fields, unlike standard fields which always take a single value. This additional table allows LincDoc to maintain data orthogonality (clean data with no duplication) within the repository's database.

The options available for these multi-value mappings are the same as those for standard mapping. They are simply displayed on a different tab and stored in a separate database table.

Automatically Mapping Unmapped Fields

If you have any fields that are not mapped, and you do not want to manually map them, you can use the auto-map unmapped fields button to have LincDoc create the mappings for you.

Manually Editing Mappings

If you choose to not have LincDoc automatically create your mappings, or if you edit your eForm or Document Package fields and want to manually map the new fields, you can do so by dragging the field entry (on the left side of the Configure dialog box) and placing it on top of the correct database column (on the right side of the dialog box).

  1. If necessary, create the new database column on the right side of the Configure dialog box using the add column button for the corresponding table.
  2. Click and drag the field from the list of fields to the desired database column.
  3. Verify that a green check mark appears when you hover over the desired column.

    Note: If a red "x" appears, instead of a green check mark, the mapping is not allowed because the field's type does not match the database column's type.

  4. Release your mouse button. The mapping is created. Notice that the field's name now appears in the field entry (which was previously blank).
  5. Click test in the top toolbar to verify the settings are sane.
  6. Click save in the top left corner of the Configure dialog box to save all of your new mappings.

Clearing All Existing Mappings

You can remove all existing mappings and tables, allowing you to restart the mapping process from scratch, using the clear mapping button.

When this button is clicked, all existing mappings and tables are removed, and the warning icon appears next to each field listed on the left side of the Configure dialog box.

Important: Although the tables are removed from the Configure dialog box, they are not deleted from the repository's database. If you recreate the tables, as described below, new tables are created in addition to the existing tables and numbered accordingly (although only the new tables are displayed in the Configure dialog box). If you want to delete the existing tables, you need to drop them.

You can now remap your fields using any of the following options:

Advanced: Mapping Multiple Fields to a Single Column

It is possible to map multiple fields to the same column in a table. However, this task is considered advanced usage of LincDoc. For more information, see SQL Repository: Mapping Multiple Fields to a Single Column.

Deleting Mappings

You can temporarily delete mappings, with the intent of re-mapping them at a later time, or you can have LincDoc permanently ignore the mapping of a field.

Proceed to one of the following sections below for details:

Temporarily Deleting a Mapping

You can delete individual mappings, to remap them or to temporarily adjust mappings, using the "x" button.

This "x" button can be accessed from either the left side (field list) or the right side (database table list) of the Configure dialog box. The effect is the same, regardless of which side you use. The icon only appears once you have clicked a field or database column, as shown below.

When you click the "x" button itself, the mapping is removed and the warning icon appears in the field list on the left side of the Configure dialog box.

Permanently Ignoring a Mapping

Once you have deleted a mapping, you can tell LincDoc to ignore it (via the ignored check box), which allows you to not map the field and avoid any potential errors that typically occur when fields are not mapped. In essence, you are telling LincDoc that you are aware of the missing mapping but you do not want to map the field.

Important: The data submitted via an ignored, unmapped field is not saved to your repository's database. As a result, when an existing, submitted form is accessed using the Data Entry View, any unmapped fields will be blank.

Configuring Repository Database Tables

Several options are available for configuring your repository's database tables, allowing you to create customized tables and corresponding mappings, as necessary.

Proceed to one of the following sections below for more information:

Collapsing/Expanding Individual Tables

If you have multiple tables displayed on the right side of the Configure dialog box, it may be helpful, at times, to collapse or expand different tables to limit the information displayed.

Clicking the blank portion of a table's title bar, to the right of the table's name and options, allows you to collapse and expand the table.

Once collapsed, only the table's title bar is shown.

You can expand the table by clicking the title bar again.

Removing Individual Table Columns

You can manually remove specific table columns using the remove column button within the column itself. This option is useful if you remove a field from your form and the corresponding table column is no longer needed.

  1. On the right side of the Configure dialog box, locate the table that contains the column you want to remove.
  2. Click the column to select it.
    The column is highlighted, and the remove column button appears to the right of the column's name.
  3. Click the remove column button.
    The column is removed from the database table.

Manually Adding Tables for Mapping

You can add new tables, if you want more control over the number of tables in your repository's database or how your fields are distributed. New tables can either be entirely new to the repository or use other tables, already added to the repository's shared database, but not used by the current eForm or Document Package.

Warning: If you attempt to add in a table not created by LincDoc, it may not work (for example, due to custom table constraints already in place on the table). Mapping to non-LincDoc created tables is not officially supported, but it may work in many situations. You will know it is working if you generate a form and verify that you see expected results in the non-LincDoc table(s).

Tables are created using the add table to mapping button, which appears at the top of the list of tables on the right side of the Configure dialog box.

 

When this button is clicked, additional options appear.

If you select new table, a new table is added to the bottom of the table list on the right side of the Configure dialog box. You can rename the table, if desired.

If you select use existing table, a list of tables in the current repository database is displayed in the Select table dialog box, allowing you to choose one.

Once you click an existing table, it is added to the bottom of the list of tables on the right side of the Configure dialog box.

Fixing Broken Mappings

If you map a field to a specific type, but then change the field type in your form, the mapping will become invalid.

For example, if you accidentally define a date field as a text type, map the fields in your repository, and then change the date field to a date type, the existing mapping (to a text type field) will no longer be valid and must be remapped. A message, similar to the one show below, will appear when you save your eForm or Document Package.

You can fix these types of issues directly in the repository's Configure dialog box.

  1. Locate the broken mapping.
    If you hover over the "warning" icon, a message appears, informing you that the mapping is invalid.
  2. In the table (on the right side of the dialog box), click the invalid entry to select it.
    The entry is highlighted.
  3. Click the remove column button to remove the entire field.

    The Drop column dialog box appear.
  4. Click Execute.
    The existing entry, including the mapping, is removed.
  5. On the left side of the dialog box, in the field/section list, locate and click the "warning" icon adjacent to the unmapped field's name.

    The Field not mapped dialog box appears.
  6. Click Auto-map.
    A new entry is created in the corresponding table on the right side of the dialog box.
  7. Click the "warning" icon adjacent to the newly added table entry.
    The Missing column dialog box appears.
  8. Click the Execute button.
    The mapping is completed and the repository will now correctly save the updated field's information.
  9. Click save, and close the Configure dialog box.

Editing Existing Columns in a Table

You can alter the name and type (text, integer, etc.) of an existing table column using the column's edit button.

Warning: Do not attempt to modify the column type if data already exists in the column unless you are a database administrator and completely understand all of the possible implications. The recommended procedure is to leave the existing column as is, and make a brand new column for the new type.

  1. Locate the table containing the column you want to edit.
  2. Click the column to select it. The column is highlighted.
  3. On the far right side of the column's entry, click the edit button.

    The column's name and type entries become editable.
  4. If desired, change the name of the column using the text box in the column entry.
  5. If desired, change the type of data the column can store using the type drop-down list.
  6. Click the save button on the far right side of the column entry.

    Note: You can also click the revert button (next to the save button) to return to the column's default name and type settings.

Manually Adding Columns to a Table

You can add columns to existing tables using the add column button for the desired table.

  1. Locate the table to which you want to add a column.
  2. Adjacent to the table's column heading, click the add column button.

    A list of options appears.
  3. Select the type of data the column will contain.
    • boolean. A true/false value.
    • bytea. A binary file (such as a picture).
    • date. A simple date value.
    • int4. An integer between -2147483648 to +2147483647.
    • int8. An integer between -9223372036854775808 to 9223372036854775807.
    • numeric. Numbers with a decimal value.
    • text. Longer text. Use this for very long text fields like comment fields; use varchar for text fields that are known to be shorter such as "last_name".
    • time. A simple time value.
    • timestamp. An object that represents a particular date and time and timezone (for example: Jan 01, 2014 12:08 AM EST).
    • uuid. A string type that is intended to hold UUID values. For more information on UUID values and how they are used in LincDoc, click here.
    • varchar. Variable-length character data
    A new entry is added to the bottom of the table (you may need to scroll down to locate it). In the following example, a text type column has been added.
  4. Type of name for the new column (replacing the default name of "new").
  5. Verify that the type of data stored in the column is correctly specified (in this example, text). If not, you can change it using the corresponding drop-down list.
  6. Click the save button on the far right side of the entry.

    Note: You can also click the revert button (next to the save button) to return to the new columns default name and type settings.

    Warning: The column is not actually created in the table when save is clicked. However, you will see the warning icon, which, if clicked, will display the commands to execute the appropriate DDL statements. These statements will create the column in the database table.

Renaming a Table

You can alter the name of an existing table using the rename button for the desired table.

  1. Click the rename button that corresponds to the table you want to rename.

    The table's name becomes editable.
  2. Change the table's name directly in the text box that appears.
  3. Click the save button.

    The table is renamed (but only in this configuration dialog), and all of the existing mappings are retained. Press the warning dialog to execute the DDL statements to actually rename the table in the database.

Viewing a Table's DDL Commands

You can view a table's data definition language (DDL) statements, which represent the statements that LincDoc uses to create the table(s) in the current mapping. In addition, you can initiate the execution of these statements. The intent of showing these statements is for advanced usage, such as when a database administrator is designing a custom back-end procedure and wishes to understand the structures (tables) that LincDoc is creating.

Tip: These commands are advanced options that will probably only be useful to database administrators.

  1. Click the show DDL button that corresponds to the table whose statements you want to view. This button appears adjacent to the table's name on the right side of the Configure dialog box.

    The Create table dialog box appears.
  2. Perform any of the following actions, as desired:

Removing an Entire Table From Your Mappings

You can remove a table from the Configure dialog box using the remove table from mapping button.

Important: This action removes the table from your mapping, but not from your database. That action requires that you drop the tables (using the drop tables button).

  1. On the right side of the Configure dialog box, locate the table you want to remove. Remember, you are only removing the table from the Configure dialog box. You are not actually deleting (dropping) the table if it currently exists in your repository's database (was previously created).
  2. Click the remove table from mapping button adjacent to the table's name.

    The table is removed from the table list.

Creating Tables in Your Repository

Once you have finalized your manual mappings and completely configured your repository's database tables (as necessary), you need to actually create the tables in your repository's database. Until you create the tables, they cannot be used by LincDoc. In effect, the Configure dialog box displays the "plan" for the mapping and repository database table creation. However, until the create tables button is clicked, this "plan" is not implemented and cannot actually be used by LincDoc to store data.

If your tables have not yet been created, but only appear on your Configure dialog box, they are marked with the warning icon, as shown below.

  1. At the top of the Configure dialog box, click the create tables button.
    Note: You can also click the warning icon itself, but only the create command for the corresponding table will be displayed.

    The Create tables dialog box appears, showing options that apply to all tables in your repository's database.
  2. Perform one of the following actions, as needed:

Deleting Tables From Your Database

You can permanently remove tables from your repository's database associated with the current eForm or Document Package. This action completely removes all tables and all associated data corresponding to the current eForm or Document Package. Other tables are not affected. It is useful if you have made major configuration changes to your mappings (such as changing the type used by fields) and need to recreate your tables.

Important: Use this option with extreme care. Once deleted, the tables and their data cannot be recovered.

Proceed to one of the sections below, based on how many tables you want to delete:

Deleting All Tables

You can quickly delete all of the tables in your repository's database (associated with the current eForm or Document Package) using the drop tables button. 

  1. At the top of the Configure dialog box, click the drop tables button.
    The Drop tables dialog box appears.
  2. Click Execute.
    All of your tables are deleted.

Deleting Select Tables

You can delete specific tables from your repository's database by accessing and executing specific drop table commands. 

  1. At the top of the Configure dialog box, click the drop tables button.
    The Drop tables dialog box appears.
  2. Click the Show DDL button.
    The individual commands and associated syntax are displayed.
  3. Click the execute button adjacent to the command for the table you want to delete.
  4. Click Yes to confirm the table removal.
    The table is permanently removed from your repository's database.

Verifying the Items Displayed

You can click the refresh button at any time to confirm that you are viewing the most recent field list, table list, and related mappings.

Testing Your Mappings and Tables

Once you have defined all of your mapping, configured your tables, and created your tables, you can test all of the items using the test button.

Once clicked, you are informed if all repository settings were successful or if there are issues that require your attention.

Using the Repository Mapping Settings

The settings available on the Configure dialog box's Options tab allows you to activate specific behaviors available with your repository.

The following options are available:

Dynamically Defining a Security Policy

You can determine whether or not a security policy is defined when a document is saved to your repository using the settings on the Configure dialog box's Security tab.

For more information on these settings, see Using SQL Repository-Specific Security.

This page was: Helpful | Not Helpful
← 27.3. Creating an Email Template