<commit_before>#! /usr/bin/env python

import sys
import sqlite3
import argparse

import jcd.common
import jcd.dao

class MinMax(object):

    StationsDayTable = "minmax_stations_day"
    ContractsDayTable = "minmax_contracts_day"

    def __init__(self, db, sample_schema, arguments):
        self._db = db
        self._sample_schema = sample_schema
        self._arguments = arguments
        assert self._db is not None
        assert self._sample_schema is not None
        assert self._arguments is not None
        self._create_tables_if_necessary()

    def _create_tables_if_necessary(self):
        if not self._db.has_table(self.StationsDayTable):
            self._create_stations_day_table()
        if not self._db.has_table(self.ContractsDayTable):
            self._create_contracts_day_table()

    def _create_stations_day_table(self):
        if self._arguments.verbose:
            print "Creating table", self.StationsDayTable
        self._db.execute_single(
            '''
            CREATE TABLE %s (
            date TEXT NOT NULL,
            contract_id INTEGER NOT NULL,
            station_number INTEGER NOT NULL,
            min_bikes INTEGER NOT NULL,
            max_bikes INTEGER NOT NULL,
            min_slots INTEGER NOT NULL,
            max_slots INTEGER NOT NULL,
            num_changes INTEGER NOT NULL,
            PRIMARY KEY (date, contract_id, station_number)
            ) WITHOUT ROWID;
            ''' % self.StationsDayTable,
            None,
            "Database error while creating table [%s]" % self.StationsDayTable)

    def _create_contracts_day_table(self):
        if self._arguments.verbose:
            print "Creating table", self.ContractsDayTable
        self._db.execute_single(
            '''
            CREATE TABLE %s (
            start_of_day INTEGER NOT NULL,
            contract_id INTEGER NOT NULL,
            min_bikes INTEGER NOT NULL,
            max_bikes INTEGER NOT NULL,
            PRIMARY KEY (start_of_day, contract_id)
            ) WITHOUT ROWID;
            ''' % self.ContractsDayTable,
            None,
            "Database error while creating table [%s]" % self.ContractsDayTable)

    def _get_operation_samples(self, operation):
        return self._db.execute_fetch_generator(
            '''
            SELECT %s(timestamp) AS timestamp,
                contract_id,
                station_number,
                available_bikes,
                available_bike_stands
            FROM %s.%s
            GROUP BY contract_id, station_number
            ''' % (operation,
                   self._sample_schema,
                   jcd.dao.ShortSamplesDAO.TableNameArchive),
            None,
            "Database error while getting boundary samples",
            True)

    def _get_first_samples(self):
        return self._get_operation_samples("min")

    def _get_last_samples(self):
        return self._get_operation_samples("max")

    def _do_stations(self, date):
        if self._arguments.verbose:
            print "Update table", self.StationsDayTable, "for", date,
        inserted = self._db.execute_single(
            '''
            INSERT OR REPLACE INTO %s
            SELECT ?,
            contract_id,
            station_number,
            MIN(available_bikes),
            MAX(available_bikes),
            MIN(available_bike_stands),
            MAX(available_bike_stands),
            COUNT(timestamp)
            FROM %s.%s
            GROUP BY contract_id, station_number
            ''' % (self.StationsDayTable,
                   self._sample_schema,
                   jcd.dao.ShortSamplesDAO.TableNameArchive),
            (date,),
            "Database error while storing daily station min max into table [%s]" % self.StationsDayTable)
        if self._arguments.verbose:
            print "... %i records" % inserted
        return inserted

    def _do_contract_stats_bike(self, date):
        if self._arguments.verbose:
            print "Update table", self.ContractsDayTable, "for", date,
        contracts = {}
        # initialize with first sample
        samples = self._get_first_samples()
        for sample in samples:
            if sample["contract_id"] not in contracts:
                contracts[sample["contract_id"]] = {
                    "stations": {},
                    # used only for db storage query
                    "date": date,
                    "contract_id": sample["contract_id"]
                }
            stations = contracts[sample["contract_id"]]["stations"]
            stations[sample["station_number"]] = sample["available_bikes"]
        for contract in contracts.itervalues():
            sum_bikes = sum(contract["stations"].itervalues())
            contract["cur"] = sum_bikes
            contract["min"] = sum_bikes
            contract["max"] = sum_bikes
        # read every sample in chronological
        samples = self._db.execute_fetch_generator(
            '''
            SELECT contract_id,
                station_number,
                available_bikes
            FROM %s.%s
            ORDER BY timestamp ASC
            ''' % (self._sample_schema,
                jcd.dao.ShortSamplesDAO.TableNameArchive),
            None,
            "Database error while getting daily samples",
            True)
        # analyze samples and update min-max
        for sample in samples:
            contract = contracts[sample["contract_id"]]
            stations = contract["stations"]
            old_bikes = stations[sample["station_number"]]
            new_bikes = sample["available_bikes"]
            stations[sample["station_number"]] = new_bikes
            delta = new_bikes - old_bikes
            if delta != 0:
                contract["cur"] += delta
                if delta > 0 and contract["cur"] > contract["max"]:
                    contract["max"] = contract["cur"]
                if delta < 0 and contract["cur"] < contract["min"]:
                    contract["min"] = contract["cur"]
        # write to db
        inserted = self._db.execute_many(
            '''
            INSERT OR REPLACE INTO %s (
                start_of_day,
                contract_id,
                min_bikes,
                max_bikes
            )
            VALUES(
                strftime('%%s', :date),
                :contract_id,
                :min,
                :max)
            ''' % self.ContractsDayTable,
            contracts.itervalues(),
            "Database error while storing daily contract min max into table [%s]" % self.ContractsDayTable)
        if self._arguments.verbose:
            print "... %i records" % inserted
        return inserted

    def run(self, date):
        self._do_stations(date)
        self._do_contract_stats_bike(date)

