Overview
Comment:Use a transliterated duplicate table, not a FTS4 table, as it would not resolve the issue of ordering unicode names
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA3-256: a419cfbfe54b377b95934d8f7ebb7dd82afafeb675d18184018f2ea5ce4c6830
User & Date: bohwaz on 2022-01-12 00:57:14
Other Links: branch diff | manifest | tags
Context
2022-02-11
14:57
Merge with trunk check-in: a2054097bc user: bohwaz tags: dev
2022-01-12
00:57
Use a transliterated duplicate table, not a FTS4 table, as it would not resolve the issue of ordering unicode names check-in: a419cfbfe5 user: bohwaz tags: dev
00:56
Require FTS4 check-in: 0d90afb4e9 user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/DB.php from [9094fbc33a] to [effc6bd097].

55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
    }

    static public function registerCustomFunctions($db)
    {
        $db->createFunction('dirname', [Utils::class, 'dirname']);
        $db->createFunction('basename', [Utils::class, 'basename']);
        $db->createFunction('unicode_like', [self::class, 'unicodeLike']);
        $db->createFunction('unicode_case_fold', [Utils::class, 'unicodeCaseFold']);
    }

    public function version(): ?string
    {
        if (-1 === $this->_version) {
            $this->connect();
            $this->_version = self::getVersion($this->db);







|







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
    }

    static public function registerCustomFunctions($db)
    {
        $db->createFunction('dirname', [Utils::class, 'dirname']);
        $db->createFunction('basename', [Utils::class, 'basename']);
        $db->createFunction('unicode_like', [self::class, 'unicodeLike']);
        $db->createFunction('transliterate_to_ascii', [Utils::class, 'unicodeTransliterate']);
    }

    public function version(): ?string
    {
        if (-1 === $this->_version) {
            $this->connect();
            $this->_version = self::getVersion($this->db);

Modified src/include/lib/Garradin/Users/DynamicFields.php from [b2b4859b85] to [7e76f2e50c].

87
88
89
90
91
92
93
94
95






96
97
98
99
100
101
102
103
		foreach ($list as $field) {
			$labels[] = $field->label;
		}

		return implode(', ', $labels);
	}

	static public function getNameFieldsSQL(): string
	{






		return implode(' || \' \' ', array_keys(self::getInstance()->fieldsBySystemUse('name')));
	}

	static public function getEntityProperties(): array
	{
		$fields = self::getEntityTypes();
		return DynamicField::SYSTEM_FIELDS + $fields;
	}







|

>
>
>
>
>
>
|







87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
		foreach ($list as $field) {
			$labels[] = $field->label;
		}

		return implode(', ', $labels);
	}

	static public function getNameFieldsSQL(?string $prefix = null): string
	{
		$fields = self::getNameFields();

		if ($prefix) {
			$fields = array_map(fn($v) => $prefix . '.' . $v, $fields);
		}

		return implode(' || \' \' ', $fields);
	}

	static public function getEntityProperties(): array
	{
		$fields = self::getEntityTypes();
		return DynamicField::SYSTEM_FIELDS + $fields;
	}
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471

472
473
474
475
476
477
478
			}
		}

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

		$new_columns = array_map(fn ($v) => sprintf('NEW.%s', $v), $columns);

		$sql = sprintf("CREATE VIRTUAL TABLE IF NOT EXISTS %s USING fts4\n(\n\tcontent=%s,\n\ttokenize = unicode61 \"remove_diacritics=2\",\n\t%s\n);", $search_table, $table_name, implode(",\n\t", $columns));
		$sql .= "\n";

		// Triggers
		$sql .= sprintf("CREATE TRIGGER IF NOT EXISTS %s_ai AFTER INSERT ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("INSERT INTO %s (docid, %s) VALUES (NEW.rowid, %s);\n", $search_table, implode(', ', $columns), implode(', ', $new_columns));
		$sql .= "END;\n";
		$sql .= sprintf("CREATE TRIGGER IF NOT EXISTS %s_au AFTER UPDATE ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("INSERT INTO %s (docid, %s) VALUES (NEW.rowid, %s);\n", $search_table, implode(', ', $columns), implode(', ', $new_columns));
		$sql .= "END;\n";
		$sql .= sprintf("\nCREATE TRIGGER IF NOT EXISTS %s_bu BEFORE UPDATE ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("DELETE FROM %s WHERE docid = OLD.rowid;\n", $search_table);
		$sql .= "END;\n";
		$sql .= sprintf("\nCREATE TRIGGER IF NOT EXISTS %s_bd BEFORE DELETE ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("DELETE FROM %s WHERE docid = OLD.rowid;\n", $search_table);
		$sql .= "END;\n";


		return $sql;
	}

	public function getCopyFields(): array
	{
		// Champs à recopier







|

|




|


|

|
|
<
<
|
<
>







453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473


474

475
476
477
478
479
480
481
482
			}
		}

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

		$new_columns = array_map(fn ($v) => sprintf('transliterate_to_ascii(NEW.%s)', $v), $columns);

		$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";

		// Triggers
		$sql .= sprintf("CREATE TRIGGER IF NOT EXISTS %s_ai AFTER INSERT ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("REPLACE INTO %s (id, %s) VALUES (NEW.id, %s);\n", $search_table, implode(', ', $columns), implode(', ', $new_columns));
		$sql .= "END;\n";
		$sql .= sprintf("CREATE TRIGGER IF NOT EXISTS %s_au AFTER UPDATE ON %s BEGIN\n\t", $search_table, $table_name);
		$sql .= sprintf("REPLACE INTO %s (id, %s) VALUES (NEW.id, %s);\n", $search_table, implode(', ', $columns), implode(', ', $new_columns));
		$sql .= "END;\n";

		foreach ($columns as $column) {


			$sql .= sprintf("CREATE INDEX IF NOT EXISTS %s_%s ON %1\$s (%2\$s);\n", $search_table, $column);

		}

		return $sql;
	}

	public function getCopyFields(): array
	{
		// Champs à recopier

Modified src/include/lib/Garradin/Users/Session.php from [40740897cc] to [ce2b759772].

113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
	}

	protected function getUserDataForSession($id)
	{
		// Mettre à jour la date de connexion
		$this->db->preparedQuery('UPDATE users SET date_login = datetime() WHERE id = ?;', [$id]);

		$sql = sprintf('SELECT u.*, u.%s AS identite,
			c.perm_connect, c.perm_web, c.perm_users, c.perm_documents,
			c.perm_subscribe, c.perm_accounting, c.perm_config
			FROM users AS u
			INNER JOIN users_categories AS c ON u.id_category = c.id
			WHERE u.id = ? LIMIT 1;',
			$this->db->quoteIdentifier(DynamicFields::getLoginField()));

		return $this->db->first($sql, $id);
	}

	protected function storeRememberMeSelector($selector, $hash, $expiry, $user_id)
	{
		return $this->db->insert('users_sessions', [







|





|







113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
	}

	protected function getUserDataForSession($id)
	{
		// Mettre à jour la date de connexion
		$this->db->preparedQuery('UPDATE users SET date_login = datetime() WHERE id = ?;', [$id]);

		$sql = sprintf('SELECT u.*, %s AS _name,
			c.perm_connect, c.perm_web, c.perm_users, c.perm_documents,
			c.perm_subscribe, c.perm_accounting, c.perm_config
			FROM users AS u
			INNER JOIN users_categories AS c ON u.id_category = c.id
			WHERE u.id = ? LIMIT 1;',
			$this->db->quoteIdentifier(DynamicFields::getLoginField('u')));

		return $this->db->first($sql, $id);
	}

	protected function storeRememberMeSelector($selector, $hash, $expiry, $user_id)
	{
		return $this->db->insert('users_sessions', [

Modified src/include/lib/Garradin/Utils.php from [f7279d34e4] to [da8a9f5dd6].

925
926
927
928
929
930
931





932
933
934
935
936
937
938
    {
        $str = str_replace(DIRECTORY_SEPARATOR, '/', $str);
        $str = trim($str, '/');
        $str = substr($str, strrpos($str, '/'));
        $str = trim($str, '/');
        return $str;
    }






    static public function unicodeCaseComparison($a, $b): int
    {
        if (!isset(self::$collator) && function_exists('collator_create')) {
            self::$collator = \Collator::create('fr_FR');

            // This is what makes the comparison case insensitive







>
>
>
>
>







925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
    {
        $str = str_replace(DIRECTORY_SEPARATOR, '/', $str);
        $str = trim($str, '/');
        $str = substr($str, strrpos($str, '/'));
        $str = trim($str, '/');
        return $str;
    }

    static public function unicodeTransliterate($str): string
    {
        return transliterator_transliterate('Any-Latin; Latin-ASCII; Lower()', $str);
    }

    static public function unicodeCaseComparison($a, $b): int
    {
        if (!isset(self::$collator) && function_exists('collator_create')) {
            self::$collator = \Collator::create('fr_FR');

            // This is what makes the comparison case insensitive

Modified src/templates/admin/index.tpl from [03e37c3c4d] to [61cdfa284f].

1
2
3
4
5
6
7
8
{include file="admin/_head.tpl" title="Bonjour %s !"|args:$user.identite current="home"}

{$banner|raw}

<nav class="tabs">
	<ul>
		<li><a href="{$admin_url}me/">Mes informations personnelles</a></li>
		<li><a href="{$admin_url}me/services.php">Suivi de mes activités et cotisations</a></li>
|







1
2
3
4
5
6
7
8
{include file="admin/_head.tpl" title="Bonjour %s !"|args:$logged_user._name current="home"}

{$banner|raw}

<nav class="tabs">
	<ul>
		<li><a href="{$admin_url}me/">Mes informations personnelles</a></li>
		<li><a href="{$admin_url}me/services.php">Suivi de mes activités et cotisations</a></li>

Modified src/www/admin/index.php from [bb797c6a32] to [959beac1a1].

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php

namespace Garradin;

use Garradin\Web\Web;
use Garradin\Files\Files;
use Garradin\Entities\Files\File;

require_once __DIR__ . '/_inc.php';

$banner = null;
Plugin::fireSignal('accueil.banniere', ['user' => $user, 'session' => $session], $banner);

$homepage = Config::getInstance()->file('admin_homepage');

if ($homepage) {
	$homepage = $homepage->render(ADMIN_URL . 'common/files/preview.php?p=' . File::CONTEXT_DOCUMENTS . '/');
}
else {











|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php

namespace Garradin;

use Garradin\Web\Web;
use Garradin\Files\Files;
use Garradin\Entities\Files\File;

require_once __DIR__ . '/_inc.php';

$banner = null;
Plugin::fireSignal('accueil.banniere', ['user' => $session->getUser(), 'session' => $session], $banner);

$homepage = Config::getInstance()->file('admin_homepage');

if ($homepage) {
	$homepage = $homepage->render(ADMIN_URL . 'common/files/preview.php?p=' . File::CONTEXT_DOCUMENTS . '/');
}
else {