Difference between revisions of "FerretMyMoney/sql/fmmTrxacts"
Jump to navigation
Jump to search
(clarification of usage of UseBank) |
(new fields for fingerprinting KMM's transactions) |
||
| Line 11: | Line 11: | ||
*** Changed '''ID''' to '''ID_Trx''' and added 2nd key, '''ID_Acct'''. | *** Changed '''ID''' to '''ID_Trx''' and added 2nd key, '''ID_Acct'''. | ||
*** Balances apply to a particular transaction within a particular account. | *** Balances apply to a particular transaction within a particular account. | ||
| + | ** '''2012-03-15''' added auto-numbered primary key '''ID''' and KMM_* fingerprinting fields | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE TABLE `fmmTrxacts` ( | <section begin=sql /><mysql>CREATE TABLE `fmmTrxacts` ( | ||
| + | ID INT NOT NULL AUTO_INCREMENT, | ||
ID_Trx VARCHAR(32) NOT NULL COMMENT "ID of KMM Transaction", | ID_Trx VARCHAR(32) NOT NULL COMMENT "ID of KMM Transaction", | ||
ID_Acct VARCHAR(32) NOT NULL COMMENT "ID of KMM Account", | ID_Acct VARCHAR(32) NOT NULL COMMENT "ID of KMM Account", | ||
| Line 21: | Line 23: | ||
UseBank BOOL DEFAULT 0 COMMENT "TRUE = use the bank's balance; ignore previous calculations", | UseBank BOOL DEFAULT 0 COMMENT "TRUE = use the bank's balance; ignore previous calculations", | ||
UseDate BOOL DEFAULT 0 COMMENT "TRUE = bank's balance is end-of-day, not specific transaction", | UseDate BOOL DEFAULT 0 COMMENT "TRUE = bank's balance is end-of-day, not specific transaction", | ||
| − | PRIMARY KEY(`ID_Trx`,`ID_Acct`) | + | KMM_memo MEDIUMTEXT DEFAULT NULL COMMENT "kmmTransactions.memo", |
| + | KMM_post DATETIME DEFAULT NULL COMMENT "kmmTransactions.postDate", | ||
| + | KMM_entry DATETIME DEFAULT NULL COMMENT "kmmTransactions.entryDate", | ||
| + | PRIMARY KEY(ID), | ||
| + | KEY(`ID_Trx`,`ID_Acct`) | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 20:14, 16 March 2012
About
- Purpose: stores extended data about KMyMoney transactions
- Fields:
- UseBank: if TRUE, set BalCalc to BalBank rather than using any prior calculations
- UseDate: if TRUE, the bank's balance should apply after the last transaction of the day, and is not tied to a specific transaction.
- The actual implementation of this could either be to always move the bank's balance to the last transaction of the day, or it could be to store the balance in a separate table such as fmmBankBals. This table doesn't care how it's done.
- History:
- 2012-02-19 designed
- 2012-02-21 added SortBank field
- 2012-03-03 changed Balance to BalCalc and BalBank; added UseBank.
- Changed ID to ID_Trx and added 2nd key, ID_Acct.
- Balances apply to a particular transaction within a particular account.
- 2012-03-15 added auto-numbered primary key ID and KMM_* fingerprinting fields
SQL
<mysql>CREATE TABLE `fmmTrxacts` (
ID INT NOT NULL AUTO_INCREMENT, ID_Trx VARCHAR(32) NOT NULL COMMENT "ID of KMM Transaction", ID_Acct VARCHAR(32) NOT NULL COMMENT "ID of KMM Account", WhenBank DATETIME DEFAULT NULL COMMENT "posting date as shown by the bank record", SortBank DECIMAL(3,2) DEFAULT NULL COMMENT "additional sorting to match bank order, if needed", BalCalc DECIMAL(9,2) DEFAULT NULL COMMENT "balance calculated after this transaction", BalBank DECIMAL(9,2) DEFAULT NULL COMMENT "balance according to bank after this transaction", UseBank BOOL DEFAULT 0 COMMENT "TRUE = use the bank's balance; ignore previous calculations", UseDate BOOL DEFAULT 0 COMMENT "TRUE = bank's balance is end-of-day, not specific transaction", KMM_memo MEDIUMTEXT DEFAULT NULL COMMENT "kmmTransactions.memo", KMM_post DATETIME DEFAULT NULL COMMENT "kmmTransactions.postDate", KMM_entry DATETIME DEFAULT NULL COMMENT "kmmTransactions.entryDate", PRIMARY KEY(ID), KEY(`ID_Trx`,`ID_Acct`) ) ENGINE = MYISAM;</mysql>