Automating Xojo Database Development with ARGen

Database applications are relatively easy with Xojo. The native Database, DatabaseRecord, and Recordset classes make it easy to connect, add, edit, and delete data in your console, desktop, and web applications. What we’ve found over the years isn’t that it’s hard to create these applications but that it’s tedious and also the very nature of Xojo tends to make life more complicated. Let me explain.

The Xojo database classes are very generic. To insert data into the recordset you either use an SQL Insert statement or use the DatabaseRecord class. The SQL statement, by itself if fraught with peril as it’s very easy to mistype the statement and miss it in testing. Even using PreparedStatements (which you should be using whenever possible) you can still screw it up. Using the DatabaseRecord class is more object oriented and without a doubt easier to use but it has one big problem: It doesn’t check datatypes. At design time it will happily let you try to put a date value into an integer field (depends on how you try, of course). It is not until runtime that you may get any sort error which leads to the next issue.

Database errors, at least in the global framework, do not generate exceptions. This means that unless you, the developer, check for the error, you may not get the results you expect. On an Insert statement, either using SQL or the DatabaseRecord, your data may simply never be inserted and you’ll never know it unless you’re checking the Error property on the database connection. The Error must be checked after EVERY SQL action whether that be SQL Select, Insert, or Delete. Even checking after a Prepare statement is wise to do so.

The good news (maybe) is that Xojo for iOS and the iOSSQLiteDatabase class will throw an exception if it experiences a database error. It’s good that you don’t need to check for the error manually, but now you need to put in exception handling around your database code and deal with exceptions accordingly. It’s not hard, but it’s also not trivial code either.

The IDE knows next to nothing about the database and certainly when you code it you have to query the database for Table and Field schema’s. To make it worse, the Xojo database framework has no idea about the concept of Views requiring you to query the database specifically to find and learn more about them. The IDE has no idea about the field datatypes either and the compiler can’t warn you about mismatched datatypes. These types of errors are only caught at runtime which is usually way too late in the process (hopefully not by the client either).

The database frameworks are not very object oriented friendly. If you have a simple Add/Edit dialog you need to know if the record is new, or not, since this changes how you code saving the data. New requires the DatabaseRecord object and an existing record can be updated via the Recordset object (both can use SQL statements but the statements themselves require different statements).

Xojo is easy to use but the ability to stick code in control events tends to make big database applications unwieldy and full of spaghetti code. Changing a field name in a commonly used table, or even worse changing the field datatype, is often at your own peril because you might have SQL statements referencing it in hundreds of places in the code. Forget it one place and you have an error in the waiting.

Finally, there’s the hooking your user interface up to your database code. Many people have Load/Save methods in every window/dialog/container that needs it. Use a SQL statement to get the data, then use the Recordset to load the user interface, and then use a Save method (that knows the difference between new and existing records) to put it back into the database. Again, the IDE compiler can’t help you if you make either a spelling mistake in the SQL statement or Field names so you won’t find these errors out until runtime (which is often too late).

There are options, to all of these problems. Creating your own data classes is a step above the standard Xojo code, in my opinion. You encapsulate a record into a Xojo class and implement your own Load and Save methods. This is a better object and forces some compiler warnings into your code. It also tends to put your database code all in one folder and/or NameSpace. You can use field name constants for field names, and force all db code into one class or module. Unfortunately, if you have hundreds of tables it’s also tedious to do all this.

There are other solutions out there, but ARGen takes some of the tediousness out of the work of creating a Xojo database application and does some other nifty things too like creating a basic User Interface for you.. ARGen is short for ActiveRecord Generator. ActiveRecord is a set of classes that map the database into equivalent Xojo namespaced classes.

Say you have a table named Employee. ARGen would create a class named ‘Employee’ in the ‘Data’ namespace and you would reference it in code as Data.Employee. Each field in the table is then mapped to a property in the class. The FirstName text field would be mapped to a string property named “FirstName”. This property is referenced as Data.Employee.FirstName and since the compiler knows this property is a string it will complain if you try and put an integer into it. Since there’s only one place you would ever define FirstName in your project it becomes really easy to change the name or change the datatype and then have the compiler catch any errors. It also means that AutoComplete in the code editor works meaning you’ll never mistype a table or field name again without the compiler catching it.

