Code Corner 3 Database Tools - Nilpferdschaf/Egroupware-Doku GitHub Wiki
EGroupware's Database Tools
EGroupware comes with a built-in database which you can use in your applications. Using this database is made easy by a graphical interface and some helpful functions, that provide full CRUD functionality.
Creating a Table
The first table you will create will just be a simple list of departments, containing the columns dep_id and dep_name. You will use this column to replace the hard-coded list of departments from the previous tutorial.
Open up the eTemplate editor to begin and click on DB tools in the sidebox. This opens the table editor.
To create your first table, select your application (helloworld if you are following the tutorial) from the Application dropdown, then enter "departments" into the text field on the right. Now click on Add table to start editing.
Now that you have created the table it is time to add the columns. For the first one, enter "dep_id" as Column name. Set the Type to int and tick the check boxes NOT NULL, Primary Key and Unique. Now click on the Add Column button (The page symbol with a small +-sign, you can see it in the screenshot below).
A new column has appeared. For this one, use "dep_name", varchar, NOT NULL and Unique as in the screenshot below.
You are now almost ready to click Write tables. Before you do, make sure EGroupware has writing rights to the helloworld/setup/ folder. If this is not the case, execute
command line:
chown -R www-data $egw_installation/helloworld/setup
in a terminal. Once that is done, click Write tables.
If you now take a look at helloworld/setup/setup.inc.php you will notice a new line at the end:
setup.inc.php:
$setup_info['helloworld']['tables'] = array('departments');
EGW added this line automatically. During installation, this assigns the newly created table to your app.
There is now also an entirely new file, helloworld/setup/tables_current.inc.php. It contains information about setting up the table departments.
Now go to the http://$your_domain/egroupware/setup page and completely reinstall the application. Simply upgrading will not work. The automatic upgrade manager does not know how to initially create the first table, whereas the installer does. After uninstalling and reinstalling the table will have automatically been created. In the next step, you will take a look at it and insert some values.
Using MySQL to manually access and edit the EGroupware database
EGroupware has created the table for you, but it is still empty. To insert the data entries, go the command line of your EGW server and type
command line:
mysql -u $egw_db_root -p
where $egw_db_root is the database root username you set during the installation of EGroupware. When you run the command, it will ask you for a password. This is the db root password, also set during installation. Hit enter again and a mysql command prompt should open.
Now type in SHOW DATABASES;. A list of databases should appear.
command line:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| egroupware |
+--------------------+
To access the EGW database, type "USE egroupware;". Now type "SHOW tables;" and a long list of tables should appear. Look through the list and find the departments table. It should be in there. To print the contents of the table, type SELECT * from departments;. This should return an empty set.
command line:
mysql> SELECT * FROM departments;
Empty set (0.00 sec)
To fill the table up, enter the following command:
command line:
mysql> insert into departments(dep_id, dep_name) values \
(0, "Human Resources"), (1, "Marketing"), \
(2, "Tech Support"), (3, "Sales"), (4, "Management");
Query OK, 5 rows affected (0.15 sec)
Records: 5 Duplicates: 0 Warnings: 0
If you run the select command again, you can see the changes you made. Type "exit" to exit the mysql command prompt.
command line:
mysql> SELECT * FROM departments;
+--------+-----------------+
| dep_id | dep_name |
+--------+-----------------+
| 0 | Human Resources |
| 4 | Management |
| 1 | Marketing |
| 3 | Sales |
| 2 | Tech Support |
+--------+-----------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
Accessing a table from your app
Now that you have a table with content it is time to use it inside the application. To do this, you should first create a new class helloworld_so inside inc/class.helloworld_so.inc.php. This class will have two fields, a private $db containing the database object and a public $dep_names to buffer the department names from the table you created.
class.helloworld_so.inc.php:
<?php
class helloworld_so {
private $db;
var $dep_names;
}
The class also needs two functions. The first one is the constructor, which clones the global EGroupware database object, sets it up for usage within your app and then buffers the table. The second one is just a getter-function.
class.helloworld_so.inc.php:
function __construct()
{
$this->db = clone ($GLOBALS ['egw']->db);
$this->db->set_app ('helloworld');
$dep_select = $this->db->select ("departments", array("dep_id", "dep_name"));
$this->dep_names = array();
foreach ($dep_select as $record)
{
$this->departments [$record ['dep_id']] = $record ['dep_name'];
}
}
function get_department_names()
{
return $this->departments;
}
The most interesting part here is the egw_db->select() function. It takes a string with a table name and an array of columns within that table. There are also other parameters which are not used here. You will require them further down. The function parses these parameters into an SQL SELECT command which will then get sent to the database. The returned object is an ADORecordSet that can be used in a foreach loop like in the code above. Everything else is just setup. The select() function and many other query functions are part of the egw_db class. Its source code can be found in $egw_install/phpgroupwareapi/inc/class.egw_db.inc.php.
To use the new class, go back to helloworld_ui, add the field $so and initialize it within the constructor.
class.helloworld_ui.inc.php:
private $so;
function __construct()
{
$this->so = new helloworld_so();
$GLOBALS ['egw_info'] ['flags'] ['app_header'] = 'Enter your position.';
}
You can now completely remove the field $departments and replace the line that sets the $selection_options.
class.helloworld_ui.inc.php:
$selection_options = array(
'dep_id' => $this->so->get_department_names()
);
plus the line that outputs into the debug area
class.helloworld_ui.inc.php:
$content ['debug'] =
"Hello " . $content ['fname'] . " " . $content ['sname'] .
". You are working as " . $content ['position'] . " in " .
$this->so->get_department_names () [$content ['dep_id']] . ".\n";
The next step is to open up Egroupware to check wether you have done everything correctly. Nothing should have changed visually. The only thing that is different is the place where the select box entries are stored. If you think this is rather disappointing, log back into the EGW database and execute the SQL command
command line:
mysql> insert into departments (dep_id, dep_name) values (5, "Quality Assurance");
Query OK, 1 row affected (0.13 sec)
After a refresh the new entry should show up in the list. All without further editing the code.
Advanced Queries (SELECT ... WHERE, DELETE, INSERT, UPDATE)
Now that you can read from the database, you might want to write back to it. The form you created already supports inserting a name and a position, but these are not saved anywhere at the moment. To change that, go back to the DB tools from the eTemplate editor and select your app (helloworld) again. Add a new table "employees."
Add new columns like in the screenshot below. Afterwards, click on Write Tables. Egroupware will ask you to increase the version number of your app. This needs to be done, because having multiple database definitions associated to the same version number would make it impossible to upgrade an app. Confirm the new version number by clicking on Yes
setup.inc.php:
$setup_info ['helloworld']['version] = '0.002';
Do not forget to also increase the version number in setup/setup.inc.php - This is not done automatically!. While you are there, take a look at the newly generated file setup/tables_update.inc.php. It was generated by Egroupware and contains information on how to update the database when upgrading from one version number to another. When you created the first table, EGW was not able to generate this file, that is why you had to completely reinstall the application earlier.
The file currently contains the following function:
tables_update.inc.php:
function helloworld_upgrade0_001()
{
$GLOBALS['egw_setup']->oProc->CreateTable('employees',array(
'fd' => array(
'emp_id' => array(
'type' => 'int','precision' => '4','nullable' => False
),
'fname' => array(
'type' => 'varchar','precision' => '255','nullable' => False
),
'sname' => array(
'type' => 'varchar','precision' => '255','nullable' => False
),
'position' => array(
'type' => 'varchar','precision' => '255','nullable' => False
),
'dep_id' => array(
'type' => 'int','precision' => '4','nullable' => False
)
),
'pk' => array('emp_id'),
'fk' => array(),
'ix' => array(),
'uc' => array('emp_id')
));
return $GLOBALS['setup_info']['helloworld']['currentver'] = '0.002';
}
The function name contains the version number and the function itself has instructions on how to upgrade that version to some higher version number, which is set and returned at the end of the function. When upgrading, EGroupware will check for the upgrade function corresponding the version that is currently installed. If the returned newer version is still smaller than the target version, subsequent calls to further upgrade functions are made until the version numbers match. What this means for you is that from now on you will have to add an upgrade function for every version number increase you make. Otherwise that version can not be upgraded.
Luckily writing an upgrade function is really simple if the table layout has not changed between versions. To get from version $a to version $b, the upgrade function could look like this:
tables_update.inc.php:
function $appname_upgrade$a()
{
return $GLOBALS['setupinfo'][$appname]['currentver'] = $b;
}
When you change the table layout, EGroupware will automatically generate the upgrade functions for you. You can just go to $your_domain/egroupware/setup and upgrade.
Open up inc/class.helloworld_so.inc.php. You will now write a function that inserts a new value into the employee table.
class.helloworld_so.inc.php:
function insert_employee($fname, $sname, $position, $dep_id)
{
$this->db->insert ('employees', array(
'fname' => $fname,
'sname' => $sname,
'position' => $position,
'dep_id' => $dep_id,
'emp_id' => $this->increment_emp_id()
));
}
function increment_emp_id() {
$max = $this->db->select("employees", "max(emp_id) as max")->fields['max'];
return 1+$max;
}
This code makes use of the db->insert() function, which takes as parameters a string containing the table name and an array of $column => $value pairs. There are a few optional parameters:
$whereis a string with a WHERE clause or array with column-name / values pairs to check if a row with that key already exists. If the row exists db::update is called.$lineis for debugging purposes and should be set to the line number via__LINE__.$fileis also for debugging purposes and should contain the file name via__FILE__.$appis the name of your app. If set to false, the app that is currently running will be used.
The function increment_emp_id() queries the database for the maximum emp_id, increases it by 1 and returns it. This is sadly necessary, since Egroupware can not make columns AUTO_INCREMENT. You could add functionality this manually via
command line:
mysql> ALTER TABLE employees MODIFY column emp_id INT AUTO_INCREMENT;
but you would have to do this every time the app is installed. A possible workaround involves directly manipulating the database and sending the ALTER query through the PHP MySQL library. Another way to do it is the egw_db->query() function which will be discussed later.
You need to use the new insert() function in helloworld_ui. Update the index() function like this:
class.helloworld_ui.inc.php:
function index($content = null)
{
$departments = $this->so->get_department_names ();
if (is_array ($content))
{
$this->so->insert_employee (
$content ['fname'],
$content ['sname'],
$content ['position'],
$content ['dep_id']
);
$content ['debug'] =
"Hello " . $content ['fname'] . " " . $content ['sname'] .
". You are working as " . $content ['position'] . " in " .
$departments[$content ['dep_id']] . ".\n";
}
else
{
$content = array();
}
$selection_options = array(
'dep_id' => $departments
);
$tmpl = new Etemplate ('helloworld.index');
$tmpl->exec ('helloworld.helloworld_ui.index', $content, $selection_options);
}
Only the red lines have changed. This will write every newly entered employee into the database in addition to displaying the welcome message. You can now try it out and insert a few random people. When you think you are done, go to the mysql command line and enter
command line:
mysql> select emp_id, sname, fname, position, dep_name
from departments, employees
where employees.dep_id = departments.dep_id
order by emp_id;
+--------+---------+---------+-------------------+-------------------+
| emp_id | sname | fname | position | dep_name |
+--------+---------+---------+-------------------+-------------------+
| 1 | Snow | John | intern | Tech Support |
| 2 | Joe | Banana | accountant | Management |
| 4 | Gilmore | Lorelai | reporter | Marketing |
| 5 | McFly | Marty | product tester | Quality Assurance |
| 7 | Bond | James | firing consultant | Human Resources |
+--------+---------+---------+-------------------+-------------------+
5 rows in set (0.00 sec)
If you want to show the results of this query in your app, you will need a slightly more advanced select query that joins the two tables, departments and employees, together. You can use the same egw_db->select() function, except now you must set a few additional parameters.
class.helloworld_so.inc.php:
function get_employees()
{
$sel_result = $this->db->select (
//tables
'departments, employees',
//columns
array(
'emp_id',
'fname',
'sname',
'position',
'departments.dep_id as dep_id',
'dep_name',
),
//WHERE (only for comparing columns to constants.)
null,
//line number (for debugging)
__LINE__,
//file name (for debugging)
__FILE__,
//OFFSET
false,
//Additional suffix to append to the query
'ORDER BY sname DESC',
//The name of you app (false means use current app)
false,
//LIMIT (0 for no limit)
0,
//JOIN (for comparing columns to columns.)
"where employees.dep_id = departments.dep_id"
);
$employees = array();
foreach ($sel_result as $record)
{
$employees [] = $record;
}
return $employees;
}
Those are a lot of parameters! In order, they are called: $tables, $columns, $where, $line, $file, $offset, $suffix, $app, $limit and $join.
$tablecontains a string with all the tables that are used by the query, separated by a ",".$columnsis an array of column names.$whereis an array of$key => $valuepairs. Only table rows where the entry on column$keymatches$valuewill be returned.$lineis for debugging purposes and should be set to the line number via__LINE__.$fileis also for debugging purposes and should contain the file name via__FILE__.$offsetdetermines how many rows of the return set should be skipped. If set to false, no rows will be skipped.$suffixis an optional suffix that will be concatenated to the end of the query. You could use it to order the result set by a certain value, as was done in the example above.$appis the name of your app. If set to false, the app that is currently running will be used.$limitlimits the result to a certain length. If it is set to 0, no limit is set.- Finally,
$joincan be used to join multiple tables together. You can not do this via the$whereparameter. The$valueof the$key => $valuepairs is expected to be a constant and$column1 => $column2would thus be parsed to "$column1 = '$column2'". The result is a comparison of the content in column 1 to the name of column 2.
Add the code above to helloworld_so. Afterwards, you want to create a new file/class inc/class.helloworld_bo.inc.php. It will contain all of your business logic to neatly separate it from all user interface and storage code. In this class, the information returned by get_employees() will be parsed into a nicely formatted string that can then be displayed by helloworld_ui.
class.helloworld_bo.inc.php:
<?php
class helloworld_bo {
private $so;
function __construct() {
$this->so = new helloworld_so();
}
function get_formatted_employee_list() {
$formatted_string = "\n\n";
$employees = $this->so->get_employees();
foreach($employees as $employee) {
$formatted_string.= $this->emp_to_string($employee);
}
return $formatted_string;
}
function emp_to_string($employee) {
return $employee ['sname'] . ", " . $employee ['fname'] . " works as " .
$employee ['position'] . " in " . $employee ['dep_name'] . ".\n";
}
}
You should add two additional functions, get_department_names() and insert_employee(), which only call the equivalent function in helloworld_so. This way, the ui object does not need direct access to the storage object. This is part of Egroupwares conding rules. Everything should be structured into ui, business and storage objects, where higher layers call the layer directly beneath them.
class.helloworld_bo.inc.php:
function get_department_names() {
return $this->so->get_department_names();
}
function insert_employee($fname, $sname, $position, $dep_id) {
return $this->so->insert_employee($fname, $sname, $position, $dep_id);
}
Having done this, all references to the storage object can now be removed from helloworld_ui and replaced by the business object.
class.helloworld_ui.inc.php:
<?php
use EGroupware\Api\Etemplate;
class helloworld_ui
{
public $public_functions = array(
'index' => true,
);
private $bo;
function __construct()
{
$this->bo = new helloworld_bo ();
$GLOBALS ['egw_info'] ['flags'] ['app_header'] = 'Enter your position.';
}
function index($content = null)
{
$departments = $this->bo->get_department_names ();
if (is_array ($content))
{
$this->bo->insert_employee (
$content ['fname'],
$content ['sname'],
$content ['position'],
$content ['dep_id']
);
$content ['debug'] =
"Hello " . $content ['fname'] . " " . $content ['sname'] . ".
You are working as " . $content ['position'] . " in " .
$departments [$content ['dep_id']] . ".\n";
$content ['debug'] .= $this->bo->get_formatted_employee_list();
}
else
{
$content = array();
}
$selection_options = array(
'dep_id' => $departments
);
$tmpl = new Etemplate ('helloworld.index');
$tmpl->exec ('helloworld.helloworld_ui.index', $content, $selection_options);
}
}
If you run the app now, you will se the following:
Of course you might also want to delete or update existing database records. To make this possible, you will first need to update the interface a bit:
You should be able to create this yourself with the help of the previous section. If you want to copy-paste the code that follows, you will need to set the Name option in the editor of the select box, text fields and submit buttons to:
- Text field "First name" : select[fname]
- Text field "Second name" : select[sname]
- Submit Button "delete it" : delete
- Text field "New first name" : change[fname]
- Text field "New second name" : change[sname]
- Text field "New position" : change[position]
- Select box : change[dep_id]
- Submit Button "Modify" : modify
Note that setting the Name of a widget to "$namespace[$key]" makes Egroupware group all widgets with equal $namespace into the same subarray of $content. This means that $content will not have entries like "change[fname]" or "change[position]". It will, however, have an entry "change", which in turn will have the keys "fname", "sname", "position" and "dep_id". Doing things this way keeps the $content array nice and organized and prevents accidentally using the same name twice.
When you do this, you will see that Egroupware automatically adds a little trashcan inside the delete button, if the Name option is set to "delete". EGW recognizes quite a few keywords in this manner. Others you can try are "close","cancel","edit" or "create". You will learn more about this feature in the section about Images and CSS.
Copy the delete() and update() functions below into helloworld_so
class.helloworld_so.inc.php:
function delete_employee($fname_select, $sname_select)
{
return $this->db->delete (
//table
'employees',
//WHERE (comparing columns to constants)
array(
'fname' => $fname_select,
'sname' => $sname_select
)
);
}
function update_employee(
$fname_select, $sname_select,
$fname_change, $sname_change,
$position_change, $dep_id_change
)
{
return $this->db->update (
//table
'employees',
//updated values
array(
'fname' => $fname_change,
'sname' => $sname_change,
'position' => $position_change,
'dep_id' => $dep_id_change
),
//WHERE (comparing columns to constants)
array(
'fname' => $fname_select,
'sname' => $sname_select
)
);
}
These both work very similarly to the insert() function. egw_db->delete() takes the table name as the first argument and an array containing $key => $value pairs as the second argument. The keys are the columns and the values are the constants to compare them to. All table rows whose entries in the key-columns match the constants will be deleted.
egw_db->modify() also takes the table name as the first argument. The second argument is the data that will be modified. This is again an array of $key => $value pairs, in the same format that was used in the insert() function. The third argument determines where the record should be updated.
Both functions have additional parameters: the line number (use __LINE__), file name (use __FILE__) and app name. These do not need to be set, but it can be helpful for debugging. Both functions return an ADORecordSet if the query executed successfully, or false if it did not.
You still should not access the storage object directly from the ui object. Add the following functions to helloworld_bo.
class.helloworld_bo.inc.php:
function delete_employee($fname_select, $sname_select)
{
return $this->so->delete_employee($fname_select, $sname_select);
}
function update_employee(
$fname_select, $sname_select,
$fname_change, $sname_change,
$position_change, $dep_id_change
)
{
return $this->so->update_employee(
$fname_select, $sname_select,
$fname_change, $sname_change,
$position_change, $dep_id_change
);
}
Finally, it is time to update the index() function again.
class.helloworld_ui.inc.php:
function index($content = null)
{
$departments = $this->bo->get_department_names ();
if (is_array ($content))
{
$select = $content ['select'];
$change = $content ['change'];
$content ['debug'] = '';
if ($content ['submit'] == 'pressed')
{
$this->bo->insert_employee (
$content ['fname'],
$content ['sname'],
$content ['position'],
$content ['dep_id']
);
$content ['debug'] .=
"Hello " . $content ['fname'] . " " . $content ['sname'] .
". You are working as " . $content ['position'] . " in " .
$departments [$content ['dep_id']] . ".\n\n";
}
if ($content ['delete'] == 'pressed')
{
if ($this->bo->delete_employee (
$select ['fname'],
$select ['sname']
))
{
$content ['debug'] .=
$select ['fname'] . " " .
$select ['sname'] .
" was removed.\n\n";
}
}
if ($content ['modify'] == 'pressed')
{
if ($this->bo->update_employee (
$select ['fname'],
$select ['sname'],
$change ['fname'],
$change ['sname'],
$change ['position'],
$change ['dep_id']
))
{
$content ['debug'] .=
$select ['fname'] . " " .
$select ['sname'] .
" was changed.\n\n";
}
}
$content ['debug'] .= $this->bo->get_formatted_employee_list ();
}
else
{
$content = array();
$content ['debug'] = $this->bo->get_formatted_employee_list ();
}
$selection_options = array(
'dep_id' => $departments,
'change' => array('dep_id' => $departments)
);
$tmpl = new Etemplate ('helloworld.index');
$tmpl->exec ('helloworld.helloworld_ui.index', $content, $selection_options);
}
This needs some explanation. First of all, after checking wether $content is an array, it is now unclear which button was pressed. Previously this was obviously the submit button, because that was the only one. You can check wether a button was pressed via $content[$button_name]. If the entry is set to "pressed", the button was pressed.
If a button is determined to be pressed, the respective function call is made and if the query executed successfully, a note is printed to the debugging area.
Note that the department-changed select box also needs to be populated with the department entries through the $selection_options.
Go back to Egroupware to check wether everything works. Banana Joe is a terrible accountant. Go and fire him. Also, Mr. Smith has accidentally put "Mr." as his first name. He is actually called "John". You should correct that.
Other queries
The functions that are part of egw_db are powerful enough for most use cases, but they only represent a subset of possible SQL queries. Occasionally you will run into a situation where they just do not cut it. For these cases there is another function, egw_db->query(). This one only takes a query string and again, some debug information like line number and file name. Whatever the query string may be, it gets send to the database. This obviously means that you will have to generate the query string manually.
Conclusion
They way it is right now, the app looks a little bit ugly. Everything is on a single page making it cluttered and not user friendly. Regular users should also not have the power to delete or modify the entire data set. This can all be alleviated by spreading the ui over multiple pages and to navigate between them, you will need to access the sidebox. This will all be part of the next section.
Download
You can download the result of this section here.