Overview
Comment: | Fix issues in dynamic fields, and drop triggers to allow changing the users table from a third-party program |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev |
Files: | files | file ages | folders |
SHA3-256: |
0b7fc4db8becf5c5548798b05626f77f |
User & Date: | bohwaz on 2022-08-07 02:47:23 |
Other Links: | branch diff | manifest | tags |
Context
2022-08-07
| ||
03:50 | Don't display progress if form fails check-in: da93edf788 user: bohwaz tags: dev | |
02:47 | Fix issues in dynamic fields, and drop triggers to allow changing the users table from a third-party program check-in: 0b7fc4db8b user: bohwaz tags: dev | |
02:23 | Fix colspan in user search check-in: c5b5dfb4c7 user: bohwaz tags: dev | |
Changes
Modified src/include/lib/Garradin/Entities/Users/DynamicField.php from [42153fc4aa] to [5a7f03a1be].
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <?php namespace Garradin\Entities\Users; use Garradin\Config; use Garradin\DB; use Garradin\Entity; use Garradin\Utils; use Garradin\Users\DynamicFields; class DynamicField extends Entity { const TABLE = 'config_users_fields'; protected ?int $id; | > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php declare(strict_types=1); namespace Garradin\Entities\Users; use Garradin\Config; use Garradin\DB; use Garradin\Entity; use Garradin\Utils; use Garradin\Entities\Files\File; use Garradin\Files\Files; use Garradin\Users\DynamicFields; class DynamicField extends Entity { const TABLE = 'config_users_fields'; protected ?int $id; |
︙ | ︙ | |||
94 95 96 97 98 99 100 | 'textarea' => 'Texte multi-lignes', 'generated' => 'Calculé', ]; const PHP_TYPES = [ 'email' => '?string', 'url' => '?string', | | | 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | 'textarea' => 'Texte multi-lignes', 'generated' => 'Calculé', ]; const PHP_TYPES = [ 'email' => '?string', 'url' => '?string', 'checkbox' => 'bool', 'date' => '?date', 'datetime' => '?DateTime', 'file' => '?string', 'password' => '?string', 'number' => '?int|float', 'tel' => '?string', 'select' => '?string', |
︙ | ︙ | |||
126 127 128 129 130 131 132 133 134 135 136 137 138 139 | 'select' => 'TEXT', 'multiple' => 'INTEGER NOT NULL DEFAULT 0', 'country' => 'TEXT', 'text' => 'TEXT', 'textarea' => 'TEXT', 'generated'=> 'GENERATED', ]; const SQL_CONSTRAINTS = [ 'checkbox' => '%1s = 1 OR %1s = 0', 'date' => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)', 'datetime' => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)', 'month' => '%1s IS NULL OR (date(%1s || \'-03\') = %1$s || \'-03\')', // Use 3rd day to avoid any potential issue with timezones ]; | > > > > > > > | 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | 'select' => 'TEXT', 'multiple' => 'INTEGER NOT NULL DEFAULT 0', 'country' => 'TEXT', 'text' => 'TEXT', 'textarea' => 'TEXT', 'generated'=> 'GENERATED', ]; const SEARCH_TYPES = [ 'email', 'text', 'textarea', 'url', ]; const SQL_CONSTRAINTS = [ 'checkbox' => '%1s = 1 OR %1s = 0', 'date' => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)', 'datetime' => '%1s IS NULL OR (date(%1$s) IS NOT NULL AND date(%1s) = %1$s)', 'month' => '%1s IS NULL OR (date(%1s || \'-03\') = %1$s || \'-03\')', // Use 3rd day to avoid any potential issue with timezones ]; |
︙ | ︙ |
Modified src/include/lib/Garradin/Entities/Users/User.php from [b1659fab5f] to [55faf09f58].
︙ | ︙ | |||
19 20 21 22 23 24 25 26 27 28 29 30 31 32 | use Garradin\Users\DynamicFields; use Garradin\Users\Session; use Garradin\Users\Users; use Garradin\Entities\Files\File; use KD2\SMTP; #[AllowDynamicProperties] class User extends Entity { const TABLE = 'users'; protected bool $_construct = false; | > | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | use Garradin\Users\DynamicFields; use Garradin\Users\Session; use Garradin\Users\Users; use Garradin\Entities\Files\File; use KD2\SMTP; use KD2\DB\EntityManager as EM; #[AllowDynamicProperties] class User extends Entity { const TABLE = 'users'; protected bool $_construct = false; |
︙ | ︙ | |||
128 129 130 131 132 133 134 135 136 | Files::delete($this->attachementsDirectory()); return parent::delete(); } public function save(bool $selfcheck = true): bool { parent::save($selfcheck); | > < | > | < < < < < | 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | Files::delete($this->attachementsDirectory()); return parent::delete(); } public function save(bool $selfcheck = true): bool { $columns = array_intersect(DynamicFields::getInstance()->getSearchColumns(), array_keys($this->_modified)); parent::save($selfcheck); // We are not using a trigger as it would make modifying the users table from outside impossible // (because the transliterate_to_ascii function does not exist) if (count($columns)) { DynamicFields::getInstance()->rebuildUserSearchCache($this->id()); } return true; } public function category(): Category { |
︙ | ︙ |
Modified src/include/lib/Garradin/Users/AdvancedSearch.php from [59c7855f9f] to [c6e199d06a].
︙ | ︙ | |||
31 32 33 34 35 36 37 | 'order' => sprintf('%s COLLATE U_NOCASE %%s', current($fields::getNameFields())), ]; $columns['is_parent'] = [ 'label' => 'Est parent', 'type' => 'boolean', 'null' => false, | | | | | | 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 | 'order' => sprintf('%s COLLATE U_NOCASE %%s', current($fields::getNameFields())), ]; $columns['is_parent'] = [ 'label' => 'Est parent', 'type' => 'boolean', 'null' => false, 'select' => 'CASE WHEN id_parent = id THEN \'Oui\' ELSE \'Non\' END', 'where' => 'id_parent IS NOT NULL AND id_parent = id', ]; $columns['is_child'] = [ 'label' => 'Est enfant', 'type' => 'boolean', 'null' => false, 'select' => 'CASE WHEN id_parent IS NOT NULL AND id_parent != id THEN \'Oui\' ELSE \'Non\' END', 'where' => 'id_parent IS NOT NULL AND id_parent != id', ]; foreach ($fields->all() as $name => $field) { /* // already included in identity if ($field->system & $field::NAME) { |
︙ | ︙ |
Modified src/include/lib/Garradin/Users/DynamicFields.php from [492eaa01ab] to [32029f163f].
1 2 3 4 5 6 7 8 | <?php namespace Garradin\Users; use Garradin\Config; use Garradin\DB; use Garradin\Utils; use Garradin\ValidationException; | > | 1 2 3 4 5 6 7 8 9 | <?php declare(strict_types=1); namespace Garradin\Users; use Garradin\Config; use Garradin\DB; use Garradin\Utils; use Garradin\ValidationException; |
︙ | ︙ | |||
16 17 18 19 20 21 22 | class DynamicFields { const PRESETS_FILE = ROOT . '/include/data/users_fields_presets.ini'; const TABLE = DynamicField::TABLE; | | | | | | 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 | class DynamicFields { const PRESETS_FILE = ROOT . '/include/data/users_fields_presets.ini'; const TABLE = DynamicField::TABLE; protected array $_fields = []; protected array $_fields_by_type = []; protected array $_fields_by_system_use = [ 'login' => [], 'password' => [], 'name' => [], 'number' => [], ]; protected array $_presets; protected array $_deleted = []; static protected $_instance; static public function getInstance(): self { if (null === self::$_instance) { self::$_instance = new self; } return self::$_instance; } |
︙ | ︙ | |||
427 428 429 430 431 432 433 434 435 436 437 438 439 440 | self::$_instance = $self; $self->createTable(); $self->createIndexes(); $self->copy('membres', User::TABLE, $fields); return $self; } public function isText(string $field) { $type = $this->_fields[$field]->type; return DynamicField::SQL_TYPES[$type] == 'TEXT'; | > > | 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 | self::$_instance = $self; $self->createTable(); $self->createIndexes(); $self->copy('membres', User::TABLE, $fields); $self->rebuildSearchTable(true); return $self; } public function isText(string $field) { $type = $this->_fields[$field]->type; return DynamicField::SQL_TYPES[$type] == 'TEXT'; |
︙ | ︙ | |||
549 550 551 552 553 554 555 | } $sql = sprintf("CREATE TABLE %s\n(\n\t%s\n);", $table_name, implode("\n\t", $create)); return $sql; } /** | | | | | < < < < < < < < < < < < < < < < | | | > | > | > | < | | > | | | > > | < | | < < < < < < | 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 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 | } $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)); if ($function) { $source = array_map(fn($a) => $function . '(' . $a . ')', $source); } return sprintf('INSERT INTO %s (id, %s) SELECT id, %s FROM %s;', $new_table_name, implode(', ', array_map([$db, 'quoteIdentifier'], $fields)), implode(', ', $source), $old_table_name ); } public function copy(string $old_table_name, string $new_table_name = User::TABLE, ?array $fields = null): void { $sql = $this->getSQLCopy($old_table_name, $new_table_name, $fields); DB::getInstance()->exec($sql); } public function create(string $table_name = User::TABLE) { $db = DB::getInstance(); $db->begin(); $this->createTable($table_name); $this->createIndexes($table_name); $db->commit(); } public function createTable(string $table_name = User::TABLE): void { $db = DB::getInstance(); $schema = $this->getSQLSchema($table_name); $db->exec($schema); } public function createIndexes(string $table_name = User::TABLE): void { $id_field = null; $db = DB::getInstance(); |
︙ | ︙ | |||
666 667 668 669 670 671 672 673 674 675 676 | // Création de l'index unique $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_id_field ON %s (%s%s);', $table_name, $id_field, $collation)); } $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_number ON %s (%s);', $table_name, $this->getNumberField())); $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_category ON %s (id_category);', $table_name)); } /** * Enregistre les changements de champs en base de données | > < | > > > > > > > > > > > > > > > > | > > | > > > | | > | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > | 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 | // Création de l'index unique $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_id_field ON %s (%s%s);', $table_name, $id_field, $collation)); } $db->exec(sprintf('CREATE UNIQUE INDEX IF NOT EXISTS users_number ON %s (%s);', $table_name, $this->getNumberField())); $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_category ON %s (id_category);', $table_name)); $db->exec(sprintf('CREATE INDEX IF NOT EXISTS users_parent ON %s (id_parent);', $table_name)); } /** * Enregistre les changements de champs en base de données */ public function rebuildUsersTable(array $fields): void { $db = DB::getInstance(); $fields = array_combine($fields, $fields); // Generated fields cannot be copied foreach ($this->_fields as $f) { if (DynamicField::SQL_TYPES[$f->type] == 'GENERATED') { unset($fields[$f->name]); } } // Always copy system fields $system_fields = array_keys(DynamicField::SYSTEM_FIELDS); $fields = array_merge(array_combine($system_fields, $system_fields), $fields); $in_transaction = $db->inTransaction(); if (!$in_transaction) { $db->beginSchemaUpdate(); } $this->createTable(User::TABLE . '_tmp'); $this->copy(User::TABLE, User::TABLE . '_tmp', $fields); $db->exec(sprintf('DROP TABLE IF EXISTS %s;', User::TABLE)); $db->exec(sprintf('ALTER TABLE %s_tmp RENAME TO %1$s;', User::TABLE)); $this->createIndexes(User::TABLE); $this->rebuildSearchTable(false); if (!$in_transaction) { $db->commitSchemaUpdate(); } } public function rebuildSearchTable(bool $from_users_table = true): void { $db = DB::getInstance(); $db->begin(); $search_table = User::TABLE . '_search'; $columns = $this->getSearchColumns(); $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 . '_tmp', User::TABLE, implode(",\n\t", $columns)); $db->exec($sql); if ($from_users_table) { // This is slower but is necessary sometimes $sql = $this->getSQLCopy(User::TABLE, $search_table . '_tmp', $this->getSearchColumns(), 'transliterate_to_ascii'); } else { $sql = $this->getSQLCopy($search_table, $search_table . '_tmp', $this->getSearchColumns()); } $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 { $db = DB::getInstance(); $columns = $this->getSearchColumns(); $keys = array_map([$db, 'quoteIdentifier'], $columns); $copy = array_map(fn($c) => sprintf('transliterate_to_ascii(%s)', $c), $keys); $sql = sprintf('INSERT OR REPLACE INTO %s_search (id, %s) SELECT id, %s FROM %1$s WHERE id = %d;', User::TABLE, implode(', ', $keys), implode(', ', $copy), $id ); $db->exec($sql); } public function add(DynamicField $df) { $this->_fields[$df->name] = $df; $this->reloadCache(); } |
︙ | ︙ | |||
740 741 742 743 744 745 746 | if (count($this->_fields_by_system_use['password']) != 1) { throw new ValidationException('Un seul champ peut être défini comme mot de passe'); } $rebuild = false; $db = DB::getInstance(); | > > > | > > > > > | | > | 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 | if (count($this->_fields_by_system_use['password']) != 1) { throw new ValidationException('Un seul champ peut être défini comme mot de passe'); } $rebuild = false; $db = DB::getInstance(); // We need to disable foreign keys BEFORE we start the transaction // this means that the config_users_fields table CANNOT have any foreign keys $db->beginSchemaUpdate(); $copy = []; foreach ($this->_fields as $field) { if (!$field->exists()) { $rebuild = true; } else { $copy[] = $field->name; } if ($field->isModified()) { $field->save(); } } foreach ($this->_deleted as $f) { $f->delete(); $rebuild = true; } $this->_deleted = []; if ($rebuild && $allow_rebuild) { // FIXME/TODO: use ALTER TABLE ... DROP COLUMN for SQLite 3.35.0+ // some conditions apply // https://www.sqlite.org/lang_altertable.html#altertabdropcol $this->rebuildUsersTable($copy); } $db->commitSchemaUpdate(); $this->reload(); } public function setOrderAll(array $order) { foreach (array_values($order) as $sort => $key) { if (!array_key_exists($key, $this->_fields)) { |
︙ | ︙ |
Modified src/www/admin/config/fields/delete.php from [75bff7061a] to [a43616dbad].
1 2 3 4 5 6 7 8 | <?php namespace Garradin; use Garradin\Users\DynamicFields; require_once __DIR__ . '/../_inc.php'; $csrf_key = 'change_fields_delete'; | > > | 1 2 3 4 5 6 7 8 9 10 | <?php declare(strict_types=1); namespace Garradin; use Garradin\Users\DynamicFields; require_once __DIR__ . '/../_inc.php'; $csrf_key = 'change_fields_delete'; |
︙ | ︙ |