Announcing LDSql

[boomerbubba]

Today I am announcing LDSql, a database system for leaders and clerks who have access to the MLS export files.

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.

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.

I have tried to accomplish much of the work of building database applications in a generalized way within the back-end structure 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. I have built in table-driven support for multilingual applications, although only the English table values are populated initially.

For any specific issues, feedback or bug reports, please post comments at the dedicated support group rather than clutter up LDSTech with my problems.

For the benefit of forum colleagues here, I confess that I have some trepidation about releasing a tool based on the MLS export files. In the short run, if the file content or format changes due to enhancements in MLS, I will take my chances and chase the changes. In the longer term, I have some fears that the Church IT department will cease support of the export files altogether. But in both respects, I will be in the same boat as the many developers and users of this data format.

LDSql is released under licensing terms that are quite permissive as open-source licenses go. At some later time, I may open the code up for contribution by other developers. Meanwhile, FWIW, anyone is welcome to consume my work product.

[Techgy]

Perhaps I simply missed it, but what security does this package have for the data that's imported from MLS?

[boomerbubba]

Originally Posted by Techgy

Perhaps I simply missed it, but what security does this package have for the data that's imported from MLS?

That depends entirely on the final use of the LDSQLite database schema. This is not an application, but the foundation for different applications.

In its simplest form on a personal computer, a SQLite database file is just a physical file, so it gets whatever security the user has for files on that computer. In a scenario where a power user accesses the database through OpenOffice, for example, that would be the case.

SQLite database files are just as secure as a spreadsheet or OpenOffice database -- no more, no less. So if a ward (with appropriate stake permission) installed this on the clerk's office desktop, policy would dictate that the files be kept locked away on a flash drive when not in use.

If a developer used this schema to build a custom application on a smartphone such as iPhone or Android, he would likely be using the SDK to drive the embedded SQLite engines that power many apps on these phones. Typically there is security buiilt into the OS for such apps.

[natet]

This looks like a lot of fun. We already have an application in our stake that uses the export format. I'm dying to try to rewrite it using sqlite now.

[boomerbubba]

Originally Posted by natet

This looks like a lot of fun. We already have an application in our stake that uses the export format. I'm dying to try to rewrite it using sqlite now.

Unfortunately, my initial release does not support the export files from stake MLS.

I have the goal of doing so, but am hampered by lack of sample test data, or even an absolutely clear understanding of the detail of the stake format. (I have a ward-level clerical calling, which gives me access to ward MLS export data.)

This is generally a big problem for developers working with the export files, and for anyone trying to troubleshoot problems relating to the data. Because the data is confidential, and there is no sanitized test data available, we sometimes are working blind.

There is not even any official documentation of the export files from the MLS team.

[natet]

Well, I have both stake and ward level callings, so I may make a stab at the stake data. I'm not sure I have a full export of the stake data, just what we use to generate the stake directory.

Even the ward data will be useful. Our scripts generates a separate PDF document for each ward, and a PDF document from the stake's data. These documents are then sent to the printer to create the directory.

[RussellHltn]

Originally Posted by boomerbubba

Unfortunately, my initial release does not support the export files from stake MLS.

I have the goal of doing so, but am hampered by lack of sample test data, or even an absolutely clear understanding of the detail of the stake format.

Do you have access to making a test install of MLS? If so, then the test unit data may be enough. According to the Wiki, it appears it's just a couple of extra fields

[Alan_Brown]

Originally Posted by RussellHltn

Do you have access to making a test install of MLS? If so, then the test unit data may be enough. According to the Wiki, it appears it's just a couple of extra fields

The problem is in the thread Boomerbubba linked to -- the test data is okay for member data, but has no callings or home/visiting teaching data. And ward callings can't be populated in the stake database at all using the test data.

[boomerbubba]

Originally Posted by RussellHltn

Do you have access to making a test install of MLS? If so, then the test unit data may be enough. According to the Wiki, it appears it's just a couple of extra fields

As Alan_Brown says, there are inherent problems generating sample data that way.

As for what the Wiki says about the stake file formats for Membership.csv and WardOrganization.csv, take it with a grain of salt. I wrote that Wiki entry based on second-hand descriptions of the data from others. I am pretty confident about my analysis of the ward export data, but I have never had a chance to wallow in the stake files. And none of these files are documented by CHQ.

Perhaps natet or soemone else with stake access could do a thorough analysis and add it to the Wiki. When I was working on my project, I decided I could perform the most generally applicable service by stopping first to document file formats on the Wiki before completing my own schema.

As a footnote, even the Membership.csv file in the stake export still has things I don't fully understand. This thread in another forum (the support group for Ward Tools, another third-party app dependent on the undocumented MLS files) demonstrates that some stake users have discovered anomalies in the content of the Unit Number field. Read the comments near the end of that thread.

[Alan_Brown]

Originally Posted by boomerbubba

Perhaps natet or soemone else with stake access could do a thorough analysis and add it to the Wiki.

I have now done this. The Export (MLS) article has updated details regarding the stake export files. It still has the same warnings that it is unofficial and simply the result of analysis. But the stake files are not that different from the ward files, so I'm pretty confident that the stake file documentation is as good as the ward file documentation.

Originally Posted by boomerbubba

As a footnote, even the Membership.csv file in the stake export still has things I don't fully understand. This thread in another forum (the support group for Ward Tools, another third-party app dependent on the undocumented MLS files) demonstrates that some stake users have discovered anomalies in the content of the Unit Number field. Read the comments near the end of that thread.

I have no personal experience with these anomalies, so I did not document them in the wiki.