Skip to content

KernelGhost/AssetLiabilityRepository

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

6 Commits
.settings
.settings
 
 
icon
icon
 
 
lib
lib
 
 
src
src
 
 
.classpath
.classpath
 
 
.gitignore
.gitignore
 
 
.project
.project
 
 
ALRBlank.SQL
ALRBlank.SQL
 
 
Readme.md
Readme.md
 
 
manifest.txt
manifest.txt
 
 

Repository files navigation

Asset Liability Repository Documentation

Build Instructions

macOS

  1. Clone the repository.

    git clone https://github.com/KernelGhost/AssetLiabilityRepository.git

  2. Create ./bin and ./build directories.

    cd AssetLiabilityRepository
mkdir bin
mkdir build

  3. Compile the program into bytecode. The resulting .class files will be placed within the ./bin directory.

    javac -d ./bin -cp "./lib/*" -deprecation ./src/*.java ./src/CustomTypes/*.java

  4. Place a copy of the graphics resources within the ./bin directory.

    mkdir -p ./bin/resources/graphics && cp -r ./src/resources/graphics ./bin/resources

  5. Create a manifest.txt.

    Note that text file must end with a new line or carriage return, otherwise the last line will not be parsed properly!

    Manifest-Version: 1.0
Main-Class: Main
Class-Path: /lib/gs-algo-1.3.jar /lib/gs-core-1.3.jar lib/gs-ui-1.3.jar lib/LGoodDatePicker-11.1.0.jar lib/mariadb-java-client-2.7.1.jar lib/xchart-3.6.3.jar lib/flatlaf-3.2.5.jar lib/flatlaf-intellij-themes-3.2.5.jar

  6. Create an executable .jar file and move it into the ./build directory.

    Note that the libraries mentioned in the manifest classpath must be located within a directory located at ./lib relative to the created .jar file for the application to launch correctly.

    jar --create --file=ALR.jar --manifest=manifest.txt -C ./bin . && mv ALR.jar ./build/ALR.jar

  7. Place a copy of the libraries within the ./build directory.

    mkdir -p ./build/lib && cp lib/* ./build/lib

  8. Use jpackage to create a native installer. A macOS disk image named Asset Liability Repository-2.0.dmg should be produced within the root directory of the repository.

    jpackage \
	--name Asset\ Liability\ Repository \
	--icon icon/appicon.icns \
	--app-version 2.0 \
	--input build \
	--main-jar ALR.jar \
	--main-class Main \
	--type dmg

  9. Clean up.

    rm -r ./bin
rm -r ./build

Linux

  1. Follow steps 1 through 7 (inclusive) from the macOS build instructions section.
  2. Move and rename the build folder. 
    mv ./build /opt/ALR
  3. Create an application launcher for your Desktop Environment (DE). For example, on the Cinnamon DE:
    1. Create Asset Liability Repository.desktop within ~/Desktop containing the following: 
      [Desktop Entry]
Name[en_AU]=Asset Liability Repository
GenericName[en_AU.UTF-8]=Asset Liability Repository
Comment=Asset Liability Repository
Icon=moneydance
Exec=java -Dsun.java2d.uiScale=2 -jar '/opt/ALR/ALR.jar'
Type=Application
Categories=Utility;
Terminal=false
    2. Mark the application launcher as executable. 
      chmod +x Asset\ Liability\ Repository.desktop
  4. Clean up. 
    rm -r ./bin

Note: It is recommended to install and use an LTS version of OpenJDK to run Asset Liability Repository.

  1. Install OpenJDK 17.

    sudo dnf install java-17-openjdk
sudo dnf install java-17-openjdk-devel

  2. Set OpenJDK 17 as the default java version.

    sudo alternatives --config java

  3. Confirm OpenJDK 17 is the default java version.

    java --version

    Expected output:

    openjdk 17.0.11 2024-04-16
OpenJDK Runtime Environment (Red_Hat-17.0.11.0.9-1) (build 17.0.11+9)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.11.0.9-1) (build 17.0.11+9, mixed mode, sharing)

Windows

  1. Clone the repository.

    git clone https://github.com/KernelGhost/AssetLiabilityRepository.git

  2. Create .\bin and .\build directories.

    cd .\AssetLiabilityRepository\
mkdir bin
mkdir build

  3. Compile the program into bytecode. The resulting .class files will be placed within the .\bin directory.

    javac -d .\bin -cp ".\lib\*" -deprecation .\src\*.java .\src\CustomTypes\*.java

  4. Place a copy of the graphics resources within the .\bin directory.

    Copy-Item -Path .\src\resources\graphics -Destination .\bin\resources\graphics -Recurse -Force

  5. Create a manifest.txt.

    Note that text file must end with a new line or carriage return, otherwise the last line will not be parsed properly!

    Manifest-Version: 1.0
Main-Class: Main
Class-Path: /lib/gs-algo-1.3.jar /lib/gs-core-1.3.jar lib/gs-ui-1.3.jar lib/LGoodDatePicker-11.1.0.jar lib/mariadb-java-client-2.7.1.jar lib/xchart-3.6.3.jar lib/flatlaf-3.2.5.jar lib/flatlaf-intellij-themes-3.2.5.jar

  6. Create an executable .jar file and move it into the .\build directory.

    Note that the libraries mentioned in the manifest classpath must be located within a directory located at ./lib relative to the created .jar file for the application to launch correctly.

    jar --create --file=ALR.jar --manifest=manifest.txt -C .\bin .; Move-Item -Path ALR.jar -Destination .\build\ALR.jar

  7. Place a copy of the libraries within the .\build directory.

    mkdir .\build\lib; Copy-Item -Path ".\lib\*.jar" -Destination .\build\lib

  8. Use jpackage to create a native application. A folder named Asset Liability Repository should be produced within the root directory of the repository.

    jpackage --name "Asset Liability Repository" --icon icon/appicon.ico --app-version 2.0 --input build --main-jar ALR.jar --main-class Main --type app-image

  9. Place everything within a .zip archive.

    Compress-Archive -Path ".\Asset Liability Repository\*" -DestinationPath "Asset Liability Repository.zip"

  10. Clean up.

    Remove-Item -LiteralPath .\bin -Force -Recurse
Remove-Item -LiteralPath .\build -Force -Recurse
Remove-Item -LiteralPath ".\Asset Liability Repository" -Force -Recurse

Database Backend

MariaDB is the recommended database backend for this project.

macOS

brew install mariadb

Linux (RHEL/CentOS/Fedora)

sudo dnf install mariadb
sudo dnf install mariadb-server
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo mysql_secure_installation

Windows

choco install mariadb

Note: To enable interoperability with macOS and Linux systems, a system variable needs to be modified to store case-preserving database, table and alias names. This is optional, but can help avoid errors when moving databases between macOS, Windows and Linux-based systems.

  1. Open C:\Program Files\MariaDB 10.6\data\my.ini in a text editor.
  2. Add lower_case_table_names=2 under the [mysqld] section. The lower_case_table_names system variable overrides operating system-level settings to control case sensitivity for database, table, and alias names:
    • 0:
      • Default on Unix-based systems.
      • Case-preserving storage.
      • Case-sensitive comparisons.
    • 1:
      • Default on Windows systems.
      • Lowercase storage.
      • Case-insensitive comparisons.
    • 2:
      • Default on macOS.
      • Case-preserving storage.
      • Case-insensitive comparisons.
  3. Restart MariaDB. 
    net stop mysql
net start mysql

Initial Setup

  1. Create a user. 
    CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  2. Create 'ALR_DB' database. 
    CREATE DATABASE ALR_DB;
  3. Grant the user privileges for the database. 
    GRANT ALL PRIVILEGES ON ALR_DB.* TO 'username'@'localhost';
  4. Use a SQL file to populate the database.
    • macOS and Linux (Bash) 
       mysql -u username -p ALR_DB < /path/to/ALR_Database_Backup.sql
    • Windows (Command Prompt) 
       "C:\Program Files\MariaDB 10.6\bin\mysql.exe" -u username -p ALR_DB < C:\path\to\ALR_Database.sql

Database Backups

To create an ALR database backup in the form of a .SQL file, run the below commands.

  • macOS and Linux (Bash) 
    mariadb-dump --user=username --password --lock-tables --databases ALR_DB > /path/to/ALR_Database_Backup.sql
  • Windows (PowerShell) 
     & "C:\Program Files\MariaDB 10.6\bin\mariadb-dump.exe" --user=username --password --lock-tables --databases ALR_DB > C:\path\to\ALR_Database_Backup.sql

Database Structure

Domn

  • Contains definitions for various integer IDs used within other tables (e.g. entity types, institution types, entity status types, service types, transaction types, etc.).
  • Should be treated as READ-ONLY.
Field Type Null Key Default Extra
DomnID int(11) NO MUL NULL
DomnName varchar(50) NO MUL NULL
DomnValue varchar(255) NO NULL

Example Domn Table:

DomnID DomnName DomnValue
0 EntityStatus Current
0 EntityType Savings Account
0 ServiceType Direct Debit (PP, PDC etc.)
0 ShareTxnType Buy
0 TxnCatg INT
1 EntityStatus Closed
1 EntityType Credit Card
1 ServiceType Direct Credit (Salary, Rent, Dividend etc.)
1 ShareTxnType Sell
1 TxnCatg DIV
2 EntityType Loan Account
2 ServiceType Net Details (URL, UserId, Pwd etc.)
2 ShareTxnType Dividend
2 TxnCatg FEE
3 EntityType Term Deposit
3 ServiceType Telebank Details (Number, UserId, Pwd etc.)
3 ShareTxnType DRP
3 TxnCatg OTHER
4 EntityType Superannuation
4 ServiceType Card Details (Number, Expiry Date, PIN etc.)
4 TxnCatg PAYMENT
5 EntityType Property
5 ServiceType Other
5 TxnCatg CREDIT
6 EntityType Shares/Managed Funds
6 TxnCatg DEP
7 EntityType Insurance
7 TxnCatg POS
8 TxnCatg XFER
9 TxnCatg DEBIT
10 TxnCatg SRVCHG
11 TxnCatg ATM
12 TxnCatg CHECK
13 TxnCatg CASH
14 TxnCatg DIRECTDEP
15 TxnCatg DIRECTDEBIT
16 TxnCatg REPEATPMT
17 TxnCatg TAX 
CREATE TABLE `Domn` (
  `DomnId` int(11) NOT NULL,
  `DomnName` varchar(50) NOT NULL,
  `DomnValue` varchar(255) NOT NULL,
  KEY `DomnId` (`DomnId`),
  KEY `DomnName` (`DomnName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `Domn` VALUES (0,'EntityStatus','Current'),(0,'EntityType','Savings Account'),(0,'ServiceType','Direct Debit (PP, PDC etc.)'),(0,'ShareTxnType','Buy'),(0,'TxnCatg','INT'),(1,'EntityStatus','Closed'),(1,'EntityType','Credit Card'),(1,'ServiceType','Direct Credit (Salary, Rent, Dividend etc.)'),(1,'ShareTxnType','Sell'),(1,'TxnCatg','DIV'),(2,'EntityType','Loan Account'),(2,'ServiceType','Net Details (URL, UserId, Pwd etc.)'),(2,'ShareTxnType','Dividend'),(2,'TxnCatg','FEE'),(3,'EntityType','Term Deposit'),(3,'ServiceType','Telebank Details (Number, UserId, Pwd etc.)'),(3,'ShareTxnType','DRP'),(3,'TxnCatg','OTHER'),(4,'EntityType','Superannuation'),(4,'ServiceType','Card Details (Number, Expiry Date, PIN etc.)'),(4,'TxnCatg','PAYMENT'),(5,'EntityType','Property'),(5,'ServiceType','Other'),(5,'TxnCatg','CREDIT'),(6,'EntityType','Shares/Managed Funds'),(6,'TxnCatg','DEP'),(7,'EntityType','Insurance'),(7,'TxnCatg','POS'),(8,'TxnCatg','XFER'),(9,'TxnCatg','DEBIT'),(10,'TxnCatg','SRVCHG'),(11,'TxnCatg','ATM'),(12,'TxnCatg','CHECK'),(13,'TxnCatg','CASH'),(14,'TxnCatg','DIRECTDEP'),(15,'TxnCatg','DIRECTDEBIT'),(16,'TxnCatg','REPEATPMT'),(17,'TxnCatg','TAX');

Holder

  • Holders are individuals with ownership over entities.
  • Holder_IDs are unique and assigned upon holder creation.
Field Type Null Key Default Extra
Holder_ID int(11) NO PRI NULL auto_increment
Holder_Name varchar(255) NO NULL
Holder_DOB date YES NULL
Holder_TFN int(11) YES NULL

Example Holder Table:

Holder_ID Holder_Name Holder_DOB Holder_TFN
1 John Appleseed 1959-06-23 NULL
2 Maria Database NULL 428573184
3 Edward E. Rawson NULL NULL
4 Samantha 1963-04-04 458294720 
CREATE TABLE `Holder` (
  `Holder_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Holder_Name` varchar(255) NOT NULL,
  `Holder_DOB` date DEFAULT NULL,
  `Holder_TFN` int(11) DEFAULT NULL,
  PRIMARY KEY (`Holder_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Institution

  • Institutions are bodies which issue entities to holders
  • Institution_IDs are unique and assigned upon institution creation.
Field Type Null Key Default Extra
Institution_ID int(11) NO PRI NULL auto_increment
Code varchar(5) NO MUL NULL
Name varchar(255) NO NULL
Address varchar(255) YES NULL
Comments varchar(255) YES NULL

Example Institution Table:

Institution_ID Code Name Address Comments
1 CBA Commonwealth Bank of Australia NULL NULL
2 NAB National Australia Bank 1 Bank Ave, Sydney NULL
3 WBC Westpac Banking Corporation NULL I like this bank 
CREATE TABLE `Institution` (
  `Institution_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Code` varchar(5) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `Comments` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Institution_ID`),
  KEY `Code` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Entity

  • Entities are given to holders by institutions (e.g. credit cards, mortgages, stocks, properties, etc.).
  • Entity_IDs are unique and assigned upon entity creation.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL auto_increment
Institution_ID int(11) NO MUL NULL
Entity_Type int(11) NO NULL
Entity_Number varchar(255) NO NULL
Entity_Status bit(1) NO NULL
Entity_Start_Date date NO NULL
Entity_End_Date date YES NULL
Description varchar(255) YES NULL
Comments varchar(255) YES NULL

Example Entity Table:

Entity_ID Institution_ID Entity_Type Entity_Number Entity_Status Entity_Start_Date Entity_End_Date Description Comments
98 1 0 582-482
38589372		 1 2001-01-03 2005-02-14 NULL Replacement
99 2 0 394-482
48295832		 0 2001-04-06 NULL NULL NULL
100 3 0 375-284
47294729		 0 2001-03-10 NULL My Card NULL 
CREATE TABLE `Entity` (
  `Entity_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Institution_ID` int(11) NOT NULL,
  `Entity_Type` int(11) NOT NULL,
  `Entity_Number` varchar(255) NOT NULL,
  `Entity_Status` bit(1) NOT NULL,
  `Entity_Start_Date` date NOT NULL,
  `Entity_End_Date` date DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `Comments` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Entity_ID`),
  KEY `FK_Entity_Institution` (`Institution_ID`),
  CONSTRAINT `FK_Entity_Institution` FOREIGN KEY (`Institution_ID`) REFERENCES `Institution` (`Institution_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Service

  • Services are provided to holders of an entity (e.g. internet banking).
  • Service_IDs are unique and assigned upon service creation.
Field Type Null Key Default Extra
Service_ID int(11) NO PRI NULL auto_increment
Entity_ID int(11) NO MUL NULL
Service_Type int(11) NO NULL
Expiry_Date date YES NULL
User_ID varchar(255) YES NULL
Pwd_PIN varchar(255) YES NULL
Frequency varchar(255) YES NULL
Contact varchar(255) YES NULL
Description varchar(255) YES NULL

Example Service Table:

Service_ID Entity_ID Service_Type Expiry_Date User_ID Pwd_PIN Frequency Contact Description
1 15 1 NULL NULL NULL Fortnightly NULL Bob's Centrelink (293 384 323)
2 15 1 1999-04-23 NULL NULL Fortnightly NULL NULL
3 16 1 1999-02-15 NULL NULL NULL NULL Martin's Salary from CBA 
CREATE TABLE `Service` (
  `Service_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Entity_ID` int(11) NOT NULL,
  `Service_Type` int(11) NOT NULL,
  `Expiry_Date` date DEFAULT NULL,
  `User_ID` varchar(255) DEFAULT NULL,
  `Pwd_PIN` varchar(255) DEFAULT NULL,
  `Frequency` varchar(255) DEFAULT NULL,
  `Contact` varchar(255) DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Service_ID`),
  KEY `FK_Service_Entity` (`Entity_ID`),
  CONSTRAINT `FK_Service_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EntityHistory

  • Entity_ID represents the child/recipient/successor entity, whilst Linked_Entity_ID represents the parent/provider/predecessor entity.
  • Neither Entity_ID or Linked_Entity_ID are unique since entities can have several parents or children.
Field Type Null Key Default Extra
Entity_ID int(11) NO MUL NULL
Linked_Entity_ID int(11) NO MUL NULL

Example EntityHistory Table:

Entity_ID Linked_Entity_ID
14 10
14 11
15 14
15 13 
CREATE TABLE `EntityHistory` (
  `Entity_ID` int(11) NOT NULL,
  `Linked_Entity_ID` int(11) NOT NULL,
  KEY `Entity_ID` (`Entity_ID`),
  KEY `Linked_Entity_ID` (`Linked_Entity_ID`),
  CONSTRAINT `FK_EntityHistory_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`),
  CONSTRAINT `FK_EntityHistory_Linked` FOREIGN KEY (`Linked_Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

HolderEntity

  • Neither Entity_ID or Holder_ID are unique, since entities can have multiple holders and holders can own multiple entities.
Field Type Null Key Default Extra
Entity_ID int(11) NO MUL NULL
Holder_ID int(11) NO MUL NULL

Example HolderEntity Table:

Entity_ID Holder_ID
45 1
46 1
45 2 
CREATE TABLE `HolderEntity` (
  `Entity_ID` int(11) NOT NULL,
  `Holder_ID` int(11) NOT NULL,
  KEY `FK_HolderEntity_Entity` (`Entity_ID`),
  KEY `FK_HolderEntity_Holder` (`Holder_ID`),
  CONSTRAINT `FK_HolderEntity_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`),
  CONSTRAINT `FK_HolderEntity_Holder` FOREIGN KEY (`Holder_ID`) REFERENCES `Holder` (`Holder_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EntityShrTxn

  • Record of all transactions that occur for a given set of shares or managed funds (e.g. purchase, sale, dividends and dividend reinvestment).
  • Not all fields are used depending on the transaction type (e.g. capital gains tax is only relevant when shares have been sold). Fields irrelevant to the type of transaction being stored are expected to be populated with zero values.
Field Type Null Key Default Extra
Shr_Txn_ID int(11) NO PRI NULL auto_increment
Entity_ID int(11) NO MUL NULL
Date date NO NULL
Txn_SubType int(11) NO NULL
Number_Shares float NO NULL
Share_Price decimal(34,2) NO NULL
Dividend_Amount decimal(34,2) NO NULL
Franking_Percentage decimal(34,2) NO NULL
Capital_Gains_Tax decimal(34,2) NO NULL
Brokerage_Charges decimal(34,2) NO NULL
Other_Charges decimal(34,2) NO NULL
Description varchar(255) YES NULL

Example EntityShrTxn Table:

Shr_Txn_ID Entity_ID Date Txn_Subtype Number_Shares Share_Price Dividend_Amount Franking_Percentage Capital_Gains_Tax Brokerage_Charges Other_Charges Description
1 1 1/1/1970 0 100 12.34 0.00 75.00 0.00 1.00 0.00 NULL
2 1 3/5/2002 0 50 2.42 0.00 100.00 0.00 0.50 0.00 NULL
3 1 3/5/2008 0 100 1.45 0.00 100.00 0.00 1.00 0.00 NULL 
CREATE TABLE `EntityShrTxn` (
  `Shr_Txn_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Entity_ID` int(11) NOT NULL,
  `Date` date NOT NULL,
  `Txn_SubType` int(11) NOT NULL,
  `Number_Shares` float NOT NULL,
  `Share_Price` decimal(34,2) NOT NULL,
  `Dividend_Amount` decimal(34,2) NOT NULL,
  `Franking_Percentage` decimal(34,2) NOT NULL,
  `Capital_Gains_Tax` decimal(34,2) NOT NULL,
  `Brokerage_Charges` decimal(34,2) NOT NULL,
  `Other_Charges` decimal(34,2) NOT NULL,
  `Description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Shr_Txn_ID`),
  KEY `FK_EntityShrTxn_Entity` (`Entity_ID`),
  CONSTRAINT `FK_EntityShrTxn_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EntityTxn

  • Record of all transactions that occur for credit cards, savings accounts and superannuation funds.
Field Type Null Key Default Extra
Txn_ID int(11) NO PRI NULL auto_increment
Entity_ID int(11) NO MUL NULL
Txn_Date date NO NULL
Txn_Catg int(11) NO NULL
Txn_DR decimal(34,2) NO NULL
Txn_CR decimal(34,2) NO NULL
Txn_Description varchar(255) YES NULL

Example EntityTxn Table:

Txn_ID Entity_ID Txn_Date Txn_Catg Txn_DR Txn_CR Txn_Description
1 2 1/1/2000 9 41.64 0.00 NULL
2 4 5/2/2007 5 0.00 67.42 NULL
3 5 12/11/2019 9 23.54 0.00 NULL 
CREATE TABLE `EntityTxn` (
  `Txn_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Entity_ID` int(11) NOT NULL,
  `Txn_Date` date NOT NULL,
  `Txn_Catg` int(11) NOT NULL,
  `Txn_DR` decimal(34,2) NOT NULL,
  `Txn_CR` decimal(34,2) NOT NULL,
  `Txn_Description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Txn_ID`),
  KEY `FK_EntityTxn_Entity` (`Entity_ID`),
  CONSTRAINT `FK_EntityTxn_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CreditCard

  • Holds additional information regarding credit cards.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Card_Limit decimal(34,2) NO NULL

Example CreditCard Table:

Entity_ID Card_Limit
34 5000.00
56 10000.00
89 12000.00 
CREATE TABLE `CreditCard` (
  `Entity_ID` int(11) NOT NULL,
  `Card_Limit` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_CreditCard_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insurance

  • Holds additional information regarding insurance policies.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Insured_Amount decimal(34,2) NO NULL
Insurance_Premium_PA decimal(34,2) NO NULL

Example Insurance Table:

Entity_ID Insured_Amount Insurance_Premium_PA
34 100000.00 2000.00
23 50000.00 600.00
65 20000.00 180.00 
CREATE TABLE `Insurance` (
  `Entity_ID` int(11) NOT NULL,
  `Insured_Amount` decimal(34,2) NOT NULL,
  `Insurance_Premium_PA` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_Insurance_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Loan

  • Holds additional information regarding loans.
  • Current_Value represents the amount remaining to be repaid.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Loan_Amount decimal(34,2) NO NULL
Current_Value decimal(34,2) NO NULL

Example Loan Table:

Entity_ID Loan_Amount Current_Value
43 300000.00 150000.00
18 500000.00 2000.00 
CREATE TABLE `Loan` (
  `Entity_ID` int(11) NOT NULL,
  `Loan_Amount` decimal(34,2) NOT NULL,
  `Current_Value` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_Loan_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Property

  • Holds additional information regarding properties.
  • If a property is yet to be sold, all fields relevant to the sale of a property (e.g. Capital_Gains_Tax) are expected to be zeroed.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Address varchar(255) NO NULL
Current_Value decimal(34,2) NO NULL
Purchase_Amount decimal(34,2) NO NULL
Sold_Amount decimal(34,2) NO NULL
Solicitors_Fees_Buy decimal(34,2) NO NULL
Solicitors_Fees_Sell decimal(34,2) NO NULL
Govt_Charges_Buy decimal(34,2) NO NULL
Govt_Charges_Sell decimal(34,2) NO NULL
Agent_Fees_Buy decimal(34,2) NO NULL
Agent_Fees_Sell decimal(34,2) NO NULL
Capital_Gains_Tax decimal(34,2) NO NULL

Example Property Table:

Entity_ID Address Current_Value Purchase_Amount Sold_Amount Solicitors_Fees_Buy Solicitors_Fees_Sell Govt_Charges_Buy Govt_Charges_Sell Agent_Fees_Buy Agent_Fees_Sell Capital_Gains_Tax
34 24 Abbott Lane, Smithfield 1400000.00 1200000.00 0.00 2000.00 0.00 20.00 0.00 300.00 0.00 0.00
56 18 Edward Ln, Randwick 2200000.00 1800000.00 0.00 1000.00 0.00 10.00 0.00 150.00 0.00 0.00 
CREATE TABLE `Property` (
  `Entity_ID` int(11) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `Current_Value` decimal(34,2) NOT NULL,
  `Purchase_Amount` decimal(34,2) NOT NULL,
  `Sold_Amount` decimal(34,2) NOT NULL,
  `Solicitors_Fees_Buy` decimal(34,2) NOT NULL,
  `Solicitors_Fees_Sell` decimal(34,2) NOT NULL,
  `Govt_Charges_Buy` decimal(34,2) NOT NULL,
  `Govt_Charges_Sell` decimal(34,2) NOT NULL,
  `Agent_Fees_Buy` decimal(34,2) NOT NULL,
  `Agent_Fees_Sell` decimal(34,2) NOT NULL,
  `Capital_Gains_Tax` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_Property_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Shares

  • Holds additional information regarding shares/managed funds.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Current_Unit_Value decimal(34,2) NO NULL

Example Shares Table:

Entity_ID Current_Unit_Value
183 3.55
188 34.85 
CREATE TABLE `Shares` (
  `Entity_ID` int(11) NOT NULL,
  `Current_Unit_Value` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_Shares_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

TermDeposit

  • Holds additional information regarding term deposits.
Field Type Null Key Default Extra
Entity_ID int(11) NO PRI NULL
Opening_Balance decimal(34,2) NO NULL
Interest_Rate decimal(34,2) NO NULL
Interest_Amount decimal(34,2) NO NULL
Bank_Fees decimal(34,2) NO NULL
Other_Charges decimal(34,2) NO NULL

Example TermDeposit Table:

Entity_ID Opening_Balance Interest_Rate Interest_Amount Bank_Fees Other_Charges
24 8500.00 5.25 160.00 0.00 0.00
57 20000.00 4.65 1300.00 0.00 0.00 
CREATE TABLE `TermDeposit` (
  `Entity_ID` int(11) NOT NULL,
  `Opening_Balance` decimal(34,2) NOT NULL,
  `Interest_Rate` decimal(34,2) NOT NULL,
  `Interest_Amount` decimal(34,2) NOT NULL,
  `Bank_Fees` decimal(34,2) NOT NULL,
  `Other_Charges` decimal(34,2) NOT NULL,
  PRIMARY KEY (`Entity_ID`),
  CONSTRAINT `FK_TermDeposit_Entity` FOREIGN KEY (`Entity_ID`) REFERENCES `Entity` (`Entity_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Debugging Cheat Sheet

MariaDB provides the backend database functionality for ALRv2. MariaDB installation instructions and database locations on other operating systems will vary. Below are some basic commands that can be used with MariaDB in order to assist any potential future debugging.

Command Description
mysql.server start Starts the MariaDB Service.
mysqladmin shutdown Stops the MariaDB Service.
CREATE USER 'NAME_OF_USER'@'localhost' IDENTIFIED BY 'ADD_A_PASSWORD'; Creates a user and sets a password for them.
ALTER USER 'NAME_OF_USER'@'localhost' IDENTIFIED BY 'NEW_PASSWORD'; Changes the password for a user.
SELECT host, user, password FROM mysql.user; Lists all users.
GRANT ALL privileges ON `ALR_DB`.* TO 'RohanBarar'@localhost; Grants all privileges to a given user.
CREATE DATABASE DatabaseName; Creates a database.
SHOW DATABASES; Lists all databases.
USE database_name; Selects a database for work.
CREATE TABLE Book(id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(100) NOT NULL, PRIMARY KEY (id)); Creates a table within the selected database. Name, then data type followed by optional modifiers. NOT NULL is self explanatory. AUTO_INCREMENT will automatically increment the id whenever a new entry is added. PRIMARY KEY selects the column to be marked as primary.
SHOW TABLES; Lists all tables within a database.
DESC TableName; Describes a given table within a database.
INSERT INTO book (id, name) VALUES(1, 'MariaDB Book'), (2, 'Example2'); Inserts multiple records into the table, populating the specified columns with the data specified within that same order.
SELECT * FROM book; Selects all records from the table book.
UPDATE book SET name='Blah', author='haha' WHERE id = 1; Updates a selected record with new column information given a condition (in this case, the id number).
Select * FROM book WHERE author = “Author2” AND name = “ass”; Selects all records within the table book where the author is Author2 and the name of the book is ass. OR can also be used instead of AND.
SHOW CREATE TABLE Tablename; Output the SQL instructions used to recreate the table.
ALTER TABLE Tablename ADD PRIMARY KEY(id) Add the column 'id' as the primary key for the table.
ALTER TABLE Tablename MODIFY COLUMN Columnname <datatype> <conditions> Modify an existing column's datatype or conditions (e.g. NOT NULL, auto_increment, etc.)
ALTER TABLE TargetTable ADD CONSTRAINT FK_TargetTable_SourceTable_ColumnName FOREIGN KEY (ColumnName) REFERENCES SourceTable(ColumnName); Creates a foreign key linking the specified column in the target table with the specified column in the source table.
ALTER TABLE Tablename DROP FOREIGN KEY FK_Name; Deletes the foreign key 'FK_Name' from the specified table.
mysqldump -u username -p database_name > data-dump.sql Requests the user's password before backing up the database to the selected path as a SQL file. Note this command should be run OUTSIDE the MariaDB shell.
mysql new_database < data-dump.sql Restores a backup of a database. Note that the new_database should be created before running this import command. Note that a user will have to be granted privileges on this database again. Note this command should be run OUTSIDE the MariaDB shell.

About

Personal finance management application. Java port of the original software (of the same name) written by my dad in the 1990s.

Resources

Readme
Activity

Stars

2 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases 1

ALR 28122023 Latest
Nov 16, 2023

Packages

 
 
 

Contributors

Languages