Databased Documentation


current version: 1.4.2

This document can also be found online at databasedapp.com/doc.html


Introduction

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.

Upgrading

Below you'll find information on how to upgrade.

v1 to v1.1.1
  • Check your database to see if the table “dbapp_columnrestrictions” exists. If not, execute the following SQL statement:
    "CREATE TABLE IF NOT EXISTS `dbapp_columnrestrictions` ( `dbapp_columnrestrictions_id` int(11) NOT NULL AUTO_INCREMENT, `dbapp_columnrestrictions_database` varchar(255) NOT NULL, `dbapp_columnrestrictions_table` varchar(255) NOT NULL, `dbapp_columnrestrictions_column` varchar(255) NOT NULL, `dbapp_columnrestrictions_restrictions` text NOT NULL, PRIMARY KEY (`dbapp_columnrestrictions_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;"
  • Update the following folders:
    – /application (every subfolder except for the /application/config folder)
    – /css
    – /js
v1.1.1 to v1.2.1
  • Locate the file "v1.1.1-to-v1.2.1.sql" in the /db_upgrade folder and execute the SQL statements in this file to update your main Databased database.
  • Update (overwrite) the following folders:
    - /application (every subfolder except for /application/config/)
    - /assets
    - /css
    - /custom
    - /js
v1.2.1 to v1.2.2
  • Update (overwrite) the following folders:
    - /application (every subfolder except for /application/config/)
v1.2.2 to v1.3.1
  • Update (overwrite) the following folders:
    - /application (every subfolder except for /application/config/)
    - /css
    - /js
  • Create a folder named "tmp" in the root folder of your application (as a sibling of the application folder) and make sure it's writable by the web server (to be sure, you can set the permissions to 777).
v1.3.1 to v1.3.2
  • Update (overwrite) the following folders:
    - /application (in the folder /application/config/ only replace the file "autoload.php")
    - /css
    - /js
v1.3.1 to v1.4.1
  • Update (overwrite) the following folders and files:
    - .htaccess
    - /application (every subfolder except for /application/config/)
    - /assets
    - /css
    - /images
    - /js
  • Create a new folder named "/uploads" and make sure it's writable. This folder has to be a sibling of /application (meaning it should be on the same level).
v1.4.1 to v1.4.2
  • Update (overwrite) the following folders and files:
    - /application/views/admin/db.php
v1.4.2 to v1.4.3
  • Update (overwrite) the following folders and files:
    - /application/views/table/table.php

Before Installing

Requirements

To be able to install Databased, you must have the following:

  • PHP 5.1.6 (older versions might work)
  • Apache webserver
  • MySQL with support for InnoDB tables and foreign keys
  • Root access to your MySQL server
  • phpMyAdmin access to setup the initial database
  • An FTP tool to upload the files

Structure

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/):
  • account.php
  • admin.php
  • columnnotes.php
  • columns.php
  • dashboard.php
  • db.php
  • recordnotes.php
  • revisions.php
  • roles.php
  • table_datasource.php
  • tablenotes.php
  • users.php
Models (/application/models/):
  • columnnote_model.php
  • db_model.php
  • issue_model.php
  • recordnote_model.php
  • relation_model.php
  • revision_model.php
  • role_model.php
  • table_model.php
  • tablenote_model.php
  • user_model.php
Views (/application/views/)

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:

  • /bootstrap (contains basic bootstrap items)
  • /css (contains custom css)
  • /custom (contains less+css)
  • /fonts (contains the fonts used in Databased)
  • /images (contains images used in Databased)
  • /js (contains the Flat UI Pro javascript files)
  • /less (contains the original Flat UI Pro less files)
Additional files/folders
  • /assets (contains some additional jQuery plugins + support files)
  • /swf (contains files used by the jQuery DataTables plugin)

CodeIgniter

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 :)


Installation

Step 1: Upload Files

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.

Step 2: Setup MySQL Database

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.

Step 3: Configuration

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.

Your first login

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:

  • user: [email protected]
  • pass: password

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.

Features

Databases

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.

Tables

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.

Columns

