9BuYzmza

1<?php
2namespace App\Http\Controllers;
3use App\Schema;
4use App\SchemaAttribute;
5use DB;
6use Illuminate\Http\Request;
7use PDO;
8class SchemaController extends Controller
9{
10    public function list_databases()
11    {
12        $data = [];
13        $databases = DB::select('show databases');
14        foreach ($databases as $database) {
15            if ($database->Database != 'mysql' && $database->Database != 'sys' && $database->Database != 'information_schema' && $database->Database != 'performance_schema') {
16                array_push($data, ['database' => $database->Database]);
17            }
18        }
19        return response()->json($data);
20    }
21    /**
22     * @param $database_name
23     * @return PDO
24     */
25    function get_pdo($database_name)
26    {
27        $host = env('DB_HOST');
28        $db = $database_name;
29        $user = env('DB_USERNAME');
30        $pass = env('DB_PASSWORD');
31        $port = env('DB_PORT');
32        $charset = 'utf8mb4';
33        $dsn = "mysql:host=$host;port=$port;dbname=$db;charset=$charset";
34        $options = [
35            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
36            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
37            PDO::ATTR_EMULATE_PREPARES => false,
38        ];
39        try {
40            $pdo = new PDO($dsn, $user, $pass, $options);
41        } catch (\PDOException $e) {
42            throw new \PDOException($e->getMessage(), (int)$e->getCode());
43        }
44        return $pdo;
45    }
46    /**
47     * return a json endpoint of tables and their column names and types
48     * @param $database
49     * @return \Illuminate\Http\JsonResponse
50     */
51    public function schema_fields($database)
52    {
53        $key = "Tables_in_" . $database;
54        $data = [];
55        $pdo = $this->get_pdo($database);
56        $statement = $pdo->prepare('show tables');
57        $statement->execute();
58        $schemas = $statement->fetchAll(PDO::FETCH_OBJ);
59        foreach ($schemas as $schema) {
60            if ($schema->$key != 'migrations' && $schema->$key != 'users' && $schema->$key != 'password_resets' && $schema->$key != 'schema_attributes' && $schema->$key != 'schemas') {
61                $inside_data = [];
62                $sql = 'show fields from ' . $schema->$key;
63                $fields_statement = $pdo->prepare($sql);
64                $fields_statement->execute();
65                $columns = $fields_statement->fetchAll(PDO::FETCH_OBJ);
66                foreach ($columns as $column) {
67                    $data_to_push = ['field' => $column->Field, 'type' => $column->Type];
68                    array_push($inside_data, $data_to_push); // call by reference because they are stacks in the  background
69                }
70                $outside_data_to_push = ['table' => $schema->$key, 'columns' => $inside_data];
71                array_push($data, $outside_data_to_push);
72            }
73        }
74        return response()->json($data);
75    }
76    /**
77     * List the schemas
78     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
79     */
80    public function index()
81    {
82        $schemas = DB::select('SHOW TABLES');
83        return view('welcome', ['schemas' => $schemas]);
84    }
85    /**
86     * display the step 1
87     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
88     */
89    public function add_schema_step_one_view()
90    {
91        return view('pages.schemas.create-schema-step-one');
92    }
93    /**
94     * display the step 1
95     * @param Schema $schema
96     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
97     */
98    public function add_schema_step_two_view(Schema $schema)
99    {
100        return view('pages.schemas.create-schema-step-two', ['schema' => $schema]);
101    }
102    /**
103     * @param Request $request
104     * @return \Illuminate\Http\RedirectResponse|\Illuminate\Routing\Redirector
105     * @throws \Illuminate\Validation\ValidationException
106     */
107    public function process_step_one(Request $request)
108    {
109        $this->validate($request, [
110            'name' => 'required|string|unique:schemas,name',
111            'number_of_attributes' => 'required|numeric'
112        ]);
113        $schema = Schema::create([
114            'name' => $request->input('name'),
115            'number_of_attributes' => $request->input('number_of_attributes')
116        ]);
117        return redirect(route('add-schema-step-2', $schema->id));
118    }
119    /**
120     * @param Request $request
121     * @throws \Illuminate\Validation\ValidationException
122     */
123    public function process_step_two(Request $request)
124    {
125        $this->validate($request, [
126            'attributes.*.name' => 'required|unique:schema_attributes,name',
127            'attributes.*.type' => 'required|string',
128        ]);
129        $attributes = collect($request->input('attributes'));
130        $names = $attributes->map(function ($item, $key) {
131            return $item['name'];
132        });
133        $unique_names = array_unique($names->toArray()); // get unique names
134        $duplicate_keys_assoc = array_diff_assoc($names->toArray(), $unique_names); // differentiate main array from the unique array
135        $duplicate_names = array_values($duplicate_keys_assoc); // remove the names from the associative array
136        if (sizeof($duplicate_names) > 0) { // message formatting using commas where duplicates are more than one
137            $message = '';
138            foreach ($duplicate_names as $key => $value) {
139                if ($key == 0) {
140                    $message = $value;
141                } else {
142                    $message = $message . ',' . $value;
143                }
144            }
145            $message = $message . ' are duplicates. Please fix this';
146            flash($message)->error();
147            return redirect()->back()->withInput($request->all());
148        }
149        $data = [];
150        foreach ($attributes as $attribute) {
151            $attribute['schema_id'] = (int)$request->input('schema_id');
152            $attribute['size'] = (int)$attribute['size'];
153            $attribute['null'] = array_key_exists('null', $attribute) ? 1 : 0;
154            $attribute['index'] = array_key_exists('index', $attribute) ? 1 : 0;
155            $attribute['primary_key'] = array_key_exists('primary_key', $attribute) ? 1 : 0;
156            array_push($data, $attribute);
157        }
158        SchemaAttribute::insert($data);
159        $schema = Schema::find($request->input('schema_id'));
160        $this->parse_to_sql($schema);
161        return redirect('/');
162    }
163    /**
164     * remove the schema
165     * @param Schema $schema
166     * @return \Illuminate\Http\RedirectResponse
167     * @throws \Exception
168     */
169    public function remove(Schema $schema)
170    {
171        $schema->delete();
172        flash('schema deleted successfully')->success();
173        return redirect()->back();
174    }
175    public function parse_to_sql(Schema $schema)
176    {
177        $index = 1;
178        $sql_statement = "create table if not exists " . $schema->name . "(";
179        foreach ($schema->attributes as $attribute) {
180            $sql_statement = $sql_statement . $attribute->name . ' ' . $attribute->type;
181            if ($attribute->size > 0) {
182                $sql_statement = $sql_statement . '(' . $attribute->size . ')';
183            }
184            if ($attribute->null) {
185                $sql_statement = $sql_statement . ' null';
186            } else {
187                $sql_statement = $sql_statement . ' not null';
188            }
189            if ($attribute->primary_key) {
190                $sql_statement = $sql_statement . ' primary key';
191            }
192            if ($schema->number_of_attributes != $index) {
193                $sql_statement = $sql_statement . ',';
194            }
195            $index++;
196        }
197        $sql_statement = $sql_statement . ');';
198        DB::statement($sql_statement);
199        foreach ($schema->attributes as $attribute) {
200            if ($attribute->index) {
201                $index_statements = 'create index ';
202                $index_statements = $index_statements . $attribute->name . ' on ' . $schema->name . '(' . $attribute->name . ');';
203                DB::statement($index_statements);
204            }
205        }
206    }
207}