SQLServer::getListUsersSqlAction() - Code Metrics - gggeek/db-3v4l - Measure and Improve Code Quality continuously with Scrutinizer

SQLServer::getListUsersSqlAction() A
last analyzed 2020-10-22 13:39 UTC

↳ Parent: SQLServer

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	4
c	1
b	0
f	0
dl	0
loc	7
rs	10
cc	1
nc	1
nop	0

<?php


namespace Db3v4l\Core\DatabaseManager;

use Db3v4l\API\Interfaces\DatabaseManager;
use Db3v4l\API\Interfaces\SqlExecutor\Executor;
use Db3v4l\Core\SqlAction\Command;

class SQLServer extends BaseManager implements DatabaseManager

{
    /**
     * Returns the sql 'action' used to list all available databases
     * @return Command

     * @todo for each database, retrieve the charset/collation

     */
    public function getListDatabasesSqlAction()
    {
        return new Command(
        // the way we create it, the user account is contained in the db
        // @todo add "WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" ?
            "SELECT name AS 'Database' FROM sys.databases ORDER BY name;",
            function ($output, $executor) {
                /** @var Executor $executor */

                return $executor->resultSetToArray($output);
            }
        );
    }

    /**
     * Returns the sql 'action' used to create a new db and accompanying user
     * @param string $dbName

     * @param string $userName

     * @param string $password

     * @param string $charset charset/collation name

     * @return Command

     * @todo prevent sql injection!

     */
    public function getCreateDatabaseSqlAction($dbName, $userName, $password, $charset = null)
    {
        $collation = $this->getCollationName($charset);

        $statements = [
            /// @see https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility
            // When using sqlcmd, we are told _not_ to use GO as query terminator.
            // Also, by default connections are in autocommit mode...
            // And yet, we need a GO to commit the db creation...
            "SET QUOTED_IDENTIFIER ON;",
            "CREATE DATABASE \"$dbName\"" . ($collation !== null ? " COLLATE $collation" : '') . ';'
        ];
        if ($userName != '') {
            $statements[] = "CREATE LOGIN \"$userName\" WITH PASSWORD = '$password', DEFAULT_DATABASE = \"$dbName\", CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;";
            $statements[] = "GO";
            $statements[] = "USE \"$dbName\";";
            $statements[] = "CREATE USER \"$userName\" FOR LOGIN \"$userName\";";
            $statements[] = "ALTER ROLE db_owner ADD MEMBER \"$userName\";";
        }
        return new Command($statements);
    }

    /**
     * Returns the sql 'action' used to drop a db and associated user account
     * @param string $dbName

     * @param string $userName

     * @return Command

     * @param bool $ifExists

     * @todo prevent sql injection!

     */
    public function getDropDatabaseSqlAction($dbName, $userName, $ifExists = false)
    {
        $ifClause = '';
        if ($ifExists) {
            $ifClause = 'IF EXISTS';
        }

        $statements = [
            "SET QUOTED_IDENTIFIER ON;"
        ];
        if ($userName != '') {
            // we assume users are 'local' to each db, as we create them by default
            $statements[] = "USE \"$dbName\";";
            $statements[] = "DROP LOGIN \"$userName\";";
            $statements[] = "DROP USER {$ifClause} \"$userName\";";
            $statements[] = "USE \"master\";";
        }
        $statements[] = "DROP DATABASE {$ifClause} \"$dbName\";";

        return new Command($statements);
    }

    /**
     * Returns the sql 'action' used to list all existing db users
     * @return Command

     */
    public function getListUsersSqlAction()
    {
        return new Command(
            "SELECT name AS 'User' FROM sys.sql_logins ORDER BY name",
            function ($output, $executor) {
                /** @var Executor $executor */

                return $executor->resultSetToArray($output);
            }
        );
    }

