USE dpm_db;

ALTER TABLE dpm_copy_filereq
       DROP INDEX CF_SURL_IDX;
ALTER TABLE dpm_copy_filereq
       DROP INDEX CT_SURL_IDX;
ALTER TABLE dpm_put_filereq
       DROP INDEX P_SURL_IDX;
ALTER TABLE dpm_put_filereq
       DROP INDEX P_PFN_IDX;
ALTER TABLE dpm_get_filereq
       DROP INDEX G_SURL_IDX;
ALTER TABLE dpm_get_filereq
       DROP INDEX G_PFN_IDX;
ALTER TABLE dpm_req
       DROP INDEX U_DESC_IDX;
ALTER TABLE dpm_pending_req
       DROP INDEX P_U_DESC_IDX;
ALTER TABLE dpm_copy_filereq
       DROP INDEX pk_c_fullid;
ALTER TABLE dpm_put_filereq
       DROP INDEX pk_p_fullid;
ALTER TABLE dpm_get_filereq
       DROP INDEX pk_g_fullid;
ALTER TABLE dpm_req
       DROP INDEX r_token;
ALTER TABLE dpm_pending_req
       DROP INDEX r_token;

DROP TABLE dpm_copy_filereq;
DROP TABLE dpm_put_filereq;
DROP TABLE dpm_get_filereq;
DROP TABLE dpm_req;
DROP TABLE dpm_pending_req;

CREATE TABLE dpm_pending_req (
       rowid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       r_ordinal INTEGER,
       r_token VARCHAR(36) BINARY,
       r_uid INTEGER,
       r_gid INTEGER,
       client_dn VARCHAR(255) BINARY,
       clienthost VARCHAR(63) BINARY,
       r_type CHAR(1) BINARY,
       u_token VARCHAR(255) BINARY,
       flags INTEGER,
       retrytime INTEGER,
       nbreqfiles INTEGER,
       ctime INTEGER,
       stime INTEGER,
       etime INTEGER,
       status INTEGER,
       errstring VARCHAR(255) BINARY,
       groups VARCHAR(255))
	TYPE = InnoDB;

CREATE TABLE dpm_req (
       rowid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       r_ordinal INTEGER,
       r_token VARCHAR(36) BINARY,
       r_uid INTEGER,
       r_gid INTEGER,
       client_dn VARCHAR(255) BINARY,
       clienthost VARCHAR(63) BINARY,
       r_type CHAR(1) BINARY,
       u_token VARCHAR(255) BINARY,
       flags INTEGER,
       retrytime INTEGER,
       nbreqfiles INTEGER,
       ctime INTEGER,
       stime INTEGER,
       etime INTEGER,
       status INTEGER,
       errstring VARCHAR(255) BINARY,
       groups VARCHAR(255))
	TYPE = InnoDB;

CREATE TABLE dpm_get_filereq (
       rowid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       r_token VARCHAR(36) BINARY,
       f_ordinal INTEGER,
       r_uid INTEGER,
       from_surl BLOB,
       protocol VARCHAR(7) BINARY,
       lifetime INTEGER,
       f_type CHAR(1) BINARY,
       s_token CHAR(36) BINARY,
       ret_policy CHAR(1),
       flags INTEGER,
       server VARCHAR(63) BINARY,
       pfn BLOB,
       actual_size BIGINT UNSIGNED,
       status INTEGER,
       errstring VARCHAR(255) BINARY)
	TYPE = InnoDB;

CREATE TABLE dpm_put_filereq (
       rowid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       r_token VARCHAR(36) BINARY,
       f_ordinal INTEGER,
       to_surl BLOB,
       protocol VARCHAR(7) BINARY,
       lifetime INTEGER,
       f_lifetime INTEGER,
       f_type CHAR(1) BINARY,
       s_token CHAR(36) BINARY,
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       requested_size BIGINT UNSIGNED,
       server VARCHAR(63) BINARY,
       pfn BLOB,
       actual_size BIGINT UNSIGNED,
       status INTEGER,
       errstring VARCHAR(255) BINARY)
	TYPE = InnoDB;

CREATE TABLE dpm_copy_filereq (
       rowid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       r_token VARCHAR(36) BINARY,
       f_ordinal INTEGER,
       from_surl BLOB,
       to_surl BLOB,
       f_lifetime INTEGER,
       f_type CHAR(1) BINARY,
       s_token CHAR(36) BINARY,
       ret_policy CHAR(1),
       ac_latency CHAR(1),
       flags INTEGER,
       actual_size BIGINT UNSIGNED,
       status INTEGER,
       errstring VARCHAR(255) BINARY)
	TYPE = InnoDB;

ALTER TABLE dpm_pending_req
       ADD UNIQUE (r_token);
ALTER TABLE dpm_req
       ADD UNIQUE (r_token);
ALTER TABLE dpm_get_filereq
       ADD UNIQUE pk_g_fullid (r_token, f_ordinal);
ALTER TABLE dpm_put_filereq
       ADD UNIQUE pk_p_fullid (r_token, f_ordinal);
ALTER TABLE dpm_copy_filereq
       ADD UNIQUE pk_c_fullid (r_token, f_ordinal);

CREATE INDEX P_U_DESC_IDX ON dpm_pending_req(u_token);
CREATE INDEX U_DESC_IDX ON dpm_req(u_token);
CREATE INDEX G_SURL_IDX ON dpm_get_filereq(FROM_SURL(255));
CREATE INDEX P_SURL_IDX ON dpm_put_filereq(TO_SURL(255));
CREATE INDEX CF_SURL_IDX ON dpm_copy_filereq(FROM_SURL(255));    
CREATE INDEX CT_SURL_IDX ON dpm_copy_filereq(TO_SURL(255));
CREATE INDEX G_PFN_IDX ON dpm_get_filereq(pfn(255));
CREATE INDEX P_PFN_IDX ON dpm_put_filereq(pfn(255));