# basic form of BEGIN-INSERT-COMMIT to write data to existing db
/usr/bin/sqlite3 db  < <( 
cat <<-'EOF'
BEGIN;
insert statement 1;
insert statement 2;
insert statement 3;
...
COMMIT;
.q
EOF
)


man sqlite3

/usr/bin/sqlite3
sqlite> .help
sqlite> .q


# file info database function
# usage: finfodb [optional: -t] [directory] [optional: filename]

function finfodb() {

   declare -a ar
   declare -i i timestamp
   declare checksum db filename searchdir

   declare dbdir="${HOME}/Library/FileInfoDB" 
   declare dbname="finfodb"
   declare sleep_before_write="0.5"
   declare -i block_insert_num=1000

   dbdir="${dbdir%/}"    # cut off trailing slash if necessary

   if [[ $# -eq 0 ]]; then 

      printf "%s\n%s\n" 'No arguments given!' 'Usage: finfodb [optional: -t] [directory] [optional: filename]'
      return 1

   elif [[ "${1}" == '-t' ]]; then

      if [[ ! -d "${2}" ]]; then printf "%s\n" "No directory: ${2}"; return 1; fi
      timestamp=1
      searchdir="${2}"
      filename="${3:-*}"

   else

      if [[ ! -d "${1}" ]]; then printf "%s\n" "No directory: ${1}"; return 1; fi
      timestamp=0
      searchdir="${1}"
      filename="${2:-*}"

   fi


   # store the SQLite databases in $dbdir
   /bin/mkdir -p "${dbdir}"

   if [[ $timestamp -eq 1 ]]; then

      # create name of SQLite database with time stamp
      db="${dbdir}/${dbname}--$(/bin/date -u +%Y-%m-%d--%H.%M.%S--%Z)"
      #db="${dbdir}/${dbname}--$(/bin/date +%Y-%m-%d--%H.%M.%S--%Z)"

   else

      db="${dbdir}/${dbname}"

   fi


   # create the SQLite database if necessary

   if [[ ! -f "${db}" ]]; then

/usr/bin/sqlite3 "${db}"  < <( 
/bin/cat <<-'EOF'
     PRAGMA encoding = "UTF-8";
     PRAGMA auto_vacuum = 1;
     create TABLE filecheck (num INTEGER PRIMARY KEY, filepath TEXT, checksum TEXT, timeEnter DATE);
     CREATE TRIGGER insert_filecheck_timeEnter AFTER INSERT ON filecheck
     BEGIN
      UPDATE filecheck SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid;
     END;
EOF
)

   fi

   i=0
   ar[${i}]='BEGIN;'$'\n'

   while read -d $'\0' filepath; do 

      # write $block_insert_num insert statements to ${db}
      # empty $ar and reset $i to 0
      if [[ $i -ge $block_insert_num ]]; then
      #if [[ "${#ar[@]}" -ge $block_insert_num ]]; then
         let i++
         ar[${i}]='COMMIT;'$'\n'
         let i++
         ar[${i}]='.q'$'\n'
         /bin/sleep $sleep_before_write
         # write insert statements stored in $ar to $db (see below)
         # delete a possible single leading space in every line (i.e. array item) using "${ar[@]/# /}"
         /usr/bin/sqlite3 "${db}" < <( printf "%s" "${ar[@]/# /}" )
         #/usr/bin/sqlite3 "${db}" < <( for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done )
         #/usr/bin/sqlite3 "${db}" <<< "$(echo "${ar[@]}" | /usr/bin/sed 's/^ *//')"
         unset -v ar i
         declare -a ar
         declare -i i=0
         ar[${i}]='BEGIN;'$'\n'
         echo
      fi

      let i++

      filepath_cleaned="${filepath//[[:cntrl:]]/}"   # remove control characters such as \n, \r, ...

      # cf. http://codesnippets.joyent.com/posts/show/1395
      if [[ ${#filepath_cleaned} -lt 85 ]]; then
         printf "\r\e[0K\e[1;32m%s\e[0m  %s" "${i}" "${filepath_cleaned}"
      else
         printf "\r\e[0K\e[1;32m%s\e[0m  %s" "${i}" "${filepath_cleaned:0:40}.....${filepath_cleaned: -40}"
      fi

      checksum="$(/sbin/md5 -q "${filepath}" 2>/dev/null)"

      # sha512
      # sudo port install openssl
      # cf. http://trac.macports.org/wiki/InstallingMacPorts
      #checksum="$(/opt/local/bin/openssl dgst -sha512 2>/dev/null < "${filepath}")"

      # escape single quotes for SQLite; cf. http://sqlite.org/lang_expr.html
      filepath="${filepath//\'/''}" 

      # escape newline characters \n
      #filepath="${filepath//$'\n'/\\n}" 
    
      # remove control characters such as \n, \r, ...  
      #filepath="${filepath//[[:cntrl:]]/}"   

      # create insert statements
      ar[${i}]="insert into filecheck (filepath,checksum) values ('${filepath}', '${checksum}');"$'\n'

   done < <(/usr/bin/find -x "${searchdir}" \( -type f -or -type l \) -name "${filename}" -print0 2>/dev/null)

   echo

   let i++
   ar[${i}]='COMMIT;'$'\n'
   let i++
   ar[${i}]='.q'$'\n'

   /bin/sleep $sleep_before_write


   # write insert statements stored in $ar to $db
   # cf. Removing whitspace from variables in bash?, http://www.jlaforums.com/viewtopic.php?t=1427403
   #/usr/bin/sqlite3 "${db}" <<< "$(for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done)"
   #/usr/bin/sqlite3 "${db}" < <( for ((i=0; i < "${#ar[@]}"; i++)); do printf "%s" "${ar[$i]#"${ar[$i]%%[! ]*}"}"; done )

   # delete a possible single leading space in every line (i.e. array item) using "${ar[@]/# /}"
   /usr/bin/sqlite3 "${db}" < <( printf "%s" "${ar[@]/# /}" )

   # alternatives with sed to trim leading whitespace
   #/usr/bin/sqlite3 "${db}" <<< "$(echo "${ar[@]}" | /usr/bin/sed 's/^ *//')"
   #/usr/bin/sqlite3 "${db}"  < <(echo "${ar[@]}" | /usr/bin/sed 's/^ *//' )


   /usr/sbin/chown -R $(/usr/bin/logname):$(/usr/bin/logname) "${dbdir}"
   /bin/chmod -R 0700 "${dbdir}"

   return 0
}



finfodb
finfodb 1234
finfodb -t
finfodb /usr/bin
finfodb /usr/bin "*z*"
finfodb -t /usr/bin
finfodb -t /usr/bin "*z*"

finfodb ~/Library
finfodb /Library

open ~/Library/FileInfoDB

sqlite3 ~/Library/FileInfoDB/finfodb  ".dump"
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from sqlite_master"
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%sync%'";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%/_sync'";
sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE filepath LIKE '%/ds%'";


sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb  "delete from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb  "select * from filecheck WHERE checksum LIKE ''";

sqlite3 ~/Library/FileInfoDB/finfodb "vacuum"

sqlite3 ~/Library/FileInfoDB/finfodb "pragma integrity_check"

<%@ Page Language="VB" %>
<script runat="server">

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim cnnTables As New System.Data.SqlClient.SqlConnection
        cnnTables.ConnectionString = "Data Source=localhost;Initial Catalog=DB_NAME;User ID=USER;Password=PASS;"

        cnnTables.Open()

        gridTables.DataSource = cnnTables.GetSchema("Tables")
        gridTables.DataBind()

        cnnTables.Close()
    End Sub

</script>
<html>
<head>
    <title>List Tables In Database</title>
</head>
<body>
<form id="myForm" runat="server">

<asp:GridView ID="gridTables" runat="server" />

</form>
</body>
</html> 

create table Users (
  userId integer unique not null,
  userFirstName varchar(50) not null,
  userLastName varchar(50) not null
);

create table "Users" (
  "userId" integer unique not null,
  "userFirstName" varchar(50) not null,
  "userLastName" varchar(50) not null
);

<?php

class xml
{
	var $dom;
	var $uri;

	function xml($uri)
	{
		$this->dom = new DOMDocument();

		if(preg_match('/\.xml$/', $uri))
		{
			$this->uri = $uri;
		}
		else
		{
			$this->uri = $this->create($uri);
		}
		$this->dom->load($this->uri);
	}

	function set($query, $value)
	{
		$path = new DOMXPath($this->dom);
		$nodes = $path->query($query);
		$nodes->item(0)->nodeValue = $value;
	}

	function get($query)
	{
		$path = new DOMXPath($this->dom);
		$nodes = $path->query($query);
		return $nodes->item(0)->nodeValue;
	}

	function save()
	{
		$this->dom->save($this->uri);
	}

	function create($uri)
	{
		// Build the URI of the template file.
		$template = sprintf('%s/0.xml', $uri);

		// If the directory doesn't exist, we can't really
		// do anything.
		if(!is_dir($uri))
		{
			exit('No directory');
		}

		// If the template file doesn't exist, we cannot
		// create a new file automatically.
		if(!file_exists($template))
		{
			exit('No template');
		}

		// Load the template XML into our DOMDocument.
		$this->dom->load($template);

		// Scan the directory into an array.
		$dir = scandir($uri);

		// Pull out the highest ID
		$id = str_replace('.xml', '', array_pop($dir));

		// Add one to it
		$id++;

		// Construct the new path with the new ID
		$uri = sprintf('%s/%s.xml', $uri, $id);

		// Save the new file
		$this->dom->save($uri);

		// Return the URI of the new XML file.
		return $uri;
	}
}

// The following code will create a new XML file
// under the directory /var/www/data/users.
$x = new xml('/var/www/data/users');
$x->set('//user/name', 'John Doe');
$x->save();

?>

ALTER TABLE TableName MODIFY ColumnName ColumnType NULL/NOT NULL AFTER OtherColumnName;

for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep -v "exclude_this");
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

record = application("sql").query("select * from categories order by lft asc")
dim output as new arraylist()
dim right as new arraylist()
do while not record.eof
  if right.count > 0
    do while right(right.count - 1) < record("rgt").value
      right.removeAt(right.count - 1)
      if right.count = 0 then exit do
    loop
  end if
  response.write(record("name").value & " is " & right.count & " levels deep in the tree..." & controlchars.cr)
  right.add(record("rgt").value)
  record.movenext
loop

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

mysqldump -u root -p —all-databases > /path/to/backups/YYYY-MM-DD-alldbs.sql
scp /path/to/backups/YYYY-MM-DD-alldbs.sql.gz user@remotehost.com:/path/to/backups
tar czvf /path/to/backups/YYYY-MM-DD-production.tgz /path/to/production
scp /path/to/backups/YYYY-MM-DD-production.tgz user@remotehost.com:/path/to/backups