API tools faq
paste
Guest User

Untitled

a guest
Apr 10th, 2018
38
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 17.74 KB | None | 0 0
raw download clone embed print report
  1. BEGIN TRANSACTION;
  2.  
  3. DO $$
  4.  
  5. DECLARE
  6.   -- Параметры схемы
  7.   the_scheme_id INTEGER := 2000;
  8.   the_scheme_name VARCHAR := 'prefact-responsum-data-loader';
  9.   the_scheme_caption VARCHAR := 'Выгрузка данных с InetProducer и загрузка в Responsum_data и Responsum';
  10.  
  11.   -- Параметры рабочей области
  12.   the_workspace_id INTEGER := 2;
  13.  
  14.   -- Параметры ноды
  15.   the_node_id INTEGER := 1;
  16.  
  17.   -- Параметры обработчиков
  18.   the_dummy_handler_id INTEGER := the_scheme_id+1;
  19.   the_dummy_handler_name VARCHAR := 'DummyHandler';
  20.   the_dummy_handler_caption VARCHAR := 'Dummy Handler';
  21.   the_dummy_handler_object_file VARCHAR := 'DummyHandler';
  22.  
  23.   the_exporter_stg_id INTEGER := the_scheme_id+2;
  24.   the_exporter_stg_name VARCHAR := 'ExporterStorage';
  25.   the_exporter_stg_caption VARCHAR := 'Загрузка данных из InetProducer';
  26.   the_exporter_stg_export_type VARCHAR := 'PandariaData';
  27.   the_exporter_stg_ftp_path VARCHAR := 'prefact_responsum_data';
  28.   the_exporter_stg_path_working VARCHAR := '/opt/projects/responsum/exporter/data/responsum_data';
  29.  
  30.   the_exporter_id INTEGER := the_scheme_id+4;
  31.   the_exporter_name VARCHAR := 'ExporterResponsum';
  32.   the_exporter_caption VARCHAR := 'Загрузка данных из IMedia и InetProducer';
  33.   the_exporter_export_type VARCHAR := 'Pandaria';
  34.   the_exporter_ftp_path VARCHAR := 'prefact_responsum';
  35.   the_exporter_path_working VARCHAR := '/opt/projects/responsum/exporter/data/responsum';
  36.  
  37.   the_loader_stg_id INTEGER := the_scheme_id+3;
  38.   the_loader_stg_name VARCHAR := 'LoaderStorage';
  39.   the_loader_stg_caption VARCHAR := 'Загрузка данных в БД Responsum_data';
  40.   the_loader_stg_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar';
  41.   the_loader_stg_host VARCHAR := '192.168.21.111';
  42.   the_loader_stg_port VARCHAR := '1433';
  43.   the_loader_stg_db_name VARCHAR := 'Responsum_data_test';
  44.   the_loader_stg_databus_url VARCHAR := 'jdbc:sqlserver://'||the_loader_stg_host||':'||the_loader_stg_port||';databaseName='||the_loader_stg_db_name||'';
  45.   the_loader_stg_databus_user VARCHAR := 'responsum_loader';
  46.   the_loader_stg_databus_pass VARCHAR := 's0wnV+xL1YQpql0Fe0xNLxgJsEHQjTAsnTWiFCtChfA=';
  47.   the_loader_stg_databus_pass_nc VARCHAR := '29sEU0QIAM71ClNdh0cA';
  48.  
  49.   the_loader_id INTEGER := the_scheme_id+6;
  50.   the_loader_name VARCHAR := 'LoaderUtil';
  51.   the_loader_caption VARCHAR := 'Загрузчик в служебную базу Responsum';
  52.   the_loader_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar';
  53.   the_loader_host VARCHAR := '192.168.21.111';
  54.   the_loader_port VARCHAR := '1433';
  55.   the_loader_db_name VARCHAR := 'Responsum_test';
  56.   the_loader_databus_url VARCHAR := 'jdbc:sqlserver://'||the_loader_host||':'||the_loader_port||';databaseName='||the_loader_db_name||'';
  57.   the_loader_databus_user VARCHAR := 'responsum_loader';
  58.   the_loader_databus_pass VARCHAR := 's0wnV+xL1YQpql0Fe0xNLxgJsEHQjTAsnTWiFCtChfA=';
  59.  
  60.   the_stopper_id INTEGER := the_scheme_id+5;
  61.   the_stopper_name VARCHAR := 'WorkStopper';
  62.   the_stopper_caption VARCHAR := 'Остановка Responsum';
  63.   the_stopper_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/start-stopper/config/log4j.properties -jar /opt/projects/responsum/start-stopper/start-stop.jar';
  64.  
  65.   the_starter_id INTEGER := the_scheme_id+8;
  66.   the_starter_name VARCHAR := 'WorkStarter';
  67.   the_starter_caption VARCHAR := 'Запуск Responsum';
  68.  
  69.   the_snapshotter_id INTEGER := the_scheme_id+7;
  70.   the_snapshotter_name VARCHAR := 'SnapshotReloader';
  71.   the_snapshotter_caption VARCHAR := 'Перегружает снапшот в Responsum Data';
  72.   the_snapshotter_object_file VARCHAR := 'java -Dlog4j.configuration=file:///opt/projects/responsum/snapshot-reloader/config/log4j.properties -jar /opt/projects/responsum/snapshot-reloader/snapshot-reloader.jar';
  73.   the_snapshotter_url VARCHAR := the_loader_stg_databus_url;
  74.   the_snapshotter_user VARCHAR := the_loader_stg_databus_user;
  75.   the_snapshotter_password VARCHAR := the_loader_stg_databus_pass_nc;
  76.  
  77.   the_dummy_end_handler_id          INTEGER := the_scheme_id+9;
  78.   the_dummy_end_handler_name        VARCHAR := 'DummyHandler-end';
  79.   the_dummy_end_handler_caption     VARCHAR := 'Dummy Handler';
  80.   the_dummy_end_handler_object_file VARCHAR := 'DummyHandler';
  81.  
  82.   -- Параметры FTP
  83.   the_ftp_host VARCHAR := '10.101.40.40';
  84.   the_exporter_ftp_user VARCHAR := 'uploadusr';
  85.   the_exporter_ftp_pass VARCHAR := 'exPo$51faVt#';
  86.  
  87.   the_loader_ftp_user VARCHAR := 'uploadusr';
  88.   the_loader_ftp_pass VARCHAR := 'GaujjvuaNZilegdgrCpyeg==';
  89.  
  90.   -- Параметры шины данных
  91.   the_exp_databus_host VARCHAR := '192.168.21.25';
  92.   the_exp_databus_port VARCHAR := '1433';
  93.   the_exp_databus_user VARCHAR := 'responsum_exporter';
  94.   the_exp_databus_pass VARCHAR := 'tb8yOOD7j96i2vvbbPdR';
  95.  
  96.  
  97. BEGIN
  98.   -- Добавляем схему и ноды
  99.   INSERT INTO core.scheme(scheme_id, name, caption, release_obj_file, workspaceid)
  100.   VALUES (the_scheme_id, the_scheme_name, the_scheme_caption, null, the_workspace_id);
  101.  
  102.   INSERT INTO core.scheme_node(scheme_id, node_id)
  103.   VALUES (the_scheme_id, the_node_id);
  104.   --
  105.  
  106. --   -- Добавляем описание параметров сеанса в этой схеме
  107. --   INSERT INTO core.param_key(param_key, param_type)
  108. --   VALUES ('responsum-loader.bundle_type', 'string'),
  109. --     ('responsum-loader.part_id', 'string'),
  110. --     ('responsum-loader.d', 'string');
  111. --
  112. --   INSERT INTO core.param_key_value(param_key, param_value)
  113. --   VALUES ('responsum-loader.bundle_type', '1'),
  114. --     ('responsum-loader.bundle_type', '2'),
  115. --     ('responsum-loader.bundle_type', '3');
  116.  
  117.   INSERT INTO core.scheme_param(scheme_id, param_name, param_key, required)
  118.   VALUES (the_scheme_id, 'responsum-loader.bundle_type', 'responsum-loader.bundle_type', true),
  119.     (the_scheme_id, 'responsum-loader.start_part_id', 'responsum-loader.part_id', true),
  120.     (the_scheme_id, 'responsum-loader.end_part_id', 'responsum-loader.part_id', true),
  121.     (the_scheme_id, 'responsum-loader.day', 'sessionData', false),
  122.     (the_scheme_id, 'responsum-loader.api.host', 'host', TRUE ),
  123.     (the_scheme_id, 'responsum-loader.stop.waiting.time-unit', 'time-unit', TRUE),
  124.     (the_scheme_id, 'responsum-loader.stop.waiting.timeout', 'timeout', TRUE);
  125.   --
  126.  
  127.   -- Добавляем обработчики
  128.   INSERT INTO core.handler(handler_id, scheme_id, name, caption, object_file, is_splitter, last_handler_id)
  129.   VALUES
  130.     -- Dummy
  131.     (the_dummy_handler_id, the_scheme_id, the_dummy_handler_name, 'Старт сеанса', 'DummyHandler', false, NULL),
  132.     -- Exporter Storage
  133.     (the_exporter_stg_id, the_scheme_id, the_exporter_stg_name, the_exporter_stg_caption, 'mono /opt/projects/responsum/exporter/Inet.Producer.Engine.Exporter.Pandaria.exe', false, NULL),
  134.     -- Loader Storage
  135.     (the_loader_stg_id, the_scheme_id, the_loader_stg_name, the_loader_stg_caption, 'java -Dlog4j.configuration=file:///opt/projects/responsum/loader/cfg/log4j.xml -Duser.dir=/opt/projects/responsum/loader -jar /opt/projects/responsum/loader/pandaria-loader.jar', false, NULL),
  136.     -- Exporter
  137.     (the_exporter_id, the_scheme_id, the_exporter_name, the_exporter_caption, 'mono /opt/projects/responsum/exporter/Inet.Producer.Engine.Exporter.Pandaria.exe', false, NULL),
  138.     -- Stopper
  139.     (the_stopper_id, the_scheme_id, the_stopper_name, the_stopper_caption, the_stopper_object_file, false, NULL),
  140.     -- Loader
  141.     (the_loader_id, the_scheme_id, the_loader_name, the_loader_caption, the_loader_object_file, false, NULL),
  142.     -- Snapshot Reloader
  143.     (the_snapshotter_id, the_scheme_id, the_snapshotter_name, the_snapshotter_caption, the_snapshotter_object_file, false, NULL),
  144.     -- Starter
  145.     (the_starter_id, the_scheme_id, the_starter_name, the_starter_caption, the_stopper_object_file, false, NULL),
  146.     -- END Dummy
  147.     (the_dummy_end_handler_id, the_scheme_id, the_dummy_end_handler_name, 'Конец сеанса', 'DummyHandler', false, NULL);
  148.  
  149.   INSERT INTO core.node_handler(node_id, handler_id)
  150.   VALUES (the_node_id, the_dummy_handler_id),
  151.     (the_node_id, the_exporter_stg_id),
  152.     (the_node_id, the_loader_stg_id),
  153.     (the_node_id, the_exporter_id),
  154.     (the_node_id, the_stopper_id),
  155.     (the_node_id, the_loader_id),
  156.     (the_node_id, the_snapshotter_id),
  157.     (the_node_id, the_starter_id),
  158.     (the_node_id, the_dummy_end_handler_id);
  159.  
  160.   INSERT INTO core.handler_graph(scheme_id, prev_handler_id, next_handler_id)
  161.   VALUES (the_scheme_id, the_dummy_handler_id, the_exporter_stg_id),
  162.     (the_scheme_id, the_exporter_stg_id, the_loader_stg_id),
  163.     (the_scheme_id, the_loader_stg_id, the_snapshotter_id),
  164.     (the_scheme_id, the_snapshotter_id, the_dummy_end_handler_id),
  165.     (the_scheme_id, the_dummy_handler_id, the_exporter_id),
  166.     (the_scheme_id, the_exporter_id, the_stopper_id),
  167.     (the_scheme_id, the_stopper_id, the_loader_id),
  168.     (the_scheme_id, the_loader_id, the_starter_id),
  169.     (the_scheme_id, the_starter_id, the_dummy_end_handler_id);
  170.   --
  171.  
  172.   -- Параметры конфигурации обработчиков
  173.   INSERT INTO core.handler_param(handler_id, param_key, param_val)
  174.   VALUES (the_exporter_stg_id,'cubeData', '{
  175.        "CubeParam": {
  176.        "PandariaExportType": "'||the_exporter_stg_export_type||'",
  177.        "FtpProxyHost": "",
  178.        "FtpUrl": "'||the_ftp_host||'",
  179.        "FtpUserName":"'||the_exporter_ftp_user||'",
  180.        "FtpPassword":"'||the_exporter_ftp_pass||'",
  181.        "FtpPort": 21,
  182.        "FtpPath": "'||the_exporter_stg_ftp_path||'"
  183.        },
  184.        "DataBusParam": {
  185.        "InetProducerConnectionString": "metadata=res://*/InetProducer.csdl|res://*/InetProducer.ssdl|res://*/InetProducer.msl;provider=System.Data.SqlClient;provider connection string=\"data source='||the_exp_databus_host||';initial catalog=InetProducer;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';Connection Timeout=300;MultipleActiveResultSets=True;App=EntityFramework\"",
  186.        "ImediaConnectionString": "Data Source='||the_exp_databus_host||'; Initial Catalog=IMedia;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';MultipleActiveResultSets=True;",
  187.        "PathWorking": "'||the_exporter_stg_path_working||'",
  188.        "CodePage": 1251
  189.        }
  190.       }'),
  191.     (the_loader_stg_id, 'home_path', '/opt/projects/responsum/loader'),
  192.     (the_loader_stg_id, 'loader_config', '{
  193.              "loader": {
  194.                "file-location": {
  195.                  "from-path": "/opt/projects/responsum/loader/data/responsum_data/from/",
  196.                  "to-path": "/opt/projects/responsum/loader/data/responsum_data/to/"
  197.                },
  198.                "file-encoding": "Cp1251",
  199.                "is-deleted": false,
  200.                "tables": [
  201.                  {
  202.                    "name": "daily",
  203.                    "parted": true,
  204.                    "schema": "prefact"
  205.                  },
  206.                  {
  207.                    "name": "demo",
  208.                    "parted": false,
  209.                    "schema": "prefact"
  210.                  },
  211.                  {
  212.                    "name": "media",
  213.                    "schema": "prefact",
  214.                    "parted": false
  215.                  },
  216.                  {
  217.                    "name": "weights_daily",
  218.                    "schema": "prefact",
  219.                    "parted": true
  220.                  },
  221.                  {
  222.                    "name": "fdaily",
  223.                    "schema": "prefact",
  224.                    "parted": true
  225.                  }
  226.                ],
  227.                "quartz-jobs": {
  228.                  "used": false,
  229.                  "properties-file-location": "./cfg/quartz.properties"
  230.                }
  231.              }
  232.            }'
  233.     ),
  234.     (the_loader_stg_id, 'ftp_config', '{
  235.          "ftp": {
  236.            "proxy": {
  237.              "host": "url-test-proxy",
  238.              "port": "3162",
  239.              "used": "false"
  240.            },
  241.            "server": {
  242.              "host": "'||the_ftp_host||'",
  243.              "port": "21",
  244.              "user": "'||the_loader_ftp_user||'",
  245.              "pass": "'||the_loader_ftp_pass||'",
  246.              "directory": "'||the_exporter_stg_ftp_path||'"
  247.            },
  248.            "used": "true"
  249.          }
  250.        }'
  251.     ),
  252.     (the_loader_stg_id, 'db_config', '{
  253.          "db": {
  254.            "url": "' || the_loader_stg_databus_url || '",
  255.            "profile-name": "Responsum",
  256.            "user": "'||the_loader_stg_databus_user||'",
  257.            "pass": "'||the_loader_stg_databus_pass||'",
  258.            "isNew": "false"
  259.          }
  260.        }'
  261.     ),
  262.     (the_loader_stg_id, 'mail_config', '{
  263.          "mail": {
  264.            "from": "webindex@web123.ru",
  265.            "host": "192.168.21.3",
  266.            "subject": "Загрузчик Pandoria.Responsum_data",
  267.            "to": "evgeny.utkin@mediascope.net",
  268.            "used": "false"
  269.          }
  270.        }'
  271.     ),
  272.     (the_exporter_id,'cubeData', '{
  273.          "CubeParam": {
  274.            "PandariaExportType": "'||the_exporter_export_type||'",
  275.            "FtpProxyHost": "",
  276.            "FtpUrl": "'||the_ftp_host||'",
  277.            "FtpUserName":"'||the_exporter_ftp_user||'",
  278.            "FtpPassword":"'||the_exporter_ftp_pass||'",
  279.            "FtpPort": 21,
  280.            "FtpPath": "'||the_exporter_ftp_path||'"
  281.          },
  282.          "DataBusParam": {
  283.            "InetProducerConnectionString": "metadata=res://*/InetProducer.csdl|res://*/InetProducer.ssdl|res://*/InetProducer.msl;provider=System.Data.SqlClient;provider connection string=\"data source='||the_exp_databus_host||';initial catalog=InetProducer;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';Connection Timeout=300;MultipleActiveResultSets=True;App=EntityFramework\"",
  284.            "ImediaConnectionString": "Data Source='||the_exp_databus_host||'; Initial Catalog=IMedia;User='||the_exp_databus_user||';Password='||the_exp_databus_pass||';MultipleActiveResultSets=True;",
  285.            "PathWorking": "'||the_exporter_path_working||'",
  286.            "CodePage": 1251
  287.          }
  288.        }'),
  289.     (the_stopper_id, 'login', 'zabbix'),
  290.     (the_stopper_id, 'password', 'zabbix'),
  291.     (the_stopper_id, 'url', '/responsum-info/zabbix/control'),
  292.     (the_stopper_id, 'isStart', false),
  293.     (the_loader_id, 'home_path', '/opt/projects/responsum/loader'),
  294.     (the_loader_id, 'loader_config', '{
  295.              "loader": {
  296.                "file-location": {
  297.                  "from-path": "/opt/projects/responsum/loader/data/responsum/from/",
  298.                  "to-path": "/opt/projects/responsum/loader/data/responsum/to/"
  299.                },
  300.                "file-encoding": "Cp1251",
  301.                "is-deleted": false,
  302.                "tables": [
  303.                  {
  304.                    "name": "holdings",
  305.                    "parted": false,
  306.                    "schema": "prefact"
  307.                  },
  308.                  {
  309.                    "name": "sites",
  310.                    "parted": false,
  311.                    "schema": "prefact"
  312.                  },
  313.                  {
  314.                    "name": "sections",
  315.                    "parted": false,
  316.                    "schema": "prefact"
  317.                  },
  318.                  {
  319.                    "name": "subsections",
  320.                    "parted": false,
  321.                    "schema": "prefact"
  322.                  },
  323.                  {
  324.                    "name": "variables",
  325.                    "parted": false
  326.                  },
  327.                  {
  328.                    "name": "var_categories",
  329.                    "parted": false
  330.                  }
  331.               ],
  332.                "quartz-jobs": {
  333.                  "used": false,
  334.                  "properties-file-location": "./cfg/quartz.properties"
  335.                }
  336.              }
  337.            }'
  338.     ),
  339.     (the_loader_id, 'ftp_config', '{
  340.         "ftp": {
  341.            "proxy": {
  342.              "host": "url-test-proxy",
  343.              "port": "3162",
  344.              "used": "false"
  345.            },
  346.            "server": {
  347.              "host": "'||the_ftp_host||'",
  348.              "port": "21",
  349.              "user": "'||the_loader_ftp_user||'",
  350.              "pass": "'||the_loader_ftp_pass||'",
  351.              "directory": "'||the_exporter_ftp_path||'"
  352.            },
  353.            "used": "true"
  354.          }
  355.        }'
  356.     ),
  357.     (the_loader_id, 'db_config', '{
  358.          "db": {
  359.            "url": "' || the_loader_databus_url || '",
  360.            "profile-name": "Responsum",
  361.            "user": "'||the_loader_databus_user||'",
  362.            "pass": "'||the_loader_databus_pass||'",
  363.            "isNew": "false"
  364.          }
  365.        }'
  366.     ),
  367.     (the_loader_id, 'mail_config', '{
  368.          "mail": {
  369.            "from": "webindex@web123.ru",
  370.            "host": "192.168.21.3",
  371.            "subject": "Загрузчик Pandoria.Responsum",
  372.            "to": "evgeny.utkin@mediascope.net",
  373.            "used": "false"
  374.          }
  375.        }'
  376.     ),
  377.     (the_snapshotter_id, 'user', the_snapshotter_user),
  378.     (the_snapshotter_id, 'password', the_snapshotter_password),
  379.     (the_snapshotter_id, 'url',the_snapshotter_url),
  380.     (the_snapshotter_id, 'procedure', 'snapshot Responsum_view_P rebuild'),
  381.     (the_starter_id, 'login', 'zabbix'),
  382.     (the_starter_id, 'password', 'zabbix'),
  383.     (the_starter_id, 'url', '/responsum-info/zabbix/control'),
  384.     (the_starter_id, 'isStart',  true);
  385.  
  386. END $$;
  387.  
  388. COMMIT;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!