Post by rph » Tue Feb 07, 2012 4:09 pm

I wanted to see how other people were handling the issue of database changes with their mods. It's no problem having a VQMod script modify some model file to run ALTER/CREATE for you at some point but that leaves me having to check it with SHOW on all the later queries that rely on those DB changes and possibly fall back to the unmodified query. That has the possibility to be a lot of extra queries and SHOW doesn't seem very fast in my testing. I've thought of going with the old school method of providing an SQL statement or file to run manually but I think that's a little too technical for some users.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by Xsecrets » Tue Feb 07, 2012 9:10 pm

if your module has an admin section you can add it to the install() method of the module. If your mod doesn't it might be worth adding an admin section just so you can use the install method.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Qphoria » Tue Feb 07, 2012 11:46 pm

Like Xsecrets said.. if possible

If not possible, I usually just append the "index()" function of the current controller that I am modding and add the sql there. I try to only do this on the admin side where possible. If my mod doesn't have an admin piece, I may add it to some related area like the admin product controller or something that will be loaded at least once before the vQmod changes are viewed. The admin side has no real worry about the small performance hit.

Code: Select all

<operation>
    <search position="after"><![CDATA[
    public function index() {
    ]]></search>
    <add trim="true"><![CDATA[
    //Q: Insert DB columns
    $query = $this->db->query("DESC ".DB_PREFIX."product xyz");
    if (!$query->num_rows) { 
        $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `xyz` int(11) default '0'");
    }
    ]]></add>
</operation>
On the plus side, it will always check the column and re-add if necessary due to a db restore from a previous version.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by JNeuhoff » Fri Feb 10, 2012 12:41 am

I usually do this:

For instance, if I wanted to add the field 'link_canoncial' to the 'category' table, I'd write a VQmod XML script which checks for the existance of the new field in admin/model/catalog/category.php, and if not there, adds it:

Code: Select all

	<file name="admin/model/catalog/category.php">
		<operation>
			<search position="after"><![CDATA[class ModelCatalogCategory extends Model {]]></search>
			<add><![CDATA[
	public function checkFieldLinkCanonical() {
		$hasLinkCanonicalField = FALSE;
		$result = $this->db->query( "DESCRIBE `".DB_PREFIX."category`;" );
		foreach ($result->rows as $row) {
			if ($row['Field'] == 'link_canonical') {
				$hasLinkCanonicalField = TRUE;
				break;
			}
		}
		if (!$hasLinkCanonicalField) {
			$sql = "ALTER TABLE `".DB_PREFIX."category` ADD `link_canonical` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
			$this->db->query( $sql );
		}
	}
			]]></add>
		</operation>
		<operation>
			<search position="replace"><![CDATA[SET parent_id = '" . (int)$data['parent_id'] . "', ]]></search>
			<add><![CDATA[SET parent_id = '" . (int)$data['parent_id'] . "', link_canonical = '" . $this->db->escape($data['link_canonical']) . "', ]]></add>
		</operation>
	</file>
and also something similar as above for the catalog/model/catalog/category.php.

Then I modify the system/engine/model.php in a VQmod XML script such as:

Code: Select all

	<file name="system/engine/model.php">
		<operation>
			<search position="after" offset="1"><![CDATA[public function __construct($registry) {]]></search>
			<add><![CDATA[
		if (defined('HTTP_ADMIN')) {
			// in frontend
			if (get_class($this) == 'ModelCatalogCategory') {
				$this->checkFieldLinkCanonical();
			}
		} else {
			// in admin backend
			if (get_class($this) == 'ModelCatalogCategory') {
				$this->checkFieldLinkCanonical();
			}
		}
			]]></add>
		</operation>
	</file>

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by rph » Mon Feb 20, 2012 5:26 am

Interesting ideas. I didn't know about DESCRIBE. I'll add that one to the repertoire (makes checking on indexing easier). For entire tables it looks like I'll be sticking with SHOW.

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by mondokat » Mon Feb 20, 2012 4:33 pm

Qphoria wrote:Like Xsecrets said.. if possible

If not possible, I usually just append the "index()" function of the current controller that I am modding and add the sql there. I try to only do this on the admin side where possible. If my mod doesn't have an admin piece, I may add it to some related area like the admin product controller or something that will be loaded at least once before the vQmod changes are viewed. The admin side has no real worry about the small performance hit.

Code: Select all

<operation>
    <search position="after"><![CDATA[
    public function index() {
    ]]></search>
    <add trim="true"><![CDATA[
    //Q: Insert DB columns
    $query = $this->db->query("DESC ".DB_PREFIX."product xyz");
    if (!$query->num_rows) { 
        $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `xyz` int(11) default '0'");
    }
    ]]></add>
</operation>
On the plus side, it will always check the column and re-add if necessary due to a db restore from a previous version.
Awesome. Simple, to the point. Add a few fields, no fuss no muss. Just what I was looking for.

New member

Posts

Joined
Mon Feb 20, 2012 3:55 pm

Post by skip » Sun Sep 06, 2015 12:27 am

Hi,
How to change length of product location field with vqmod from 128 to 1024 ?
Thanx

Active Member

Posts

Joined
Mon May 09, 2011 9:57 pm

Post by cyclops12 » Sun Apr 30, 2017 4:12 am

Qphoria wrote:
Tue Feb 07, 2012 11:46 pm
Like Xsecrets said.. if possible

If not possible, I usually just append the "index()" function of the current controller that I am modding and add the sql there. I try to only do this on the admin side where possible. If my mod doesn't have an admin piece, I may add it to some related area like the admin product controller or something that will be loaded at least once before the vQmod changes are viewed. The admin side has no real worry about the small performance hit.

Code: Select all

<operation>
    <search position="after"><![CDATA[
    public function index() {
    ]]></search>
    <add trim="true"><![CDATA[
    //Q: Insert DB columns
    $query = $this->db->query("DESC ".DB_PREFIX."product xyz");
    if (!$query->num_rows) { 
        $this->db->query("ALTER TABLE `" . DB_PREFIX . "product` ADD `xyz` int(11) default '0'");
    }
    ]]></add>
</operation>
On the plus side, it will always check the column and re-add if necessary due to a db restore from a previous version.
Excellent piece of code there Q......
Just one question...
If you use that code in vqmod to add new field to db table, should you also be able to uninstall that field if the vqmod is uninstalled and if so how would you do that ????

Expert Member

Posts

Joined
Sun Sep 27, 2015 1:10 am

Post by artcore » Sun Apr 30, 2017 4:50 am

Once the vqmod is gone there's no way to alter anything, unless you replace by a vqmod that removes the column ;D
OC3 might force an admin side for extensions; here you can use the install(), uninstall() methods where you can trigger db changes. In any case you should always create your own table and use a join. That way the original tables are never affected even when vqmod is removed. You'd just have an extra table which is harmless

Attn: I no longer provide OpenCart extensions, nor future support - this includes forum posts.
Reason: OpenCart version 3+ ;D

Thanks!


User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by cyclops12 » Sun Apr 30, 2017 5:24 am

Thank you for clearing that up for me artcore....
I know if it was a seperate module then you would have an install and uninstall function but just wasnt sure about small mods.
But good idea about the seperate tables.
Thanks

Expert Member

Posts

Joined
Sun Sep 27, 2015 1:10 am
Who is online

Users browsing this forum: No registered users and 28 guests