TypeScript knex.raw函数代码示例

 return async (db: Knex) => {
   await rollbackMarketState(db, log.market, ReportingState.AWAITING_NEXT_WINDOW);
   await db.update({
     universe: log.originalUniverse,
     needsMigration: db.raw("needsMigration + 1"),
     needsDisavowal: db.raw("needsDisavowal + 1"),
   }).into("markets").where("marketId", log.market);
   return db.update({
     disavowed: db.raw("disavowed - 1"),
   }).into("crowdsourcers").where("marketId", log.market);
 };

export async function getAccountTimeRangedStats(db: Knex, augur: Augur, params: AccountTimeRangedStatsParamsType): Promise<AccountTimeRangeResult> {
  // guards
  if (params.startTime && params.endTime && params.startTime > params.endTime)
    throw new Error("startTime must be less than or equal to endTime");
  if (!params.startTime) params.startTime = 0;
  if (!params.endTime) params.endTime = 0;

  // collates stats from several joins unrelated except by blockNumbers
  const sql = db.select(["startBlock", "endBlock"])
    .countDistinct("markets.marketId as marketsCreated")
    .countDistinct("trades.transactionhash as numberOfTrades")
    .countDistinct("trades.marketId as marketsTraded")
    .countDistinct("proceeds.marketId as redeemedPositions")
    .from(db.raw(blockNumberForTimestampQuery(db, params.startTime || 0, "start").toString()).wrap("(", ")"))
    .join(db.raw(blockNumberForTimestampQuery(db, params.endTime || 0, "end").toString()).wrap("(", ")"))
    .leftJoin(db.raw("markets on universe=? and markets.marketCreator=? and markets.creationBlockNumber between startBlock and endBlock",
      [params.universe, params.account]))
    .leftJoin(db.raw("trades as trades on (creator=? or filler=?) and (trades.blockNumber between startBlock and endblock) AND trades.marketId in (select markets.marketId from markets where universe=?)",
      [params.account, params.account, params.universe]))
    .leftJoin(db.raw("trading_proceeds as proceeds on account=? and (proceeds.blockNumber between startBlock and endBlock) and proceeds.marketId in (select markets.marketId from markets where universe=?)",
      [params.account, params.universe]));

  const res = (await sql).shift();

  const startBlock = res.startBlock;
  const endBlock = res.endBlock;
  const marketsCreated = res.marketsCreated;
  const numberOfTrades = res.numberOfTrades;
  const marketsTraded = res.marketsTraded;
  const redeemedPositions = res.redeemedPositions;

  if (!startBlock || !endBlock || startBlock > endBlock)
    throw new Error("startTime/endTime error");

  // Positions and successfulDisputes must be in a separate queries because they kill sqlLite3 otherwise.
  const positionsResult = (await getPositions(db, startBlock, endBlock, params)).shift();
  const positions = positionsResult.positions;

  const successfulDisputesResult = (await getSuccessfulDisputes(db, startBlock, endBlock, params)).shift();
  const successfulDisputes = successfulDisputesResult.successfulDisputes;

  const result: AccountTimeRangeResult = {
    positions,
    marketsCreated,
    numberOfTrades,
    successfulDisputes,
    marketsTraded,
    redeemedPositions,
  };

  return result;
}

export async function getAccountTransferHistory(db: Knex, augur: {}, params: t.TypeOf<typeof AccountTransferHistoryParams>): Promise<Array<TransferRow<string>>> {
  const query = db("transfers").select([
    "transfers.transactionHash",
    "transfers.logIndex",
    "transfers.blockNumber as creationBlockNumber",
    "transfers.sender",
    "transfers.recipient",
    "transfers.token",
    "transfers.value",
    "blocks.timestamp as creationTime",
    "blocks.blockHash",
    "tokens.symbol",
    "tokens.outcome",
    "tokens.marketId",
    db.raw("CASE WHEN transfers.transactionHash IN (SELECT DISTINCT transactionHash FROM trades UNION SELECT DISTINCT transactionHash FROM orders) THEN 1 ELSE 0 END as isInternalTransfer"),
  ]).where((db: Knex): Knex.QueryBuilder => db.where("sender", params.account).orWhere("recipient", params.account));
  query.join("blocks", "blocks.blockNumber", "transfers.blockNumber");
  query.join("tokens", "tokens.contractAddress", "transfers.token");
  if (params.isInternalTransfer != null) query.where("isInternalTransfer", params.isInternalTransfer);
  if (params.token != null) query.andWhere("token", params.token);
  if (params.earliestCreationTime != null) query.where("creationTime", ">=", params.earliestCreationTime);
  if (params.latestCreationTime != null) query.where("creationTime", "<=", params.latestCreationTime);
  const results = await queryModifier<TransferRow<BigNumber>>(db, query, "transfers.blockNumber", "desc", params);
  return results.map((result) => formatBigNumberAsFixed<TransferRow<BigNumber>, TransferRow<string>>(result));
}

