simple collision box and sphere 3d with sql javascript test

threesql.js

// sqlite wasm
// <script type="importmap">
//   {
//     "imports":{
//       "three":"https://cdn.jsdelivr.net/npm/three@0.183.2/+esm"
//     }
//   }
// </script>

import * as THREE from 'https://cdn.jsdelivr.net/npm/three@0.183.2/+esm';
import { OrbitControls } from 'https://cdn.jsdelivr.net/npm/three@0.183.2/examples/jsm/controls/OrbitControls.js';
import van from "https://cdn.jsdelivr.net/gh/vanjs-org/van/public/van-1.6.0.min.js"
import sqlite3InitModule from 'https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm@3.51.2-build8/+esm';
import { Pane } from 'https://cdn.jsdelivr.net/npm/tweakpane@4.0.5/dist/tweakpane.min.js';

const log = console.log;
const error = console.error;
var sqlite3;
var db;

var scene = null;
var camera = null;
var renderer = null;
var controls = null;

const PARAMS = {
  speed: 0.5,
  position:{x:0,y:0,z:0}
};

var player_pos;
const keys = {};
const meshes = new Map();

const initializeSQLite = async () => {
  try {
      log('Loading and initializing SQLite3 module...');
      sqlite3 = await sqlite3InitModule({
          locateFile: (filename, scriptDirectory) => {
          // Most common cases:
          if (filename.endsWith('.wasm')) {
            // return 'https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm@3.51.2-build8/sqlite3.wasm';  // ← your custom path
            return 'https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm@3.51.2-build8/dist/sqlite3.wasm';  // ← your custom path
            // or relative: return '/assets/sqlite3.wasm';
          }
          // fallback — important for other files (e.g. .js if any)
          return scriptDirectory + filename;
        },
        // Optional: useful for debugging
        print: console.log,
        printErr: console.error
      });
      log('Done initializing. Running demo...');
      start(sqlite3);
    } catch (err) {
      console.error('Initialization error:', err.name, err.message);
    }
};

const start = (sqlite3) => {
  log('Running SQLite3 version', sqlite3.version.libVersion);
  db =
    'opfs' in sqlite3
      ? new sqlite3.oo1.OpfsDb('/mydb.sqlite3')
      : new sqlite3.oo1.DB('/mydb.sqlite3', 'ct');
  log(
    'opfs' in sqlite3
      ? `OPFS is available, created persisted database at ${db.filename}`
      : `OPFS is not available, created transient database ${db.filename}`,
  );
  // Your SQLite code here.
  // console.log(db);
  runDB(db);
};

function runDB(db){

  // Setup schema & data (exec supports multi-statement strings)
db.exec(`
  CREATE TABLE IF NOT EXISTS entities (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    x   REAL NOT NULL DEFAULT 0,
    y   REAL NOT NULL DEFAULT 0,
    z   REAL NOT NULL DEFAULT 0,
    vx  REAL NOT NULL DEFAULT 0,
    vy  REAL NOT NULL DEFAULT 0,
    vz  REAL NOT NULL DEFAULT 0,
    ax  REAL NOT NULL DEFAULT 0,
    ay  REAL NOT NULL DEFAULT 0,
    az  REAL NOT NULL DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS shapes_sphere (
    entity_id INTEGER PRIMARY KEY REFERENCES entities(id) ON DELETE CASCADE,
    radius    REAL NOT NULL DEFAULT 1.0
  );

  CREATE TABLE IF NOT EXISTS shapes_box (
    entity_id INTEGER PRIMARY KEY REFERENCES entities(id) ON DELETE CASCADE,
    size_x    REAL NOT NULL DEFAULT 1.0,
    size_y    REAL NOT NULL DEFAULT 1.0,
    size_z    REAL NOT NULL DEFAULT 1.0
  );

  -- Sample data
  INSERT OR IGNORE INTO entities (id, type, x, y, z) VALUES
    (1, 'player',   0, 0, 0),
    (2, 'obstacle', 5, 0, 0),
    (3, 'obstacle',-4, 0, 3);

  INSERT OR IGNORE INTO shapes_sphere (entity_id, radius) VALUES
    (1, 1.2),   -- player
    (2, 2.0);   -- sphere obstacle

  INSERT OR IGNORE INTO shapes_box (entity_id, size_x, size_y, size_z) VALUES
    (3, 3.0, 1.5, 1.5);  -- box obstacle
`);

  setup_init();
  setup_three();
  syncScene();
  animate();
  setup_tweakpane();
}

