Migrating Moodle to a Plesk Linux server

Following up on some work that I started earlier in the month. I am using a Plesk Version 10 server to host
a Moodle 2.x system.

Some more config work was required to migrate the Moodle system including editing the database records to change the embedded paths (file system paths) and the embedded URL’s (absolute URL’s for data files).

The addition of an SSL certificate created a problem but it was only that the Plesk process provides for modifications to http and https as two separate files. The following needed to be added as well.

[bash]

#mcedit /var/www/vhosts/your_domain_name/conf/vhost_ssl.conf

[/bash]

and create an entry like

[xml]
<Directory /var/www/vhosts/your_domain_name/httpdocs/>
php_admin_value open_basedir "/var/www/vhosts/your_domain_name/httpdocs:/var/www/vhosts/your_domain_name/moodledata"
</Directory>
[/xml]

So that both http and https have the open_basedir restriction avoided.

Converting osCommerce to osCmax mySQL database structure

I am in the process of converting some older osCommerce based web sites to osCmax and the underlying database structure is different. The following is the step by step that I have worked through for one site.

Updated: 24th Aug 2011: Embedded updates for tables in the following as I worked through the conversion.

In preparing for this I have exported the structure only from the old site and from the current osCmax svn version that I have running on my system. I use Beyond Compare from Scooter Software for my file / folder comparison work and I have opened up the two structure files for comparison.

Note: the old osCommerce is based on an early 2.1 version updated and modified for unique requirements over the 10 years or so it has been in operation. It’s been a reluctance to even start contemplating this change that has led it to be so long.

Table: address_book:

The first two fields are in reverse order and the address_book_id is now a unique auto_increment field.
The address_book_id field in the old db will need to be dumped and the auto_increment value accepted.
entry_company_tax_id is a new field
entry_telephone is no longer used – checking my data they are all NULL so not required

Tables: address_book and address_book_new both exist in the old osCommerce database but not in osCmax. Both tables are populated so I’ll need to compare the data in the two.
Update: address_book_new is from an attempt to convert to creloaded some years ago, it is not an osCommerce table and can be ignored.

Table: anti_robotreg does not exist in osCmax and is empty in the old system. Dump this.

Tables: admin, etc. through to authors_info do not exist in the old system. These can be ignored.

Table: banners and banners_history no change.
Update: There is data in the new system that can be cleared.

Table: cache does not exist in the old.

Table: categories. Need to add new fields to old to align with new.
after sort_order
`categories_featured_until` date default NULL,
`categories_featured` tinyint(1) default ‘0’,
after last_modified add
`categories_hide_from_groups` varchar(255) NOT NULL default ‘@’,

In old remove the fields
`categories_customizable` tinyint(1) DEFAULT ‘1’,
`tpl_file` varchar(30) DEFAULT NULL,
these are not in the new

Table: categories_description
Add two new fields to old to align with new
`categories_description` text,
`categories_heading_title` varchar(64) default NULL,

Update: the categories column order changed to match and confirmed that data in new can be cleared.

Tables: configuration, configuration_group, counter, & counter_history are the same barring some minor formatting and default settings that should not impact.

Update: configuration table has a huge difference in the number of entries. The data content needs to be compared and merged rather than overwritten.

Table: countries
Add a new field to old to align with new
`active` tinyint(3) unsigned default ‘1’,

Table: currencies, no change.

Table: customers
Add new field after customers_id
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,

Change `customers_default_address_id` int(5) to `customers_default_address_id` int(11)
Add new fields after customers_newsletter
`customers_newsletter_type` varchar(32) NOT NULL default ‘html’,
`guest_account` tinyint(1) NOT NULL default ‘0’,
`customers_login` varchar(96) default NULL,
`customers_group_name` varchar(27) NOT NULL default ‘Retail’,
`customers_group_id` int(11) NOT NULL default ‘0’,
`customers_group_ra` enum(‘0′,’1’) NOT NULL default ‘0’,
`customers_payment_allowed` varchar(255) NOT NULL,
`customers_shipment_allowed` varchar(255) NOT NULL,
`customers_paypal_payerid` varchar(20) default NULL,
`customers_paypal_ec` tinyint(1) unsigned NOT NULL default ‘0’,

