FFL2:FFL2 Main/Dev Environment/Create Fantacy Database

Prep dev DB

• Setup the 'fantacist' database user on your dev machine

grant all privileges on fantacy.* to 'fantacist'@'localhost' identified by 'password';

• Setup the 'ffl_readonly' database user on your dev machine

grant select on fantacy.* to 'ffl_readonly'@'localhost' identified by 'password';

• Create the ICap.sql script with the following content

--
-- Function to deal with captilisingm i.e. first letter of each word upper case, otherwise lower case
--
DELIMITER $$

DROP FUNCTION IF EXISTS `ICap`$$

CREATE FUNCTION `ICap`(mystring varchar(1000))
	RETURNS VARCHAR(1000)
BEGIN

DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
DECLARE myoutstring VARCHAR(1000) DEFAULT LOWER(mystring);
DECLARE upperstring VARCHAR(1000) DEFAULT UPPER(mystring);

WHILE i <= CHAR_LENGTH(mystring) DO
	SET myc = SUBSTRING(mystring, i, 1);
	SET pc = CASE WHEN i = 1 THEN ' ' ELSE SUBSTRING(mystring, i - 1, 1) END;
	IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
	    SET myoutstring = INSERT(myoutstring, i, 1, substring(upperstring, i, 1));
	END IF;
	SET i = i + 1;
END WHILE;

RETURN myoutstring;

END$$

DELIMITER ;

Export Data from Server

1. Export database as follows (enter the password when prompted)

mysqldump -u fantacist -p fantacy > fantacy_full.sql

1. gzip dump file

gzip fantacy_full.sql

Import Data

1. Copy the dump file onto your dev machine

scp <user>@<domain>:fantacy_full.sql.gz .

1. Unzip the dump file (example assumes you have gunzip available)

gunzip fantacy_full.sql.gz

1. Recreate the 'fantacy' schema as follows (Note: do this as the MySQL root user)

D:\Projects\fantacy\Database>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.37 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database fantacy;
Query OK, 57 rows affected (0.66 sec)

mysql> create database fantacy;
Query OK, 1 row affected (0.00 sec)

mysql> use fantacy;
Database changed
mysql> source ICap.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> source fantacy_full.sql
Query OK, 0 rows affected (0.00 sec)

...
...
...

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>