-- MySQL dump 10.10
--
-- Host: localhost    Database: test2
-- ------------------------------------------------------
-- Server version	5.0.26-Debian_3-log

/*!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 */;

--
-- Temporary table structure for view `__vue_villes_fr`
--

DROP TABLE IF EXISTS `__vue_villes_fr`;
/*!50001 DROP VIEW IF EXISTS `__vue_villes_fr`*/;
/*!50001 CREATE TABLE `__vue_villes_fr` (
  `id` mediumint(8) unsigned,
  `reference` varchar(255),
  `local_reference` varchar(255),
  `ref_sous_region` smallint(5) unsigned,
  `longitude` decimal(12,7),
  `latitude` decimal(12,7),
  `rayon_alentours` smallint(5) unsigned,
  `ville` longtext,
  `ref_langue` int(5)
) */;

--
-- Table structure for table `codes_postaux`
--

DROP TABLE IF EXISTS `codes_postaux`;
CREATE TABLE `codes_postaux` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ref_ville` mediumint(8) unsigned NOT NULL,
  `code_postal` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cle` (`ref_ville`,`code_postal`),
  KEY `ref_ville` (`ref_ville`),
  KEY `code_postal` (`code_postal`),
  CONSTRAINT `codes_postaux_ibfk_1` FOREIGN KEY (`ref_ville`) REFERENCES `villes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `codes_postaux`
--

LOCK TABLES `codes_postaux` WRITE;
/*!40000 ALTER TABLE `codes_postaux` DISABLE KEYS */;
INSERT INTO `codes_postaux` VALUES (36570,2441905,'97139'),(36571,2441905,'97142'),(36572,2441906,'97121');
/*!40000 ALTER TABLE `codes_postaux` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `regions`
--

DROP TABLE IF EXISTS `regions`;
CREATE TABLE `regions` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `reference` varchar(255) NOT NULL COMMENT 'Identifiant unique par région venant de la ''global cities database''',
  `ref_pays` tinyint(3) unsigned NOT NULL,
  `code` varchar(5) NOT NULL,
  `region` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ref_pays` (`ref_pays`),
  CONSTRAINT `regions_ibfk_1` FOREIGN KEY (`ref_pays`) REFERENCES `pays` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `regions`
--