function getEntitiesWithShape() {
  const entities = [];

  // Spheres
  db.exec({
    sql: `
      SELECT e.id, e.type, e.x, e.y, e.z, e.vx, e.vy, e.vz,
             s.radius
      FROM entities e
      JOIN shapes_sphere s ON s.entity_id = e.id`,
    rowMode: 'object',
    callback: row => {
      row.shapeType = 'sphere';
      entities.push(row);
    }
  });

  // Boxes
  db.exec({
    sql: `
      SELECT e.id, e.type, e.x, e.y, e.z, e.vx, e.vy, e.vz,
             b.size_x, b.size_y, b.size_z
      FROM entities e
      JOIN shapes_box b ON b.entity_id = e.id`,
    rowMode: 'object',
    callback: row => {
      row.shapeType = 'box';
      entities.push(row);
    }
  });

  return entities;
}

function setup_init(){
  
  addEventListener('keydown', e => keys[e.key.toLowerCase()] = true);
  addEventListener('keyup',   e => keys[e.key.toLowerCase()] = false);

  // Optional: log version
  console.log('SQLite version:', sqlite3.version.libVersion);
}

function setup_three(){
  // ────────────────────────────────────────
  // Three.js setup (sync from DB)
  // ────────────────────────────────────────
  // console.log("threejs");
  scene = new THREE.Scene();
  scene.background = new THREE.Color(0x222233);

  camera = new THREE.PerspectiveCamera(75, innerWidth / innerHeight, 0.1, 1000);
  camera.position.set(0, 8, 12);
  camera.lookAt(0, 0, 0);

  renderer = new THREE.WebGLRenderer({ antialias: true });
  
  const dirLight = new THREE.DirectionalLight(0xffffff, 1.2);
  dirLight.position.set(10, 15, 7);
  scene.add(dirLight, new THREE.AmbientLight(0x404060));

  const size = 10;
  const divisions = 10;
  const gridHelper = new THREE.GridHelper( size, divisions );
  scene.add( gridHelper );

  renderer.setSize(innerWidth, innerHeight);
  controls = new OrbitControls( camera, renderer.domElement );
  van.add(document.body, renderer.domElement);
  window.addEventListener('resize', resizeWindow)
}

function resizeWindow(event){
  const newWidth = window.innerWidth;
  const newHeight = window.innerHeight;
  // Update the camera's aspect ratio
  camera.aspect = newWidth / newHeight;
  // Call updateProjectionMatrix() to apply the camera changes
  camera.updateProjectionMatrix();
  // Update the renderer's size
  renderer.setSize(innerWidth, innerHeight);
}

function animate(){
  const speed = 0.12;
  let moved = false;
  
  if (keys['w']) { tryMoveEntity(1,  0, 0, -speed); moved = true; }
  if (keys['s']) { tryMoveEntity(1,  0, 0,  speed); moved = true; }
  if (keys['a']) { tryMoveEntity(1, -speed, 0, 0); moved = true; }
  if (keys['d']) { tryMoveEntity(1,  speed, 0, 0); moved = true; }

  if (moved) syncScene();

  controls.update();
  renderer.render(scene, camera);
  requestAnimationFrame(animate);
}

