Tuesday 12 June 2018

The Medrec 12.2.1.3 Datamodel DDL

Next week I deliver the training 'Weblogic 12c Tuning and Troubleshooting' . One of the labs is to have the sample application MedRec generate Stuck Threads, so that the students can investigate and try to solve that. Or actually configure the server so that it will cause a automatic restart.

To do so I have to deliberately break the application and so I need the source. I have an earlier version of the application, but not the sources. So I have to go to the latest MedRec. I actually like that, because it looks more modern.

The MedRec application is available if you install WebLogic with samples.

You can run the script demo_oracle.ddl  from against the database:
$WL_HOME/samples/server/examples/src/examples/common/ddl

The medrec.ear can be found at:
$WL_HOME/samples/server/medrec/dist/standalone

I ran in quite some confusion and frustration, but I found that this combination, although from the same samples folder, does not work. Not only this medrec.ear expects the tables in plural (PRESCRIPTIONS) where the script creates them in singular (PRESCRIPTION), it expects a separate DRUGS table with a foreign key column DRUG_ID in PRESCRIPTIONS.  And a few other changes.

I had a version of the scripts from earlier versions of WebLogic's MedRec. Based the exceptions in the server log, I refactored/reverse engineered the scripts.

Using those I could succesfully login and view the Patient records of fred@golf.com:


First we need to create a a schema using createDBUserMedrec.sql:
prompt Create user medrec with connect, resource roles;
grant connect, resource to medrec identified by  welcome1;
alter user medrec 
default tablespace users
temporary tablespace temp; 
alter user medrec quota unlimited on users;

Drop tables (if needed) using medrec_dropall.sql:
DROP TABLE "MEDREC"."ADMINISTRATORS";

DROP TABLE "MEDREC"."OPENJPA_SEQUENCE_TABLE"; 

DROP TABLE "MEDREC"."PATIENTS";

DROP TABLE "MEDREC"."PATIENTS_RECORDS";

DROP TABLE "MEDREC"."PHYSICIANS";

DROP TABLE "MEDREC"."PRESCRIPTIONS";

DROP TABLE "MEDREC"."DRUGS";

DROP TABLE "MEDREC"."RECORDS";

DROP TABLE "MEDREC"."RECORDS_PRESCRIPTIONS";

Create the tables using medrec_tables.sql:
CREATE TABLE "MEDREC"."ADMINISTRATORS" (
   "ID" INTEGER NOT NULL,
   "EMAIL" VARCHAR(255),
   "PASSWORD" VARCHAR(255),
   "USERNAME" VARCHAR(255),
   "VERSION" INTEGER,
   PRIMARY KEY ( "ID" ) );


CREATE TABLE "MEDREC"."OPENJPA_SEQUENCE_TABLE" (
   "ID" SMALLINT NOT NULL,
   "SEQUENCE_VALUE" INTEGER,
   PRIMARY KEY ( "ID" ) );


CREATE TABLE "MEDREC"."PATIENTS" (
   "ID" INTEGER NOT NULL,
   "EMAIL" VARCHAR(255),
   "PASSWORD" VARCHAR(255),
   "USERNAME" VARCHAR(255),
   "PHONE" VARCHAR(255),
   "DOB" TIMESTAMP,
   "GENDER" VARCHAR(20),
   "SSN" VARCHAR(255),
   "STATUS" VARCHAR(20),
   "VERSION" INTEGER,
   "FIRSTNAME" VARCHAR(255),
   "LASTNAME" VARCHAR(255),
   "MIDDLENAME" VARCHAR(255),
   "CITY" VARCHAR(255),
   "COUNTRY" VARCHAR(255),
   "STATE" VARCHAR(255),
   "STREET1" VARCHAR(255),
   "STREET2" VARCHAR(255),
   "ZIP" VARCHAR(255),
   PRIMARY KEY ( "ID" ) );


CREATE TABLE "MEDREC"."PATIENTS_RECORDS" (
   "PATIENT_ID" INTEGER,
   "RECORDS_ID" INTEGER );


CREATE TABLE "MEDREC"."PHYSICIANS" (
   "ID" INTEGER NOT NULL,
   "EMAIL" VARCHAR(255),
   "PASSWORD" VARCHAR(255),
   "USERNAME" VARCHAR(255),
   "PHONE" VARCHAR(255),
   "VERSION" INTEGER,
   "FIRSTNAME" VARCHAR(255),
   "LASTNAME" VARCHAR(255),
   "MIDDLENAME" VARCHAR(255),
   PRIMARY KEY ( "ID" ) );


CREATE TABLE "MEDREC"."DRUGS" 
   ( "ID" NUMBER(*,0) NOT NULL ENABLE, 
 "NAME" VARCHAR2(255 BYTE), 
 "FREQUENCY" VARCHAR2(255 BYTE), 
    "PRICE" NUMBER(10,2),
 "VERSION" NUMBER(*,0),
   PRIMARY KEY ( "ID" ) );


CREATE TABLE "MEDREC"."PRESCRIPTIONS" (
   "ID" INTEGER NOT NULL,
   "DATE_PRESCRIBED" TIMESTAMP,
   "FREQUENCY" VARCHAR(255),
   "INSTRUCTIONS" VARCHAR(255),
   "REFILLS_REMAINING" INTEGER,
   "VERSION" INTEGER,
   "DOSAGE" NUMBER, 
   "DRUG_ID" NUMBER, 
   PRIMARY KEY ( "ID" ) );

CREATE TABLE "MEDREC"."RECORDS" (
   "ID" INTEGER NOT NULL,
   "CREATE_DATE" TIMESTAMP,
   "RECORDDATE" TIMESTAMP,
   "DIAGNOSIS" VARCHAR(255),
   "NOTES" VARCHAR(255),
   "SYMPTOMS" VARCHAR(255),
   "VERSION" INTEGER,
   "PATIENT_ID" INTEGER NOT NULL,
   "PHYSICIAN_ID" INTEGER NOT NULL,
   "DIASTOLIC_BLOOD_PRESSURE" INTEGER,
   "HEIGHT" INTEGER,
   "PULSE" INTEGER,
   "SYSTOLIC_BLOOD_PRESSURE" INTEGER,
   "TEMPERATURE" INTEGER,
   "WEIGHT" INTEGER,
   PRIMARY KEY ( "ID" ) );

CREATE TABLE "MEDREC"."RECORDS_PRESCRIPTIONS" (
   "RECORD_ID" INTEGER,
   "PRESCRIPTIONS_ID" INTEGER );


Insert data using medrec_data.sql:
INSERT INTO "MEDREC"."ADMINISTRATORS" (
"ID", "EMAIL", "PASSWORD", "USERNAME", "VERSION" )
VALUES (
201,'admin@avitek.com','weblogic','admin@avitek.com',1
);

COMMIT;

INSERT INTO "MEDREC"."OPENJPA_SEQUENCE_TABLE" (
"ID", "SEQUENCE_VALUE" )
VALUES (
0,251
);

COMMIT;

INSERT INTO "MEDREC"."PATIENTS"
VALUES (
51,'page@fish.com','weblogic','page@fish.com','4151234564',
TIMESTAMP '1972-03-18 00:00:00','MALE','888888888','APPROVED',3,
'Page','Trout','A','Ponte Verde','United States','FL',
'235 Montgomery St','Suite 15','32301'
);
INSERT INTO "MEDREC"."PATIENTS"
VALUES (
52,'fred@golf.com','weblogic','fred@golf.com','4151234564',
TIMESTAMP '1965-04-26 00:00:00','MALE','123456789','APPROVED',3,
'Fred','Winner','I','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'
);
INSERT INTO "MEDREC"."PATIENTS"
VALUES (
53,'volley@ball.com','weblogic','volley@ball.com','4151234564',
TIMESTAMP '1971-09-17 00:00:00','MALE','333333333','APPROVED',3,
'Gabrielle','Spiker','H','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'
);
INSERT INTO "MEDREC"."PATIENTS"
VALUES (
54,'charlie@star.com','weblogic','charlie@star.com','4151234564',
TIMESTAMP '1973-11-29 00:00:00','MALE','444444444','REGISTERED',3,
'Charlie','Florida','E','Ponte Verde','United States','FL',
'235 Montgomery St','Suite 15','32301'
);
INSERT INTO "MEDREC"."PATIENTS"
VALUES (
55,'larry@bball.com','weblogic','larry@bball.com','4151234564',
TIMESTAMP '1959-03-13 00:00:00','MALE','777777777','APPROVED',3,
'Larry','Parrot','J','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'
);

COMMIT;

INSERT INTO "MEDREC"."PHYSICIANS" (
"ID", "EMAIL", "PASSWORD", "USERNAME", "PHONE", "VERSION", "FIRSTNAME", "LASTNAME", "MIDDLENAME" )
VALUES (
1,'mary@md.com','weblogic','mary@md.com','1234567812',4,'Mary','Oblige','J'
);

COMMIT;

Insert into "MEDREC"."DRUGS"  (ID,NAME,FREQUENCY,PRICE,VERSION) values (101,'Advil','1/4hrs',1.0, 2);
Insert into "MEDREC"."DRUGS"  (ID,NAME,FREQUENCY,PRICE,VERSION) values (102,'Codeine','1/6hrs',2.5,2);
Insert into "MEDREC"."DRUGS"  (ID,NAME,FREQUENCY,PRICE,VERSION) values (103,'Drixoral','1tspn/4hrs',3.75,2);

COMMIT;

