<commit_before>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';




-- -----------------------------------------------------
-- Table `query_objects`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `query_objects` ;

CREATE  TABLE IF NOT EXISTS `query_objects` (
  `query_object_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `instance` VARCHAR(50) NOT NULL DEFAULT 'lava' ,
  `scope` VARCHAR(50) NOT NULL ,
  `module` VARCHAR(50) NOT NULL ,
  `section` VARCHAR(50) NOT NULL ,
  `target` VARCHAR(50) NOT NULL ,
  `short_desc` VARCHAR(50) NULL DEFAULT NULL ,
  `standard` TINYINT(4) NOT NULL DEFAULT '1' ,
  `primary_link` TINYINT(4) NOT NULL DEFAULT '1' ,
  `secondary_link` TINYINT(4) NOT NULL DEFAULT '1' ,
  `notes` TEXT NULL DEFAULT NULL ,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`query_object_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


DELETE from query_objects where instance='lava' and scope='crms' and module='query' and section<>'nacc';

INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','patient','demographics','Demographics',1,0,1);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','enrollment','status','Enrollment Status',1,0,0);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','scheduling','visits','Visits',1,0,0);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','assessment','instruments','Instrument Tracking',1,0,0);
  
  
  


-- -----------------------------------------------------
-- procedure lq_get_objects
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_get_objects`;
DELIMITER $$

DELIMITER $$
CREATE PROCEDURE `lq_get_objects` (user_name varchar(50), host_name varchar(25))
BEGIN
SELECT concat(`instance`,'_',`scope`,'_',`module`) as query_source,concat(`section`,'_',`target`) as query_object_name , `short_desc`, `standard`,`primary_link`,`secondary_link` from `query_objects`;
END$$

$$
DELIMITER ;


DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_audit_event
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_audit_event`;

DELIMITER $$
CREATE PROCEDURE `lq_audit_event` (user_name varchar(50),host_name varchar(25),lq_query_object varchar(100),lq_query_type varchar(25))
BEGIN



INSERT INTO audit_event_work (`audit_user`,`audit_host`,`audit_timestamp`,`action`,`action_event`)

    VALUES(user_name,host_name,NOW(),CONCAT('lava.*.query.',lq_query_object),lq_query_type);


  
END


$$

DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_authenticate_user
--
-- This procedure is is used for authenticating a user for LavaQuery usage. 
--
-- Currently it just checks that input password against the authuser email column
--
-- TODO: 
-- after upgrading to MySQL 5.5, will be able to use SHA encryption for local (database)
-- accounts where password is stored in authuser table encrypted via SHA with 256 bits 
-- (this encryption is done in Java using the Acegi API). As of MySQL 5.5 there is an 
-- sha2 function, while prior this there is only an sha (aka sha1) function in MySQL which 
-- does SHA 160 bit encryption.
--
-- TODO:
-- if this authentication fails, have LavaQuery xls attempt LDAP authentication.
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_authenticate_user`;
DELIMITER $$

DELIMITER $$
CREATE  PROCEDURE `lq_authenticate_user`(user_login varchar(50),user_password varchar(50))
BEGIN
DECLARE user_id int;

SELECT `UID` into user_id from `authuser` where `Login` = user_login AND `email` = user_password;

IF(user_id > 0) THEN

  SELECT  1 as auth_user;

ELSE

  SELECT 0 as auth_user;

END IF;


END$$

$$
DELIMITER ;


DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_check_user_auth
--
-- NOTE: currently not used as lq_authenticate_user encompasses this check. however, if<commit_msg>modified lq_authenticate_user to authenticate supplied password against the authuser password column, not the email column, using the MySQL sha2 to decrypt NOTE: this required MySQL 5.5 or later as earlier versions do not support the sha2 function<commit_after>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';




-- -----------------------------------------------------
-- Table `query_objects`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `query_objects` ;

CREATE  TABLE IF NOT EXISTS `query_objects` (
  `query_object_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `instance` VARCHAR(50) NOT NULL DEFAULT 'lava' ,
  `scope` VARCHAR(50) NOT NULL ,
  `module` VARCHAR(50) NOT NULL ,
  `section` VARCHAR(50) NOT NULL ,
  `target` VARCHAR(50) NOT NULL ,
  `short_desc` VARCHAR(50) NULL DEFAULT NULL ,
  `standard` TINYINT(4) NOT NULL DEFAULT '1' ,
  `primary_link` TINYINT(4) NOT NULL DEFAULT '1' ,
  `secondary_link` TINYINT(4) NOT NULL DEFAULT '1' ,
  `notes` TEXT NULL DEFAULT NULL ,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`query_object_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


DELETE from query_objects where instance='lava' and scope='crms' and module='query' and section<>'nacc';

INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','patient','demographics','Demographics',1,0,1);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','enrollment','status','Enrollment Status',1,0,0);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','scheduling','visits','Visits',1,0,0);
INSERT INTO query_objects(instance,scope,module,section,target,short_desc,standard,primary_link,secondary_link) 
  VALUES('lava','crms','query','assessment','instruments','Instrument Tracking',1,0,0);
  
  
  


-- -----------------------------------------------------
-- procedure lq_get_objects
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_get_objects`;
DELIMITER $$

