ó
wõ!_c           @   s   d  d l  Z  d  d l Z d  d l Z d  d l Z d  d l Z d  d l Td  d l m Z d  d l j	 Z	 d  d l
 m Z d e f d „  ƒ  YZ d S(   iÿÿÿÿN(   t   *(   t   DBConnector(   t   IntegrityErrort   DeviceDBc           B   s5   e  Z d  „  Z d „  Z d „  Z d „  Z d „  Z RS(   c         C   s2   t  j |  | | | | ƒ t j |  _ d |  _ d  S(   Ni    (   R   t   __init__t   settingst   loggert   retry_count(   t   selft   hostt   usert   passwordt   database(    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyR      s    c   K      C   sÁ  |  j  j d t t j ƒ  ƒ d | j d | j d t | j ƒ ƒ | | j d | j } | j d | j } | j d |  j	 d | f ƒ | j
 ƒ  }	 |	 d  k r|  j  j d ƒ |	 d	 }
 d |  j	 d
 } | j | |
 | j f ƒ | j
 ƒ  } | d  k rPd | | j <|  j  j d t t j ƒ  ƒ d | j d | j d ƒ d  Sd } x2 | j D]' } d | j j ƒ  k r`| j } q`q`Wt } | j d |
 f ƒ | j
 ƒ  } | d  k r| | d	 k r|  j  j d j d | d | d	 ƒ ƒ t } | d	 } qq¼|  j  j d ƒ n¡ | j d d ƒ }
 |
 j d d ƒ }
 |  j  j d t t j ƒ  ƒ d | j d | j d ƒ |
 d | j f |
 d | f |
 d |
 f g } d } | j | | ƒ |
 d |
 d | j | j |
 d f |
 d |
 d | j | j |
 d f |
 d |
 d | j | j |
 d f g } d  } | j | | ƒ |  j  j d t t j ƒ  ƒ d | j d | j d! ƒ |  j j ƒ  g  } d } xf | j D][ } d | j j ƒ  k r¿| j } n  | j |
 | j |
 | j | j | j |
 | j f ƒ q˜Wt } | j d |
 f ƒ | j
 ƒ  } | d  k r´| | d	 k rq|  j  j d j d | d | d	 ƒ ƒ t } | d	 } qÄd" } | j | |
 f ƒ | j ƒ  } x+ | D] } | d	 } qWn |  j  j d# ƒ d$ } | j | | ƒ x' | j D] } | j d% |
 | f ƒ qäW| j d& |
 |
 |
 | j f ƒ |  j  j d t t j ƒ  ƒ d | j d | j d' ƒ |  j j ƒ  |  j  j d t t j ƒ  ƒ d | j d | j d( ƒ d) } g  } g  } g  } g  } | j |
 d* |
 d* | j d+ d+ | d+ d, f
 ƒ g  } i  }  x¼| j D]±}! g  | D] }" |! j |" k r|" ^ q}# t |# ƒ d- k r©|  j |! j ƒ rn|  |! j c d- 7<n d. |  |! j <|  |! j }$ d/ j d0 |! j d1 |$ ƒ |! _ n  | j |! j ƒ | j d2 |! j f ƒ |  j  j d3 t |! j ƒ ƒ | j
 ƒ  }% |% d  k r\|% d- d  k r\|% d	 }& |% d- j d d ƒ }' |% d. }( |( rS|( j  ƒ  }( n  d4 } n|  j  j! d t t j ƒ  ƒ d | j d | j d5 |! j d6 | j d7 ƒ | j" d8 k rÑ|  j  j! d9 ƒ qn  d  }( d  }) d: }* x¿ |! j D]´ } | j d; k rV| j }* |* r,d< |* j ƒ  k r,d< }* n! |* rMd= |* j ƒ  k rMd> }* n  |* }& qí| j d? k rq| j }( qí| j d@ k rí| j r¡| j j# dA ƒ }) q¡qíqíW|! j }' |  j  j dB ƒ dC } |  j  j |! j$ ƒ | j | |! j d |* |( |! j$ |' f ƒ |  j  j dD ƒ d } | t |' ƒ }+ d  }, d  }- |  j  j dE t |) ƒ ƒ |) rž	t |) ƒ d	 k rž	|) d	 }, |, s|	dF }, n  t |) ƒ d- k r¤	|) d- }- q¤	n dF }, dG } y/ | j | |+ |* |! j | j |, |- |( f ƒ Wn# t% k
 rþ	}. |  j  j! dH ƒ n Xd }/ x, |! j D]! }0 |0 j d k r
