tag:blogger.com,1999:blog-86865360471337683242024-03-14T01:15:38.988-07:00Tracking mechanism for phpMyAdminThis is my blog for my accepted Google of Summer Code 2009 project. For an introduction please read the project information at http://socghop.appspot.com/document/show/user/webskipper/projectinfoAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-8686536047133768324.post-17366897625585290462009-08-18T08:15:00.000-07:002009-08-18T14:32:11.643-07:00Status report August 11th - August 18th<iframe allowfullscreen='allowfullscreen' webkitallowfullscreen='webkitallowfullscreen' mozallowfullscreen='mozallowfullscreen' width='461' height='384' src='https://www.blogger.com/video.g?token=AD6v5dzVEcUxtYxaBlj5X3ep7TfUcLHn17YuyJ0kDzYXCJxb3O13FS9rReI-zi3ze5Fkb60pcwLWzCTwF29FlHa9fw' class='b-hbp-video b-uploaded' frameborder='0'></iframe><br /><br /><span style="font-size:85%;"><a href="http://page.mi.fu-berlin.de/rutkowsk/phpMyAdmin/howto.avi">http://page.mi.fu-berlin.de/rutkowsk/phpMyAdmin/howto.avi</a></span><br /><br />KEY ACCOMPLISHMENTS LAST WEEK<br />=============================<br /><br />- Wrote additional documentation, added information about the<br />config vars to Documentation.html<br /><br />- Used add_message(_file).sh to add lang vars.<br /><br />- Made the tracking of SQL dump imports working.<br /><br />- Created a short how-to video (see my blog)<br /><br />- Asked friends to evaluate my project.<br /><br />- Registered to PMA wiki, but can't edit all articles.<br /><br />KEY TASKS THAT STALLED<br />======================<br />--<br /><br />KEY CONCERNS<br />============<br />Evaluation<br /><br />TASKS IN THE UPCOMING WEEK(s)<br />==========================<br />- Improve the mechanismAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-52716751077416086232009-08-10T07:05:00.000-07:002009-08-10T07:06:41.346-07:00Status report August 3rd - August 10thHi all,<br /><br />KEY ACCOMPLISHMENTS LAST WEEK<br />=============================<br /><br />- I added an user-friendly visualisation of the structure snapshots.<br />It is similar to the structure page of phpMyAdmin.<br /><br />- Secondly I added handlers for CREATE/DROP/ALTER DATABASE.<br />Tracking for these commands is enabled by default. User has not to<br />create a version for it, it will created by the Tracker autmatically.<br /><br />- An output for these tracked commands is available on the "Tracking"<br />page for databases now.<br /><br />- Added a line number to the tracking report.<br /><br />KEY TASKS THAT STALLED<br />======================<br />Evaluation<br /><br />KEY CONCERNS<br />============<br />Evaluation, Documentation, Optimization.<br /><br /><br />TASKS IN THE UPCOMING WEEK<br />==========================<br />- Try to get feedback from users and developers.<br />- Write documentation, improve my code.<br /><br /><br />Best regards<br /><br />AlexanderAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-36663041334213480522009-08-02T16:40:00.000-07:002009-08-02T17:48:14.621-07:00Status report July 25th - August 2ndHi all,<br /><br />my report is a little bit delayed, my great-grandfather had 90th birthday on friday. I hope I will reach this age too ;)<br /><br />KEY ACCOMPLISHMENTS LAST WEEK<br />=============================<br /><br />- 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.<br /><br />- A new grey-colored icon indicates whether a table has versions but deactive tracking status.<br /><br />- Added handlers for CREATE/DROP INDEX, CREATE/ALTER/DROP VIEW.<br /><br />- Auto-creation functionality implemented. It creates a version of new tables and views automatically.<br /><br />- Some new config vars added. Here a short introduction:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">// Creates tracking of new tables and views automatically. Default value is true.<br />$cfg['Servers'][$i]['tracking_version_auto_create'] = true|false</span></span><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">// Adds "DROP VIEW" statement as prefix of "CREATE VIEW" logs. Default value is true.<br />$cfg['Servers'][$i]['tracking_add_drop_view'] = true|false<br /><br /></span></span><span style="font-size:85%;"><span style="font-family:courier new;">// Adds "DROP TABLE" statement as prefix of "CREATE TABLE" logs. Default value is true.<br />$cfg['Servers'][$i]['tracking_add_drop_table'] = true|false<br /></span></span><span style="font-size:85%;"><span style="font-family:courier new;"><br />// Defines the statements which the auto-creation mechanism uses for new tracked versions.<br />// Default value is<br />// CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,<br />// CREATE INDEX,DROP INDEX,<br />// INSERT,UPDATE,DELETE,TRUNCATE,REPLACE,<br />// CREATE VIEW,ALTER VIEW,DROP VIEW,<br />// CREATE DATABASE,ALTER DATABASE,DROP DATABASE;<br /><br /></span></span><span style="font-size:85%;"><span style="font-family:courier new;">$cfg['Servers'][$i]['tracking_default_statements'] = ...</span></span><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;"></span></span>KEY TASKS THAT STALLED<br />======================<br />-<br /><br />KEY CONCERNS<br />============<br />Finishing list of deliverables.<br /><br /><br />TASKS IN THE UPCOMING WEEK<br />==========================<br />- I want to focus on structure snapshots. The snapshot is saved in a php array.<br />A user-friendly visualisation of it would be fine. Comparison of snapshots is an idea.<br /><br />- Secondly I want to add an ouput of tracked CREATE/DROP/ALTER DATABASE statements<br />to the "Tracking" overview page for databases.<br /><br />- When time is left: start first phase of testing and evaluation. Try to get more feedback from users and developers.<br /><br /><br /><br />Best regards<br /><br />AlexanderAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-47452672862882900782009-07-24T13:15:00.000-07:002009-07-24T13:24:56.481-07:00Status report July 18th - 24th<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiN5rHNdXY_Q6vgklvuTRv9c3iBY8ZV35ouW0P2u07H6fJkf3Tg5pHxs57MfuStztPFgewijnNCo_Hgw_QIi4RrFo4fcuwIVn_Q5sRom_jT1E4vrPdZl7zJWH6QLFUH8oW3DK56J_fkzc76/s1600-h/tracking_mech_july_24th.png"><img style="cursor: pointer; width: 320px; height: 256px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiN5rHNdXY_Q6vgklvuTRv9c3iBY8ZV35ouW0P2u07H6fJkf3Tg5pHxs57MfuStztPFgewijnNCo_Hgw_QIi4RrFo4fcuwIVn_Q5sRom_jT1E4vrPdZl7zJWH6QLFUH8oW3DK56J_fkzc76/s320/tracking_mech_july_24th.png" alt="" id="BLOGGER_PHOTO_ID_5362122955417990898" border="0" /></a><br /><br />Hi all,<br /><br />KEY ACCOMPLISHMENTS LAST WEEK<br />=============================<br />- I added a separate filter functionality to the tracking report.<br />Now it is possible to list data definition, data manipulation<br />or both kinds of statements. Date ranges are supported.<br /><br />- Secondly I added an user filter. PMA_Tracker tracks the<br />logged in username. The tracking report page supports<br />to list all tracked usernames (*), one username or a list<br />of usernames with ',' as delimiter.<br /><br />- The export form uses the described filter settings.<br /><br />- I added a new export option: "SQL dump as file download".<br />Users can directly download the statements.<br /><br />- I did some work on the SQL dump page. It shows the SQL query<br />form now. The lines<br /><br />CREATE database IF NOT EXISTS pma_temp_db;<br />USE pma_temp_db;<br /><br />will automatically added to the dump. The user can copy the whole<br />dump to the clipboard or execute it by clicking on the submit button.<br />If he submits it he gets a "temporary" table and can do fine things<br />with it (use PMA export functionality for example).<br /><br />- I decided to cut off `dbname` . prefixes from tracked statements.<br />I used a Perl regular expression for that. Only without these prefixes<br />the dump export to other databases is possible.<br /><br />- I merged my Subversion branch with trunk/phpMyAdmin and<br />committed the changes. Not a big job, but good to see that it worked.<br /><br />- German translation added.<br /><br /><br />KEY TASKS THAT STALLED<br />======================<br />I tried to use libraries/sqlparser.lib.php within my PMA_Tracker<br />but it produces a lot of notices.<br />In addition PMA_SQP_analyze() works only for SELECT queries.<br />Did not see an easy (time-efficient) way to enable it for my PMA_Tracker<br />so I reduced the queue priority for this job.<br /><br /><br />KEY CONCERNS<br />============<br />Tracking for whole databases is still open.<br /><br /><br />TASKS IN THE UPCOMING WEEK<br />==========================<br />- Create first prototype of tracking page for databases / set of tables.<br />- Add handlers for CREATE/DROP INDEX, CREATE/ALTER/DROP VIEW.<br />- Inform users about deactivated tracking versions (use an icon or<br />notice message).<br /><br /><br />Finally I want to call attention to<br /><br /><a class="moz-txt-link-freetext" href="http://demo.phpmyadmin.net/gsoc-alex/">http://demo.phpmyadmin.net/gsoc-alex/</a><br /><br />Its a copy of my svn branch (thanks to Michal).<br /><br />Best regards and have a nice WE<br /><br />AlexanderAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-20906726887443123182009-07-17T13:34:00.001-07:002009-07-17T13:34:58.203-07:00Status report July 13th - 17thHi all,<br /><br />here is my report:<br /><br />KEY ACCOMPLISHMENTS LAST WEEK<br />=============================<br />- I got my own SVN branch phpmyadmin/branches/gsoc/alex/phpMyAdmin<br />(thanks to Marc for the permissions).<br /><br />- 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.<br /><br />- Switched from Eclipse 3.2 to NetBeans IDE 6.7. PHP eclipse nerved<br />me because it was sometimes unstable and did not open some files. NetBeans seems to be more optimized for PHP development.<br /><br />- Did some work on my code too <span class="moz-smiley-s1"><span> :) </span></span><br />Here is a summary of my committed changes to SVN branch:<br /><br />+ First tracking mechanism patch applied.<br />+ Problems causing PHP notices solved.<br />+ Some useless PMA_DBI_select_db() calls removed.<br />+ Bug: after renaming a table the list of tracking versions is broken (Fixed)<br />+ Using PMA_query_as_controluser where it is possible.<br />+ New tab "Tracking" for the table view added.<br />+ A little bit PMA_Tracker code refactoring.<br />+ SQL dump of pma_tracking to scripts/create_tables.sql added.<br />+ Localization introduced, using string vars in english-utf-8.inc.php for outputs.<br />+ Short php tags and short print instruction removed how it is recommended in PEAR coding standards.<br />(Thanks to Michal for the helpful hints).<br /><br />KEY TASKS THAT STALLED<br />======================<br />Parsing for additional commands like ALTER VIEW etc.<br />First I want to read and tryout PMA SQL parse functions.<br />It makes more sense to optimize the parser of PMA_Tracker before<br />adding new types of SQL statements.<br /><br />KEY CONCERNS<br />============<br />Tracking functionality for whole databases. At current status a user has to select every single table that he wants to track.<br /><br />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.<br /><br />TASKS IN THE UPCOMING WEEK<br />==========================<br />- Make me familiar with the PMA SQL parser library / functions.<br />- Try to use PMA SQL parser library.<br />- I think it would be a good idea to track the usernames too. I will add<br />this attribute to the tracker log and build a filter to the tracking report.<br /><br />Best regards and have a nice WE<br /><br />AlexanderAlexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-83580623929789133322009-07-12T12:26:00.001-07:002009-07-12T12:35:29.786-07:00First version finished<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXJ9SNTY8xlXyLUMdMr95Uhv21C5aGQh3Gh7GCR9nRo5zEUp6dLj-CsL4xpIUPrjRTiyW28tx2Jm7PR-foUzlqIaFWRmWD3Euf5iCx-Grj4O3TfwJNduVeliLku31HEilo0_c6gFiY1v1j/s1600-h/tracking_page.png"><img style="cursor: pointer; width: 320px; height: 256px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXJ9SNTY8xlXyLUMdMr95Uhv21C5aGQh3Gh7GCR9nRo5zEUp6dLj-CsL4xpIUPrjRTiyW28tx2Jm7PR-foUzlqIaFWRmWD3Euf5iCx-Grj4O3TfwJNduVeliLku31HEilo0_c6gFiY1v1j/s320/tracking_page.png" alt="" id="BLOGGER_PHOTO_ID_5357659492575522866" border="0" /></a><br /><br />Hi all,<br /><br />I finished my part for Googles mid term evaluation.<br />Please feel free to try out my code, you will find it here:<br /><br /><a href="http://page.mi.fu-berlin.de/rutkowsk/tracking_mechanism.zip">http://page.mi.fu-berlin.de/rutkowsk/tracking_mechanism.zip</a><br /><br />KEY ACCOMPLISHMENTS LAST WEEKS<br />==============================<br />Finished following functionalities:<br />- Tables can be tracked by mechanism now<br />- System logs data definition and data manipulation statements:<br />INSERT, UDATE, DELETE, TRUNCATE, ALTER TABLE, RENAME TABLE<br />- Tracking can be activated for a subset of these commands<br />- Tables with active tracking are marked with an "eye" icon.<br />- Simple versioning system. User can create new tracking versions.<br />- Deactivation and Reactivation of tracking jobs is possible.<br />- Simple schema (structure) snapshot, uses SHOW FULL COLUMNS FROM and<br />SHOW INDEX FROM<br />- When user is selecting a tracked table, a notice will printed out<br />- Switching between tables within a database is possible<br />- Tracking report is available. Contains dates and statements.<br />- Simple export form added, it can be used for undo action.<br />(structure only|data only|both) -> (SQL dump|SQL execution)<br />- Date_from - Date_to range selection for exports added.<br /><br />KEY CONCERNS<br />============<br />I want to teach the tracker more SQL statements:<br />ALTER VIEW, CREATE INDEX, DROP INDEX<br /><br />Tracker should handle whole databases.<br /><br />TASKS IN THE UPCOMING WEEK(S)<br />=============================<br />- Evaluate your feedback to my PMA_Tracker<br />- Localize english-only strings<br />- Add more export possibilities<br />- Snapshot comparison would be fine<br />- Prettify some layout elements<br /><br />I hope you are happy about the new Tracker, please send me your<br />feedback. I need it for a successful progress.Alexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-74573875897229341622009-06-03T15:39:00.000-07:002009-06-03T15:54:05.658-07:00HTML pages for tracking tables<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoX86Zeb38oAch9rbLjRHb0pCJlMYpPTcdVx7GMJmrg0zplsjiCw8CG3WPvKF6qqKRWxI_2GQRhIIb0z4EMKHtFpo7mKJ_np5wryNM8BYSdBYtBQqQeIwtizhBLTmm1axfJ-rE6q4bxQAU/s1600-h/tracking_table_active.png"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 249px; height: 157px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoX86Zeb38oAch9rbLjRHb0pCJlMYpPTcdVx7GMJmrg0zplsjiCw8CG3WPvKF6qqKRWxI_2GQRhIIb0z4EMKHtFpo7mKJ_np5wryNM8BYSdBYtBQqQeIwtizhBLTmm1axfJ-rE6q4bxQAU/s320/tracking_table_active.png" alt="" id="BLOGGER_PHOTO_ID_5343235874244198162" border="0" /></a><br />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<br />to create a new version (tracking for previous version is then deactived) and the user can make a selection of data definition commands.Alexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-50183615543776086812009-06-03T15:19:00.000-07:002009-06-03T16:03:23.630-07:00Modelling pma_trackingFor 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:<br /><br /><span style=";font-family:verdana;font-size:85%;" >CREATE TABLE IF NOT EXISTS `pma_tracking` (<br />`db_name` varchar(64) collate utf8_bin NOT NULL,<br />`table_name` varchar(64) collate utf8_bin NOT NULL,<br />`version` int(10) unsigned NOT NULL,<br />`date_created` datetime NOT NULL,<br />`date_updated` datetime NOT NULL,<br />`schema_snapshot` text collate utf8_bin NOT NULL,<br />`schema_sql` text collate utf8_bin,<br />`data_sql` text collate utf8_bin,<br />`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,<br />PRIMARY KEY (`db_name`,`table_name`,`version`)<br />) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;</span><span style="font-size:85%;"><br /></span><br /><br />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.Alexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0tag:blogger.com,1999:blog-8686536047133768324.post-44191846903861567582009-05-01T12:51:00.000-07:002009-05-01T13:02:17.530-07:00Viewing the code...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...Alexanderhttp://www.blogger.com/profile/00804602465627761983noreply@blogger.com0