Configure.IT Help Center | Reference Manuals | Back-end Configuration

Database Configuration

What is Database?

A Database is like an electronic filing system where information is organized in such a way that it becomes easy for computer to fetch the required data easily. Most databases contain multiple tables, which may each include several various fields. For example, an organization database may include tables for products, employees, and financial records. Each of these tables would have several fields which are relevant to the information stored in the table.

How to retrieve data stored in Database?

With Configure.IT, a database is a separate repository that stores a collection of data (tables). Each database has one or more distinct table(s) for creating, accessing, managing, searching and replicating the data it holds.

Configure.IT provides Database support for SQL, SQLite, MySQL, MS SQL and PostgreSQL. All the Configure.IT users get access of MySQL and SQLite and use any one of their choice, Whereas PostgreSQL and MS SQL are available only to Pro users. These databases are assigned to the project by Configure.IT team on demand/request.

One can request required data by using Query from Database. There can be different type of Queries. Select Query is used just to retrieve/list down the data while Action Query is used to ask for additional operations on the data stored in Database, such as insertion, updating, or deletion. Query can fetch data from single or multiple tables. Languages used to interact with databases are called Query Languages. Structured Query Language (SQL) is the well-known standard.

Which Database to Select?

Technical users know merits and demerits of each database so they can choose which database to chose for which project. But as Configure.IT aims to provide solution for non-technical users too, let's understand what is the difference and which one would be more suitable for which kind of project.

In a nutshell, MySQL is an open source RDBMS (Relational DataBase Server) designed to serve multiple users concurrently and uniquely whereas SQLite is a library for embedding a relational database within an project and aimed primarily at single user environments.

Making the learning curve easy, SQLite is a project-resident library, while MySQL is generally used as a client-server DBMS where the client process interacts with the DB server process using some sort of networking. So MySQL is mandatory for all the project except Standalone & local project. Though MySQL has an project-resident usage mode too, SQLite is a lightweight one. SQLite are highly useful for standalone projects and projects on small devices.

To summarise, Any Mobile project which requires local and server, will have to have MySQL or similar RDBMS as SQLite will operate at mobile end only.

Tables:

Definition:

As you know database is made of different tables. Each table is meant for different purposes. User makes different tables as per his requirements to store various records. Table is made of vertical columns and horizontal rows. When a row and a column intersects, its called cell. Value of a cell can be referenced by the cell address. Different data can be combined or even if we need only some fields from any table, then also those fields can be fetched by SQL Query.

List of Tables:
- +Add New - In addition to the default tables, you can add the new tables here, based on your requirement. (Refer +Add New section for more details )
- Drop Table - You can delete/drop a table from database using this option. It allows you to drop single/multiple tables. Select all the tables you want to delete and click on this button. It will ask for the confirmation message in order to avoid deletion by mistake.
- View All - It shows all tables (default and user-made)in the project.
- Copy Tables - It allows you to copy the tables from other projects. In case, there is a table residing in another project of yours which you would like to use here, then simply copy tables from other project.
- Query Logs - It shows the list of queries fired during the time duration. User is prompted on a screen where he need to enter Start date & time and End date & time.
- Sync Tables - This will allow user to sync all the tables of the project at once. Data synchronization is the process of maintaining the consistency and uniformity of data. Syncing Tables ensures that regardless of data modifications, all changes are merged with the original data source. It assures that same copy or version of data is used in all devices from source to destination.
- SQL Editor - This will redirect user on SQL Editor in another window. It provides Query editor like phpmyadmin. To user this editor, user must have basic knowledge of SQL queries. It provides auto-complete feature within the editor. This ready-to-use editor is used for Select, Insert and Update Queries only. No other queries like Drop, Show Tables etc will be entertained.
- Export Tables - This option will allow user to export tables. It exports all existing tables in SQL File. Once you confirm that you want to Export the Tables, it will download sql file of tables in “Download” folder of your device. This feature will only be available to users who has purchased backend or full source code. It also depends on the plan user has subscribed. Check http://www.configure.it/compare-packages/ for more details.
- Import Tables - User can Import database residing on his Remote server. Select the remote server from where you want to import the database, from the list (shows you added remote servers in dropdown) and tables are shown based on the selected remote server.
- Categories - Categories define distribution, grouping or classification of database having particular shared characteristics. The purpose is to simplify and ease the data.
Table Listing fields:
There are 20 Default tables which have "mod_" prefix. Based on these tables, default modules are created in the Admin Panel. You can add more fields in the default table, if required.
- Table Name - All the auto-generated as well as custom-made tables are displayed here.
- Table Code - Table code will be automatically generated, based on the name of the table.
- Primary Field - Primary Key field data which will be generated automatically and this will be unique.
- No: of Fields - The number of fields in the table is displayed here.
- View Table Data - This option allows you to view the data in the table. It will fire Select *(all) query and all the data residing in that particular table will be displayed.
- Actions - The Action is to edit the respective table.
List of Default Tables: Here is the list of default tables which user will see as soon as the new project will be configured.
- mod_admin
- mod_admin_menu
- mod_admin_navigation_log
- mod_cache_tables
- mod_customers
- mod_city
- mod_country
- mod_executed_notifications
- mod_group_master
- mod_group_rights
- mod_language
- mod_log_history
- mod_page_settings
- mod_push_notifications
- mod_setting
- mod_setting_lang
- mod_state
- mod_system_email
- mod_system_email_vars
- mod_ws_token

Add New Table

Apart from default tables, user need to make tables as per his requirement. To make new table and fields click on “+Add New” button. It will redirect the user on following screen.

While adding new table, user need to fill following fields:

Table Name - The name can be alphanumeric. Lowercase letters, numeric values ,% (percentage sign) , _ (underscore sign) are allowed. It will give you error if the table name already exists else will confirm the availability. This is a mandatory field.
Table Code - Based on the table name, it will automatically take the input. Though its auto-filled, it's editable too. Just need to remember that it should be unique. It also checks validations for all the required fields and also for duplicity. This is a mandatory field.
Multi Lingual Table - This option will reflect only if you have selected more than one language for the project on the project backend dashboard page. Enable the checkbox here for the project to support multiple languages.

Making the Project multilingual is possible via project Console. It is useful as all people around the world can use it. But we recommend to use Multilingual Tables only if its really required as it will occupy the storage space and make the project slow.

