ONEWEB now provides a Designer tool to design the data model for your application. Users can design the model from scratch by creating the entities, columns, and adding relationships or they can import an existing schema from your database into the Data Designer.

This Data Design is the starting point for using the other Database Designer Tools as well.

From version 4.0.19.09 ONEWEB provides a feature to design application schema using the ER Diagram designer tool. Users can design new tables and relationships as well as import existing schema for re-engineering. From version 5.0.21.01 we have added SQL Builder and Data Viewer tools as well to the list of Database Tools. Now ONEWEB provides

Data Designer - To design application schema

SQL Builder - To help generate SQL queries without knowing SQL syntax

Data Viewer - To view database tables and structures

Open the application and choose the ‘Data Designer’ menu

Click the icon ‘Create Diagram Designer’ to open the modal window.

Give some name and click on the ‘Create ER-Diagram’ button

Create Data Diagram success. Click on the icon ‘Start design diagram’ to open the ER Diagram

Once the user opens the project he will see the design space.

Inside the Diagram Designer project, you can find a Side Menu

In the Side Menu there are the following options:

Search box - to search and filter elements from your side menu Table - to view the Entities/ tables in your data model. Link - to view the relationships or links in your data model.

In the Data Designer, the user can design a data model or ER Diagram from scratch or import it from an existing system. To create a new entity user has to choose the Entity icon from the toolbar menu and click and drag on the diagram panel where you want to place the entity.

The new entity will be created there and the Entity Properties window will open on the right-hand side where you can configure your entity properties.

Feature : Entity (General)

Name : To name the table.

Comment : To add details to the table.

Color : To change the color of the table card.

Feature : Entity > (Columns)

Name : To generate column names.

Type : To define the column type.

N (Not null) : To set the column value as not null.

PK (Primary Key) : To define columns as the primary key.

Length : To set the length of the column.

Precision : To set the precision for the number after the decimal.

Default Value : To specify the default value of the column.

Auto Increment : To assign a column to be incremented automatically every time the data is inserted

Delete : To delete columns.

Feature : Entity > (Indexes & Unique key constraints)

Users can use this section to specify the unique constraints or index keys for the table. It will not show on the design diagram , but will be generated during the DDL generation process.

Feature: Entity > (Node Problem)

Errors : To show errors.

Warning : To show a warning.

Feature: Entity (Preview SQL (DDL))

To generate the DDL for the table

Feature: Entity (Delete)

To delete the entity or table

ONEWEB provides utility to compare two versions of the schema. The utility is available under the Tools section in the Database Tools menu.

Inside the compare schema utility, the user can choose to create a new comparison by selecting the source and target ER Diagram and versions.

When execute the system will open the comparison analysis screen.

Users can expand the My ER Diagram Menu on the left-hand side to choose the ER Diagram and the tables to be used in the SQL query. Drag and drop the table from the left panel onto the design diagram to start building the query.

Once the user drag and drop the table into the design panel, it will show all the columns in that table in the design panel.

Users can also see the other SQL Builder projects in their app on the side menu under My Query. Users can drag and drop another builder project to the design panel to use it as a sub query. It will show the output of that query as a table in the design panel.

SQL Builder also has a configuration panel that shows the current SQL configuration which is being built

It has the following tabs

• Columns : Displays the field or columns that are selected from the table for running the query.

• Union List : To configure union with another query.

• Join List : Displays the different joins between tables in the query.

• Query : Displays the output SQL query from the builder.

• Data : In this panel, user can execute the SQL command to get sample data if a valid connection is present.

It also has the following submenu in the columns tab.

Columns Tab Columns that are selected in the design panel will show in the columns tab and users can add additional configurations to them if they want.

If offers the following features

• Select: Select to include the columns in the select clause or un-select as required User can also delete the column tab by clicking at icon ‘x’

• Function: To add SQL functions to the select columns like Min, Max, Count, etc.

• Distinct: To set a column value as distinct in select.

• Column: To display the name of selected columns.

• Alias: To configure alias name for a select phrase.

• Object: Schema Name and Table name

• Group by: For selecting the column to be used in group by claus

• Having: For setting the condition for group_by clauses

• Sort: To include the column in the sort clause.

• Sort Type: To set sort by ascending or descending.

Union List Tab When you have more than one SQL project in your application, you can join them together to form complex queries. The union tab allows users to configure the union with another SQL builder project that is selected and dragged to the design panel.

Join List Tab To configure the join condition for the SQL query.

Query Tab To generate the SQL query from all the configurations.

Data Tab To execute the SQL query generated against a valid connection and view the result set from the database. The current version retrieves only the first 5 records from the result set.

Click on the + Condition to specify the condition

