Here's a working framework project and some documentation (and lots of tips), especially useful to VFP programmers transferring to RealStudio (or thinking about it), which is originally where we came from, or for anyone new to RealStudio who wants to write database applications.
This isn't the only or best way to do anything in RealStudio, however it works for us.
This version compiles successfully on RS2010r5.1, and may require additional plugins.
It's a little difficult to separate our VFP meanderings from a real project (because it relies on stuff in app, as well as various modules), so this framework is a real project - it's just something we put together to help clubs manage membership renewals and financial reporting - it's not very exciting, but it does contain a reasonably good base to start from.
I've stripped out various licence numbers etc that we obviously cannot distribute and saved it as a project called "framework". Download the zip file and unzip into your project/s work folder.
It also includes our free ExcelWriter class and a bunch of other stuff not actually used in this project, but you might find useful (like ExtraXML module). You also get our VFP module which contains a fairly comprehensive implementation of various Visual Foxpro commands and supporting forms like WaitWindow etc., and a SQL Window too.
Had I the time (and the will) I would have moved all the extra app stuff (like db, rec etc) into a module (e.g. Common, which is where maOpt dictionary is defined), but at the time I was learning and didn't know any better - what you're getting is evolution...however it all works, you just need to be aware of where things are - and you can change it however you want.
Framework project: Open it, save it to a separate folder (named for the project you are creating, and use the same name for the project - believe me, you don't want lots of projects in the same folder.
Change it, take out what you don't need, but as you do so run a build to make sure it's all working. I've placed it on this page so others can use it too. If you enhance it or discover a ruinous bug, let me know (please).
We also add the following sub folders:
archive (a folder to hold backups created by the application)
base (contains seed database and release notes)
man (guides and documentation about the project),
man/pdf (pdf editions of the guides for distribution),
reports (contains all OTR reports for the application - this typically is distributed)
res (resources used by this project, like graphics, special fonts, whatever - graphics libraries rapidly get out of control so local copies are always best - you have been advised)
schema (contains the xml class definitions created by our datadict utility)
xml, html, sql (these are optional) but are handy for storing common sql templates, html templates and xml templates - suggest you always name the text files used as templates as "*.txt" - hat way you can write a txt file, store it in the appropriate folder, then drag it into your project and reference it as a string at runtime - a really cool facet of RS. For example, dim s a string=xml001 (where xml001 is actually a link to a file called xml001.txt in the xml sub-folder), modify the string somehow by maybe replacing parameter placeholders/symbols, whatever and it's ready to use. It's just simpler and more logical that way. Avoid using *.xml or anything other than txt. If you use *.xml, RS will attempt to load it as a class and fail...probably. You've been warned.
Database design: We use Base (menial.co.uk) fro SQLite database design and modification. On PC we use SQLite 2009 Pro (free, and comes with an ODBC driver, a bit weird sometimes and prefer to work on Mac anyway). I then use a "class generator" to write class xml files that can be used in RS projects (see schema folder for examples of what is produced). You then drag the resulting xml files into your schema folder in your project - RS will complain like anything about the format etc, but it works so I've never bothered reviewing it.
Using our datadict class generator you can easily create reacord "classes" for your project, then in code you "scatter" a record from a recordset into an instance of the class, then refer to it as you would in VFP (e.g. company.name). Check out the spControls and base classes for much more info about this, and look at the project code - it's not that hard, but it took a lot of work to get it that way).
Our (also free) datadict project lets you generate these class XML files with ease.
NOTE!!! Our framework requires every table to have an auto-increment ID field. Not only is this good practice, the framework simply will not work without it. You have been told.
Schema management: If (when) you change your database design you need to propagate that new design out to your clients. We use app.kSchemaVersion to store the schema release for the current build, and when we OpenDatabase it can then run a CheckSchemaVersion procedure that compares the current database schema version (we store it as an entry in a pref table in our database - it's structure never changes and it's simply a collection of key/value pairs like a dictionary. It's all in the framework project, so just follow it all through.
If it's lower than kSchemaVersion it loops over a case statement managing the sql required to change each schema release and updates the pref value on each iteration (in case it crashes and needs to continue, rather than start again).
If it's higher, then the binary probably won't play well with the database in it's current form and the user needs to update the binary (instead of using some old release with later version data). Bail out!
If it's the same, nothing happens and the program can continue.
Boolean values: DO NOT USE BOOLEANS IN SQLITE DATABASES - use a smallint with 0|1 values instead (you have been warned!)
Plugins: I also rely on various libraries - buy a complete MonkeyBread plugin licence (they're on special from time-to-time), and if you want decent reporting and have the time buy the DynaPDF plugin too. We also use Einhugur's plugins as they provide some very useful UI controls. You'll probably need any plugin libraries that controls in this project may use - most likely are Einhugur and MonkeyBread plugins (you can get trial versions).
Comms: we use FTP Suite for FTP and RealStudio's TCP/IP classes, they're not bad now and we have great success with RESTful web service access etc. SOAP is rubbish - don't go there (it's also so old-school as to not be funny and the whole concept might suit a race of futuristic super-compute
XML: we roll our own builders and record/field extractors - pretty easy stuff as it's all strings. We have used RS's XML classes to check for valid XML structures mostly but I like it real simple.
ZIP: we use Thomas Tempelmann's ZIP library (free version embedded here) - he has released a later version and asks for a contribution and he'll send a licence code in return (we have paid for a licence for the later version and use it in other projects). Visit www.tempel.org. We usually provide for internal backup (to the backup sub-folder) maintaining the most recent 10 backups, an external backup option (to usb stick etc), and an optional internet backup option to our server using ftp.
Reporting: we currently use OnTarget Reports http://www.ontargetreports.com/ - absolute rubbish, crashes constantly and doesn't scale in the designer so it's real hard to get things laid out properly - don't expect VFP's great reporting design and output options from this but it's the only thing that gets close, and it's cheap. Make sure you save after every change you make - you have been warned. I'm working (when time permits) on a visual report writer that provides page views, band views etc - more Mac-like that will run with embedded SQL or a recordset and emit PDF and XLS (native BIFF format) files and maybe html too, but I only really need PDF and let the user decide how to print (printing in RS is a nightmare). We'll release it as a free library (you'd still need to buy DynaPDF) eventually.
Any project with OTR (On Target) in it will have at the very least these errors when the project starts:
DO NOT RESOLVE THESE - YOUR REPORTS WILL NO LONGER WORK!
Windows: Oh, the horror! If you're developing cross-platform you need to know that RS runs debug apps differently - on Mac the debug app runs in the project folder. On Windows it runs in a sub-folder (don't you hate that?). This means when you "open" a database connection in code you need to make sure it looks in the right place - with Windows you get a new debug folder with no database in it, so your app needs to look in the parent folder. So, if your app depends on local folder stuff make sure you have appropriate target-aware code to look in the right place. Go figure.
Resources: Check out http://www.famfamfam.com/lab/icons/silk/ for a great looking library of icons.
* enjoy *