Overview
Comment:Implement experimental support for a {{#select col1, col2 FROM table WHERE id=:id ...; debug=1 :id=$id}} section in Brindille
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA3-256: 15e9813c63d67b2b19e98709181931c4b000afa6cadec846ddb80e9a65201fec
User & Date: bohwaz on 2023-02-05 16:03:24
Other Links: branch diff | manifest | tags
Context
2023-02-05
16:40
Use new dot dotation for defining arrays in skeletons check-in: ebfa0fb440 user: bohwaz tags: dev
16:03
Implement experimental support for a {{#select col1, col2 FROM table WHERE id=:id ...; debug=1 :id=$id}} section in Brindille check-in: 15e9813c63 user: bohwaz tags: dev
15:35
Provide a more helpful error message if tables parameter is missing in 'sql' section check-in: 40103768e5 user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/UserTemplate/Sections.php from [5a8e474314] to [d2377ceed9].

897
898
899
900
901
902
903




904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933

934
935
936
937
938
939
940
			'select' => '*',
			'order' => '1',
			'begin' => 0,
			'limit' => 100,
			'where' => '',
		];





		if (empty($params['tables'])) {
			throw new Brindille_Exception(sprintf('"sql" section: missing parameter "tables" on line %d', $line));
		}

		foreach ($defaults as $key => $default_value) {
			if (!isset($params[$key])) {
				$params[$key] = $default_value;
			}
		}

		// Allow for count=true, count=1 and also count="DISTINCT user_id" count="id"
		if (!empty($params['count'])) {
			$params['select'] = sprintf('COUNT(%s) AS count', $params['count'] == 1 ? '*' : $params['count']);
			$params['order'] = '1';
		}

		if (!empty($params['where']) && !preg_match('/^\s*AND\s+/i', $params['where'])) {
			$params['where'] = ' AND ' . $params['where'];
		}

		$sql = sprintf('SELECT %s FROM %s WHERE 1 %s %s %s ORDER BY %s LIMIT %d,%d;',
			$params['select'],
			$params['tables'],
			$params['where'] ?? '',
			isset($params['group']) ? 'GROUP BY ' . $params['group'] : '',
			isset($params['having']) ? 'HAVING ' . $params['having'] : '',
			$params['order'],
			$params['begin'],
			$params['limit']
		);


		$db = DB::getInstance();

		try {
			$statement = $db->protectSelect(null, $sql);

			$args = [];







>
>
>
>
|
|
|

|
|
|
|
|

|
|
|
|
|

|
|
|

|
|
|
|
|
|
|
|
|
|
>







897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
			'select' => '*',
			'order' => '1',
			'begin' => 0,
			'limit' => 100,
			'where' => '',
		];

		if (isset($params['sql'])) {
			$sql = $params['sql'];
		}
		else {
			if (empty($params['tables'])) {
				throw new Brindille_Exception(sprintf('"sql" section: missing parameter "tables" on line %d', $line));
			}

			foreach ($defaults as $key => $default_value) {
				if (!isset($params[$key])) {
					$params[$key] = $default_value;
				}
			}

			// Allow for count=true, count=1 and also count="DISTINCT user_id" count="id"
			if (!empty($params['count'])) {
				$params['select'] = sprintf('COUNT(%s) AS count', $params['count'] == 1 ? '*' : $params['count']);
				$params['order'] = '1';
			}

			if (!empty($params['where']) && !preg_match('/^\s*AND\s+/i', $params['where'])) {
				$params['where'] = ' AND ' . $params['where'];
			}

			$sql = sprintf('SELECT %s FROM %s WHERE 1 %s %s %s ORDER BY %s LIMIT %d,%d;',
				$params['select'],
				$params['tables'],
				$params['where'] ?? '',
				isset($params['group']) ? 'GROUP BY ' . $params['group'] : '',
				isset($params['having']) ? 'HAVING ' . $params['having'] : '',
				$params['order'],
				$params['begin'],
				$params['limit']
			);
		}

		$db = DB::getInstance();

		try {
			$statement = $db->protectSelect(null, $sql);

			$args = [];

Modified src/include/lib/Garradin/UserTemplate/UserTemplate.php from [293ac1234e] to [104fd9ea3c].

197
198
199
200
201
202
203























204
205
206
207
208
209
210

			if (!$in_loop) {
				throw new Brindille_Exception(sprintf('Error on line %d: break can only be used inside a section', $line));
			}

			return '<?php break; ?>';
		});























	}

	public function setSource(string $path)
	{
		$this->file = null;
		$this->path = $path;
		$this->modified = filemtime($path);







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







197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233

			if (!$in_loop) {
				throw new Brindille_Exception(sprintf('Error on line %d: break can only be used inside a section', $line));
			}

			return '<?php break; ?>';
		});


		$this->registerCompileBlock('#select', function (string $name, string $sql, Brindille $tpl, int $line) {
			$params = '';
			$i = 0;

			$sql = strtok($sql, ';');
			$extra_params = strtok(false);

			$sql = preg_replace_callback('/\{(\$.*?)\}/', function ($match) use (&$params, &$i) {
				$i++;
				$params .= ' :p' . $i . '=' . $match[1];
				return ':p' . $i;
			}, $sql);

			$params .= ' sql=' . var_export('SELECT ' . $sql, true) . ' ' . $extra_params;

			return $this->_section('sql', $params, $line);
		});

		$this->registerCompileBlock('/select', function (string $name, string $params, Brindille $tpl, int $line) {
			return $this->_close('sql', '{{/select}}');
		});
	}

	public function setSource(string $path)
	{
		$this->file = null;
		$this->path = $path;
		$this->modified = filemtime($path);