Anonymous - 2006-01-04

This question appeared in a thread on the user mailing list on Wed, Jan 04, 2006.

QUESTION: Can you install openQRM with PostGres instead of mySQL?

ANSWER: yes.

HOW TO: Read the thread below first to see decibels feedback and instructions then follow these steps:

STEP 1: Cut and paste the following code into a file and save it as “create.sql”.

--------------------------------------------------------------------------------
-- Filename: ./src/base/include/db/mysql/create.sql
--
-- The contents of this file are subject to the Qlusters Public License
-- Version 1.1 (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.openqrm.org/content/view/33/69/.
--
-- Software distributed under the License is distributed on an AS IS basis,
-- WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
-- the specific language governing rights and limitations under the License.
--
-- The Original Code is openQRM.
--
-- The Initial Developer of the Original Code is Qlusters Corporation. Portions
-- created by Qlusters Corporation are Copyright (C) 2003-2005. All Rights
-- Reserved.
--
-- Contributor(s):
--
-- Description:
--
-- Comments:
--------------------------------------------------------------------------------
create database :QRM_DATABASE_NAME;
\c :QRM_DATABASE_NAME

set client_min_messages = error;

create table qrm_roles (
    id SERIAL NOT NULL CONSTRAINT qrm_roles__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE null,

    role_name varchar(255) null
) WITHOUT OIDs;

create table qrm_users (
    id SERIAL NOT NULL CONSTRAINT qrm_users__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE null,

    user_name varchar(50) not null,
    user_password varchar(50) not null,

    status smallint null,
      
    CONSTRAINT qrm_users__user_name UNIQUE (user_name)
) WITHOUT OIDs;

create table qrm_users_roles (
    user_id int not null
        CONSTRAINT qrm_users_roles__user_id references qrm_users(id) ON DELETE CASCADE,
    role_id int not null
        CONSTRAINT qrm_roles_roles__role_id references qrm_roles(id) ON DELETE CASCADE,

    CONSTRAINT qrm_users_roles__user_role PRIMARY KEY(user_id, role_id)
) WITHOUT OIDS;
-- Index on just user_id is probably pointless...
CREATE INDEX qrm_users_roles__role ON qrm_users_roles( role_id );

create table qrm_events (
    id SERIAL NOT NULL CONSTRAINT qrm_events__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(), -- Should probably be not null
    event_key varchar(255) not null,
    event_level smallint not null,
    resolving_event_id int null
        CONSTRAINT qrm_events__resolving_event REFERENCES qrm_events(id) ON DELETE SET NULL
) WITHOUT OIDS;
CREATE INDEX qrm_events__event_key ON qrm_events( event_key );
CREATE INDEX qrm_events__event_level ON qrm_events( event_level );
CREATE INDEX qrm_events__creation_time ON qrm_events( creation_time );
CREATE INDEX qrm_events__resolving_event_id ON qrm_events( resolving_event_id );

create table qrm_events_entities (
    id SERIAL NOT NULL CONSTRAINT qrm_events_entities__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    entity_id int not null,
    entity_type smallint not null,
    -- should be same size as ve/node name
    entity_name varchar(50) not null,
    is_removed smallint not null default 0
           
) WITHOUT OIDS;
CREATE INDEX qrm_events_entities__entity_id__entity_type ON qrm_events_entities( entity_id, entity_type );

create table qrm_events_entities_relations (
    event_id int not null
        CONSTRAINT RI_qrm_events_entities_relations__event REFERENCES qrm_events( id ) ON DELETE CASCADE,

    event_entity_id int not null
        CONSTRAINT RI_qrm_events_entities_relations__event_entity REFERENCES qrm_events_entities( id ) ON DELETE CASCADE,

    event_entity_index int not null,

    CONSTRAINT qrm_events_entities__PK_event__event_entity PRIMARY KEY(event_id, event_entity_id)
) WITHOUT OIDS;
CREATE INDEX qrm_events_entities_relations__event ON qrm_events_entities_relations(event_id);
CREATE INDEX qrm_events_entities_relations__event_entity ON qrm_events_entities_relations(event_entity_id);

create table qrm_event_attributes (
    id SERIAL NOT NULL CONSTRAINT qrm_event_attributes__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
   
    attr_name varchar(255) not null,
    attr_value text not null,

    event_id int  not null
        CONSTRAINT RI_qrm_events_attributes__event REFERENCES qrm_events( id ) ON DELETE CASCADE

) WITHOUT OIDS;
CREATE INDEX qrm_event_attributes__event__attr_name ON qrm_event_attributes( event_id, attr_name );

create table qrm_properties (
    id SERIAL NOT NULL CONSTRAINT qrm_properties__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    entity_id int,
    entity_type varchar(255),
    category varchar(255),

    property varchar(255) not null,
    value text null,

    description text null,
   
    meta varchar(255),

    CONSTRAINT qrm_properties__entity__type__category__property UNIQUE( entity_id,entity_type,category,property )
) WITHOUT OIDS;

create table qrm_cpu_architectures (
    id SERIAL NOT NULL CONSTRAINT qrm_cpu_architectures__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    CONSTRAINT qrm_cpu_architectures__name UNIQUE( name )
) WITHOUT OIDS;

create table qrm_cpu_models (
    id SERIAL NOT NULL CONSTRAINT qrm_cpu_models__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    -- should probably be cpu_architecture_id
    architecture_id int
        CONSTRAINT qrm_cpu_models__architecture REFERENCES qrm_cpu_architectures( id ),
    regex varchar(255) not null,
    order_index int not null default 0,
    CONSTRAINT qrm_cpu_models__name UNIQUE( name )
) WITHOUT OIDS;
CREATE INDEX qrm_cpu_models__architecture ON qrm_cpu_models( architecture_id );

create table qrm_tags (
    id SERIAL NOT NULL CONSTRAINT qrm_tags__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    description text,
    CONSTRAINT qrm_tags__name UNIQUE(name)
) WITHOUT OIDS;

create table qrm_storage_types (
    id SERIAL NOT NULL CONSTRAINT qrm_storage_types__id PRIMARY KEY,
    name varchar(50) not null,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    comments text,
    interface smallint not null,
    map_script varchar(255) ,
    map_script_parameters varchar(255),
    unmap_script varchar(255),
    unmap_script_parameters varchar(255),
    CONSTRAINT qrm_storage_types__name UNIQUE(name)
) WITHOUT OIDS;

create table qrm_storage_servers (
    id SERIAL NOT NULL CONSTRAINT qrm_storage_servers__id PRIMARY KEY,
    name varchar(50) not null,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    comments text,
    storage_type_id int
        CONSTRAINT qrm_storage_servers__storage_type REFERENCES qrm_storage_types( id ),
    ip_identifier varchar(255) ,
    -- should use inet type in PostgreSQL
    -- ip int(4),
    management_ip_identifier varchar(255),
    -- should use inet type in PostgreSQL
    -- management_ip int(4),
    env_vars text,
    CONSTRAINT qrm_storage_servers__name UNIQUE(name)
) WITHOUT OIDS;
CREATE INDEX qrm_storage_servers__storage_type ON qrm_storage_servers( storage_type_id );

create table qrm_kernel_images (
    id SERIAL NOT NULL CONSTRAINT qrm_kernel_images__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    editable smallint not null default 1, -- Should be BOOLEAN ?
    location varchar(255) null,
    version varchar(255) null,
    comments text,
    cpu_architecture_id int
        CONSTRAINT qrm_kernel_images__architecture REFERENCES qrm_cpu_architectures( id ),
    is_multi_processor smallint not null default 0, -- Should be BOOLEAN ?
    memory_model smallint not null,
    is_hugemem smallint not null default 0, -- Should be BOOLEAN ?
    special_support bigint null,
    CONSTRAINT qrm_kernel_images__name UNIQUE(name),
    CONSTRAINT qrm_kernel_images__location UNIQUE(location)
) WITHOUT OIDS;
CREATE INDEX qrm_kernel_images__architecture ON qrm_kernel_images( cpu_architecture_id );

create table qrm_filesystem_images (
    id SERIAL NOT NULL CONSTRAINT qrm_filesystem_images__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    storage_server_id int
        CONSTRAINT qrm_filesystem_images__storage_server REFERENCES qrm_storage_servers( id ),
    comments text,
    identifier varchar(255) ,
    config_script varchar(255) ,
    config_parameters text,
    is_shared smallint not null default 0, -- BOOLEAN?
    cpu_architecture_id int
        CONSTRAINT qrm_filesystem_images__cpu_architecture REFERENCES qrm_cpu_architectures( id ),
    env_vars text,
    CONSTRAINT qrm_filesystem_images__name UNIQUE(name)
--    CONSTRAINT qrm_filesystem_images__storage_server__identifier UNIQUE(storage_server_id,identifier),
) WITHOUT OIDS;
CREATE INDEX qrm_filesystem_images__storage_server_id ON qrm_filesystem_images( storage_server_id );
CREATE INDEX qrm_filesystem_images__cpu_architecture_id ON qrm_filesystem_images( cpu_architecture_id );

create table qrm_pool_config (
    id SERIAL NOT NULL CONSTRAINT qrm_pool_config__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    virtual_environment_id int not null,
    partition_tech varchar(255),

    pool_min_count int not null default 0,
    pool_max_count int not null default 0,
    pool_percent int null,
    enabled smallint
) WITHOUT OIDS;
CREATE INDEX qrm_pool_config__creation_time ON qrm_pool_config( creation_time );
CREATE INDEX qrm_pool_config__partition_tech ON qrm_pool_config( partition_tech );
CREATE INDEX qrm_pool_config__virtual_environment_id ON qrm_pool_config( virtual_environment_id );

create table qrm_pool_states (
    id SERIAL NOT NULL CONSTRAINT qrm_pool_states__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    virtual_environment_id int null,

    available_count int not null default 0,
    required_count int not null default 0,
    status int not null default -1,

    type smallint not null,
    state_key varchar(50) null
) WITHOUT OIDS;
CREATE INDEX qrm_pool_states__creation_time ON qrm_pool_states( creation_time );
CREATE INDEX qrm_pool_states__virtual_environment_id ON qrm_pool_states( virtual_environment_id );
CREATE INDEX qrm_pool_states__status ON qrm_pool_states( status );

create table qrm_virtual_environment_statistics (
    id SERIAL NOT NULL CONSTRAINT qrm_virtual_environment_statistics__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    cpu_utilization numeric(4,2) null,
    memory_used int null,
    cpu_load numeric(4,2) null,
    swap_used int null,
   
     virtual_environment_id int null,

    available_cpus int null,
    available_memory int null
) WITHOUT OIDS;
CREATE INDEX qrm_virtual_environment_statistics__virtual_environment ON qrm_virtual_environment_statistics( virtual_environment_id );
CREATE INDEX qrm_virtual_environment_statistics__virtual_environment__creation_time ON qrm_virtual_environment_statistics( virtual_environment_id, creation_time );

create table qrm_event_listeners (
    id SERIAL NOT NULL CONSTRAINT qrm_event_listeners__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    event_key varchar(255) not null,
    entity_id int,
    entity_type smallint,

    classloader_id varchar(255),

    listener_data bytea  ,

    is_outside_tx smallint default 0,

    listener_id varchar(255) not null,

    type varchar(50) not null,

    priority smallint not null,

    execution bytea not null,

    CONSTRAINT qrm_event_listeners__entity__type__key__listener UNIQUE (entity_id, entity_type, event_key, listener_id)
) WITHOUT OIDS;

create table qrm_virtual_environments (
    id SERIAL NOT NULL CONSTRAINT qrm_virtual_environments__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    name varchar(50) not null,
    comment text null,
    parameters text null,
   
    -- is_* should be BOOLEAN?
    is_xha smallint not null default 0,
    is_multi_server smallint not null default 0,
    is_ssi smallint not null default 0,
    is_ahf smallint not null default 0,
    is_tam smallint not null default 0,
    is_disable smallint default 1 not null,
    aar_heartbeat_timeout int default null,
    is_migratable smallint default 0 not null,
    partition_tech varchar(150) null,
    ahf_recover_length smallint default 0,
   
    -- resources
    min_resources int default 1 not null,
    max_resources int default 1 not null,
    initial_resources int default 1 not null,

    -- policy
    policy_action smallint default null,
    policy_id int default 1,
    policy_arguments text null,
    policy_scope int default 1,
   
    -- status
    composite_status smallint not null,
    lifecycle_status smallint not null,
    provisioning_status smallint not null,
    application_status smallint not null,
    lock_count int not null default 0,
    required_resources int not null,
    gui_state smallint not null,

    -- xha status
    xha_sanity_status smallint not null,
    xha_working_status smallint not null,

    -- virtual profile
    profile_type int not null default 1,
    profile_cpu_num int not null default 1,
    profile_cpu_speed int not null default 0,
--    profile_cpu_model varchar(50) null,
    cpu_architecture_id int,
   
--    profile_kernel_image varchar(50) null,
    profile_is_shared_image smallint not null default 0,
    profile_ram int not null default 0,

    -- cmrs
    kernel_image_id int null,

    current_pool_config int null,
--    foreign key(current_pool_config) references qrm_pool_config(id) on delete cascade,

    current_pool_state int null,
--    foreign key(current_pool_state) references qrm_pool_states(id) on delete cascade,

    current_virtual_environment_statistics int null,
--    foreign key(current_virtual_environment_statistics) references qrm_virtual_environment_statistics(id) on delete cascade,

    CONSTRAINT qrm_virtual_environments__name UNIQUE(name),
    foreign key(kernel_image_id) references qrm_kernel_images(id) ,
    foreign key(cpu_architecture_id) references qrm_cpu_architectures(id)
) WITHOUT OIDS;
CREATE INDEX qrm_virtual_environments__composite__lifecycle__provisioning__application ON qrm_virtual_environments(composite_status, lifecycle_status, provisioning_status, application_status);
CREATE INDEX qrm_virtual_environments__xha_sanity__xha_working ON qrm_virtual_environments(xha_sanity_status, xha_working_status);
CREATE INDEX qrm_virtual_environments__current_pool_config ON qrm_virtual_environments(current_pool_config);
CREATE INDEX qrm_virtual_environments__current_pool_state ON qrm_virtual_environments(current_pool_state);
CREATE INDEX qrm_virtual_environments__current_ve_stats ON qrm_virtual_environments(current_virtual_environment_statistics);
CREATE INDEX qrm_virtual_environments__kernel_image ON qrm_virtual_environments(kernel_image_id);
CREATE INDEX qrm_virtual_environments__cpu_architecture ON qrm_virtual_environments(cpu_architecture_id);

create table qrm_node_statistics (
    id SERIAL NOT NULL CONSTRAINT qrm_node_statistics__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    cpu_utilization numeric(4,2) null,
    memory_used int null,
    up_time int null,
    cpu_load numeric(4,2) null,
    swap_used int null,

    node_id int null
) WITHOUT OIDS;
CREATE INDEX qrm_node_statistics__node ON qrm_node_statistics( node_id );
CREATE INDEX qrm_node_statistics__node__creation_time ON qrm_node_statistics( node_id, creation_time );

create table qrm_ve_filesystem_images (
    id SERIAL NOT NULL CONSTRAINT qrm_ve_filesystem_immages__id PRIMARY KEY,
    filesystem_image_id int not null,
    virtual_environment_id int null,
    position int not null,
--    CONSTRAINT qrm_ve_filesystem_images__ve__filesystem_image UNIQUE(virtual_environment_id, filesystem_image_id),
    foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade,  
    foreign key(filesystem_image_id) references qrm_filesystem_images(id) on delete cascade  
) WITHOUT OIDS;
CREATE INDEX qrm_ve_filesystem_images__virtual_environment_id ON qrm_ve_filesystem_images( virtual_environment_id );
CREATE INDEX qrm_ve_filesystem_images__filesystem_image_id ON qrm_ve_filesystem_images( filesystem_image_id );

create table qrm_vm_statistics (
    id SERIAL NOT NULL CONSTRAINT qrm_vm_statistics__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    cpu_utilization numeric(4,2) null,
    memory_used int null,
    up_time int null,
    cpu_load numeric(4,2) null,
    swap_used int null,

    vm_id int null
) WITHOUT OIDS;
CREATE INDEX qrm_vm_statistics__vm ON qrm_vm_statistics( vm_id );
CREATE INDEX qrm_vm_statistics__vm__creation_time ON qrm_vm_statistics( vm_id, creation_time );

create table qrm_nodes (
    id SERIAL NOT NULL CONSTRAINT qrm_nodes__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    name varchar(50) not null,
    external_id varchar(45) null,

    os_version varchar(255) null,
    kernel_version varchar(255) null,
    memory int null,
    num_cpus int null,
    cpu_speed int null,
--cpu_model varchar(50) null,
    cpu_model_id int,
    local_filesystem_image_definition_id int,
    foreign key(local_filesystem_image_definition_id) references qrm_filesystem_images(id) on delete set null,

    is_dual_boot smallint not null default 0,

    is_maintenance smallint not null default 0,
    hostname varchar(255) null,
    swap int null,
    mac_addr int ,  
    ip int null,
    ifconfig text null,
    comment text null,
    parameters text null,
    location varchar(255) null,

   
    -- states
    operation_status smallint not null,
    role_status smallint not null,
    context smallint not null,
    operation_update_timestamp int not null,
    gui_state smallint not null,
    -- xha states
    xha_role_status smallint not null,
    xha_sync_status smallint not null,

    -- cmrs
    partition_tech varchar(150) not null,
   
    controlled_by varchar(200) null,
    is_available smallint not null default 0,

    current_node_statistics int null,
--    foreign key(current_node_statistics) references qrm_node_statistics(id) on delete cascade,

    CONSTRAINT qrm_nodes__external UNIQUE(external_id),
    CONSTRAINT qrm_nodes__mac_addr UNIQUE(mac_addr),
    foreign key(cpu_model_id) references qrm_cpu_models(id)
) WITHOUT OIDS;
CREATE INDEX qrm_nodes__ip ON qrm_nodes(ip);
CREATE INDEX qrm_nodes__memory ON qrm_nodes(memory);
CREATE INDEX qrm_nodes__num_cpus ON qrm_nodes(num_cpus);
CREATE INDEX qrm_nodes__cpu_speed ON qrm_nodes(cpu_speed);
CREATE INDEX qrm_nodes__cpu_model ON qrm_nodes(cpu_model_id);
CREATE INDEX qrm_nodes__swap ON qrm_nodes(swap);
CREATE INDEX qrm_nodes__current_node_statistics ON qrm_nodes(current_node_statistics);
CREATE INDEX qrm_nodes__local_filesystem_image_definition ON qrm_nodes(local_filesystem_image_definition_id);

create table qrm_cpu_data(
    id SERIAL NOT NULL CONSTRAINT qrm_cpu_data__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    available_speed int not null,
    node_id int not null
) WITHOUT OIDS;
CREATE INDEX qrm_cpu_data__node ON qrm_cpu_data(node_id);

create table qrm_vm_cpu_data(
    id SERIAL NOT NULL CONSTRAINT qrm_vm_cpu_data__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
    cpu_data_id int null,
    vm_id int null
) WITHOUT OIDS;
CREATE INDEX qrm_vm_cpu_data__cpu_data ON qrm_vm_cpu_data(cpu_data_id);
CREATE INDEX qrm_vm_cpu_data__vm ON qrm_vm_cpu_data(vm_id);

create table qrm_virtual_machines (
    id SERIAL NOT NULL CONSTRAINT qrm_virtual_machines__id PRIMARY KEY,
    creation_time TIMESTAMP WITH TIME ZONE default now(),

    assign_time TIMESTAMP WITH TIME ZONE default null,
    cpu_speed_quota int null,
    current_vm_statistics int null,
    external_id    varchar(45) null,
    external_partition_key varchar(255) null,
    comment text null,   
    os_version varchar(255) null,
    kernel_version varchar(255) null,
    hostname varchar(255) null,
      assigned_filesystem_image_id int null,
     kernel_id int null,
    mac_addr int ,  
    memory_quota int null,
    name varchar(50) null,
    node_id int null,
    num_cpus_quota  int null,
    swap int null,
    ip int null,
    vem_key smallint null,
    ve_id int null,
    partition_tech varchar(150) not null,
   
   -- states
    operation_status smallint not null,
    state_context smallint not null,
    derived_state smallint not null,

    -- xha states
    xha_role_status smallint not null,
    xha_sync_status smallint not null,

    foreign key(assigned_filesystem_image_id) references qrm_ve_filesystem_images(id) on delete set null,
    foreign key(ve_id) references qrm_virtual_environments(id) on delete set null,

--    foreign key(current_node_statistics) references qrm_node_statistics(id) on delete cascade,

    CONSTRAINT qrm_virtual_machines__ve__vem_key UNIQUE(ve_id, vem_key),
    CONSTRAINT qrm_virtual_machines__mac_addr UNIQUE(mac_addr)
) WITHOUT OIDS;
CREATE INDEX qrm_virtual_machines__ip ON qrm_virtual_machines(ip);
CREATE INDEX qrm_virtual_machines__node ON qrm_virtual_machines(node_id);
CREATE INDEX qrm_virtual_machines__memory_quota ON qrm_virtual_machines(memory_quota);
CREATE INDEX qrm_virtual_machines__num_cpus_quota ON qrm_virtual_machines(num_cpus_quota);
CREATE INDEX qrm_virtual_machines__cpu_speed_quota ON qrm_virtual_machines(cpu_speed_quota);
CREATE INDEX qrm_virtual_machines__swap ON qrm_virtual_machines(swap);
CREATE INDEX qrm_virtual_machines__assigned_filesystem_image ON qrm_virtual_machines(assigned_filesystem_image_id);

create table qrm_nodes_tags (
    id SERIAL NOT NULL CONSTRAINT qrm_nodes_tags__id PRIMARY KEY,

    tag_id int not null,
    foreign key(tag_id) references qrm_tags(id),-- on delete cascade,

    node_id int not null,
    foreign key(node_id) references qrm_nodes(id) on delete cascade,
   
    CONSTRAINT qrm_nodes_tags__tag__node UNIQUE(tag_id, node_id)
) WITHOUT OIDS;

create table qrm_vm_interfaces (
    id SERIAL NOT NULL CONSTRAINT qrm_vm_interfaces__id PRIMARY KEY,
    name varchar(50) not null,
    ip int not null,
    mac varchar(50) not null,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
     vm_id int not null,
    foreign key(vm_id) references qrm_virtual_machines(id) on delete cascade
) WITHOUT OIDS;
CREATE INDEX qrm_vm_interfaces__ip ON qrm_vm_interfaces( ip );
CREATE INDEX qrm_vm_interfaces__vm_id ON qrm_vm_interfaces( vm_id );

create table qrm_virtual_environments_tags (
    tag_id int not null,
    foreign key(tag_id) references qrm_tags(id),-- on delete cascade,
   
    virtual_environment_id int not null,
    foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade,

    CONSTRAINT qrm_virtual_environments_tags__PK_tag__virtual_environment PRIMARY KEY (tag_id, virtual_environment_id)
) WITHOUT OIDS;
CREATE INDEX qrm_virtual_environments_tags__tag ON qrm_virtual_environments_tags( tag_id );
CREATE INDEX qrm_virtual_environments_tags__virtual_environment ON qrm_virtual_environments_tags( virtual_environment_id );

create table qrm_filters (
    id SERIAL NOT NULL CONSTRAINT qrm_filters__id PRIMARY KEY,
    name varchar(50) not null,
    description text ,
    query text,
    editable smallint not null default 1, --BOOLEAN?
    type smallint not null,
    sort_by varchar(50),
    sort_order smallint,
    CONSTRAINT qrm_filters__name__type UNIQUE (name,type)
) WITHOUT OIDS;

create table qrm_node_interfaces (
    id SERIAL NOT NULL CONSTRAINT qrm_node_interfaces__id PRIMARY KEY,
    name varchar(50) not null,
    ip int not null,
    mac varchar(50) not null,
    creation_time TIMESTAMP WITH TIME ZONE default now(),
     node_id int not null,
    foreign key(node_id) references qrm_nodes(id) on delete cascade
) WITHOUT OIDS;
CREATE INDEX qrm_node_interfaces__node ON qrm_node_interfaces( node_id );
CREATE INDEX qrm_node_interfaces__ip ON qrm_node_interfaces( ip );

create table qrm_favorites (
    user_id int not null,
    foreign key(user_id) references qrm_users(id) on delete cascade,
    entity_id int not null,
    entity_type smallint not null,
    CONSTRAINT qrm_favorites__user__entity__entity_type UNIQUE (user_id, entity_id, entity_type )
) WITHOUT OIDS;
CREATE INDEX qrm_favorites__user_id ON qrm_favorites( user_id );
CREATE INDEX qrm_favorites__entity__entity_type ON qrm_favorites( entity_id, entity_type );

create table qrm_metrics_definitions (
    id SERIAL NOT NULL CONSTRAINT qrm_metrics_definitions__id PRIMARY KEY,
    name varchar(50) not null,
    entity_type smallint not null,
    comments text,
    is_reserved smallint not null default 0,

    CONSTRAINT qrm_metrics_definitions__name__entity_type UNIQUE (name,entity_type)
) WITHOUT OIDS;

create table qrm_ve_metrics_values (
    id SERIAL NOT NULL CONSTRAINT qrm_ve_metrics_values__id PRIMARY KEY,
    metric_id int null,
    value text,
    virtual_environment_id int null,
   
    CONSTRAINT qrm_ve_metrics_values__metric__virtual_environment UNIQUE (metric_id,virtual_environment_id),
   
    foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade,
    foreign key(metric_id) references qrm_metrics_definitions(id) on delete cascade
) WITHOUT OIDS;
CREATE INDEX qrm_ve_metrics_values__metric ON qrm_ve_metrics_values( metric_id );
CREATE INDEX qrm_ve_metrics_values__virtual_environment ON qrm_ve_metrics_values( virtual_environment_id );

create table qrm_node_metrics_values (
    id SERIAL NOT NULL CONSTRAINT qrm_node_metrics_values__id PRIMARY KEY,
    metric_id int null,
    value text,
    node_id int null,
   
    CONSTRAINT qrm_node_metrics_values__metric__node UNIQUE (metric_id,node_id),
    foreign key(node_id) references qrm_nodes(id) on delete cascade,
    foreign key(metric_id) references qrm_metrics_definitions(id) on delete cascade
) WITHOUT OIDS;
CREATE INDEX qrm_node_metrics_values__node ON qrm_node_metrics_values( node_id );
CREATE INDEX qrm_node_metrics_values__metric ON qrm_node_metrics_values( metric_id );

create table qrm_vm_metrics_values (
    id SERIAL NOT NULL CONSTRAINT qrm_vm_metrics_values__id PRIMARY KEY,
    metric_id int null,
    value text,
    vm_id int null,
   
    CONSTRAINT qrm_vm_metrics_values__metric__vm UNIQUE (metric_id,vm_id),
    foreign key(vm_id) references qrm_virtual_machines(id) on delete cascade,
    foreign key(metric_id) references qrm_metrics_definitions(id) on delete cascade
) WITHOUT OIDS;
CREATE INDEX qrm_vm_metrics_values__vm_id ON qrm_vm_metrics_values( vm_id );
CREATE INDEX qrm_vm_metrics_values__metric_id ON qrm_vm_metrics_values( metric_id );

-- currently, not working for innodb tables:
-- alter table qrm_virtual_environment auto_increment = 2;

-- virtual environments relations
alter table qrm_virtual_environment_statistics
add foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade;

--alter table qrm_pool_config
--add foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade;

--alter table qrm_pool_states
--add foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete cascade;

--alter table qrm_ve_filesystem_images
--add foreign key(virtual_environment_id) references qrm_virtual_environments(id) on delete set null;

-- nodes relations
alter table qrm_node_statistics
add foreign key(node_id) references qrm_nodes(id) on delete cascade;

-- vm relations
alter table qrm_vm_statistics
add foreign key(vm_id) references qrm_virtual_machines(id) on delete cascade;

STEP 2: Cut and paste the following code into a file and save it as “init.sql”.

--------------------------------------------------------------------------------
-- Filename: ./src/base/include/db/mysql/init.sql
--
-- The contents of this file are subject to the Qlusters Public License
-- Version 1.1 (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.openqrm.org/content/view/33/69/.
--
-- Software distributed under the License is distributed on an AS IS basis,
-- WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
-- the specific language governing rights and limitations under the License.
--
-- The Original Code is openQRM.
--
-- The Initial Developer of the Original Code is Qlusters Corporation. Portions
-- created by Qlusters Corporation are Copyright (C) 2003-2005. All Rights
-- Reserved.
--
-- Contributor(s):
--
-- Description:
--
-- Comments:
--------------------------------------------------------------------------------
\c :QRM_DATABASE_NAME

insert into qrm_roles(id, role_name, creation_time) values(1, 'Superuser', now());
insert into qrm_roles(id, role_name, creation_time) values(2, 'User', now());

insert into qrm_users(user_name, user_password, creation_time, status)
    values('qrm', 'qrm', now(), 1);
insert into qrm_users(user_name, user_password, creation_time, status)
    values('qrm-event', 'qrm-event', now(), 1);

insert into qrm_users_roles(role_id, user_id) values(1, 1);
insert into qrm_users_roles(role_id, user_id) values(2, 1);

------------------- PROPERTIES -------------------------------
insert into qrm_properties(creation_time, entity_type, category, property, value, description)
    values(now(), 'com.qlusters.qrm', 'configuration', 'com.qlusters.qrm.server.logic.config.ConfigurationHandler.refreshInterval', '5', 'Refresh interval for jsp pages');
insert into qrm_properties(creation_time, entity_type, category, property, value)
    values(now(), 'com.qlusters.qrm', 'configuration', 'com.qlusters.qrm.server.logic.recovery.Status.firstTime', 'true');
   
------------------------ TAGS ----------------------------------------------
insert into qrm_tags (id, creation_time, name, description) values(1, now(),'HBA','Predefined');
insert into qrm_tags (id, creation_time, name, description) values(2, now(),'Fast_Interconnect','Predefined');
insert into qrm_tags (id, creation_time, name, description) values(3, now(),'UPS','Predefined');
insert into qrm_tags (id, creation_time, name, description) values(4, now(),'Opteron','Predefined');

------------------------ POOL DATA ----------------------------------------------
--find another way to create infinity later
insert into qrm_pool_config (id, creation_time, virtual_environment_id,partition_tech, pool_min_count, pool_max_count,
    pool_percent,enabled) values(1, now(), 0, 'com.qlusters.qrm.server.logic.partitioning', 0, 2147483647, 0,1);

------------------------ Virtual Machine METRICS ----------------------------------------------
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(1,'load',3,'Load',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(2,'cpu_utilization',3,'CPU utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(3,'memory_utilization',3,'Memory utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(4,'swap_utilization',3,'Swap utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(5,'uptime',3,'Uptime',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(6,'cpu_number',3,'Number of CPUs',1);

------------------------ VE METRICS ----------------------------------------------
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(7,'load',1,'Load',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(8,'cpu_utilization',1,'CPU utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(9,'memory_utilization',1,'Memory utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(10,'swap_utilization',1,'Swap utilization',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(11,'available_cpu',1,'Available CPUs',1);
insert into qrm_metrics_definitions (id, name, entity_type, comments, is_reserved)
    values(12,'available_memory',1,'Available memory',1);

------------------------ STORAGE_TYPES -------------------------------------------
insert into qrm_storage_types (id, name, comments, interface)
        values(1, 'NFS', 'NFS/NAS storage server', 1);
insert into qrm_storage_types (id, name, comments, interface)
        values(2, 'local', 'Local Storage type (for easy migration)', 4);
insert into qrm_storage_types (id, name, comments, interface)
        values(3, 'iScsi', 'iScsi target storage server', 2);

------------------------ STORAGE_SERVERS -------------------------------------------
insert into qrm_storage_servers (id, name, storage_type_id, comments, ip_identifier)
        values(1, 'local', 2, 'Local Storage server (for easy migration)', 'local');

STEP 3: Run the files using the following code:

psql -v QRM_DATABASE_NAME=qrm -f filename.sql

###The original thread###

On Wed, Jan 04, 2006 at 03:24:41PM -0600, Matt Bradbury wrote:

What options are available for installing openQRM with a different db? I would prefer to use postGres (or maybe Oracle). Is there an easy way to install openQRM and point it to one of my existing db installations? Or will future installers allow for this type of installation?

-Matt

On Wed, Jan 04, 2006 at 03:35:07 PM -0600, Jim C. Nasby wrote:

Actually, I looked at exactly that just the other day. Here's the email that I sent to William:
-----
Here's the files. Run them using

psql -v QRM_DATABASE_NAME=qrm -f filename.sql

Though, typically one will specify what database to connect to on the command line, not in a .sql script. That will probably cause issues on pre-8.1 versions.

I didn't migrate drop.sql because PostgreSQL doesn't have a DROP IF EXISTS. Best bet is to psql -c 'DROP DATABASE blah' | grep -v 'error message'. I also didn't migrate user.sql because there's not really any permissions to setup if you're connecting as the owner of the tables anyway, which appears to be the case.

Also, keep in mind this is basically a proof of concept and something that should allow you to test against PostgreSQL. If you do this for real it will make more sense to have a rational means for one file to generate DDL for both databases. Or just ditch MySQL and gain handy little things like views, triggers, and stored procedures. :)

Finally, keep in mind that PostgreSQL's default config is very conservative. I suspect database performance isn't something critical here, but if things start acting slow it likely means you just need to do some tuning. You'll also want to make sure you're vacuuming things (most easily done by enabling autovacuum in 8.1).

--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"