|0 j }/ q
q
WdI }1 dJ }2 | j |1 |' |/ |' |/ f ƒ | j |2 |' |/ |' |/ f ƒ y* t& |' ƒ |  j  j dK |' dL | ƒ Wn! |  j  j dM |' ƒ d } n X| t |' ƒ }3 |3 d |& }4 | |4 dA 7} | j |4 ƒ | j |3 ƒ | j |
 |3 |
 | |! j' |( |3 t |' ƒ f ƒ | j dN |3 f ƒ | j
 ƒ  }5 |5 d  k r†|5 d	 }6 |5 d- }7 |5 d. }8 nA |  j  j! d t t j ƒ  ƒ d | j d | j dO |3 ƒ q| j |
 |3 |
 |3 | j |7 |8 | |6 |( f
 ƒ g  }9 xN |! j D]C }0 |9 j |! j( |0 j |! j( |0 j |0 j |0 j |! j( |0 j f ƒ qWt |9 ƒ d	 k rdP }: | j |: ƒ dQ }; | j |; |9 ƒ x- |! j D] }< | j dR |! j( |< f ƒ q‘WqqW|  j  j d t t j ƒ  ƒ d | j d | j dS ƒ |  j j ƒ  |  j  j dT ƒ |  j  j dU | ƒ | j dV | ƒ |  j  j d t t j ƒ  ƒ d | j d | j dW ƒ |  j j ƒ  d }= | j dX |
 ƒ | j ƒ  }> g  |> D] }? |? d- ^ q¦}@ |  j  j) |@ ƒ t* t+ |@ ƒ t+ | ƒ t+ dY d* g ƒ ƒ }A |  j  j dZ t |A ƒ ƒ t |A ƒ d- k rod[ j, |A ƒ }B d\ j d] |B ƒ }C |  j  j d^ |B ƒ | j |C |
 f ƒ n  |> g  k rŒ|> d	 d	 }= n  | rb|  j  j d t t j ƒ  ƒ d_ ƒ | j d` | |
 | f ƒ |  j  j d t t j ƒ  ƒ d | j d | j da ƒ | j db | j | |
 f ƒ |  j  j d t t j ƒ  ƒ d | j d | j dc ƒ nb |= d k rÄ| j dd | j |
 f ƒ |  j  j d t t j ƒ  ƒ d | j d | j de ƒ n  |  j  j df ƒ |  j  j | ƒ dg } |  j  j | ƒ | j | | ƒ |  j  j dh ƒ |  j  j d t t j ƒ  ƒ d | j d | j di ƒ dj |
 dk }D | j |D ƒ | j
 ƒ  }E |E d  k rù|  j  j dl | j d | j dm ƒ dn |
 do }F dp |
 dq | |F d }G |  j  j dr |G ƒ | j |G ƒ n |  j  j d t t j ƒ  ƒ d | j d | j ds ƒ xP t- t | ƒ ƒ D]< }H dt |
 du | |H }I |
 | |H f }J | j |I |J ƒ qFW|  j  j d t t j ƒ  ƒ d | j d | j dv ƒ |  j j ƒ  dw } | j | |
 |
 | j | j |
 f ƒ | j dx k rv| j dy j dz |
 d{ |
 ƒ ƒ |  j  j d| ƒ |  j  j d} |
 ƒ |  j  j d~ ƒ | j d |
 |
 | f ƒ n  |  j  j d t t j ƒ  ƒ d | j d | j d€ ƒ |  j j ƒ  d  S(   Ns   DeviceDB: (s   ) t    s    processing device. Version: t   _s   use sq   ; SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE DEVICE_NAME='ldid' AND DEVICE_VALUE=%ss   device exists already. Updatei    s…   ;  SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version' AND DEVICE_VALUE=%st   DEVICE_VERSION_EXISTSs<    device version already exists. No modification will be madet    t   aliassp   SELECT DEVICE_VALUE FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='alias's$   logger alias not equal: {_a} <> {_b}t   _at   _bs%   Alias of device has not been changed.t   -t   dasht   /t   slashs0    Insert device id into xxx_RAW_Device_Propertiest	   Device_IDt   ldidt   Database_Containersa   INSERT INTO xxx_RAW_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES (%s, %s, %s)t   Device_Classt   Module_Namet   Types8  IF NOT EXISTS (SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME=%s)INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,%s,%s) ELSE UPDATE xxx_Raw_Device_Properties SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%ss.    First Commit on xxx_Raw_Device_Properties forsc   SELECT DISTINCT TOP 1 virtual_device_name AS alias FROM dbo.xxx_Virtual_Devices WHERE Table_Name=%ss   Device has no alias parameters0  IF NOT EXISTS (SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME=%s) INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,%s,%s) ELSE UPDATE xxx_Raw_Device_Properties SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%ssL   DELETE FROM xxx_Raw_Device_Properties WHERE TABLE_NAME=%s AND DEVICE_NAME=%ss¯   IF NOT EXISTS (SELECT * FROM xxx_Device_Mappings (READUNCOMMITTED) WHERE Table_Name=%s) INSERT INTO xxx_Device_Mappings (Table_Name, RAW_Table_Name, RAW_Name) VALUES(%s,%s,%s)s)    Second Commit on xxx_Device_Mappings forsD    finished inserting device parameters into xxx_RAW_Device_PropertiessB   Date_and_time datetime, Storage_time datetime, Storage_delay int, t   Storage_delayt   DELAYt   secondsi   i   s   {_vtype}_{_nr}t   _vtypet   _nrs]   SELECT datatype, vpc, unit FROM COMMONDB.DBO.value_type (READUNCOMMITTED) WHERE value_type=%ss   v.type:t   COL_SR_s    vpc for value_type: s     and device id: s&    either could not be found or was nullt    skygateCGZOTFgfkhik7hzgrm2hbq4aEs'   DeviceDB: unknown vtype will be ignoreds   nvarchar(32)t   datatypet   floatt   intt   bigintt   unitt   classt   ,t   pos1s®   INSERT INTO COMMONDB.DBO.value_type
                            (value_type, description, datatype, unit, storage_type, vpc)
                        VALUES(%s,%s,%s,%s,%s,%s)t   pos2s   param classes: t   UNKNOWNsû   INSERT INTO [cdb].[dbo].[CDB_COLUMN_TYPES]
                         (Column_Value,Column_Type_String,Translation_Key,Device_Type,
                          Device_Class,Device_Class_2,Unit_String)
                          VALUES(%s,%s,%s,%s,%s,%s,%s)s/   Column does already exists in cdb_column_types.s'  IF NOT EXISTS (SELECT * FROM [cdb].[dbo].[Translations] WHERE Language=1 AND Translation_Key=%s AND Translation=%s) 
                                        INSERT INTO [cdb].[dbo].[Translations] (Language, Translation_Key, Translation) 
                                        VALUES(1, %s, %s)s&  IF NOT EXISTS (SELECT * FROM [cdb].[dbo].[Translations] WHERE Language=2 AND Translation_Key=%s AND Translation=%s)
                                        INSERT INTO [cdb].[dbo].[Translations] (Language, Translation_Key, Translation) 
                                        VALUES(2, %s, %s)s   value_vpc: s	    prefix: s   reset column prefix. vpc: %ssz   SELECT Translation_Key, Device_Class, Device_Class_2 FROM cdb.dbo.CDB_COLUMN_TYPES (READUNCOMMITTED) WHERE Column_Value=%ssH    column_value was not found in cdb.dbo.CDB_COLUMN_TYPES for column name s   IF NOT EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='xxx_value_parameter')
                                    CREATE TABLE [dbo].[xxx_value_parameter](
                                    [lvid] [nvarchar](100) NOT NULL,
                                    [value_key] [nvarchar](100) NOT NULL,
                                    [value] [nvarchar](100) NOT NULL,
                                CONSTRAINT [xxx_value_parameter_pkey] PRIMARY KEY CLUSTERED ([lvid] ASC,[value_key] ASC))sß   IF NOT EXISTS (SELECT * FROM xxx_value_parameter (READUNCOMMITTED) WHERE lvid=%s AND value_key=%s) INSERT INTO xxx_value_parameter VALUES(%s,%s,%s) ELSE UPDATE xxx_value_parameter SET value=%s WHERE lvid=%s AND value_key=%ss>   DELETE FROM xxx_value_parameter WHERE lvid=%s AND value_key=%ss$    Third Commit on xxx_value_parameters…  IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name=%s) "
                             "INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID, Unit_String) "
                             "VALUES(%s, %s, %s, %s, %s, %s, %s, '-1', %s) s(   DeviceDB: insertMesCurvVirtDevParams: %ssE  IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name=%s) INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID, Unit_String) VALUES(%s, %s, %s, %s, %s, %s, %s, '-1', %s) s,    finished inserting into xxx_Virtual_Devicess^   SELECT Device_Type, Column_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%st   COL_E_TODAY_CALCs   DeviceDB: columns_deactive %ss   ','s   DELETE FROM xxx_Virtual_Devices
                               WHERE Table_name=%s 
                               AND Column_Name IN ('{_col}')t   _cols*   DeviceDB: del col from virtual devices: %ss!   ) - device alias has been changedsZ   UPDATE xxx_RAW_Device_Properties SET DEVICE_NAME=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%ss!    Update xxx_RAW_Device_PropertiessY   UPDATE xxx_Virtual_Devices SET Device_Type=%s, Virtual_Device_Name=%s WHERE Table_Name=%ss    Update xxx_Virtual_DevicessA   UPDATE xxx_Virtual_Devices SET Device_Type=%s WHERE Table_Name=%ss#    Update xxx_Virtual_Devices in elses!   insert into raw_device_propertiess)  IF NOT EXISTS (SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND COLUMN_NAME=%s) INSERT INTO xxx_RAW_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE, DEVICE_UNIT, COLUMN_NAME, DEVICE_VPC, VALUE_TYPE, DEVICE_TRANSMIT) VALUES(%s,%s,%s,%s,%s,%s,1,1) s%   insert into raw_device_properties endsE    finished inserting measurement curves into xxx_RAW_Device_PropertiessU   SELECT table_name FROM INFORMATION_SCHEMA.TABLES (READUNCOMMITTED) WHERE TABLE_NAME='s   ' s
   DeviceDB: s+    data table does not exist. Creating table.s   CONSTRAINT s!   _pkey PRIMARY KEY (Date_and_time)s   CREATE TABLE t   (s   create table sql: %ss>    data table exists. Checking if an update is needed for devices‡   IF NOT EXISTS (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS (READUNCOMMITTED) WHERE TABLE_NAME=%s AND COLUMN_NAME=%s)ALTER TABLE s    ADD s    Fifth Commit for devices  IF NOT EXISTS (SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) 
                                                     WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version') 
                                            INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,'Config_Version',%s) 
                                        ELSE UPDATE xxx_Raw_Device_Properties 
                                            SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version't   INVERTERs  IF NOT EXISTS 
                        (SELECT * FROM sys.columns
                         WHERE object_id = OBJECT_ID(N'[dbo].[{_tbl_1}]')   
                         AND name = 'COL_E_TODAY_CALC') 
                         ALTER TABLE {_tbl_2} ADD COL_E_TODAY_CALC bigintt   _tbl_1t   _tbl_2s!   DeviceDB: create COL_E_TODAY_CALCs   DeviceDB: table_name: %ss'   DeviceDB: Column_Name: COL_E_TODAY_CALCsy  IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name='COL_E_TODAY_CALC') INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID) VALUES(%s, 'COL_E_TODAY_CALC', 'INVERTER', 'E_TODAY', 'E_TODAY', %s, 'ETODAY_ADJUSTED', '-1') s    Final Commit for device.(.   R   t   debugt   strt   ost   getpidt   loggerIdt   deviceIdt   versiont   executeR   t   fetchonet   Nonet
   parameterst   keyt   lowert   valuet   Falset   formatt   Truet   replacet   executemanyt   categoryt   dtypet   _connectiont   committ   appendt   fetchallt   paramsToDeletet   deviceValuest   vtypet   lent   gett   stript   warningt   autht   splitt   storageR   R'   t   valueIdt   lvidt   infot   listt   sett   joint   range(K   R   t   curt   dev_containert   devt   table_prefixt   column_prefixt   listStatusCodest   default_table_nameR   t   resTableNamet
   table_namet   statementCheckDevVersiont   resVt	   dev_aliast   pt   aliasChangedt   rest	   old_aliast   propListt   sqlt	   paramListt   statementInsertDevVersiont	   paramValst   rt   statementInsertDevParamt   pkeyt   columnst   columnDefinitionsArrt   columnNamesArrt   insertMesCurvRawPropParamst   insertMesCurvVirtDevParamst	   vtype_arrt   vtype_duplicationst   vt   st   matchingt   numbert   resValueTypet   value_storage_typet	   value_vpct
   value_unitt   param_classest   value_datatypet   col_namet   class1t   class2t   errt   value_aliast   vpt   statement_tr_key_gert   statement_tr_key_engt   column_namet   columnDefinitiont   resCTt   value_tkt   value_dct	   value_dc2t   valParamValst
   sql_createt   statementInsertValParamt   vkeyt   old_dev_categoryt   res_vdt   colt   table_columnst   columns_deactivet   col_deact_stringt   del_statementt   statementCheckTablet   resFromCheckt
   constraintt   statementCreateValTablet   indext   statementColumnst   params(    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyt
   __set_data   s   G
:&:!!':&	::(

	P							(
	/
 
"

>A$:		:,$:==:&::	
	:c         C   s­  |  j  j d ƒ |  j d  k r©i  } |  j j ƒ  } | j d j d |  j ƒ ƒ | j d ƒ | j ƒ  d } | |  j k r• |  j  j	 d ƒ t
 ‚ n  d } d } g  } y3 |  j | ƒ |  j j ƒ  |  j | | j ƒ } Wn# t
 k
 rÿ } |  j  j d	 ƒ n Xx¦| j D]˜}	 |	 j | k rQd
 | |	 j <|  j  j d j d |	 j ƒ ƒ q
n  d }
 xH|
 |  j k r¡y- |  j | | |	 | | | ƒ |  j d }
 WqZt
 k
 r} |  j  j	 d t t j ƒ  ƒ d |	 j d |	 j d t t j ƒ  ƒ ƒ |  j j ƒ  t | ƒ j d ƒ d k r€|
 d 7}
 |
 |  j k ršd | |	 j <|  j  j d t t j ƒ  ƒ d |	 j d |	 j d ƒ qšqž|  j d }
 d | |	 j <qZXqZWq
Wn  | S(   Ns.   DeviceDB: entered processing setdevice requests	   use {_db}t   _dbs   select db_name()i    s3   Mismatch between current db and routing db. Exitingt   Data_Table_R#   s0   Something went wrong with the hidden device codet   SUCCESSs:   DeviceDB: device {_dev} is marked as hidden. Going to skipt   _devi   s   DeviceDB: (s   ) R   s)    operation failed for device: Error msg: t   deadlockiÿÿÿÿt   SERVER_SIDE_ERRORs    Giving up on retrying (   R   R5   RJ   R>   t   cursorR<   RD   R   R=   t   errort	   Exceptiont#   _DeviceDB__create_hidden_devs_tableRK   t   _DeviceDB__get_hidden_devicesR9   t   listObjectsR:   R   t   _DeviceDB__set_dataR6   R7   R8   t	   tracebackt
   format_exct   rollbackt   find(   R   t   devContainerRd   R_   t   curr_dbRb   Rc   t   hidden_devicest   eRa   t   tryCount(    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyt   setDataß  sR    	J
=c         C   s&   d j  d |  j ƒ } | j | ƒ d  S(   Ns·  IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[xxx_Hidden_Devices]') and OBJECTPROPERTY(id, N'IsTable') = 1)
                BEGIN
                CREATE TABLE dbo.[xxx_Hidden_Devices]
                (
                    sdid nvarchar(100), 
                    lid nvarchar(100)        
                    CONSTRAINT hidden_devices_pk PRIMARY KEY (sdid, lid)
                )
                END
             t   _schema(   RD   R   R<   (   R   R_   Rp   (    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyt   __create_hidden_devs_table   s    c         C   sQ   g  } d } | j  | | f ƒ | j ƒ  } x | D] } | j | d ƒ q2 W| S(   Ns0   SELECT sdid FROM xxx_Hidden_Devices WHERE lid=%si    (   R<   RM   RL   (   R   R_   t   lidt   hidden_devsRp   Rm   t   row(    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyt   __get_hidden_devices0  s    (   t   __name__t
   __module__R   Rµ   R¿   R²   R³   (    (    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyR      s   		ÿ Ë	A	(   t   datetimet   reR7   t   timeR¶   t   src.containers.DeviceContainert   src.dbconnectors.dbR   t   src.settingsR   t   pymssqlR   R   (    (    (    s0   /var/www/titaniumci/src/dbconnectors/DeviceDB.pyt   <module>   s   