LOCK TABLES `regions` WRITE;
/*!40000 ALTER TABLE `regions` DISABLE KEYS */;
INSERT INTO `regions` VALUES (3551,'',68,'01','Guadeloupe'),(3552,'',68,'02','Martinique');
/*!40000 ALTER TABLE `regions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sous_regions`
--

DROP TABLE IF EXISTS `sous_regions`;
CREATE TABLE `sous_regions` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `reference` varchar(255) NOT NULL COMMENT 'Identifiant unique par sous-région venant de la ''global cities database''',
  `ref_region` smallint(5) unsigned NOT NULL,
  `code` varchar(5) NOT NULL,
  `sous_region` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ref_region` (`ref_region`),
  CONSTRAINT `sous_regions_ibfk_1` FOREIGN KEY (`ref_region`) REFERENCES `regions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `sous_regions`
--

LOCK TABLES `sous_regions` WRITE;
/*!40000 ALTER TABLE `sous_regions` DISABLE KEYS */;
INSERT INTO `sous_regions` VALUES (4308,'',3551,'971','Guadeloupe'),(4309,'',3552,'972','Martinique');
/*!40000 ALTER TABLE `sous_regions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `structures`
--

DROP TABLE IF EXISTS `structures`;
CREATE TABLE `structures` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `facturation_ref_ville` mediumint(8) unsigned default NULL,
  `facturation_ref_code_postal` mediumint(8) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `facturation_ref_ville` (`facturation_ref_ville`),
  KEY `facturation_ref_code_postal` (`facturation_ref_code_postal`),
  CONSTRAINT `structures_ibfk_87` FOREIGN KEY (`facturation_ref_ville`) REFERENCES `villes` (`id`),
  CONSTRAINT `structures_ibfk_88` FOREIGN KEY (`facturation_ref_code_postal`) REFERENCES `codes_postaux` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `structures`
--

LOCK TABLES `structures` WRITE;
/*!40000 ALTER TABLE `structures` DISABLE KEYS */;
INSERT INTO `structures` VALUES (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
/*!40000 ALTER TABLE `structures` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `traductions`
--

DROP TABLE IF EXISTS `traductions`;
CREATE TABLE `traductions` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `label_table` varchar(255) NOT NULL,
  `label_champ` varchar(255) NOT NULL,
  `ref` mediumint(8) unsigned NOT NULL,
  `ref_langue` tinyint(3) unsigned NOT NULL,
  `texte` text NOT NULL,
  `texte_clean` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `label_table` (`label_table`),
  KEY `label_champ` (`label_champ`),
  KEY `ref` (`ref`),
  KEY `ref_langue` (`ref_langue`),
  CONSTRAINT `traductions_ibfk_1` FOREIGN KEY (`ref_langue`) REFERENCES `langues` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `traductions`
--

LOCK TABLES `traductions` WRITE;
/*!40000 ALTER TABLE `traductions` DISABLE KEYS */;
/*!40000 ALTER TABLE `traductions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `villes`
--

DROP TABLE IF EXISTS `villes`;
CREATE TABLE `villes` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `reference` varchar(255) NOT NULL COMMENT 'Identifiant unique par ville venant de la ''global cities database''',
  `local_reference` varchar(255) NOT NULL COMMENT 'France : code INSEE',
  `ref_langue` tinyint(3) unsigned NOT NULL,
  `ref_sous_region` smallint(5) unsigned NOT NULL,
  `ville` varchar(255) NOT NULL,
  `longitude` decimal(12,7) default NULL COMMENT 'en degrés',
  `latitude` decimal(12,7) default NULL COMMENT 'en degrés',
  `rayon_alentours` smallint(5) unsigned NOT NULL default '5',
  PRIMARY KEY  (`id`),
  KEY `ref_sous_region` (`ref_sous_region`),
  KEY `ref_langue` (`ref_langue`),
  CONSTRAINT `villes_ibfk_1` FOREIGN KEY (`ref_sous_region`) REFERENCES `sous_regions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `villes`
--

LOCK TABLES `villes` WRITE;
/*!40000 ALTER TABLE `villes` DISABLE KEYS */;
INSERT INTO `villes` VALUES (2441905,'','97101',5,4308,'Les Abymes',NULL,NULL,5),(2441906,'','97102',5,4308,'Anse-Bertrand',NULL,NULL,5);
/*!40000 ALTER TABLE `villes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Final view structure for view `__vue_villes_fr`
--

/*!50001 DROP TABLE IF EXISTS `__vue_villes_fr`*/;
/*!50001 DROP VIEW IF EXISTS `__vue_villes_fr`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `__vue_villes_fr` AS select `villes`.`id` AS `id`,`villes`.`reference` AS `reference`,`villes`.`local_reference` AS `local_reference`,`villes`.`ref_sous_region` AS `ref_sous_region`,`villes`.`longitude` AS `longitude`,`villes`.`latitude` AS `latitude`,`villes`.`rayon_alentours` AS `rayon_alentours`,if((`traduc1`.`texte` is not null),`traduc1`.`texte`,`villes`.`ville`) AS `ville`,if((`traduc1`.`texte` is not null),`traduc1`.`ref_langue`,`villes`.`ref_langue`) AS `ref_langue` from (`villes` left join `traductions` `traduc1` on(((`traduc1`.`label_table` = _utf8'villes') and (`traduc1`.`label_champ` = _utf8'ville') and (`traduc1`.`ref` = `villes`.`id`) and (`traduc1`.`ref_langue` = _utf8'5')))) */;
/*!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 2006-11-15 21:14:45