osCommerce

The database schema for osCommerce Online Merchant v3.0 has been updated to include foreign key relationships between related tables. This update allows MySQL to natively support foreign keys on InnoDB databases that are directly defined in the database schema, and uses a fallback mechanism for MyISAM databases where foreign key relationships are defined in a database table.

Foreign key relationships allows records to be linked together through multiple database tables, for example, linking products to categories. Until now, when a product was to be deleted using the Administration Tool, extra PHP code had to be written within the delete function to also delete entries in other database tables that shared a relationship with the product being deleted.

This is fine for standard installations where the relationships between database tables are known, however when add-ons create new database tables during installation, changes were required in core source code files to also perform database actions on the tables they created.

Now with foreign key relationships, changes to core source code files are no longer required and changes to the related database tables are performed natively on InnoDB databases or automatically through the database class for MyISAM databases. This cleans the codebase considerably and allows one simple query to take care of the whole database. For example:

delete from osc_products where products_id = 1

This one simple query now automatically takes care of deleting the product language definitions, product reviews, category assignments, special prices, shopping cart entries, and any other product table relationships without any additional queries or PHP code necessary.

The following foreign key constraints are supported for MyISAM databases for both ON UPDATE and ON DELETE operations:

• CASCADE, automatically update or delete child records when a parent record is being updated or deleted (eg, delete all product related information when a product is being deleted)
• SET NULL, automatically sets the child record field value to null when a parent record is being updated or deleted (eg, clear the product manufacturer value when a manufacturer is being deleted)
• RESTRICT, prevents a parent record from being updated or deleted if child records depend on it (eg, don’t allow order status levels to be deleted if they are in use by orders)

Add-On developers can take advantage of foreign keys by defining relationships directly in the database schema for InnoDB databases, and by entering relationships in the osc_fk_relationships table for MyISAM databases. Examples of foreign keys defined in the osc_fk_relationships table are:

The osc_fk_relationships table is only used for MyISAM databases and is used by the database class to check on the defined constraints when UPDATE and DELETE queries are being performed.

There are currently 68 foreign key relationships defined that will be introduced in the osCommerce Online Merchant v3.0 Beta 1 release.

The changes are currently available in my development branch at GitHub and will be pushed to the main development branch after further testing and code clean up has been performed. My branch is available at:

http://github.com/haraldpdl/oscommerce/

Documentation on MySQL’s foreign key implementation can be found here:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

osCommerce Online Merchant v3.0 Alpha 5 introduces a strict level of error reporting directly in the core framework. This is performed by setting the PHP error reporting setting to E_ALL and by setting the MySQL sql_mode to STRICT_ALL_TABLES at runtime.

All warnings and errors are logged in a text file located in the “work” directory and can be easily viewed in the Administration Tool -> Tools -> Error Log application and summary module.

This is the only Administration Tool application where it’s a good sign when it doesn’t show anything. It gives a nice feeling knowing that any sort of warning or error in the core framework has been checked for, from uninitialized PHP variables to problems with MySQL queries.

Setting the error reporting at such strict levels ensures a high quality of coding standards is kept in the core framework and passes this along to add-ons that are developed and installed.

This makes it easier for developers working on addons where they can see where warnings and errors are occuring in what they have been working on, and motivates them to fix the problems to improve the quality of the finalized work.

A configuration parameter will be introduced in v3.0 Beta 1 to disable the output of PHP warnings and errors on the catalog frontend. This allows the output of warnings and errors to occur under development environments, and to be disabled on production servers.

osCommerce Online Merchant v3.0 Alpha 5 has taken a big step forward in utilizing newer PHP and MySQL features. The minimum requirements to run v3.0 Alpha 5 are PHP v5.2.0+ and MySQL v4.1.13+ or v5.0.7+. A further requirement is the mysqli extension in PHP as the normal mysql database class was removed from v3.0 Alpha 5.

Both PHP and MySQL highly recommend the use of the newer mysqli extension over the older mysql extension.

It’s been reported that there are still service providers using MySQL v5.0.67. This is actually fine as it is a much newer version than the minimum requirement of v5.0.7.

To clarify the MySQL versioning scheme, the third number in the version string (”7″) is incremented for each new release in the release level.

MySQL v5.0.7 was actually a beta released on the 15th June 2005, and v5.0.67 was released on the 8th August 2008.

There have also been some reports that some service providers are still using PHP v5.1.6. RedHat Enterprise Linux and CentOS are vendors still using PHP v5.1.6. This is unfortunate as it does not meet the minimum PHP v5.2.0 requirement to install and use osCommerce Online Merchant v3.0 Alpha 5.

PHP v5.2.0 was released on the 2nd November 2006.

The first reports of v3.0 Alpha 5 not running properly was due to the use of PHP v5.1.6 which does not contain the json_encode() function introduced in PHP v5.2.0. This function is used heavily in the Administration Tool with the new dynamic table listing and live search features.

A compatibility function exists that might be the saviour to the json_encode() problem for earlier PHP v5.X versions. If you are interested in testing this out, feel free to fork osCommerce on Github and report back of your findings.

There has recently been a whole bunch of goodies various mysql developers and users have blogged about which I read through Planet MySQL, and thought it would be a nice idea to share these along.

Granted, these are mostly for the newer versions of MySQL (4.1/5.0).

• DBDesigner4 is now MySQL Workbench
• Creating users with root privileges, but for limited databases
• How To Index For Joins
• MySQL 5.0 Stored Procedures : Getting Started
• MySQL 5.0 Stored Procedures : Part 2
• MySQL 5.0 Stored Procedures : Part 3
• INSERT … ON DUPLICATE KEY UPDATE
• MySQL 5 Replication
• MySQL FULLTEXT Indexing and Searching
• What is the Number Between the Brackets for an Integer Column?
• MySQ5 and the almighty ARCHIVE Engine
• Joining Multiple Tables
• Storing an IP address in a database table

That’s a lot of good reading! But don’t take my wording; read the source, Luke!

Planet MySQL