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: 5a288b21b9b44fa26cb4166dd32d78b2b40d9f5b4d644468b9ff513acce1c56d
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
225
226


227



228
229
230
231
232
233

234
235



236
237
238
239
240
241
242
		return (object) [
			'groups' => $query,
			'order' => 'id',
			'desc' => true,
		];
	}

	public function schema(): array
	{


		$tables = ['acc_transactions', 'acc_transactions_lines', 'acc_accounts', 'acc_years', 'acc_projects'];



		$out = [];
		$db = DB::getInstance();

		foreach ($tables as $table) {
			$out[$table] = $db->getTableSchema($table);
		}


		return $out;



	}

	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







|

>
>
|
>
>
>
|
<
|
<
<
|
>
|
|
>
>
>







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



26


27
28
29
30
31
32
33
34
35
	 * - '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 schema of supported tables


	 */
	abstract public function schema(): array;

	/**
	 * Builds a DynamicList object from the supplied search groups
	 */
	abstract public function make(string $query): DynamicList;

	/**







>
>
>
|
>
>

|







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
224

225


226
227
228
229

230
231
232
233
234
235
236
		}
	}

	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;
		}

		if ($this->target == self::TARGET_ACCOUNTING) {

			return ['acc_transactions' => null, 'acc_transactions_lines' => null, 'acc_accounts' => null, 'acc_charts' => null, 'acc_years' => null, 'acc_transactions_users' => null, 'acc_projects' => null];


		}
		else {
			return ['users' => null, 'users_search' => null, 'users_categories' => null];
		}

	}

	public function getGroups(): array
	{
		if ($this->type != self::TYPE_JSON) {
			throw new \LogicException('Only JSON searches can use this method');
		}







>
>
>
>
>
>
>
>
>
>
>
>
>
>







|
>
|
>
>

<
<
|
>







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
146
147
148

149




150
151
152
153
154
155
156
157

158
159


160
161
162
163
164
165
166
			'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 schema(): array
	{
		$db = DB::getInstance();

		$r = $db->query(sprintf('SELECT name, sql FROM sqlite_master WHERE %s ORDER BY name;', $db->where('name', ['users', 'users_categories'])));




		
		$schema = [];
		$row = [];
		while ($row = $r->fetchArray(\SQLITE3_ASSOC)) {
			$schema[] = array_merge($row, (['comment' => null, 'columns' => []]));
			// Still need to load columns definition here to match Accounting\AdvancedSearch::schema() method
			// See KD2\DB\SQLite3::getTableSchema()
		}


		return $schema;


	}

	public function simple(string $query, bool $allow_redirect = false): \stdClass
	{
		$operator = 'LIKE %?%';
		$db = DB::getInstance();








|

|
>
|
>
>
>
>
|
<
<
<
<
<
<
|
>
|
|
>
>







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
4
5
6
7
8
9
10
11
12

<table class="schema list auto {$class}">
	<caption>
		{$table.name}
		{if $table.comment}<br /><small><em>{$table.comment}</em></small>{/if}
	</caption>
	<thead>
		<tr>
		{if $indexes !== null}
			<td>Index</td>
		{/if}
			<th>Colonne</th>



|
|







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
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>{$column.type}</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)}
					&rarr;
					{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>{$column.comment}</td>
		</tr>
	{/foreach}
	</tbody>
</table>

{if $indexes}
<h2 class="ruler">Liste des index</h2>







|













|







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)}
					&rarr;
					{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
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 = !$is_admin || (!$session->canAccess($session::SECTION_CONFIG, $session::ACCESS_ADMIN) && $is_unprotected);
?>

{form_errors}

<fieldset>
{if $s->type != $s::TYPE_JSON}
	{if $sql_disabled}





|







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
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&nbsp;!</dd>
			{/if}

			<dd>
				{foreach from=$schema item="table"}
				<details>
					<summary>Schéma&nbsp;: <strong>{$table.name}</strong> (<em>{$table.comment}</em>)</summary>
					{include file="common/_sql_table.tpl" indexes=null class=null}
					</div>
				</details>
				{/foreach}
			</dd>
		</dl>
		<p class="submit">







|







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&nbsp;!</dd>
			{/if}

			<dd>
				{foreach from=$schema item="table"}
				<details>
					<summary>Table&nbsp;: <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
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->getAdvancedSearch()->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');
}







|











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);

}