Books demo

The books demo shows how to implement a simple DB Sync client app to manage a library of books.

Demo description

This demo shows an example of implementing DB Sync in an existing application.

The demo uses a database named "library", which contains "book" and "author" tables.

In this topic, we use the direct mode of the DB Sync framework, to be used for testing and demo purpose. Consequently, you do not need a GAS to be installed. In In order to use the GAS/SSO/GIP mode, follow the instruction found in Set up DB Sync with GAS/GIP, and directly jump to the section Start the DB Sync server. In the Client app configuration file step, define a client configuration file with parameters for the GAS.

Tip:

The instructions provided in this topic are for a command line terminal. Alternatively, you can use Genero Studio with the library.4pw GST project provided in DBSYNCDIR/demo/books.

Environment setup

  • Requires Genero FGL, GWS, GDC, GAS, GIP, GMA, GMI, GWA: 5.01+

  • Requires a PostgreSQL 16+ server to be installed on your computer.

  • Requires a running GDC to display DB Sync console apps and the demo client app.

  • Set the DB Sync environment as described in Package installation, to set DBSYNCDIR, PATH, FGLDIR, FGLLDPATH, FGLRESOURCEPATH.

  • Needs UTF-8 application locale with CHAR length semantics.

    Windows:
    set LANG=.fgluft8
set FGL_LENGTH_SEMANTICS=CHAR
    UNIX:
    export LANG=en_US.uft8
export FGL_LENGTH_SEMANTICS=CHAR

  • For server-side programs:

    Windows:
    set DBDATE=Y4MD-
set DBFORMAT=
set DBMONEY=
    UNIX:
    export DBDATE="Y4MD-"
unset DBFORMAT
unset DBMONEY

Demo directories

The books demo source files are located is in DBSYNCDIR/demo/books, with the following sub-directories:
  • version_0:
    • library.sql: The SQL commands to create database tables. This file is used to create the database tables.
    • library.sch: The FGL schema file corresponding to the SQL tables. This file is required to compile source files.
    • main.4gl: The original source program, using a direct connection to the central database server.
    • f_books.per: The original source form definition.
    • fglprofile: The fglprofile with database connection parameters to connect to the server database with the original app.
  • version_1:
    • library.sch: The FGL schema file corresponding to the SQL tables. This file is required to compile source files.
    • main.4gl: The adapted source program, using the DB Sync API to implement database synchronization.
    • f_books.per: The source form definition with some additional toolbar buttons to handle synchronization tasks.
    • library_schema.json: The DB Sync database schema definition.
    • myconfig_library.json: The configuration file for server-side DB Sync tools and server program.
    • dbsync_source.json: The configuration file for the client app, when using the direct mode.
    • dbsync_source_gas.json: The configuration file for the client app, when using the GAS/GIP mode.
    • library.4pw: The Genero Studio project file.
    • library.4db: The Genero Studio database schema file.

Current working directory

Go to the version_1 directory in the books demo:

Windows:
cd %DBSYNCDIR%\demo\books\version_1
UNIX:
cd $DBSYNCDIR/demo/books/version_1

Demo database creation with PostgreSQL

Create the "library" database in PostgreSQL as well as the "pgsuser" user.

Assuming the PostgreSQL root user is "postgres":
createdb --username=postgres --template=template0
         --encoding=utf8 --locale=en_US.utf8
         library

psql --username=postgres library

library=# CREATE USER pgsuser PASSWORD 'fourjs';
library=# GRANT ALL PRIVILEGES ON DATABASE library TO pgsuser;
library=# GRANT CREATE ON SCHEMA public TO pgsuser;
library=# \q

Demo SQL tables creation

Create the SQL tables from the library.sql script provided in the version_0 directory:

Windows:

psql --username=pgsuser library < ..\version_0\library.sql

UNIX:

psql --username=pgsuser library < ../version_0/library.sql
Tip:

To create the SQL tables in an Informix® database, you need to replace b_plot VARCHAR(500) by b_plot LVARCHAR(500), and comment out the lines that reset the PostgreSQL sequences at the end of the SQL script.

Server configuration file

The demo provides a ready-to-use server configuration file, myconfig_library.json, pre-configured with the library_schema.json and SQL connection parameters.

To view the content of the server configuration file, start the DB Sync config program with the "myconfig_library.json" filename as parameter:
dbsync_setup server-config -c myconfig_library.json

Observe the DB Sync schema file is defined as "library_schema.json" and the SQL connection parameters to the PostgreSQL "library" database are populated.

Close the configuration tool.

Upon closing, the tool asks you to create the DB Sync system tables. Answer NO. We will create the tables from the command line in the next step.

Create DB Sync system tables

