Configuring Aptana to connect MySql databases

Using Aptana is great but having to constantly switch to PhpMyadmin to check Mysql table structures can be tiring (even if [CMD + TAB] or [CTRL + TAB] makes it a bit easier). It may then be useful to configure a MySql connection in Aptana.

This post will tackle the configuration on Mac OSx 10.6 with MAMP installed. But you could easily configure Aptana the same way on LAMP (Linux) or WAMP (Windows) platforms.

Therefore, you’ll need the MySql Connector/J driver that can be found on http://www.mysql.com/downloads/connector/j/. Download and unzip it in the /Mamp/Library/share/Mysql/Connectors/ folder.

1. Install Sql Explorer plugin in Aptana

First, you’ll need to install the Sql Explorer plugin for Aptana : Select Menu Help / Install Software, then click “Add” to add a site for the plugin install. Give it a name (MySql) and the following location : http://eclipsesql.sourceforge.net/

Then select your newly created source site from the combo box and tick “SQL Explorer” before clicking the “finish” button :

Add download site for Sql Explorer

2. Create a MySql connection

Open Aptana and select Menu Window / Open perspective / Other / SQL Explorer. Then select Menu Window / Showview / Connections to create a new connection to Mysql :

Window - Showview - Connections (create a MySql connection)

Click on the “Create new connection profile” button to configure the MySql driver : Create new database connection profile to display :

Configure Mysql connection

Give a name to your connection : Mysql

Then click on “Add/Edit drivers”, then expand the “SQL Explorer” from the left menu and click on “JDBC Drivers” and select “MySql Driver”:

Edit MySql driver

Then click the “Copy” button to edit preferences on a copy of the item and give your driver a new name (My MySql driver) :

Editing Mysql driver

Edit the example URL as follows :

jdbc:mysql://localhost:3306/

You still need to change the port since port 3306 is the default MySql connection port. Open MAMP and click Preferences / Ports :

Checking MAMP MySql connection port

then change the MySql driver example URL :

jdbc:mysql://localhost:8889/

Then click the “Extra class path” button to add the MySql Connector/J driver (file mysql-connector-java-5.1.14-bin.jar) that is to be found in /Mamp/Library/share/Mysql/Connectors/.

and fill in the Driver class name as follows :

com.mysql.jdbc.Driver

Your driver preferences dialog should look like this :

Mysql driver preferences

Then click “OK”. Aptana now shows your driver as being correctly configured :

Mysql driver correctly configured

Click “OK” to select “My MySql Driver” for the present connection.

In the “Create new connection profile” dialog, select the newly created Mysql driver (My Mysql Driver) from the combo box. Tick “auto logon” and type the MySql root username and password :

Set MySql username and password

and click “OK”. Your Connection is now ready :

MySql connection ready

Right click your “Mysql” connection to view database structures :

View Database structures

Local Web Servers

As a webdeveloper on a Server Linux distro as OpenSuse, you simply have to activate and configure Apache and MySql.

If you’re working on a Windows station, you need to install a web server application to execute your PHP scripts.

Years ago, I used to work with EasyPhp, which stopped to evolve for some time 3 or 4 years ago. So I switched to WampServer at the time, which is still the one I use daily. It provides you with Apache, Mysql, Phpmyadmin (php scripts to handle your databases). It is really easy to install and has the up-to-date versions of PHP and MySql available.

I’ve recently come across ZMWS. ZazouMiniWebServer is a standalone webserver that can be installed on a Windows machine. It is released with PHP4 and PHP5 but you could also add handlers to make it work with ASP, PERL,… :

I’ve tested ZMWS on a USB stick as a portable web server.

You could distribute your PHP web project on a CD and have an auto-browsable dynamic website.

The website of the ZMWS Open Source project provides you with documentation and a forum.

Webliography :

ZazouMiniWebServer

WampServer

Database normalization

Database normalization provides a set of easy-to-apply principles that can help the developer structuring its database and improve MySql performance.

Normalizing a database is to organize its data more efficiently thanks to normal rules or principles. For more efficiency, building tables and the relations between the tables will be the result of the normalization process. DB normalization ensures :

  • an optimized structure that does not waste disk space (the same data should not be found in more than one table for obvious reasons : updating several tables for the same data is absurd)
  • logical dependencies (implies storing relevant data inside or related to the right table)

Normal forms

I’ll stick here to the 3 first rules, guidelines that db-literates call Normal Forms. These rules should add enough efficiency to your database. Keep in mind you may sometimes prefer not to apply a rule for practical reasons. Indeed, normalization consists in adding relation tables, for example, that will require the developer to change his MySql queries.