Table: customers_basket no change.

Table: customers_basket_attributes
add new field after products_options_value_id
`products_options_value_text` varchar(32) default NULL,

Tables: customers_groups is new customers_info no change.

Table customers_new is in old but not new. Need to validate data in this and customers prior to export.

Tables: customers_notes and wishlist related, extra fields, and feeds are only in new.

Table: geozones no change, new tables for google etc only in new.

Table: languages
Add new field after sort_order
`meta_keywords` varchar(255) default NULL,

Table: manufacturers
Add new fields after manufacturers_image
`manufacturers_featured_until` date default NULL,
`manufacturers_featured` tinyint(1) default ‘0’,
`manufacturer_featured_until` date default NULL,
`manufacturer_featured` tinyint(1) default ‘0’,

Table: manufacturers_info
Add new field after manufacturers_url
`manufacturers_description` text,

Table: orders
Add new field after customers_address_format_id
`customers_dummy_account` tinyint(3) unsigned NOT NULL,

Remove `delivery_telephone` varchar(32) DEFAULT NULL, from old table as not in new data is all null.
Remove `billing_telephone` varchar(32) DEFAULT NULL,

Old field `cc_ccv` varchar(3) DEFAULT NULL COMMENT ‘CCV 3 Digits’, is not in new but seems like a necessary field. Need to include for the moment and dump later if not required. Some values are present.

Old field `payment_method` varchar(32) NOT NULL DEFAULT ”, is changed to
`payment_method` varchar(255) NOT NULL DEFAULT ”
Not sure how this interacts with payment processes. Will retain field as payment_method_old leaving the new field as payment_method. Refer to table orders_pay_methods which is also retained and linked to this field but is not in the new structure.

Three fields in old can be dropped as they are all empty.
`account_name` varchar(32) NOT NULL DEFAULT ”,
`account_number` varchar(20) DEFAULT NULL,
`po_number` varchar(12) DEFAULT NULL,

New fields to add after currency_value
`paypal_ipn_id` int(11) NOT NULL default ‘0’,
`fedex_tracking` varchar(20) NOT NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
`shipping_tax` decimal(7,4) NOT NULL default ‘0.0000’,
`shipping_module` varchar(255) default NULL,

Table: orders_pay_methods not in new. Retain in new until determining how this works.

Table: orders_premade_comments not in old.

Table: orders_products
Add new field after `products_model`
`products_code` varchar(40) NOT NULL,

Add new field after `products_quantity`
`products_stock_attributes` varchar(255) default NULL,

Table: orders_products_attributes
Add new field after `products_options_values`
`code_suffix` varchar(16) NOT NULL,

Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,

Table: orders_products_download no change.

Table: orders_ship_methods not in new. appears to relate to new field in orders regarding shipping_module. Retain and test post change.

Table: orders_status
Add new fields after `orders_status_name`
`public_flag` int(11) default ‘1’,
`downloads_flag` int(11) default ‘0’,

Tables: orders_status_history and orders_total no change

Table: packaging is new

Table: paypalipn_txn appears the same / similar to paypal_ipn. Will need to look at data changes.

There are many fields in old and not in new and vice-versa. Trying to match the data will be time-consuming. Need to assess if this needs to be done or not. Retain old table as itself for the time being.

Table: pm_configuration is new.

Table: products is also complex in the changes. Need to review.

Table: products_attributes
Add new fields after `options_values_id`
`code_suffix` varchar(16) NOT NULL,
`suffix_sort_order` int(4) NOT NULL default ‘0’,
and Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_attributes_download no change

Table: products_description
Add new field after `products_name`
`products_short` text,

Add new fields after `products_description` text,
`tab1` text,
`tab2` text,
`tab3` text,
`tab4` text,
`tab5` text,
`tab6` text,

Table: products_groups is new

Table: products_notifications no change

Table: products_options

