Skip to content

Instantly share code, notes, and snippets.

@dipo1

dipo1/osce.toolbox.refactor.md

Last active June 26, 2025 16:54
Show Gist options
  • Save dipo1/b598834d07de11ae2f663b77211ef390 to your computer and use it in GitHub Desktop.
Save dipo1/b598834d07de11ae2f663b77211ef390 to your computer and use it in GitHub Desktop.
Download ZIP
OSCE Toolbox Refactor
Raw
osce.toolbox.refactor.md

Folder Structure:

/osce
├── /api
│   ├── /example
│   │   ├── list.get.php
│   │   ├── view.get.php
│   │   ├── add.post.php
│   │   ├── update.patch.php
│   │   └── delete.delete.php
│   ├── example.php
│   ├── example_list.get.php
│   ├── example_view.get.php
│   ├── example_add.post.php
│   ├── example_update.patch.php
│   └── example_delete.delete.php
├── /assets
│       ...
├── /lib
│   ├── /Example
│   │       ...
│   ├── Example.php
│   ├── helpers.php
│   └── autoload.php
├── /pages
│   ├── /path
│   │   └── /to
│   │       └── example.php
│   └─── example.php
├── /public
│   ├── /storage
│   │   ├── /assets
│   │   │       ...
│   │   └── /uploads
│   │           ...
│   ├── .htaccess
│   └── index.php
├── /storage
│   ├── /assets
│   │       ...
│   └── /uploads
│           ...
├── /templates
│   ├── /example
│   │   ├── /assets
│   │   │      ...
│   │   └── index.phtml
│   └── example.phtml
├── /vendor
│       ...
├── .env
├── .gitignore
├── App.php
├── composer.json
└── composer.lock

Before:

example.php

<?php
require_once 'inc/conn.php';

$id = softSan(get('id'));

if(get('post')) {
  mysqli_query($conn, "UPDATE ... WHERE id = ".$id);

  echo 1;
  exit;
}

$query = mysqli_query($conn, "SELECT ...");
$data = $query->fetch_assoc();

require_once 'header.php';
?>

<div>
  <?php echo $data['value'] ?>
</div>

<style>
  ....
</style>

<script>
  ....
</script>

<?php require_once 'footer.php'; ?>

After:

pages/example.php

<?php
/** @var App\Route $route */

$route->setLayout('default'); // Layout Wrapper (page only), @optional: defaults to 'default', @param. 'default' | 'blank' | 'admin' ...

$db = db(); //or App\Database::$instance

$stmt = $db->prepare("SELECT...");
$stmt->execute();
$data = $stmt->get_result()->fetch_assoc();

return view('example', array('data' => $data));

templates/example/index.phtml or templates/example.phtml

<?php /** @var array $data */ ?>
<?php use_asset('example.css'); /*Or use_asset('templates/example/assets/example.css') */ ?>
<?php use_asset('example.js'); /*Or use_asset('templates/example/assets/example.js') */ ?>

<div>
  <?= $data['value'] ?>
  <?= view('nested/template') ?>
</div>

api/example.post.php

<?php
/** @var App\Route $route */

$db = db(); //or App\Database::$instance

// input() sanitizes by default, can be disabled with a boolean param.
$id = $request->input('id'); // Or $request->get('...'), $request->post('...'), $request->file('...')

$result = array('status' => false, 'data' => null, 'message' => '');

$update_stmt = $db->prepare("UPDATE...");

if($update_stmt->affected_rows) {
  $result['status'] = true;

  $stmt = $db->prepare("SELECT...");
  $stmt->bind_param('i', $id);
  $stmt->execute();
  $result['data'] = $stmt->get_result()->fetch_assoc();
  $result['message'] = '...';
} else {
  $result['message'] = '...';
}

return $result;

Database Structure:

article_categories
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

