Following on the heels of Bob Keeney’s blog post about making Xojo Should Scream Business, I have some thoughts on an approach that might help make that happen. So strap in and tuck in for my $0.02 worth of advice if it’s even worth that much.
My history is largely from the Visual Foxpro world. I’ve done other technologies and other languages as well, but a *LOT* of my income has been from paid Foxpro and Visual Foxpro consulting and full-time employment. Foxpro is a very data-centric development tool. In fact, Foxpro as a tool is [A] a programming language and [B] a database all on it’s own. The database is limited. I haven’t recommended using the Foxpro database for over 10 years now. There are better databases. MUCH better databases. Foxpro as a language, works very well with these other databases. PostgreSQL, MySQL, M$ SQL Server, Oracle, and others.
Foxpro is already very business centric. The majority of Foxpro development has been in business development efforts as compared to product development. Have there been Foxpro products for sale? Commercial software products for sale? Sure. But the Foxpro mindset has very much been in solving business problems. In fact, when coming to the Xojo development world, it was a pleasure to experience the mindset of other developers that that embrace the commercial product development. This blog post however, is in regards to helping Xojo be naturally more business centric.
As a developer with his foot in both of these worlds, I find it very funny that both of these developer worlds have very little respect for each other.
The Foxpro developers think of Xojo as a toy. It’s owned by a small company (compared to Microsoft), it’s not data-centric as Foxpro, it’s object model doesn’t help show inherited code, and it is a language that isn’t even on the Tiobe index. All of this is true. However, I get paid to solve business problems on multiple hardware platforms. I’ve looked around and Xojo is the best tool I can find that approaches Foxpro’s productivity and is actively supported. Cross-platform using native controls. Wow! Who wouldn’t be excited about this?
On the other side of the fence, Xojo developers think that Foxpro is a dead language that has been abandoned. Microsoft last released an update to Visual Foxpro in 2009, plus Visual Foxpro only runs on Windows. Visual Foxpro is only 32bit and will only ever be 32bit in a 64bit world. All of this is true. However, even after all of this time Foxpro appears in the list of the top 50 most popular languages. While Microsoft will never release the core of Foxpro written in C++, the portions of Foxpro that is written in in the Foxpro language has been open-sourced. There are a lot of very smart people currently maintaining and releasing updates to important portions of Foxpro. How cool is that?!?
I think these two active, clever, and similar minded communities should have a greater cross-over of talent. “Can’t we all get along?” – R.King
Enough talk. Let’s get right to it. The problem lies with the RECORDSET.
- Recordsets are final. There’s not much you can do to them after they’re created. More on this later.
- Recordsets are fragile. It’s not advisable to move the pointer back and forth in the recordset. You can move forward, but I’ve had problems moving the record pointer back to the top and doing a MovePrev.
- Recordsets can only pull from a single data source at a time.
- Recordsets don’t know much about the native data type of the source field from the source table.
Notice that I’m not arguing to do away recordsets. We need them. But we need to add something and that is intermediate between the external database and the internal recordset. Currently from the Xojo database framework, you can create a SQLiteRecordSet. What I propose is that we need to be able to create a target of a SQLiteMemoryTable. Hang with me here for a second as I establish what this buys us.
This SQLiteMemoryTable is what us long time Visual Foxpro developers would call a CURSOR. The concept of cursors in VB6 was a bad word. They didn’t work well. In Foxpro, they’re wicked fast, durable, and flexible. Cursors are in memory tables. When they are released, the memory is recovered. If there needed to be some cache written to the hard drive, when the cursor is released those files are cleaned up automatically. But the word ‘cursor’ is prone to misinterpretation of purpose. I would like to call this new feature something more descriptive. I think “SQLiteMemoryTable” might be a better fit.
Did I mention that Foxpro cursors are fast? I’ve done an optimized SQL Select into a cursor with a million records that takes a fraction of a second. The more fields, the more rows, the less optimized the SQL, the longer it will take. It’s still wicked fast. We don’t want Xojo to SQL Select into RecordSets and then have to turn around and run them back into In-memory SQLite tables just to get the power of SQL back. We want to get there in a single step.
How does this help Xojo become more attractive to business application developers?
Businesses draw from many different databases. They most popularly draw from the worlds #1 database: Excel spreadsheets. It seems that for every new CEO and CIO, they’re switching from one huge ERP system to another as if their business problems are caused by not having the right database. But I digress. By creating this new SQLiteMemoryTable, we can create a bridge against these desperate data structures.
Let’s take a look at what I do all of the time in Foxpro. I pull records into a cursor (think SQLiteMemoryTable) and then UNION them together in another step.
lcOrderID = "1234567890"
SELECT 1 AS nType, OrderID, PADL( ALLTRIM( STR( nItem ) ), 5 ) AS cItem, ;
cDesc, nQty, nCost, nSubTotal, ;
askprice, nDisc ;
FROM OracleDBSalesItem ;
LEFT OUTER JOIN MASTER ;
ON OracleDBSalesItem.nItem = MASTER.Item_No ;
WHERE OrderID = lcOrderID ;
AND NOT DELETED() ;
ORDER BY SalesItem.nItem ;
INTO CURSOR TempItems
SELECT 2 AS nType, OrderID, "Labor" AS cItem, ;
cLaborDesc AS cDesc, nQty, nCost, nQty * nCost AS nSubTotal, ;
CAST( 0.0 AS N(10,2)) AS askprice, 0.0 AS nDisc ;
FROM MySQLDBSalesLabor ;
WHERE OrderID = lcOrderID ;
ORDER BY dCreate ;
INTO CURSOR TempLabor
SELECT * FROM TempItems ;
SELECT * FROM TempLabor ;
ORDER BY nType, cItem, cDesc ;
INTO CURSOR TempInventory
Notice that there is a good bit of magic up above, and you may have missed it. Foxpro determined what the source field types were and honored them into the cursor. Why is this important? Because of the subsequent UNION. If the types didn’t match, then all sorts of things go wrong. Strings would get truncated, math doesn’t work properly, date confusion. So, when I’m asking for a SQLiteMemoryTable, I’m also asking for Xojo to be able to talk and determine field types and handle them properly. I know that’s a tall order, but it needs to be there to keep Xojo an easy development tool to get to know. If Foxpro had this feature 20 years ago, we should be able to have it today.
Did you see the big elephant in the room? I used SQL to UNION two SQLiteMemoryTables together within Xojo! How much value does that add to Xojo?!? Does that scream “business” ?!? I think so. What does that do for the value of Xojo. This is huge. Epic. By allowing me to SQL Select and then SQL Select again and then SQL Select some more, it allows me to iteratively approach a data solution. I don’t need to be able to be a SQL guru to achieve the exact result in a single bite. I can have more of my business logic in Xojo (if I choose to do so) as opposed to be reliant on the DBA to “do me a favor” and write a stored procedure for me. Xojo becomes the central tool.
While we’re on the subject of SQLiteMemoryTables, another powerful tool in Visual Foxpro is the BROWSE window. At any point in debugging a program, I can call for a BROWSE window. This displays the data in its own window. Think of a window that is filled with a listbox for every field in the SQLiteMemoryTable. Heck, I can even call the browse with a ton of filters and options. Powerful. Here’s the Foxpro help:
BROWSE [FIELDS FieldList] [FONT cFontName [, nFontSize [, nFontCharSet]]]
[STYLE cFontStyle] [FOR lExpression1 [REST]] [FORMAT]
[FREEZE FieldName] [KEY eExpression1 [, eExpression2]] [LAST | NOINIT]
[LOCK nNumberOfFields] [LPARTITION] [NAME ObjectName] [NOAPPEND]
[NOCAPTIONS] [NODELETE] [NOEDIT | NOMODIFY] [NOLGRID] [NORGRID]
[NOLINK] [NOMENU] [NOOPTIMIZE] [NOREFRESH] [NORMAL] [NOWAIT]
[PARTITION nColumnNumber [LEDIT] [REDIT]]
[PREFERENCE PreferenceName] [SAVE] [TIMEOUT nSeconds]
[TITLE cTitleText] [VALID [:F] lExpression2 [ERROR cMessageText]]
[WHEN lExpression3] [WIDTH nFieldWidth] [WINDOW WindowName1]
[IN [WINDOW] WindowName2 | IN SCREEN] [COLOR SCHEME nSchemeNumber]
With the new SQLiteMemoryTable, I have options for reporting as well. In my Foxpro development, I pound my data into submission … and then at the last minute I ask the user “what do you want to do with this data?” To a report or print preview? Sure. That’s a given. Export it to Excel? XML? CSV? Append the data to an email? Easy peasy. Because we’ve manipulated our data, each of these output options get the same data, just in a different layout. This really helps with consistency. Heck, it makes me more productive. Businesses like that.
While we’re talking about reporting needs, this SQLiteMemoryTable concept allows me to turn one report into hundreds. I often allow my users to specify the primary and secondary report order. I create these fake fields in the SQLiteMemoryTable (cursor) and never display it, but use it to control the output order. Here’s an example that I’ve Xojo-ified for you:
IF optionReportOrder1.Value = "LastName" THEN
cPrimarySort = "cLastNameField"
cPrimarySort = "STR( dCreate )"
IF optionReportOrder2.Value = "Order Amount" THEN
cSecondarySort = "nOrderAmt"
cSecondarySort = "cShipToState + cShipToCity"
DIM cSQLSelect AS String = "SELECT " + cPrimarySort + " AS cPrimary, " + cSecondarySort + " AS cSecondary, * " + _
" FROM PrevouslyRetrievedMemoryTable ORDERY BY cPrimary, cSecondary"
DIM oReportData AS SQLiteMemoryTable = oSQLiteMemoryTable.SQLSelect( cSQLSelect )
// Now you can ask the user where they want this data outputted to.
The problem with SQLite
We’ve got a problem. I never claimed this would be easy. SQLite is not strongly typed. That’s great for simplicity, speed, and flexibility. But this flexibility may cause us trouble. Xojo is going to have to do more of the strong typing on the behalf of SQLite. It’s just something to overcome.
I’ll get to Part 2 soon, but I hope I’ve piqued your interest in the idea of a SQLiteMemoryTable as a way to enhance the value of Xojo and increase it’s appeal to business application developers.