LDSql

Search this site

Welcome to the LDSql home!

This site is home for the LDSql database system, which is intended for leaders and clerks in LDS wards and stakes, and for software developers who help support them.
 
It is a free database product based on the popular, public-domain database engine, SQLite, which runs portably on many personal computers, smartphones and other devices.
 
The LDSql system is not a self-contained application, but designed as a database back end for many different applications.  Developers might use it as a foundation to write programs for Windows, Mac or Linux computers, or on handheld devices such as iPhone, Android or Blackberry phones.  Power users might use LDSql as-is by linking it into products such as Microsoft Access or OpenOffice. (See Using LDSql without programming.)
 
A rich variety of generic programs, often themselves free, are available to work with SQLite databases.  One such tool is SQLite Manager, an add-on for the Firefox browser.  (If you use Firefox, you already have SQLite installed on your computer.)  And there are many other ways to install it or embed SQLite software into custom applications.
 
The developer of LDSql has tried to accomplish much of the work of building database applications in a generalized way within the back-end structure (called a schema by database geeks) so the users or software implementers can work more easily with the data in their own front-end tools.  With much of the grunt work accomplished in SQL, it should be much quicker to build custom applications for various personal platforms.
 
Version 0.9 of LDSql is based on data files exported from the ward's MLS computer software.  These files include confidential membership data and are not intended for use by general ward members.  So if you are not an authorized user of such data, LDSql will be of no use to you.  If you are, talk to your bishop or clerk about providing the data files to you.
 
How to Use LDSql:
  1. Get the LDSql distribution package from Downloads.
  2. Obtain four MLS export files from the computer in the clerk's office: Membership.csv, HomeTeaching.csv VisitingTeaching.csv and Organization.csv
  3. Use the SQLite database program of your choice to import these four files into the corresponding tables of the simple staging database called MLS_Export_Ward.db.
  4. Close that database, and open (connect to) the database called LDSQLite_Ward.db.  With that database open, temporarily attach the MLS_Export_Ward.db database with the alias name Raw.
  5. Using your database program, run the provided script called Populate_LDSQLite_Ward.sql.  That script will transform the raw data into the structure used by LDSQLite_Ward.
  6. Then you are ready to browse or query the data.
Programmers, of course, can do all this under control of their own application, and such implementations may not use the physical files above.  Choose your favorite SQLite vehicle: ODBC, JDBC, embedded C, Windows dlls, iPhone or Android SDKs, etc.  The opportunities to use and extend this site's free code, distributed under the open-source MIT license model (which is quite permissive) are limited mainly by own your creativity.  See License and Copyright.
 
Benefits of the LDSql Structure
 
This schema provides many benefits over the raw MLS export data, which is not distributed in a form that is optimal for database work.  LDSql has:
  • Views of the membership data organized by individual and by family, and by organization such as priesthood quorums, Relief Society, and classes within the Primary, Young Men or Young Women auxiliaries.
  • Home- and visiting-teaching assignments by quorum, district, and companionship.
  • Built-in logic converting date formats and automatically calculating ages and key birthdays of members and significant dates that leaders need to know.
  • Names reformatted variously for automating mailings or emails -- "Brother and Sister Jones," "Frank & Emily Brown," etc.
  • Data fields that can easily identify recent converts (AgeAtBaptism, YearsSinceBaptism), and fields that can select which members and families are serving in callings or as home or visiting teachers.  (This is a useful proxy for "active.")  Prospective elders are also flagged.
  • Predefined views that emulate all the reports comprising the Bishopric Action and Interview List in MLS.  Like most views in the system, these views also include extended value-added fields that the MLS reports do not.
  • Support for multilingual applications.  Internal logic is based on language-neutral codes.  Display strings are table-driven, so foreign-language translations will be easy to create by populating a single, short _String table.  (In the current release, only English and Spanish entries in the _String table are populated.)
  • Synergy of the database engine and LDSql views make it easy to build queries such as: Select the home-teachers of prospective elders (who might pursue a goal of attending the temple) and those home teachers' own temple-recommend status.  Select the visiting teachers of recent converts or single mothers.  Count the priesthood-holders in specific geographic areas identified by MLS Geo Codes.
  • Primary and foreign keys, and normalized tables.  (This characteristic is significant to database programmers.)
Future versions of LDSql might include extensions for geographic coordinates.

 

Sign in  |  Recent Site Activity  |  Terms  |  Report Abuse  |  Print page  |  Powered by Google Sites