function syncScene() {
  const entities = getEntitiesWithShape();

  // Cleanup deleted meshes
  for (const [idStr, mesh] of meshes) {
    if (!entities.some(e => e.id === +idStr)) {
      scene.remove(mesh);
      meshes.delete(idStr);
    }
  }

  for (const e of entities) {
    let mesh = meshes.get(String(e.id));

    if (!mesh) {
      let geo;
      if (e.shapeType === 'sphere') {
        geo = new THREE.SphereGeometry(e.radius || 1, 16, 12);
      } else if (e.shapeType === 'box') {
        geo = new THREE.BoxGeometry(
          e.size_x || 1,
          e.size_y || 1,
          e.size_z || 1
        );
      } else {
        geo = new THREE.SphereGeometry(1, 16, 12); // fallback
      }

      const color = e.type === 'player' ? 0x44ff44 : 0xff4444;
      mesh = new THREE.Mesh(geo, new THREE.MeshStandardMaterial({ color }));
      scene.add(mesh);
      meshes.set(String(e.id), mesh);
    }

    mesh.position.set(e.x, e.y, e.z);
  }
}

// Helper: get all entities as array of plain objects
function getEntities() {
  const rows = [];
  db.exec({
    sql: 'SELECT * FROM entities',
    rowMode: 'object',
    callback: row => rows.push(row)
  });
  return rows;
}

function wouldCollide(movingId, newX, newY, newZ) {
  // Get self entity with its shape
  const self = db.selectObject(`
    SELECT 
      e.x, e.y, e.z,
      s.radius,
      b.size_x, b.size_y, b.size_z
    FROM entities e
    LEFT JOIN shapes_sphere s ON s.entity_id = e.id
    LEFT JOIN shapes_box   b ON b.entity_id = e.id
    WHERE e.id = ?
  `, [movingId]);

  if (!self) {
    console.warn(`Entity ${movingId} not found in wouldCollide`);
    return false;
  }

  const selfPos = new THREE.Vector3(newX, newY, newZ);
  const selfIsSphere = self.radius !== null;
  const selfIsBox    = self.size_x !== null;

  // Get all other entities with their shapes
  const others = [];
  db.exec({
    sql: `
      SELECT 
        e.id,
        e.x, e.y, e.z,
        s.radius,
        b.size_x, b.size_y, b.size_z
      FROM entities e
      LEFT JOIN shapes_sphere s ON s.entity_id = e.id
      LEFT JOIN shapes_box   b ON b.entity_id = e.id
      WHERE e.id != ?`,
    bind: [movingId],
    rowMode: 'object',
    callback: row => others.push(row)
  });

  for (const other of others) {
    const otherPos = new THREE.Vector3(other.x, other.y, other.z);
    const otherIsSphere = other.radius !== null;
    const otherIsBox    = other.size_x !== null;

    let collides = false;

    if (selfIsSphere && otherIsSphere) {
      // Sphere vs Sphere
      const dist = selfPos.distanceTo(otherPos);
      const sumRadius = (self.radius || 1) + (other.radius || 1);
      collides = dist < sumRadius * 0.999;

    } else if (selfIsSphere && otherIsBox) {
      // Sphere vs Box (accurate enough for most games)
      const boxMin = new THREE.Vector3(
        other.x - other.size_x / 2,
        other.y - other.size_y / 2,
        other.z - other.size_z / 2
      );
      const boxMax = new THREE.Vector3(
        other.x + other.size_x / 2,
        other.y + other.size_y / 2,
        other.z + other.size_z / 2
      );

      // Closest point on box to sphere center
      const closest = new THREE.Vector3(
        Math.max(boxMin.x, Math.min(selfPos.x, boxMax.x)),
        Math.max(boxMin.y, Math.min(selfPos.y, boxMax.y)),
        Math.max(boxMin.z, Math.min(selfPos.z, boxMax.z))
      );

      const distSq = selfPos.distanceToSquared(closest);
      const rSq = (self.radius || 1) ** 2;
      collides = distSq < rSq * 0.999;

    } else if (selfIsBox && otherIsSphere) {
      // Box vs Sphere → just swap roles (same logic)
      const boxMin = new THREE.Vector3(
        selfPos.x - self.size_x / 2,
        selfPos.y - self.size_y / 2,
        selfPos.z - self.size_z / 2
      );
      const boxMax = new THREE.Vector3(
        selfPos.x + self.size_x / 2,
        selfPos.y + self.size_y / 2,
        selfPos.z + self.size_z / 2
      );

      const closest = new THREE.Vector3(
        Math.max(boxMin.x, Math.min(otherPos.x, boxMax.x)),
        Math.max(boxMin.y, Math.min(otherPos.y, boxMax.y)),
        Math.max(boxMin.z, Math.min(otherPos.z, boxMax.z))
      );

      const distSq = otherPos.distanceToSquared(closest);
      const rSq = (other.radius || 1) ** 2;
      collides = distSq < rSq * 0.999;

    } else if (selfIsBox && otherIsBox) {
      // Box vs Box (simple AABB intersection)
      const selfMin = new THREE.Vector3(
        selfPos.x - self.size_x / 2,
        selfPos.y - self.size_y / 2,
        selfPos.z - self.size_z / 2
      );
      const selfMax = new THREE.Vector3(
        selfPos.x + self.size_x / 2,
        selfPos.y + self.size_y / 2,
        selfPos.z + self.size_z / 2
      );

      const otherMin = new THREE.Vector3(
        other.x - other.size_x / 2,
        other.y - other.size_y / 2,
        other.z - other.size_z / 2
      );
      const otherMax = new THREE.Vector3(
        other.x + other.size_x / 2,
        other.y + other.size_y / 2,
        other.z + other.size_z / 2
      );

      collides = !(
        selfMax.x < otherMin.x || selfMin.x > otherMax.x ||
        selfMax.y < otherMin.y || selfMin.y > otherMax.y ||
        selfMax.z < otherMin.z || selfMin.z > otherMax.z
      );
    }

    if (collides) {
      console.log(`Collision predicted! id=${movingId} vs id=${other.id}`);
      return true;
    }
  }

  return false;
}

function hitMaterial(){
  const playerMesh = meshes.get("1");
  if (playerMesh) {
    playerMesh.material.color.set(0xff0000);
    setTimeout(() => playerMesh.material.color.set(0x44ff44), 200);
  }
}

function tryMoveEntity(id, dx, dy, dz) {
  // Fetch current position + shape info using LEFT JOIN
  const row = db.selectObject(`
    SELECT 
      e.x, e.y, e.z,
      s.radius,
      b.size_x, b.size_y, b.size_z
    FROM entities e
    LEFT JOIN shapes_sphere s ON s.entity_id = e.id
    LEFT JOIN shapes_box b ON b.entity_id = e.id
    WHERE e.id = ?
  `, [id]);

  if (row === undefined || row === null) {
    console.log(`Entity id=${id} not found`);
    return false;
  }

  const { x, y, z, radius, size_x, size_y, size_z } = row;

  // Determine shape type
  const isSphere = radius !== null;
  const isBox    = size_x !== null;

  console.log(`Moving entity ${id} (${isSphere ? 'sphere' : isBox ? 'box' : 'unknown'})`, { x, y, z });

  let newX = x + dx;
  let newY = y + dy;
  let newZ = z + dz;

  // Collision check with new position
  if (wouldCollide(id, newX, newY, newZ)) {
    // Sliding logic: try moving only on XZ plane, then only on X, etc.
    if (!wouldCollide(id, newX, y, newZ)) {
      newY = y;           // allow X+Z movement
      hitMaterial();
    } 
    else if (!wouldCollide(id, x, newY, newZ)) {
      newX = x;           // allow Y+Z movement (rare in top-down)
      hitMaterial();
    } 
    else {
      console.log("Movement fully blocked");
      return false;       // completely blocked
    }
  }

  // Apply the (possibly adjusted) movement
  db.exec({
    sql: 'UPDATE entities SET x=?, y=?, z=? WHERE id=?',
    bind: [newX, newY, newZ, id]
  });

  // Update tweakpane display
  PARAMS.position.x = newX;
  PARAMS.position.y = newY;
  PARAMS.position.z = newZ;
  if (player_pos) player_pos.refresh();

  return true;
}

function setup_tweakpane(){
  const pane = new Pane();
  player_pos = pane.addBinding(PARAMS, 'position',{
    // readonly: true,
    // multiline: true,
    // bufferSize: 10,
    // interval: 1000,
  });
}

initializeSQLite();