Migrar totes les taules de QueEsticFent 1.0 a postgresql

[anadal-fundaciobit]

Aqui hi ha un script SQL en format MySQL. Les taules següents s'han de migrar a POstgresql seguint la normativa de GenApp

FALTEN: (1) Crear seqüències (2) Falta assignar seqüències a Claus Primaries

NOTA: Es recomana anar taula a taula i després de cada creació de taula executar genapp i actualitzar BBDD (No generar codi !!!)

--
-- table structure for table "grups"
--

drop table if exists grups;

create table grups (
  grupid bigint not null,
  nom character varying(255)  not null,
  descripcio character varying(1000)  default null,
  nivellseguretat bigint default null,
  primary key  ("grupid")
);

--
-- table structure for table grupsusuari"
--

drop table if exists grupsusuari;
create table grupsusuari (
  grupsusuariid bigint not null,
  usuariid character varying(20) character  not null,
  grupid bigint not null,
  primary key  ("grupsusuariid"),
  key fk_grupsusuari_1 ("usuariid"),
  key fk_grupsusuari_2 ("grupid"),
  constraint grupsusuari_ibfk_1 foreign key ("grupid") references grups ("grupid")
);

--
-- table structure for table usuaris"
--

drop table if exists usuaris;
create table usuaris (
  usuariid character varying(100) not null,
  nom character varying(100) not null,
  llinatge1 character varying(100) default null,
  llinatge2 character varying(100) default null,
  correu character varying(100) not null,
  descripcio character varying(100) default null,
  extensio character varying(100) default null,
  naixement date default null,
  telefoncasa character varying(100) default null,
  telefonmobil character varying(100) default null,
  contrasenya character varying(100) default null
);

--
-- table structure for table accions"
--

drop table if exists accions;
create table accions (
  accioid bigint not null,
  nom character varying(45)  not null,
  nomllegenda character varying(45)  not null,
  color character varying(45)  default null,
  descripcio character varying(500)  default null,
  primary key  ("accioid")
);

--
-- table structure for table departaments"
--

drop table if exists departaments;
create table departaments (
  departamentid bigint not null,
  nom character varying(300) character  not null,
  coordinadorid character varying(200) character  not null,
  nomldap character varying(200) character  not null,
  descripcio character varying(3000) character  default null,
  primary key  ("departamentid")
);

--
-- table structure for table festius"
--

drop table if exists festius;
create table festius (
  festiuid bigint not null,
  nom character varying(300) character  not null,
  data date not null,
  descripcio character varying(2000) character  default null,
  primary key  ("festiuid")
);

--
-- table structure for table grups"
--

drop table if exists grups;
create table grups (
  grupid bigint not null,
  nom character varying(255)  not null,
  descripcio character varying(1000)  default null,
  nivellseguretat bigint default null,
  primary key  ("grupid")
);

--
-- table structure for table jos_twitter"
--

drop table if exists jos_twitter;
create table jos_twitter (
  id bigint not null,
  username character varying(25)  not null,
  comentario character varying(255)  default null,
  fecha datetime default null,
  user_id bigint default null,
  ip character varying(50)  default null,
  primary key  ("id")
);

--
-- table structure for table modificacionsqueesticfent"
--

drop table if exists modificacionsqueesticfent;
create table modificacionsqueesticfent (
  modificacioid bigint not null,
  accioid bigint not null,
  usuariid character varying(45)  not null,
  projecteid bigint not null,
  data timestamp not null default current_timestamp,
  queesticfentid bigint default null comment '-1 significa nou',
  dada1 character varying(250)  default null,
  dada2 character varying(250)  default null,
  primary key  ("modificacioid"),
  key accioid ("accioid"),
  key modificacionsqueesticfent_ibfk_1 ("projecteid"),
  constraint accioid foreign key ("accioid") references accions ("accioid") on delete no action on update no action,
  constraint modificacionsqueesticfent_ibfk_1 foreign key ("projecteid") references projectes ("projecteid")
);

--
-- table structure for table personalprojecte"
--

drop table if exists personalprojecte;
create table personalprojecte (
  personalprojecteid bigint not null,
  usuariid character varying(50)  not null,
  projecteid bigint not null,
  ordre bigint not null,
  percent int(7) default null,
  primary key  ("personalprojecteid"),
  key projecteid ("projecteid"),
  constraint personalprojecte_ibfk_1 foreign key ("projecteid") references projectes ("projecteid")
);

--
-- table structure for table projectes"
--

drop table if exists projectes;
create table projectes (
  projecteid bigint not null,
  nom character varying(45)  not null,
  departamentid bigint not null,
  descripcio character varying(500)  default null,
  actiu tinyint(1) default null,
  fromdata datetime not null,
  todata datetime default null,
  primary key  ("projecteid"),
  key departamentid ("departamentid"),
  constraint projectes_ibfk_1 foreign key ("departamentid") references departaments ("departamentid")
);

--
-- table structure for table usuarisdepartament"
--

drop table if exists usuarisdepartament;
create table usuarisdepartament (
  usuaridepartamentid bigint not null,
  usuariid character varying(20)  not null,
  departamentid bigint not null,
  primary key  ("usuaridepartamentid"),
  key departamentid ("departamentid"),
  constraint departamentsusuari_ibfk_1 foreign key ("departamentid") references departaments ("departamentid")
);

[fbosch-fundaciobit]

S'han migrat totes les taules i generat el codi base de GenApp.