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?

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!

ARGen 1.6.3

ARGen64BKeeney Software released a new version of ARGen today.  ARGen is an ActiveRecord class generator for Xojo and Real Studio.  It allows you to generate all of the classes required to use ActiveRecord in your projects.

Version 1.6.3 Fixes a CubeSQL bug that kept the Load function from working properly.  You could get around this by using the FindByID shared method but some people prefer the Load method.

This version also modifies the ActiveRecord classes a bit so it uses the prefix/suffix preferences used in ARGen to make sure your classes have the required property to match the field in the table.  For example, if you had a field PlaywrightID and were using a prefix of ‘i’ it will now flag the developer if there isn’t a property named iPlaywriteID.  In previous versions it didn’t really matter what you used.  This is just one more developer aid.

It is recommended that all Windows users update manually since a bug was discovered in earlier versions of the auto updater.

Database Primary Keys

The primary key that you define for your tables is critical to a well performing and well designed database.  The primary key to your table is very important so it’s worth spending some time on it BEFORE you start coding.

Let’s start out by describing what the primary key does.  The primary key is the unique identifier of a record for each table.  Regardless of how many rows the table has in it, the primary key is the pointer to a particular row.  This key will never change during the entire lifespan of the row.  There will never, ever, be two records with the same ID.

The primary key should never change.  This is why using a text field as a primary key that contains human readable/changeable data is such a bad idea.  I’ve worked on databases where they used Name as a primary key.  This is such a bad idea especially in cultures where women change their last name when getting married.  Even exempting female name changes most places in the world allow you to change your name legally.  Since it can change it’s a bad idea for use as a primary key.

Another example that we’ve dealt with in the past is using a Social Security Number (national id).  Another bad idea for a number of reasons.  One, data entry mistakes happen.  Two, government agency mistakes occasionally happen where there are two (or more) people with the same SSN.  Three, it’s not uncommon in certain parts of the country where undocumented workers use the same SSN.  Four, identity fraud is common.  Because this means it’s possible for two people or more people to have the same SSN it’s not a unique identifier.  Avoid using it.  It’s  a bad idea.

We recommend using an auto increment integer primary key.  Let the database do what it does best and let it pick it out for you.  That way there will never be any collisions or duplicates.  It’s what databases do very well all on their own.

I’ve worked on projects where there was no auto-increment primary key and was defined as an integer.  The developer devised a scheme to get the largest record ID and increment it by one when needed.  This was fine when a single user used the app but when multiple people were inserting data at the same time collisions occurred generating errors because primary keys must be unique.  It was ugly to fix and it all would have been avoided if they had used an auto increment primary key to begin with.

Someone asked me the other day if ActiveRecord could use a text primary key.  No, and it won’t ever.  It expects an integer primary key.  If you ARE using a text primary key.  Stop.  Add an auto-increment primary key and then add a Unique Index on the text field.

Real Studio/Xojo has some interesting things happen if you try to open a recordset that’s not returning the primary key in the query.  It won’t be able to update it in some cases.  You’ll get a generic message about not know which record you’re trying to access.  I’ve had some issues, in the past, using many-to-many tables that couldn’t be edited even though they had a unique index on fields.  So I recommend adding an auto-increment primary key event to many-to-many tables.  Then, if you have constraints on your index (like the index needing to be unique) you can add them there.  So far it’s worked for me.

For primary keys we use a standard naming convention:  tablename_ID.  A lot of people use a generic ID field but we’ve found that to be less than ideal when doing table joins.  If you have table A, B, and C all with an ID field joining them together means I have to put an alias in my SQL statement so I can retrieve those primary keys later.  By using the A_ID, B_ID, C_ID I never have to alias the field names in a query.  Plus, it has the added benefit of making the SQL easier to read because the name is explicit and the _ID tells me its a primary key.

Each database has its own internal record id.  SQLite uses rowid and you can actually query against it.  If you haven’t specified a primary key in your table it will use that rowid as the primary key.  When you specify a primary key, the specified primary key and rowID are the same.  However, if you do not specify the primary key the rowID is not guaranteed to stay the same.  If you were relying upon rowid to identify records in other tables you might be putting yourself in through a world of hurt if the user ever does a vacuum on the database.    The moral of the story is don’t rely upon the internal id – always specify your own primary key (that auto increments if you haven’t heard it enough now).

If you get into the habit of using an auto increment integer primary key you’ll avoid many problems later.  You don’t have to use our naming standard for primary keys but doing so will improve the readability of your SQL statements.  Primary keys are too important for anyone/thing but the database to figure them out.

Happy Coding!

Defining Primary Keys For Real Studio Apps

An issue that seems to come up fairly regularly with people new to database applications in Real Studio is how to make unique record identifiers when they insert data.  The simple answer is that you don’t – the database itself already knows how to do this.

SQLite databases (REALSQLDatabase in Real Studio) automatically have a rowid regardless if you define your own primary key or not.  You can access this rowid via sql and, in fact, have to use if you want to edit a recordset (if you don’t have a primary key defined) or you’ll get a database error along the lines of it not knowing which record to edit.  However, as soon as you define the primary key field it, and the rowid, are one in the same and hold the same value.

I’ve seen some developers come up with elaborate schemes to come up with unique primary id’s for the database tables.  This is a bad idea!  That database can (and should) do that for you.  It’s exceptionally dangerous to come up with your own scheme.  Bugs happen, mistakes happen, and the problem only gets worse when you add in multiple user databases where multiple connections can insert data at any time and when you have billions, or trillions, of rows of data.  Let the database do that work for you!  It was designed to do that afterall.

I think part of the problem is the built-in Database Editor in Real Studio is awful.  It doesn’t give you the option of making an auto-increment field.  See screenshot below:

 

 

 

We ditched the built-in editor many years ago for other tools.  For SQLite we use a variety of tools and they ALL do a better job of managing your SQLite database than Real Studio.  For example, take a look at the options that Base http://menial.co.uk/base/ gives us just for the primary key:

 

The same goes for all the other databases that Real Studio supports.  There are tools that are genuinely better in almost every aspect that the Real Studio Database Editor.  Do a little research and you’ll find tons of tools that range from free to hundreds of dollars.  If you want editors that are similar across all databases I’d recommend Navicat http://www.navicat.com.

A lot of our projects use SQLite.  We use straight SQL to create our tables, indexes, and relationships.  Here is an example of defining an integer primary key that auto increments:

Create Table IF NOT EXISTS  T_Account(Account_ID    INTEGER  PRIMARY KEY AUTOINCREMENT, ShortAcctNumber    TEXT NULL …..

We always use the tools to create the database during development and then have the tools generate the initial SQL for us.  It’s simple and fast and I don’t have to remember the details.  I just define the fields and then copy and paste the SQL into the project.  I’m lazy that way.

If you’re looking for example of this process you can take a look at our subscription training videos at http://www.bkeeney.com/RealStudioTraining/realstudiotraining.cgi.  Our Journal Entry project goes from start to finish creating the database from scratch via code and the showing how to add, edit, and delete data from the database.

Depending upon the rowid can be detrimental to the future of your project.  They can be reused whereas a primary key cannot.  Take a look at this blog post from Marco over at SQLABS:  http://www.sqlabs.com/blog/2010/12/sqlite-and-unique-rowid-something-you-really-need-to-know/.

A quick scan of MySQL and PostgreSQL documentation shows that they don’t use rowid so if you have to migrate from SQLite to a database server your rowid code is now invalid and you’ll rewrite your code to make and use a primary key.  So it’s just easier to start defining your primary, integer, auto increment keys now and start using the primary key field instead of rowid.

Let the primary key work for you.  Don’t create your own scheme for numbering your primary key field and for your own sanity ditch the Real Studio Database Editor.  Happy coding!

Getting Started with SQL?

Many Real Studio projects require the use of a database.  In 10 years of Real Studio consulting and many, many projects both internally and for clients, I can tell you the projects that did NOT use a database simply because they are the oddities in my career.  If truth be told, even some that didn’t use a database could have been, and perhaps should have been using a database.

A lot of Real Studio developers avoid them because the term ‘database’ is scary and mysterious and brings up images of having to wrestle with huge problematic installations of MS SQL Server, PostgreSQL and MySQL.  While you might have to use them eventually, we do a lot of development on the lowly and surprisingly powerful SQLite.  It is a lightweight database that offers most of the features of the big database servers.  But for many, SQL, the language behind databases, is mysterious and arcane.  Not so!

At last weeks Real Studio Database Days training in Frankfurt, Simon Larkin of QiSQL gave a very interesting talk about SQL.  He also has a section on his website devoted to learning SQL.  His SQL School takes you through the basics of the terms and then starts introducing you to more and more complex situations.

Equally important is his Database Design section that walks you through the best ways of designing your database.  Learn about primary keys, entities, relationships and database normalization.  All in all the tutorials are very well done and if you’re just starting to use databases this is a good place to start.

Simon likes to use raw SQL, which is fine, but I’m lazy and SQL-challenged at times so I tend to use a number of SQLite utility applications.  Since each seems to have some compelling feature over the others I use the one that best fits the project.  I use Base, SQLiteManager, NaviCat, and the FireFox plugin SQLite Manager.  Of these, SQLiteManager and NaviCat can open encrypted databases and only Navicat being able to remember the encryption key.  Navicat also has a visual Query Builder that newbies to SQL might find attractive.

What SQLite tool are you using and why?

ActiveRecord for Real Studio

We are Real Studio consultants.  It’s what we do and we do a LOT of projects.  If I had to put a percentage on the projects that are database driven I’d have to say that it’s above 95% for the past ten years.

Real Studio doesn’t have database binding like Visual Basic 6 but it’s not a real big deal.  If anything, the lack of binding makes the code more explicit (i.e. easier to read) and you don’t have to go hunting through control properties to find table and field names.  The Real Studio database classes are generic so it doesn’t matter, generally, what database you’re connecting to.  The drawback to the lack of binding and the generic classes is that it does lend itself to creating the same code over and over and over again.

Because of the nature of Real Studio many users tend to put their db code into the form (window) and tie it to controls.  This leads to spaghetti code with the database specific code all over the place and makes changes to your database harder.  Seth has done two presentations at ARBP conferences 2009, 2011 and introduced attendees to ActiveRecord that we’ve used for years now.

Active Record is a very simple, and limited Object Relational Model (ORM) system.  It allows us to create REALbasic classes that the IDE knows about.  It’s not exceptionally strong with the relational data, or large blobs, but it can be programmed to handle it.

In a new project we’re converting an existing Visual Basic 6 project with roughly 25 tables and several tables have over a hundred fields each.  Using conventional means it would mean having a database editor open so I can copy and paste field names all the time.  However, using ActiveRecord we created the classes (we have a utility to do this) and now the IDE knows the table and field names.  This makes coding very fast and they’re is no worrying about spelling errors and there’s no longer any issue of what the data type is because the class knows what it is.  This is nice since the compiler will pick up any many errors that may not usually find until runtime.

The client was ecstatic after the conversion since he figured that would have taken about 20 hours to convert the VB6 code into something useable in RB.  Instead, between our utility and ActiveRecord it took me less than 4 hours.  So now instead of spending all the time getting classes ready, we’re doing the real work of connecting up the UI to a set of data aware classes.

Another feature that was added was to flag the developer if a field is in the database that isn’t in the class.  How many times do you add a field to the database (or a coworker does) and you forget to hook it up.  This doesn’t happen using ActiveRecord.  You can have class properties that aren’t a field, but if you delete a field property that’s been used in the application the compiler will flag you on it and that’s very useful too.

ActiveRecord makes extensive use of Shared Methods so that all of the database code for that table is access from that class and that class only.  It has a number of methods built-in such as getting a list of rows (in array form) and finding a record by the primary key.  It’s easily extensible.

Like I said earlier, it’s not perfect.  It doesn’t handle relational data at all, but it can be modified to do so.  Large blobs can slow it down, but in the few times this has been a big deal we’ve implemented ‘lazy loading’ where we don’t load that particular field until we ask for it.

We have a single tutorial page up for it now at the main website.  We’ll eventually turn this into video tutorials and we’ll demonstrate it in more video’s.  It’s an MIT style license so feel free to use it.  If you have additions and suggestions, please don’t hesitate to contact us.

More information, and downloadable classes can be found at http://www.bkeeney.com/realbasic/activerecord

What Feature Would You Remove From Real Studio?

I ran across a Twitter post today that asked what feature they’d remove from FileMaker Pro.  I don’t use FileMaker (not in many, many years at least), but I thought it was a very good question.

It’s a good question in regards to Real Studio too as it makes you think about what you don’t use.  I’ve asked the question before on what’s the one thing you need above all else in Real Studio.  But removing something is a much harder question.  So it should probably be something that’s not very good, or makes things worse, or something made irrelevant by 3rd party tools.

After thinking about it for just a few seconds I came up with the one thing that I never use in Real Studio:  The Database Editor.  For me, it’s the one thing that is worse than useless since it makes the job of managing your databases harder.  I mean,it’s just not very good, in my opinion.  Based on my experience answering questions in the Real Software forums it’s not an uncommon experience.

In reality, the database editor experience is much like any generic tool:  it just doesn’t have the features that match up well to tools built for the specific database.  If you want a good SQLite tool there are some awesome commercial versions available.  Heck, there is a freeware version that works inside of FireFox that’s better than the DB editor, IMO.  The same goes with MySQL, PostgreSQL, Oracle and any other database that RS supports.

I’m a big fan of Navicat as they have versions of each of the aforementioned databases.  Granted, Navicat has a generic user interface and it’s a Java app (I think) but it’s the only thing that Navicat does (database admin tools).  It’s interface is consistent across all of their versions so it’s no big deal to move from the SQLite version to the PostgreSQL or the MySQL version.

If the Database Editor was removed from Real Studio would anyone really notice?  What would you remove from Real Studio if you had the chance?

REAL Server Discontinued

REAL Software quietly announced on the NUG yesterday that REAL Server is no longer part of its stable of products.  They also said that current licenses for REAL Server will be honored by SQLabs, the once and current owner of the product.

This is not particularly good news for those that use REAL Server.  REAL Server was a product that was heavily hyped for several years and is no longer available for sale and support through REAL Software.

The good news is that the product isn’t going away.  REAL Server (or REAL SQL Server for those that have been around long enough) was purchased from SQLabs years ago and the primary developer, Marco Bambini, became a REAL Software employee and continued development of the product.

Marco and REAL Software parted ways several months ago.  It was not until this week that REAL Software acknowledged his departure and its effect.  It seems that as part of their separation deal, REAL Server will go back to SQLabs and be sold and supported by them.  More info here.

REAL Server, for those that don’t know the history, was originally written by Marco Bambini of SQLabs.  It was original called SQLite Server  and it made SQLite database files networkable and multi-user.

This was pretty exciting at the time because even though SQLite databases were lightweight and easy-to-use they didn’t work very well across network drives, it had none of the mechanisms required to handle multiple users, and it had no foreign key constraints.  From a certain perspective the acquisition and subsequent development of the product made sense from REAL Software’s perspective because it gave a migration path from the single-user SQLite database to an easy-to-use, install and administer database server.

Unfortunately, making SQLite into a database server proved to be difficult.  Until very recently, an SQLite database knew nothing about foreign key constraints (and even now you have to go out of your way to use them).  It also didn’t do any logic to handle concurrent users and all the headaches that go along with it (like record locking and user access control).  The fact the REAL Server did do some of this was a testament to how much work they put into it.  They fit the proverbial square peg into the proverbial round hole.

Unfortunately, REAL Server cost money and it was hard to compete against the MySQL and PostgreSQL database servers of the world which were mostly free.  For a while the license of MySQL was  a huge unknown mess (is it really any better now?) and REAL Server was marketed as a safe alternative to MySQL.

It’s hard to compete against free and well established database servers with hoards of developers contributing to it.  Foreign key constraints and stored procedures and all sorts of other goodies were already in MySQL and PostgreSQL and both continued to evolve with new features while REAL Server stagnated.

At REAL World 2008 Geoff Perlmann showed off a demo of a new version of REAL Software that allowed for plugins, written in c, to become its new pseudo stored procedures.  It was also supposed to show huge improvements in the number of concurrent users, have server side cursors, have client messaging and a host of other new features.

The demo was light on details but it was released later that year.  The last official release of REAL Server was in 2009.  However, many developers have found some of those new features to be buggy, and have stuck with the 2008 version.  Meanwhile, REAL Server has been stuck in perpetual beta since then with no appreciable work.  Now, users are stuck in limbo waiting for SQLabs to come up with a new release version.

Am I surprised by any of these developments?  No.  I always thought (http://www.bkeeneybriefs.com/2009/03/real-studio-the-good-the-bad-the-ugly/, http://www.bkeeneybriefs.com/2008/11/changes-at-real-software-part-deux/) that REAL Server was a product waiting for an audience.  And, as I said earlier, it’s very hard to compete with free, especially when you get into all of the drawbacks.  Heaven help you if you tried to get REAL Server inside of a corporate environment where database servers are specified to the nth degree and require dedicated support personnel.

This news sucks – especially if you had invested a lot of time and effort into REAL Server development in your projects.  The lack of new versions in the past year should have been a good clue, though.  Also, there were very few posts about REAL Server in the REAL Software forums.

I feel that the focus on the hobbyist developer blinds RS, sometimes, to what professional developers will gladly pay for.  Don’t get me wrong, there are a LOT of hobbyist REAL Studio developers and that’s great, but it’s been my experience that the hobbyist developers can’t pay for much and REAL Studio was a cost (even as inexpensive as it was) that most couldn’t afford.

It was a losing battle from the start, really.  It’s too bad that RS had to go through the painful realization that buying and building upon a product sometimes isn’t good enough.  It was too expensive for hobbyist developers and it wasn’t powerful enough for the professional developers.

In a nutshell it never took off and that’s sad.  It was a distraction and a drain on resources during a time when multiple developers were laid off due to the hard economic times.  Cocoa is now running into its third year of development and one has to seriously wonder if the distraction of REAL Server, even if it was just one developer, cost them some serious development time.  Certainly one could argue the money spent on development and marketing of REAL Server could have been better spent on other things.

Ultimately, the message this sends to the community is not a very good one.  From now on, we, as users, will have to weigh the impact of relying upon any tool from the company since it may or may not be supported years from now.  Granted, in this case, REAL Server is finding a new home and there will be support for the foreseeable future but what about the next new thing?  Do we have to wonder about Linux or Web App support five years from now?

What are your thoughts?