KISS your Koha patron categories

A case study of applying the KISS principle to simplify and standardize your Koha patron categories.

KISS, an acronym for “keep it simple, stupid” or “keep it stupid simple”, is a design principle noted by the U.S. Navy in 1960. The KISS principle states that most systems work best if they are kept simple rather than made complicated; therefore, simplicity should be a key goal in design, and unnecessary complexity should be avoided. The phrase has been associated with aircraft engineer Kelly Johnson. Variations on the phrase include: “Keep it simple, silly”, “keep it short and simple”, “keep it simple and straightforward”, “keep it small and simple”, or “keep it stupid simple”. [1]

Earlier today we received a request to add a new SQL report to generate a list of all students with their email ids from the librarian at our client partner Sister Nivedita University. The request prima facie was simple. However, when we looked at the patron categories for students under Administration, the first reaction was “Uh oh!”.

Let me explain. There were 8 patron categories for students (of all types) and all these student patron categories had different mnemonic codes that were as follows:

DIP Diploma – 2 years
GNM-3 General Nursing and Midwifery – 3 years
RS Research scholar
SPG-2 Student Postgraduate – 2 years
SPG-3 Student Postgraduate – 3 years
SUG-3 Student Undergraduate – 3 years
SUG-4 Student Undergraduate – 4 years
SUG-5 Student Undergraduate – 5 years

Still, just to fulfill the service request, all we needed to do was to create an SQL query like

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode IN 
('DIP', 'GNM-3', 'RS', 'SPG-2', 'SPG-3', 'SUG-3', 'SUG-4', 'SUG-5')

The ‘problem’

There was no way to know from the mnemonic values that all these codes designated a student patron category, as there was no uniform standardization of the values used for student categories. To put the problem in a perspective that library professionals will understand easily – e.g. if we saw a DDC class number that started with 004, we’ll immediately know that it belongs to “computer science“, but what if the computer science documents were marked out with numbers that did not denote computer science under DDC???

Further, if in the future, the library added another student patron category, the SQL report would need to be updated in order to give the correct output that will include members belonging to this newly added category.

The ‘solution’

Luckily the solution was both simple and straightforward. We had to standardize and re-code the student patron categories and update the affected patrons. To do that we prefixed every student patron category with “STD_“. And thus, the student patron categories became:

STD_DIP2 Diploma – 2 years
STD_GNM3 General Nursing and Midwifery – 3 years
STD_RS Research scholar
STD_PG2 Student Postgraduate – 2 years
STD_PG3 Student Postgraduate – 3 years
STD_UG3 Student Undergraduate – 3 years
STD_UG4 Student Undergraduate – 4 years
STD_UG5 Student Undergraduate – 5 years

This was followed by updating the categorycode in the borrowers table to reflect the new changes. As a result the SQL query became much simplified:

SELECT
   cardnumber, firstname, surname, email
FROM borrowers 
WHERE categorycode LIKE 'STD_%'

If the library added a new student patron category in the future, all they would need to do is prefix the code with “STD_” and this query would continue to work without any change with this simple and straightforward update.

Legalese

This solution required us to directly access the production database with MySQL’s foreign key check turned off with SET FOREIGN_KEY_CHECKS=0;. If you get inspired to follow this, please ensure you have a complete database backup before you attempt this. And if by chance, you manage to mess it up or damage your database while attempting to do this, we are NOT responsible in any manner.

References [1] https://en.wikipedia.org/wiki/KISS_principle

ESI-PGIMSR (Kolkata) selects L2C2 Technologies as their Koha support partner.

We are pleased to welcome our newest client partner ESI-PGIMSR (Joka) Library to our cloud-hosted Koha ILS platform.

About ESI-PGIMSR

ESI-PGIMSR, Kolkata, also known by its full name Employees State Insurance Post Graduate Institute of Medical Sciences and Research & Employees State Insurance Corporation Medical College, Kolkata, is a MCI recognised medical college established in 2013 under the aegis of the ESI Corporation, an autonomous body under the Ministry of Labour and Employment, Government of India.

About the library

