create_engine.ddl

实现了SyncML无线同步协议

--
-- This script contains the ddl to create the engine database.
--
-- @author Stefano Fornari @ Funambol
--
-- @version $Id: create_engine.ddl,v 1.12 2004/05/18 08:49:22 luigiafassina Exp $
--

create table sync4j_principal
(
  username    varchar(32) not null,
  device      varchar(128) not null,
  id          varchar(128) not null,
  
  constraint pk_sync4j_principal primary key (id)
);



create table sync4j_user
(
  username   varchar(32) not null,
  password   varchar(32) not null,
  email      varchar(50),
  first_name varchar(255),
  last_name  varchar(255),
  internal_user char(1 ) not null default 'N',
  
  constraint pk_sync4j_user primary key (username)
);



create table sync4j_device
(
  id          varchar(128) not null,
  description varchar(255),
  type        varchar(255),
  
  constraint pk_sync4j_device primary key (id)
);



create table sync4j_sync_source
(
  uri    varchar(128) not null,
  config varchar(255) not null,
  name         varchar(200) not null,
  sourcetype   varchar(128) not null,

  
  constraint pk_sync4j_sync_source primary key (uri)
);



create table sync4j_last_sync
(
  principal   varchar(128) not null,
  sync_source varchar(128) not null,
  last_anchor varchar(20 ),
  start_sync  datetime,
  end_sync    datetime,
  
  constraint pk_sync4j_last_sync primary key (principal, sync_source)
);



create table sync4j_client_mapping
(
  principal   varchar(128) not null,
  sync_source varchar(128) not null,
  luid        varchar(200) not null,
  guid        varchar(200) not null,
  
  constraint pk_clientmapping primary key (principal, sync_source, luid, guid)
);



CREATE TABLE sync4j_id
(
  idspace VARCHAR(30) NOT NULL,
  counter INT         NOT NULL,
  
  CONSTRAINT pk_sync4j_id PRIMARY KEY (idspace)
);


CREATE TABLE sync4j_module
(
  id          VARCHAR(128) NOT NULL,
  name        VARCHAR(200) NOT NULL,
  description VARCHAR(200),
  
  CONSTRAINT pk_sync4j_module PRIMARY KEY (id)
);


CREATE TABLE sync4j_sync_source_type
(
  id           VARCHAR(128) NOT NULL,
  description  VARCHAR(200),
  class        VARCHAR(255) NOT NULL,
  admin_class  VARCHAR(255),
  
  CONSTRAINT pk_sync4j_sst PRIMARY KEY (id)
);


CREATE TABLE sync4j_module_sync_source_type
(
  module     VARCHAR(128) NOT NULL,
  sourcetype VARCHAR(128) NOT NULL,
  
  CONSTRAINT pk_sync4j_msst PRIMARY KEY (module, sourcetype)
);



CREATE TABLE sync4j_connector
(
  id            VARCHAR(128) NOT NULL,
  name          VARCHAR(200) NOT NULL,
  description   VARCHAR(200),
  admin_class   VARCHAR(255),
    
  CONSTRAINT pk_sync4j_connector PRIMARY KEY (id)
);


CREATE TABLE sync4j_module_connector
(
  module      VARCHAR(128) NOT NULL,
  connector   VARCHAR(128) NOT NULL,
    
  CONSTRAINT pk_sync4j_module_connector PRIMARY KEY (module, connector)
);


CREATE TABLE sync4j_connector_source_type
(
  connector  VARCHAR(128) NOT NULL,
  sourcetype VARCHAR(128) NOT NULL,
  
  CONSTRAINT pk_sync4j_connector_sst PRIMARY KEY (connector, sourcetype)
);


CREATE TABLE sync4j_role
(
  role        VARCHAR(128) NOT NULL,
  description VARCHAR(200) NOT NULL,
 
  CONSTRAINT pk_sync4j_role PRIMARY KEY (role)
);


CREATE TABLE sync4j_user_role
(
  username VARCHAR(32 ) NOT NULL,
  role     VARCHAR(128) NOT NULL,
 
  CONSTRAINT pk_sync4j_user_role PRIMARY KEY (username,role)
);


alter table sync4j_principal add constraint fk_user foreign key (
  username
)
references sync4j_user (
  username
);


alter table sync4j_principal add constraint fk_device foreign key (
  device
)
references sync4j_device (
  id
);


alter table sync4j_last_sync add constraint fk_principal foreign key (
  principal
)
references sync4j_principal (
  id
);


alter table sync4j_last_sync add constraint fk_source foreign key (
  sync_source
)
references sync4j_sync_source (
  uri
);


alter table sync4j_client_mapping add constraint fk_principal1 foreign key (
  principal
)
references sync4j_principal (
  id
);


alter table sync4j_client_mapping add constraint fk_source1 foreign key (
  sync_source
)
references sync4j_sync_source (
  uri
);