/*
* Автор: Галкин А.И.
* Дата: 2014-10-17
* Задача: http://jira.softage.ru/jira/browse/SDFO-355
* Назначение: Синхронизация схем и xaml шаблонов
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2014-10-17.ForNSISync.sql';
	purpose       varchar2(4000) := 'Синхронизация схем и xaml шаблонов';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	commit;

exception
	when others then
		rollback;
		raise;
end;
/

create or replace PROCEDURE LoadBlobFromFile 
(
  p_dirName IN VARCHAR2 
, p_fileName IN VARCHAR2
, p_blob in out BLOB
) AS 
l_bfile   BFILE;
BEGIN
   l_bfile := BFILENAME (p_dirName, p_fileName);
   DBMS_LOB.OPEN (l_bfile, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LoadFromFile (DEST_LOB   => p_blob,
                          SRC_LOB    => l_bfile,
                          AMOUNT     => DBMS_LOB.GETLENGTH (l_bfile));
   DBMS_LOB.CLOSE (l_bfile);
END LoadBlobFromFile;
/

GRANT EXECUTE ON KFM.LoadBlobFromFile TO KFM_ROLE;

create or replace PROCEDURE LoadMessageSchemaFromFile(
    p_dirName     VARCHAR2,
    p_fileName    VARCHAR2,
    p_versionCode VARCHAR2)
IS
  l_bfile BFILE;
  l_blob BLOB;
BEGIN
  UPDATE MESSAGES_VERSIONS
  SET XML_SCHEMA = empty_blob()
  WHERE VERSIONCODE = p_versionCode;

  SELECT MV.XML_SCHEMA
  INTO l_blob
  FROM MESSAGES_VERSIONS mv
  WHERE MV.VERSIONCODE = p_versionCode
  FOR UPDATE;
  
  LoadBlobFromFile(p_dirName, p_fileName, l_blob);
END LoadMessageSchemaFromFile;
/

GRANT EXECUTE ON KFM.LoadMessageSchemaFromFile TO KFM_ROLE
/

CREATE OR REPLACE PROCEDURE LoadMessageXamlFromFile (
    p_dirName     VARCHAR2,
    p_fileName    VARCHAR2,
    p_isEditable  NUMBER,
    p_versionCode VARCHAR2)
IS
  l_bfile BFILE;
  l_blob BLOB;
  l_count NUMBER;
BEGIN
  SELECT COUNT (*)
  INTO l_count
  FROM MESSAGES_TEMPLATES
  WHERE VERSIONCODE = p_versionCode AND IS_EDITABLE = p_isEditable;
  IF (l_count       = 0) THEN -- insert
    INSERT INTO MESSAGES_TEMPLATES (VERSIONCODE, IS_EDITABLE, TEMPLATE)
      VALUES(p_versionCode, p_isEditable, empty_blob ())
    RETURNING TEMPLATE
    INTO l_blob;
  ELSE -- update
    UPDATE MESSAGES_TEMPLATES
    SET TEMPLATE = empty_blob()
    WHERE VERSIONCODE = p_versionCode AND IS_EDITABLE = p_isEditable;
  
    SELECT TEMPLATE
    INTO l_blob
    FROM MESSAGES_TEMPLATES
    WHERE VERSIONCODE = p_versionCode AND IS_EDITABLE = p_isEditable
    FOR UPDATE;
  END IF;

  LoadBlobFromFile(p_dirName, p_fileName, l_blob);
END LoadMessageXamlFromFile;
/

GRANT EXECUTE ON KFM.LoadMessageXamlFromFile TO KFM_ROLE;