Add new fields after `products_options_name`
`products_options_track_stock` tinyint(4) NOT NULL default ‘0’,
`products_options_type` int(5) NOT NULL default ‘0’,
`products_options_length` smallint(2) NOT NULL default ’32’,
`products_options_comment` varchar(32) default NULL,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_options_products is not in new.

Table: products_options_description is new

Tables: products_options_values, products_options_values_to_products_options are the same

Tables: products_price_break & products_stock are new

Table: products_to_categories is the same

Tables: products_to_discount_categories, products_xsell, & quick_links are new

Table: reviews
Add new field after `reviews_rating`
`approved` tinyint(3) unsigned default ‘0’,

Tables: scart, all search*, shipping_manifest and slideshow are new.

Table: sessions no change.

Table: specials
Add new field after `status`
`customers_group_id` int(11) NOT NULL default ‘0’,

Table: suppliers is custom to the old system. links in products and currencies need to be managed. relates to proposed module to manage outbound purchase orders and product costing.

Table: specials_retail_prices is new

Tables: tax_class, tax_rates no change

Table: theme_configuration is new

Table: tep_meta_tags is not in new

Tables: topics* and usu_cache are new

Table: whos_online
Add new fields after `ip_address`
`hostname` varchar(255) NOT NULL,
`country_code` varchar(2) NOT NULL,
`country_name` varchar(64) NOT NULL,
`region_name` varchar(64) NOT NULL,
`city` varchar(64) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,

Add new fields after `last_page_url` text NOT NULL,
`http_referer` varchar(255) NOT NULL,
`user_agent` varchar(255) NOT NULL,

Tables: zones* are no change

Next is to build a script to make the structure changes and then extend it to convert the data. I need this in order to do some trial runs before a live cut-over. It will be useful for the creLoaded stores that I need to do as well.

Mozilla Update version 6.0

Sometimes automatic updates are a blessing and others…..

Mozilla Thunderbird was updated for me today and I now have loads of issues like the following trying to send mail:

An error occurred sending mail: The mail server sent an incorrect greeting: Cannot connect to SMTP server 61.9.168.249 (61.9.168.249:25), connect error 10060.

The server is bigpond and the connection error leads me to the Mozilla forums, where I guess I am not alone:

General Error
SQL ERROR [ mysql4 ]

User ‘mozine_forums_rw’ has exceeded the ‘max_user_connections’ resource (current value: 20) [1226]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

Cannot save drafts, cannot send, cannot uninstall… hours of messing around at my cost. Bugger!

osCmax Slideshow process in a free CSS stylesheet template

The slideshow process in osCmax 2.5 RC1 has had me baffled for a couple of days (well at least the hours that I have had available to look at it during those days).

First up was a bit of redundant code that I flagged in a bug report and then finally getting to grips with the fact that the {slideshow} element in the main_page.code.php file is not in fact the complete deal but just the javascript bit and that the page modules section {modulecontroller} also needs to be called from the html page.

Once I danced around all of this to gain an understanding of how to get the slideshow working in the main index page, I then faced the admin console for the slideshow.

Annoyance #1 is that the slideshow images page includes a short cut to the slideshow settings page but the slideshow settings page has no corresponding return button requiring a 4 step menu traverse which is painful particularly with a glidepad and no mouse. But due to the hard coded construct of the configuration process for editing admin items it is a major task to change the system to fix this annoyance. Another day.

For future reference:

  • the main settings page in the admin is controlled / presented via the configuration.php file
  • the index code for the file makes it difficult for link backs
  • the file permissions process will need to be allowed for
  • the current menu process on the left and the edit process on the right might need to change
  • some of the showcase js script options are not offered in the slideshow settings

There was no second annoyance just a desire to recreate the slideshow elements that the FreeCSS_Sky stylesheet had included. The FreeCSS_Sky stylesheet template included not just an image slide but associated text. I think this is a great option and in order to adapt osCmax to replicate this I needed to add an option to the database and the slideshow tools to cater for the text object.

The process will need to include:

  • add the extra database fields to the slideshow table and the configuration table
  • modify the javascript populating file slideshow.js.php to read the fields
  • modify slideshow.php to present the fields in the admin
  • modify the javascript that actually does the work to present the new data

But this is for another day.