3 database snippets

A General Purpose SQL-to-HTML Routine for CoffeeScript/JavaScript/Node.js

Using node-mysql or similar, the following CoffeeScript routine will generate an HTML table containing the data in a SQL result set, including column headings:

sql_to_html:(connection,query,bindvars,callback)->
  connection.query query, bindvars, (err,rows,fields)->
    if err?
       callback(err)
    else
      buffer = '<table border=1><tr>'
      for field in fields
        buffer += "<th>#{field.name}</th>"
      buffer += '</tr>'
      for row in rows
        buffer += '<tr>'
        for field in fields
          buffer += "<td>#{row[field.name]}</td>"
        buffer += '</tr>'
      buffer += '</table>'
      callback(null,buffer)
Published 8 Feb 2014

 

Finding and Closing Idle Connections in PostgreSQL

To enumerate all database connections that have been idle for at least 10 minutes:

SELECT
  DATE_TRUNC('second',NOW()-query_start) AS age,
  client_port,
  current_query
FROM
  pg_stat_activity
WHERE
  current_query = '<IDLE>'
  AND NOW() - query_start > '00:10:00'
ORDER BY
  age DESC;

To close all database connections that have been idle for at least 10 minutes:

SELECT
  pg_terminate_backend(procpid)
FROM
  pg_stat_activity
WHERE
 current_query = '<IDLE>'
 AND now() - query_start > '00:10:00';

WARNING Don't be fooled by the SELECT statement used here. The query above will drop database connections.

Published 14 Dec 2015
Tagged database and postgresql.

 

A Cheat Sheet for SQLite

General

  • Most of the SQLite "meta" commands begin with a dot. When in doubt, try .help
  • Use Ctrl-d or .exit or .quit to exit (and Ctrl-c to terminiate a long-running SQL query).
  • Enter .show to see current settings.

Meta-data

  • Enter .databases to see a list of mounted databases.
  • Enter .tables to see a list of table names.
  • Enter .index to see a list of index names.
  • Enter .schema TABLENAME to see the create table statement for a given table.

Import and Export

  • Enter .output FILENAME to pipe output to the specified file. (Use .output stdout to return to the default behavior or printing results to the console.)
  • Enter .mode [csv|column|html|insert|line|list|tabs|tcl] to change the way in which query results are printed.
  • Enter .separator DELIM to change the delimiter used in (list-mode) exports and imports. (Defaults to |.)
  • Enter .dump [TABLEPATTERN] to create a collection of SQL statements for recreating the database (or just those tables with naames matching the optional TABLEPATTERN).
  • Enter .read FILENAME to execute the specified file as a SQL script.
Published 18 Sep 2013

 

This page was generated at 4:16 PM on 26 Feb 2018.
Copyright © 1999 - 2018 Rodney Waldhoff.