MySQL --> PSQL (Schema anpassen)

Datenbank Migration (z. B. von MySQL auf PostgreSQL)
Antworten
vci
Neuer Benutzer
Beiträge: 3
Registriert: Fr 18. Dez 2009, 11:39

MySQL --> PSQL (Schema anpassen)

Beitrag von vci »

Hallo zusammen,

ich bin dabei einen dedicated Server zusammenzubauen (fuer mich und ein paar Freunde). Dabei ist die Wahl auf PSQL gefallen und nun würde ich gerne von diesem HowTo: ISP Mail Server mit Exim 4, Dovecot, MySQL und SpamAssassin auf Debian GNU/Linux etch
Die Datenbankschemata auf PSQL anpassen. Könnt ihr mir dabei helfen, bitte?

Ich habe mir das bisher so überlegt:

Code: Alles auswählen

CREATE TABLE mail_accounts (
  id serial NOT NULL,
  local_part varchar(255) NOT NULL default ' ',
  domain serial NOT NULL,
  forward varchar(255) NOT NULL,
  cc varchar(255) default NULL,
  name varchar(255) NOT NULL default ' ',
  pwclear varchar(255) NOT NULL default ' ',
  pwcrypt varchar(255) NOT NULL default ' ',
  is_away varchar(1) NOT NULL default 'N',
  away_text text,
  spam_check varchar(1) NOT NULL default 'N',
  spam_purge varchar(1) NOT NULL default 'N',
  virus_check varchar(1) NOT NULL default 'N',
  is_enabled varchar(1) NOT NULL default 'Y',
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  CHECK (is_away IN ('Y','N')),
  CHECK (spam_check IN ('Y','N')),
  CHECK (spam_purge IN ('Y','N')),
  CHECK (virus_check IN ('Y','N')),
  CHECK (is_enabled IN ('Y','N')),
  PRIMARY KEY  (id),
  UNIQUE (domain,local_part)
)

CREATE TABLE domains (
  id serial NOT NULL,
  domain varchar(255) NOT NULL,
  is_enabled varchar(3) NOT NULL,
  mail_host serial NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  CHECK (is_enabled IN ('yes','no')),
  PRIMARY KEY  (id)
)


Probleme bisher:
-Wie bilde ich das auto_increment korrekt ab? (serial ?)
-Wie bilde ich den enum korrekt ab? (check ?)
-Wie bilde ich primary key, unique key und key korrekt ab? ( ? )
-Passt das nachher, wenn ich bei den Daten timestamp statt integer verwende? ( ? )
-Wie passe ich die constraints an?

Vielen Dank für eure Hilfe!

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Beitrag von akretschmer »

vci hat geschrieben:Hallo zusammen,

ich bin dabei einen dedicated Server zusammenzubauen (fuer mich und ein paar Freunde). Dabei ist die Wahl auf PSQL gefallen und nun würde ich gerne von diesem HowTo: ISP Mail Server mit Exim 4, Dovecot, MySQL und SpamAssassin auf Debian GNU/Linux etch
Die Datenbankschemata auf PSQL anpassen. Könnt ihr mir dabei helfen, bitte?

Ich habe mir das bisher so überlegt:

Code: Alles auswählen

CREATE TABLE mail_accounts (
  id serial NOT NULL,
  local_part varchar(255) NOT NULL default ' ',
  domain serial NOT NULL,
  forward varchar(255) NOT NULL,
  cc varchar(255) default NULL,
  name varchar(255) NOT NULL default ' ',
  pwclear varchar(255) NOT NULL default ' ',
  pwcrypt varchar(255) NOT NULL default ' ',
  is_away varchar(1) NOT NULL default 'N',
  away_text text,
  spam_check varchar(1) NOT NULL default 'N',
  spam_purge varchar(1) NOT NULL default 'N',
  virus_check varchar(1) NOT NULL default 'N',
  is_enabled varchar(1) NOT NULL default 'Y',
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  CHECK (is_away IN ('Y','N')),
  CHECK (spam_check IN ('Y','N')),
  CHECK (spam_purge IN ('Y','N')),
  CHECK (virus_check IN ('Y','N')),
  CHECK (is_enabled IN ('Y','N')),
  PRIMARY KEY  (id),
  UNIQUE (domain,local_part)
)

CREATE TABLE domains (
  id serial NOT NULL,
  domain varchar(255) NOT NULL,
  is_enabled varchar(3) NOT NULL,
  mail_host serial NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  CHECK (is_enabled IN ('yes','no')),
  PRIMARY KEY  (id)
)


Probleme bisher:
-Wie bilde ich das auto_increment korrekt ab? (serial ?)
-Wie bilde ich den enum korrekt ab? (check ?)
-Wie bilde ich primary key, unique key und key korrekt ab? ( ? )
-Passt das nachher, wenn ich bei den Daten timestamp statt integer verwende? ( ? )
-Wie passe ich die constraints an?

Vielen Dank für eure Hilfe!



Ich würde:

- statt varchar(N) gleich TEXT nehmen
- statt varchar(1) und zu prüfen ob das Y oder N ist gleich BOOL nehmen

Auto_increment macht man mitserial, ja. Wo brauchst Du ENUM? Den Rest Deiner Fragen versteh ich nicht wirklich.

