Using SQLite to maintain your monthly bills.

I was looking for a small application to maintain my every month bills. The application had to be small and script-able. I basically wanted to keep track of the bills that have been generated and pending payment and the bills that have been paid.

First we will create the database schema with the following command:

1
CREATE TABLE bills(name varchar(20), due_dt text, p_dt text, amount integer);

So, I built on the knowledge gathered on SQLite. And here is a small script that I wrote:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#!/bin/bash -
#===============================================================================
#
#          FILE:  bills.sh
#
#         USAGE:  ./bills.sh
#
#   DESCRIPTION:  Complete bill management
#
#       OPTIONS:  ---
#  REQUIREMENTS:  ---
#          BUGS:  ---
#         NOTES:  ---
#        AUTHOR:  <a class="zem_slink" title="Amit Agarwal" rel="homepage" href="http://amit-agarwal.co.in">Amit Agarwal</a> (AKA), <a class="linkification-ext" title="Linkification: mailto:[email protected]" href="mailto:[email protected]">[email protected]t-agarwal.co.in</a>
#       COMPANY:  Individual
#       VERSION:  1.0
#       CREATED:  08/12/2010 08:28:05 AM <a class="zem_slink" title="Indian Standard Time" rel="geolocation" href="http://maps.google.com/maps?ll=23.1827777778,75.7772222222&amp;spn=0.1,0.1&amp;q=23.1827777778,75.7772222222%20%28Indian%20Standard%20Time%29&amp;t=h">IST</a>
#      REVISION:  ---
#===============================================================================

#Directory where you created the file.
dir="$HOME/Bills"

#The file that will be used as schema.

file=bills

#The table that you created above.

table=bills

#Here are the names of the accounts. Dont use the first one.
names=(Junk A B C D E)

#Here are the respective dates.
due_dts=(0 01 02 03 04 05)

menu_items=(
"1. View all pending"
"2. Make payment"
"3. Add pending payment"
"4. Display the Schema"
)
menu_function=( display_pending make_pay insert_due display_schema)

#===  FUNCTION  ================================================================
#          NAME:  display_menu
#   DESCRIPTION:  Display the menu
#    PARAMETERS:
#       RETURNS:
#===============================================================================
display_menu ()
{
for i in `seq 0 ${#menu_items[@]}`
do
echo ${menu_items[$i]}
done
}    # ----------  end of function display_menu  ----------

#===  FUNCTION  ================================================================
#          NAME:  insert_due
#   DESCRIPTION:  Insert a pending payment
#    PARAMETERS:
#       RETURNS:
#===============================================================================
insert_due ()
{
mn=$(date +%m)
for i in `seq 1 ${#names[@]}`
do
echo $i ${names[$i]}
done
read -p "Enter selection :: " selection
read -p "Enter the amount ::" amt
if [ ${due_dts[$selection]} -le  `date +"%d"` ]
then
mn=$(echo $mn |sed \'s/^0*//\')
let mn=$mn+1
[ $mn -le 9 ] &amp;&amp; mn="0$mn"
echo $mn
fi
dd=$(echo `date +"%Y-"`$mn-${due_dts[$selection]} 00:00:00)

echo "insert into $table values ("${names[$selection]}", "$dd", "", "$amt");"|sqlite3 $file
}

#===  FUNCTION  ================================================================
#          NAME:  display_schema
#   DESCRIPTION:  Display the schema for the table
#    PARAMETERS:
#       RETURNS:
#===============================================================================

display_schema ()
{
echo ".schema $table"|sqlite3 $file
}    # ----------  end of function display_schema  ----------

#===  FUNCTION  ================================================================
#          NAME:  make_pay
#   DESCRIPTION:  Update the payment in the database
#    PARAMETERS:
#       RETURNS:
#===============================================================================
make_pay ()
{
echo "Select one of the following :"
for i in `seq 1 ${#names[@]}`
do
echo $i ${names[$i]}
done
read -p "Enter selection :: " selection
dd=$(echo `date +"%Y-%m-%d %H:%M:%S"`)

echo "update $table set p_dt="$dd" where name="${names[$selection]}" and p_dt="";"|sqlite3 $file
}    # ----------  end of function make_pay  ----------

#===  FUNCTION  ================================================================
#          NAME:  display_pending
#   DESCRIPTION:  Display all the pending payments
#    PARAMETERS:
#       RETURNS:
#===============================================================================
display_pending ()
{
echo "select name,amount,due_dt from $table where p_dt="";"|sqlite3 $file | sed -e \'s/|/\\t\\t--/g\' -e \'s/00:.*//\'
}    # ----------  end of function display_pending  ----------

cd $dir
display_menu
read -p "Please make a selection :: " input
((input--))
eval ${menu_function[$input]}

Hope this will save you the effort to write a new application. Feel free to use this program/application. Modify this or whatever. If you are distributing this, please give due credit.

\"Enhanced

SQLite – simple use in scripts

Today we will look a the most simple use\’s in the scripts and how to use them.

echo \”insert into bills values (\”$name\”,$date_now, $date_now,$1)\”|sqlite bills

or the other way to do this is

sqlite3 bills \”insert into bills values (\”$name\”,$date_now, $date_now,$1)\”

OK, now in the next post we will see a simple application that I am currently developing or planning.

\"Enhanced

SQLite : Manager for this simple database

We will look at sqliteman – SQLite Database manager.

If you are looking for a tool for tuning SQL statements, manage tables, views, or triggers, administrate the database space and index statistics then Sqliteman is the perfect choice.
If you are looking for a graphical queries creation wizards, user interface designers for your database, or an universal report tool try the applications designed for tasks such this (Kexi, knoda).

You can visist the home page of the project to get a little more details.

Now on to the features that I liked and how to use this.

First install the application:

sudo yum install sqliteman

Once done, run the application:

sqliteman

Now time for some screenshots:

\"SQLite \"SQLite \"SQLite \"SQLite \"SQLite
\"Enhanced