Physical Record Delete - This option will provide you choice if record deletion is just to be flagged (where status = deleted and the deleted record won't be shown to user but will reside in the database) or it should be logically/physically deleted from the database also. If you select "Yes", then the data will be physically removed from database. On selecting "No", the data will be available in the database with “Deleted” flag/status, for future reference. This would take up the one record space in the database.
Number of Fields - Select the required number of fields to be added in the table. This can be increased later on while adding fields details too.
Category - Select the category from the available list in the dropdown. You can even add a new custom category. This is a mandatory field.
Comments - Comments help you add a short description of the Table. This is an optional field but it's useful. Providing proper comments will help any user to know why this table is created and what are the other uses & relations of this table with other tables. If developer gets changed then any new person can understand easily by reading comments.
Keywords - Add a keyword to indicate the content of a table. It’s like short description.
Add - This will add a new table in your database.
Cancel - This will not save any details entered and will redirect the user on previous screen.
Import Table from Excel - If user already has the table structure with/without data is ready in excel then he can import the table from excel to Configure.IT. Excel file can be on local drive, cloud drive or on the web. This will save the time and work both. Earlier work can be bought directly using this feature.

Enter Table name, select category and file/data source and choose the file to upload. Click on “Continue” to have a look at the data within table (as shown in below fig.). “Cancel” will redirect the user on previous screen.

Next Screen will show the records of uploaded file. Be default, all the fields will shown there. If user wants to deselect some unnecessary fields, he can do from this screen and eliminate extra fields. Click “Save” to finish the import process. “Back” will redirect user on previous screen. And “Cancel” will cancel the whole import process.

Edit Table:

Editing existing table is almost similar to Add Table as shown in below figure. In Edit Mode, it shows the table segregated in four section as explained below:

Table Details: This will show you same details as we added while creating new table.
Table Records: This will list down all the data fed by user in table as records.
Table Structure: This will show you list of fields with their properties.
Table Indexes: If any Index is added for the table, it will display the list.

Table Details:

This is same as “Add Table” except one field - Usage. It shows where and how the table is used within the project. Click to check the usage of the table in the entire project which will redirect user on screen same as below shown. Clicking on “Go” will redirect user on Edit page of that particular Module.

Table Records:

This will display the list of records added by admin/user in the table. From this screen user can edit, delete, add and view records easily.

Add New Row: If user wants to add new record then he just need to click on “+” below the list of the records and pop-up window will open. User can fill the fields and add new record. Clicking on “Submit” will add new record to the table.

Edit Selected Row: If user wants to modify any record then he just need to select the single record and click on Edit button at the bottom of the list. This will open that record in edit mode in popup window. User can make the necessary changes and Submit the changes.

Navigation buttons are given at the left-bottom of the window which will allow user to edit one by one records.

View Selected Row: Clicking on this option will just open the selected record in View Only Mode. User won’t be able to make any changes in it.

Navigation buttons are given at the left-bottom of the window which will allow user to view one by one records. Clicking on “Close” will close the window and user will be redirected on listing page.

Delete Selected Row: If user select the record and click on this option then it will ask for the confirmation and once user confirms, it will delete the record.
Find Records: To search the record from the table, user can select the field, criteria and value. Clicking on “Query” will display the query above criteria and clicking again on it will disappear the written query. Clicking on “+” will add one more field for criteria selection. “Find” will search for the satisfying records.

Reload Grid: This will refresh the records.
Batch Edit: This will allow to edit all selected records at once.
Batch Save: This will save all the records opened for Batch Editing at the same time.

Table Structure:

A database table consists of rows and columns. Structure is the list of fields of the table. Each field makes column whereas each record makes a row.

+Add New - From here, you can add more number of fields to your table. Clicking on it will ask you the number of fields you want to add. Write the desired number and you will be prompt with the following screen. Check “Add New Table Field” for more detailed information.
Drop Fields - Select number of fields which are not required and use this option to delete the field.
Copy Fields - It will allow you to copy fields from another table. When you click on "Copy Fields" button, it will show Popup. Select table will show you drop down of the existing tables from the same project. Select the table and its fields will be displayed. Select the fields to copy and select the position of the field.

By default, the new/copied field will be added at the end of the table if user hasn’t selected the position after which field this field to place.

Relation Views - Using this user can define the relationship with other Tables. Using this option, two fields of different tables can be mapped.

This will list all the other available tables and related fields in the dropdown. User need to select the relation field to set up. If you relate table with another table from the database, then there is no need to relate it in admin module while module creation. It will then, take the relations, automatically.

Two kind of relations can be set:

Virtual: Selection of this will keep the internal system informed about the relations among tables while creating tables/API. It won’t affect the database at all.

Physical: This will set database level relations among tables using Foreign Keys.

When you relate table to another table, you can set the actions to be taken on dependent record in child table “ON DELETE” & “ON UPDATE” operations. This is further divided in, Set Null, No Action, Restrict and Cascade.

SET NULL - On update/delete in referencing table, related record in child table will set to NULL. Dependency is defined by the foreign key.
NO ACTION - The update/delete is rejected if there are one or more related foreign key values in a referencing table. So there won’t be any action at all.
RESTRICT - If the relation view is set to Physical, selection of this action will directly affect DB. E.g. If relation is set between Country and State tables using common field - country_id, while deleting any country from the country table, it will restrict it. Deletion of the country won’t be allowed till there is no states associated with the same country.
CASCADE - It will reflect the change when the parent changes. (If you delete/updated a row, rows in child tables that reference that particular row will also be deleted/updated). Same changes will be in the child table as in referencing table.

Add to Lang - If you want to add more fields in Multilingual, then the same can be done from here. Simply, select the fields, Click on this button and the fields will be added in Lang table. You will see green icon for every field which is added for Multilingual. By default, the primary key field will be multilingual.

Drop from Lang - If you want to remove fields from Multilingual (Lang Table) then, select the fields and drop the same from using this button. After confirmation, it will drop the field from multilingual.

Save Order - This will freeze and save the order of your fields.

Default Template Fields - This dropdown contains number of custom fields as template. Selecting any from the list, will auto-fill the rest of the fields and add it to the table automatically.

Listing of fields:

Field Name - This helps when you want to add a custom field. Give the desired name to the field.
Field Code - It will automatically fetch the code, depending upon the field name you have entered.
Field Type -
- General - Integer, Varchar, Text, Date
- Numeric - Tiny Integer, Small Integer, Medium Integer, Integer, Big Integer, Float, Decimal, Double, Boolean
- String - Character, Varchar, Text, Medium Text, Long Text, Binary, Enum, Set
- Date and Time - Date, DateTime, Time, TimeStamp
- For more information, you may refer: http://www.techonthenet.com/mysql/datatypes.php
Field Length - It will show the length depending upon the field type or customized length as per the requirement.
Primary Key/Unique Key - It automatically takes Primary Key. No need to add or define primary key in table.

Note - primary key cannot be blank.

You can even assign a unique key. It is optional. It can be left as blank. Primary key will always be unique key.

Action
- Edit Field - You can edit the field, already defined.
- Drop Field - Will allow to delete the field after confirmation.
- More - Other actions user can perform over the fields are as below:
  - Add Index: If user want to add index on the field for more speed and random lookup, he can using this.
  - Add Unique: This action will make sure that the field contains unique value only.
  - Add Full Text: This will allow Full Text search for the said field. So if user enters search criteria e.g. “Name is” then it will search the database for full string “Name is” not either “name” or “is”.

Table Index

What is Indexing:

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Indexing can be single index or multiple index. To add index on any field, select “Add Index” from “more” actions from Table Structure Listing. It will ask for the confirmation before adding index and add index with default field which can be edited using “Edit” action in “Table Indexes” listing.

Single Index: Indexing can be done for single field.

Delete: Clicking on “Delete” action will ask for confirmation and on confirmation, it will remove the indexing from the field.

Edit Indexing: While editing the index it will redirect user on below screen where more fields can be added if multiple indexing is required and index type (Full Text, Index or Unique) can be chosen depending upon the requirement.

Multiple Index: Multiple indexing can be either directly on combination of multiple columns or multiple indexes on different fields within single table. Below screen shows multiple indexes.

Add New Table Fields

Based on the number of fields selected, fill in the field details.

Batch Edit - You can perform a task on an entire batch of records. Select the required records and edit the same in a batch. Similarly, you can even save in a batch using Batch Save.
At End of Table - Selecting this option lets you add the new field at the end of the table.
After - ‘After’ option lets you add the field after a specific field.
Table Fields - It provides default common fields. You can use from the default fields by selecting and it will be added to your table.
- Custom Field: It provides some commonly used fields that are in-built like Firstname , lastname, username, password, company name, etc. Select the required field from the dropdown of "custom field".
- Intelligent Data Types
  - eGender for "Enum" datatype (While entering field value,Don’t use single quotes )
  - tDescription for "Text" datatype
  - dModifiedDate for "Date"datatype
  - iQty for "Int" datatype
  - vName for "Varchar" datatype
Field Name: This is for internal use. This name will be used by DB. This won’t be displayed to the users of the project. In Configure.IT, we follow the pattern i.e. Datatype + Field name (vAddress).
Field Code : This can either be same as field name or can be different. Giving short and self-explanatory name is desirable.
Data Type: Select the datatype of the field value from the drop down. All the available datatypes are categorized for easy selection.
Length: what should be the length of the field, selecting data type may auto fill this field too. User can customize it.
Default: If there is any default value associated with the field, it can be entered here.
Null: Checking the checkbox will allow to accept Null.
Unsigned: This data types can hold large positive values but cannot hold negative values. So this option will be available only if the datatype is integer or similar.
Action / Delete: If at any time user decides to remove the field then delete icon can be clicked and it will ask for confirmation before deletion.

Remove Empty Rows - Sometimes when user opt for more fields to add and end up adding less field then clicking on this will remove the unused rows. Saving won't be allowed till all the rows are filled so at that time, clicking this will save the time and all empty rows will be deleted at once.

Add New - At the time of adding field values, if user want to add new field then clicking this will allow to append new field. Number of fields to enter can be written in the textbox provide beside “Add New” button.

Save & Add - This will let the user save current fields and add more fields.

Save & Close - This will save the data entered.

Cancel - This will cancel the process of adding field and redirect the user on previous screen.

Views

Definition:

A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. It is used to implement the security mechanism in the SQL Server.

Advantages of View over Table:

Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the suppressed tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
Views can join and combine multiple tables into a single virtual table.
Views can act as comprehensive tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
Views can hide the complexity of data or the whole table content. For example, a view could appear as Production2016 or Sales2017, transparently partitioning the actual underlying table.
Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents so it's quick to retrieve only specific data from server.
Depending on the SQL engine used, views can provide extra security

Add New View

View Title: Title will be shown to users.
View Name: This name is for DB to identify the view.
Algorithm: There are three types of View process algorithms as below:
- Undefined: For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and even a view cannot be updatable if a temporary table is used. This is the default option.
- Merge: For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
- Temptable: For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement. If we use TEMPTABLE explicitly then the locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement.
Table: Select the table from the dropdown
Column Names: Give Column names for which Views are to be added. By default, there are 2 columns. User can add more columns by clicking on “Add New”. User can even remove any columns by clicking “delete” button.
Primary Column: Which column should be considered as Primary
Definition: Specify the query definition to be executed.
Add: This will let the user add new View
Cancel: This will cancel the process of adding new View.

Views - List

This will show the list of the Views created by user for different purposes.

Add New: Used to add new View which is explained above in detail.
Delete: Select the record to be removed and click on “Delete”. After confirmation, it will delete the data.
View All: clicking on this icon will show all the records regardless any filtration.
Sync Views: This will allow user to sync all the views created by user in the project at once. After syncing views, all the changes will be merged with the original data source. This makes sure that same version stays both at source and destination end.

Please note, once your database is deployed on your remote server, all the data will be saved directly there only, not in local database. So there will not be any need for syncing thenafter.

Export Views : This option will allow user to export Views. SQL file will contain related SQL queries for Views, which will be available to download in "Download" folder of your device. It will ask the user for confirmation. This feature will only be available to users who has purchased backend or full source code. It also depends on the plan user has subscribed. Check http://www.configure.it/compare-packages/ for more details.
Search: User can find records by View Name, Main Table and Algorithm.

Triggers

Definition

A database trigger is procedural code that is consequently executed in response to some events on a particular table or view in a database. The trigger is generally used to maintain the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Add New Trigger

To add new SQL Trigger, user need to fill the details like when the Trigger should execute and for how long.

While adding new Trigger, user need to provide details in below fields:

Title: This is useful for users. Users can distinguish trigger by this name.
Trigger Name: Name of the Trigger, DB will identify trigger with this name
Table: Select the table from the dropdown.
Time: Specify when the Trigger should be executed. Options are after & before.
Event Time: What is the type of Event i.e. Insert, Update or Delete
Definition: Specify the query definition to be executed.

SQL Triggers - List

Add New: Used to add new Trigger. Explained above in detail.
Delete : The record and click on “Delete” button to remove the record.
View All: Click on “View All” icon anytime when user want to see all the records without any filters.
Sync Triggers: This will allow user to sync all the Triggers added by user in the project at once. After syncing triggers, all the changes will be merged with the main source of data. This way same copy or version of data will be used in all devices from source to destination.

Syncing is very useful feature to eliminating any differences in data.

Please note, once your database is deployed on your remote server, all the data will be saved directly there only, not in local database. So there will not be any need for syncing thenafter.

Export Triggers: This option will allow user to export SQL Triggers. It exports all existing SQL triggers in SQL File. Once you confirm Export of Triggers, it will download sql file of Triggers in “Download” folder of your device.

This feature will only be available to users who has purchased backend or full source code. It also depends on the plan user has subscribed. Check http://www.configure.it/compare-packages/ for more details.

Search: User can search Triggers by different filtration criteria like Trigger Name, Table Name, Execute Time and Event Type

Procedures

Definition:

User may need to make some small programs within the database to perform some tasks. Such subprograms can either be procedure or function. Procedure and Function are different as procedure doesn’t return any value, used just to perform an action while Function returns a single value, used to compute and return answer in terms of value.

In SQL procedure is created by writing SQL Query starting with Create Procedure… but to simplify it and make user-friendly, Configure.IT has a form where you can select/fill required data and everything else will be taken care of automatically.

Add New Procedure

To add new procedure, click on “Add New” and it will open a form to be filled.

Procedure Title : Descriptive name for the understanding of user
Procedure Name : This name will be saved in the database
Parameters
- Direction: There can be different parameters of the procedure as explained below:
  - IN : Indicates the input parameter i.e. value passed by the caller of the procedure
  - OUT : Indicates the output parameter i.e. value passed out to the caller
  - INOUT : Indicates that parameter value passed by the caller and then sent back to the caller
- Name: Name of the parameter
- Type : All the variable types will be populated in drop-down to select from
- Length: This may take automatically depending upon the selection of variable type and would not allow editing, may allow modifying for some variable types.
- Options: Option to delete the row/record
- Add New: This will allow to add one more parameter by adding a row
Definition: Code / Logic of the procedure to be written in this section.
Security Type: Security of the database can be managed using below two options:
- Definer: If Security Type is set to DEFINER, a user even with only EXECUTE permission for routine can call and get the output of the stored procedure regardless of whether that user has permission on MYSQL database or not.
- Invoker: This is opposite of Definer. To run the procedure with Invoker rights should have EXECUTE rights as well as permission of the database/table to fetch the data.
SQL Data Access:
- No SQL: Indicates that the procedure contains no SQL statements.
- Contains SQL: Indicates that the procedure contains SQL instructions, but it doesn’t contain statements that read or write data
- Reads SQL Data: Explicitly indicates that the procedure contains only reading of the data, no instructions for modification. E.g. SELECT query.
- Modifies SQL Data: Indicates that procedure may contain instruction which may write/modify data. E.g. UPDATE, INSERT, DELETE
Comments : Any extra comments / more detailed description so any new user can understand the purpose and logic
Add : This will add the new procedure.
Cancel : This will cancel the process of adding new procedure.

View List of Procedures:

+Add New: This will allow you to add new Procedure as explained above.
Delete : Select any record and click on delete icon to remove particular procedure
View All: This will remove all the filters and show all the records
Sync Procedures: This will sync the procedures with the remote server database, if any. Please note, once your database is deployed on your remote server, all the data will be saved directly there only, not in local database. So there will not be any need for syncing thenafter.
Export Procedures: This option will allow user to export procedures. It exports all existing procedures in SQL File. Once you confirm that you want to Export, it will download sql file in “Download” folder of your device.

Search: User can search for the procedures from the list by criterias like Procedure Name, Security Type and SQL Data Access
List of Procedures: User can view the list of procedures with following fields as display fields. Rest of the fields or code of the procedure can be viewed by clicking on procedure name or opening it in edit mode. Display fields list is as below:
- Procedure Name
- Security Type
- SQL Data Access
- Actions : Edit and Delete

Functions

Definition

Let's start by understanding the difference between FUNCTIONS and PROCEDURES. Non-technical users might not be knowing the difference.

A Function always returns a value using the return statement. A Procedure may return one or more values through OUT parameters or may not return at all. Function can be called from SQL statements whereas procedure can not be called from the SQL statements. Functions are normally used for computations whereas procedures are normally used for executing business logic. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

Add New Functions:

To add new Function in the database, please fill the form shown in below screenshot:

Function Title : Descriptive name for the understanding of user
Function Name : This name will be saved in the database
Return Type : Defines return value type i.e. integer, float etc.
Return Length : Maximum length can be greyed for some value types and for some, user may write.
Parameters
- Name: Name of the parameter
- Type: All the variable types will be populated in drop-down to select from
- Length: This may take automatically depending upon the selection of variable type and would not allow editing, may allow modifying for some variable types.
- Options: Option to delete the row/record
- Add New: This will allow to add one more parameter by adding a row
Definition: Code / Logic of the function to be written in this section.
Security Type: Security of the database can be managed using below two options:
- Definer: If Security Type is set to DEFINER, a user even with only EXECUTE permission for routine can call and get the output of the function regardless of whether that user has permission on MYSQL database or not.
- Invoker: This is opposite of Definer. To run the function with Invoker rights should have EXECUTE rights as well as permission of the database/table to fetch the data.
SQL Data Access:
- No SQL: Indicates that the function contains no SQL statements.
- Contains SQL: Indicates that the function contains SQL instructions, but it doesn’t contain statements that read or write data
- Reads SQL Data: Explicitly indicates that the function contains only reading of the data, no instructions for modification. E.g. SELECT query.
- Modifies SQL Data: Indicates that function may contain instruction which may write/modify data. E.g. UPDATE, INSERT, DELETE
Comments : Any extra comments / more detailed description so any new user can understand the purpose and logic
Add : This will add the new function .
Cancel : This will cancel the process of adding new function

SQL Functions - List

+Add New: This will allow you to add new function as explained above.
Delete : Select any record and click on delete icon to remove particular function
View All: This will remove all the filters and show all the records
Sync Functions : This will sync the functions with the remote server database, if any. Please note, once your database is deployed on your remote server, all the data will be saved directly there only, not in local database. So there will not be any need for syncing thenafter.
Export Functions: This option will allow user to export functions. It exports all existing functions in SQL File. Once you confirm that you want to Export, it will download sql file in “Download” folder of your device.

Search: User can search for the functions from the list by criterias like Function Name, Return Type, Security Type and SQL Data Access
List of Functions: User can view the list of functions with following fields as display fields. Rest of the fields or code of the function can be viewed by clicking on function name or opening it in edit mode. Display fields list is as below:
- Function Name
- Return Type
- Security Type
- SQL Data Access
- Actions : Edit and Delete

Database Configuration

Database Configuration

What is Database?

How to retrieve data stored in Database?

Which Database to Select?

Tables:

Add New Table

Edit Table:

Table Details:

Table Records:

Table Structure:

Table Index

Views

Definition:

Advantages of View over Table:

Add New View

Views - List

Triggers

Definition

Add New Trigger

SQL Triggers - List

Procedures

Definition:

Add New Procedure

View List of Procedures:

Functions

Definition

Add New Functions:

SQL Functions - List

Connect with Us

Need Support

Contact Us