TypeScript better-sqlite3.prepare函数代码示例

 // data could, e.g., be a uuencoded image
 // We return the sha1 hash of it, and store it, along with a reference count.
 // ipynb = (optional) text that is also stored and will be
 //         returned when get_ipynb is called
 //         This is used for some iframe support code.
 save(data, type, ipynb?): string {
   if (BASE64_TYPES.includes(type)) {
     data = Buffer.from(data, "base64");
   } else {
     data = Buffer.from(data);
   }
   const sha1: string = misc_node.sha1(data);
   const row = this._db.prepare("SELECT * FROM blobs where sha1=?").get(sha1);
   if (row == null) {
     this._db
       .prepare("INSERT INTO blobs VALUES(?, ?, ?, ?, ?)")
       .run([sha1, data, type, ipynb, new Date().valueOf()]);
   } else {
     this._db
       .prepare("UPDATE blobs SET time=? WHERE sha1=?")
       .run([new Date().valueOf(), sha1]);
   }
   return sha1;
 }

 _clean(): void {
   // Delete anything old...
   // The main point of this blob store being in the db is to ensure that when the
   // project restarts, then user saves an ipynb,
   // that they do not loose any work.  So a few weeks should be way more than enough.
   // Note that TimeTravel may rely on these old blobs, so images in TimeTravel may
   // stop working after this long.  That's a tradeoff.
   this._db
     .prepare("DELETE FROM blobs WHERE time <= ?")
     .run(months_ago(1) - 0);
 }

exports.loadHist = function(from, to, stock_name, category){
  var db = new Database('afina.db');
  var sqlLoadHist = 'select * from trade_history t';
  var params:string[] = [];
  if(arguments.length >0){
    sqlLoadHist += ' where t.trade_date >= date(?) and t.trade_date <= date(?) and t.stock_name like ? || "%" and t.in_out like ? || "%"';
    params = [from, to, stock_name, category];
  }
  sqlLoadHist += ' order by 1, 2, 3'

  return db.prepare(sqlLoadHist).all(params);
}

 _init(): void {
   if (JUPYTER_BLOBS_DB_FILE == "memory") {
     this._db = new Database(".db", { memory: true });
   } else {
     this._db = new Database(JUPYTER_BLOBS_DB_FILE);
   }
   this._db
     .prepare(
       "CREATE TABLE IF NOT EXISTS blobs (sha1 TEXT, data BLOB, type TEXT, ipynb TEXT, time INTEGER)"
     )
     .run();
   this._clean(); // do this once on start
 }

 get_ipynb(sha1: string): any {
   const row = this._db
     .prepare("SELECT ipynb, type, data FROM blobs where sha1=?")
     .get(sha1);
   if (row == null) {
     return;
   }
   if (row.ipynb != null) {
     return row.ipynb;
   }
   if (BASE64_TYPES.includes(row.type)) {
     return row.data.toString("base64");
   } else {
     return row.data.toString();
   }
 }

 // used in testing
 delete_all_blobs(): void {
   this._db.prepare("DELETE FROM blobs").run();
 }

 keys(): string[] {
   return this._db
     .prepare("SELECT sha1 FROM blobs")
     .all()
     .map(x => x.sha1);
 }

  /*
  free(sha1: string): void {
    // instead, stuff gets freed 1 month after last save.
  }
  */

  // Return data with given sha1, or undefined if no such data.
  get(sha1: string): undefined | Buffer {
    const x = this._db.prepare("SELECT data FROM blobs where sha1=?").get(sha1);
    if (x != null) {
      return x.data;
    }
  }