Tracking mechanism for phpMyAdmin

Status report August 11th - August 18th

2009-08-18T08:15:00.000-07:00

http://page.mi.fu-berlin.de/rutkowsk/phpMyAdmin/howto.avi

KEY ACCOMPLISHMENTS LAST WEEK
=============================

- Wrote additional documentation, added information about the
config vars to Documentation.html

- Used add_message(_file).sh to add lang vars.

- Made the tracking of SQL dump imports working.

- Created a short how-to video (see my blog)

- Asked friends to evaluate my project.

- Registered to PMA wiki, but can't edit all articles.

KEY TASKS THAT STALLED
======================
--

KEY CONCERNS
============
Evaluation

TASKS IN THE UPCOMING WEEK(s)
==========================
- Improve the mechanism

Status report August 3rd - August 10th

2009-08-10T07:05:00.000-07:00

Hi all,

KEY ACCOMPLISHMENTS LAST WEEK
=============================

- I added an user-friendly visualisation of the structure snapshots.
It is similar to the structure page of phpMyAdmin.

- Secondly I added handlers for CREATE/DROP/ALTER DATABASE.
Tracking for these commands is enabled by default. User has not to
create a version for it, it will created by the Tracker autmatically.

- An output for these tracked commands is available on the "Tracking"
page for databases now.

- Added a line number to the tracking report.

KEY TASKS THAT STALLED
======================
Evaluation

KEY CONCERNS
============
Evaluation, Documentation, Optimization.

TASKS IN THE UPCOMING WEEK
==========================
- Try to get feedback from users and developers.
- Write documentation, improve my code.

Best regards

Alexander

Status report July 25th - August 2nd

2009-08-02T16:40:00.000-07:00

Hi all,

my report is a little bit delayed, my great-grandfather had 90th birthday on friday. I hope I will reach this age too ;)

KEY ACCOMPLISHMENTS LAST WEEK
=============================

- Created an overview page for (un)tracked tables/views in a database. It gives a short status report of tracked tables/views and links to the "create version" page for untracked tables.

- A new grey-colored icon indicates whether a table has versions but deactive tracking status.

- Added handlers for CREATE/DROP INDEX, CREATE/ALTER/DROP VIEW.

- Auto-creation functionality implemented. It creates a version of new tables and views automatically.

- Some new config vars added. Here a short introduction:

// Creates tracking of new tables and views automatically. Default value is true.
$cfg['Servers'][$i]['tracking_version_auto_create'] = true|false

// Adds "DROP VIEW" statement as prefix of "CREATE VIEW" logs. Default value is true.
$cfg['Servers'][$i]['tracking_add_drop_view'] = true|false

// Adds "DROP TABLE" statement as prefix of "CREATE TABLE" logs. Default value is true.
$cfg['Servers'][$i]['tracking_add_drop_table'] = true|false

// Defines the statements which the auto-creation mechanism uses for new tracked versions.
// Default value is
// CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,
// CREATE INDEX,DROP INDEX,
// INSERT,UPDATE,DELETE,TRUNCATE,REPLACE,
// CREATE VIEW,ALTER VIEW,DROP VIEW,
// CREATE DATABASE,ALTER DATABASE,DROP DATABASE;

$cfg['Servers'][$i]['tracking_default_statements'] = ...

KEY TASKS THAT STALLED
======================
-

KEY CONCERNS
============
Finishing list of deliverables.

TASKS IN THE UPCOMING WEEK
==========================
- I want to focus on structure snapshots. The snapshot is saved in a php array.
A user-friendly visualisation of it would be fine. Comparison of snapshots is an idea.

- Secondly I want to add an ouput of tracked CREATE/DROP/ALTER DATABASE statements
to the "Tracking" overview page for databases.

- When time is left: start first phase of testing and evaluation. Try to get more feedback from users and developers.

Best regards

Alexander

Status report July 18th - 24th

