source: trunk/scripts/mysql_create.sql @ 704

#
# Copyright (c) 2005-2008 David Carter <dcarter@arm4.org> and others.
# All rights reserved.   This program and the accompanying materials
# are made available under the terms of the Eclipse Public License v1.0
# which accompanies this distribution, and is available at
# http://www.eclipse.org/legal/epl-v10.html
# 
# Contributors: 
# David Carter - Initial implementation
#
#######################################################################
#
# If you are installing for the first time, you can simply
# direct this file to mysql as STDIN:
#
# $ mysql --user=root --password=<MySQL-root-password> < mysql_create.sql
#
# If you are upgrading from a previous version, you will need to comment
# out the the user creation steps below, as well as the schemas for any
# tables that already exist.

USE mysql;

REPLACE INTO user (host, user, password)
    VALUES (
        'localhost',
        'arm4',
  -- IMPORTANT: Change this password!
        PASSWORD('arm4')
    );

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
                 delete_priv, create_priv, drop_priv)
    VALUES (
        'localhost',
        'arm4',
        'arm4',
        'Y', 'Y', 'Y', 'Y',
        'Y', 'Y'
    );

FLUSH PRIVILEGES;

#
# Create the database
#

CREATE DATABASE IF NOT EXISTS arm4;

USE arm4;

CREATE TABLE IF NOT EXISTS application (
  appl_id BINARY(16) PRIMARY KEY UNIQUE NOT NULL,
  application_name TEXT NOT NULL,
  md5_digest BINARY(16) UNIQUE NOT NULL,
  charset INT NOT NULL,
  default_trace_flags SMALLINT DEFAULT 1, # arm4_shm_transaction_trace_all
  default_trace_parameter BIGINT DEFAULT 0,
  default_appdex_threshold BIGINT DEFAULT 4); # ARM4_APPDEX_DEFAULT_RESPONSE_TIME

GRANT SELECT, INSERT, UPDATE, DELETE ON application TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_identity (
  appl_id BINARY(16) PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  value TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_identity TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_context (
  appl_id BINARY(16) PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  name TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_context TO arm4@localhost;

CREATE TABLE IF NOT EXISTS trans (
  tran_id BINARY(16) PRIMARY KEY UNIQUE NOT NULL,
  transaction_name TEXT NOT NULL,
  md5_digest BINARY(16) UNIQUE NOT NULL,
  appl_id BINARY(16) NOT NULL,
  trace_flags SMALLINT DEFAULT 1,
  appdex_flags SMALLINT DEFAULT 2,
  trace_parameter BIGINT DEFAULT 0,
  appdex_threshold BIGINT DEFAULT 4);

GRANT SELECT, INSERT, UPDATE, DELETE ON trans TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_identity (
  tran_id BINARY(16) PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  value TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_identity TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_context (
  tran_id BINARY(16) PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  name TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_context TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_uri (
  tran_id BINARY(16) PRIMARY KEY NOT NULL,
  uri TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_uri TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_metric_binding (
  tran_id BINARY(16) PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  id BINARY(16) NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_metric_binding TO arm4@localhost;

CREATE TABLE IF NOT EXISTS metric (
  metric_id BINARY(16) PRIMARY KEY UNIQUE NOT NULL,
  metric_name TEXT NOT NULL,
  metric_units TEXT,
  md5_digest BINARY(16) UNIQUE NOT NULL,
  appl_id BINARY(16) NOT NULL,
  metric_format TINYINT NOT NULL,
  metric_usage SMALLINT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON metric TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_instance (
  app_handle BIGINT PRIMARY KEY NOT NULL,
  appl_id BINARY(16) NOT NULL,
  active TINYINT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_instance TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_group (
  app_handle BIGINT PRIMARY KEY NOT NULL,
  app_group TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_group TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_instance_id (
  app_handle BIGINT PRIMARY KEY NOT NULL,
  instance_name TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_instance_id TO arm4@localhost;

CREATE TABLE IF NOT EXISTS application_instance_context (
  app_handle BIGINT PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  property_name TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON application_instance_context TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_instance (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  appl_id BINARY(16) NOT NULL,
  tran_id BINARY(16) NOT NULL,
  app_handle BIGINT NOT NULL,
  arrival_time BIGINT NOT NULL,
  arrival_time_usec BIGINT NOT NULL,
  stop_time BIGINT NOT NULL,
  stop_time_usec BIGINT NOT NULL,
  response_time BIGINT NOT NULL,
  response_time_usec BIGINT NOT NULL,
  blocked_time BIGINT NOT NULL,
  blocked_time_usec BIGINT NOT NULL,
  tran_status INT NOT NULL,
  active INT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_instance TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_context_values (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  value TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_context_values TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_instance_uri (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  uri TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_instance_uri TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_metric_value (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  slot TINYINT NOT NULL,
  format TINYINT NOT NULL,
  metric_usage SMALLINT NOT NULL,
  valid TINYINT NOT NULL,
  metric_value BLOB NOT NULL,
  string_value TEXT);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_metric_value TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_user (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  valid TINYINT NOT NULL,
  id BINARY(16) NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_user TO arm4@localhost;

CREATE TABLE IF NOT EXISTS transaction_diag (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  detail TEXT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON transaction_diag TO arm4@localhost;

CREATE TABLE IF NOT EXISTS correlator (
  tran_handle BIGINT PRIMARY KEY NOT NULL,
  parent_correlator BLOB,
  current_correlator BLOB);

GRANT SELECT, INSERT, UPDATE, DELETE ON correlator TO arm4@localhost;

CREATE TABLE IF NOT EXISTS sequence (
  sequence_name VARCHAR(64) PRIMARY KEY NOT NULL,
  sequence_value INT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON sequence TO arm4@localhost;

CREATE TABLE IF NOT EXISTS trace_stats (
  time_seconds BIGINT PRIMARY KEY NOT NULL,
  tran_id BINARY(16) NOT NULL,
  counter BIGINT NOT NULL);

GRANT SELECT, INSERT, UPDATE, DELETE ON trace_stats TO arm4@localhost;