phpMyIDE 0.5a - Real Functionality. A First Look.

I am enormously pleased to be releasing this version, because this is what I am actually using for my projects now. With this release, I got so much going in it that I jumped from 0.3c to 0.5a. I have not put the code up at the site yet, but it should go up by the end of tonight.

First off, a couple things you need to know:

phpMyIDE requires the json_encode and json_decode function in PHP. If you are using php 5.2.[n] then these functions came automatically, if you are using a previous version then the json package would have had to been installed via PEAR or PECL or manually. I’d imagine that most providers include this package, since it is a pretty durn important one.
mysqli_ enabled: phpMyIDE will run without the mysqli class library, but the command prompt will not. Again, most hosts should have this installed because the mysql_ functions have pretty much been deprecated and lots of software now uses the mysqli library. PHP 4.1 came with the option to install mysqli, so this is nothing new. The PHP instance would have had to have the line –mysqli_enabled in the configuration directive at compile time – this is not something that you can “enable” at run time. Use phpinfo() in a web page or php –I from the command line and look for mysqli to make sure.
New Create SQL – there are a couple new tables that you’ll need to create for 0.5, so your best bet is to drop table phpmyide, create it again and let the create sql create the tables for you.
config.php file: In preparation for multiple connections, the config.php file (in the root of the project) has been restructured completely. The new file looks like this:

Code:

<?php

$siteHost = '42.phpmyide';
$siteURL = '/';
$sitePort = 80;

$connections = array();
$connections[0]['name'] = 'Local (42)';
$connections[0]['host'] = '127.0.0.1';
$connections[0]['user'] = 'username';
$connections[0]['password'] = 'password';
$connections[0]['phpmyadmin'] = 'http://42.phpmyadmin';

?>

is ever used. The phpmyadmin variable describes where the phpMyAdmin instance is for the connected server.

New editing features: Views and Scratchpads
Not earthshaking, but certainly helpful, is the completion of editing Views and the addition of a “scratchpads” area of the editor.

Scratchpads are not saveable – they are simply an area for you to type/cut/paste etc. You may have as many open at one time as you like.

Views behave as you would expect – When creating a new one, however, you’ll note that I needed to tag the view to a living database and table, so the default View code when you request a new one is:

Code:

CREATE ALGORITHM=UNDEFINED
DEFINER=username@localhost
SQL SECURITY DEFINER
VIEW selectedDB.newviewname
AS select phpmyide.preferences.key AS key,phpmyide.preferences.value AS value from phpmyide.preferences

…note how the “as” pieces are tied to phpmyide – this is where you’d change the view to the structure of the view that you wish to implement. Views are backed up in the TimeMachine just like procedures, functions and triggers.

The Command Line
Far and away the biggest change is the implementation of the Command Line. I am really pleased with both its functionality as well as the GUI. I was going for an emulation of the shell MySQL interface, because I’m an old typer and I think it works well.

Some highlights:

It supports unlimited “roll back” ie., you can scroll back and see what you’ve typed and what was responded
a couple additions to the MySQL lexicon, including “clear” (clears the terminal buffer) use (un-uses a database directive like “use phpmyide”) show use (shows the database that is currently being used) show history (show the command history) and show connection (show what connection is currently in force, defined by the config.php file.)
The up and down arrows scroll through the command history, just like the Linux shell behavior

Here is a clip from an actual session:

Code:

Note that even stored procedures with multiple responses come through to the command line perfectly.

The command line looks like a true persistent connection, but it is not. “use phpmyide” works because of a trick that I’m doing, not because you’ve got a handle to a persistent connection. So for example, a mutlti-line SQL command like this:

Code:

drop database if exists someDB;
create database someDB;

will work perfectly – the command line can execute multi-line statements. So two commands issued from the command line like this will fail:

Code:

insert into aTableWithAutoIncrement(afield) values('the value')

… then on the next line…

Code:

select LAST_INSERT_ID()

the LAST_INSERT_ID is undefined because it’s not a persistent connection. However, if you typed that last SQL into a scratchpad, copy & pasted it as multiple commands thus:

Code:

insert into aTableWithAutoIncrement(afield) values(‘the value’);
select LAST_INSERT_ID();

it will work, because both commands were executed on the same connection.

Select * from a ReallyBigTable will also work just fine, because of the way that I “collect” multiple responses at the server and then feed them back out to the client at about 128 lines per Ajax request. There is a “cancel” button if you selected from a table and want to stop the response. Here we can see me doing a selection against a spider IP table. The SQL was:

Code:

select id, engineid, address, useragent from spidertrack_spiders where useragent > ' ' order by useragent

which produces about 29K results. I pressed the Cancel button midway:

A Simple First Session
What I’d like to do now is actually run through the steps of implementing a stored procedure, trigger and view, using the features of phpMyIDE. In this example, we will create a new database, add two tables to it with a one-to-many relationship. Table 2 will contain rows of data that should be summed up into Table 1. We’ll use a procedure to do the actually summation, a trigger to fire it off and a view to display it. I’ll also demonstrate using phpMyAdmin inside of phpMyIDE.

So, off we go. Assuming we just started up, our window looks like this:

The first thing to do is create our database. Clicking on the Command Prompt tab we execute 2 SQL statements.
Clicking refresh on the Script Explorer shows us that the database we created does indeed exist.

Just for giggles, enter a couple SQL commands to see how the command prompt works:

use phpmyide
show tables
show databases
show status
select * from preferences
clear

Let’s use phpMyAdmin to add our two tables. Select the phpMyAdmin tab, select our new DB and create two tables as such:

Alternately, you could use the Command Prompt to execute this multi-line SQL:



use phptest;

CREATE TABLE tablea (
  id int(11) NOT NULL auto_increment,
  tablebsum int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE tableb (
  id int(11) NOT NULL auto_increment,
  tablea_id int(11) NOT NULL,
  quantity int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now when clicking on the new database in the Script Explorer we can see our tables:

Note that with the Database Explorer we can see the structure of the tables as well, which may be important later if we have the Editor or Command Prompt pane up and can’t see phpMyAdmin:

Now let's use a mutli-line SQL statement to insert a record into table A, and then table B (we know that the ID of table A is going to be 1, so this will work OK):


use phptest;
insert into tablea() values();
insert into tableb(tablea_id, quantity) values(1, 5);
insert into tableb(tablea_id, quantity) values(1, 7);
insert into tableb(tablea_id, quantity) values(1, 9);

Show that our work has borne fruit:

Time for our first procedure. Make sure the Script Explorer tab is open, click on our new DB, then the Procedures radio button. Click the “New” icon over the files area, and name our new procedure tablesum. You can see that the IDE has created the procedure, updated the Procedures window to show it’s existence and opened it up for you in the editing pane:

The essence of our stored procedure will be to accept an “ID” as a parameter, then do a sum for each row in Table B with that tablea_id and put it into the Table A sum field. Our stored procedure looks like the image below. Save the procedure by clicking on the Save icon in the Editor pane:

Click on the command tab and execute the stored procedure. Remember to supply the one parameter, the TableA id to the stored procedure. Then "select all" from the items from Table A and we can see that tableA was updated with the sum of rows in tableb:

Now it’s time for a trigger. What we want is anytime a row is modified in Table B, to fire our stored procedure to re-sum for Table A. From the Script Explorer, make sure you're on the phptest database then click on the Triggers radio button. We're putting a trigger on tableb because we want tablea updated anytime something happens to tableb. Let's start with the After Update trigger. Click on the New icon on the Trigger Grid to create a trigger placeholder and default code (Sorry the picture is a little squished):

Now let’s write the trigger code. Basically we’re going to tell MySQL to call our procedure with the ID of the row that was just modified. Remember to save the trigger by clicking on the Save icon:

Now you’ll need to put the trigger online, because so far it is only stored in the DB but not in the trigger position. Click the on/off switch in the trigger grid to enable the trigger. One of two things will happen: it will either “go green” meaning that the trigger is online, or it will stay red and you’ll get a syntax (or other) error. For now, we’ll assume that you’ve entered the trigger correctly and it is now online:

Go to the phpMyAdmin pane, browse Table B, edit one of the quantity records and save it. I'm going to change row 2 to 25. Then go to the Command Prompt and press the up arrow to bring back the “Select * from Table A” SQL line:

Whoohoo! When we edited in phpMyAdmin, the trigger called our procedure! Now let’s implement a trigger for After Insert. Just as we did before, click on the new icon for the After Insert trigger, add the exact same code to it, save it and put it online. Now we want to do the After Delete trigger, but there’s a little change – we cannot use the “NEW” keyword, because there isn’t a new record after a delete – only an insert or update. So we use the “OLD” keyword:

Put the Delete trigger online and you’re all set! With these 3 in the green, we know that we are enforcing our logic on every possible action against tableb:

Now let’s implement a view to show us Table A. Click on the Views radio button and then the New icon. Name the view, “coolness.” Again, the editor will create default code and open the file for you. Change the view so that the code looks like this:

Save the view. If you get no message, things are good. Go back to the Command Prompt and type “select * from coolness” :

That's about it. As I said, I should have the new code up by the end of the day or tomorrow at the latest. I'll post in Announcements when it's ready to go.

/perk

	Welcome, Guest. Please login or register.	September 03, 2010, 05:35:45 AM
		Login with username, password and session length