Completed
Push — master ( d4e813...ce0d98 )
by Mehmet
02:46
created

SQL::delete()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 8
Code Lines 5

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 4
CRAP Score 2.032

Importance

Changes 2
Bugs 0 Features 0
Metric Value
c 2
b 0
f 0
dl 0
loc 8
ccs 4
cts 5
cp 0.8
rs 9.4285
cc 2
eloc 5
nc 2
nop 2
crap 2.032
1
<?php
2
3
namespace Soupmix;
4
/*
5
SQL Adapter
6
*/
7
8
use Doctrine\DBAL\Connection;
9
use Doctrine\DBAL\Schema\Table;
10
use Doctrine\DBAL\Schema\Column;
11
use Doctrine\DBAL\Types\Type;
12
use Doctrine\DBAL\Schema\Index;
13
14
class SQL implements Base
15
{
16
    protected $doctrine = null;
17
    protected $dbName = null;
18
    protected static $columnDefaults = [
19
        'name'      => null,
20
        'type'      => 'string',
21
        'type_info' => null,
22
        'maxLength' => 255,
23
        'default'   => null,
24
        'index'     => null,
25
        'index_type'=> null,
26
]   ;
27
28 3
    public function __construct($config, Connection $client)
29
    {
30 3
        $this->doctrine = $client;
31 3
        $this->dbName = $config['db_name'];
32 3
    }
33
34 2
    public function getConnection()
35
    {
36 2
        return $this->doctrine;
37
    }
38
39 3
    public function create($collection, $fields)
40
    {
41 3
        $columns = [];
42 3
        $indexes = [];
43 3
        $schemaManager = $this->doctrine->getSchemaManager();
44 3
        $columns[] = new Column('id', Type::getType('integer'), ['unsigned' => true, 'autoincrement' => true] );
45 3
        $indexes[] = new Index($collection.'_PK', ['id'], false, true);
46
47 3
        list($columns, $indexes, $tmpIndexes) = $this->createAddColumns($collection, $columns, $indexes, $fields);
48 3 View Code Duplication
        if(count($tmpIndexes)>0){
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
49 3
            $indexes[] = new Index($collection . '_IDX', $tmpIndexes, false, false);
50 3
        }
51 3
        $table = new Table($collection, $columns, $indexes);
52 3
        return $schemaManager->createTable($table);
53
    }
54
55 3
    protected function createAddColumns($collection, $columns, $indexes, $fields)
56
    {
57 3
        $tmpIndexes = [];
58 3
        foreach ($fields as $field){
59 3
            $field = array_merge(self::$columnDefaults, $field);
60 3
            $options = [];
61 3
            if ($field['type'] == 'integer' && $field['type_info'] == 'unsigned') {
62
                $options['unsigned'] = true;
63
            }
64 3
            $options['length'] = $field['maxLength'];
65 3
            $options['default'] = $field['default'];
66 3 View Code Duplication
            if ($field['index'] !== null) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
67 3
                if ( $field['index_type'] == 'unique' ) {
68 3
                    $indexes[] = new Index($collection . '_' . $field['name'] . '_UNQ', [$field['name']], true, false);
69 3
                } else {
70 3
                    $tmpIndexes[] = $field['name'];
71
                }
72 3
            }
73 3
            $columns[] = new Column($field['name'], Type::getType($field['type']), $options );
74 3
        }
75
76 3
        return [ $columns, $indexes, $tmpIndexes];
77
    }
78
79 3
    public function drop($collection)
80
    {
81 3
        $schemaManager = $this->doctrine->getSchemaManager();
82 3
        if ($schemaManager->tablesExist([$collection])) {
83 2
            return $schemaManager->dropTable($collection);
84
        } else {
85 1
            return null;
86
        }
87
    }
88
89
    public function truncate($collection)
