From 7c4c40906099244c966ea9eb3e412fea8d17d28c Mon Sep 17 00:00:00 2001
From: Philipp <admin@philipp.info>
Date: Thu, 29 Dec 2022 22:36:08 +0100
Subject: [PATCH] Change key-value table - Make "k" as primary key - Added
"updated_at"
---
database.sql | 22 +++++++++--
doc/database/db_key-value.md | 17 ++++-----
.../Type/DBKeyValueStorage.php | 5 ++-
static/dbstructure.config.php | 7 ++--
.../KeyValueStorage/DBKeyValueStorageTest.php | 38 +++++++++++++++++--
5 files changed, 68 insertions(+), 21 deletions(-)
diff --git a/database.sql b/database.sql
index 4384d44f86..892de72f1b 100644
--- a/database.sql
+++ b/database.sql
@@ -843,11 +843,10 @@ CREATE TABLE IF NOT EXISTS `intro` (
-- TABLE key-value
--
CREATE TABLE IF NOT EXISTS `key-value` (
- `id` int unsigned NOT NULL auto_increment COMMENT '',
- `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
+ `k` varbinary(50) NOT NULL COMMENT '',
`v` mediumtext COMMENT '',
- PRIMARY KEY(`id`),
- UNIQUE INDEX `k` (`k`)
+ `updated_at` int unsigned NOT NULL COMMENT 'timestamp of the last update',
+ PRIMARY KEY(`k`)
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='A key value storage';
--
@@ -1843,6 +1842,21 @@ CREATE TABLE IF NOT EXISTS `worker-ipc` (
PRIMARY KEY(`key`)
) ENGINE=MEMORY DEFAULT COLLATE utf8mb4_general_ci COMMENT='Inter process communication between the frontend and the worker';
+--
+-- TABLE advancedcontentfilter_rules
+--
+CREATE TABLE IF NOT EXISTS `advancedcontentfilter_rules` (
+ `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented rule id',
+ `uid` int unsigned NOT NULL COMMENT 'Owner user id',
+ `name` varchar(255) NOT NULL COMMENT 'Rule name',
+ `expression` mediumtext NOT NULL COMMENT 'Expression text',
+ `serialized` mediumtext NOT NULL COMMENT 'Serialized parsed expression',
+ `active` boolean NOT NULL DEFAULT '1' COMMENT 'Whether the rule is active or not',
+ `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date',
+ PRIMARY KEY(`id`),
+ INDEX `uid_active` (`uid`,`active`)
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Advancedcontentfilter addon rules';
+
--
-- VIEW application-view
--
diff --git a/doc/database/db_key-value.md b/doc/database/db_key-value.md
index a346e6827e..514fdaa698 100644
--- a/doc/database/db_key-value.md
+++ b/doc/database/db_key-value.md
@@ -6,19 +6,18 @@ A key value storage
Fields
------
-| Field | Description | Type | Null | Key | Default | Extra |
-| ----- | ----------- | ------------- | ---- | --- | ------- | -------------- |
-| id | | int unsigned | NO | PRI | NULL | auto_increment |
-| k | | varbinary(50) | NO | | | |
-| v | | mediumtext | YES | | NULL | |
+| Field | Description | Type | Null | Key | Default | Extra |
+| ---------- | ---------------------------- | ------------- | ---- | --- | ------- | ----- |
+| k | | varbinary(50) | NO | PRI | NULL | |
+| v | | mediumtext | YES | | NULL | |
+| updated_at | timestamp of the last update | int unsigned | YES | | NULL | |
Indexes
------------
-| Name | Fields |
-| ------- | --------- |
-| PRIMARY | id |
-| k | UNIQUE, k |
+| Name | Fields |
+| ------- | ------ |
+| PRIMARY | k |
Return to [database documentation](help/database)
diff --git a/src/Core/KeyValueStorage/Type/DBKeyValueStorage.php b/src/Core/KeyValueStorage/Type/DBKeyValueStorage.php
index 60e6087e28..1c9e44ce8e 100644
--- a/src/Core/KeyValueStorage/Type/DBKeyValueStorage.php
+++ b/src/Core/KeyValueStorage/Type/DBKeyValueStorage.php
@@ -89,7 +89,10 @@ class DBKeyValueStorage extends AbstractKeyValueStorage
$dbValue = ValueConversion::toDbValue($value);
- $return = $this->database->update(self::DB_KEY_VALUE_TABLE, ['v' => $dbValue], ['k' => $offset], true);
+ $return = $this->database->update(self::DB_KEY_VALUE_TABLE, [
+ 'v' => $dbValue,
+ 'updated_at' => time()
+ ], ['k' => $offset], true);
if (!$return) {
throw new \Exception(sprintf('database update failed: %s', $this->database->errorMessage()));
diff --git a/static/dbstructure.config.php b/static/dbstructure.config.php
index 0128b0343e..3b3d6067c1 100644
--- a/static/dbstructure.config.php
+++ b/static/dbstructure.config.php
@@ -892,13 +892,12 @@ return [
"key-value" => [
"comment" => "A key value storage",
"fields" => [
- "id" => ["type" => "int unsigned", "not null" => "1", "extra" => "auto_increment", "primary" => "1", "comment" => ""],
- "k" => ["type" => "varbinary(50)", "not null" => "1", "default" => "", "comment" => ""],
+ "k" => ["type" => "varbinary(50)", "not null" => "1", "primary" => "1", "comment" => ""],
"v" => ["type" => "mediumtext", "comment" => ""],
+ "updated_at" => ["type" => "int unsigned", "not null" => "0", "comment" => "timestamp of the last update"],
],
"indexes" => [
- "PRIMARY" => ["id"],
- "k" => ["UNIQUE", "k"],
+ "PRIMARY" => ["k"],
],
],
"locks" => [
diff --git a/tests/src/Core/KeyValueStorage/DBKeyValueStorageTest.php b/tests/src/Core/KeyValueStorage/DBKeyValueStorageTest.php
index 16fa9ab7e7..967da78a46 100644
--- a/tests/src/Core/KeyValueStorage/DBKeyValueStorageTest.php
+++ b/tests/src/Core/KeyValueStorage/DBKeyValueStorageTest.php
@@ -24,6 +24,7 @@ namespace Friendica\Test\src\Core\KeyValueStorage;
use Friendica\Core\Config\ValueObject\Cache;
use Friendica\Core\KeyValueStorage\Capabilities\ICanManageKeyValuePairs;
use Friendica\Core\KeyValueStorage\Type\DBKeyValueStorage;
+use Friendica\Database\Database;
use Friendica\Database\Definition\DbaDefinition;
use Friendica\Database\Definition\ViewDefinition;
use Friendica\Test\DatabaseTestTrait;
@@ -35,6 +36,9 @@ class DBKeyValueStorageTest extends KeyValueStorageTest
{
use DatabaseTestTrait;
+ /** @var Database */
+ protected $database;
+
protected function setUp(): void
{
parent::setUp();
@@ -56,9 +60,37 @@ class DBKeyValueStorageTest extends KeyValueStorageTest
$basePath = new BasePath(dirname(__FILE__, 5), $_SERVER);
- $database = new StaticDatabase($cache, new Profiler($cache), (new DbaDefinition($basePath->getPath()))->load(), (new ViewDefinition($basePath->getPath()))->load());
- $database->setTestmode(true);
+ $this->database = new StaticDatabase($cache, new Profiler($cache), (new DbaDefinition($basePath->getPath()))->load(), (new ViewDefinition($basePath->getPath()))->load());
+ $this->database->setTestmode(true);
+
+ return new DBKeyValueStorage($this->database);
+ }
+
+ /** @dataProvider dataTests */
+ public function testUpdatedAt($k, $v)
+ {
+ $instance = $this->getInstance();
+
+ $instance->set($k, $v);
+
+ self::assertEquals($v, $instance->get($k));
+ self::assertEquals($v, $instance[$k]);
+
+ $entry = $this->database->selectFirst(DBKeyValueStorage::DB_KEY_VALUE_TABLE, ['updated_at'], ['k' => $k]);
+ self::assertNotEmpty($entry);
+
+ $updateAt = $entry['updated_at'];
+
+ $instance->set($k, 'another_value');
+
+ self::assertEquals('another_value', $instance->get($k));
+ self::assertEquals('another_value', $instance[$k]);
+
+ $entry = $this->database->selectFirst(DBKeyValueStorage::DB_KEY_VALUE_TABLE, ['updated_at'], ['k' => $k]);
+ self::assertNotEmpty($entry);
+
+ $updateAtAfter = $entry['updated_at'];
- return new DBKeyValueStorage($database);
+ self::assertLessThanOrEqual($updateAt, $updateAtAfter);
}
}
--
2.39.5