The library is located at the 2nd Floor of the Academic Building with floor area of more than 3000 sq. ft. Users have facility of for easy browsing, reading and borrowing of Books and Journals. The library caters to the need of M.B.B.S. students, Research Scholars, Post Graduate Students, Senior Residents, Junior Residents, Medical Teaching Faculty and other Doctors and Paramedical Staff of the Institute. The library follows open access system with books arranged using the Decimal Classification (D.D.C.) scheme.

“Fixing” ascii sorting in SQL Reports

A quick-n-dirty SQL tuturial

The Problem

Recently there was a request from IDSK – they wanted the accession register SQL report for their Rabindranath Tagore Grant collection books to be listed in a natural sort. Documents of this collection carry the prefix “RT” which is fine and dandy. The “problem” started after that. In their earlier legacy system (SOUL v1) when these books had been accessioned, they did not use an fixed uniform length accession no for this collection. For example, instead of RT00001, it was entered RT1, RT2 and so on. Further, their problem was compounded by another glitch. In several of these accession nos, there were spaces inserted between “RT” and the actual numerical part. When they migrated to Koha with L2C2 Technologies, these problems were carried over to the new system as data curation and correction was not part of the deliverables. Now, three years later, when reports were being asked for, these issues had come back to haunt them.

The “Solution”

The RT collection has over 4000+ books and a manual fix was not feasible at this time. So using the ORDER BY keyword in the report’s SQL like this: ORDER BY LPAD(REPLACE(REPLACE(TRIM(items.barcode)," ",""),"RT",""), 10, ' ') ASC we at least managed to hide the problem and also got natural sorting to work. The first REPLACE() took out any space in the accession no, and the second REPLACE() stripped off the “RT” prefix, leaving us with only the number. Finally using LPAD() we zero prefixed the number so that each one was exactly 10 characters long. And we had our naturally sorted list of accession numbers in the report as we can see below.

Bangiya Sahitya Parishat Library goes online with L2C2 Technologies

The 126-year old heritage Library chooses L2C2 Technologies as their Koha hosting service provider.

The 126-year old heritage library of Bangiya Sahitya Parishat (aka Bangiya Sahitya Parishad) chooses L2C2 Technologies as their Koha hosting service provider, and it is with great joy admixed with a sense of greater responsibility that we welcome our newest client-partner. The OPAC is available at https://library.bangiyasahityaparishat.org/

About Bangiya Sahitya Parishat

Originally founded on 23 July, 1893 as the “Bengal Academy of Literature” literary society, the institution renamed itself as “Bangiya Sahitya Parishat” in 1894 with Romesh Chunder Dutt as it’s first president and Rabindranath Tagore and Navinchandra Sen as vice presidents. With the study and development of the Bengali language and literature as its main objective, the society has over the years, has delved into the cultural, historical, archaeological, sociological and other scientific studies and researches with special reference to Bengal. The society moved into its present address in 1908. The land the building stands on was donated by Maharaja Manindra Chandra Nandy of Cossimbazar Raj. In 1997, Kolkata Municipal Corporation notified the building as a Grade-I heritage building.

About the library

Established in 1894, the Bangiya Sahitya Parishat Library was the brainchild of Archarya Ramendra Sundar Tribedi. From its early beginning with a handful of donated books and periodicals, the library today in its collection has 1,01,719 books and 17,558 journals. The collection includes very rare and valuable books as well as many rare 19th century periodicals in Bengali and English such as ‘Digdarshan’, ‘Samachar Darpan’, ‘Samachar Chandrika’, ‘Friend of India’ etc. [1] The library has several important collections, the most important highlight being the “Vidyasagar Collection” donated by the erstwhile Maharajah of Lalgola. Given the richness of its collections, the library attracts readers and researchers from both India and abroad.

References: [1] https://bangiyasahityaparishat.org/library/

SSH using PuTTY.exe into a vagrant managed kohadevbox VirtualBox VM on Win 10 Prof

Steps to set up public-key based login into a vagrant managed kohadevbox VM

What is kohadevbox?

As the README.md at https://gitlab.com/koha-community/kohadevbox says – “Create a development environment for the Koha ILS project. It uses Vagrant and Ansible to set up a VirtualBox.” Basically it automates the long and somewhat complex process of manually setting up a development system for #kohails.

Why PuTTY?

From the website – PuTTY is an SSH and telnet client, developed originally by Simon Tatham for the Windows platform. PuTTY is open source software that is available with source code and is developed and supported by a group of volunteers. It is our go-to tool on Windows for accessing physical Koha boxes or VMs via SSH.

Vagrant sets up SSH service on the VM to use public-key authentication. Therefore our PuTTY instance needs to know the private key that Vagrant had setup inside the VM, in order to login into it. As many posts in Stackoverflow indicates, this is a point where many people get stuck. Usually they try with %USERPROFILE%\.vagrant.d\insecure_private_key and that typically fails.

Steps for kohadevbox

Using git-bash go to the directory where you had cloned kohadevbox. Be default it will be %USERPROFILE%/git/kohadevbox if you have simply followed the instructions from kohadevbox README.md file. In our case, it was %USERPROFILE%/gitdev/kohadevbox as we had changed it.

Run the command

vagrant ssh-config

You should see something like this:

The line to note in the output is IdentityFile. That’s the private key you need to grab and feed to PuTTYGen for it to convert it into a PuTTY compatible .ppk format private key.

NOTE: This path to the key is going to be different for different users, so do not attempt to copy-paste what you see here, instead use your own IdentityFile value.

Now we convert the vagrant_private_key from IdentityFile and save it as a .ppk file.

And lastly we import it into PuTTY like this and we are good to go!

Finally we are logged in into the VM

NOTES: The versions of software used – (a) PuTTY.exe – 0.71 (b) Vagrant – 2.2.7 (c) Git Bash – 2.25.0 (d) VirtualBox – 6.1.2

Nirmala College Library,Muvattupuzha chooses L2C2 Technologies for Koha Support

Nirmala College, Muvattupuzha, Kerala, India, migrated from proprietary LMS to Koha ILS on the cloud

We are happy to announce that Nirmala College, Muvattupuzha has chosen to L2C2 Technologies for migration from a legacy proprietary software to Koha on our hosted Koha on the cloud platform.

About Nirmala College

Established in 1953, Nirmala College, Muvattupuzha is a first-grade college affiliated to Mahatma Gandhi University, Kottayam. It is a minority Christian institution governed and managed by the Corporate Educational Agency, Syrian Catholic Diocese of Kothamangalam. In strict compliance with the vision of the founding fathers, the college has served the community, irrespective of caste, creed and colour and has contributed to the world quite large a number of value-based Nirmalites. Her performance in academic and non-academic activities has been acknowledged by the NIRF rankings 2017 in the form of its position on the 91st among the list of colleges in the country. In addition, the college has been bestowed with the prestigious ‘Star College’ status by the Department of Bio-Technology (DBT), Government of India, in recognition of the advancements made by the college in the field of science education, with a handsome financial grant. All science departments of the college are supported by the DST- FIST scheme.

The college is located on a hill top at the doorstep of high ranges in the western ghats. The campus is spread over 53 acres of land with state of the art facilities for a constructive learning experience. The college has now grown to become a centre of learning that offers graduation in 16 disciplines, post-graduation in 14 and research facility in six. The college also runs 17 certificate/ diploma programmes. The IGNOU Study Centre, functioning in the college, offers 11 UG and 04 PG Programmes. The Nirmala Civil Service Academy, functioning in the college, caters to the needs of civil service aspirants.

From : http://nirmalacollege.ac.in/welcome-to-nirmala-college/

About the Library

The Library has a collection of over 80,000 volumes. The Library subscribes to 190 journals and periodicals both national and international. The entire legacy data was migrated over to Koha ILS. The OPAC is available at https://nirmala-opac.l2c2.co.in/

A Koha ILS mashup to turbo-charge the sort1 and sort2 patron / borrower record fields

This video shares the idea about how to turbo-charge the sort1 and sort2 fields for patron records in Koha by utilizing Koha’s very own reports web service in tandem with a bit of JQuery.

If you are feeling impatient because you already know the background, feel free to jump to 3 minutes 10 seconds into the playtime, to see the actual action in progress.

Runtime: 14 mins 35 sec.

Show me the money…. er.. the barcode?

Fixing the issue of checked out items barcodes’ not showing up for patrons logged into the Koha OPAC.

The Problem

Earlier this morning, Pranab Roy who manages the Library at the Karnavati University‘s UnitedWorld School of Business‘s Kolkata campus popped up on WhatsApp with a question – “Sir, why is Koha not showing the barcode / accession number of a borrowed document when an user logs in into their own account via the OPAC?”

This is what he meant. And this is actually the expected behaviour, with Koha doing exactly what it was asked to do. I could understand his confusion since the OPAC search’s details view showed the barcode quite nicely, then why not for the users themselves?

A bit of backstory

Karnavati University Libraries had shifted to L2C2 Technologies‘ cloud platform from a pre-existing Koha instance maintained by a 3rd party. As such the system had quite a few issues. While we had fixed a larger number of these during the initial on-boarding stage, some of these are getting ironed out only now as the librarians hit these “bumps on the road”. Pranab’s problem was one such.

The Solution

The option to display the barcodes for a logged-in OPAC user’s checkouts (issues) is driven by the SHOW_BARCODE patron attribute. In Koha, patron attributes or more correctly ExtendedPatronAttributes are library-defined custom fields that can be applied to patron records e.g. voter / aadhaar card number, registration number etc.

SHOW_BARCODE is a boolean variable that is defined as either Yes or No. and it is loaded into Koha usually during the web-installer phase of Koha’s installation from the optional SQL dump file: /usr/share/koha/intranet/cgi-bin/installer/data/mysql/en/optional/patron_atributes.sql in Debian package based installations. The tiny file contains a single SQL INSERT statement:

INSERT INTO `borrower_attribute_types` (`code`, `description`, `repeatable`, `unique_id`, `opac_display`, `staff_searchable`, `authorised_value_category`) VALUES (‘SHOW_BCODE’, ‘Show barcode on the summary screen items listings’, 0, 0, 1, 0, ‘YES_NO’);

In the case of Karnavati University this optional patron attribute was not imported during the *original* installation done by the 3rd party support provider at the time. And without “SHOW_BARCODE” being set, Koha had no way of displaying the barcodes of checked out books to patrons logged in via the OPAC.

In the end, the following two lines followed by enabling the ExtendedPatronAttributes system preference cleared off the issue for Karnavati:
$ cd /usr/share/koha/intranet/cgi-bin/installer/data/mysql/en/optional
$ mysql -uroot -p koha_karnavati < patron_atributes.sql.

A memcached restart later (to be safe rather than sorry) the OPAC started showing the barcodes to logged-in patrons.

Using an office suite to generate the sql for Koha’s calendar

An in-house technique we use for fast-track, accurate loading of the long list of Indian holidays into Koha.

LEGAL DISCLAIMAR: This tutorial is strictly meant for educational purpose. We are in no way responsible if you attempt to follow these steps and end up messing up your production Koha installation somehow.

Setting up yearly calendars is an important task in Koha. Especially in a country like India where we also use other calendars including lunar calendars. Since the religious festivals / holidays usually use these other calendars, the holidays often fall on different days in different years in the Gregorian calendar that Koha uses.

As support providers, we are often asked to setup the calendar for our client-partners. While Koha provides a rather useful user-interface to setup calendars under the Tools menu, we have often found that not only setting up the long list of Indian holidays a tedious affair, it is also quite prone to operator error. So for the Unique holidays we usually use a LibreOffice (a Free Software Office Suite) Calc spreadsheet to quickly generate the batch of SQL statements that can be directed imported into the special_holidays table of Koha using the SQL backend.

We ask our client-partners to send us a spreadsheet that lists the holidays and the days they fall on according to the Gregorian calendar (see the columns marked in yellow above). And then we use a couple of simple formulae to generate the necessary SQL. This approach has two advantages : (a) it’s very fast and (b) completely free of errors from our end.

In this present case, it took us less than 2 minute to upload all the 25 holidays for 2020 into this particular Koha instance.

Caveat The only risk here is that we are going to access the database directly from the command-line and that, unless you know what you are doing, can end up badly, *if* you mess things up.

Getting LE certbot-auto to work on an aging Debian 7.x

Stuck with Debian 7 in 2020 and need certbot-auto to work? Here’s how we did it.