class Activity(object):

    StationsDayTable = "activity_stations_day"
    ContractsDayTable = "activity_contracts_day"
    GlobalDayTable = "activity_global_day"

    StationsWeekTable = "activity_stations_week"
    ContractsWeekTable = "activity_contracts_week"
    GlobalWeekTable = "activity_global_week"

    StationsMonthTable = "activity_stations_month"
    ContractsMonthTable = "activity_contracts_month"
    GlobalMonthTable = "activity_global_month"

    StationsYearTable = "activity_stations_year"
    ContractsYearTable = "activity_contracts_year"
    GlobalYearTable = "activity_global_year"

    def __init__(self, db, sample_schema, arguments):
        self._db = db<commit_msg>sum up min max contracts into globals<commit_after>#! /usr/bin/env python

import sys
import sqlite3
import argparse

import jcd.common
import jcd.dao

class MinMax(object):

    StationsDayTable = "minmax_stations_day"
    ContractsDayTable = "minmax_contracts_day"
    GlobalsDayTable = "minmax_globals_day"

    def __init__(self, db, sample_schema, arguments):
        self._db = db
        self._sample_schema = sample_schema
        self._arguments = arguments
        assert self._db is not None
        assert self._sample_schema is not None
        assert self._arguments is not None
        self._create_tables_if_necessary()

    def _create_tables_if_necessary(self):
        if not self._db.has_table(self.StationsDayTable):
            self._create_stations_day_table()
        if not self._db.has_table(self.ContractsDayTable):
            self._create_contracts_day_table()
        if not self._db.has_table(self.GlobalsDayTable):
            self._create_globals_day_table()

    def _create_stations_day_table(self):
        if self._arguments.verbose:
            print "Creating table", self.StationsDayTable
        self._db.execute_single(
            '''
            CREATE TABLE %s (
            date TEXT NOT NULL,
            contract_id INTEGER NOT NULL,
            station_number INTEGER NOT NULL,
            min_bikes INTEGER NOT NULL,
            max_bikes INTEGER NOT NULL,
            min_slots INTEGER NOT NULL,
            max_slots INTEGER NOT NULL,
            num_changes INTEGER NOT NULL,
            PRIMARY KEY (date, contract_id, station_number)
            ) WITHOUT ROWID;
            ''' % self.StationsDayTable,
            None,
            "Database error while creating table [%s]" % self.StationsDayTable)

    def _create_contracts_day_table(self):
        if self._arguments.verbose:
            print "Creating table", self.ContractsDayTable
        self._db.execute_single(
            '''
            CREATE TABLE %s (
            start_of_day INTEGER NOT NULL,
            contract_id INTEGER NOT NULL,
            min_bikes INTEGER NOT NULL,
            max_bikes INTEGER NOT NULL,
            PRIMARY KEY (start_of_day, contract_id)
            ) WITHOUT ROWID;
            ''' % self.ContractsDayTable,
            None,
            "Database error while creating table [%s]" % self.ContractsDayTable)

    def _create_globals_day_table(self):
        if self._arguments.verbose:
            print "Creating table", self.GlobalsDayTable
        self._db.execute_single(
            '''
            CREATE TABLE %s (
                start_of_day INTEGER NOT NULL,
                min_bikes INTEGER NOT NULL,
                max_bikes INTEGER NOT NULL,
                PRIMARY KEY (start_of_day)
            ) WITHOUT ROWID;
            ''' % self.GlobalsDayTable,
            None,
            "Database error while creating table [%s]" % self.GlobalsDayTable)

    def _get_operation_samples(self, operation):
        return self._db.execute_fetch_generator(
            '''
            SELECT %s(timestamp) AS timestamp,
                contract_id,
                station_number,
                available_bikes,
                available_bike_stands
            FROM %s.%s
            GROUP BY contract_id, station_number
            ''' % (operation,
                   self._sample_schema,
                   jcd.dao.ShortSamplesDAO.TableNameArchive),
            None,
            "Database error while getting boundary samples",
            True)

    def _get_first_samples(self):
        return self._get_operation_samples("min")

    def _get_last_samples(self):
        return self._get_operation_samples("max")

    def _do_stations(self, date):
        if self._arguments.verbose:
            print "Update table", self.StationsDayTable, "for", date,
        inserted = self._db.execute_single(
            '''
            INSERT OR REPLACE INTO %s
            SELECT ?,
            contract_id,
            station_number,
            MIN(available_bikes),
            MAX(available_bikes),
            MIN(available_bike_stands),
            MAX(available_bike_stands),
            COUNT(timestamp)
            FROM %s.%s
            GROUP BY contract_id, station_number
            ''' % (self.StationsDayTable,
                   self._sample_schema,
                   jcd.dao.ShortSamplesDAO.TableNameArchive),
            (date,),
            "Database error while storing daily station min max into table [%s]" % self.StationsDayTable)
        if self._arguments.verbose:
            print "... %i records" % inserted
        return inserted

    def _do_contract_stats_bike(self, date):
        if self._arguments.verbose:
            print "Update table", self.ContractsDayTable, "for", date,
        contracts = {}
        # initialize with first sample
        samples = self._get_first_samples()
        for sample in samples:
            if sample["contract_id"] not in contracts:
                contracts[sample["contract_id"]] = {
                    "stations": {},
                    # used only for db storage query
                    "date": date,
                    "contract_id": sample["contract_id"]
                }
            stations = contracts[sample["contract_id"]]["stations"]
            stations[sample["station_number"]] = sample["available_bikes"]
        for contract in contracts.itervalues():
            sum_bikes = sum(contract["stations"].itervalues())
            contract["cur"] = sum_bikes
            contract["min"] = sum_bikes
            contract["max"] = sum_bikes
        # read every sample in chronological
        samples = self._db.execute_fetch_generator(
            '''
            SELECT contract_id,
                station_number,
                available_bikes
            FROM %s.%s
            ORDER BY timestamp ASC
            ''' % (self._sample_schema,
                jcd.dao.ShortSamplesDAO.TableNameArchive),
            None,
            "Database error while getting daily samples",
            True)
        # analyze samples and update min-max
        for sample in samples:
            contract = contracts[sample["contract_id"]]
            stations = contract["stations"]
            old_bikes = stations[sample["station_number"]]
            new_bikes = sample["available_bikes"]
            stations[sample["station_number"]] = new_bikes
            delta = new_bikes - old_bikes
            if delta != 0:
                contract["cur"] += delta
                if delta > 0 and contract["cur"] > contract["max"]:
                    contract["max"] = contract["cur"]
                if delta < 0 and contract["cur"] < contract["min"]:
                    contract["min"] = contract["cur"]
        # write to db
        inserted = self._db.execute_many(
            '''
            INSERT OR REPLACE INTO %s (
                start_of_day,
                contract_id,
                min_bikes,
                max_bikes
            )
            VALUES(
                strftime('%%s', :date),
                :contract_id,
                :min,
                :max)
            ''' % self.ContractsDayTable,
            contracts.itervalues(),
            "Database error while storing daily contract min max into table [%s]" % self.ContractsDayTable)
        if self._arguments.verbose:
            print "... %i records" % inserted
        return inserted

    def _do_globals(self, date):
        if self._arguments.verbose:
            print "Update table", self.GlobalsDayTable, "for", date,
        # sum up contracts into globals
        inserted = self._db.execute_single(
            '''
            INSERT OR REPLACE INTO %s
                SELECT start_of_day,
                    sum(min_bikes),
                    sum(max_bikes)
                FROM %s
                WHERE start_of_day = strftime('%%s', ?, 'start of day')
                GROUP BY start_of_day
            ''' % (self.GlobalsDayTable, self.ContractsDayTable),
            (date,),
            "Database error while storing daily global min max into table [%s]" % self.ContractsDayTable)
        if self._arguments.verbose:
            print "... %i records" % inserted
        return inserted

    def run(self, date):
        self._do_stations(date)
        self._do_contract_stats_bike(date)
        self._do_globals(date)

class Activity(object):

    StationsDayTable = "activity_stations_day"
    ContractsDayTable = "activity_contracts_day"
    GlobalDayTable = "activity_global_day"

    StationsWeekTable = "activity_stations_week"
    ContractsWeekTable = "activity_contracts_week"
    GlobalWeekTable = "activity_global_week"

    StationsMonthTable = "activity_stations_month"
    ContractsMonthTable = "activity_contracts_month"
    GlobalMonthTable = "activity_global_month"

    StationsYearTable = "activity_stations_year"
    ContractsYearTable = "activity_contracts_year"
    GlobalYearTable = "activity_global_year"

    def __init__(self, db, sample_schema, arguments):
        self._db = db
{'fraction_non_alphanumeric': 0.0831217506952001, 'fraction_numerical': 0.0004836174585902551, 'mean_word_length': 1.86161563743297, 'pattern_counts': {'":': 6, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '444673', 'n_tokens_mistral': 4536, 'n_tokens_neox': 4306, 'n_words': 1027}