90
    {
91
        return $this->client->doctrine->query('TRUNCATE TABLE `' . $collection . '`');
0 ignored issues
show
Bug introduced by
The property client does not exist. Did you maybe forget to declare it?

In PHP it is possible to write to properties without declaring them. For example, the following is perfectly valid PHP code:

class MyClass { }

$x = new MyClass();
$x->foo = true;

Generally, it is a good practice to explictly declare properties to avoid accidental typos and provide IDE auto-completion:

class MyClass {
    public $foo;
}

$x = new MyClass();
$x->foo = true;
Loading history...
92
    }
93
94
    public function createIndexes($collection, $indexes)
95
    {
96
        $schemaManager = $this->doctrine->getSchemaManager();
97
        $tmpIndexes = [];
98
        foreach ($indexes as $field){
99
            $field = array_merge(self::$columnDefaults, $field);
100 View Code Duplication
            if ($field['index'] !== null) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
101
                if ( $field['index_type'] == 'unique' ) {
102
                    $indexes[] = new Index($collection . '_' . $field['name'] . '_UNQ', [$field['name']], true, false);
103
                } else {
104
                    $tmpIndexes[] = $field['name'];
105
                }
106
            }
107
        }
108 View Code Duplication
        if (count($tmpIndexes) > 0) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
109
            $indexes[] = new Index($collection . '_IDX', $tmpIndexes, false, false);
110
        }
111
        foreach ($indexes as $index) {
112
            $schemaManager->createIndex($index, $collection);
113
        }
114
    }
115
116 3
    public function insert($collection, $values)
117
    {
118 3
        $insertion = $this->doctrine->insert($collection, $values);
119 3
        if($insertion !== 0) {
120 3
            return $this->doctrine->lastInsertId();
121
        }
122
        return null;
123
    }
124
125
    public function update($collection, $filter, $values)
126
    {
127
        return $this->doctrine->update($collection, $values, $filter);
128
    }
129
130 2
    public function delete($collection, $filter)
131
    {
132 2
        $numberOfDeletedItems = $this->doctrine->delete($collection, $filter);
133 2
        if ($numberOfDeletedItems>0) {
134 2
            return 1;
135
        }
136
        return 0;
137
    }
138
139 1
    public function get($collection, $docId)
140
    {
141 1
        return $this->doctrine->fetchAssoc('SELECT * FROM ' . $collection . ' WHERE id = ?', array($docId));
142
    }
143
144 1
    public function find($collection, $filters, $fields = null, $sort = null, $offset = 0, $limit = 25, $debug = false)
145
    {
146 1
        $query = $this->query($collection);
147 1
        foreach ($filters as $filter => $value) {
148 1
            if (is_array($value)) {
149 1
                $query->orFilters($value);
150 1
            } else {
151 1
                $query->andFilter($filter, $value);
152
            }
153 1
        }
154 1
        return $query->returnFields($fields)
155 1
            ->sortFields($sort)
156 1
            ->offset($offset)
157 1
            ->limit($limit)
158 1
            ->run();
159
    }
160
161 2
    public function buildQuery($collection, $filters)
162
    {
163 2
        $queryBuilder = $this->doctrine->createQueryBuilder();
164 2
        $queryBuilder->from($collection);
165 2
        if ($filters === null) {
166
            return $queryBuilder;
167
        }
168 2
        return $this->buildQueryFilters($queryBuilder, $filters);
169
    }
170
171 3
    protected function buildQueryFilters($queryBuilder, $filters)
172
    {
173 2
        foreach ($filters as $key => $value) {
174 3
            if (strpos($key, '__') === false && is_array($value)) {
175 2
                $queryBuilder = $this->buildQueryForOr($queryBuilder, $value);
176 2
                continue;
177
            }
178 2
            $queryBuilder = $this->buildQueryForAnd($queryBuilder, $key, $value);
179 2
        }
180 2
        return $queryBuilder;
181
    }
182
183 2
    protected function buildQueryForAnd($queryBuilder, $key, $value)
