Database Configuration

Database Tables

Here ,Default table listing of your projects (Table Name,Table Code,Primary Field).

There are 16 Default tables which have "mod_" prefix & “sys_admin_cache_tables”.

Based on these tables, default modules are created at Admin Panel  those we have seen in “Basic overview of configure.IT”  part.

If you want to add any extra field in default table then you can do it easily.

Default Tables

  1. mod_admin
  2. mod_admin_menu
  3. mod_admin_navigation_log
  4. mod_city
  5. mod_country
  6. mod_group_master
  7. mod_group_rights
  8. mod_language
  9. mod_log_history
  10. mod_page_settings
  11. mod_setting
  12. mod_setting_lang
  13. mod_state
  14. mod_system_email
  15. mod_system_email_vars
  16. sys_admin_cache_tables

  1. Search By : It allows you to search by "Table Code" & "Primary Field". To removing filter, you can click on "Show All" button
  2. Drop Table : It allows you to drop single/multiple tables. Select particular tables and click on this button.
  3. Show All : It shows all tables in list.
  4. Sync Tables : It allows you to sync all tables. If you modify existing tables then sync tables so it will be reflected at module section if you have used that table.
  5. Copy Tables : It allows you to copy tables from other projects. So if you have common tables which you want to use in your project then just copy tables from other projects. Click on copy tables, select project & select tables which you want to use in your project & click on copy button.
  6. SQL Query Editor : It provides Query editor like phpmyadmin. No need to go backend for using query editor. It provides ready to use query editor in which you can check your sql query for Select / Insert / Update only.
  7. Note : Don’t use Delete  and other DDL queries like DROP, SHOW TABLES.

  8. Export Tables : It allows you to export sql file of all existing tables. You are able to download sql file of tables here.
  9. Import Tables : It allows you to import tables from remote server. Select any remote server from the list. (shows you added remote servers in dropdown) and tables are shown to you based on selected remote server.
  10. It allows you to add new remote server details also. You can edit server and database details from here .

    See below screen

  11. View Table Data : It allows you to view table data. No need to go backend for checking table data. You can directly view table data from here.
  12. Add New It allows to add new table. Just enter your Table Name,Table Code, Comments, Keywords.

Table name → allowed only lowercase , numeric value ,%  , _

Table code   →  according your table name it will automatically take input.

It also checks validations for all required fields & also for duplication validations if table is already exist.

When your project is Multilingual, it asks for adding this table in multilingual or not.

After adding new table , you will see below screen with Auto Generated Primary Key.

If you add your table in Multilingual then your primary key field will be automatically set in Multilingual (Lang Table) & you will see green icon at primary key which shows that your field is set in Multilingual .

Add to Lang Table : If you want to add more fields in Multilingual then you can do it with  “Add to Lang Table”. Just select fields , Click on this button & fields will be added in Lang table. You will see green icon for every field which is added for Multilingual.

Drop from Lang Table : If you want to remove fields from Multilingual (Lang Table) then select fields & click on this button. It will be removed.

Table Fields

It provides default common fields. If you want to use it then just select it. It will be added your in table.

  1. Primary Key : It automatically takes Primary Key. No need to add or define primary key in table.

    You can add new field “At the end of the Table” or “After” particular field

    Custom Fields
    It provides some commonly used fields in built like Firstname , last name , username , password , company name etc. Just select it from dropdown of "custom field".

    If you want to add your new custom field which is not in common listed fields, then you can also create it easily. Just keep "None" over there & define your field name , Datatype , length & Null value.

    While creating custom fields, it uses 'Intelligent Data type selection' based on 'field name'.

    It automatically takes data type according fisrt letter of your field name for Basic Data types (Varchar , Int , Text , Date , Enum).

    So if you want to set data type as "Varchar" for your field then you have to take field name like "vName ".

    Here from "vName" , it will consider "v" as "varchar".

    Intelligent Data Types

    1. vName for "Varchar" datatype
    2. eGender for "Enum" datatype (While entering field value,Don’t use single quotes )
    3. tDescription for "Text" datatype
    4. dModifiedDate for "Date"datatype
    5. iQty for "Int" datatype

    If you want to set another data type , then you can set it normally. See below screen

  2. Add New : It allows you to create Add new one field in table
  3. Save and Add : It allows you to save entered fields and allow to add extra fields
  4. Save and Close : It allows you to save entered fields and close that popup
  5. Cancel : It will cancel to add data fields

Get Started After Table Creation

After creating table , You can see created table's details.

Table Name , Table Code , Primary key , Total no of fields , Comments , Keywords

  1. Table Name : Display Table name.
  2. Table Code : Display Table Code.
  3. Primary Key :Display Auto Generated Primary key according your Table name.
  4. Total Fields : Total no of fields in Table.
  5. Comments : Display Table comments
  6. Keywords : Display Table comments

Add to Lang Table :

If you want to add more fields in Multilingual then you can do it with  “Add to Lang Table”. Just select fields , Click on this button & fields will be added in Lang table. You will see green icon for every field which is added for Multilingual. (We have discussed it earlier).

Drop from Lang Table:

If you want to remove fields from Multilingual (Lang Table) then select fields & click on this button. It will be removed.(We have discussed it earlier).

  1. Modules using this table : When you create particular module using this table , you can see module name here. We will see later how to create Admin module.
  2. Relation Views : Here , You can create Relations with other Tables. It will show in dropdown box.  If you relate table with another table from database then there is no need to relate it in admin module while creating module using this table.
  3. It will automatically take relations overthere. We will see it later.  Now you can see drop down box for relations.

    When you relate table to another table,you can set “ON DELETE” & “ON UPDATE”  operations.
    You can see 4 options for “ON DELETE” & “ON UPDATE”.

    • SET NULL : It will update/delete the parent table row, but set the mis-matching foreign key columns in our child table to NULL (note that the table column must not be defined as NOT NULL).
    • NO ACTION : The update/delete is rejected if there are one or more related foreign key values in a referencing table
    • RESTRICT : The update/delete is rejected if there are one or more related foreign key values in a referencing table
    • CASCADE : IT will propagate the change when the parent changes. (If you delete/updated a row, rows in constrained tables that reference that row will also be deleted/updated).
  4. Copy Fields : It will allow you to copy fields from another table. When you click on "Copy Fields" button, it will show Popup. When you can select table from Dropdown box and select particular field that you want to use in your table.
  5. Add New Field : It will allow you to add new field in your existing table on the fly.
  6. Drop Fields : It will allow you to drop multiple unnecessary field. Just select particular field & click on this.
  7. Add Index : It will allow you to add multiple indexes for table. Just select particular field & click on this.
  8. Drop Index : It will allow you to drop multiple indexes for table. Just select particular field & click on this.
  9. Save Order : Saves the current order in which all the fields have been arranged
  10. Sort Fields : The individual fields can be dragged and dropped to any position. Remember to click on “Save Sequence” after changing the order, else the changes will not be reflected.
  11. Edit Fields : Edit particular field
  12. Add Index Add Index particular field.
  13. Drop Field : Drop particular field