When an ActiveRecord project first starts up and connects to the database we call a Register method that calls out which tables/views are added into ActiveRecord. ActiveRecord scans the schema of the table and then attempts to match up every field with a corresponding property on the class. If a field is missing from your class it will generate an error that’s caught only while debugging (a “table x is missing field y” type of message).

Each ActiveRecord instance is a complete record. It has built-in Save and Delete methods. It also has events for Before and After Create, Before and After Update, to let you do things globally for each record. One example we use all the time are CreatedBy, CreatedDate fields in the Before Create event and ModifiedBy and ModifiedDate fields in the Before Update event. One place, ever, to put that data into the database rather than the potential thousands of places it might. There is also a convenient IsModified method to figure out if data has actually changed or not.

The Save method knows if it’s a new record or an existing record and does the right thing behind the scenes for you. Using a transaction, using a PreparedStatement. I think this one feature alone saves us hundreds of hours of coding because it’s a simple Save call. Nothing more be done. It really takes the worry out of it.

There are other coding features that we could get into but it all revolves around making creating database applications in Xojo fast and easy. ARGen creates a bunch of helper functions like IsDuplicate, List, and so on to help make your life easier. ARGen has a free mode which allows you to get the ActiveRecord classes and up to two tables at a time done.

For many years this is all ARGen did and we used it with great success. However, we found ourselves spending a lot of time on the next step – creating windows, dialogs, and containers and hooking up the database classes to the User Interface. Practically every table you run across has a List and Edit form. In version 2 we added the ability create a rudimentary User Interface which saves even more time. You only get this ability with the fully paid version.

When generating a Xojo project ARGen puts in a number of #pragma error statements where the developer needs to look at code and uncomment code and/or fix code. It’s hard to guess and frankly we don’t want your newly generated project to compile without you having to fix some code. Trust me – it’s better this way. There are simply too many variables and ways of doing things. Because of the #pragma error statements I like to bring over classes and UI over from the generated project as I need them rather than all at once. No need to fix hundreds of pieces of code until you’re ready to look at them.

ARGen and ActiveRecord isn’t perfect and it’s not the only solution around. ARGen lets the compiler do some work for you and eliminates some of the very common, but trivial, mistakes. It also lets you save a ton of time when it comes to standard database code and building user interfaces. It isn’t a panacea for development but ARGen can save you a lot of time and effort.

If you’d like to learn more about ARGen and what it can do for you, please download the free/demo version from the product page at http://www.bkeeney.com/allproducts/argen/. There are several videos on the ARGen menu that show more details.

Update:  I did a Xojo webinar a while back that talks about some of this in detail.  It’s a little dated but worth watching:  http://developer.xojo.com/webinar-simplying-db-access-with-bkeeney-activerecord

The Xojo Community is Awesome

Have I told you how much I love the Xojo community?  I’ve been part of it for fifteen years and I’ve met hundreds of Xojo developers at developers conferences and probably exchanged emails with thousands more.  I am amazed at how much this community helps each other and I wish there was a way to promote that as a key feature of the product.  It’s a big deal.  Really!

If you’re just starting out using Xojo know that there are a bunch of people, myself included, that are willing to help out, if we can, on your journey.  Programming is hard.  Well, I don’t think it’s hard because I’ve been doing it for so long, but it is complex at times and that makes it hard.  Just ask your question in the Xojo forums and you’ll almost always get an answer within hours.

Even Xojo pros, such as myself, have need of help.  Xojo covers Mac, Windows, Linux desktop, console, and web apps.  It does iOS apps for iPhone and iPad.  It now does Raspberry Pi for heavens sake!  It works with dozens of different databases.  There is simply no way any one person is going to know everything there is to know about Xojo.  It just can’t happen.  So yes, I go to the forums, all the time, and ask for help.

Just the other day I asked for some help with WooCommerce.  Not Xojo related, really, but certainly related to a project we’re working on for a client.  Within a few hours I had half a dozen developers private message me saying they might be able to help.  Subsequent contact narrowed that list down a bit but the point is that I have probably shaved off several days worth of work simply by asking for advice.

I am biased towards Xojo, naturally, as it’s been my primary development language for fifteen years.  I think I’d be hard pressed to find such a friendly community.  I call many on the forums my friends even though I’ve never physically met them.  The few that I’ve met in person have lived up to their forum reputations and are really friends for life.

