Robert Brewer avatar Robert Brewer committed c829ec0

Optimized the ledger with new *_mult columns in transactions, obligations.

Comments (0)

Files changed (3)

flowrate/__init__.py

         # Accept the new transaction definition.
         vals = req.json["body"]
         credit, debit = popint(vals['credit']), popint(vals['debit'])
+        credit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s", (credit,)).fetchone()
+        debit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s", (debit,)).fetchone()
+        amount = vals['amount']
         txrow = db.execute(
             "UPDATE transactions SET amount = %s, "
             "credit_account = %s, debit_account = %s, postdate = %s, "
-            "description = %s WHERE id = %s RETURNING *;",
-            (vals['amount'], credit, debit,
-             vals['postdate'], vals['description'], req.txid)).fetchone()
+            "description = %s, credit_mult = %s, debit_mult = %s "
+            "WHERE id = %s RETURNING *;",
+            (amount, credit, debit,
+             vals['postdate'], vals['description'],
+             -1 if credit_type in ('asset', 'expense') else 1,
+             1 if debit_type in ('asset', 'expense') else -1,
+             req.txid)).fetchone()
 
         flows.unfulfill(txrow.id)
         flows.fulfill(txrow)
         # Accept the new transaction definition.
         vals = req.json["body"]
         credit, debit = popint(vals['credit']), popint(vals['debit'])
+        credit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s", (credit,)).fetchone()
+        debit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s", (debit,)).fetchone()
+        amount = vals['amount']
         newrow = db.execute(
             "INSERT INTO transactions"
-            " (amount, credit_account, debit_account, postdate, description) "
-            "VALUES (%s, %s, %s, %s, %s) RETURNING *",
-            (vals["amount"], credit, debit,
-             vals["postdate"], vals['description'])).fetchone()
+            " (amount, credit_account, debit_account, postdate, description, "
+            "credit_mult, debit_mult) "
+            "VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING *",
+            (amount, credit, debit,
+             vals["postdate"], vals['description'],
+             -1 if credit_type in ('asset', 'expense') else 1,
+             1 if debit_type in ('asset', 'expense') else -1,
+             )).fetchone()
 
         flows.fulfill(newrow)
 

flowrate/flowrate.sql

     debit_account integer NOT NULL,
     description text,
     amount numeric(10, 2) NOT NULL,
-    remaining numeric(10, 2) NOT NULL
+    remaining numeric(10, 2) NOT NULL,
+    -- Denormalized for ledger:
+    credit_mult numeric(10, 2) NOT NULL,
+    debit_mult numeric(10, 2) NOT NULL
 );
 
 
     credit_account integer NOT NULL,
     debit_account integer NOT NULL,
     description text,
-    amount numeric(10, 2) NOT NULL
+    amount numeric(10, 2) NOT NULL,
+    -- Denormalized for ledger:
+    credit_mult numeric(10, 2) NOT NULL,
+    debit_mult numeric(10, 2) NOT NULL
 );
 
 
 CREATE OR REPLACE VIEW ledger AS
     SELECT postdate,
            credit_account AS account,
-           (CASE WHEN a1.type IN ('asset', 'expense') THEN 0 - amount ELSE amount END) as amount
-    FROM transactions t1 LEFT JOIN accounts a1 ON t1.credit_account = a1.id
+           amount * credit_mult as amount
+    FROM transactions t1
     UNION ALL
     SELECT postdate,
            debit_account AS account,
-           (CASE WHEN a2.type IN ('asset', 'expense') THEN amount ELSE 0 - amount END) as amount
-    FROM transactions t2 LEFT JOIN accounts a2 ON t2.debit_account = a2.id
+           amount * debit_mult as amount
+    FROM transactions t2
     ORDER BY 1, 2;
 
 ALTER TABLE ledger OWNER TO postgres;
 -- View: flowledger
 
 CREATE OR REPLACE VIEW flowledger AS
-    SELECT postdate,
-           credit_account AS account,
-           (CASE WHEN a1.type IN ('asset', 'expense')
-                 THEN 0 - amount ELSE amount
-            END) AS total,
-           (CASE WHEN a1.type IN ('asset', 'expense')
-                 THEN 0 - remaining ELSE remaining
-            END) AS amount
-    FROM obligations LEFT JOIN accounts a1 ON obligations.credit_account = a1.id
+    SELECT postdate, credit_account AS account,
+           amount * credit_mult AS total, remaining * credit_mult AS amount
+    FROM obligations
     UNION ALL
-    SELECT postdate,
-           debit_account AS account,
-           (CASE WHEN a2.type IN ('asset', 'expense')
-                 THEN amount ELSE 0 - amount
-            END) AS total,
-           (CASE WHEN a2.type IN ('asset', 'expense')
-                 THEN remaining ELSE 0 - remaining
-            END) AS amount
-    FROM obligations LEFT JOIN accounts a2 ON obligations.debit_account = a2.id
+    SELECT postdate, debit_account AS account,
+           amount * debit_mult AS total, remaining * debit_mult AS amount
+    FROM obligations
     ORDER BY 1, 2;
 
 ALTER TABLE flowledger OWNER TO postgres;

flowrate/flows.py

             if diff % self.period == 0:
                 # Yield one transaction for the year/month/week on the day.
                 yield {
-                    'id': None, 'postdate': postdate,
+                    'id': None,
+                    'postdate': postdate,
                     'credit': self.credit_account,
                     'debit': self.debit_account,
                     'description': self.description,
         else:
             dategroupformat = 'YYYY-MM'
 
+        credit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s",
+            (self.credit_account,)).fetchone()
+        debit_type = db.execute(
+            "SELECT type FROM accounts WHERE id = %s",
+            (self.debit_account,)).fetchone()
+
         obs = {}
         for ob in self.obligations():
             # TODO: this is slow. Can we change it to an "INSERT INTO ... FROM"?
             row = flowrate.db.execute(
                 "INSERT INTO obligations "
                 "(flowid, postdate, credit_account, debit_account,"
-                " description, amount, remaining, dategroupformat) "
-                "VALUES (%s, %s, %s, %s, %s, %s, 0.0, %s) RETURNING id;",
+                " description, amount, remaining, dategroupformat,"
+                " credit_mult, debit_mult) "
+                "VALUES (%s, %s, %s, %s, %s, %s, 0.0, %s, %s, %s) "
+                "RETURNING id;",
                 (self.id, ob['postdate'], ob['credit'], ob['debit'],
-                 ob['description'], ob['amount'], dategroupformat)).fetchone()
+                 ob['description'], ob['amount'], dategroupformat,
+                 -1 if credit_type in ('asset', 'expense') else 1,
+                 1 if debit_type in ('asset', 'expense') else -1,
+                 )).fetchone()
             obs[row.id] = ob
 
         # Now, fulfill the new obligations
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.