The Village Green

2219 - Working with Open-Source Web and Database Systems – Ron Patterson, Web Database Specialist, Academy School District 20 – 6/20/07

Handouts available on the TIE site.

Web Development
Ron started with background info about the web, its early days using static web pages which moved to dynamic driven from databases. http://www.w3.org and www.apache.org as good resources. Academy 20 uses a three-tier model based on open-source tools.

Open Source Software
Open source software is managed by community development. In many cases you can use a main distribution or version, or you can contract with a 3rd party vendor who supports a particular distribution. Licensing fees are usually lower or no cost. Problem resolution is usually quicker. http://www.opensource.com

PHP
Open source. Based on the C programming language. Designed for web page deployment. Supports MySQL, PostgreSQL, Oracle, ODBC, dBase, InterBase, Ingres, LDAP, and other database platforms. http://www.php.net

We looked at a sample PHP script. It is very similar to HTML with which I’m familiar. Just like a CSS style, or Java, there is a special set of PHP tags. <?php indicates the start of a script. Look for a text editor that helps with code formatting. I like to use TextWrangler.

Database systems centralize data management, structure the data, a database looks a lot like a spreadsheet with tables (files), rows (record), columns (field), relationships (how one table relates to another. There are also languages used to query the data to retrieve information. SQL is a structured query language. Managed backups may include rollbacks so you can restore from earlier data.

MySQL
There is an organization around MySQL, which is open source development and support. This does run on different platforms. http://www.mysql.com and http://dev.mysql.com for more information.

There are specifics to consider with MySQL. It supports char, varchar, text, and blob as data types. Each can hold different amounts of data. Same for integer, smallint, bigint, tinyint, float, double, decimal, numeric, date, time, datetime (big difference from Oracle,) timestamp, enum, and set.

PostgreSQL
Open source. Support on multiple platforms, similar data types with support for arrays, mathematical complex, geo-spatial data. http://www.postgresql.com and http://www.postgresql.org for more info.

Other open source resources include InterBase/Firebird, Berkley DB XML, OpenLDAP, OpenBase SQL, Perl, Ruby, Python, and Java (not really completely open source).

Perl
This is the language I saw in the Tuesday session. Open source, C-like programming, database independence, used for CGI (common gateway interface or web application), great for batch database maintenance. http://www.perl.com

Common pitfalls
Take some time with the initial configuration. Register_global settings in PHP 4.1 and up, variable definition and initialization is not required as in many other languages, missing $ symbol on variables, and missing end tags in HTML. Watch out for truncating varchar data. Don’t set the output before the header. Look for missing semi-colons and end braces. Use == for conditional tests rather than =,

Examples
Sample commands were shown on a slide. It is mostly command line. Commands do things like create records, insert new rows (records) or columns (fields,) set dats types, print results, set primary key (like name or field id) which makes the database efficient, update, select, and query or sort results.

We also looked at a simple address book example. The actual code is listed in the handout for the session.

Resources
http://www.oreilly.com
Managing & Using MySQL, 2nd Edition
Web Database Applications with PHP & MySQL
MySQL Cookbook
MySQL Reference Manual
MySQL Pocket Reference
Practical PostgreSQL
PostgreSQL Essential Reference
Programming PHP
PHP Cookbook
http://hotscripts.com
http://tacosw.com
The Web Programming CD Bookshelf, Version 1.0
Marc Liyanage, http://www.entropy.ch/software/macosx/
Vim, http://www.macvim.org/OSX/

New Web Technologies
Dynamic HTML
Ajax - Asynchronous Javascript (this is the fuel for many web 2.0 applications, which allows for reduced page reloads to handle data or user interactions)
RPC - Remote Procedure Call capable
XML and other data handling
Result: Minimal reloading of pagesNew Web Technologies

Example
Ron showed MySQL Administrator (OS X). Allows for a very granular view of your running database, including option to kill (end) specific queries. There were other OS X tools demoed. CocoaMySQL lets you select a database, table, and it displays the structure, allowing you to manipulate the structure of the table. Academy is now using SchoolCenter for their web content management system, which is actually a PHP and MySQL solution.