create_engine.ddl

实现了SyncML无线同步协议

--
-- This script contains the ddl to create the engine database,
--   as created with dbschema from ids921uc42.
--
-- @author Hoyte Swager @ DigiDoc
--
-- @version $Id: create_engine.ddl,v 1.11 2004/05/18 08:49:21 luigiafassina Exp $
--

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

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',
    
    primary key (username)  constraint pk_sync4j_user
  );

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

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,

    
    primary key (uri)  constraint pk_sync4j_sync_source
  );

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

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,
    
    primary key (principal,sync_source,luid,guid)  constraint pk_clientmapping
);
create table sync4j_id
(
  idspace varchar(30) not null,
  counter int         not null,
  
  primary key (idspace) constraint pk_sync4j_id
);


create table sync4j_module
(
  id          varchar(128) not null,
  name        varchar(200) not null,
  description varchar(200),
  
  primary key (id) constraint pk_sync4j_module
);


create table sync4j_sync_source_type
(
  id           varchar(128) not null,
  description  varchar(200),
  class        varchar(255) not null,
  admin_class  varchar(255),
  
  primary key (id) constraint pk_sync4j_sst
);


create table sync4j_module_sync_source_type
(
  module     varchar(128) NOT NULL,
  sourcetype varchar(128) NOT NULL,
  
  primary key (module, sourcetype) constraint pk_sync4j_msst
);



create table sync4j_connector
(
  id            varchar(128) not null,
  name          varchar(200) not null,
  description   varchar(200),
  admin_class   varchar(255),
    
  primary key (id) constraint pk_sync4j_connector 
);


create table sync4j_module_connector
(
  module      varchar(128) NOT NULL,
  connector   varchar(128) NOT NULL,
    
  primary key (module, connector) constraint pk_sync4j_module_connector
);


create table sync4j_connector_source_type
(
  connector  varchar(128) NOT NULL,
  sourcetype varchar(128) NOT NULL,
  
  primary key (connector, sourcetype) constraint pk_sync4j_connector_sst 
);


create table sync4j_role
(
  role        varchar(128) NOT NULL,
  description varchar(200) not null,
 
  primary key (role) constraint pk_sync4j_role
);


create table sync4j_user_role
(
  username varchar(32 ) NOT NULL,
  role     varchar(128) NOT NULL,
 
  primary key (username,role) constraint pk_sync4j_user_role
);


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

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

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

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

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

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