    /**

     * @param string $userName

     * @param bool $ifExists

     * @return Command

     * @todo prevent sql injection!

     */
    public function getDropUserSqlAction($userName, $ifExists = false)
    {
        $ifClause = '';
        if ($ifExists) {
            $ifClause = 'IF EXISTS';
        }

        /// @todo if the user is created inside a specific db, this will fail. We need to add a USE DB cmd 1st...
        ///       to find out if a user exists in the current db: SELECT DATABASE_PRINCIPAL_ID('$user');
        return new Command([

            "SET QUOTED_IDENTIFIER ON;",
            "DROP LOGIN \"$userName\";",
            "DROP USER {$ifClause} \"$userName\";"
        ]);
someFunctionCall(
    $firstArgument,
    $secondArgument,
    $thirdArgument
); // Closing parenthesis on a new line.
    }

    /**
     * Returns the sql 'action' used to list all available collations
     * @return Command

     */
    public function getListCollationsSqlAction()
    {
        return new Command(
            'SELECT name AS Collation FROM fn_helpcollations();',
            function ($output, $executor) {
                /** @var Executor $executor */

                return $executor->resultSetToArray($output);
            }
        );
    }

    /**
     * Returns the sql 'action' used to retrieve the db instance version info
     * @return Command

     */
    public function getRetrieveVersionInfoSqlAction()
    {
        return new Command(
            "SELECT @@version",
            function ($output, $executor) {
                /** @var Executor $executor */

                $output = $executor->resultSetToArray($output);
                $line = $output[0];
                preg_match('/Microsoft SQL Server +([^ ]+) +([^ ]+) +/', $line, $matches);
                return $matches[1] . ' ' . $matches[2];
            }
        );
    }

    /**
     * Transform collation name into a supported one
     * @param null|string $charset so far only 'utf8' is supported...

     * @return null|string

     * @todo what shall we accept as valid input, ie. 'generic' charset names ? maybe do 2 passes: known-db-charset => generic => specific for each db ?

     *       see: https://www.iana.org/assignments/character-sets/character-sets.xhtml for IANA names
     */
    protected function getCollationName($charset)
    {
        if ($charset == null) {

            return null;
        }

        $charset = trim(strtolower($charset));

        // accept official iana charset name, but most dbs prefer 'utf8'...
        if ($charset == 'utf-8') {
            $charset = 'utf8';
        }

        if ($charset == 'utf8') {
            if (version_compare(
                str_replace(array('.ga', '.cu'), array('.0', '.'), $this->databaseConfiguration['version']),
                '2019',
                '>=')
someFunctionCall(
    $firstArgument,
    $secondArgument,
    $thirdArgument
); // Closing parenthesis on a new line.
            ) {
                /// @todo allow to set this via configuration
                // default collation for sql server on Linux is SQL_Latin1_General_CP1_CI_AS; we use the UTF8 variant
                $charset = 'Latin1_General_100_CI_AI_SC_UTF8';
            }
        }

        return $charset;
    }
}


