What is the best way to implement a setup/install script for Magento 1.7+

Question

I have a strange issue whereby my install script doesn't create the necessary tables for my module. I have only been able to get the script to run once and it was visible inside of the core_resource table. However, none of the tables were created and I have not been able to re-run the script when Magento loads.

I have read that there are two types of install script. One that allows for raw SQL and one which uses Varien_Db_Ddl_table.

Could some body clarify which type I should use please?

setup SQL

<?php

$installer = $this;

$installer->startSetup();

$installer->run("
--

DROP TABLE IF EXISTS {$this->getTable('modulename')};
   CREATE TABLE {$this->getTable('modulename')} (
  `c_id` int unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default,
  `middle_name` varchar(64) NULL default,
  `surname` varchar(64) NOT NULL default,
  `identity_id` char (32) NOT NULL,
  `house_number` varchar (16) NOT NULL,
  `flat_number` varchar (16) NOT NULL,
  `street` varchar (64) NOT NULL,
  `postal_code` varchar (16),
  `city` varchar (64) NOT NULL,
  `country` varchar (64) NOT NULL,
  `region` int NOT NULL FOREIGN KEY (`id_r`),
  `email` varchar (128) NOT NULL,
  `phone_num` char (16) NOT NULL,
  `registration_date` datetime NULL,
  `approval_date` datetime NULL,
  `approved` smallint NOT NULL default 0,
  `status` smallint(6) NOT NULL default 1,
  `deleted` smallint NOT NULL,
  `content` text NOT NULL default '',
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`c_id`),
  FOREIGN KEY (`id_s`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_regions')};
 CREATE TABLE {$this->getTable('modulename_regions')}(
  `cr_id` int NOT NULL auto_increment,
  `id_c` int NOT NULL,
  `id_r` int NOT NULL FOREIGN KEY (`id_r`),
  `deleted` NOT NULL default 0,
  PRIMARY KEY (`cr_id`),
  FOREIGN KEY CONSTRAINT(`id_c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_state')};
 CREATE TABLE {$this->getTable('modulename_regions')}(
 `cs_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`cr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_manager_level')};
CREATE TABLE {$this->getTable('manage_level')}(
 `ml_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`ms_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_manager_state')};
 CREATE TABLE {$this->getTable('modulename_manager')}(
 `ms_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`ms_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_manager')};
  CREATE TABLE {$this->getTable('manager')} (
  `m_id` int unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `middle_name` varchar(64) NULL default '',
  `surname` varchar(64) NOT NULL default '',
  `identity_id` char (32) NOT NULL,
  `house_number` varchar (16) NOT NULL,
  `flat_number` varchar (16) NOT NULL,
  `street` varchar (64) NOT NULL,
  `postal_code` varchar (16) '',
  `city` varchar (64) NOT NULL,
  `country` varchar (64) NOT NULL,
  `region` int NOT NULL,
  `email` varchar (128) NOT NULL,
  `phone_num` char (16) NOT NULL,
  `registration_date` datetime NULL,
  `approval_date` datetime NULL,
  `approved` smallint NOT NULL default 0,
  `status` smallint(6) NOT NULL default '1',
  `parent_id` int NULL default'',
  `deleted` smallint NOT NULL,
  `content` text NOT NULL default '',
  `status` smallint(6) NOT NULL default 0,
   PRIMARY KEY (`m_id`)
   FOREIGN KEY (`ms_id`),
   FOREIGN KEY (id_m),
   FOREIGN KEY (`r_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_manger_regions')};
 CREATE TABLE {$this->getTable('manager_regions')}(
 `mr_id` int NOT NULL auto_increment,
 `id_m` int NOT NULL,
 `id_r` int NOT NULL,
 `deleted` smallint NOT NULL default 0,
  PRIMARY KEY (`ms_id`),
  CONSTRAINT FOREIGN KEY UNIQUE (id_r),
  FOREIGN KEY (m_id),
  FOREIGN KEY (`r_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('modulename_region')};
 CREATE TABLE {$this->getTable('region')}(
 `r_id` int NOT NULL auto_increment,
 `name` varchar(64) NOT NULL,
 `deleted` smallint NOT NULL default 0,
  PRIMARY KEY (`r_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();

Config.xml

<?xml version="1.0"?>
<config>
  <modules>
    <Namespace_Modulename>
      <version>0.1.0</version>
    </Namespace_Modulename>
  </modules>
  <global>
    <helpers>
      <modulename>
        <class>Namespace_Modulename_Helper</class>
      </modulename>
    </helpers>
    <blocks>
      <modulename>
        <class>Namespace_Modulename_Block</class>
      </modulename>
    </blocks>
    <models>
      <modulename>
        <class>Namespace_Modulename_Model</class>
        <resourceModel>modulename_mysql4</resourceModel>
      </modulename>
      <modulename_mysql4>
        <class>Namespace_Modulename_Model_Mysql4</class>
        <entities>        
              <modulename>
                <table>modulename</table>
              </modulename>       
              <regions>
                <table>modulename_regions</table>
              </regions>          
              <state>
                <table>modulename_state</table>
              </state>        
              <managerlevel>
                <table>modulename_manager_level</table>
              </managerlevel>         
              <managerstate>
                <table>modulename_manager_state</table>
              </managerstate>         
              <manager>
                <table>modulename_manager</table>
              </manager>          
              <manageregions>
                <table>modulename_manager_regions</table>
              </manageregions>        
              <region>
                <table>modulename_region</table>
              </region>
        </entities>
      </modulename_mysql4>
    </models>
    <resources>
      <modulename_setup>
        <setup>
          <module>Namespace_Modulename</module>
        </setup>
        <connection>
          <use>core_setup</use>
        </connection>
      </modulename_setup>
      <modulename_write>
        <connection>
          <use>core_write</use>
        </connection>
      </modulename_write>
      <modulename_read>
        <connection>
          <use>core_read</use>
        </connection>
      </modulename_read>
    </resources>
  </global>
  <admin>
    <routers>
      <modulename>
        <use>admin</use>
        <args>
          <module>Namespace_Modulename</module>
          <frontName>modulename</frontName>
        </args>
      </modulename>
    </routers>
  </admin>
  <adminhtml>
    <menu>
      <modulename module="modulename">
        <title>modulename</title>
        <sort_order>100</sort_order>
        <children>
          <manager module="modulename">
            <title>Manage Manager</title>
            <sort_order>50</sort_order>
            <action>modulename/adminhtml_manager</action>
          </manager>
        </children>
      </modulename>
    </menu>
    <acl>
      <resources>
        <all>
          <title>Allow Everything</title>
        </all>
        <admin>
          <children>
            <modulename translate="title" module="modulename">
              <title>modulename</title>
              <sort_order>1000</sort_order>
              <children>
          <manager translate="title">
            <title>Manage Manager</title>
            <sort_order>50</sort_order>
          </manager>
              </children>
            </modulename>
          </children>
        </admin>
      </resources>
    </acl>
    <layout>
      <updates>
        <modulename>
          <file>modulename.xml</file>
        </modulename>
      </updates>
    </layout>
  </adminhtml>
</config>

Revised SQL Code:

<?php

$installer = $this;

$installer->startSetup();

$installer->run("
--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename')};
   CREATE TABLE {$this->getTable('namespace/modulename')} (
  `c_id` int unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default,
  `middle_name` varchar(64) NULL default,
  `surname` varchar(64) NOT NULL default,
  `identity_id` char (32) NOT NULL,
  `house_number` varchar (16) NOT NULL,
  `flat_number` varchar (16) NOT NULL,
  `street` varchar (64) NOT NULL,
  `postal_code` varchar (16),
  `city` varchar (64) NOT NULL,
  `country` varchar (64) NOT NULL,
  `region` int NOT NULL FOREIGN KEY (`id_r`),
  `email` varchar (128) NOT NULL,
  `phone_num` char (16) NOT NULL,
  `registration_date` datetime NULL,
  `approval_date` datetime NULL,
  `approved` smallint NOT NULL default 0,
  `status` smallint(6) NOT NULL default 1,
  `deleted` smallint NOT NULL,
  `content` text NOT NULL default '',
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`c_id`),
  FOREIGN KEY (`id_s`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_regions')};
 CREATE TABLE {$this->getTable('namespace/modulename_regions')}(
  `cr_id` int NOT NULL auto_increment,
  `id_c` int NOT NULL,
  `id_r` int NOT NULL FOREIGN KEY (`id_r`),
  `deleted` NOT NULL default 0,
  PRIMARY KEY (`cr_id`),
  FOREIGN KEY CONSTRAINT(`id_c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_state')};
 CREATE TABLE {$this->getTable('namespace/modulename_regions')}(
 `cs_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`cr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_manager_level')};
CREATE TABLE {$this->getTable('belcorp/manage_level')}(
 `ml_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`ms_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_manager_state')};
 CREATE TABLE {$this->getTable('namespace/modulename_manager')}(
 `ms_id` int NOT NULL auto_increment,
 `state` varchar(16) NOT NULL,
 `deleted` tinyint NOT NULL default 0,
  PRIMARY KEY (`ms_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_manager')};
  CREATE TABLE {$this->getTable('manager')} (
  `m_id` int unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `middle_name` varchar(64) NULL default '',
  `surname` varchar(64) NOT NULL default '',
  `identity_id` char (32) NOT NULL,
  `house_number` varchar (16) NOT NULL,
  `flat_number` varchar (16) NOT NULL,
  `street` varchar (64) NOT NULL,
  `postal_code` varchar (16) '',
  `city` varchar (64) NOT NULL,
  `country` varchar (64) NOT NULL,
  `region` int NOT NULL,
  `email` varchar (128) NOT NULL,
  `phone_num` char (16) NOT NULL,
  `registration_date` datetime NULL,
  `approval_date` datetime NULL,
  `approved` smallint NOT NULL default 0,
  `status` smallint(6) NOT NULL default '1',
  `parent_id` int NULL default'',
  `deleted` smallint NOT NULL,
  `content` text NOT NULL default '',
  `status` smallint(6) NOT NULL default 0,
   PRIMARY KEY (`m_id`)
   FOREIGN KEY (`ms_id`),
   FOREIGN KEY (id_m),
   FOREIGN KEY (`r_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_manger_regions')};
 CREATE TABLE {$this->getTable('belcorp/manager_regions')}(
 `mr_id` int NOT NULL auto_increment,
 `id_m` int NOT NULL,
 `id_r` int NOT NULL,
 `deleted` smallint NOT NULL default 0,
  PRIMARY KEY (`ms_id`),
  CONSTRAINT `mr_id` FOREIGN KEY (`id_r`) REFERENCES {$this->getTable('namespace/modulename_region')}(`r_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (m_id),
  FOREIGN KEY (`r_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

DROP TABLE IF EXISTS {$this->getTable('namespace/modulename_region')};
 CREATE TABLE {$this->getTable('belcorp/region')}(
 `r_id` int NOT NULL auto_increment,
 `name` varchar(64) NOT NULL,
 `deleted` smallint NOT NULL default 0,
  PRIMARY KEY (`r_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();

If you were able to see your module in core_resource then most probably the issue is not the contents of the install script (yet). post the config.xml file of your module. And the path to the install script. — Marius
– Marius, Commented Nov 1, 2013 at 18:57

mageUz · Accepted Answer · 2013-11-02 03:25:42Z

1

Are you sure, this $this->getTable('modulename') returns correct db table name? Try use like this:

DROP TABLE IF EXISTS {$this->getTable('modelname/sometable')};
CREATE TABLE IF NOT EXISTS {$this->getTable('modelname/sometable')} (
  `book_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `product_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `test_some_test_fk_2` FOREIGN KEY (`product_id`) REFERENCES {$this->getTable('catalog/product')}(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And your config.xml should be:

    <models>
        <modelname> <!-- Attention -->
            <class>Some_Module_Model</class>
            <resourceModel>some_module_mysql4</resourceModel>
        </modelname>
        <some_module_mysql4>
            <class>Some_Module_Model_Mysql4</class>
            <entities>
                <tablename> <!-- Attention -->
                    <table>some_test_table</table>
                </tablename>
            </entities>
        </some_module_mysql4>
    </models>

And I am sure your sql queries also wrong. Test this queries vai some db manager before adding to installer.

EDIT

Follow to great tuts:
http://alanstorm.com/magento_setup_resources
http://inchoo.net/ecommerce/magento/magento-install-install-upgrade-data-and-data-upgrade-scripts/

edited Nov 2, 2013 at 3:25

answered Nov 1, 2013 at 18:48

mageUz

6,2743 gold badges31 silver badges52 bronze badges

@Marius Thanks for the replies, I hope you can help me to resolve this. I have added my config.xml above

user1704524
– user1704524

2013-11-01 19:20:48 +00:00
Commented Nov 1, 2013 at 19:20
thankyou for your reply I will try out your method and post back as soon as...

user1704524
– user1704524

2013-11-01 19:21:24 +00:00
Commented Nov 1, 2013 at 19:21
@Marius I've tried to re-run the script but I now get a blank screen...

user1704524
– user1704524

2013-11-01 19:27:17 +00:00
Commented Nov 1, 2013 at 19:27
Added some links, follow them

mageUz
– mageUz

2013-11-02 03:23:58 +00:00
Commented Nov 2, 2013 at 3:23

Add a comment |

Stack Exchange Network

What is the best way to implement a setup/install script for Magento 1.7+

1 Answer 1

EDIT

Your Answer

Hot Network Questions

What is the best way to implement a setup/install script for Magento 1.7+

1 Answer 1

EDIT

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions