def test_connection(self, conn_dict):
        conn_string = self.__build_connection_string(conn_dict)
        try:
            service = SeriesService(conn_string, True)
            site = service.get_test_data()
        except SQLAlchemyError:
            return False

        return True
Esempio n. 2
0
 def get_db_version(self, conn_string):
     if isinstance(conn_string, dict):
         conn_string = self._build_connection_string(conn_string)
     service = SeriesService(conn_string)
     #if not self.version:
     try:
         self.version = service.get_db_version()
     except Exception as e:
         logger.error("Exception: %s" % e.message)
         return None
     return self.version
Esempio n. 3
0
 def get_db_version(self, conn_string):
     if isinstance(conn_string, dict):
         conn_string = self._build_connection_string(conn_string)
     service = SeriesService(conn_string)
     #if not self.version:
     try:
         self.version = service.get_db_version()
     except Exception as e:
         logger.error("Exception: %s" % e.message)
         return None
     return self.version
Esempio n. 4
0
    def __init__(self, series_id, connection=None, connection_string="", debug=False):
        self._connection = connection
        self._series_id = series_id
        self._filter_from_selection = False
        self._debug = debug

        if connection_string is not "":
            self._session_factory = SessionFactory(connection_string, debug)
            self._series_service = SeriesService(connection_string, debug)
        elif factory is not None:
            self._session_factory = factory
            service_manager = ServiceManager()
            self._series_service = service_manager.get_series_service()
        else:
            # One or the other must be set
            print "Must have either a connection string or session factory"
            # TODO throw an exception

        self._edit_session = self._session_factory.get_session()

        if self._connection == None:
            series_service = SeriesService(connection_string, False)
            series = series_service.get_series_by_id(series_id)
            DataValues = [
                (
                    dv.id,
                    dv.data_value,
                    dv.value_accuracy,
                    dv.local_date_time,
                    dv.utc_offset,
                    dv.date_time_utc,
                    dv.site_id,
                    dv.variable_id,
                    dv.offset_value,
                    dv.offset_type_id,
                    dv.censor_code,
                    dv.qualifier_id,
                    dv.method_id,
                    dv.source_id,
                    dv.sample_id,
                    dv.derived_from_id,
                    dv.quality_control_level_id,
                )
                for dv in series.data_values
            ]
            self._connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
            tmpCursor = self._connection.cursor()
            self.init_table(tmpCursor)
            tmpCursor.executemany("INSERT INTO DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)

        self._connection.commit()
        self._cursor = self._connection.cursor()

        self._populate_series()
Esempio n. 5
0
    def __init__(self,
                 series_id,
                 connection=None,
                 connection_string="",
                 debug=False):
        # print "Series id: ", series_id
        self._connection = connection
        self._series_id = series_id
        self._filter_from_selection = False
        self._debug = debug

        if (connection_string is not ""):
            self._session_factory = SessionFactory(connection_string, debug)
            self._series_service = SeriesService(connection_string, debug)
        elif (factory is not None):
            self._session_factory = factory
            service_manager = ServiceManager()
            self._series_service = service_manager.get_series_service()
        else:
            # One or the other must be set
            print "Must have either a connection string or session factory"
            # TODO throw an exception

        self._edit_session = self._session_factory.get_session()

        if self._connection == None:
            series_service = SeriesService(connection_string, False)
            series = series_service.get_series_by_id(series_id)
            DataValues = [
                (dv.id, dv.data_value, dv.value_accuracy, dv.local_date_time,
                 dv.utc_offset, dv.date_time_utc, dv.site_id, dv.variable_id,
                 dv.offset_value, dv.offset_type_id, dv.censor_code,
                 dv.qualifier_id, dv.method_id, dv.source_id, dv.sample_id,
                 dv.derived_from_id, dv.quality_control_level_id)
                for dv in series.data_values
            ]
            self._connection = sqlite3.connect(
                ":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
            tmpCursor = self._connection.cursor()
            self.init_table(tmpCursor)
            tmpCursor.executemany(
                "INSERT INTO DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                DataValues)

        self._connection.commit()
        self._cursor = self._connection.cursor()

        self._populate_series()
Esempio n. 6
0
 def get_series_service(self, conn_dict=""):
     conn_string = ""
     if conn_dict:
         conn_string = self._build_connection_string(conn_dict)
         self._current_conn_dict = conn_dict
     else:
         conn_string = self._build_connection_string(self._current_conn_dict)
     return SeriesService(conn_string, self.debug)
Esempio n. 7
0
    def __init__(self,
                 series_id,
                 connection=None,
                 connection_string="",
                 debug=False):
        '''

        :param series_id:
        :param connection: memory database,  contains connection to remote database
        :param connection_string: connection to remote database
        :param debug:
        :return:
        '''

        self._series_id = series_id
        self._filter_from_selection = False
        self._debug = debug

        if connection_string is "" and connection is not None:
            self.memDB = connection
            #self._series_service = self.memDB.series_service#SeriesService(connection_string, debug)

        elif connection_string is not "" and connection is None:
            from odmtools.odmdata import MemoryDatabase
            self.memDB = MemoryDatabase()  #(series_service)
            self.memDB.set_series_service(
                SeriesService(connection_string, False))

        else:
            logger.error(
                "must send in either a remote db connection string or a memory database object"
            )

        logger.debug("Initializing Memory Database")
        self.memDB.initEditValues(series_id)
        logger.debug("Finished Initializing Memory Database")
        self._populate_series()
        self.reset_filter()
Esempio n. 8
0
 def get_series_service(self):
     conn_string = self.__build_connection_string(self._current_connection)
     return SeriesService(conn_string, self.debug)
Esempio n. 9
0
class EditService():
    # Mutual exclusion: cursor, or connection_string
    def __init__(self, series_id, connection=None, connection_string="", debug=False):
        self._connection = connection
        self._series_id = series_id
        self._filter_from_selection = False
        self._debug = debug

        if (connection_string is not ""):
            self._session_factory = SessionFactory(connection_string, debug)
            self._series_service = SeriesService(connection_string, debug)
        elif (factory is not None):
            #TODO code has changed to no longer use a session factory, refactor so it is correct SR
            self._session_factory = factory
            service_manager = ServiceManager()
            self._series_service = service_manager.get_series_service()
        else:
            # One or the other must be set
            print("Must have either a connection string or session factory")
            #
            # TODO throw an exception

        self._edit_session = self._session_factory.getSession()

        if self._connection == None:
            series_service = SeriesService(connection_string, False)
            series = series_service.get_series_by_id(series_id)
            DataValues = [(dv.id, dv.data_value, dv.value_accuracy, dv.local_date_time, dv.utc_offset, dv.date_time_utc,
                           dv.site_id, dv.variable_id, dv.offset_value, dv.offset_type_id, dv.censor_code,
                           dv.qualifier_id, dv.method_id, dv.source_id, dv.sample_id, dv.derived_from_id,
                           dv.quality_control_level_id) for dv in series.data_values]
            self._connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
            tmpCursor = self._connection.cursor()
            self.init_table(tmpCursor)
            tmpCursor.executemany("INSERT INTO DataValues VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)

        self._connection.commit()
        self._cursor = self._connection.cursor()

        self._populate_series()
        self.reset_filter()

    def _populate_series(self):
        # [(ID, value, datetime), ...]
        self._cursor.execute("SELECT ValueID, DataValue, LocalDateTime FROM DataValues ORDER BY LocalDateTime")
        results = self._cursor.fetchall()

        self._series_points = results


    def _test_filter_previous(self):
        if not self._filter_from_selection:
            self.reset_filter()

    ###################
    # Filters
    ###################
    # operator is a character, either '<' or '>'
    def filter_value(self, value, operator):
        self._test_filter_previous()

        if operator == '<':  # less than
            for i in range(len(self._series_points)):
                # If it's not already in the selection, skip it
                if (self._filter_from_selection and not self._filter_list[i]):
                    continue
                if self._series_points[i][1] < value:
                    self._filter_list[i] = True
                else:
                    self._filter_list[i] = False
        if operator == '>':  # greater than
            for i in range(len(self._series_points)):
                if (self._filter_from_selection and not self._filter_list[i]):
                    continue
                if self._series_points[i][1] > value:
                    self._filter_list[i] = True
                else:
                    self._filter_list[i] = False

    def filter_date(self, before, after):
        self._test_filter_previous()

        previous_date_filter = False
        if before != None:
            tmp = []
            for i in range(len(self._series_points)):
                if (self._filter_from_selection and not self._filter_list[i]):
                    continue
                if self._series_points[i][2] < before:
                    self._filter_list[i] = True
                else:
                    self._filter_list[i] = False
            previous_date_filter = True  # We've done a previous date filter
        if after != None:
            for i in range(len(self._series_points)):
                if ((previous_date_filter or self._filter_from_selection)
                    and not self._filter_list[i]):
                    continue
                if self._series_points[i][2] > after:
                    self._filter_list[i] = True
                else:
                    self._filter_list[i] = False

    # Data Gaps
    def data_gaps(self, value, time_period):
        length = len(self._series_points)

        value_sec = 0

        if time_period == 'second':
            value_sec = value
        if time_period == 'minute':
            value_sec = value * 60
        if time_period == 'hour':
            value_sec = value * 60 * 60
        if time_period == 'day':
            value_sec = value * 60 * 60 * 24

        tmp = {}

        for i in xrange(length):
            if (self._filter_from_selection and
                    not self._filter_list[i]):
                continue

            if i + 1 < length:  # make sure we stay in bounds
                point1 = self._series_points[i]
                point2 = self._series_points[i + 1]
                interval = point2[2] - point1[2]
                interval_total_sec = interval.total_seconds()

                if interval_total_sec >= value_sec:
                    tmp[i] = True
                    tmp[i + 1] = True

        self.reset_filter()
        for key in tmp.keys():
            self._filter_list[key] = True

    def value_change_threshold(self, value):
        length = len(self._series_points)
        tmp = {}
        for i in xrange(length):
            if (self._filter_from_selection and
                    not self._filter_list[i]):
                continue

            if i + 1 < length:  # make sure we stay in bounds
                point1 = self._series_points[i]
                point2 = self._series_points[i + 1]
                if abs(point1[1] - point2[1]) >= value:
                    tmp[i] = True
                    tmp[i + 1] = True

        self.reset_filter()
        for key in tmp.keys():
            self._filter_list[key] = True

    def select_points_tf(self, tf_list):
        self._filter_list = tf_list

    def select_points(self, id_list=[], datetime_list=[]):
        self.reset_filter()

        # This should be either one or the other. If it's both, id is used first.
        # If neither are set this function does nothing.
        if len(id_list)>0:
            for i in range(len(self._series_points)):
                if self._series_points[i][0] in id_list:
                    self._filter_list[i] = True
        elif datetime_list != None:
            for i in range(len(self._series_points)):

                if self._series_points[i][2] in datetime_list:
                    self._filter_list[i] = True
        else:
            pass


    def reset_filter(self):
        self._filter_list = [False] * len(self._series_points)

    def toggle_filter_previous(self, value):
        self._filter_from_selection = value


    ###################
    # Gets
    ###################
    def get_series(self):
        return self._series_service.get_series_by_id(self._series_id)

    def get_series_points(self):
        #all point in the series
        return self._series_points

    def get_filtered_points(self):
        #list of selected points
        tmp = []
        for i in range(len(self._series_points)):
            if self._filter_list[i]:
                tmp.append(self._series_points[i])

        return tmp

    def get_filtered_dates(self):

        return [x[2] for x in self.get_filtered_points()]

    def get_filter_list(self):
        #true or false list the length of the entire series. true indicate the point is selected
        return self._filter_list

    def get_qcl(self, qcl_id):
        return self._series_service.get_qcl_by_id(qcl_id)



    def get_method(self, method_id):
        return self._series_service.get_method_by_id(method_id)

    def get_variable(self, variable_id):
        return self._series_service.get_variable_by_id(variable_id)


    #################
    # Edits
    #################

    def change_value(self, value, operator):
        filtered_points = self.get_filtered_points()
        tmp_filter_list = self._filter_list
        query = "UPDATE DataValues SET DataValue = "
        if operator == '+':
            query += " DataValue + %s " % (value)

        if operator == '-':
            query += " DataValue - %s " % (value)

        if operator == '*':
            query += " DataValue * %s " % (value)

        if operator == '=':
            query += "%s " % (value)

        query += "WHERE ValueID IN ("
        for i in range(len(filtered_points) - 1):
            query += "%s," % (filtered_points[i][0])
        query += "%s)" % (filtered_points[-1][0])
        self._cursor.execute(query)

        self._populate_series()
        self._filter_list = tmp_filter_list

    def add_points(self, points):
        query = "INSERT INTO DataValues (DataValue, ValueAccuracy, LocalDateTime, UTCOffset, DateTimeUTC, OffsetValue, OffsetTypeID, "
        query += "CensorCode, QualifierID, SampleID, SiteID, VariableID, MethodID, SourceID, QualityControlLevelID) "
        query += "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        self._cursor.executemany(query, points)
        self._populate_series()

    def delete_points(self):
        query = "DELETE FROM DataValues WHERE ValueID IN ("
        filtered_points = self.get_filtered_points()
        num_filtered_points = len(filtered_points)
        if num_filtered_points > 0:
            for i in range(num_filtered_points - 1):  # loop through the second-to-last active point
                query += "%s," % (filtered_points[i][0])  # append its ID
            query += "%s)" % (filtered_points[-1][0])  # append the final point's ID and close the set

            # Delete the points from the cursor
            self._cursor.execute(query)

            self._populate_series()
            self.reset_filter()

    def interpolate(self):
        tmp_filter_list = self._filter_list
        groups = self.get_selection_groups()

        for group in groups:
            # determine first and last point for the interpolation
            first_index = group[0] - 1
            last_index = group[-1] + 1
            # ignore this group (which is actually the whole set)
            # if it includes the first or last point of the series
            if first_index <= 0 or last_index == len(self._series_points):
                continue

            first_point = self._series_points[first_index]
            last_point = self._series_points[last_index]
            a = 0
            c = (last_point[2] - first_point[2]).total_seconds()
            f_a = first_point[1]
            f_c = last_point[1]
            update_list = []
            for i in group:
                b = (self._series_points[i][2] - first_point[2]).total_seconds()
                # linear interpolation formula: f(b) = f(a) + ((b-a)/(c-a))*(f(c) - f(a))
                new_val = f_a + ((b - a) / (c - a)) * (f_c - f_a)
                point_id = self._series_points[i][0]
                update_list.append((new_val, point_id))
            query = "UPDATE DataValues SET DataValue = ? WHERE ValueID = ?"
            self._cursor.executemany(query, update_list)

        self._populate_series()
        self._filter_list = tmp_filter_list

    def drift_correction(self, gap_width):
        tmp_filter_list = self._filter_list
        groups = self.get_selection_groups()

        # only perform a drift correction if there's a single group
        if len(groups) == 1:
            group = groups[0]
            first_index = group[0]
            last_index = group[-1]
            first_point = self._series_points[first_index]
            last_point = self._series_points[last_index]
            x_l = (last_point[2] - first_point[2]).total_seconds()

            update_list = []
            for i in group:
                point = self._series_points[i]
                x_i = (point[2] - first_point[2]).total_seconds()
                # y_n = y_0 + G(x_i / x_l)
                new_val = point[1] + gap_width * (x_i / x_l)
                update_list.append((new_val, point[0]))
            query = "UPDATE DataValues SET DataValue = ? WHERE ValueID = ?"
            self._cursor.executemany(query, update_list)

            self._populate_series()
            self._filter_list = tmp_filter_list

            return True
        else:
            return False

    def get_selection_groups(self):
        length = len(self._series_points)
        found_group = False
        groups = []
        cur_group = []
        for i in range(length):
            if self._filter_list[i]:
                if not found_group:
                    found_group = True
                cur_group.append(i)  # Append the actual index to the point
                if i == length - 1:
                    groups.append(cur_group)
            elif not self._filter_list[i] and found_group:
                found_group = False
                groups.append(cur_group)
                cur_group = []
            else:
                continue

        return groups

    def flag(self, qualifier_id):
        filtered_points = self.get_filtered_points()
        query = "UPDATE DataValues SET QualifierID = %s WHERE ValueID = ?" % (qualifier_id)
        self._cursor.executemany(query, [(str(x[0]),) for x in filtered_points])

    ###################
    # Save/Restore
    ###################

    def restore(self):
        self._connection.rollback()
        self._populate_series()
        self.reset_filter()

    def save(self, var=None, method=None, qcl=None, isSave=False):
        dvs = []
        is_new_series = False

        if var is not None:
            self._cursor.execute("UPDATE DataValues SET VariableID = %s" % (var.id))
            is_new_series = True
        if method is not None:
            self._cursor.execute("UPDATE DataValues SET MethodID = %s" % (method.id))
            is_new_series = True
        # check that the code is not zero
        #if qcl is not None and qcl.code != 0:
        if qcl is not None:
            self._cursor.execute("UPDATE DataValues SET QualityControlLevelID = %s" % (qcl.id))
            is_new_series = True
        #else:
        #    raise ValueError("Quality Control Level cannot be zero")

        self._cursor.execute("SELECT * FROM DataValues ORDER BY LocalDateTime")
        results = self._cursor.fetchall()

        # ValueID, DataValue, ValueAccuracy, LocalDateTime, UTCOffset, DateTimeUTC, SiteID, VariableID,
        # OffsetValue, OffsetTypeID, CensorCode, QualifierID, MethodID, SourceID, SampleID, DerivedFromID, QualityControlLevelID
        for row in results:
            dv = self._build_dv_from_tuple(row)

            if is_new_series:
                dv.id = None
            dvs.append(dv)

        series = self._series_service.get_series_by_id(self._series_id)

        if is_new_series:
            series = series_module.copy_series(series)
            if var:
                series.variable_id = var.id
                series.variable_code = var.code
                series.variable_name = var.name
                series.speciation = var.speciation
                series.variable_units_id = var.variable_unit.id
                series.variable_units_name = var.variable_unit.name
                series.sample_medium = var.sample_medium
                series.value_type = var.value_type
                series.time_support = var.time_support
                series.time_units_id = var.time_unit.id
                series.time_units_name = var.time_unit.name
                series.data_type = var.data_type
                series.general_category = var.general_category
            if method:
                series.method_id = method.id
                series.method_description = method.description
            if qcl:
                series.quality_control_level_id = qcl.id
                series.quality_control_level_code = qcl.code

        series.begin_date_time = dvs[0].local_date_time
        series.end_date_time = dvs[-1].local_date_time
        series.begin_date_time_utc = dvs[0].date_time_utc
        series.end_date_time_utc = dvs[-1].date_time_utc
        series.value_count = len(dvs)

        if not is_new_series:
            # delete old dvs
            old_dvs = series.data_values
            self._series_service.delete_dvs(old_dvs)

        series.data_values = dvs
        print("series.data_values: %s" % ([x for x in series.data_values]))
        if self._series_service.save_series(series, dvs, isSave):
            print ("series saved!")
        else:
            print("Crap happened")

    def create_qcl(self, code, definition, explanation):
        return self._series_service.create_qcl(code, definition, explanation)

    def create_method(self, description, link):
        return self._series_service.create_method(description, link)

    def create_variable(self, code, name, speciation, variable_unit_id, sample_medium,
                        value_type, is_regular, time_support, time_unit_id, data_type, general_category, no_data_value):

        return self._series_service.create_variable(code, name, speciation, variable_unit_id, sample_medium,
                                                    value_type, is_regular, time_support, time_unit_id, data_type,
                                                    general_category, no_data_value)

    def reconcile_dates(self, parent_series_id):
        # FUTURE FEATURE: pull in new field data from another series and add to this series
        # (i.e one series contains new field data of an ediited series at a higher qcl)
        pass

    def _build_dv_from_tuple(self, dv_tuple):
        dv = DataValue()

        dv.id_list = dv_tuple[0]
        dv.data_value = dv_tuple[1]
        dv.value_accuracy = dv_tuple[2]
        dv.local_date_time = dv_tuple[3]
        dv.utc_offset = dv_tuple[4]
        dv.date_time_utc = dv_tuple[5]
        dv.site_id = dv_tuple[6]
        dv.variable_id = dv_tuple[7]
        dv.offset_value = dv_tuple[8]
        dv.offset_type_id = dv_tuple[9]
        dv.censor_code = dv_tuple[10]
        dv.qualifier_id = dv_tuple[11]
        dv.method_id = dv_tuple[12]
        dv.source_id = dv_tuple[13]
        dv.sample_id = dv_tuple[14]
        dv.derived_from_id = dv_tuple[15]
        dv.quality_control_level_id = dv_tuple[16]

        return dv

    def init_table(self, cursor):
        cursor.execute("""CREATE TABLE DataValues
                (ValueID INTEGER NOT NULL,
                DataValue FLOAT NOT NULL,
                ValueAccuracy FLOAT,
                LocalDateTime TIMESTAMP NOT NULL,
                UTCOffset FLOAT NOT NULL,
                DateTimeUTC TIMESTAMP NOT NULL,
                SiteID INTEGER NOT NULL,
                VariableID INTEGER NOT NULL,
                OffsetValue FLOAT,
                OffsetTypeID INTEGER,
                CensorCode VARCHAR(50) NOT NULL,
                QualifierID INTEGER,
                MethodID INTEGER NOT NULL,
                SourceID INTEGER NOT NULL,
                SampleID INTEGER,
                DerivedFromID INTEGER,
                QualityControlLevelID INTEGER NOT NULL,

                PRIMARY KEY (ValueID)
                UNIQUE (DataValue, LocalDateTime, SiteID, VariableID, MethodID, SourceID, QualityControlLevelID))
               """)
Esempio n. 10
0
 def get_db_version(self, conn_dict):
     conn_string = self.__build_connection_string(conn_dict)
     service = SeriesService(conn_string)
     if not self.version:
         self.version = service.get_db_version()
     return self.version
Esempio n. 11
0
 def get_db_version(self, conn_dict):
     conn_string = self.__build_connection_string(conn_dict)
     service = SeriesService(conn_string)
     if not self.version:
         self.version = service.get_db_version()
     return self.version