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.10 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,
  
  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,
  
  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)
);