從 Zip/SQLite 開始程式設計 Gambas
資料庫是硬碟上具有結構的檔案，因此它可以儲存大量資訊並快速訪問它。
SQLite 是一種型別的資料庫。它是由 Dwayne Richard Hipp（1961 年出生於北卡羅來納州）編寫的。它於 2000 年 8 月首次釋出。它是公共領域的，這意味著任何人都可以免費使用它。Google Chrome、Firefox、智慧手機的 Android 作業系統、Skype、Adobe Reader 和 iPhone 都使用 SQLite。它就是很好。您應該把它讀作“S Q L Lite”，正如維基百科所說。
資料庫將資訊儲存在表中。Gambas 有一個表格檢視。它也有行和列。您可以將資料庫表視為資料庫檔案中的一個不可見的表格檢視。
- 行稱為記錄。列稱為欄位。
例如，一位教師可能有一個包含“學生”表的資料庫。在該表中，每位學生都有一行。檢視整行，您將看到學生 ID、姓氏、名字、性別、出生日期、地址、電話號碼。這些是欄位。它們是列。
| 學生 ID | 姓氏 | 名字 | 性別 | 出生日期 | 地址 | 電話號碼 |
| 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 |
這可能是一個表格檢視或資料庫檔案中的一個表。
每個資料庫表都必須有一個主鍵。每條記錄都必須為此欄位具有唯一的 value：一個其他人都不共享的 value。最簡單的辦法是稱之為RecID 並編號為 1、2、3... 等。在上表中，主鍵將是學生 ID，它是一個整數。前四位數字是入學年份。（我們可能會有另一個名為入學年份的列，並且只為學生 ID 使用順序號。）
在 SQLite 中，所有資料都以字串形式儲存，即使您可能將某些列指定為整數、其他列指定為字串，而其他列指定為日期。SQLite 非常寬容：您可以將非數字內容放入整數列等等，但儘量不要這樣做。空單元格為 NULL。也要儘量避免它們。當您建立一個新的空白記錄時，將 values 初始化為空字串“”。
SQLite 是 Gambas 的一個元件（可選部分）。還有一個數據庫訪問元件。在專案選單 > 屬性... > 元件頁面上，確保選中gb.db 和gb.db.sqlite3。如果您的專案中沒有這些元件，您將在嘗試執行程式時立即收到錯誤。
您向SQLite 傳送訊息，它使用一種稱為SQL（“S Q L”或“sequel”，無論哪種讀法都可以）的特殊語言將答案發送回您。這意味著學習另一種語言，但最常用的簡單語句並不難學。無論如何，這些是我知道的唯一語句。SQL 由 Donald D. Chamberlin 和 Raymond F. Boyce 發明，並於 1974 年首次出現。SQL 是如此普遍，以至於所有編寫資料庫的人都知道它。它是一個國際標準。SQLite 是它的一個實現。
例如，您可能會向SQLite 傳送一條訊息，內容為
SELECT * FROM Students
這表示“從‘學生’表中選擇所有內容”。這將為您提供整個表。或者您可能只想檢視男性學生
SELECT * FROM Students WHERE Sex = 'M'
也許您想要所有人，但您希望女性排在前面，男性排在後面
SELECT * FROM Students ORDER BY Sex
這將首先獲取女性，因為“F”位於“M”之前。所有女性將以隨機順序排列，男性也是如此，除非您寫入
SELECT * FROM Students ORDER BY Sex DESC, LastName ASCSELECT * FROM Students ORDER BY Sex DESC, LastName ASC
這將返回一個表，其中男性排在前面（按姓氏字母順序排列），然後是女性（按姓氏字母順序排列）。您可能只想檢視學生姓名，因此您可以寫入
SELECT FirstName, LastName FROM Students ORDER BY LastName
也許您只想檢視 2019 年入學的學生。現在，這是學生 ID 的一部分。您只想檢視學生 ID 號以“2019”開頭的學生。您可以使用“萬用字元”。百分號 (%) 表示“這裡可以是任何內容”。
SELECT FirstName, LastName FROM Students WHERE StudentID LIKE '2019%'
當您將這些SELECT 語句傳送到資料庫時，SQLite 將向您傳送回一個表。Gambas 稱之為RESULT。假設您有一個名為db1 的資料庫（就 Gambas 而言），它連線到您的硬碟上的實際資料庫檔案MyStudentDatabase.sqlite。您需要一個結果來儲存回覆
Dim res as Result
res = db1.exec("SELECT * FROM Students")
res 包含您請求的資訊。您可能希望列印這些資訊，或在表格檢視中顯示它們，或者在陣列中內部儲存它們以便對其進行計算。您需要迴圈遍歷這些記錄，因此
While res.Available
'do something with res!FirstName, res!LastName and res!DateOfBirth etc
res.MoveNext
Wend
對於在表格檢視中顯示資訊，有一個特殊的事件會在每次單元格必須在其內容繪製到螢幕上時觸發。如果您的記錄集很大，這將特別有用。表格檢視不需要在自身中包含所有記錄的所有資訊。它可以在需要顯示時獲取資訊。這裡需要注意的是：如果您依賴於表格檢視中的所有資訊，那麼它可能存在或可能不存在。這是一個使用_Data 事件的示例，在需要顯示錶格檢視中的特定單元格時，從結果表 res 中獲取資訊
Public Sub TableView1_Data(Row As Integer, Column As Integer)
res.MoveTo(row)
If Column = 0 Then
TableView1.Data.Text = res!FirstName
Else
TableView1.Data.Text = res!LastName
Endif
End
請注意，TableView1.Data.Text 表示單元格中的文字。
請注意，我們使用result.MoveTo 轉到特定記錄，使用result.MoveNext 一次遍歷一個記錄，並使用result.Available 檢查是否還有另一個記錄要移到。在設定表格檢視中要包含的行數時，result.RecordCount 很有用。
除了訪問資料庫中的資訊之外，使用資料庫時，您還需要能夠
- 新增記錄
- 刪除記錄
- 修改記錄
除了最簡單的資料庫之外，所有資料庫都包含多個表。表可以相互連結，因此記錄可以包含指向其他表中適用的行的指路牌。指路牌是另一個表中記錄的記錄 ID 或其他主鍵。例如，政治候選人的資料庫可能包含指向其所屬政黨的指路牌。SQL 非常聰明，它可以同時檢視這兩個表，以為您提供您需要的資訊，例如這兩個表的“連線”。（候選人屬於特定政黨，而政黨對各種問題制定了政策。）
SELECT Candidate,PolicyOnPensions FROM Candidates,Parties WHERE Candidate.PartyID = Parties.PartyID AND Candidates.Electorate="Fairfax"
下一個程式來自https://kalaharix.wordpress.com/Gambas/creating-a-databases-and-tables-from-Gambas/，稍作調整。它在您的主資料夾中建立一個名為Test.sqlite 的資料庫，用隨機的兩位數填充它，然後訪問資料庫以在表格檢視中顯示它們。
您需要一個包含名為tv1 的表格檢視的窗體。將其設定得又長又薄，因為它有兩列。
程式碼為
' Gambas class file
Private db1 As New Connection
Private rs As Result
Public Sub SetupTableView()
tv1.header = GridView.Horizontal
tv1.grid = True
tv1.Rows.count = 0
tv1.Columns.count = 2
tv1.Columns[0].text = "RecID"
tv1.Columns[1].text = "Value"
tv1.Columns[0].width = 55
tv1.Columns[1].width = 55
End
Public Sub CreateDatabase()
db1.Type = "sqlite"
db1.host = User.home
db1.name = ""
'delete an existing test.sqlite
If Exist(User.home & "/Test.sqlite") Then
Kill User.home & "/Test.sqlite"
Endif
'create test.sqlite
db1.Open
db1.Databases.Add("Test.sqlite")
db1.Close
End
Public Sub MakeTable()
Dim hTable As Table
db1.name = "Test.sqlite"
db1.Open
hTable = db1.Tables.Add("RandomNumbers")
hTable.Fields.Add("RecID", db.Integer)
hTable.Fields.Add("Value", db.Integer)
hTable.PrimaryKey = ["RecID"]
hTable.Update
End
Public Sub FillTable()
Dim i As Integer
Dim rs1 As Result
db1.Begin
rs1 = db1.Create("RandomNumbers")
For i = 1 To 10000
rs1!RecID = i
rs1!Value = Rand(10, 99)
rs1.Update
Next
db1.Commit
Catch
db1.Rollback
Message.Error(Error.Text)
End
Public Sub ReadData()
'read the database
Dim SQL As String = "SELECT * FROM RandomNumbers"
rs = db1.Exec(SQL)
End
Public Sub Form_Open()
SetupTableView
CreateDatabase
MakeTable
FillTable
ReadData
End
Public Sub Form_Activate()
'change the rowcount of the gridview from 0 to the number of records.
'This triggers the data handling event
tv1.Rows.Count = rs.Count
End
Public Sub tv1_Data(Row As Integer, Column As Integer)
rs.moveTo(row)
If Column = 0 Then tv1.Data.Text = rs!RecID Else tv1.Data.Text = rs!Value
'If Column = 0 Then tv1.Data.Text = Str(rs["RecID"]) Else tv1.Data.Text = Str(rs["Value"])
'Either of these two lines will do it.
End
Public Sub Form_Close()
db1.Close
End
當您使用資料庫時，會建立一個臨時“日誌”檔案。該檔案在“提交”時會合併到資料庫中。如果您不想提交，您可以將資料庫“回滾”到您進行最新更改之前的樣子。臨時檔案包含“事務”，這意味著您剛剛進行的最新更改資料庫的工作。這就是db1.Begin、db1.Commit 和db1.Rollback 的含義。
上面的程式是製作資料庫時需要調整的一個很好的模板。
此應用程式儲存現金支出的記錄。您可以將每項支出分配到一個類別。每次分配到類別時，都會計算出類別的總計，並且您可以檢視支出中的幾分之幾用於每個類別。
如果您知道自己花了例如 100 歐元，但您只能說明例如 85 歐元，那麼您可以在類別之間分配剩餘的 15 歐元。
在放手編寫程式碼之前，以及檢視窗體之後，我們將看一下設計此類應用程式的過程。
檔案選單包含MenuNewDatabase、MenuOpen 和MenuQuit 項。
資料選單包含MenuNewSpending、MenuNewCategory、MenuClearSpending、MenuClearCategories、MenuRound、MenuUnselectAll、MenuCalculate 和MenuCopy 項。
幫助選單是可選的。
您無法完全看到其名稱的文字框是tbDistribute。
程式啟動時會開啟上次開啟的資料庫檔案，如果這是第一次使用，則會提示建立新的資料庫檔案，或者如果上次開啟後你偷偷地移動了資料庫檔案，則會提示你找到它。它還會在支出和類別表格檢視中新增一個空白行。
當為選定的支出行選擇一個類別時（點選類別行中的任何位置，但不包括名稱列，然後按 Enter 鍵），類別總計和百分比將重新計算。
在目標文字框中輸入內容是可選的。如果文字框中有數字，則會計算“剩餘待分配”金額。
在內部，資料庫有兩個表，名為支出和類別。您可以看到對應於這兩個資料庫表的兩個表格檢視。以下是每個表中的欄位。
兩個主鍵是支出ID和類別ID。它們按順序對記錄進行編號（1、2、3...）。
支出表的類別欄位包含一個數字，當您在類別表中查詢該數字時，會得到類別名稱。這樣做的好處是：如果您更改類別名稱的拼寫，只需更改一次即可。
隱藏列
[edit | edit source]使用者不需要看到記錄 ID。它們是資料庫內部的。它們必須是唯一的：每條記錄必須有自己的記錄 ID。它們是支出和類別表的 主鍵。它們將是表格檢視中的第一列，但將隱藏（寬度為零）。此外，在支出表中，使用者不希望看到類別 ID（對某個類別的引用）。它將是支出表中的最後一列，也是寬度為零。列從零開始，因此它是第 5 列，位於金額列的右側。
列出任務
[edit | edit source]在用鉛筆和紙張勾勒出表單設計並規劃好表格和欄位之後，我們接下來考慮希望程式執行的操作。需要注意的是，資料庫可以執行的操作：新增、刪除、修改（以及顯示資料）。下面列出了要執行的任務。這些將是子程式。
| 資料庫 | |
| 新建資料庫 | 在磁碟上建立一個新的資料庫檔案，其中包含兩個表。 |
| 開啟資料庫 | 開啟資料庫並在啟動時顯示其中的內容。 |
| 常規 | |
| 計算 | 為每個類別彙總總計並計算百分比。 |
| 計算總計 | 支出和類別表中金額的總計。 |
| 設定表格檢視 | 合適的行數和列數以及列標題。 |
| 支出表 | |
| 新建支出 | 向支出表新增記錄。 |
| 顯示支出 | 在 tv1（表格檢視）中顯示支出表中的內容。 |
| 整理支出表 | 實際上是顯示支出的最後部分。交替顯示藍色線條。 |
| 彙總支出 | “計算總計”的一部分；彙總所有支出總計。 |
| 清除類別（使其成為右鍵選單）。 | |
| 在選定行上按 DEL 或 BACKSPACE 鍵時刪除記錄。 | |
| 類別表 | |
| 新建類別 | 向類別表新增記錄。 |
| 顯示類別 | 在 tvCategories 中顯示類別表中的內容。 |
| 整理類別表 | 顯示類別的最後部分。交替顯示藍色線條。 |
| 彙總類別 | “計算總計”的一部分；彙總所有類別金額。 |
| 將預設類別插入類別表（選單項）。 | |
| 在類別行上按 Enter 鍵 | 在行上按 Enter 鍵會在選定的支出行上插入類別。 |
| 在選定行上按 DEL 或 BACKSPACE 鍵時刪除記錄。 | |
| 其他功能 | |
| 計算剩餘的分配金額。 | |
| 將剩餘的金額分配到各個類別。 | |
| 幫助視窗 | |
| 將當前使用的資料庫儲存在設定中，以便下次使用。 | |
| 將所有內容複製為文字，以便貼上到文字處理文件中。 | |
| 將數字四捨五入到整數歐元（並檢查總計是否差一）。 | |
| 一個“退出”選單項，用於關閉程式。 | |
| 有用函式 | |
| 從 ID 獲取類別名稱 | 給定 CatID 編號，返回類別名稱（字串）。 |
| 整理 | 給定使用者選擇的檔名，刪除非法字元。 |
現在開始程式設計。編寫子程式。計算子程式何時會被呼叫以執行其工作。一些子程式可以被分配到選單中。另一些子程式可以在您點選某些內容時執行。您是將要使用此程式的人：您希望點選按鈕嗎？您希望在新增新類別或新支出交易時彈出一個視窗嗎？有沒有什麼好的方法，即直觀的方法，可以讓事情自然而然地發生，正如新使用者所期望的那樣？我們思考一下，並提出一些想法。
- 我們可以從每個表中的空白行開始，您可以直接在這些行中輸入內容。
- 在單元格中完成輸入後，儲存該單元格。避免點選儲存按鈕。
- 在行中的最後一個單元格中按Enter鍵，會建立新的一行。
- 當選中類別行並且使用者按Enter鍵時，會將該類別放入選定（高亮顯示）的支出行中。移動到下一行沒有類別的支出行，以便您可以點選類別行並按Enter鍵輸入類別。因此，您可以在輸入完其他所有內容後，在最後為所有行輸入類別。
- 在啟動時，開啟上次開啟的資料庫。如果沒有，則可以選擇建立一個新資料庫，或者瀏覽以找到您移動過的資料庫，或者其他人可能透過 USB 或電子郵件給您的資料庫。
- 透過點選類別來編輯類別。
- 透過點選支出表中的單元格來編輯單元格（類別除外，只需在類別表中的一行上按Enter鍵即可插入新的類別）。
- 當您將支出行分配到某個類別時，重新計算所有類別的百分比。
- 當您更改目標文字框中的總計金額時，執行減法運算，以計算出剩餘的待分配金額。
- 在空白的單元格中放入空白，而不是零。
- 在任一表格檢視中按Delete或Backspace鍵將刪除選定（高亮顯示）的行，並將從資料庫中刪除其記錄。沒有疑問，沒有確認請求，它只是執行操作。一次只能刪除一行，如果您錯誤地按了Delete鍵，重新輸入非常容易。
- 如果表格檢視行上的第一個單元格中包含記錄 ID 號碼，則該記錄存在，儲存操作只需更新它。如果它是空白的，則資料庫必須首先建立一個新的記錄，併為其分配下一個最高的記錄號碼，將記錄號碼放在第一個單元格中，然後更新它。
以下是FMain表單上的物件名稱。
面板：Panel1（粉色）、Panel2（藍色）。
顯示“支出”、“類別”、“目標：”、“= 已完成：”、“+ 剩餘待分配：”、“金額：”的標籤。
名為“LabSpendingTotal”和“LabCategoriesTotal”的標籤，位於表格檢視的右上角。
表格檢視：tv1 用於支出，tvCategories 用於類別。
文字框：tbTarget、tbDone、tbToDo、tbDistribute。
按鈕：bDistribute。
檔案選單：MenuNewDatabase、MenuOpen（Ctrl-O）、MenuQuit（Ctrl-Q）。
資料選單：MenuNewSpending（Ctrl-N）、MenuNewCategory（Ctrl-K）、MenuClearSpending、MenuClearCategories、MenuDefaultCategories、MenuRound（Ctrl-R）、MenuUnselectAll（Ctrl-空格鍵）、MenuCalculate（F4）、MenuCopy（Ctrl-C）。
幫助選單：幫助和說明（F1）（開啟一個名為幫助的單獨表單。在上面放置您喜歡的內容。）
類別選單（不可見，因此不在主選單欄上）：MenuClearCategory（當您右鍵單擊支出表中的類別單元格時，該選單會彈出）。
這是程式碼。程式碼後面是對 SQL 語句的解釋。
Public fdb As New Connection 'finance database
Public rs As Result 'result set after querying database
Public SQL As String
Public Sub Form_Open()
SetUpTableViews
If IsNull(Settings["Database/host"]) Then
Select Case Message.Question("Create a new data file, or open an existing one?", "New...", "Open...", "Quit")
Case 1 'new
NewDatabase
Case 2 'open
OpenDatabase(Null, Null)
Case Else
Quit
End Select
Else
OpenDatabase(Settings["Database/host"], Settings["Database/name"])
Endif
End
Public Sub Form_Close()
fdb.Close 'close connection
End
Public Sub OpenDatabase(dbHost As String, dbName As String) 'if these are null, ask where the database is
If Not Exist(dbHost &/ dbName) Or IsNull(dbHost) Then 'it's not where it was last time, or path not supplied
Dialog.Title = "Where is the database?"
Dialog.Filter = ["*.db"]
Dialog.Path = User.Home &/ "Documents/"
If Dialog.OpenFile() Then Return ' User pressed Cancel; still can't open a database
Dim s As String = Dialog.Path
Dim p As Integer = RInStr(s, "/") 'position of last slash
fdb.host = Left(s, p)
fdb.Name = Mid(s, p + 1)
Else
fdb.host = dbHost
fdb.Name = dbName
End If
Try fdb.Close
fdb.type = "sqlite3"
Try fdb.Open
If fdb.Opened Then
FMain.Caption = fdb.host &/ fdb.Name
Settings["Database/host"] = fdb.host
Settings["Database/name"] = fdb.Name
Else
Message.Info("<b>Couldn't connect.</b><br><br>... please try again or create a new database.")
Return
Endif
ShowSpending
ShowCategories
Calculate
End
Public Sub NewDatabase()
Dialog.Path = User.Home & "/" 'setting it to "~/" doesn't work
Dialog.Title = "Create a New Database"
If Dialog.SaveFile() Then Return 'clicked Cancel
Dim s As String = Dialog.Path & ".db"
Dim p As Integer = RInStr(s, "/") 'position of last slash
Dim FName As String = Mid(s, p + 1)
fdb.host = Left(s, p)
fdb.Name = "" 'This MUST be left blank. If not, database file will not be created
fdb.Type = "sqlite3"
If Exist(s) Then Kill s 'delete existing file of that name
fdb.Close
Try fdb.Open 'opens a connection to the database; do this after setting properties and before creating
If Error Then
Message("Unable to open the database file<br><br>" & Error.Text)
Return
Endif
fdb.Databases.Add(fName) 'does the creating
fdb.Close
Dim dbTable As Table
fdb.name = fName
Try fdb.Open
If Not fdb.opened Then
Message("Unable to open the data file")
Return
Endif
dbTable = fdb.Tables.Add("Spending")
dbTable.Fields.Add("SpendingID", db.Integer)
dbTable.Fields.Add("TransDate", db.String)
dbTable.Fields.Add("Category", db.Integer)
dbTable.Fields.Add("Comment", db.String)
dbTable.Fields.Add("Amount", db.Float)
dbTable.PrimaryKey = ["SpendingID"]
dbTable.Update
rs = fdb.Create("Spending")
If fdb.Error Then
Message("Couldn't create the Spending table.<br><br>: " & Error.Text)
Return
Endif
rs!SpendingID = 1
rs!TransDate = ""
rs!Category = 0
rs!Comment = ""
rs!Amount = 0.0
rs.Update
fdb.Commit
If fdb.Error Then
Message("Couldn't save a first record in the Spending table.<br><br>: " & Error.Text)
Return
Endif
fdb.Close
fdb.name = fName
Try fdb.Open
If Not fdb.opened Then
Message("Unable to open the data file")
Return
Endif
dbTable = fdb.Tables.Add("Categories")
dbTable.Fields.Add("CatID", db.Integer)
dbTable.Fields.Add("Category", db.String)
dbTable.PrimaryKey = ["CatID"]
dbTable.Update
rs = fdb.Create("Categories")
If fdb.Error Then
Message("Couldn't create the Categories table.<br><br>: " & Error.Text)
Return
Endif
rs!CatID = 1
rs!Category = ""
rs.Update
fdb.Commit
If fdb.Error Then
Message("Couldn't save a first record in the Categories table.<br><br>: " & Error.Text)
Return
Endif
End
Public Sub DoTotals()
labCategoriesTotal.Text = SumTheCategories()
labSpendingTotal.text = SumTheSpending()
tbDone.Text = labSpendingTotal.Text
End
Public Sub ShowSpending()
rs = fdb.Exec("SELECT * FROM Spending")
Dim L, CatID As Integer
Dim CatName As String
tv1.Rows.Count = 0 'clear
If Not IsNull(rs) Then
While rs.Available
tv1.Rows.Count += 1
L = tv1.Rows.max
tv1[L, 0].text = rs!SpendingID
tv1[L, 1].Text = rs!TransDate
tv1[L, 2].Text = Format(rs!Amount, "0.00")
CatName = rs!Category
If Not IsNull(CatName) Then
CatID = If(IsNull(Val(CatName)), -1, Val(CatName))
If CatID > -1 Then tv1[L, 3].Text = CategoryNameFromID(CatID)
Endif
tv1[L, 4].Text = rs!Comment
tv1[L, 5].Text = rs!Category 'Category ID in this hidden column
rs.MoveNext
Wend
Endif
If tv1.Rows.Count = 0 Then tv1.Rows.Count = 1
TidySpendingTable
End
Public Sub ShowCategories()
rs = fdb.Exec("SELECT * FROM Categories")
Dim L As Integer
Dim t As Float
tvCategories.Rows.Count = 0 'clear
If Not IsNull(rs) Then
While rs.Available
tvCategories.Rows.Count += 1
L = tvCategories.Rows.max
tvCategories[L, 0].text = rs!CatID
tvCategories[L, 3].Text = rs!Category
rs.MoveNext
Wend
Endif
If tvCategories.Rows.Count = 0 Then tvCategories.Rows.Count = 1
TidyCategoriesTable
End
Public Sub NewSpending()
tv1.Rows.count = tv1.Rows.count + 1
tv1.MoveTo(tv1.Rows.Max, 1)
tv1.Edit
End
Public Sub NewCategory()
tvCategories.Rows.count = tvCategories.Rows.count + 1
tvCategories.row += 1
tvCategories.Edit
End
Public Sub tv1_Insert()
NewSpending
End
Public Sub tvCategories_Insert()
NewCategory
End
Public Sub tv1_Click()
Select Case tv1.Column
Case 1, 2, 4
tv1.Edit
Case 3
If tvCategories.Rows.Count > 0 Then
tvCategories.SetFocus
tvCategories.Rows[0].Selected = True
Endif
End Select
End
Public Sub tvCategories_Click()
If tvCategories.Column = 3 Then tvCategories.Edit
End
Public Sub SetUpTableViews()
Dim i As Integer
tv1.Columns.count = 6
tv1.Rows.count = 1
tv1.Columns[0].Width = 0
tv1.Columns[1].Alignment = Align.Center
tv1.Columns[2].Alignment = Align.Right
For i = 1 To tv1.Columns.Max - 1
tv1.Columns[i].Width = Choose(i, 80, 80, 130, tv1.Width - tv1.ClientW - 306)
tv1.Columns[i].Text = Choose(i, "Date", "Amount", "Category", "Comment")
Next
tvCategories.Columns.count = 4
tvCategories.Rows.count = 1
tvCategories.Columns[0].Width = 0
For i = 1 To tvCategories.Columns.Max
tvCategories.Columns[i].Width = Choose(i, 60, 60, tvCategories.Width - tvCategories.ClientW - 350)
tvCategories.Columns[i].Text = Choose(i, "Total", "%", "Category")
Next
tvCategories.Columns[1].Alignment = Align.right
tvCategories.Columns[2].Alignment = Align.Center
tv1.Columns[5].Width = 0
End
Public Sub TidySpendingTable()
For i As Integer = 0 To tv1.Rows.Max
For j As Integer = 0 To tv1.Columns.Max
If j = 2 Or j = 3 Then tv1[i, j].Padding = 4
If i Mod 2 = 1 Then tv1[i, j].Background = &hF0F0FF
Next
Next
End
Public Sub TidyCategoriesTable()
For i As Integer = 0 To tvCategories.Rows.Max
For j As Integer = 1 To tvCategories.Columns.Max
tvCategories[i, j].Padding = 4
If i Mod 2 = 1 Then tvCategories[i, j].Background = &hF0F0FF
Next
Next
End
Public Sub Massage(s As String) As String
'Doesn't like spaces or hyphens in file names. Doesn't complain; just doesn't create the file.
Dim z As String
For i As Integer = 0 To Len(s) - 1
If IsLetter(s[i]) Or IsDigit(s[i]) Or s[i] = "_" Or s[i] = "." Then z &= s[i] Else z &= "_"
Next
Return z
End
Public Sub tvCategories_Save(Row As Integer, Column As Integer, Value As String)
Dim RecID As Integer
Dim OriginalValue As String = tvCategories[Row, Column].Text
tvCategories[Row, Column].Text = Value
If IsNull(tvCategories[Row, 0].Text) Then 'no record ID, so we need a new record
Dim Res As Result
SQL = "SELECT MAX(CatID) as 'TheMax' FROM Categories"
Res = fdb.Exec(SQL)
If IsNull(Res!TheMax) Then RecID = 1 Else RecID = Res!TheMax + 1
tvCategories[Row, 0].Text = RecID
SQL = "INSERT INTO Categories(CatID,Category) VALUES(" & RecID & ",'')"
fdb.Exec(SQL)
If fdb.Error Then Message("Couldn't save:<br><br>" & SQL & "<br><br>" & Error.Text)
Endif
'update the record
RecID = tvCategories[Row, 0].Text
SQL = "UPDATE Categories SET Category = '" & Value & "' WHERE CatID='" & RecID & "'"
Try fdb.Exec(SQL)
If fdb.Error Then Message("Couldn't save:" & SQL & "<br><br>" & Error.Text)
If Value <> OriginalValue Then ShowSpending 'category name was changed
End
Public Sub tv1_Save(Row As Integer, Column As Integer, Value As String)
Dim RecID As Integer
Dim FieldName As String = Choose(Column, "TransDate", "Amount", "Category", "Comment")
If IsNull(tv1[Row, 0].Text) Then 'There's no Record ID, so insert a new record
Dim Res As Result
SQL = "SELECT MAX(SpendingID) as 'TheMax' FROM Spending"
Try Res = fdb.Exec(SQL)
If IsNull(Res!TheMax) Then RecID = 1 Else RecID = Res!TheMax + 1
tv1[Row, 0].Text = RecID
SQL = "INSERT INTO Spending(SpendingID,TransDate,Amount,Category,Comment) VALUES('" & RecID & "',' ',' ',' ',' ')"
Try fdb.Exec(SQL)
If Error Then
Message("Couldn't save: " & Error.Text)
Return
Endif
Endif
'update record
RecID = tv1[Row, 0].Text
SQL = "UPDATE Spending SET " & FieldName & " = '" & Value & "' WHERE SpendingID='" & RecID & "'"
Try fdb.Exec(SQL)
If Error Then
Message("Couldn't save:" & SQL & "<br><br>" & Error.Text)
Return
Endif
If Column = 2 Then
tv1[Row, Column].Text = Format(Val(Value), "###0.00")
Calculate 'amount has changed
Else
tv1[Row, Column].Text = Value
Endif
Catch
Message("Couldn't save ... have you created and opened a database yet?")
Stop Event 'Don't go automatically to the next cell. If you do, you'll get this message twice.
End
Public Sub tv1_KeyPress()
Select Case Key.Code
Case Key.BackSpace, Key.Del 'remove record
Dim RecID As Integer = tv1[tv1.Row, 0].Text
SQL = "DELETE FROM Spending WHERE SpendingID='" & RecID & "'"
Try fdb.Exec(SQL)
If Error Then
Message("Couldn't delete<br><br>" & Error.Text)
Else
tv1.Rows.Remove(tv1.Row)
If tv1.Rows.Count = 0 Then tv1.Rows.Count = 1
Endif
Case Key.Enter, Key.Return
If tvCategories.Rows.Count > 0 Then
tvCategories.SetFocus
tvCategories.Rows[0].Selected = True
Endif
End Select
End
Public Sub tvCategories_KeyPress()
Select Case Key.Code
Case Key.BackSpace, Key.Del 'remove record
Dim RecID As Integer = tvCategories[tvCategories.Row, 0].Text
SQL = "DELETE FROM Categories WHERE CatID='" & RecID & "'"
Try fdb.Exec(SQL)
If Error Then
Message("Couldn't delete<br><br>" & Error.Text)
Else
tvCategories.Rows.Remove(tvCategories.Row)
Endif
Case Key.Enter, Key.Return
EnterOnCategoryLine 'action on pressing Enter
tvCategories.UnSelectAll
End Select
End
Public Sub MenuClearSpending_Click()
fdb.Exec("DELETE FROM Spending")
tv1.Rows.count = 1
tv1.Clear
End
Public Sub MenuClearCategories_Click()
fdb.Exec("DELETE FROM Categories")
tvCategories.Rows.count = 1
tvCategories.Clear
End
Public Sub CategoryNameFromID(ID As Integer) As String
Dim res As Result = fdb.Exec("SELECT Category FROM Categories WHERE CatID=" & ID)
If Not res.Available Then Return "?"
If IsNull(res!Category) Then Return "-"
Return res!Category
End
Public Sub EnterOnCategoryLine() 'apply this category to the selected Spending line
If tv1.row < 0 Then Return
If IsNull(tv1[tv1.row, 0].text) Then
Message("Please save this spending record first by entering some other item of data; there's no record ID yet.")
Return
Endif
tv1[tv1.row, 3].text = tvCategories[tvCategories.row, 3].Text
Dim CategoryID As String = tvCategories[tvCategories.row, 0].Text
Dim SpendingID As String = tv1[tv1.row, 0].text
tv1[tv1.row, 5].text = CategoryID
SQL = "UPDATE Spending SET Category='" & CategoryID & "' WHERE SpendingID='" & SpendingID & "'"
Try fdb.Exec(SQL)
If Error Then
Message("Couldn't save the category<br><br>" & SQL & "<br><br>" & Error.text)
Return
Endif
Calculate
For i As Integer = tv1.row To tv1.Rows.Max
If IsNull(tv1[i, 3].text) Then
tv1.Rows[i].Selected = True 'select the next Spending row that needs a category
tvCategories.SetFocus
Return
Endif
Next
tv1.SetFocus
End
Public Sub Calculate()
Dim i, j, CategoryID As Integer
Dim t, GrandTotal As Float
Dim res As Result
Dim s As String
For i = 0 To tvCategories.Rows.Max 'every category
If IsNull(tvCategories[i, 0].Text) Then Continue
CategoryID = tvCategories[i, 0].Text
Try Res = fdb.Exec("SELECT Total(Amount) AS TotalAmount FROM Spending WHERE Category=" & CategoryID)
If Error Then
Message("Couldn't total<br><br>" & Error.Text)
Continue
Endif
While res.Available
t = res!TotalAmount
GrandTotal += t
If t = 0 Then tvCategories[i, 1].Text = "" Else tvCategories[i, 1].Text = Format(t, "##0.00")
res.MoveNext
Wend
Next
If GrandTotal = 0 Then Return
For i = 0 To tvCategories.Rows.Max
s = tvCategories[i, 1].Text
If Not IsNull(s) And If Val(s) > 0 Then tvCategories[i, 2].Text = Format(100 * Val(s) / GrandTotal, "##0.##") Else tvCategories[i, 2].Text = ""
Next
tbDone.Text = Format(GrandTotal, "##0.00")
labSpendingTotal.Text = tbDone.Text
labCategoriesTotal.Text = SumTheCategories()
If Not IsNull(tbTarget.text) Then
tbToDo.Text = Format(Val(tbTarget.Text) - GrandTotal, "##0.00")
tbDistribute.Text = tbToDo.Text
Endif
End
Public Sub SaveCategoriesTable()
For i As Integer = 0 To tvCategories.Rows.Max
SaveCategoryLine(i)
Next
End
Public Sub SaveCategoryLine(i As Integer) 'i is the line number
Dim RecID As Integer
Dim t, pct As Float
Dim s, CategoryName As String
Dim res As Result
RecID = Val(tvCategories[i, 0].Text)
CategoryName = tvCategories[i, 3].Text
t = If(IsNull(tvCategories[i, 1].Text), 0, Val(tvCategories[i, 1].Text))
s = tvCategories[i, 2].Text
pct = If(IsNull(s), 0, Val(s))
If IsNull(RecID) Then 'new record needed
res = fdb.Exec("SELECT Max(CatID) AS MaxCatID FROM Categories")
RecID = res!MaxCatID + 1
SQL = "INSERT INTO Categories(CatID,Category) VALUES(" & RecID & "," & CategoryName & ")"
fdb.Exec(SQL)
If Error Then
Message("Couldn't insert a new record<br><br>" & SQL & "<br><br>" & Error.text)
Return
Endif
Else
SQL = "UPDATE Categories SET Category='" & CategoryName & "' WHERE CatID=" & RecID
Try fdb.Exec(SQL)
'before checking Error, don't forget to use TRY. Otherwise Error will be set and you'll seem to have an error when you don't
If Error Then
Message("Couldn't update a record<br><br>" & SQL & "<br><br>" & Error.text)
Return
Endif
Endif
End
Public Sub SumTheCategories() As String
Dim t As Float
Dim s As String
For i As Integer = 0 To tvCategories.Rows.Max
s = tvCategories[i, 1].Text
If Not IsNull(s) Then t += Val(s)
Next
Return Format(t, "##0.00")
End
Public Sub SumTheSpending() As String
Dim t As Float
Dim s As String
For i As Integer = 0 To tv1.Rows.Max
s = tv1[i, 2].Text
If Not IsNull(s) Then t += Val(s)
Next
Return Format(t, "##0.00")
End
Public Sub MenuCalculate_Click()
Calculate
End
Public Sub tbTarget_LostFocus()
If Not IsNull(tbTarget.text) Then tbTarget.Text = Format(Val(tbTarget.Text), "##0.00") Else tbTarget.Text = ""
Calculate
End
Public Sub tbTarget_KeyPress()
If Key.Code = Key.Enter Or Key.Code = Key.Return Then FMain.SetFocus
End
Public Sub bDistribute_Click()
Dim t, pct, y, z As Float
If IsNull(tbDistribute.Text) Then Return
Dim x As Float = Val(tbDistribute.Text)
For i As Integer = 0 To tvCategories.Rows.Max
If IsNull(tvCategories[i, 1].Text) Then Continue
If IsNull(tvCategories[i, 2].Text) Then Continue
t = Val(tvCategories[i, 1].Text)
pct = Val(tvCategories[i, 2].Text)
y = t + pct / 100 * x
z += y 'running total
If y = 0 Then tvCategories[i, 1].Text = "" Else tvCategories[i, 1].Text = Format(y, "##0.00")
SaveCategoryLine(i)
Next
labCategoriesTotal.text = Format(z, "##0.00")
FMain.SetFocus
End
Public Sub tbDistribute_LostFocus() 'when leaving, fix the appearance
If Not IsNull(tbDistribute.text) Then tbDistribute.Text = Format(Val(tbDistribute.Text), "##0.00") Else tbDistribute.Text = ""
End
Public Sub tbDistribute_KeyPress() 'enter leaves the textbox
If Key.Code = Key.Enter Or Key.Code = Key.Return Then FMain.SetFocus
End
Public Sub MenuDefaultCategories_Click()
Try fdb.Exec("DELETE FROM Categories") 'it might be already cleared
tvCategories.Rows.Count = 9
tvCategories.Clear
Dim s As String
For i As Integer = 0 To 8
s = Choose(i + 1, "Provisions", "Travel", "Medical", "Donations", "Papers etc", "Clothes", "Personal", "Phone", "Repairs")
tvCategories[i, 3].text = s
tvCategories[i, 0].text = i + 1
SQL = "INSERT INTO Categories(CatID,Category) VALUES(" & Str(i + 1) & ",'" & s & "')"
Try fdb.Exec(SQL)
If Error Then Message("Couldn't insert a new record in the categories table.<br><br>" & SQL & "<br><br>" & Error.Text)
Next
labCategoriesTotal.text = ""
End
Public Sub MenuRound_Click()
Dim s As String
Dim x, t As Float
For i As Integer = 0 To tvCategories.Rows.Max
s = tvCategories[i, 1].Text
If IsNull(s) Then
tvCategories[i, 1].Text = ""
Else
x = Round(Val(s))
t = t + x
tvCategories[i, 1].Text = x
Endif
Next
labCategoriesTotal.Text = Format(t, "##0.00")
For i As Integer = 0 To tvCategories.Rows.Max
s = tvCategories[i, 2].Text
If Not IsNull(s) Then tvCategories[i, 2].Text = Round(Val(s))
Next
End
Public Sub MenuOpen_Click()
OpenDatabase(Null, Null)
End
Public Sub MenuNewDatabase_Click()
NewDatabase
End
Public Sub MenuNewSpending_Click()
NewSpending
End
Public Sub MenuNewCategory_Click()
NewCategory
End
Public Sub MenuQuit_Click()
Quit
End
Public Sub MenuCopy_Click()
Dim s, z As String
Dim i, j As Integer
For i = 0 To tv1.Rows.Max
s = tv1[i, 1].Text
For j = 2 To 4
s &= gb.Tab & tv1[i, j].Text
Next
z &= If(IsNull(z), "", gb.NewLine) & s
Next
z &= gb.NewLine
For i = 0 To tvCategories.Rows.Max
s = tvCategories[i, 1].Text
For j = 2 To 3
s &= gb.Tab & tvCategories[i, j].Text
Next
z &= If(IsNull(z), "", gb.NewLine) & s
Next
z &= gb.NewLine & gb.NewLine & "Total Withdrawn: " & tbTarget.Text & gb.tab & " = Total Accounted For: " & tbDone.Text & gb.tab & " + Cash on hand: " & tbToDo.Text
Clipboard.Copy(z)
End
Public Sub MenuShowHelp_Click()
Help.ShowModal
End
Public Sub MenuClearCategory_Click()
Dim RecID As Integer = tv1[tv1.row, 0].Text
fdb.Exec("UPDATE Spending Set Category=' ' WHERE SpendingID=" & RecID)
tv1[tv1.row, 3].Text = "" 'Cat text
tv1[tv1.row, 5].Text = "" 'Cat ID
Calculate
End
Public Sub MenuUnselectAll_Click()
tv1.Rows.UnSelectAll
tvCategories.Rows.UnSelectAll
End
SQL 語句
[edit | edit source]其中一些語句按原樣使用。另一些語句是由多個部分組成的字串。您可能會看到SQL = …。語句的一部分是 SQL，欄位名稱可能會在正確的位置新增，並存儲在變數中，例如。或者記錄 ID 可能儲存在名為 RecID 的變數中。在傳送到 SQLite 的字串中使用單引號。在 Gambas 中組裝語句時使用雙引號。
| SELECT * FROM Spending | 從支出表中選擇所有內容。 |
| SELECT * FROM Categories | 從類別表中選擇所有內容。 |
| SELECT MAX(CatID) as 'TheMax' FROM Categories | 從類別表中獲取最大的 CatID，並將其命名為“TheMax”。 |
| INSERT INTO Categories(CatID,Category) VALUES(123,'Entertainment') | 在類別表中建立一個新記錄。 將123放入CatID欄位中，將Entertainment放入Category欄位中。 |
| UPDATE Categories SET Category = 'Entertainment' WHERE CatID='123' | 必須更新類別表。 在CatID等於123的記錄中，將Entertainment放入Category欄位中。 |
| SELECT MAX(SpendingID) as 'TheMax' FROM Spending | 在支出表中找到最大的SpendingID，並將其命名為“TheMax”。 |
| INSERT INTO Spending(SpendingID,TransDate,Amount,Category,Comment) VALUES('123',' ',' ',' ',' ') | 在支出表中建立一個新記錄。 支出ID = 123 TransDate = 空白 金額 = 空白 類別 = 空白 註釋 = 空白 |
| UPDATE Spending SET TransDate = '4-11-2019' WHERE SpendingID='123' | 將“4-11-2019”放入支出表中SpendingID為 123 的記錄的TransDate欄位中。 |
| DELETE FROM Spending WHERE SpendingID='123' | 刪除支出表中記錄 ID 為 123 的記錄。 |
| DELETE FROM Categories WHERE CatID='123' | 刪除類別表中記錄 ID 為 123 的記錄。 |
| DELETE FROM Spending | 刪除支出表中的所有記錄。所有資料都會消失，永遠不會再出現。 |
| DELETE FROM Categories | 刪除類別表中的所有記錄。所有類別記錄都會消失，永遠不會再出現。 |
| SELECT Category FROM Categories WHERE CatID=123 | 給我CatID記錄號為 123 的類別的名稱。 |
| UPDATE Spending SET Category='4' WHERE SpendingID='123' | 將支出表中記錄 123 的Category欄位設定為 4。此支出專案歸入第四個類別，無論它是什麼。要查詢第四個類別是什麼，請查詢類別表，並找到CatID=4 的記錄。 |
| SELECT Total(Amount) AS TotalAmount FROM Spending WHERE Category='4' | 獲取支出表中Category欄位為 4 的所有記錄的Amount欄位中所有數字的總和。簡單地說，就是將類別 4 中的所有支出金額加起來。將結果命名為“TotalAmount”。 |
| SELECT Max(CatID) AS MaxCatID FROM Categories | 從類別表中獲取最大的 CatID。將其命名為MaxCatID。 |
| SQL = "INSERT INTO Categories(CatID,Category) VALUES(4,Travel)" | 建立一個新的類別記錄。將CatID欄位設定為 4，將Category欄位設定為“Travel”。 |
| UPDATE Categories SET Category='Travel' WHERE CatID=4 | 更新Categories表中記錄 ID 為 4 的記錄。將“Travel”放入Category欄位中。 |
| UPDATE Spending Set Category=' ' WHERE SpendingID=123 | 將空白放入Spending表中記錄 123 的Category欄位中。 |
這些語句可以是SELECT、INSERT、DELETE或UPDATE。
這些模式是
SELECT fields FROM table
SELECT fields FROM table WHERE field = something
SELECT * FROM table
SELECT Total(field) AS nameForIt FROM table
SELECT Max(field) AS nameForIt FROM table
INSERT INTO table(fields) VALUES(values)
DELETE FROM table
DELETE FROM table WHERE field = something
UPDATE table SET field = something WHERE keyfield = something
這些不是唯一的 SQL 語句：還有很多。它們足以讓你對 SQL 有一個基本的瞭解。SQLite 的線上幫助可以在這裡找到
http://www.sqlitetutorial.net/
關於使用 UPDATE 語句的最重要的一點
| 更新記錄時要小心。
如果你省略 WHERE 子句，所有記錄都將被更新！ |
|---|
例如，不要寫這個：UPDATE Spending SET Amount=12.50。這會將 12.50 放入所有記錄的Amount欄位中。所有金額都將變成 12.50。你應該說UPDATE Spending SET Amount=12.50 WHERE SpendingID=42。