Programming Gambas from Zip/Tray Icon Notebook

Design a Notebook Tray Icon The idea for this program is simple: whatever text you have on the clipboard, click on a handy little tray icon and it will be saved to an SQLite database. Nothing seems to happen, but the text is saved in a new record. There is also the means to search for notes already saved. This is done in a window that appears when you middle-click the tray icon. I would have preferred it to appear on right-clicking the icon, but right-clicking can only make a menu appear. That is what right-clicks do: they show contextual menus. Now, middle-clicking is fine if you have a mouse with a middle wheel or button that can be clicked. For those of us who use a laptop’s trackpad, middle-clicking is simulated by clicking both left and right buttons simultaneously. On KDE there is an option in System Settings that can disable this, but by default it is enabled. Experiment with the main window being hidden when the application starts if you like, or not the skiptaskbar property so the minimised main window does not get listed with the other open applications in the panel, but the effects did not appeal at all so they have been left at their defaults. A Google image search will find a suitable picture for the tray icon: alt=Tray Icon|frameless|66x66px It does not need to be any particular size. It scales itself nicely when the program runs. The window has a large text area to show a note, and a textbox at the top left in which to type search text. There are also three labels that display the date/time when the note was saved, the position of the note among all the notes that have been found with that search text in them, and, for interest, the record ID of the note. alt=Screenshot of Gambas Notebook main form|frameless|650x650px alt=Main form for Gambas Notebook program|frameless|649x649px The tray icon (bottom left corner) can be placed anywhere. It does not appear in the window. It appears in the system tray (usually at the bottom right corner of the screen, in KDE’s default panel). The tray icon comes in its own component, so check Project > Properties to see that it and the database components are included: alt=Components needed for Gambas Notebook program|frameless|644x644px In summary, to use this notebook: Copy any text to the clipboard. Click the tray icon and your text is saved. Click the tray icon with both left and right mouse buttons at the same time to search for a note. Sometimes it is useful to save the text then bring up the window and add some key words that will help you find the note again. I sometimes add the words JOKE or QUOTE or FAMILY HISTORY. That way, by typing “QUOTE” in the search box, all my quotes appear and I can step through them one at a time. Copying all selected notes would be a useful feature to add. The SQL select statement appears in the window caption, for interest. For good measure, a TEXT menu has four entries for adjusting text: alt=Gambas Notebook Text Menu|frameless Type Extra… positions the cursor at the end of the text of the visible note, ready for you to type something extra. Tidy gets rid of multiple spaces, multiple tabs and multiple blank lines, and removes leading and trailing spaces. Sentences joins broken sentences. Text copied from emails often has distinct lines. Double-space separates the paragraphs with a blank line. These text operations work on the whole of the text if there is no selection, or on the selected text if there is a selection. The shortcuts are there because often I find myself doing the last three in quick succession to get the text looking decent. The other menus are: alt=Gambas Notebook Other Menus|frameless|401x401px The database is an SQLite file. You might wonder where the OPEN and NEW menu items are. For simplicity, the program creates a new database when it opens if none exists with the name and in the place it expects to find it. It is called Notebook.sqlite and it is in the Home directory. It is easy enough to change the code to make it in the Documents directory if you wish.File > Backup is a useful menuitem that copies the database file to a Backups folder in the Documents folder, date-stamping the file name to show when it was created and to not interfere with earlier backups. File > Renumber makes the record ID’s sequential, with no gaps. It, and the Vacuum item below it, are unnecessary. Vacuum tidies the internal structure of the database file. It uses SQLite’s inbuilt vacuum command. File > Quit closes the application. File > Hide makes the window invisible. Clicking the close box on the window also only hides the window; it does not close the application. For this trick, the window’s Persistent property is set to true. The window persists, invisibly, when its close box is clicked.When you type in the search textbox an SQL Select statement selects all the notes that contain that text. It is a simple search: it does not search for notes containing any of the words, ordered from best to least matching. It searches for exactly the text that is typed. As you type each letter the search is performed. A public property in the mdb module, Public rs As Result, stores the results of the search. Notes > Show All clears the search and finds all the notes. This happens when the program starts: all notes are selected.Notes > New… lets you type a new note that is saved when you leave the text area. Notes > Delete deletes the note currently displayed from the database. Notes > Clear All clears all notes from the database. There is no undo but there is a chance to bail.Window properties are:Arrangement = Vertical Stacking = Above Width = 800 Height = 500The Tray Icon (ti1) properties that need to be set are:Tooltip = Click to save clipboard text Visible = TrueBe sure to set it to visible or you will not see the tray icon when the program is run, and there will be no elegant way of quitting the program when it is running after you hide the window that shows initially. The Stacking property ensures that the window remains above other windows. If you click in the window belonging to your web browser, for example, the Notebook window does not get covered by the browser window but remains on top, floating above it. This can be useful for utility type programs. The last thing to note before getting to the code is the use of the keyboard’s arrow keys. UP and DOWN take you to the first and last of the found notes respectively. LEFT and RIGHT step you back or forwards through the found notes. “Found notes” means those that are found to have the string of text in them that you typed in the textbox, or, if nothing has been typed, all the notes. A few comments follow the code. Code relating to the database is collected in a module called mdb. mdb Module Code ' Gambas module file Public db1 As New Connection Public rs As Result Public SQLSel As String Public Sub CreateDatabase() db1.Type = "sqlite" db1.host = User.home db1.name = "" 'delete an existing Notebook.sqlite If Exist(User.home & "/Notebook.sqlite") Then Kill User.home & "/Notebook.sqlite" Endif 'create Notebook.sqlite db1.Open db1.Databases.Add("Notebook.sqlite") db1.Close End Public Sub ConnectDatabase() db1.Type = "sqlite" db1.host = User.home db1.name = "Notebook.sqlite" db1.Open SelectAllNotes Debug("Notes file connected:<br>" & db1.Host &/ db1.Name & "<br><br>" & rs.Count & " records") End Public Sub MakeTable() Dim hTable As Table db1.name = "Notebook.sqlite" db1.Open hTable = db1.Tables.Add("Notes") hTable.Fields.Add("KeyID", db.Integer) hTable.Fields.Add("Created", db.Date) hTable.Fields.Add("Note", db.String) hTable.PrimaryKey = ["KeyID"] hTable.Update Message("Notes file created:<br>" & db1.Host &/ db1.Name) End Public Sub SelectAllNotes() rs = db1.Exec("SELECT * FROM Notes") FMain.Caption = "SELECT * FROM Notes" rs.MoveLast End Public Sub Massage(z As String) As String While InStr(z, "''") > 0 'this avoids a build-up of single apostrophes Replace(z, "''", "'") Wend Return Replace(z, "'", "''") End Public Sub AddRecord(s As String, t As Date) As String Dim rs1 As Result Dim NextID As Integer If rs.Max = -1 Then NextID = 1 Else NextID = db1.Exec("SELECT Max(KeyID) AS TheMax FROM Notes")!TheMax + 1 db1.Begin rs1 = db1.Create("Notes") rs1!KeyID = NextID rs1!Created = t 'time rs1!Note = Massage(s) rs1.Update db1.Commit SelectAllNotes Return NextID Catch db1.Rollback Message.Error(Error.Text) End Public Sub UpdateRecord(RecNum As Integer, NewText As String) db1.Exec("UPDATE Notes SET Note='" & Massage(NewText) & "' WHERE KeyID=" & RecNum) Dim pos As Integer = rs.Index 'Refresh the result cursor, so the text in it is updated as well as in the database file. This is tricky. If IsNull(SQLSel) Then rs = db.Exec("SELECT * FROM Notes") Else rs = db.Exec(SQLSel) 'SQLSel is the last search, set by typing in tbSearch rs.MoveTo(pos) 'Ooooh yes! It did it. Catch Message.Error("<b>Update error.</b><br><br>" & Error.Text) End Public Sub MoveRecord(KeyCode As Integer) As Boolean If rs.Count = 0 Then Return False Select Case KeyCode Case Key.Left If rs.Index > 0 Then rs.MovePrevious Else rs.MoveLast Return True Case Key.Right If rs.Index < rs.Max Then rs.MoveNext Else rs.MoveFirst Return True Case Key.Up rs.MoveFirst Return True Case Key.Down rs.MoveLast Return True End Select Return False End Public Sub ClearAll() If Message.Warning("Delete all notes? This cannot be undone.", "Ok", "Cancel") = 1 Then db1.Exec("DELETE FROM Notes") SelectAllNotes Endif End Public Sub DeleteRecord(RecNum As Integer) db1.Exec("DELETE FROM Notes WHERE KeyID='" & RecNum & "'") SelectAllNotes End Public Sub SearchFor(s As String) SQLSel = "SELECT * FROM Notes WHERE Note LIKE '%" & Massage(s) & "%'" If IsNull(s) Then SelectAllNotes SQLSel = "" Else FMain.Caption = SQLSel rs = db1.Exec(SQLSel) Endif End Public Sub Renumber() Dim res As Result = db.Exec("SELECT * FROM Notes ORDER BY KeyID") Dim i As Integer = 1 Dim x As Integer Application.Busy += 1 While res.Available x = res!KeyID db.Exec("UPDATE Notes SET KeyID=" & i & " WHERE KeyID=" & x) i += 1 res.MoveNext Wend SelectAllNotes Application.Busy -= 1 End Public Sub Vacuum() As String Dim fSize1, fSize2 As Float fSize1 = Stat(db1.Host &/ db1.Name).Size / 1000 'kB db1.Exec("Vacuum") fSize2 = Stat(db1.Host &/ db1.Name).Size / 1000 'kB Dim Units As String = "kB" If fSize1 > 1000 Then 'megabyte range fSize1 /= 1000 fSize2 /= 1000 Units = "MB" Endif Return Format(fSize1, "#.0") & Units & " -> " & Format(fSize2, "#.0") & Units & " (" & Format(fSize1 - fSize2, "#.00") & Units & ")" End Public Sub Backup() As String If Not Exist(User.Home &/ "Documents/Backups/") Then Mkdir User.Home &/ "Documents/Backups" Dim fn As String = "Notebook " & Format(Now, "yyyy-mm-dd hh-nn") Dim source As String = db1.Host &/ db1.Name Dim dest As String = User.Home &/ "Documents/Backups/" & fn Try Copy source To dest If Error Then Return "Couldn't save -> " & Error.Text Else Return "Saved -> /Documents/Backups/" & fn End The main form’s code ' Gambas class file Public OriginalText As String Public Sub ti1_Click() Dim TimeAdded As String If Clipboard.Type = Clipboard.Text Then TimeAdded = mdb.AddRecord(Clipboard.Paste("text/plain"), Now()) End Public Sub Form_Open() If Not Exist(User.Home &/ "Notebook.sqlite") Then 'create notebook data file mdb.CreateDatabase mdb.MakeTable mdb.SelectAllNotes Else mdb.ConnectDatabase ShowRecord Endif End Public Sub MenuQuit_Click() mdb.db1.Close ti1.Delete Quit End Public Sub Form_KeyPress() If mdb.MoveRecord(Key.Code) Then ShowRecord Stop Event Endif End Public Sub ShowRecord() If mdb.rs.count = 0 Then ClearFields Return Endif ta1.Text = Replace(mdb.rs!Note, "''", "'") Dim d As Date = mdb.rs!Created labTime.text = Format(d, gb.MediumDate) & " " & Format(d, gb.LongTime) labRecID.text = mdb.rs!KeyID labLocation.text = Str(mdb.rs.Index + 1) & "/" & mdb.rs.Count OriginalText = ta1.Text End Public Sub MenuClear_Click() mdb.ClearAll ClearFields labTime.Text = "No records" End Public Sub MenuCopy_Click() Clipboard.Copy(ta1.Text) End Public Sub MenuDeleteNote_Click() Dim RecNum As Integer = Val(labRecID.Text) mdb.DeleteRecord(RecNum) 'after which all records selected; now to relocate... Dim res As Result = db.Exec("SELECT * FROM Notes WHERE KeyID<" & RecNum) res.MoveLast Dim i As Integer = res.Index mdb.rs.MoveTo(i) ShowRecord End Public Sub ClearFields() ta1.Text = "" labRecID.Text = "" labTime.Text = "" labLocation.Text = "" End Public Sub MenuNewNote_Click() ClearFields ta1.SetFocus End Public Sub ta1_GotFocus() OriginalText = ta1.Text End Public Sub ta1_LostFocus() If ta1.Text = OriginalText Then Return 'no change SaveOrUpdate End Public Sub tbSearch_Change() mdb.SearchFor(tbSearch.Text) ShowRecord Catch Message.Error(Error.Text) End Public Sub ta1_KeyPress() If Key.Code = Key.Esc Then Me.SetFocus 'clear focus from textarea; this triggers a record update End Public Sub tbSearch_KeyPress() If Key.Code = Key.Esc Then Me.SetFocus End Public Sub MenuShowAll_Click() mdb.SelectAllNotes ShowRecord End Public Sub KeepReplacing(InThis As String, LookFor As String, Becomes As String) As String Dim z As String = InThis While InStr(z, LookFor) > 0 z = Replace(z, LookFor, Becomes) Wend Return z End Public Sub SaveOrUpdate() If IsNull(labRecID.Text) Then 'new record If IsNull(ta1.Text) Then Return Dim d As Date = Now() labRecID.Text = mdb.AddRecord(ta1.Text, d) labTime.text = Format(d, gb.MediumDate) & " " & Format(d, gb.LongTime) Else 'update If IsNull(ta1.Text) Then mdb.DeleteRecord(Val(labRecID.Text)) ClearFields 'maybe leave everything empty? Else mdb.UpdateRecord(Val(labRecID.Text), ta1.Text) Endif Endif End Public Sub MenuTidy_Click() OriginalText = ta1.Text If IsNull(ta1.Text) Then Return Dim z As String = If(ta1.Selection.Length = 0, Trim(ta1.Text), Trim(ta1.Selection.Text)) z = KeepReplacing(z, gb.NewLine, "|") z = KeepReplacing(z, gb.Tab & gb.Tab, gb.Tab) z = KeepReplacing(z, " ", " ") z = KeepReplacing(z, "| ", "|") z = KeepReplacing(z, "|" & gb.tab, "|") z = KeepReplacing(z, "||", "|") z = KeepReplacing(z, "|", gb.NewLine) If ta1.Selection.Length = 0 Then ta1.Text = z Else ta1.Selection.Text = z SaveOrUpdate End Public Sub MenuSentences_Click() OriginalText = ta1.Text If IsNull(ta1.Text) Then Return Dim z As String = If(ta1.Selection.Length = 0, Trim(ta1.Text), Trim(ta1.Selection.Text)) z = KeepReplacing(z, gb.NewLine, "~") z = KeepReplacing(z, "~ ", "~") z = KeepReplacing(z, ".~", "|") z = KeepReplacing(z, "~", " ") z = KeepReplacing(z, " ", " ") z = KeepReplacing(z, "|", "." & gb.NewLine) If ta1.Selection.Length = 0 Then ta1.Text = z Else ta1.Selection.Text = z SaveOrUpdate End Public Sub MenuUndo_Click() Dim z As String = ta1.Text ta1.Text = OriginalText OriginalText = z SaveOrUpdate End Public Sub MenuRenumber_Click() mdb.Renumber ShowRecord End Public Sub MenuVacuum_Click() Me.Caption = "File size -> " & mdb.Vacuum() End Public Sub MenuBackup_Click() Me.Caption = mdb.Backup() End Public Sub MenuTypeExtra_Click() ta1.SetFocus ta1.Text &= gb.NewLine & gb.NewLine ta1.Select(ta1.Text.Len) End Public Sub MenuDoubleSpace_Click() OriginalText = ta1.Text If IsNull(ta1.Text) Then Return Dim z As String = If(ta1.Selection.Length = 0, Trim(ta1.Text), Trim(ta1.Selection.Text)) z = KeepReplacing(z, gb.NewLine, "|") z = KeepReplacing(z, "||", "|") z = KeepReplacing(z, "|", gb.NewLine & gb.NewLine) If ta1.Selection.Length = 0 Then ta1.Text = z Else ta1.Selection.Text = z SaveOrUpdate End Public Sub ti1_MiddleClick() Me.Show Me.Activate tbSearch.Text = "" mdb.SelectAllNotes ShowRecord End Public Sub MenuHide_Click() Me.Hide End Two useful functions The Massage(string) function is necessary for handling the saving of text that has single apostrophes in it. SQL statements use single apostrophes to surround strings. A single apostrophe in the string will terminate the string and what follows will be a syntax error as it will be incomprehensible. To include an apostrophe it has to be doubled. For example, to save the string Fred’s house it has to first be converted (“massaged”) to Fred’’s house. The KeepReplacing(InThis, LookFor, ReplaceWithThis) function performs replacements until the LookFor string is no longer present. For example, if you wanted to remove multiple x’s from abcxxxxdef and just have one single x you cannot just use Replace(“abcxxxxdef”, “xx”, “x”), for this would produce abcxxdef. The first double-x becomes a single x, and the second double-x becomes a single x. You still have a double-x. You have to keep replacing until there are no more double-x’s. Last Words That’s all, folks, except for the reference appendices. May I finish where I began, with a word of thanks to Benoît Minisini. This programming environment is a delight to use. With the gratitude of all of us users we sing, glass in hand, “For he’s a jolly good fellow, and so say all of us”. Gerard Buzolic 25 September 2019Printing Afterword