#!/usr/bin/ksh

#
## -_- $Name: PerfGraph-2_4d $ % $Id: mk_perfdb,v 1.22.6.3.2.3 2001/01/28 20:16:17 sietse Exp $
#

# Default database name is perfdb - changeable by defining the variable PERFDB
PERFDB=${PERFDB:-perfdb}

# Default pgsql path
PGSQLDIR=${PGSQLDIR:-/usr/local/sysmansw/runtime/pgsql/bin}

# Default perl path
PERLDIR=${PERLDIR:-/usr/local/sysmansw/runtime/perl/bin}


##########################################################################
# Make version 1.0 of the database
##########################################################################

mk_v1_0 ()
{
$PGSQLDIR/psql -c "create database $LOGNAME" template1 > /dev/null 2>&1 #create user db
$PGSQLDIR/psql -q -c "create database $PERFDB" #create meta database

$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;

create table perfdb_ver(
	version		varchar
);

create table lic(
	lic		char(32)
);

create table supp_systypes (
	supptype	varchar
);

create table systems(
	hostname	varchar,
	systype		varchar
);

insert into perfdb_ver    values('1.0');
insert into supp_systypes values('solaris');
insert into supp_systypes values('linux2_0_3x');

create table tables (
	systype		varchar,
	tablename	varchar,
        multiple	char,		-- multiple data elements per timestamp?
	datatype	char,		-- differential or cumulative?
        gatherfrom	varchar,	-- how to get data
        description	varchar		-- long name of this table
);

create table fields (
        fieldseq	int,		-- sequence nr (column in table)
	fieldtable	varchar,
	fieldname	varchar,
	fieldtype	char,		-- c = char, i = int, f = float
	fieldshdesc	varchar,
	fieldlongdesc	varchar,
	groupnum	int		-- to which group belongs this field
);

-- Fill tables

begin transaction;

-- s = means "single entry"
-- m = means "multiple entries"
-- d = means values are "differential"
-- c = means values are "cumulative"

insert into tables values ('solaris', 'sysload',	's',	'd',	'uptime',	'CPU load');
insert into tables values ('solaris', 'netstat',	'm',	'c',	'netstat',	'Network load');
insert into tables values ('solaris', 'sar_cpu',	's',	'd',	'sar',		'CPU states');
insert into tables values ('solaris', 'sar_disk',	'm',	'd',	'sar',		'Disks');
insert into tables values ('solaris', 'sar_q',		's',	'd',	'sar',		'Queues');
insert into tables values ('solaris', 'sar_rw',		's',	'd',	'sar',		'Read/write');
insert into tables values ('solaris', 'sar_swp',	's',	'd',	'sar',		'Swap info');
insert into tables values ('solaris', 'sar_sys',	's',	'd',	'sar',		'Syscalls');
insert into tables values ('solaris', 'sar_ino',	's',	'd',	'sar',		'Inodes');
insert into tables values ('solaris', 'sar_io',		's',	'd',	'sar',		'IO info');
insert into tables values ('solaris', 'sar_size',	's',	'd',	'sar',		'Table sizes');
insert into tables values ('solaris', 'sar_ipc',	's',	'd',	'sar',		'IPC');
insert into tables values ('solaris', 'sar_pgin',	's',	'd',	'sar',		'Page in');
insert into tables values ('solaris', 'sar_pgout',	's',	'd',	'sar',		'Page out');
insert into tables values ('solaris', 'sar_fmem',	's',	'd',	'sar',		'Free memory');
insert into tables values ('solaris', 'sar_mem',	's',	'd',	'sar',		'Memory info');

insert into tables values ('linux2_0', 'sysload',	's',	'd',	'uptime',	'CPU load');
insert into tables values ('linux2_0', 'proc_cpu',	's',	'c',	'proc',		'CPU states');
insert into tables values ('linux2_0', 'proc_net',	'm',	'c',	'proc',		'Network load');
insert into tables values ('linux2_0', 'proc_disk',	'm',	'c',	'proc',		'Disk io');
insert into tables values ('linux2_0', 'proc_intr',	's',	'c',	'proc',		'Interrupts');

-- Fill fields

insert into fields values (1, 'sar_cpu',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_cpu',	'usr',		'i', '%usr', '% user time', 1);
insert into fields values (3, 'sar_cpu',	'sys',		'i', '%sys', '% system time', 1);
insert into fields values (4, 'sar_cpu',	'wio',		'i', '%wio', '% io wait time', 1);
insert into fields values (5, 'sar_cpu',	'idle',		'i', '%idle', '% idle time', 2);

insert into fields values (1, 'sar_disk',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_disk',	'disk',		'c', 'disk', 'Disk', 1);
insert into fields values (3, 'sar_disk',	'busy',		'i', '%busy', '% busy', 1);
insert into fields values (4, 'sar_disk',	'avq',		'f', 'av. q.', 'average queue', 2);
insert into fields values (5, 'sar_disk',	'rwps',		'i', 'r+w/s', 'r+w / sec.', 3);
insert into fields values (6, 'sar_disk',	'blksps',	'i', 'blks/s', 'blocks / sec.', 3);
insert into fields values (7, 'sar_disk',	'avwait',	'f', 'av. wait', 'average wait(ms)', 4);
insert into fields values (8, 'sar_disk',	'avserv',	'f', 'av. serv', 'average service(ms)', 4);

insert into fields values (1, 'sar_q',		'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_q',		'runqsz',	'f', 'runq sz', 'run queue size', 1);
insert into fields values (3, 'sar_q',		'runocc',	'i', '%run occ', '%run occ', 2);
insert into fields values (4, 'sar_q',		'swpqsz',	'f', 'swpq sz', 'swap queue size', 1);
insert into fields values (5, 'sar_q',		'swpocc',	'i', '%swp occ', '%swap occ', 2);

insert into fields values (1, 'sar_rw',		'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_rw',		'breadps',	'i', 'bread/s', 'block read / sec.', 1);
insert into fields values (3, 'sar_rw',		'lreadps',	'i', 'lread/s', 'logical read / sec.', 1);
insert into fields values (4, 'sar_rw',		'rcache',	'i', '%rcache', '%read cache / sec.', 2);
insert into fields values (5, 'sar_rw',		'bwritps',	'i', 'bwrite/s', 'block write / sec.', 1);
insert into fields values (6, 'sar_rw',		'lwritps',	'i', 'lwrite/s', 'logical write / sec.', 1);
insert into fields values (7, 'sar_rw',		'wcache',	'i', '%wcache', '%write cache / sec.', 2);
insert into fields values (8, 'sar_rw',		'preadps',	'i', 'pread/s', 'physical read / sec.', 3);
insert into fields values (9, 'sar_rw',		'pwritps',	'i', 'pwrite/s', 'physical write / sec.', 3);

insert into fields values (1, 'sar_swp',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_swp',	'swpinps',	'f', 'swpin/s', 'swap in / sec.', 1);
insert into fields values (3, 'sar_swp',	'bswinps',	'f', 'bswin/s', 'bswap in / sec.', 1);
insert into fields values (4, 'sar_swp',	'swpotps',	'f', 'swpout/s', 'swap out / sec.', 1);
insert into fields values (5, 'sar_swp',	'bswotps',	'f', 'bswout/s', 'bswap out / sec.', 1);
insert into fields values (6, 'sar_swp',	'pswchps',	'i', 'pswch/s', 'pswap ch / sec.', 2);

insert into fields values (1, 'sar_sys',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_sys',	'scallps',	'i', 'scall/s', 'syscall / sec.', 1);
insert into fields values (3, 'sar_sys',	'sreadps',	'i', 'sread/s', 'sysread / sec.', 2);
insert into fields values (4, 'sar_sys',	'swritps',	'i', 'swrit/s', 'syswrite / sec.', 2);
insert into fields values (5, 'sar_sys',	'forkps',	'f', 'fork/s', 'fork / sec.', 3);
insert into fields values (6, 'sar_sys',	'execps',	'f', 'exec/s', 'exec / sec.', 3);
insert into fields values (7, 'sar_sys',	'rcharps',	'i', 'rchar/s', 'rchar / sec.', 4);
insert into fields values (8, 'sar_sys',	'wcharps',	'i', 'wchar/s', 'wchar / sec.', 4);

insert into fields values (1, 'sar_ino',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_ino',	'igetps',	'i', 'iget/s', 'inode get / sec.', 1);
insert into fields values (3, 'sar_ino',	'nameips',	'i', 'namei/s', 'name inode / sec.', 1);
insert into fields values (4, 'sar_ino',	'dirbkps',	'i', 'dirbk/s', 'dir block / sec.', 1);

insert into fields values (1, 'sar_io',		'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_io',		'rawchps',	'i', 'raw ch/s', 'raw char / sec.', 1);
insert into fields values (3, 'sar_io',		'canchps',	'i', 'can ch/s', 'cancel char / sec.', 1);
insert into fields values (4, 'sar_io',		'outchps',	'i', 'out ch/s', 'out char / sec.', 1);
insert into fields values (5, 'sar_io',		'rcvinps',	'i', 'recv ch/s', 'receive int / sec.', 2);
insert into fields values (6, 'sar_io',		'xmtinps',	'i', 'xmit ch/s', ' xmit int char / sec.', 2);
insert into fields values (7, 'sar_io',		'mdminps',	'i', 'mdm ch/s', ' mdm int char / sec.', 2);

insert into fields values (1, 'sar_size',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_size',	'proccur',	'i', 'proccur', 'proc table current', 1);
insert into fields values (3, 'sar_size',	'procmax',	'i', 'procmax', 'proc table maximal', 2);
insert into fields values (4, 'sar_size',	'procov',	'i', 'procov', 'proc table overflow', 1);
insert into fields values (5, 'sar_size',	'inodcur',	'i', 'inodcur', 'inode table current', 3);
insert into fields values (6, 'sar_size',	'inodmax',	'i', 'inodmax', 'inode table maximal', 3);
insert into fields values (7, 'sar_size',	'inodov',	'i', 'inodov', 'inode table overflow', 3);
insert into fields values (8, 'sar_size',	'filecur',	'i', 'filecur', 'file table current', 4);
insert into fields values (9, 'sar_size',	'filemax',	'i', 'filemax', 'file table maximal', 4);
insert into fields values (10, 'sar_size',	'fileov',	'i', 'fileov', 'file table overflow', 4);
insert into fields values (11, 'sar_size',	'lockcur',	'i', 'lockcur', 'lock table current', 5);
insert into fields values (12, 'sar_size',	'lockmax',	'i', 'lockmax', 'lock table maximal', 5);

insert into fields values (1, 'sar_ipc',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_ipc',	'msgps',	'f', 'msg/s', 'messages / sec', 1);
insert into fields values (3, 'sar_ipc',	'semaps',	'f', 'sema/s', 'semaphores / sec', 1);

insert into fields values (1, 'sar_pgin',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_pgin',	'atchps',	'f', 'atch/s', 'atch / sec.', 1);
insert into fields values (3, 'sar_pgin',	'pginps',	'f', 'pgin/s', 'page in / sec.', 1);
insert into fields values (4, 'sar_pgin',	'ppginps',	'f', 'ppgin/s', 'ppage in / sec.', 1);
insert into fields values (5, 'sar_pgin',	'pfltps',	'f', 'pflt/s', 'page fault / sec.', 2);
insert into fields values (6, 'sar_pgin',	'vfltps',	'f', 'vflt/s', 'virtual fault / sec.', 2);
insert into fields values (7, 'sar_pgin',	'slockps',	'f', 'slock/s', 's-lock / sec.', 3);

insert into fields values (1, 'sar_pgout',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_pgout',	'pgoutps',	'f', 'pgout/s', 'page out / sec.', 1);
insert into fields values (3, 'sar_pgout',	'ppgoutps',	'f', 'ppgout/s', 'ppage out / sec.', 1);
insert into fields values (4, 'sar_pgout',	'pgfreeps',	'f', 'pgfree/s', 'page free / sec.', 2);
insert into fields values (5, 'sar_pgout',	'pgscanps',	'f', 'pgscan/s', 'page scan / sec.', 2);
insert into fields values (6, 'sar_pgout',	'ufs_ipf',	'f', '%ufs ipf', '%ufs ipf / sec.', 3);

insert into fields values (1, 'sar_fmem',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_fmem',	'freemem',	'i', 'free mem', 'free memory', 1);
insert into fields values (3, 'sar_fmem',	'freeswap',	'i', 'free swap', 'free swap', 2);

insert into fields values (1, 'sar_mem',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sar_mem',	'sml_mem',	'i', 'sml mem', 'sml memory', 1);
insert into fields values (3, 'sar_mem',	'alloc',	'i', 'alloc', 'allocate', 1);
insert into fields values (4, 'sar_mem',	'fail',		'i', 'fail', 'fail', 4);
insert into fields values (5, 'sar_mem',	'lg_mem',	'i', 'lg mem', 'lg memory', 2);
insert into fields values (6, 'sar_mem',	'alloc2',	'i', 'alloc2', 'allocate 2', 2);
insert into fields values (7, 'sar_mem',	'fail2',	'i', 'fail2', 'fail 2', 4);
insert into fields values (8, 'sar_mem',	'ovsz_alloc',	'i', 'ovsz alloc', 'ovsz allocate', 3);
insert into fields values (9, 'sar_mem',	'fail3',	'i', 'fail3', 'fail 3', 4);

insert into fields values (1, 'sysload',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'sysload',	'users',	'i', 'users', 'logged in users', 2);
insert into fields values (3, 'sysload',	'load1',	'f', 'load 1 min', 'load 1', 1);
insert into fields values (4, 'sysload',	'load2',	'f', 'load 5 min', 'load 2', 1);
insert into fields values (5, 'sysload',	'load3',	'f', 'load 15 min', 'load 3', 1);

insert into fields values (1, 'netstat',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'netstat',	'iface',	'c', 'interface', 'interface', 0);
insert into fields values (3, 'netstat',	'ipkts',	'i', 'i-pkts', 'input packets', 1);
insert into fields values (4, 'netstat',	'ierrs',	'i', 'i-errs', 'input errors', 2);
insert into fields values (5, 'netstat',	'opkts',	'i', 'o-pkts', 'output packets', 1);
insert into fields values (6, 'netstat',	'oerrs',	'i', 'o-errs', 'output errors', 2);
insert into fields values (7, 'netstat',	'coll',		'i', 'collisions', 'collisions', 2);
insert into fields values (8, 'netstat',	'queue',	'i', 'queue', 'queue', 0);

insert into fields values (1, 'proc_cpu',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'proc_cpu',	'sys',		'i', '%sys', '% system time', 1);
insert into fields values (3, 'proc_cpu',	'nice',		'i', '%nice', '% nice', 1);
insert into fields values (4, 'proc_cpu',	'usr',		'i', '%usr', '% user time', 1);
insert into fields values (5, 'proc_cpu',	'idle',		'i', '%idle', '% idle time', 2);

insert into fields values (1, 'proc_net',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'proc_net',	'iface',	'c', 'interface', 'interface', 0);
insert into fields values (3, 'proc_net',	'rxok',		'i', 'RX-OK', 'Rx Ok', 1);
insert into fields values (4, 'proc_net',	'rxerr',	'i', 'RX-ERR', 'Rx error', 2);
insert into fields values (5, 'proc_net',	'rxdrp',	'i', 'RX-DRP', 'Rx drop', 2);
insert into fields values (6, 'proc_net',	'rxfifo',	'i', 'RX-FIFO', 'Rx fifo', 2);
insert into fields values (7, 'proc_net',	'rxframe',	'i', 'RX-FRAME', 'Rx frame', 2);
insert into fields values (8, 'proc_net',	'txok',		'i', 'TX-OK', 'Tx Ok', 1);
insert into fields values (9, 'proc_net',	'txerr',	'i', 'TX-ERR', 'Tx error', 2);
insert into fields values (10, 'proc_net',	'txdrp',	'i', 'TX-DRP', 'Tx drop', 2);
insert into fields values (11, 'proc_net',	'txfifo',	'i', 'TX-FIFO', 'Tx fifo', 2);
insert into fields values (12, 'proc_net',	'txframe',	'i', 'TX-FRAME', 'Tx frame', 2);
insert into fields values (13, 'proc_net',	'carrier',	'i', 'CARRIER', 'Carrier', 0);

insert into fields values (1, 'proc_disk',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'proc_disk',	'disk',		'c', 'disk dev', 'disk device', 1);
insert into fields values (3, 'proc_disk',	'disk_disk',	'i', 'disk', 'disk', 1);
insert into fields values (4, 'proc_disk',	'disk_rio',	'i', 'disk_rio', 'disk read io', 2);
insert into fields values (5, 'proc_disk',	'disk_wio',	'i', 'disk_wio', 'disk write io', 2);
insert into fields values (6, 'proc_disk',	'disk_rblk',	'i', 'disk_rblk', 'disk read bock', 3);
insert into fields values (7, 'proc_disk',	'disk_wblk',	'i', 'disk_wblk', 'disk write bock', 3);

insert into fields values (1, 'proc_intr',	'tstamp',	'i', 'timestamp', 'timestamp', 0);
insert into fields values (2, 'proc_intr',	'int_tot',	'i', 'int total', 'interrupts total', 0);
insert into fields values (3, 'proc_intr',	'int_00',	'i', 'int 00', 'interrupt 00', 1);
insert into fields values (4, 'proc_intr',	'int_01',	'i', 'int 01', 'interrupt 01', 1);
insert into fields values (5, 'proc_intr',	'int_02',	'i', 'int 02', 'interrupt 02', 1);
insert into fields values (6, 'proc_intr',	'int_03',	'i', 'int 03', 'interrupt 03', 1);
insert into fields values (7, 'proc_intr',	'int_04',	'i', 'int 04', 'interrupt 04', 2);
insert into fields values (8, 'proc_intr',	'int_05',	'i', 'int 05', 'interrupt 05', 2);
insert into fields values (9, 'proc_intr',	'int_06',	'i', 'int 06', 'interrupt 06', 2);
insert into fields values (10, 'proc_intr',	'int_07',	'i', 'int 07', 'interrupt 07', 2);
insert into fields values (11, 'proc_intr',	'int_08',	'i', 'int 08', 'interrupt 08', 3);
insert into fields values (12, 'proc_intr',	'int_09',	'i', 'int 09', 'interrupt 09', 3);
insert into fields values (13, 'proc_intr',	'int_10',	'i', 'int 10', 'interrupt 10', 3);
insert into fields values (14, 'proc_intr',	'int_11',	'i', 'int 11', 'interrupt 11', 3);
insert into fields values (15, 'proc_intr',	'int_12',	'i', 'int 12', 'interrupt 12', 4);
insert into fields values (16, 'proc_intr',	'int_13',	'i', 'int 13', 'interrupt 13', 4);
insert into fields values (17, 'proc_intr',	'int_14',	'i', 'int 14', 'interrupt 14', 4);
insert into fields values (18, 'proc_intr',	'int_15',	'i', 'int 15', 'interrupt 15', 4);

end transaction;

__EOF
}

##########################################################################
# Make version 2.0 of the database
##########################################################################
mk_v2_0 ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;

begin transaction;

-- Tables

insert into tables values('solaris',  'usertbl',        's',    'd',    'user',     'User defined');

insert into tables values('linux2_0', 'usertbl',        's',    'd',    'user',     'User defined');

insert into tables values ('aix',     'sysload',        's',    'd',    'uptime',   'CPU load');
insert into tables values ('aix',     'netstataix',     'm',    'c',    'netstat',  'Network load');
insert into tables values ('aix',     'iostat',         'm',    'd',    'iostat',   'Disk stats');
insert into tables values ('aix',     'vmstataix',      's',    'd',    'vmstat',   'Memory info');
insert into tables values ('aix',     'sar_inoaix',     's',    'd',    'sar',      'Inode stats');
insert into tables values ('aix',     'sar_rw',         's',    'd',    'sar',      'Read/write');
insert into tables values ('aix',     'sar_sys',        's',    'd',    'sar',      'Sys calls');
insert into tables values ('aix',     'sar_kproc',      's',    'd',    'sar',      'Kproc stats');
insert into tables values ('aix',     'sar_ipc',        's',    'd',    'sar',      'IPC stats');
insert into tables values ('aix',     'sar_q',          's',    'd',    'sar',      'Queues');
insert into tables values ('aix',     'sar_slots',      's',    'd',    'sar',      'Slots');
insert into tables values ('aix',     'sar_cpu',        's',    'd',    'sar',      'CPU states');
insert into tables values ('aix',     'sar_sizeaix',    's',    'd',    'sar',      'Table sizes');
insert into tables values ('aix',     'sar_csw',        's',    'd',    'sar',      'Context switches');
insert into tables values ('aix',     'sar_io',         's',    'd',    'sar',      'IO info');
insert into tables values ('aix',     'usertbl',        's',    'd',    'user',     'User defined');

insert into tables values ('hpux',    'sysload',	's',	'd',	'uptime',   'CPU load');
insert into tables values ('hpux',    'netstatux',	'm',	'c',	'netstat',  'Network load');
insert into tables values ('hpux',    'vmstatux',	's',	'd',	'vmstat',   'Memory info');
insert into tables values ('hpux',    'sar_cpu',	's',	'd',	'sar',	    'CPU states');
insert into tables values ('hpux',    'sar_diskux',	'm',	'd',	'sar',	    'Disks');
insert into tables values ('hpux',    'sar_q',		's',	'd',	'sar',	    'Queues');
insert into tables values ('hpux',    'sar_rw',		's',	'd',	'sar',	    'Read/write');
insert into tables values ('hpux',    'sar_swp',	's',	'd',	'sar',	    'Swap info');
insert into tables values ('hpux',    'sar_sys',	's',	'd',	'sar',	    'Syscalls');
insert into tables values ('hpux',    'sar_ino',	's',	'd',	'sar',	    'Inodes');
insert into tables values ('hpux',    'sar_io',		's',	'd',	'sar',	    'IO info');
insert into tables values ('hpux',    'sar_sizeux',	's',	'd',	'sar',	    'Table sizes');
insert into tables values ('hpux',    'sar_ipc',	's',	'd',	'sar',	    'IPC');
insert into tables values ('hpux',    'usertbl',        's',    'd',    'user',     'User defined');

insert into tables values ('linux2_2', 'sysload',	's',	'd',	'uptime',   'CPU load');
insert into tables values ('linux2_2', 'proc_cpu',	's',	'c',	'proc',     'CPU states');
insert into tables values ('linux2_2', 'proc_net2',	'm',	'c',	'proc',     'Network load');
insert into tables values ('linux2_2', 'proc_disk',	'm',	'c',	'proc',     'Disk io');
insert into tables values ('linux2_2', 'proc_intr',	's',	'c',	'proc',     'Interrupts');
insert into tables values ('linux2_2', 'proc_meminfo',	's',	'd',	'proc',     'Memory info');
insert into tables values ('linux2_2', 'proc_size',	's',	'd',	'proc',     'Table sizes');
insert into tables values ('linux2_2', 'usertbl',       's',    'd',    'user',     'User defined');

-- Fill fields

-- For all

insert into fields values(1, 'usertbl',         'tstamp',       'i', 'tstamp',        '', 0);
insert into fields values(2, 'usertbl',         'user1',        'i', 'user1',         '', 1);
insert into fields values(3, 'usertbl',         'user2',        'i', 'user2',         '', 2);
insert into fields values(4, 'usertbl',         'user3',        'i', 'user3',         '', 3);
insert into fields values(5, 'usertbl',         'user4',        'i', 'user4',         '', 4);

-- For AIX

insert into fields values (1, 'netstataix',     'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values (2, 'netstataix',     'iface',        'c', 'interface',     '', 0);
insert into fields values (3, 'netstataix',     'ipkts',        'i', 'i-pkts',        '', 1);
insert into fields values (4, 'netstataix',     'ierrs',        'i', 'i-errs',        '', 2);
insert into fields values (5, 'netstataix',     'opkts',        'i', 'o-pkts',        '', 1);
insert into fields values (6, 'netstataix',     'oerrs',        'i', 'o-errs',        '', 2);
insert into fields values (7, 'netstataix',     'coll',         'i', 'collisions',    '', 2);

insert into fields values(1, 'iostat',          'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'iostat',          'disk',         'c', 'disk',          '', 0);
insert into fields values(3, 'iostat',          'tmact',        'f', '% tm_act',      '', 1);
insert into fields values(4, 'iostat',          'kbps',         'f', 'Kb p/s',        '', 2);
insert into fields values(5, 'iostat',          'tps',          'f', 't p/s',         '', 3);
insert into fields values(6, 'iostat',          'kbread',       'i', 'Kb read',       '', 4);
insert into fields values(7, 'iostat',          'kbwrtn',       'i', 'Kb written',    '', 4);

insert into fields values(1, 'sar_inoaix',      'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'sar_inoaix',      'igetps',       'i', 'iget p/s',      '', 1);
insert into fields values(3, 'sar_inoaix',      'lookuppnps',   'i', 'lookup pn/s',   '', 2);
insert into fields values(4, 'sar_inoaix',      'dirblkps',     'i', 'dirblk p/s',    '', 3);

insert into fields values(1, 'sar_kproc',       'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'sar_kproc',       'kschedps',     'i', 'ksched p/s',    '', 1);
insert into fields values(3, 'sar_kproc',       'kprocov',      'i', 'kprocov',       '', 2);
insert into fields values(4, 'sar_kproc',       'kexitps',      'i', 'kexit p/s',     '', 1);

insert into fields values(1, 'sar_slots',       'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'sar_slots',       'slots',        'i', 'slots',         '', 1);
insert into fields values(3, 'sar_slots',       'cycleps',      'f', 'cycles p/s',    '', 2);
insert into fields values(4, 'sar_slots',       'faultps',      'f', 'faults p/s',    '', 2);
insert into fields values(5, 'sar_slots',       'odiops',       'f', 'odio p/s',      '', 2);

insert into fields values(1, 'sar_sizeaix',     'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'sar_sizeaix',     'proccur',      'i', 'proc curr',     '', 1);
insert into fields values(3, 'sar_sizeaix',     'procmax',      'i', 'proc max',      '', 2);
insert into fields values(4, 'sar_sizeaix',     'inodcur',      'i', 'inod curr',     '', 3);
insert into fields values(5, 'sar_sizeaix',     'inodmax',      'i', 'inod max',      '', 3);
insert into fields values(6, 'sar_sizeaix',     'filecur',      'i', 'file curr',     '', 4);
insert into fields values(7, 'sar_sizeaix',     'filemax',      'i', 'file max',      '', 4);
insert into fields values(8, 'sar_sizeaix',     'thrdcur',      'i', 'thread curr',   '', 5);
insert into fields values(9, 'sar_sizeaix',     'thrdmax',      'i', 'thread max',    '', 6);

insert into fields values(1, 'sar_csw',         'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'sar_csw',         'cswchps',      'i', 'cntswch p/s',   '', 1);

insert into fields values (1, 'vmstataix',      'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values (2, 'vmstataix',      'avm',          'i', 'active vm',     '', 1);
insert into fields values (3, 'vmstataix',      'free',         'i', 'free list',     '', 1);
insert into fields values (4, 'vmstataix',      're',           'i', 'pg reclaim',    '', 1);
insert into fields values (5, 'vmstataix',      'pi',           'i', 'paged in',      '', 2);
insert into fields values (6, 'vmstataix',      'po',           'i', 'paged out',     '', 2);
insert into fields values (7, 'vmstataix',      'fr',           'i', 'freed / sec',   '', 2);
insert into fields values (8, 'vmstataix',      'sr',           'i', 'scan rate',     '', 3);
insert into fields values (9, 'vmstataix',      'cy',           'i', 'cy',            '', 3);

-- For HP-UX

insert into fields values (1, 'netstatux',      'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values (2, 'netstatux',      'iface',        'c', 'interface',     '', 0);
insert into fields values (3, 'netstatux',      'ipkts',        'i', 'i-pkts',        '', 1);
insert into fields values (4, 'netstatux',      'ierrs',        'i', 'i-errs',        '', 2);
insert into fields values (5, 'netstatux',      'opkts',        'i', 'o-pkts',        '', 1);
insert into fields values (6, 'netstatux',      'oerrs',        'i', 'o-errs',        '', 2);
insert into fields values (7, 'netstatux',      'coll',         'i', 'collisions',    '', 2);

insert into fields values ( 1, 'vmstatux',      'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values ( 2, 'vmstatux',      'avm',          'i', 'active vm',     '', 1);
insert into fields values ( 3, 'vmstatux',      'free',         'i', 'free list',     '', 1);
insert into fields values ( 4, 'vmstatux',      're',           'i', 'pg reclaim',    '', 1);
insert into fields values ( 5, 'vmstatux',      'at',           'i', 'faults',        '', 2);
insert into fields values ( 6, 'vmstatux',      'pi',           'i', 'paged in',      '', 2);
insert into fields values ( 7, 'vmstatux',      'po',           'i', 'paged out',     '', 2);
insert into fields values ( 8, 'vmstatux',      'fr',           'i', 'freed / sec',   '', 2);
insert into fields values ( 9, 'vmstatux',      'de',           'i', 'mem shortfall', '', 3);
insert into fields values (10, 'vmstatux',      'sr',           'i', 'scan rate',     '', 4);

insert into fields values (1, 'sar_diskux',	'tstamp',	'i', 'timestamp',     '', 0);
insert into fields values (2, 'sar_diskux',	'disk',		'c', 'disk',          '', 1);
insert into fields values (3, 'sar_diskux',	'busy',		'f', '%busy',         '', 1);
insert into fields values (4, 'sar_diskux',	'avq',		'f', 'av. q.',        '', 2);
insert into fields values (5, 'sar_diskux',	'rwps',		'i', 'r+w/s',         '', 3);
insert into fields values (6, 'sar_diskux',	'blksps',	'i', 'blks/s',        '', 3);
insert into fields values (7, 'sar_diskux',	'avwait',	'f', 'av. wait',      '', 4);
insert into fields values (8, 'sar_diskux',	'avserv',	'f', 'av. serv',      '', 4);

insert into fields values (1, 'sar_sizeux',	'tstamp',	'i', 'timestamp',     '', 0);
insert into fields values (2, 'sar_sizeux',	'textcur',	'i', 'textcur',       '', 3);
insert into fields values (3, 'sar_sizeux',	'textmax',	'i', 'textmax',       '', 3);
insert into fields values (4, 'sar_sizeux',	'textov',	'i', 'textov',        '', 3);
insert into fields values (5, 'sar_sizeux',	'proccur',	'i', 'proccur',       '', 1);
insert into fields values (6, 'sar_sizeux',	'procmax',	'i', 'procmax',       '', 2);
insert into fields values (7, 'sar_sizeux',	'procov',	'i', 'procov',        '', 1);
insert into fields values (8, 'sar_sizeux',	'inodcur',	'i', 'inodcur',       '', 4);
insert into fields values (9, 'sar_sizeux',	'inodmax',	'i', 'inodmax',       '', 4);
insert into fields values (10, 'sar_sizeux',	'inodov',	'i', 'inodov',        '', 4);
insert into fields values (11, 'sar_sizeux',	'filecur',	'i', 'filecur',       '', 5);
insert into fields values (12, 'sar_sizeux',	'filemax',	'i', 'filemax',       '', 5);
insert into fields values (13, 'sar_sizeux',	'fileov',	'i', 'fileov',        '', 5);

-- For Linux 2_2

insert into fields values (1, 'proc_meminfo',	'tstamp',	'i', 'timestamp',     '', 0);
insert into fields values (2, 'proc_meminfo',	'memtotal',	'i', 'mem total',     '', 1);
insert into fields values (3, 'proc_meminfo',	'memfree',	'i', 'mem free',      '', 1);
insert into fields values (4, 'proc_meminfo',	'memshared',	'i', 'mem shared',    '', 1);
insert into fields values (5, 'proc_meminfo',	'buffers',	'i', 'buffers',       '', 2);
insert into fields values (6, 'proc_meminfo',	'cached',	'i', 'cached',        '', 2);
insert into fields values (7, 'proc_meminfo',	'swaptotal',	'i', 'swap total',    '', 3);
insert into fields values (8, 'proc_meminfo',	'swapfree',	'i', 'swap free',     '', 3);

insert into fields values (1, 'proc_net2',	'tstamp',	'i', 'timestamp',     '', 0);
insert into fields values (2, 'proc_net2',	'iface',	'c', 'interface',     '', 0);
insert into fields values (3, 'proc_net2',	'rxbytes',	'i', 'RX-BYTES',      '', 1);
insert into fields values (4, 'proc_net2',	'rxpkts',	'i', 'RX-PACKETS',    '', 1);
insert into fields values (5, 'proc_net2',	'rxerr',	'i', 'RX-ERR',        '', 2);
insert into fields values (6, 'proc_net2',	'rxdrp',	'i', 'RX-DRP',        '', 2);
insert into fields values (7, 'proc_net2',	'rxfifo',	'i', 'RX-FIFO',       '', 2);
insert into fields values (8, 'proc_net2',	'rxframe',	'i', 'RX-FRAME',      '', 2);
insert into fields values (9, 'proc_net2',	'rxcompr',	'i', 'RX-COMPR',      '', 2);
insert into fields values (10, 'proc_net2',	'rxmult',	'i', 'RX-MULTI',      '', 2);
insert into fields values (11, 'proc_net2',	'txbytes',	'i', 'TX-BYTES',      '', 1);
insert into fields values (12, 'proc_net2',	'txpkts',	'i', 'TX-PACKETS',    '', 1);
insert into fields values (13, 'proc_net2',	'txerr',	'i', 'TX-ERR',        '', 2);
insert into fields values (14, 'proc_net2',	'txdrp',	'i', 'TX-DRP',        '', 2);
insert into fields values (15, 'proc_net2',	'txfifo',	'i', 'TX-FIFO',       '', 2);
insert into fields values (16, 'proc_net2',	'txcolls',	'i', 'TX-COLLS',      '', 2);
insert into fields values (17, 'proc_net2',	'txcarrier',	'i', 'TX-CARRIER',    '', 2);
insert into fields values (18, 'proc_net2',	'txcompr',	'i', 'TX-COMPR',      '', 0);

insert into fields values(1, 'proc_size',       'tstamp',       'i', 'timestamp',     '', 0);
insert into fields values(2, 'proc_size',       'filemax',      'i', 'file max',      '', 1);
insert into fields values(3, 'proc_size',       'filecur1',     'i', 'file curr 1',   '', 1);
insert into fields values(4, 'proc_size',       'filecur2',     'i', 'file curr 2',   '', 1);
insert into fields values(5, 'proc_size',       'filecur3',     'i', 'file curr 3',   '', 1);
insert into fields values(6, 'proc_size',       'inodmax',      'i', 'inod max',      '', 2);
insert into fields values(7, 'proc_size',       'inodcur1',     'i', 'inod curr 1',   '', 2);
insert into fields values(8, 'proc_size',       'inodcur2',     'i', 'file curr 2',   '', 2);

delete from supp_systypes;
end transaction;

vacuum analyze;

begin transaction;

insert into supp_systypes values('solaris');
insert into supp_systypes values('hpux');
insert into supp_systypes values('aix');
insert into supp_systypes values('linux2_0');
insert into supp_systypes values('linux2_2');

update perfdb_ver set version = '2.0';
update tables set systype = 'linux2_0' where systype = 'linux2_0_3x';
update systems set systype = 'linux2_0' where systype = 'linux2_0_3x';
update supp_systypes set supptype = 'linux2_0' where supptype = 'linux2_0_3x';

alter table systems add (gathering char);
update systems set gathering='y';

-- work-around for Postgres bug:
select hostname, systype, gathering into systems2 from systems;
drop table systems;
alter table systems2 rename to systems;

end transaction;

__EOF

# Now upgrade existing databases

dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c 'select hostname from systems'`
for db in $dbs
do
$PGSQLDIR/psql -q -d $db > /dev/null << __EOF
create table usertbl   (tstamp int, user1 int, user2 int, user3 int, user4 int);
create table usertbl_d (tstamp int, user1 int, user2 int, user3 int, user4 int);
create table usertbl_w (tstamp int, user1 int, user2 int, user3 int, user4 int);
create table usertbl_m (tstamp int, user1 int, user2 int, user3 int, user4 int);
create table usertbl_y (tstamp int, user1 int, user2 int, user3 int, user4 int);
create index usertbl_idx   on usertbl(tstamp);
create index usertbl_d_idx on usertbl(tstamp);
create index usertbl_w_idx on usertbl(tstamp);
create index usertbl_m_idx on usertbl(tstamp);
create index usertbl_y_idx on usertbl(tstamp);

create table userfldnames(fieldname varchar, fieldshdesc varchar);
insert into userfldnames values('user1', 'user1');
insert into userfldnames values('user2', 'user2');
insert into userfldnames values('user3', 'user3');
insert into userfldnames values('user4', 'user4');

__EOF
done

}

##########################################################################
# Make version 2.1 of the database
##########################################################################
mk_v2_1 ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;

begin transaction;
update perfdb_ver set version = '2.1';
update systems set gathering='r' where gathering='y';
end transaction;

__EOF

}

##########################################################################
# Make version 2.2 of the database
##########################################################################
mk_v2_2 ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;

begin transaction;

update perfdb_ver set version = '2.2';
insert into supp_systypes values('NT4');

-- Tables

-- insert into tables values('NT4',     'usertbl',        's',    'd',    'NT4',   'User defined');
insert into tables values('NT4',     'NTsysload',      's',    'd',    'NT4',   'CPU load');
insert into tables values('NT4',     'NTcpustates',    's',    'd',    'NT4',   'CPU states');
insert into tables values('NT4',     'NTmemory',       's',    'd',    'NT4',   'Memory');
insert into tables values('NT4',     'NTsyscalls',     's',    'd',    'NT4',   'System calls');
insert into tables values('NT4',     'NTfileops',      's',    'd',    'NT4',   'File operations');
insert into tables values('NT4',     'NTprocs',        's',    'd',    'NT4',   'Processes');
insert into tables values('NT4',     'NTcache',        's',    'd',    'NT4',   'Cache');
insert into tables values('NT4',     'NTpaging',       's',    'd',    'NT4',   'Paging files');
insert into tables values('NT4',     'NTredirRW',      's',    'd',    'NT4',   'Redir r/w');
insert into tables values('NT4',     'NTredirSess',    's',    'c',    'NT4',   'Redir sessions');
insert into tables values('NT4',     'NTserverRW',     's',    'd',    'NT4',   'Server r/w');
insert into tables values('NT4',     'NTserverSess',   's',    'c',    'NT4',   'Server sessions');
insert into tables values('NT4',     'NTphysdisk',     'm',    'd',    'NT4',   'Disks physical');
insert into tables values('NT4',     'NTlogdisk',      'm',    'd',    'NT4',   'Disks logical');

-- Fields

insert into fields values( 1, 'NTsysload',     'tstamp',       'i', 'timestamp',                '', 0);
insert into fields values( 2, 'NTsysload',     'cpuload',      'f', '% CPU busy',               '', 1);
insert into fields values( 3, 'NTsysload',     'sessions',     'f', 'sessions',                 '', 2);

insert into fields values( 1, 'NTcpustates',   'tstamp',       'i', 'timestamp',                '', 0);
insert into fields values( 2, 'NTcpustates',   'user_time',    'f', '% user time',              '', 1);
insert into fields values( 3, 'NTcpustates',   'priv_time',    'f', '% priviliged time',        '', 1);
insert into fields values( 4, 'NTcpustates',   'DPC_time',     'f', '% DPC time',               '', 2);
insert into fields values( 5, 'NTcpustates',   'intr_time',    'f', '% interrupt time',         '', 2);

insert into fields values( 1, 'NTmemory',      'tstamp',       'i', 'timestamp',                '', 0);
insert into fields values( 2, 'NTmemory',      'avail',        'i', 'Available kbytes',         '', 1);
insert into fields values( 3, 'NTmemory',      'committed',    'i', 'Committed kbytes',         '', 1);
insert into fields values( 4, 'NTmemory',      'cache',        'i', 'Cache kbytes',             '', 1);
insert into fields values( 5, 'NTmemory',      'pg_input',     'i', 'Pages input/sec',          '', 2);
insert into fields values( 6, 'NTmemory',      'pg_output',    'i', 'Pages output/sec',         '', 2);
insert into fields values( 7, 'NTmemory',      'pool_pg',      'i', 'Pool paged kbytes',        '', 3);
insert into fields values( 8, 'NTmemory',      'pool_nonpg',   'i', 'Pool nonpaged kbytes',     '', 3);
insert into fields values( 9, 'NTmemory',      'free_tblent',  'i', 'Free pg tbl entries',      '', 4);

insert into fields values( 1, 'NTsyscalls',    'tstamp',       'i', 'timestamp',                '', 0);
insert into fields values( 2, 'NTsyscalls',    'cont_sw',      'f', 'Context switches/sec',     '', 1);
insert into fields values( 3, 'NTsyscalls',    'syscalls',     'f', 'System calls/sec',         '', 1);
insert into fields values( 4, 'NTsyscalls',    'interrupts',   'f', 'Interrupts/sec',           '', 2);
insert into fields values( 5, 'NTsyscalls',    'exc_disp',     'f', 'Exc dispatches/sec',       '', 3);
insert into fields values( 6, 'NTsyscalls',    'float_emu',    'f', 'Floating emu/sec',         '', 3);

insert into fields values( 1, 'NTfileops',     'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTfileops',     'read_op',        'f', 'Read ops/sec',           '', 1);
insert into fields values( 3, 'NTfileops',     'write_op',       'f', 'Write ops/sec',          '', 1);
insert into fields values( 4, 'NTfileops',     'ctrl_op',        'f', 'Control ops/sec',        '', 1);
insert into fields values( 5, 'NTfileops',     'read_bytes',     'f', 'Read bytes/sec',         '', 2);
insert into fields values( 6, 'NTfileops',     'write_bytes',    'f', 'Written bytes/sec',      '', 2);
insert into fields values( 7, 'NTfileops',     'ctrl_bytes',     'f', 'Control bytes/sec',      '', 2);
insert into fields values( 8, 'NTfileops',     'data_op',        'f', 'Data ops/sec',           '', 3);

insert into fields values( 1, 'NTprocs',       'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTprocs',       'processes',      'f', 'Processes',              '', 1);
insert into fields values( 3, 'NTprocs',       'threads',        'f', 'Threads',                '', 1);
insert into fields values( 4, 'NTprocs',       'events',         'f', 'Events',                 '', 2);
insert into fields values( 5, 'NTprocs',       'semas',          'f', 'Semaphores',             '', 3);
insert into fields values( 6, 'NTprocs',       'mutexes',        'f', 'Mutexes',                '', 3);
insert into fields values( 7, 'NTprocs',       'sections',       'f', 'Sections',               '', 3);

insert into fields values( 1, 'NTcache',       'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTcache',       'data_maps',      'f', 'Data maps/sec',          '', 1);
insert into fields values( 3, 'NTcache',       'data_map_hits',  'f', '%Data map hits',         '', 2);
insert into fields values( 4, 'NTcache',       'pin_reads',      'f', 'Pin reads/sec',          '', 1);
insert into fields values( 5, 'NTcache',       'pin_read_hits',  'f', '%Pin read hits',         '', 2);
insert into fields values( 6, 'NTcache',       'cp_reads',       'f', 'Copy reads/sec',         '', 1);
insert into fields values( 7, 'NTcache',       'cp_read_hits',   'f', '%Copy read hits',        '', 2);
insert into fields values( 8, 'NTcache',       'mdl_reads',      'f', 'MDL reads/sec',          '', 1);
insert into fields values( 9, 'NTcache',       'mdl_read_hits',  'f', '%MDL read hits',         '', 2);
insert into fields values(10, 'NTcache',       'fst_reads',      'f', 'Fast reads/sec',         '', 3);
insert into fields values(11, 'NTcache',       'lazy_wrt',       'f', 'Lazy write pgs/sec',     '', 4);
insert into fields values(12, 'NTcache',       'data_flush',     'f', 'Data flush pgs/sec',     '', 4);

insert into fields values( 1, 'NTpaging',      'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTpaging',      'usage',          'f', '% usage',                '', 1);
insert into fields values( 3, 'NTpaging',      'peakusage',      'f', '% peak usage',           '', 1);

insert into fields values( 1, 'NTredirRW',     'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTredirRW',     'bytes',          'f', 'bytes total/sec',        '', 1);
insert into fields values( 3, 'NTredirRW',     'fileops',        'f', 'file data ops/sec',      '', 2);
insert into fields values( 4, 'NTredirRW',     'packets',        'f', 'packets/sec',            '', 2);
insert into fields values( 5, 'NTredirRW',     'bytesrcv',       'f', 'bytes received/sec',     '', 3);
insert into fields values( 6, 'NTredirRW',     'bytestx',        'f', 'bytes transm./sec',      '', 3);
insert into fields values( 7, 'NTredirRW',     'read_den',       'f', 'reads denied/sec',       '', 4);
insert into fields values( 8, 'NTredirRW',     'write_den',      'f', 'writes denied/sec',      '', 4);
insert into fields values( 9, 'NTredirRW',     'net_err',        'f', 'network errs/sec',       '', 4);

insert into fields values( 1, 'NTredirSess',   'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTredirSess',   'srv_sess',       'f', 'sessions/min',           '', 1);
insert into fields values( 3, 'NTredirSess',   'srv_reconn',     'f', 'reconnects/min',         '', 5);
insert into fields values( 4, 'NTredirSess',   'conns_core',     'f', 'conns core/min',         '', 3);
insert into fields values( 5, 'NTredirSess',   'conns_lm20',     'f', 'conns LM 2.0/min',       '', 3);
insert into fields values( 6, 'NTredirSess',   'conns_lm21',     'f', 'conns LM 2.1/min',       '', 3);
insert into fields values( 7, 'NTredirSess',   'conns_wnt',      'f', 'conns Win NT/min',       '', 2);
insert into fields values( 8, 'NTredirSess',   'disconns',       'f', 'disconnects/min',        '', 4);
insert into fields values( 9, 'NTredirSess',   'hung',           'f', 'sess. hung/min',         '', 5);

insert into fields values( 1, 'NTserverRW',    'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTserverRW',    'bytes',          'f', 'bytes total/sec',        '', 1);
insert into fields values( 3, 'NTserverRW',    'bytesrcv',       'f', 'bytes received/sec',     '', 2);
insert into fields values( 4, 'NTserverRW',    'bytestx',        'f', 'bytes sent/sec',         '', 2);
insert into fields values( 5, 'NTserverRW',    'cont_q',         'f', 'context blk qd/sec',     '', 3);

insert into fields values( 1, 'NTserverSess',  'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTserverSess',  'logons',         'f', 'logons/min',             '', 1);
insert into fields values( 3, 'NTserverSess',  'timed_out',      'f', 'sess timed out/min',     '', 2);
insert into fields values( 4, 'NTserverSess',  'errored_out',    'f', 'sess errd out/min',      '', 2);
insert into fields values( 5, 'NTserverSess',  'logged_off',     'f', 'sess logoff/min',        '', 2);
insert into fields values( 6, 'NTserverSess',  'forced_off',     'f', 'sess forcedoff/min',     '', 2);
insert into fields values( 7, 'NTserverSess',  'err_logon',      'f', 'errs logon/min',         '', 3);
insert into fields values( 8, 'NTserverSess',  'err_acc_perm',   'f', 'errs acc perms/min',     '', 3);
insert into fields values( 9, 'NTserverSess',  'err_grant_acc',  'f', 'errs granted/min',       '', 3);
insert into fields values(10, 'NTserverSess',  'err_system',     'f', 'errs system/min',        '', 3);
insert into fields values(11, 'NTserverSess',  'pool_np_fail',   'f', 'nonpaged fail/min',      '', 4);
insert into fields values(12, 'NTserverSess',  'pool_p_fail',    'f', 'paged fail/min',         '', 4);

insert into fields values( 1, 'NTphysdisk',    'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTphysdisk',    'disk',           'c', 'disk number',            '', 0);
insert into fields values( 3, 'NTphysdisk',    'disktime',       'f', '% usage',                '', 1);
insert into fields values( 4, 'NTphysdisk',    'qlen',           'f', 'queue len',              '', 2);
insert into fields values( 5, 'NTphysdisk',    'sec_xfer',       'f', 'disk time/xfer',         '', 3);
insert into fields values( 6, 'NTphysdisk',    'xfer_sec',       'f', 'xfers/sec',              '', 4);
insert into fields values( 7, 'NTphysdisk',    'bytes_sec',      'f', 'bytes/sec',              '', 5);
insert into fields values( 8, 'NTphysdisk',    'bytes_xfer',     'f', 'bytes/xfer',             '', 6);

insert into fields values( 1, 'NTlogdisk',     'tstamp',         'i', 'timestamp',              '', 0);
insert into fields values( 2, 'NTlogdisk',     'disk',           'c', 'disk drive',             '', 0);
insert into fields values( 3, 'NTlogdisk',     'disktime',       'f', '% usage',                '', 1);
insert into fields values( 4, 'NTlogdisk',     'qlen',           'f', 'queue len',              '', 2);
insert into fields values( 5, 'NTlogdisk',     'sec_xfer',       'f', 'disk time/xfer',         '', 3);
insert into fields values( 6, 'NTlogdisk',     'xfer_sec',       'f', 'xfers/sec',              '', 4);
insert into fields values( 7, 'NTlogdisk',     'bytes_sec',      'f', 'bytes/sec',              '', 5);
insert into fields values( 8, 'NTlogdisk',     'bytes_xfer',     'f', 'bytes/xfer',             '', 6);

alter table systems add (nickname varchar);
update systems set nickname=hostname;

-- work-around for Postgres bug:
select hostname, nickname, systype, gathering into systems2 from systems;
drop table systems;
alter table systems2 rename to systems;

end transaction;

__EOF

# Now upgrade existing databases
# Create dbinfo table

dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c 'select hostname from systems'`
for db in $dbs
do
    q="select systype from systems where hostname='$db'"
    systype=`$PGSQLDIR/psql -d $PERFDB -q -t -c "$q"`

    $PGSQLDIR/psql -q -d $db > /dev/null << __EOF

create table dbinfo(version varchar, systype varchar, nickname varchar, gathering char);
insert into dbinfo values ('2.2', '$systype', '$db', 'n');

__EOF
done

# Divide sar_mem fields for Solaris by 1024 to reflect Kb of memory

dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c "select hostname from systems where systype='solaris'"`
for db in $dbs
do
    $PGSQLDIR/psql -q -d $db > /dev/null << __EOF

update sar_mem   set sml_mem=sml_mem/1024, alloc=alloc/1024, lg_mem=lg_mem/1024,
                     alloc2=alloc2/1024, ovsz_alloc=ovsz_alloc/1024;
update sar_mem_d set sml_mem=sml_mem/1024, alloc=alloc/1024, lg_mem=lg_mem/1024,
                     alloc2=alloc2/1024, ovsz_alloc=ovsz_alloc/1024;
update sar_mem_w set sml_mem=sml_mem/1024, alloc=alloc/1024, lg_mem=lg_mem/1024,
                     alloc2=alloc2/1024, ovsz_alloc=ovsz_alloc/1024;
update sar_mem_m set sml_mem=sml_mem/1024, alloc=alloc/1024, lg_mem=lg_mem/1024,
                     alloc2=alloc2/1024, ovsz_alloc=ovsz_alloc/1024;
update sar_mem_y set sml_mem=sml_mem/1024, alloc=alloc/1024, lg_mem=lg_mem/1024,
                     alloc2=alloc2/1024, ovsz_alloc=ovsz_alloc/1024;

__EOF
done

}

##########################################################################
# Make version 2.2-inww of the database
##########################################################################
mk_v2_2_inww ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;

begin transaction;

update perfdb_ver set version = '2.2-inww';

delete from fields where fieldtable = 'usertbl';
insert into fields values(1, 'usertbl',         'tstamp',       'i', 'tstamp',        '', 0);
insert into fields values(2, 'usertbl',         'user01',       'i', 'user01',        '', 1);
insert into fields values(3, 'usertbl',         'user02',       'i', 'user02',        '', 1);
insert into fields values(4, 'usertbl',         'user03',       'i', 'user03',        '', 2);
insert into fields values(5, 'usertbl',         'user04',       'i', 'user04',        '', 2);
insert into fields values(6, 'usertbl',         'user05',       'i', 'user05',        '', 3);
insert into fields values(7, 'usertbl',         'user06',       'i', 'user06',        '', 3);
insert into fields values(8, 'usertbl',         'user07',       'i', 'user07',        '', 4);
insert into fields values(9, 'usertbl',         'user08',       'i', 'user08',        '', 4);
insert into fields values(10, 'usertbl',        'user09',       'i', 'user09',        '', 5);
insert into fields values(11, 'usertbl',        'user10',       'i', 'user10',        '', 5);
insert into fields values(12, 'usertbl',        'user11',       'i', 'user11',        '', 6);
insert into fields values(13, 'usertbl',        'user12',       'i', 'user12',        '', 6);
insert into fields values(14, 'usertbl',        'user13',       'i', 'user13',        '', 7);
insert into fields values(15, 'usertbl',        'user14',       'i', 'user14',        '', 7);
insert into fields values(16, 'usertbl',        'user15',       'i', 'user15',        '', 8);
insert into fields values(17, 'usertbl',        'user16',       'i', 'user16',        '', 8);

end transaction;

__EOF

dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c "select hostname from systems where not systype='NT4'"`
for db in $dbs
do
    $PGSQLDIR/psql -q -d $db > /dev/null << __EOF

alter table usertbl rename user1 to user01;
alter table usertbl rename user2 to user02;
alter table usertbl rename user3 to user03;
alter table usertbl rename user4 to user04;
alter table usertbl add (user05  int);
alter table usertbl add (user06  int);
alter table usertbl add (user07  int);
alter table usertbl add (user08  int);
alter table usertbl add (user09  int);
alter table usertbl add (user10 int);
alter table usertbl add (user11 int);
alter table usertbl add (user12 int);
alter table usertbl add (user13 int);
alter table usertbl add (user14 int);
alter table usertbl add (user15 int);
alter table usertbl add (user16 int);
update usertbl set user05=0, user06=0, user07=0, user08=0, user09=0, user10=0,
                   user11=0, user12=0, user13=0, user14=0, user15=0, user16=0;

alter table usertbl_d rename user1 to user01;
alter table usertbl_d rename user2 to user02;
alter table usertbl_d rename user3 to user03;
alter table usertbl_d rename user4 to user04;
alter table usertbl_d add (user05  int);
alter table usertbl_d add (user06  int);
alter table usertbl_d add (user07  int);
alter table usertbl_d add (user08  int);
alter table usertbl_d add (user09  int);
alter table usertbl_d add (user10 int);
alter table usertbl_d add (user11 int);
alter table usertbl_d add (user12 int);
alter table usertbl_d add (user13 int);
alter table usertbl_d add (user14 int);
alter table usertbl_d add (user15 int);
alter table usertbl_d add (user16 int);
update usertbl_d set user05=0, user06=0, user07=0, user08=0, user09=0, user10=0,
                     user11=0, user12=0, user13=0, user14=0, user15=0, user16=0;

alter table usertbl_w rename user1 to user01;
alter table usertbl_w rename user2 to user02;
alter table usertbl_w rename user3 to user03;
alter table usertbl_w rename user4 to user04;
alter table usertbl_w add (user05  int);
alter table usertbl_w add (user06  int);
alter table usertbl_w add (user07  int);
alter table usertbl_w add (user08  int);
alter table usertbl_w add (user09  int);
alter table usertbl_w add (user10 int);
alter table usertbl_w add (user11 int);
alter table usertbl_w add (user12 int);
alter table usertbl_w add (user13 int);
alter table usertbl_w add (user14 int);
alter table usertbl_w add (user15 int);
alter table usertbl_w add (user16 int);
update usertbl_w set user05=0, user06=0, user07=0, user08=0, user09=0, user10=0,
                     user11=0, user12=0, user13=0, user14=0, user15=0, user16=0;

alter table usertbl_m rename user1 to user01;
alter table usertbl_m rename user2 to user02;
alter table usertbl_m rename user3 to user03;
alter table usertbl_m rename user4 to user04;
alter table usertbl_m add (user05  int);
alter table usertbl_m add (user06  int);
alter table usertbl_m add (user07  int);
alter table usertbl_m add (user08  int);
alter table usertbl_m add (user09  int);
alter table usertbl_m add (user10 int);
alter table usertbl_m add (user11 int);
alter table usertbl_m add (user12 int);
alter table usertbl_m add (user13 int);
alter table usertbl_m add (user14 int);
alter table usertbl_m add (user15 int);
alter table usertbl_m add (user16 int);
update usertbl_m set user05=0, user06=0, user07=0, user08=0, user09=0, user10=0,
                     user11=0, user12=0, user13=0, user14=0, user15=0, user16=0;

alter table usertbl_y rename user1 to user01;
alter table usertbl_y rename user2 to user02;
alter table usertbl_y rename user3 to user03;
alter table usertbl_y rename user4 to user04;
alter table usertbl_y add (user05  int);
alter table usertbl_y add (user06  int);
alter table usertbl_y add (user07  int);
alter table usertbl_y add (user08  int);
alter table usertbl_y add (user09  int);
alter table usertbl_y add (user10 int);
alter table usertbl_y add (user11 int);
alter table usertbl_y add (user12 int);
alter table usertbl_y add (user13 int);
alter table usertbl_y add (user14 int);
alter table usertbl_y add (user15 int);
alter table usertbl_y add (user16 int);
update usertbl_y set user05=0, user06=0, user07=0, user08=0, user09=0, user10=0,
                     user11=0, user12=0, user13=0, user14=0, user15=0, user16=0;

update userfldnames set fieldname='user01' where fieldname='user1';
update userfldnames set fieldname='user02' where fieldname='user2';
update userfldnames set fieldname='user03' where fieldname='user3';
update userfldnames set fieldname='user04' where fieldname='user4';
update userfldnames set fieldshdesc='user01' where fieldshdesc='user1';
update userfldnames set fieldshdesc='user02' where fieldshdesc='user2';
update userfldnames set fieldshdesc='user03' where fieldshdesc='user3';
update userfldnames set fieldshdesc='user04' where fieldshdesc='user4';
insert into userfldnames values('user05', 'user05');
insert into userfldnames values('user06', 'user06');
insert into userfldnames values('user07', 'user07');
insert into userfldnames values('user08', 'user08');
insert into userfldnames values('user09', 'user09');
insert into userfldnames values('user10', 'user10');
insert into userfldnames values('user11', 'user11');
insert into userfldnames values('user12', 'user12');
insert into userfldnames values('user13', 'user13');
insert into userfldnames values('user14', 'user14');
insert into userfldnames values('user15', 'user15');
insert into userfldnames values('user16', 'user16');


__EOF

done

}

##########################################################################
# Make version 2.3 of the database
##########################################################################
mk_v2_3 ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;
begin transaction;
update perfdb_ver set version = '2.3';
end transaction;

__EOF


dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c "select hostname from systems"`
for db in $dbs
do
    $PGSQLDIR/psql -q -d $db > /dev/null << __EOF

begin transaction;
update dbinfo set version='2.3';
end transaction;

__EOF

done

}

##########################################################################
# Make version 2.4 of the database
##########################################################################
mk_v2_4 ()
{
$PGSQLDIR/psql -q > /dev/null << __EOF

\connect $PERFDB;
begin transaction;
update perfdb_ver set version = '2.4';
end transaction;

__EOF


dbs=`$PGSQLDIR/psql -d $PERFDB -q -t -c "select hostname from systems"`
for db in $dbs
do
    $PGSQLDIR/psql -q -d $db > /dev/null << __EOF

begin transaction;
update dbinfo set version='2.4';
end transaction;

__EOF

done

}

##########################################################################
# Main
##########################################################################
dbver='0.0'

$PGSQLDIR/psql -l > /dev/null
ret=$?
if [ $ret != 0 ]
then
    echo "psql returned an error - has postgres been installed?"
    echo "Stopping creation of perf database"
    exit 1
fi

dbs=`$PGSQLDIR/psql -l | grep $PERFDB | awk -F\| '{print $1}'`
dbs=`$PGSQLDIR/psql -l | $PERLDIR/perl -e "while (\\$db = <STDIN>) {chomp(\\$db); @db = split /[| ]/, \\$db; if (@db[0] eq \"$PERFDB\") {print @db[0]}}"`
dbs=`echo $dbs`
if [ "$dbs" = "$PERFDB" ]
then
    echo "Database $PERFDB already seems to exist."
    echo "Checking version..."
    dbver=`$PGSQLDIR/psql -d $PERFDB -c "select * from perfdb_ver" -q -t`
    dbver=`echo $dbver`
fi

case $dbver in
'0.0')    mk_v1_0
          mk_v2_0
          mk_v2_1
          mk_v2_2
          mk_v2_2_inww
          mk_v2_3
          mk_v2_4
          ;;
'1.0')    echo Upgrading from version 1.0 to version 2.4...
          mk_v2_0
          mk_v2_1
          mk_v2_2
          mk_v2_2_inww
          mk_v2_3
          mk_v2_4
          ;;
'2.0')    echo Upgrading from version 2.0 to version 2.4...
          mk_v2_1
          mk_v2_2
          mk_v2_2_inww
          mk_v2_3
          mk_v2_4
          ;;
'2.1')    echo Upgrading from version 2.1 to version 2.4...
          mk_v2_2
          mk_v2_2_inww
          mk_v2_3
          mk_v2_4
          ;;
'2.2')    echo Upgrading from version 2.2 to version 2.4...
          mk_v2_2_inww
          mk_v2_3
          mk_v2_4
          ;;
'2.2-inww') echo Upgrading from version 2.2-inww to version 2.4...
          mk_v2_3
          mk_v2_4
          ;;
'2.3')    echo Upgrading from version 2.3 to version 2.4...
          mk_v2_4
          ;;
'2.4')    echo "Database $PERFDB is up to date; no action"
          ;;
esac

echo Done