Normal forms must be applied in the right order : further normalisation can only take place if the previous normalisation rule has already been applied.

For more convenience, I’ll take the example of a discography database that will store albums of famous artists.

Creating the database on the fly with no normalization experience could result in the creation of one single table :

records_table :

record_ID : 1 (defined as Primary_KEY)

artist : Fleetwood Mac is a British-American band that formed in 1967…

record_title : The pious bird of good omen

record_style : Blues, Rock, Pop

record_label : blue horizon

record_release : 1969

With this very simple table, one can easily understand that :

  • a SELECT query cannot get information about the artist without a record_ID
  • an UPDATE query that wants to modify the artist biography will have to do so in every row that is associated with that artist
  • a DELETE query will delete the record information as well as the artist information

These limitations may produce anomalies such as data corruption, MySql resource problems (with LIKE subqueries),… So, it’s time to normalize that database and we will do it thanks to the relational theory.

Relational theory

1NF => 1st Normal Form : the atomic form

Each table field should have an atomic value, i.e. only one single value in a column of your table. In the above example, the fields ‘artist’ and ‘record_style’ contain more than one value. This is why applying 1NF will result in the creation of 2 extra tables :

records_table :

record_ID : 1 (defined as Primary_KEY)

record_title : The pious bird of good omen

record_release : 1969

and

artists_table :

artist_ID : 1 (defined as Primary Key)

artist_name : Fleetwood Mac

artist_bio : Fleetwood Mac is a British-American band that formed in 1967…

and

styles_table (with 3 rows) :

style_ID : 1 (defined as Primary Key)

style_name : blues

style_ID : 2

style_name : pop

style_ID : 3

style_name : rock

and

labels_table :

label_ID : 1 (defined as Primary Key)

label_name : Blue Horizon

You’ll easily understand it is now easy to add an artist without adding a record from that artist, you can delete a record without deleting artist information,…

Data is now separated in different tables. We still have to create relations between these tables. Database relations can be of the following types:

1 to 1 : one element of table A corresponds to one element of table B and that element from table B corresponds to only one element of table A

1 to N : a record from the records_table can have only one label but that label may have more than one records. In that case, we add a foreign key to the records table containing the ID of the label

N to N : a record from the records_table can have more than one artist and an artist from the artists_table can have more than one record. In that case, we implement a third table called records_artists_table, a relational table that will link the records_table with the artists_table

So, let’s add a foreign key to the records_table :

records_table : 1 to N relationship

record_ID : 1 (defined as Primary_KEY)

record_title : The pious bird of good omen

record_release : 1969

label_ID : 1

and create the 2 new tables :

records_artists_table : N to N relationship

record_ID : 1

artist_ID : 1

We could also create the same kind of relationship between the records and the styles of music :

records_styles_table : N to N relationship

record_ID : 1

style_ID : 1

2NF => 2nd Normal Form : the relationships to keys

This Normal Form ensures non-key columns strictly depend on the primary key. So, in case of a composite primary key (a row in the table is identified thanks to the record_ID and the author_ID), all non-key columns must depend on the 2 fields, and not one of them only. Imagine the following table structure :

records_reviews table :

record_ID : 1 (defined as primary key with the following field)

author_ID : 1 (defined as primary key with the previous field)

review : This compilation album is one of my favourites…

author_website : http://www.domain.com

In the above example, the field author_website doesn’t strictly depend on the composite key record_ID-author_ID. It only depends on the author_ID. Applying the second normal form will result in moving the author_website field to the author table.

3NF => 3rd Normal Form

With the third normal form, every field must depend on the primary key. In other words, a field that depends on another column than a primary key violates the 3NF. Addresses in a table are a good example of 3NF violation. Imagine the following table structure :
labels_table :
label_ID : 1 (primary key)
label_name : Blue Horizon
label_zip : 1770
label_city : Sherborn
label_state : MA
In this table, the fields label_city and label_state strictly depend on the label_zip field, and not to the label_ID primary key. In order to make this table 3NF compliant, we have to create a zip table :
zip table :
zip_ID : 1770
zip_city : Sherborn
zip_state : MA
Then, the labels table will look like the following :
labels_table :
label_ID : 1 (primary key)
label_name : Blue Horizon
zip_ID : 1770
For some reasons, the zip_state field may also require a specific states table :
states_table :
state_ID : 1 (primary key)
state_name : MA
Normalization as stated above allows you to build a clean database structure but also implies more complexity in your application. Indeed, you’ll have to change your database queries. Your application will deal with more tables, so you’ll have to join tables in your queries, which can result in less performance for your applications.
I would advise developers to be aware of normalization, and to stop normalizing their tables just before the tables structure becomes too complicated. In our example, retrieving one row from the labels table results in creating the following query :
SELECT * FROM labels as A INNER JOIN zip as B ON B.zip_ID=A.zip_ID INNER JOIN states as C ON C.state_ID=B.state_ID WHERE A.label_ID=’1′ LIMIT 1;
Webliography :

