Export a listing

An export is the same listing in another representation. Add export: to a report or a list, and the engine serves the file from the same query.

Because it is the same query, an export inherits the page’s access rule, the region’s where:, and the reader’s row filter. A CSV file contains the rows that reader can see, and nothing else.

The file is at the page’s own address with ?mtd_export= and the region’s table: /members?mtd_export=members for a CSV, members.xlsx for a workbook, and events.ics for a calendar. The page draws these links for you. When one page exports the same table twice, give one of the two regions an export_name:.

Offer a CSV file

report of members "All members" {
  columns: last_name, first_name, email, joined_on
  sort:    last_name
  export:  csv
}

The file uses the dialect of the application’s locale:: commas and a . before the cents for en, and the semicolons German Excel opens without an import dialog for de. A ref column is expanded into its display columns, so a reader gets names rather than row numbers.

A cell that begins with =, +, -, or @ is neutralized. A spreadsheet treats such a cell as a formula, and a formula that arrived from a form is somebody else’s code running on the reader’s computer. The engine puts an apostrophe in front of such a cell. The apostrophe is visible, and that is the trade: a reader can see it, and no code runs.

Offer an Excel workbook

report of members "All members" {
  columns: last_name, first_name, fee, joined_on
  sort:    last_name
  export:  csv, xlsx
}

export: xlsx offers the same rows as an .xlsx workbook. The two words are independent. Declare both to offer both files.

The downloads are in the menu beside the report’s first button. Add export_button: true to show them under their own Export control instead. See the toolbar.

A workbook cell carries a type. A number is a number, a date is a date, and the reader’s own spreadsheet program decides how each one looks. This removes the two compromises a CSV cannot avoid:

• No dialect. A CSV must guess which separators the reader’s program expects. The engine writes the ones of the application’s locale, so a German file opens as one column of text in an English spreadsheet program, and the other way round. A workbook states the number 1234.5 and the program shows 1.234,50 € in Germany and 1,234.50 € in Ireland, from one file.
• No apostrophe. A workbook cell cannot be a formula, because a formula is a separate element and the engine writes none. A cell that begins with = arrives as those characters. The cells are exactly what the table holds.

Choose per reader, not per table. A CSV is the format a program reads: an import into another tool, a script, an accountant’s software. A workbook is the format a person opens.

The header row is bold and frozen, and every column carries a filter arrow. A datetime heading names its time zone, for the reason a CSV heading does: a cell holding 19:30 says nothing about which 19:30, and the file outlives the page it came from.

Two limits are the format’s own, not the engine’s:

• A whole number above 9,007,199,254,740,992 is written as text. A spreadsheet holds every number as a 64-bit float, so a larger one would be stored rounded and without warning. That column stops adding up, and the reader can see why.
• A cell longer than 32,767 characters is shortened, and the engine writes … at the end. Excel repairs a longer file on open, which drops content silently.

Offer a calendar feed

export: ics turns a listing into a calendar that a phone can subscribe to. Name the columns that make a row into an event.

page public_events "Events" {
  route:  /events
  access: public

  list of events "Coming up" {
    columns: name, starts_at, place
    where:   published
    sort:    starts_at
    export:  ics

    at:      starts_at
    ends:    ends_at
    place:   place
    details: description
    repeat:  recurrence
  }
}

The page shows the subscription address and a webcal: link, so a reader can add the calendar without copying a URL by hand. The address ends in ?mtd_export=events.ics, after the region’s table. It stays the same when you add a note or a region above the list, so a phone that subscribed once keeps its calendar.

Property	What it names
at	the column that starts the event
ends	the column that ends it
place	the location shown in the calendar
details	the description
repeat	the column that describes a repeating series
all_day	a bool column. A true row is a day with no time.
alarm	a bool column. A true row may remind the reader.
alarm_before	how many minutes before the event the reminder fires
url	the word page. Each entry links back to the page.
url_note	a sentence written above that link in the description.

Show an event that lasts whole days

Some events have no time. A preparation day, a holiday, a registration week: the reader must know which days, not which minute.

Declare a bool column and name it in all_day:

table events "Events" {
  display:   name
  name       text required     "Name"
  starts_at  datetime required "Starts at"
  ends_at    datetime          "Ends at"
  whole_day  bool              "All day" { default: false }
}

list of events "Coming up" {
  export:  ics
  at:      starts_at
  ends:    ends_at
  all_day: whole_day
}

A true row is sent as a date and not as an instant, and ends_at is the last day. One table can hold both kinds of event.

The day comes from the application’s time zone, not from the reader’s. A shared row must fall on one day for everybody.

If the at: column is a date rather than a datetime, every row of that feed is already an all-day event. all_day: beside such a column is a compile error.

Remind the reader before an event

A reminder needs two decisions, and the engine sends one only when both say yes.

1. The author decides which events may remind anybody. Declare a bool column and name it in alarm.
2. The reader decides how many minutes before, or that they want no reminder. Give them a column of their own and name it in alarm_before.

table subscriptions "Calendar subscriptions" {
  display: token
  token            unguessable(20) "Token"
  reminder_minutes int             "Remind me (minutes before)" {
    default: 0
    min:     0
    max:     1440
  }
}

page subscribe "My calendar" {
  route:  /calendar/{token}
  access: public

  data subscription of subscriptions

  list of events "Events in this calendar" {
    export: ics
    at:     starts_at

    alarm:        remind
    alarm_before: :subscription.reminder_minutes
  }
}

alarm_before is the only calendar property that reads a value from the page. Everything else names a column of the table the region lists. Write a plain column name instead if one lead time is right for every reader.

A lead time of 0, a negative number, or no value sends no reminder. alarm: without alarm_before: is a compile error, because a lead time nobody chose would arrive on a stranger’s phone at an hour nobody chose.

A reminder in a subscribed calendar is not honoured everywhere

The engine sends the reminder. The reader’s calendar app decides what to do with it, and the apps disagree:

Calendar app	What it does with the reminder
Apple Calendar (iPhone, iPad, Mac)	shows it
Google Calendar	ignores it and uses the reader’s own setting for that subscribed calendar
Outlook, Thunderbird	varies by version

Tell the reader this where they set the number. The sample calendar app in dsl/examples/kingshot-calendar carries a note on its subscription page.

This table is from each client’s documented behaviour. It has not been measured on devices.

Link an entry back to the page

Write url: page and every entry links back to the page its feed came from. A reader who taps an event in their calendar lands on the page they can change their settings on.

list of events "Events in this calendar" {
  export: ics
  at:     starts_at
  url:    page
}

The address is also added to the end of the description, because Apple Calendar shows the link and Google Calendar and Outlook do not.

An address on its own says nothing about what is at the other end. Write url_note: and your sentence goes on the line above it:

list of events "Events in this calendar" {
  export:   ics
  at:       starts_at
  url:      page
  url_note: "You decide which events are in this calendar. Change your settings here:"
}

The description then ends with a blank line, your sentence, and the address on a line of its own. Write the sentence in your application’s language. The engine does not write one for you, because what your page does is yours to say. url_note: needs url:.

The address carries what the page’s address carries

On a page bound by an unguessable token, url: page puts that token in every entry. A calendar entry is a thing people forward. This is why you have to write the property.

How a repeating event is handled

A series is sent as one rule that the reader’s calendar expands, and not as one entry per occurrence.

A series is sent in local time with its time zone, and not in UTC. A weekly meeting defined in UTC drifts by an hour at every daylight-saving change, which is not what “every Tuesday at 18:00” means to the person who wrote it.

That time zone is the application’s, from app { timezone: }. A reader who selects another time zone for the pages changes no byte of the feed. A subscription is one calendar for everybody who holds the link, so it cannot repeat at a different instant for each reader. See Set the time zone.

A one-off event is sent as a UTC instant.

Caching

The feed answers a poll with an ETag, and returns 304 when nothing changed. Phones poll about once an hour, so this matters more than it looks.

NOTE: A calendar address is a URL somebody’s phone holds for a long time. Bind a private feed to an unguessable column rather than to a row id, and the engine rate-limits wrong guesses per tenant and client.