TypeScript knex.select函數代碼示例

async function getDataForCalculateVolumeForAllMarkets(db: Knex, opts: RebuildVolumeOpts): Promise<{
  allMarkets: Array<RebuildVolumeMarketsRow>,
  allTrades: Array<RebuildVolumeTradesRow>,
}> {
  // Why opts.manualPostProcessDatabaseResults? our DB layer converts whitelisted
  // fields automatically via postProcessDatabaseResults(), but Knex may be
  // passed from a context where this isn't setup (such as in a DB migration).
  const allMarkets: Array<RebuildVolumeMarketsRow> = await db.select("marketId", "minPrice", "maxPrice").from("markets").then((result) => opts.manualPostProcessDatabaseResults ? postProcessDatabaseResults(result) : result);
  const allTrades: Array<RebuildVolumeTradesRow> = await db.select("marketId", "outcome", "numCreatorTokens", "numCreatorShares", "numFillerTokens", "numFillerShares").from("trades").then((result) => opts.manualPostProcessDatabaseResults ? postProcessDatabaseResults(result) : result);
  return {
    allMarkets,
    allTrades,
  };
}

export async function getForkMigrationTotals(db: Knex, augur: Augur, params: t.TypeOf<typeof ForkMigrationTotalsParams>) {
  const query = db.select([
      "universe",
      "payouts.isInvalid",
      "payouts.payout0",
      "payouts.payout1",
      "payouts.payout2",
      "payouts.payout3",
      "payouts.payout4",
      "payouts.payout5",
      "payouts.payout6",
      "payouts.payout7",
      "token_supply.supply AS repTotal"]).from("universes")
    .join("token_supply", "universes.reputationToken", "token_supply.token")
    .join("payouts", "payouts.marketId", "universes.universe")
    .where("universes.parentUniverse", params.parentUniverse);

  const forkMigrationTotals: Array<ForkMigrationTotalsRow<BigNumber>> = await query;
  return forkMigrationTotals.reduce((acc: UIForkMigrationTotals<string>, cur) => {
    const payout: Array<string> = [
      cur.payout0, cur.payout1, cur.payout2, cur.payout3, cur.payout4, cur.payout5, cur.payout6, cur.payout7,
    ].filter((payout: BigNumber|null): boolean => payout != null).map( (payout: BigNumber) => payout.toString());
    const universeTotals = formatBigNumberAsFixed<Partial<UIForkMigrationTotalsRow<BigNumber>>, Partial<UIForkMigrationTotalsRow<string>>>({
      universe: cur.universe,
      repTotal: cur.repTotal,
      isInvalid: Boolean(cur.isInvalid),
    });
    acc[cur.universe] = Object.assign({ payout }, universeTotals) as UIForkMigrationTotalsRow<string>;
    return acc;
  }, {});
}

export async function insertPayout(db: Knex, marketId: Address, payoutNumerators: Array<string|number|null>, invalid: boolean, tentativeWinning: boolean): Promise<number> {
  const payoutRow: { [index: string]: string|number|boolean|null } = {
    marketId,
    isInvalid: invalid,
  };
  payoutNumerators.forEach((value, i): void => {
    if (value == null) return;
    payoutRow["payout" + i] = new BigNumber(value, 10).toString();
  });
  const payoutIdRow: { payoutId: number }|null = await db.select("payoutId").from("payouts").where(payoutRow).first();
  if (payoutIdRow != null) {
    return payoutIdRow.payoutId;
  } else {
    const payoutRowWithTentativeWinning = Object.assign({},
      payoutRow,
      { tentativeWinning: Number(tentativeWinning) },
    );
    let query = db.insert(payoutRowWithTentativeWinning).into("payouts");
    if (db.client.config.client !== "sqlite3") {
      query = query.returning("payoutId");
    }
    const payoutIdRow: Array<number> = await query;
    if (!payoutIdRow || !payoutIdRow.length) throw new Error("No payoutId returned");
    return payoutIdRow[0];
  }
}

