Naturally, Zend Studio for Eclipse has an entire perspective devoted to the management of the data source. Select the perspective titled Database Development to see this collection of views. You should see a layout similar to that shown in Figure 9.1.

Figure 9.1. The Database Development perspective in its initial default presentation.

As you can see just by opening this perspective, some views are open that you may not have seen previously. The Data Source Explorer view is the main view on the left side of the IDE, and you will find that you use this view the most during your work within this perspective.

Before you can use this perspective to its optimal value, you have to establish some connections to a database source. Zend Studio for Eclipse has some data sources predefined for you, and in this chapter, we use the MySQL data source. In the Data Source Explorer view, right-click on the SQL Databases item to bring up the pop-up menu. Only two items come up: New and Refresh. Click on the New item to open a connectivity dialog, entitled New JDBC Connection Profile, as shown in Figure 9.2.

Figure 9.2. New JDBC connection profile dialog.

Give your database connection a useful name and description for it if you want. Then decide whether you want the connection to automatically connect each time you open the Data Source Explorer. Here you will also provide your connection authorization credentials and alternately the specific name of the database you are connecting to. Be sure to test your connection here before moving forward just to be sure you are communicating to the database correctly.

Click Next to move to the last dialog in this wizard process; here, you see a summary of the connection information that you have just established. Click Finish and you are ready to use your new database interface.

Still in the Database Development perspective, you should now be able to drill down to your named database and look at the tables and structures defined within it, as shown in Figure 9.3.

Figure 9.3. Database connection tree view in the Data Source Explorer.

Because this is a sample database being used with this book, you will, naturally, see different tables and different column names and definitions than shown here, but the overall concept is the same.

Now that we have a valid and operational connection to the local database, let’s draw some data out of the tables to see what that looks like.

Using the tree view to the data source just established, expand the tree to the Tables level (refer to Figure 9.3 if you need to). Right-click on any table name and select Data from the resulting pop-up menu; then select Sample Contents from the next pop-up menu. You trigger a sample SQL Select statement to be run against the table that you have chosen.

When this is done, you see an insertion into the SQL Results view (which is divided into two panes). On the left side, you see the status results of the run query, and on the right side is the SQL statement that was run and sample data that was retrieved. This window is shown in Figure 9.4. Note that you can combine the right display pane from two separate tabs into one by clicking on the toolbar item labeled Display Results in Single Tab in the toolbar at the top of the right pane. The mouse pointer is pointing to it in Figure 9.4.

Figure 9.4. SQL Results view showing result of sample data query.

You can also write your own SQL queries to send to the database through your SQL connection within Zend Studio for Eclipse. This is a little different in its approach. To send SQL code to the database, you need to create a new SQL-type file so that Zend Studio for Eclipse knows that it should contain SQL code and therefore be able to handle it properly. Select File, New and then select SQL File from the next-level menu. This opens the New SQL File dialog, where you can name the file and assign it to a project (see Figure 9.5).

Figure 9.5. New SQL file being assigned to a project and database source.

After you create the file, Zend Studio for Eclipse adds sql as a filename extension and opens the file in the code editing section of this perspective. As you can see in Figure 9.6, you can edit and create SQL statements that are more complex than the simple data inquiry shown in the previous section. In this figure, there is a series of drop-down lists across the top of this code editor that display the current assigned connections to this SQL file. If you have other connections that this SQL context can also interface with, flexibility is added here to change those connections on the fly and run other SQL if you so desire. After you write the SQL code that you want, you can execute it against the current connection by pressing Ctrl+Alt+X, or if you have selected SQL in a larger file to run, you can press the more simple Alt+X to run the code (these options also appear on the right-click pop-up menu for you to select).

Figure 9.6. New SQL file created and open for editing.

The data in your tables also can be edited from within this perspective. Under the Data submenu in the right-click pop-up menu, there are three other options we have not covered yet. The first is labeled Edit. When you select this option while pointing to a table, the data from that table is loaded into an Editor tab, and you are allowed to edit the contents. Keep in mind that after you edit the contents, you still have to save the changes back into the table. After you save your changes to the contents of a table, an SQL UPDATE statement is actually generated and executed against the database; this is then displayed in the SQL Results view for further scrutiny.

The other two items on the Data submenu are Load and Extract. They are basically import and export options available on a table-by-table basis.

The Database Development perspective is not limited to query execution alone. You can also delete tables and draw out the Data Definition Language (DDL) for all defined tables.

To delete an unwanted table, select that table in the Data Source Explorer, right-click, and choose Delete from the pop-up menu. This generates the drop table SQL code for you and opens it in a new code editor for you to inspect before you execute the code. Following is an example of the code generated for you:

ALTER TABLE admin_menu CHANGE COLUMN menuid menuid INT NOT NULL;
ALTER TABLE admin_menu DROP PRIMARY KEY;
DROP TABLE admin_menu;

The process to generate the DDL code for your tables is similar. Select a table, right-click, and then select Generate DDL from the pop-up menu. A dialog appears with options, as shown in Figure 9.7. Here, you can choose the generation options such as adding a DROP TABLE directive, using fully qualified names, and including comments. After you choose these options, a second wizard page provides more options. When you complete the wizard, Zend Studio for Eclipse generates the appropriate code, as shown in the following example:

CREATE TABLE admin_menu (
    menuid INT NOT NULL AUTO_INCREMENT,
    menu_order INT DEFAULT 0 NOT NULL,
    menu_text VARCHAR(50) DEFAULT '' NOT NULL,
    belongs_to VARCHAR(50) DEFAULT '' NOT NULL,
    url VARCHAR(100) DEFAULT '' NOT NULL,
    active TINYINT DEFAULT 0 NOT NULL,
    target VARCHAR(15) DEFAULT '' NOT NULL,
    PRIMARY KEY (menuid)
) ENGINE=MyISAM;

Figure 9.7. Initial wizard used to generate DDL for a table.

In this chapter you saw many ways to connect to and manipulate data in an SQL environment. You saw how to create a data source connection, run simple SQL queries against the connected database, and extract and manipulate both the data and DDL of the tables.