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.13 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  timestamp,
  end_sync    timestamp,
  
  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
);