184
    {
185 2
        $sqlOptions = self::buildFilter([$key => $value]);
186 2 View Code Duplication
        if (in_array($sqlOptions['method'], ['in', 'notIn'])) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
187
            $queryBuilder->andWhere(
188
                $queryBuilder->expr()->{$sqlOptions['method']}( $sqlOptions['key'], $sqlOptions['value'])
189
            );
190
            return $queryBuilder;
191
        }
192 2
        $queryBuilder->andWhere(
193 2
                '`'.$sqlOptions['key'].'`'
194 2
                . ' ' . $sqlOptions['operand']
195 2
                . ' ' . $queryBuilder->createNamedParameter($sqlOptions['value'])
196 2
            );
197 2
        return $queryBuilder;
198
    }
199 2
    protected function buildQueryForOr($queryBuilder, $value)
200
    {
201 2
        $orQuery =[];
202 2
        foreach ($value as $orValue) {
203 2
            $subKey = array_keys($orValue)[0];
204 2
            $subValue = $orValue[$subKey];
205 2
            $sqlOptions = self::buildFilter([$subKey => $subValue]);
206 2 View Code Duplication
            if (in_array($sqlOptions['method'], ['in', 'notIn'])) {
0 ignored issues
show
Duplication introduced by
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
207 1
                $orQuery[] =  $queryBuilder->expr()->{$sqlOptions['method']}( $sqlOptions['key'], $sqlOptions['value']);
208 1
                continue;
209
            }
210 2
            $orQuery[] =
211 2
                '`'.$sqlOptions['key'].'`'
212 2
                . ' ' . $sqlOptions['operand']
213 2
                . ' ' . $queryBuilder->createNamedParameter($sqlOptions['value']);
214
215 2
        }
216 2
        $queryBuilder->andWhere(
217 2
            '(' . implode(' OR ', $orQuery) . ')'
218 2
        );
219 2
        return $queryBuilder;
220
    }
221
222 2
    public function query($collection)
223
    {
224 2
        return new SQLQueryBuilder($collection, $this);
225
    }
226
227 2
    public static function buildFilter($filter)
228
    {
229 2
        $key = array_keys($filter)[0];
230 2
        $value = $filter[$key];
231 2
        $operator = ' = ';
232 2
        $method = 'eq';
233
        $options =[
234 2
            'gte'       => ['method' => 'gte', 'operand' => ' >= '],
235 2
            'gt'        => ['method' => 'gt', 'operand' => ' > '],
236 2
            'lte'       => ['method' => 'lte', 'operand' => ' <= '],
237 2
            'lt'        => ['method' => 'lt', 'operand' => ' < '],
238 2
            'in'        => ['method' => 'in', 'operand' => ' IN '],
239 2
            '!in'       => ['method' => 'notIn', 'operand' => ' NOT IN '],
240 2
            'not'       => ['method' => 'not', 'operand' => ' NOT '],
241 2
            'wildcard'  => ['method' => 'like', 'operand' => ' LIKE '],
242 2
            'prefix'    => ['method' => 'like', 'operand' => ' LIKE '],
243 2
        ];
244 2
        if (strpos($key, '__') !== false) {
245 2
            preg_match('/__(.*?)$/i', $key, $matches);
246 2
            $key        = str_replace($matches[0], '', $key);
247 2
            $queryOperator   = $matches[1];
248 2
            $method     = $options[$queryOperator]['method'];
249 2
            $operator   = $options[$queryOperator]['operand'];
250
            switch ($queryOperator) {
251 2
                case 'wildcard':
252 1
                    $value = '%'.str_replace(array('?', '*'), array('_', '%'), $value).'%';
253 1
                    break;
254 2
                case 'prefix':
255 1
                    $value = $value.'%';
256 1
                    break;
257
            }
258 2
        }
259
        return [
260 2
            'key'       => $key,
261 2
            'operand'   => $operator,
262 2
            'method'    => $method,
263
            'value'     => $value
264 2
        ];
265
    }
266
267
}
268