Philips Hue Bridge lights and scenes with Alopex DB

What the service does

The Philips Hue Bridge is the heart of the Hue lighting and security system. It connects lights, cameras and sensors, and talks to devices over Zigbee and Bluetooth. It also works with Alexa, Google Home, Apple Home, IFTTT and more. This page rebuilds the lights and scenes that the bridge holds.

Where the data lives

Alopex DB SQL tables: lights, rooms, scenes and sensors live in SQL tables. A scene is a set of rows with brightness and color per light.
SQL: switching all the lights in a room is a SQL query that joins rooms and scenes.
KV: the current state of each light is read and written quickly as KV keyed by light ID.

Sending commands to the lights is the job of the bridge software.

Deployment

Alopex DB is embedded in the bridge software. The light and scene data stays on the bridge in the home, so the lights keep working when the internet drops.

Why Alopex DB fits

Smart lighting data is the relationship between lights, rooms and scenes. Alopex DB keeps those relationships in SQL tables and queries them while embedded in the bridge. On the cloud side, where many homes come together, the same engine runs as a server or cluster.

See it in code

Lights, rooms and scenes live in tables, and applying a scene to a room is a join between rooms and scenes. Just the core.

import alopex

db = alopex.Database.open("hue.alopex")

# 照明・部屋・シーン（照明ごとの明るさと色）を持つ
db.execute_sql("CREATE TABLE IF NOT EXISTS lights (id INTEGER PRIMARY KEY, room TEXT, name TEXT)")
db.execute_sql("CREATE TABLE IF NOT EXISTS scenes (id INTEGER PRIMARY KEY, name TEXT)")
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS scene_lights (
        scene_id INTEGER,
        light_id INTEGER,
        brightness INTEGER,
        color TEXT
    )
""")

for lid, room, name in [(1, "居間", "天井"), (2, "居間", "スタンド"), (3, "寝室", "枕元")]:
    db.execute_sql("INSERT INTO lights (id, room, name) VALUES (?, ?, ?)", [lid, room, name])
db.execute_sql("INSERT INTO scenes (id, name) VALUES (?, ?)", [1, "映画"])
for light_id, brightness, color in [(1, 10, "warm"), (2, 30, "warm")]:
    db.execute_sql("INSERT INTO scene_lights (scene_id, light_id, brightness, color) VALUES (?, ?, ?, ?)", [1, light_id, brightness, color])

# 「居間」に「映画」シーンを当てる＝部屋の照明ごとの設定をSQLで引く
rows = db.execute_sql("""
    SELECT l.name, sl.brightness, sl.color
    FROM scene_lights AS sl
    JOIN lights AS l ON l.id = sl.light_id
    WHERE sl.scene_id = ? AND l.room = ?
    ORDER BY l.id
""", [1, "居間"])

for row in rows:
    print(row["name"], row["brightness"], row["color"])

Joining rooms and scenes pulls every light’s setting at once; the bridge turns that into commands to the lights.

Embed Alopex DB, which handles the relationship between lights and scenes in SQL, in your hub software.