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