XDC Video Sale

The Xojo Developer Conference (XDC) is a fantastic event.  I highly recommend going to one.  The 2019 XDC event this year is May 1st through the 3rd in Miami, Florida.  I look forward to XDC as you will not find a greater density of Xojo developers on the planet!  Everyone there is all-in on Xojo and you can easily talk Xojo non-stop for three full days (or longer).  More details on XDC at https://www.xojo.com/xdc/

At XDC 2015 they started recording the sessions and attendees get these recordings as part of their conference admission.  This was a great addition because it made choosing between sessions a lot easier because you know you could watch it later.  Plus you could rewatch sessions whenever you wanted.

The XDC videos are also for sale after the conference.  They were not inexpensive but certainly cheaper than going to the event.  Xojo recently announced that the XDC videos from 2018 are now available for $99.  The 2016 videos are available for $75.  These are great prices for 64 videos that cover everything from the Keynote by Geoff Perlman, to database topics, to Windows and Linux and iOS and everything in between.  I haven’t totaled up the exact hours but if my rough calculations are correct the two years of videos adds up to over 1900 minutes of video.  I guarantee there’s a topic you’re interested in that is in this list.

I’m so looking forward to XDC 2019.  Not only to escape a fairly brutal winter in the midwest but to reconnect with my friends and talk Xojo non-stop for days on end.  But also to go to sessions that haven’t been done before.  Many of the speakers had done their favorite topics several times and chose different topics (I did this intentionally to get outside my box).  You can find the season list at https://www.xojo.com/xdc/sessions/

As I usually do I’ll do blog posts on the more interesting topics.  I expect to hear more about Web 2.0, Interops, Android, Xojo Plugins and much more.

Hope to you see you at XDC.  If we’ve never met please stop me and introduce yourself and tell me how you ‘know’ me.  Some read the blog and some have bought our Xojo developer products.  I love to hear about your experiences and how I can serve you better.

Updating Records with Transactions

In my last blog post I talked about inserting records into a SQLite database and found that using transactions makes them incredibly fast.  Does the same hold true for updating an existing record?  We’ll find out.

In this set of tests I inserted 100,000 records and then randomly selected 10,000 records to update with our same random name, age, and date values.  In theory this means I have records distributed all over the range of the database.

In the first test we use a simple RecordSet which means we have to query for it first.  Then call its Edit method, change the values and call update.  It’s a two step process.

For i As Integer = 0 To ari.Ubound
   Dim rs As recordset
   rs = db.SQLSelect("Select * from t_table where table_id = " + Str(ari(i)))
   If db.error Then
      If db.Error Then
         MsgBox "DB Error: " + db.ErrorMessage
         Return
      End
   End
   rs.edit
   rs.Field("Name").stringvalue = GetName
   rs.Field("Age").integervalue = GetAge
   rs.Field("TheDate").DateValue = New date
   rs.Update
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

Unsurprisingly, this method took about 49 seconds for 10,000 records.

In test two I switched to using a PreparedStatement to update the record.

Dim sql As String = "Update t_table Set Name = ?, Age=?, TheDate =? WHERE table_id = ?"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INT64)
For i As Integer = 0 To ari.Ubound
   ps.Bind(0, GetName)
   ps.bind(1, GetAge)
   ps.Bind(2, New Date)
   ps.bind(3, ari(i) )
   ps.SQLExecute
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

When run for 10,000 records this takes about 47 seconds so there is a little bit of time savings by not querying the database for the data to begin with.  It’s also possible that the PreparedStatements aren’t nearly as efficient as one would hope.

I then repeated the same tests within a Transaction.  In both cases updating records effectively took no time at all!  If I increase the number of Updates to 10,000 both the RecordSet update and the PreparedStatement takes about 8 seconds.  

I’m surprised at these results because you would think that doing the query required for the RecordSet would take more time but it’s no different than using the PreparedStatement SQL Update.  Why is that?  I have a couple of theories.  First, the table isn’t very complex with only 4 columns and I’m only ever querying and updating based on the primary key.  I’m sure a more complex query would slow things down.  We also have no indexes on any the columns which can cause the database to do more work behind the scenes.

Regardless, we learned a few things today.  First, database transactions are critical to large scale database manipulation in a timely manner.  Second, it doesn’t matter if you use RecordSet Edit/Update or if you use PreparedStatements as either one is fast enough.

What sorts of questions do you have about databases that I can test for you?

Database Transactions in Xojo

Every now and then someone on the Xojo forum wonders why inserting data into an SQLite database is so slow.  Databases are designed to hold billions of records and do it fast, right?  So why is their application so slow?  The answer is they are relying upon the built-in transaction in Xojo.

By default SQLite databases do an automatic transaction for you.  This means that as soon as you attempt to insert, update, or delete data the work to write that change to disk happens as soon as possible.

For example, let’s take the following bit of code to insert data into a table:

For i As Integer = 1 To kMax
   Dim dbr As New DatabaseRecord
   dbr.Column("Name") = GetName
   dbr.IntegerColumn("Age") = GetAge
   dbr.DateColumn("TheDate") = New date
   db.InsertRecord "t_table", dbr
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

GetName gets a random name from an array that has 26 names in it and adds a random integer between 1000 and 10000.  GetAge returns a random integer between 1 and 74. 

This is pretty simple insert and if you run this 10,000 times it takes roughly 49 seconds saving to a desktop SQLite file on my 5k iMac.  And in so doing the application is locked up for that entire time because I’m in the tight loop.  This is simply unacceptable.

I’m sure someone is screaming why are you using the DatabaseRecord!  It’s slow!  It’s inefficient!  You should be using PreparedStatement’s!  Okay, so using pretty much the same logic:

Dim sql As String = "Insert into t_table (Name, Age, TheDate) Values(?, ?, ?);"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
For i As Integer = 1 To kMax
   ps.Bind(0, GetName)
   ps.bind(1, GetAge)
   ps.Bind(2, New Date)
   ps.SQLExecute
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage
      Return
   End
Next

This takes about 48 seconds.  No big time savings there.  Obviously that’s not the improvement we need.

What we need to do is put these inserts in a database transaction using the following bit of code before the loop starts:

db.SQLExecute("BEGIN TRANSACTION")
If db.Error Then
   MsgBox "DB Error: " + db.ErrorMessage
   Return
End

And then at the end use:

db.Commit

Using the DatabaseRecord method takes a whopping 1 second with 10,000 records.  Using the prepared statement is so fast that my measured elapsed time in seconds is effectively 0.  

If I up the number of records inserted to 1,000,000 I get an interesting result.  The DatabaseRecord method takes 27 seconds where the PreparedStatement method takes 47 seconds.  And if I declare the PreparedStatement inside the loop it now takes 55 seconds.

What have we learned in this blog post?  First, using a database transaction is considerably faster than using the default transaction behavior.  Second, using DatabaseRecord is pretty fast and depending upon the number of record inserted it might be considerably faster.  Honestly, I didn’t expect this.  

In the next blog post I’ll look at Record updates and what are the best methods.

Formatted Text 3.2

Lenexa, KS (February 12th 2019) — BKeeney Software Inc. releases a major update to Formatted Text Control. Developers can now implement tab leaders, change character spacing, implement text drop shadows, and use standard keyboard handling in their applications that require a robust word processing control. This version contains dozens of new, changed, and updated items.

Formatted Text Control (FTC) is a set of classes that offer developers word processing capabilities for their Mac and Windows desktop applications. FTC has several viewing modes that gives users a choice of Page View, Edit View, or Single Line view. FTC supports inline graphics, hyperlinks, and text formatting that the built-in Xojo TextArea control just can’t do. Users can import and save text, RTF, and XML format documents.

FTC version 3.2.0 is a free update to all 3.x users and is recommended. Existing users should be notified via FileShare but can contact BKeeney Software at support@bkeeney.com if they have any questions.

FTC is sold as unencrypted source code and costs $150 USD. More information and demo projects are available at https://www.bkeeney.com/formatted-text-control/ .

Complete change list:
New Items:

  • Contains code from the ImagePlay Effects Library – http://imageplay.sourceforge.net 
  • Tab Leaders implemented
  • Issue #3832: Implement Control + Up/Down Arrow Key Handling
  • Issue #3833: Implement paragraph moving via keyboard handling
  • Issue #3700: Drop Shadow available on Windows and Linux
  • Issue #3795: The FTDocument properties characterStyles and paragraphStyles need to be accessible to subclasses.

Added CharacterStyles and ParagraphStyles getter/setters so developers can create their own style editor.
* Character Spacing implemented (requires Xojo >= 2015.4)

Bug Fixes:

  • Issue #3650: CustomObjects Demo: Added FTFile.Clone method so it works with FTIterator propertly.
  • Issue #3653: macOS: “getDoubleClickTime” – Cocoa replacement for old CarbonLib call
  • Issue #3699: Add Win64 Declares
  • Issue #3654: Hi-DPI Mode: FTPicture – Handles drawn incorrect and matches wrong.
  • Issue #3702: DragRect is half height in HiDPI
  • Fixed an issue with FTParagraph clones that have a different ID after going through the FTIterator
  • Issue #2007/3605: Candidate Window Shows up in Wrong Location
  • Issue #3646: FTParagraph.getXML saves wrong TabStop Properties
  • Issue #3709: FTPicture ignores Nudge property
  • Issue #3748: FTDocument.selectionBold,Italic, shadow, strikethrough, subscript, superscript, underline now works properly
  • Issue #3752: FTRBScript: Fix for printing line numbers
  • Issue #3763: RTFReader/FtcRtfReader ignored the left/right margin and first indent of paragraphs.
  • Issue #3764: RTFReader ignored “\sb” SpaceBefore of paragraphs.
  • Win64 Issue: Modified FTCUndoPaste.constructor and FTCUndoManager.savePaste to allow pasting (Xojo Compiler Issue. Feedback 53967)
  • Issue #3750: Suggestion & comparison of the Office applications for the display optimization of the paragraph background color
  • Issue #3775: Implement Corner Color Property in Formatted Text (Switch between Light/DarkMode – Xojo Version >= 2018.3)
  • Issue #3777: Implement Page Shadow Property in Formatted Text (Switch between Light/DarkMode – Xojo Version >= 2018.3)
  • Issue #3755: Printing has the side-effect of scrolling the editing window to the top
  • Issue #3794: FTDocument.getSelectedParagraphs returns first paragraph of multi-paragraph doc regardless of insertion point
  • Issue #3796: FTStyleRun.copyStyleData does not copy background color
  • Issue #3820: RTFWriter missed “\par” after the last Paragraph
  • Issue #3774: FormattedText — changeParagraphMargins method name changeParagraphMarigns
  • Issue #3762: FTDocument.selectionFontColor returns sameColor false when it should be true
  • Issue #3842: FTStyleRun.updateWith Style sets textColor black when undefined
  • Issue #3806: FTDocument.addParagraphStyle and addCharacterStyle bypassed by insertXML and cannot be overridden in subclass
  • Issue #3818: Print RB Code Editor: Unwanted positive y-offset of the content compared to its line number
  • Issue #3637: FTCParagraphStyle.backgroundColor won’t set correctly
  • Issue #3910: RTF-Reader ignores local value for first line indent in paragraphs
  • Issue #3914: First Paragraph on Page with Background Color ignores Before Space
  • Issue #3917: Paragraphs Background Color ignores Hanging Indent

Changes:

  • Renamed FormattedText.xDisplayPosition to FormattedText.hScrollValue to better reflect what it is.
  • Renamed FormattedText.lastXDisplayPosition to FormattedText.lastHScrollValue
  • Renamed FormattedText.yDisplayPosition to FormattedText.vScrollValue to better reflect what it is.
  • Renamed FormattedText.lastYDisplayPosition to FormattedText.lastVScrollValue
  • Renamed FormattedText.GetResolution to FormattedText.GetMonitorPixelsPerInch to better reflect what it does.
  • Renamed FTDocument.SetResolution to FTDocument.setMonitorPixelsPerInch
  • Renamed FTDocument.GetResolution to FTDocument.getMonitorPixelsPerInch
  • Renamed FTDocument.Resolution to FTDocument.MonitorPixelsPerInch
  • FormattedText.DISPLAY_ALIGN_CENTER/LEFT/RIGHT replaced with Display_Alignment enum
  • FormattedText.MODE_PAGE/NORMAL/EDIT/Single replaced with Display_Mode enum
  • FTLine.FIRST_LINE/MIDDLE/LAST/BOTH replaced with Line_Type enum
  • FTPicture.Handle constants converted to FTPicture.Handle_Type enum
  • FTParagraph.Alignment constants converted to FTParagraph.Alignment_Type enum
  • Removed Alignment constants from RTFConstants module
  • Changed how hyperlinks are drawn in FTObject.drawHyperLink
  • Changed how the gradient shadow is drawn in FTPage.drawPageViewMode
  • Changed how strikethroughs are drawn in FTObject.drawStrikethrough
  • Changed how underline is drawing in FTObject.drawUnderline
  • Can now read/write shadow properties from/to RTF Documents
  • Added AcceptFileDrop to test window for testing custom objects. Added FTFile into project.
  • Changed how Styles are saved and read in XML format.
  • Issue #3802/1456: Command + (Shift Key) + Arrow Keys doesn’t move insertion point correctly
  • Changed RB Script classes to Xojo Script.
  • Now have TabLeaders (WORK IN PROGRESS)
  • Embedded FTC demo now does text formatting with keyboard shortcuts.
  • Removed unused FTDocument.DocOffset method.
  • Fixed TargetCocoa and TargetWin32 constants with more appropriate ones for 2018 R4.
  • Removed duplicated code line in RTFReader.SetupKnownCommands
  • Updated ParagraphWindow to easy format First Line and Hanging Indents

PARTIAL IMPLEMENATION:
* Issue #3753: PageBreaks won’t load from RTF

BKS Tab Control – Drag Rearrange Tabs!

Lenexa, KS (February 8th 2019) — BKS Tab Control update can now drag rearrange

BKeeney Software releases a minor update for BKS Tab Control enabling users to drag and drop to rearrange tabs. Developers can enable or disable this feature using a switch in the Xojo IDE. A new event, TabOrderChanged, is raised when the user performs a drag-rearrange.

The BKS Tab Control is a set of classes that offer developers a classic “tabs control” for Xojo Desktop applications. The Tab Control can attach to any RectControl or Window, and will maintain a relationship to this parent control if and when it changes size. Tabs can be displayed in one of four directions (North, South, East, West) and individually offer options like a close button, an icon, a background color, and can be disabled.

Other features:
  • Optional CloseBox can be positioned on the left of right
  • If the CloseBox is selected a CancelClose event is fired
  • Optional Icon can be positioned on the left or right
  • Each tab can be independently styled (colors, fonts, text decorations)
  • Each tab can be disabled
  • Tabs that overflow may be accessed with an overflow popup menu
  • Works in HiDPI, non-HiDPI, and dark modes

BKS Tab Control has been tested in macOS, Windows, and several varieties of Linux with Xojo 2018 R4 and back to Xojo 2017 R1. The control may work unchanged in older versions of Xojo.

BKS Tab Control is sold as unencrypted source code and costs $75 USD. Existing customers can download the update with the original link on their sales receipt. More information and a demo project are available at: https://www.bkeeney.com/allproducts/bks-tab-control/

modGtk3 for Xojo

Linux has always been kind of an odd duck when it comes to Xojo.  If you create Linux applications with the IDE running in Linux the default sizes for controls is 26 which is different than MacOS or Windows since their default control height is 22.  See the initial screenshot:  the controls in Linux just don’t look right at the default Mac/Win control height.

The standard answer for many years was to subclass your controls and modify the height of the controls in Linux.  This was okay but it hard to make your UI look good on all three target platforms.

Xojo 2017 R2 changed the drawing system it uses.  The switch to GTK3 made it possible to modify the CSS of your application theoretically making it possible to make all platforms look and behave the same.

Just because you can modify the CSS means it’s a trivial task.  Several long time Xojo community members Jim McKay and Jürg Otter stepped up to the plate and put in the time to figure it out.  The result is at https://bitbucket.org/pidog/modgtk3/src/master/.

In this screenshot you can see that everything looks as you’d expect.

Implementing this in your own project is simple.  Download the BitBucket repository, open the project in Xojo and copy the GTK3 folder into  your own project.  Then in the App.Open event put these three lines in:

modGTK3.initGtkEntryFix
modGTK3.initGtkWidgetHeightFix
modGTK3.InitGlobalGTK3Style

That’s it.  Voila!  Now your app looks better in Linux and there’s no need to subclass your controls.  I realize this isn’t magic but it sure seems like it.