Wozu dient domain serial NOT NULL in der ersten Tabelle? Willst Du damit auf die 2. Tabelle referenzieren? Falls ja: ändere das in domain int references domain


Andreas
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

vci
Neuer Benutzer
Beiträge: 3
Registriert: Fr 18. Dez 2009, 11:39

Beitrag von vci »

Danke fuer deine Antwort.

Die Erlaeuterung der Schemata findest du hier:
ISP Mail Server mit Exim 4, Dovecot, MySQL und SpamAssassin auf Debian GNU/Linux etch

Richtig, das domain aus mail_accounts soll auf die Tabelle domains verweisen!

Ich bin noch relativ neu im Bereich Datenbanken und habe bisher nur ganz billige MySQL-Konstrukte erzeugt, daher nun die Schwierigkeiten das nun alles anzupassen :-)

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Beitrag von akretschmer »

vci hat geschrieben:Danke fuer deine Antwort.

Die Erlaeuterung der Schemata findest du hier:
ISP Mail Server mit Exim 4, Dovecot, MySQL und SpamAssassin auf Debian GNU/Linux etch

Richtig, das domain aus mail_accounts soll auf die Tabelle domains verweisen!

Ich bin noch relativ neu im Bereich Datenbanken und habe bisher nur ganz billige MySQL-Konstrukte erzeugt, daher nun die Schwierigkeiten das nun alles anzupassen :-)


Okay, Link kurz überflogen.

Ja, wie gesagt, diese ENUM mit 'Yes', und 'No' anstelle von BOOL ist IMHO Schwachfug.

Wenn das läuft, sag Bescheid. Das MySQL an PG anzupassen scheint mir machbar, kannst ja bei konkreten Problemen fragen.

Andreas
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

vci
Neuer Benutzer
Beiträge: 3
Registriert: Fr 18. Dez 2009, 11:39

Beitrag von vci »

Danke, ja Boolean zu nehmen macht da deutlich mehr Sinn, da man trotzdem einfach 'yes' und 'no' verwenden kann.

Was mir noch nicht so ganz klar ist, das Konstrukt:

Code: Alles auswählen

CREATE TABLE domains (
...
`mail_host` int(16) NOT NULL,
...
KEY `domain` (`domain`),
KEY `mail_host` (`mail_host`)
...
)

ALTER TABLE `domains`
  ADD CONSTRAINT `domains_ibfk_3` FOREIGN KEY (`mail_host`) REFERENCES `hosts` (`id`) ON DELETE CASCADE;


a) Also KEY erstellt ein Index, pendant in Postgresql ist ... ? CREATE INDEX ?
Oder gibt es da etwas, was direkt bei CREATE TABLE genutzt werden kann?
Scheinbar kann man in PostgreSQL auch unique keys keine Namen zuweisen, ist das richtig?
b) Sinn des constraints ist es ja, die referenzierten Tabellen zu loeschen wenn der ein Eintrag aus Domains geloescht wird.
In Postgresql sieht das dann so aus, oder?

Code: Alles auswählen

CREATE TABLE domains (
 ...
    mail_host int REFERENCES hosts NOT NULL ON DELETE CASCADE,
 ...
)


Also ohne separaten Befehl.

akretschmer
Erfahrener Benutzer
Beiträge: 7733
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Beitrag von akretschmer »

vci hat geschrieben:Danke, ja Boolean zu nehmen macht da deutlich mehr Sinn, da man trotzdem einfach 'yes' und 'no' verwenden kann.

Was mir noch nicht so ganz klar ist, das Konstrukt:

Code: Alles auswählen

CREATE TABLE domains (
...
`mail_host` int(16) NOT NULL,
...
KEY `domain` (`domain`),
KEY `mail_host` (`mail_host`)
...
)

ALTER TABLE `domains`
  ADD CONSTRAINT `domains_ibfk_3` FOREIGN KEY (`mail_host`) REFERENCES `hosts` (`id`) ON DELETE CASCADE;


a) Also KEY erstellt ein Index, pendant in Postgresql ist ... ? CREATE INDEX ?
Oder gibt es da etwas, was direkt bei CREATE TABLE genutzt werden kann?
Scheinbar kann man in PostgreSQL auch unique keys keine Namen zuweisen, ist das richtig?
b) Sinn des constraints ist es ja, die referenzierten Tabellen zu loeschen wenn der ein Eintrag aus Domains geloescht wird.
In Postgresql sieht das dann so aus, oder?

Code: Alles auswählen

CREATE TABLE domains (
 ...
    mail_host int REFERENCES hosts NOT NULL ON DELETE CASCADE,
 ...
)


Also ohne separaten Befehl.



Es gibt viele Wege nach Rom:

Code: Alles auswählen

test=# create table t1 (id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
Zeit: 2,336 ms
test=*# create table t2 (i int references t1);
CREATE TABLE
Zeit: 1,613 ms
test=*# create table t3 (i int, foreign key (i) references t1(id));
CREATE TABLE
Zeit: 1,564 ms
test=*# create table t4 (i int);
CREATE TABLE
Zeit: 0,833 ms
test=*# alter table t4 add foreign key (i) references t1(id) on delete cascade;
ALTER TABLE
Zeit: 2,548 ms


Andreas
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten