Announcing LDSql

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.

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

Techgy wrote: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.

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.

natet wrote: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 problemfor 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.

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.

boomerbubba wrote: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

RussellHltn wrote: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.

RussellHltn wrote: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.

boomerbubba wrote: 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.

boomerbubba wrote: 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.

Alan_Brown wrote: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.

Thank you for doing that. I have a couple of followup questions about the content of the stake files.

1) With regard to the "Indiv ID" field of Membership.csv, Organization.csv and WardOrganization.csv, is the value of "Indiv ID" assigned by the stake MLS system, or is it somehow transmitted from the corresponding value of "Indiv ID" used in the ward export files?

2) Similarly, is the value of "HofH ID" in Membership.csv assigned by the stake MLS software, or does it use the value from ward MLS?

This is quite significant, because in the ward exports (and thus in the schema I developed for LDSQLite), the 'Indiv ID" field and the "HofH ID" fields are the natural primary and foreign keys for several tables. If those values are originated locally in stake MLS, they also would be unique within the scope of a stake. But if they are derived from ward MLS systems, they would only be unique when combined with Unit Number.

3) Do I understand correctly that the Unit Number field is integer digits, with no leading zeros? That is what the Wiki implies, so I assume it is correct. But it doesn't hurt to reconfirm. And I have seen some other applications in which a leading zero is applied.

boomerbubba wrote: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.

Out of unit members. Hmmmm, that could give rise to some special situations.

- A "out of unit member" record is created in "Unit A", but the actual records are in "Unit B" - what should the stake MLS export show? Two records for the same person - one in each ward? Or one record - but for what unit (Unit A, Unit B, or the stake)?

- What if the "Out of unit member" is from another stake?

- What if information is incomplete in the "Out of unit" member record?

- What if the member subsequently moves out of "Unit B", but still has a "Out of Unit" record in "Unit A"? What is the results if the export is done at any point in this process?

RussellHltn wrote:Out of unit members. Hmmmm, that could give rise to some special situations.

Interesting questions, indeed, but unfortunately they can't be answered with the test data, because there is no way to get an out of unit member into the stake membership data. You can only create an out of unit member at the ward level, which then presumably gets transmitted to the stake. But the transmission mechanism is disabled for test data.

boomerbubba wrote: 1) With regard to the "Indiv ID" field of Membership.csv, Organization.csv and WardOrganization.csv, is the value of "Indiv ID" assigned by the stake MLS system, or is it somehow transmitted from the corresponding value of "Indiv ID" used in the ward export files?

2) Similarly, is the value of "HofH ID" in Membership.csv assigned by the stake MLS software, or does it use the value from ward MLS?

I'm 99.99% sure that the answer to both of these is that the IDs are assigned by the Stake MLS software, independent of the ID values in Ward MLS.

In the test data, the IDs are the same in both Ward and Stake MLS, but that can't be true in general. If the value were generated by Ward MLS, then transmitted to the stake, there would most certainly be collisions, thus requiring the use of the unit to create a unique key.

But I don't see any collisions in my stake's data, and the IDs for about 3000 members range from about 212000 to 219000. To be 100% sure I'd have to go to one of the wards and do an export, but the probability of 3000 IDs having no collisions in a range of 7000 when they were generated independently in 7 different wards has to be so close to 0 that I feel pretty safe in concluding that they are generated in Stake MLS.

So one could not link ward and stake export files using these IDs, but you should be safe within the realm of stake export files using only the Indiv ID and HoH ID as unique keys, just as you can do in the realm of a collection of ward export files.

boomerbubba wrote: 3) Do I understand correctly that the Unit Number field is integer digits, with no leading zeros? That is what the Wiki implies, so I assume it is correct. But it doesn't hurt to reconfirm. And I have seen some other applications in which a leading zero is applied.

Yes, that is correct. The test unit for wards is 108, and it appears in the export files simply as "108", not "0000108".

Alan_Brown wrote:unfortunately they can't be answered with the test data

Right. Which leaves this to those of us with access to stake data to figure it out. It helps if you have a singles or student ward in the boundaries.

I'll go out on a limb and guess that the stake unit number shows up in the stake membership CSV when a member is a part of more than one unit. I should be able to test that theory.

RussellHltn wrote:Right. Which leaves this to those of us with access to stake data to figure it out. It helps if you have a singles or student ward in the boundaries.

I'll go out on a limb and guess that the stake unit number shows up in the stake membership CSV when a member is a part of more than one unit. I should be able to test that theory.

Some commenters in the Ward Tools thread, stake MLS users themselves, seemed to think the situation had something to do with the timing of move-ins and/or move-outs -- perhaps when stake MLS gets updated before the update is finally applied in the ward MLS databases.

Alan_Brown wrote:I'm 99.99% sure that the answer to both of these is that the IDs are assigned by the Stake MLS software, independent of the ID values in Ward MLS. "

Thanks. That analysis seems solid enough that this derived fact should be written into the verbiage of the Wiki.

Alan_Brown wrote:But I don't see any collisions in my stake's data, and the IDs for about 3000 members range from about 212000 to 219000.

That's interesting. In ward MLS, the "Indiv Id" and "HofH ID" values apparently just start incrementing at a very small integer, initialized at the time MLS was first installed. (The lowest value in my ward today for each field is 11.) So unless your stake started using MLS many years before MLS was invented and had a couple of hundred thousand move-outs, which seems impossible, stake MLS initializes its incrementation of IDs at a very large number.

boomerbubba wrote:That's interesting. In ward MLS, the "Indiv Id" and "HofH ID" values apparently just start incrementing at a very small integer, initialized at the time MLS was first installed. (The lowest value in my ward today for each field is 11.) So unless your stake started using MLS many years before MLS was invented and had a couple of hundred thousand move-outs, which seems impossible, stake MLS initializes its incrementation of IDs at a very large number.

I agree that the ID values just start incrementing at a very small integer (I'm pretty sure that integer is "1"). But I don't think Stake MLS is any different from Ward MLS in this regard.

I have another theory about how a stake would have such high ID values, related to the way Stake MLS processes data. The first part of the theory I am pretty confident about:

• Whenever a stake does a Request Unit Refresh Data, the membership data is imported into the stake from the wards, and all Indiv and HoH IDs are recalculated, but the starting value for those IDs continues to auto-increment from where it left off.

Although we've done a few unit refreshes, that postulate alone does not seem to explain why our 3000 members would be distributed across 7000 ID values -- we haven't had 4000 move-ins/move-outs since our last refresh (our stake is relatively stable).

Another interesting point is that there is one gap of 2300 IDs somewhere in the series of IDs. Perhaps a ward requesting a refresh can also cause that ward's members to get a new set of Indiv IDs. That part of the theory would be harder to test.

But as fun as this speculation may be, it's only speculation and probably doesn't make much difference in the grand scheme of things.

Alan_Brown wrote:But as fun as this speculation may be, it's only speculation and probably doesn't make much difference in the grand scheme of things.

What is significant to users of the export data is that if the ID values do reset upon a unit refresh, that would affect any applications that depend on "Indiv ID" or "HofH ID" as persistent values, for such purposes as linking to external tables of user notes or photos.

So if we are confident of that fact, it should probably be in the Wiki.

(Of course, all these things would be so much better documented by the people who really know, the MLS development team.)

boomerbubba wrote:What is significant to users of the export data is that if the ID values do reset upon a unit refresh, that would affect any applications that depend on "Indiv ID" or "HofH ID" as persistent values, for such purposes as linking to external tables of user notes or photos.

So if we are confident of that fact, it should probably be in the Wiki.

(Of course, all these things would be so much better documented by the people who really know, the MLS development team.)

That's a good point. But upon further reflection, I'm not sufficiently confident of my theory to document it. Sometime in the next month or two, I think our stake will request another refresh, so I'll be able to test the theory then.

This is lousy marketing practice, but I am renaming this system LDSql.

The new URL for the website is http://www.ldsql.com/

The detailed content of the site will be edited as soon as it is feasible for me to do so.

Meanwhile, the original post at the top of this thread cannot be edited by me. I would be obliged if any moderator empowered to do so would make that change at the top. Or at least make an insert to that effect in the top-level post.

boomerbubba wrote:Meanwhile, the original post at the top of this thread cannot be edited by me. I would be obliged if any moderator empowered to do so would make that change at the top.

Done. But I would note that it appears that you missed one instance of LDSQLite on your LDSql site -- the one in the upper left corner.

I'm guessing you're working on it, but you also might want to rename your support group on Google Groups.

Alan_Brown wrote:Done. But I would note that it appears that you missed one instance of LDSQLite on your LDSql site -- the one in the upper left corner.

I'm guessing you're working on it, but you also might want to rename your support group on Google Groups.

Thanks much!

I think I have done what I can do immediately on the main web site. To fix the link on the upper left corner, using the otherwise quick-and-easy authoring system at Google Sites, I think I have to rebuild the whole thing. I also will set up a new Google Groups.

EDIT: As it turns out, renaming both the web site and the Google Group was quite easy. (Thanks, Google!) All the high-references to the old name have now been changed to the new name. The URL for the support group did have to change. It is now http://groups.google.com/group/ldsql

I will take my time, and some care, to rename internal references in attached filenames and documentation in the next release.

One last observation about the stake and ward exports:

Now that Alan_Brown has cleared up the rules governing the "Indiv ID" and "HofH ID" columns, the three stake export files seem to contain the data for a robust schema based on solid primary and foreign keys.

That is not quite true for the ward MLS exports. They include HomeTeaching.csv and VisitingTeaching.csv, which are pretty lame as MLS spits them out. As we have discussed in this forum and the Community dev Wiki before, the MLS developers omitted any keys at all from those files. So they pose an inherent risk that there will be ambiguous records for home teachers, visitiing teachers or supervisors, and even a remote chance of ambiguous records for households or sisters being taught.

Every developer who relates these ward files together must grapple with that klunky problem. The solution I adopted in the LDSql schema is to select, semi-arbitrarily, a unique key for a member or household, and provide flags and views to warn users of potential ambiguities.

I have released a new version. The principal new features of v 0.9.2 are views that emulate the subreports of the Bishopric Action and Interview List in MLS, with extended fields that are not in the MLS reports also included:

• "Rpt Children Near Baptism"
• "Rpt Unbaptized Members"
• "Rpt Overdue Aaronic Ordinations"
• "Rpt YM Near Missions"
• "Rpt Men Not Served Mission"
• "Rpt Potential Missionary Couples"
• "Rpt Bishop Youth Interviews"
• "Rpt Bishopric Youth Interviews"
• "Rpt YSA Interviews"

See the Downloads section of the LDSql web site.

I have released v 0.9.3, which is available at the free Download page of the LDSql website:

http://www.ldsql.com

The principal new features of v 0.9.3 are a view that emulates the New Member Report in MLS, as well as enumerated callings and home-and visiting-teaching assignments for each member, concatenated and formatted for display. Those columns are available in most existing views. (Some now-redundant columns were deprecated.) See the release notes in the distribution package for details.

I have released v 0.9.4 of LDSql. It reimplements as tables three views introduced in 0.9.3. This change cures performance problems exhibited by that earlier release. These tables, containing concatenated text values by member for callings and HT/VT assignments, are populated at the time the database is loaded.

The prior release performed okay on my Windows laptop and desktop, but not well in when I prototyped queries of the schema on my Android.

boomerbubba wrote:Thanks much!

I think I have done what I can do immediately on the main web site. To fix the link on the upper left corner, using the otherwise quick-and-easy authoring system at Google Sites, I think I have to rebuild the whole thing. I also will set up a new Google Groups.

EDIT: As it turns out, renaming both the web site and the Google Group was quite easy. (Thanks, Google!) All the high-references to the old name have now been changed to the new name. The URL for the support group did have to change. It is now http://groups.google.com/group/ldsql

I will take my time, and some care, to rename internal references in attached filenames and documentation in the next release.

;)
The Link in the first post at the top of this thread for the Support Group needs to be updated too.

Also, and your hopefully already addressing this, but does LDSql support the new export format of MLS 3.1 ? As you mention in the earlyer part of this thread, changes in the export from MLS will affect the ability of LDSql to properly work. I have been using MyWard from Shiblon software and it no longer works and will probably not get fixed since there have been no updates since 2006. I also tested MLS companion database and it didn't work with the new export format from MLS 3.1 either.

I'm hoping your LDSql will be a good replacement to MyWard. :p:)

elderlarryhawkes wrote:;)
The Link in the first post at the top of this thread for the Support Group needs to be updated too.

Also, and your hopefully already addressing this, but does LDSql support the new export format of MLS 3.1 ? As you mention in the earlyer part of this thread, changes in the export from MLS will affect the ability of LDSql to properly work. I have been using MyWard from Shiblon software and it no longer works and will probably not get fixed since there have been no updates since 2006. I also tested MLS companion database and it didn't work with the new export format from MLS 3.1 either.

Yes, I am working on that. I anticipated the changes in 3.1, but until I saw the beta I -- like developers of any software that reads the MLS export files -- could not prepare in advance. I imagine that most third-party software will need to be fixed to accommodate the changes.

elderlarryhawkes wrote: I'm hoping your LDSql will be a good replacement to MyWard. :p:)

LDSql is not really intended as a standalone application for end users. Rather it is designed as a generalized back end for multiple applications on different platforms. I am optimistic that developers of some apps, such as the recently open-source Ward Tools, will adopt it going forward. I have been in touch with several such developers.

An alternate target for LDSql are "power users" on personal computers, who can use the tables and views as-is to browse and query, import into spreadsheets or generic database programs, etc. But I have not built any user-friendly, front-end application for that.

