ASP.NET Issue Tracker Starter Kit in PHP and PostgreSQL (Race to Linux)

• Download source code (PostgreSQL) - 40.9 Kb
• Download source code (MySQL) - 45 Kb

Introduction

Porting the ASP.NET application for the Race to Linux has been quite a challenge this week. I was eager to test my latest development tools, which include a small persistent PHP framework with a data access layer, and a new novice-friendly generative software development application I call Code Stylist IDE. These tools were written by me and are available from my software company, Megapump, Inc.

Porting from ASP.NET

First things first, I looked at the table schemas for the ASP.NET Issue Tracker. Hmm, 18 tables, not a trivial project! I shortened the table names a bit and began creating the PostgreSQL tables via Code Stylist. Next, I mapped the relationships between the tables, and in less than an hour I had 90% of the PHP, SQL, CSS, JavaScript and the HTML template code ready to go. In fact, I could have submitted my entry the same day but the contest requirements stipulated that the ported application should look nearly identical to the original.

Challenges

The hardest part about porting the ASP.NET application to PHP was trying to thread the auto-generated back-end code into the repurposed front-end code. I saved the original pages as HTML and used the web browser OmniWeb (it has a great 'Reformat' command) to tidy it up before extracting the crucial bits of the layout code. I was very much impressed by the ASP.NET output, it was quite concise and Microsoft has some amazing JavaScript wizardry going on in there. Unfortunately, given that this was a Race, I decided right away to implement much of that functionality server-side (in PHP) rather than try to adapt the JavaScript. Given more time it might be possible to take advantage of some of that infrastructure.

The template

After analyzing the HTML from the ASP.NET application, I was able to slice the header, footer and the sidebar code that was common to each page. I added a %content% tag at the center and pasted it into the template view in Code Stylist. On previewing or publishing, the %content% tag gets replaced with <?php echo $page_content; ?>. When opening a page in a browser, the execution order dictated by Code Stylist is roughly:

1. Front controller connects to the database and reads page-specific template values from the XML store.
2. Page controller builds data objects for the views.
3. Page view is cached using output buffering.
4. Template view (header, footer and sidebars) wraps the page view (content) and the whole is sent to the browser.

The pages

The default page generators plus the user-defined table relationships were all I needed to instantly build the complete page and the template views and controllers, including the complex multi-table SELECT/JOIN queries, for most of the pages in the application. However, the generated views did not look at all like the original and I didn't have time to customize the generators, so it was necessary to throw out the view code in favor of the chunks carefully cut from the ASP.NET Issue Tracker.

Integration

The final step of porting the ASP.NET application to PHP was to replace the form input names so that they matched the auto-generated controller code. For instance, in the ASP.NET implementation an input field has code like name="txtTitle", but the generated code uses tablename_fieldname so I would change the source to name="issues_title" and that was the routine throughout. After getting the forms sorted out, I added behaviors with simple JavaScript for the various buttons, and server-side code to populate popup menus.

Results

The resulting application looks and works almost exactly like the original, since it shares most of the same HTML. It was a lot of painstaking handiwork to get everything functional, but my new generative coding methods were of big help and are sure to improve from the experience that I have gained. This code will be implemented for MySQL, keep an eye on this for more "developments".

The environment

I tested with PHP 4.3.11 and PostgreSQL 7.4.8, but everything should work under PHP 5 as well. Code Stylist IDE (Windows, Mac, Linux) can output the IssueTracker.cst project file to MySQL or PostgreSQL, with more databases to be supported soon.

Setting it up (short version)

PostgreSQL

• Inside the .zip file is a file named issue_tracker.tar.gz - this file should be transferred to the Linux server.
• SSH or Telnet to the Linux server, use the following command to unpack the archive: 'tar -xvzf issue_tracker.tar.gz'.
• Create a PostgreSQL database: 'createdb issuetracker' and create the tables 'psql issuetracker < issue_tracker/tables.sql'.
• Edit the CONFIG file (Pico is an easy text editor) 'pico issue_tracker/config.php'.
• And finally, copy the folder to your web server document folder. In my case I typed 'mv issue_tracker /home/brian/public_html/megapump/tracker'.
• Open a browser to the index.php file and you are ready to go!
• Bugs? Please let me know. Thanks!

