Best Practices

This document highlights what we believe are best practices when developing applications with AppSynergy.

Schema Naming Suggestions

Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex.

Table Names

We recommend that you name your tables in the singular and in CamelCase (i.e. Invoice rather than Invoices and InvoiceItem rather than InvoiceItems).

Column Names

We recommend that you name your columns in CamelCase and sparingly use the underscore character (i.e. Invoice_ID is recommended whereas InvoiceId is not, however InvoiceDate is recommended whereas Invoice_Date is not).

• Primary Key Columns: Should follow the pattern TableName_ID (e.g. Invoice_ID, PurchaseOrder_ID). Do not use just ID as the primary key column name.
• Foreign Key Columns: Should have the same name as the Primary Key column they link to. Only in advanced schemas where a table may have multiple foreign key references to the same table (e.g. TableX), use a naming scheme like this: ShipTo_TableX_ID, ShipFrom_TableX_ID.

Trigger Names

We recommend that you use the default AppSynergy naming scheme for your triggers (e.g. Invoice_AFTER_UPDATE should be the name for an AFTER UPDATE trigger on the Invoice table).

Stored Procedure Names

When naming a stored procedure we recommend that you use a naming convention like this: TableName_MyAction; if a stored procedure doesn't primarily operate on a single table, keep in mind that the procedure names are stored alphabetically.

Stored Function Names

When naming a stored function we recommend that you use a naming convention like this: TableName_GetSomething. The "Get" helps signify that the function will return a value.

Primary Keys

We recommend that you use an AutoKey column for the primary key on your tables except in special circumstances like the following:

• User table. Certain tables may have a natural primary key that is guaranteed to be unique. For example a table of users could have its EmailAddress column as its primary key.
• State table. The natural primary key might be the state abbreviation.

We recommend that you never use Composite Primary Keys (these are Primary Keys composed of multiple columns). If you are an advanced database developer, we suggest you instead use an AutoKey for the primary key and then add an additional UNIQUE INDEX over the other column(s) that are required to be unique. Notwithstanding this rule, there are a handful of edge cases where a composite primary key is best, but those examples are beyond the scope of this documentation. You can add a unique index via Tools > SQL Console with a command like the following:

Generating AutoKey Values

AppSynergy automatically generates AutoKey values when records are added via the UI. However, if you need to insert records via a database trigger or stored routine you will need to call the parasql_next_val() function to generate the primary key:

NOTE: You cannot simply leave an AutoKey column blank and have the system generate it (as you would with an AUTO_INCREMENT column). Unlike an AUTO_INCREMENT column, an AutoKey column allows "reservations" of values for use within a modern web UI.

UI Design

• Use Header Boxes. Most Record and Report objects benefit from the use of a header. Simply drag a Box object from the palette and then drop objects (like text and buttons) inside this box. You will see this being done in various tutorial videos.
• Use Modal Panels. A modal panel is a pop-up panel that forces the user to interact with it before they can go back to using the rest of the application. Modal panels are a great way of simplifying the user experience by not presenting too much information all at once. See the Modal Panels Tutorial Video for more info.
• Delete Buttons. If you decide a Record needs a Delete button, place the Delete button inside the record – not outside the record – so there is no ambiguity of what is being deleted. Also, consider not having a Delete button at all, as it is often better to design applications with something like an Inactive checkbox on the record and then use filtering to control what is shown to the user.

Auto Publish

Once your application is in use by other people, we strongly recommend turning Auto Publish OFF. See File > App Settings for details.

• When Auto Publish is OFF, other users cannot see the changes you make to your application until you click File > Publish App.
• With Auto Publish OFF you also have a longer Revert To history to undo unwanted changes. The last 10 published versions of your application are kept, along with recent unpublished changes, which makes undoing mistakes easier.

Be aware that if you modify a database table that an application uses, an AutoPublish will be forced automatically on that application.

Modifying Tables

For a variety of reasons listed below, we recommend that you do not modify tables that are in use by a production application except to add a new column to the table or add an index to an existing column.

Any modification to a production database table should be carefully considered:

• Adding a new column to an existing table is safe.
• Changing the Index attribute of a column is safe.
• Changing the Required attribute of a column MIGHT cause problems. First, if the column has blank values, you will need to specify a value for the blank rows via Tools > Bulk Update before making the column required.
• Changing a table name or column name MIGHT cause problems. When a table name or column name is changed, AppSynergy modifies every application that references that table name or column name. However references to that table or column name in formulas, filters, triggers, stored routines, and scheduled events are not updated; you will need to fix these references manually.
• Changing the datatype of a column will LIKELY cause problems. Most datatype changes require that AppSynergy remove any field widgets in your UI based on that column; you will need to add these back manually. The exceptions to this are 1) changing the length of a character column or 2) switching between numeric datatypes; both should be safe unless you have written triggers that expect a certain length or datatype.
• Changing a Primary Key or Foreign Key column will LIKELY cause problems. AppSynergy requires that you break any foreign key links, save those changes, rename, then recreate the foreign key(s). Doing this will cause foreign key column formula references to break; you will need to fix these references manually.

Manual Schema Changes

If you create or modify database schema outside of the AppSynergy user interface, beware of the following:

• When AppSynergy creates or modifies tables it also maintains a variety of extended meta data that is critical to the proper operation. You will see a set of meta data tables named parasql_* in your database. These should never be modified except by AppSynergy or by special instructions provided by AppSynergy support.
• If you issue CREATE TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended meta data will not be created; this limits AppSynergy functionality with these tables. You can, however, manually add the extended meta data with special instructions provided by AppSynergy support if needed.
• If you issue ALTER TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended meta data will not be updated. Further, your application's references to table names, column names, data types, etc. will not be updated. This may result in applications with broken references. Issuing an ALTER TABLE command only for the purpose of adding a column or a unique index is safe so long as no existing column definitions are changed.
• You should never change the password for any AppSynergy linked user account. This includes root@localhost, root@%, u####@%, and db#####public@% user accounts. Also, you should never change permissions on any AppSynergy linked user account except via the AppSynergy UI (AppSynergy uses extended meta data to manage roles). Never create your own database user accounts directly; always use the AppSynergy UI. If you need to create additional database user accounts for other software to access the database directly, you should do so by creating an API Key via Tools > API Keys so that the security permissions for that user can be managed via the AppSynergy UI.

NOTE: Do not make excess copies of your application(s). Each copy of an application must be opened and modified every time a table in your database is modified. Extra copies of your application will slow down the table modification process, potentially enough to cause it to timeout.

Temporarily Enabling the General Log

If you need to see a log of all database activity you can do so by enabling the general log. Because this will log all connections, queries, data modifications, etc. it can generate very large log files very quickly if left enabled. We suggest you enable it, do your research, then disable it. Enabling the general log requires root access, so you will need to enable it and query the log via a third-party tool like MySQL Workbench.

To enable the general log:

To query the general log data:

To disable the general log when you are done:

Importing A Database Using Command Line Tools

Please refer to the AppSynergy Data Migration Guide

Exporting A Database Using Command Line Tools

AppSynergy automatically performs nightly backups of your entire database. You can also create a backup at any time on demand to your own local computer.

The recommended way to create a local backup of your database is as follows:

The above command will ensure all code and data is backed up, including any history records from those tables with their audit trail enabled.

The options --master-data --single-transaction ensure that a global read lock is acquired on all tables at the beginning of the backup to ensure that a consistent snapshot of the entire database is achieved without blocking ongoing read and write activity.

Connecting To Your Database via MySQL Workbench

NOTE: AppSynergy has an integrated SQL Console which eliminates much of the need for MySQL Workbench. See Tools > SQL Console for details.

You can connect directly to your AppSynergy database with MySQL Workbench. Workbench allows you to issue any SQL command as root, which has slightly more permissions than an AppSynergy Administrator.