Insert into "MEDREC"."PRESCRIPTIONS"  (ID,DATE_PRESCRIBED,FREQUENCY,INSTRUCTIONS,REFILLS_REMAINING,VERSION,DOSAGE,DRUG_ID) values (101,to_timestamp('18-JUL-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'1/4hrs',null,0,2,1,101);
Insert into "MEDREC"."PRESCRIPTIONS"  (ID,DATE_PRESCRIBED,FREQUENCY,INSTRUCTIONS,REFILLS_REMAINING,VERSION,DOSAGE,DRUG_ID) values (102,to_timestamp('30-JUN-93 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'1/6hrs',null,1,2,1,102);
Insert into "MEDREC"."PRESCRIPTIONS"  (ID,DATE_PRESCRIBED,FREQUENCY,INSTRUCTIONS,REFILLS_REMAINING,VERSION,DOSAGE,DRUG_ID) values (103,to_timestamp('18-JUL-99 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'1tspn/4hrs',null,0,2,1,103);


COMMIT;

INSERT INTO "MEDREC"."RECORDS"
VALUES (
151,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Allergic to coffee. Drink tea.',
'','Drowsy all day.',2,51,1,85,70,75,125,98,180
);
INSERT INTO "MEDREC"."RECORDS"
VALUES (
152,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Light cast needed.',
'At least 20 sprained ankles since 15.','Sprained ankle.',
2,53,1,85,70,75,125,98,180
);
INSERT INTO "MEDREC"."RECORDS"
VALUES (
153,TIMESTAMP '1989-08-05 00:00:00',TIMESTAMP '1989-08-05 00:00:00','Severely sprained interior ligament. Surgery required.','Cast will be necessary before and after.','Twisted knee while playing soccer.',2,52,1,85,70,75,125,98,180
);
INSERT INTO "MEDREC"."RECORDS"
VALUES (
154,TIMESTAMP '1993-06-30 00:00:00',TIMESTAMP '1993-06-30 00:00:00','Common cold. Prescribed codiene cough syrup.','Call back if not better in 10 days.','Sneezing, coughing, stuffy head.',2,52,1,85,70,75,125,98,180
);
INSERT INTO "MEDREC"."RECORDS"
VALUES (
155,TIMESTAMP '1999-07-18 00:00:00',TIMESTAMP '1999-07-18 00:00:00','Mild stroke.  Aspirin advised.','Patient needs to stop smoking.','Complains about chest pain.',2,52,1,85,70,75,125,98,180
);
INSERT INTO "MEDREC"."RECORDS"
VALUES (
156,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Patient is crazy.  Recommend politics.','','Overjoyed with everything.',2,55,1,85,70,75,125,98,180
);

COMMIT;

INSERT INTO "MEDREC"."RECORDS_PRESCRIPTIONS"
VALUES (
154,102
);
INSERT INTO "MEDREC"."RECORDS_PRESCRIPTIONS"
VALUES (
155,101
);
INSERT INTO "MEDREC"."RECORDS_PRESCRIPTIONS"
VALUES (
155,103
);

COMMIT;
To install the datasource you can use this wlst script, createDataSource.py:
#############################################################################
# Create DataSource for WLS 12c Tuning & Troubleshooting workshop
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.1, 2018-01-22
#
#############################################################################
# Modify these values as necessary
import os,sys, traceback
scriptName = sys.argv[0]
adminHost=os.environ["ADM_HOST"]
adminPort=os.environ["ADM_PORT"]
admServerUrl = 't3://'+adminHost+':'+adminPort
ttServerName=os.environ["TTSVR_NAME"]
adminUser='weblogic'
adminPwd='welcome1'
#
dsName = 'MedRecGlobalDataSourceXA'
dsJNDIName = 'jdbc/MedRecGlobalDataSourceXA'
initialCapacity = 5
maxCapacity = 10
capacityIncrement = 1
driverName = 'oracle.jdbc.xa.client.OracleXADataSource'
dbUrl = 'jdbc:oracle:thin:@darlin-vce.darwin-it.local:1521:orcl'
dbUser = 'medrec'
dbPassword = 'welcome1'
#
def createDataSource(dsName, dsJNDIName, initialCapacity, maxCapacity, capacityIncrement, dbUser, dbPassword, dbUrl, targetSvrName):
  # Check if data source already exists
  try:
    cd('/JDBCSystemResources/' + dsName)
    print 'The JDBC Data Source ' + dsName + ' already exists.'
    jdbcSystemResource=cmo
  except WLSTException:
    print 'Creating new JDBC Data Source named ' + dsName + '.'
    edit()
    startEdit()
    cd('/')
    # Create data source
    jdbcSystemResource = create(dsName, 'JDBCSystemResource')
    jdbcResource = jdbcSystemResource.getJDBCResource()
    jdbcResource.setName(dsName)
    # Set JNDI name
    jdbcResourceParameters = jdbcResource.getJDBCDataSourceParams()
    jdbcResourceParameters.setJNDINames([dsJNDIName])
    jdbcResourceParameters.setGlobalTransactionsProtocol('TwoPhaseCommit')
    # Create connection pool
    connectionPool = jdbcResource.getJDBCConnectionPoolParams()
    connectionPool.setInitialCapacity(initialCapacity)
    connectionPool.setMaxCapacity(maxCapacity)
    connectionPool.setCapacityIncrement(capacityIncrement)
    # Create driver settings
    driver = jdbcResource.getJDBCDriverParams()
    driver.setDriverName(driverName)
    driver.setUrl(dbUrl)
    driver.setPassword(dbPassword)
    driverProperties = driver.getProperties()
    userProperty = driverProperties.createProperty('user')
    userProperty.setValue(dbUser)
    # Set data source target
    targetServer = getMBean('/Servers/' + targetSvrName)
    jdbcSystemResource.addTarget(targetServer)
    # Activate changes
    save()
    activate(block='true')
    print 'Data Source created successfully.'
  return jdbcSystemResource

def main():
  # Connect to administration server
  try:
    connect(adminUser, adminPwd, admServerUrl)
    #
    createDataSource(dsName, dsJNDIName, initialCapacity, maxCapacity, capacityIncrement, dbUser, dbPassword, dbUrl,ttServerName)
    #    
    print("\nExiting...")
    exit()
  except:
    apply(traceback.print_exception, sys.exc_info())
    exit(exitcode=1)
#call main()
main()

Also Medrec needs an administrative user, createUser.py:
print 'starting the script ....'
#
adminHost=os.environ["ADM_HOST"]
adminPort=os.environ["ADM_PORT"]
admServerUrl = 't3://'+adminHost+':'+adminPort
#
adminUser='weblogic'
adminPwd='welcome1'
#
realmName = 'myrealm' 
#
def addUser(realm,username,password,description):
  print 'Prepare User',username,'...'
  if realm is not None:
    authenticator = realm.lookupAuthenticationProvider("DefaultAuthenticator")
    if authenticator.userExists(username)==1:
      print '[Warning]User',username,'has been existed.'
    else:
      authenticator.createUser(username,password,description)
      print '[INFO]User',username,'has been created successfully'


connect(adminUser,adminPwd,admServerUrl)

security=getMBean('/').getSecurityConfiguration()
realm=security.lookupRealm(realmName)
addUser(realm,'administrator','administrator123','MedRec Administrator')

disconnect()


Deploy the medrec application, deployMedRec.py:
#############################################################################
# Deploy MedRec for WLS 12c Tuning & Troubleshooting workshop
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2018-01-22
#
#############################################################################
# Modify these values as necessary
import os,sys, traceback
scriptName = sys.argv[0]
adminHost=os.environ["ADM_HOST"]
adminPort=os.environ["ADM_PORT"]
admServerUrl = 't3://'+adminHost+':'+adminPort
ttServerName=os.environ["TTSVR_NAME"]
adminUser='weblogic'
adminPwd='welcome1'
#
appName = 'medrec'
appSource = '../ear/medrec.ear'
#
# Deploy the application
def deployApplication(appName, appSource, targetServerName):
  print 'Deploying application ' + appName + '.'
  progress = deploy(appName=appName,path=appSource,targets=targetServerName)
  # Wait for deploy to complete
  while progress.isRunning():
   pass
  print 'Application ' + appName + ' deployed.'
#
#
def main():
  # Connect to administration server
  try:
    connect(adminUser, adminPwd, admServerUrl)
    #
    deployApplication(appName, appSource, ttServerName)
    #    
    print("\nExiting...")
    exit()
  except:
    apply(traceback.print_exception, sys.exc_info())
    exit(exitcode=1)
#call main()
main()


Monday 11 June 2018

Add Weblogic12c Vagrant project

Last week I proudly presented a talk on  how to create, provision and maintain VMs with Vagrant, including installing Oracle software (Database, FusionMiddlware,etc.). It was during the nlOUG Tech Experience 18.  I've written about it in the last few posts.

Today I added my WLS12c Vagrant project to Github. Based on further insights I refactored my database12c installation a bit and pushed an updated Database12c Vagrant project to Github as well.

Main updates are that I disliked the 'Zipped' subfolder in the stage folder paths for the installations. And I wanted all the installations extracted into the same main folder. Then I can clean that up more easily.

You should check the references to the stage folder. Download the appropriate Oracle installer zip files and place them in the appropriate folder:



Coming up: a SOA 12c (including SOA, BPM and OSB) complete with creation of the domain (based on my scripts from 2016 and my TechExperience talk of last year), and configuration of nodemanager. Ready to start up after 'up'.

I also uploaded my slides of my talk to slideshare: