From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on gnuweeb.org X-Spam-Level: X-Spam-Status: No, score=-0.8 required=5.0 tests=ALL_TRUSTED,DKIM_SIGNED, DKIM_VALID,DKIM_VALID_AU,DKIM_VALID_EF,NO_DNS_FOR_FROM,URIBL_BLOCKED autolearn=no autolearn_force=no version=3.4.6 Received: from localhost.localdomain (unknown [138.197.159.143]) by gnuweeb.org (Postfix) with ESMTPSA id 1B0B47FA05; Tue, 28 Jun 2022 06:21:45 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gnuweeb.org; s=default; t=1656397305; bh=qnVnqpou7DlbGD4ES/UyjK+Z0UkV4+XsWWnUrFZqlOc=; h=From:To:Cc:Subject:Date:In-Reply-To:References:From; b=nXtkKFrTj7ZTQl3arHUyYQ/XNACsKsYyk/ZDN+JfXjVVFnDrzENdp+Gi0broIca0s uf5ne2cHPy+GBT6Tc7XhEms4GtzjZwz2OLeoMstzTX5SOk7pmJjZKH7nE+363fAS4R sVVFv94HVoqWRwKrUFF90DLsXqqnrWKfSlsiDKJ7V4HmH6srTQv9y99Vc1GayCt+kn pxpzPDnx+uOH3ebAbcv7K+sICPqCnxZWGXfKifEb+vTEdVu87P6vQohJeJJX8l90vs wi2GTmo9NYFFbVn4QjY4e15FX5Vn3Ows+DY027s4b01x6aLU8JM7fSoxxvcZDI9xER b4I2z1aD10tnw== From: Alviro Iskandar Setiawan To: Ammar Faizi Cc: GNU/Weeb Mailing List , Alviro Iskandar Setiawan Subject: [RFC PATCH v1 1/1] db: Create starting DDL for saving transaction Date: Tue, 28 Jun 2022 06:21:39 +0000 Message-Id: <20220628062139.262361-2-alviro.iskandar@gnuweeb.org> X-Mailer: git-send-email 2.27.0 In-Reply-To: <20220628062139.262361-1-alviro.iskandar@gnuweeb.org> References: <20220628062139.262361-1-alviro.iskandar@gnuweeb.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: In order to keep the transaction record consistent, we can use a chaining hash system that generates the transaction ID by using its parent ID and the current transaction details. For example, to create a new transaction, we have several things to note, like: 1. date 2. description 3. commiter 4. amount [ other metadata stuff may be included ] We can then generate the transaction id by using a hash function, let's say SHA1: trx_info = date . description . commiter . amount . parent_trx_id trx_id = SHA1(info) SAVE(trx_id, date, description, commiter, amount, parent_trx_id) Note that since the current trx ID uses its parent trx ID to generate itself, it makes the situation impossible to rewrite the history without breaking the whole chain. That's the point of the consistency in this design. We can verify that the data is consistent by just re-hashing the transaction info together with its parent trx id. Also note that, for the first transaction, it doesn't have a parent trx id. So only the first transaction doesn't use a parent trx id to generate itself. Let's call it the genesis transaction. Signed-off-by: Alviro Iskandar Setiawan --- database.sql | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) create mode 100644 database.sql diff --git a/database.sql b/database.sql new file mode 100644 index 0000000..d191a45 --- /dev/null +++ b/database.sql @@ -0,0 +1,25 @@ +SET NAMES utf8; +SET time_zone = '+00:00'; +SET foreign_key_checks = 0; +SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; + +SET NAMES utf8mb4; + +DROP TABLE IF EXISTS `transactions`; +CREATE TABLE `transactions` ( + `id` bigint unsigned NOT NULL AUTO_INCREMENT, + `trx_id` binary(20) NOT NULL, + `parent_trx_id` binary(20) NOT NULL, + `committer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, + `amount` bigint unsigned NOT NULL, + `notes` text COLLATE utf8mb4_unicode_520_ci NOT NULL, + `created_at` datetime NOT NULL, + PRIMARY KEY (`id`), + KEY `committer` (`committer`), + KEY `amount` (`amount`), + KEY `created_at` (`created_at`), + KEY `trx_id` (`trx_id`), + KEY `parent_trx_id` (`parent_trx_id`), + FULLTEXT KEY `notes` (`notes`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; + -- 2.34.1