#Region Project Attributes
#MainFormWidth: 400
#MainFormHeight: 500
#End Region
Sub Process_Globals
Private fx As JFX
Private MainForm As Form
Private SQL As SQL
Dim cmbStudentID As ComboBox
Dim lblStudentName As Label
Dim lblBirthday As Label
Dim cmbStudentID As ComboBox
Dim txtGrade As TextField
Dim lstFailedTest As ListView
Dim btnSetGrade As Button
Dim cmbTests As ComboBox
Dim TableView1 As TableView
End Sub
Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
MainForm.BackColor = fx.Colors.White
MainForm.Title = "DBUtils"
MainForm.Show
File.MakeDir(File.DirApp, "data")
SQL.Initialize(File.DirApp, "data/1.db", True)
'Delete old tables and create new ones.
DBUtils.DropTable(SQL, "Students")
DBUtils.DropTable(SQL, "Grades")
DBUtils.DropTable(SQL, "DBVersion")
'Create the students table which holds data about each student.
Dim m As Map
m.Initialize
m.Put("Id", DBUtils.DB_TEXT)
m.Put("First Name", DBUtils.DB_TEXT)
m.Put("Last Name", DBUtils.DB_TEXT)
m.Put("Birthday", DBUtils.DB_INTEGER)
Dim l1 As List
l1.Initialize
l1.AddAll(Array As String("1", "2", "3"))
DBUtils.CreateTable(SQL, "Students", m, "Id")
Dim m As Map
m.Initialize 'clear the map
m.Put("Id", DBUtils.DB_TEXT)
m.Put("Test", DBUtils.DB_TEXT)
m.Put("Grade", DBUtils.DB_INTEGER)
DBUtils.CreateTable(SQL, "Grades", m, "")
FillStudentsTable
FillGradesTable
DBUtils.ExecuteList(SQL, "SELECT Id FROM Students", Null, 0, cmbStudentID.Items)
DBUtils.ExecuteTableView(SQL, "SELECT Id, [First Name], [Last Name], Birthday FROM Students", Null, 0, TableView1)
'convert the birthday ticks value to a date string
DateTime.DateFormat = "yyyy/MM/dd" 'sortable format
For Each row() As Object In TableView1.Items
row(3) = DateTime.Date(row(3))
Next
End Sub
Sub FillStudentsTable
Dim ListOfMaps As List
ListOfMaps.Initialize
Dim id As Int
For i = 1 To 100
Dim m As Map
m.Initialize
id = Rnd(id + 1, id + 10000)
m.Put("Id", NumberFormat2(id, 6, 0, 0, False))
m.Put("First Name", "John")
m.Put("Last Name", "Smith" & i)
m.Put("Birthday", DateTime.Add(DateTime.Now, Rnd(-100, 0), Rnd(-12, 0), Rnd(-30, 0)))
ListOfMaps.Add(m)
Next
DBUtils.InsertMaps(SQL, "Students", ListOfMaps)
End Sub
Sub FillGradesTable
'Building this table is a little bit more complicated.
'We will create 20 tests and for each test we will give each student a grade.
'We need to first get the list of possible student IDs.
Dim Table As List
Table = DBUtils.ExecuteMemoryTable(SQL, "SELECT Id FROM Students", Null, 0)
'Table is a list of arrays. Each array holds a single item.
Dim Cols() As String
Dim ListOfMaps As List
ListOfMaps.Initialize
For test = 1 To 20
For student = 0 To Table.Size - 1
Dim m As Map
m.Initialize
Cols = Table.Get(student)
m.Put("Id", Cols(0))
m.Put("Test", "Test #" & test)
m.Put("Grade", Rnd(0, 101)) 'The upper value is exclusive
ListOfMaps.Add(m)
Next
Next
DBUtils.InsertMaps(SQL, "Grades", ListOfMaps)
End Sub
Sub cmbStudentID_ValueChanged (Value As Object)
Dim m As Map
m = DBUtils.ExecuteMap(SQL, "SELECT Id, [First Name], [Last Name], Birthday FROM students WHERE id = ?", _
Array As String(Value))
If m = Null Then 'Null will return if there is no match
lblStudentName.Text = "N/A"
lblBirthday.Text = ""
Else
lblStudentName.Text = m.Get("first name") & " " & m.Get("last name") 'keys are lower cased!
lblBirthday.Text = DateTime.Date(m.Get("birthday"))
End If
'Get the tests for this specific student (currently it is all tests).
DBUtils.ExecuteList(SQL, "SELECT test FROM Grades WHERE id = ?", _
Array As String(Value), 0, cmbTests.Items)
cmbTests.SelectedIndex = 0
FindFailedTests(Value)
If TableView1.SelectedRowValues (0) <> Value Then
For Each row() As Object In TableView1.Items
If row(0) = Value Then
TableView1.SelectedRowValues = row
TableView1.ScrollTo(TableView1.SelectedRow)
Exit
End If
Next
End If
End Sub
Sub cmbTests_SelectedIndexChanged(Index As Int, Value As Object)
If Index = -1 Then Return
'Show the grade of this test
Dim m As Map
m = DBUtils.ExecuteMap(SQL, "SELECT Grade FROM Grades WHERE id = ? AND test = ?", _
Array As String(cmbStudentID.Value, Value))
If m.IsInitialized = False Then
txtGrade.Text = "N/A"
Else
txtGrade.Text = m.Get("grade")
End If
End Sub
Sub FindFailedTests(StudentId As String)
'Find all tests of this student with grade lower than 55.
'Note that we use SQLite concatenation operator to add 'Grade: ' before each grade.
DBUtils.ExecuteList(SQL, "SELECT test || ', Grade: ' || grade FROM Grades WHERE id = ? AND grade <= 55", _
Array As String(StudentId), 0, lstFailedTest.Items)
End Sub
Sub txtGrade_Action
btnSetGrade_Action
End Sub
Sub btnSetGrade_Action
'check that the value is valid
If IsNumber(txtGrade.Text) = False OR txtGrade.Text > 100 OR txtGrade.Text < 0 Then
'ToastMessageShow("Invalid value. Value should be between 0 to 100.", True)
Return
End If
'set the grade of the record with the correct id and test values.
Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("id", cmbStudentID.Value)
WhereFields.Put("test", cmbTests.Value)
DBUtils.UpdateRecord(SQL, "Grades", "Grade", txtGrade.Text, WhereFields)
'Refresh the failed tests list
FindFailedTests(cmbStudentID.Value)
End Sub
Sub lstFailedTest_ItemClick (Position As Int, Value As Object)
'Value is an array of strings
Dim values() As String
values = Value
Dim testName As String
testName = values(0)
'find the index of this test in spnrTests and set it.
For i = 0 To cmbTests.Items.Size - 1
If testName = cmbTests.Items.Get(i) Then
cmbTests.SelectedIndex = i
Exit
End If
Next
txtGrade.SelectAll
End Sub
Sub TableView1_SelectedRowChanged(Index As Int, Row() As Object)
If Index = -1 Then Return
cmbStudentID.Value = Row(0) 'id is the first column
End Sub
Sub TableView1_WidthChanged (Width As Double)
Dim tv As TableView = Sender
For i = 0 To tv.ColumnsCount - 1
tv.SetColumnWidth(i, Width / tv.ColumnsCount)
Next
End Sub