export async function getDisputeTokens(db: Knex, augur: {}, params: t.TypeOf<typeof DisputeTokensParams>) {
  const query: Knex.QueryBuilder = db.select(["payouts.*", "disputes.crowdsourcerId as disputeToken", "balances.balance", "market_state.reportingState"]).from("disputes");
  query.join("markets", "markets.marketId", "crowdsourcers.marketId");
  query.leftJoin("market_state", "markets.marketStateId", "market_state.marketStateId");
  query.leftJoin("blocks", "markets.creationBlockNumber", "blocks.blockNumber");
  query.join("crowdsourcers", "crowdsourcers.crowdsourcerId", "disputes.crowdsourcerId");
  query.join("payouts", "payouts.payoutId", "crowdsourcers.payoutId");
  query.join("balances", "crowdsourcers.crowdsourcerId", "balances.token");
  query.where("universe", params.universe).where("disputes.reporter", params.account).where("balances.balance", ">", 0).where("balances.owner", params.account);
  if (params.stakeTokenState == null || params.stakeTokenState === "ALL") {
    // currently, do nothing, leaving this in case we want to flavor how we group or present response
  } else if (params.stakeTokenState === "UNFINALIZED") {
    query.whereNot("market_state.reportingState", ReportingState.FINALIZED);
  } else if (params.stakeTokenState === "UNCLAIMED") {
    query.where("payouts.winning", 1);
  } else {
    throw new Error("Invalid disputeTokenState");
  }
  const disputeTokens: Array<DisputeTokensRowWithTokenState<BigNumber>> = await query;
  return disputeTokens.reduce((acc: UIDisputeTokens<string>, cur) => {
    const tokenInfo = reshapeDisputeTokensRowToUIDisputeTokenInfo(cur);
    acc[cur.disputeToken] = formatBigNumberAsFixed<UIDisputeTokenInfo<BigNumber>, UIDisputeTokenInfo<string>>(tokenInfo);
    return acc;
  }, {});
}

export async function getFeeWindows(db: Knex, augur: Augur, params: t.TypeOf<typeof FeeWindowsParams>) {
  if (params.universe == null || params.account == null) throw new Error("Must provide both universe and account");

  let query = db.select(["fee_windows.feeWindow", "fee_windows.startTime", "fee_windows.endTime", "balances.balance", "participation_token.supply AS participationTokenStake", db.raw("IFNULL(fee_token.supply, 0) AS feeTokenStake"), db.raw("IFNULL(cash.balance, 0) as totalFees")]).from("fee_windows")
    .join("balances", "fee_windows.feeWindow", "balances.token")
    .join("token_supply AS participation_token", "participation_token.token", "fee_windows.feeWindow")
    .leftJoin("token_supply AS fee_token", "fee_token.token", "fee_windows.feeToken")
    .leftJoin("balances AS cash", function () {
      this
        .on("cash.owner", db.raw("fee_windows.feeWindow"))
        .andOn("cash.token", db.raw("?", getCashAddress(augur)));
    })
    .where("fee_windows.universe", params.universe)
    .where("balances.balance", ">", 0)
    .where("balances.owner", params.account);
  if (!params.includeCurrent) query = query.where("fee_windows.startTime", "<", getCurrentTime());

  const unclaimedFeeWindowsRows: Array<UnclaimedFeeWindowsRow<BigNumber>> = await query;
  return unclaimedFeeWindowsRows.reduce((acc: UnclaimedFeeWindows<string>, cur) => {
    const totalStake = cur.participationTokenStake.plus(cur.feeTokenStake);
    acc[cur.feeWindow] = formatBigNumberAsFixed<UnclaimedFeeWindowInfo<BigNumber>, UnclaimedFeeWindowInfo<string>>({
      startTime: cur.startTime,
      endTime: cur.endTime,
      balance: cur.balance,
      expectedFees: cur.balance.times(cur.totalFees).dividedBy(totalStake),
    });
    return acc;
  }, {});
}

export async function getAllOrders(db: Knex, augur: {}, params: t.TypeOf<typeof AllOrdersParams>): Promise<AllOrders> {
  const query = db.select(
    [
      "orderId",
      "marketId",
      "originalTokensEscrowed",
      "originalSharesEscrowed",
      "tokensEscrowed",
      "sharesEscrowed",
    ]).from("orders")
    .where("orderCreator", params.account)
    .where("orderState", "OPEN");
  const allOrders: Array<AllOrdersRow<BigNumber>> = await query;
  return allOrders.reduce((acc: AllOrders, cur: AllOrdersRow<BigNumber>) => {
    acc[cur.orderId] = formatBigNumberAsFixed<AllOrdersRow<BigNumber>, AllOrdersRow<string>>({
      orderId: cur.orderId,
      originalTokensEscrowed: cur.originalTokensEscrowed,
      originalSharesEscrowed: cur.originalSharesEscrowed,
      tokensEscrowed: cur.tokensEscrowed,
      sharesEscrowed: cur.sharesEscrowed,
      marketId: cur.marketId,
    });
    return acc;
  }, {});
}

