Event sourcing #9

New Issue

oysteikt · 2024-08-22T09:04:39+02:00

oysteikt commented

2024-08-22 09:04:39 +02:00

What if instead of tracking the current amount of money and the current price (state grrr), we track transactions and price changes (maybe even optionally which store someone bought a batch from?).

Also, note that with a full rewrite of the database logic, we should also be fixing #5 in the same run

A quick status update

A lot of the basic accumulator SQL queries are finished and working, a wide variety of tests has been added to smooth out edge cases and clearly define the semantics of the program

What's been done

New database models
New database queries
Documentation for the new economics model (economics.md)
CI/CD for running tests
Tests for models and queries (mostly) (coverage, results)
Joint transactions
New transaction type THROW_PRODUCT and it's related query (mostly, there are test failures)
New product_owners query (mostly, there are test failures)
Joint transaction tests and related user_balance/product_stock tests
Implement any other remaining tests
Fix remaining test failures
Benchmarking for large queries (e.g. generate n relevant random transactions with a set seed, and benchmark query)

What's missing

This is stuff that we need to fix before putting this into production

Cache generation - We need to generate checkpoints in the database to avoid accumulating data since the dawn of time. This should not be too hard, the accumulative queries already has a "use_cache" flag, so we can just turn it off, accumulate and then update the cache. This also needs tests, and a timer of some sort.
Test the cache generation
Rewrite the menus to use the new backend functions - The new "backend" does not fit the old API exactly, so a lot of stuff needs to be modified. I have been thinking that we should just use the opportunity for libdibification (#13) since so much stuff needs to be edited, but that also makes the task bigger.
Review what database indices could be useful for speeding up queries
Test the new queries against a postgresql database before deployment
Deployment - duh.
- We need a script to migrate the old state into the new database schema

Followup stuff

This is stuff that can be added after this is done, but is not needed to put the new code into production

Implement menus for the THROW_PRODUCT transactions
Implement user_products query
Add a flag to let individual tests export their database as an sqlite database artifact
Document writing and running tests (add note about how to print sql statements)
Document how the event log and the caching works
Fix formatting hook for OperationError SQL statements (see tests/conftest.py)
Benchmarking for caching
Fast-forward syncing of databases (ref #17)
Make toggling 'hidden' into a transaction
Write a query to determine the state of the economy
Menu items to inspect statistics about the cache
Manual recaching all the way from the bottom
Verification commands to ensure sane state through the entire event log
Multithreaded test runner
Consider moving large and reused sub-expressions to views
Consider using materialized views on postgres to cache subcalculations
Create a forward-running state calculator to update cache instead of recursively querying backwards

What if instead of tracking the current amount of money and the current price (state grrr), we track transactions and price changes (maybe even optionally which store someone bought a batch from?). Also, note that with a full rewrite of the database logic, we should also be fixing #5 in the same run --- ### A quick status update A lot of the basic accumulator SQL queries are finished and working, a wide variety of tests has been added to smooth out edge cases and clearly define the semantics of the program ### What's been done - [X] New database models - [X] New database queries - [X] Documentation for the new economics model ([economics.md](https://git.pvv.ntnu.no/Projects/dibbler/src/branch/event-sourcing/docs/economics.md)) - [X] CI/CD for running tests - [X] Tests for models and queries (mostly) ([coverage](https://pages.pvv.ntnu.no/Projects/dibbler/event-sourcing/coverage/index.html), [results](https://pages.pvv.ntnu.no/Projects/dibbler/event-sourcing/test-report)) - [X] Joint transactions - [X] New transaction type `THROW_PRODUCT` and it's related query (mostly, there are test failures) - [X] New `product_owners` query (mostly, there are test failures) - [X] Joint transaction tests and related `user_balance`/`product_stock` tests - [X] Implement any other remaining tests - [X] Fix remaining test failures - [X] Benchmarking for large queries (e.g. generate n relevant random transactions with a set seed, and benchmark query) ### What's missing This is stuff that we need to fix before putting this into production - [ ] Cache generation - We need to generate checkpoints in the database to avoid accumulating data since the dawn of time. This should not be too hard, the accumulative queries already has a "use_cache" flag, so we can just turn it off, accumulate and then update the cache. This also needs tests, and a timer of some sort. - [ ] Test the cache generation - [ ] Rewrite the menus to use the new backend functions - The new "backend" does not fit the old API exactly, so a lot of stuff needs to be modified. I have been thinking that we should just use the opportunity for libdibification (#13) since so much stuff needs to be edited, but that also makes the task bigger. - [ ] Review what database indices could be useful for speeding up queries - [ ] Test the new queries against a postgresql database before deployment - [ ] Deployment - duh. - [ ] We need a script to migrate the old state into the new database schema ### Followup stuff This is stuff that can be added after this is done, but is not needed to put the new code into production - Implement menus for the `THROW_PRODUCT` transactions - Implement `user_products` query - Add a flag to let individual tests export their database as an sqlite database artifact - Document writing and running tests (add note about how to print sql statements) - Document how the event log and the caching works - Fix formatting hook for `OperationError` SQL statements (see `tests/conftest.py`) - Benchmarking for caching - Fast-forward syncing of databases (ref #17) - Make toggling 'hidden' into a transaction - Write a query to determine the state of the economy - Menu items to inspect statistics about the cache - Manual recaching all the way from the bottom - Verification commands to ensure sane state through the entire event log - Multithreaded test runner - Consider moving large and reused sub-expressions to views - Consider using materialized views on postgres to cache subcalculations - Create a forward-running state calculator to update cache instead of recursively querying backwards

oysteikt added the feature request label 2024-08-22 09:04:39 +02:00

oysteikt referenced this issue

2025-05-05 14:38:19 +02:00

Add offline mode #17

oysteikt added reference event-sourcing

2025-06-05 09:35:18 +02:00

oysteikt referenced this issue

2025-06-05 11:13:16 +02:00

Add license #11

oysteikt commented

2025-12-08 13:53:15 +01:00

EDIT: moved to main description

oysteikt self-assigned this 2025-12-10 08:52:22 +01:00

oysteikt added the big label 2025-12-10 08:53:04 +01:00

oysteikt commented

2025-12-12 09:37:14 +01:00

SQL go brr

WITH RECURSIVE rec_cte_price(i, TIME, transaction_id, price, product_count) AS
  (SELECT 0 AS i,
          0 AS TIME,
          NULL AS transaction_id,
          0 AS price,
          0 AS product_count
   UNION ALL SELECT trx_subset_price.i AS i,
                    trx_subset_price.time AS TIME,
                    trx_subset_price.id AS transaction_id,
                    CASE
                        WHEN (trx_subset_price.type = 'buy_product') THEN rec_cte_price.price
                        WHEN (trx_subset_price.type = 'add_product') THEN CAST(ceil((rec_cte_price.price * max(rec_cte_price.product_count, 0) + trx_subset_price.per_product * trx_subset_price.product_count) / ((max(rec_cte_price.product_count, 0) + trx_subset_price.product_count) + 0.0)) AS INTEGER)
                        WHEN (trx_subset_price.type = 'adjust_stock') THEN rec_cte_price.price
                        ELSE rec_cte_price.price
                    END AS price,
                    CASE
                        WHEN (trx_subset_price.type = 'buy_product') THEN rec_cte_price.product_count - trx_subset_price.product_count
                        WHEN (trx_subset_price.type = 'joint') THEN rec_cte_price.product_count - trx_subset_price.product_count
                        WHEN (trx_subset_price.type = 'add_product') THEN rec_cte_price.product_count + trx_subset_price.product_count
                        WHEN (trx_subset_price.type = 'adjust_stock') THEN rec_cte_price.product_count + trx_subset_price.product_count
                        ELSE rec_cte_price.product_count
                    END AS product_count
   FROM
     (SELECT row_number() OVER (
                                ORDER BY "transaction".time ASC) AS i,
             "transaction".id AS id,
             "transaction".time AS TIME,
             "transaction".type AS TYPE,
             "transaction".product_count AS product_count,
             "transaction".per_product AS per_product
      FROM "transaction",

        (SELECT row_number() OVER (
                                   ORDER BY "transaction".time ASC) AS i,
                "transaction".id AS id,
                "transaction".amount AS amount,
                "transaction".interest_rate_percent AS interest_rate_percent,
                "transaction".penalty_multiplier_percent AS penalty_multiplier_percent,
                "transaction".penalty_threshold AS penalty_threshold,
                "transaction".product_count AS product_count,
                "transaction".product_id AS product_id,
                "transaction".time AS TIME,
                "transaction".transfer_user_id AS transfer_user_id,
                "transaction".type AS TYPE,
                trx_subset_subset.user_shares AS user_shares,
                trx_subset_subset.user_count AS user_count
         FROM
           (SELECT "transaction".id AS id,
                   NULL AS user_shares,
                   NULL AS user_count
            FROM "transaction"
            WHERE ("transaction".user_id = ?
                   AND "transaction".type IN ('add_product',
                                              'adjust_balance',
                                              'buy_product',
                                              'transfer')
                   OR "transaction".type = 'transfer'
                   AND "transaction".transfer_user_id = ?
                   OR "transaction".type IN ('throw_product',
                                             'adjust_interest',
                                             'adjust_penalty'))
              AND "transaction".time <=
                (SELECT "transaction".time
                 FROM "transaction"
                 WHERE "transaction".id = ?)
            UNION ALL SELECT "transaction".id AS id,
                             sum(CASE
                                     WHEN (count_trx.user_id = ?) THEN 1
                                     ELSE 0
                                 END) AS user_shares,
                             count(count_trx.id) AS user_count
            FROM
              (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id
               FROM "transaction"
               WHERE "transaction".type = 'joint_buy_product'
                 AND "transaction".user_id = ?
                 AND "transaction".time <=
                   (SELECT "transaction".time
                    FROM "transaction"
                    WHERE "transaction".id = ?)) AS sub_joint_transaction
            JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id
            JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id
            GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset
         JOIN "transaction" ON "transaction".id = trx_subset_subset.id
         ORDER BY "transaction".time ASC) AS trx_subset
      WHERE "transaction".type IN ('buy_product',
                                   'add_product',
                                   'adjust_stock',
                                   'joint')
        AND "transaction".product_id = trx_subset.product_id
        AND "transaction".time <=
          (SELECT "transaction".time
           FROM "transaction"
           WHERE "transaction".id = ?)
      ORDER BY "transaction".time ASC) AS trx_subset_price,
        rec_cte_price
   WHERE trx_subset_price.i = rec_cte_price.i + 1),
               rec_cte_joint_price(i, TIME, transaction_id, price, product_count) AS
  (SELECT 0 AS i,
          0 AS TIME,
          NULL AS transaction_id,
          0 AS price,
          0 AS product_count
   UNION ALL SELECT trx_subset_joint_price.i AS i,
                    trx_subset_joint_price.time AS TIME,
                    trx_subset_joint_price.id AS transaction_id,
                    CASE
                        WHEN (trx_subset_joint_price.type = 'buy_product') THEN rec_cte_joint_price.price
                        WHEN (trx_subset_joint_price.type = 'add_product') THEN CAST(ceil((rec_cte_joint_price.price * max(rec_cte_joint_price.product_count, 0) + trx_subset_joint_price.per_product * trx_subset_joint_price.product_count) / ((max(rec_cte_joint_price.product_count, 0) + trx_subset_joint_price.product_count) + 0.0)) AS INTEGER)
                        WHEN (trx_subset_joint_price.type = 'adjust_stock') THEN rec_cte_joint_price.price
                        ELSE rec_cte_joint_price.price
                    END AS price,
                    CASE
                        WHEN (trx_subset_joint_price.type = 'buy_product') THEN rec_cte_joint_price.product_count - trx_subset_joint_price.product_count
                        WHEN (trx_subset_joint_price.type = 'joint') THEN rec_cte_joint_price.product_count - trx_subset_joint_price.product_count
                        WHEN (trx_subset_joint_price.type = 'add_product') THEN rec_cte_joint_price.product_count + trx_subset_joint_price.product_count
                        WHEN (trx_subset_joint_price.type = 'adjust_stock') THEN rec_cte_joint_price.product_count + trx_subset_joint_price.product_count
                        ELSE rec_cte_joint_price.product_count
                    END AS product_count
   FROM
     (SELECT row_number() OVER (
                                ORDER BY "transaction".time ASC) AS i,
             "transaction".id AS id,
             "transaction".time AS TIME,
             "transaction".type AS TYPE,
             "transaction".product_count AS product_count,
             "transaction".per_product AS per_product
      FROM "transaction",

        (SELECT row_number() OVER (
                                   ORDER BY "transaction".time ASC) AS i,
                "transaction".id AS id,
                "transaction".amount AS amount,
                "transaction".interest_rate_percent AS interest_rate_percent,
                "transaction".penalty_multiplier_percent AS penalty_multiplier_percent,
                "transaction".penalty_threshold AS penalty_threshold,
                "transaction".product_count AS product_count,
                "transaction".product_id AS product_id,
                "transaction".time AS TIME,
                "transaction".transfer_user_id AS transfer_user_id,
                "transaction".type AS TYPE,
                trx_subset_subset.user_shares AS user_shares,
                trx_subset_subset.user_count AS user_count
         FROM
           (SELECT "transaction".id AS id,
                   NULL AS user_shares,
                   NULL AS user_count
            FROM "transaction"
            WHERE ("transaction".user_id = ?
                   AND "transaction".type IN ('add_product',
                                              'adjust_balance',
                                              'buy_product',
                                              'transfer')
                   OR "transaction".type = 'transfer'
                   AND "transaction".transfer_user_id = ?
                   OR "transaction".type IN ('throw_product',
                                             'adjust_interest',
                                             'adjust_penalty'))
              AND "transaction".time <=
                (SELECT "transaction".time
                 FROM "transaction"
                 WHERE "transaction".id = ?)
            UNION ALL SELECT "transaction".id AS id,
                             sum(CASE
                                     WHEN (count_trx.user_id = ?) THEN 1
                                     ELSE 0
                                 END) AS user_shares,
                             count(count_trx.id) AS user_count
            FROM
              (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id
               FROM "transaction"
               WHERE "transaction".type = 'joint_buy_product'
                 AND "transaction".user_id = ?
                 AND "transaction".time <=
                   (SELECT "transaction".time
                    FROM "transaction"
                    WHERE "transaction".id = ?)) AS sub_joint_transaction
            JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id
            JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id
            GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset
         JOIN "transaction" ON "transaction".id = trx_subset_subset.id
         ORDER BY "transaction".time ASC) AS trx_subset
      WHERE "transaction".type IN ('buy_product',
                                   'add_product',
                                   'adjust_stock',
                                   'joint')
        AND "transaction".product_id = trx_subset.product_id
        AND "transaction".time <=
          (SELECT "transaction".time
           FROM "transaction"
           WHERE "transaction".id = ?)
      ORDER BY "transaction".time ASC) AS trx_subset_joint_price,
        rec_cte_joint_price
   WHERE trx_subset_joint_price.i = rec_cte_joint_price.i + 1),
               rec_cte(i, TIME, transaction_id, balance, interest_rate_percent, penalty_threshold, penalty_multiplier_percent) AS
  (SELECT 0 AS i,
          0 AS TIME,
          NULL AS transaction_id,
          0 AS balance,
          100 AS interest_rate_percent,
          -100 AS penalty_threshold,
          200 AS penalty_multiplier_percent
   UNION ALL SELECT trx_subset.i AS i,
                    trx_subset.time AS TIME,
                    trx_subset.id AS transaction_id,
                    CASE
                        WHEN (trx_subset.type = 'adjust_balance') THEN rec_cte.balance + trx_subset.amount
                        WHEN (trx_subset.type = 'add_product') THEN rec_cte.balance + trx_subset.amount
                        WHEN (trx_subset.type = 'buy_product') THEN rec_cte.balance -
                               (SELECT CAST(ceil(CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0) + ((CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0)) * CAST(rec_cte.interest_rate_percent - 100 AS FLOAT)) / (100.0 + 0.0) + (((CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0)) * CAST(rec_cte.penalty_multiplier_percent - 100 AS FLOAT)) / (100.0 + 0.0)) * CAST(rec_cte.balance < rec_cte.penalty_threshold AS INTEGER)) AS INTEGER) AS ceil_1
                                FROM rec_cte_price
                                ORDER BY i DESC
                                LIMIT 1
                                OFFSET ?)
                        WHEN (trx_subset.type = 'joint') THEN rec_cte.balance -
                               (SELECT CAST(ceil(CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0) + ((CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0)) * CAST(rec_cte.interest_rate_percent - 100 AS FLOAT)) / (100.0 + 0.0) + (((CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0)) * CAST(rec_cte.penalty_multiplier_percent - 100 AS FLOAT)) / (100.0 + 0.0)) * CAST(rec_cte.balance < rec_cte.penalty_threshold AS INTEGER)) AS INTEGER) AS ceil_2
                                FROM rec_cte_joint_price
                                ORDER BY i DESC
                                LIMIT 1
                                OFFSET ?)
                        WHEN (trx_subset.type = 'transfer'
                              AND trx_subset.transfer_user_id = ?) THEN rec_cte.balance + trx_subset.amount
                        WHEN (trx_subset.type = 'transfer'
                              AND trx_subset.transfer_user_id != ?) THEN rec_cte.balance - trx_subset.amount
                        ELSE rec_cte.balance
                    END AS balance,
                    CASE
                        WHEN (trx_subset.type = 'adjust_interest') THEN trx_subset.interest_rate_percent
                        ELSE rec_cte.interest_rate_percent
                    END AS interest_rate_percent,
                    CASE
                        WHEN (trx_subset.type = 'adjust_penalty') THEN trx_subset.penalty_threshold
                        ELSE rec_cte.penalty_threshold
                    END AS penalty_threshold,
                    CASE
                        WHEN (trx_subset.type = 'adjust_penalty') THEN trx_subset.penalty_multiplier_percent
                        ELSE rec_cte.penalty_multiplier_percent
                    END AS penalty_multiplier_percent
   FROM
     (SELECT row_number() OVER (
                                ORDER BY "transaction".time ASC) AS i,
             "transaction".id AS id,
             "transaction".amount AS amount,
             "transaction".interest_rate_percent AS interest_rate_percent,
             "transaction".penalty_multiplier_percent AS penalty_multiplier_percent,
             "transaction".penalty_threshold AS penalty_threshold,
             "transaction".product_count AS product_count,
             "transaction".product_id AS product_id,
             "transaction".time AS TIME,
             "transaction".transfer_user_id AS transfer_user_id,
             "transaction".type AS TYPE,
             trx_subset_subset.user_shares AS user_shares,
             trx_subset_subset.user_count AS user_count
      FROM
        (SELECT "transaction".id AS id,
                NULL AS user_shares,
                NULL AS user_count
         FROM "transaction"
         WHERE ("transaction".user_id = ?
                AND "transaction".type IN ('add_product',
                                           'adjust_balance',
                                           'buy_product',
                                           'transfer')
                OR "transaction".type = 'transfer'
                AND "transaction".transfer_user_id = ?
                OR "transaction".type IN ('throw_product',
                                          'adjust_interest',
                                          'adjust_penalty'))
           AND "transaction".time <=
             (SELECT "transaction".time
              FROM "transaction"
              WHERE "transaction".id = ?)
         UNION ALL SELECT "transaction".id AS id,
                          sum(CASE
                                  WHEN (count_trx.user_id = ?) THEN 1
                                  ELSE 0
                              END) AS user_shares,
                          count(count_trx.id) AS user_count
         FROM
           (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id
            FROM "transaction"
            WHERE "transaction".type = 'joint_buy_product'
              AND "transaction".user_id = ?
              AND "transaction".time <=
                (SELECT "transaction".time
                 FROM "transaction"
                 WHERE "transaction".id = ?)) AS sub_joint_transaction
         JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id
         JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id
         GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset
      JOIN "transaction" ON "transaction".id = trx_subset_subset.id
      ORDER BY "transaction".time ASC) AS trx_subset,
        rec_cte
   WHERE trx_subset.i = rec_cte.i + 1)
SELECT rec_cte.balance
FROM rec_cte
ORDER BY rec_cte.i DESC
LIMIT 1
OFFSET 0

SQL go brr ```sql WITH RECURSIVE rec_cte_price(i, TIME, transaction_id, price, product_count) AS (SELECT 0 AS i, 0 AS TIME, NULL AS transaction_id, 0 AS price, 0 AS product_count UNION ALL SELECT trx_subset_price.i AS i, trx_subset_price.time AS TIME, trx_subset_price.id AS transaction_id, CASE WHEN (trx_subset_price.type = 'buy_product') THEN rec_cte_price.price WHEN (trx_subset_price.type = 'add_product') THEN CAST(ceil((rec_cte_price.price * max(rec_cte_price.product_count, 0) + trx_subset_price.per_product * trx_subset_price.product_count) / ((max(rec_cte_price.product_count, 0) + trx_subset_price.product_count) + 0.0)) AS INTEGER) WHEN (trx_subset_price.type = 'adjust_stock') THEN rec_cte_price.price ELSE rec_cte_price.price END AS price, CASE WHEN (trx_subset_price.type = 'buy_product') THEN rec_cte_price.product_count - trx_subset_price.product_count WHEN (trx_subset_price.type = 'joint') THEN rec_cte_price.product_count - trx_subset_price.product_count WHEN (trx_subset_price.type = 'add_product') THEN rec_cte_price.product_count + trx_subset_price.product_count WHEN (trx_subset_price.type = 'adjust_stock') THEN rec_cte_price.product_count + trx_subset_price.product_count ELSE rec_cte_price.product_count END AS product_count FROM (SELECT row_number() OVER ( ORDER BY "transaction".time ASC) AS i, "transaction".id AS id, "transaction".time AS TIME, "transaction".type AS TYPE, "transaction".product_count AS product_count, "transaction".per_product AS per_product FROM "transaction", (SELECT row_number() OVER ( ORDER BY "transaction".time ASC) AS i, "transaction".id AS id, "transaction".amount AS amount, "transaction".interest_rate_percent AS interest_rate_percent, "transaction".penalty_multiplier_percent AS penalty_multiplier_percent, "transaction".penalty_threshold AS penalty_threshold, "transaction".product_count AS product_count, "transaction".product_id AS product_id, "transaction".time AS TIME, "transaction".transfer_user_id AS transfer_user_id, "transaction".type AS TYPE, trx_subset_subset.user_shares AS user_shares, trx_subset_subset.user_count AS user_count FROM (SELECT "transaction".id AS id, NULL AS user_shares, NULL AS user_count FROM "transaction" WHERE ("transaction".user_id = ? AND "transaction".type IN ('add_product', 'adjust_balance', 'buy_product', 'transfer') OR "transaction".type = 'transfer' AND "transaction".transfer_user_id = ? OR "transaction".type IN ('throw_product', 'adjust_interest', 'adjust_penalty')) AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?) UNION ALL SELECT "transaction".id AS id, sum(CASE WHEN (count_trx.user_id = ?) THEN 1 ELSE 0 END) AS user_shares, count(count_trx.id) AS user_count FROM (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id FROM "transaction" WHERE "transaction".type = 'joint_buy_product' AND "transaction".user_id = ? AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?)) AS sub_joint_transaction JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset JOIN "transaction" ON "transaction".id = trx_subset_subset.id ORDER BY "transaction".time ASC) AS trx_subset WHERE "transaction".type IN ('buy_product', 'add_product', 'adjust_stock', 'joint') AND "transaction".product_id = trx_subset.product_id AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?) ORDER BY "transaction".time ASC) AS trx_subset_price, rec_cte_price WHERE trx_subset_price.i = rec_cte_price.i + 1), rec_cte_joint_price(i, TIME, transaction_id, price, product_count) AS (SELECT 0 AS i, 0 AS TIME, NULL AS transaction_id, 0 AS price, 0 AS product_count UNION ALL SELECT trx_subset_joint_price.i AS i, trx_subset_joint_price.time AS TIME, trx_subset_joint_price.id AS transaction_id, CASE WHEN (trx_subset_joint_price.type = 'buy_product') THEN rec_cte_joint_price.price WHEN (trx_subset_joint_price.type = 'add_product') THEN CAST(ceil((rec_cte_joint_price.price * max(rec_cte_joint_price.product_count, 0) + trx_subset_joint_price.per_product * trx_subset_joint_price.product_count) / ((max(rec_cte_joint_price.product_count, 0) + trx_subset_joint_price.product_count) + 0.0)) AS INTEGER) WHEN (trx_subset_joint_price.type = 'adjust_stock') THEN rec_cte_joint_price.price ELSE rec_cte_joint_price.price END AS price, CASE WHEN (trx_subset_joint_price.type = 'buy_product') THEN rec_cte_joint_price.product_count - trx_subset_joint_price.product_count WHEN (trx_subset_joint_price.type = 'joint') THEN rec_cte_joint_price.product_count - trx_subset_joint_price.product_count WHEN (trx_subset_joint_price.type = 'add_product') THEN rec_cte_joint_price.product_count + trx_subset_joint_price.product_count WHEN (trx_subset_joint_price.type = 'adjust_stock') THEN rec_cte_joint_price.product_count + trx_subset_joint_price.product_count ELSE rec_cte_joint_price.product_count END AS product_count FROM (SELECT row_number() OVER ( ORDER BY "transaction".time ASC) AS i, "transaction".id AS id, "transaction".time AS TIME, "transaction".type AS TYPE, "transaction".product_count AS product_count, "transaction".per_product AS per_product FROM "transaction", (SELECT row_number() OVER ( ORDER BY "transaction".time ASC) AS i, "transaction".id AS id, "transaction".amount AS amount, "transaction".interest_rate_percent AS interest_rate_percent, "transaction".penalty_multiplier_percent AS penalty_multiplier_percent, "transaction".penalty_threshold AS penalty_threshold, "transaction".product_count AS product_count, "transaction".product_id AS product_id, "transaction".time AS TIME, "transaction".transfer_user_id AS transfer_user_id, "transaction".type AS TYPE, trx_subset_subset.user_shares AS user_shares, trx_subset_subset.user_count AS user_count FROM (SELECT "transaction".id AS id, NULL AS user_shares, NULL AS user_count FROM "transaction" WHERE ("transaction".user_id = ? AND "transaction".type IN ('add_product', 'adjust_balance', 'buy_product', 'transfer') OR "transaction".type = 'transfer' AND "transaction".transfer_user_id = ? OR "transaction".type IN ('throw_product', 'adjust_interest', 'adjust_penalty')) AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?) UNION ALL SELECT "transaction".id AS id, sum(CASE WHEN (count_trx.user_id = ?) THEN 1 ELSE 0 END) AS user_shares, count(count_trx.id) AS user_count FROM (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id FROM "transaction" WHERE "transaction".type = 'joint_buy_product' AND "transaction".user_id = ? AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?)) AS sub_joint_transaction JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset JOIN "transaction" ON "transaction".id = trx_subset_subset.id ORDER BY "transaction".time ASC) AS trx_subset WHERE "transaction".type IN ('buy_product', 'add_product', 'adjust_stock', 'joint') AND "transaction".product_id = trx_subset.product_id AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?) ORDER BY "transaction".time ASC) AS trx_subset_joint_price, rec_cte_joint_price WHERE trx_subset_joint_price.i = rec_cte_joint_price.i + 1), rec_cte(i, TIME, transaction_id, balance, interest_rate_percent, penalty_threshold, penalty_multiplier_percent) AS (SELECT 0 AS i, 0 AS TIME, NULL AS transaction_id, 0 AS balance, 100 AS interest_rate_percent, -100 AS penalty_threshold, 200 AS penalty_multiplier_percent UNION ALL SELECT trx_subset.i AS i, trx_subset.time AS TIME, trx_subset.id AS transaction_id, CASE WHEN (trx_subset.type = 'adjust_balance') THEN rec_cte.balance + trx_subset.amount WHEN (trx_subset.type = 'add_product') THEN rec_cte.balance + trx_subset.amount WHEN (trx_subset.type = 'buy_product') THEN rec_cte.balance - (SELECT CAST(ceil(CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0) + ((CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0)) * CAST(rec_cte.interest_rate_percent - 100 AS FLOAT)) / (100.0 + 0.0) + (((CAST(price * trx_subset.product_count * 1 AS FLOAT) / (1 + 0.0)) * CAST(rec_cte.penalty_multiplier_percent - 100 AS FLOAT)) / (100.0 + 0.0)) * CAST(rec_cte.balance < rec_cte.penalty_threshold AS INTEGER)) AS INTEGER) AS ceil_1 FROM rec_cte_price ORDER BY i DESC LIMIT 1 OFFSET ?) WHEN (trx_subset.type = 'joint') THEN rec_cte.balance - (SELECT CAST(ceil(CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0) + ((CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0)) * CAST(rec_cte.interest_rate_percent - 100 AS FLOAT)) / (100.0 + 0.0) + (((CAST(price * trx_subset.product_count * trx_subset.user_shares AS FLOAT) / (trx_subset.user_count + 0.0)) * CAST(rec_cte.penalty_multiplier_percent - 100 AS FLOAT)) / (100.0 + 0.0)) * CAST(rec_cte.balance < rec_cte.penalty_threshold AS INTEGER)) AS INTEGER) AS ceil_2 FROM rec_cte_joint_price ORDER BY i DESC LIMIT 1 OFFSET ?) WHEN (trx_subset.type = 'transfer' AND trx_subset.transfer_user_id = ?) THEN rec_cte.balance + trx_subset.amount WHEN (trx_subset.type = 'transfer' AND trx_subset.transfer_user_id != ?) THEN rec_cte.balance - trx_subset.amount ELSE rec_cte.balance END AS balance, CASE WHEN (trx_subset.type = 'adjust_interest') THEN trx_subset.interest_rate_percent ELSE rec_cte.interest_rate_percent END AS interest_rate_percent, CASE WHEN (trx_subset.type = 'adjust_penalty') THEN trx_subset.penalty_threshold ELSE rec_cte.penalty_threshold END AS penalty_threshold, CASE WHEN (trx_subset.type = 'adjust_penalty') THEN trx_subset.penalty_multiplier_percent ELSE rec_cte.penalty_multiplier_percent END AS penalty_multiplier_percent FROM (SELECT row_number() OVER ( ORDER BY "transaction".time ASC) AS i, "transaction".id AS id, "transaction".amount AS amount, "transaction".interest_rate_percent AS interest_rate_percent, "transaction".penalty_multiplier_percent AS penalty_multiplier_percent, "transaction".penalty_threshold AS penalty_threshold, "transaction".product_count AS product_count, "transaction".product_id AS product_id, "transaction".time AS TIME, "transaction".transfer_user_id AS transfer_user_id, "transaction".type AS TYPE, trx_subset_subset.user_shares AS user_shares, trx_subset_subset.user_count AS user_count FROM (SELECT "transaction".id AS id, NULL AS user_shares, NULL AS user_count FROM "transaction" WHERE ("transaction".user_id = ? AND "transaction".type IN ('add_product', 'adjust_balance', 'buy_product', 'transfer') OR "transaction".type = 'transfer' AND "transaction".transfer_user_id = ? OR "transaction".type IN ('throw_product', 'adjust_interest', 'adjust_penalty')) AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?) UNION ALL SELECT "transaction".id AS id, sum(CASE WHEN (count_trx.user_id = ?) THEN 1 ELSE 0 END) AS user_shares, count(count_trx.id) AS user_count FROM (SELECT DISTINCT "transaction".joint_transaction_id AS joint_transaction_id FROM "transaction" WHERE "transaction".type = 'joint_buy_product' AND "transaction".user_id = ? AND "transaction".time <= (SELECT "transaction".time FROM "transaction" WHERE "transaction".id = ?)) AS sub_joint_transaction JOIN "transaction" ON "transaction".id = sub_joint_transaction.joint_transaction_id JOIN "transaction" AS count_trx ON count_trx.joint_transaction_id = "transaction".id GROUP BY count_trx.joint_transaction_id) AS trx_subset_subset JOIN "transaction" ON "transaction".id = trx_subset_subset.id ORDER BY "transaction".time ASC) AS trx_subset, rec_cte WHERE trx_subset.i = rec_cte.i + 1) SELECT rec_cte.balance FROM rec_cte ORDER BY rec_cte.i DESC LIMIT 1 OFFSET 0 ```

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Projects/dibbler#9