To create DB Sync system tables in the "library" database, run the DB Sync setup program with the following command:
dbsync_setup create-tables -c myconfig_library.json
When you need to drop the DB Sync system tables use the following command:
dbsync_setup drop-tables -c myconfig_library.json

DB Sync database schema

The demo sources contain a ready-to-use DB Sync database schema definition: "library_schema.json".

Use the DB Sync schema editor to see the content of the schema:
dbsync_schema edit -c myconfig_library.json

Close the schema definition tool.

Register the demo app

Start the DB Sync app definition console with the following command:
dbsync_app_conf -c myconfig_library.json

Create a new app with the app id "app_library".

Associate the "author" and "book" tables to this app:

  • Click on the Tables button in the toolbar, select all SQL tables, then click the OK button.

Close the app definition console.

Create DB Sync app users

Start the DB Sync user configuration console with the following command:
dbsync_user_conf -x -c myconfig_library.json
You must use the -x option to be able to create DB Sync users.
Note:

With the GAS/GIP mode, DB Sync users are created automatically, then first time a user/app makes a sync request. When using the direct mode, end users need to be defined in the DB Sync user configuration console.

Create two users with the IDs "ted" and "mike".

For the user "ted":

  • Click on the Apps button in the toolbar and select the "app_library" app.

Do the same for the user "mike".

Close the user definition console.

Start the DB Sync server

Open a dedicated terminal, set the envionment as explained in the beginning of this topic, then execute the following command to start the DB Sync server:
dbsync_server -v -c myconfig_library.json -p 6394

Client app configuration file

The client apps need a configuration file with parameters defining the schema file and connection to the DB Sync server.

A ready-to-use client app config files are provided in the version_1 directory.

Run the setup tool program to check the client configuration file content:
dbsync_setup client-config -c dbsync_source.json

Make sure to define the correct host name, TCP port, HTTP protocol (HTTPS is not needed for the demo in direct mode).

Close the config tool.

When using GAS/SSO/GIP mode, complete the provided dbsync_source_gas.json file with your specific settings, and use this file with the demo program.

DB Sync API calls in the client app

To view the required code adaptations to implement DB Sync in an app, compare the version_0/main.4gl source code with version_1/main.4gl.

For example, on Linux, you can use the following command:
vimdiff ./version_0/main.4gl main.4gl

Key points:

  1. Load DB Sync source configuration file with read_dbsync_source_file(). For a mobile app, use base.Application.getProgramDir() to access program files.

  2. Load the database schema, initialize SQL options and check the schema with load_schema(). For a mobile app, use base.Application.getProgramDir() to access program files.

  3. Implement a user login/password dialog, in the the _init_user_ident() function.

    When using the direct mode, we only need a user login and password.

    When using GAS/GIP/SSO, authenticate users with the OAuthAPI, using the dbsync_source_gas.json file. You need to register the "app_library" demo app in the DB Sync app definition console, to get the client id and secret id to fill the "client_id" and "client_secret" parameters in the dbsync_source_gas.json file.

  4. Implement an option to do an initial database copy, or reuse the current local database. On a mobile device, the SQLite database file must be created in the application sandbox presistent dir, using os.Path.join(os.Path.pwd(). Search for get_database_copy() usage in the demo source code.

  5. Connect to the local SQLite database. Search for CONNECT TO in the code.

  6. Adapt the code implementing INSERT / UPDATE / DELETE instructions:

    Surround the SQL statements and DB Sync API calls with a TRY/CATCH block and use the transaction control commands BEGIN WORK / COMMIT WORK and ROLLBACK WORK to control the local SQL transaction.

    Use the register_local_insert/update/delete() functions to register changes in the local DB Sync changes log.

  7. Implement the synchronization process with the DBSync API, to collect local changes, send the changes and receive central modifications, then apply central changes to the local database, and finally send a return receipt. See the ON ACTION dbsync code for more details.

Launch the client app

Open a new terminal, set up the environment for DB Sync client apps, and start two client app instances:

For example, on UNIX:
fglrun main.42m dbsync_source.json &
fglrun main.42m dbsync_source.json &

For each app instance, enter a user id and password: For example, Ted and Mike.

If it does not exists, the app re-creates a local SQLite database file with the name library_userid.dbf, and issue a database copy request from the DB Sync server.

Test 1: Make changes as Ted in the first app instance, click Sync to upload to central database, then click Sync on Mike's app and see that the update appears on Mike's app.

Test 2: Make changes as Ted in the first app instance, and make changes on same item as Mike in the second app instance. Sync as Ted first - Ted's changes go to the central database. Sync as Mike second - you get the error message "Synchronization error: -50950 - Daya sync request failed Do full sync?". Answer Yes. Mike's changes are lost and Ted's changes appears. This is the expected behavior.