boomerbubba wrote:An alternate target for LDSql are "power users" on personal computers, who can use the tables and views as-is to browse and query, import into spreadsheets or generic database programs, etc. But I have not built any user-friendly, front-end application for that.

The output of MyWard was a set of HTML files that could be put on a PC or PDA, and with any web browser, used as a quick reference for most all ward callings and membership info.
Will LDSql output possibly provide an HTML output option?

elderlarryhawkes wrote:The output of MyWard was a set of HTML files that could be put on a PC or PDA, and with any web browser, used as a quick reference for most all ward callings and membership info.
Will LDSql output possibly provide an HTML output option?

If somebody writes one. The SQLite engine and my own LDSql schema are freely open for anyone to use.

My own focus is primarily on the back-end logic, where I think I can have the most general impact and jump-start development of lots of different apps. But I can't possibly write all the front-end apps myself. The number of good ideas exceeds my available time. I also have a day job and a day calling.

I have released a new version of LDSql, v 0.9.5. This release is an extensive restructuring to accommodate similar restructuring introduced by MLS v 3.1.0.

These changes had the general effect of allowing the recording of phone numbers and email addresses for individual members as well as for households. Because of the limitations of MLS exports, this also affected the logic of how members and households are linked in the home- and visiting-teaching tables.

Almost all tables and views were affected, as were all the scripts. There were new, deleted and renamed columns for individual and household phone numbers and email addresses.

Because of the extensive restructuring, this version is not backwards-compatible with earlier versions of MLS. To load data from such earlier MLS exports, use the LDSql schema and scripts from v 0.9.4.

I have released a new version of LDSql, v 0.9.6.

This version primarily makes it compatible with the MLS v 3.1.1 export format. The 3.1.1 release corrected a typo in a column name of the VisitingTeaching.csv export file in v 3.1.0, and that change broke third-party programs based on the 3.1.0 format.

The v 0.9.9 release alters the schema and scripts to support the loading of MLS tables with certain confidential fields suppressed. This is so auxiliary leaders can use LDSql, or applications based on LDSql, without being exposed to the confidential data.

The process is driven by the loading of the data upstream. Any of the following data columns from the Membership.csv export file can be globally suppressed by some external process:

MRN
Priesthood
Married
Mission
Spouse Member
Sealed to Spouse
Sealed to Prior
Baptized
Confirmed
Endowed
Rec Exp

If LDSql detects that data for any of those columns is globally absent, it will gracefully treat all data derived from that confidential column as Nulls.

To take advantage of this feature, some script or app would need to be created to allow a clerk to sanitize the files for the auxiliary leader because MLS lacks such functionality. I may write such an app someday, but others will probably build it.

boomerbubba wrote:The v 0.9.9 release...

So what will version 1.0.0 have in store? Or do version numbers have no real significance in relation to feature sets?

mkmurray wrote:So what will version 1.0.0 have in store? Or do version numbers have no real significance in relation to feature sets?

Well, I started out calling it 0.9.x because it was so new, sort of If It Works It's Not a Test. Now that LDSql has some real developer/users -- the open-source Ward Tools team has adopted it as the back end for a new-generation product -- I guess I should think about calling some version 1.0 soon.

The next major goal is to support stake MLS exports. That might be what it takes to claim such status in my own mind.

The open-source Ward Tools project team has now released a user-friendly loading tool for building an LDSql ward database directly from the four core MLS export CSV files. This application also provides options to suppress confidential fields from the database, and clean up the formatting of U.S. phone numbers.

While the Ward Tools retrieval apps using such a database (focusing on handheld devices) are still a work in progress, this utility can be used today by power users who want to browse or query the database on a personal computer. For these purposes, generic tools such as the Firefox SQLite Manager or OpenOffice can provide a lot of functionality without programming. For example, the database can drive mail-merge programs or perform some custom queries and reports that MLS cannot.

The free loading application -- a Windows versionand a Mac version-- are available from the Ward Tools site. For more information on using LDSql without programming, see this page at the LDSql site.

The Export (MLS) article has updated details regarding the bet on export files. It calm has the same monitions that it is intimate and simply the result of analytic thinking.

cyresjones wrote:The Export (MLS) article has updated details regarding the bet on export files. It calm has the same monitions that it is intimate and simply the result of analytic thinking.

First of all, welcome to the LDS Tech Forum. We hope that your experience is helpful to you and to the forum. In that regard, may I ask you a favor? Would you please repeat your recent post and clarify what you intended to say. It's a little confusing.

Thanks and again, welcome.

Techgy
Community Moderator