1			<?php
2
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing file doc comment Loading history...
3			namespace Db3v4l\Core\DatabaseManager;
4
5			use Db3v4l\API\Interfaces\DatabaseManager;
6			use Db3v4l\API\Interfaces\SqlExecutor\Executor;
7			use Db3v4l\Core\SqlAction\Command;
8
9			class SQLServer extends BaseManager implements DatabaseManager
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing doc comment for class SQLServer Loading history...
10			{
11			/**
12			* Returns the sql 'action' used to list all available databases
13			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history...
14			* @todo for each database, retrieve the charset/collation
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @todo tag indented incorrectly; expected 3 spaces but found 1 Loading history...
15			*/
16			public function getListDatabasesSqlAction()
17			{
18			return new Command(
19			// the way we create it, the user account is contained in the db
20			// @todo add "WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" ?
21			"SELECT name AS 'Database' FROM sys.databases ORDER BY name;",
22			function ($output, $executor) {
23			/** @var Executor $executor */
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The open comment tag must be the only content on the line Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report Missing short description in doc comment Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The close comment tag must be the only content on the line Loading history...
24			return $executor->resultSetToArray($output);
25			}
26			);
27			}
28
29			/**
30			* Returns the sql 'action' used to create a new db and accompanying user
31			* @param string $dbName
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
32			* @param string $userName
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
33			* @param string $password
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
34			* @param string $charset charset/collation name
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Expected 2 spaces after parameter name; 1 found Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
35			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @return cannot be grouped with parameter tags in a doc comment Loading history...
36			* @todo prevent sql injection!
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @todo cannot be grouped with parameter tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @todo tag indented incorrectly; expected 3 spaces but found 1 Loading history...
37			*/
38			public function getCreateDatabaseSqlAction($dbName, $userName, $password, $charset = null)
39			{
40			$collation = $this->getCollationName($charset);
41
42			$statements = [
43			/// @see https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility
44			// When using sqlcmd, we are told _not_ to use GO as query terminator.
45			// Also, by default connections are in autocommit mode...
46			// And yet, we need a GO to commit the db creation...
47			"SET QUOTED_IDENTIFIER ON;",
48			"CREATE DATABASE \"$dbName\"" . ($collation !== null ? " COLLATE $collation" : '') . ';'
49			];
50			if ($userName != '') {
51			$statements[] = "CREATE LOGIN \"$userName\" WITH PASSWORD = '$password', DEFAULT_DATABASE = \"$dbName\", CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;";
52			$statements[] = "GO";
53			$statements[] = "USE \"$dbName\";";
54			$statements[] = "CREATE USER \"$userName\" FOR LOGIN \"$userName\";";
55			$statements[] = "ALTER ROLE db_owner ADD MEMBER \"$userName\";";
56			}
57			return new Command($statements);
58			}
59
60			/**
61			* Returns the sql 'action' used to drop a db and associated user account
62			* @param string $dbName
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
63			* @param string $userName
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
64			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @return cannot be grouped with parameter tags in a doc comment Loading history...
65			* @param bool $ifExists
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tags must be grouped together in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Expected 3 spaces after parameter type; 1 found Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
66			* @todo prevent sql injection!
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @todo cannot be grouped with parameter tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @todo tag indented incorrectly; expected 3 spaces but found 1 Loading history...
67			*/
68			public function getDropDatabaseSqlAction($dbName, $userName, $ifExists = false)
69			{
70			$ifClause = '';
71			if ($ifExists) {
72			$ifClause = 'IF EXISTS';
73			}
74
75			$statements = [
76			"SET QUOTED_IDENTIFIER ON;"
77			];
78			if ($userName != '') {
79			// we assume users are 'local' to each db, as we create them by default
80			$statements[] = "USE \"$dbName\";";
81			$statements[] = "DROP LOGIN \"$userName\";";
82			$statements[] = "DROP USER {$ifClause} \"$userName\";";
83			$statements[] = "USE \"master\";";
84			}
85			$statements[] = "DROP DATABASE {$ifClause} \"$dbName\";";
86
87			return new Command($statements);
88			}
89
90			/**
91			* Returns the sql 'action' used to list all existing db users
92			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history...
93			*/
94			public function getListUsersSqlAction()
95			{
96			return new Command(
97			"SELECT name AS 'User' FROM sys.sql_logins ORDER BY name",
98			function ($output, $executor) {
99			/** @var Executor $executor */
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The open comment tag must be the only content on the line Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report Missing short description in doc comment Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The close comment tag must be the only content on the line Loading history...
100			return $executor->resultSetToArray($output);
101			}
102			);
103			}
104
105			/**
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing short description in doc comment Loading history...
106			* @param string $userName
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
107			* @param bool $ifExists
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Missing parameter comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Expected 3 spaces after parameter type; 1 found Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
108			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @return cannot be grouped with parameter tags in a doc comment Loading history...
109			* @todo prevent sql injection!
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @todo cannot be grouped with parameter tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @todo tag indented incorrectly; expected 3 spaces but found 1 Loading history...
110			*/
111			public function getDropUserSqlAction($userName, $ifExists = false)
112			{
113			$ifClause = '';
114			if ($ifExists) {
115			$ifClause = 'IF EXISTS';
116			}
117
118			/// @todo if the user is created inside a specific db, this will fail. We need to add a USE DB cmd 1st...
119			/// to find out if a user exists in the current db: SELECT DATABASE_PRINCIPAL_ID('$user');
120			return new Command([
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The opening parenthesis of a multi-line function call should be the last content on the line. Loading history...
121			"SET QUOTED_IDENTIFIER ON;",
122			"DROP LOGIN \"$userName\";",
123			"DROP USER {$ifClause} \"$userName\";"
124			]);
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report For multi-line function calls, the closing parenthesis should be on a new line. If a function call spawns multiple lines, the coding standard suggests to move the closing parenthesis to a new line: someFunctionCall( $firstArgument, $secondArgument, $thirdArgument ); // Closing parenthesis on a new line. Loading history...
125			}
126
127			/**
128			* Returns the sql 'action' used to list all available collations
129			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history...
130			*/
131			public function getListCollationsSqlAction()
132			{
133			return new Command(
134			'SELECT name AS Collation FROM fn_helpcollations();',
135			function ($output, $executor) {
136			/** @var Executor $executor */
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The open comment tag must be the only content on the line Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report Missing short description in doc comment Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The close comment tag must be the only content on the line Loading history...
137			return $executor->resultSetToArray($output);
138			}
139			);
140			}
141
142			/**
143			* Returns the sql 'action' used to retrieve the db instance version info
144			* @return Command
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history...
145			*/
146			public function getRetrieveVersionInfoSqlAction()
147			{
148			return new Command(
149			"SELECT @@version",
150			function ($output, $executor) {
151			/** @var Executor $executor */
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The open comment tag must be the only content on the line Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report Missing short description in doc comment Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report The close comment tag must be the only content on the line Loading history...
152			$output = $executor->resultSetToArray($output);
153			$line = $output[0];
154			preg_match('/Microsoft SQL Server +([^ ]+) +([^ ]+) +/', $line, $matches);
155			return $matches[1] . ' ' . $matches[2];
156			}
157			);
158			}
159
160			/**
161			* Transform collation name into a supported one
162			* @param null\|string $charset so far only 'utf8' is supported...
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report There must be exactly one blank line before the tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @param tag indented incorrectly; expected 2 spaces but found 1 Loading history...
163			* @return null\|string
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @return cannot be grouped with parameter tags in a doc comment Loading history...
164			* @todo what shall we accept as valid input, ie. 'generic' charset names ? maybe do 2 passes: known-db-charset => generic => specific for each db ?
			0 ignored issues – show Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag @todo cannot be grouped with parameter tags in a doc comment Loading history... Coding Style introduced 2020-08-13 15:11 UTC by Report Bug Copy Issue Report Tag value for @todo tag indented incorrectly; expected 3 spaces but found 1 Loading history...
165			* see: https://www.iana.org/assignments/character-sets/character-sets.xhtml for IANA names
166			*/
167			protected function getCollationName($charset)
168			{
169			if ($charset == null) {
			0 ignored issues – show Bug introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report It seems like you are loosely comparing `$charset` of type `null\|string` against `null`; this is ambiguous if the string can be empty. Consider using a strict comparison `===` instead. Loading history...
170			return null;
171			}
172
173			$charset = trim(strtolower($charset));
174
175			// accept official iana charset name, but most dbs prefer 'utf8'...
176			if ($charset == 'utf-8') {
177			$charset = 'utf8';
178			}
179
180			if ($charset == 'utf8') {
181			if (version_compare(
182			str_replace(array('.ga', '.cu'), array('.0', '.'), $this->databaseConfiguration['version']),
183			'2019',
184			'>=')
			0 ignored issues – show Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report This line of the multi-line function call does not seem to be indented correctly. Expected `12` spaces, but found `16`. Loading history... Coding Style introduced 2019-12-20 12:42 UTC by Report Bug Copy Issue Report For multi-line function calls, the closing parenthesis should be on a new line. If a function call spawns multiple lines, the coding standard suggests to move the closing parenthesis to a new line: someFunctionCall( $firstArgument, $secondArgument, $thirdArgument ); // Closing parenthesis on a new line. Loading history...
185			) {
186			/// @todo allow to set this via configuration
187			// default collation for sql server on Linux is SQL_Latin1_General_CP1_CI_AS; we use the UTF8 variant
188			$charset = 'Latin1_General_100_CI_AI_SC_UTF8';
189			}
190			}
191
192			return $charset;
193			}
194			}
195

gggeek / db-3v4l

SQLServer::getListUsersSqlAction() A last analyzed 2020-10-22 13:39 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SQLServer::getListUsersSqlAction() A
last analyzed 2020-10-22 13:39 UTC