# Programming Gambas from Zip/SQLite
## SQLite Databases

A database is a file on the hard drive that has a structure to it so that it can hold large amounts of information and access it quickly.
SQLite is one type of database. It was written by Dwayne Richard Hipp (born 1961 in North Carolina). It was first released in August 2000. It is public domain, meaning anyone can use it free of charge. Google Chrome, Firefox, the Android operating system for smartphones, Skype, Adobe Reader and the iPhone all use SQLite. It’s just nice. And you pronounce it “S Q L Lite”, so saith Wikipedia.
Databases store information in tables. Gambas has a tableview. This, too, has rows and columns. You can think of a database table as an invisible tableview in the database file.
* Rows are called Records. Columns are called Fields.
For example, a teaching might have a database with a Students table. In that table there is a row for every student. Looking across the row you see StudentID, FirstName, LastName, Sex, DateOfBirth, Address, PhoneNumber. These are the fields. They are the columns.
| StudentID | FirstName | LastName | Sex | DateOfBirth | Address | PhoneNumber |
| 2019001 | Mary | Smith | F | 2008-06-23 | 21 Holly Crt, Bundaberg | 07324657 |
| 2019002 | Jim | Jones | M | 2003-02-19 | 14 Primrose St, Bundaberg | 07123456 |
| 2019003 | Lucy | Watkins | F | 2003-10-05 | 5 Flower St, Bundaberg | 07938276 |
This could be a TableView, or a Table in a Database file.
Every database table has to have a Primary Key. Every record must have a unique value for this field: one that no one else shares. The simplest is to call it RecID and number 1, 2, 3... etc. In the table above, the primary key is going to be the StudentID and it is an integer. The first four digits are the year of enrolment. (We could have another column for YearOfEnrolment and just use a sequence number for the StudentID.)
In SQLite all data is stored as strings, even though you might specify some columns as integers, others as strings and others as dates. SQLite is very forgiving: you can put things that aren’t numbers into integer columns and so on, but try not to. Empty cells are NULL. Try to avoid those, too. When you make a new blank record, initialise values to the empty string, “”.

## Including the Database Facility

SQLite is a component (optional part) of Gambas. There is also a Database access component. On the Project Menu > Properties… > Components page, be sure to tick gb.db and gb.db.sqlite3. Without these components in your project you will get errors as soon as you try to run your program.

## SQL — Structured Query Language

You send messages to SQLite and it sends answers back to you using a special language called SQL (“S Q L” or “sequel”, pronounce it either way.) This means learning another language, but the simple statements that are used most frequently are not difficult to learn. They are the only ones I know, anyway. SQL was invented by Donald D. Chamberlin and Raymond F. Boyce and first appeared in 1974. SQL is so universal that everyone who writes databases knows of it. It is an international standard. SQLite is one implementation of it.
For example, you might send a message to SQLite saying
This says, “select everything from the Students table”. This gives you the whole table. Or you might only want the students who are male:
Perhaps you want everyone, but you want the females first and the males second:
That will get the females first, because “F” comes before “M”. The females will all be in random order and likewise the males unless you write
This returns a table to you with males first (alphabetically by surname) followed by females (alphabetically by surname).
You might only want the students names, so you could write
Perhaps you want only those students who were enrolled in 2019. Now, this is part of the StudentID. You want only those students whose StudentID number starts with “2019”. You use a “wildcard”. The percent (%) sign means “anything here will do”.
When you send these SELECT statements to the database, SQLite will send you back a table. Gambas calls it a RESULT.
Suppose you have a database called db1 (as far as Gambas is concerned) and it is attached to MyStudentDatabase.sqlite which is the actual database file on your hard drive. You need a result to store the reply:
res has the information you asked for. You might want to print the information, or show it in a tableview, or hold it internally in arrays so you can do calculations on it. You need to cycle through the records thus:
For displaying information in a tableview there is a special event that is triggered each time a cell has to have its contents painted on the screen. It is particularly useful if your set of records is large. The tableview does not have to hold all the information from all the records in itself. It can get the information as it needs it for when it has to be displayed. Be a little careful here: if you are depending on all the information being in the tableview, it may or it may not be all there. This is an example of using the _Data event, getting the information from the result table res when it is needed to display a particular cell in the tableview:
Notice the use of TableView1.Data.Text , which represents the text in the cell.
Notice we have result.MoveTo to go to a particular record, result.MoveNext if we are stepping through them one at a time, and result.Available to check to see if there is another record to MoveNext to. Useful in setting the number of rows to have in your tableview is result.RecordCount.
Besides accessing the information in the database, with databases you want to be able to:
* Add records
* Delete records
* Modify records
All but the simplest databases have more than one table in them. Tables can be linked to each other, so records can have signposts in them to indicate lines in other tables that apply. The signpost is the record ID or other primary key of a record in another table. For example, a database of political candidates might have a signpost to the party they belong to. SQL is so smart it can look up the two tables at once to provide you with the information you need, for example this ‘join’ of two tables. (Candidates are in a particular party, and it is the parties that have policies on a variety of issues.)

## Database with a Single Table, to be filled with Random Numbers

The next program comes from https://kalaharix.wordpress.com/Gambas/creating-a-databases-and-tables-from-Gambas/ slightly rearranged. It creates a database in your home folder called Test.sqlite, fills it with random two-digit numbers, then accesses the database to show them in a tableview.
You need a form with a tableview called tv1. Make it long and thin, as it has 2 columns.
The code is
When you work with a database a temporary “journal” file is created. That file is incorporated into the database when it is “committed”. If you don’t want to commit, you “rollback” the database to what it was before you made these latest changes. The temporary file contains the “transaction”, meaning the latest work you have just done to change the database. That is what the db1.Begin, db1.Commit and db1.Rollback mean.
The above program is a good template to adapt when making a database.

## A Cash Spending Application

This application saves records of cash spending. You can allocate each expenditure to a category. Each time you allocate to a category, totals are worked out for the categories and you can see what fraction of your spending went to each of the categories.
If you know you spent, say, €100, and you can only account for, say €85, you can distribute the remaining €15 among the categories.
Before letting loose on the code and after a look at the form we shall take a look at the process of designing such an application.
The File menu has items MenuNewDatabase, MenuOpen and MenuQuit.
The Data Menu has items MenuNewSpending, MenuNewCategory, MenuClearSpending, MenuClearCategories, MenuRound, MenuUnselectAll, MenuCalculate and MenuCopy.
The Help menu is optional.
The textbox whose name you cannot quite see above is tbDistribute.
The program starts by opening the last database file that was open, or prompting to make a new one if it is the first time, or locating it if you sneakily moved it since the last time it was open. It also starts with a blank row in the Spending and Categories tableviews.
When a category is chosen for the selected spending line (click a category line anywhere except in the name column and press Enter) the category totals and percentages are recalculated.
Typing in the Target textbox is optional. If there is a number in it, “Still to do” will be calculated.
Internally, the database has two tables called Spending and Categories. You can see two tableviews corresponding to the two database tables. These are the fields in each table:
The two primary keys are SpendingID and CatID. They number the records in sequence (1, 2, 3...)
The Spending table’s Category field contains a number which, when you look it up in the Categories table, gives you the category name. This is good: if you change the spelling of a category name you only have to change it once.

### Hidden Columns

The user does not need to see the record IDs. They are internal to the database. They have to be unique: each record must have its own record ID. They are the primary keys of the Spending and Categories tables. They will be the very first columns in the tableviews, but they will be hidden from view (zero width). Also, in the Spending table, the user does not want to see the Category ID (the reference to one of the categories). It will be the last column in the Spending table, and also zero width. The columns start from zero, so it is column 5, just to the right of the Amount column.

### List the Jobs

