Pilot Database Viewer

This page provides a read-only view of the pilot database. Select a table from the dropdown menu below to view the data.

db = FileAttachment("pilot_mareano.sqlite").sqlite()

// 2. Get Table Names
tables_query = db.query("SELECT name FROM sqlite_schema WHERE type='table' AND name NOT LIKE 'sqlite_%'")
table_names = tables_query.map(row => row.name)

// 3. Table Selector
viewof selected_table = Inputs.select(table_names, {
  label: "Select Table:",
  value: table_names[0]
})

// 4. Rows Per Page Selector
viewof pageSize = Inputs.select([10, 20, 50, 100], {
  label: "Rows per page:",
  value: 20
})

count_result = db.query(`SELECT COUNT(*) as total FROM "${selected_table}"`)
total_rows = count_result[0].total
max_page = Math.ceil(total_rows / pageSize)

// 6. Page Slider
viewof currentPage = Inputs.range([1, max_page], {
  label: "Page number:", 
  step: 1, 
  value: 1
})

// Summary Text
md`**Page ${currentPage}** of ${max_page} &nbsp;*(Total rows: ${total_rows})*`

offset = (currentPage - 1) * pageSize
data_paged = db.query(`SELECT * FROM "${selected_table}" LIMIT ${pageSize} OFFSET ${offset}`)

// 8. Display Table
Inputs.table(data_paged, {
  layout: "auto",
  rows: pageSize,
  format: {
    year: d3.format("d"),
    start_year: d3.format("d"),
    end_year: d3.format("d")
  }
})