Using SQLite to maintain your monthly bills.

2010-08-24 1107 words 6 mins read

Linux

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 <a class="zem_slink" title="Database" rel="wikipedia" href="http://en.wikipedia.org/wiki/Database">database schema with the following command:

  <td>
    <div class="text codecolorer">
      CREATE TABLE bills(name varchar(20), due_dt text, p_dt text, amount integer);
    </div>
  </td>
</tr>

So, I built on the knowledge gathered on <a class="zem_slink" title="SQLite" rel="homepage" href="http://sqlite.org/">SQLite. And here is a small script that I wrote:

  <td>
    <div class="bash codecolorer">
      <span class="co0">#!/bin/bash -</span><br /> <span class="co0">#===============================================================================</span><br /> <span class="co0">#</span><br /> <span class="co0">#          FILE:  bills.sh</span><br /> <span class="co0">#</span><br /> <span class="co0">#         USAGE:  ./bills.sh</span><br /> <span class="co0">#</span><br /> <span class="co0">#   DESCRIPTION:  Complete bill management</span><br /> <span class="co0">#</span><br /> <span class="co0">#       OPTIONS:  ---</span><br /> <span class="co0">#  REQUIREMENTS:  ---</span><br /> <span class="co0">#          BUGS:  ---</span><br /> <span class="co0">#         NOTES:  ---</span><br /> <span class="co0">#        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:amit.agarwal@amit-agarwal.co.in" href="mailto:amit.agarwal@amit-agarwal.co.in">amit.agarwal@amit-agarwal.co.in</a></span><br /> <span class="co0">#       COMPANY:  Individual</span><br /> <span class="co0">#       VERSION:  1.0</span><br /> <span class="co0">#       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&spn=0.1,0.1&q=23.1827777778,75.7772222222%20%28Indian%20Standard%20Time%29&t=h">IST</a></span><br /> <span class="co0">#      REVISION:  ---</span><br /> <span class="co0">#===============================================================================</span><br /> <br /> <span class="co0">#Directory where you created the file.</span><br /> <span class="re2">dir</span>=<span class="st0">"<span class="es2">$HOME</span>/Bills"</span><br /> <br /> <span class="co0">#The file that will be used as schema.</span><br /> <br /> <span class="re2">file</span>=bills<br /> <br /> <span class="co0">#The table that you created above.</span><br /> <br /> <span class="re2">table</span>=bills<br /> <br /> <span class="co0">#Here are the names of the accounts. Dont use the first one.</span><br /> <span class="re2">names</span>=<span class="br0">&#40;</span>Junk A B C D E<span class="br0">&#41;</span><br /> <br /> <span class="co0">#Here are the respective dates.</span><br /> <span class="re2">due_dts</span>=<span class="br0">&#40;</span><span class="nu0"></span> 01 02 03 04 05<span class="br0">&#41;</span><br /> <br /> <span class="re2">menu_items</span>=<span class="br0">&#40;</span><br /> <span class="st0">"1. View all pending"</span><br /> <span class="st0">"2. Make payment"</span><br /> <span class="st0">"3. Add pending payment"</span><br /> <span class="st0">"4. Display the Schema"</span><br /> <span class="br0">&#41;</span><br /> <span class="re2">menu_function</span>=<span class="br0">&#40;</span> display_pending make_pay insert_due display_schema<span class="br0">&#41;</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_menu</span><br /> <span class="co0">#   DESCRIPTION:  Display the menu</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> display_menu <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0"></span> <span class="co1">${#menu_items[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="co1">${menu_items[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_menu  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  insert_due</span><br /> <span class="co0">#   DESCRIPTION:  Insert a pending payment</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> insert_due <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="re2">mn</span>=$<span class="br0">&#40;</span><span class="kw2">date</span> +<span class="sy0">%</span>m<span class="br0">&#41;</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0">1</span> <span class="co1">${#names[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="re1">$i</span> <span class="co1">${names[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter selection :: "</span> selection<br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter the amount ::"</span> amt<br /> <span class="kw1">if</span> <span class="br0">&#91;</span> <span class="co1">${due_dts[$selection]}</span> -le  <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%d"</span><span class="sy0">`</span> <span class="br0">&#93;</span><br /> <span class="kw1">then</span><br /> <span class="re2">mn</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="re1">$mn</span> <span class="sy0">|</span><span class="kw2">sed</span> <span class="co3">\'</span>s<span class="sy0">/</span>^<span class="nu0"></span><span class="sy0">*//</span><span class="co3">\'</span><span class="br0">&#41;</span><br /> <span class="kw3">let</span> <span class="re2">mn</span>=<span class="re1">$mn</span>+<span class="nu0">1</span><br /> <span class="br0">&#91;</span> <span class="re1">$mn</span> <span class="re5">-le</span> <span class="nu0">9</span> <span class="br0">&#93;</span> <span class="sy0">&</span>amp;<span class="sy0">&</span>amp; <span class="re2">mn</span>=<span class="st0">"0<span class="es2">$mn</span>"</span><br /> <span class="kw3">echo</span> <span class="re1">$mn</span><br /> <span class="kw1">fi</span><br /> <span class="re2">dd</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%Y-"</span><span class="sy0">`</span><span class="re1">$mn</span>-<span class="co1">${due_dts[$selection]}</span> 00:00:00<span class="br0">&#41;</span><br /> <br /> <span class="kw3">echo</span> <span class="st0">"insert into <span class="es2">$table</span> values ("</span><span class="co1">${names[$selection]}</span><span class="st0">", "</span><span class="re1">$dd</span><span class="st0">", "</span><span class="st0">", "</span><span class="re1">$amt</span><span class="st0">");"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_schema</span><br /> <span class="co0">#   DESCRIPTION:  Display the schema for the table</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> <br /> display_schema <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">".schema <span class="es2">$table</span>"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_schema  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  make_pay</span><br /> <span class="co0">#   DESCRIPTION:  Update the payment in the database</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> make_pay <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">"Select one of the following :"</span><br /> <span class="kw1">for</span> i <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0">1</span> <span class="co1">${#names[@]}</span><span class="sy0">`</span><br /> <span class="kw1">do</span><br /> <span class="kw3">echo</span> <span class="re1">$i</span> <span class="co1">${names[$i]}</span><br /> <span class="kw1">done</span><br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Enter selection :: "</span> selection<br /> <span class="re2">dd</span>=$<span class="br0">&#40;</span><span class="kw3">echo</span> <span class="sy0">`</span><span class="kw2">date</span> +<span class="st0">"%Y-%m-%d %H:%M:%S"</span><span class="sy0">`</span><span class="br0">&#41;</span><br /> <br /> <span class="kw3">echo</span> <span class="st0">"update <span class="es2">$table</span> set p_dt="</span><span class="re1">$dd</span><span class="st0">" where name="</span><span class="co1">${names[$selection]}</span><span class="st0">" and p_dt="</span><span class="st0">";"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function make_pay  ----------</span><br /> <br /> <span class="co0">#===  FUNCTION  ================================================================</span><br /> <span class="co0">#          NAME:  display_pending</span><br /> <span class="co0">#   DESCRIPTION:  Display all the pending payments</span><br /> <span class="co0">#    PARAMETERS:</span><br /> <span class="co0">#       RETURNS:</span><br /> <span class="co0">#===============================================================================</span><br /> display_pending <span class="br0">&#40;</span><span class="br0">&#41;</span><br /> <span class="br0">&#123;</span><br /> <span class="kw3">echo</span> <span class="st0">"select name,amount,due_dt from <span class="es2">$table</span> where p_dt="</span><span class="st0">";"</span><span class="sy0">|</span>sqlite3 <span class="re1">$file</span> <span class="sy0">|</span> <span class="kw2">sed</span> <span class="re5">-e</span> <span class="co3">\'</span>s<span class="sy0">/|/</span>\\t\\t--<span class="sy0">/</span>g<span class="co3">\'</span> <span class="re5">-e</span> <span class="co3">\'</span>s<span class="sy0">/</span>00:.<span class="sy0">*//</span><span class="co3">\'</span><br /> <span class="br0">&#125;</span>    <span class="co0"># ----------  end of function display_pending  ----------</span><br /> <br /> <span class="kw3">cd</span> <span class="re1">$dir</span><br /> display_menu<br /> <span class="kw2">read</span> <span class="re5">-p</span> <span class="st0">"Please make a selection :: "</span> input<br /> <span class="br0">&#40;</span><span class="br0">&#40;</span>input--<span class="br0">&#41;</span><span class="br0">&#41;</span><br /> <span class="kw3">eval</span> <span class="co1">${menu_function[$input]}</span>
    </div>
  </td>
