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
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
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.
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>
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:
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>
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
Awesome. Simple, to the point. Add a few fields, no fuss no muss. Just what I was looking for.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.
On the plus side, it will always check the column and re-add if necessary due to a db restore from a previous version.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>
Excellent piece of code there Q......Qphoria wrote: ↑Tue Feb 07, 2012 11:46 pmLike 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.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>
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
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+
Thanks!
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
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
Who is online
Users browsing this forum: No registered users and 28 guests