MyDatabase

MyDatabase is a universal database solution based on sqlite.

Properties:

• Fields: 1x Listbox from separate table, 1x textfield 100, 1x textfield 255, 3x textfield 50, 3x decimal, 1x date, 1x checkbox
• Add, delete, edit data in GUI
• Add, delete, edit queries in GUI
• Export query results to csv

MyDatabase is not free, the price is 1,99 €.

Manual

Manual for MyDatabase

Applications first start
The database is stored on sdcard and named "MyDatabase.db". Three tables will be created on first start:

Categories
Fields: _id integer primary key autoincrement, name varchar(20) UNIQUE
the table contains your inserted categories

Queries
Fields: _id integer primary key autoincrement, name varchar(50) UNIQUE, sql varchar(255)
the table contains your created queries

Notes
Fields: __id integer primary key autoincrement, category varchar(20), theme varchar(100), text_l varchar(255), favorit integer, date date, text_s1 varchar(50), text_s2 varchar(50), text_s3 varchar(50), number_1 real, number_2 real, number_3 real
the table is the main table containing your data

The first entry
After starting the main screen is empty, because no data are entered. Press the hardware menu button of your phone, the menu will appear. See attached image 1
Hint: the menu is only available in the main screen
By pressing "New entry", a form will be displayed. See attached image 2/1
Enter some data in the form, if you activate the checkbox, the given entry will be listed in the start screen, the date should be entered as explained or easy taken from the date picker, the numeric fields have a point as decimal separator.
By saving ("Save" button) the data will be written to the database and displayed in the list with theme and date. See attached image 2/2

Further entries
If you press an already entered item from the list, the data of this item will be displayed for editing. This is also an easy way to create similarly entries. Simply change the data and create the new entry with "Save as new". See attached image 3

Creating new categories
Press the hardware menu button of your phone and select "Queries". The form to manage queries will be displayed. There are two queries already implemented. Select "Insert category" from the listbox. Replace the XXXXXX with your category name and press "Execute". See attached image 4/1
A green messagebox confirms the success, if it's red, the query name already exists.
Now the new category is available in the data entry form. See attached image 4/2

Search by theme
Press the hardware menu button of your phone and select "Search for themes". The search screen is displayed. See attached image 5/1
Enter the search string and press "Show". The related entries will be found and displayed. See attached image 5/2
By clicking a listed item the screen to edit the data will occur.

Managing queries
Press the hardware menu button of your phone and select "Queries". The form to manage queries will be displayed. The easiest way to make a new query is to overwrite the name and the sql text an to press "Save as new". A green messagebox confirms the success, if it's red, the query name already exists. See attached image 6/1
By pressing "Execute" the query will be processed. If the query string is not crrect, a red warning messagebox will be displayed, so check the query syntax. If als is fine, the query results will be displayed in a new window. See attached image 6/2.
By pressing "Export" the result will be stored in the file /sdcard/Mydatabase.csv. If the file already exists it will be overwritten. So after an export you should rename the file.

Example: Cars expenses

Objective

There are different kind of expenses, e.g.:
fuel
insurance
repairs
inspection
other (some accessories ...)
So we should make one category for car, to separate this project from others. In this category we'll generate 5 themes to divide the kinds of expenses. To build summaries, the paid value will be entered in the first numeric field.

The new category "Car"
Press "Queries" from the menu, in the queries screen select "Insert Category" and replace XXXXXX with Car and execute the query.
See attached image 1

Making the themes

Fuel
Press "New entry" from the menu. Select "Car" from category, enter "Fuel" in the theme field and give a description to the next field. We'll check at least one entry per theme to show at start to make it easy to add new entries. The date will be entered or taken from the date picker. The text field not necessary, so we'll use them as label for the numeric fields. In the first numeric field the paid value, in the second the liters and in the third the kilometers since last refuel will be entered. Store the entry with "Save".
See details in attached image 2/1

Repair
Press the entry Fuel from the list. Change the theme to "Repair", enter the reason in the next textfield, change date and change value for "Paid". Make the last two textfield and numerics empty and "Save as new".
See details in attached image 2/2

Insurance, Inspection, Other
Make the first entries for the themes Insurance, Inspection and Other analog to Repair.

The queries

Expenses by theme
The expenses will be summarized by theme. Press "Queries" from the menu, in the queries screen. Enter "Car by theme" in the name field and:
select theme, SUM(number_1) from Notes where category="Car" group by theme
in the sql field and "Save as new". Execute the new query.
See details in attached image 3/1+2

Fuel consumption
The average of fuel consumption will be reported. Press "Queries" from the menu, in the queries screen. Enter "Car fuel average" in the name field and:
select SUM(number_2)/SUM(number_3)*100 AS Average from Notes where category="Car" and theme="Fuel"
in the sql field and "Save as new". Execute the new query.
See details in attached image 4/1+2

Expenses per year
Make a new entry for the theme "Fuel". Select the entry Fuel from the list, change at least the date to one in 2011 and "Save as new".
The expenses per year will be reported. Press "Queries" from the menu, in the queries screen. Enter "Car per year" in the name field and:
select strftime("%Y",date) as Year, SUM(number_1) as Costs from Notes where category="Car" group by strftime("%Y",date)
in the sql field and "Save as new". Execute the new query.
Hint: strftime substracts the year from a date
See details in attached image 5/1+2

Done!
In future enter your expenses in the related themes and use your stored queries for reporting.

Recent changes

v 2.1
Fixed: sql error at some devices by starting the app