SQL Builder is a Designer Tool provided by ONEWEB to create SQL queries without knowledge of any SQL syntax, by simply choosing the columns from tables in your ER Diagram. Since the SQL Builder is based on the ER Diagram, the user has to first generate the database structure using ER Diagram. Users can either create it from scratch or import an existing schema from the database onto the ER Diagram Designer.

Once the user has a valid Diagram, he/she can start building SQL queries on that.

Data Viewer is the tool provided by ONEWEB to interact with databases directly from the ONEWEB platform. Users do not need another Database tool to connect and view the data from the database. Users can use this tool to browse the database objects, see data from tables, run queries, and do every other thing that you do with a simple database visualizing tool.

Inside SQL Builder ONEWEB also provides an option to connect to the database and run the query to verify. Users can find the Connection option on the Side Menu

Click on the Connection, it opens the dialog to configure New Connection or you can use an existing connection in your application.

How to use Existing Connection Users can choose to select the connection by choosing the Database, then choose a server, and finally select database schema by clicking on ‘set active’.

Once the connection is successful, the user can see the server info updated on the Connection menu.

Users can expand the connection and browse for the tables. Click on ‘Connection’, then click the database name and then the schema name.

How to confgure New Connection : Click on the New Connection button.

Now follow the wizard through the following steps.

• Database Engine : Select Database Engine

• Connection Detail

• Database driver: User can choose from existing drivers or upload a new driver version. User also has a sample driver attached here to download and use.

Once the configuration is complete, the user can test it by using the Test option. If the alert message shows success, then it means an SQL Builder can connect already.

Click the close button in an alert box and click Save & Done button.

Once the new connection is successfully created, it will be shown in the list of available connections. The user can edit and delete by clicking on the icon beside the connection name.

Open application and choose the ‘Data Designer’ menu

Click the icon ‘Create SQL-Builder’ to open the modal window. To create, Enter the name and click on ‘Create SQL-Builder’

Create SQL-Builder success. Click on the icon ‘Start design SQL-Builder’ to open the SQL builder project

Once the user opens the project, they can see the design space.

Inside the SQL Builder project, you can find a Side Menu

In the Side Menu there are the following options:

• Search box - to search and filter elements from your side menu

• Connection - to manage connections

• My ER-Diagram - to view the ER Diagrams in your app and include the tables in Design Panel.

• My Query - to view the other queries in your app and include the query in Design Panel.

Side Menu can be closed by clicking on the icon for ‘AppSpace’

Open the application and choose the Database menu. Click on the Data Viewer menu under Tools.

System will open the Data Viewer screen for you.

The Data Viewer screen has two types of menu - the Toolbar and the Sidebar. The sidebar has the search and filter option and is used to browse the connections and tables. The toolbar menu has the controls to interact with the data.

Enter the database details.

Choose the Database Driver details. The user has an option to upload a new driver version on this screen.

Once the configuration is completed, the user can test the connection using the Test button. If a test is successful then the user can click Save & Done.

The user can also access this connection from the list of existing connections.

Expand the connections to view the schema and table details in the sidebar. The user can double click on a table to fetch the data from that table.

Users can also add search and filter conditions in the search field to filter the records.

Each column name also has the sort by ascending or descending options as well.

The Data Viewer also shows the total number of records fetched and the count of fetches at the bottom of the records panel.

How to generate SQL from the tables Data Viewer also provides an option to generate basic SQL Statements from the records. Users can choose Insert statement, Update or Delete Statement.

Data Viewer provides the below set of controls on the Sidebar

Data Viewer provides the following set of tools on Toolbar to work with the records fetched from database.

How to use Search The Search field is used to search and filter the tables on the side menu.

Hide Tables The hide/show icon on the tables is to hide or show a table from the design panel.

Toolbar Menus Data Designer provides a list of controls on the toolbar at the top.

Transfer Controls The transfer control provides the following options. The transfer From Database is enabled only if there is an active connection.

Under Import, there are two options currently provided by the Data Designer - From XML and From Database. User can import to Data Designer from an XML file which is ideally an export from another Data Designer. Or the user has to follow the template to create the XML for import.

Users can use the import from database option if there is an active connection. If the user chooses the Import from the database and if there are manually created entities then again there are two types of import - Merge and Replace. Users can choose to merge or replace the data from the database.

Data Designer also provides the option to export to PDF, or image files. Users can also use it to generate SQL statements (DDLs) or an XML file.

When generate as SQL statements user might need to map the columns from ER Diagram to specific data types in the database. For this, the Data Designer provides the Column Type mapping feature

Users can preset the data types for different database engines. POS Engine setting

Data Designer provides an option to choose the database engine type and version to be used as the default engine for generating SQL.

Users can connect to a particular database connection from the connection control. It has also the option to configure a new Connection. Please go through the Connection configuration in SQL Builder to learn about how to configure a new connection. Entity Properties Window When a user selects a particular entity for edit, the Entity Properties window will open.