Val Town Code SearchReturn to Val Town

1import { Hono } from "https://esm.sh/hono@3.11.7";
2import { runMigrations } from "./database/migrations.ts";
3import * as db from "./database/queries.ts";
4import games from "./routes/games.ts";
5import scoring from "./routes/scoring.ts";
14});
15
16// Initialize database on startup
17await runMigrations();
18

1098            <div style="background-color: #FFFFFF; border: 1px solid #ccc; padding: 16px; border-radius: 8px; margin: 20px 0;">
1099              <p><strong>CSV Format:</strong> The file should have columns: Name, Email, Signed At</p>
1100              <p><strong>Note:</strong> This will add signatures to the existing database. Duplicates based on email will be skipped.</p>
1101            </div>
1102            

1import { Hono } from "https://esm.sh/hono@3.11.7";
2import { readFile, serveFile } from "https://esm.town/v/std/utils/index.ts";
3import * as db from "../database/queries.ts";
4
5const staticRoutes = new Hono();

1import { Hono } from "https://esm.sh/hono@3.11.7";
2import type { CreateGameRequest, ApiResponse, Game } from "../../shared/types.ts";
3import * as db from "../database/queries.ts";
4
5const games = new Hono();

1import { sqlite } from "https://esm.town/v/stevekrouse/sqlite";
2
3// Database schema for pickleball scoring app
4export async function runMigrations() {
5  console.log("Running database migrations...");
6
7  // Sites table
84  await insertDefaultData();
85  
86  console.log("Database migrations completed successfully");
87}
88

