Schema

Tables Involved

failover_servers

NOTES
* November 5, 2010 - Schema change. Return failover_for to NOT NULL.
Constraint: A failover server (server_id) can have only one entry in this table (it’s the PK) and thus be in only one state at a time. 'available' is no longer a state of this table because we are create a failover_server_pool table

DROP TABLE IF EXISTS failover_servers;
CREATE TABLE failover_servers (
  server_id int(10) NOT NULL,
  failover_for int(10) NOT NULL,
  state enum( 'provisioning', 'syncing', 'active', 'recycling', 'trashed') NOT NULL,
  description varchar(255) DEFAULT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_modified timestamp NOT NULL,
  PRIMARY KEY (server_id),
  UNIQUE KEY (failover_for),
  CONSTRAINT failover_pool_server 
  FOREIGN KEY (server_id)
  REFERENCES failover_server_pool(server_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

failover_server_pool

Pool of available servers. As simple of a schema as one can get:

CREATE TABLE failover_server_pool (
  server_id int(10) NOT NULL,
  created timestamp NOT NULL,
  PRIMARY KEY (server_id),
  FOREIGN KEY (server_id)
  REFERENCES server(server_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

server_features

CREATE TABLE `server_features` (
   `server_id` int(10) NOT NULL,
   `feature` varchar(64) NOT NULL,
   `description` varchar(255) NULL,
  PRIMARY KEY(server_id, feature),
  FOREIGN KEY (server_id)
  REFERENCES server(server_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

server_pri_failover

CREATE TABLE `server_pri_failover` (
  `server_id` int(11) NOT NULL,
  `incoming_failover_server` int(11) NOT NULL,
  `incoming_failover_extension` int(11) NOT NULL,
  `incoming_routing_num` varchar(45) NOT NULL,
  `incoming_routing_dest` varchar(30) NOT NULL,
  `incoming_secondary_routing_dest` varchar(45) NOT NULL,
  `incoming_secondary_routing_dest_is_vmbox` varchar(45) NOT NULL,
  PRIMARY KEY  (`server_id`),
  FOREIGN KEY (`server_id`) 
  REFERENCES server(`server_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 

Original prototype table - failover_servers

CREATE TABLE failover_servers( 
  failover_server_id int(11) NOT NULL, 
  server_id int(11) DEFAULT NULL, 
  PRIMARY KEY(failover_server_id, server_id), 
  FOREIGN KEY (failover_server_id)
  REFERENCES server(server_id),
  FOREIGN KEY (server_id)
  REFERENCES server(server_id)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8
;

drplus_failover_status (prototype)

 CREATE TABLE `drplus_failover_status` (
  `server_id` int(10) NOT NULL,
  `failover_for` int(10) NOT NULL,
  `state` enum('provisioning','active','recycling') NOT NULL,
   `description` varchar(255) NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`server_id`),
  FOREIGN KEY (server_id)
  REFERENCES server(server_id),
  FOREIGN KEY (failover_for)
  REFERENCES server(server_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Change table engine to InnoDB

ALTER table server
ENGINE=InnoDB;

NOTE: When creating a foreign key constraints from an InnoDB table then the target table must use InnoDB Engine as well.

Generate ORM Mapper Classes

To automatically generate the Result classes for the schema we do:

perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./  -e '
make_schema_at("F::DB",  
{ 
    constraint => qr/server_features|drplus_failover_status/,
    debug => 1,
 }, 
[ "DBI:mysql:pbxtra:localhost","dbuser", "pass" ])'

NOTE: This dump (as is) should be run from F-test/ directory to put the Result classes into F-test/lib/F/DB/Result directory. One can change dump_to_dir variable if they want to dump from somewhere else.

Create DBIC Model (and Result Classes) for Catalyst Framework

script/failover_create.pl model DB DBIC::Schema Failover::Schema \
create=static dbi:mysql:database=pbxtra dbuser generic

Schema and namespace

load_namespaces(
result_namespace => ‘+F::DB::Result’,
)

My tags:

 

Popular tags: