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