I tracked down the technical marketing manager for “Autodesk MapServer Enterprise” who explained the FDO technology in a way I could understand. He compared it to the power converters you use when you travel. You have this base and you plug-in different connectors based on the country you visit. The base is FDO, the connectors are the FDO providers. Both of these parts (save the providers for Oracle and SQL Server) are now open sourced by Autodesk.
The fact that Autodesk has open sourced this technology means that any developer can write a new provider. A provider accesses content. That content might be a file format (SHP) or a database (MySQL) or even another interface (WMS, WFS) or program (OGR, GDAL).