-- MySQL dump 10.11 -- -- Host: localhost Database: central_impact -- ------------------------------------------------------ -- Server version 5.0.51a /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `invoices_seo` -- DROP TABLE IF EXISTS `invoices_seo`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `invoices_seo` ( `id_invoices_seo` int(10) unsigned NOT NULL auto_increment, `invoices_id_invoices` int(10) unsigned NOT NULL, `fk_invoices_id_invoices` int(10) unsigned NOT NULL, `referer_url` char(255) NOT NULL, `entry_point` char(255) NOT NULL, `link_from` char(255) NOT NULL, PRIMARY KEY (`id_invoices_seo`), KEY `fk_invoices_seo_invoices` (`invoices_id_invoices`), CONSTRAINT `fk_invoices_seo_invoices` FOREIGN KEY (`invoices_id_invoices`) REFERENCES `invoices` (`id_invoices`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; -- -- Dumping data for table `invoices_seo` -- LOCK TABLES `invoices_seo` WRITE; /*!40000 ALTER TABLE `invoices_seo` DISABLE KEYS */; /*!40000 ALTER TABLE `invoices_seo` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'central_impact' -- DELIMITER ;; /*!50003 DROP FUNCTION IF EXISTS `clean_fmp_return` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `clean_fmp_return`(str TEXT) RETURNS text CHARSET latin1 DETERMINISTIC BEGIN SELECT REPLACE(str,char(11),'') INTO str; RETURN str; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP FUNCTION IF EXISTS `clean_fmp_specials` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `clean_fmp_specials`(str TEXT) RETURNS text CHARSET latin1 DETERMINISTIC BEGIN SELECT REPLACE(str,'Ê','') INTO str; RETURN str; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP FUNCTION IF EXISTS `convert_fmp_return` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `convert_fmp_return`(str TEXT) RETURNS text CHARSET latin1 DETERMINISTIC BEGIN SELECT REPLACE(str,char(11),'\n') INTO str; RETURN str; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP FUNCTION IF EXISTS `date_format_reverse` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 FUNCTION `date_format_reverse`(str CHAR(10)) RETURNS date DETERMINISTIC BEGIN DECLARE My_DATE DATE; SET My_DATE = IF(str like '' , '0000-00-00' , CONCAT_WS('-' , SUBSTRING_INDEX(str,'/',-1 ) , left(str , POSITION('/' IN str) - 1 ) , MID(str , POSITION('/' IN str) + 1 , LOCATE('/',str,4) - POSITION('/' IN str) -1 ) ) ); RETURN My_DATE; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP PROCEDURE IF EXISTS `mycount` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `mycount`() begin declare mycount bigint; SELECT count(*) from invoices into mycount ; SELECT mycount; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP PROCEDURE IF EXISTS `split_string` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `split_string`( IN input TEXT, IN delimiter VARCHAR(10), IN string_id INT unsigned, IN create_table boolean ) SQL SECURITY INVOKER COMMENT 'Splits a supplied string using using the given delimiter,\nplacin' BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE cur_string_id INT; DECLARE delimiter_length TINYINT UNSIGNED; if create_table = TRUE then DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( id int unsigned not null, value VARCHAR(1000) NOT NULL ) ENGINE=MEMORY; end if; SET remainder = input; SET cur_string_id = string_id; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string_id,cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; /*!50003 DROP PROCEDURE IF EXISTS `split_string_into_table` */;; /*!50003 SET SESSION SQL_MODE=""*/;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%`*/ /*!50003 PROCEDURE `split_string_into_table`() SQL SECURITY INVOKER COMMENT 'Splits a supplied string using using the given delimiter,\nplacin' BEGIN declare v_account_id INT; declare v_email TEXT; declare v_no_more_rows boolean; declare csr_curr_table cursor for select account_id , email from tmp_customers; declare continue handler for NOT FOUND set v_no_more_rows := TRUE; -- create temporary table call split_string("","","",TRUE); -- loop through the cursor open csr_curr_table; my_loop: loop -- fetch data fetch csr_curr_table into v_account_id , v_email; -- basic cursor loop control exits if cursor is exhausted if v_no_more_rows then close csr_curr_table; leave my_loop; end if; -- insert data in the temporary table call split_string(v_email,"\n",v_account_id,FALSE); end loop; END */;; /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;; DELIMITER ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2008-04-08 18:05:19