inventory/src/main/java/org/opendaylight/transportpce/inventory/query/Queries.java

   1 /*
   2  * Copyright © 2016 AT&T and others.  All rights reserved.
   3  *
   4  * This program and the accompanying materials are made available under the
   5  * terms of the Eclipse Public License v1.0 which accompanies this distribution,
   6  * and is available at http://www.eclipse.org/legal/epl-v10.html
   7  */
   8
   9 package org.opendaylight.transportpce.inventory.query;
  10
  11 import com.google.common.base.Preconditions;
  12 import com.google.common.base.Strings;
  13
  14 public final class Queries {
  15
  16     private static final String DEVICE_INFO_INSERT;
  17
  18     private static final String DEVICE_SHELF_INSERT;
  19
  20     private static final String DEVICE_SHELF_SLOT_INSERT;
  21
  22     private static final String DEVICE_CP_INSERT;
  23
  24     private static final String DEVICE_CP_PORT_INSERT;
  25
  26     private static final String DEVICE_CP_SLOT_INSERT;
  27
  28     private static final String DEVICE_INTERFACES_INSERT;
  29
  30     private static final String DEVICE_INTERFACE_ODU_OPU_MIS_TX_INSERT;
  31
  32     private static final String DEVICE_INTERFACE_ODU_OPU_MIS_RX_INSERT;
  33
  34     private static final String DEVICE_INTERFACE_ODU_OPU_MIS_EXP_INSERT;
  35
  36     private static final String DEVICE_INTERFACE_TCM_INSERT;
  37
  38     private static final String DEVICE_PROTOCOL_INSERT;
  39
  40     private static final String DEVICE_PROTOCOL_PORT_CONFIG_INSERT;
  41
  42     private static final String DEVICE_PROTOCOL_LLDP_NBR_LIST_INSERT;
  43
  44     private static final String DEVICE_PROTOCOL_RSTP_INSERT;
  45
  46     private static final String DEVICE_PROTOCOL_RSTP_BRIDGEPORT_INSERT;
  47
  48     private static final String DEVICE_PROTOCOL_RSTP_BRIDGEPORT_ATTR_INSERT;
  49
  50     private static final String DEVICE_INTERNAL_LINK_INSERT;
  51
  52     private static final String DEVICE_EXTERNAL_LINK_INSERT;
  53
  54     private static final String DEVICE_PHYSICAL_LINK_INSERT;
  55
  56     private static final String DEVICE_DEGREE_INSERT;
  57
  58     private static final String DEVICE_DEGREE_CIRCUITPACK_INSERT;
  59
  60     private static final String DEVICE_CONNECTION_PORT_INSERT;
  61
  62     private static final String DEVICE_SHARED_RISK_GROUP_INSERT;
  63
  64     private static final String DEVICE_SRG_CIRCUITPACK_INSERT;
  65
  66     private static final String DEVICE_ROADM_CONNECTIONS_INSERT;
  67
  68     private static final String DEVICE_CONNECTION_MAP_INSERT;
  69
  70     private static final String DEVICE_WAVELENGTH_INSERT;
  71
  72     private static final String SERVICE_PATH_LIST;
  73
  74     private static final String SERVICE_UPDATE;
  75
  76     private static final String SERVICE_EVENT_INSERT;
  77
  78     private static final String SERVICE_INSERT;
  79
  80     private static final String SERVICE_HCON_CREATE;
  81
  82     private static final String SERVICE_HCON_DIVERSITY_CREATE;
  83
  84     private static final String SERVICE_HCON_DIVERSITY_CAPABILITY_CREATE;
  85
  86     private static final String SERVICE_HCON_COMPONENT_CREATE;
  87
  88     private static final String SERVICE_HCON_COROUTING_CREATE;
  89
  90     private static final String SERVICE_SCON_CREATE;
  91
  92     private static final String SERVICE_SCON_DIVERSITY_CREATE;
  93
  94     private static final String SERVICE_SCON_DIVERSITY_CAPABILITY_CREATE;
  95
  96     private static final String SERVICE_SCON_COMPONENT_CREATE;
  97
  98     private static final String SERVICE_SCON_COROUTING_CREATE;
  99
 100     private static final String SERVICE_FIBERSPAN_SRLG_CREATE;
 101
 102     private static final String SERVICE_EQUIPMENT_SRG_CREATE;
 103
 104     private static final String SERVICE_SUPPORTING_SVC_CREATE;
 105
 106     private static final String SERVICE_DELETE;
 107
 108     static {
 109
 110         DEVICE_INFO_INSERT =
 111             "INSERT INTO %sinv_dev_info (node_id, node_number, node_type, clli, vendor, model, serial_id, ipAddress, "
 112             + " prefix_length, default_gateway, "
 113             + "source, current_ipAddress, current_prefix_length, current_default_gateway, macAddress, "
 114             + "software_version, openroadm_version, "
 115             + "template, current_datetime, geo_latitude, geo_longitude, max_degrees, max_srgs, "
 116             + "max_num_bin_15min_historical_pm, "
 117             + "max_num_bin_24hour_historical_pm, sw_version, sw_validation_timer, activation_date_time, create_date, "
 118             + "update_date) VALUES "
 119             + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 120
 121         DEVICE_SHELF_INSERT =
 122             "INSERT INTO %sinv_dev_shelf"
 123             + " (node_id, shelf_name, shelf_type, rack, shelf_position, administrative_state, vendor, model, "
 124             + "serial_id, type, product_code, manufacture_date, clei, hardware_version, operational_state, "
 125             + "equipment_state, due_date, "
 126             + "create_date, update_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 127
 128         DEVICE_SHELF_SLOT_INSERT =
 129             "INSERT INTO %sinv_dev_shelf_slot "
 130             + " (node_id, shelf_name, slot_name, label, provisioned_circuit_pack, slot_status, create_date, "
 131             + "update_date)  "
 132             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 133
 134         DEVICE_CP_INSERT =
 135             "INSERT INTO %sinv_dev_circuit_pack"
 136             + " (node_id, circuit_pack_name, circuit_pack_type, circuit_pack_product_code, administrative_state, "
 137             + "vendor, model, serial_id, type, "
 138             + " product_code, manufacture_date, clei, hardware_version, operational_state, cpc_type, cpc_extension, "
 139             + "equipment_state, circuit_pack_mode, shelf, slot, "
 140             + " subSlot, due_date, pcp_circuit_pack_name, pcp_cp_slot_name, create_date, update_date) "
 141             + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 142
 143
 144         DEVICE_CP_PORT_INSERT =
 145             "INSERT INTO %sinv_dev_cp_ports "
 146             + " (node_id, circuit_pack_name, port_name, port_type, port_qual, port_wavelength_type, port_direction, "
 147             + "label, circuit_id, "
 148             + "administrative_state, operational_state, logical_connection_point, partner_port_circuit_pack_name, "
 149             + "partner_port_port_name, parent_port_circuit_pack_name, "
 150             + "parent_port_port_name, roadm_port_port_power_capability_min_rx, "
 151             + "roadm_port_port_power_capability_min_tx, roadm_port_port_power_capability_max_rx, "
 152             + "roadm_port_port_power_capability_max_tx, roadm_port_capable_wavelengths, "
 153             + "roadm_port_available_wavelengths, roadm_port_used_wavelengths, "
 154             + "transponder_port_port_power_capability_min_rx, transponder_port_port_power_capability_min_tx, "
 155             + "transponder_port_port_power_capability_max_rx, "
 156             + "transponder_port_port_power_capability_max_tx, transponder_port_capable_wavelengths, "
 157             + "otdr_port_launch_cable_length, otdr_port_port_direction, "
 158             + "ila_port_port_power_capability_mix_rx, ila_port_port_power_capability_mix_tx, "
 159             + "ila_port_port_power_capability_max_rx, ila_port_port_power_capability_max_tx, "
 160             + "create_date, update_date)  "
 161             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
 162             + "?, ?, ?, ?)";
 163
 164         DEVICE_CP_SLOT_INSERT =
 165             "INSERT INTO %sinv_dev_cp_slots "
 166             + " (node_id, circuit_pack_name, slot_name, label, provisioned_circuit_pack, slot_status, slot_type, "
 167             + "create_date, update_date)  "
 168             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 169
 170         DEVICE_INTERFACES_INSERT =
 171             "INSERT INTO %sinv_dev_interfaces "
 172             + " (node_id, name, description, type, administrative_state, operational_state, circuit_id, "
 173             + "supporting_interface, supporting_circuit_pack_name, "
 174             + "supporting_port, ethernet_speed, ethernet_fec, ethernet_duplex, ethernet_mtu, "
 175             + "ethernet_auto_negotiation, ethernet_curr_speed, "
 176             + "ethernet_curr_duplex, mci_mcttp_min_freq, mci_mcttp_max_freq, mci_mcttp_center_freq, "
 177             + "mci_mcttp_slot_width, mci_nmc_ctp_frequency, mci_nmc_ctp_width, "
 178             + "och_rate, och_frequency, och_width, och_wavelength_number, och_modulation_format, och_transmit_power, "
 179             + "ots_fiber_type, ots_span_loss_receive, "
 180             + "ots_span_loss_transmit, ots_ingress_span_loss_aging_margin, ots_eol_max_load_pin, odu_rate, "
 181             + "odu_function, odu_monitoring_mode, odu_no_oam_function, "
 182             + "odu_proactive_delay_measurement_enabled, odu_poa_trib_port_number, odu_tx_sapi, odu_tx_dapi, "
 183             + "odu_tx_operator, odu_accepted_sapi, odu_accepted_dapi, "
 184             + "odu_accepted_operator, odu_expected_sapi, odu_expected_dapi, odu_tim_act_enabled, odu_tim_detect_mode, "
 185             + "odu_degm_intervals, odu_degthr_percentage, "
 186             + "opu_payload_type, opu_rx_payload_type, opu_exp_payload_type, opu_payload_interface, "
 187             + "maint_testsignal_enabled, maint_testsignal_testpattern, "
 188             + "maint_testsignal_type, maint_testsignal_biterrors, maint_testsignal_biterrorsterminal, "
 189             + "maint_testsignal_syncseconds, "
 190             + "maint_testsignal_syncsecondsterminal, otu_rate, otu_fec, otu_tx_sapi, otu_tx_dapi, otu_tx_operator, "
 191             + "otu_accepted_sapi, otu_accepted_dapi, "
 192             + "otu_accepted_operator, otu_expected_sapi, otu_expected_dapi, otu_tim_act_enabled, otu_tim_detect_mode, "
 193             + "otu_degm_intervals, otu_degthr_percentage, "
 194             + "otu_maint_loopback_enabled, otu_maint_type, mt_otu_rate, mt_otu_fec, mt_otu_maint_loopback, "
 195             + "mt_otu_enabled, mt_otu_type, create_date, update_date)  "
 196             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
 197             + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
 198             + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
 199             + "?, ?)";
 200
 201         DEVICE_INTERFACE_ODU_OPU_MIS_TX_INSERT =
 202             "INSERT INTO %sinv_dev_interface_odu_opu_tx_msi "
 203             + " (node_id, interface_name, trib_slot, odtu_type, trib_port, trib_port_payload, create_date, "
 204             + "update_date )  "
 205             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 206
 207         DEVICE_INTERFACE_ODU_OPU_MIS_RX_INSERT =
 208             "INSERT INTO %sinv_dev_interface_odu_opu_rx_msi "
 209             + " (node_id, interface_name, trib_slot, odtu_type, trib_port, trib_port_payload, create_date, "
 210             + "update_date )  "
 211             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 212
 213         DEVICE_INTERFACE_ODU_OPU_MIS_EXP_INSERT =
 214             "INSERT INTO %sinv_dev_interface_odu_opu_exp_msi "
 215             + " (node_id, interface_name, trib_slot, odtu_type, trib_port, trib_port_payload, create_date, "
 216             + "update_date )  "
 217             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 218
 219         DEVICE_INTERFACE_TCM_INSERT =
 220             "INSERT INTO %sinv_dev_interface_odu_tcm "
 221             + " (node_id, interface_name, layer, monitoring_mode, ltc_act_enabled, "
 222             + "proactive_delay_measurement_enabled, tcm_direction, tx_sapi, tx_dapi, tx_operator, accepted_sapi, "
 223             + "accepted_dapi, accepted_operator, expected_sapi, expected_dapi, tim_act_enabled, tim_detect_mode, "
 224             + "degm_intervals, degthr_percentage, create_date, update_date )  "
 225             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
 226
 227         DEVICE_PROTOCOL_INSERT =
 228             "INSERT INTO %sinv_dev_proto_lldp"
 229                 + "(node_id,"
 230                 + "adminstatus,"
 231                 + "msgtxinterval,"
 232                 + "msgtxholdmultiplier,"
 233                 + "create_date,"
 234                 + "update_date )"
 235                 + "values ( ?,"
 236                 + "?,"
 237                 + "?,"
 238                 + "?,"
 239                 + "?,"
 240                 + "? )";
 241
 242         DEVICE_PROTOCOL_PORT_CONFIG_INSERT =
 243             "INSERT INTO %sinv_dev_proto_lldp_port_config "
 244             + " (node_id, ifname, adminstatus, create_date, update_date )  "
 245             + "VALUES (?, ?, ?, ?, ? )";
 246
 247
 248         DEVICE_PROTOCOL_LLDP_NBR_LIST_INSERT =
 249             "INSERT INTO %sinv_dev_proto_lldp_nbr_lst "
 250             + " (node_id, ifname, remotesysname, remotemgmtaddresssubtype, remotemgmtaddress, remoteportidsubtype, "
 251             + "remoteportid, remotechassisidsubtype, remotechassisid, create_date, update_date )  "
 252             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 253
 254
 255         DEVICE_PROTOCOL_RSTP_INSERT =
 256             "INSERT INTO %sinv_dev_proto_rstp "
 257             + " (node_id, bridge_name, bridge_priority, shutdown, hold_time, hello_time, max_age, forward_delay, "
 258             + "transmit_hold_count, "
 259             + "root_bridge_port, root_path_cost, root_bridge_priority, root_bridge_id, root_hold_time, "
 260             + "root_hello_time, root_max_age, "
 261             + "root_forward_delay, bridge_id, topo_change_count, time_since_topo_change, create_date, update_date )  "
 262             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 263
 264         DEVICE_PROTOCOL_RSTP_BRIDGEPORT_INSERT =
 265             "INSERT INTO %sinv_dev_proto_rstp_bridge_port "
 266             + " (node_id, bridge_name, ifname, cost, priority, create_date, update_date )  "
 267             + "VALUES (?, ?, ?, ?, ?, ?, ?)";
 268
 269         DEVICE_PROTOCOL_RSTP_BRIDGEPORT_ATTR_INSERT =
 270             "INSERT INTO %sinv_dev_proto_rstp_bridge_port_attr "
 271             + " (node_id, bridge_name, ifname, bridge_port_state, bridge_port_role, bridge_port_id, "
 272             + "open_edge_bridge_port, designated_bridge_port, designated_bridgeid, create_date, update_date  )  "
 273             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 274
 275         DEVICE_INTERNAL_LINK_INSERT =
 276             "INSERT INTO %sinv_dev_internal_link "
 277             + " (node_id, internal_link_name, source_circuit_pack_name, source_port_name, "
 278             + "destination_circuit_pack_name, destination_port_name, create_date, update_date )  "
 279             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 280
 281         DEVICE_EXTERNAL_LINK_INSERT =
 282             "INSERT INTO %sinv_dev_external_link "
 283             + " (node_id, external_link_name, source_node_id, source_circuit_pack_name, source_port_name, "
 284             + "destination_node_id, destination_circuit_pack_name, destination_port_name, create_date, update_date)  "
 285             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 286
 287         DEVICE_PHYSICAL_LINK_INSERT =
 288             "INSERT INTO %sinv_dev_physical_link "
 289             + " (node_id, physical_link_name, source_circuit_pack_name, source_port_name, "
 290             + "destination_circuit_pack_name, destination_port_name, create_date, update_date )  "
 291             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 292
 293         DEVICE_DEGREE_INSERT =
 294             "INSERT INTO %sinv_dev_degree "
 295             + " (node_id, degree_number, max_wavelengths, otdr_port_circuit_pack_name, otdr_port_port_name, "
 296             + "mc_capabilities_slot_width_granularity, mc_capabilities_center_freq_granularity, "
 297             + "mc_capabilities_min_slots, mc_capabilities_max_slots, create_date, update_date )  "
 298             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 299
 300         DEVICE_DEGREE_CIRCUITPACK_INSERT =
 301             "INSERT INTO %sinv_dev_degree_circuit_packs "
 302             + " (node_id, degree_number, cktpk_index, circuit_pack_name, create_date, update_date )  "
 303             + "VALUES (?, ?, ?, ?, ?, ?)";
 304
 305         DEVICE_CONNECTION_PORT_INSERT =
 306             "INSERT INTO %sinv_dev_degree_connection_ports "
 307             + " (node_id, degree_number, conn_port_index, circuit_pack_name, port_name, create_date, update_date )  "
 308             + "VALUES (?, ?, ?, ?, ?, ?, ?)";
 309
 310         DEVICE_SHARED_RISK_GROUP_INSERT =
 311             "INSERT INTO %sinv_dev_srg "
 312             + " (node_id, max_add_drop_ports, current_provisioned_add_drop_ports, srg_number, wavelength_duplication, "
 313             + "mc_cap_slot_width_granularity, mc_cap_center_freq_granularity, mc_cap_min_slots, mc_cap_max_slots, "
 314             + "create_date, update_date )  "
 315             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 316
 317         DEVICE_SRG_CIRCUITPACK_INSERT =
 318             "INSERT INTO %sinv_dev_srg_circuit_pack "
 319             + " (node_id, srg_number, ckptk_index, circuit_pack_name, create_date, update_date )  "
 320             + "VALUES (?, ?, ?, ?, ?, ?)";
 321
 322         DEVICE_ROADM_CONNECTIONS_INSERT =
 323             "INSERT INTO %sinv_dev_roadm_connections "
 324             + " (node_id, connection_name, connection_number, wavelength_number, opticalcontrolmode, "
 325             + "target_output_power, src_if, dst_if, create_date, update_date  )  "
 326             + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 327
 328         DEVICE_CONNECTION_MAP_INSERT =
 329             "INSERT INTO %sinv_dev_connection_map "
 330             + " (node_id, connection_map_number, source_circuit_pack_name, source_port_name, create_date, "
 331             + "update_date )  "
 332             + "VALUES (?, ?, ?, ?, ?, ?)";
 333
 334         DEVICE_WAVELENGTH_INSERT = "INSERT INTO %sinv_dev_wavelength_map "
 335             + " (node_id, wavelength_number, center_frequency, wavelength, create_date, update_date )  "
 336             + "VALUES (?, ?, ?, ?, ?, ?)";
 337
 338         SERVICE_PATH_LIST =
 339             "INSERT INTO %sinv_ser_pathlist ( path_name ) values (?)";
 340
 341         SERVICE_UPDATE =
 342             "UPDATE %sinv_ser_main "
 343             + "set "
 344             + "common_id = ? "
 345             + "sdnc_req_header_request_id = ? "
 346             + "sdnc_req_header_rpc_action = ? "
 347             + "sdnc_req_header_notification_url = ? "
 348             + "sdnc_req_header_request_system_id = ? "
 349             + "connection_type = ? "
 350             + "lifecycle_state = ? "
 351             + "administrative_state = ? "
 352             + "operational_state = ? "
 353             + "serv_condition = ? "
 354             + "a_end_service_format = ? "
 355             + "a_end_service_rate = ? "
 356             + "a_end_clli = ? "
 357             + "a_end_node_id = ? "
 358             + "a_end_tx_port_device_name = ? "
 359             + "a_end_tx_port_type = ? "
 360             + "a_end_tx_port_name = ? "
 361             + "a_end_tx_port_rack = ? "
 362             + "a_end_tx_port_shelf = ? "
 363             + "a_end_tx_port_slot = ? "
 364             + "a_end_tx_port_sub_slot = ? "
 365             + "a_end_tx_lgx_device_name = ? "
 366             + "a_end_tx_lgx_port_name = ? "
 367             + "a_end_tx_lgx_port_rack = ? "
 368             + "a_end_tx_lgx_port_shelf = ? "
 369             + "a_end_tx_tail_rnode_id = ? "
 370             + "a_end_tx_xport_cp_name = ? "
 371             + "a_end_tx_xport_port_name = ? "
 372             + "a_end_tx_tail_roadm_port_aid = ? "
 373             + "a_end_tx_tail_roadm_port_rack_loc = ? "
 374             + "a_end_rx_port_device_name = ? "
 375             + "a_end_rx_port_type = ? "
 376             + "a_end_rx_port_name = ? "
 377             + "a_end_rx_port_rack = ? "
 378             + "a_end_rx_port_shelf = ? "
 379             + "a_end_rx_port_slot = ? "
 380             + "a_end_rx_port_sub_slot = ? "
 381             + "a_end_rx_lgx_device_name = ? "
 382             + "a_end_rx_lgx_port_name = ? "
 383             + "a_end_rx_lgx_port_rack = ? "
 384             + "a_end_rx_lgx_port_shelf = ? "
 385             + "a_end_rx_tail_rnode_id = ? "
 386             + "a_end_rx_xport_cp_name = ? "
 387             + "a_end_rx_xport_port_name = ? "
 388             + "a_end_rx_tail_roadm_port_aid = ? "
 389             + "a_end_rx_tail_roadm_port_rack_loc = ? "
 390             + "a_end_optic_type = ? "
 391             + "a_end_router_node_id = ? "
 392             + "a_end_router_ip_address = ? "
 393             + "a_end_router_url = ? "
 394             + "a_end_user_label = ? "
 395             + "z_end_service_format = ? "
 396             + "z_end_service_rate = ? "
 397             + "z_end_clli = ? "
 398             + "z_end_node_id = ? "
 399             + "z_end_tx_port_device_name = ? "
 400             + "z_end_tx_port_type = ? "
 401             + "z_end_tx_port_name = ? "
 402             + "z_end_tx_port_rack = ? "
 403             + "z_end_tx_port_shelf = ? "
 404             + "z_end_tx_port_slot = ? "
 405             + "z_end_tx_port_sub_slot = ? "
 406             + "z_end_tx_lgx_device_name = ? "
 407             + "z_end_tx_lgx_port_name = ? "
 408             + "z_end_tx_lgx_port_rack = ? "
 409             + "z_end_tx_lgx_port_shelf = ? "
 410             + "z_end_tx_tail_rnode_id = ? "
 411             + "z_end_tx_xport_cp_name = ? "
 412             + "z_end_tx_xport_port_name = ? "
 413             + "z_end_tx_tail_roadm_port_aid = ? "
 414             + "z_end_tx_tail_roadm_port_rack_loc = ? "
 415             + "z_end_rx_port_device_name = ? "
 416             + "z_end_rx_port_type = ? "
 417             + "z_end_rx_port_name = ? "
 418             + "z_end_rx_port_rack = ? "
 419             + "z_end_rx_port_shelf = ? "
 420             + "z_end_rx_port_slot = ? "
 421             + "z_end_rx_port_sub_slot = ? "
 422             + "z_end_rx_lgx_device_name = ? "
 423             + "z_end_rx_lgx_port_name = ? "
 424             + "z_end_rx_lgx_port_rack = ? "
 425             + "z_end_rx_lgx_port_shelf = ? "
 426             + "z_end_rx_tail_rnode_id = ? "
 427             + "z_end_rx_xport_cp_name = ? "
 428             + "z_end_rx_xport_port_name = ? "
 429             + "z_end_rx_tail_roadm_port_aid = ? "
 430             + "z_end_rx_tail_roadm_port_rack_loc = ? "
 431             + "z_end_optic_type = ? "
 432             + "z_end_router_node_id = ? "
 433             + "z_end_router_ip_address = ? "
 434             + "z_end_router_url = ? "
 435             + "z_end_user_label "
 436             + "due_date = ? "
 437             + "end_date = ? "
 438             + "nc_code = ? "
 439             + "nci_code = ? "
 440             + "secondary_nci_code = ? "
 441             + "customer = ? "
 442             + "customer_contact = ? "
 443             + "operator_contact = ? "
 444             + "latency = ? "
 445             + "fiber_span_srlgs = ? "
 446             + "supp_serv_name = ? "
 447             + "update_date"
 448             + " where service_name = ? ";
 449
 450         SERVICE_EVENT_INSERT =
 451             "INSERT INTO %sinv_ser_events"
 452             + "(event,"
 453             + "event_key,"
 454             + "event_value,"
 455             + "change_key,"
 456             + "change_orig_val,"
 457             + "change_new_val,"
 458             + "event_date )"
 459             + "values ( ?"
 460             + "?"
 461             + "?"
 462             + "?"
 463             + "?"
 464             + "?"
 465             + "? )";
 466
 467         SERVICE_INSERT =
 468             "INSERT INTO %sinv_ser_main "
 469                     + "( service_name, "
 470                     + "common_id, "
 471                     + "sdnc_req_header_request_id, "
 472                     + "sdnc_req_header_rpc_action, "
 473                     + "sdnc_req_header_notification_url, "
 474                     + "sdnc_req_header_request_system_id, "
 475                     + "connection_type, "
 476                     + "lifecycle_state, "
 477                     + "administrative_state, "
 478                     + "operational_state, "
 479                     + "serv_condition, "
 480                     + "a_end_service_format, "
 481                     + "a_end_service_rate, "
 482                     + "a_end_clli, "
 483                     + "a_end_node_id, "
 484                     + "a_end_tx_port_device_name, "
 485                     + "a_end_tx_port_type, "
 486                     + "a_end_tx_port_name, "
 487                     + "a_end_tx_port_rack, "
 488                     + "a_end_tx_port_shelf, "
 489                     + "a_end_tx_port_slot, "
 490                     + "a_end_tx_port_sub_slot, "
 491                     + "a_end_tx_lgx_device_name, "
 492                     + "a_end_tx_lgx_port_name, "
 493                     + "a_end_tx_lgx_port_rack, "
 494                     + "a_end_tx_lgx_port_shelf, "
 495                     + "a_end_tx_tail_rnode_id, "
 496                     + "a_end_tx_xport_cp_name, "
 497                     + "a_end_tx_xport_port_name, "
 498                     + "a_end_tx_tail_roadm_port_aid, "
 499                     + "a_end_tx_tail_roadm_port_rack_loc, "
 500                     + "a_end_rx_port_device_name, "
 501                     + "a_end_rx_port_type, "
 502                     + "a_end_rx_port_name, "
 503                     + "a_end_rx_port_rack, "
 504                     + "a_end_rx_port_shelf, "
 505                     + "a_end_rx_port_slot, "
 506                     + "a_end_rx_port_sub_slot, "
 507                     + "a_end_rx_lgx_device_name, "
 508                     + "a_end_rx_lgx_port_name, "
 509                     + "a_end_rx_lgx_port_rack, "
 510                     + "a_end_rx_lgx_port_shelf, "
 511                     + "a_end_rx_tail_rnode_id, "
 512                     + "a_end_rx_xport_cp_name, "
 513                     + "a_end_rx_xport_port_name, "
 514                     + "a_end_rx_tail_roadm_port_aid, "
 515                     + "a_end_rx_tail_roadm_port_rack_loc, "
 516                     + "a_end_optic_type, "
 517                     + "a_end_router_node_id, "
 518                     + "a_end_router_ip_address, "
 519                     + "a_end_router_url, "
 520                     + "a_end_user_label, "
 521                     + "z_end_service_format, "
 522                     + "z_end_service_rate, "
 523                     + "z_end_clli, "
 524                     + "z_end_node_id, "
 525                     + "z_end_tx_port_device_name, "
 526                     + "z_end_tx_port_type, "
 527                     + "z_end_tx_port_name, "
 528                     + "z_end_tx_port_rack, "
 529                     + "z_end_tx_port_shelf, "
 530                     + "z_end_tx_port_slot, "
 531                     + "z_end_tx_port_sub_slot, "
 532                     + "z_end_tx_lgx_device_name, "
 533                     + "z_end_tx_lgx_port_name, "
 534                     + "z_end_tx_lgx_port_rack, "
 535                     + "z_end_tx_lgx_port_shelf, "
 536                     + "z_end_tx_tail_rnode_id, "
 537                     + "z_end_tx_xport_cp_name, "
 538                     + "z_end_tx_xport_port_name, "
 539                     + "z_end_tx_tail_roadm_port_aid, "
 540                     + "z_end_tx_tail_roadm_port_rack_loc, "
 541                     + "z_end_rx_port_device_name, "
 542                     + "z_end_rx_port_type, "
 543                     + "z_end_rx_port_name, "
 544                     + "z_end_rx_port_rack, "
 545                     + "z_end_rx_port_shelf, "
 546                     + "z_end_rx_port_slot, "
 547                     + "z_end_rx_port_sub_slot, "
 548                     + "z_end_rx_lgx_device_name, "
 549                     + "z_end_rx_lgx_port_name, "
 550                     + "z_end_rx_lgx_port_rack, "
 551                     + "z_end_rx_lgx_port_shelf, "
 552                     + "z_end_rx_tail_rnode_id, "
 553                     + "z_end_rx_xport_cp_name, "
 554                     + "z_end_rx_xport_port_name, "
 555                     + "z_end_rx_tail_roadm_port_aid, "
 556                     + "z_end_rx_tail_roadm_port_rack_loc, "
 557                     + "z_end_optic_type, "
 558                     + "z_end_router_node_id, "
 559                     + "z_end_router_ip_address, "
 560                     + "z_end_router_url, "
 561                     + "z_end_user_label, "
 562                     /*+ "hcon_cust_code, "
 563                     + "hcon_gen_div_existing_serv, "
 564                     + "hcon_gen_div_app_site, "
 565                     + "hcon_gen_div_app_node, "
 566                     + "hcon_gen_div_app_srlg, "
 567                     + "hcon_gen_excl_fiber_bundle, "
 568                     + "hcon_gen_excl_site, "
 569                     + "hcon_gen_excl_node_id, "
 570                     + "hcon_gen_excl_supp_serv_name, "
 571                     + "hcon_gen_incl_fiber_bundle, "
 572                     + "hcon_gen_incl_site, "
 573                     + "hcon_gen_incl_node_id, "
 574                     + "hcon_gen_incl_supp_serv_name, "
 575                     + "hcon_gen_max_latency, "
 576                     + "hcon_corout_existing_serv, "
 577                     + "scon_cust_code, "
 578                     + "scon_gen_div_existing_serv, "
 579                     + "scon_gen_div_app_site, "
 580                     + "scon_gen_div_app_node, "
 581                     + "scon_gen_div_app_srlg, "
 582                     + "scon_gen_excl_fiber_bundle, "
 583                     + "scon_gen_excl_site, "
 584                     + "scon_gen_excl_node_id, "
 585                     + "scon_gen_excl_supp_serv_name, "
 586                     + "scon_gen_incl_fiber_bundle, "
 587                     + "scon_gen_incl_site, "
 588                     + "scon_gen_incl_node_id, "
 589                     + "scon_gen_incl_supp_serv_name, "
 590                     + "scon_gen_max_latency, "
 591                     + "scon_corout_existing_serv, " */
 592                     + "due_date, "
 593                     + "end_date, "
 594                     + "nc_code, "
 595                     + "nci_code, "
 596                     + "secondary_nci_code, "
 597                     + "customer, "
 598                     + "customer_contact, "
 599                     + "operator_contact, "
 600                     + "latency, "
 601                     + "fiber_span_srlgs, "
 602                     + "supp_serv_name, "
 603                     + "create_date, "
 604                     + "update_date) "
 605                     + "values ( "
 606                     /*+ "?, "
 607                     + "?, "
 608                     + "?, "
 609                     + "?, "
 610                     + "?, "
 611                     + "?, "
 612                     + "?, "
 613                     + "?, "
 614                     + "?, "
 615                     + "?, "
 616                     + "?, "
 617                     + "?, "
 618                     + "?, "
 619                     + "?, "
 620                     + "?, "
 621                     + "?, "
 622                     + "?, "
 623                     + "?, "
 624                     + "?, "
 625                     + "?, "
 626                     + "?, "
 627                     + "?, "
 628                     + "?, "
 629                     + "?, "
 630                     + "?, "
 631                     + "?, "
 632                     + "?, "
 633                     + "?, "
 634                     + "?, "
 635                     + "?, " */
 636                     + "?, "
 637                     + "?, "
 638                     + "?, "
 639                     + "?, "
 640                     + "?, "
 641                     + "?, "
 642                     + "?, "
 643                     + "?, "
 644                     + "?, "
 645                     + "?, "
 646                     + "?, "
 647                     + "?, "
 648                     + "?, "
 649                     + "?, "
 650                     + "?, "
 651                     + "?, "
 652                     + "?, "
 653                     + "?, "
 654                     + "?, "
 655                     + "?, "
 656                     + "?, "
 657                     + "?, "
 658                     + "?, "
 659                     + "?, "
 660                     + "?, "
 661                     + "?, "
 662                     + "?, "
 663                     + "?, "
 664                     + "?, "
 665                     + "?, "
 666                     + "?, "
 667                     + "?, "
 668                     + "?, "
 669                     + "?, "
 670                     + "?, "
 671                     + "?, "
 672                     + "?, "
 673                     + "?, "
 674                     + "?, "
 675                     + "?, "
 676                     + "?, "
 677                     + "?, "
 678                     + "?, "
 679                     + "?, "
 680                     + "?, "
 681                     + "?, "
 682                     + "?, "
 683                     + "?, "
 684                     + "?, "
 685                     + "?, "
 686                     + "?, "
 687                     + "?, "
 688                     + "?, "
 689                     + "?, "
 690                     + "?, "
 691                     + "?, "
 692                     + "?, "
 693                     + "?, "
 694                     + "?, "
 695                     + "?, "
 696                     + "?, "
 697                     + "?, "
 698                     + "?, "
 699                     + "?, "
 700                     + "?, "
 701                     + "?, "
 702                     + "?, "
 703                     + "?, "
 704                     + "?, "
 705                     + "?, "
 706                     + "?, "
 707                     + "?, "
 708                     + "?, "
 709                     + "?, "
 710                     + "?, "
 711                     + "?, "
 712                     + "?, "
 713                     + "?, "
 714                     + "?, "
 715                     + "?, "
 716                     + "?, "
 717                     + "?, "
 718                     + "?, "
 719                     + "?, "
 720                     + "?, "
 721                     + "?, "
 722                     + "?, "
 723                     + "?, "
 724                     + "?, "
 725                     + "?, "
 726                     + "?, "
 727                     + "?, "
 728                     + "?, "
 729                     + "?, "
 730                     + "?, "
 731                     + "?, "
 732                     + "?, "
 733                     + "?, "
 734                     + "?, "
 735                     + "?, "
 736                     + "?, "
 737                     + "?, "
 738                     + "?, "
 739                     + "?, "
 740                     + "?, "
 741                     + "?) ";
 742
 743         SERVICE_HCON_CREATE =
 744             "insert into @inv_ser_hcon (service_name, customer_code, create_date, update_date)"
 745             + "values (?, ?, ?, ?) ";
 746
 747         SERVICE_HCON_DIVERSITY_CREATE =
 748             "insert into @inv_ser_hcon_diversity_service (service_name, existing_service, create_date, update_date)"
 749             + "values (?, ?, ?, ?) ";
 750
 751         SERVICE_HCON_DIVERSITY_CAPABILITY_CREATE =
 752             "insert into @inv_ser_hcon_diversity_service_capability (service_name, site, node, srlg, create_date, "
 753             + "update_date)"
 754             + "values (?, ?, ?, ?, ?) ";
 755
 756         SERVICE_HCON_COMPONENT_CREATE =
 757             "insert into @inv_ser_hcon_components (service_name, application, component, value, create_date, "
 758             + "update_date)"
 759             + "values (?, ?, ?, ?, ?, ?) ";
 760
 761         SERVICE_HCON_COROUTING_CREATE =
 762             "insert into @inv_ser_hcon_corouting (service_name, existing_service, create_date, update_date)"
 763             + "values (?, ?, ?, ?) ";
 764
 765         SERVICE_SCON_CREATE =
 766             "insert into @inv_ser_scon (service_name, customer_code, create_date, update_date)"
 767             + "values (?, ?, ?, ?) ";
 768
 769         SERVICE_SCON_DIVERSITY_CREATE =
 770             "insert into @inv_ser_scon_diversity_service (service_name, existing_service, create_date, update_date)"
 771             + "values (?, ?, ?, ?) ";
 772
 773         SERVICE_SCON_DIVERSITY_CAPABILITY_CREATE =
 774             "insert into @inv_ser_scon_diversity_service_capability (service_name, site, node, srlg, create_date, "
 775             + "update_date)"
 776             + "values (?, ?, ?, ?, ?) ";
 777
 778         SERVICE_SCON_COMPONENT_CREATE =
 779             "insert into @inv_ser_scon_components (service_name, application, component, value, create_date, "
 780             + "update_date)"
 781             + "values (?, ?, ?, ?, ?, ?) ";
 782
 783         SERVICE_SCON_COROUTING_CREATE =
 784             "insert into @inv_ser_scon_corouting (service_name, existing_service, create_date, update_date)"
 785             + "values (?, ?, ?, ?) ";
 786
 787         SERVICE_FIBERSPAN_SRLG_CREATE =
 788             "insert into inv_ser_fiber_span_srlgs values (service_name, fiber_span_srlg, create_date, update_date) "
 789             + "values(?, ?, ?, ?)";
 790
 791         SERVICE_EQUIPMENT_SRG_CREATE =
 792             "insert into inv_ser_equipment_srgs values (service_name, srg_number, create_date, update_date) "
 793             + "values(?, ?, ?, ?)";
 794
 795         SERVICE_SUPPORTING_SVC_CREATE =
 796             "insert into inv_ser_supporting_service values (service_name, supporting_service_name, create_date, "
 797             + "update_date) "
 798             + "values(?, ?, ?, ?)";
 799
 800         SERVICE_DELETE =
 801             "DELETE FROM %sinv_ser_main WHERE service_name = ?";
 802
 803     }
 804
 805     private Queries() {
 806         // util class
 807     }
 808
 809     public static Query getQuery() {
 810         return new Query();
 811     }
 812
 813     public static final class Query {
 814         private String sql;
 815         private String schema;
 816
 817         private Query() {
 818             this.schema = "";
 819         }
 820
 821         public Query withSchema(String schema0) {
 822             this.schema = schema0;
 823             return this;
 824         }
 825
 826         public Query deviceInfoInsert() {
 827             this.sql = DEVICE_INFO_INSERT;
 828             return this;
 829         }
 830
 831         public Query deviceShelfInsert() {
 832             this.sql = DEVICE_SHELF_INSERT;
 833             return this;
 834         }
 835
 836         public Query deviceShelfSlotInsert() {
 837             this.sql = DEVICE_SHELF_SLOT_INSERT;
 838             return this;
 839         }
 840
 841         public Query deviceCircuitPackInsert() {
 842             this.sql = DEVICE_CP_INSERT;
 843             return this;
 844         }
 845
 846         public Query deviceCPSlotInsert() {
 847             this.sql = DEVICE_CP_SLOT_INSERT;
 848             return this;
 849         }
 850
 851         public Query deviceCPPortInsert() {
 852             this.sql = DEVICE_CP_PORT_INSERT;
 853             return this;
 854         }
 855
 856         public Query deviceInterfacesInsert() {
 857             this.sql = DEVICE_INTERFACES_INSERT;
 858             return this;
 859         }
 860
 861         public Query deviceInterfaceTcmInsert() {
 862             this.sql = DEVICE_INTERFACE_TCM_INSERT;
 863             return this;
 864         }
 865
 866         public Query deviceInterfaceOtnOduTxMsiInsert() {
 867             this.sql = DEVICE_INTERFACE_ODU_OPU_MIS_TX_INSERT;
 868             return this;
 869         }
 870
 871         public Query deviceInterfaceOtnOduRxMsiInsert() {
 872             this.sql = DEVICE_INTERFACE_ODU_OPU_MIS_RX_INSERT;
 873             return this;
 874         }
 875
 876         public Query deviceInterfaceOtnOduExpMsiInsert() {
 877             this.sql = DEVICE_INTERFACE_ODU_OPU_MIS_EXP_INSERT;
 878             return this;
 879         }
 880
 881         public Query deviceProtocolInsert() {
 882             this.sql = DEVICE_PROTOCOL_INSERT;
 883             return this;
 884         }
 885
 886         public Query deviceProtocolPortConfigInsert() {
 887             this.sql = DEVICE_PROTOCOL_PORT_CONFIG_INSERT;
 888             return this;
 889         }
 890
 891         public Query deviceProtocolLldpNbrlistInsert() {
 892             this.sql = DEVICE_PROTOCOL_LLDP_NBR_LIST_INSERT;
 893             return this;
 894         }
 895
 896         public Query deviceProtocolRstpInsert() {
 897             this.sql = DEVICE_PROTOCOL_RSTP_INSERT;
 898             return this;
 899         }
 900
 901         public Query deviceProtocolRstpBridgePortInsert() {
 902             this.sql = DEVICE_PROTOCOL_RSTP_BRIDGEPORT_INSERT;
 903             return this;
 904         }
 905
 906         public Query deviceProtocolRstpBridgePortAttrInsert() {
 907             this.sql = DEVICE_PROTOCOL_RSTP_BRIDGEPORT_ATTR_INSERT;
 908             return this;
 909         }
 910
 911         public Query deviceInternalLinkInsert() {
 912             this.sql = DEVICE_INTERNAL_LINK_INSERT;
 913             return this;
 914         }
 915
 916         public Query deviceExternalLinkInsert() {
 917             this.sql = DEVICE_EXTERNAL_LINK_INSERT;
 918             return this;
 919         }
 920
 921         public Query devicePhysicalLinkInsert() {
 922             this.sql = DEVICE_PHYSICAL_LINK_INSERT;
 923             return this;
 924         }
 925
 926
 927         public Query deviceDegreeInsert() {
 928             this.sql = DEVICE_DEGREE_INSERT;
 929             return this;
 930         }
 931
 932         public Query deviceDegreeCircuitPackInsert() {
 933             this.sql = DEVICE_DEGREE_CIRCUITPACK_INSERT;
 934             return this;
 935         }
 936
 937         public Query deviceDegreeConnectionPortInsert() {
 938             this.sql = DEVICE_CONNECTION_PORT_INSERT;
 939             return this;
 940         }
 941
 942
 943         public Query deviceSharedRiskGroupInsert() {
 944             this.sql = DEVICE_SHARED_RISK_GROUP_INSERT;
 945             return this;
 946         }
 947
 948
 949         public Query deviceSrgCircuitPackInsert() {
 950             this.sql = DEVICE_SRG_CIRCUITPACK_INSERT;
 951             return this;
 952         }
 953
 954
 955         public Query deviceRoadmConnectionsInsert() {
 956             this.sql = DEVICE_ROADM_CONNECTIONS_INSERT;
 957             return this;
 958         }
 959
 960
 961         public Query deviceConnectionMapInsert() {
 962             this.sql = DEVICE_CONNECTION_MAP_INSERT;
 963             return this;
 964         }
 965
 966
 967         public Query deviceWavelengthInsert() {
 968             this.sql = DEVICE_WAVELENGTH_INSERT;
 969             return this;
 970         }
 971
 972         public Query serviceCreate() {
 973             this.sql = SERVICE_INSERT;
 974             return this;
 975         }
 976
 977         public Query serviceEventCreate() {
 978             this.sql = SERVICE_EVENT_INSERT;
 979             return this;
 980         }
 981
 982         public Query serviceUpdate() {
 983             this.sql = SERVICE_UPDATE;
 984             return this;
 985         }
 986
 987         public Query serviceDelete() {
 988             this.sql = SERVICE_DELETE;
 989             return this;
 990         }
 991
 992         public Query servicePathListCreate() {
 993             this.sql = SERVICE_PATH_LIST;
 994             return this;
 995         }
 996
 997         public Query serviceHConCreate() {
 998             this.sql = SERVICE_HCON_CREATE;
 999             return this;
1000         }
1001
1002         public Query serviceHConDiversityCreate() {
1003             this.sql = SERVICE_HCON_DIVERSITY_CREATE;
1004             return this;
1005         }
1006
1007         public Query serviceHConDiversityCapabilityCreate() {
1008             this.sql = SERVICE_HCON_DIVERSITY_CAPABILITY_CREATE;
1009             return this;
1010         }
1011
1012         public Query serviceHConComponentCreate() {
1013             this.sql = SERVICE_HCON_COMPONENT_CREATE;
1014             return this;
1015         }
1016
1017         public Query serviceHConCoroutingCreate() {
1018             this.sql = SERVICE_HCON_COROUTING_CREATE;
1019             return this;
1020         }
1021
1022         public Query serviceSConDiversityCreate() {
1023             this.sql = SERVICE_SCON_DIVERSITY_CREATE;
1024             return this;
1025         }
1026
1027         public Query serviceSConDiversityCapabilityCreate() {
1028             this.sql = SERVICE_SCON_DIVERSITY_CAPABILITY_CREATE;
1029             return this;
1030         }
1031
1032         public Query serviceSConComponentCreate() {
1033             this.sql = SERVICE_SCON_COMPONENT_CREATE;
1034             return this;
1035         }
1036
1037         public Query serviceSConCoroutingCreate() {
1038             this.sql = SERVICE_SCON_COROUTING_CREATE;
1039             return this;
1040         }
1041
1042         public Query serviceFiberspanSrlgCreate() {
1043             this.sql = SERVICE_FIBERSPAN_SRLG_CREATE;
1044             return this;
1045         }
1046
1047         public Query serviceEquipmentSrgCreate() {
1048             this.sql = SERVICE_EQUIPMENT_SRG_CREATE;
1049             return this;
1050         }
1051
1052         public Query serviceSupportingSvcCreate() {
1053             this.sql = SERVICE_SUPPORTING_SVC_CREATE;
1054             return this;
1055         }
1056
1057         public String get() {
1058             Preconditions.checkArgument(!Strings.isNullOrEmpty(this.sql), "No query selected");
1059             return String.format(this.sql, this.schema.concat("."));
1060         }
1061     }
1062 }