Having sketched out a form design and planned the tables and fields with pencil and paper, we next think of what we want the program to do. It is good to keep in mind the things databases do: Add, Delete, Modify (as well as display the data). Here is a list. These are going to be the subs.
| Database | |
| NewDatabase | Create a new database file on disk with its two tables |
| OpenDatabase | Open the database and display what is in it on startup |
| General | |
| Calculate | Add up totals and work out percentages for each category |
| DoTotals | Grand totals for amounts in spending and categories tables |
| SetupTableViews | The right number of rows and columns and column headings |
| Spending Table | |
| NewSpending | Add a record to the Spending table |
| ShowSpending | Display what is in the Spending table in tv1, the tableview |
| TidySpendingTable | The final part of ShowSpending, really. Alternating blue lines |
| SumSpending | Part of “DoTotals”; add up all the spending totals |
| | Clear a category (make it a right-click menu) |
| | Delete a record when you press DEL or BACKSPACE on a selected line |
| Categories Table | |
| NewCategory | Add a record to the Categories table |
| ShowCategories | Display what is in the Categories table in tvCategories |
| TidyCategoriesTable | The final part of ShowCategories. Alternating blue lines. |
| SumCategories | Part of “DoTotals”; add up all the category amounts |
| | Insert default categories into the categories table (a menu item) |
| EnterOnCategoryLine | Enter on a line inserts category on the selected spending line. |
| | Delete a record when you press DEL or BACKSPACE on a selected line |
| Other Features | |
| | Work out how much is left to allocate |
| | Distribute what is left among the categories |
| | A Help window |
| | Save what database we are using in Settings for next time |
| | Copy everything as text, to paste into a word processing document |
| | Round numbers to whole euros (and check totals are not out by one) |
| | A Quit menu item to close the program |
| Useful Functions | |
| CategoryNameFromID | Given the CatID number, return the Category Name (a string) |
| Massage | Given the user’s choice of filename, remove bad characters |
Now it is time to program. Write the subs. Work out when they will be called on to do their work. Some can be consigned to menus. Some can happen when you click things. You are the one who is going to use this program: Do you want to click buttons? Do you want windows to pop up when you add a new category or a new spending transaction? Are there nice ways of doing things—intuitive ways—so things can happen naturally, as a new user might expect them to happen? We do some thinking and come up with some ideas:
* We can do with a blank row in each table to start with, that you can type in.
* When you finish typing in a cell, save that cell. Avoid having to click a Save button.
* When you press Enter in the last cell of the line, make a new line.
* When a category line is selected and the user presses Enter, put that category into whatever line in the spending table that is selected (highlighted). Move to the next spending line that doesn’t have a category so you can click a category line and Enter it. So you can enter categories for all the lines at the end, after you have entered everything else.
* When you start, open the same database you had open last time. If none, give a choice of creating a new database or browsing to find the database that you moved or somebody may have given to you on a USB or in an email.
* Edit a category by clicking on it.
* Edit a cell in the spending table by clicking on it (except the category — just Enter on a line in the categories table to put a new one in.)
* When you allocate a spending line to a category, recalculate the percentages for all categories.
* When you change the total in the Target textbox, do a subtraction to find out how much you still have left to allocate.
* Put blanks into cells that have nothing in them rather than zeros.
* Pressing Delete or backspace in either of the tableviews will delete the selected (highlighted) line and delete its record from the database. No questions, no confirmation request—it just does it. Only one line can be deleted at a time, and it is easy enough to re-enter if you press Delete by mistake.
* If the first cell on a tableview row has a record ID number in it, the record exists and saving just has to update it. If it is blank, the database has to first create a new record, giving it the next highest record number, put its record number in the first cell, and then update it.
Here are the names of the objects on the form FMain:
Panels: Panel1 (pink), Panel2 (blue)
Labels saying “Spending”, “Categories”, “Target:”, “= Done:”, “+ Still to do:”, “Amount:”
Labels called “LabSpendingTotal” and “LabCategoriesTotal” top right of the tableviews.
TableViews: tv1 for spending and tvCategories
TextBoxes: tbTarget, tbDone, tbToDo, tbDistribute
Button: bDistribute
File Menu: MenuNewDatabase, MenuOpen (Ctrl-O), MenuQuit (Ctrl-Q)
Data Menu: MenuNewSpending (Ctrl-N), MenuNewCategory (Ctrl-K), MenuClearSpending, MenuClearCategories, MenuDefaultCategories, MenuRound (Ctrl-R), MenuUnselectAll (Ctrl-Space), MenuCalculate (F4), MenuCopy (Ctrl-C)
Help Menu: Help and Instructions (F1) (Opens a separate form called Help. Put on it what you like.)
Category Menu (invisible, so it is not on the main menubar): MenuClearCategory (This one pops up with you right-click a category cell in the spending table.)
Here is the code. Following it is an explanation of the SQL statements.

### SQL Statements

Some of these statements are used as they appear. Others are a string that is built up from parts. You might see SQL = … . Bits of the statement are SQL and the field name might be added to it in the right place and be stored in a variable, for example. Or perhaps the record ID might be in a variable called RecID. Use single quotes in the string that is sent to SQLite. Use double quotes when assembling the statement in Gambas.
| SELECT * FROM Spending | Select everything from the Spending table |
| SELECT * FROM Categories | Select everything from the Categories table |
| SELECT MAX(CatID) as 'TheMax' FROM Categories | Get the highest CatID from the Categories table and call it “TheMax”. |
| INSERT INTO Categories(CatID,Category) VALUES(123,'Entertainment') | Create a new record in the Categories table. Put 123 into the CatID field and Entertainment into the Category field. |
| UPDATE Categories SET Category = 'Entertainment' WHERE CatID='123' | The Categories table has to be updated. In the record with CatID equal to 123, put Entertainment in the Category field. |
| SELECT MAX(SpendingID) as 'TheMax' FROM Spending | Find the biggest SpendingID in the Spending table and call it “TheMax”. |
| INSERT INTO Spending(SpendingID,TransDate,Amount,Category,Comment) VALUES('123',' ',' ',' ',' ') | Create a new record in the Spending table. SpendingID = 123 TransDate = a blank Amount = a blank Category = a blank Comment = a blank |
| UPDATE Spending SET TransDate = '4-11-2019' WHERE SpendingID='123' | Put “4-11-2019” into the TransDate field of the record in the Spending table that has a SpendingID of 123. |
| DELETE FROM Spending WHERE SpendingID='123' | Delete the record in the Spending table that has a record ID of 123. |
| DELETE FROM Categories WHERE CatID='123' | Delete the record in the Categories table that has a record ID of 123. |
| DELETE FROM Spending | Delete every record from the Spending table. All the data disappears, never to be seen again. |
| DELETE FROM Categories | Delete every record from the Categories table. All the category records, gone forever. |
| SELECT Category FROM Categories WHERE CatID=123 | Give me the name of the category that goes with the CatID record number 123. |
| UPDATE Spending SET Category='4' WHERE SpendingID='123' | Set the Category field of record 123 of the Spending table to 4. This spending item goes into the fourth category, whatever that is. To find out what the fourth category is, look up the Categories table and find the record with CatID=4 |
| SELECT Total(Amount) AS TotalAmount FROM Spending WHERE Category='4' | Get the sum of all the numbers in the Amount fields of all the records in the Spending table that have 4 in their Category field. Simply, add up all the amounts spent in category 4. Call the answer “TotalAmount” |
| SELECT Max(CatID) AS MaxCatID FROM Categories | Get the highest CatID from the Categories table. Call it MaxCatID. |
| SQL = "INSERT INTO Categories(CatID,Category) VALUES(4,Travel)" | Create a new Categories record. Set the CatID field equal to 4 and the Category to “Travel”. |
| UPDATE Categories SET Category='Travel' WHERE CatID=4 | Update the Categories record that has a record ID of 4. Put “Travel” into the Category field. |
| UPDATE Spending Set Category=' ' WHERE SpendingID=123 | Put a blank into the Category field of Spending record 123 |
The statements are either SELECT, INSERT, DELETE or UPDATE.
The patterns are:
These are not the only SQL statements: there are many more. They are enough to get a working knowledge of SQL. Online help for SQLite can be found at
http://www.sqlitetutorial.net/
http://sqlitetutorials.com/
https://www.w3schools.com/sql/
A most important point about using the UPDATE statement:
* Be careful when updating records.
* If you omit the WHERE clause, ALL records will be updated!
For example, do not write this: UPDATE Spending SET Amount=12.50 .This puts 12.50 into the Amount field of every record. All amounts become 12.50. You should say UPDATE Spending SET Amount=12.50 WHERE SpendingID=42 .