branch-1.2/ambari-server/src/main/resources/Ambari-DDL.sql

--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

DROP SCHEMA IF EXISTS ambari CASCADE;

DROP ROLE IF EXISTS "ambari-server";

CREATE ROLE "ambari-server" LOGIN ENCRYPTED PASSWORD 'bigdata';

CREATE SCHEMA ambari
  AUTHORIZATION "ambari-server";

COMMENT ON SCHEMA ambari
  IS 'test schema';

SET search_path TO ambari;

/* Table for storing user information*/
CREATE TABLE Users
(
user_name VARCHAR,
user_password VARCHAR,
ldap_user boolean DEFAULT FALSE NOT NULL,
create_time TIMESTAMP DEFAULT now() NOT NULL,
PRIMARY KEY(user_name, ldap_user)
);

/*Table for storing roles list - can be dropped out if list of roles is predefined and limited on upper layer*/
CREATE TABLE Roles
(
role_name VARCHAR PRIMARY KEY
);

/*Users - Roles mapping table*/
CREATE TABLE user_roles
(
user_name VARCHAR,
ldap_user boolean default false,
role_name VARCHAR references Roles(role_name),
PRIMARY KEY(user_name, ldap_user, role_name),
FOREIGN KEY(user_name, ldap_user) REFERENCES Users(user_name, ldap_user)
);

/* Overall clusters table - all created/managed clusters */
CREATE TABLE Clusters
(
cluster_id BIGSERIAL,
cluster_name VARCHAR(100) UNIQUE NOT NULL,
desired_cluster_state VARCHAR DEFAULT '' NOT NULL,
cluster_info VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id)
);

/* All hosts for all clusters */
CREATE TABLE Hosts
(
host_name VARCHAR NOT NULL,
ipv4 VARCHAR UNIQUE,
ipv6 VARCHAR UNIQUE,
total_mem BIGINT DEFAULT '0' NOT NULL,
cpu_count INTEGER DEFAULT '0' NOT NULL,
cpu_info VARCHAR DEFAULT '' NOT NULL,
os_arch VARCHAR DEFAULT '' NOT NULL,
disks_info VARCHAR DEFAULT '' NOT NULL,
os_info VARCHAR DEFAULT '' NOT NULL,
os_type VARCHAR DEFAULT '' NOT NULL,
discovery_status VARCHAR DEFAULT '' NOT NULL,
last_registration_time BIGINT DEFAULT '0' NOT NULL,
rack_info VARCHAR DEFAULT '/default-rack' NOT NULL,
host_attributes VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (host_name)
);

/* Cluster Hosts mapping table */
CREATE TABLE ClusterHostMapping
(
  cluster_id BIGINT references Clusters(cluster_id),
  host_name VARCHAR references Hosts(host_name),
  PRIMARY KEY(cluster_id, host_name)
);

CREATE TABLE ClusterServices
(
cluster_id BIGINT NOT NULL references Clusters(cluster_id),
service_name VARCHAR,
service_enabled INTEGER DEFAULT '0' NOT NULL,
PRIMARY KEY (cluster_id,service_name)
);

/* Configs at a service level */
/* This will be used in most scenarios for homogenous clusters */
/* Snapshot is a blob for all properties and their values. There is no separate row for each property */
/* A special service called AMBARI or GLOBAL can be leveraged for global level configs */
CREATE TABLE ServiceConfig
(
config_version SERIAL /*INTEGER NOT NULL AUTO_INCREMENT*/,
cluster_id BIGINT NOT NULL,
service_name VARCHAR NOT NULL,
config_snapshot VARCHAR DEFAULT '' NOT NULL,
config_snapshot_time timestamp NOT NULL,
PRIMARY KEY (config_version),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);

/* Configs that are overridden at the component level */
/* Combination of serviceconfig and servicecomponentconfig table 
    defines the config for a given component. 
    Absence of an entry implies the component’s configs are same as that of the overall service config */
