MySQL Database Cookbook

-- MYSQL Cookbook. Make databases and tables, create a user, set his password
-- the password shown here "PutYourFavePasswordHere" should be changed to your
-- preferred password, before running these scripts.
-- Instructions:
-- SSH into your MYsql server or open a dos prompt on your server
-- use this command to logon to mysql as the root user:   mysql -u root -p
-- Copy & paste these scriptlets into the window, or save them to a file, and use
-- this command to run them:  source afile.sql;
-- that's all.
--
-- MAKE A DATABASE FOR MAGENTO CE EDITION
DROP database magento_ce;
CREATE DATABASE magento_ce;
USE magento_ce;
GRANT ALL ON magento_ce.* TO magentosql@localhost IDENTIFIED BY 'PutYourFavePasswordHere';
FLUSH PRIVILEGES;


-- ADD AN ISAM TABLE FOR STORE LOCATIONS 
CREATE TABLE IF NOT EXISTS `location` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`lat` varchar(256) NOT NULL,
`lan` varchar(256) NOT NULL,
`name` varchar(128) NOT NULL,
PRIMARY KEY (`id`) )
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


-- CREATE A DATABASE FOR FRANCHISE MANAGEMENT STUFF 
drop database franchise_management;
create database franchise_management;
use franchise_management;
GRANT ALL ON franchise_management.* TO drs@localhost IDENTIFIED BY 'PutYourFavePasswordHere';
GRANT ALL ON franchise_management.* TO amirul@localhost IDENTIFIED BY 'PutYourFavePasswordHere';
FLUSH PRIVILEGES;

--
-- Table structure for table `storelocations`
-- 
DROP TABLE IF EXISTS `storelocations`;
CREATE TABLE `storelocations` (
  `id` int(10) NOT NULL auto_increment,
  `region` varchar(32) NOT NULL,
  `locationstr` varchar(128) NOT NULL,
  `latitude` float default NULL,
  `longitude` float default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=329 DEFAULT CHARSET=utf8 COMMENT='Store Locations Table';

-- MAKE A DATABASE FOR MAGENTO CE EDITION
DROP database sonarqube;
CREATE DATABASE sonarqube;
USE sonarqube;
GRANT ALL ON sonarqube.* TO sonar@localhost IDENTIFIED BY 'PutYourFavePasswordHere';
FLUSH PRIVILEGES;

--- Append a date field with default value after a field called POSTED
ALTER TABLE PRESS ADD STARTDATE DATE DEFAULT '1980-01-01 0:0:00' AFTER POSTED;

-- Append a varchar with default value
ALTER TABLE LOANGROUP ADD LOANTYPE VARCHAR(8) DEFAULT 'FIXED' AFTER LENDER;

update LOANGROUP set LOANTYPE="ARM" where TITLE like "%Adjustable%";

--- MAKE A DATABASE FOR LENDERS CASE-INSENSITIVE SEARCHABLE
DROP TABLE IF EXISTS `LENDERS`;
CREATE TABLE `LENDERS` (
        ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        OWNER INT,
        NAME VARCHAR(40),
        URL VARCHAR(40),
        PRINCIPAL INT,
        POSTED DATE

) ENGINE INNODB, CHARSET=latin1 COLLATE latin1_general_ci COMMENT='LENDERS';