Overview
Comment:Add SQL view in config to see what's going on
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA1: 18f1a9262769199de70e98b7f04154075e7b3fa0
User & Date: bohwaz on 2021-01-19 13:35:31
Other Links: manifest | tags
Context
2021-01-19
14:14
Fix [3eb74d23ce1656cf9b0a8a29cd33a80f793630f0]: transfer old fees to new year if possible, or alert that some manual operation needs to be done check-in: 4ae6254acd user: bohwaz tags: trunk, stable, 1.0.3
13:35
Add SQL view in config to see what's going on check-in: 18f1a92627 user: bohwaz tags: trunk, stable
2021-01-18
18:33
Display unselected if there is no valid year selected check-in: 4a56b00519 user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/DynamicList.php from [68c794c644] to [35e6a8d407].

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
	protected $count = 'COUNT(*)';
	protected $desc = true;
	protected $per_page = 100;
	protected $page = 1;

	private $count_result;

	public function __construct(array $columns, string $tables, string $conditions)
	{
		$this->columns = $columns;
		$this->tables = $tables;
		$this->conditions = $conditions;
		$this->order = key($columns);
	}








|







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
	protected $count = 'COUNT(*)';
	protected $desc = true;
	protected $per_page = 100;
	protected $page = 1;

	private $count_result;

	public function __construct(array $columns, string $tables, string $conditions = '1')
	{
		$this->columns = $columns;
		$this->tables = $tables;
		$this->conditions = $conditions;
		$this->order = key($columns);
	}

Modified src/include/lib/Garradin/Recherche.php from [d53f7c5508] to [7a7550c4f7].

573
574
575
576
577
578
579



















580
581
582
583
584
585
586
		else {
			$sql_query = sprintf('SELECT id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;',
				$query_columns, $target, $query_groups, $order, $desc, (int) $limit);
		}

		return $sql_query;
	}




















	/**
	 * Lancer une recherche SQL
	 */
	public function searchSQL(string $target, $query, array $force_select = null, bool $no_limit = false, bool $unprotected = false)
	{
		if (!in_array($target, self::TARGETS, true))







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







573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
		else {
			$sql_query = sprintf('SELECT id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;',
				$query_columns, $target, $query_groups, $order, $desc, (int) $limit);
		}

		return $sql_query;
	}

	static public function rawSQL(string $query, array $allowed_tables = null, bool $no_limit = false): array
	{
		if (!$no_limit && !preg_match('/LIMIT\s+\d+/i', $query))
		{
			$query = preg_replace('/;?\s*$/', '', $query);
			$query .= ' LIMIT 100';
		}

		$st = DB::getInstance()->protectSelect($allowed_tables, $query);
		$res = $st->execute();
		$out = [];

		while ($row = $res->fetchArray(\SQLITE3_ASSOC)) {
			$out[] = (object) $row;
		}

		return $out;
	}

	/**
	 * Lancer une recherche SQL
	 */
	public function searchSQL(string $target, $query, array $force_select = null, bool $no_limit = false, bool $unprotected = false)
	{
		if (!in_array($target, self::TARGETS, true))

Modified src/templates/admin/config/_menu.tpl from [23c20c4779] to [79a96b7037].

2
3
4
5
6
7
8


9
10
11
12
13
	<ul>
		<li{if $current == 'index'} class="current"{/if}><a href="{$admin_url}config/">Général</a></li>
		<li{if $current == 'categories'} class="current"{/if}><a href="{$admin_url}config/categories/">Catégories de membres</a></li>
		<li{if $current == 'fiches_membres'} class="current"{/if}><a href="{$admin_url}config/membres.php">Fiche des membres</a></li>
		<li{if $current == 'site'} class="current"{/if}><a href="{$admin_url}config/site.php">Site public</a></li>
		<li{if $current == 'donnees'} class="current"{/if}><a href="{$admin_url}config/donnees/">Sauvegarde et restauration</a></li>
		<li{if $current == 'plugins'} class="current"{/if}><a href="{$admin_url}config/plugins.php">Extensions</a></li>


		{if ENABLE_TECH_DETAILS}
		<li{if $current == 'logs'} class="current"{/if}><a href="{$admin_url}config/logs.php?type=errors">Journaux</a></li>
		{/if}
	</ul>
</nav>







>
>





2
3
4
5
6
7
8
9
10
11
12
13
14
15
	<ul>
		<li{if $current == 'index'} class="current"{/if}><a href="{$admin_url}config/">Général</a></li>
		<li{if $current == 'categories'} class="current"{/if}><a href="{$admin_url}config/categories/">Catégories de membres</a></li>
		<li{if $current == 'fiches_membres'} class="current"{/if}><a href="{$admin_url}config/membres.php">Fiche des membres</a></li>
		<li{if $current == 'site'} class="current"{/if}><a href="{$admin_url}config/site.php">Site public</a></li>
		<li{if $current == 'donnees'} class="current"{/if}><a href="{$admin_url}config/donnees/">Sauvegarde et restauration</a></li>
		<li{if $current == 'plugins'} class="current"{/if}><a href="{$admin_url}config/plugins.php">Extensions</a></li>
		<li{if $current == 'sql'} class="current"{/if}><a href="{$admin_url}config/sql.php">SQL</a></li>

		{if ENABLE_TECH_DETAILS}
		<li{if $current == 'logs'} class="current"{/if}><a href="{$admin_url}config/logs.php?type=errors">Journaux</a></li>
		{/if}
	</ul>
</nav>

Added src/templates/admin/config/sql.tpl version [825b5e0b95].









































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
{include file="admin/_head.tpl" title="SQL" current="config" custom_css=["styles/config.css"]}

{include file="admin/config/_menu.tpl" current="sql"}

{form_errors}

{if $query}
	<h2 class="ruler">Requête SQL</h2>

	<form method="post" action="{$self_url}">
		<fieldset>
			<legend>Requête SQL</legend>
			<dl>
				{input type="textarea" cols="70" rows="10" name="query" default=$query}
			</dl>
			<p class="submit">
				{button type="submit" name="run" label="Exécuter" shape="search" class="main"}
			</p>
		</fieldset>
	</form>

	{if !empty($result)}

		<p class="help">{$result|count} résultats trouvés pour cette requête.</p>
		<table class="list search">
			<thead>
				<tr>
					{foreach from=$result_header item="label"}
						<td>{$label}</td>
					{/foreach}
					<td></td>
				</tr>
			</thead>
			<tbody>
				{foreach from=$result item="row"}
					<tr>
						{foreach from=$row key="key" item="value"}
							<td>
								{if null == $value}
									<em>NULL</em>
								{else}
									{$value}
								{/if}
							</td>
						{/foreach}
					</tr>
				{/foreach}
			</tbody>
		</table>

	{elseif $result !== null}

		<p class="block alert">
			Aucun résultat trouvé.
		</p>

	{/if}

{elseif $table}
	<h2 class="ruler">Table : {$table}</h2>

	{include file="common/dynamic_list_head.tpl"}

		{foreach from=$list->iterate() item="row"}
			<tr>
				{foreach from=$row item="value"}
				<td>
					{if null == $value}
						<em>NULL</em>
					{else}
						{$value}
					{/if}
				</td>
				{/foreach}
				<td></td>
			</tr>
		{/foreach}

		</tbody>
	</table>

	{pagination url=$list->paginationURL() page=$list.page bypage=$list.per_page total=$list->count()}

{else}

	<p class="help block">
		Cette page vous permet de visualiser les données brutes de la base de données.
	</p>

	<form method="post" action="{$self_url}">
		<fieldset>
			<legend>Requête SQL</legend>
			<dl>
				{input type="textarea" cols="70" rows="10" name="query" default=$query}
			</dl>
			<p class="submit">
				{button type="submit" name="run" label="Exécuter" shape="search" class="main"}
			</p>
		</fieldset>
	</form>

	<h2 class="ruler">Liste des tables</h2>

	<dl class="describe">
		{foreach from=$tables_list key="name" item="table"}
			<dt><a href="?table={$name}">{$name}</a></dt>
			<dd><em>{$table.count} lignes</em></dd>
			<dd><pre>{$table.sql}</pre></dd>
		{/foreach}
	</dl>

	<h2 class="ruler">Liste des index</h2>

	<dl class="describe">
		{foreach from=$index_list key="name" item="sql"}
			<dt>{$name}</dt>
			<dd><pre>{$sql}</pre></dd>
		{/foreach}
	</dl>

	<h2 class="ruler">Liste des triggers</h2>

	<dl class="describe">
		{foreach from=$triggers_list key="name" item="sql"}
			<dt>{$name}</dt>
			<dd><pre>{$sql}</pre></dd>
		{/foreach}
	</dl>

{/if}

{include file="admin/_foot.tpl"}

Added src/www/admin/config/sql.php version [a8f0ac9d7b].

























































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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
56
57
58
59
60
<?php
namespace Garradin;

use KD2\ErrorManager;

require_once __DIR__ . '/_inc.php';

$list = null;
$table = qg('table');
$query = f('query');

$db = DB::getInstance();
$tables_list = $db->getGrouped('SELECT name, sql, NULL AS count FROM sqlite_master WHERE type = \'table\' ORDER BY name;');
$index_list = null;
$triggers_list = null;
$result = null;
$result_header = null;

if ($table) {
	$all_columns = $db->get(sprintf('PRAGMA table_info(%s);', $db->quoteIdentifier($table)));

	if (!$all_columns) {
		throw new UserException('This table does not exist');
	}

	$columns = [];

	foreach ($all_columns as $c) {
		$columns[$c->name] = ['label' => $c->name];
	}

	$list = new DynamicList($columns, $table);
	$list->orderBy(key($columns), false);
	$list->loadFromQueryString();
}
elseif ($query) {
	try {
		$result = Recherche::rawSQL($query);

		if (count($result)) {
			$result_header = array_keys((array)reset($result));
		}
	}
	catch (\Exception $e) {
		$form->addError($e->getMessage());
	}
}
else {
	foreach ($tables_list as $name => &$data) {
		$data->count = $db->count($name);
	}

	unset($data);
	$index_list = $db->getAssoc('SELECT name, sql FROM sqlite_master WHERE type = \'index\' AND name NOT LIKE \'sqlite_%\' ORDER BY name;');
	$triggers_list = $db->getAssoc('SELECT name, sql FROM sqlite_master WHERE type = \'trigger\' ORDER BY name;');
}

$tpl->assign(compact('index_list', 'triggers_list', 'tables_list', 'query', 'table', 'list', 'result', 'result_header'));

$tpl->display('admin/config/sql.tpl');