In this section, we will introduce and explain the Offline database, as well as explain how to create a new transaction. The database is one of the most important components of the Offline Suite and it is necessary to understand it in order to fully use the many features of Offline. It is also necessary to create a new transaction before any further actions can be performed in Offline. We will cover the following topics:
- Database Structure
- Creating a Database
- Common Data Columns
- Using Database Attributes
Database Structure
The Offline Database is the repository for all data used in Offline. As such, the database is one of the most important components of the Offline Suite, as the database is where all data is stored while the network connection is unavailable. Once a transaction is edited, the edited data is also stored in the database. The database is a part of and resides on the same system as the Offline Server as shown in the architecture diagram below:
The Offline database uses a scalable SQLite database which is created at run time. SQLite is a software library that implements a self-contained, server-less, zero-configuration, transactional SQL database engine. SQLite is the most widely-deployed SQL database engine in the world at present, and the source code resides in the public domain.
When a user starts the Offline server, all code in the Javascript files is read. In addition, the metadata that has been coded in the Javascript files to create a data table is processed while the database and data tables are being created. Metadata is defined as being the definitions of the data table columns.
Each SAP transaction is represented by an individual data table in the Offline database. Transactions and data tables are maintained in a one to one relationship - each transaction can only have a single data table associated with it. The fields defined within a data table are elements of an array. Data can either be stored in or read from a data table.
The database is created only once. If a user wishes to modify or delete metadata in a particular data table, the only method to do this is to delete the database and restart the Offline server. A new database will be created when the Offline server is re-started.
Every data table in the database is associated with a particular transaction. But not every transaction (ex: mainmenu.js) needs to have a data table in the database. Each data table can only be tied to a single table control in Offline and each table control can only display data from a single data table.
Creating a Database
To create a new database, a user would add the code for any relevant elements into the database. To do this, it is necessary to use the addDatabaseTable function. The syntax for this function is as follows:
objxxx.addDatabaseTable([
{fieldname:"field1",columntitle:" ",columnwidth:width,fieldtype:"DBF_STRING",fieldlength:length,keytype:DBT_NON_PRIMARY_KEY,columntype:columntype,columnstate:columnstate},
{fieldname:"field2",columntitle:" ",columnwidth:width,fieldtype:"DBF_STRING",fieldlength:length,keytype:DBT_NON_PRIMARY_KEY,columntype:columntype,columnstate:columnstate},
{fieldname:"field3",columntitle:" ",columnwidth:width,fieldtype:"DBF_STRING",fieldlength:length,keytype:DBT_NON_PRIMARY_KEY,columntype:columntype,columnstate:columnstate},
]);
This code creates an object named 'objxxx' with three fields - FIeld1, Field2 and Field3. Each field contains a string value and a keytype. In Offline, all user-created columns are set as keytype 'DBT_NON_PRIMARY_KEY'.
Database Attributes
Attributes are used by Offline to construct the data tables for transactions. As was stated above, these attributes are used to define the data table columns. Each column has a number of specific attributes and are defined in the following sections.
Fieldname
The fieldname is the actual name of a given column. It serves as a reference point for access to the data contained in the relevant column. The fieldname should match the corresponding WS variables that are processed in the Liquid UI Server. These variables store the values which are either captured from SAP or which are used to populate SAP. In order for data to be passed to or read from Offline during synchronization, the names and sizes specified for the variables and the database fields must match.
Columntitle
The Columntitle is the display name of a given column. It is not necessarily identical to the fieldname.
Columnwidth
Columnwidth defines the actual size of the column. It is usually specified as a numerical value and refers to how many columns wide on a screen the given field may span.
Fieldtype
The Fieldtype refers to the type of value that will be contained in any given column. The values can be one of the following types:
- Strings
- Integers
- Floating Numbers
Fieldlength
The field length refers to the size of the data that is to be contained in the column. It must be the same as the GuiXT variables that will be displayed in the column.
Keytype
This indicates if the column is a primary key or not. In Offline, all user columns created in a given data table are always non-primary key. For a complete explanation of what a primary key is, please see the Generic Database Columns section.
Columntype
The Columntype determines whether a column is displayed as an edit column or as a checkbox. It can also indicate if a column is readonly or not. See the following three examples:
- CTRL_TABLE_EDIT
- CTRL_TABLE_STATIC
- CTRL_TABLE_CHECKBOX
Columnstate
The Columnstate indicates if a column is read-only, enabled or disabled. See the following three examples:
- CTRL_CS_ENABLE
- CTRL_CS_RDONLY
- CTRL_CS_DISABLE
Index
The Index is explained in detail in the Indexing section later in this document. Therefore we will not cover it here.
Generic Database Columns
There are five columns that are common to all data tables in Offline. In this section we will define and explain these five common data tables. The common data tables are as follows:
The recID column is an alphanumeric column. All data tables have a primary key and the recID column is where this value is stored in the data table. Primary keys are generated automatically whenever a record is added to a data table through the generic save function - SR3GenericFunctionKeyF11Save - in the SynR3.js file. However, if the user is trying to add a record on his own using the AddRecord function, then the primary key needs to be generated using the newRecID function as shown in the following example:
var strRecID = system.newRecId('$S1');
It is importnat to pass the argument '$1' to the new RecID ( ) function. This argument '$1' is used to generate a unique 15-character RecID. If the variable '$1' is not passed in the code, the new RecID( ) function does not know what type of RecID to generate.
Status
The Status column specifies the status of the data in a given data table. Essentially, this column specifies if the data is being modified or not.
Session
The Session column stores the session ID for the session when the data was created. Each time a user logs into Offline, the session ID will change. This enables Offline to know when and by whom each record in the database has been created. The session ID is then compared against the value contained in the Session column. If there is a match, then Offline will also compare the user ID of the user logged into the application with the Id of the user logged into the device. This is to ensure that only data belonging to a given user is displayed to that user. There are four data tables associated with the user and session information. These are described as follows:
- Session Table: The Session ID described above is stored in session table.
- Login Data Table: The user name will be stored in the Login Data table.
- Session Data table: The username of the currently logged in user is also stored in the Session data table when a transaction is modified. This way the particular modification can be associated with the relevant user and the modifications made.
- Transaction Data table: Each transaction is associated with the current session. Therefore Offline knows that a record created in a given session belongs only to the user who executed the transaction in the given session.
Modified
The Modified column indicates if data is being modified in Offline or not. Any time a user saves or updates data in Offline, these actions will trigger a modified flag, which is stored in this column. Offline checks this column prior to synchronization occurring. If the column is marked, it notifies Offline that data contained in the column must be uploaded and synchronized with SAP.
Message
The Message column displays any messages retrieved from SAP during synchronization. By viewing these captured messages, users can determine the actions they should take with the data stored in Offline.
