PackDB Documentation

Syntax Overview

PackDB adds a DECIDE clause to standard SQL. You declare decision variables, define constraints with SUCH THAT, and optionally set an objective with MAXIMIZE or MINIMIZE. PackDB solves the optimization problem and returns every candidate row with the optimal variable values filled in.

Complete Syntax

SELECT select_list
FROM table_expression
[WHERE filter_conditions]
DECIDE variable [IS type] [, variable2 [IS type] ...]
SUCH THAT
    constraint [AND constraint ...]
[MAXIMIZE | MINIMIZE] SUM(linear_expression)

Execution Order

PackDB processes your query in this order:

1. 1
   FROM + WHERE — Standard SQL filtering produces candidate rows
2. 2
   DECIDE — Creates one decision variable per candidate row
3. 3
   SUCH THAT + Objective — Constraints and objective are sent to the solver
4. 4
   SELECT — Returns all candidate rows with their optimal variable values

Decision variables are available in SUCH THAT, MAXIMIZE/MINIMIZE, and SELECT.

The DECIDE Clause

Use DECIDE to declare one or more decision variables. Each candidate row gets its own instance of each variable. The solver assigns optimal values to these variables.

DECIDE variable_name [IS type] [, variable_name2 [IS type] ...]

Variable Types

Specify the variable type with IS BOOLEAN or IS INTEGER. If you omit the type, the default is INTEGER.

DECIDE x IS BOOLEAN

DECIDE quantity IS INTEGER

Multiple Variables

Declare multiple variables separated by commas. Each row gets its own instance of every variable.

-- Two variables per row: include or not, and how many
DECIDE x IS BOOLEAN, quantity IS INTEGER

Using Variables in SELECT

Reference decision variables in SELECT to see their optimal values in the output. You can also use them in expressions.

SELECT id, price, x, (x * price) AS cost
FROM Products
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * price) <= 1000
MAXIMIZE SUM(x * profit);

This returns every product row. The x column shows 1 for selected products and 0 for skipped ones. The cost column shows the price for selected items and 0 otherwise.

The SUCH THAT Clause

Use SUCH THAT to define constraints that the solution must satisfy. Constraints are separated by AND. All constraints must be linear in decision variables.

Row-Level Constraints

A constraint without SUM() applies independently to each row. Use this to bound individual variable values.

SUCH THAT
    x >= 0
    AND x <= 5

Each row's x is independently constrained between 0 and 5.

Aggregate Constraints

Wrap an expression in SUM() to constrain the total across all rows. This is how you express global limits like total weight or budget.

SUCH THAT
    x IS BOOLEAN
    AND SUM(x * weight) <= 100
    AND SUM(x * volume) <= 50
    AND SUM(x) >= 5

The total weight of selected items must be at most 100, total volume at most 50, and at least 5 items must be selected.

Comparison Operators

BETWEEN and IN

Use BETWEEN to constrain a variable to a range, or IN to restrict it to specific values.

-- x must be between 1 and 10 (inclusive)
SUCH THAT x BETWEEN 1 AND 10

-- x must be one of these values
SUCH THAT x IN (1, 2, 3)

Scalar Subqueries

Use a subquery to compute a constraint bound from another table. The subquery must return a single value and cannot reference columns from the outer query.

SUCH THAT
    SUM(x) <= (SELECT COUNT(*) FROM Drivers)

Linearity Requirement

Every constraint (and the objective) must be linear in decision variables. A term is linear if it multiplies a variable by a constant or a table column — never by another variable.

x * 5
x * price
SUM(x * price + y * cost)

x * x
x * y
SIN(x)

Full Example

SELECT id, value, weight, x
FROM Items
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * weight) <= 100
    AND SUM(x * volume) <= 50
    AND SUM(x) >= 5
    AND SUM(x) <= (SELECT capacity FROM Config WHERE name = 'max_items')
MAXIMIZE SUM(x * value);

Select at least 5 items without exceeding weight, volume, or capacity limits. Maximize total value.

The Objective Clause

Use MAXIMIZE or MINIMIZE followed by a SUM() expression to tell the solver what to optimize. The objective must be linear and must involve at least one decision variable.

-- Maximize total value of selected items
MAXIMIZE SUM(x * value)

-- Minimize total cost
MINIMIZE SUM(x * cost)

-- Complex coefficients work too
MAXIMIZE SUM(x * (revenue - cost) * discount_factor)

-- Multiple variables in one objective
MAXIMIZE SUM(x * profit_a + y * profit_b)

Requirements

• • The objective must use SUM(). Other aggregates (COUNT, AVG, MAX, MIN) are not supported.
• • Must involve at least one decision variable.
• • Must be linear (same rules as constraints).
• • Constant terms are ignored (they don't affect which solution is optimal).

COUNT Workaround

To maximize the number of selected items, use SUM(x) with a boolean variable instead of COUNT.

-- Want to select as many items as possible?
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 100
MAXIMIZE SUM(x);

Since x is 0 or 1, SUM(x) counts how many rows are selected.

Feasibility Queries (No Objective)

You can omit the objective entirely. In that case, the solver finds any feasible assignment that satisfies all constraints.

SELECT id, x
FROM Items
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * weight) <= 50
    AND SUM(x) = 3;

Returns any combination of exactly 3 items whose total weight is at most 50.

Solver Outcomes

After processing, the solver reports one of three outcomes:

Best Practices

SELECT * FROM Products
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 50
MAXIMIZE SUM(x * value);

SELECT * FROM Products
WHERE category = 'electronics'
  AND in_stock = true
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * weight) <= 50
MAXIMIZE SUM(x * value);

SUCH THAT
    x IS BOOLEAN
    AND SUM(x * weight) <= 50  -- try increasing this
    AND SUM(x) >= 10           -- or lowering this

SELECT o.id, p.name, x
FROM Orders o
JOIN Products p ON o.product_id = p.id
DECIDE x IS BOOLEAN
SUCH THAT SUM(x * p.weight) <= 100
MAXIMIZE SUM(x * p.profit);