Overview
Comment: | Show schema as tables, not raw SQL, in SQL search, also give access to more tables for user search, like in trunk |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev |
Files: | files | file ages | folders |
SHA3-256: |
5a288b21b9b44fa26cb4166dd32d78b2 |
User & Date: | bohwaz on 2023-01-25 01:29:14 |
Other Links: | branch diff | manifest | tags |
Context
2023-01-25
| ||
02:14 | Use new export menu for export from user/accounting search, also allow everyone to export check-in: 7d4d36dcb2 user: bohwaz tags: dev | |
01:29 | Show schema as tables, not raw SQL, in SQL search, also give access to more tables for user search, like in trunk check-in: 5a288b21b9 user: bohwaz tags: dev | |
2023-01-24
| ||
22:30 | Fix Advanced user search manual SQL query recording check-in: 7dde1969ce user: alinaar tags: dev | |
Changes
Modified src/include/lib/Garradin/Accounting/AdvancedSearch.php from [9aec6782a8] to [208743c768].
︙ | ︙ | |||
218 219 220 221 222 223 224 | return (object) [ 'groups' => $query, 'order' => 'id', 'desc' => true, ]; } | | > > | > > > | < | < < | > | | > > > | 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | return (object) [ 'groups' => $query, 'order' => 'id', 'desc' => true, ]; } public function schemaTables(): array { return [ 'acc_transactions' => 'Écritures', 'acc_transactions_lines' => 'Lignes des écritures', 'acc_accounts' => 'Comptes des plans comptables', 'acc_years' => 'Exercices', 'acc_projects' => 'Projets', ]; } public function tables(): array { return array_merge(array_keys($this->schemaTables()), [ 'acc_charts', 'acc_transactions_users', ]); } public function make(string $query): DynamicList { $tables = 'acc_transactions AS t INNER JOIN acc_transactions_lines AS l ON l.id_transaction = t.id INNER JOIN acc_accounts AS a ON l.id_account = a.id |
︙ | ︙ |
Modified src/include/lib/Garradin/AdvancedSearch.php from [a528aa46e8] to [b5d15a883f].
︙ | ︙ | |||
19 20 21 22 23 24 25 | * - 'normalize' (string) will normalize the user entry to a specific format (accepted: tel, money) * - 'null' (bool) if true, the user will be able to search for NULL values * - 'type' (string) type of HTML input */ abstract public function columns(): array; /** | > > > | > > | | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | * - 'normalize' (string) will normalize the user entry to a specific format (accepted: tel, money) * - 'null' (bool) if true, the user will be able to search for NULL values * - 'type' (string) type of HTML input */ abstract public function columns(): array; /** * Returns list of tables that should be documented for SQL queries */ abstract public function schemaTables(): array; /** * Returns list of tables the user has access to for SQL queries */ abstract public function tables(): array; /** * Builds a DynamicList object from the supplied search groups */ abstract public function make(string $query): DynamicList; /** |
︙ | ︙ |
Modified src/include/lib/Garradin/Entities/Search.php from [3c8b0f1365] to [8226ea7bcc].
︙ | ︙ | |||
210 211 212 213 214 215 216 217 218 219 220 221 222 223 | } } public function export(string $format) { CSV::export($format, 'Recherche', $this->iterateResults(), $this->getHeader()); } public function getProtectedTables(): ?array { if ($this->type != self::TYPE_SQL || $this->target == self::TARGET_ALL) { return null; } | > > > > > > > > > > > > > > | > | > > < < | > | 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 | } } public function export(string $format) { CSV::export($format, 'Recherche', $this->iterateResults(), $this->getHeader()); } public function schema(): array { $out = []; $db = DB::getInstance(); foreach ($this->getAdvancedSearch()->schemaTables() as $table => $comment) { $schema = $db->getTableSchema($table); $schema['comment'] = $comment; $out[$table] = $schema; } return $out; } public function getProtectedTables(): ?array { if ($this->type != self::TYPE_SQL || $this->target == self::TARGET_ALL) { return null; } $list = $this->getAdvancedSearch()->tables(); $tables = []; foreach ($list as $name) { $tables[$name] = null; } return $tables; } public function getGroups(): array { if ($this->type != self::TYPE_JSON) { throw new \LogicException('Only JSON searches can use this method'); } |
︙ | ︙ |
Modified src/include/lib/Garradin/Users/AdvancedSearch.php from [4a02c4725d] to [83c157aa40].
︙ | ︙ | |||
139 140 141 142 143 144 145 | 'select' => '\'À jour\'', 'where' => 'id IN (SELECT id_user FROM services_users WHERE id_service %s AND (expiry_date IS NULL OR expiry_date > date()))', ]; return $columns; } | | | > | > > > > | < < < < < < | > | | > > | 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | 'select' => '\'À jour\'', 'where' => 'id IN (SELECT id_user FROM services_users WHERE id_service %s AND (expiry_date IS NULL OR expiry_date > date()))', ]; return $columns; } public function schemaTables(): array { return [ 'users' => 'Membres', 'users_categories' => 'Catégories de membres', 'services' => 'Activités', 'services_fees' => 'Tarifs des activités', 'services_users' => 'Inscriptions aux activités', ]; } public function tables(): array { return array_merge(array_keys($this->schemaTables()), [ 'users_search', ]); } public function simple(string $query, bool $allow_redirect = false): \stdClass { $operator = 'LIKE %?%'; $db = DB::getInstance(); |
︙ | ︙ |
Modified src/templates/common/_sql_table.tpl from [383cca6618] to [5e045a0cbd].
1 2 3 | <table class="schema list auto {$class}"> <caption> | | | | 1 2 3 4 5 6 7 8 9 10 11 12 | <table class="schema list auto {$class}"> <caption> <strong><tt>{$table.name}</tt></strong> {if $table.comment} <small><em>({$table.comment})</em></small>{/if} </caption> <thead> <tr> {if $indexes !== null} <td>Index</td> {/if} <th>Colonne</th> |
︙ | ︙ | |||
27 28 29 30 31 32 33 | {if array_key_exists($column.name, $idx.columns)} <a class="num">{$i}{if $idx.unique}<sup>U</sup>{/if}</a> {/if} {/foreach} </td> {/if} <th>{$column.name}</th> | | | | 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | {if array_key_exists($column.name, $idx.columns)} <a class="num">{$i}{if $idx.unique}<sup>U</sup>{/if}</a> {/if} {/foreach} </td> {/if} <th>{$column.name}</th> <td>{if $column.type}{$column.type}{else}<em>Dynamique</em>{/if}</td> <td>{if $column.notnull}{else}Oui{/if}</td> <td>{if $column.dflt_value !== null}<tt>{$column.dflt_value}</tt>{elseif !$column.notnull}<em>NULL</em>{else}<em>Aucune</em>{/if}</td> <td> {if !empty($column.fk)} → {if !empty($fk_link)} <a href="?table_info={$column.fk.table}">{$column.fk.table}</a> {else} {$column.fk.table} {/if} ({$column.fk.to}) {/if} </td> <td class="comment">{$column.comment}</td> </tr> {/foreach} </tbody> </table> {if $indexes} <h2 class="ruler">Liste des index</h2> |
︙ | ︙ |
Modified src/templates/common/search/advanced.tpl from [96fd92f960] to [03c3decebc].
1 2 3 4 5 | <?php assert(isset($columns)); assert(isset($s)); assert(isset($is_admin)); $is_unprotected = $s->type == $s::TYPE_SQL_UNPROTECTED; | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 | <?php assert(isset($columns)); assert(isset($s)); assert(isset($is_admin)); $is_unprotected = $s->type == $s::TYPE_SQL_UNPROTECTED; $sql_disabled = (!$session->canAccess($session::SECTION_CONFIG, $session::ACCESS_ADMIN) && $is_unprotected); ?> {form_errors} <fieldset> {if $s->type != $s::TYPE_JSON} {if $sql_disabled} |
︙ | ︙ | |||
21 22 23 24 25 26 27 | {input type="checkbox" name="unprotected" value=1 label="Autoriser l'accès à toutes les tables de la base de données" default=$is_unprotected} <dd class="help">Attention : en cochant cette case vous autorisez la requête à lire toutes les données de toutes les tables de la base de données !</dd> {/if} <dd> {foreach from=$schema item="table"} <details> | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | {input type="checkbox" name="unprotected" value=1 label="Autoriser l'accès à toutes les tables de la base de données" default=$is_unprotected} <dd class="help">Attention : en cochant cette case vous autorisez la requête à lire toutes les données de toutes les tables de la base de données !</dd> {/if} <dd> {foreach from=$schema item="table"} <details> <summary>Table : <strong>{$table.comment}</strong> (<tt>{$table.name}</tt>)</summary> {include file="common/_sql_table.tpl" indexes=null class=null} </div> </details> {/foreach} </dd> </dl> <p class="submit"> |
︙ | ︙ |
Modified src/www/admin/common/search.php from [7d02598e37] to [e384bbac5f].
︙ | ︙ | |||
100 101 102 103 104 105 106 | } catch (UserException $e) { $form->addError($e->getMessage()); } } $is_admin = $session->canAccess($access_section, $session::ACCESS_ADMIN); | | | 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | } catch (UserException $e) { $form->addError($e->getMessage()); } } $is_admin = $session->canAccess($access_section, $session::ACCESS_ADMIN); $schema = $s->schema(); $columns = $s->getAdvancedSearch()->columns(); $columns = array_filter($columns, fn($c) => $c['label'] ?? null && $c['type'] ?? null); // remove columns only for dynamiclist $tpl->assign(compact('s', 'list', 'header', 'results', 'columns', 'is_admin', 'schema')); if ($s->target == $s::TARGET_ACCOUNTING) { $tpl->display('acc/search.tpl'); } else { $tpl->display('users/search.tpl'); } |
Modified src/www/admin/static/styles/06-tables.css from [07ef155132] to [f310e2111f].
︙ | ︙ | |||
318 319 320 321 322 323 324 325 | table tr.dragging { opacity: 0.5; outline: 4px solid var(--gBorderColor) !important; } table.dragging td { opacity: 0; } | > > > > > | 318 319 320 321 322 323 324 325 326 327 328 329 330 | table tr.dragging { opacity: 0.5; outline: 4px solid var(--gBorderColor) !important; } table.dragging td { opacity: 0; } table.schema caption { background: rgba(var(--gSecondColor), 0.2); } |