MySql Introduction to db normalization

W.J. Gilmore’s introduction to database normalization

W. Kent’s simple guide to 5 normal forms

MySql Fulltext search

Scripting an internal search engine requires :

  • a client form to submit requests
  • database fields to search posted keywords
  • database queries to match the form request

I want to focus on the database requirements to make such a search.

I used to build queries with sub-queries of the following type :

SELECT id,title,content from table_name WHERE title LIKE ‘%”.$search.”%’ OR content LIKE ‘%”.$search.”%’;

which performs a kind of regular expression search and can lead to MySql resource problems (large number of rows parsed, large number of results found,…).

Under MySql, a FULLTEXT search may solve this resource problem and achieve the search since it performs the search in indexed data. This feature allows

  • stopwords (words which will be excluded by the server itself)
  • boolean searches (using + or -, for example, to add to search or substract from search)
  • relevancy scoring

Type of table that allows fulltext indexing

FULLTEXT search is available in MyISAM tables. Though the InnoDB Storage Engine offers transactions (commit and rollback error handling), the fulltext search feature is absent. So, let’s stick to MyISAM tables.

You can define a FULLTEXT index on one or more table fields of types CHAR, VARCHAR and TEXT. As stated in MySql reference on the subject, creating rows in a table with fulltext index will take longer. So, if lots of rows need to be treated with a fulltext index (a fulltext index will be most efficient in this very case), it is better to first insert the rows, then create the index.

You can add a fulltext index on the 2 fields “title” and “content” with the following query :

ALTER TABLE table_name ADD FULLTEXT index_name (‘title’ , ‘content’);

Once the table has a fulltext index defined, the query can be the following :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘);

The fields in the MATCH statement have to be the same as defined in the table’s fulltext index, and you can define a fulltext index on one or more field.

Fulltext search parameters

MySql is configured with a set of automatic parameters :

  • the results are ordered by the relevancy score MySql gives to each row found
  • words with less than 4 characters will be ignored
  • the fulltext index contains complete words only
  • depending on the language MySql is set to, a list of stopwords will be automatically ignored
  • the score for each row depends on the number of occurences of that word in the table. If a word occurs in more than half the lines, it will be ignored

Changing these default parameters will only be possible if your MySql Web Server allows you to modify those parameters.

Boolean mode search

The MATCH… AGAINST… statement allows you to perform a Boolean search :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘”.$search.”‘ IN BOOLEAN MODE);

MySql will then return all rows matching the searched words even if more than 50% of the rows contain the keyword.

Boolean searches allow you to use operators like + or – to force MySql to return rows containing ‘works’ and hide rows containing ‘tables’ :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘+works -tables’ IN BOOLEAN MODE);

You can also use the * operator as a wildcard in Boolean mode to search part of a word :

SELECT id,title,content from table_name WHERE MATCH(title,content) AGAINST (‘peopl*’ IN BOOLEAN MODE);

Webliography :

MySql FULLTEXT search reference

MySql FULLTEXT search – Boolean mode reference

PHP5 to watch over MySql queries

If the developer wants to write more efficient MySql queries, he may want to display all the queries a web page has generated and the time the whole script took to output its Html result. This is useful while developing. Once the website is launched, you may get rid of these script lines (just comment them with // or /* */) in order not to take extra CPU time.

Changes in the MySql class

In order to count MySql queries generated by a web page and display the list of them, he should add a static variable to his MySql Class :

public static $queries = array();

When initializing the class, the static variable $queries becomes an empty array. Each time a query will be sent to the class for execution, array $queries will be incremented this way :
array_push(self::$queries,$query);
Before destroying the $mysql object
return count(self::$queries);
will return the number of MySql queries generated by a Web page.
return self::$queries;
will return the array containing all MySql queries for the current web page.

PHP script execution time

In order to check how long it takes a script from start to end, just work with microtime :
<?php
//top of PHP script
$start_time = microtime(true); // >= PHP 5.0.0 gives microtime as a float
//bottom of PHP script
$end_time = microtime(true);
$total_time = $end_time – $start_time;
?>

MySql5 – Multiple queries

If you want to read more about MySql5 improvements, please read the official release notes.

The aim of this post is to write more efficient MySql queries. Some servers watch over the MySql resource a web page uses. If the number of requests for one single web page is considered too resource-hungry, the developer may receive a warning (or more) from the host!

Read article “PHP5 to watch over MySql queries” if you want PHP to display MySql queries generated in a specific Web page.

What I want to focus on here is a syntax that was introduced in MySql 4.1 but which is still rarely used :

INSERT INTO… ON DUPLICATE KEY UPDATE…

But first, let’s develop multiple queries for deletion.

Deleting several rows in one single query (DELETE)

Instead of dynamically producing queries in order to delete rows in a table :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

FOREACH($array as $value) {

$sql = “DELETE FROM table_name WHERE id=’$value’”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 3 queries, you can do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= ” OR “;

$sql .= “id=’$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE “.$sql; //builds the single query : DELETE FROM table_name WHERE id=’1′ OR id=’2′ OR id=’4′;

$mysql->strictExecute($sql); //Execute query through $mysql object

which will produce one single query to DB.

I would even advise using the IN predicate :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$array = array(1,2,4); //rows to be deleted

$sql = “”;

FOREACH($array as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “‘$value’”; //adds string id=’$value’ to $sql

}

$sql = “DELETE FROM table_name WHERE id IN (“.$sql.”)”; //builds the single query : DELETE FROM table_name WHERE id IN (’1′, ’2′, ’4′);

$mysql->strictExecute($sql); //Execute query through $mysql object

Inserting several rows in one single query (INSERT TO)

In order to insert multiple rows in one query, do the following :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“title”=>”Mysql explained”,”n_pages”=>”350″),

1=>array(“title”=>”PHP explained”,”n_pages”=>”150″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “, “;

$sql .= “(‘”.$value["title"].”‘,’”.$value["n_pages"].”‘)”;

}

$sql = “INSERT INTO table_name(title,n_pages) VALUES”.$sql;

$mysql->strictExecute($sql); //Execute query through $mysql object

Updating several rows in one single query (INSERT INTO… ON DUPLICATE KEY UPDATE)

Updating MySql rows can be done this way :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

$sql .= “UPDATE table_name SET title=’”.$value["title"].”‘,n_pages=’”.$value["n_pages"].”‘ WHERE id=’”.$value["id"].”‘”;

$mysql->strictExecute($sql); //Execute query through $mysql object

}

which will produce 2 queries.

The trick here is to generate an INSERT INTO query with the ON DUPLICATE KEY UPDATE syntax. In case the key already exists, only an update will take place. If the key doesn’t exist, Mysql will operate a classical insert into query :

$mysql = Mysql(); //initiate object

$mysql->Connect(); //initiate connection to DB

$books = array( 0=>array(“id”=>1,”title”=>”Mysql explained”,”n_pages”=>”450″),

1=>array(“id”=>2,”title”=>”PHP explained”,”n_pages”=>”450″)

); //rows to be inserted

$sql = “”;

FOREACH($books as $value) {

IF($sql <> “”) $sql .= “,”;

$sql .= (‘”.$value["id"].”‘,’”.$value["title"].”‘,’”.$value["n_pages"].”‘);

}

$sql = “INSERT INTO table_name(id,title,n_pages) VALUES”.$sql.” ON DUPLICATE KEY UPDATEid=VALUES(id),title=VALUES(title),n_pages=VALUES(n_pages)”;

$mysql->strictExecute($sql); //Execute query through $mysql object

Webliography :

MySql5 Release notes

PhpMyadmin no authentication

PhpMyadmin is a free software developed in PHP to manage MySql databases.

For local development purposes only, the developer may want to skip authentication while connecting to PhpMyadmin. For obvious security reasons, do not choose this configuration on a web server.

Skipping authentication is possible if you modify /config.inc.php file with the following lines :

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = ‘config’; //default value : ‘cookie’
/* Server parameters */
$cfg['Servers'][$i]['host'] = ‘localhost’;
$cfg['Servers'][$i]['user'] = ‘root’; //type MySql user as variable value
$cfg['Servers'][$i]['password'] = ”; //type MySql password as variable value

tested on PhpMyadmin 3.2.4

Webliography :

PhpMyadmin.net