Database Manual · Chapter 3

Table Schema — `order_history`

The sample handles all demos with a single table order_history. The domain is "cooking / order processing history" — one row records the order start/end timestamps, weight (g) and result ("Done" · "NG" · "Pending") of one drink.

CREATE TABLE

When the sample project first runs, the SQLite file XDatabase/LocalDB.db is auto-created, but the order_history table must already exist. Run the following DDL once in DB Studio or any external SQLite tool (DBeaver, sqlite3 CLI, …).

CREATE TABLE IF NOT EXISTS order_history (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  order_no    TEXT    NOT NULL,
  menu_name   TEXT    NOT NULL,
  start_time  TEXT,
  end_time    TEXT,
  weight_g    REAL    DEFAULT 0,
  result      TEXT    DEFAULT 'Pending',
  is_error    INTEGER DEFAULT 0
);

Column Meaning

Column	Type	Meaning
`id`	INTEGER PK	Auto-incremented identifier. The `WHERE` key for UPDATE / DELETE
`order_no`	TEXT	Order number (`O1001`, `O1002`, …). `Data::OrderSeq` auto-increments
`menu_name`	TEXT	Menu name. Sample cycles through 7 — Americano · Latte · Cappuccino · Espresso · Mocha · Green Tea · Lemonade
`start_time`	TEXT	Order start time. `SYS.DateTimeString`
`end_time`	TEXT	Order end time. Set to `SYS.DateTimeString` when Update is pressed
`weight_g`	REAL	Extraction weight (g). Hard-coded 250 on Update for demo purposes
`result`	TEXT	`"Pending"` → `"Done"` / `"NG"`
`is_error`	INTEGER	0/1 (false/true). Kept as a separate column to distinguish error flag from result string

SQLite has a dynamic type system, so specifying INTEGER / TEXT / REAL is enough. When porting to MSSQL, restate with stricter types like NVARCHAR, DATETIME2, DECIMAL(10,3).

Empty Table Auto-fills 5 Rows

Data::DB_Open() checks the row count right after connecting, and if zero, calls DB_InsertInitialSamples() to bulk-INSERT 5 learning rows in a transaction.

// Data.xms excerpt
int rowCnt = DB["local"].RunSqlScalarInt("SELECT COUNT(*) FROM order_history");
if( rowCnt == 0 )
{
   Log($"order_history is empty, insert initial 5 samples");
   DB_InsertInitialSamples();
}

The pool data of DB_InsertInitialSamples:

order_no	menu_name	weight_g	result	is_error
O1001	Americano	250	Done	0
O1002	Latte	300	Done	0
O1003	Cappuccino	280	Done	0
O1004	Espresso	30	NG	1
O1005	Mocha	320	Done	0

So just create the table — one Open is enough to set up demo data.

After Schema Changes

If you add/remove columns, also update the DB_Refresh SELECT column list (Ch. 7) and the DataGrid's Columns definition.
The WHERE id = ? pattern is scattered around the script, so keeping the PK named id is safest.

Next Chapter

With the schema in place, we'll verify data in DB Studio's SQL · Data tabs before writing any script.