MySQL (new!)

• Inside the .zip file is a file named issue_tracker_mysql.tar.gz - this file should be transferred to the Linux server.
• SSH or Telnet to the Linux server, use the following command to unpack the archive: 'tar -xvzf issue_tracker_mysql.tar.gz'.
• Create a MySQL database: 'mysql -u root' and then 'create database issuetracker'.
• Select the database: 'use issuetracker'.
• Run the SQL script file to make the tables: '\. issue_tracker/tables.sql'.
• Edit the CONFIG file (Pico is an easy text editor) 'pico issue_tracker/config.php'.
• And finally, copy the folder to your web server document folder. In my case I typed 'mv issue_tracker /home/brian/public_html/megapump/tracker'.

Setting it up (SUSE Linux step-by-step)

You must be logged in with a standard (not root) user account.

1. Open YaST.
2. Click Install and Remove Software.
3. Search for 'mysql'.
4. Check these boxes:
   • mysql
   • mysql-client
   • php4-mysql
5. Search for 'php'.
6. Check these boxes:
   • php4
   • php4-session
   • php4-mcrypt
   • php4-gd
7. Search for 'apache'.
8. Check these boxes:
   • apache2
   • apache2-mod_php4
9. Click the Accept button.
10. Click Continue (accept Automatic Changes) (may be prompted to insert SUSE install CDs at this point).
11. Click Finish.
12. Close YaST.
13. Open Konsole (System->Terminal->Konsole).
14. Type 'su' [enter].
15. Type the root password [enter].
16. Type '/etc/init.d/mysqld restart' [enter].
17. Type '/etc/init.d/apache2 restart' [enter].
18. Type 'exit' [enter] (leave Konsole open).
19. In a web browser (Konquerer, Firefox) go to www.codeproject.com.
20. Log in.
21. Go to www.codeproject.com/useritems/codestylist.asp.
22. Click the Download Source Code link (MySQL).
23. Click Save As [enter].
24. On the left half of the window (if using Konqueror), click public_html.
25. Close the browser.
26. In Konsole type 'cd public_html' [enter].
27. Type 'unzip *zip' [enter].
28. Type 'tar -xvzf *gz' [enter].
29. Type 'mysql -u root' [enter].
30. Type 'create database issuetracker;' [enter].
31. Type 'use issuetracker;' [enter].
32. Type '\. issue_tracker/tables.sql' [enter].
33. Type '\q' [enter].
34. Type 'pico issue_tracker/config.php' [enter].
35. Use arrow keys to move down to the line with db_name = "".
36. Type 'issuetracker' between the quotes.
37. Type control-x.
38. Type 'y'.
39. Type [enter].
40. Open a browser to localhost/~username/issue_tracker (where username is your SUSE login name).

System settings

If you get 'error in connect function of PostgreSQL class'

In the issue_tracker/config.php file, set $db_host = ''; and $db_port = '';. With those strings blank, PHP will use local sockets instead of TCP/IP to connect to PostgreSQL.

And

(As root) check the access control file called pg_hba.conf. On many systems (RedHat/SUSE) it's in /var/lib/pgsql/data. You can find it like this: 'find / -name pg_hba.conf' and edit it like this 'pico /var/lib/pgsql/data/pg_hba.conf'. At the end of the file you can paste the following two lines:

host all all 0.0.0.0 255.255.255.255 trust
local all all trust trust

All other lines should be commented out with a '#'. Now restart PostgreSQL (as root) '/etc/init.d/postgresql restart'

Final possibility

You may need to set 'tcpip_socket = true' in /var/lib/pgsql/data/postgresql.conf. Change issue_tracker/config.php to use $db_host='localhost'; and $db_port='5432';. And finally restart PostgreSQL.