async function queryModifierUserland<T>(
  db: Knex,
  query: Knex.QueryBuilder,
  defaultSortBy: string,
  defaultSortOrder: string,
  sortLimitParams: Partial<SortLimit>,
): Promise<Array<T>> {
  type RowWithSort = T & { xMySorterFieldx: BigNumber };

  let sortField: string = defaultSortBy;
  let sortDescending: boolean = defaultSortOrder.toLowerCase() === "desc";

  if (sortLimitParams.sortBy != null) {
    sortField = sortLimitParams.sortBy;
    if (typeof (sortLimitParams.isSortDescending) !== "undefined" && sortLimitParams.isSortDescending !== null) {
      sortDescending = sortLimitParams.isSortDescending;
    }
  }

  const rows: Array<RowWithSort> = await query.select(db.raw(`?? as "xMySorterFieldx"`, [sortField]));
  const ascendingSorter = (left: RowWithSort, right: RowWithSort) => safeBigNumberCompare(left.xMySorterFieldx, right.xMySorterFieldx);
  const descendingSorter = (left: RowWithSort, right: RowWithSort) => safeBigNumberCompare(right.xMySorterFieldx, left.xMySorterFieldx);
  const results = rows.sort(sortDescending ? descendingSorter : ascendingSorter);
  if (sortLimitParams.limit == null && sortLimitParams.offset == null)
    return results;
  return results.slice(sortLimitParams.offset || 0, sortLimitParams.limit || results.length);
}

exports.up = async (knex: Knex): Promise<any> => {
  return knex.raw("DROP VIEW IF EXISTS balances_detail").then((): PromiseLike<any> => {
    return knex.raw(`CREATE VIEW balances_detail AS
      SELECT tokens."contractAddress" as token, symbol, "marketId", outcome, "feeWindow", owner, balance, supply FROM balances
      LEFT JOIN tokens ON balances.token = tokens."contractAddress"
      LEFT JOIN token_supply ON token_supply.token = tokens."contractAddress"`);
  });
};