async function getVolume(db: Knex, startBlock: number, endBlock: number, params: PlatformActivityStatsParamsType): Promise<Knex.QueryBuilder> {
  return db
    .select("amount as volume")
    .from("trades")
    .innerJoin("markets", "markets.marketId", "trades.marketId")
    .whereBetween("trades.blockNumber", [startBlock, endBlock])
    .andWhere("markets.universe", params.universe);
}

 return knex.schema.hasColumn("markets", "openInterest").then(async(exists) => {
   if (!exists) await knex.schema.table("markets", (t) => t.string("openInterest").defaultTo("0"));
   const markets = await knex.select("marketId").from("markets");
   for (const market of markets) {
     await updateMarketOpenInterest(knex, market.marketId);
   }
   return;
 });

 public async migrateUp(): Promise<any> {
   await this.db.schema.dropTableIfExists("search_en");
   await this.db.schema.raw(`CREATE VIRTUAL TABLE search_en USING fts4(marketId, category, tags, shortDescription, longDescription, scalarDenomination, resolutionSource)`);
   const markets: Array<MarketsRow<BigNumber>>  = await this.db.select("*").from("markets");
   for (const market of markets) {
       await this.addSearchData(contentSearchBuilder(formatBigNumberAsFixed(market)));
     }
 }

  return async(db: Knex) => {
    const amount: BigNumber = new BigNumber(log.amount, 10);
    const price: BigNumber = new BigNumber(log.price, 10);
    const orderType: string = log.orderType;
    const moneyEscrowed: BigNumber = new BigNumber(log.moneyEscrowed, 10);
    const sharesEscrowed: BigNumber = new BigNumber(log.sharesEscrowed, 10);
    const shareToken: Address = log.shareToken;
    const tokensRow: TokensRow|undefined = await db.first("marketId", "outcome").from("tokens").where({ contractAddress: shareToken });
    if (!tokensRow) throw new Error(`market and outcome not found for shareToken ${shareToken} (${log.transactionHash}`);
    const marketId = tokensRow.marketId;
    const outcome = tokensRow.outcome!;
    const marketsRow: MarketsRow<BigNumber> = await db.first("minPrice", "maxPrice", "numTicks", "numOutcomes").from("markets").where({ marketId });
    if (!marketsRow) throw new Error(`market not found: ${marketId}`);
    const minPrice = marketsRow.minPrice;
    const maxPrice = marketsRow.maxPrice;
    const numTicks = marketsRow.numTicks;
    const tickSize = numTicksToTickSize(numTicks, minPrice, maxPrice);
    const numOutcomes = marketsRow.numOutcomes;
    const fullPrecisionAmount = augur.utils.convertOnChainAmountToDisplayAmount(amount, tickSize);
    const fullPrecisionPrice = augur.utils.convertOnChainPriceToDisplayPrice(price, minPrice, tickSize);
    const orderTypeLabel = orderType === "0" ? "buy" : "sell";
    const displaySharesEscrowed = augur.utils.convertOnChainAmountToDisplayAmount(sharesEscrowed, tickSize).toString();
    const displayTokensEscrowed = fixedPointToDecimal(moneyEscrowed, BN_WEI_PER_ETHER).toString();
    const orderData: OrdersRow<string> = {
      marketId,
      blockNumber: log.blockNumber,
      transactionHash: log.transactionHash,
      logIndex: log.logIndex,
      outcome,
      shareToken,
      orderCreator: log.creator,
      orderState: OrderState.OPEN,
      tradeGroupId: log.tradeGroupId,
      orderType: orderTypeLabel,
      price: formatOrderPrice(orderTypeLabel, minPrice, maxPrice, fullPrecisionPrice),
      amount: formatOrderAmount(fullPrecisionAmount),
      originalAmount: formatOrderAmount(fullPrecisionAmount),
      fullPrecisionPrice: fullPrecisionPrice.toString(),
      fullPrecisionAmount: fullPrecisionAmount.toString(),
      originalFullPrecisionAmount: fullPrecisionAmount.toString(),
      originalTokensEscrowed: displayTokensEscrowed,
      originalSharesEscrowed: displaySharesEscrowed,
      tokensEscrowed: displayTokensEscrowed,
      sharesEscrowed: displaySharesEscrowed,
    };
    const orderId = { orderId: log.orderId };
    const ordersRows: Array<Partial<OrdersRow<BigNumber>>> = await db.select("marketId").from("orders").where(orderId);
    let upsertOrder: QueryBuilder;
    if (!ordersRows || !ordersRows.length) {
      upsertOrder = db.insert(Object.assign(orderData, orderId)).into("orders");
    } else {
      upsertOrder = db.from("orders").where(orderId).update(orderData);
    }
    await upsertOrder;
    await marketPendingOrphanCheck(db, orderData);

    augurEmitter.emit(SubscriptionEventNames.OrderCreated, Object.assign({}, log, orderData));
  };