2009-07-24T13:15:00.000-07:00

Hi all,

KEY ACCOMPLISHMENTS LAST WEEK
=============================
- I added a separate filter functionality to the tracking report.
Now it is possible to list data definition, data manipulation
or both kinds of statements. Date ranges are supported.

- Secondly I added an user filter. PMA_Tracker tracks the
logged in username. The tracking report page supports
to list all tracked usernames (*), one username or a list
of usernames with ',' as delimiter.

- The export form uses the described filter settings.

- I added a new export option: "SQL dump as file download".
Users can directly download the statements.

- I did some work on the SQL dump page. It shows the SQL query
form now. The lines

CREATE database IF NOT EXISTS pma_temp_db;
USE pma_temp_db;

will automatically added to the dump. The user can copy the whole
dump to the clipboard or execute it by clicking on the submit button.
If he submits it he gets a "temporary" table and can do fine things
with it (use PMA export functionality for example).

- I decided to cut off `dbname` . prefixes from tracked statements.
I used a Perl regular expression for that. Only without these prefixes
the dump export to other databases is possible.

- I merged my Subversion branch with trunk/phpMyAdmin and
committed the changes. Not a big job, but good to see that it worked.

- German translation added.

KEY TASKS THAT STALLED
======================
I tried to use libraries/sqlparser.lib.php within my PMA_Tracker
but it produces a lot of notices.
In addition PMA_SQP_analyze() works only for SELECT queries.
Did not see an easy (time-efficient) way to enable it for my PMA_Tracker
so I reduced the queue priority for this job.

KEY CONCERNS
============
Tracking for whole databases is still open.

TASKS IN THE UPCOMING WEEK
==========================
- Create first prototype of tracking page for databases / set of tables.
- Add handlers for CREATE/DROP INDEX, CREATE/ALTER/DROP VIEW.
- Inform users about deactivated tracking versions (use an icon or
notice message).

Finally I want to call attention to

http://demo.phpmyadmin.net/gsoc-alex/

Its a copy of my svn branch (thanks to Michal).

Best regards and have a nice WE

Alexander

Status report July 13th - 17th

2009-07-17T13:34:00.001-07:00

Hi all,

here is my report:

KEY ACCOMPLISHMENTS LAST WEEK
=============================
- I got my own SVN branch phpmyadmin/branches/gsoc/alex/phpMyAdmin
(thanks to Marc for the permissions).

- SVN copy from trunk/phpMyAdmin was unexpected not easy for me, because I made the mistake to use http instead of https and so I got the error message "502 BAD GATEWAY". First I thought it was a problem with my subclipse plugin but then I found the hint about the protocol and then the copy process worked fine.

- Switched from Eclipse 3.2 to NetBeans IDE 6.7. PHP eclipse nerved
me because it was sometimes unstable and did not open some files. NetBeans seems to be more optimized for PHP development.

- Did some work on my code too :)
Here is a summary of my committed changes to SVN branch:

+ First tracking mechanism patch applied.
+ Problems causing PHP notices solved.
+ Some useless PMA_DBI_select_db() calls removed.
+ Bug: after renaming a table the list of tracking versions is broken (Fixed)
+ Using PMA_query_as_controluser where it is possible.
+ New tab "Tracking" for the table view added.
+ A little bit PMA_Tracker code refactoring.
+ SQL dump of pma_tracking to scripts/create_tables.sql added.
+ Localization introduced, using string vars in english-utf-8.inc.php for outputs.
+ Short php tags and short print instruction removed how it is recommended in PEAR coding standards.
(Thanks to Michal for the helpful hints).

KEY TASKS THAT STALLED
======================
Parsing for additional commands like ALTER VIEW etc.
First I want to read and tryout PMA SQL parse functions.
It makes more sense to optimize the parser of PMA_Tracker before
adding new types of SQL statements.

KEY CONCERNS
============
Tracking functionality for whole databases. At current status a user has to select every single table that he wants to track.

Is it possible to forward prepared SQL dump code to the export script of PMA ? When yes I could use it to deliver more export types than SQL dump and SQL execution.

TASKS IN THE UPCOMING WEEK
==========================
- Make me familiar with the PMA SQL parser library / functions.
- Try to use PMA SQL parser library.
- I think it would be a good idea to track the usernames too. I will add
this attribute to the tracker log and build a filter to the tracking report.

Best regards and have a nice WE

Alexander

First version finished

2009-07-12T12:26:00.001-07:00

Hi all,

I finished my part for Googles mid term evaluation.
Please feel free to try out my code, you will find it here:

http://page.mi.fu-berlin.de/rutkowsk/tracking_mechanism.zip

KEY ACCOMPLISHMENTS LAST WEEKS
==============================
Finished following functionalities:
- Tables can be tracked by mechanism now
- System logs data definition and data manipulation statements:
INSERT, UDATE, DELETE, TRUNCATE, ALTER TABLE, RENAME TABLE
- Tracking can be activated for a subset of these commands
- Tables with active tracking are marked with an "eye" icon.
- Simple versioning system. User can create new tracking versions.
- Deactivation and Reactivation of tracking jobs is possible.
- Simple schema (structure) snapshot, uses SHOW FULL COLUMNS FROM and
SHOW INDEX FROM
- When user is selecting a tracked table, a notice will printed out
- Switching between tables within a database is possible
- Tracking report is available. Contains dates and statements.
- Simple export form added, it can be used for undo action.
(structure only|data only|both) -> (SQL dump|SQL execution)
- Date_from - Date_to range selection for exports added.

KEY CONCERNS
============
I want to teach the tracker more SQL statements:
ALTER VIEW, CREATE INDEX, DROP INDEX

Tracker should handle whole databases.

TASKS IN THE UPCOMING WEEK(S)
=============================
- Evaluate your feedback to my PMA_Tracker
- Localize english-only strings
- Add more export possibilities
- Snapshot comparison would be fine
- Prettify some layout elements

I hope you are happy about the new Tracker, please send me your
feedback. I need it for a successful progress.

HTML pages for tracking tables

2009-06-03T15:39:00.000-07:00

I am writing some html forms for the tracking mechanism. First we need pages for enabling / disabling tracking functionality for tables. So I created a first draft for a table with activated tracking. My plan for this page is to list all versions of the selected table, if there is any. Secondly it will be possible
to create a new version (tracking for previous version is then deactived) and the user can make a selection of data definition commands.

Modelling pma_tracking

2009-06-03T15:19:00.000-07:00

For the tracking mechanism we need a table to storage information about the tracked database and table manipulations. I want to differentiate between data definition (schema) and data manipulation commands. So, my first step was to develop a simple table named pma_tracking which I can use for my tracking operations later:

CREATE TABLE IF NOT EXISTS `pma_tracking` (
`db_name` varchar(64) collate utf8_bin NOT NULL,
`table_name` varchar(64) collate utf8_bin NOT NULL,
`version` int(10) unsigned NOT NULL,
`date_created` datetime NOT NULL,
`date_updated` datetime NOT NULL,
`schema_snapshot` text collate utf8_bin NOT NULL,
`schema_sql` text collate utf8_bin,
`data_sql` text collate utf8_bin,
`tracking` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') collate utf8_bin default NULL,
PRIMARY KEY (`db_name`,`table_name`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;

This table layout based on a proposal by a SF feature requester. I am not sure if it is a good idea to use "set" as datatype (it's not SQL standard), but for a first version I will use it.

Viewing the code...

2009-05-01T12:51:00.000-07:00

I am very happy that my proposal was accepted by Google and the phpMyAdmin team. I prepared my Eclipse installation and checked out the source code from the subversion repository. First I have to find out which existing libraries and templates I can use for the new functionalities. You will hear from me soon...