articles
+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL                |                |
| title       | varchar(256) | NO   |     | NULL                |                |
| content     | text         | NO   |     | NULL                |                |
| slug        | varchar(256) | YES  | UNI | NULL                |                |
| image_url   | varchar(255) | YES  |     | NULL                |                |
| custom_url  | varchar(128) | YES  |     | NULL                |                |
| views       | int(11)      | YES  |     | 0                   |                |
| is_hidden   | tinyint(1)   | YES  |     | 0                   |                |
| style       | tinyint(4)   | YES  |     | 1                   |                |
| category_id | int(11)      | YES  | MUL | NULL                |                |
| created_at  | timestamp    | YES  |     | current_timestamp() |                |
+-------------+--------------+------+-----+---------------------+----------------+

countries
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

devices
+-------------+-----------------------------------+------+-----+---------------------+----------------+
| Field       | Type                              | Null | Key | Default             | Extra          |
+-------------+-----------------------------------+------+-----+---------------------+----------------+
| id          | int(11)                           | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)                           | NO   | MUL | NULL                |                |
| device_type | enum('phone','tablet','computer') | NO   |     | NULL                |                |
| accessed_at | timestamp                         | YES  |     | current_timestamp() |                |
+-------------+-----------------------------------+------+-----+---------------------+----------------+

hall_of_fame
+-------------+------------+------+-----+---------------------+----------------+
| Field       | Type       | Null | Key | Default             | Extra          |
+-------------+------------+------+-----+---------------------+----------------+
| id          | int(11)    | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)    | NO   | MUL | NULL                |                |
| content     | text       | NO   |     | NULL                |                |
| category_id | int(11)    | YES  | MUL | NULL                |                |
| featured    | tinyint(1) | YES  |     | 0                   |                |
| anonymous   | tinyint(1) | YES  |     | 0                   |                |
| created_at  | timestamp  | YES  |     | current_timestamp() |                |
| deleted_at  | tinyint(1) | YES  |     | NULL                |                |
+-------------+------------+------+-----+---------------------+----------------+

hall_of_fame_categories
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

notes
+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL                | auto_increment |
| upload_id  | int(11)      | NO   | MUL | NULL                |                |
| user_id    | int(11)      | NO   | MUL | NULL                |                |
| title      | varchar(254) | NO   |     | NULL                |                |
| content    | longtext     | NO   |     | NULL                |                |
| created_at | timestamp    | YES  |     | current_timestamp() |                |
| deleted_at | tinyint(1)   | YES  |     | NULL                |                |
+------------+--------------+------+-----+---------------------+----------------+

notifications
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_id        | int(11)     | NO   | MUL | NULL    |                |
| newsletter     | tinyint(1)  | YES  |     | 0       |                |
| marketing      | tinyint(1)  | YES  |     | 0       |                |
| billing_expiry | tinyint(1)  | YES  |     | 1       |                |
| source         | varchar(64) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

payments
+-------------------------+---------------+------+-----+---------------------+----------------+
| Field                   | Type          | Null | Key | Default             | Extra          |
+-------------------------+---------------+------+-----+---------------------+----------------+
| id                      | int(11)       | NO   | PRI | NULL                | auto_increment |
| user_id                 | int(11)       | NO   | MUL | NULL                |                |
| payment_id              | varchar(64)   | YES  |     | NULL                |                |
| session_id              | varchar(255)  | YES  |     | NULL                |                |
| subscription_id         | varchar(255)  | YES  |     | NULL                |                |
| source                  | varchar(32)   | YES  |     | NULL                |                |
| amount                  | decimal(10,2) | YES  |     | NULL                |                |
| currency                | varchar(4)    | YES  |     | GBP                 |                |
| interval                | varchar(16)   | YES  |     | NULL                |                |
| interval_count          | int(11)       | YES  |     | NULL                |                |
| period_start            | timestamp     | YES  |     | NULL                |                |
| period_end              | timestamp     | YES  |     | NULL                |                |
| status                  | varchar(32)   | YES  |     | NULL                |                |
| price_id                | varchar(255)  | YES  |     | NULL                |                |
| customer_id             | varchar(255)  | YES  |     | NULL                |                |
| stripe_confirmed_active | tinyint(1)    | YES  |     | 0                   |                |
| created_at              | timestamp     | YES  |     | current_timestamp() |                |
+-------------------------+---------------+------+-----+---------------------+----------------+

quizzes
+--------------+--------------+------+-----+---------------------+----------------+
| Field        | Type         | Null | Key | Default             | Extra          |
+--------------+--------------+------+-----+---------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id      | int(11)      | NO   | MUL | NULL                |                |
| company_name | varchar(255) | NO   |     | NULL                |                |
| questions    | longtext     | NO   |     | NULL                |                |
| responses    | text         | YES  |     | NULL                |                |
| created_at   | timestamp    | YES  |     | current_timestamp() |                |
+--------------+--------------+------+-----+---------------------+----------------+

referrals
+-----------------+---------------+------+-----+---------------------+----------------+
| Field           | Type          | Null | Key | Default             | Extra          |
+-----------------+---------------+------+-----+---------------------+----------------+
| id              | int(11)       | NO   | PRI | NULL                | auto_increment |
| referrer_id     | int(11)       | NO   | MUL | NULL                |                |
| referred_id     | int(11)       | NO   | MUL | NULL                |                |
| plan            | varchar(32)   | YES  |     | NULL                |                |
| amount          | decimal(10,2) | YES  |     | NULL                |                |
| subscription_id | varchar(64)   | YES  |     | NULL                |                |
| paid_at         | timestamp     | YES  |     | NULL                |                |
| created_at      | timestamp     | YES  |     | current_timestamp() |                |
+-----------------+---------------+------+-----+---------------------+----------------+

reviews
+-------------+------------+------+-----+---------------------+----------------+
| Field       | Type       | Null | Key | Default             | Extra          |
+-------------+------------+------+-----+---------------------+----------------+
| id          | int(11)    | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)    | NO   | MUL | NULL                |                |
| resource_id | int(11)    | NO   |     | NULL                |                |
| comment     | text       | YES  |     | NULL                |                |
| highlight   | longtext   | YES  |     | NULL                |                |
| parent_id   | int(11)    | YES  | MUL | NULL                |                |
| created_at  | timestamp  | YES  |     | current_timestamp() |                |
| deleted_at  | tinyint(1) | YES  |     | NULL                |                |
+-------------+------------+------+-----+---------------------+----------------+

scenario_scores
+-------------+-------------+------+-----+---------------------+----------------+
| Field       | Type        | Null | Key | Default             | Extra          |
+-------------+-------------+------+-----+---------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)     | NO   | MUL | NULL                |                |
| resource_id | int(11)     | NO   |     | NULL                |                |
| score       | int(11)     | NO   |     | NULL                |                |
| max_score   | int(11)     | NO   |     | NULL                |                |
| tag_name    | varchar(64) | YES  |     | NULL                |                |
| tag_type    | varchar(32) | YES  |     | NULL                |                |
| session_id  | varchar(64) | YES  |     | NULL                |                |
| hidden      | tinyint(1)  | YES  |     | 0                   |                |
| created_at  | timestamp   | YES  |     | current_timestamp() |                |
+-------------+-------------+------+-----+---------------------+----------------+

sessions
+---------------+-------------+------+-----+---------------------+----------------+
| Field         | Type        | Null | Key | Default             | Extra          |
+---------------+-------------+------+-----+---------------------+----------------+
| id            | int(11)     | NO   | PRI | NULL                | auto_increment |
| user_id       | int(11)     | NO   | MUL | NULL                |                |
| session_id    | varchar(64) | YES  |     | NULL                |                |
| landing_page  | text        | YES  |     | NULL                |                |
| bounced       | tinyint(1)  | YES  |     | 1                   |                |
| session_start | timestamp   | YES  |     | current_timestamp() |                |
| session_end   | timestamp   | YES  |     | NULL                |                |
+---------------+-------------+------+-----+---------------------+----------------+

universities
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