So maybe this is my belated Thanksgiving post.  I am thankful that so many years ago I jumped both feet first into the tool.  I asked questions – many of the silly and redundant.  I became more proficient and then made another jump to start blogging about it, making products for other developers, and training the next generation of developers.

So if you are in need of a cross-platform development tool I highly recommend Xojo.  It ain’t perfect but no development tool is.  If you jump in I think you’ll love the community.  I know I do.

What say you fellow Xojo developers?

Introducing ARGen 2.0

ARGen More Powerful Than Ever!

BKeeney Software is pleased to announce the release of version 2 of ARGen, our ActiveRecord generator utility.  The new release includes many enhancements.  Some of the highlights are:

  • Can now create User Interface elements.
  • Create entire projects for Desktop and Web projects with the proper database connections for each type.
  • Choose between standard database error reporting and a more robust version that BKeeney Software provides.
  • Can now create foreign key elements automatically.
  • Ability to create relationships without having to put them into foreign keys in the database.
  • Works with more databases.
  • Added ability to use database views.
  • Completely redesigned application!

Purchase Mac Version
Purchase Windows Version

Note:  Without the paid upgrade, you can still access the free version with nag screen.  You can use the free version with no time limitation.  The free version is limited to two tables at a time and will not create any User Interface elements.

If you are an existing user and did not receive an email containing an upgrade coupon code please contact us at support@bkeeney.com.

If you want to see ARGen 2.0 in action, please visit our new video at http://www.bkeeney.com/allproducts/argen/argen-2-0/

Product Home Page:  http://www.bkeeney.com/allproducts/argen/

XojoTalk 027 – Database Goddess

We at BKeeney Software are blessed in so many ways.  All of our employees bring a unique and interesting mix of talents and experiences.  It’s not just about one person and we often bounce ideas off each other to get the best possible result.

In the latest XojoTalk, Paul interviews our CEO, Carol Keeney, and how she uses Xojo and gets her thoughts on databases.    Carol has a ton of project management experience that makes running BKeeney Software easy since we often have a half dozen projects going at a time.

She has a lot of database experience too.  That has given her the “Database Goddess” nickname.  Again, that experience is so helpful for the programmers because, really, you don’t want your programmers designing the database.  We tend to do things the easy way which might not be the right way.

I thought it was an excellent interview.  You can find the XojoTalk at http://blog.xojo.com/2016/07/26/xojotalk-027-database-goddess/

Views are Your Friend

One of the challenges I see regularly in my work as a database administrator is finding a way to bridge the gap between the well-normalized physical implementation of a database behind an application and providing a way to make the data available in an easy-to-use (and understand) fashion for the end user.  For many clients this means letting them design their own reports.  Database views are my go-to method for making a complex database structure easy to use for end-users and even developers.

Views have a number of advantages.  For one, they are available in all SQL databases, including SQLite.  Views allow you to reduce the complexity of the end user experience by managing and limiting the data presented.  This might mean taking a very complex query joining multiple tables and presenting it in a single table with the end user (or developer) not needing to know the gory details of the SQL behind it.

This last point is important for the developers too.  Having the programmers figure out the complex joins for a query pulling from multiple tables is sometimes challenging and not always a good use of their time.  Having the Xojo application do all that work for a complex query is sometimes painfully slow.  Views not only speed up the development process but make the Xojo application more efficient since the database has already created the view and optimized it internally.

Views are more secure since you can limit the data shown and control who has the rights to view it.  By giving users and developers access to the underlying tables you may be exposing sensitive data.  Views are an easy way of sanitizing data.  We use views as read-only constructs so the user cannot update the data (note:  not all databases treat views as read-only).

Views can be created, modified and deleted via simple SQL statements.  The syntax of the statements (in particular for modifying views) varies a bit from database to database but the details can be easily found.  To create a view, the syntax is:

    CREATE VIEW viewname AS

SELECT (fill in your query here)

We recently used views implementing a large customer records management (CRM) system for an insurance broker.  Their database has around 100 tables containing information on customers, policies, related products, agent and commission data.  The data is normalized and it isn’t always straightforward to get related data.  The client does not have a dedicated IT staff and reporting is the primary responsibility of a part-time administrative employee, “Jane”.

Our goal was to provide Jane with the easiest means possible to write any report that was requested by management.  While Jane has some knowledge of databases and foreign keys, she isn’t technical and doesn’t have any training on the SQL language.

Our answer was to use our desktop reporting tool, BKeeney Shorts, customized to only show her views created solely for reporting.  (We did this via a simple naming standard in the database.)  She is presented with options such as:  Agent Commission, Prospect Contact and Client Policy Details.  If she needs an additional view, it’s easy to add it to the database and not impact the Xojo code behind the application. (It doesn’t require a recompile and redeployment of the software).

The view incorporates the calculations behind the data, so the reports are always consistent in terms of things such as how commission checks are calculated or who is the primary agent for an account.  By using views we ensure that consistent results are shown to the end user even if someone else is assigned to write reports

Views provide a level of abstraction between the database and the end user and we find  them very useful.  For the developers, it means they don’t have to figure out the complex queries and have the Xojo application create inefficient and complex queries.  Views are your friend!

The Good, the Bad, The Ugly of Deploying Xojo Web Apps

It’s been a very busy first half of the year at BKeeney Software.  We’ve just finished up three medium sized web apps.  One was deployed to Windows Server, one deployed to a Linux web server, and the last to Xojo Cloud.  We’ve done a couple of small web apps to a Xojo friendly host too.  I will describe our experiences below.

Windows Server

The first project was a CRM project where we converted a really old MS Access and ACT! system to Xojo.  The UI wasn’t all that complicated but the data conversion was a pain since it was so dirty.  We ended up using a MySQL database server.

Since we didn’t want to go through the hassle of getting an IIS server we made the decision to deploy as a standalone app.  The hardest part of the whole installation was figuring out how to create the service.  We ended up using NSSM – the Non-Sucking Service Manager http://www.nssm.cc to create the service.  After that, installation and updates were a breeze by simply stopping the service, changing the files, and restarting the service.  We are able to VPN into the server and copy the necessary files over.

The server was virtual and running on a VMWare server.  Despite our specifications the server was set up initially with a single core (virtual) processor.  Performance all the way around sucked.  We complained, the client complained, and as soon as they upped it to dual core processors (as we specified) everything went smoothly after that.

Xojo Cloud

The second project was another CRM-type project though a little smaller.  It was using an SQLite database.  We converted the data from a FileMaker database.  The data was much cleaner and more straightforward so it was a relatively easy transition.

The client decided to host on Xojo Cloud so deployment is done from with the Xojo IDE.  The only file we didn’t deploy via the IDE was the database itself which we transferred via SFTP to the appropriate shared document directory on the Xojo Cloud server.

Xojo Cloud, for the most part, works flawlessly.  There have been occasions, however, where it fails to upload properly.  It’s a frustrating experience, to be sure, but Xojo customer service has always been able to fix it.  When it happens on a weekend you’re kind of out of luck.  Here are a couple of suggestions to try and get around the issue:  1)  Restart your server via the Xojo control panel.  2) Change the application ID of your application and try uploading again.

Linux VPS

Our third application has been around a while but we switched over from using SQLite to MySQL after our little db reached about three and half million rows in single table.  It still worked but some of the queries were taking *minutes* to complete.  The decision to move over to MySQL wasn’t a hard one.

You’d think that updating an existing app would be easy.  This one turned out to be anything but easy.  Since this one bites a lot of people deploying Xojo web apps to various Linux distorts it’s worth writing them all down.

  • Until Xojo web apps are 64 bit you’ll have to make sure the 32 bit compatibility libraries are installed.  Each is different so it’s ‘fun’ finding them.  Honestly, check on the Xojo forums to see what people have found.  Hopefully, this becomes a non-issue with Xojo 2015 R3 being scheduled to compile 64 bit apps.
  • Make sure permissions are 775 for all files and directories *including* the overall directory.  The latter one has bitten me more than once.
  • Make sure permissions for the config.cfg file is 664 or 666 (I’ve seen it both ways and I’ve never had a problem with either one).
  • Make sure the owner and group of the files matches that of the system.  If you’re FTP client shows owners/groups and it says something like “mixed” you’ve got a problem.  You’ll need to figure out how to change the owner/group on your own.  This may involved getting SSH access.  It may be easier to work through the CPanel File Manager to upload files because you’re guaranteed, then, to have the right owner/group.
  • Make sure the .htaccess file has been uploaded.  Since it’s a hidden file on Mac OS X it’s easy to miss this one.  If you zip the output directory and then upload the zip it will be there automatically.  If you’re zipping the contents of the directory don’t forget it!
  • Does the server have libicu installed?  Xojo 2015 R2 requires libicu and if you don’t have it your app will just crash with maybe an obscure error in the error log like “Can’t find missing file.”  I had this happen to me this week and when I reverted to 2014 R2 the app started up right away.  If you suspect you’re missing a library and have SSH access, try starting the app via the command line.  It will tell you what the missing library is.  You don’t always have SSH access so that’s kind of a drag.

Not really an issue with web app deployment, per se, but it’s an issue we deal with quite a bit.  MySQL on Mac OS X doesn’t care what the case is of a table or view.  tTable (mixed case) is the same as ttable (all lowercase).  On Linux and Windows this does not appear to be the case.  I know there’s a system variable that can turn this on or off but for this project the client had existing databases on the server and I really didn’t want to muck anything up on them.  Many queries that were running great on our local (Mac) system were not on the server.  Switching everything to lowercase isn’t a big deal (especially with ActiveRecord) but some db apps this will be a nightmare.  So don’t forget to test!

1701 Software Inc. http://www.1701software.com

We have deployed to 1701 Software several times in the past six months for ourselves and for various clients.  We’ve found their servers to be Xojo friendly from the get go (not so with most hosts) and their service is excellent.  They also offer a number of database options that Xojo Cloud does not like CubeSQL and Valentina in addition to MySQL and PostgreSQL.  Frankly, the fact that they are Xojo developers too makes them the top of my list of non Xojo hosting services.  Their prices are pretty good too for those that are price conscious.

That is our experience with deploying Xojo web apps in the past six months.  When everything works right it’s incredibly easy.  When it doesn’t – well, you tend to swear like a sailor.  Hopefully you’ll find this guide to be somewhat useful.  Anything I forgot?

Happy Coding!

[Edit:  Updated to not include an implied timeframe for the R3 release.]

Converting FileMaker to Xojo And ActiveRecord

We are currently converting a FileMaker app to a Xojo web app.  We are about  3/4 of the way through the project and it’s been a surprisingly easy conversion.  Our biggest challenge has been normalizing the database since the original FileMaker developer did some things that were less than ideal.

Hal Gumbert over at Camp Software is starting a series of blogposts on their own transition from FileMaker to Xojo.  It is a recommended read.

One of the big things that many developers want coming from FileMaker and MS Access and other tools where the database is tightly integrated into the development tool is data binding.  It makes for a quick way to load/save data to and from the user interface.  We don’t do data binding and I’ll get into that a bit later.

In Hal’s blog post he goes into the various Xojo options and ActiveRecord is one of them.  I thought I’d spend a little time talking about ActiveRecord to fill you in on what it does.

ActiveRecord eliminates many common mistakes that developers have when creating database applications using Xojo.  How many times have you mistyped a table or field name in an SQL query?  We used to do it a lot and ActiveRecord eliminates much of it.  It does this by creating a NameSpace module and creating a class for each table.  The properties in those classes then map to the field in each table.

A register function for ActiveRecord uses Introspection to ensure you have all of the tables and fields from the database mapped in your classes.  If not, an assertion takes place in debug mode which tells the developer if they’re missing a table, field, or if a field is mapped to the wrong datatype.  This is very handy on large projects where you might be adding a bunch of fields to meet changing conditions and this way you definitely will not forget to add them to the ActiveRecord classes.

Creating the classes can be tedious especially with very large databases.  Our ARGen utility will help generate the classes for your by scanning your database and creating the classes for you.  For some this might seem backwards but we tend to design the database first and then code to it and we find that ARGen does 75% of the repetitive work for us by creating the classes and adding some shared methods to each class that help in queries and finding a particular record.

Once created, using ARGen is fairly simple.  To get a list of records in normal Xojo code you would create a query.  ActiveRecord does something similar using a class shared method.  Here is an example of using the List shared method to load a ListBox:



for each oCompany as Data.T_Company in Data.T_Company.List(sCriteria, sSort, iOffset)
   
   lst.AddRow oCompany.sCompanyNameCurrent,  _
   
   oCompany.sStreet1, oCompany.sCity, _
   
   oCompany.sStateCode, _
   
   oCompany.sZipCode, _
   
   oCompany.sCompanyStatusName, _
   
   oCompany.sAgentName, _
   
   oCompany.sParentName
   
   dim iRow as integer = lst.LastIndex
   
   lst.RowTag(iRow) = oCompany
   
next


Data is the NameSpace and we are calling the T_Company List method and we pass in three parameters.  The first is our search criteria, the second is the sort criteria, and the last is the offset which allows us to ‘page’ our data.  It returns an array of Data.T_Company objects and we simply add what we need to the ListBox and stash the object in the RowTag event.  The best part about this is that AutoComplete in the Xojo code editor will show us the table and field names and we don’t have to remember any of it.

Screen Shot 2015-06-24 at 9.49.32 AM

When we wish to edit the record we grab it from the ListBox.RowTag property and pass it in to our editor.



dim oCompany as Data.T_Company =  lst.RowTag(lst.ListIndex)

dim pg as new pgCompanyDetails

pg.Display oCompany


ActiveRecord doesn’t do data binding.  We simply don’t find it useful for a variety of reasons.  First, to do data binding your need to have controls that can handle the data source.  We could create control subclasses but after working with custom data bindings in Xojo on a project or two I was not happy with the endless tweaking we had to do to get them to work properly.  Maybe someone with more patience that I do will be satisfied with it but I never was.  Plus, most developers I’ve met that have done data binding on large projects remain unsatisfied in some form or another or go to extraordinary lengths to make it ‘easy’ (like having every field be string even for things that should clearly be a numeric data type).

Instead we chose a much simpler route.  In our edit forms we have three methods:  Load, Save, Validate.  We feel this offers us some advantages over binding.  First, everything is local to the window.  We don’t have to go find the subclass that handles the data load, save, and validate.  This lets us customize everything for that particular form.  An example Load method:



Private Sub Load()
   
   if moCompany.IsNew then
      
      lblCompanyID.text = "New"
      
      pmStatus.Enabled = false
      
   else
      
      lblCompanyID.text = moCompany.iCompany_ID.ToString
      
      pmStatus.setid moCompany.iCompanyStatus_ID
      
      pmStatus.Enabled = true
      
   end
   
   //Other code here
   
   if moCompany.IsNew then
      
      ccDatePicker1.dtmSelected = new date
      
   else
      
      ccDatePicker1.dtmSelected = moCompany.dtClientSince
      
   end
   
   txtCompany.text  = moCompany.sCompanyNameCurrent
   
   ccLastModified1.SetRecord moCompany
   
End Sub


Right away we can see that what we load depends if the record is new or existing.  Data binding wouldn’t help us there.  Labels and TextFields are the easies to do data binding with but since you’ll need a TextField to do a numbers only field or a date field you now have to create multiple subclasses.  Probably not a big deal but we’ve found it to be a hassle.  Having everything local means we can handle the edge cases with ease rather than having to modify the control subclass that’s doing the binding.

Before we can call our save method we have a Validate method that simply returns true if everything is okay.  If not, it presents a message to the user:



Private Function Validate() As boolean
   
   SetError ""
   
   if txtCompany.text.trim = "" then
      
      seterror "Validation Error.  Company name cannot be blank."
      
      txtCompany.SetFocus
      
      return false
      
   end
   
   if Data.T_Company.IsDuplicate(txtCompany.text.trim, moCompany.ID) then
      
      seterror "Validation Error. That Company name is already in use."
      
      txtCompany.SetFocus
      
      return false
      
   end
   
   return true
   
End Function


Then finally in our Save method we load data from the controls into the object for saving:



Private Sub Save()
   
   moCompany.CompanyStatus pmStatus.RowTag(pmStatus.ListIndex)
   
   moCompany.dtClientSince = ccDatePicker1.dtmSelected
   
   moCompany.sCompanyNameCurrent = txtCompany.text
   
   moCompany.iCompanyEmployeeCount = txtNumberOfEmployees.text.val
   
   moCompany.SICCode ccSic1.SICcode
   
   moCompany.sURL = txtWebSite.text
   
   moCompany.sTaxIDNumber = txtTaxID.text
   
   moCompany.bInactive = chkInactive.Value
   
   moCompany.save
   
End Sub


Note that our save method doesn’t care if it’s a new or existing record.  Behind the scenes ActiveRecord does the appropriate Insert or Update prepared statements.

Every place where we are editing data we have these three Load, Save, Validate methods.  Everyone on our team knows to look for those so it’s very easy for our team to work on projects collaboratively and know pretty much what’s going on.

Could ActiveRecord do data binding?  Sure.  The classes are open source so feel free to modify them to your hearts content but I truly believe it’s more a matter of the controls being the real pain.

ActiveRecord has a number of events that are handy to use.  We track who created and who changed the records using 4 fields on each table CreatedDate, ModifiedDate, CreatedByID, and ModifiedByID.  We add the BeforeCreate and BeforeUpdate events.  For example, the BeforeCreate event looks like this:



Sub BeforeCreate()
   
   dtCreatedDate = new date
   
   if session.oUser <> nil then
      
      iCreatedBy = Session.ouser.iUser_ID
      
   end
   
End Sub


This gets called before we save anything so the class properties get modified before we attempt to save.  In many projects we have an audit trail to know who changed what data so we add the AfterCreate and AfterSave events of Data.T_Company and pass the entire object into the Audit table:



Sub AfterCreate()
   
   dim oAudit as Data.T_Audit = Data.T_Audit.AuditAdd(self)
   
   oAudit.iCompany_ID = self.id
   
   oAudit.Save
   
End Sub


Then it’s up to the Audit class to query the ActiveRecord class to find changed data and put that into its table.  Again, the code to do this is one one spot rather than all over the project.

I could spend hours talking about ActiveRecord as we tend to use on all of our new database projects.  It speeds up development of database applications.  It eliminates many of the common errors.  It tends to force most database code into the NameSpace classes.  And the compiler can warn you if you’re doing bad things with data.

ActiveRecord is not for EVERY project but we’ve found it incredibly useful in our consulting.  If you dread doing a database project because of the tediousness of database coding then perhaps ActiveRecord is for you.

We recently did a webinar with Xojo on ActiveRecord.  You can view it at http://developer.xojo.com/webinar-simplying-db-access-with-bkeeney-activerecord.  ActiveRecord itself is open source.  ARGen is $19.95.  We also use ActiveRecord in one of start to finish training projects at our training site called Link Share.

ActiveRecord home page 

ARGen home page 

Xojo Training Site

Database Field Madness!

Two clients in the last several weeks have shown up with the exact same issue and it’s time to talk about it.  In both cases the clients had a field in a table that could contain multiple sets of data.  This is a really bad idea and if you find yourself doing it…well…stop it!

In the first case their company table has an email field and at some point in their past they decided that some users needed two email addresses.  Instead of creating an additional field for an alternate email address they simply decided to concatenate the data into one string separated by a comma.

To be honest I’m not sure why it sounded better to concatenate the data rather than create a second field but that’s what they did.  This decision was made years ago, of course, by a developer no longer working for the company, but it’s now up to me to ‘fix’ it.

With emails being prolific it might make more sense to have an email specific table that tie to the customer and can be inactivated.  This solves a number of issues.  It lets users have more than one email addresses.  The other thing it does is keep a history of the customer email addresses so if you are trying to verify a user account via the phone it might be a way to verify their identify when all else fails (not that I’d solely use that).

In the second case the customer table has several fields that can grow over time.  One field has notes separated by carriage returns, another field has billing data and another has payment data and each has ‘records’ in that single field separated by carriage returns and the data in each record separated by pipes.

Example:  The notes field for a single customer might be something like this:

6/20/2010 This is a note

6/30/2010 This is another note

7/5/2014 This is another note that could be pages long.

If you find yourself designing your database like this STOP right now and step away from the keyboard!  Databases are really good at having tables with related data.  Your notes, bills, and payments tables would all have a foreign key references back to the customer table.  That way you can have as many of those children tables as needed without affecting the customer table.  Having fields that grow exponentially in a single record is a bad thing.

Another issue that I see a lot is that you, the programmer, should never, ever, generate your own record id’s.  Let the database do that unless you have some really special-use scenario where you can do a better job of it.  To be clear, I’ve never seen this scenario.  Instead, your primary key should be an auto-incrementing integer and is something that you should never be able to modify.  That’s not to say that you can’t create your own ‘human readable’ code but it should never be used as the primary key of your table.

