function DatabaseTasks_sqlsrv::initializeDatabase in Drupal driver for SQL Server and SQL Azure 7

Same name and namespace in other branches

1. 7.3 sqlsrv/install.inc \DatabaseTasks_sqlsrv::initializeDatabase()
2. 7.2 sqlsrv/install.inc \DatabaseTasks_sqlsrv::initializeDatabase()

Make SQL Server Drupal friendly.

File

sqlsrv/install.inc, line 28

Class

DatabaseTasks_sqlsrv

Code

function initializeDatabase() {
  $database = Database::getConnection();
  $database->bypassQueryPreprocess = TRUE;
  try {

    // SUBSTRING() function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'SUBSTRING', N'FN') IS NOT NULL BEGIN DROP FUNCTION SUBSTRING END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [SUBSTRING](@op1 nvarchar(max), @op2 sql_variant, @op3 sql_variant) RETURNS nvarchar(max) AS
BEGIN
  RETURN CAST(SUBSTRING(CAST(@op1 AS nvarchar(max)), CAST(@op2 AS int), CAST(@op3 AS int)) AS nvarchar(max))
END
EOF
);

    // SUBSTRING_INDEX() function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'SUBSTRING_INDEX', N'FN') IS NOT NULL BEGIN DROP FUNCTION SUBSTRING_INDEX END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [SUBSTRING_INDEX](@string varchar(8000), @delimiter char(1), @count int) RETURNS varchar(8000) AS
BEGIN
  DECLARE @result varchar(8000)
  DECLARE @end int
  DECLARE @part int
  SET @end = 0
  SET @part = 0
  IF (@count = 0)
  BEGIN
    SET @result = ''
  END
  ELSE
  BEGIN
    IF (@count < 0)
    BEGIN
      SET @string = REVERSE(@string)
    END
    WHILE (@part < ABS(@count))
    BEGIN
      SET @end = CHARINDEX(@delimiter, @string, @end + 1)
      IF (@end = 0)
      BEGIN
        SET @end = LEN(@string) + 1
        BREAK
      END
      SET @part = @part + 1
    END
    SET @result = SUBSTRING(@string, 1, @end - 1)
    IF (@count < 0)
    BEGIN
      SET @result = REVERSE(@result)
    END
  END
  RETURN @result
END
EOF
);

    // GREATEST() function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'GREATEST', N'FN') IS NOT NULL BEGIN DROP FUNCTION GREATEST END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [GREATEST](@op1 real, @op2 real) RETURNS real AS
BEGIN
  DECLARE @result real
  SET @result = CASE WHEN @op1 >= @op2 THEN @op1 ELSE @op2 END
  RETURN @result
END
EOF
);

    // CONCAT() function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'CONCAT', N'FN') IS NOT NULL BEGIN DROP FUNCTION CONCAT END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [CONCAT](@op1 sql_variant, @op2 sql_variant) RETURNS nvarchar(4000) AS
BEGIN
  DECLARE @result nvarchar(4000)
  SET @result = CAST(@op1 AS nvarchar(4000)) + CAST(@op2 AS nvarchar(4000))
  RETURN @result
END
EOF
);

    // IF(expr1, expr2, expr3) function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'IF', N'FN') IS NOT NULL BEGIN DROP FUNCTION [IF] END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [IF](@expr1 sql_variant, @expr2 sql_variant, @expr3 sql_variant) RETURNS sql_variant AS
BEGIN
  DECLARE @result sql_variant
  SET @result = CASE WHEN CAST(@expr1 AS int) != 0 THEN @expr2 ELSE @expr3 END
  RETURN @result
END
EOF
);

    // MD5(expr1) function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'MD5', N'FN') IS NOT NULL BEGIN DROP FUNCTION [MD5] END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [MD5](@value varchar(255)) RETURNS varchar(32) AS
BEGIN
  RETURN SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)),3,32);
END
EOF
);

    // LPAD(@str, @len, @padstr) function.
    $database
      ->query(<<<EOF
IF OBJECT_ID (N'LPAD', N'FN') IS NOT NULL BEGIN DROP FUNCTION [LPAD] END
EOF
);
    $database
      ->query(<<<EOF
CREATE FUNCTION [dbo].[LPAD](@str nvarchar(max), @len int, @padstr nvarchar(max)) RETURNS nvarchar(4000) AS
BEGIN
  RETURN left(@str + replicate(@padstr,@len),@len);
END
EOF
);
    $database->bypassQueryPreprocess = FALSE;
  } catch (Exception $e) {
    $this
      ->fail(st('Drupal could not be correctly setup with the existing database. Revise any errors.'));
  }
}