</tr>

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

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.

How to dump out SQL from an SQLite database (posterous.mclov.in)
SQLabs announces SQLiteManager 3.5 – Powerful SQlite Database Manager (themactrack.com)
Using SQLite (oreilly.com)
Mongrel2 Sqlite3 Config System Working (sheddingbikes.com)

$\"Enhanced$

Tags: Application programming interface Database Database engine Server (computing) sql SQL-92 SQLite Tools

2010/08/21 SQLite – simple use in scripts
2010/08/19 SQLite : Manager for this simple database
2010/08/17 SQLite – Easy to use, dependable and good to use database for small applications.
2010/06/04 SQuirrel SQL for Graphical interface to Oracle/MySQL and loads of other database – OSS and free.
2010/07/25 importing outlook pst files in evolution in Fedora 13 ( Goddard)

Authored By Amit Agarwal

Amit Agarwal, Linux and Photography are my hobbies.Creative Commons Attribution 4.0 International License.

❮ Gmail Videochat Comes to Linux SQLite – simple use in scripts ❯

We notice you're using an adblocker. If you like our webite please keep us running by whitelisting this site in your ad blocker. We’re serving quality, related ads only. Thank you!

I've whitelisted your website.

Not now

Related articles by Zemanta

Related Articles:

Authored By Amit Agarwal