CREATE TABLE ServiceComponentConfig
(
config_version SERIAL /*INTEGER NOT NULL AUTO_INCREMENT*/,
cluster_id BIGINT NOT NULL,
service_name VARCHAR NOT NULL,
component_name VARCHAR NOT NULL,
config_snapshot VARCHAR DEFAULT '' NOT NULL,
config_snapshot_time timestamp NOT NULL,
PRIMARY KEY (config_version),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);

/* For overridding configs on a per host level for heterogenous clusters */
CREATE TABLE ServiceComponentHostConfig
(
config_version SERIAL /*INTEGER NOT NULL AUTO_INCREMENT*/,
cluster_id BIGINT NOT NULL,
service_name VARCHAR NOT NULL,
component_name VARCHAR NOT NULL,
host_name VARCHAR NOT NULL references Hosts(host_name),
config_snapshot VARCHAR DEFAULT '' NOT NULL,
config_snapshot_time timestamp NOT NULL,
PRIMARY KEY (config_version),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);

CREATE TABLE ServiceDesiredState
(
cluster_id BIGINT,
service_name VARCHAR DEFAULT '' NOT NULL,
desired_state VARCHAR DEFAULT '' NOT NULL,
desired_host_role_mapping INTEGER DEFAULT '0' NOT NULL,
desired_stack_version VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id, service_name),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);

CREATE TABLE HostComponentMapping /*HostRoleMapping*/
(
cluster_id BIGINT,
service_name VARCHAR DEFAULT '' NOT NULL,
host_component_mapping_id SERIAL /*INTEGER NOT NULL AUTO_INCREMENT*/,
host_component_mapping_snapshot VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id, service_name, host_component_mapping_id),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);


CREATE TABLE ClusterState
(
cluster_id BIGINT NOT NULL references Clusters(cluster_id),
current_cluster_state VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id)
);

CREATE TABLE HostState
(
/*cluster_id INTEGER references Clusters(cluster_id),*/
host_name VARCHAR NOT NULL references Hosts(host_name),
available_mem INTEGER DEFAULT '0' NOT NULL,
last_heartbeat_time INTEGER DEFAULT '0' NOT NULL,
time_in_state INTEGER DEFAULT '0' NOT NULL,
agent_version VARCHAR DEFAULT '' NOT NULL,
health_status VARCHAR,
current_state VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (host_name)
);


CREATE TABLE ServiceComponentDesiredState
(
cluster_id BIGINT references Clusters(cluster_id),
service_name VARCHAR DEFAULT '' NOT NULL,
component_name VARCHAR DEFAULT '' NOT NULL,
desired_state VARCHAR DEFAULT '' NOT NULL,
desired_stack_version VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id,service_name,component_name),
FOREIGN KEY (cluster_id, service_name) REFERENCES ClusterServices(cluster_id, service_name)
);


CREATE TABLE HostComponentState
(
cluster_id BIGINT,
service_name VARCHAR DEFAULT '' NOT NULL,
host_name VARCHAR DEFAULT '' NOT NULL references Hosts(host_name),
component_name VARCHAR DEFAULT '' NOT NULL,
current_state VARCHAR DEFAULT '' NOT NULL,
current_config_version VARCHAR DEFAULT '' NOT NULL,
current_stack_version VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id, service_name, host_name, component_name),
FOREIGN KEY (cluster_id, service_name, component_name) REFERENCES ServiceComponentDesiredState(cluster_id, service_name, component_name)
);

CREATE TABLE HostComponentDesiredState
(
cluster_id BIGINT,
service_name VARCHAR DEFAULT '' NOT NULL,
host_name VARCHAR NOT NULL references Hosts(host_name),
component_name VARCHAR DEFAULT '' NOT NULL,
desired_state VARCHAR DEFAULT '' NOT NULL,
desired_config_version VARCHAR DEFAULT '' NOT NULL, /* desired config version defines a combined version of service/component/node-component config versions */
desired_stack_version VARCHAR DEFAULT '' NOT NULL,
PRIMARY KEY (cluster_id,host_name,component_name),
FOREIGN KEY (cluster_id, service_name, component_name) REFERENCES ServiceComponentDesiredState(cluster_id, service_name, component_name)
);

