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:

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.

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:

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:

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:

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 = 500

The Tray Icon (ti1) properties that need to be set are:

Tooltip = Click to save clipboard text Visible = True

Be 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 2019

Programming Gambas from Zip
← Printing	Tray Icon Notebook	Afterword →