Background

I will be building on the Python code we wrote in Part 2: Query the API to move our data into a database.

Goals

  1. Add functionality to our code from Part 2 that takes a Pandas Data Frame and puts it in our MySQL database.
  2. Create separate helper functions that create a SQLAlchemy Engine object, make API Requests, and query our MySQL database for the most recent date.
  3. Integrate these helper functions into two main functions: one to pull and save all data starting from the oldest date in the Bike Arlington database and one to pull only the data missing from our MySQL database.
  4. Save all Bike Arlington data on the details of each Bikeometer and all data on the actual bicycle counts to our MySQL database.

Note: If I were to do this project again, I’d break the functions up into smaller functions to make testing easier. A common recommendation is that each function should only do one thing. If you are describing the functionality and you have to use the word ‘and’, you should break the function up.

Step 1: Migrating Bikeometer Details

    ## Pandas .to_sql Method

We will utilize the Pandas module to move our data into MySQL with the to_sql method. This method will operate on our dataframe and we will pass in a few parameters. First is our table name that we will create. Second is our engine that was created using SQLAlchemy. Third we specify to not create a column as the index. Last, we specify that if our table already exists in the database, add our data to the table without touching the existing data.

df.to_sql('counts_daily', con=engine, index=False, if_exists='append')

Let’s work on adding the to_sql method to our code that pulls the Bikeometer details.

First, let’s define a helper function that will create a SQLAlchemy engine object. Let’s call the function ‘create_new_engine’. All we have to do is pass in the name of the database as an argument (bikeometers_db).

Connect to the database

Check out Part 3 to learn how I connect to my database.

Below I define a function that will connect to my database.

import os
from sqlalchemy import create_engine
# Workaround to get this document to Knit
# Created temp user name that will be deleted once the post is uploaded.
def create_new_engine(db_name):
  return create_engine(f'mysql+mysqldb://{os.environ["arlington_user"]}:{os.environ["arlington_password"]}@localhost/bikeometers_db', echo=False)

Define the function

I’ve taken the get_bikeometer_details function from Part 2 as well as the GET request steps and made them into a single function bikeometer_to_sql.

import requests
import re
import xml.etree.ElementTree as ET
import pandas as pd

def bikeometer_to_sql():
    '''
    Makes a GET request to the Bike Arlington API using the GetAllCounters 
    method as a parameter. The API returns a response object that is first
    converted to a string, cleaned, and converted to an XML object. The XML 
    object is then parsed for the relevant information, and added to a list.
    Each list, representing a single Bikeometer, is converted to a tuple and added 
    to a final list which is converted to a dataframe and uploaded to the database. 
     '''
    # Calls our helper function to create an engine.
    engine = create_new_engine('bikeometers_db')
    url = 'http://webservices.commuterpage.com/counters.cfc?wsdl'
    # Defines the method in a dictionary used to make the request
    counter_reqest_methods = {'method': 'GetAllCounters'}
    # Assigns response object to variable 'response'
    response = requests.get(url, params=counter_reqest_methods)
    # Save the content of that request (string) to memory 
    string_data = response.text
    # Clean the string
    clean_string_data = re.sub(r'[\n|\t]', '', string_data)
    # Convert string to XML object
    root = ET.fromstring(clean_string_data)
    # Create the empty list that will include [(Name, counterID, Lat, Long, Region, region_id)(...)]
    bikeometer_details = []
    # Iterate through the children, grandchildren, and great-grandchildren and grab the data  
    for child in root:
        # From child 'counter' gets the attribute 'id' of the counter and adds it to single_list
        single_list = [child.get('id')]
        # Loops through the grandchildren of root
        for grandchild in list(child):   
            if grandchild.text != None and grandchild.tag != 'description' and grandchild.tag != 'trail_id' and grandchild.tag != 'trail_name':
                # If the grandchild is region, loop through region and grab the grandchildren data
                if grandchild.tag == 'region':  
                    for great_grandchild in list(grandchild): 
                        single_list.append(great_grandchild.text)
                # If the grandchild is not region, the data is available in grandchild.text
                else: 
                    single_list.append(grandchild.text)
        # Cast the list into a tuple making it easier to migrate data to the database
        single_tuple = tuple(single_list)
        # Appends tuples to the list
        bikeometer_details.append(single_tuple)
    # Establishes the names of the columns
    columns = ('bikeometer_id', 'name', 'latitude', 'longitude', 'region', 'region_id')
    # Creates a pandas data frame
    df = pd.DataFrame(data=bikeometer_details, columns = columns)
    # Assigns 'types' to each column
    df[["name", "latitude", "longitude", "region", 'region_id']] = df[["name", "latitude", "longitude", "region", 'region_id']].astype('str')
    df[["bikeometer_id"]] = df[["bikeometer_id"]].astype('int')
    # Moves data to MySQL
    df.to_sql('bikeometer_details', con=engine, index=False, if_exists='replace')

Test the function

Now we run the function.

bikeometer_to_sql()

Let’s check to see what our new table looks like by executing a MySQL statement.

I’m going to run the MySQL commands from R so first I’ll establish a connection to the database through R. You can also run the MySQL commands in the MysQL Workbench or the MySQL Command Line.

library(DBI)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={MySQL ODBC 8.0 Unicode Driver};", 
                 server = "localhost", db = "bikeometers_db", user = Sys.getenv("arlington_user"), password = Sys.getenv("arlington_password"))
SELECT * FROM bikeometer_details
Displaying records 1 - 10
bikeometer_id name latitude longitude region region_id
33 110 Trail 38.885315 -77.065022 Arlington 1
30 14th Street Bridge 38.874260 -77.044610 Arlington 1
43 15th Street NW 38.907470 -77.034610 DC 3
24 Ballston Connector 38.882950 -77.121235 Arlington 1
59 Bluemont Connector 38.880440 -77.119290 Arlington 1
56 BWP Counter 38.933140 -76.938320 Prince George’s County 5
47 Capital Crescent Trail #1 38.979500 -77.096760 Montgomery County 4
48 Capital Crescent Trail #2 38.943070 -77.115660 Montgomery County 4
10 CC Connector 38.857702 -77.047373 Arlington 1
20 Clarendon EB bike lane 38.888780 -77.090421 Arlington 1

Now that we have our bikeometer_details table in our database, we won’t need to run this code again unless a new Bikeometer is added.

Next, we will request the daily bike counts for every day since a counter was installed and save it in our database.

Step 2: Migrating Bike Counts

Again, we will build on the code from Part 2.

Find the oldest Bikeometer

In Part 2, we created a function called api_counts_to_list which pulled the bicycle counts using an arbitrary hard-coded start date, end date, and a single Bikeometer. However, now we need to pull the data for all Bikeometers and all avaiable dates.

To make our life easier, let’s select the oldest Bikeometer and use it’s start date as our start date for all Bikeometers. Our function is written so that if there is no data for a requested date, our function just skips those dates and moves on without error.

We will call the built-in Bike Arlington API method: ‘getMinDates’ on all the Bikeometer IDs in the below bikeometer_id_list.

bikeometer_id_list = ['33','30','43','24','59','56','47','48','10','20',
                           '35','57','18','6', '3','58','61','62','38','44','14',
                           '60','5','42','37','27','26','8','7','51', '52',
                           '45','22','21','36','34','41','9','39','16','15',
                           '54','55','31','28','11','2','25','19']

Now we will create a simple for loop to call the getMinDates method on all the Bikeometers.

# Assign the url of the 
url = 'http://webservices.commuterpage.com/counters.cfc?wsdl'

# Defines the method in a dictionary used to make the request
for bikeometer_id in bikeometer_id_list: 
  counter_reqest_methods = {'method': 'getMinDates', 'counterID': bikeometer_id}
  # Save the GetAllCounters request to memory
  response = requests.get(url, params=counter_reqest_methods)
  response.text
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>07/22/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/10/2013</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/22/2014</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/06/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>01/01/2019</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>03/19/2017</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>04/25/2016</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>04/25/2016</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>09/02/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/27/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/11/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>07/05/2017</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/28/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>03/06/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/08/2010</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>04/16/2020</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>01/01/2019</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>01/01/2019</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/18/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/22/2014</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/04/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>01/01/2019</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>04/01/2010</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>03/30/2016</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/18/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/17/2013</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>06/19/2013</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>08/12/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>08/11/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>06/03/2017</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>06/01/2021</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/22/2014</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/21/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>03/31/2013</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/04/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>05/15/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/04/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>09/01/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/04/2015</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/01/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/21/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>06/03/2017</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>06/03/2017</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/10/2013</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>04/01/2014</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>10/14/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>02/01/2011</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>12/10/2012</string></var></struct></data></wddxPacket>"
## "<wddxPacket version='1.0'><header/><data><struct><var name='STARTDATE'><string>11/21/2012</string></var></struct></data></wddxPacket>"

Skimming through the returned responses, we can see that the first date was 4/1/2010. This will be our first start date in our function.

Create helper functions

Ultimately, we will have two functions when we are finished: one that pulls all the dates starting from 4/1/2010 (all_counts_by_date_to_sql) and one that pulls only the new dates.(new_counts_by_date_to_sql)

To help us out, we will create a helper function based on the api_counts_to_list function we made in Part 2.

The goal is to have the main functions all_counts_by_date_to_sql and new_counts_by_date_to_sql pass in the bikeometer_id, start date and end date to our helper function api_counts_to_list. If you have no data in your database, you would call the all_counts_by_date_to_sql function to make requests starting on 4/1/2010. If you already have data in your MySQL database, the new_counts_by_date_to_sql function will query your database and only request data for dates not in your database.

Note: As I write this up, I’m realizing you could write one function that would query your MySQL database and depending upon the response, it could call the appropriate function using an if/else statement, but, as they say, that’s not MVP.

Here is the helper function that will request data from the Bike Arlington API using the ‘GetCountInDateRange’ method. This function was modified from Part 2 to accept arguments for a bikeometer_id, start_date, end_date, and count_in_date_range_list (a list to hold all the count data). We will hard-code the mode=‘B’ to request bike data not pedestrian, interval=‘d’ to request counts by day, start_time and end_time to get data for the entire day, and direction=’’ to pull both the inbound and outbound directions that the Bikeometers can sense.

Note: The Bike Arlington documentation does not mention this, but some Bikeometers can’t distinguish between inbound or outbound and their counts instead use the label ‘A’. By leaving the direction field blank, our function will include all ‘A’ directions too.

We will be using a few functions from the datetime module so we start by importing them.

from datetime import date, datetime, timedelta

def api_counts_to_list(bikeometer_id, 
                               start_date,
                               end_date, 
                               count_in_date_range_list,
                               mode='B', 
                               interval='d', 
                               start_time='0:00', 
                               end_time= '23:59', 
                               direction='') -> list:
    request_parameters = {'method': 'GetCountInDateRange',
            'counterID': str(bikeometer_id),
            'startDate': start_date,
            'endDate': end_date,
            'mode': str(mode),
            'interval': str(interval),
            'direction': direction}
    response = requests.get(url, params=request_parameters)
        #TODO: Add except for non-200 response
    # Convert response to a string
    xml_data = response.text
    # Clean the data
    clean_xml_data = re.sub(r'[\n|\t]', '', xml_data)
    # Cast the string to an XML element
    tree = ET.fromstring(clean_xml_data)
    # Create the empty dictionary to put the data in
    #CountInDateRangeDict = {}
    # Parse the GetCountInDateRange XML file for the count and date and save CountInDateRangeDict
    for type_tag in tree.findall('count'):
        count = type_tag.get('count')
        date = type_tag.get('date')
        # Converts counter date to a date object
        date = datetime.strptime(date, '%m/%d/%Y').date()
        year = date.year
        month = date.month
        day = date.day
        month_day = f'{month}_{day}'
        direction = type_tag.get('direction')
        if date.weekday() <= 4:
            is_weekend = 0
        else:
            is_weekend = 1
        single_tuple = (bikeometer_id, date, direction, count, is_weekend, year, month, day, month_day)
        count_in_date_range_list.append(single_tuple)
    return count_in_date_range_list

Create all_counts_by_date_to_sql function

Now that we have our helper function complete, let’s create the all_counts_by_date_to_sql which will pull all the data starting on 4/1/2010.

When designing our function, it’s important to remember that the Bike Arlington API will only allow requests of 1 year or less. There are many ways to get the data into your database. I chose to pull all the necessary data before saving it all in my database in one chunk. You may choose to incrementally pull and save the data 1 year at a time. Both ways have their pros and cons and it’s up to you to decide and justify your decision.

def all_counts_by_date_to_sql():
    # Creates the parameters to drive the connection
    engine = create_new_engine('bikeometers_db')
    # Tests the connection and throws an error if not established
    engine.connect()
    # Bike Arlington API only accepts query ranges of 1 year or less
    start_date = date(year=2010, month=4, day=1)
    end_date = date(year=2011, month=4, day=1)
    # Create a list to hold count data
    count_in_date_range_list = []
    # We will pull data up to yesterday's date
    while end_date <= date.today() - timedelta(days=1):  
    # Hard-coded the bikeometer_id list to not stress the server making unnecessary requests 
        bikeometer_id_list = ['33','30','43','24','59','56','47','48','10','20',
                           '35','57','18','6', '3','58','61','62','38','44','14',
                           '60','5','42','37','27','26','8','7','51', '52',
                           '45','22','21','36','34','41','9','39','16','15',
                           '54','55','31','28','11','2','25','19']
        for bikeometer_id in bikeometer_id_list:
            # Dates YYYY-MM-DD format converted to MM-DD-YYY and made into a string to search Counter API
            clean_start_date = f'{start_date.month}/{start_date.day}/{start_date.year}'
            clean_end_date = f'{end_date.month}/{end_date.day}/{end_date.year}'
            # Calls the get_count_in_date_range function and passes in hard-coded
            # parameters like the start_date, which is the first datapoint in
            # Bike Arlington server. Optimally, these would not be hardcoded to  
            # make the porgram more future-proof.
            api_counts_to_list(bikeometer_id, clean_start_date, clean_end_date, \
            count_in_date_range_list, interval='d')
        # If end date is yesterday's date, congratulations, you pulled all available dates
        # We won't pull today's date in case the data has not been uploaded to their servers yet
        if end_date == date.today() - timedelta(days=1):
            break                
        # Adds a year to start date and end date
        start_date = start_date.replace(year = start_date.year + 1)
        end_date = end_date.replace(year = end_date.year + 1)
        # If end date is after yesterday's date, set the end date to yesterday and pull the data one last time
        if end_date >= date.today():
            end_date = date.today() - timedelta(days=1)
    columns = ('bikeometer_id', 'date', 'direction', 'count', 'is_weekend', 'year', 'month', 'day', 'month_day')
    df = pd.DataFrame(count_in_date_range_list, columns=columns)
    # Makes bikeometer_id coulmn type int
    df[["bikeometer_id"]] = df[["bikeometer_id"]].astype('int')
    # Replaces table counts, use if_exists='append' to insert new values into the table
    df.to_sql('counts_daily', con=engine, index=False, if_exists='replace')
    # Queries your and returns a message to the user indicating the last day in their server
    with engine.connect() as con:
        date_list = con.execute('SELECT MAX(Date) FROM counts_daily')
        for day in date_list:
            last_day = day[0]
            print(f'The newest date in counts_daily is {last_day}')
    engine.dispose()

If you look at the last bit of code in the above function, after the data is saved to my database I make a query to my database to return the most recent date. If the date returned is yesterday’s date, it’s a good sign that the data was uploaded successfully.

Now that the function is defined, let’s run it.

all_counts_by_date_to_sql()

Let’s take a look at our table!

SELECT * FROM counts_daily GROUP BY bikeometer_id ORDER BY bikeometer_id asc;
Displaying records 1 - 10
bikeometer_id date direction count is_weekend year month day month_day
2 2011-02-01 I 6 0 2011 2 1 2_1
3 2010-12-08 I 16 0 2010 12 8 12_8
5 2010-04-01 I 344 0 2010 4 1 4_1
7 2011-08-11 I 527 0 2011 8 11 8_11
8 2011-08-12 I 447 0 2011 8 12 8_12
9 2011-09-01 I 1243 0 2011 9 1 9_1
10 2011-09-02 I 343 0 2011 9 2 9_2
11 2011-10-14 I 155 0 2011 10 14 10_14
14 2012-12-04 A 73 0 2012 12 4 12_4
15 2012-11-21 A 19 0 2012 11 21 11_21

Create new_counts_by_date_to_sql function

Now that we have our all_counts_by_date_to_sql function, we need our new_counts_by_date_to_sql function which will only pull the new dates.

To only pull the new dates, let’s make a helper function that will return the most recent date in our MySQL database.

def last_sql_date_counts_daily(engine):
    ''' Returns the last date in the MySQL database as a datetime object'''
    with engine.connect() as con:
        last_date = con.execute('SELECT MAX(Date) FROM counts_daily')
    for day in last_date:
        date = day[0]
    return date

With our helper function defined, I’ll use the previous function as a template and just change how the start date and end date variables are first defined.

I also added two checks in the beginning. The first is if the last_sql_date_counts_daily returns None, it most likely means there is no data in the database and the function stops. The second check is if yesterday’s date is already in the database, the function returns ‘No new data available’ to the user without making any API requests. Everything else should be the same.

def new_counts_by_day_to_sql():
    engine = create_new_engine('counts')
    # Establish connection to MySQL database before making all the API calls.
    #TODO: If no data is in the MySQL database, custom except error
    # This prevents the program from pulling today's date
    start_date = last_sql_date_counts_daily(engine) + timedelta(days=1)
    if start_date == None:
        print('No data found in MySQL database.')
        print('Please use all_counts_to_sql() function')
        return None
    if start_date >= date.today() - timedelta(days=1):
        #TODO: throw an exception error instead of print
        print('No new data avaiable')
        return None
    end_date = start_date.replace(year = start_date.year + 1)
    if end_date >= date.today():
        end_date = date.today() - timedelta(days=1)
     ## Test db Connection##
    # Creates the parameters to drive the connection
    # Bike Arlington API only accepts query ranges of 1 year or less
    count_in_date_range_list = []   
    while end_date <= date.today() - timedelta(days=1):  
    # Hardcoded the counterID list to not stress the server
        bikeometer_id_list = ['33','30','43','24','59','56','47','48','10','20',
                           '35','57','18','6', '3','58','61','62','38','44','14',
                           '60','5','42','37','27','26','8','7','51', '52',
                           '45','22','21','36','34','41','9','39','16','15',
                           '54','55','31','28','11','2','25','19']
        for bikeometer_id in bikeometer_id_list:
            # Dates YYYY-MM-DD format converted to MM-DD-YYY and made into a string to search Counter API
            clean_start_date = f'{start_date.month}/{start_date.day}/{start_date.year}'
            clean_end_date = f'{end_date.month}/{end_date.day}/{end_date.year}'
            # Calls the get_count_in_date_range function and passes in hard-coded
            # parameters like the start_date, which is the first datapoint in
            # Bike Arlington server. Optimally, these would not be hardcoded  
            # make the porgram more future-proof.
            api_counts_to_list(bikeometer_id, clean_start_date, clean_end_date, count_in_date_range_list, interval='d')
        # If end date is yesterday's date, congratulations, you pulled all available dates
        # We won't pull today's date in case the data has not been uploaded to their servers yet
        if end_date == date.today() - timedelta(days=1):
            break                
        # Adds a year to start date and end date
        start_date = start_date.replace(year = start_date.year + 1)
        end_date = end_date.replace(year = end_date.year + 1)
        # If end date is after yesterday's date, set the end date to yesterdayand pull the data one last time
        if end_date >= date.today():
            end_date = date.today() - timedelta(days=1)    
    columns = ('bikeometer_id', 'date', 'direction', 'count', 'is_weekend', 'year', 'month', 'day', 'month_day')
    df = pd.DataFrame(count_in_date_range_list, columns=columns)
    df[["bikeometer_id"]] = df[["bikeometer_id"]].astype('int')
    # Replaces table counts, use if_exists='append' to insert new values into the table
    df.to_sql('counts_daily', con=engine, index=False, if_exists='append')
    # Reads the table
    #df2 = pd.read_sql('counters', con=engine)
    with engine.connect() as con:
        date_list = con.execute('SELECT MAX(Date) FROM counts_daily')
        for day in date_list:
            last_day = day[0]
            print(f'The newest date in counts_daily is {last_day}')
    # Do we need engine.dispose?
    engine.dispose()
new_counts_by_day_to_sql()
## No new data avaiable

I ran the previous function today so this function returns ‘No new data available’. Try it for yourself tomorrow and compare the time it takes to pull 1 day’s worth of data compared to over 10 years worth of data.

In the next post I’ll start creating visualizations of the data.

LS0tDQp0aXRsZTogIlZpc3VhbGl6aW5nIEFybGluZ3RvbiBCaWtvbWV0ZXJzIg0Kc3VidGl0bGU6ICJQYXJ0IDQ6IFNhdmUgeW91ciBEYXRhIGluIGEgTXlTUUwgRGF0YWJhc2UiDQpvdXRwdXQ6DQogIGh0bWxfZG9jdW1lbnQ6IA0KICAgIHRvYzogeWVzDQogICAgdG9jX2RlcHRoOiAyDQogICAgdG9jX2Zsb2F0OiB5ZXMNCiAgICBoaWdobGlnaHQ6IHplbmJ1cm4NCiAgICBjb2RlX2Rvd25sb2FkOiB0cnVlDQogICAgaW5jbHVkZXM6DQogICAgICBpbl9oZWFkZXI6IGhlYWRlci5odG1sDQotLS0NClwgDQpcIA0KDQojIEJhY2tncm91bmQNCkkgd2lsbCBiZSBidWlsZGluZyBvbiB0aGUgUHl0aG9uIGNvZGUgd2Ugd3JvdGUgaW4gW1BhcnQgMjogUXVlcnkgdGhlIEFQSV0oaHR0cHM6Ly9uYXRoYW5zcHJvamVjdHMuY29tL3BhcnRfMl9xdWVyeV90aGVfYXBpLmh0bWwpIHRvIG1vdmUgb3VyIGRhdGEgaW50byBhIGRhdGFiYXNlLiANCg0KIyBHb2Fscw0KMS4gQWRkIGZ1bmN0aW9uYWxpdHkgdG8gb3VyIGNvZGUgZnJvbSBbUGFydCAyXShodHRwczovL25hdGhhbnNwcm9qZWN0cy5jb20vcGFydF8yX3F1ZXJ5X3RoZV9hcGkuaHRtbCkgdGhhdCB0YWtlcyBhIFBhbmRhcyBEYXRhIEZyYW1lIGFuZCBwdXRzIGl0IGluIG91ciBNeVNRTCBkYXRhYmFzZS4gDQoyLiBDcmVhdGUgc2VwYXJhdGUgKipoZWxwZXIgZnVuY3Rpb25zKiogdGhhdCBjcmVhdGUgYSAqU1FMQWxjaGVteSBFbmdpbmUqIG9iamVjdCwgbWFrZSBBUEkgUmVxdWVzdHMsIGFuZCBxdWVyeSBvdXIgTXlTUUwgZGF0YWJhc2UgZm9yIHRoZSBtb3N0IHJlY2VudCBkYXRlLg0KMy4gSW50ZWdyYXRlIHRoZXNlIGhlbHBlciBmdW5jdGlvbnMgaW50byB0d28gbWFpbiBmdW5jdGlvbnM6IG9uZSB0byBwdWxsIGFuZCBzYXZlIGFsbCBkYXRhIHN0YXJ0aW5nIGZyb20gdGhlIG9sZGVzdCBkYXRlIGluIHRoZSBCaWtlIEFybGluZ3RvbiBkYXRhYmFzZSBhbmQgb25lIHRvIHB1bGwgb25seSB0aGUgZGF0YSBtaXNzaW5nIGZyb20gb3VyIE15U1FMIGRhdGFiYXNlLiANCjQuIFNhdmUgYWxsIEJpa2UgQXJsaW5ndG9uIGRhdGEgb24gdGhlIGRldGFpbHMgb2YgZWFjaCBCaWtlb21ldGVyIGFuZCBhbGwgZGF0YSBvbiB0aGUgYWN0dWFsIGJpY3ljbGUgY291bnRzIHRvIG91ciBNeVNRTCBkYXRhYmFzZS4NCg0KTm90ZTogSWYgSSB3ZXJlIHRvIGRvIHRoaXMgcHJvamVjdCBhZ2FpbiwgSSdkIGJyZWFrIHRoZSBmdW5jdGlvbnMgdXAgaW50byBzbWFsbGVyIGZ1bmN0aW9ucyB0byBtYWtlIHRlc3RpbmcgZWFzaWVyLiBBIGNvbW1vbiByZWNvbW1lbmRhdGlvbiBpcyB0aGF0IGVhY2ggZnVuY3Rpb24gc2hvdWxkIG9ubHkgZG8gb25lIHRoaW5nLiBJZiB5b3UgYXJlIGRlc2NyaWJpbmcgdGhlIGZ1bmN0aW9uYWxpdHkgYW5kIHlvdSBoYXZlIHRvIHVzZSB0aGUgd29yZCAnYW5kJywgeW91IHNob3VsZCBicmVhayB0aGUgZnVuY3Rpb24gdXAuDQoNCiMgU3RlcCAxOiBNaWdyYXRpbmcgQmlrZW9tZXRlciBEZXRhaWxzDQpcIA0KXCANCiMjIFBhbmRhcyAudG9fc3FsIE1ldGhvZCAgDQoNCldlIHdpbGwgdXRpbGl6ZSB0aGUgUGFuZGFzIG1vZHVsZSB0byBtb3ZlIG91ciBkYXRhIGludG8gTXlTUUwgd2l0aCB0aGUgW3RvX3NxbF0oaHR0cHM6Ly9wYW5kYXMucHlkYXRhLm9yZy9kb2NzL3JlZmVyZW5jZS9hcGkvcGFuZGFzLkRhdGFGcmFtZS50b19zcWwuaHRtbCkgbWV0aG9kLiBUaGlzIG1ldGhvZCB3aWxsIG9wZXJhdGUgb24gb3VyIGRhdGFmcmFtZSBhbmQgd2Ugd2lsbCBwYXNzIGluIGEgZmV3IHBhcmFtZXRlcnMuIEZpcnN0IGlzIG91ciB0YWJsZSBuYW1lIHRoYXQgd2Ugd2lsbCBjcmVhdGUuIFNlY29uZCBpcyBvdXIgZW5naW5lIHRoYXQgd2FzIGNyZWF0ZWQgdXNpbmcgU1FMQWxjaGVteS4gVGhpcmQgd2Ugc3BlY2lmeSB0byBub3QgY3JlYXRlIGEgY29sdW1uIGFzIHRoZSBpbmRleC4gTGFzdCwgd2Ugc3BlY2lmeSB0aGF0IGlmIG91ciB0YWJsZSBhbHJlYWR5IGV4aXN0cyBpbiB0aGUgZGF0YWJhc2UsIGFkZCBvdXIgZGF0YSB0byB0aGUgdGFibGUgd2l0aG91dCB0b3VjaGluZyB0aGUgZXhpc3RpbmcgZGF0YS4NCg0KYGBge3B5dGhvbiBldmFsPUZBTFNFfQ0KZGYudG9fc3FsKCdjb3VudHNfZGFpbHknLCBjb249ZW5naW5lLCBpbmRleD1GYWxzZSwgaWZfZXhpc3RzPSdhcHBlbmQnKQ0KYGBgDQoNCkxldCdzIHdvcmsgb24gYWRkaW5nIHRoZSAqKnRvX3NxbCoqIG1ldGhvZCB0byBvdXIgY29kZSB0aGF0IHB1bGxzIHRoZSBCaWtlb21ldGVyIGRldGFpbHMuDQoNCkZpcnN0LCBsZXQncyBkZWZpbmUgYSAqKmhlbHBlciBmdW5jdGlvbioqIHRoYXQgd2lsbCBjcmVhdGUgYSBTUUxBbGNoZW15ICplbmdpbmUqIG9iamVjdC4gTGV0J3MgY2FsbCB0aGUgZnVuY3Rpb24gJ2NyZWF0ZV9uZXdfZW5naW5lJy4gQWxsIHdlIGhhdmUgdG8gZG8gaXMgcGFzcyBpbiB0aGUgbmFtZSBvZiB0aGUgZGF0YWJhc2UgYXMgYW4gYXJndW1lbnQgKGJpa2VvbWV0ZXJzX2RiKS4NCg0KIyMgQ29ubmVjdCB0byB0aGUgZGF0YWJhc2UNCg0KQ2hlY2sgb3V0IFtQYXJ0IDNdKGh0dHBzOi8vbmF0aGFuc3Byb2plY3RzLmNvbS9wYXJ0XzNfc2V0dXBfeW91cl9kYXRhYmFzZS5odG1sKSB0byBsZWFybiBob3cgSSBjb25uZWN0IHRvIG15IGRhdGFiYXNlLg0KDQpCZWxvdyBJIGRlZmluZSBhIGZ1bmN0aW9uIHRoYXQgd2lsbCBjb25uZWN0IHRvIG15IGRhdGFiYXNlLg0KDQpgYGB7cHl0aG9ufQ0KaW1wb3J0IG9zDQpmcm9tIHNxbGFsY2hlbXkgaW1wb3J0IGNyZWF0ZV9lbmdpbmUNCiMgV29ya2Fyb3VuZCB0byBnZXQgdGhpcyBkb2N1bWVudCB0byBLbml0DQojIENyZWF0ZWQgdGVtcCB1c2VyIG5hbWUgdGhhdCB3aWxsIGJlIGRlbGV0ZWQgb25jZSB0aGUgcG9zdCBpcyB1cGxvYWRlZC4NCmRlZiBjcmVhdGVfbmV3X2VuZ2luZShkYl9uYW1lKToNCiAgcmV0dXJuIGNyZWF0ZV9lbmdpbmUoZidteXNxbCtteXNxbGRiOi8ve29zLmVudmlyb25bImFybGluZ3Rvbl91c2VyIl19Ontvcy5lbnZpcm9uWyJhcmxpbmd0b25fcGFzc3dvcmQiXX1AbG9jYWxob3N0L2Jpa2VvbWV0ZXJzX2RiJywgZWNobz1GYWxzZSkNCmBgYA0KDQojIyBEZWZpbmUgdGhlICBmdW5jdGlvbg0KDQpJJ3ZlIHRha2VuIHRoZSAqZ2V0X2Jpa2VvbWV0ZXJfZGV0YWlscyogZnVuY3Rpb24gZnJvbSBbUGFydCAyXShodHRwczovL25hdGhhbnNwcm9qZWN0cy5jb20vcGFydF8yX3F1ZXJ5X3RoZV9hcGkuaHRtbCkgYXMgd2VsbCBhcyB0aGUgR0VUIHJlcXVlc3Qgc3RlcHMgYW5kIG1hZGUgdGhlbSBpbnRvIGEgc2luZ2xlIGZ1bmN0aW9uICpiaWtlb21ldGVyX3RvX3NxbCouDQoNCmBgYHtweXRob24gIH0NCmltcG9ydCByZXF1ZXN0cw0KaW1wb3J0IHJlDQppbXBvcnQgeG1sLmV0cmVlLkVsZW1lbnRUcmVlIGFzIEVUDQppbXBvcnQgcGFuZGFzIGFzIHBkDQoNCmRlZiBiaWtlb21ldGVyX3RvX3NxbCgpOg0KICAgICcnJw0KICAgIE1ha2VzIGEgR0VUIHJlcXVlc3QgdG8gdGhlIEJpa2UgQXJsaW5ndG9uIEFQSSB1c2luZyB0aGUgR2V0QWxsQ291bnRlcnMgDQogICAgbWV0aG9kIGFzIGEgcGFyYW1ldGVyLiBUaGUgQVBJIHJldHVybnMgYSByZXNwb25zZSBvYmplY3QgdGhhdCBpcyBmaXJzdA0KICAgIGNvbnZlcnRlZCB0byBhIHN0cmluZywgY2xlYW5lZCwgYW5kIGNvbnZlcnRlZCB0byBhbiBYTUwgb2JqZWN0LiBUaGUgWE1MIA0KICAgIG9iamVjdCBpcyB0aGVuIHBhcnNlZCBmb3IgdGhlIHJlbGV2YW50IGluZm9ybWF0aW9uLCBhbmQgYWRkZWQgdG8gYSBsaXN0Lg0KICAgIEVhY2ggbGlzdCwgcmVwcmVzZW50aW5nIGEgc2luZ2xlIEJpa2VvbWV0ZXIsIGlzIGNvbnZlcnRlZCB0byBhIHR1cGxlIGFuZCBhZGRlZCANCiAgICB0byBhIGZpbmFsIGxpc3Qgd2hpY2ggaXMgY29udmVydGVkIHRvIGEgZGF0YWZyYW1lIGFuZCB1cGxvYWRlZCB0byB0aGUgZGF0YWJhc2UuIA0KICAgICAnJycNCiAgICAjIENhbGxzIG91ciBoZWxwZXIgZnVuY3Rpb24gdG8gY3JlYXRlIGFuIGVuZ2luZS4NCiAgICBlbmdpbmUgPSBjcmVhdGVfbmV3X2VuZ2luZSgnYmlrZW9tZXRlcnNfZGInKQ0KICAgIHVybCA9ICdodHRwOi8vd2Vic2VydmljZXMuY29tbXV0ZXJwYWdlLmNvbS9jb3VudGVycy5jZmM/d3NkbCcNCiAgICAjIERlZmluZXMgdGhlIG1ldGhvZCBpbiBhIGRpY3Rpb25hcnkgdXNlZCB0byBtYWtlIHRoZSByZXF1ZXN0DQogICAgY291bnRlcl9yZXFlc3RfbWV0aG9kcyA9IHsnbWV0aG9kJzogJ0dldEFsbENvdW50ZXJzJ30NCiAgICAjIEFzc2lnbnMgcmVzcG9uc2Ugb2JqZWN0IHRvIHZhcmlhYmxlICdyZXNwb25zZScNCiAgICByZXNwb25zZSA9IHJlcXVlc3RzLmdldCh1cmwsIHBhcmFtcz1jb3VudGVyX3JlcWVzdF9tZXRob2RzKQ0KICAgICMgU2F2ZSB0aGUgY29udGVudCBvZiB0aGF0IHJlcXVlc3QgKHN0cmluZykgdG8gbWVtb3J5IA0KICAgIHN0cmluZ19kYXRhID0gcmVzcG9uc2UudGV4dA0KICAgICMgQ2xlYW4gdGhlIHN0cmluZw0KICAgIGNsZWFuX3N0cmluZ19kYXRhID0gcmUuc3ViKHInW1xufFx0XScsICcnLCBzdHJpbmdfZGF0YSkNCiAgICAjIENvbnZlcnQgc3RyaW5nIHRvIFhNTCBvYmplY3QNCiAgICByb290ID0gRVQuZnJvbXN0cmluZyhjbGVhbl9zdHJpbmdfZGF0YSkNCiAgICAjIENyZWF0ZSB0aGUgZW1wdHkgbGlzdCB0aGF0IHdpbGwgaW5jbHVkZSBbKE5hbWUsIGNvdW50ZXJJRCwgTGF0LCBMb25nLCBSZWdpb24sIHJlZ2lvbl9pZCkoLi4uKV0NCiAgICBiaWtlb21ldGVyX2RldGFpbHMgPSBbXQ0KICAgICMgSXRlcmF0ZSB0aHJvdWdoIHRoZSBjaGlsZHJlbiwgZ3JhbmRjaGlsZHJlbiwgYW5kIGdyZWF0LWdyYW5kY2hpbGRyZW4gYW5kIGdyYWIgdGhlIGRhdGEgIA0KICAgIGZvciBjaGlsZCBpbiByb290Og0KICAgICAgICAjIEZyb20gY2hpbGQgJ2NvdW50ZXInIGdldHMgdGhlIGF0dHJpYnV0ZSAnaWQnIG9mIHRoZSBjb3VudGVyIGFuZCBhZGRzIGl0IHRvIHNpbmdsZV9saXN0DQogICAgICAgIHNpbmdsZV9saXN0ID0gW2NoaWxkLmdldCgnaWQnKV0NCiAgICAgICAgIyBMb29wcyB0aHJvdWdoIHRoZSBncmFuZGNoaWxkcmVuIG9mIHJvb3QNCiAgICAgICAgZm9yIGdyYW5kY2hpbGQgaW4gbGlzdChjaGlsZCk6ICAgDQogICAgICAgICAgICBpZiBncmFuZGNoaWxkLnRleHQgIT0gTm9uZSBhbmQgZ3JhbmRjaGlsZC50YWcgIT0gJ2Rlc2NyaXB0aW9uJyBhbmQgZ3JhbmRjaGlsZC50YWcgIT0gJ3RyYWlsX2lkJyBhbmQgZ3JhbmRjaGlsZC50YWcgIT0gJ3RyYWlsX25hbWUnOg0KICAgICAgICAgICAgICAgICMgSWYgdGhlIGdyYW5kY2hpbGQgaXMgcmVnaW9uLCBsb29wIHRocm91Z2ggcmVnaW9uIGFuZCBncmFiIHRoZSBncmFuZGNoaWxkcmVuIGRhdGENCiAgICAgICAgICAgICAgICBpZiBncmFuZGNoaWxkLnRhZyA9PSAncmVnaW9uJzogIA0KICAgICAgICAgICAgICAgICAgICBmb3IgZ3JlYXRfZ3JhbmRjaGlsZCBpbiBsaXN0KGdyYW5kY2hpbGQpOiANCiAgICAgICAgICAgICAgICAgICAgICAgIHNpbmdsZV9saXN0LmFwcGVuZChncmVhdF9ncmFuZGNoaWxkLnRleHQpDQogICAgICAgICAgICAgICAgIyBJZiB0aGUgZ3JhbmRjaGlsZCBpcyBub3QgcmVnaW9uLCB0aGUgZGF0YSBpcyBhdmFpbGFibGUgaW4gZ3JhbmRjaGlsZC50ZXh0DQogICAgICAgICAgICAgICAgZWxzZTogDQogICAgICAgICAgICAgICAgICAgIHNpbmdsZV9saXN0LmFwcGVuZChncmFuZGNoaWxkLnRleHQpDQogICAgICAgICMgQ2FzdCB0aGUgbGlzdCBpbnRvIGEgdHVwbGUgbWFraW5nIGl0IGVhc2llciB0byBtaWdyYXRlIGRhdGEgdG8gdGhlIGRhdGFiYXNlDQogICAgICAgIHNpbmdsZV90dXBsZSA9IHR1cGxlKHNpbmdsZV9saXN0KQ0KICAgICAgICAjIEFwcGVuZHMgdHVwbGVzIHRvIHRoZSBsaXN0DQogICAgICAgIGJpa2VvbWV0ZXJfZGV0YWlscy5hcHBlbmQoc2luZ2xlX3R1cGxlKQ0KICAgICMgRXN0YWJsaXNoZXMgdGhlIG5hbWVzIG9mIHRoZSBjb2x1bW5zDQogICAgY29sdW1ucyA9ICgnYmlrZW9tZXRlcl9pZCcsICduYW1lJywgJ2xhdGl0dWRlJywgJ2xvbmdpdHVkZScsICdyZWdpb24nLCAncmVnaW9uX2lkJykNCiAgICAjIENyZWF0ZXMgYSBwYW5kYXMgZGF0YSBmcmFtZQ0KICAgIGRmID0gcGQuRGF0YUZyYW1lKGRhdGE9YmlrZW9tZXRlcl9kZXRhaWxzLCBjb2x1bW5zID0gY29sdW1ucykNCiAgICAjIEFzc2lnbnMgJ3R5cGVzJyB0byBlYWNoIGNvbHVtbg0KICAgIGRmW1sibmFtZSIsICJsYXRpdHVkZSIsICJsb25naXR1ZGUiLCAicmVnaW9uIiwgJ3JlZ2lvbl9pZCddXSA9IGRmW1sibmFtZSIsICJsYXRpdHVkZSIsICJsb25naXR1ZGUiLCAicmVnaW9uIiwgJ3JlZ2lvbl9pZCddXS5hc3R5cGUoJ3N0cicpDQogICAgZGZbWyJiaWtlb21ldGVyX2lkIl1dID0gZGZbWyJiaWtlb21ldGVyX2lkIl1dLmFzdHlwZSgnaW50JykNCiAgICAjIE1vdmVzIGRhdGEgdG8gTXlTUUwNCiAgICBkZi50b19zcWwoJ2Jpa2VvbWV0ZXJfZGV0YWlscycsIGNvbj1lbmdpbmUsIGluZGV4PUZhbHNlLCBpZl9leGlzdHM9J3JlcGxhY2UnKQ0KDQpgYGANCg0KIyMgVGVzdCB0aGUgZnVuY3Rpb24NCk5vdyB3ZSBydW4gdGhlIGZ1bmN0aW9uLg0KYGBge3B5dGhvbiBldmFsPUZBTFNFLCBjYWNoZT1UUlVFfQ0KYmlrZW9tZXRlcl90b19zcWwoKQ0KYGBgDQoNCkxldCdzIGNoZWNrIHRvIHNlZSB3aGF0IG91ciBuZXcgdGFibGUgbG9va3MgbGlrZSBieSBleGVjdXRpbmcgYSBNeVNRTCBzdGF0ZW1lbnQuDQoNCkknbSBnb2luZyB0byBydW4gdGhlIE15U1FMIGNvbW1hbmRzIGZyb20gUiBzbyBmaXJzdCBJJ2xsIGVzdGFibGlzaCBhIGNvbm5lY3Rpb24gdG8gdGhlIGRhdGFiYXNlIHRocm91Z2ggUi4gWW91IGNhbiBhbHNvIHJ1biB0aGUgTXlTUUwgY29tbWFuZHMgaW4gdGhlIE15c1FMIFdvcmtiZW5jaCBvciB0aGUgTXlTUUwgQ29tbWFuZCBMaW5lLg0KDQpgYGB7ciB9DQpsaWJyYXJ5KERCSSkNCmNvbiA8LSBkYkNvbm5lY3Qob2RiYzo6b2RiYygpLCAuY29ubmVjdGlvbl9zdHJpbmcgPSAiRHJpdmVyPXtNeVNRTCBPREJDIDguMCBVbmljb2RlIERyaXZlcn07IiwgDQogICAgICAgICAgICAgICAgIHNlcnZlciA9ICJsb2NhbGhvc3QiLCBkYiA9ICJiaWtlb21ldGVyc19kYiIsIHVzZXIgPSBTeXMuZ2V0ZW52KCJhcmxpbmd0b25fdXNlciIpLCBwYXNzd29yZCA9IFN5cy5nZXRlbnYoImFybGluZ3Rvbl9wYXNzd29yZCIpKQ0KYGBgDQoNCg0KYGBge3NxbCBjb25uZWN0aW9uPWNvbn0NClNFTEVDVCAqIEZST00gYmlrZW9tZXRlcl9kZXRhaWxzDQpgYGANCg0KTm93IHRoYXQgd2UgaGF2ZSBvdXIgYmlrZW9tZXRlcl9kZXRhaWxzIHRhYmxlIGluIG91ciBkYXRhYmFzZSwgd2Ugd29uJ3QgbmVlZCB0byBydW4gdGhpcyBjb2RlIGFnYWluIHVubGVzcyBhIG5ldyBCaWtlb21ldGVyIGlzIGFkZGVkLg0KDQpOZXh0LCB3ZSB3aWxsIHJlcXVlc3QgdGhlIGRhaWx5IGJpa2UgY291bnRzIGZvciBldmVyeSBkYXkgc2luY2UgYSBjb3VudGVyIHdhcyBpbnN0YWxsZWQgYW5kIHNhdmUgaXQgaW4gb3VyIGRhdGFiYXNlLiANCg0KIyBTdGVwIDI6IE1pZ3JhdGluZyBCaWtlIENvdW50cw0KDQpBZ2Fpbiwgd2Ugd2lsbCBidWlsZCBvbiB0aGUgY29kZSBmcm9tIFtQYXJ0IDJdKGh0dHBzOi8vbmF0aGFuc3Byb2plY3RzLmNvbS9wYXJ0XzJfcXVlcnlfdGhlX2FwaS5odG1sKS4NCg0KIyMgRmluZCB0aGUgb2xkZXN0IEJpa2VvbWV0ZXINCg0KSW4gW1BhcnQgMl0oaHR0cHM6Ly9uYXRoYW5zcHJvamVjdHMuY29tL3BhcnRfMl9xdWVyeV90aGVfYXBpLmh0bWwpLCB3ZSBjcmVhdGVkIGEgZnVuY3Rpb24gY2FsbGVkICoqYXBpX2NvdW50c190b19saXN0Kiogd2hpY2ggcHVsbGVkIHRoZSBiaWN5Y2xlIGNvdW50cyB1c2luZyBhbiBhcmJpdHJhcnkgaGFyZC1jb2RlZCBzdGFydCBkYXRlLCBlbmQgZGF0ZSwgYW5kIGEgc2luZ2xlIEJpa2VvbWV0ZXIuIEhvd2V2ZXIsIG5vdyB3ZSBuZWVkIHRvIHB1bGwgdGhlIGRhdGEgZm9yICoqYWxsKiogQmlrZW9tZXRlcnMgYW5kICoqYWxsKiogYXZhaWFibGUgZGF0ZXMuDQoNClRvIG1ha2Ugb3VyIGxpZmUgZWFzaWVyLCBsZXQncyBzZWxlY3QgdGhlIG9sZGVzdCBCaWtlb21ldGVyIGFuZCB1c2UgaXQncyBzdGFydCBkYXRlIGFzIG91ciAqc3RhcnQgZGF0ZSogZm9yIGFsbCBCaWtlb21ldGVycy4gT3VyIGZ1bmN0aW9uIGlzIHdyaXR0ZW4gc28gdGhhdCBpZiB0aGVyZSBpcyBubyBkYXRhIGZvciBhIHJlcXVlc3RlZCBkYXRlLCBvdXIgZnVuY3Rpb24ganVzdCBza2lwcyB0aG9zZSBkYXRlcyBhbmQgbW92ZXMgb24gd2l0aG91dCBlcnJvci4NCg0KV2Ugd2lsbCBjYWxsIHRoZSBidWlsdC1pbiBCaWtlIEFybGluZ3RvbiBBUEkgbWV0aG9kOiAnZ2V0TWluRGF0ZXMnIG9uIGFsbCB0aGUgQmlrZW9tZXRlciBJRHMgaW4gdGhlIGJlbG93ICpiaWtlb21ldGVyX2lkX2xpc3QqLg0KDQpgYGB7cHl0aG9uICB9DQpiaWtlb21ldGVyX2lkX2xpc3QgPSBbJzMzJywnMzAnLCc0MycsJzI0JywnNTknLCc1NicsJzQ3JywnNDgnLCcxMCcsJzIwJywNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICczNScsJzU3JywnMTgnLCc2JywgJzMnLCc1OCcsJzYxJywnNjInLCczOCcsJzQ0JywnMTQnLA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgJzYwJywnNScsJzQyJywnMzcnLCcyNycsJzI2JywnOCcsJzcnLCc1MScsICc1MicsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAnNDUnLCcyMicsJzIxJywnMzYnLCczNCcsJzQxJywnOScsJzM5JywnMTYnLCcxNScsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAnNTQnLCc1NScsJzMxJywnMjgnLCcxMScsJzInLCcyNScsJzE5J10NCmBgYA0KDQpOb3cgd2Ugd2lsbCBjcmVhdGUgYSBzaW1wbGUgKmZvciBsb29wKiB0byBjYWxsIHRoZSAqZ2V0TWluRGF0ZXMqIG1ldGhvZCBvbiBhbGwgdGhlIEJpa2VvbWV0ZXJzLg0KDQpgYGB7cHl0aG9uICB9DQojIEFzc2lnbiB0aGUgdXJsIG9mIHRoZSANCnVybCA9ICdodHRwOi8vd2Vic2VydmljZXMuY29tbXV0ZXJwYWdlLmNvbS9jb3VudGVycy5jZmM/d3NkbCcNCg0KIyBEZWZpbmVzIHRoZSBtZXRob2QgaW4gYSBkaWN0aW9uYXJ5IHVzZWQgdG8gbWFrZSB0aGUgcmVxdWVzdA0KZm9yIGJpa2VvbWV0ZXJfaWQgaW4gYmlrZW9tZXRlcl9pZF9saXN0OiANCiAgY291bnRlcl9yZXFlc3RfbWV0aG9kcyA9IHsnbWV0aG9kJzogJ2dldE1pbkRhdGVzJywgJ2NvdW50ZXJJRCc6IGJpa2VvbWV0ZXJfaWR9DQogICMgU2F2ZSB0aGUgR2V0QWxsQ291bnRlcnMgcmVxdWVzdCB0byBtZW1vcnkNCiAgcmVzcG9uc2UgPSByZXF1ZXN0cy5nZXQodXJsLCBwYXJhbXM9Y291bnRlcl9yZXFlc3RfbWV0aG9kcykNCiAgcmVzcG9uc2UudGV4dA0KDQpgYGANCg0KU2tpbW1pbmcgdGhyb3VnaCB0aGUgcmV0dXJuZWQgcmVzcG9uc2VzLCB3ZSBjYW4gc2VlIHRoYXQgdGhlIGZpcnN0IGRhdGUgd2FzIDQvMS8yMDEwLiBUaGlzIHdpbGwgYmUgb3VyIGZpcnN0ICpzdGFydCBkYXRlKiBpbiBvdXIgZnVuY3Rpb24uIA0KDQojIyBDcmVhdGUgaGVscGVyIGZ1bmN0aW9ucw0KDQpVbHRpbWF0ZWx5LCB3ZSB3aWxsIGhhdmUgdHdvIGZ1bmN0aW9ucyB3aGVuIHdlIGFyZSBmaW5pc2hlZDogb25lIHRoYXQgcHVsbHMgYWxsIHRoZSBkYXRlcyBzdGFydGluZyBmcm9tIDQvMS8yMDEwICgqKmFsbF9jb3VudHNfYnlfZGF0ZV90b19zcWwqKikgYW5kIG9uZSB0aGF0IHB1bGxzIG9ubHkgdGhlIG5ldyBkYXRlcy4oKipuZXdfY291bnRzX2J5X2RhdGVfdG9fc3FsKiopDQoNClRvIGhlbHAgdXMgb3V0LCB3ZSB3aWxsIGNyZWF0ZSBhICoqaGVscGVyIGZ1bmN0aW9uKiogYmFzZWQgb24gdGhlICoqYXBpX2NvdW50c190b19saXN0KiogZnVuY3Rpb24gd2UgbWFkZSBpbiBbUGFydCAyXShodHRwczovL25hdGhhbnNwcm9qZWN0cy5jb20vcGFydF8yX3F1ZXJ5X3RoZV9hcGkuaHRtbCkuDQoNClRoZSBnb2FsIGlzIHRvIGhhdmUgdGhlIG1haW4gZnVuY3Rpb25zICoqYWxsX2NvdW50c19ieV9kYXRlX3RvX3NxbCoqIGFuZCAqKm5ld19jb3VudHNfYnlfZGF0ZV90b19zcWwqKiBwYXNzIGluIHRoZSAqYmlrZW9tZXRlcl9pZCosICpzdGFydCBkYXRlKiBhbmQgKmVuZCBkYXRlKiB0byBvdXIgaGVscGVyIGZ1bmN0aW9uICoqYXBpX2NvdW50c190b19saXN0KiouIElmIHlvdSBoYXZlIG5vIGRhdGEgaW4geW91ciBkYXRhYmFzZSwgeW91IHdvdWxkIGNhbGwgdGhlICAqKmFsbF9jb3VudHNfYnlfZGF0ZV90b19zcWwqKiBmdW5jdGlvbiB0byBtYWtlIHJlcXVlc3RzIHN0YXJ0aW5nIG9uIDQvMS8yMDEwLiBJZiB5b3UgYWxyZWFkeSBoYXZlIGRhdGEgaW4geW91ciBNeVNRTCBkYXRhYmFzZSwgdGhlICoqbmV3X2NvdW50c19ieV9kYXRlX3RvX3NxbCoqIGZ1bmN0aW9uIHdpbGwgcXVlcnkgeW91ciBkYXRhYmFzZSBhbmQgb25seSByZXF1ZXN0IGRhdGEgZm9yIGRhdGVzIG5vdCBpbiB5b3VyIGRhdGFiYXNlLg0KDQpOb3RlOiBBcyBJIHdyaXRlIHRoaXMgdXAsIEknbSByZWFsaXppbmcgeW91IGNvdWxkIHdyaXRlIG9uZSBmdW5jdGlvbiB0aGF0IHdvdWxkIHF1ZXJ5IHlvdXIgTXlTUUwgZGF0YWJhc2UgYW5kIGRlcGVuZGluZyB1cG9uIHRoZSByZXNwb25zZSwgaXQgY291bGQgY2FsbCB0aGUgYXBwcm9wcmlhdGUgZnVuY3Rpb24gdXNpbmcgYW4gKmlmL2Vsc2UqIHN0YXRlbWVudCwgYnV0LCBhcyB0aGV5IHNheSwgdGhhdCdzIG5vdCBNVlAuDQoNCkhlcmUgaXMgdGhlICpoZWxwZXIgZnVuY3Rpb24qIHRoYXQgd2lsbCByZXF1ZXN0IGRhdGEgZnJvbSB0aGUgQmlrZSBBcmxpbmd0b24gQVBJIHVzaW5nIHRoZSAnR2V0Q291bnRJbkRhdGVSYW5nZScgbWV0aG9kLiBUaGlzIGZ1bmN0aW9uIHdhcyBtb2RpZmllZCBmcm9tIFtQYXJ0IDJdKGh0dHBzOi8vbmF0aGFuc3Byb2plY3RzLmNvbS9wYXJ0XzJfcXVlcnlfdGhlX2FwaS5odG1sKSB0byBhY2NlcHQgYXJndW1lbnRzIGZvciBhIGJpa2VvbWV0ZXJfaWQsIHN0YXJ0X2RhdGUsIGVuZF9kYXRlLCBhbmQgY291bnRfaW5fZGF0ZV9yYW5nZV9saXN0IChhIGxpc3QgdG8gaG9sZCBhbGwgdGhlIGNvdW50IGRhdGEpLiBXZSB3aWxsIGhhcmQtY29kZSB0aGUgbW9kZT0nQicgdG8gcmVxdWVzdCAqYmlrZSogZGF0YSBub3QgKnBlZGVzdHJpYW4qLCBpbnRlcnZhbD0nZCcgdG8gcmVxdWVzdCBjb3VudHMgYnkgKmRheSosIHN0YXJ0X3RpbWUgYW5kIGVuZF90aW1lIHRvIGdldCBkYXRhIGZvciB0aGUgZW50aXJlIGRheSwgYW5kIGRpcmVjdGlvbj0nJyB0byBwdWxsIGJvdGggdGhlICppbmJvdW5kKiBhbmQgKm91dGJvdW5kKiBkaXJlY3Rpb25zIHRoYXQgdGhlIEJpa2VvbWV0ZXJzIGNhbiBzZW5zZS4NCg0KTm90ZTogVGhlIEJpa2UgQXJsaW5ndG9uIGRvY3VtZW50YXRpb24gZG9lcyBub3QgbWVudGlvbiB0aGlzLCBidXQgc29tZSBCaWtlb21ldGVycyBjYW4ndCBkaXN0aW5ndWlzaCBiZXR3ZWVuICppbmJvdW5kKiBvciAqb3V0Ym91bmQqIGFuZCB0aGVpciBjb3VudHMgaW5zdGVhZCB1c2UgdGhlIGxhYmVsICdBJy4gQnkgbGVhdmluZyB0aGUgKmRpcmVjdGlvbiogZmllbGQgYmxhbmssIG91ciBmdW5jdGlvbiB3aWxsIGluY2x1ZGUgYWxsICdBJyBkaXJlY3Rpb25zIHRvby4gDQoNCldlIHdpbGwgYmUgdXNpbmcgYSBmZXcgZnVuY3Rpb25zIGZyb20gdGhlICpkYXRldGltZSogbW9kdWxlIHNvIHdlIHN0YXJ0IGJ5IGltcG9ydGluZyB0aGVtLg0KDQpgYGB7cHl0aG9uICB9DQpmcm9tIGRhdGV0aW1lIGltcG9ydCBkYXRlLCBkYXRldGltZSwgdGltZWRlbHRhDQoNCmRlZiBhcGlfY291bnRzX3RvX2xpc3QoYmlrZW9tZXRlcl9pZCwgDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgc3RhcnRfZGF0ZSwNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBlbmRfZGF0ZSwgDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgY291bnRfaW5fZGF0ZV9yYW5nZV9saXN0LA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIG1vZGU9J0InLCANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBpbnRlcnZhbD0nZCcsIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHN0YXJ0X3RpbWU9JzA6MDAnLCANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBlbmRfdGltZT0gJzIzOjU5JywgDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgZGlyZWN0aW9uPScnKSAtPiBsaXN0Og0KICAgIHJlcXVlc3RfcGFyYW1ldGVycyA9IHsnbWV0aG9kJzogJ0dldENvdW50SW5EYXRlUmFuZ2UnLA0KICAgICAgICAgICAgJ2NvdW50ZXJJRCc6IHN0cihiaWtlb21ldGVyX2lkKSwNCiAgICAgICAgICAgICdzdGFydERhdGUnOiBzdGFydF9kYXRlLA0KICAgICAgICAgICAgJ2VuZERhdGUnOiBlbmRfZGF0ZSwNCiAgICAgICAgICAgICdtb2RlJzogc3RyKG1vZGUpLA0KICAgICAgICAgICAgJ2ludGVydmFsJzogc3RyKGludGVydmFsKSwNCiAgICAgICAgICAgICdkaXJlY3Rpb24nOiBkaXJlY3Rpb259DQogICAgcmVzcG9uc2UgPSByZXF1ZXN0cy5nZXQodXJsLCBwYXJhbXM9cmVxdWVzdF9wYXJhbWV0ZXJzKQ0KICAgICAgICAjVE9ETzogQWRkIGV4Y2VwdCBmb3Igbm9uLTIwMCByZXNwb25zZQ0KICAgICMgQ29udmVydCByZXNwb25zZSB0byBhIHN0cmluZw0KICAgIHhtbF9kYXRhID0gcmVzcG9uc2UudGV4dA0KICAgICMgQ2xlYW4gdGhlIGRhdGENCiAgICBjbGVhbl94bWxfZGF0YSA9IHJlLnN1YihyJ1tcbnxcdF0nLCAnJywgeG1sX2RhdGEpDQogICAgIyBDYXN0IHRoZSBzdHJpbmcgdG8gYW4gWE1MIGVsZW1lbnQNCiAgICB0cmVlID0gRVQuZnJvbXN0cmluZyhjbGVhbl94bWxfZGF0YSkNCiAgICAjIENyZWF0ZSB0aGUgZW1wdHkgZGljdGlvbmFyeSB0byBwdXQgdGhlIGRhdGEgaW4NCiAgICAjQ291bnRJbkRhdGVSYW5nZURpY3QgPSB7fQ0KICAgICMgUGFyc2UgdGhlIEdldENvdW50SW5EYXRlUmFuZ2UgWE1MIGZpbGUgZm9yIHRoZSBjb3VudCBhbmQgZGF0ZSBhbmQgc2F2ZSBDb3VudEluRGF0ZVJhbmdlRGljdA0KICAgIGZvciB0eXBlX3RhZyBpbiB0cmVlLmZpbmRhbGwoJ2NvdW50Jyk6DQogICAgICAgIGNvdW50ID0gdHlwZV90YWcuZ2V0KCdjb3VudCcpDQogICAgICAgIGRhdGUgPSB0eXBlX3RhZy5nZXQoJ2RhdGUnKQ0KICAgICAgICAjIENvbnZlcnRzIGNvdW50ZXIgZGF0ZSB0byBhIGRhdGUgb2JqZWN0DQogICAgICAgIGRhdGUgPSBkYXRldGltZS5zdHJwdGltZShkYXRlLCAnJW0vJWQvJVknKS5kYXRlKCkNCiAgICAgICAgeWVhciA9IGRhdGUueWVhcg0KICAgICAgICBtb250aCA9IGRhdGUubW9udGgNCiAgICAgICAgZGF5ID0gZGF0ZS5kYXkNCiAgICAgICAgbW9udGhfZGF5ID0gZid7bW9udGh9X3tkYXl9Jw0KICAgICAgICBkaXJlY3Rpb24gPSB0eXBlX3RhZy5nZXQoJ2RpcmVjdGlvbicpDQogICAgICAgIGlmIGRhdGUud2Vla2RheSgpIDw9IDQ6DQogICAgICAgICAgICBpc193ZWVrZW5kID0gMA0KICAgICAgICBlbHNlOg0KICAgICAgICAgICAgaXNfd2Vla2VuZCA9IDENCiAgICAgICAgc2luZ2xlX3R1cGxlID0gKGJpa2VvbWV0ZXJfaWQsIGRhdGUsIGRpcmVjdGlvbiwgY291bnQsIGlzX3dlZWtlbmQsIHllYXIsIG1vbnRoLCBkYXksIG1vbnRoX2RheSkNCiAgICAgICAgY291bnRfaW5fZGF0ZV9yYW5nZV9saXN0LmFwcGVuZChzaW5nbGVfdHVwbGUpDQogICAgcmV0dXJuIGNvdW50X2luX2RhdGVfcmFuZ2VfbGlzdA0KDQpgYGANCg0KIyMgQ3JlYXRlIGFsbF9jb3VudHNfYnlfZGF0ZV90b19zcWwgZnVuY3Rpb24NCg0KTm93IHRoYXQgd2UgaGF2ZSBvdXIgaGVscGVyIGZ1bmN0aW9uIGNvbXBsZXRlLCBsZXQncyBjcmVhdGUgdGhlICoqYWxsX2NvdW50c19ieV9kYXRlX3RvX3NxbCoqIHdoaWNoIHdpbGwgcHVsbCBhbGwgdGhlIGRhdGEgc3RhcnRpbmcgb24gNC8xLzIwMTAuIA0KDQpXaGVuIGRlc2lnbmluZyBvdXIgZnVuY3Rpb24sIGl0J3MgaW1wb3J0YW50IHRvIHJlbWVtYmVyIHRoYXQgdGhlIEJpa2UgQXJsaW5ndG9uIEFQSSB3aWxsIG9ubHkgYWxsb3cgcmVxdWVzdHMgb2YgMSB5ZWFyIG9yIGxlc3MuIFRoZXJlIGFyZSBtYW55IHdheXMgdG8gZ2V0IHRoZSBkYXRhIGludG8geW91ciBkYXRhYmFzZS4gSSBjaG9zZSB0byBwdWxsIGFsbCB0aGUgbmVjZXNzYXJ5IGRhdGEgYmVmb3JlIHNhdmluZyBpdCBhbGwgaW4gbXkgZGF0YWJhc2UgaW4gb25lIGNodW5rLiBZb3UgbWF5IGNob29zZSB0byBpbmNyZW1lbnRhbGx5IHB1bGwgYW5kIHNhdmUgdGhlIGRhdGEgMSB5ZWFyIGF0IGEgdGltZS4gQm90aCB3YXlzIGhhdmUgdGhlaXIgcHJvcyBhbmQgY29ucyBhbmQgaXQncyB1cCB0byB5b3UgdG8gZGVjaWRlIGFuZCBqdXN0aWZ5IHlvdXIgZGVjaXNpb24uDQoNCmBgYHtweXRob24gIH0NCmRlZiBhbGxfY291bnRzX2J5X2RhdGVfdG9fc3FsKCk6DQogICAgIyBDcmVhdGVzIHRoZSBwYXJhbWV0ZXJzIHRvIGRyaXZlIHRoZSBjb25uZWN0aW9uDQogICAgZW5naW5lID0gY3JlYXRlX25ld19lbmdpbmUoJ2Jpa2VvbWV0ZXJzX2RiJykNCiAgICAjIFRlc3RzIHRoZSBjb25uZWN0aW9uIGFuZCB0aHJvd3MgYW4gZXJyb3IgaWYgbm90IGVzdGFibGlzaGVkDQogICAgZW5naW5lLmNvbm5lY3QoKQ0KICAgICMgQmlrZSBBcmxpbmd0b24gQVBJIG9ubHkgYWNjZXB0cyBxdWVyeSByYW5nZXMgb2YgMSB5ZWFyIG9yIGxlc3MNCiAgICBzdGFydF9kYXRlID0gZGF0ZSh5ZWFyPTIwMTAsIG1vbnRoPTQsIGRheT0xKQ0KICAgIGVuZF9kYXRlID0gZGF0ZSh5ZWFyPTIwMTEsIG1vbnRoPTQsIGRheT0xKQ0KICAgICMgQ3JlYXRlIGEgbGlzdCB0byBob2xkIGNvdW50IGRhdGENCiAgICBjb3VudF9pbl9kYXRlX3JhbmdlX2xpc3QgPSBbXQ0KICAgICMgV2Ugd2lsbCBwdWxsIGRhdGEgdXAgdG8geWVzdGVyZGF5J3MgZGF0ZQ0KICAgIHdoaWxlIGVuZF9kYXRlIDw9IGRhdGUudG9kYXkoKSAtIHRpbWVkZWx0YShkYXlzPTEpOiAgDQogICAgIyBIYXJkLWNvZGVkIHRoZSBiaWtlb21ldGVyX2lkIGxpc3QgdG8gbm90IHN0cmVzcyB0aGUgc2VydmVyIG1ha2luZyB1bm5lY2Vzc2FyeSByZXF1ZXN0cyANCiAgICAgICAgYmlrZW9tZXRlcl9pZF9saXN0ID0gWyczMycsJzMwJywnNDMnLCcyNCcsJzU5JywnNTYnLCc0NycsJzQ4JywnMTAnLCcyMCcsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAnMzUnLCc1NycsJzE4JywnNicsICczJywnNTgnLCc2MScsJzYyJywnMzgnLCc0NCcsJzE0JywNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICc2MCcsJzUnLCc0MicsJzM3JywnMjcnLCcyNicsJzgnLCc3JywnNTEnLCAnNTInLA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgJzQ1JywnMjInLCcyMScsJzM2JywnMzQnLCc0MScsJzknLCczOScsJzE2JywnMTUnLA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgJzU0JywnNTUnLCczMScsJzI4JywnMTEnLCcyJywnMjUnLCcxOSddDQogICAgICAgIGZvciBiaWtlb21ldGVyX2lkIGluIGJpa2VvbWV0ZXJfaWRfbGlzdDoNCiAgICAgICAgICAgICMgRGF0ZXMgWVlZWS1NTS1ERCBmb3JtYXQgY29udmVydGVkIHRvIE1NLURELVlZWSBhbmQgbWFkZSBpbnRvIGEgc3RyaW5nIHRvIHNlYXJjaCBDb3VudGVyIEFQSQ0KICAgICAgICAgICAgY2xlYW5fc3RhcnRfZGF0ZSA9IGYne3N0YXJ0X2RhdGUubW9udGh9L3tzdGFydF9kYXRlLmRheX0ve3N0YXJ0X2RhdGUueWVhcn0nDQogICAgICAgICAgICBjbGVhbl9lbmRfZGF0ZSA9IGYne2VuZF9kYXRlLm1vbnRofS97ZW5kX2RhdGUuZGF5fS97ZW5kX2RhdGUueWVhcn0nDQogICAgICAgICAgICAjIENhbGxzIHRoZSBnZXRfY291bnRfaW5fZGF0ZV9yYW5nZSBmdW5jdGlvbiBhbmQgcGFzc2VzIGluIGhhcmQtY29kZWQNCiAgICAgICAgICAgICMgcGFyYW1ldGVycyBsaWtlIHRoZSBzdGFydF9kYXRlLCB3aGljaCBpcyB0aGUgZmlyc3QgZGF0YXBvaW50IGluDQogICAgICAgICAgICAjIEJpa2UgQXJsaW5ndG9uIHNlcnZlci4gT3B0aW1hbGx5LCB0aGVzZSB3b3VsZCBub3QgYmUgaGFyZGNvZGVkIHRvICANCiAgICAgICAgICAgICMgbWFrZSB0aGUgcG9yZ3JhbSBtb3JlIGZ1dHVyZS1wcm9vZi4NCiAgICAgICAgICAgIGFwaV9jb3VudHNfdG9fbGlzdChiaWtlb21ldGVyX2lkLCBjbGVhbl9zdGFydF9kYXRlLCBjbGVhbl9lbmRfZGF0ZSwgXA0KICAgICAgICAgICAgY291bnRfaW5fZGF0ZV9yYW5nZV9saXN0LCBpbnRlcnZhbD0nZCcpDQogICAgICAgICMgSWYgZW5kIGRhdGUgaXMgeWVzdGVyZGF5J3MgZGF0ZSwgY29uZ3JhdHVsYXRpb25zLCB5b3UgcHVsbGVkIGFsbCBhdmFpbGFibGUgZGF0ZXMNCiAgICAgICAgIyBXZSB3b24ndCBwdWxsIHRvZGF5J3MgZGF0ZSBpbiBjYXNlIHRoZSBkYXRhIGhhcyBub3QgYmVlbiB1cGxvYWRlZCB0byB0aGVpciBzZXJ2ZXJzIHlldA0KICAgICAgICBpZiBlbmRfZGF0ZSA9PSBkYXRlLnRvZGF5KCkgLSB0aW1lZGVsdGEoZGF5cz0xKToNCiAgICAgICAgICAgIGJyZWFrICAgICAgICAgICAgICAgIA0KICAgICAgICAjIEFkZHMgYSB5ZWFyIHRvIHN0YXJ0IGRhdGUgYW5kIGVuZCBkYXRlDQogICAgICAgIHN0YXJ0X2RhdGUgPSBzdGFydF9kYXRlLnJlcGxhY2UoeWVhciA9IHN0YXJ0X2RhdGUueWVhciArIDEpDQogICAgICAgIGVuZF9kYXRlID0gZW5kX2RhdGUucmVwbGFjZSh5ZWFyID0gZW5kX2RhdGUueWVhciArIDEpDQogICAgICAgICMgSWYgZW5kIGRhdGUgaXMgYWZ0ZXIgeWVzdGVyZGF5J3MgZGF0ZSwgc2V0IHRoZSBlbmQgZGF0ZSB0byB5ZXN0ZXJkYXkgYW5kIHB1bGwgdGhlIGRhdGEgb25lIGxhc3QgdGltZQ0KICAgICAgICBpZiBlbmRfZGF0ZSA+PSBkYXRlLnRvZGF5KCk6DQogICAgICAgICAgICBlbmRfZGF0ZSA9IGRhdGUudG9kYXkoKSAtIHRpbWVkZWx0YShkYXlzPTEpDQogICAgY29sdW1ucyA9ICgnYmlrZW9tZXRlcl9pZCcsICdkYXRlJywgJ2RpcmVjdGlvbicsICdjb3VudCcsICdpc193ZWVrZW5kJywgJ3llYXInLCAnbW9udGgnLCAnZGF5JywgJ21vbnRoX2RheScpDQogICAgZGYgPSBwZC5EYXRhRnJhbWUoY291bnRfaW5fZGF0ZV9yYW5nZV9saXN0LCBjb2x1bW5zPWNvbHVtbnMpDQogICAgIyBNYWtlcyBiaWtlb21ldGVyX2lkIGNvdWxtbiB0eXBlIGludA0KICAgIGRmW1siYmlrZW9tZXRlcl9pZCJdXSA9IGRmW1siYmlrZW9tZXRlcl9pZCJdXS5hc3R5cGUoJ2ludCcpDQogICAgIyBSZXBsYWNlcyB0YWJsZSBjb3VudHMsIHVzZSBpZl9leGlzdHM9J2FwcGVuZCcgdG8gaW5zZXJ0IG5ldyB2YWx1ZXMgaW50byB0aGUgdGFibGUNCiAgICBkZi50b19zcWwoJ2NvdW50c19kYWlseScsIGNvbj1lbmdpbmUsIGluZGV4PUZhbHNlLCBpZl9leGlzdHM9J3JlcGxhY2UnKQ0KICAgICMgUXVlcmllcyB5b3VyIGFuZCByZXR1cm5zIGEgbWVzc2FnZSB0byB0aGUgdXNlciBpbmRpY2F0aW5nIHRoZSBsYXN0IGRheSBpbiB0aGVpciBzZXJ2ZXINCiAgICB3aXRoIGVuZ2luZS5jb25uZWN0KCkgYXMgY29uOg0KICAgICAgICBkYXRlX2xpc3QgPSBjb24uZXhlY3V0ZSgnU0VMRUNUIE1BWChEYXRlKSBGUk9NIGNvdW50c19kYWlseScpDQogICAgICAgIGZvciBkYXkgaW4gZGF0ZV9saXN0Og0KICAgICAgICAgICAgbGFzdF9kYXkgPSBkYXlbMF0NCiAgICAgICAgICAgIHByaW50KGYnVGhlIG5ld2VzdCBkYXRlIGluIGNvdW50c19kYWlseSBpcyB7bGFzdF9kYXl9JykNCiAgICBlbmdpbmUuZGlzcG9zZSgpDQoNCmBgYA0KDQpJZiB5b3UgbG9vayBhdCB0aGUgbGFzdCBiaXQgb2YgY29kZSBpbiB0aGUgYWJvdmUgZnVuY3Rpb24sIGFmdGVyIHRoZSBkYXRhIGlzIHNhdmVkIHRvIG15IGRhdGFiYXNlIEkgbWFrZSBhIHF1ZXJ5IHRvIG15IGRhdGFiYXNlIHRvIHJldHVybiB0aGUgbW9zdCByZWNlbnQgZGF0ZS4gSWYgdGhlIGRhdGUgcmV0dXJuZWQgaXMgeWVzdGVyZGF5J3MgZGF0ZSwgaXQncyBhIGdvb2Qgc2lnbiB0aGF0IHRoZSBkYXRhIHdhcyB1cGxvYWRlZCBzdWNjZXNzZnVsbHkuDQoNCk5vdyB0aGF0IHRoZSBmdW5jdGlvbiBpcyBkZWZpbmVkLCBsZXQncyBydW4gaXQuDQoNCmBgYHtweXRob24gZXZhbD1GQUxTRSwgY2FjaGU9VFJVRX0NCmFsbF9jb3VudHNfYnlfZGF0ZV90b19zcWwoKQ0KYGBgDQoNCkxldCdzIHRha2UgYSBsb29rIGF0IG91ciB0YWJsZSENCg0KYGBge3NxbCBjb25uZWN0aW9uPWNvbn0NClNFTEVDVCAqIEZST00gY291bnRzX2RhaWx5IEdST1VQIEJZIGJpa2VvbWV0ZXJfaWQgT1JERVIgQlkgYmlrZW9tZXRlcl9pZCBhc2M7DQpgYGANCg0KIyMgQ3JlYXRlIG5ld19jb3VudHNfYnlfZGF0ZV90b19zcWwgZnVuY3Rpb24NCg0KTm93IHRoYXQgd2UgaGF2ZSBvdXIgKiphbGxfY291bnRzX2J5X2RhdGVfdG9fc3FsKiogZnVuY3Rpb24sIHdlIG5lZWQgb3VyICoqbmV3X2NvdW50c19ieV9kYXRlX3RvX3NxbCoqIGZ1bmN0aW9uIHdoaWNoIHdpbGwgb25seSBwdWxsIHRoZSBuZXcgZGF0ZXMuDQoNClRvIG9ubHkgcHVsbCB0aGUgbmV3IGRhdGVzLCBsZXQncyBtYWtlIGEgaGVscGVyIGZ1bmN0aW9uIHRoYXQgd2lsbCByZXR1cm4gdGhlIG1vc3QgcmVjZW50IGRhdGUgaW4gb3VyIE15U1FMIGRhdGFiYXNlLg0KDQpgYGB7cHl0aG9uICB9DQpkZWYgbGFzdF9zcWxfZGF0ZV9jb3VudHNfZGFpbHkoZW5naW5lKToNCiAgICAnJycgUmV0dXJucyB0aGUgbGFzdCBkYXRlIGluIHRoZSBNeVNRTCBkYXRhYmFzZSBhcyBhIGRhdGV0aW1lIG9iamVjdCcnJw0KICAgIHdpdGggZW5naW5lLmNvbm5lY3QoKSBhcyBjb246DQogICAgICAgIGxhc3RfZGF0ZSA9IGNvbi5leGVjdXRlKCdTRUxFQ1QgTUFYKERhdGUpIEZST00gY291bnRzX2RhaWx5JykNCiAgICBmb3IgZGF5IGluIGxhc3RfZGF0ZToNCiAgICAgICAgZGF0ZSA9IGRheVswXQ0KICAgIHJldHVybiBkYXRlDQoNCmBgYA0KDQpXaXRoIG91ciBoZWxwZXIgZnVuY3Rpb24gZGVmaW5lZCwgSSdsbCB1c2UgdGhlIHByZXZpb3VzIGZ1bmN0aW9uIGFzIGEgdGVtcGxhdGUgYW5kIGp1c3QgY2hhbmdlIGhvdyB0aGUgc3RhcnQgZGF0ZSBhbmQgZW5kIGRhdGUgdmFyaWFibGVzIGFyZSBmaXJzdCBkZWZpbmVkLiANCg0KSSBhbHNvIGFkZGVkIHR3byBjaGVja3MgaW4gdGhlIGJlZ2lubmluZy4gVGhlIGZpcnN0IGlzIGlmIHRoZSAqKmxhc3Rfc3FsX2RhdGVfY291bnRzX2RhaWx5KiogcmV0dXJucyBOb25lLCBpdCBtb3N0IGxpa2VseSBtZWFucyB0aGVyZSBpcyBubyBkYXRhIGluIHRoZSBkYXRhYmFzZSBhbmQgdGhlIGZ1bmN0aW9uIHN0b3BzLiBUaGUgc2Vjb25kIGNoZWNrIGlzIGlmIHllc3RlcmRheSdzIGRhdGUgaXMgYWxyZWFkeSBpbiB0aGUgZGF0YWJhc2UsIHRoZSBmdW5jdGlvbiByZXR1cm5zICdObyBuZXcgZGF0YSBhdmFpbGFibGUnIHRvIHRoZSB1c2VyIHdpdGhvdXQgbWFraW5nIGFueSBBUEkgcmVxdWVzdHMuIEV2ZXJ5dGhpbmcgZWxzZSBzaG91bGQgYmUgdGhlIHNhbWUuDQoNCmBgYHtweXRob259DQpkZWYgbmV3X2NvdW50c19ieV9kYXlfdG9fc3FsKCk6DQogICAgZW5naW5lID0gY3JlYXRlX25ld19lbmdpbmUoJ2NvdW50cycpDQogICAgIyBFc3RhYmxpc2ggY29ubmVjdGlvbiB0byBNeVNRTCBkYXRhYmFzZSBiZWZvcmUgbWFraW5nIGFsbCB0aGUgQVBJIGNhbGxzLg0KICAgICNUT0RPOiBJZiBubyBkYXRhIGlzIGluIHRoZSBNeVNRTCBkYXRhYmFzZSwgY3VzdG9tIGV4Y2VwdCBlcnJvcg0KICAgICMgVGhpcyBwcmV2ZW50cyB0aGUgcHJvZ3JhbSBmcm9tIHB1bGxpbmcgdG9kYXkncyBkYXRlDQogICAgc3RhcnRfZGF0ZSA9IGxhc3Rfc3FsX2RhdGVfY291bnRzX2RhaWx5KGVuZ2luZSkgKyB0aW1lZGVsdGEoZGF5cz0xKQ0KICAgIGlmIHN0YXJ0X2RhdGUgPT0gTm9uZToNCiAgICAgICAgcHJpbnQoJ05vIGRhdGEgZm91bmQgaW4gTXlTUUwgZGF0YWJhc2UuJykNCiAgICAgICAgcHJpbnQoJ1BsZWFzZSB1c2UgYWxsX2NvdW50c190b19zcWwoKSBmdW5jdGlvbicpDQogICAgICAgIHJldHVybiBOb25lDQogICAgaWYgc3RhcnRfZGF0ZSA+PSBkYXRlLnRvZGF5KCkgLSB0aW1lZGVsdGEoZGF5cz0xKToNCiAgICAgICAgI1RPRE86IHRocm93IGFuIGV4Y2VwdGlvbiBlcnJvciBpbnN0ZWFkIG9mIHByaW50DQogICAgICAgIHByaW50KCdObyBuZXcgZGF0YSBhdmFpYWJsZScpDQogICAgICAgIHJldHVybiBOb25lDQogICAgZW5kX2RhdGUgPSBzdGFydF9kYXRlLnJlcGxhY2UoeWVhciA9IHN0YXJ0X2RhdGUueWVhciArIDEpDQogICAgaWYgZW5kX2RhdGUgPj0gZGF0ZS50b2RheSgpOg0KICAgICAgICBlbmRfZGF0ZSA9IGRhdGUudG9kYXkoKSAtIHRpbWVkZWx0YShkYXlzPTEpDQogICAgICMjIFRlc3QgZGIgQ29ubmVjdGlvbiMjDQogICAgIyBDcmVhdGVzIHRoZSBwYXJhbWV0ZXJzIHRvIGRyaXZlIHRoZSBjb25uZWN0aW9uDQogICAgIyBCaWtlIEFybGluZ3RvbiBBUEkgb25seSBhY2NlcHRzIHF1ZXJ5IHJhbmdlcyBvZiAxIHllYXIgb3IgbGVzcw0KICAgIGNvdW50X2luX2RhdGVfcmFuZ2VfbGlzdCA9IFtdICAgDQogICAgd2hpbGUgZW5kX2RhdGUgPD0gZGF0ZS50b2RheSgpIC0gdGltZWRlbHRhKGRheXM9MSk6ICANCiAgICAjIEhhcmRjb2RlZCB0aGUgY291bnRlcklEIGxpc3QgdG8gbm90IHN0cmVzcyB0aGUgc2VydmVyDQogICAgICAgIGJpa2VvbWV0ZXJfaWRfbGlzdCA9IFsnMzMnLCczMCcsJzQzJywnMjQnLCc1OScsJzU2JywnNDcnLCc0OCcsJzEwJywnMjAnLA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgJzM1JywnNTcnLCcxOCcsJzYnLCAnMycsJzU4JywnNjEnLCc2MicsJzM4JywnNDQnLCcxNCcsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAnNjAnLCc1JywnNDInLCczNycsJzI3JywnMjYnLCc4JywnNycsJzUxJywgJzUyJywNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICc0NScsJzIyJywnMjEnLCczNicsJzM0JywnNDEnLCc5JywnMzknLCcxNicsJzE1JywNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICc1NCcsJzU1JywnMzEnLCcyOCcsJzExJywnMicsJzI1JywnMTknXQ0KICAgICAgICBmb3IgYmlrZW9tZXRlcl9pZCBpbiBiaWtlb21ldGVyX2lkX2xpc3Q6DQogICAgICAgICAgICAjIERhdGVzIFlZWVktTU0tREQgZm9ybWF0IGNvbnZlcnRlZCB0byBNTS1ERC1ZWVkgYW5kIG1hZGUgaW50byBhIHN0cmluZyB0byBzZWFyY2ggQ291bnRlciBBUEkNCiAgICAgICAgICAgIGNsZWFuX3N0YXJ0X2RhdGUgPSBmJ3tzdGFydF9kYXRlLm1vbnRofS97c3RhcnRfZGF0ZS5kYXl9L3tzdGFydF9kYXRlLnllYXJ9Jw0KICAgICAgICAgICAgY2xlYW5fZW5kX2RhdGUgPSBmJ3tlbmRfZGF0ZS5tb250aH0ve2VuZF9kYXRlLmRheX0ve2VuZF9kYXRlLnllYXJ9Jw0KICAgICAgICAgICAgIyBDYWxscyB0aGUgZ2V0X2NvdW50X2luX2RhdGVfcmFuZ2UgZnVuY3Rpb24gYW5kIHBhc3NlcyBpbiBoYXJkLWNvZGVkDQogICAgICAgICAgICAjIHBhcmFtZXRlcnMgbGlrZSB0aGUgc3RhcnRfZGF0ZSwgd2hpY2ggaXMgdGhlIGZpcnN0IGRhdGFwb2ludCBpbg0KICAgICAgICAgICAgIyBCaWtlIEFybGluZ3RvbiBzZXJ2ZXIuIE9wdGltYWxseSwgdGhlc2Ugd291bGQgbm90IGJlIGhhcmRjb2RlZCAgDQogICAgICAgICAgICAjIG1ha2UgdGhlIHBvcmdyYW0gbW9yZSBmdXR1cmUtcHJvb2YuDQogICAgICAgICAgICBhcGlfY291bnRzX3RvX2xpc3QoYmlrZW9tZXRlcl9pZCwgY2xlYW5fc3RhcnRfZGF0ZSwgY2xlYW5fZW5kX2RhdGUsIGNvdW50X2luX2RhdGVfcmFuZ2VfbGlzdCwgaW50ZXJ2YWw9J2QnKQ0KICAgICAgICAjIElmIGVuZCBkYXRlIGlzIHllc3RlcmRheSdzIGRhdGUsIGNvbmdyYXR1bGF0aW9ucywgeW91IHB1bGxlZCBhbGwgYXZhaWxhYmxlIGRhdGVzDQogICAgICAgICMgV2Ugd29uJ3QgcHVsbCB0b2RheSdzIGRhdGUgaW4gY2FzZSB0aGUgZGF0YSBoYXMgbm90IGJlZW4gdXBsb2FkZWQgdG8gdGhlaXIgc2VydmVycyB5ZXQNCiAgICAgICAgaWYgZW5kX2RhdGUgPT0gZGF0ZS50b2RheSgpIC0gdGltZWRlbHRhKGRheXM9MSk6DQogICAgICAgICAgICBicmVhayAgICAgICAgICAgICAgICANCiAgICAgICAgIyBBZGRzIGEgeWVhciB0byBzdGFydCBkYXRlIGFuZCBlbmQgZGF0ZQ0KICAgICAgICBzdGFydF9kYXRlID0gc3RhcnRfZGF0ZS5yZXBsYWNlKHllYXIgPSBzdGFydF9kYXRlLnllYXIgKyAxKQ0KICAgICAgICBlbmRfZGF0ZSA9IGVuZF9kYXRlLnJlcGxhY2UoeWVhciA9IGVuZF9kYXRlLnllYXIgKyAxKQ0KICAgICAgICAjIElmIGVuZCBkYXRlIGlzIGFmdGVyIHllc3RlcmRheSdzIGRhdGUsIHNldCB0aGUgZW5kIGRhdGUgdG8geWVzdGVyZGF5YW5kIHB1bGwgdGhlIGRhdGEgb25lIGxhc3QgdGltZQ0KICAgICAgICBpZiBlbmRfZGF0ZSA+PSBkYXRlLnRvZGF5KCk6DQogICAgICAgICAgICBlbmRfZGF0ZSA9IGRhdGUudG9kYXkoKSAtIHRpbWVkZWx0YShkYXlzPTEpICAgIA0KICAgIGNvbHVtbnMgPSAoJ2Jpa2VvbWV0ZXJfaWQnLCAnZGF0ZScsICdkaXJlY3Rpb24nLCAnY291bnQnLCAnaXNfd2Vla2VuZCcsICd5ZWFyJywgJ21vbnRoJywgJ2RheScsICdtb250aF9kYXknKQ0KICAgIGRmID0gcGQuRGF0YUZyYW1lKGNvdW50X2luX2RhdGVfcmFuZ2VfbGlzdCwgY29sdW1ucz1jb2x1bW5zKQ0KICAgIGRmW1siYmlrZW9tZXRlcl9pZCJdXSA9IGRmW1siYmlrZW9tZXRlcl9pZCJdXS5hc3R5cGUoJ2ludCcpDQogICAgIyBSZXBsYWNlcyB0YWJsZSBjb3VudHMsIHVzZSBpZl9leGlzdHM9J2FwcGVuZCcgdG8gaW5zZXJ0IG5ldyB2YWx1ZXMgaW50byB0aGUgdGFibGUNCiAgICBkZi50b19zcWwoJ2NvdW50c19kYWlseScsIGNvbj1lbmdpbmUsIGluZGV4PUZhbHNlLCBpZl9leGlzdHM9J2FwcGVuZCcpDQogICAgIyBSZWFkcyB0aGUgdGFibGUNCiAgICAjZGYyID0gcGQucmVhZF9zcWwoJ2NvdW50ZXJzJywgY29uPWVuZ2luZSkNCiAgICB3aXRoIGVuZ2luZS5jb25uZWN0KCkgYXMgY29uOg0KICAgICAgICBkYXRlX2xpc3QgPSBjb24uZXhlY3V0ZSgnU0VMRUNUIE1BWChEYXRlKSBGUk9NIGNvdW50c19kYWlseScpDQogICAgICAgIGZvciBkYXkgaW4gZGF0ZV9saXN0Og0KICAgICAgICAgICAgbGFzdF9kYXkgPSBkYXlbMF0NCiAgICAgICAgICAgIHByaW50KGYnVGhlIG5ld2VzdCBkYXRlIGluIGNvdW50c19kYWlseSBpcyB7bGFzdF9kYXl9JykNCiAgICAjIERvIHdlIG5lZWQgZW5naW5lLmRpc3Bvc2U/DQogICAgZW5naW5lLmRpc3Bvc2UoKQ0KYGBgDQoNCmBgYHtweXRob259DQpuZXdfY291bnRzX2J5X2RheV90b19zcWwoKQ0KYGBgDQoNCkkgcmFuIHRoZSBwcmV2aW91cyBmdW5jdGlvbiB0b2RheSBzbyB0aGlzIGZ1bmN0aW9uIHJldHVybnMgJ05vIG5ldyBkYXRhIGF2YWlsYWJsZScuIFRyeSBpdCBmb3IgeW91cnNlbGYgdG9tb3Jyb3cgYW5kIGNvbXBhcmUgdGhlIHRpbWUgaXQgdGFrZXMgdG8gcHVsbCAxIGRheSdzIHdvcnRoIG9mIGRhdGEgY29tcGFyZWQgdG8gb3ZlciAxMCB5ZWFycyB3b3J0aCBvZiBkYXRhLg0KDQpJbiB0aGUgW25leHQgcG9zdF0oaHR0cHM6Ly9uYXRoYW5zcHJvamVjdHMuY29tL3BhcnRfNV9jcmVhdGluZ19pbml0aWFsX3Zpc3VhbGl6YXRpb25zLmh0bWwpIEknbGwgc3RhcnQgY3JlYXRpbmcgdmlzdWFsaXphdGlvbnMgb2YgdGhlIGRhdGEuDQo=