|
Detailed Table of Contents (PDF) here
Chapter 1: Why Client-Server? Why VFP? Why MySQL?
Chapter 2: Development and Deployment Scenarios
Chapter 3: Installing MySQL on Windows
Chapter 4: Installing MySQL on Linux
Chapter 5: Configuration of Users and Hosts
Chapter 6: Connecting VFP to MySQL
Chapter 7: Configuring MySQL
Chapter 8: The Interactive Use of MySQL
Chapter 9: Under the Hood: Where MySQL Keeps Its Data
Chapter 10: Creating Data Sets from Scratch
Chapter 11: Populating a MySQL Database: LOAD DATA INFILE
Chapter 12: Populating a MySQL Database Programmatically
Chapter 13: Advanced Data Issues
Chapter 14: Constructing SQL to Avoice SQL Injection
Chapter 15: Religious Wars: Remote Views, CursorAdapters, and SQL PassThrough
Chapter 16: A Client-Server State of Mind
Chapter 17: xBase to SQL Conversion Issues
Chapter 18: A Client-Server User Interface for Querying
Chapter 19: A Client-Server User Interface for Add/Edit/Delete
Chapter 20: Relational Integrity
Chapter 21: Getting Started with Stored Procedures
Chapter 22: Deployment
Chapter 1: Why VFP? Why MySQL?
VFP and MySQL make an incredible combination for fat-client, client-server and Web applications. However, Microsoft's many years of marketing neglect has relegated VFP to near invisibility. Thus, its capabilities are largely unknown and its selection as a development tool has become a questionable choice in the eyes of many uninformed or prejudiced users. And MySQL, despite its growing acceptance in many areas of the IT arena, is still a relative newcomer in terms of mind-share with many US users due to its open source roots and European origins. This chapter explains why VFP and MySQL is a compelling combination, and gives you, the developer, ammunition for those "discussions" with management.
Since this book covers both VFP and MySQL, it's likely that some readers are coming from an 'experienced in VFP, inexperienced in MySQL' perspective while others are more familiar with MySQL and less so with VFP. And it's probable that some are new to client-server completely. As a result, after discussing client-server architecture in general, I'll cover what each tool is and the benefits of using it.
Chapter 2. Development and Deployment Scenarios
Given that MySQL is a true cross-platform database that runs on Windows, Linux, and Macintosh (as well as others), there are many combinations of environments in which VFP works with MySQL. Together with the segregated requirements of client-server components and the disparate needs of a VFP/MySQL developer during development, testing, and deployment, it can be confusing to understand what pieces are required and where they fit. This chapter discusses the various scenarios, what the components for each combination are, and how they fit together.
Chapter 3. Installation on Windows
Installation is like finding the emergency brake in your car. Once you know where it is, it seems rather trivial. However, if you can't find it, you're not going very far. In this chapter, I'll show you how to install MySQL on a Windows box, starting with downloading the right files, and finishing up with some troubleshooting steps. In subsequent chapters, I'll describe the connection process between MySQL and VFP.
Chapter 4. Installation on Linux
This chapter supplements the on-line documentation with a detailed, step-by-step description of how to install MySQL from RPMs onto a Linux machine. It's from the perspective of an experienced Windows programmer who is new to Linux and provides background and perspective behind the steps. I'll show you how to install MySQL on a SuSE Linux box, starting with downloading the right files, continuing with the installation, and finishing up with addressing potential problems. Then I'll discuss an alternative installation of MySQL on a Fedora Core 5/6 machine. Finally, I'll describe what has happened as a result of the installation. In subsequent chapters, I'll describe the connection process between MySQL and VFP.
Chapter 5. Configuration of Users and Hosts
Once you confirm that the MySQL server is up and running, you need to do some configuration of users and hosts, and then test connecting to the server locally. The concepts and steps involved are the same for both Windows and Linux, so you need to read this chapter regardless of which operating system you're using.
Chapter 6. Connecting VFP to MySQL
Now that we have MySQL server up and running with the right users and hosts, it's time to direct our attention to the Windows box running VFP. In this chapter, we will connect to MySQL from within Visual FoxPro, using the MySQL ODBC driver and a brief set of VFP commands.
Chapter 7. Configuring MySQL
MySQL's operation is controlled by a configuration file. You can use a graphical front-end, called the MySQL Administrator, to change this file, or you can edit this file directly. In this chapter, I'll first show you how to install and use the Administrator. Once you're comfortable with this tool, I'll cover the essential configuration items you'll want to look at and possibly tweak yourself, and then briefly cover the rest of the settings. Finally, I'll discuss how to edit the configuration file directly.
Chapter 8. The Interactive Use of MySQL
Once the database server is up and running, and you have proven that you can connect to it from Visual FoxPro, what next? You'll need to get comfortable using MySQL in an interactive way - creating databases and tables, and then adding, editing, and deleting data from those structures. In this chapter, I'll discuss using the MySQL Query Browser to work with MySQL in an interactive session. After covering installation, I'll tour the various parts of the interface, and then show how to use it to perform data definition (creating databases and tables) as well as data manipulation (querying, adding, updating, and deleting data). Finally, I'll provide pointers to some popular third party replacements.
Chapter 9. Under the Hood: Where MySQL Keeps Its Data
When MySQL starts up, what happens under the hood? How does it know where the databases are? How does it know who can connect to the system, and, once connected, which databases they can access, and what they can do to a database and the data inside? While this topic has been broached in Chapter 7, "Configuration", there's plenty more still to come regarding how MySQL finds its data and how access to that data (privileges) is stored and structured. This chapter will finish up what we started in Chapter 7 so you know how MySQL drives, and is driven, by its privilege data stores.
Chapter 10. Creating Data Sets from Scratch
This chapter covers two separate topics. First, I discuss the differences between Visual FoxPro data types and MySQL data types, and how to choose which type you should use when creating a brand new table. Second, I cover how to create an empty database and how to set all of the features of the table and field editors. For those of you who are worried about that big chunk of data sitting in an existing database, do not fret! I'll cover conversion from text files to MySQL in the next chapter, and from DBFs to MySQL in the chapter after that.
Chapter 11. Populating a MySQL Database: LOAD DATA INFILE
Just as a database without tables or a table without fields isn't very interesting, a database without data isn't likely to attract much attention. In this chapter, I cover the first of two primary mechanisms for getting large amounts of data into your new MySQL database: using the native MySQL LOAD command. In the next chapter, I'll cover writing a conversion program that spins through a DBF and populates the corresponding MySQL table via SQL INSERTs.
Chapter 12. Populating a MySQL Database Programmatically
In this chapter, I cover the second of two primary mechanisms for getting large amounts of data into your new MySQL database: writing a conversion program that spins through a DBF and populates the corresponding MySQL table via SQL INSERTs.
Chapter 13. Advanced Data Issues
Once you're comfortable with creating MySQL databases and populating them from VFP tables, you'll spend some time doing so and may possibly run up against some issues more sophisticated than simply filling character and numeric fields as you did in the last chapters. In this chapter, I'll cover tips and tricks with BIT fields, Blobs, Nulls, and ENUMs.
Chapter 14. Constructing SQL to Avoid SQL Injection
Most people have heard the term "SQL Injection" but are often unconcerned about the subject, feeling it's a security problem - and since they're just database developers - it couldn't possibly apply to them. Even if you're building applications today where the threat of SQL Injection isn't applicable, it behooves you to learn more about it and to develop best practices. So, if you do enter that arena in the future, you're not suddenly vulnerable due to long-developed habits that are suddenly inappropriate in a new environment. In this chapter, I describe what SQL Injection is, when you might run into it, and how to program defensively to avoid it.
Chapter 15. Religious Wars: Remote View, CursorAdapters and SQL PassThrough
With the introduction of CursorAdapters in VFP 8, there are now three different ways to access remote data in Visual FoxPro. How do you choose which one to use? In this chapter, I'll discuss the pros and cons of each mechanism, and explain why I use SQL PassThrough in this book.
Chapter 16. A Client-Server State of Mind
Fox developers are among the most dedicated group of programmers in the world. Attend a conference and ask how many have been using the product for more than ten years, and you'll get a large number of hands raised, and probably a few snickers ("Ten? Hell, I've been using Fox since it was dBASE II in 1981!") But with this depth of experience comes a certain mindset in the ways of doing things, and it can be hard to change habits ingrained over decades of practice. It's time to consider changing that outlook to a client-server state of mind. This chapter covers the conceptual differences and gets you thinking in terms of client-server architecture and what it means to your systems when you work with record sets instead of whole tables.
Chapter 17. From xBase to SQL - how to move xBASE code to work with
a SQL backend
During your career as a client-server developer, you're bound to run into a situation where a customer or end user asks you to convert a traditional LAN application to work with a SQL back end. Even if the application has been designed with this conversion in mind, it's not an easy job; at the other end of the scope, such a process can be arduous to the point of requiring a rewrite. As a result, there isn't a simple cookbook approach, nor a one-size-fits-all strategy. Nonetheless, the tip sheet isn't completely blank. Here are some issues and reference charts to help you along.
Chapter 18. User Interface for Querying
Up to now, this discussion has been all programmatic. What about forms? You know - those things that the user looks at data in? In this chapter, I'll cover the creation of a typical user interface for querying a database, made up of a filter form for specifying parameters, and a results form that displays the matching records and allows the user to navigate among them. I'll show both a simple parent-child join as well as a form that uses a couple of lookup ('minor-entity') tables.
Chapter 19. A Client/Server Interface for Add/Edit/Delete
In the last chapter we finally got into some code. Unfortunately, it was all read-only code. What about ENTERING and SAVING data? Or ADDING records? Maybe even getting rid of them! Sure, you probably want to save data once in a while. Remember the work you went through 15 years ago to learn how to make your applications multi-user - when you couldn't just assume that only one user was touching the data? A similar paradigm shift is staring at you now as well - except that you already have some experience with handling contention. In this chapter, I'll discuss the basic mechanism to add, edit, and delete data both in single and multiple table forms. I'll also cover a number of techniques that are useful during the implementation of these forms.
Chapter 20. Relational Integrity
Most Visual FoxPro developers are familiar with the concepts of relational integrity,
and many have at least dabbled with having the database enforce RI instead of hand-writing custom code to do so. In this chapter, we'll add RI to one of our sample application's forms.
Chapter 21. Getting Started with Stored Proceduress
MySQL 5.0 made news with the addition of stored procedures, a feature that many claimed prevented MySQL from being one of "the big boys" in the database server arena. In this chapter, we'll explore how MysQL's stored procedures work and how to use them from within Visual FoxPro.
Chapter 22. Deployment
As the saying goes, "It's all fun and games until someone gets hurt." So it goes with development - it's also all fun and games until you actually have to make it work in production. Isn't that someone else's job? Unfortunately, probably not. In this chapter, we'll discuss various issues with getting your VFP application up and running in a production environment.
|
