how to create sql installer in custom module in magento for creating table by script

Question

i need to create sql installer in magento.....i created a module and i created table by sql query directly to sql editor...

but for global module i need to create sql installer...in magento sql... i m giving my config detail....and also i want to know that what changes in necessary for executing sql script in magento ...also i want to know that what should in sql script.

my config..

<?xml version="1.0" encoding="UTF-8" ?>
<config>
    <!-- module configuration -->
    <modules>
        <Webcreon_Seller>
            <version>0.0.1</version>
        </Webcreon_Seller>
    </modules>
    <!-- module configuration end -->
    <frontend>
        <routers>
            <seller>
                <use>standard</use>
                <args>
                    <module>Webcreon_Seller</module>
                    <frontName>seller</frontName>  
                </args>
            </seller>
        </routers>
     <layout>
            <updates>
                <seller>
                      <file>sellerform.xml</file>
                </seller>
            </updates>
       </layout>
    </frontend>
    <admin>
     <routers>
         <seller>
            <use>admin</use>
            <args>
               <module>Webcreon_Seller</module>
               <frontName>adminseller</frontName>
            </args>
         </seller>
      </routers>
 </admin>
 <adminhtml>
   <layout>
      <updates>
          <seller>
              <file>sellerform.xml</file>
           </seller>
      </updates>
   </layout>
   <menu>
      <customer translate="title" module="adminhtml">

         <sort_order>10</sort_order>
         <children>
             <set_time>
                   <title>Seller List</title>
                   <action>adminseller/adminhtml_index</action>
              </set_time>
          </children>
       </customer>
    </menu>
</adminhtml> 

    <global>
        <blocks>
            <seller>
                <class>Webcreon_Seller_Block</class>
            </seller>
         </blocks>
         <helpers>
            <seller>
                <class>Webcreon_Seller_Helper</class>
            </seller> 
        </helpers>
              <models>
          <seller>
                <class>Webcreon_Seller_Model</class>
                 <resourceModel>seller_mysql4</resourceModel>
            </seller> 
            <seller_mysql4>
             <class>Webcreon_Seller_Model_Mysql4</class>
             <entities>
                 <seller>
                   <table>db_vendor</table>
                 </seller>
              </entities>
          </seller_mysql4>
        </models>
        <resources>
        <!-- connection to write -->
        <seller_write>
            <connection>
                <use>core_write</use>
            </connection>
        </seller_write>
        <!-- connection to read -->
       <seller_read>
          <connection>
             <use>core_read</use>
          </connection>
       </seller_read>
</resources>
    </global>


</config>

Answer 1

In your config file under "resources" tag add the following above

<seller_setup>
    <setup>
         <module>Webcreon_Seller</module>
    </setup>
    <connection>
      <use>core_setup</use>
    </connection>
 </seller_setup>

Now create the folder named "sql" inside your module.And create folder named "seller_setup" inside sql folder. Inside that create a php file named "mysql4-install-0.1.0.php". Remember one thing that in this filename 0.1.0 should match the module version that you defined in config.xml at the top. In your case it is ok. Now inside that create your table with the fields. I have provided an example below.Edit as your requirement.

     <?php

$installer = $this;

$installer->startSetup();

$installer->run("
DROP TABLE IF EXISTS {$this->getTable('vendor_profile')};
CREATE TABLE {$this->getTable('vendor_profile')} (
  `vendor_profile_id` int(11) unsigned NOT NULL auto_increment,
  `vendor_id` int(11) unsigned NOT NULL,
  `discipline` varchar(255) NULL default '',
  `studio_title` varchar(255) NULL default '',
  `profilepic`  varchar(255) NULL ,
  `bannerpic`  varchar(255) NULL ,
  `biography` TEXT NOT NULL default '',
  `facebookid` varchar(255) NOT NULL default '',
  `twitterid` varchar(255) NOT NULL default '',



PRIMARY KEY (`vendor_profile_id`),FOREIGN KEY(vendor_id) REFERENCES udropship_vendor(vendor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();

Now after everything is set up refresh your web page and see your database you will see your table. For upgrade script refer to magento commerce wiki.

Hope this will help.