CREATE TABLE STAGE
(
   cluster_id BIGINT references Clusters(cluster_id),
   request_id BIGINT DEFAULT '0',
   stage_id BIGINT DEFAULT '0' NOT NULL,
   log_info VARCHAR DEFAULT '' NOT NULL,
   PRIMARY KEY (request_id, stage_id)
);

CREATE TABLE HOST_ROLE_COMMAND
(
   task_id BIGSERIAL NOT NULL,
   request_id BIGINT NOT NULL,
   stage_id BIGINT NOT NULL,
   host_name VARCHAR DEFAULT '' NOT NULL references Hosts(host_name),
   role VARCHAR DEFAULT '' NOT NULL,
   command VARCHAR DEFAULT '' NOT NULL,
   event VARCHAR DEFAULT '' NOT NULL, /** Refer to ServiceComponentHostEventType.java */
   exitCode INTEGER DEFAULT '0' NOT NULL,
   status VARCHAR DEFAULT '' NOT NULL, /** PENDING, QUEUED, IN_PROGRESS, COMPLETED, FAILED, TIMEDOUT, ABORTED **/
   std_error VARCHAR DEFAULT '' NOT NULL,
   std_out VARCHAR DEFAULT '' NOT NULL,
   start_time BIGINT DEFAULT -1 NOT NULL,
   last_attempt_time BIGINT DEFAULT -1 NOT NULL,
   attempt_count SMALLINT DEFAULT 0 NOT NULL,
   PRIMARY KEY (task_id),
   FOREIGN KEY (request_id, stage_id) REFERENCES STAGE(request_id, stage_id)
);

CREATE TABLE EXECUTION_COMMAND
(
   task_id BIGINT DEFAULT '0' NOT NULL references HOST_ROLE_COMMAND(task_id),
   command VARCHAR NOT NULL, /** Serialized ExecutionCommand **/
   PRIMARY KEY(task_id)
);


CREATE TABLE ROLE_SUCCESS_CRITERIA
(
   request_id BIGINT NOT NULL,
   stage_id BIGINT NOT NULL,
   role VARCHAR DEFAULT '' NOT NULL,
   success_factor FLOAT DEFAULT 1,
   PRIMARY KEY(role, request_id, stage_id),
   FOREIGN KEY (request_id, stage_id) REFERENCES STAGE(request_id, stage_id)
);

--CREATE TABLE ActionStatus 
--(
--cluster_id INTEGER references Clusters(cluster_id),
--host_name VARCHAR DEFAULT '' NOT NULL references Hosts(host_name),
--role VARCHAR DEFAULT '' NOT NULL,
--request_id INTEGER DEFAULT '0' NOT NULL,
--stage_id INTEGER DEFAULT '0' NOT NULL,
--event VARCHAR DEFAULT '' NOT NULL,
--task_id INTEGER DEFAULT '0' NOT NULL,
--status VARCHAR DEFAULT '' NOT NULL, /* PENDING, QUEUED, COMPLETED, FAILED,, ABORTED */ 
--log_info VARCHAR DEFAULT '' NOT NULL,
--continue_criteria bytea /*BLOB*/ DEFAULT '' NOT NULL, /* Define continuation criteria for moving to next stage */
--PRIMARY KEY (cluster_id, host_name, role, request_id, stage_id)
--);


GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ambari TO "ambari-server";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ambari TO "ambari-server";

BEGIN;

insert into Roles(role_name) 
select 'admin'
union all
select 'user';

insert into Users(user_name, user_password)
select 'administrator','538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00'
union all
select 'test','d2f5da28bf8353e836fbae0a7f586b9cbda03f590910998957383371fbacba7e4088394991305ef8';

insert into user_roles(user_name,role_name)
select 'test','user'
union all
select 'administrator','admin';

COMMIT;