At the time of this writing, Databased supports the following column types:

  • Numbers (up to 11 digits)
  • Small text (up to 255 characters)
  • Big text
  • Date
  • Select (define several values from the user can choose one, will result in a select dropdown being displayed)
Files and images

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:

Rule Description Example
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[6]
max_length Returns FALSE if the form element is longer then the parameter value. max_length[12]
exact_length Returns FALSE if the form element is not exactly the parameter value. exact_length[8]
greater_than Returns FALSE if the form element is less than the parameter value or not numeric. greater_than[8]
less_than Returns FALSE if the form element is greater than the parameter value or not numeric. less_than[8]
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.

Importing data into Databased

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.

Roles, Users and Permissions

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.

Administrators
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.

Revisions

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.

Relational Data

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.

Notes

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.

How do I...?


Enable an existing database in Databased

  1. Make sure you have Administration rights
  2. Log into the application using your username and password
  3. Open the Database drop down from the top menu and choose "Manage databases"
  4. Locate the database you wish to enable and make sure it has a GREEN circle in front of the name (note that if you'll see a red circle instead, this database can not be enabled in Databased)
  5. Click the panel to open it up
  6. Check the check box labeled "Enable database in Databased"

Create a new database in Databased

  1. Make sure you have Administration rights
  2. Log into the application using your username and password
  3. Open the Database drop down from the top menu and choose "Manage databases"
  4. Click on the tab labeled "Create Database"
  5. Enter a name for the new database
  6. Check the check box labeled "Enable in Databased" if you want to enable the database
  7. Click the "Create database" button

Add a table to a database

  1. Make sure you have permission to modify the database in question
  2. Log into the application using your username and password
  3. Open the Database drop down from the top menu and choose the database you want to work on
  4. Look towards the bottom left corner and look for a green button with a white + sign in it. Click this button
  5. Enter a name for the new table and create the desired columns
  6. When ready, click the button labeled "Add table" to create the new table

Setup a foreign key on a column

  1. Make sure you have permission to modify the table in question
  2. Log into the application using your username and password
  3. Open the Database drop down from the top menu and choose the database you want to work on
  4. Navigate to the table you want to work on using the tabs at the bottom of the page
  5. Click on the second tab (labeled "Table columns")
  6. Find the column you want to create the foreign key for and click the "edit" button for this column
  7. In the popup, find the section labeled "Connect to" and choose the "table > column" you want this column to link to
  8. Click the button labeled "Update column"

Customise the data view

  1. Log into the application using your username and password
  2. Open the Database drop down from the top menu and choose the database you want to work on
  3. Navigate to the table you want to work on using the tabs at the bottom of the page
  4. Click on the second tab (labeled "Table columns")
  5. Use the checkboxes on the left of each column to control which columns appear in the data view and which don't
  6. When you're ready, click the grey button labeled "Reload data view"

Rename a table

  1. Make sure you have permission to modify the table in question
  2. Log into the application using your username and password
  3. Open the Database drop down from the top menu and choose the database you want to work on
  4. Navigate to the table you want to work on using the tabs at the bottom of the page
  5. Click on the tab labeled "More"
  6. Type the new name into the name field
  7. Click the button labeled "Update table"

Change a user's details

  1. Make sure you have Admin permissions or are allowed to manage Users and Roles
  2. Log into the application using your username and password
  3. Click on "Users" in the top menu
  4. Select the user from the left menu
  5. Make the desired changes
  6. Click the green update button

Change a user's permissions

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:

  1. Make sure you have Admin permissions or are allowed to manage Users and Roles
  2. Log into the application using your username and password
  3. Click "Roles & Permissions" from the top menu
  4. Select the Role from the left menu
  5. You will be able to set permissions on each database and table
  6. Click the button labeled "Save Permissions"

How do I export data from Databased?

Please note that in order to export data from Databased, your browser will need to have Flash installed.

  • Log into the application using your username and password
  • Open the Database drop down from the top menu and choose the database you want to export data from
  • Navigate to the table you want to export data from using the tabs at the bottom of the page
  • Use the search bar filter in the top right corner to load the records you'd like to export
  • Click one of the CSV/Excel/PDF buttons to choose your download format and your download will begin