This document can also be found online at databasedapp.com/doc.html
Databased is a self-hosted database application, allowing access to MySQL databases for non-technical people and technical people alike. With Databased we are trying to make working with MySQL databases easier and available for everyone.
Databased is not (yet) a replacement for phpMyAdmin or other MySQL administration tools as it offers only limited functionality mostly required by "regular" users. By "regular" users we mean sales staff, customer service reps, accountants etc.
Databased is built using CodeIgniter 2.1.4 and Flat UI Pro.
Below you'll find information on how to upgrade.v1 to v1.1.1
To be able to install Databased, you must have the following:
Like mentioned before, Databased is built using CodeIgniter and Flat UI Pro. To learn more about CodeIgniter, please visit the CodeIgniter website or read the online documentation here. To learn more about Flat UI Pro, please have a look here.
In addition to the default CodeIgniter files, Databased uses the following custom files:Controllers (/application/controllers/):
All files and folders are custom, except for the folder "/application/views/auth".Flat UI Pro files
The following folders are part of the Flat UI Pro kit:
Before continuing with the installation of Databased, please make sure your hosting is capable of hosting CodeIgniter 2.1.4. In 99% of the cases, this won't be a be a problem :)
You'll need to start with uploading the Databased files. You can either upload "Databased.zip" and unpack it on your hosting or unpack "Databased.zip" locally first and then upload the files. Unpacking the file "Databased.zip" will create a folder named "Databased" which contains all the application files.
Databased can run either in the root folder of your hosting, or in a sub folder. After unpacking/uploading, please double check and make sure a file named ".htaccess" can be found in the folder to which you have uploaded the Databased files.
Databased requires it's own MySQL database to function properly. Use phpMyAdmin or any other MySQL administration tool to create a new MySQL database. You can name this database whatever you like, but make sure you remember (or write it down) the name, as you'll need to put this into the configuration file later.
Once you have created the MySQL database, import the SQL file named "Databased.sql". You can find this file in the "Databased" folder after unpacking the "Databased.zip" file. Importing the SQL file will create all the required tables in your new MySQL database. Please make sure you don't change anything in this database.
The final step of the installation process is the configuration of Databased. Use your FTP tool to edit the following files as per the following instructions:/application/config/config.php
Find the line
$config['support_email'] = '[email protected]'; (around line 365) and change the email address to the one you want to use for support issues. When users are presented with an error, in some cases this email address is presented as well so your users can contact you with issues they might have.
The second item in the configuration file which needs to be set, can be found around line 227:
$config['encryption_key'] = 'SecretKeyGoesHere';. You will need to set an encryption key, which could be any string of any number of characters (not setting an encryption key will result in the application not working properly).
These are the only two required changes to "/application/config/config.php". There are other values you can change if you'd like (do so on your own risk though). To learn more about the config.php, please read through the CodeIgniter documentation here.Removing the "index.php" from the URLS (pretty URLs)
Edit the file /application/config/config.php, and remove the "index.php" value for the
$config['index_page'] variable (on line 29).
Finally, make sure you have a usable ".htaccess" file in the root folder of your application (this file is provided with the application)./application/config/database.php
The last file you'll need to update is one above, in there you'll need to enter the correct details to connect to your database. Please note that the MySQL user used to connect to your database should have ROOT access.
After completing the installation instructions, you're now ready to login for the first time. Navigate your browser to your Databased install and use the following details to login:
These login details give Administrator access to your new Databased installation. A new installation is ALWAYS set to use the login details as per the above, so be sure to change both the username and password right away.
Databased can be used to either manage existing databases or to create and manage new databases. Once you have installed Databased, it's not yet configured to manage any databases.
To enable existing databases or create a new one, navigate to the database section (click "Database" from the top menu and then click "Manage databases"). You will now see all databases on your MySQL server (or none if there aren't any databases created yet). When looking over the existing databases, you will either a green circle or a red circle inf front of each database. The green circle means the database meets the requirements and can be managed through Databased, a red circle means the database does not meet the requirements and can not be managed by Databased (please note that each table is required to have a primary key set on the first column).
To enable a database, click the panel to open it up and check the check box. You'll see a confirmation message stating that this database is now available in Databased.
To create a new database, click the second tab ("Create Database") and enter a name for your new database. Click the "Create database" button to create the database. When creating a new database, you can instantly enable the new database in Databased by checking the check box labeled "Enable in Databased".
PLEASE NOTE: databases can only be managed by Users with administrator rights.
When a user is logged into Databased, he or she will only be able to access the Tables to which his or her Role has access to. The available Tables are displayed trough tabs at the bottom of the screen when working on a database. If the user's Role is allowed to create Tables, he or she will also see a green button in the bottom left corner which will allow for the creation of new Tables.
When an Admin user creates a new Table, by default nobody on the system will have access to it until an Admin user gives the proper permissions to one or more Roles.
When a non-admin user creates a new Table, he or she can choose to keep the Table private (meaning only he or she and Admin users will be able to access it), share the table with other Users in his or her Role or share the table with all Users in the application.Private records
Databased has the option to define certain tables as "private" for certain user roles. This will result in users with this role to only be able to access their own records within that table. This function allows for multiple users all using one and the same table but only being able to access their own records.
At the time of this writing, Databased supports the following column types:
Databased allows you to upload files/images and link these from within records/cells. To do this, edit a cell or record and check the checkbox labeled "Advanced editor (incl file uploads)". This will enable the advanced editor which allows you to create HTML and upload files/images using the toolbar.Connections
Columns can be connected to a second table. This would mean the column in question can only contain values that match the primary key of that second table. To make this more user-friendly, Databased can display values from a different column in that second table.
For example, let's say table one is called "customers" and table two is called "books". The "customers" table contains a column called "customerBook" which you would like to link to the "books" table. The "books" table's primary key column is named "books_id" and the table has another column named "books_title". Now, by linking the "customerBook" column from the first table to the "books" table, we're saying that that column can only contain values from the "books_id" column. To make it more user-friendly though, you can connect "customerBook" to "books > books_title" so that the application will display the "books_title" rather then primary key values.Restrictions
Databased allows for a variety of restrictions which can be applied to columns to make sure data entered meets certain criteria. Currently, the following restrictions are supported:
|required||Returns FALSE if the form element is empty.|
|min_length||Returns FALSE if the form element is shorter then the parameter value.||min_length|
|max_length||Returns FALSE if the form element is longer then the parameter value.||max_length|
|exact_length||Returns FALSE if the form element is not exactly the parameter value.||exact_length|
|greater_than||Returns FALSE if the form element is less than the parameter value or not numeric.||greater_than|
|less_than||Returns FALSE if the form element is greater than the parameter value or not numeric.||less_than|
|alpha||Returns FALSE if the form element contains anything other than alphabetical characters.|
|alpha_numeric||Returns FALSE if the form element contains anything other than alpha-numeric characters.|
|alpha_dash||Returns FALSE if the form element contains anything other than alpha-numeric characters, underscores or dashes.|
|numeric||Returns FALSE if the form element contains anything other than numeric characters.|
|integer||Returns FALSE if the form element contains anything other than an integer.|
|is_natural||Returns FALSE if the form element contains anything other than a natural number: 0, 1, 2, 3, etc.|
|is_natural_no_zero||Returns FALSE if the form element contains anything other than a natural number, but not zero: 1, 2, 3, etc.|
|valid_email||Returns FALSE if the form element does not contain a valid email address.|
|valid_emails||Returns FALSE if any value provided in a comma separated list is not a valid email.|
|valid_ip||Returns FALSE if the supplied IP is not valid. Accepts an optional parameter of "IPv4" or "IPv6" to specify an IP format.|
At the moment of this writing, Databased supports importing of CSV (comma/character separated values) files only. If you have Excel sheets you'd like to import into Databased, you should export these as CSV files from Excel first after which you can import the data into Databased.
When importing data, you can either use this data to build an entire new table, or to add data to an existing table.Importing data into a new table
To create a new table using an imported CSV file, click the green button with the white "+" in front of your table tabs at the bottom of the window (in the bottom left corner). If you're not seeing this button, this means you don't have permission to create new tables and you should contact the application's administrator.
After clicking the button, open up the second tab labeled "Import data". You should start off by entering a name for your new table (make sure this name contains only alpha numeric characters: a-z, 1-9, underscored and dashes, NO SPACES!). Next, you should choose the file you'd like to import.
When importing a CSV file, it's possible that your file contains the column names on the first line of the file, if this is the case, make sure you tick the check box labeled "First row contains column names", this way Databased will use those column names to create your new table.
There two additional advanced settings available: the column delimiter and the column enclosure characters. Under normal circumstances, you don't need to change these settings, as Databased will auto-detect both. Only if you're having issues importing the data and your table is not built properly, you can try altering these settings. Be sure to investigate the structure of your CSV file before changing these settings.
Please note: since Databased requires all tables to have a primary key set, it will add a primary key column to your imported data as well. It will first detect wether the first column contains unique values, if it does, Databased will set this column as the primary key. If the first column does not contain unique values, Databased will add an additional column to your table which will function as the primary key (which will be set to auto increment).Importing data into an existing table
The second way to use the data import function is by importing data into an existing table. To do this, bring up the table into which you'd like to import data. Next, the click the grey button labeled "Import data" which will bring up a popup. In this popup you can choose you're CSV file and upload it.
Please note: when importing data into an existing table, it's trivial to make sure the structure of your CSV files matches with the structure of your table! If you have 10 columns in your table, you'll need to have 10 values per row in your CSV file. If the structures don't match, the import will probably fail and Databased will show you an error message.
When importing data into an existing table, you'll have the same two advanced settings available as when importing data into a new table: the column delimiter and the value enclosure character.
Databased is built with user roles in mind. This means that you define a certain role (for example Sales Rep, Customer Service or Accounting) and setup permissions for this Role. Next, you assign a Role to a user account which will determine what the user can or can not do.
Databased uses a combination of MySQL's implementation of user accounts and permissions and Databased's own user system. Each user account created in Databased is connected with a MySQL user, this is done to make sure users can only do what they're allowed and to keep the application and database safe from misuse.
For each Role, permissions are set on a database level AND on a table level; meaning different databases and different tables can have different permissions.
When a new user account is created, Databased will automatically send an notification email to the user's email address with the login details.
Users and Roles can be managed by users belonging to the Administration group or users who have been assigned the permission to manage Users and Roles by a Administrator.
Databased comes with built-in password recovery as well. If you or another user forgot his or her password, simply click the "Forgot your password?" link on the login page. This will send out an email with a link to the password reset page on which a new password can be created.
Please note that by default, Databased has one user (with user id "1") and one group ("Administrator" group). This role can not be modified to make sure admins can always access the application. The original Admin User could theoretically be deleted, however if there aren't any other admin users when the original admin is deleted, no one will be able to manage the application anymore. Furthermore, be sure NOT to alter the group id of the Administrator group as this might stop Databased from functioning properly.
Databased automatically stores old values of records and individual cells which can be restored at any given moment. When a cell or record is updated, the previous values are stored and can be accessed through the "Revisions" tab.
Both the Cell and Record popups have the Revisions tab, allowing to restore single cells or entire records.
At the moment, there is no expiration date set for Revisions, meaning all revisions are stored indefinitely. You can however, remove Revisions manually for any Cell or Record.
Databased allows for a column in a certain table to be connected to a second table, which means that the first column will hold value from the second table (please not that this functionality is only available for InnoDB tables, when different tables are used, this functionality will be automatically hidden).
Foreign keys will have to be declared within the application before Databased will be able to use them. When setting up existing databases, you will need to re-declare these foreign keys so that they become available in the application.
To setup a foreign key on a column, you will need to have permissions to modify tables in the database.
Once declared properly, Databased will automatically present a dropdown box displaying the allowed options when editing a single Cell or Record.
Databased allows users to attach notes to Columns, Tables and Databases. These notes can serve to describe what certain items are for and how certain items should be used.
When looking a table screen, you'll find a tab named "Notes". In here, notes for the current table can be created, edited or deleted.
When editing a column or Cell, notes can be attached to the Column through the "Notes" tab in the popup.
When editing a Record, notes can be created, edited and deleted through the "Notes" tab.
Notes can be created by ANY user, no matter what permission this user has. Administrators can edit and delete other users notes as well as their own.
To change a user's permissions, you will need to alter the permissions of the role assigned to the User question. Please note that when updating a Role, these changes will affect all Users with that same Role.
To update a Role's permissions:
Please note that in order to export data from Databased, your browser will need to have Flash installed.