OpenVMS Source Code Demos
MYSQL_DEMO_PROFILEDB
/* =========================================================================================================================
title : MySQL_DEMO_PROFILEDB_92.sql
created: 2014-03-06
author : Neil Rieck
purpose: I am exploring the difficulties associated with converting from RMS (an ISAM technology for OpenVMS) to MySQL.
: Note: Ten years ago, I did a similar trial conversion of this app (ProfileDB) from RMS to Oracle-RDB which you
: can find on line. We could not afford the Oracle-RDB license which was quoted at $30k
notes : 1) Profile-db is our database of profiled users on our system. It has links into our +100 applications.
: 2) keep the same variable names and string lengths for now
: 3) the first five "chunk3 fields" should never have been placed here and so will be skipped in this conversion
: 4) the filler field is now superfluous so will be skipped
: 5) d91 means disk record on channel 91. The original author used the same record map and variables for two
: related applications: "profile main" on channel 91 and "profile skills" on channel 92. Like junk-DNA, our
: system bears the scars of poor planning.
: 6) use this script to create a database and table. Then run MySQL_DEMO04.exe to insert the data
!
! this is the old RMS record definition for use with VMS-BASIC
!========================================================================================================================
! Title : Profile_xx.rec
! History:
! Ver When Who What
! --- ------ --- --------------------------------------------------------------------------------------------------------
! 92 140306 XXX 1. copied for export
!========================================================================================================================
!
!=======================================================================
! define data file names required by the following maps
!=======================================================================
declare string constant &
k_fs_pro_empl$ = "csmis$dat:profiledb_employee_92.DAT" ,! was fx$ (employee) &
k_fs_pro_skil$ = "csmis$dat:profiledb_skill_92.DAT" ,! was fs$ (skill) &
!
map (profile) ! &
string d91_group = 3, ! csm group name chunk1- &
d91_csmis_id = 12, ! csmis id chunk1 &
d91_last_name = 20, ! given name chunk1 &
d91_first_name = 15, ! surname chunk1 &
d91_init = 3, ! initials chunk1 &
d91_inactive = 1, ! inactive flag chunk1 &
d91_pin = 7, ! pin chunk2- &
d91_labour = 5, ! labour rate (now: S.O.T.) chunk2 &
d91_job_code = 3, ! job code from nomas chunk2 &
d91_pref_prog = 3, ! what program to run chunk2 &
d91_alerts = 5, ! alert option bits chunk2 &
d91_work_phone = 10, ! work phone 10 digits chunk2 &
d91_home_phone = 10, ! home phone 10 digits chunk2 &
d91_cell_phone = 10, ! cell phone 10 digits chunk2 &
d91_pager_prefix = 2, ! for future pager fix chunk2 &
d91_pager_num = 8, ! pager last 8 digits chunk2 &
d91_pager_type = 1, ! type of pager a/n/t chunk2 &
d91_ardis_num = 8, ! mobile term num (T.O.D.) chunk2 &
d91_ardis_type = 1, ! hand held term (NOMAS Flag) chunk2 &
d91_org_code = 8, ! org code chunk2 &
d91_title = 3, ! csmis title chunk2 &
d91_report_centre = 12, ! home office chunk2 &
d91_safety = 1, ! auto safety check chunk2 &
d91_printer = 3, ! default printer chunk2 &
d91_priv = 5, ! application privs chunk2 &
d91_language = 1, ! default language chunk2 &
d91_mail_service = 3, ! service of choice chunk2 &
d91_mail_id = 39, ! service id chunk2 &
d91_system = 20, ! system chunk3 &
d91_skill = 1, ! skills chunk3 &
d91_callout = 1, ! callout a.....z chunk3 &
d91_trained = 8, ! date last trained chunk3 &
d91_notes = 42, ! notes chunk3 &
d91_filler$ = 26, ! room to grow ... chunk3 &
d91_align = 0 !
====================================================================================================================== */
--
-- sql starts here
--
create database if not exists icsis;
use icsis;
--
-- this chunk is basically a one-for-one conversion of our VMS-BASIC map titled: Profiledb_92.rec
--
create table if not exists profile (
d91_group varchar( 3), -- csm group name chunk1- &
d91_csmis_id varchar( 12), -- csmis id chunk1 &
d91_last_name varchar( 20), -- given name chunk1 &
d91_first_name varchar( 15), -- surname chunk1 &
d91_init varchar( 3), -- initials chunk1 &
d91_inactive varchar( 1), -- inactive flag chunk1 &
d91_pin varchar( 7), -- pin chunk2- &
d91_labour varchar( 5), -- labour rate (now: S.O.T.) chunk2 &
d91_job_code varchar( 3), -- job code from nomas chunk2 &
d91_pref_prog varchar( 3), -- what program to run chunk2 &
d91_alerts varchar( 5), -- alert option bits chunk2 &
d91_work_phone varchar( 10), -- work phone 10 digits chunk2 &
d91_home_phone varchar( 10), -- home phone 10 digits chunk2 &
d91_cell_phone varchar( 10), -- cell phone 10 digits chunk2 &
d91_pager_prefix varchar( 2), -- for future pager fix chunk2 &
d91_pager_num varchar( 8), -- pager last 8 digits chunk2 &
d91_pager_type varchar( 1), -- type of pager a/n/t chunk2 &
d91_ardis_num varchar( 8), -- mobile term num (T.O.D.) chunk2 &
d91_ardis_type varchar( 1), -- hand held term (NOMAS Flag) chunk2 &
d91_org_code varchar( 8), -- org code chunk2 &
d91_title varchar( 3), -- csmis title chunk2 &
d91_report_centre varchar( 12), -- home office chunk2 &
d91_safety varchar( 1), -- auto safety check chunk2 &
d91_printer varchar( 3), -- default printer chunk2 &
d91_priv varchar( 5), -- application privs chunk2 &
d91_language varchar( 1), -- default language chunk2 &
d91_mail_service varchar( 3), -- service of choice chunk2 &
d91_mail_id varchar( 39), -- service id chunk2 &
primary key (d91_pin) );
--
-- for performance reasons, it is usually a good idea to only add indexes after data has been inserted into the table.
-- However, having a constraint (eg. no-dups) without an index will slow down insertions
--
-- caveat: can't use CREATE INDEX on a primary key (must use alter table)
-- create unique index pin_idx on table profile(d91_pin);
--
alter table profile add unique index pin_idx (d91_pin);
-- end of file