unsubscriptions
+-----------------+--------------+------+-----+---------------------+----------------+
| Field           | Type         | Null | Key | Default             | Extra          |
+-----------------+--------------+------+-----+---------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id         | int(11)      | NO   | MUL | NULL                |                |
| reason          | varchar(128) | YES  |     | NULL                |                |
| subreason       | varchar(128) | YES  |     | NULL                |                |
| unsubscribed_at | timestamp    | YES  |     | current_timestamp() |                |
+-----------------+--------------+------+-----+---------------------+----------------+

upload_categories
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

upload_comments
+--------------+------------+------+-----+---------------------+----------------+
| Field        | Type       | Null | Key | Default             | Extra          |
+--------------+------------+------+-----+---------------------+----------------+
| id           | int(11)    | NO   | PRI | NULL                | auto_increment |
| upload_id    | int(11)    | NO   | MUL | NULL                |                |
| user_id      | int(11)    | NO   | MUL | NULL                |                |
| comment_text | text       | NO   |     | NULL                |                |
| parent_id    | int(11)    | YES  | MUL | NULL                |                |
| created_at   | timestamp  | YES  |     | current_timestamp() |                |
| is_hidden    | tinyint(1) | YES  |     | 0                   |                |
+--------------+------------+------+-----+---------------------+----------------+

uploads
+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL                |                |
| title       | varchar(256) | NO   |     | NULL                |                |
| content     | longtext     | YES  |     | NULL                |                |
| category_id | int(11)      | YES  | MUL | NULL                |                |
| file_url    | text         | YES  |     | NULL                |                |
| file_type   | varchar(10)  | YES  |     | NULL                |                |
| diagnosis   | varchar(128) | YES  |     | NULL                |                |
| module      | varchar(64)  | YES  |     | NULL                |                |
| station     | varchar(64)  | YES  |     | NULL                |                |
| year        | varchar(16)  | YES  |     | NULL                |                |
| tags        | longtext     | YES  |     | NULL                |                |
| difficulty  | tinyint(4)   | YES  |     | 1                   |                |
| price       | decimal(8,2) | YES  |     | NULL                |                |
| is_free     | tinyint(1)   | YES  |     | 0                   |                |
| is_ai       | tinyint(1)   | YES  |     | 0                   |                |
| reviewed_by | int(11)      | YES  | MUL | NULL                |                |
| review_time | timestamp    | YES  |     | NULL                |                |
| hidden      | tinyint(1)   | YES  |     | 0                   |                |
| created_at  | timestamp    | YES  |     | current_timestamp() |                |
| updated_at  | timestamp    | YES  |     | NULL                |                |
+-------------+--------------+------+-----+---------------------+----------------+

users
+---------------+---------------------------------------------+------+-----+---------------------+----------------+
| Field         | Type                                        | Null | Key | Default             | Extra          |
+---------------+---------------------------------------------+------+-----+---------------------+----------------+
| id            | int(11)                                     | NO   | PRI | NULL                | auto_increment |
| email         | varchar(128)                                | NO   | UNI | NULL                |                |
| password      | varchar(255)                                | NO   |     | NULL                |                |
| first_name    | varchar(64)                                 | YES  |     | NULL                |                |
| last_name     | varchar(64)                                 | YES  |     | NULL                |                |
| university_id | int(11)                                     | YES  | MUL | NULL                |                |
| phone         | varchar(20)                                 | YES  |     | NULL                |                |
| address       | text                                        | YES  |     | NULL                |                |
| city          | varchar(64)                                 | YES  |     | NULL                |                |
| state         | varchar(64)                                 | YES  |     | NULL                |                |
| country_id    | int(11)                                     | NO   | MUL | NULL                |                |
| zip           | varchar(16)                                 | YES  |     | NULL                |                |
| role          | enum('student','admin','editor','reviewer') | YES  |     | student             |                |
| last_login    | timestamp                                   | YES  |     | NULL                |                |
| created_at    | timestamp                                   | YES  |     | current_timestamp() |                |
+---------------+---------------------------------------------+------+-----+---------------------+----------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment