AndroidDeviceInfo/server/db.js

const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');

const DB_PATH = process.env.DEVICE_DB_PATH || path.join(__dirname, 'data', 'devices.db');
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

db.exec(`
  CREATE TABLE IF NOT EXISTS phones (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    deviceName    TEXT NOT NULL,
    manufacturer  TEXT NOT NULL,
    model         TEXT NOT NULL UNIQUE,
    image         TEXT,
    count         INTEGER NOT NULL DEFAULT 1
  );

  CREATE TABLE IF NOT EXISTS phone_codenames (
    phone_id  INTEGER NOT NULL REFERENCES phones(id) ON DELETE CASCADE,
    codename  TEXT NOT NULL,
    PRIMARY KEY (phone_id, codename)
  );
  CREATE INDEX IF NOT EXISTS idx_phone_codenames_codename ON phone_codenames(codename);

  -- Per-model CMF (Color/Material/Finish) code -> real color name/asset.
  -- See docs/CMF_COLOR.md for how this code is read from a device and why
  -- there's no public API to resolve it -- this table is the only source.
  CREATE TABLE IF NOT EXISTS cmf_colors (
    model     TEXT NOT NULL,
    cmf_code  TEXT NOT NULL,
    name      TEXT NOT NULL,
    folder    TEXT,
    image     TEXT,
    PRIMARY KEY (model, cmf_code)
  );

  -- Aggregate count of how many reports for a given model carried each CMF
  -- code, so two units of the same model can be told apart by color.
  CREATE TABLE IF NOT EXISTS phone_color_reports (
    phone_id  INTEGER NOT NULL REFERENCES phones(id) ON DELETE CASCADE,
    cmf_code  TEXT NOT NULL,
    count     INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (phone_id, cmf_code)
  );

  -- Self-update feed for the bundled device-names sqlite asset the
  -- AndroidDeviceInfo library ships (see DeviceDatabase.checkForUpdates()).
  -- One row, updated whenever you publish a new device-names db.
  CREATE TABLE IF NOT EXISTS device_names_update (
    id       INTEGER PRIMARY KEY CHECK (id = 1),
    version  TEXT NOT NULL,
    db_path  TEXT NOT NULL
  );
`);

// --- Query helpers -----------------------------------------------------

const getPhoneByModel = db.prepare(`SELECT * FROM phones WHERE model = ?`);

const getCodenamesForPhone = db.prepare(
  `SELECT codename FROM phone_codenames WHERE phone_id = ?`
);

const getColorsForPhone = db.prepare(
  `SELECT cmf_code, count FROM phone_color_reports WHERE phone_id = ?`
);

const getCmfEntry = db.prepare(
  `SELECT * FROM cmf_colors WHERE model = ? AND cmf_code = ?`
);

const findByDeviceNameSubstring = db.prepare(
  `SELECT * FROM phones WHERE deviceName LIKE ? COLLATE NOCASE LIMIT 1`
);

const findByCodenameModelManufacturer = db.prepare(`
  SELECT phones.* FROM phones
  JOIN phone_codenames ON phone_codenames.phone_id = phones.id
  WHERE phone_codenames.codename = ? COLLATE NOCASE
    AND phones.model = ? COLLATE NOCASE
    AND phones.manufacturer LIKE ? COLLATE NOCASE
  LIMIT 1
`);

const insertPhone = db.prepare(`
  INSERT INTO phones (deviceName, manufacturer, model, image, count)
  VALUES (@deviceName, @manufacturer, @model, @image, 1)
`);

const incrementPhoneCount = db.prepare(
  `UPDATE phones SET count = count + 1 WHERE id = ?`
);

const insertCodename = db.prepare(`
  INSERT OR IGNORE INTO phone_codenames (phone_id, codename) VALUES (?, ?)
`);

const upsertColorReport = db.prepare(`
  INSERT INTO phone_color_reports (phone_id, cmf_code, count)
  VALUES (?, ?, 1)
  ON CONFLICT(phone_id, cmf_code) DO UPDATE SET count = count + 1
`);

const getUpdateFeed = db.prepare(
  `SELECT version, db_path FROM device_names_update WHERE id = 1`
);

/** Full API-shape phone object: base row + codenames[] + colors{code:count}. */
function hydratePhone(phone) {
  if (!phone) return null;
  const codenames = getCodenamesForPhone.all(phone.id).map(r => r.codename);
  const colors = {};
  for (const row of getColorsForPhone.all(phone.id)) colors[row.cmf_code] = row.count;
  return { ...phone, codename: codenames.length === 1 ? codenames[0] : codenames, colors };
}

/** Record (or update) a device report. Returns the hydrated phone row. */
function reportDevice({ deviceName, manufacturer, model, codename, image, cmfColor }) {
  const tx = db.transaction(() => {
    let phone = getPhoneByModel.get(model);
    if (!phone) {
      insertPhone.run({ deviceName, manufacturer, model, image: image || null });
      phone = getPhoneByModel.get(model);
    } else {
      incrementPhoneCount.run(phone.id);
    }
    if (codename) insertCodename.run(phone.id, codename);
    if (cmfColor) upsertColorReport.run(phone.id, cmfColor);
    return getPhoneByModel.get(model);
  });
  return hydratePhone(tx());
}

module.exports = {
  db,
  hydratePhone,
  reportDevice,
  findByDeviceNameSubstring,
  findByCodenameModelManufacturer,
  getCmfEntry,
  getUpdateFeed,
};