TableDumper::where() - Code Metrics - Inspection of "Scrutinizer Auto-Fixes" - Y0lk/sqldumper - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 3d1041...cba42a )

by Gabriel

created 2020-02-01 23:29 UTC

TableDumper::where() A

↳ Parent: TableDumper

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	5
ccs	3
cts	3
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	1
crap	1

<?php
namespace Y0lk\SQLDumper;

use PDO;

/**
 * A TableDumper instance is used to dump a single Table, allowing you to specify certain dump options for this table only
 *
 * @author Gabriel Jean <[email protected]>
 */
class TableDumper {

    /**
     * @var Table   Table instance related to this dumper
     */
    protected $table;

    /**
     * @var boolean Specifies whether to include the table's structure (CREATE statement)
     */
    protected $withStructure = true;

    /**
     * @var boolean Specifies whether to include the table's data (INSERT statements)
     */
    protected $withData = true;

    /**
     * @var boolean Specifies whether to include a DROP TABLE statement
     */
    protected $withDrop = true;

    /**
     * @var string WHERE parameters written as a regular SQL string to select specific data from this table
     */
    protected $where = '';


    /**
     * @param Table Table this dumper will be used for
     */
    public function __construct(Table $table)
    {
        $this->table = $table;
    }

    /**
     * Specifies whether to include the table's structure (CREATE statement)
     * 
     * @param  boolean  $withStructure  TRUE to include structure, FALSE otherwise
     * 
     * @return TableDumper  Returns the TableDumper instance
     */
    public function withStructure(bool $withStructure = true): TableDumper
    {
        $this->withStructure = $withStructure;
        return $this;
    }

    /**
     * Specifies whether to include the table's data (INSERT statements)
     * 
     * @param  boolean  $withData   TRUE to include data, FALSE otherwise
     * 
     * @return TableDumper  Returns the TableDumper instance
     */
    public function withData(bool $withData = true): TableDumper
    {
        $this->withData = $withData;
        return $this;
    }

    /**
     * Specifies whether to include a DROP TABLE statement
     * 
     * @param  boolean  $withDrop   TRUE to include DROP statement, FALSE otherwise
     * 
     * @return TableDumper  Returns the TableDumper instance
     */
    public function withDrop(bool $withDrop = true): TableDumper
    {
        $this->withDrop = $withDrop;
        return $this;
    }

    /**
     * Returns whether or not this dumper will include the table's structure
     * 
     * @return boolean Returns TRUE if it includes it, FALSE otherwise
     */
    public function hasStructure(): bool
    {
        return $this->withStructure;
    }


    /**
     * Returns whether or not this dumper will include the table's data
     * 
     * @return boolean Returns TRUE if it includes it, FALSE otherwise
     */
    public function hasData(): bool
    {
        return $this->withData;
    }


    /**
     * Returns whether or not this dumper will include the table's DROP statement
     * 
     * @return boolean Returns TRUE if it includes it, FALSE otherwise
     */
    public function hasDrop(): bool
    {
        return $this->withDrop;
    }


    /**
     * Add WHERE parameters to use when dumping the data 
     * 
     * @param  string   $where_string   SQL string that you would normally write after WHERE keyowrd
     * 
     * @return TableDumper  Returns the TableDumper instance
     */
    public function where(string $where_string): TableDumper
    {
        $this->where = $where_string;
        return $this;
    }

    /**
     * Get the where param string
     * @return string Returns the WHERE param string
     */
    public function getWhere(): string
    {
        return $this->where;
    }

    /**
     * Get the Table related to this dumper
     * 
     * @return Table    Returns the Table instance
     */
    public function getTable(): Table
    {
        return $this->table;
    }

    /**
     * Writes the CREATE statement to the dump stream
     * 
     * @param  PDO      $db     PDO instance to use for DB queries
     * @param  resource $stream Stream to write the dump to
     * 
     * @return void
     */
    public function dumpCreateStatement(PDO $db, $stream): void 
    {
        $stmt = $db->query('SHOW CREATE TABLE `'.$this->table->getName().'`');

        fwrite($stream, $stmt->fetchColumn(1).";\r\n");

        $stmt->closeCursor();
    }

    /**
     * Writes the DROP statement to the dump stream
     * 
     * @param  resource $stream Stream to write the dump to
     * 
     * @return void
     */
    public function dumpDropStatement($stream): void
    {
        fwrite($stream, 'DROP TABLE IF EXISTS `'.$this->table->getName()."`;\r\n");
    }

    /**
     * Writes the INSERT statements to the dump stream
     * 
     * @param  PDO      $db     PDO instance to use for DB queries
     * @param  resource $stream Stream to write the dump to
     * 
     * @return void
     */
    public function dumpInsertStatement(PDO $db, $stream): void
    {
        //Get data from table
        $select = 'SELECT * FROM '.$this->table->getName();

        if (!empty($this->where)) {
            $select .= ' WHERE '.$this->where;
        }

        //Add limit
        $limit = 1000;
        $select .= ' LIMIT :limit OFFSET :offset';

        $stmt = $db->prepare($select);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);

        $i = 0;
        $j = 0;

        //Dump an INSERT of all rows with paging 
        do {    
            $stmt->bindValue(':offset', $i*$limit, PDO::PARAM_INT);
            $stmt->execute();

            while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
                //Write start of INSERT statement
                if ($j === 0) {
                    //Gets keys from array indexes of first row
                    $listFields = array_keys($row);

                    //Escape them
                    foreach ($listFields as $key => $field) {
                        $listFields[$key] = '`'.$field.'`';
                    }

                    $fields = implode(',', $listFields);
                    
                    fwrite($stream, 'INSERT INTO `'.$this->table->getName().'` ('.$fields.') VALUES ');
                }

                //Write values of this row
                $valuesDump = '';

                if ($j > 0) {
                    $valuesDump .= ", \r\n";
                }

                $listValues = array_values($row);

                //Quote values or replace with NULL if null
                foreach ($listValues as $key => $value) {
                    $quotedValue = str_replace("'", "\'", str_replace('"', '\"', $value));
                    $listValues[$key] = (!isset($value) ? 'NULL' : "'".$quotedValue."'");
                }

                //Add values from this row to valuesDump
                $valuesDump .= '('.implode(',', $listValues).')';

                fwrite($stream, $valuesDump);
                $j++;
            }

            $stmt->closeCursor();
            $i++;

        } while ($j === $i*$limit);

        //If there was at least one row, write end of INSERT statement
        if ($j > 0) {
            fwrite($stream, ";\r\n");
        }
    }

    /**
     * Writes all the SQL statements of this dumper to the dump stream
     * 
     * @param  PDO      $db     PDO instance to use for DB queries
     * @param  resource $stream Stream to write the dump to
     * 
     * @return void
     */
    public function dump(PDO $db, $stream): void
    {
        //Drop table statement
        if ($this->withDrop) {
            $this->dumpDropStatement($stream);
        }

        //Create table statement
        if ($this->withStructure) {
            $this->dumpCreateStatement($db, $stream);
        }

        //Data
        if ($this->withData) {
            $this->dumpInsertStatement($db, $stream);
        }
    }
}

1		<?php
2		namespace Y0lk\SQLDumper;
3
4		use PDO;
5
6		/**
7		* A TableDumper instance is used to dump a single Table, allowing you to specify certain dump options for this table only
8		*
9		* @author Gabriel Jean <[email protected]>
10		*/
11		class TableDumper {
12
13		/**
14		* @var Table Table instance related to this dumper
15		*/
16		protected $table;
17
18		/**
19		* @var boolean Specifies whether to include the table's structure (CREATE statement)
20		*/
21		protected $withStructure = true;
22
23		/**
24		* @var boolean Specifies whether to include the table's data (INSERT statements)
25		*/
26		protected $withData = true;
27
28		/**
29		* @var boolean Specifies whether to include a DROP TABLE statement
30		*/
31		protected $withDrop = true;
32
33		/**
34		* @var string WHERE parameters written as a regular SQL string to select specific data from this table
35		*/
36		protected $where = '';
37
38
39		/**
40		* @param Table Table this dumper will be used for
41		*/
42	105	public function __construct(Table $table)
43		{
44	105	$this->table = $table;
45	105	}
46
47		/**
48		* Specifies whether to include the table's structure (CREATE statement)
49		*
50		* @param boolean $withStructure TRUE to include structure, FALSE otherwise
51		*
52		* @return TableDumper Returns the TableDumper instance
53		*/
54	30	public function withStructure(bool $withStructure = true): TableDumper
55		{
56	30	$this->withStructure = $withStructure;
57	30	return $this;
58		}
59
60		/**
61		* Specifies whether to include the table's data (INSERT statements)
62		*
63		* @param boolean $withData TRUE to include data, FALSE otherwise
64		*
65		* @return TableDumper Returns the TableDumper instance
66		*/
67	21	public function withData(bool $withData = true): TableDumper
68		{
69	21	$this->withData = $withData;
70	21	return $this;
71		}
72
73		/**
74		* Specifies whether to include a DROP TABLE statement
75		*
76		* @param boolean $withDrop TRUE to include DROP statement, FALSE otherwise
77		*
78		* @return TableDumper Returns the TableDumper instance
79		*/
80	21	public function withDrop(bool $withDrop = true): TableDumper
81		{
82	21	$this->withDrop = $withDrop;
83	21	return $this;
84		}
85
86		/**
87		* Returns whether or not this dumper will include the table's structure
88		*
89		* @return boolean Returns TRUE if it includes it, FALSE otherwise
90		*/
91	45	public function hasStructure(): bool
92		{
93	45	return $this->withStructure;
94		}
95
96
97		/**
98		* Returns whether or not this dumper will include the table's data
99		*
100		* @return boolean Returns TRUE if it includes it, FALSE otherwise
101		*/
102	39	public function hasData(): bool
103		{
104	39	return $this->withData;
105		}
106
107
108		/**
109		* Returns whether or not this dumper will include the table's DROP statement
110		*
111		* @return boolean Returns TRUE if it includes it, FALSE otherwise
112		*/
113	39	public function hasDrop(): bool
114		{
115	39	return $this->withDrop;
116		}
117
118
119		/**
120		* Add WHERE parameters to use when dumping the data
121		*
122		* @param string $where_string SQL string that you would normally write after WHERE keyowrd
123		*
124		* @return TableDumper Returns the TableDumper instance
125		*/
126	6	public function where(string $where_string): TableDumper
127		{
128	6	$this->where = $where_string;
129	6	return $this;
130		}
131
132		/**
133		* Get the where param string
134		* @return string Returns the WHERE param string
135		*/
136	3	public function getWhere(): string
137		{
138	3	return $this->where;
139		}
140
141		/**
142		* Get the Table related to this dumper
143		*
144		* @return Table Returns the Table instance
145		*/
146	72	public function getTable(): Table
147		{
148	72	return $this->table;
149		}
150
151		/**
152		* Writes the CREATE statement to the dump stream
153		*
154		* @param PDO $db PDO instance to use for DB queries
155		* @param resource $stream Stream to write the dump to
156		*
157		* @return void
158		*/
159	21	public function dumpCreateStatement(PDO $db, $stream): void
160		{
161	21	$stmt = $db->query('SHOW CREATE TABLE `'.$this->table->getName().'`');
162
163	21	fwrite($stream, $stmt->fetchColumn(1).";\r\n");
164
165	21	$stmt->closeCursor();
166	21	}
167
168		/**
169		* Writes the DROP statement to the dump stream
170		*
171		* @param resource $stream Stream to write the dump to
172		*
173		* @return void
174		*/
175	24	public function dumpDropStatement($stream): void
176		{
177	24	fwrite($stream, 'DROP TABLE IF EXISTS `'.$this->table->getName()."`;\r\n");
178	24	}
179
180		/**
181		* Writes the INSERT statements to the dump stream
182		*
183		* @param PDO $db PDO instance to use for DB queries
184		* @param resource $stream Stream to write the dump to
185		*
186		* @return void
187		*/
188	27	public function dumpInsertStatement(PDO $db, $stream): void
189		{
190		//Get data from table
191	27	$select = 'SELECT * FROM '.$this->table->getName();
192
193	27	if (!empty($this->where)) {
194	3	$select .= ' WHERE '.$this->where;
195		}
196
197		//Add limit
198	27	$limit = 1000;
199	27	$select .= ' LIMIT :limit OFFSET :offset';
200
201	27	$stmt = $db->prepare($select);
202	27	$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
203
204	27	$i = 0;
205	27	$j = 0;
206
207		//Dump an INSERT of all rows with paging
208		do {
209	27	$stmt->bindValue(':offset', $i*$limit, PDO::PARAM_INT);
210	27	$stmt->execute();
211
212	27	while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
213		//Write start of INSERT statement
214	24	if ($j === 0) {
215		//Gets keys from array indexes of first row
216	24	$listFields = array_keys($row);
217
218		//Escape them
219	24	foreach ($listFields as $key => $field) {
220	24	$listFields[$key] = '`'.$field.'`';
221		}
222
223	24	$fields = implode(',', $listFields);
224
225	24	fwrite($stream, 'INSERT INTO `'.$this->table->getName().'` ('.$fields.') VALUES ');
226		}
227
228		//Write values of this row
229	24	$valuesDump = '';
230
231	24	if ($j > 0) {
232	24	$valuesDump .= ", \r\n";
233		}
234
235	24	$listValues = array_values($row);
236
237		//Quote values or replace with NULL if null
238	24	foreach ($listValues as $key => $value) {
239	24	$quotedValue = str_replace("'", "\'", str_replace('"', '\"', $value));
240	24	$listValues[$key] = (!isset($value) ? 'NULL' : "'".$quotedValue."'");
241		}
242
243		//Add values from this row to valuesDump
244	24	$valuesDump .= '('.implode(',', $listValues).')';
245
246	24	fwrite($stream, $valuesDump);
247	24	$j++;
248		}
249
250	27	$stmt->closeCursor();
251	27	$i++;
252
253	27	} while ($j === $i*$limit);
254
255		//If there was at least one row, write end of INSERT statement
256	27	if ($j > 0) {
257	24	fwrite($stream, ";\r\n");
258		}
259	27	}
260
261		/**
262		* Writes all the SQL statements of this dumper to the dump stream
263		*
264		* @param PDO $db PDO instance to use for DB queries
265		* @param resource $stream Stream to write the dump to
266		*
267		* @return void
268		*/
269	3	public function dump(PDO $db, $stream): void
270		{
271		//Drop table statement
272	3	if ($this->withDrop) {
273	3	$this->dumpDropStatement($stream);
274		}
275
276		//Create table statement
277	3	if ($this->withStructure) {
278	3	$this->dumpCreateStatement($db, $stream);
279		}
280
281		//Data
282	3	if ($this->withData) {
283	3	$this->dumpInsertStatement($db, $stream);
284		}
285		}
286		}

Y0lk / sqldumper

Push — master ( 3d1041...cba42a )

TableDumper::where() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like