23```
24├── backend/
25│   ├── database/
26│   │   ├── migrations.ts    # Database schema
27│   │   └── queries.ts       # Database operations
28│   ├── routes/
29│   │   ├── games.ts         # Game management API

1# Hello SQLite!
2
3This project includes a [Node.js](https://nodejs.org/en/about/) server script that uses a persistent [SQLite](https://www.sqlite.org) database. The app also includes a front-end with two web pages that connect to the database using the server API. 📊
4
5The home page presents the user with a poll where they can choose an option, then the page presents the results in a chart. The admin page displays the log of past choices and allows the user to clear it by supplying an admin key (you can set this up by following the steps in `TODO.md`). 🔒
19← `.env`: The environment is cleared when you initially remix the project, but you will add a new env variable value when you follow the steps in `TODO.md` to set up an admin key.
20
21### Server and database
22
23← `server.js`: The Node.js server script for your new site. The JavaScript defines the endpoints in the site API. The API processes requests, connects to the database using the `sqlite` script in `src`, and sends info back to the client (the web pages that make up the app user interface, built using the Handlebars templates in `src/pages`).
24
25← `/src/sqlite.js`: The database script handles setting up and connecting to the SQLite database. The `server.js` API endpoints call the functions in the database script to manage the data.
26
27← `/src/data.json`: The data config file includes the database manager script–`server.js` reads the `database` property to import the correct script.
28
29When the app runs, the scripts build the database:
30
31← `.data/choices.db`: Your database is created and placed in the `.data` folder, a hidden directory whose contents aren’t copied when a project is remixed. You can see the contents of `.data` in the console by selecting __Tools__ >  __Logs__.
32
33### User interface
37← `src/pages`: The handlebars files that make up the site user interface. The API in `server.js` sends data to these templates to include in the HTML.
38
39← `src/pages/index.hbs`: The site homepage presents a form when the user first visits. When the visitor submits a preference through the form, the app calls the `POST` endpoint `/`, passing the user selection. The `server.js` endpoint updates the database and returns the user choices submitted so far, which the page presents in a chart (using [Chart.js](https://www.chartjs.org/docs/)–you can see the code in the page `head`).
40
41← `src/pages/admin.hbs`: The admin page presents a table displaying the log of most recent picks. You can clear the list by setting up your admin key (see `TODO.md`). If the user attempts to clear the list without a valid key, the page will present the log again.

1/**
2 * Module handles database management
3 *
4 * Server API calls the methods in here to query and update the SQLite database
5 */
6
7// Utilities we need
8import * as fs from "fs";
9import { Database, open } from "sqlite";
10import * as sqlite3 from "sqlite3";
11
24}
25
26interface DatabaseInterface {
27  getSubmissions(): Promise<Submission[] | null>;
28  processSubmission(name: string, email: string): Promise<Submission[] | null>;
34}
35
36// Initialize the database
37const dbFile: string = "./.data/signatures.db";
38const exists: boolean = fs.existsSync(dbFile);
39let db: Database<sqlite3.Database, sqlite3.Statement>;
40
41/*
43- https://www.npmjs.com/package/sqlite
44*/
45const initializeDatabase = async (): Promise<void> => {
46  try {
47    db = await open({
48      filename: dbFile,
49      driver: sqlite3.Database,
50    });
51
52    // We use try and catch blocks throughout to handle any database errors
53    try {
54      // The async / await syntax lets us write the db operations in a way that won't block the app
55      if (!exists) {
56        // Database doesn't exist yet - create Submissions and Log tables
57        await db.run(
58          "CREATE TABLE Submissions (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, created TEXT)",
74        await db.run(
75          "INSERT INTO Log (action, timestamp) VALUES (?, ?)",
76          ["Database initialized with sample signatures", currentTime],
77        );
78      } else {
79        // We have a database already - write Submissions records to log for info
80        const existingSubmissions: Partial<Submission>[] = await db.all("SELECT id, name, created from Submissions");
81        console.log(existingSubmissions);
85    }
86  } catch (error) {
87    console.error("Failed to initialize database:", error);
88  }
89};
90
91// Initialize the database connection
92initializeDatabase();
93
94// Our server script will call these methods to connect to the db
95const databaseModule: DatabaseInterface = {
96  /**
97   * Get the submissions in the database
98   *
99   * Return everything in the Submissions table
106      return submissions;
107    } catch (dbError) {
108      // Database connection error
109      console.error(dbError);
110      return null;
149   *
150   * Receive the signature ID from server
151   * Remove the signature from the database
152   * Add a log entry
153   * Return success/failure flag
267};
268
269export = databaseModule;

1{
2  "database": "sqlite.js",
3  "errorMessage": "Whoops! Error connecting to the database–please try again!",
4  "setupMessage": "🚧 Whoops! Looks like the database isn't setup yet! 🚧"
5}

1/**
2 * This is the main server script that provides the API endpoints
3 * The script uses the database helper in /src
4 * The endpoints retrieve, update, and return data to the page handlebars files
5 *
26}
27
28interface DatabaseConfig {
29  database: string;
30  errorMessage: string;
31  setupMessage: string;
45}
46
47interface DatabaseInterface {
48  getSubmissions(): Promise<Submission[] | false>;
49  processSubmission(name: string, email: string): Promise<Submission[] | false>;
114}
115
116// We use a module for handling database operations in /src
117const data: DatabaseConfig = require("./src/data.json");
118const db: DatabaseInterface = require("./src/" + data.database);
119
120/**
121 * Home route for the app
122 *
123 * Return the form submissions from the database helper script
124 * The home route may be called on remix in which case the db needs setup
125 *
133  let params: PageParams = request.query.raw ? {} : { seo: seo };
134
135  // Get the available submissions from the database
136  const submissions: Submission[] | false = await db.getSubmissions();
137  if (submissions) {
156 *
157 * Retrieve form data from body
158 * Send data to database helper
159 * Return updated list of submissions
160 */
176      params.error = "Please complete the captcha";
177
178      // Get the available submissions from the database for display on the form page
179      submissions = await db.getSubmissions();
180      if (submissions) {
201      params.error = "Captcha verification failed. Please try again.";
202
203      // Get the available submissions from the database for display on the form page
204      submissions = await db.getSubmissions();
205      if (submissions) {
214    // Captcha verified! Continue with the form processing
215    if (request.body.name && request.body.email) {
216      // Process the submission with the database helper
217      submissions = await db.processSubmission(request.body.name, request.body.email);
218      if (submissions) {
224      params.error = "Please provide both name and email.";
225
226      // Get the available submissions from the database for display on the form page
227      submissions = await db.getSubmissions();
228      if (submissions) {
235    params.results = false;
236
237    // Get the available submissions from the database for display on the form page
238    submissions = await db.getSubmissions();
239    if (submissions) {