// created on 21/5/2002 at 20:03
// Npgsql.NpgsqlCommand.cs
//
// Author:
// Francisco Jr. (fxjrlists@yahoo.com.br)
//
// Copyright (C) 2002 The Npgsql Development Team
// npgsql-general@gborg.postgresql.org
// http://gborg.postgresql.org/project/npgsql/projdisplay.php
//
// Permission to use, copy, modify, and distribute this software and its
// documentation for any purpose, without fee, and without a written
// agreement is hereby granted, provided that the above copyright notice
// and this paragraph and the following two paragraphs appear in all copies.
//
// IN NO EVENT SHALL THE NPGSQL DEVELOPMENT TEAM BE LIABLE TO ANY PARTY
// FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
// INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
// DOCUMENTATION, EVEN IF THE NPGSQL DEVELOPMENT TEAM HAS BEEN ADVISED OF
// THE POSSIBILITY OF SUCH DAMAGE.
//
// THE NPGSQL DEVELOPMENT TEAM SPECIFICALLY DISCLAIMS ANY WARRANTIES,
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
// AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
// ON AN "AS IS" BASIS, AND THE NPGSQL DEVELOPMENT TEAM HAS NO OBLIGATIONS
// TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Common.Logging;
using Npgsql.Localization;
using NpgsqlTypes;
namespace Npgsql
{
///
/// Represents a SQL statement or function (stored procedure) to execute
/// against a PostgreSQL database. This class cannot be inherited.
///
#if WITHDESIGN
[System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
#endif
[System.ComponentModel.DesignerCategory("")]
public sealed partial class NpgsqlCommand : DbCommand, ICloneable
{
#region Fields
NpgsqlConnection _connection;
NpgsqlConnector _connector;
NpgsqlTransaction _transaction;
String _commandText;
int? _timeout;
readonly NpgsqlParameterCollection _parameters = new NpgsqlParameterCollection();
String _planName;
PrepareStatus _prepared = PrepareStatus.NotPrepared;
byte[] _preparedCommandText;
NpgsqlRowDescription _currentRowDescription;
long _lastInsertedOid;
// locals about function support so we don`t need to check it everytime a function is called.
bool _functionChecksDone;
bool _functionNeedsColumnListDefinition; // Functions don't return record by default.
UpdateRowSource _updateRowSource = UpdateRowSource.Both;
static readonly Array ParamNameCharTable;
internal Type[] ExpectedTypes { get; set; }
short[] _resultFormatCodes;
internal const int DefaultTimeout = 20;
const string AnonymousPortal = "";
static readonly ILog _log = LogManager.GetCurrentClassLogger();
#endregion Fields
#region Constructors
// Constructors
static NpgsqlCommand()
{
ParamNameCharTable = BuildParameterNameCharacterTable();
}
///
/// Initializes a new instance of the NpgsqlCommand class.
///
public NpgsqlCommand()
: this(String.Empty, null, null)
{
}
///
/// Initializes a new instance of the NpgsqlCommand class with the text of the query.
///
/// The text of the query.
public NpgsqlCommand(String cmdText)
: this(cmdText, null, null)
{
}
///
/// Initializes a new instance of the NpgsqlCommand class with the text of the query and a NpgsqlConnection.
///
/// The text of the query.
/// A NpgsqlConnection that represents the connection to a PostgreSQL server.
public NpgsqlCommand(String cmdText, NpgsqlConnection connection)
: this(cmdText, connection, null)
{
}
///
/// Initializes a new instance of the NpgsqlCommand class with the text of the query, a NpgsqlConnection, and the NpgsqlTransaction.
///
/// The text of the query.
/// A NpgsqlConnection that represents the connection to a PostgreSQL server.
/// The NpgsqlTransaction in which the NpgsqlCommand executes.
public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
_planName = String.Empty;
_commandText = cmdText;
Connection = connection;
CommandType = CommandType.Text;
Transaction = transaction;
}
///
/// Used to execute internal commands.
///
internal NpgsqlCommand(String cmdText, NpgsqlConnector connector, int commandTimeout = 20)
{
_planName = String.Empty;
_commandText = cmdText;
_connector = connector;
CommandTimeout = commandTimeout;
CommandType = CommandType.Text;
// Removed this setting. It was causing too much problem.
// Do internal commands really need different timeout setting?
// Internal commands aren't affected by command timeout value provided by user.
// timeout = 20;
}
#endregion Constructors
#region Public properties
///
/// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
///
/// The Transact-SQL statement or stored procedure to execute. The default is an empty string.
[Category("Data"), DefaultValue("")]
public override String CommandText
{
get { return _commandText; }
set
{
// [TODO] Validate commandtext.
_commandText = value;
UnPrepare();
_functionChecksDone = false;
}
}
///
/// Gets or sets the wait time before terminating the attempt
/// to execute a command and generating an error.
///
/// The time (in seconds) to wait for the command to execute.
/// The default is 20 seconds.
[DefaultValue(DefaultTimeout)]
public override int CommandTimeout
{
get
{
return _timeout ?? (
_connection != null
? _connection.CommandTimeout
: (int)NpgsqlConnectionStringBuilder.GetDefaultValue(Keywords.CommandTimeout)
);
}
set
{
if (value < 0) {
throw new ArgumentOutOfRangeException("value", L10N.CommandTimeoutLessZero);
}
_timeout = value;
}
}
///
/// Gets or sets a value indicating how the
/// CommandText property is to be interpreted.
///
/// One of the CommandType values. The default is CommandType.Text.
[Category("Data"), DefaultValue(CommandType.Text)]
public override CommandType CommandType { get; set; }
///
/// DB connection.
///
protected override DbConnection DbConnection
{
get { return Connection; }
set { Connection = (NpgsqlConnection)value; }
}
///
/// Gets or sets the NpgsqlConnection
/// used by this instance of the NpgsqlCommand.
///
/// The connection to a data source. The default value is a null reference.
[Category("Behavior"), DefaultValue(null)]
public new NpgsqlConnection Connection
{
get { return _connection; }
set
{
if (_connection == value)
{
return;
}
//if (this._transaction != null && this._transaction.Connection == null)
// this._transaction = null;
// All this checking needs revising. It should be simpler.
// This this.Connector != null check was added to remove the nullreferenceexception in case
// of the previous connection has been closed which makes Connector null and so the last check would fail.
// See bug 1000581 for more details.
if (_transaction != null && _connection != null && Connector != null && Connector.Transaction != null)
{
throw new InvalidOperationException(L10N.SetConnectionInTransaction);
}
if (_connection != null) {
_connection.StateChange -= OnConnectionStateChange;
}
_connection = value;
if (_connection != null) {
_connection.StateChange += OnConnectionStateChange;
}
Transaction = null;
if (_connection != null)
{
_connector = _connection.Connector;
_prepared = _connector != null && _connector.AlwaysPrepare ? PrepareStatus.NeedsPrepare : PrepareStatus.NotPrepared;
}
}
}
///
/// Design time visible.
///
public override bool DesignTimeVisible { get; set; }
///
/// Gets or sets how command results are applied to the DataRow
/// when used by the Update
/// method of the DbDataAdapter.
///
/// One of the UpdateRowSource values.
#if WITHDESIGN
[Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
#endif
public override UpdateRowSource UpdatedRowSource
{
get { return _updateRowSource; }
set
{
switch (value)
{
// validate value (required based on base type contract)
case UpdateRowSource.None:
case UpdateRowSource.OutputParameters:
case UpdateRowSource.FirstReturnedRecord:
case UpdateRowSource.Both:
_updateRowSource = value;
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
///
/// Returns oid of inserted row. This is only updated when using executenonQuery and when command inserts just a single row. If table is created without oids, this will always be 0.
///
public Int64 LastInsertedOID
{
get { return _lastInsertedOid; }
}
///
/// Returns whether this query will execute as a prepared (compiled) query.
///
public bool IsPrepared
{
get
{
switch (_prepared)
{
case PrepareStatus.NotPrepared:
return false;
case PrepareStatus.NeedsPrepare:
case PrepareStatus.Prepared:
return true;
default:
throw new ArgumentOutOfRangeException();
}
}
}
#endregion Public properties
#region State management
volatile int _state;
///
/// Gets the current state of the connector
///
internal CommandState State
{
get { return (CommandState)_state; }
set
{
var newState = (int)value;
if (newState == _state)
return;
Interlocked.Exchange(ref _state, newState);
}
}
void OnConnectionStateChange(object sender, StateChangeEventArgs stateChangeEventArgs)
{
switch (stateChangeEventArgs.CurrentState)
{
case ConnectionState.Broken:
case ConnectionState.Closed:
_prepared = PrepareStatus.NotPrepared;
break;
case ConnectionState.Open:
switch (stateChangeEventArgs.OriginalState)
{
case ConnectionState.Closed:
case ConnectionState.Broken:
_prepared = _connector != null && _connector.AlwaysPrepare ? PrepareStatus.NeedsPrepare : PrepareStatus.NotPrepared;
break;
}
break;
case ConnectionState.Connecting:
case ConnectionState.Executing:
case ConnectionState.Fetching:
return;
default:
throw new ArgumentOutOfRangeException();
}
}
#endregion State management
#region Parameters
///
/// Creates a new instance of an DbParameter object.
///
/// An DbParameter object.
protected override DbParameter CreateDbParameter()
{
return CreateParameter();
}
///
/// Creates a new instance of a NpgsqlParameter object.
///
/// A NpgsqlParameter object.
public new NpgsqlParameter CreateParameter()
{
return new NpgsqlParameter();
}
///
/// DB parameter collection.
///
protected override DbParameterCollection DbParameterCollection
{
get { return Parameters; }
}
///
/// Gets the NpgsqlParameterCollection.
///
/// The parameters of the SQL statement or function (stored procedure). The default is an empty collection.
#if WITHDESIGN
[Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
#endif
public new NpgsqlParameterCollection Parameters { get { return _parameters; } }
static Array BuildParameterNameCharacterTable()
{
// Table has lower bound of (int)'.';
var paramNameCharTable = Array.CreateInstance(typeof(byte), new[] { 'z' - '.' + 1 }, new int[] { '.' });
paramNameCharTable.SetValue((byte)'.', (int)'.');
for (int i = '0'; i <= '9'; i++)
{
paramNameCharTable.SetValue((byte)i, i);
}
for (int i = 'A'; i <= 'Z'; i++)
{
paramNameCharTable.SetValue((byte)i, i);
}
paramNameCharTable.SetValue((byte)'_', (int)'_');
for (int i = 'a'; i <= 'z'; i++)
{
paramNameCharTable.SetValue((byte)i, i);
}
return paramNameCharTable;
}
#endregion
#region Prepare
///
/// Creates a prepared version of the command on a PostgreSQL server.
///
public override void Prepare()
{
_log.Debug("Prepare command");
CheckConnectionState();
UnPrepare();
PrepareInternal();
}
void PrepareInternal()
{
// Use the extended query parsing...
_planName = _connector.NextPlanName();
const string portalName = "";
_preparedCommandText = GetCommandText(true);
NpgsqlRowDescription returnRowDesc = null;
ParameterDescriptionResponse parameterDescription = null;
var numInputParameters = 0;
for (var i = 0; i < _parameters.Count; i++)
{
if (_parameters[i].IsInputDirection)
{
numInputParameters++;
}
}
var parameterTypeOIDs = new int[numInputParameters];
for (int i = 0, j = 0; i < _parameters.Count; i++)
{
if (_parameters[i].IsInputDirection)
{
parameterTypeOIDs[j++] = _parameters[i].TypeInfo.NpgsqlDbType == NpgsqlDbType.Unknown ? 0 : _connector.OidToNameMapping[_parameters[i].TypeInfo.Name].OID;
}
}
// Write Parse, Describe, and Sync messages to the wire.
_connector.SendParse(_planName, _preparedCommandText, parameterTypeOIDs);
_connector.SendDescribeStatement(_planName);
_connector.SendSync();
// Tell to mediator what command is being sent.
_connector.Mediator.SetSqlSent(_preparedCommandText, NpgsqlMediator.SQLSentType.Parse);
// Look for ParameterDescriptionResponse, NpgsqlRowDescription in the responses, discarding everything else.
while (true)
{
var msg = _connector.ReadMessage();
if (msg is ReadyForQueryMsg) {
break;
}
var asParameterDescription = msg as ParameterDescriptionResponse;
if (asParameterDescription != null)
{
parameterDescription = asParameterDescription;
continue;
}
var asRowDesc = msg as NpgsqlRowDescription;
if (asRowDesc != null)
{
returnRowDesc = asRowDesc;
continue;
}
var asDisposable = msg as IDisposable;
if (asDisposable != null)
{
asDisposable.Dispose();
}
}
if (parameterDescription != null)
{
if (parameterDescription.TypeOIDs.Length != numInputParameters)
{
throw new InvalidOperationException("Wrong number of parameters. Got " + numInputParameters + " but expected " + parameterDescription.TypeOIDs.Length + ".");
}
for (int i = 0, j = 0; j < numInputParameters; i++)
{
if (_parameters[i].IsInputDirection)
{
_parameters[i].SetTypeOID(parameterDescription.TypeOIDs[j++], _connector.NativeToBackendTypeConverterOptions);
}
}
}
if (returnRowDesc != null)
{
_resultFormatCodes = new short[returnRowDesc.NumFields];
for (var i = 0; i < returnRowDesc.NumFields; i++)
{
var returnRowDescData = returnRowDesc[i];
if (returnRowDescData.TypeInfo != null)
{
// Binary format?
// PG always defaults to text encoding. We can fix up the row description
// here based on support for binary encoding. Once this is done,
// there is no need to request another row description after Bind.
returnRowDescData.FormatCode = returnRowDescData.TypeInfo.SupportsBinaryBackendData ? FormatCode.Binary : FormatCode.Text;
_resultFormatCodes[i] = (short)returnRowDescData.FormatCode;
}
else
{
// Text format (default).
_resultFormatCodes[i] = (short)FormatCode.Text;
}
}
}
else
{
_resultFormatCodes = new short[] { 0 };
}
// Save the row description for use with all future Executes.
_currentRowDescription = returnRowDesc;
_prepared = PrepareStatus.Prepared;
}
void UnPrepare()
{
if (_prepared == PrepareStatus.Prepared)
{
_connector.ExecuteBlind("DEALLOCATE " + _planName);
_currentRowDescription = null;
_prepared = PrepareStatus.NeedsPrepare;
}
_preparedCommandText = null;
}
#endregion Prepare
#region Query preparation
///
/// This method substitutes the Parameters, if exist, in the command
/// to their actual values.
/// The parameter name format is :ParameterName.
///
/// A version of CommandText with the Parameters inserted.
internal byte[] GetCommandText()
{
return string.IsNullOrEmpty(_planName) ? GetCommandText(false) : GetExecuteCommandText();
}
bool CheckFunctionNeedsColumnDefinitionList()
{
// If and only if a function returns "record" and has no OUT ("o" in proargmodes), INOUT ("b"), or TABLE
// ("t") return arguments to characterize the result columns, we must provide a column definition list.
// See http://pgfoundry.org/forum/forum.php?thread_id=1075&forum_id=519
// We would use our Output and InputOutput parameters to construct that column definition list. If we have
// no such parameters, skip the check: we could only construct "AS ()", which yields a syntax error.
// Updated after 0.99.3 to support the optional existence of a name qualifying schema and allow for case insensitivity
// when the schema or procedure name do not contain a quote.
// The hard-coded schema name 'public' was replaced with code that uses schema as a qualifier, only if it is provided.
String returnRecordQuery;
var parameterTypes = new StringBuilder("");
// Process parameters
var seenDef = false;
foreach (NpgsqlParameter p in Parameters)
{
if (p.IsInputDirection)
{
parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID.ToString() + " ");
}
if ((p.Direction == ParameterDirection.Output) || (p.Direction == ParameterDirection.InputOutput))
{
seenDef = true;
}
}
if (!seenDef)
{
return false;
}
// Process schema name.
var schemaName = String.Empty;
var procedureName = String.Empty;
var fullName = CommandText.Split('.');
const string predicate = "prorettype = ( select oid from pg_type where typname = 'record' ) "
+ "and proargtypes=:proargtypes and proname=:proname "
// proargmodes && array['o','b','t']::"char"[] performs just as well, but it requires PostgreSQL 8.2.
+ "and ('o' = any (proargmodes) OR 'b' = any (proargmodes) OR 't' = any (proargmodes)) is not true";
if (fullName.Length == 2)
{
returnRecordQuery =
"select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where " + predicate + " and n.nspname=:nspname";
schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
}
else
{
// Instead of defaulting don't use the nspname, as an alternative, query pg_proc and pg_namespace to try and determine the nspname.
// schemaName = "public"; // This was removed after build 0.99.3 because the assumption that a function is in public is often incorrect.
returnRecordQuery = "select count(*) > 0 from pg_proc p where " + predicate;
procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower();
}
bool ret;
using (var c = new NpgsqlCommand(returnRecordQuery, Connection))
{
c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Oidvector));
c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Name));
c.Parameters[0].Value = parameterTypes.ToString();
c.Parameters[1].Value = procedureName;
if (!string.IsNullOrEmpty(schemaName))
{
c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Name));
c.Parameters[2].Value = schemaName;
}
ret = (bool)c.ExecuteScalar();
}
return ret;
}
void AddFunctionColumnListSupport(Stream st)
{
var isFirstOutputOrInputOutput = true;
st.WriteString(" AS (");
for (var i = 0; i < Parameters.Count; i++)
{
var p = Parameters[i];
switch (p.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
if (isFirstOutputOrInputOutput)
{
isFirstOutputOrInputOutput = false;
}
else
{
st.WriteString(", ");
}
st.WriteString(p.CleanName);
st.WriteByte((byte) ASCIIBytes.Space);
st.WriteString(p.TypeInfo.Name);
break;
}
}
st.WriteByte((byte)ASCIIBytes.ParenRight);
}
///
/// Process this._commandText, trimming each distinct command and substituting paramater
/// tokens.
///
///
/// UTF8 encoded command ready to be sent to the backend.
byte[] GetCommandText(bool prepare)
{
var commandBuilder = new MemoryStream();
if (CommandType == CommandType.TableDirect)
{
foreach (var table in _commandText.Split(';'))
{
if (table.Trim().Length == 0)
{
continue;
}
commandBuilder
.WriteString("SELECT * FROM ")
.WriteString(table)
.WriteString(";");
}
}
else if (CommandType == CommandType.StoredProcedure)
{
if (!prepare && !_functionChecksDone)
{
_functionNeedsColumnListDefinition = Parameters.Count != 0 && CheckFunctionNeedsColumnDefinitionList();
_functionChecksDone = true;
}
commandBuilder.WriteString("SELECT * FROM ");
if (_commandText.TrimEnd().EndsWith(")"))
{
if (!AppendCommandReplacingParameterValues(commandBuilder, _commandText, prepare, false))
{
throw new NotSupportedException("Multiple queries not supported for stored procedures");
}
}
else
{
commandBuilder
.WriteString(_commandText)
.WriteByte((byte)ASCIIBytes.ParenLeft);
if (prepare)
{
AppendParameterPlaceHolders(commandBuilder);
}
else
{
AppendParameterValues(commandBuilder);
}
commandBuilder.WriteByte((byte)ASCIIBytes.ParenRight);
}
if (!prepare && _functionNeedsColumnListDefinition)
{
AddFunctionColumnListSupport(commandBuilder);
}
}
else
{
if (!AppendCommandReplacingParameterValues(commandBuilder, _commandText, prepare, !prepare))
{
throw new NotSupportedException("Multiple queries not supported for prepared statements");
}
}
return commandBuilder.ToArray();
}
void AppendParameterPlaceHolders(Stream dest)
{
var first = true;
for (var i = 0; i < _parameters.Count; i++)
{
var parameter = _parameters[i];
if (parameter.IsInputDirection)
{
if (first)
{
first = false;
}
else
{
dest.WriteString(", ");
}
AppendParameterPlaceHolder(dest, parameter, i + 1);
}
}
}
void AppendParameterPlaceHolder(Stream dest, NpgsqlParameter parameter, int paramNumber)
{
dest.WriteByte((byte)ASCIIBytes.ParenLeft);
if (parameter.UseCast && parameter.Size > 0)
{
dest.WriteString("${0}::{1}", paramNumber, parameter.TypeInfo.GetCastName(parameter.Size));
}
else
{
dest.WriteString("$" + paramNumber);
}
dest.WriteByte((byte)ASCIIBytes.ParenRight);
}
void AppendParameterValues(Stream dest)
{
var first = true;
for (var i = 0; i < _parameters.Count; i++)
{
var parameter = _parameters[i];
if (parameter.IsInputDirection)
{
if (first)
{
first = false;
}
else
{
dest.WriteString(", ");
}
AppendParameterValue(dest, parameter);
}
}
}
void AppendParameterValue(Stream dest, NpgsqlParameter parameter)
{
var serialised = parameter.TypeInfo.ConvertToBackend(parameter.NpgsqlValue, false, Connector.NativeToBackendTypeConverterOptions);
// Add parentheses wrapping parameter value before the type cast to avoid problems with Int16.MinValue, Int32.MinValue and Int64.MinValue
// See bug #1010543
// Check if this parenthesis can be collapsed with the previous one about the array support. This way, we could use
// only one pair of parentheses for the two purposes instead of two pairs.
dest.WriteByte((byte)ASCIIBytes.ParenLeft);
dest.WriteByte((byte) ASCIIBytes.ParenLeft);
dest.WriteBytes(serialised);
dest.WriteByte((byte)ASCIIBytes.ParenRight);
if (parameter.UseCast)
{
dest.WriteString("::{0}", parameter.TypeInfo.GetCastName(parameter.Size));
}
dest.WriteByte((byte)ASCIIBytes.ParenRight);
}
static bool IsParamNameChar(char ch)
{
if (ch < '.' || ch > 'z')
{
return false;
}
return ((byte)ParamNameCharTable.GetValue(ch) != 0);
}
static bool IsLetter(char ch)
{
return 'a' <= ch && ch <= 'z' || 'A' <= ch && ch <= 'Z';
}
static bool IsIdentifierStart(char ch)
{
return 'a' <= ch && ch <= 'z' || 'A' <= ch && ch <= 'Z' || ch == '_' || 128 <= ch && ch <= 255;
}
static bool IsDollarTagIdentifier(char ch)
{
return 'a' <= ch && ch <= 'z' || 'A' <= ch && ch <= 'Z' || '0' <= ch && ch <= '9' || ch == '_' || 128 <= ch && ch <= 255;
}
static bool IsIdentifier(char ch)
{
return 'a' <= ch && ch <= 'z' || 'A' <= ch && ch <= 'Z' || '0' <= ch && ch <= '9' || ch == '_' || ch == '$' || 128 <= ch && ch <= 255;
}
///
/// Append a region of a source command text to an output command, performing parameter token
/// substitutions.
///
/// Stream to which to append output.
/// Command text.
///
///
/// false if the query has multiple statements which are not allowed
bool AppendCommandReplacingParameterValues(Stream dest, string src, bool prepare, bool allowMultipleStatements)
{
var standardConformantStrings = _connection != null && _connection.Connector != null && _connection.Connector.IsConnected ? _connection.UseConformantStrings : true;
var currCharOfs = 0;
var end = src.Length;
var ch = '\0';
var lastChar = '\0';
var dollarTagStart = 0;
var dollarTagEnd = 0;
var currTokenBeg = 0;
var blockCommentLevel = 0;
Dictionary paramOrdinalMap = null;
if (prepare)
{
paramOrdinalMap = new Dictionary();
for (int i = 0, cnt = 0; i < _parameters.Count; i++)
{
if (_parameters[i].IsInputDirection)
{
paramOrdinalMap[_parameters[i]] = ++cnt;
}
}
}
if (allowMultipleStatements && _parameters.Count == 0)
{
dest.WriteString(src);
return true;
}
None:
if (currCharOfs >= end)
{
goto Finish;
}
lastChar = ch;
ch = src[currCharOfs++];
NoneContinue:
for (; ; lastChar = ch, ch = src[currCharOfs++])
{
switch (ch)
{
case '/': goto BlockCommentBegin;
case '-': goto LineCommentBegin;
case '\'': if (standardConformantStrings) goto Quoted; else goto Escaped;
case '$': if (!IsIdentifier(lastChar)) goto DollarQuotedStart; else break;
case '"': goto DoubleQuoted;
case ':': if (lastChar != ':') goto ParamStart; else break;
case '@': if (lastChar != '@') goto ParamStart; else break;
case ';': if (!allowMultipleStatements) goto SemiColon; else break;
case 'e':
case 'E': if (!IsLetter(lastChar)) goto EscapedStart; else break;
}
if (currCharOfs >= end)
{
goto Finish;
}
}
ParamStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = src[currCharOfs];
if (IsParamNameChar(ch))
{
if (currCharOfs - 1 > currTokenBeg)
{
dest.WriteString(src.Substring(currTokenBeg, currCharOfs - 1 - currTokenBeg));
}
currTokenBeg = currCharOfs++ - 1;
goto Param;
}
else
{
currCharOfs++;
goto NoneContinue;
}
}
goto Finish;
Param:
// We have already at least one character of the param name
for (; ; )
{
lastChar = ch;
if (currCharOfs >= end || !IsParamNameChar(ch = src[currCharOfs]))
{
var paramName = src.Substring(currTokenBeg, currCharOfs - currTokenBeg);
NpgsqlParameter parameter;
if (_parameters.TryGetValue(paramName, out parameter))
{
if (parameter.IsInputDirection)
{
if (prepare)
{
AppendParameterPlaceHolder(dest, parameter, paramOrdinalMap[parameter]);
}
else
{
AppendParameterValue(dest, parameter);
}
currTokenBeg = currCharOfs;
}
}
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
else
{
currCharOfs++;
}
}
Quoted:
while (currCharOfs < end)
{
if (src[currCharOfs++] == '\'')
{
ch = '\0';
goto None;
}
}
goto Finish;
DoubleQuoted:
while (currCharOfs < end)
{
if (src[currCharOfs++] == '"')
{
ch = '\0';
goto None;
}
}
goto Finish;
EscapedStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = src[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
goto NoneContinue;
}
goto Finish;
Escaped:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '\'')
{
goto MaybeConcatenatedEscaped;
}
if (ch == '\\')
{
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
}
}
goto Finish;
MaybeConcatenatedEscaped:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
if (ch != ' ' && ch != '\t' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscaped2:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
if (ch == '-')
{
if (currCharOfs >= end)
{
goto Finish;
}
ch = src[currCharOfs++];
if (ch == '-')
{
goto MaybeConcatenatedEscapeAfterComment;
}
lastChar = '\0';
goto NoneContinue;
}
if (ch != ' ' && ch != '\t' && ch != '\n' & ch != '\r' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscapeAfterComment:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
}
goto Finish;
DollarQuotedStart:
if (currCharOfs < end)
{
ch = src[currCharOfs];
if (ch == '$')
{
// Empty tag
dollarTagStart = dollarTagEnd = currCharOfs;
currCharOfs++;
goto DollarQuoted;
}
if (IsIdentifierStart(ch))
{
dollarTagStart = currCharOfs;
currCharOfs++;
goto DollarQuotedInFirstDelim;
}
lastChar = '$';
currCharOfs++;
goto NoneContinue;
}
goto Finish;
DollarQuotedInFirstDelim:
while (currCharOfs < end)
{
lastChar = ch;
ch = src[currCharOfs++];
if (ch == '$')
{
dollarTagEnd = currCharOfs - 1;
goto DollarQuoted;
}
if (!IsDollarTagIdentifier(ch))
{
goto NoneContinue;
}
}
goto Finish;
DollarQuoted:
{
var tag = src.Substring(dollarTagStart - 1, dollarTagEnd - dollarTagStart + 2);
var pos = src.IndexOf(tag, dollarTagEnd + 1); // Not linear time complexity, but that's probably not a problem, since PostgreSQL backend's isn't either
if (pos == -1)
{
currCharOfs = end;
goto Finish;
}
currCharOfs = pos + dollarTagEnd - dollarTagStart + 2;
ch = '\0';
goto None;
}
LineCommentBegin:
if (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '-')
{
goto LineComment;
}
lastChar = '\0';
goto NoneContinue;
}
goto Finish;
LineComment:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto None;
}
}
goto Finish;
BlockCommentBegin:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '*')
{
blockCommentLevel++;
goto BlockComment;
}
if (ch != '/')
{
if (blockCommentLevel > 0)
{
goto BlockComment;
}
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
BlockComment:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '*')
{
goto BlockCommentEnd;
}
if (ch == '/')
{
goto BlockCommentBegin;
}
}
goto Finish;
BlockCommentEnd:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch == '/')
{
if (--blockCommentLevel > 0)
{
goto BlockComment;
}
goto None;
}
if (ch != '*')
{
goto BlockComment;
}
}
goto Finish;
SemiColon:
while (currCharOfs < end)
{
ch = src[currCharOfs++];
if (ch != ' ' && ch != '\t' && ch != '\n' & ch != '\r' && ch != '\f') // We don't check for comments after the last ; yet
{
return false;
}
}
// implicit goto Finish
Finish:
dest.WriteString(src.Substring(currTokenBeg, end - currTokenBeg));
return true;
}
byte[] GetExecuteCommandText()
{
var result = new MemoryStream();
result.WriteString("EXECUTE {0}", _planName);
if (_parameters.Count != 0)
{
result.WriteByte((byte)ASCIIBytes.ParenLeft);
for (var i = 0; i < Parameters.Count; i++)
{
var p = Parameters[i];
if (i > 0)
{
result.WriteByte((byte)ASCIIBytes.Comma);
}
// Add parentheses wrapping parameter value before the type cast to avoid problems with Int16.MinValue, Int32.MinValue and Int64.MinValue
// See bug #1010543
result.WriteByte((byte)ASCIIBytes.ParenLeft);
byte[] serialization;
serialization = p.TypeInfo.ConvertToBackend(p.NpgsqlValue, false, Connector.NativeToBackendTypeConverterOptions);
result.WriteBytes(serialization);
result.WriteByte((byte)ASCIIBytes.ParenRight);
if (p.UseCast)
{
result.WriteString(string.Format("::{0}", p.TypeInfo.GetCastName(p.Size)));
}
}
result.WriteByte((byte)ASCIIBytes.ParenRight);
}
return result.ToArray();
}
#endregion Query preparation
#region Execute Non Query
///
/// Executes a SQL statement against the connection and returns the number of rows affected.
///
/// The number of rows affected if known; -1 otherwise.
public override int ExecuteNonQuery()
{
return ExecuteNonQueryInternal();
}
///
/// Asynchronous version of
///
/// The token to monitor for cancellation requests.
/// A task representing the asynchronous operation, with the number of rows affected if known; -1 otherwise.
#if NET45
public override async Task ExecuteNonQueryAsync(CancellationToken cancellationToken)
#else
public async Task ExecuteNonQueryAsync(CancellationToken cancellationToken)
#endif
{
cancellationToken.ThrowIfCancellationRequested();
cancellationToken.Register(Cancel);
try
{
return await ExecuteNonQueryInternalAsync();
}
catch (NpgsqlException e)
{
if (e.Code == "57014")
throw new TaskCanceledException(e.Message);
throw;
}
}
#if !NET45
public Task ExecuteNonQueryAsync()
{
return ExecuteNonQueryAsync(CancellationToken.None);
}
#endif
#if NET45
[MethodImpl(MethodImplOptions.AggressiveInlining)]
#endif
[GenerateAsync]
int ExecuteNonQueryInternal()
{
_log.Debug("ExecuteNonQuery");
//We treat this as a simple wrapper for calling ExecuteReader() and then
//update the records affected count at every call to NextResult();
int? ret = null;
using (var rdr = GetReader(CommandBehavior.SequentialAccess))
{
do
{
var thisRecord = rdr.RecordsAffected;
if (thisRecord != -1)
{
ret = (ret ?? 0) + thisRecord;
}
_lastInsertedOid = rdr.LastInsertedOID ?? _lastInsertedOid;
}
while (rdr.NextResult());
}
return ret ?? -1;
}
#endregion Execute Non Query
#region Execute Scalar
///
/// Executes the query, and returns the first column of the first row
/// in the result set returned by the query. Extra columns or rows are ignored.
///
/// The first column of the first row in the result set,
/// or a null reference if the result set is empty.
public override Object ExecuteScalar()
{
return ExecuteScalarInternal();
}
///
/// Asynchronous version of
///
/// The token to monitor for cancellation requests.
/// A task representing the asynchronous operation, with the first column of the
/// first row in the result set, or a null reference if the result set is empty.
#if NET45
public override async Task