PocketMoney 3.0 Data Formats (c) 2003 by Catamount Software =========================================================== Several users/linux developers have requested the PocketMoney database format so they can create support on Linux for PocketMoney. Below are the record formats for the PM-Transaction record, PM-Account record, and PM-Lookups record. I'm copying the record structs out of my header files. The record structs are for the unpacked records. The packed records (which is what is stored in the database) looked exactly the same except the CharPtr are the packed strings. Since, you are all developers that need to use this I'm sure you'll happy with the documentation or lack there of. ;-) !!! Please let me know if you create anything based on this info. It could change in the future and I'd like to try to keep you informed of any changes. -- Hardy Macia -------------------------------------------------------------------------------- General Database Info 3.0 -------------------------------------------------------------------------------- #define appFileCreator 'Pkt$' #define appVersionNum 0x0F #define kTransactionDBType 'Data' #define kTransactionDBName "PM-Transactions" #define kAccountDBType 'Dat2' #define kAccountDBName "PM-Accounts" #define kLookupDBType 'Dat5' #define kLookupDBName "PM-Lookups" -------------------------------------------------------------------------------- TransactionDB Info -------------------------------------------------------------------------------- TransactionDB is the main database. The only real gotcha is that transfers are stored as two parts (transfer from and tranfer to). The transfer from is a positive amount and transfer to is a negative amount. The date and amount must be the same for both halfs of the transfer and the payee & account must be compliments of each other in each half of the transfer. // Define Transaction Types #define withdrawal 0 #define deposit 1 #define transfer 2 typedef UInt8 TransactionType; typedef UInt32 vDate; #define kMaxSplits 5 // the greatest number of splits that PocketMoney current supports // this will change in the future typedef struct { double amount; CharPtr category; CharPtr description; CharPtr category; // category CharPtr description; // memo double xrate; // exchange rate (must be greater than 0, it is usually 1.0) } vSplit; typedef struct { double amount; // amount double xrate; // exchange rate (greater than 0) UInt32 internalTransferLinkUID; // unused UInt32 externalBudgetLinkUID; // link to QuikBudget record UInt32 externalAutoLinkUID; // unused char *category; // category char *className; // class char *memo; // description char *transferToAccountName; // if a transfer it will hold // the account name // if not a transger it will // be null char *currency; // currency } vSplit, * vSplitPtr; typedef struct { UInt8 recordVersion; // appVersionNum Boolean cleared; // cleared vDate lastModTime; // last time record was modified vDate date; // transaction date double subTotal; // amount (will be negative for // withdraws and positive for // deposits. Transfers will be -/+ // depending on if it is a transfer // to or a transfer from.) double runningBalance; // for future use CharPtr account; // account CharPtr payee; // payee (or an account name if // transaction type is a transfer) CharPtr chkNum; // check number or id UInt8 numSplits; // up to 255 splits vSplit *splits; // split items } TransactionDBRecordType, * TransactionDBRecordPtr -------------------------------------------------------------------------------- AccountDB Info -------------------------------------------------------------------------------- You must create an account record in the account db if you download a new account into PocketMoney. Otherwise PocketMoney won't know about the account and therefore won't display it in the Account Balance screen. // Define Account Types #define typeBank 0 #define typeCash 1 #define typeCreditCard 2 #define typeAsset 3 #define typeLiability 4 typedef UInt8 AccountTypeType; typedef enum { balanceAbsolute=0, balanceCleared, balanceRunning } BalanceType; typedef enum { feeFixed=0, feePercent } AccountFeeType; typedef enum { ccAccountCategory=0, ccAccountPayee, ccCategory, ccCategoryPayee, ccIdPayee, ccMemo, ccPayee} CustomColumnType; typedef struct { UInt8 recordVersion; UInt8 lastSyncFlag; // internal PocketMoney use double balance; // account balance -- sum of // all transactions within this // account. double cleared; // cleared balance -- sum of // all cleared transactions // within this account. AccountTypeType type; UInt8 reserved2; double limit; // credit limit or minimum // balance Boolean noLimit; // no limit is set UInt8 reserved3; double fee; AccountFeeType fixedPercent; Boolean totalWorth; BalanceType AbsoluteOrClearedBalance; CustomColumnType customColumn; double xrate; // exchange rate for this // account - (must be greater // than 0, it is usually 1.0) char currencySuffix[4]; char currencyPrefix[4]; Int16 currencyDecimalPlace; RGBColorType rowColor; // changed in version 2.5 of // PocketMoney is struct // {uint8,uint8,uint8,uint8} CharPtr account; CharPtr expires; CharPtr number; CharPtr phone; CharPtr institution; CharPtr notes; CharPtr chknum; // next check number to use vDate lastSyncID; // last palm IR Synced to vDate lastSyncTime; // last time IR Synced } AccountDBRecordType, * AccountDBRecordPtr; -------------------------------------------------------------------------------- LookupDB Info -------------------------------------------------------------------------------- You'll need this info if you want to modify the popup lists in PocketMoney. // Lookup Types #define kAccountsListStr_old '' #define kCategoriesListStr_old '' #define kIdsListStr_old '' #define kAmountsListStr_old '' #define kPayeesListStr_old '' #define kCurrencyPrefixesListStr_old '' #define kCurrencySuffixesListStr_old '' #define kAccountsListStr 1 #define kCategoriesListStr 2 #define kIdsListStr 3 #define kAmountsListStr 4 #define kClassListStr 5 #define kCurrencyPrefixesListStr 6 #define kCurrencySuffixesListStr 7 typedef struct { CharPtr type; // this can be an above type or a // category name if the lookup name // is a payee CharPtr LookupName; } LookupDBRecordType, * LookupDBRecordPtr; =========================================================== ----- OLD FORMATS ------ =========================================================== PocketMoney 2.6 Data Formats (c) 1999-2001 by Catamount Software =========================================================== -------------------------------------------------------------------------------- General Database Info 2.6 -------------------------------------------------------------------------------- #define appFileCreator 'Pkt$' #define appVersionNum 0x0C #define kTransactionDBType 'Data' #define kTransactionDBName "PM-Transactions" #define kAccountDBType 'Dat2' #define kAccountDBName "PM-Accounts" #define kLookupDBType 'Dat5' #define kLookupDBName "PM-Lookups" -------------------------------------------------------------------------------- TransactionDB Info -------------------------------------------------------------------------------- TransactionDB is the main database. The only real gotcha is that transfers are stored as two parts (transfer from and tranfer to). The transfer from is a positive amount and transfer to is a negative amount. The date and amount must be the same for both halfs of the transfer and the payee & account must be compliments of each other in each half of the transfer. // Define Transaction Types #define withdrawal 0 #define deposit 1 #define transfer 2 typedef UInt8 TransactionType; typedef UInt32 vDate; #define kMaxSplits 5 // the greatest number of splits that PocketMoney current supports // this will change in the future typedef struct { double amount; CharPtr category; CharPtr description; } vSplit; typedef struct { UInt8 recordVersion; // appVersionNum UInt8 beamed; // record beamed via IR Sync vDate date; // transaction date TransactionType transactionType; // type UInt8 reserved2; double amount; // amount (will be negative for withdraws and positive for deposits. Transfers will be -/+ depending on if it is a transfer to or a transfer from) double xrate; // exchange rate (must be greater than 0, it is usually 1.0) double runningBalance; // for future use Boolean cleared; // cleared CharPtr account; // account CharPtr category; // category CharPtr payee; // payee (or an account name if transaction type is a transfer) CharPtr chkNum; // check number or id CharPtr description; // memo Int16 numSplits; // number of splits (must be 0-5). This will increase in the future. vSplit splits[5]; // split items } TransactionDBRecordType, * TransactionDBRecordPtr -------------------------------------------------------------------------------- AccountDB Info -------------------------------------------------------------------------------- You must create an account record in the account db if you download a new account into PocketMoney. Otherwise PocketMoney won't know about the account and therefore won't display it in the Account Balance screen. // Define Account Types #define typeBank 0 #define typeCash 1 #define typeCreditCard 2 typedef UInt8 AccountTypeType; typedef enum { balanceAbsolute=0, balanceCleared, balanceRunning } BalanceType; typedef enum { feeFixed=0, feePercent } AccountFeeType; typedef enum { ccAccountCategory=0, ccAccountPayee, ccCategory, ccCategoryPayee, ccIdPayee, ccMemo, ccPayee} CustomColumnType; typedef struct { UInt8 recordVersion; UInt8 reserved1; double balance; // account balance -- sum of all transactions within this account. The hotsync process should update this, but running recompute balance from PocketMoney will recompute it also. double cleared; // cleared balance -- sum of all cleared transactions within this account. The hotsync process should update this, but running recompute balance from PocketMoney will recompute it also. AccountTypeType type; UInt8 reserved2; double limit; // credit limit or minimum balance Boolean noLimit; // no limit is set UInt8 reserved3; double fee; AccountFeeType fixedPercent; Boolean totalWorth; BalanceType AbsoluteOrClearedBalance; CustomColumnType customColumn; double xrate; // exchange rate for this account - (must be greater than 0, it is usually 1.0) char currencySuffix[4]; char currencyPrefix[4]; Int16 currencyDecimalPlace; RGBColorType rowColor; // changed in version 2.5 of PocketMoney is struct {uint8,uint8,uint8,uint8} CharPtr account; CharPtr expires; CharPtr number; CharPtr phone; CharPtr institution; } AccountDBRecordType, * AccountDBRecordPtr; -------------------------------------------------------------------------------- LookupDB Info -------------------------------------------------------------------------------- You'll need this info if you want to modify the popup lists in PocketMoney. // Lookup Types #define kAccountsListStr_old '' #define kCategoriesListStr_old '' #define kIdsListStr_old '' #define kAmountsListStr_old '' #define kPayeesListStr_old '' #define kCurrencyPrefixesListStr_old '' #define kCurrencySuffixesListStr_old '' #define kAccountsListStr 1 #define kCategoriesListStr 2 #define kIdsListStr 3 #define kAmountsListStr 4 //#define kPayeesListStr 5 #define kCurrencyPrefixesListStr 6 #define kCurrencySuffixesListStr 7 typedef struct { CharPtr type; // this can be an above type or a category name if the lookup name is a payee CharPtr LookupName; } LookupDBRecordType, * LookupDBRecordPtr; -------------------------------------------------------------------------------- Contact Info -------------------------------------------------------------------------------- Catamount Software PO Box 8276 Essex VT 05451 http://www.catamount.com pocketmoney-palm@catamount.com Last modified : October 2, 2001 -------------------------------------------------------------------------------- Change History -------------------------------------------------------------------------------- April 15, 2003: PocketMoney 3.0: changed many things. October 2, 2001: PocketMoney 2.6 - type string changed in lookupDB records from the *_old characters to the numbers 1-7. Change required to deal with Japanese ROMs and multibyte characters. kPayeesListStr is undefined because it is the category name. Querying on the cateogry name will return all payees for that category. August 2001: PocketMoney 2.5 - added rowColor to the AccountDBRecord June 1999: Initial release