exports.up = async (knex: Knex): Promise<any> => {
  return knex.raw("DROP VIEW IF EXISTS all_participants").then((): PromiseLike<any> => {
    return knex.raw(`CREATE VIEW all_participants AS
      SELECT
        markets.universe,
        markets."marketId",
        markets."feeWindow",
        'initial_report' as type,
        "initialReporter" as participantAddress,
        initial_reports.reporter,
        initial_reports."blockNumber",
        "amountStaked" as size,
        "amountStaked" as participantSupply,
        ${knex.client.config.client === "sqlite3" ?
        `CASE WHEN redeemed = 0 THEN initial_reports.amountStaked ELSE 0 END as reporterBalance,` :
        `CASE WHEN redeemed = false THEN initial_reports."amountStaked" ELSE '0' END as reporterBalance,`}
        contractBalances.token as reputationToken,
        contractBalances.balance as reputationTokenBalance,
        initial_reports."payoutId",
        1 as completed,
        "reportingState",
        markets.forking,
        ${knex.client.config.client === "sqlite3" ? "disavowed" : "cast(disavowed AS BOOLEAN) as disavowed"},
        markets."needsDisavowal"
      FROM initial_reports
        JOIN markets ON markets."marketId" = initial_reports."marketId"
        JOIN universes ON markets.universe = universes.universe
        JOIN balances as contractBalances ON (contractBalances.owner = initial_reports."initialReporter" AND contractBalances.token = universes."reputationToken")
        JOIN market_state ON markets."marketStateId" = market_state."marketStateId"
      union
      SELECT
        markets.universe,
        crowdsourcers."marketId",
        markets."feeWindow",
        'crowdsourcer' as type,
        crowdsourcers."crowdsourcerId" as participantAddress,
        accountBalances.owner as reporter,
        crowdsourcers."blockNumber",
        crowdsourcers.size,
        participantSupply.supply as participantSupply,
        accountBalances.balance as reporterBalance,
        contractBalances.token as reputationToken,
        contractBalances.balance as reputationTokenBalance,
        crowdsourcers."payoutId",
        crowdsourcers.completed,
        "reportingState",
        markets.forking,
        ${knex.client.config.client === "sqlite3" ? "crowdsourcers.disavowed" : "cast(crowdsourcers.disavowed AS BOOLEAN) as disavowed"},
        markets."needsDisavowal" from crowdsourcers
        JOIN markets ON markets."marketId" = crowdsourcers."marketId"
        JOIN universes ON markets.universe = universes.universe
        JOIN balances as accountBalances ON (accountBalances.token = crowdsourcers."crowdsourcerId")
        JOIN balances as contractBalances ON (contractBalances.owner = crowdsourcers."crowdsourcerId" AND contractBalances.token = universes."reputationToken")
        JOIN token_supply as participantSupply ON participantSupply.token = crowdsourcers."crowdsourcerId"
        JOIN market_state ON markets."marketStateId" = market_state."marketStateId"`);
  });
};

exports.up = async (knex: Knex): Promise<any> => {
  await knex.schema.dropTableIfExists("wcl_profit_loss_timeseries");

  await knex.schema.createTable("wcl_profit_loss_timeseries", (table: Knex.CreateTableBuilder): void => {
    table.string("account", 42).notNullable();
    table.string("marketId", 42).notNullable();
    table.specificType("outcome", "integer NOT NULL CONSTRAINT nonnegativeOutcome CHECK (\"outcome\" >= 0)");
    table.specificType("price", "varchar(255) NOT NULL CONSTRAINT nonnegativeAmount CHECK (ltrim(\"price\", '-') = \"price\")");
    table.string("position", 42).notNullable();
    table.string("quantityOpened", 42).notNullable();
    table.string("profit", 255).defaultTo("0");
    table.string("frozenFunds", 255).defaultTo("0");
    table.string("realizedCost", 255).defaultTo("0");
    table.string("transactionHash", 66).notNullable();
    table.specificType("timestamp", "integer NOT NULL CONSTRAINT nonnegativeTimestamp CHECK (\"timestamp\" >= 0)");
    table.specificType("logIndex", "integer NOT NULL CONSTRAINT \"nonnegativelogIndex\" CHECK (\"logIndex\" >= 0)");
    table.specificType("blockNumber", "integer NOT NULL CONSTRAINT positiveOrderBlockNumber CHECK (\"blockNumber\" > 0)");
  });

  const query = knex("trades")
    .select(knex.raw([`"marketId"`, "outcome", "amount", "price", `"orderType"`, "creator", "filler", "false as claim", `"blockNumber"`, `"logIndex"`, `"transactionHash", "numCreatorTokens", "numCreatorShares", "numFillerTokens", "numFillerShares"`]))
    .union((builder: Knex.QueryBuilder) => {
      return builder
        .from("trading_proceeds")
        .select(knex.raw([
          `"marketId"`, // TradeOrClaimRow.marketId
          "0", // TradeOrClaimRow.outcome
          "'0'", // TradeOrClaimRow.amount
          "'0'", // TradeOrClaimRow.price
          "'0'", // TradeOrClaimRow.orderType
          "''", // TradeOrClaimRow.creator
          "account", // TradeOrClaimRow.filler NB row.filler passed as `account` below
          "true as claim", // TradeOrClaimRow.claim
          `"blockNumber"`,
          `"logIndex"`,
          `"transactionHash"`,
          "'0'", // TradeOrClaimRow.numCreatorTokens
          "'0'", // TradeOrClaimRow.numCreatorShares
          "'0'", // TradeOrClaimRow.numFillerTokens
          "'0'", // TradeOrClaimRow.numFillerShares
        ]));
    });
  query.orderByRaw(`"blockNumber", "logIndex"`);

  const results: Array<TradeOrClaimRow> = await query;

  for (const row of results) {
    if (row.claim) {
      await updateProfitLossClaimProceeds(knex, row.marketId, row.filler, row.transactionHash, row.blockNumber, row.logIndex);
    } else {
      await updateProfitLoss(knex, row.marketId, row.orderType === "buy" ? row.amount : row.amount.negated(), row.creator, row.outcome, row.price, row.transactionHash, row.blockNumber, row.logIndex, row);
      await updateProfitLoss(knex, row.marketId, row.orderType === "sell" ? row.amount : row.amount.negated(), row.filler, row.outcome, row.price, row.transactionHash, row.blockNumber, row.logIndex, row);
    }
  }

  await knex.schema.dropTableIfExists("profit_loss_timeseries");
};

 await each(marketIds, async (marketIdRow) => {
   await updateMarketState(db, marketIdRow.marketId, blockNumber, ReportingState.AWAITING_FINALIZATION);
   augurEmitter.emit(SubscriptionEventNames.MarketState, {
     universe: marketIdRow.universe,
     marketId: marketIdRow.marketId,
     reportingState: ReportingState.AWAITING_FINALIZATION,
   });
   return db("payouts")
     .where({ marketId: marketIdRow.marketId })
     .update("winning", db.raw(`"tentativeWinning"`));
 });

exports.up = (knex: Knex)  => knex.raw(`
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  CREATE TABLE ${TODO_TABLE} (
      guid uuid DEFAULT uuid_generate_v4() UNIQUE,
      ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      title varchar NOT NULL,
      content text NOT NULL
    );

    CREATE INDEX id_idx ON ${TODO_TABLE} (guid);
    CREATE INDEX ts_idx ON ${TODO_TABLE} (ts);
    CREATE INDEX title_idx ON ${TODO_TABLE} (title);
`);

async function copyRunDbFixerScript(dbFileNamePath: string, backupDbPath: string): Promise<void> {
  await promisify(fs.copyFile)(dbFileNamePath, backupDbPath);
  // need to do this because cli runs migrations in .ts and augur-app runs migrations in .js
  const db: Knex = Knex({
    client: "sqlite3",
    connection: {
      filename: backupDbPath,
    },
    acquireConnectionTimeout: 5 * 60 * 1000,
    useNullAsDefault: true,
  });
  await db.raw("update knex_migrations set name = substr(name,1, length(name)-2) || 'js';");
  await db.destroy();
}

async function getParticipationTokenEthFees(db: Knex, augur: Augur, reporter: Address, universe: Address): Promise<Array<ParticipationTokenEthFee>> {
  const participationTokenQuery = db
    .select([
      "fee_windows.feeWindow",
      "participationToken.balance AS participationTokens",
      db.raw("IFNULL(feeTokenSupply.supply,0) as feeTokenSupply"),
      db.raw("IFNULL(participationTokenSupply.supply,0) as participationTokenSupply"),
      db.raw("IFNULL(cashFeeWindow.balance,0) as cashFeeWindow"),
    ])
    .from("fee_windows");
  participationTokenQuery.join("balances AS participationToken", function() {
    this.on("participationToken.token", db.raw("fee_windows.feeWindow")).andOn("participationToken.owner", db.raw("?", [reporter]));
  });
  participationTokenQuery.leftJoin("balances AS cashFeeWindow", function() {
    this.on("cashFeeWindow.owner", db.raw("fee_windows.feeWindow")).on("cashFeeWindow.token", db.raw("?", getCashAddress(augur)));
  });
  participationTokenQuery
    .leftJoin("token_supply as feeTokenSupply", "feeTokenSupply.token", "fee_windows.feeToken")
    .leftJoin("token_supply as participationTokenSupply", "participationTokenSupply.token", "fee_windows.feeWindow")
    .whereNot("participationTokens", "0")
    .where("fee_windows.state", FeeWindowState.PAST)
    .where("fee_windows.universe", universe);
  const participationTokens: Array<ParticipationTokensEthFeeRow> = await participationTokenQuery;
  return _.map(participationTokens, (participationToken) => {
    const totalFeeTokensInFeeWindow = new BigNumber(participationToken.feeTokenSupply).plus(new BigNumber(participationToken.participationTokenSupply));
    const cashInFeeWindow = new BigNumber(participationToken.cashFeeWindow);
    const participationTokens = new BigNumber(participationToken.participationTokens);
    const reporterShareOfFeeWindow = totalFeeTokensInFeeWindow.isZero() ? ZERO : participationTokens.dividedBy(totalFeeTokensInFeeWindow);
    const ethFees = reporterShareOfFeeWindow.times(cashInFeeWindow);
    return {
      feeWindow: participationToken.feeWindow,
      ethFees,
      participationTokens,
    };
  });
}

 public async migrateUp(): Promise<any> {
     await this.db.schema.table("markets", (markets) => {
       markets.specificType("searchProperties", "tsvector");
     });
     await this.db("markets").update({
       searchProperties: this.db.raw(`
             setweight(to_tsvector('english', coalesce(category, '')), 'A') ||
             setweight(to_tsvector('english', coalesce(tag1, '')), 'A') ||
             setweight(to_tsvector('english', coalesce(tag2, '')), 'A') ||
             setweight(to_tsvector('english', coalesce("shortDescription", '')), 'B') ||
             setweight(to_tsvector('english', coalesce("longDescription", '')), 'B') ||
             setweight(to_tsvector('english', coalesce("scalarDenomination", '')), 'C') ||
             setweight(to_tsvector('english', coalesce("resolutionSource", '')), 'C')
             `),
     });
     await this.db.schema.raw(`CREATE INDEX market_search_idx ON markets USING gin("searchProperties");`);
 }

async function getMarketsReportingParticipants(db: Knex, reporter: Address, universe: Address, participantEthFees: Array<ParticipantEthFee>): Promise<FormattedMarketInfo> {
  const initialReportersQuery = db("initial_reports")
    .distinct("initial_reports.initialReporter")
    .select(["initial_reports.disavowed", "initial_reports.marketId", "markets.universe", "market_state.reportingState", "markets.forking", "markets.needsMigration"])
    .join("markets", "initial_reports.marketId", "markets.marketId")
    .join("fee_windows", "markets.feeWindow", "fee_windows.feeWindow")
    .join("market_state", "market_state.marketStateId", "markets.marketStateId")
    .whereRaw("(markets.forking OR market_state.reportingState IN (?, ?, ?) OR (initial_reports.disavowed != 0 OR markets.needsDisavowal) AND (fee_windows.state = ? OR reportingState = ? ) )", [
      ReportingState.AWAITING_FINALIZATION,
      ReportingState.FINALIZED,
      ReportingState.FORKING,
      FeeWindowState.PAST,
      ReportingState.AWAITING_FORK_MIGRATION,
    ])
    .where("markets.universe", universe)
    .where("initial_reports.reporter", reporter)
    .where("initial_reports.redeemed", 0);
  const crowdsourcersQuery = db("crowdsourcers")
    .distinct("crowdsourcers.crowdsourcerId")
    .select(["crowdsourcers.disavowed", "crowdsourcers.marketId", "markets.universe", "market_state.reportingState", "markets.forking", "markets.needsMigration", "balances.balance as amountStaked"])
    .join("balances", "balances.token", "crowdsourcers.crowdsourcerId")
    .join("markets", "crowdsourcers.marketId", "markets.marketId")
    .join("fee_windows", "crowdsourcers.feeWindow", "fee_windows.feeWindow")
    .join("market_state", "market_state.marketStateId", "markets.marketStateId")
    .whereRaw("(markets.forking or (market_state.reportingState IN (?, ?, ?)) OR (crowdsourcers.disavowed != 0 OR markets.needsDisavowal) AND (fee_windows.state = ? OR reportingState = ? ) )", [
      ReportingState.AWAITING_FINALIZATION,
      ReportingState.FINALIZED,
      ReportingState.FORKING,
      FeeWindowState.PAST,
      ReportingState.AWAITING_FORK_MIGRATION,
    ])
    .andWhere("markets.universe", universe)
    .andWhere("balances.owner", reporter)
    .andWhereNot("balances.balance", "0");
  const forkedMarketQuery = db("markets")
    .first(["markets.marketId", "markets.universe", db.raw("market_state.reportingState = 'FINALIZED' as isFinalized")])
    .where("markets.forking", 1)
    .where("markets.universe", universe)
    .join("market_state", "markets.marketId", "market_state.marketId");

  const initialReporters: Array<UnclaimedInitialReporterRow> = await initialReportersQuery;
  const crowdsourcers: Array<UnclaimedCrowdsourcerRow> = await crowdsourcersQuery;
  const forkedMarket: ForkedMarket = await forkedMarketQuery;
  return formatMarketInfo(initialReporters, crowdsourcers, forkedMarket, participantEthFees);
}

export async function getUniversesInfo(db: Knex, augur: Augur, params: t.TypeOf<typeof UniverseInfoParams>): Promise<Array<UIUniverseInfoRow<string>>> {
  const parentUniverseRow: ParentUniverseRow = await db.select("parentUniverse").from("universes").where("universe", params.universe).first();
  if (parentUniverseRow === undefined) return [];
  const query = db.select([
    "universes.universe",
    "universes.parentUniverse",
    "payouts.isInvalid",
    "payouts.payout0",
    "payouts.payout1",
    "payouts.payout2",
    "payouts.payout3",
    "payouts.payout4",
    "payouts.payout5",
    "payouts.payout6",
    "payouts.payout7",
    "balances.balance",
    "token_supply.supply",
    db.raw("count(markets.marketId) as numMarkets")]).from("universes")
    .where("universes.parentUniverse", params.universe) // Children
    .orWhere("universes.parentUniverse", parentUniverseRow.parentUniverse) // Siblings
    .orWhere("universes.universe", params.universe) // Universe
    .orWhere("universes.universe", parentUniverseRow.parentUniverse) // Parent
    .leftJoin("token_supply", "token_supply.token", "universes.reputationToken")
    .leftJoin("balances", function () {
      this
        .on("balances.owner", db.raw("?", [params.account]))
        .on("balances.token", "universes.reputationToken");
    })
    .leftJoin("markets", "markets.universe", "universes.universe")
    .leftJoin("payouts", "payouts.payoutId", "universes.payoutId")
    .groupBy("universes.universe");
  const universeInfoRows: Array<UniverseInfoRow<BigNumber>> = await query;
  return universeInfoRows.map((row: UniverseInfoRow<BigNumber>) => {
    return formatBigNumberAsFixed<UIUniverseInfoRow<BigNumber>, UIUniverseInfoRow<string>>({
      universe: row.universe,
      parentUniverse: row.parentUniverse,
      balance: row.balance || "0",
      supply: row.supply || "0",
      numMarkets: row.numMarkets,
      payout: normalizedPayoutsToFixed(normalizePayouts(row)).payout,
      isInvalid: Boolean(row.isInvalid),
    });
  });
}

 .union((builder: Knex.QueryBuilder) => {
   return builder
     .from("trading_proceeds")
     .select(knex.raw([
       `"marketId"`, // TradeOrClaimRow.marketId
       "0", // TradeOrClaimRow.outcome
       "'0'", // TradeOrClaimRow.amount
       "'0'", // TradeOrClaimRow.price
       "'0'", // TradeOrClaimRow.orderType
       "''", // TradeOrClaimRow.creator
       "account", // TradeOrClaimRow.filler NB row.filler passed as `account` below
       "true as claim", // TradeOrClaimRow.claim
       `"blockNumber"`,
       `"logIndex"`,
       `"transactionHash"`,
       "'0'", // TradeOrClaimRow.numCreatorTokens
       "'0'", // TradeOrClaimRow.numCreatorShares
       "'0'", // TradeOrClaimRow.numFillerTokens
       "'0'", // TradeOrClaimRow.numFillerShares
     ]));
 });