|
These are the steps that any process would typically take to populate an LDSql database under program control. The precise implementation will be platform-dependent. For example, the SDK of a phone platform or a programming language may not allow running whole SQL scripts at once, but provide methods to encapsulate individual SQL statements. There may even be special methods for beginning and ending transactions, or making inserts to tables. But the basic flow is probably similar.
First, create the MLS_Export_Ward and LDSql_Ward databases. This is easily accomplished by running the two SQLite scripts provied: Create_MLS_Export_Ward.sql and Create_LDSql_Ward.sql using the SQLite interface of the target platform. For example, smartphones such as Android and iPHone already embed an intance of SQLite for application storage, and an SDK to drive it. (Alternately, if the target architecture allows direct access to its file system for persistent data storage, the implementer could skip those "create" scripts and just use the unpopulated but defined SQLite database files provided in the standard LDSql distribution: MLS_Export_Ward.db and LDSql_Ward.db. This might be the typical case for a PC, Mac or Linux application. There is no need to maintain the convention of the .db extension. The files may be renamed however you like.) If the MLS_Export_Ward database is already populated with old MLS data, initialize it by executing a DELETE statement to empty each table. Import each of the CSV files exported from MLS into its corresponding table in the raw staging database, MLS_Export_Ward. This step is dependent on the target platform and its programming environment A typical process would open the MLS_Export_Ward database, loop through the CSV files, parse each record and execute a SQLite INSERT statement. The tables of MLS_Export-Ward are simple, one-to-one tables that correlate with the structure of each CSV file.
For support of non-ASCII characters, such as accent marks, SQLite uses UTF-8 encoding internally. The MLS export files do not, but rather uses the Latin-1 encoding that is part of the native Windows 1252 character set. So it is your responsibility to do the conversion to UTF-8. A workaround might be to use a modern text editor to convert the MLS export files to UTF-8 in the first place.
For best performance, implementers are strongly advised to execute a BEGIN TRANSACTION statement before an INSERT loop and a COMMIT statement following it. After the MLS_Export_Ward database is populated, close it (disconnect). Then open (connect to) the LDSql_Ward database, and execute an ATTACH statement to attach the MLS_Export_Ward database, giving the attached database the alias: Raw. Now execute the Populate_LDSql_Ward.sql script. This script accomplishes all the transformations to empty and reload the LDSql_Ward tables from the raw staging tables. Again, for performance reasons, precede the running of Populate_LDSql_Ward.sql with a BEGIN TRANSACTION statement and follow it with a COMMIT statement. (These statements were deliberately omitted from the script so that it also could be used by interactive power users in some GUI tool such as SQLite Manager or SQLite Maestro. Such tools often wrap scripts in their own BEGIN TRANSACTION/COMMIT pairs, so they fail if the transaction statements are included redundantly.) The LDSql_Ward database is now ready for querying by your application. The loading process may do some cleanup, emptying the tables of the staging database MLS_Export_Ward with DELETE and VACUUM statements, and deleting the CSV files to save space and maintain security. There is no need to keep the MLS_Export_Ward database populated, because there the LDSql_Ward database will recreate all these denormalized views on the fly. (For example, the view called Membership in LDSql_Ward mirrors the Membership table of MLS_Export_Ward, as well as the field structure of the original Membership.csv file exported from MLS.) |