BEGIN TRANSACTION;

CREATE TABLE dc_object (
	id serial NOT NULL PRIMARY KEY,
	dc varchar(80) NOT NULL UNIQUE,
	description varchar(255)
);

CREATE TABLE organizational_unit (
	id serial NOT NULL PRIMARY KEY,
	ou varchar(40) NOT NULL UNIQUE,
	description varchar(255)
);

CREATE TABLE posix_group (
	id serial NOT NULL PRIMARY KEY,
	cn varchar(255) NOT NULL UNIQUE,
	gidnumber int NOT NULL UNIQUE
);

CREATE TABLE samse_ou (
	id serial NOT NULL PRIMARY KEY,
	ou varchar(255) NOT NULL,
	uid varchar(40) NOT NULL UNIQUE,
	legalform varchar(255),
	capital varchar(255),
	collectiveconvention varchar(255),
	description varchar(255),
	responsable varchar(1024),
	quality varchar(255),
	internationalisdnnumber varchar(25),
	businesscategory varchar(80),
	telephonenumber varchar(25),
	facsimiletelephonenumber varchar(25),
	street varchar(255),
	postofficebox varchar(10),
	postalcode varchar(6),
	l varchar(255),
	region varchar(80),
	rpvnumber varchar(15),
	companynumber int,
	agencyshield varchar(80),
	maildomain varchar(80),
	codeape varchar(15),
	realsite varchar(40),
	codelidis int
);

CREATE TABLE group_member (
	group_id int NOT NULL references posix_group(id),
	memberuid varchar(6),
	UNIQUE(group_id,memberuid)
);

CREATE TABLE samse_person (
	id serial NOT NULL PRIMARY KEY,
	ou varchar(255) NOT NULL,
	employeenumber varchar(6) NOT NULL UNIQUE,
	uid varchar(10) NOT NULL,
	cn varchar(128),
	sn varchar(80),
	givenname varchar(40),
	displayname varchar(128),
	mail varchar(255),
	mailboxname varchar(80),
	title varchar(255),
	employeetype char(1),
	departmentnumber int,
	arrivaldate varchar(10),
	birthdate varchar(10),
	leavingdate varchar(10),
	contributiongroup char(2),
	sexe char(1),
	userpassword varchar(80),
	loginshell varchar(80),
	homedirectory varchar(80),
	uidnumber int,
	gidnumber int,
	shadowlastchange int,
	shadowmin int,
	shadowmax int,
	shadowwarning int,
	shadowexpire int,
	telephonenumber varchar(25),
	fixrpvnumber varchar(10),
	mobile varchar(25),
	mobilerpvnumber varchar(10),
	assistant varchar(255),
	codeemploi varchar(10),
	codeagence int,
	fonction varchar(1),
	service varchar(255),
	typeRTT varchar(10)
);

CREATE TABLE samse_activite (
	person_id int NOT NULL references samse_person(id),
	activite varchar(10)
);


END TRANSACTION;
