0xDEADBEEF

RSS odkazy english edition

sqlite-stuffing.d

19. 7. 2021 #kód
import etc.c.sqlite3;
import std.conv;
import std.random;
import std.range;
import std.string;

void main() {
  auto rand = Xorshift();

  alias randomAge    = () => uniform!"[]"(1, 3, rand) * 5;
  alias randomActive = () => uniform!"[]"(0, 1, rand);
  alias randomBool   = () => uniform!"[]"(0, 1, rand);
  alias randomCode   = () {
    auto chars = "QWERTZUIOPLKJHGFDSAYXCVBNM12345678";
    auto r = uniform(0, ulong.max);
    char[6] res;
    static foreach (i; 0 .. 6) {
      res[i] = chars[(r >> (i*5)) & 31];
    }
    return res;
  };

  void checkCode(int code) {
    if (code != SQLITE_OK) throw new Exception("error "~code.to!string);
  }


  sqlite3* db;
  sqlite3_open("sqlite.db".toStringz, &db);

  void exec(string sql) {
    checkCode(sqlite3_exec(db, sql.toStringz, null, null, null));
  }

  exec("
    PRAGMA journal_mode = OFF;
    PRAGMA synchronous = 0;
    PRAGMA cache_size = 1000000;
    PRAGMA locking_mode = EXCLUSIVE;
    PRAGMA temp_store = MEMORY;
    create table IF NOT EXISTS user (
      id INTEGER not null primary key,
      area CHAR(6),
      age INTEGER not null,
      active INTEGER not null
    );
  ");

  enum batchSize = 100;

  sqlite3_stmt* statement3;
  auto args3 = ",(null, ?, ?, ?)";
  auto sql3 = "INSERT INTO user VALUES" ~ (args3.repeat(batchSize).join)[1 .. $];
  checkCode(sqlite3_prepare_v2(db, sql3.toStringz, -1, &statement3, null));

  sqlite3_stmt* statement2;
  auto args2 = ",(null, null, ?, ?)";
  auto sql2 = "INSERT INTO user VALUES" ~ (args2.repeat(batchSize).join)[1 .. $];
  checkCode(sqlite3_prepare_v2(db, sql2.toStringz, -1, &statement2, null));

  exec("begin transaction");

  foreach (i; 0 .. 1_000_000) {
    auto age = randomAge();
    auto active = randomActive();

    sqlite3_stmt* s;

    if (randomBool()) {
      s = statement3;
      foreach (j; 0 .. batchSize) {
        sqlite3_bind_text(s, j*3+1, randomCode().ptr, 6, null);
        sqlite3_bind_int(s,  j*3+2, age);
        sqlite3_bind_int(s,  j*3+3, active);
      }

    } else {
      s = statement2;
      foreach (j; 0 .. batchSize) {
        sqlite3_bind_int(s, j*2+1, age);
        sqlite3_bind_int(s, j*2+2, active);
      }
    }

    auto rc = sqlite3_step(s);
    if (rc != SQLITE_DONE) {
      throw new Exception(rc.to!string);
    }

    sqlite3_reset(s);
  }

  exec("commit transaction");

  sqlite3_finalize(statement3);
  sqlite3_finalize(statement2);
  sqlite3_close(db);
}
píše k47 (@kaja47, k47)