DELIMITER $$
CREATE PROCEDURE `lq_get_objects` (user_name varchar(50), host_name varchar(25))
BEGIN
SELECT concat(`instance`,'_',`scope`,'_',`module`) as query_source,concat(`section`,'_',`target`) as query_object_name , `short_desc`, `standard`,`primary_link`,`secondary_link` from `query_objects`;
END$$

$$
DELIMITER ;


DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_audit_event
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_audit_event`;

DELIMITER $$
CREATE PROCEDURE `lq_audit_event` (user_name varchar(50),host_name varchar(25),lq_query_object varchar(100),lq_query_type varchar(25))
BEGIN



INSERT INTO audit_event_work (`audit_user`,`audit_host`,`audit_timestamp`,`action`,`action_event`)

    VALUES(user_name,host_name,NOW(),CONCAT('lava.*.query.',lq_query_object),lq_query_type);


  
END


$$

DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_authenticate_user
--
-- This procedure is is used for authenticating a user for LavaQuery usage. 
--
-- Authentication is done against the authuser table. So the user uses the same credentials
-- as in LAVA and can use LAVA to change their password.
-- NOTE: this only works for LAVA users where authenticationType is "LOCAL" (i.e. stored in
-- authuser. it does not work for "UCSF AD", although the LavaQuery xls file could be
-- programmed to try UCSF AD (i.e. LDAP) authentication if LOCAL authentication fails.
--
-- TODO:
-- if this authentication fails, have LavaQuery xls attempt LDAP authentication.
-- -----------------------------------------------------
DROP procedure IF EXISTS `lq_authenticate_user`;
DELIMITER $$

DELIMITER $$
CREATE  PROCEDURE `lq_authenticate_user`(user_login varchar(50),user_password varchar(50))
BEGIN
DECLARE user_id int;

SELECT `UID` into user_id from `authuser` where `Login` = user_login AND `password` = convert(sha2(concat(user_password,'{',`UID`,'}'),256) USING latin1);

IF(user_id > 0) THEN

  SELECT  1 as auth_user;

ELSE

  SELECT 0 as auth_user;

END IF;


END$$

$$
DELIMITER ;


DELIMITER ;
-- -----------------------------------------------------
-- procedure lq_check_user_auth
--
-- NOTE: currently not used as lq_authenticate_user encompasses this check. however, if
{'fraction_non_alphanumeric': 0.21439509954058192, 'fraction_numerical': 0.014329468387661344, 'mean_word_length': 3.2784277023865234, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4436579', 'n_tokens_mistral': 2959, 'n_tokens_neox': 2742, 'n_words': 701}