📎 Link Manager

\n \n\n {isSettingsOpen && (\n

SQL Query Settings

setSqlQuery(e.target.value)}\n                rows={4}\n                style={{width: '100%', marginBottom: '10px'}}\n                placeholder=\"Enter your SQL query (defaults to SELECT * FROM links)\"\n              />\n              <button type=\"submit\">Execute Query</button>\n              <button \n                type=\"button\" \n                onClick={() => setIsSettingsOpen(false)}\n                style={{marginLeft: '10px'}}\n              >\n                Close\n              </button>\n            </form>\n\n            {queryError && (\n              <div style={{color: 'red', marginTop: '10px'}}>\n                Error: {queryError}\n              </div>\n            )}\n\n            {queryResults && queryResults.length > 0 && (\n              <div>\n                <h3>Query Results:</h3>\n                <table style={{width: '100%', borderCollapse: 'collapse'}}>\n                  <thead>\n                    <tr>\n                      {Object.keys(queryResults[0] || {}).map(key => (\n                        <th key={key} style={{border: '1px solid #ddd', padding: '8px'}}>\n                          {key}\n                        </th>\n                      ))}\n                    </tr>\n                  </thead>\n                  <tbody>\n                    {queryResults.map((row, index) => (\n                      <tr key={index}>\n                        {Object.values(row).map((value, cellIndex) => (\n                          <td \n                            key={cellIndex} \n                            style={{\n                              border: '1px solid #ddd', \n                              padding: '8px',\n                              maxWidth: '200px',\n                              overflow: 'hidden',\n                              textOverflow: 'ellipsis'\n                            }}\n                          >\n                            {typeof value === 'object' \n                              ? JSON.stringify(value) \n                              : String(value)}\n                          </td>\n                        ))}\n                      </tr>\n                    ))}\n                  </tbody>\n                </table>\n              </div>\n            )}\n          </div>\n        </div>\n      )}\n\n      <form onSubmit={handleSubmit}>\n        <input \n          type=\"text\" \n          placeholder=\"Title\" \n          value={newLink.title || ''} \n          onChange={e => setNewLink({...newLink, title: e.target.value})} \n        />\n        <input \n          type=\"url\" \n          placeholder=\"URL\" \n          value={newLink.href || ''} \n          onChange={e => setNewLink({...newLink, href: e.target.value})} \n        />\n        <button type=\"submit\">Add Link</button>\n      </form>\n      <ul>\n        {links.map(link => (\n          <li key={link.uuid}>\n            <a href={link.href} target={link.target}>{link.title}</a>\n            <span>{link.tags.join(', ')}</span>\n          </li>\n        ))}\n      </ul>\n      <a \n        href={import.meta.url.replace(\"esm.town\", \"val.town\")} \n        target=\"_top\" \n        style={{color: '#888', fontSize: '0.8em'}}\n      >\n        View Source\n      </a>\n    </div>\n  );\n}\n\nfunction client() {\n  createRoot(document.getElementById(\"root\")).render(<LinkManager />);\n}\nif (typeof document !== \"undefined\") { client(); }\n\nexport default async function server(request: Request): Promise<Response> {\n  const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n  const KEY = \"sql_test\";\n  const TABLE_NAME = `${KEY}_links_v2`;\n\n  // Ensure table exists\n  await sqlite.execute(`\n    CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (\n      uuid TEXT PRIMARY KEY,\n      guid TEXT,\n      title TEXT,\n      href TEXT,\n      hrefSecondary TEXT,\n      target TEXT,\n      rel TEXT,\n      tags TEXT,\n      createdAt TEXT,\n      updatedAt TEXT,\n      metadata TEXT\n    )\n  `);\n\n  // New endpoint for SQL queries\n  if (request.method === 'POST' && new URL(request.url).pathname === '/query') {\n    try {\n      const { query } = await request.json();\n      \n      // Basic security: prevent destructive queries\n      const sanitizedQuery = query.trim().toUpperCase();\n      if (\n        sanitizedQuery.startsWith('DROP ') || \n        sanitizedQuery.startsWith('DELETE ') || \n        sanitizedQuery.startsWith('ALTER ') || \n        sanitizedQuery.startsWith('CREATE ') ||\n        sanitizedQuery.startsWith('INSERT ')\n      ) {\n        return Response.json({ \n          error: 'Destructive queries are not allowed' \n        }, { status: 400 });\n      }\n\n      // Modify query to use our specific table name if no table specified\n      const finalQuery = sanitizedQuery.includes('FROM ') \n        ? query.replace(/FROM\\s+links/i, `FROM ${TABLE_NAME}`)\n        : `SELECT * FROM ${TABLE_NAME} WHERE 1=1`;\n\n      const result = await sqlite.execute(finalQuery);\n      \n      // Parse JSON fields if they exist\n      const processedRows = result.rows.map(row => {\n        const processedRow = {...row};\n        if (processedRow.tags) {\n          try { processedRow.tags = JSON.parse(processedRow.tags); } catch {}\n        }\n        if (processedRow.metadata) {\n          try { processedRow.metadata = JSON.parse(processedRow.metadata); } catch {}\n        }\n        return processedRow;\n      });\n\n      return Response.json({ results: processedRows });\n    } catch (error) {\n      return Response.json({ \n        error: error instanceof Error ? error.message : 'Unknown error' \n      }, { status: 500 });\n    }\n  }\n\n  // Handle different routes\n  if (request.method === 'GET' && new URL(request.url).pathname === '/links') {\n    const result = await sqlite.execute(`SELECT * FROM ${TABLE_NAME}`);\n    const links = result.rows.map(row => ({\n      ...row,\n      tags: JSON.parse(row.tags),\n      metadata: JSON.parse(row.metadata)\n    }));\n    return Response.json(links);\n  }\n\n  if (request.method === 'POST' && new URL(request.url).pathname === '/links') {\n    const link = await request.json();\n    \n    // Generate UUIDs if not provided\n    link.uuid = link.uuid || crypto.randomUUID();\n    link.guid = link.guid || crypto.randomUUID();\n    link.createdAt = link.createdAt || new Date().toISOString();\n    link.updatedAt = new Date().toISOString();\n    \n    await sqlite.execute(`\n      INSERT OR REPLACE INTO ${TABLE_NAME} \n      (uuid, guid, title, href, hrefSecondary, target, rel, tags, createdAt, updatedAt, metadata)\n      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n    `, [\n      link.uuid, \n      link.guid, \n      link.title, \n      link.href, \n      link.hrefSecondary || '', \n      link.target || '_blank', \n      link.rel || 'noopener', \n      JSON.stringify(link.tags || []), \n      link.createdAt, \n      link.updatedAt, \n      JSON.stringify(link.metadata || {})\n    ]);\n\n    const result = await sqlite.execute(`SELECT * FROM ${TABLE_NAME}`);\n    const links = result.rows.map(row => ({\n      ...row,\n      tags: JSON.parse(row.tags),\n      metadata: JSON.parse(row.metadata)\n    }));\n    return Response.json(links);\n  }\n\n  // Serve HTML\n  return new Response(`\n    <html>\n      <head>\n        <title>Link Manager</title>\n        <style>${css}</style>\n      </head>\n      <body>\n        <div id=\"root\"></div>\n        <script src=\"https://esm.town/v/std/catch\"></script>\n        <script type=\"module\" src=\"${import.meta.url}\"></script>\n      </body>\n    </html>\n  `, {\n    headers: { 'Content-Type': 'text/html' }\n  });\n}\n\nconst css = `\nbody { \n  font-family: system-ui, sans-serif; \n  max-width: 600px; \n  margin: 0 auto; \n  padding: 20px; \n}\nform { \n  display: flex; \n  gap: 10px; \n  margin-bottom: 20px; \n}\ninput { \n  flex-grow: 1; \n  padding: 8px; \n}\nul { \n  list-style-type: none; \n  padding: 0; \n}\nli { \n  display: flex; \n  justify-content: space-between; \n  padding: 10px; \n  border-bottom: 1px solid #eee; \n}\n`;","deleted","label",[99],"permalink","https://www.val.town/x/aazad/sql_test/code/main.tsx","extension","actionData","errors"]
</article></body></html>