OpenCart Community

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.

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.

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.
On the plus side, it will always check the column and re-add if necessary due to a db restore from a previous version.

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:
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:

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.

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.

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

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 ?

Once the vqmod is gone there's no way to alter anything, unless you replace by a vqmod that removes the column
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

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