diff options
| author | jakefeasel <jfeasel@gmail.com> | 2015-03-06 17:18:26 -0800 |
|---|---|---|
| committer | jakefeasel <jfeasel@gmail.com> | 2015-03-06 17:18:26 -0800 |
| commit | a1dd2e944e5254bad419a504e727c3813caa9311 (patch) | |
| tree | 7e6ae1a11b7be589eece8f608ecd9e8afb04cfcf | |
| parent | 0749da2dba3025de6de96dea9ec32befe8cc9c0f (diff) | |
| download | sqlfiddle2-a1dd2e944e5254bad419a504e727c3813caa9311.zip sqlfiddle2-a1dd2e944e5254bad419a504e727c3813caa9311.tar.gz sqlfiddle2-a1dd2e944e5254bad419a504e727c3813caa9311.tar.bz2 | |
AWS-oriented changes for migrating and dealing with pre-existing site data
| -rw-r--r-- | src/main/resources/db/sqlfiddle/data.sql | 42 | ||||
| -rw-r--r-- | src/main/resources/db/sqlfiddle/migrate.sql | 112 | ||||
| -rw-r--r-- | src/main/resources/db/sqlfiddle/schema.sql | 2 | ||||
| -rw-r--r-- | vagrant_scripts/.gitignore | 1 | ||||
| -rwxr-xr-x | vagrant_scripts/idm_aws.sh | 16 | ||||
| -rwxr-xr-x | vagrant_scripts/idm_bootstrap.sh | 2 | ||||
| -rwxr-xr-x | vagrant_scripts/pg93_bootstrap.sh | 5 |
7 files changed, 158 insertions, 22 deletions
diff --git a/src/main/resources/db/sqlfiddle/data.sql b/src/main/resources/db/sqlfiddle/data.sql index 5df9002..fa9240d 100644 --- a/src/main/resources/db/sqlfiddle/data.sql +++ b/src/main/resources/db/sqlfiddle/data.sql @@ -13,12 +13,6 @@ SET search_path = public, pg_catalog; -- -- Data for Name: db_types; Type: TABLE DATA; Schema: public; Owner: postgres -- ---2 MySQL 5.5.32 MySQL \nCREATE database db_#databaseName# default CHARACTER SET = utf8 default COLLATE = utf8_general_ci;\nGRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,TRIGGER,EXECUTE\n ON db_#databaseName#.* TO user_#databaseName#@'%' IDENTIFIED BY '#databaseName#';\n org.gjt.mm.mysql.Driver \n DROP DATABASE db_#databaseName#; drop user user_#databaseName#@'%';\n allowMultiQueries=true \N Separate multiple statements in MySQL by terminating each one with a semicolon. 2/a2581/1 explain extended \N \N host \N 1 show databases ---10 PostgreSQL 8.3.20 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N \N 10/f9585/1 explain \N \N host \N 0 select datname as schema_name from pg_database ---12 PostgreSQL 9.2.4 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#;\n\t \N \N \N 12/6d80e/1 explain \N \N host \N 0 select datname as schema_name from pg_database ---11 PostgreSQL 8.4.17 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N \N 11/6d80e/1 explain \N \N host \N 0 select datname as schema_name from pg_database ---3 MS SQL Server 2008 SQL Server \nCREATE DATABASE db_#databaseName#;\nGO\n\nUSE db_#databaseName#;\nGO\n\nCREATE LOGIN user_#databaseName#\nWITH PASSWORD = '#databaseName#';\nGO\n\nCREATE USER user_#databaseName#;\nGO\n\nGRANT \nCREATE TABLE,\nCREATE TYPE,\nCREATE VIEW,\nCREATE PROCEDURE,\nCREATE FUNCTION,\nCREATE FULLTEXT CATALOG,\nEXECUTE,\nDELETE,\nINSERT,\nREFERENCES,\nSELECT,\nSHOWPLAN,\nUPDATE\nTO user_#databaseName#;\nGO\n\n\n\nALTER LOGIN user_#databaseName# WITH DEFAULT_DATABASE=db_#databaseName#;\nGO\n\nGRANT ALTER ON SCHEMA::dbo TO user_#databaseName#;\nGO\n\nuse master;\n\n net.sourceforge.jtds.jdbc.Driver exec dbo.clearDBUsers 'db_#databaseName#';\nGO\ndrop database db_#databaseName#;\nGO\ndrop login user_#databaseName#;\n \N GO SQL Server supports multiple statements in a batch separated by semicolons. Separate statement batches with a line consisting of a single GO command, as needed. 3/1fa93/1 \nSET SHOWPLAN_XML ON;\nGO\n \nGO\nSET SHOWPLAN_XML OFF \n<?xml version="1.0" encoding="utf-8"?>\n<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"\n xmlns:msxsl="urn:schemas-microsoft-com:xslt"\n xmlns:s="http://schemas.microsoft.com/sqlserver/2004/07/showplan"\n exclude-result-prefixes="msxsl s xsl">\n <xsl:output method="html" indent="no" omit-xml-declaration="yes" />\n\n <!-- Disable built-in recursive processing templates -->\n <xsl:template match="*|/|text()|@*" mode="NodeLabel2" />\n <xsl:template match="*|/|text()|@*" mode="ToolTipDescription" />\n <xsl:template match="*|/|text()|@*" mode="ToolTipDetails" />\n\n <!-- Default template -->\n <xsl:template match="/">\n <xsl:apply-templates select="s:ShowPlanXML" />\n </xsl:template>\n\n <!-- Outermost div that contains all statement plans. -->\n <xsl:template match="s:ShowPlanXML">\n <div class="qp-root">\n <xsl:apply-templates select="s:BatchSequence/s:Batch/s:Statements/s:StmtSimple" /> \n </div>\n </xsl:template>\n \n <!-- Matches a branch in the query plan (either an operation or a statement) -->\n <xsl:template match="s:RelOp|s:StmtSimple">\n <div class="qp-tr">\n <div>\n <div class="qp-node">\n <xsl:apply-templates select="." mode="NodeIcon" />\n <xsl:apply-templates select="." mode="NodeLabel" />\n <xsl:apply-templates select="." mode="NodeLabel2" />\n <xsl:apply-templates select="." mode="NodeCostLabel" />\n <xsl:call-template name="ToolTip" />\n </div>\n </div>\n <div><xsl:apply-templates select="*/s:RelOp" /></div>\n </div>\n </xsl:template>\n\n <!-- Writes the tool tip -->\n <xsl:template name="ToolTip">\n <div class="qp-tt">\n <div class="qp-tt-header"><xsl:value-of select="@PhysicalOp | @StatementType" /></div>\n <div><xsl:apply-templates select="." mode="ToolTipDescription" /></div>\n <xsl:call-template name="ToolTipGrid" />\n <xsl:apply-templates select="* | @* | */* | */@*" mode="ToolTipDetails" />\n </div>\n </xsl:template>\n\n <!-- Writes the grid of node properties to the tool tip -->\n <xsl:template name="ToolTipGrid">\n <table>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="s:QueryPlan/@CachedPlanSize" />\n <xsl:with-param name="Label">Cached plan size</xsl:with-param>\n <xsl:with-param name="Value" select="concat(s:QueryPlan/@CachedPlanSize, ' B')" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Physical Operation</xsl:with-param>\n <xsl:with-param name="Value" select="@PhysicalOp" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Logical Operation</xsl:with-param>\n <xsl:with-param name="Value" select="@LogicalOp" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Actual Number of Rows</xsl:with-param>\n <xsl:with-param name="Value" select="s:RunTimeInformation/s:RunTimeCountersPerThread/@ActualRows" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateIO" />\n <xsl:with-param name="Label">Estimated I/O Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@EstimateIO" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateCPU" />\n <xsl:with-param name="Label">Estimated CPU Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@EstimateCPU" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <!-- TODO: Estimated Number of Executions -->\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Number of Executions</xsl:with-param>\n <xsl:with-param name="Value" select="s:RunTimeInformation/s:RunTimeCountersPerThread/@ActualExecutions" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Degree of Parallelism</xsl:with-param>\n <xsl:with-param name="Value" select="s:QueryPlan/@DegreeOfParallelism" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Memory Grant</xsl:with-param>\n <xsl:with-param name="Value" select="s:QueryPlan/@MemoryGrant" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateIO | @EstimateCPU" />\n <xsl:with-param name="Label">Estimated Operator Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:variable name="EstimatedOperatorCost">\n <xsl:call-template name="EstimatedOperatorCost" />\n </xsl:variable>\n <xsl:variable name="TotalCost">\n <xsl:value-of select="ancestor::s:StmtSimple/@StatementSubTreeCost" />\n </xsl:variable>\n \n <xsl:call-template name="round">\n <xsl:with-param name="value" select="$EstimatedOperatorCost" />\n </xsl:call-template>\n (<xsl:value-of select="format-number(number($EstimatedOperatorCost) div number($TotalCost), '0%')" />)\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@StatementSubTreeCost | @EstimatedTotalSubtreeCost" />\n <xsl:with-param name="Label">Estimated Subtree Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@StatementSubTreeCost | @EstimatedTotalSubtreeCost" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Estimated Number of Rows</xsl:with-param>\n <xsl:with-param name="Value" select="@StatementEstRows | @EstimateRows" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@AvgRowSize" />\n <xsl:with-param name="Label">Estimated Row Size</xsl:with-param>\n <xsl:with-param name="Value" select="concat(@AvgRowSize, ' B')" />\n </xsl:call-template>\n <!-- TODO: Actual Rebinds\n TODO: Actual Rewinds -->\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="s:IndexScan/@Ordered" />\n <xsl:with-param name="Label">Ordered</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:choose>\n <xsl:when test="s:IndexScan/@Ordered = 1">True</xsl:when>\n <xsl:otherwise>False</xsl:otherwise>\n </xsl:choose>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Node ID</xsl:with-param>\n <xsl:with-param name="Value" select="@NodeId" />\n </xsl:call-template>\n </table>\n </xsl:template>\n\n <!-- Calculates the estimated operator cost. -->\n <xsl:template name="EstimatedOperatorCost">\n <xsl:variable name="EstimateIO">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="@EstimateIO" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:variable name="EstimateCPU">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="@EstimateCPU" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:value-of select="number($EstimateIO) + number($EstimateCPU)" />\n </xsl:template>\n\n <!-- Renders a row in the tool tip details table. -->\n <xsl:template name="ToolTipRow">\n <xsl:param name="Label" />\n <xsl:param name="Value" />\n <xsl:param name="Condition" select="$Value" />\n <xsl:if test="$Condition">\n <tr>\n <th><xsl:value-of select="$Label" /></th>\n <td><xsl:value-of select="$Value" /></td>\n </tr> \n </xsl:if>\n </xsl:template>\n\n <!-- Prints the name of an object. -->\n <xsl:template match="s:Object | s:ColumnReference" mode="ObjectName">\n <xsl:param name="ExcludeDatabaseName" select="false()" />\n <xsl:choose>\n <xsl:when test="$ExcludeDatabaseName">\n <xsl:for-each select="@Table | @Index | @Column | @Alias">\n <xsl:value-of select="." />\n <xsl:if test="position() != last()">.</xsl:if>\n </xsl:for-each>\n </xsl:when>\n <xsl:otherwise>\n <xsl:for-each select="@Database | @Schema | @Table | @Index | @Column | @Alias">\n <xsl:value-of select="." />\n <xsl:if test="position() != last()">.</xsl:if>\n </xsl:for-each>\n </xsl:otherwise>\n </xsl:choose>\n </xsl:template>\n\n <!-- Displays the node cost label. -->\n <xsl:template match="s:RelOp" mode="NodeCostLabel">\n <xsl:variable name="EstimatedOperatorCost"><xsl:call-template name="EstimatedOperatorCost" /></xsl:variable>\n <xsl:variable name="TotalCost"><xsl:value-of select="ancestor::s:StmtSimple/@StatementSubTreeCost" /></xsl:variable>\n <div>Cost: <xsl:value-of select="format-number(number($EstimatedOperatorCost) div number($TotalCost), '0%')" /></div>\n </xsl:template>\n\n <!-- Dont show the node cost for statements. -->\n <xsl:template match="s:StmtSimple" mode="NodeCostLabel" />\n\n <!-- \n ================================\n Tool tip detail sections\n ================================\n The following section contains templates used for writing the detail sections at the bottom of the tool tip,\n for example listing outputs, or information about the object to which an operator applies.\n -->\n\n <xsl:template match="*/s:Object" mode="ToolTipDetails">\n <!-- TODO: Make sure this works all the time -->\n <div class="qp-bold">Object</div>\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:template>\n\n <xsl:template match="s:SetPredicate[s:ScalarOperator/@ScalarString]" mode="ToolTipDetails">\n <div class="qp-bold">Predicate</div>\n <div><xsl:value-of select="s:ScalarOperator/@ScalarString" /></div>\n </xsl:template>\n\n <xsl:template match="s:OutputList[count(s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Output List</div>\n <xsl:for-each select="s:ColumnReference">\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:for-each>\n </xsl:template>\n\n <xsl:template match="s:NestedLoops/s:OuterReferences[count(s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Outer References</div>\n <xsl:for-each select="s:ColumnReference">\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:for-each>\n </xsl:template>\n\n <xsl:template match="@StatementText" mode="ToolTipDetails">\n <div class="qp-bold">Statement</div>\n <div><xsl:value-of select="." /></div>\n </xsl:template>\n\n <xsl:template match="s:Sort/s:OrderBy[count(s:OrderByColumn/s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Order By</div>\n <xsl:for-each select="s:OrderByColumn">\n <div>\n <xsl:apply-templates select="s:ColumnReference" mode="ObjectName" />\n <xsl:choose>\n <xsl:when test="@Ascending = 1"> Ascending</xsl:when>\n <xsl:otherwise> Descending</xsl:otherwise>\n </xsl:choose>\n </div>\n </xsl:for-each>\n </xsl:template>\n\n <!-- TODO: Seek Predicates -->\n\n <!-- \n ================================\n Node icons\n ================================\n The following templates determine what icon should be shown for a given node\n -->\n\n <!-- Use the logical operation to determine the icon for the "Parallelism" operators. -->\n <xsl:template match="s:RelOp[@PhysicalOp = 'Parallelism']" mode="NodeIcon" priority="1">\n <xsl:element name="div">\n <xsl:attribute name="class">qp-icon-<xsl:value-of select="translate(@LogicalOp, ' ', '')" /></xsl:attribute>\n </xsl:element>\n </xsl:template>\n\n <!-- Use the physical operation to determine icon if it is present. -->\n <xsl:template match="*[@PhysicalOp]" mode="NodeIcon">\n <xsl:element name="div">\n <xsl:attribute name="class">qp-icon-<xsl:value-of select="translate(@PhysicalOp, ' ', '')" /></xsl:attribute>\n </xsl:element>\n </xsl:template>\n \n <!-- Matches all statements. -->\n <xsl:template match="s:StmtSimple" mode="NodeIcon">\n <div class="qp-icon-Statement"></div>\n </xsl:template>\n\n <!-- Fallback template - show the Bitmap icon. -->\n <xsl:template match="*" mode="NodeIcon">\n <div class="qp-icon-Catchall"></div>\n </xsl:template>\n\n <!-- \n ================================\n Node labels\n ================================\n The following section contains templates used to determine the first (main) label for a node.\n -->\n\n <xsl:template match="s:RelOp" mode="NodeLabel">\n <div><xsl:value-of select="@PhysicalOp" /></div>\n </xsl:template>\n\n <xsl:template match="s:StmtSimple" mode="NodeLabel">\n <div><xsl:value-of select="@StatementType" /></div>\n </xsl:template>\n\n <!--\n ================================\n Node alternate labels\n ================================\n The following section contains templates used to determine the second label to be displayed for a node.\n -->\n\n <!-- Display the object for any node that has one -->\n <xsl:template match="*[*/s:Object]" mode="NodeLabel2">\n <xsl:variable name="ObjectName">\n <xsl:apply-templates select="*/s:Object" mode="ObjectName">\n <xsl:with-param name="ExcludeDatabaseName" select="true()" />\n </xsl:apply-templates>\n </xsl:variable>\n <div>\n <xsl:value-of select="substring($ObjectName, 0, 36)" />\n <xsl:if test="string-length($ObjectName) >= 36">…</xsl:if>\n </div>\n </xsl:template>\n\n <!-- Display the logical operation for any node where it is not the same as the physical operation. -->\n <xsl:template match="s:RelOp[@LogicalOp != @PhysicalOp]" mode="NodeLabel2">\n <div>(<xsl:value-of select="@LogicalOp" />)</div>\n </xsl:template>\n\n <!-- Disable the default template -->\n <xsl:template match="*" mode="NodeLabel2" />\n\n <!-- \n ================================\n Tool tip descriptions\n ================================\n The following section contains templates used for writing the description shown in the tool tip.\n -->\n\n <xsl:template match="*[@PhysicalOp = 'Table Insert']" mode="ToolTipDescription">Insert input rows into the table specified in Argument field.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Compute Scalar']" mode="ToolTipDescription">Compute new values from existing values in a row.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Sort']" mode="ToolTipDescription">Sort the input.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Clustered Index Scan']" mode="ToolTipDescription">Scanning a clustered index, entirely or only a range.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Stream Aggregate']" mode="ToolTipDescription">Compute summary values for groups of rows in a suitably sorted stream.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Hash Match']" mode="ToolTipDescription">Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Bitmap']" mode="ToolTipDescription">Bitmap.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Clustered Index Seek']" mode="ToolTipDescription">Scanning a particular range of rows from a clustered index.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Index Seek']" mode="ToolTipDescription">Scan a particular range of rows from a nonclustered index.</xsl:template>\n\n <xsl:template match="*[@PhysicalOp = 'Parallelism' and @LogicalOp='Repartition Streams']" mode="ToolTipDescription">Repartition Streams.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Parallelism']" mode="ToolTipDescription">An operation involving parallelism.</xsl:template>\n \n <xsl:template match="*[s:TableScan]" mode="ToolTipDescription">Scan rows from a table.</xsl:template>\n <xsl:template match="*[s:NestedLoops]" mode="ToolTipDescription">For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.</xsl:template>\n <xsl:template match="*[s:Top]" mode="ToolTipDescription">Select the first few rows based on a sort order.</xsl:template>\n\n <!-- \n ================================\n Number handling\n ================================\n The following section contains templates used for handling numbers (scientific notation, rounding etc...)\n -->\n\n <!-- Outputs a number rounded to 7 decimal places - to be used for displaying all numbers.\n This template accepts numbers in scientific notation. -->\n <xsl:template name="round">\n <xsl:param name="value" select="0" />\n <xsl:variable name="number">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="$value" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:value-of select="round(number($number) * 10000000) div 10000000" />\n </xsl:template>\n \n <!-- Template for handling of scientific numbers\n See: http://www.orm-designer.com/article/xslt-convert-scientific-notation-to-decimal-number -->\n <xsl:variable name="max-exp">\n <xsl:value-of select="'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'" />\n </xsl:variable>\n\n <xsl:template name="convertSciToNumString">\n <xsl:param name="inputVal" select="0" />\n\n <xsl:variable name="numInput">\n <xsl:value-of select="translate(string($inputVal),'e','E')" />\n </xsl:variable>\n\n <xsl:choose>\n <xsl:when test="number($numInput) = $numInput">\n <xsl:value-of select="$numInput" />\n </xsl:when> \n <xsl:otherwise>\n <!-- ==== Mantisa ==== -->\n <xsl:variable name="numMantisa">\n <xsl:value-of select="number(substring-before($numInput,'E'))" />\n </xsl:variable>\n\n <!-- ==== Exponent ==== -->\n <xsl:variable name="numExponent">\n <xsl:choose>\n <xsl:when test="contains($numInput,'E+')">\n <xsl:value-of select="substring-after($numInput,'E+')" />\n </xsl:when>\n <xsl:otherwise>\n <xsl:value-of select="substring-after($numInput,'E')" />\n </xsl:otherwise>\n </xsl:choose>\n </xsl:variable>\n\n <!-- ==== Coefficient ==== -->\n <xsl:variable name="numCoefficient">\n <xsl:choose>\n <xsl:when test="$numExponent > 0">\n <xsl:text>1</xsl:text>\n <xsl:value-of select="substring($max-exp, 1, number($numExponent))" />\n </xsl:when>\n <xsl:when test="$numExponent < 0">\n <xsl:text>0.</xsl:text>\n <xsl:value-of select="substring($max-exp, 1, -number($numExponent)-1)" />\n <xsl:text>1</xsl:text>\n </xsl:when>\n <xsl:otherwise>1</xsl:otherwise>\n </xsl:choose>\n </xsl:variable>\n <xsl:value-of select="number($numCoefficient) * number($numMantisa)" />\n </xsl:otherwise>\n </xsl:choose>\n </xsl:template>\n</xsl:stylesheet>\n host //*[@StatementType="COMMIT TRANSACTION"] 0 SELECT name as [schema_name] FROM master..sysdatabases ---1 PostgreSQL 9.1.9 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N Separate multiple statements in PostgreSQL by terminating each one with a semicolon. 1/6ccc5/2 explain \N \N host \N 0 select datname as schema_name from pg_database COPY db_types (id, full_name, simple_name, setup_script_template, jdbc_class_name, drop_script_template, custom_jdbc_attributes, batch_separator, notes, sample_fragment, execution_plan_prefix, execution_plan_suffix, execution_plan_xslt, context, execution_plan_check, is_latest_stable, list_database_script) FROM stdin; 4 Oracle 11g R2 Oracle \ncreate user user_#databaseName# identified by "#databaseName#" default tablespace fiddledata profile fiddleusers\n/\ngrant create session, create synonym, create table, create type, create view, create materialized view, create procedure, create sequence, create trigger to user_#databaseName#\n/\nalter user user_#databaseName# quota 5M on fiddledata\n/\ncreate table system.deferred_#databaseName# (val NUMBER(1) CONSTRAINT deferred_#databaseName#_ck CHECK(val =1) DEFERRABLE INITIALLY DEFERRED)\n/\ngrant insert on system.deferred_#databaseName# to user_#databaseName#\n/\n oracle.jdbc.OracleDriver DECLARE\n l_cnt integer;\nBEGIN\n EXECUTE IMMEDIATE 'alter user user_#databaseName# account lock';\n FOR x IN (SELECT *\n FROM v$session\n WHERE username = 'USER_#databaseName#')\n LOOP\n EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';\n END LOOP;\n\n -- Wait for as long as it takes for all the sessions to go away\n LOOP\n SELECT COUNT(*)\n INTO l_cnt\n FROM v$session\n WHERE username = 'USER_#databaseName#';\n EXIT WHEN l_cnt = 0;\n dbms_lock.sleep( 1 );\n END LOOP;\n\n EXECUTE IMMEDIATE 'drop user user_#databaseName# cascade';\n\n EXECUTE IMMEDIATE 'drop table system.deferred_#databaseName#';\nEND; \N / Oracle does not support multiple statements in batch. Separate each statement with a line consisting of a single /, and do not terminate statements with semicolons. 4/c0be1/1 \n\texplain plan set STATEMENT_ID = '#schema_short_code#/#query_id#' for \n \n\n\n/\n\nselect dbms_xplan.display_plan(format=>'ALL', type=>'ACTIVE', statement_id => '#schema_short_code#/#query_id#') as XPLAN FROM dual \n<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">\n<xsl:output method="html" indent="no" omit-xml-declaration="yes" cdata-section-elements="report_id info hint"/>\n\n<xsl:template match="/">\n <script type="text/xml" class="oracle_xplan_xml">\n<xsl:copy-of select="/html/body/script[@type='text/xml']"/>\n </script>\n <div class="oracle_xplan"></div>\n</xsl:template>\n</xsl:stylesheet>\n host \N 1 select schema_name from (select distinct lower(replace(owner, 'USER', 'DB')) as schema_name from dba_objects) tmp @@ -27,6 +21,14 @@ COPY db_types (id, full_name, simple_name, setup_script_template, jdbc_class_nam 5 SQLite (SQL.js) SQLite \nCREATE USER user_#databaseName# PASSWORD '#databaseName#' sqljs \N \N \N \N 5/b5362/1 \N \N \N browser \N 1 \N 9 MySQL 5.6 MySQL \nCREATE database db_#databaseName# default CHARACTER SET = utf8 default COLLATE = utf8_general_ci;\nGRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,TRIGGER,EXECUTE\n ON db_#databaseName#.* TO user_#databaseName#@'%' IDENTIFIED BY '#databaseName#';\nGRANT SELECT ON performance_schema.* TO user_#databaseName#@'%';\n org.gjt.mm.mysql.Driver \n DROP DATABASE db_#databaseName#; drop user user_#databaseName#@'%';\n \N \N \N 9/dcb16/1 explain extended \N \N host \N 0 show databases 15 PostgreSQL 9.3 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1 user=postgres');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = upper('DB_#databaseName#'); DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N \N 15/35773/1 explain \N \N host \N 1 select schema_name from (select datname as schema_name from pg_database) t +1 PostgreSQL 9.1 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N Separate multiple statements in PostgreSQL by terminating each one with a semicolon. 1/6ccc5/2 explain \N \N host \N 0 select datname as schema_name from pg_database +2 MySQL 5.5 MySQL \nCREATE database db_#databaseName# default CHARACTER SET = utf8 default COLLATE = utf8_general_ci;\nGRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,TRIGGER,EXECUTE\n ON db_#databaseName#.* TO user_#databaseName#@'%' IDENTIFIED BY '#databaseName#';\n org.gjt.mm.mysql.Driver \n DROP DATABASE db_#databaseName#; drop user user_#databaseName#@'%';\n allowMultiQueries=true \N Separate multiple statements in MySQL by terminating each one with a semicolon. 2/a2581/1 explain extended \N \N host \N 0 show databases +8 MySQL 5.1 MySQL \nCREATE database db_#databaseName# default CHARACTER SET = utf8 default COLLATE = utf8_general_ci;\nGRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,TRIGGER,EXECUTE\n ON db_#databaseName#.* TO user_#databaseName#@'%' IDENTIFIED BY '#databaseName#';\n org.gjt.mm.mysql.Driver \n DROP DATABASE db_#databaseName#; drop user user_#databaseName#@'%';\n \N \N \N 8/a2581/1 explain extended \N \N host \N 0 show databases +11 PostgreSQL 8.4 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N \N 11/6d80e/1 explain \N \N host \N 0 select datname as schema_name from pg_database +12 PostgreSQL 9.2 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#;\n\t \N \N \N 12/6d80e/1 explain \N \N host \N 0 select datname as schema_name from pg_database +13 MySQL 5.7 MySQL \nCREATE database db_#databaseName# default CHARACTER SET = utf8 default COLLATE = utf8_general_ci;\nGRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,TRIGGER,EXECUTE\n ON db_#databaseName#.* TO user_#databaseName#@'%' IDENTIFIED BY '#databaseName#';\n org.gjt.mm.mysql.Driver \n DROP DATABASE db_#databaseName#; drop user user_#databaseName#@'%';\n allowMultiQueries=true \N Separate multiple statements in MySQL by terminating each one with a semicolon. 2/a2581/1 explain extended \N \N host \N 0 show databases +3 MS SQL Server 2008 SQL Server \nCREATE DATABASE db_#databaseName#;\nGO\n\nUSE db_#databaseName#;\nGO\n\nCREATE LOGIN user_#databaseName#\nWITH PASSWORD = '#databaseName#';\nGO\n\nCREATE USER user_#databaseName#;\nGO\n\nGRANT \nCREATE TABLE,\nCREATE TYPE,\nCREATE VIEW,\nCREATE PROCEDURE,\nCREATE FUNCTION,\nCREATE FULLTEXT CATALOG,\nEXECUTE,\nDELETE,\nINSERT,\nREFERENCES,\nSELECT,\nSHOWPLAN,\nUPDATE\nTO user_#databaseName#;\nGO\n\n\n\nALTER LOGIN user_#databaseName# WITH DEFAULT_DATABASE=db_#databaseName#;\nGO\n\nGRANT ALTER ON SCHEMA::dbo TO user_#databaseName#;\nGO\n\nuse master;\n\n net.sourceforge.jtds.jdbc.Driver exec dbo.clearDBUsers 'db_#databaseName#';\nGO\ndrop database db_#databaseName#;\nGO\ndrop login user_#databaseName#;\n \N GO SQL Server supports multiple statements in a batch separated by semicolons. Separate statement batches with a line consisting of a single GO command, as needed. 3/1fa93/1 \nSET SHOWPLAN_XML ON;\nGO\n \nGO\nSET SHOWPLAN_XML OFF \n<?xml version="1.0" encoding="utf-8"?>\n<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"\n xmlns:msxsl="urn:schemas-microsoft-com:xslt"\n xmlns:s="http://schemas.microsoft.com/sqlserver/2004/07/showplan"\n exclude-result-prefixes="msxsl s xsl">\n <xsl:output method="html" indent="no" omit-xml-declaration="yes" />\n\n <!-- Disable built-in recursive processing templates -->\n <xsl:template match="*|/|text()|@*" mode="NodeLabel2" />\n <xsl:template match="*|/|text()|@*" mode="ToolTipDescription" />\n <xsl:template match="*|/|text()|@*" mode="ToolTipDetails" />\n\n <!-- Default template -->\n <xsl:template match="/">\n <xsl:apply-templates select="s:ShowPlanXML" />\n </xsl:template>\n\n <!-- Outermost div that contains all statement plans. -->\n <xsl:template match="s:ShowPlanXML">\n <div class="qp-root">\n <xsl:apply-templates select="s:BatchSequence/s:Batch/s:Statements/s:StmtSimple" /> \n </div>\n </xsl:template>\n \n <!-- Matches a branch in the query plan (either an operation or a statement) -->\n <xsl:template match="s:RelOp|s:StmtSimple">\n <div class="qp-tr">\n <div>\n <div class="qp-node">\n <xsl:apply-templates select="." mode="NodeIcon" />\n <xsl:apply-templates select="." mode="NodeLabel" />\n <xsl:apply-templates select="." mode="NodeLabel2" />\n <xsl:apply-templates select="." mode="NodeCostLabel" />\n <xsl:call-template name="ToolTip" />\n </div>\n </div>\n <div><xsl:apply-templates select="*/s:RelOp" /></div>\n </div>\n </xsl:template>\n\n <!-- Writes the tool tip -->\n <xsl:template name="ToolTip">\n <div class="qp-tt">\n <div class="qp-tt-header"><xsl:value-of select="@PhysicalOp | @StatementType" /></div>\n <div><xsl:apply-templates select="." mode="ToolTipDescription" /></div>\n <xsl:call-template name="ToolTipGrid" />\n <xsl:apply-templates select="* | @* | */* | */@*" mode="ToolTipDetails" />\n </div>\n </xsl:template>\n\n <!-- Writes the grid of node properties to the tool tip -->\n <xsl:template name="ToolTipGrid">\n <table>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="s:QueryPlan/@CachedPlanSize" />\n <xsl:with-param name="Label">Cached plan size</xsl:with-param>\n <xsl:with-param name="Value" select="concat(s:QueryPlan/@CachedPlanSize, ' B')" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Physical Operation</xsl:with-param>\n <xsl:with-param name="Value" select="@PhysicalOp" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Logical Operation</xsl:with-param>\n <xsl:with-param name="Value" select="@LogicalOp" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Actual Number of Rows</xsl:with-param>\n <xsl:with-param name="Value" select="s:RunTimeInformation/s:RunTimeCountersPerThread/@ActualRows" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateIO" />\n <xsl:with-param name="Label">Estimated I/O Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@EstimateIO" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateCPU" />\n <xsl:with-param name="Label">Estimated CPU Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@EstimateCPU" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <!-- TODO: Estimated Number of Executions -->\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Number of Executions</xsl:with-param>\n <xsl:with-param name="Value" select="s:RunTimeInformation/s:RunTimeCountersPerThread/@ActualExecutions" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Degree of Parallelism</xsl:with-param>\n <xsl:with-param name="Value" select="s:QueryPlan/@DegreeOfParallelism" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Memory Grant</xsl:with-param>\n <xsl:with-param name="Value" select="s:QueryPlan/@MemoryGrant" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@EstimateIO | @EstimateCPU" />\n <xsl:with-param name="Label">Estimated Operator Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:variable name="EstimatedOperatorCost">\n <xsl:call-template name="EstimatedOperatorCost" />\n </xsl:variable>\n <xsl:variable name="TotalCost">\n <xsl:value-of select="ancestor::s:StmtSimple/@StatementSubTreeCost" />\n </xsl:variable>\n \n <xsl:call-template name="round">\n <xsl:with-param name="value" select="$EstimatedOperatorCost" />\n </xsl:call-template>\n (<xsl:value-of select="format-number(number($EstimatedOperatorCost) div number($TotalCost), '0%')" />)\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@StatementSubTreeCost | @EstimatedTotalSubtreeCost" />\n <xsl:with-param name="Label">Estimated Subtree Cost</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:call-template name="round">\n <xsl:with-param name="value" select="@StatementSubTreeCost | @EstimatedTotalSubtreeCost" />\n </xsl:call-template>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Estimated Number of Rows</xsl:with-param>\n <xsl:with-param name="Value" select="@StatementEstRows | @EstimateRows" />\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="@AvgRowSize" />\n <xsl:with-param name="Label">Estimated Row Size</xsl:with-param>\n <xsl:with-param name="Value" select="concat(@AvgRowSize, ' B')" />\n </xsl:call-template>\n <!-- TODO: Actual Rebinds\n TODO: Actual Rewinds -->\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Condition" select="s:IndexScan/@Ordered" />\n <xsl:with-param name="Label">Ordered</xsl:with-param>\n <xsl:with-param name="Value">\n <xsl:choose>\n <xsl:when test="s:IndexScan/@Ordered = 1">True</xsl:when>\n <xsl:otherwise>False</xsl:otherwise>\n </xsl:choose>\n </xsl:with-param>\n </xsl:call-template>\n <xsl:call-template name="ToolTipRow">\n <xsl:with-param name="Label">Node ID</xsl:with-param>\n <xsl:with-param name="Value" select="@NodeId" />\n </xsl:call-template>\n </table>\n </xsl:template>\n\n <!-- Calculates the estimated operator cost. -->\n <xsl:template name="EstimatedOperatorCost">\n <xsl:variable name="EstimateIO">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="@EstimateIO" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:variable name="EstimateCPU">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="@EstimateCPU" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:value-of select="number($EstimateIO) + number($EstimateCPU)" />\n </xsl:template>\n\n <!-- Renders a row in the tool tip details table. -->\n <xsl:template name="ToolTipRow">\n <xsl:param name="Label" />\n <xsl:param name="Value" />\n <xsl:param name="Condition" select="$Value" />\n <xsl:if test="$Condition">\n <tr>\n <th><xsl:value-of select="$Label" /></th>\n <td><xsl:value-of select="$Value" /></td>\n </tr> \n </xsl:if>\n </xsl:template>\n\n <!-- Prints the name of an object. -->\n <xsl:template match="s:Object | s:ColumnReference" mode="ObjectName">\n <xsl:param name="ExcludeDatabaseName" select="false()" />\n <xsl:choose>\n <xsl:when test="$ExcludeDatabaseName">\n <xsl:for-each select="@Table | @Index | @Column | @Alias">\n <xsl:value-of select="." />\n <xsl:if test="position() != last()">.</xsl:if>\n </xsl:for-each>\n </xsl:when>\n <xsl:otherwise>\n <xsl:for-each select="@Database | @Schema | @Table | @Index | @Column | @Alias">\n <xsl:value-of select="." />\n <xsl:if test="position() != last()">.</xsl:if>\n </xsl:for-each>\n </xsl:otherwise>\n </xsl:choose>\n </xsl:template>\n\n <!-- Displays the node cost label. -->\n <xsl:template match="s:RelOp" mode="NodeCostLabel">\n <xsl:variable name="EstimatedOperatorCost"><xsl:call-template name="EstimatedOperatorCost" /></xsl:variable>\n <xsl:variable name="TotalCost"><xsl:value-of select="ancestor::s:StmtSimple/@StatementSubTreeCost" /></xsl:variable>\n <div>Cost: <xsl:value-of select="format-number(number($EstimatedOperatorCost) div number($TotalCost), '0%')" /></div>\n </xsl:template>\n\n <!-- Dont show the node cost for statements. -->\n <xsl:template match="s:StmtSimple" mode="NodeCostLabel" />\n\n <!-- \n ================================\n Tool tip detail sections\n ================================\n The following section contains templates used for writing the detail sections at the bottom of the tool tip,\n for example listing outputs, or information about the object to which an operator applies.\n -->\n\n <xsl:template match="*/s:Object" mode="ToolTipDetails">\n <!-- TODO: Make sure this works all the time -->\n <div class="qp-bold">Object</div>\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:template>\n\n <xsl:template match="s:SetPredicate[s:ScalarOperator/@ScalarString]" mode="ToolTipDetails">\n <div class="qp-bold">Predicate</div>\n <div><xsl:value-of select="s:ScalarOperator/@ScalarString" /></div>\n </xsl:template>\n\n <xsl:template match="s:OutputList[count(s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Output List</div>\n <xsl:for-each select="s:ColumnReference">\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:for-each>\n </xsl:template>\n\n <xsl:template match="s:NestedLoops/s:OuterReferences[count(s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Outer References</div>\n <xsl:for-each select="s:ColumnReference">\n <div><xsl:apply-templates select="." mode="ObjectName" /></div>\n </xsl:for-each>\n </xsl:template>\n\n <xsl:template match="@StatementText" mode="ToolTipDetails">\n <div class="qp-bold">Statement</div>\n <div><xsl:value-of select="." /></div>\n </xsl:template>\n\n <xsl:template match="s:Sort/s:OrderBy[count(s:OrderByColumn/s:ColumnReference) > 0]" mode="ToolTipDetails">\n <div class="qp-bold">Order By</div>\n <xsl:for-each select="s:OrderByColumn">\n <div>\n <xsl:apply-templates select="s:ColumnReference" mode="ObjectName" />\n <xsl:choose>\n <xsl:when test="@Ascending = 1"> Ascending</xsl:when>\n <xsl:otherwise> Descending</xsl:otherwise>\n </xsl:choose>\n </div>\n </xsl:for-each>\n </xsl:template>\n\n <!-- TODO: Seek Predicates -->\n\n <!-- \n ================================\n Node icons\n ================================\n The following templates determine what icon should be shown for a given node\n -->\n\n <!-- Use the logical operation to determine the icon for the "Parallelism" operators. -->\n <xsl:template match="s:RelOp[@PhysicalOp = 'Parallelism']" mode="NodeIcon" priority="1">\n <xsl:element name="div">\n <xsl:attribute name="class">qp-icon-<xsl:value-of select="translate(@LogicalOp, ' ', '')" /></xsl:attribute>\n </xsl:element>\n </xsl:template>\n\n <!-- Use the physical operation to determine icon if it is present. -->\n <xsl:template match="*[@PhysicalOp]" mode="NodeIcon">\n <xsl:element name="div">\n <xsl:attribute name="class">qp-icon-<xsl:value-of select="translate(@PhysicalOp, ' ', '')" /></xsl:attribute>\n </xsl:element>\n </xsl:template>\n \n <!-- Matches all statements. -->\n <xsl:template match="s:StmtSimple" mode="NodeIcon">\n <div class="qp-icon-Statement"></div>\n </xsl:template>\n\n <!-- Fallback template - show the Bitmap icon. -->\n <xsl:template match="*" mode="NodeIcon">\n <div class="qp-icon-Catchall"></div>\n </xsl:template>\n\n <!-- \n ================================\n Node labels\n ================================\n The following section contains templates used to determine the first (main) label for a node.\n -->\n\n <xsl:template match="s:RelOp" mode="NodeLabel">\n <div><xsl:value-of select="@PhysicalOp" /></div>\n </xsl:template>\n\n <xsl:template match="s:StmtSimple" mode="NodeLabel">\n <div><xsl:value-of select="@StatementType" /></div>\n </xsl:template>\n\n <!--\n ================================\n Node alternate labels\n ================================\n The following section contains templates used to determine the second label to be displayed for a node.\n -->\n\n <!-- Display the object for any node that has one -->\n <xsl:template match="*[*/s:Object]" mode="NodeLabel2">\n <xsl:variable name="ObjectName">\n <xsl:apply-templates select="*/s:Object" mode="ObjectName">\n <xsl:with-param name="ExcludeDatabaseName" select="true()" />\n </xsl:apply-templates>\n </xsl:variable>\n <div>\n <xsl:value-of select="substring($ObjectName, 0, 36)" />\n <xsl:if test="string-length($ObjectName) >= 36">…</xsl:if>\n </div>\n </xsl:template>\n\n <!-- Display the logical operation for any node where it is not the same as the physical operation. -->\n <xsl:template match="s:RelOp[@LogicalOp != @PhysicalOp]" mode="NodeLabel2">\n <div>(<xsl:value-of select="@LogicalOp" />)</div>\n </xsl:template>\n\n <!-- Disable the default template -->\n <xsl:template match="*" mode="NodeLabel2" />\n\n <!-- \n ================================\n Tool tip descriptions\n ================================\n The following section contains templates used for writing the description shown in the tool tip.\n -->\n\n <xsl:template match="*[@PhysicalOp = 'Table Insert']" mode="ToolTipDescription">Insert input rows into the table specified in Argument field.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Compute Scalar']" mode="ToolTipDescription">Compute new values from existing values in a row.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Sort']" mode="ToolTipDescription">Sort the input.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Clustered Index Scan']" mode="ToolTipDescription">Scanning a clustered index, entirely or only a range.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Stream Aggregate']" mode="ToolTipDescription">Compute summary values for groups of rows in a suitably sorted stream.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Hash Match']" mode="ToolTipDescription">Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Bitmap']" mode="ToolTipDescription">Bitmap.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Clustered Index Seek']" mode="ToolTipDescription">Scanning a particular range of rows from a clustered index.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Index Seek']" mode="ToolTipDescription">Scan a particular range of rows from a nonclustered index.</xsl:template>\n\n <xsl:template match="*[@PhysicalOp = 'Parallelism' and @LogicalOp='Repartition Streams']" mode="ToolTipDescription">Repartition Streams.</xsl:template>\n <xsl:template match="*[@PhysicalOp = 'Parallelism']" mode="ToolTipDescription">An operation involving parallelism.</xsl:template>\n \n <xsl:template match="*[s:TableScan]" mode="ToolTipDescription">Scan rows from a table.</xsl:template>\n <xsl:template match="*[s:NestedLoops]" mode="ToolTipDescription">For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.</xsl:template>\n <xsl:template match="*[s:Top]" mode="ToolTipDescription">Select the first few rows based on a sort order.</xsl:template>\n\n <!-- \n ================================\n Number handling\n ================================\n The following section contains templates used for handling numbers (scientific notation, rounding etc...)\n -->\n\n <!-- Outputs a number rounded to 7 decimal places - to be used for displaying all numbers.\n This template accepts numbers in scientific notation. -->\n <xsl:template name="round">\n <xsl:param name="value" select="0" />\n <xsl:variable name="number">\n <xsl:call-template name="convertSciToNumString">\n <xsl:with-param name="inputVal" select="$value" />\n </xsl:call-template>\n </xsl:variable>\n <xsl:value-of select="round(number($number) * 10000000) div 10000000" />\n </xsl:template>\n \n <!-- Template for handling of scientific numbers\n See: http://www.orm-designer.com/article/xslt-convert-scientific-notation-to-decimal-number -->\n <xsl:variable name="max-exp">\n <xsl:value-of select="'0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'" />\n </xsl:variable>\n\n <xsl:template name="convertSciToNumString">\n <xsl:param name="inputVal" select="0" />\n\n <xsl:variable name="numInput">\n <xsl:value-of select="translate(string($inputVal),'e','E')" />\n </xsl:variable>\n\n <xsl:choose>\n <xsl:when test="number($numInput) = $numInput">\n <xsl:value-of select="$numInput" />\n </xsl:when> \n <xsl:otherwise>\n <!-- ==== Mantisa ==== -->\n <xsl:variable name="numMantisa">\n <xsl:value-of select="number(substring-before($numInput,'E'))" />\n </xsl:variable>\n\n <!-- ==== Exponent ==== -->\n <xsl:variable name="numExponent">\n <xsl:choose>\n <xsl:when test="contains($numInput,'E+')">\n <xsl:value-of select="substring-after($numInput,'E+')" />\n </xsl:when>\n <xsl:otherwise>\n <xsl:value-of select="substring-after($numInput,'E')" />\n </xsl:otherwise>\n </xsl:choose>\n </xsl:variable>\n\n <!-- ==== Coefficient ==== -->\n <xsl:variable name="numCoefficient">\n <xsl:choose>\n <xsl:when test="$numExponent > 0">\n <xsl:text>1</xsl:text>\n <xsl:value-of select="substring($max-exp, 1, number($numExponent))" />\n </xsl:when>\n <xsl:when test="$numExponent < 0">\n <xsl:text>0.</xsl:text>\n <xsl:value-of select="substring($max-exp, 1, -number($numExponent)-1)" />\n <xsl:text>1</xsl:text>\n </xsl:when>\n <xsl:otherwise>1</xsl:otherwise>\n </xsl:choose>\n </xsl:variable>\n <xsl:value-of select="number($numCoefficient) * number($numMantisa)" />\n </xsl:otherwise>\n </xsl:choose>\n </xsl:template>\n</xsl:stylesheet>\n host //*[@StatementType="COMMIT TRANSACTION"] 0 SELECT name as [schema_name] FROM master..sysdatabases +10 PostgreSQL 8.3 PostgreSQL \n\nCREATE USER user_#databaseName# PASSWORD '#databaseName#';\nCREATE DATABASE db_#databaseName# OWNER user_#databaseName# ENCODING 'UTF8' TEMPLATE db_template;\ncommit;\nALTER USER user_#databaseName# SET statement_timeout = 30000;\nSELECT dblink_connect('#databaseName#', 'dbname=db_#databaseName# hostaddr=127.0.0.1');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE deferred_check (id INT PRIMARY KEY)');\nSELECT dblink_exec('#databaseName#', 'INSERT INTO deferred_check VALUES (1)');\nSELECT dblink_exec('#databaseName#', 'CREATE TABLE db_#databaseName#.public.deferred_#databaseName# (fk INT NOT NULL)');\nSELECT dblink_exec('#databaseName#', 'ALTER TABLE ONLY deferred_#databaseName# ADD CONSTRAINT deferred_#databaseName#_ref FOREIGN KEY (fk) REFERENCES deferred_check(id) DEFERRABLE INITIALLY DEFERRED');\nSELECT dblink_exec('#databaseName#', 'GRANT INSERT ON deferred_#databaseName# TO user_#databaseName#');\nSELECT dblink_disconnect('#databaseName#');\n\n org.postgresql.Driver SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE upper(pg_stat_activity.datname) = 'DB_#databaseName#'; DROP DATABASE db_#databaseName#;DROP USER user_#databaseName#; \N \N \N 10/f9585/1 explain \N \N host \N 0 select datname as schema_name from pg_database \. @@ -34,7 +36,7 @@ COPY db_types (id, full_name, simple_name, setup_script_template, jdbc_class_nam -- Name: db_types_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- -SELECT pg_catalog.setval('db_types_id_seq', 15, true); +SELECT pg_catalog.setval('db_types_id_seq', 16, true); -- @@ -60,31 +62,31 @@ SELECT pg_catalog.setval('hosts_id_seq', 5, true); -- COPY schema_defs (id, db_type_id, short_code, last_used, ddl, current_host_id, md5, statement_separator, owner_id, structure_json) FROM stdin; -589300 9 dcb16 2014-05-11 05:15:24.462 \n\nCREATE TABLE ForgeRock\n\t(`id` int, `productName` varchar(7), `description` varchar(55))\n;\n\t\nINSERT INTO ForgeRock\n\t(`id`, `productName`, `description`)\nVALUES\n\t(1, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),\n\t(2, 'OpenAM', 'Full-featured access management'),\n\t(3, 'OpenDJ', 'Robust LDAP server for Java')\n;\n \N dcb16f2d44703cf35623e5c8650f070e ; \N [{"columns":[{"type":"INT(10)","name":"id"},{"type":"VARCHAR(7)","name":"productName"},{"type":"VARCHAR(55)","name":"description"}],"table_type":"TABLE","table_name":"forgerock"}] -589301 15 35773 2014-05-11 05:24:44.982 create table jsonData (\n id serial primary key,\n data json\n);\n\ninsert into jsonData (data) values (\n'{\n "a": 1,\n "b": 2,\n "c": ["dog","cat","mouse"],\n "d": {\n "x": true\n }\n }\n'::json),\n(\n'{\n "a": 20,\n "b": 40,\n "c": ["fish","cat","rat","hamster"],\n "d": {\n "x": false\n }\n }\n'::json); \N 357738cadc59cd69eeb683e8e0f8fd8d ; \N [{"columns":[{"type":"serial(10)","name":"id"},{"type":"json(2147483647)","name":"data"}],"table_type":"TABLE","table_name":"jsondata"}] -589302 7 44b90 2014-05-11 05:28:27.178 -- this version is using your browser's built-in SQLite\nCREATE TABLE supportContacts\n\t(\n id integer primary key, \n type varchar(20), \n details varchar(30)\n );\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(1, 'Email', 'admin@sqlfiddle.com');\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(2, 'Twitter', '@sqlfiddle'); \N 44b900020923ae6d1b517286d9440347 ; \N \N -589303 5 b5362 2014-05-11 05:30:43.667 CREATE TABLE supportContacts \n\t(\n id integer primary key, \n type varchar(20), \n details varchar(30)\n );\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(1, 'Email', 'admin@sqlfiddle.com');\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(2, 'Twitter', '@sqlfiddle'); \N b5362d09c5119808a6c7409fd00a64b7 ; \N \N -589338 4 c0be1 2014-12-07 21:32:48.736 -- table for our logging\ncreate table log_table\n( message varchar2(200)\n)\n//\n\n-- create package spec\ncreate or replace package pkg_test\nis\n -- define one public procedure\n procedure do;\nend;\n//\n\n-- create package body\ncreate or replace package body pkg_test\nis\n -- private log procedure\n procedure log(p_message in varchar)\n is\n begin\n insert into log_table(message) values (p_message);\n end;\n\n -- private function to return twice the input\n function double(p_number in number)\n return number\n is\n begin\n return 2 * p_number;\n end;\n\n -- public procedure that does. :)\n procedure do\n is\n begin\n log('2 * 12 = ' || double(12));\n end;\n\nend;\n// \N c0be1c42165643ccdd365afbd01e3cf0 // \N [{"columns":[{"type":"VARCHAR2(200)","name":"MESSAGE"}],"table_type":"TABLE","table_name":"LOG_TABLE"}] -589339 6 a7540 2014-12-07 21:53:17.243 \n\nCREATE TABLE ForgeRock\n ([productName] varchar(13), [description] varchar(57))\n;\n \nINSERT INTO ForgeRock\n ([productName], [description])\nVALUES\n ('OpenIDM', 'Platform for building enterprise provisioning solutions'),\n ('OpenAM', 'Full-featured access management'),\n ('OpenDJ', 'Robust LDAP server for Java')\n;\n \N a7540325c43a47db91002a51023b5ec2 ; \N [{"columns":[{"type":"varchar(13)","name":"productName"},{"type":"varchar(57)","name":"description"}],"table_type":"TABLE","table_name":"ForgeRock"}] +1589300 9 dcb16 2014-05-11 05:15:24.462 \n\nCREATE TABLE ForgeRock\n\t(`id` int, `productName` varchar(7), `description` varchar(55))\n;\n\t\nINSERT INTO ForgeRock\n\t(`id`, `productName`, `description`)\nVALUES\n\t(1, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),\n\t(2, 'OpenAM', 'Full-featured access management'),\n\t(3, 'OpenDJ', 'Robust LDAP server for Java')\n;\n \N dcb16f2d44703cf35623e5c8650f070e ; \N [{"columns":[{"type":"INT(10)","name":"id"},{"type":"VARCHAR(7)","name":"productName"},{"type":"VARCHAR(55)","name":"description"}],"table_type":"TABLE","table_name":"forgerock"}] +1589301 15 35773 2014-05-11 05:24:44.982 create table jsonData (\n id serial primary key,\n data json\n);\n\ninsert into jsonData (data) values (\n'{\n "a": 1,\n "b": 2,\n "c": ["dog","cat","mouse"],\n "d": {\n "x": true\n }\n }\n'::json),\n(\n'{\n "a": 20,\n "b": 40,\n "c": ["fish","cat","rat","hamster"],\n "d": {\n "x": false\n }\n }\n'::json); \N 357738cadc59cd69eeb683e8e0f8fd8d ; \N [{"columns":[{"type":"serial(10)","name":"id"},{"type":"json(2147483647)","name":"data"}],"table_type":"TABLE","table_name":"jsondata"}] +1589302 7 44b90 2014-05-11 05:28:27.178 -- this version is using your browser's built-in SQLite\nCREATE TABLE supportContacts\n\t(\n id integer primary key, \n type varchar(20), \n details varchar(30)\n );\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(1, 'Email', 'admin@sqlfiddle.com');\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(2, 'Twitter', '@sqlfiddle'); \N 44b900020923ae6d1b517286d9440347 ; \N \N +1589303 5 b5362 2014-05-11 05:30:43.667 CREATE TABLE supportContacts \n\t(\n id integer primary key, \n type varchar(20), \n details varchar(30)\n );\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(1, 'Email', 'admin@sqlfiddle.com');\n\nINSERT INTO supportContacts\n(id, type, details)\nVALUES\n(2, 'Twitter', '@sqlfiddle'); \N b5362d09c5119808a6c7409fd00a64b7 ; \N \N +1589338 4 c0be1 2014-12-07 21:32:48.736 -- table for our logging\ncreate table log_table\n( message varchar2(200)\n)\n//\n\n-- create package spec\ncreate or replace package pkg_test\nis\n -- define one public procedure\n procedure do;\nend;\n//\n\n-- create package body\ncreate or replace package body pkg_test\nis\n -- private log procedure\n procedure log(p_message in varchar)\n is\n begin\n insert into log_table(message) values (p_message);\n end;\n\n -- private function to return twice the input\n function double(p_number in number)\n return number\n is\n begin\n return 2 * p_number;\n end;\n\n -- public procedure that does. :)\n procedure do\n is\n begin\n log('2 * 12 = ' || double(12));\n end;\n\nend;\n// \N c0be1c42165643ccdd365afbd01e3cf0 // \N [{"columns":[{"type":"VARCHAR2(200)","name":"MESSAGE"}],"table_type":"TABLE","table_name":"LOG_TABLE"}] +1589339 6 a7540 2014-12-07 21:53:17.243 \n\nCREATE TABLE ForgeRock\n ([productName] varchar(13), [description] varchar(57))\n;\n \nINSERT INTO ForgeRock\n ([productName], [description])\nVALUES\n ('OpenIDM', 'Platform for building enterprise provisioning solutions'),\n ('OpenAM', 'Full-featured access management'),\n ('OpenDJ', 'Robust LDAP server for Java')\n;\n \N a7540325c43a47db91002a51023b5ec2 ; \N [{"columns":[{"type":"varchar(13)","name":"productName"},{"type":"varchar(57)","name":"description"}],"table_type":"TABLE","table_name":"ForgeRock"}] \. -- -- Name: schema_defs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- -SELECT pg_catalog.setval('schema_defs_id_seq', 589339, true); +SELECT pg_catalog.setval('schema_defs_id_seq', 1589339, true); -- -- Data for Name: queries; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY queries (schema_def_id, sql, md5, id, statement_separator, author_id) FROM stdin; -589300 -- visit ForgeRock.com for details on the Open Identity Stack\n-- sqlfiddle.com is built with OpenIDM\n\nSELECT\n productName,\n description\nFROM\n ForgeRock 486c85215008690438a43ad3fab9b0f7 1 ; \N -589301 SELECT\n json_extract_path_text(data, 'b') as b,\n json_array_length(data->'c') as numAnimals\nFROM \n jsonData\nWHERE\n json_extract_path_text(data->'d', 'x') = 'true'\n afe7d9b310445307acdfadba54e5c4dd 1 ; \N -589302 select * from supportContacts\norder by id desc 7f9a9b537f2a77076a5be8b98c5d1ae3 1 ; \N -589303 select * from supportContacts\norder by id desc 7f9a9b537f2a77076a5be8b98c5d1ae3 1 ; \N -589338 -- execute the public procedure of the package\nbegin\n pkg_test.do;\nend;\n//\n\n-- dbms_output doesn't work, so we log into a table \n-- (just like in real life) and select all records from it here\nselect *\nfrom log_table\n// 3c3c7a6bd5bd0d4007f2597361943d54 1 // \N -589339 -- visit ForgeRock.com for details on the Open Identity Stack\n-- sqlfiddle.com is built with OpenIDM\n\nSELECT\n productName,\n description\nFROM\n ForgeRock 486c85215008690438a43ad3fab9b0f7 1 ; \N +1589300 -- visit ForgeRock.com for details on the Open Identity Stack\n-- sqlfiddle.com is built with OpenIDM\n\nSELECT\n productName,\n description\nFROM\n ForgeRock 486c85215008690438a43ad3fab9b0f7 1 ; \N +1589301 SELECT\n json_extract_path_text(data, 'b') as b,\n json_array_length(data->'c') as numAnimals\nFROM \n jsonData\nWHERE\n json_extract_path_text(data->'d', 'x') = 'true'\n afe7d9b310445307acdfadba54e5c4dd 1 ; \N +1589302 select * from supportContacts\norder by id desc 7f9a9b537f2a77076a5be8b98c5d1ae3 1 ; \N +1589303 select * from supportContacts\norder by id desc 7f9a9b537f2a77076a5be8b98c5d1ae3 1 ; \N +1589338 -- execute the public procedure of the package\nbegin\n pkg_test.do;\nend;\n//\n\n-- dbms_output doesn't work, so we log into a table \n-- (just like in real life) and select all records from it here\nselect *\nfrom log_table\n// 3c3c7a6bd5bd0d4007f2597361943d54 1 // \N +1589339 -- visit ForgeRock.com for details on the Open Identity Stack\n-- sqlfiddle.com is built with OpenIDM\n\nSELECT\n productName,\n description\nFROM\n ForgeRock 486c85215008690438a43ad3fab9b0f7 1 ; \N \. -- diff --git a/src/main/resources/db/sqlfiddle/migrate.sql b/src/main/resources/db/sqlfiddle/migrate.sql new file mode 100644 index 0000000..ecb3d5b --- /dev/null +++ b/src/main/resources/db/sqlfiddle/migrate.sql @@ -0,0 +1,112 @@ +CREATE TABLE users_old ( + id integer NOT NULL, + email character varying(1000) +); + +CREATE TABLE user_fiddles_old ( + id integer NOT NULL, + user_id integer NOT NULL, + schema_def_id integer NOT NULL, + query_id integer, + last_accessed timestamp without time zone DEFAULT now(), + num_accesses integer DEFAULT 1, + show_in_history smallint DEFAULT 1, + favorite smallint DEFAULT 0 +); + +-- these servers are hosted remotely, and only available if the connection to that remote environment is available +COPY hosts (id, db_type_id, jdbc_url_template, default_database, admin_username, admin_password) FROM stdin; +5 1 jdbc:postgresql://POSTGRESQL91_HOST:5432/#databaseName# postgres postgres password +6 2 jdbc:mysql://MYSQL55_HOST:3306/#databaseName#?allowMultiQueries=true&useLocalTransactionState=true&useUnicode=true&characterEncoding=UTF-8 mysql root password +7 11 jdbc:postgresql://POSTGRESQL84_HOST:5432/#databaseName# postgres postgres password +8 8 jdbc:mysql://MYSQL51_HOST:3306/#databaseName#?allowMultiQueries=true&useLocalTransactionState=true&useUnicode=true&characterEncoding=UTF-8 mysql root password +9 12 jdbc:postgresql://POSTGRESQL92_HOST:5432/#databaseName# postgres postgres password +10 13 jdbc:mysql://MYSQL57_HOST:3306/#databaseName#?allowMultiQueries=true&useLocalTransactionState=true&useUnicode=true&characterEncoding=UTF-8 mysql root password +\. + +SELECT pg_catalog.setval('hosts_id_seq', 11, true); + +-- 1589338 is the same schema / query in both the old and the new versions of the site. Deleting it here so that it won't cause a conflict when imported +DELETE FROM queries WHERE schema_def_id = 1589338; +DELETE FROM schema_defs WHERE id = 1589338; + +CREATE EXTENSION dblink; + +--pg_dump -U postgres -a -t users -t user_fiddles -t query_sets -t schema_defs -t queries sqlfiddle | ssh -i sqlfiddle2pem.pem ubuntu@10.0.0.16 psql -U postgres sqlfiddle +SELECT dblink_connect('sqlfiddle_old', 'dbname=sqlfiddle hostaddr=10.0.0.113 user=postgres'); + +INSERT INTO users_old (id,email) +SELECT id,email from dblink('sqlfiddle_old', 'SELECT id,email FROM users WHERE openid_server = ''https://www.google.com/accounts/o8/ud''') AS u(id integer, email character varying(1000)); + +INSERT INTO user_fiddles_old (id,user_id,schema_def_id,query_id,last_accessed,num_accesses,show_in_history,favorite) +SELECT id,user_id,schema_def_id,query_id,last_accessed,num_accesses,show_in_history,favorite +FROM dblink('sqlfiddle_old', 'SELECT uf.id,uf.user_id,uf.schema_def_id,uf.query_id,uf.last_accessed,uf.num_accesses,uf.show_in_history,uf.favorite FROM user_fiddles uf INNER JOIN users u ON u.id = uf.user_id WHERE u.openid_server = ''https://www.google.com/accounts/o8/ud''') +AS u(id integer,user_id integer,schema_def_id integer,query_id integer,last_accessed timestamp without time zone,num_accesses integer,show_in_history smallint,favorite smallint); + +INSERT INTO schema_defs (id,db_type_id,short_code,last_used,ddl,current_host_id,md5,statement_separator,owner_id,structure_json) +SELECT id,db_type_id,short_code,last_used,ddl,current_host_id,md5,statement_separator,owner_id,structure_json +FROM dblink('sqlfiddle_old', 'SELECT id,db_type_id,short_code,last_used,ddl,current_host_id,md5,statement_separator,owner_id,structure_json FROM schema_defs') +as t( + id integer, + db_type_id integer, + short_code character varying(32), + last_used timestamp without time zone, + ddl text, + current_host_id integer, + md5 character varying(32), + statement_separator character varying(5), + owner_id integer, + structure_json text +); + +INSERT INTO queries (schema_def_id,sql,md5,id,statement_separator,author_id) +SELECT schema_def_id,sql,md5,id,statement_separator,author_id +FROM dblink('sqlfiddle_old', 'SELECT schema_def_id,sql,md5,id,statement_separator,author_id FROM queries') +as t( + schema_def_id integer, + sql text, + md5 character varying(32), + id integer, + statement_separator character varying(5), + author_id integer +); + +INSERT INTO query_sets (id,query_id,schema_def_id,row_count,execution_time,succeeded,sql,execution_plan,error_message,columns_list) +SELECT id,query_id,schema_def_id,row_count,execution_time,succeeded,sql,execution_plan,error_message,columns_list +FROM dblink('sqlfiddle_old', 'SELECT id,query_id,schema_def_id,row_count,execution_time,succeeded,sql,execution_plan,error_message,columns_list FROM query_sets') +as t( + id integer, + query_id integer, + schema_def_id integer, + row_count integer, + execution_time integer, + succeeded smallint, + sql text, + execution_plan text, + error_message text, + columns_list character varying(500) +); + +SELECT dblink_disconnect('sqlfiddle_old'); + + +insert into users (email, id, issuer) +select email, min(id) as id, 'accounts.google.com' as issuer from users_old +group by email; + +insert into user_fiddles (user_id, schema_def_id, query_id, last_accessed, num_accesses, show_in_history, favorite) +select u.id, uf.schema_def_id, uf.query_id, uf.last_accessed, uf.num_accesses, uf.show_in_history, uf.favorite +from users u + inner join user_fiddles_old uf on + u.id = uf.user_id +UNION +select u2.id, uf.schema_def_id, uf.query_id, uf.last_accessed, uf.num_accesses, uf.show_in_history, uf.favorite +from users_old u + inner join user_fiddles_old uf on + u.id = uf.user_id + inner join users u2 on + u.email = u2.email +where not exists (select * from users where users.id = u.id); + +drop table users_old; +drop table user_fiddles_old; diff --git a/src/main/resources/db/sqlfiddle/schema.sql b/src/main/resources/db/sqlfiddle/schema.sql index b5a706b..a12bf1c 100644 --- a/src/main/resources/db/sqlfiddle/schema.sql +++ b/src/main/resources/db/sqlfiddle/schema.sql @@ -250,7 +250,7 @@ ALTER SEQUENCE user_fiddles_id_seq OWNED BY user_fiddles.id; CREATE TABLE users ( id integer NOT NULL, issuer character varying(1000) NOT NULL, - subject character varying(1000) NOT NULL, + subject character varying(1000), email character varying(1000) ); diff --git a/vagrant_scripts/.gitignore b/vagrant_scripts/.gitignore new file mode 100644 index 0000000..0d13a98 --- /dev/null +++ b/vagrant_scripts/.gitignore @@ -0,0 +1 @@ +openvpn
\ No newline at end of file diff --git a/vagrant_scripts/idm_aws.sh b/vagrant_scripts/idm_aws.sh new file mode 100755 index 0000000..0425d1d --- /dev/null +++ b/vagrant_scripts/idm_aws.sh @@ -0,0 +1,16 @@ +#!/bin/bash +if [ -d "./openvpn" ] +then + + apt-get --yes --force-yes install openvpn + cp ./openvpn/* /etc/openvpn + + echo "192.168.199.2 POSTGRESQL84_HOST" >> /etc/hosts + echo "192.168.199.3 POSTGRESQL92_HOST" >> /etc/hosts + echo "192.168.199.4 MYSQL51_HOST" >> /etc/hosts + echo "192.168.199.5 MYSQL55_HOST" >> /etc/hosts + echo "192.168.199.6 MYSQL57_HOST" >> /etc/hosts + echo "192.168.199.7 POSTGRESQL91_HOST" >> /etc/hosts + service openvpn restart + +fi
\ No newline at end of file diff --git a/vagrant_scripts/idm_bootstrap.sh b/vagrant_scripts/idm_bootstrap.sh index c00e12a..ecd9b84 100755 --- a/vagrant_scripts/idm_bootstrap.sh +++ b/vagrant_scripts/idm_bootstrap.sh @@ -13,7 +13,7 @@ echo "10.0.0.15 MYSQL56_HOST" >> /etc/hosts echo "10.0.0.17 ORACLE11G_HOST" >> /etc/hosts echo "10.0.0.17 SQLSERVER2014_HOST" >> /etc/hosts -apt-get --yes --force-yes install openjdk-7-jdk maven npm varnish +apt-get --yes --force-yes install openjdk-7-jdk maven npm varnish s3cmd cp /vagrant/src/main/resources/varnish/default.vcl /etc/varnish cp /vagrant/src/main/resources/varnish/default_varnish /etc/default/varnish ln -s /usr/bin/nodejs /usr/bin/node diff --git a/vagrant_scripts/pg93_bootstrap.sh b/vagrant_scripts/pg93_bootstrap.sh index c1a8f54..0899af1 100755 --- a/vagrant_scripts/pg93_bootstrap.sh +++ b/vagrant_scripts/pg93_bootstrap.sh @@ -32,3 +32,8 @@ psql -U postgres sqlfiddle < /vagrant/src/main/resources/db/sqlfiddle/data.sql # initialize the openidm repository psql -U postgres < /vagrant/src/main/resources/db/openidm/createuser.pgsql psql -U openidm < /vagrant/src/main/resources/db/openidm/openidm.pgsql + +if ping -c 1 10.0.0.113 &> /dev/null +then + psql -U postgres sqlfiddle < /vagrant/src/main/resources/db/sqlfiddle/migrate.sql +fi
\ No newline at end of file |