Yes, it is 2020 and it is very late in the day to be still using Debian 7.x. But you just may have a piece of critical infrastructure that is still running on that Debian 7 box and moving it may not be an immediate possibility. Your infrastructure component also happens to use LetEncrypt certs for SSL. Your certificate has just expired and you ran certbot to issue a new certificate. And BAMMMM! you hit this!

Replacing certbot-auto…
Creating virtual environment…
Installing Python packages…
/opt/eff.org/certbot/venv/bin/python: No module named pip.__main__; ‘pip’ is a package and cannot be directly executed
Traceback (most recent call last):
File “/tmp/tmp.BLzjDMi7yW/pipstrap.py”, line 177, in
sys.exit(main())
File “/tmp/tmp.BLzjDMi7yW/pipstrap.py”, line 149, in main
pip_version = StrictVersion(check_output([python, ‘-m’, ‘pip’, ‘–version’])
File “/usr/lib/python2.7/subprocess.py”, line 544, in check_output
raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command ‘[‘/opt/eff.org/certbot/venv/bin/python’, ‘-m’, ‘pip’, ‘–version’]’ returned non-zero exit status 1

Well, we did. The problem stems from the fact that with certbot-auto version 0.32 it stopped working with EOLed Linux distributions. This has hit distros like Debian 7.x that EOLed towards the end of 2018 which also dropped official certbot support. Debian 7.x (wheezy) uses an ancient version of pip that cannot be run as a module (python -m pip). And hence the mess.

This is how we got over it for the moment (it is Jan 2020 at the time of writing)

1. rm -rf /opt/eff.org

2. Download old 0.31 version of certbot-auto so that we can get around the version issueswget https://raw.githubusercontent.com/certbot/certbot/75499277be6699fd5a9b884837546391950a3ec9/certbot-auto

3. chmod +x ./certbot-auto

4. Run certbot-auto with the necessary switch ./certbot-auto --no-self-upgrade

And this was the result

Bootstrapping dependencies for Debian-based OSes… (you can skip this with –no-bootstrap)
Hit http://archive.debian.org wheezy Release.gpg
Hit http://archive.debian.org wheezy Release
Hit http://archive.debian.org wheezy/contrib Translation-en
Hit http://archive.debian.org wheezy/main Translation-en
Hit http://archive.debian.org wheezy/non-free Translation-en
Hit http://archive.debian.org wheezy/main amd64 Packages
Hit http://archive.debian.org wheezy/non-free amd64 Packages
Hit http://archive.debian.org wheezy/contrib amd64 Packages
Hit http://archive.debian.org wheezy/main i386 Packages
Hit http://archive.debian.org wheezy/non-free i386 Packages
Hit http://archive.debian.org wheezy/contrib i386 Packages
Reading package lists… Done
Reading package lists… Done
Building dependency tree
Reading state information… Done
gcc is already the newest version.
python is already the newest version.
python-dev is already the newest version.
python-virtualenv is already the newest version.
openssl is already the newest version.
libffi-dev is already the newest version.
libaugeas0 is already the newest version.
libssl-dev is already the newest version.
ca-certificates is already the newest version.
augeas-lenses is already the newest version.
The following packages were automatically installed and are no longer required:
libapache2-mod-fcgid libcarp-assert-more-perl libcarp-assert-perl libcgi-compile-perl libcgi-emulate-psgi-perl libdevel-stacktrace-ashtml-perl
libfcgi-procmanager-perl libfile-pushd-perl libfilesys-notify-simple-perl libfreeradius-client2 libhash-multivalue-perl libhtml-lint-perl libhttp-body-perl
libmodule-refresh-perl libplack-perl libtest-longstring-perl libtest-requires-perl libtest-sharedfork-perl libtest-tcp-perl rt4-apache2 rt4-clients rt4-db-sqlite
Use ‘apt-get autoremove’ to remove them.
0 upgraded, 0 newly installed, 0 to remove and 155 not upgraded.
Creating virtual environment…
Installing Python packages…
Installation succeeded.
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator apache, Installer apache

Which names would you like to activate HTTPS for?
– – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Shared in the hope that it may help someone else in a similar position.