Migración de BD de Mysql a Pg Saludos
Estoy pasando una BD de mysql, de un módulo de un sistema que quiero probar, a Postgresql...
Lo que tiene el original en Mysql
Código:
-- MySQL dump 10.11
--
-- Host: localhost Database: p4a_base_application
-- ------------------------------------------------------
-- Server version 5.0.45-Debian_1ubuntu3.3-log
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) default NULL,
`name` text,
`label` text,
`position` int(11) default NULL,
`visible` tinyint(4) default '1',
`access_level` text,
`action` text,
`param1` text,
`parent_name` text,
`parent_position` int(11) default NULL,
`menu` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
LOCK TABLES `menu` WRITE;
INSERT INTO `menu` VALUES (1,NULL,'admin','Admin',1,1,'10',NULL,NULL,NULL,NULL,'Admin'),(2,1,'p4a_users','Users',1,1,'10','openMask',NULL,'admin',1,'Admin->Users'),(3,1,'p4a_menu_mask','Menu',1,1,'10','openMask',NULL,'admin',1,'Admin->Menu');
UNLOCK TABLES;
DROP TABLE IF EXISTS `p4a_menu_id_seq`;
CREATE TABLE `p4a_menu_id_seq` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
LOCK TABLES `p4a_menu_id_seq` WRITE;;
INSERT INTO `p4a_menu_id_seq` VALUES (3);
UNLOCK TABLES;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`user` text,
`pass` text,
`level` int(11) default NULL,
`default_mask` text,
`name` text,
`surname` text,
`country` text,
`address` text,
`city` text,
`tel1` text,
`tel2` text,
`fax` text,
`mobile` text,
`email` text,
`note` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',10,'p4a_menu_mask',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
UNLOCK TABLES;
DROP TABLE IF EXISTS `users_id_seq`;
CREATE TABLE `users_id_seq` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
LOCK TABLES `users_id_seq` WRITE;
INSERT INTO `users_id_seq` VALUES (1);
UNLOCK TABLES;
Como lo llevo en Postgresql:
Código:
SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;
CREATE SCHEMA base_application;
ALTER SCHEMA base_application OWNER TO carbonara;
COMMENT ON SCHEMA base_application IS 'Aplicación sencilla que incluye: autenticación y gestión de users, y gestión de opciones de menú';
SET search_path = base_application, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE menu (
id serial NOT NULL,
parent_id integer,
name character varying(20) NOT NULL,
label character varying(50),
position smallint,
visible boolean DEFAULT true NOT NULL,
access_level smallint,
action character varying(100) DEFAULT 'openMask'::character varying,
param1 text,
parent_name character varying(20),
parent_postion smallint,
menu character varying
);
ALTER TABLE base_application.menu OWNER TO carbonara;
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu', 'id'), 1, false);
CREATE SEQUENCE p4a_menu_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE base_application.p4a_menu_id_seq OWNER TO carbonara;
SELECT pg_catalog.setval('p4a_menu_id_seq', 1, false);
CREATE TABLE users (
id serial NOT NULL,
"user" character varying(20) NOT NULL,
pass character varying NOT NULL,
level smallint,
default_mask character varying(50),
name character varying(100)
);
ALTER TABLE base_application.users OWNER TO carbonara;
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('users', 'id'), 1, false);
INSERT INTO menu VALUES (1, NULL, 'admin', 'Admin', 1, true, 10, NULL, NULL, NULL, NULL, 'Admin');
INSERT INTO menu VALUES (2, 1, 'p4a_users', 'Users', 1, true, 10, 'openMask', NULL, 'admin', 1, 'Admin->Users');
INSERT INTO menu VALUES (3, 1, 'p4a_menu_mask', 'Menu', 1, true, 10, 'openMask', NULL, 'admin', 1, 'Admin->Menu');
INSERT INTO users VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 10, 'p4a_menu_mask', 'Administrador');
ALTER TABLE ONLY menu
ADD CONSTRAINT menu_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
Levanto la BD en pg, y bien, sin problemas... pero, hago la consulta:
Código:
SELECT * FROM users WHERE user = 'admin' AND pass = '21232f297a57a5a743894a0e4a801fc3'
y no devuelve nada... la cambio a:
Código:
SELECT * FROM users WHERE pass = '21232f297a57a5a743894a0e4a801fc3'
y funciona perfecto! En la tabla, como pueden ver en el dump, sólo existe un registro, y en user está admin
Ven algo que yo no? Alguna idea? Gracias de antemano
__________________ Gracias de todas todas
-----
Linux! |