Implemented caching during tokenization. Added a script for testing performance. Bumped the dev version number.v1.2.0

4 files changed, 162 insertions, 8 deletions

diff --git a/composer.json b/composer.json
index 6fb0095..d2be0bc 100644
--- a/composer.json
+++ b/composer.json
@@ -21,7 +21,7 @@
     },
     "extra": {
         "branch-alias": {
-            "dev-master": "1.2.x-dev"
+            "dev-master": "1.3.x-dev"
         }
     }
 }
diff --git a/lib/SqlFormatter.php b/lib/SqlFormatter.php
index bc59498..e66b23b 100644
--- a/lib/SqlFormatter.php
+++ b/lib/SqlFormatter.php
@@ -8,7 +8,7 @@
  * @copyright  2012 Jeremy Dorn
  * @license    http://www.opensource.org/licenses/lgpl-license.php LGPL
  * @link       http://github.com/jdorn/sql-formatter
- * @version    1.1.0
+ * @version    1.2.0
  */
 class SqlFormatter
 {
@@ -76,6 +76,26 @@ class SqlFormatter
     // This is a combination of all the boundary characters and all the whitespace characters
     protected static $all_boundaries;
 
+    //cache variables
+    //Only tokens shorter than this size will be cached.  Somewhere between 10 and 20 seems to work well for most cases.
+    public static $max_cachekey_size = 15;
+    protected static $token_cache = array();
+    protected static $cache_hits = 0;
+    protected static $cache_misses = 0;
+    
+    /**
+     * Get stats about the token cache
+     * @return Array An array containing the keys 'hits', 'misses', 'entries', and 'size' in bytes
+     */
+    public static function getCacheStats() {
+        return array(
+            'hits'=>self::$cache_hits,
+            'misses'=>self::$cache_misses,
+            'entries'=>count(self::$token_cache),
+            'size'=>strlen(serialize(self::$token_cache))
+        );
+    }
+    
     /**
      * Return the next token and token type in a SQL string.
      * Quoted strings, comments, reserved words, whitespace, and punctuation are all their own tokens.
@@ -284,21 +304,50 @@ class SqlFormatter
         $old_string_len = strlen($string) + 1;
 
         $token = null;
+        
+        $current_length = strlen($string);
 
         // Keep processing the string until it is empty
-        while (strlen($string)) {
+        while ($current_length) {
             // If the string stopped shrinking, there was a problem
-            if ($old_string_len <= strlen($string)) {
+            if ($old_string_len <= $current_length) {
                 throw new Exception("SQL Parse Error - Unable to tokenize string at character ".($original_length - $old_string_len));
             }
-            $old_string_len = strlen($string);
+            $old_string_len =  $current_length;
 
-            // Get the next token and the token type
-            $token = self::getNextToken($string, $token);
+            // Determine if we can use caching
+            if($current_length >= self::$max_cachekey_size) {
+                $cacheKey = substr($string,0,self::$max_cachekey_size);
+            }
+            else {
+                $cacheKey = false;
+            }
+
+            // See if the token is already cached
+            if($cacheKey && isset(self::$token_cache[$cacheKey])) {
+                //retrieve from cache
+                $token = self::$token_cache[$cacheKey];
+                $token_length = strlen($token['token']);
+                self::$cache_hits++;
+            }
+            else {
+                // Get the next token and the token type
+                $token = self::getNextToken($string, $token);
+                $token_length = strlen($token['token']);
+                self::$cache_misses++;
+                
+                // If the token is shorter than the max length, store it in cache
+                if($cacheKey && $token_length < self::$max_cachekey_size) {
+                    self::$token_cache[$cacheKey] = $token;
+                }
+            }
+            
             $tokens[] = $token;
 
             //advance the string
-            $string = substr($string, strlen($token['token']));
+            $string = substr($string, $token_length);
+            
+            $current_length -= $token_length;
         }
 
         return $tokens;
diff --git a/tests/performance.php b/tests/performance.php
new file mode 100644
index 0000000..a4270a0
--- /dev/null
+++ b/tests/performance.php
@@ -0,0 +1,44 @@
+<?php
+require '../lib/SqlFormatter.php';
+//this is the default value
+//set to '0' to disable caching
+//a value between 10 and 20 seems to give the best result
+SqlFormatter::$max_cachekey_size = 15;
+
+$contents = file('sql.sql');
+
+//track time and memory usage
+$start = microtime(true);
+$ustart = memory_get_usage(true);
+
+//track number of queries and size of queries
+$queries = 0;
+$bytes = 0;
+
+//format each query 3 times
+for ($i =0; $i<3; $i++) {
+    foreach ($contents as $query) {
+		//this tries to mix up the queries so we aren't just running the same thing a bunch of times
+        $query = str_replace('tablename', rand(1, 10000), $query);
+
+		//do formatting and highlighting
+        SqlFormatter::format($query);
+        
+        $queries++;
+        $bytes += strlen($query);
+    }
+}
+
+$uend = memory_get_usage(true);
+$end = microtime(true);
+
+echo "<p>Formatted $queries queries.</p>";
+
+echo "<p>Average query length of ".number_format($bytes/$queries,5)." characters</p>";
+
+echo "<p>Took ".number_format($end-$start,5)." seconds total, ".number_format(($end-$start)/$queries,5)." seconds per query, ".number_format(1000*($end-$start)/$bytes,5)." seconds per 1000 characters</p>";
+
+echo "<p>Used ".number_format($uend-$ustart)." bytes of memory</p>";
+
+echo "<h3>Cache Stats</h3><pre>".print_r(SqlFormatter::getCacheStats(),true)."</pre>";
+
diff --git a/tests/sql.sql b/tests/sql.sql
new file mode 100644
index 0000000..de6bdeb
--- /dev/null
+++ b/tests/sql.sql
@@ -0,0 +1,61 @@
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+CREATE DATABASE myNewDatabase
+DROP DATABASE database_name , database_name
+USE master
+EXEC sp_dboption myOldData, 'Single User', TRUE
+EXEC sp_renamedb 'myOldData', 'myNewData'
+EXEC sp_dboption myNewData, 'Single User', FALSE
+CREATE TABLE tablename (COLUMN_NAME column_type [(column_width[,column_precision])] .
+CREATE TABLE tablename (fname     VARCHAR(20)   NOT NULL,lname     VARCHAR(30)   NOT NULL,hire_date SMALLDATETIME NULL,ssn       CHAR(11)      NOT NULL)
+EXECUTE sp_help practice -- shows structure of table
+ALTER TABLE XYZ ADD mi CHAR(1) NULL
+SELECT * INTO tablename FROM tablename
+SELECT * FROM tablename
+IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn') BEGIN ALTER TABLE MyTableName ADD newcolumn VARCHAR(32) NOT NULL DEFAULT '' END
+IF EXISTS (SELECT * FROM dbo.syscolumns WHERE id = object_id(N'[dbo].[Projects]') AND NAME = 'ProjectManager') ALTER TABLE Projects DROP COLUMN [ProjectManager]
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+INSERT INTO tablename (1996,'ford') -- INTO is an optional keyword in SQLServer
+INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word
+INSERT INTO tablename VALUES(1001,'The Odyssey','Homer',NULL)
+INSERT INTO TABLE (c17,c4,c8,c3)
+EXECUTE sp_mystored_procedure1
+INSERT INTO OtherDatabaseName.tablename
+SELECT * FROM tablename WHERE loginid ='mfincher'
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+UPDATE tablename SET col = VALUE|col|expr
+UPDATE TABLE_NAME SET column_id = expr  WHERE condition
+UPDATE tablename SET password = 'newpass' WHERE USER = 'quest'
+UPDATE tablename SET password = 'newpass' WHERE (UserID > 1) AND (UserID < 113)
+UPDATE tablename SET password = 'newpass', change='Y' WHERE USER = 'quest'
+UPDATE tablename SET discount = discount + .1 WHERE lowqty >= 100
+UPDATE tablename SET LNAME = UPPER(LNAME)
+UPDATE tablename SET ytd_sales = (SELECT SUM(qty) FROM sales WHERE sales.title_id = titles.title_id AND ord_date BETWEEN '01/01/94' AND '12/31/94')
+INSERT adult SELECT ju.member_no,  ad.street,  ad.city,  ad.state,  ad.zip, ad.phone_no, DATEADD(YY, 1, GETDATE()) FROM tablename ju JOIN tablename ad ON ju.adult_member_no = ad.member_no WHERE (DATEADD(YY, 18, ju.birth_date) < GETDATE())
+DELETE FROM tablename WHERE userid < 50
+DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table)
+TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction
+DELETE tablename WHERE title_id IN (SELECT title_id FROM ...)
+SELECT str(myField, 12, 2) FROM tablename
+SELECT CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3)  AS 'Year  Day', COUNT(TIME) AS 'Key Question Pages', AVG(TIME) AS 'avg time (msec)' FROM tablename WHERE NAME='Key' GROUP BY CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3) ORDER BY CAST(datepart(yy,dtime) AS VARCHAR(4)) + ' ' + str(datepart(dy,dtime),3) ASC
+SELECT DISTINCT NAME FROM SYSDATABASES
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id
+SELECT tablename.match_id AS match_id0, tablename.match_date AS match_date1, tablename.hometeam_goals AS hometeam_goals2, tablename.awayteam_goals AS awayteam_goals3, t1_.team_id AS team_id4, t1_.name AS name5, t1_.goals_given AS goals_given6, t1_.goals_taken AS goals_taken7, t1_.wins AS wins8, t1_.draws AS draws9, t1_.losses AS losses10, t2_.team_id AS team_id11, t2_.name AS name12, t2_.goals_given AS goals_given13, t2_.goals_taken AS goals_taken14, t2_.wins AS wins15, t2_.draws AS draws16, t2_.losses AS losses17, tablename.awayteam_id AS awayteam_id18, tablename.hometeam_id AS hometeam_id19  FROM `match` tablename INNER JOIN team t1_ ON tablename.hometeam_id = t1_.team_id INNER JOIN team t2_ ON tablename.awayteam_id = t2_.team_id