Another thing, take the time to use the native data types for the database.  If it’s an integer use an integer field type.  If it’s a date then use a Date or DateTime field type.  Booleans, if not a native data type for the database you’re using, can be a TinyInt with a length of 1.  Your conversion to and from Xojo will not be an issue and you let the database do a tremendous amount of work for you.  One client had an Amount field set up as string.  To get a total amount they had to load the entire recordset in, loop through it, convert the string to a double and keep a running total.  Instead, they could have done a simple Sum in an SQL statement and let the database do all the work!  Trust me, it’s much faster that way.

Those are my database hot button topics.  My DBA wife (i.e. The Database Goddess) has her own hot button topics and has beaten them out of me convinced me of the error of my ways and I no longer do them (or at least not without a reprimand).

In most cases the clients didn’t know any better and I’m sure at some point in my distant software development past I did some silly things like that too.  Those silly bad habits were beaten out of me after several accounting projects where database speed was essential.

What sorts of database blunders have you seen that now drive you nuts?

Xojo Cloud Database Support

Last week Xojo announced new features for Xojo Cloud.  They now support MySQL and PostgreSQL database servers in addition to SQLite that they have supported since day one.  One of the interesting features with the database support is that db admin tools that support SSL tunnels can connect to the database as if it was running locally.  In my testing it was surprisingly easy to setup and use.

The first thing to do is log into your Xojo Cloud account control panel.  Then simply enable either the MySQL or PostgreSQL database and enable the SSL Tunnel.  In each case you will receive a username and password that you’ll need to copy before moving on to the next step.

Screen Shot 2015-02-26 at 10.28.25 AM

Our MySQL admin tool of choice is NaviCat.  Setting it up was pretty easy to do.  Create a new connection and then navigate to the SSH tab.  Enter your server IP address, the username and password.

Screen Shot 2015-02-26 at 10.32.30 AM

Then navigate to the General tab and enter a Name for this connection (I used Xojo Cloud).  Because you’re using the SSL Tunnel you need to enter ‘localhost’ into the Host field.  Enter your Xojo supplied username and password and then test your connection.

Screen Shot 2015-02-26 at 10.38.00 AM

After that, everything acts just as if the server were local to you.  In this example I created a sample database named ‘bkeeney’ and a table called ’t_temp’.

Screen Shot 2015-02-26 at 10.34.09 AM

Your Xojo web application, then, will connect to it via the localhost parameter along with username and password suppled to you from Xojo.  Because it’s inside the firewall your web app needs to do nothing more.

Setting up a database server in Xojo Cloud really is that simple.  It just works.  From start to finish it only takes a few minutes to get up and running.  It’s a great addition to Xojo Cloud.

Data Paging Control

A lot of Xojo developers don’t give too much thought to how much data they’re loading into a listbox. For many desktop apps a couple of thousand rows is not uncommon and, frankly, not a big deal. Push that to ten thousand rows and things start to get dicey and when you get to a million rows you’re talking some serious wait time for all million rows to get added to a list box.

For web apps it’s even worse. When the server gets the request to load a WebListbox with a million rows it has to build all of the HTML, first, on the server, push it down the internet connection to your browser, and THEN the browser has to reconstruct those million rows of HTML into a display. Any time you deal with strings there is a performance penalty and needless to say a million rows of data is huge hit to performance.

Trying to show the user a million rows is bad on multiple levels. First, your application is slammed with unnecessary string handling and second, the user can’t possibly handle a million rows of data. The listbox scrolling alone would be a nightmare! Just don’t do this!

Web apps have been using paging controls for years to limit the amount of data the user sees. I’ve seen some web sites limit this data to 100 rows and some to even less unless the user specifies more. That way the onus is on the user for the webpage being slow.  And more recently I’ve seen more desktop apps limiting the amount of data too.

Data Page Control

Today we released a new 48 minute training video showing you how to build your own paging control in Xojo desktop and web apps. We build the Paging Control using a Container Control and standard controls and then use it control a listbox. Then it’s a matter of using the SQL keywords LIMIT and OFFSET to control which records are returned. Of course the video comes with a desktop and web project file with source code you can use in your own projects.

The running example of the web app is at http://xojo.bkeeney.com/BKSWebExamples/#datapaging

This video is available to subscribers at http://xojo.bkeeney.com/XojoTraining/xojotraining.cgi?video=338

If you’ve not looked at our training videos you might find some interesting things. I invite you to take a look!