6 files changed, 178 insertions, 0 deletions
diff --git a/packages/pipeline/src/utils/constants.ts b/packages/pipeline/src/utils/constants.ts
new file mode 100644
index 000000000..56f3e82d8
--- /dev/null
+++ b/packages/pipeline/src/utils/constants.ts
@@ -0,0 +1,3 @@
+// Block number when the Exchange contract was deployed to mainnet.
+export const EXCHANGE_START_BLOCK = 6271590;
+export const INFURA_ROOT_URL = 'https://mainnet.infura.io';
diff --git a/packages/pipeline/src/utils/get_ohlcv_trading_pairs.ts b/packages/pipeline/src/utils/get_ohlcv_trading_pairs.ts
new file mode 100644
index 000000000..9d3ef2fba
--- /dev/null
+++ b/packages/pipeline/src/utils/get_ohlcv_trading_pairs.ts
@@ -0,0 +1,92 @@
+import { fetchAsync } from '@0x/utils';
+import * as R from 'ramda';
+import { Connection } from 'typeorm';
+
+export interface TradingPair {
+    fromSymbol: string;
+    toSymbol: string;
+    latestSavedTime: number;
+}
+
+const COINLIST_API = 'https://min-api.cryptocompare.com/data/all/coinlist?BuiltOn=7605';
+
+interface CryptoCompareCoinListResp {
+    Data: Map<string, CryptoCompareCoin>;
+}
+
+interface CryptoCompareCoin {
+    Symbol: string;
+    BuiltOn: string;
+    SmartContractAddress: string;
+}
+
+const TO_CURRENCIES = ['USD', 'EUR', 'ETH', 'USDT'];
+const ETHEREUM_IDENTIFIER = '7605';
+const HTTP_OK_STATUS = 200;
+/**
+ * Get trading pairs with latest scraped time for OHLCV records
+ * @param conn a typeorm Connection to postgres
+ */
+export async function fetchOHLCVTradingPairsAsync(
+    conn: Connection,
+    source: string,
+    earliestBackfillTime: number,
+): Promise<TradingPair[]> {
+    // fetch existing ohlcv records
+    const latestTradingPairs: Array<{
+        from_symbol: string;
+        to_symbol: string;
+        latest: string;
+    }> = await conn.query(`SELECT
+    MAX(end_time) as latest,
+    from_symbol,
+    to_symbol
+    FROM raw.ohlcv_external
+    GROUP BY from_symbol, to_symbol;`);
+
+    const latestTradingPairsIndex: { [fromSym: string]: { [toSym: string]: number } } = {};
+    latestTradingPairs.forEach(pair => {
+        const latestIndex: { [toSym: string]: number } = latestTradingPairsIndex[pair.from_symbol] || {};
+        latestIndex[pair.to_symbol] = parseInt(pair.latest, 10); // tslint:disable-line:custom-no-magic-numbers
+        latestTradingPairsIndex[pair.from_symbol] = latestIndex;
+    });
+
+    // get token symbols used by Crypto Compare
+    const allCoinsResp = await fetchAsync(COINLIST_API);
+    if (allCoinsResp.status !== HTTP_OK_STATUS) {
+        return [];
+    }
+    const allCoins: CryptoCompareCoinListResp = await allCoinsResp.json();
+    const erc20CoinsIndex: Map<string, string> = new Map();
+    Object.entries(allCoins.Data).forEach(pair => {
+        const [symbol, coinData] = pair;
+        if (coinData.BuiltOn === ETHEREUM_IDENTIFIER && coinData.SmartContractAddress !== 'N/A') {
+            erc20CoinsIndex.set(coinData.SmartContractAddress.toLowerCase(), symbol);
+        }
+    });
+
+    // fetch all tokens that are traded on 0x
+    const rawTokenAddresses: Array<{ tokenaddress: string }> = await conn.query(
+        `SELECT DISTINCT(maker_token_address) as tokenaddress FROM raw.exchange_fill_events UNION
+      SELECT DISTINCT(taker_token_address) as tokenaddress FROM raw.exchange_fill_events`,
+    );
+    const tokenAddresses = R.pluck('tokenaddress', rawTokenAddresses);
+
+    // join token addresses with CC symbols
+    const allTokenSymbols: string[] = tokenAddresses
+        .map(tokenAddress => erc20CoinsIndex.get(tokenAddress.toLowerCase()) || '')
+        .filter(x => x);
+
+    // generate list of all tokens with time of latest existing record OR default earliest time
+    const allTradingPairCombinations: TradingPair[] = R.chain(sym => {
+        return TO_CURRENCIES.map(fiat => {
+            return {
+                fromSymbol: sym,
+                toSymbol: fiat,
+                latestSavedTime: R.path<number>([sym, fiat], latestTradingPairsIndex) || earliestBackfillTime,
+            };
+        });
+    }, allTokenSymbols);
+
+    return allTradingPairCombinations;
+}
diff --git a/packages/pipeline/src/utils/index.ts b/packages/pipeline/src/utils/index.ts
new file mode 100644
index 000000000..2096a0a39
--- /dev/null
+++ b/packages/pipeline/src/utils/index.ts
@@ -0,0 +1,38 @@
+import { BigNumber } from '@0x/utils';
+export * from './transformers';
+export * from './constants';
+
+/**
+ * If the given BigNumber is not null, returns the string representation of that
+ * number. Otherwise, returns null.
+ * @param n The number to convert.
+ */
+export function bigNumbertoStringOrNull(n: BigNumber): string | null {
+    if (n == null) {
+        return null;
+    }
+    return n.toString();
+}
+
+/**
+ * Logs an error by intelligently checking for `message` and `stack` properties.
+ * Intended for use with top-level immediately invoked asynchronous functions.
+ * @param e the error to log.
+ */
+export function handleError(e: any): void {
+    if (e.message != null) {
+        // tslint:disable-next-line:no-console
+        console.error(e.message);
+    } else {
+        // tslint:disable-next-line:no-console
+        console.error('Unknown error');
+    }
+    if (e.stack != null) {
+        // tslint:disable-next-line:no-console
+        console.error(e.stack);
+    } else {
+        // tslint:disable-next-line:no-console
+        console.error('(No stack trace)');
+    }
+    process.exit(1);
+}
diff --git a/packages/pipeline/src/utils/transformers/big_number.ts b/packages/pipeline/src/utils/transformers/big_number.ts
new file mode 100644
index 000000000..5f2e4d565
--- /dev/null
+++ b/packages/pipeline/src/utils/transformers/big_number.ts
@@ -0,0 +1,16 @@
+import { BigNumber } from '@0x/utils';
+import { ValueTransformer } from 'typeorm/decorator/options/ValueTransformer';
+
+export class BigNumberTransformer implements ValueTransformer {
+    // tslint:disable-next-line:prefer-function-over-method
+    public to(value: BigNumber | null): string | null {
+        return value === null ? null : value.toString();
+    }
+
+    // tslint:disable-next-line:prefer-function-over-method
+    public from(value: string | null): BigNumber | null {
+        return value === null ? null : new BigNumber(value);
+    }
+}
+
+export const bigNumberTransformer = new BigNumberTransformer();
diff --git a/packages/pipeline/src/utils/transformers/index.ts b/packages/pipeline/src/utils/transformers/index.ts
new file mode 100644
index 000000000..232c1c5de
--- /dev/null
+++ b/packages/pipeline/src/utils/transformers/index.ts
@@ -0,0 +1,2 @@
+export * from './big_number';
+export * from './number_to_bigint';
diff --git a/packages/pipeline/src/utils/transformers/number_to_bigint.ts b/packages/pipeline/src/utils/transformers/number_to_bigint.ts
new file mode 100644
index 000000000..85560c1f0
--- /dev/null
+++ b/packages/pipeline/src/utils/transformers/number_to_bigint.ts
@@ -0,0 +1,27 @@
+import { BigNumber } from '@0x/utils';
+import { ValueTransformer } from 'typeorm/decorator/options/ValueTransformer';
+
+const decimalRadix = 10;
+
+// Can be used to convert a JavaScript number type to a Postgres bigint type and
+// vice versa. By default TypeORM will silently convert number types to string
+// if the corresponding Postgres type is bigint. See
+// https://github.com/typeorm/typeorm/issues/2400 for more information.
+export class NumberToBigIntTransformer implements ValueTransformer {
+    // tslint:disable-next-line:prefer-function-over-method
+    public to(value: number): string {
+        return value.toString();
+    }
+
+    // tslint:disable-next-line:prefer-function-over-method
+    public from(value: string): number {
+        if (new BigNumber(value).greaterThan(Number.MAX_SAFE_INTEGER)) {
+            throw new Error(
+                `Attempted to convert PostgreSQL bigint value (${value}) to JavaScript number type but it is too big to safely convert`,
+            );
+        }
+        return Number.parseInt(value, decimalRadix);
+    }
+}
+
+export const numberToBigIntTransformer = new NumberToBigIntTransformer();