Database Changes

Making some changes to the database structure based on feedback from other rare breed breeders. I’ve moved the registration info out to a separate set of tables to account for sheep that may be registered with more than 2 registries. I have found some breeds with 3 or more different registrations, example US BWMS, UK BWMS and Natural Colored, so needed to allow for more than 2 different registration numbers per sheep. I’ve also started to add the tables and placeholders necessary to handle semen collections and dealing with lambs sired by rams that have died. Also embryo collection or eggs and handling lambs with a genetic mother who is different from the rearing mother for embryo transfer. That part is still a work in progress but I’m making headway.

Squashed a few more lambing bugs. Just in time. We are within the gestation period now and expect lambs any day now.

Back in the Saddle

After a bit of a hiatus while we did normal things like moving breeding rams in and out of pens, all handled by making any required changes to the database by hand I am back at updating LambTracker.

Current top priority task, integrate the pull requests from other developers and update the demo and blank database structures to be the current design.

As we’ve added new modules I’ve added to the database tables but I haven’t gone back to update the demo and blank versions of the database.

Lots of Clean-up

I’m doing a lot of code clean-up and fixing the GitHub Repository. I am also adding the manual into GitHub although I am writing it in Scrivener. Up next is attempting to create a repository for my SQLite queries.

The model I am moving to for branching and development is described here:

a-successful-git-branching-model

Hopefully it will make the code a bit easier to understand for new people.

Some LambTracker Bugs

We just finished sheep experiment week. We are working with the USDA on developing non-surgical AI procedures for sheep. Last year we successfully used LambTracker to collect a lot of data during the week on insemination depths, estrus characteristics etc. However, since then I had added the days in age calculation to the Evaluate Sheep function and did not test that the custom evaluations still worked. So LambTracker failed during the inseminations. I didn’t have time to debug it then but I do now and I need to do some re-writing of the days in age code to get it working again. I re-used a cursor I thought was not being used at all and that caused all sorts of database errors.

I am also adding a lot of the desktop functions to create and set breeding records, both the ram side and the ewe side that I did by hand last year. So far that is working fairly well but it’s slower than I’d like to get the data all in correctly.

Desktop Progress & A Request

I have been doing further work on the chromeos-apk system for running an Android program within the Chrome browser. It appears to be working well enough and is getting better all the time so at this point that is my focus for making the desktop portions of LambTracker.

So far the first function I am working on is setting and removing Alerts. I have the setting of them down but removing them is proving more difficult.

The biggest issue seems to be Android’s way of handling listviews and trying to use list adapters to manage the data.

My request if for additional programmers. At the Livestock Conservancy meeting I got many ideas and suggestions for enhancements. Since I am really the only programmer working on this it is necessarily slow going.

The development tools are all freely available and you can do development on Macintosh, Linux or Windows systems. All the code is up on GitHub so you can see where I am.

So I’m asking for any readers who are programmers or who have friends or relatives who are programmers who want to help make freely available flock management software more robust please join in. Go fork the repository and get started. I’m always available to help explain my thoughts and also send out info on specific requests that have been made.

Some new Activities

Moving forward looking at using the Android program as the desktop version. Current task is developing several new Activities that will be used to test the desktop options.

First up is removing sheep. We are butchering and culling stock now and I need a faster way to document the removal of a sheep.
Another one is reviewing an entire sheep record to fill in missing details or other problems.

Entering Back Data

Now that all of our existing sheep are in LambTracker and the 2014 lambs’ data is only in LambTracker I started seriously working to enter in the 16 year history of the flock into the database. I’ve discovered a number of issues and developed some workflows that seem to be making the process go much faster. This post will be updated a bit as I get everything documented.

I started LambTracker with the assumption that you would not have to enter in all the history before you started using it. This is good for allowing the users to test the viability of the software first but does make adding reams of back data more painful. Here is the current workflow I am using as I enter in 16 years of history with our flock.

1. Pick a table within the LambTracker database to work on.
2. If the table already has data in it then do a query to get all the data out and save it as a .csv file. If you do not have any data in that table now the get a copy of the blank spreadsheet document that replicates the structure of that table.

Now you have to take one of 2 paths depending on whether you are updating existing records or inserting new ones.

For updating the existing table and its data:

Bring the .csv file into a spreadsheet program. I use LibreOffice. Take care to ensure that fields that might appear to be numbers (NSIP ID, registration ID and so on) are brought in as text fields only.

Edit the spreadsheet file to update and add missing information as required.

Save a copy as a .csv file.

Use a csv to sql tool to create update statements for the file.

Back up your existing LambTracker database.

Run the queries to update the database.

Verify the new data are in correctly.

Back up the database again.

Repeat as necessary for each table to update.

For inserting new records into the table:

Edit a copy of the blank spreadsheet document that replicates the structure of that table adding data as required. Pay particular attention to the format and ensure that all text fields are really stored as text. Also dates must be in the format YYYY-MM-DD stored as text for LambTracker to work properly. Times are stored as text fields too HH:MM:SS

Save a copy as a .csv file.

Use a csv to sql tool to create update statements for the file.

Back up your existing LambTracker database.

Run the queries to insert new records into the database.

Verify the new data are in correctly.

Back up the database again.

Repeat as necessary for each table to create or add to.

There is a lot more that I’ve discovered about how to do this efficiently but this is the gist of it.