Overview
Comment:Use users_search table in search
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA3-256: f9ce20c14d45d45f6d8f25eb1b85fd144bfc5699e500d53e54e90ed3268244fa
User & Date: bohwaz on 2022-08-07 23:19:32
Other Links: branch diff | manifest | tags
Context
2022-08-08
00:08
Fix users_search indexes check-in: e7783eafbe user: bohwaz tags: dev
2022-08-07
23:19
Use users_search table in search check-in: f9ce20c14d user: bohwaz tags: dev
22:57
Change wording for parent/children feature check-in: 2ab90c453d user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/Entities/Search.php from [7f67419860] to [5235b4f0c6].

197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
			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];
		}
		else {
			return ['users' => null, 'users_categories' => null];
		}
	}

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







|







197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
			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];
		}
		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');

Modified src/include/lib/Garradin/Entities/Users/DynamicField.php from [bf1dfa7815] to [970a86d524].

202
203
204
205
206
207
208





209
210
211
212
213
214
215
	{
		if ($this->system & self::PASSWORD || $this->system & self::NUMBER || $this->system & self::NAME || $this->system & self::LOGIN) {
			return false;
		}

		return true;
	}






	public function selfCheck(): void
	{
		// Disallow name change if the field exists
		if ($this->exists()) {
			$this->assert(!$this->isModified('name'));
			$this->assert(!$this->isModified('type'));







>
>
>
>
>







202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
	{
		if ($this->system & self::PASSWORD || $this->system & self::NUMBER || $this->system & self::NAME || $this->system & self::LOGIN) {
			return false;
		}

		return true;
	}

	public function hasSearchCache(): bool
	{
		return in_array($this->type, DynamicField::SEARCH_TYPES);
	}

	public function selfCheck(): void
	{
		// Disallow name change if the field exists
		if ($this->exists()) {
			$this->assert(!$this->isModified('name'));
			$this->assert(!$this->isModified('type'));

Modified src/include/lib/Garradin/Users/AdvancedSearch.php from [0dbf6a0142] to [7ff03da74e].

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

		$columns['id'] = [];

		$columns['identity'] = [
			'label'    => $fields::getNameLabel(),
			'type'     => 'text',
			'null'     => true,
			'select'   => $fields::getNameFieldsSQL(),

			'order'    => sprintf('%s COLLATE U_NOCASE %%s', current($fields::getNameFields())),
		];

		$columns['is_parent'] = [
			'label' => 'Est responsable',
			'type' => 'boolean',
			'null' => false,
			'select' => 'CASE WHEN is_parent = 1 THEN \'Oui\' ELSE \'Non\' END',
			'where' => 'is_parent %s',
		];

		$columns['is_child'] = [
			'label' => 'Est rattaché',
			'type' => 'boolean',
			'null' => false,
			'select' => 'CASE WHEN id_parent IS NOT NULL THEN \'Oui\' ELSE \'Non\' END',
			'where' => 'id_parent IS NOT NULL',
		];

		foreach ($fields->all() as $name => $field)
		{
			/*
			// already included in identity
			if ($field->system & $field::NAME) {
				continue;
			}
			*/

			// nope
			if ($field->system & $field::PASSWORD) {
				continue;
			}



			$column = [
				'label'    => $field->label,
				'type'     => 'text',
				'null'     => true,


			];

			if ($fields->isText($name)) {
				$column['order'] = sprintf('%s COLLATE U_NOCASE %%s', $name);
			}

			if ($field->type == 'checkbox')







|
>
|






|
|






|
|
















>
>

|
|
|
>
>







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

		$columns['id'] = [];

		$columns['identity'] = [
			'label'    => $fields::getNameLabel(),
			'type'     => 'text',
			'null'     => true,
			'select'   => $fields::getNameFieldsSQL('u'),
			'where'    => $fields::getNameFieldsSQL('us'),
			'order'    => sprintf('us.%s %%s', current($fields::getNameFields())),
		];

		$columns['is_parent'] = [
			'label' => 'Est responsable',
			'type' => 'boolean',
			'null' => false,
			'select' => 'CASE WHEN u.is_parent = 1 THEN \'Oui\' ELSE \'Non\' END',
			'where' => 'u.is_parent %s',
		];

		$columns['is_child'] = [
			'label' => 'Est rattaché',
			'type' => 'boolean',
			'null' => false,
			'select' => 'CASE WHEN u.id_parent IS NOT NULL THEN \'Oui\' ELSE \'Non\' END',
			'where' => 'u.id_parent IS NOT NULL',
		];

		foreach ($fields->all() as $name => $field)
		{
			/*
			// already included in identity
			if ($field->system & $field::NAME) {
				continue;
			}
			*/

			// nope
			if ($field->system & $field::PASSWORD) {
				continue;
			}

			$identifier = $db->quoteIdentifier($name);

			$column = [
				'label'  => $field->label,
				'type'   => 'text',
				'null'   => true,
				'select' => sprintf('u.%s', $identifier),
				'where'  => sprintf('%s.%s %%s', $field->hasSearchCache() ? 'us' : 'u', $identifier),
			];

			if ($fields->isText($name)) {
				$column['order'] = sprintf('%s COLLATE U_NOCASE %%s', $name);
			}

			if ($field->type == 'checkbox')
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
			'order' => $column,
			'desc'  => false,
		];
	}

	public function make(string $query): DynamicList
	{
		$tables = 'users u';
		return $this->makeList($query, $tables, 'identity', false, ['id', 'identity']);
	}

	public function defaults(): \stdClass
	{
		return (object) ['groups' => [[
			'operator' => 'AND',







|







201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
			'order' => $column,
			'desc'  => false,
		];
	}

	public function make(string $query): DynamicList
	{
		$tables = 'users AS u INNER JOIN users_search AS us USING (id)';
		return $this->makeList($query, $tables, 'identity', false, ['id', 'identity']);
	}

	public function defaults(): \stdClass
	{
		return (object) ['groups' => [[
			'operator' => 'AND',

Modified src/include/lib/Garradin/Users/DynamicFields.php from [633a8f01fd] to [e50df998d6].

544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
			$create[] = $line;
		}

		$sql = sprintf("CREATE TABLE %s\n(\n\t%s\n);", $table_name, implode("\n\t", $create));
		return $sql;
	}

	/**
	 * Returns the SQL query used to create the search table
	 * This table is useful to make LIKE searches on unicode columns
	 */
	public function getSQLSearchSchema(string $table_name = null): ?string
	{
		$db = DB::getInstance();
		$search_table = $table_name ?? User::TABLE . '_search';

		$columns = $this->getSearchColumns();

		if (!count($columns)) {
			return null;
		}

		$sql = sprintf("CREATE TABLE IF NOT EXISTS %s\n(\n\tid INTEGER PRIMARY KEY NOT NULL REFERENCES %s (id) ON DELETE CASCADE,\n\t%s\n);", $search_table, $table_name, implode(",\n\t", $columns));
		$sql .= "\n";

		foreach ($columns as $column) {
			$sql .= sprintf("CREATE INDEX IF NOT EXISTS %s ON %s (%s);\n", $db->quoteIdentifier($search_table . '_' . $column), $search_table, $db->quoteIdentifier($column));
		}

		return $sql;
	}

	public function getSearchColumns(): array
	{
		$columns = [];

		foreach ($this->_fields as $key => $cfg) {
			if (in_array($cfg->type, DynamicField::SEARCH_TYPES)) {
				$columns[$key] = $key;
			}
		}

		return $columns;
	}

	public function getSQLCopy(string $old_table_name, string $new_table_name = User::TABLE, array $fields = null, string $function = null): string
	{
		$db = DB::getInstance();
		unset($fields['id']);
		$source = array_map([$db, 'quoteIdentifier'], array_keys($fields));







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<


<
|
<
<
<
<
<
<
<







544
545
546
547
548
549
550

























551
552

553







554
555
556
557
558
559
560
			$create[] = $line;
		}

		$sql = sprintf("CREATE TABLE %s\n(\n\t%s\n);", $table_name, implode("\n\t", $create));
		return $sql;
	}


























	public function getSearchColumns(): array
	{

		return array_keys(array_filter($this->_fields, fn ($f) => $f->hasSearchCache()));







	}

	public function getSQLCopy(string $old_table_name, string $new_table_name = User::TABLE, array $fields = null, string $function = null): string
	{
		$db = DB::getInstance();
		unset($fields['id']);
		$source = array_map([$db, 'quoteIdentifier'], array_keys($fields));
730
731
732
733
734
735
736
737
738
739
740
741


742
743
744
745
746
747
748
		}

		$db->exec($sql);
		$db->exec(sprintf('DROP TABLE IF EXISTS %s;', $search_table));
		$db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', $search_table));

		foreach ($columns as $column) {
			$sql .= sprintf("CREATE INDEX IF NOT EXISTS %s ON %s (%s);\n",
				$db->quoteIdentifier($search_table . '_' . $column),
				$search_table,
				$db->quoteIdentifier($column)
			);


		}

		$db->commit();
	}

	public function rebuildUserSearchCache(int $id): void
	{







|




>
>







697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
		}

		$db->exec($sql);
		$db->exec(sprintf('DROP TABLE IF EXISTS %s;', $search_table));
		$db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', $search_table));

		foreach ($columns as $column) {
			$sql = sprintf("CREATE INDEX IF NOT EXISTS %s ON %s (%s);\n",
				$db->quoteIdentifier($search_table . '_' . $column),
				$search_table,
				$db->quoteIdentifier($column)
			);

			$db->exec($sql);
		}

		$db->commit();
	}

	public function rebuildUserSearchCache(int $id): void
	{