lazysite

Build a listing from a data table

Keep a product list, an events calendar or a directory as records the site holds, and show them on any page from a line of front matter.

Many sites carry a list that changes more often than the pages around it: products, events, a staff directory, member entries. Typed into a page by hand, every change is a page edit and the list's layout drifts from row to row. Moved to a separate database, the site gains a server, a schema kept somewhere else, and code to join the two.

There is a middle path: the site holds the records itself, in a shape it has been told about, and a page reads them like any other variable.

The general approach

  1. Describe the shape once - the fields, their types, and which field identifies a row.
  2. Keep the records in a store the site owns, checked against that shape on every write.
  3. Bind a page to a query - "the first twenty, ordered by name" - and let the template loop over the rows.
  4. Collect new rows through a form, with the same spam and rate controls as any other form.

How lazysite does it

Switch it on

Data tables ship disabled. An operator enables Data tables on the manager's Extensions page, and the manager then shows a Data tables page under Setup. Working with tables through the manager, the control API or MCP needs the manage_data capability, granted through a group.

Declare the table, then create it

A table is described in a small YAML file:

title: Products
key: code
fields:
  code:
    type: text
    required: true
  name:
    type: text
  price:
    type: decimal
    digits: 8
    places: 2

The field types are text, integer, decimal, boolean, date, datetime and enum. The descriptor is saved through save_data_table (MCP) or data-table-save (control API), which checks it before storing it and names the field and the reason when it refuses one. A migration (migrate_data_table) then creates the stored table, and plan_data_migration shows what a migration would do before anything changes. Rows go in with save_data_row, and each one is checked against its declared type: a decimal with too many places is refused, never rounded.

Put the rows on a page

Front matter binds a page variable to a query:

---
title: Our products
tt_page_var:
  products: db:products(order=name,limit=20)
  total:    db:products.count()
---

and the page loops over the rows:

[% total %] products
[% FOREACH p IN products %]<li>[% p.name %] - [% p.price %]</li>[% END %]

A binding can filter on any declared field (conditions are AND-combined), order ascending or descending, return a count, or return one value from one row. limit= is capped at 500, and a binding with no limit= returns up to 200; every list binding also carries a <name>_total variable with the true count, so a page can say "showing 200 of 250". Every value is HTML-escaped as it reaches the page, and a value used as a whole link target goes through the url_safe filter. How fresh the page is follows its own ttl:.

A table starts closed to anonymous visitors: until its descriptor says public: true, a visitor sees no rows from it on any page. Signed-in accounts may read it unless a read list narrows it. A where: line on the table (for example where: status != spam) holds rows back from visitors while the manager still shows every row.

Let visitors add rows through a form

Visitors write to a table through a form, which brings rate limits, spam assessment, quarantine and an audit trail. An account holding manage_data creates a table handler on the Handlers page, mapping each form field to a column:

handlers:
  - id: store-enquiries
    type: table
    name: Enquiries
    table: enquiries
    fields: name=name,email=email,message=body

Binding a form to that handler then needs only manage_forms. A form field the mapping leaves out is dropped, and a submission whose values the table's types refuse tells the visitor it failed. A submission copy is kept for the Submissions page by default (keep_copy). Who may read the stored rows is still the table's own public: setting.

Who may write

  • manage_data writes every table, except one whose writable_by names groups the account is outside of.
  • write_data is the grant for an app's own users: it inserts, updates and deletes rows only in tables whose writable_by names one of its groups.
  • row_policy: true (with timestamps: true) lets each row belong to the account that wrote it, so a member amends their own entry and nobody else's. A binding written db:applications(mine) shows each signed-in viewer their own rows.

Removing a table, or rebuilding one in a way that loses a column, writes a safety export of its rows first.