end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

sql.js によるブラウザでの sqlite3 操作

web assemblyのおかげでしょうか、最近は javascriptで sqlite3 のデータベースファイルをそのまま扱えるらしい。

参考url

install sqlite3

DOS> wsl

$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.7 (Ootpa)

$ sudo yum install sqlite

$ /usr/bin/sqlite3 --version
3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e＜略＞38alt1

create ddl

-- 教師
DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers ( teacher_id   BIGINT PRIMARY KEY,
                        teacher_name VARCHAR(64) );
INSERT INTO teachers VALUES (101,'先生一郎');
INSERT INTO teachers VALUES (102,'先生二郎');
INSERT INTO teachers VALUES (103,'先生三郎');

-- 学生
DROP TABLE IF EXISTS students;
CREATE TABLE students ( student_id BIGINT PRIMARY KEY,
                        student_name VARCHAR(64) );
INSERT INTO students VALUES (301,'生徒一子');
INSERT INTO students VALUES (302,'生徒二子');
INSERT INTO students VALUES (303,'生徒三子');
INSERT INTO students VALUES (304,'生徒四子');
INSERT INTO students VALUES (305,'生徒五子');
INSERT INTO students VALUES (306,'生徒六子');
INSERT INTO students VALUES (307,'生徒七子');
INSERT INTO students VALUES (308,'生徒八子');
INSERT INTO students VALUES (309,'生徒九子');

-- 講座
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
  course_id   VARCHAR(16)  PRIMARY KEY,
  course_name VARCHAR(128) NOT NULL,
  teacher_id  BIGINT,
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) );
INSERT INTO courses VALUES (1,'国語',101);
INSERT INTO courses VALUES (2,'算数',102);
INSERT INTO courses VALUES (3,'理科',103);

-- 受講
DROP TABLE IF EXISTS student_courses;
CREATE TABLE student_courses (
  course_id  VARCHAR(16),
  student_id BIGINT,
  PRIMARY KEY (course_id, student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id));

INSERT INTO student_courses VALUES (1,301);
INSERT INTO student_courses VALUES (1,302);
INSERT INTO student_courses VALUES (1,303);
INSERT INTO student_courses VALUES (1,306);
INSERT INTO student_courses VALUES (1,307);
INSERT INTO student_courses VALUES (1,308);
INSERT INTO student_courses VALUES (1,310);
INSERT INTO student_courses VALUES (2,301);
INSERT INTO student_courses VALUES (2,309);
INSERT INTO student_courses VALUES (3,310);

create database

$ /usr/bin/sqlite3 test.sqlite3 < test_ddl.sql

test sql.js

<meta charset="utf8" />
<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/sql-wasm.js">
</script>
<script>
  async function init_sqljs() {
      const sqlPromise = initSqlJs({
          locateFile:file=>`https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/${file}`
      });
      
      const dataPromise = fetch("test.sqlite3").then(res => res.arrayBuffer());
      const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
      const db = new SQL.Database(new Uint8Array(buf));

      let sql = `
SELECT c.course_name, t.teacher_name, s.student_name
FROM student_courses sc
JOIN students s ON(sc.student_id=s.student_id)
JOIN courses  c ON(sc.course_id=c.course_id)
JOIN teachers t ON(c.teacher_id=t.teacher_id)
WHERE s.student_name=?
ORDER BY c.course_id, s.student_id`;
      const stmt = db.prepare(sql);
      stmt.bind(["生徒一子"]);
      //console.log( stmt );
      
      let sql_result = "";
      while( stmt.step() ) {
          let row = stmt.getAsObject();
          sql_result += JSON.stringify(row);
      }
      document.querySelector("#sql_result").innerHTML = sql_result;
      
      stmt.free();
      //const dbBinary = db.export();
      db.close();
  }
  
  init_sqljs();
</script>
<body>
  https://sql.js.org
  https://github.com/sql-js/sql.js
  Output is here.
  <div id="sql_result"></div>d
</body>
</html>