IUG 2023

👋 Welcome!

Hello, IUG 2023! Here you’ll find all the notes, links, and goodies for my presentations. Besides the usual slide decks and notes, you’ll find links and resources to help you go even further with anything I brought up in my talks. You’ll find my contact info below so, if you have questions, feel free to drop an email, message me on Mastodon, or hit me up on Discord!

✉️ Email: cyberpunklibrarian [at] protonmail [dot] com

🐘 Mastodon: CyberpunkLibrarian on Hackers.Town

💬 Discord: CyberpunkLibrarian#7569 – Want to join the IUG Discord? Drop me an email or a message on Mastodon!

🤓 Useful Polaris SQL: A repository of various Polaris SQL queries to help you on your way. Available online as a website and a GitLab repo.

🧑🏽‍💻 Beginner’s Guide to System Administration for Polaris

With Katie LeBlanc and Daniel Messer

Description: In this session we will be covering the basics of getting started as a System Administrator for your Polaris library.  Do you need tips on setting up staff accounts with permissions or creating new collections? We will be dipping our toes into best practices for some of those pesky policy and database tables.

Slide Deck – PowerPoint | PDF

Outline – PDF

Polaris Online Documentation – Download all the manuals!

Polaris Training from Innovative – Training services and certifications.

🫣 SQL for the Skittish

Salvage your sanity beyond SELECT

Description: New to Polaris SQL? How’s it been going? Okay? Excellent! You’ve been writing some queries, doing some SELECT statements to get that sweet, sweet data? Awesome! Now, all we need you to do is UPDATE a couple of things and would you mind DELETEing a few rows from this table?

I used to get nervous whenever I had to actually change something in the Polaris database and, guess what? I still do. But I have several strategies to stifle SQL squemishness – and maybe they can help you too.

Slide Deck – Keynote | PowerPoint | PDF

Outline – PDF

Azure Data Studio – Better, faster, possibly stronger than SSMS. An excellent cross-platform T-SQL environment.

High Color Queries (HCQ) – My favourite theme for Azure Data Studio. Neon look but also that bright text on dark background contrast is incredibly pleasing to my eyes. Installation instructions.

Azure Data Studio Extensions – Add new and increased functionality to Azure Data Studio! You can do it right from the app itself.

SQL Server Express – Run your own SQL server! Pull copies of tables from production and try your code without fear of messing up the real data.

ChatGPT – Because sometimes, AI can help you get where you want to go.

Copying Polaris Tables to a Local SQL Express Database – A quick, illustrated, step-by-step guide to copying tables from a Polaris database into a local SQL Express database running on your own computer. That way you can work with that data as you would on the server, without the worry of anything going wrong with your actual data.

This video offers a quick and easy look at the process of BEGIN TRAN, COMMIT, and ROLLBACK.

UPDATES:

Some folks at the SQL talk asked if I’d post the prompts and results of my interaction with ChatGPT when I was experimenting with its ability to write T-SQL code for the Polaris databases.

This first prompt was based off an interaction between Wes Osborn and Jeremy Goldstein on the IUG Discord server. Jeremy was looking for a way to manage some mischief with his call numbers and his problem intrigued me. Here’s his prompt, looking for an answer in Postgres. For accessibility, his prompt was:

Given a free text field named call_number, can you write a postgres sql query to identify the
most common phrases that appear at the start of that field?

I modified his idea slightly to work with Polaris and asked the following question:

Given a free text field called CallNumber, in a table called Polaris.ItemRecordDetails, can you write a T-SQL query that identifies the most common phrases that appear at the start of the field?

The result that came back wasn’t quite what I was after, but it was more than good enough to get me started:

SELECT TOP 10
    LEFT(CallNumber, CHARINDEX(' ', CallNumber + ' ') - 1) AS Phrase,
    COUNT(*) AS Count
FROM
    Polaris.ItemRecordDetails
WHERE
    CallNumber LIKE '% %' -- to exclude records without spaces
GROUP BY
    LEFT(CallNumber, CHARINDEX(' ', CallNumber + ' ') - 1)
ORDER BY
    Count DESC;

The second time I used the tool, I was looking to add a new library into the Item Availability Display table. Now, to be sure, I just wanted to get that library into the table with a basic SortOrder and System/Local setup. Tweaking it by hand would be required but that’s a whole lot better than adding it by hand in SA over a table full of 90-some-odd libraries. I also only wanted to add the library for certain branches/library in the table. So my query would be calling on a temporary table of OrganizationIDs pulled from a SELECT statement. That was easy, but I was having trouble with the logic involved with the INSERT so I figured, why not? Let’s ask the AI:

Given a database table called Branches with a column of integers, can you write a T-SQL query that inserts those integers along with other values into a different table, looping through the process until all values from the Branches table have been inserted along with the other data?

DECLARE @BranchID INT
DECLARE @OtherValue1 VARCHAR(50)
DECLARE @OtherValue2 INT
DECLARE Branches_Cursor CURSOR FOR 
SELECT BranchID FROM Branches
OPEN Branches_Cursor
FETCH NEXT FROM Branches_Cursor INTO @BranchID
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO NewTable (BranchID, OtherValue1, OtherValue2)
    VALUES (@BranchID, @OtherValue1, @OtherValue2)
    
    FETCH NEXT FROM Branches_Cursor INTO @BranchID
END
CLOSE Branches_Cursor
DEALLOCATE Branches_Cursor

Once again, I had to make some changes, but this was enough to get me started. Asking the AI saved me at least an hour of trying, checking, fixing, and trying again.

Some other attendees were interested in the text replacement apps I recommended. I use aText on macOS but TextExpander is also very popular. For Windows, try Phrase Express, and you’ll find it’s got the same functionality. At least one person asked about my triggers and replacements, here’s what I use for SQL coding:

I also have some triggers to produce full SQL code. For instance, btd> will give me a Better Transaction Details layout for a given TransactionID. For others, I have some quick drops to get some basic information that I always seem to need like:

porgs>

SELECT * FROM Polaris.Polaris.Organizations WITH (NOLOCK)

sipports>

SELECT
    ssp.OrganizationID AS [Organization ID],
    o.Name AS [Library],
    ssp.TCPIPPortNumber AS [Port]
FROM
    Polaris.Polaris.SIPServicePorts ssp WITH (NOLOCK)
INNER JOIN
    Polaris.Polaris.Organizations o WITH (NOLOCK) ON o.OrganizationID = ssp.OrganizationID
ORDER BY
    [Library] ASC

Seriously, once you figure out what a text replacement app can do for you, it’s life changing. I use this software for everything from my email signature to boilerplate text for help desk tickets.

🖥 Wait, You Use a Mac?

Polaris administration from the other side

Description: It’s morning. I arrive on-site to help manage some mischief. After a quick conversation with the staff, I get to work. I open my backpack and pull out my beloved MacBook Pro. And one of the librarians says:

“Wait, you use a Mac? I thought Polaris was a Windows thing.”

This happens to me a lot. But as a Polaris admin, you have choices. You can absolutely work all aspects of Polaris with a Mac or an iPad. I know, because I’ve done it for years. Discover for yourself how you can GTD with Apple.

Slide Deck – Keynote | PowerPoint | PDF

Outline – PDF

Setapp – If you’re a Mac user and you’re looking for a good deal on a tonne of useful apps, you might want to consider a Setapp subscription. There are a lot of apps I regularly use that I got from Setapp.

Windows as a Service – You can have your Mac and Windows too, without dual booting or anything like that. It’s pretty easy to set up a VNC server on a Windows computer and then connect to it via Screen Sharing on your Mac. TightVNC has been around for years and it’s a free and open source solution for remote desktop access. Or, if you want something a bit more local, you can run Windows virtually through software like VirtualBox.

Microsoft Remote Desktop for macOS – The same RDP you know and love.

Micosoft Outlook – Well, I guess you have to. The macOS Mail.app isn’t too good right now and besides, so many libraries and enterprises use Exchange Server. At least it’s free these days!

iTerm – You are a professional library person. You deserve a professional terminal so you can do professional things on the command line, professionally.

Tot – What if you had a notepad that quickly and seamlessly syncs to all of your Apple devices? Someplace to write out a ticket update that you’ll paste into a web form? Or keep a checklist? Tot is your app.

CleanShot X – This app takes screenshots to the next level. So many features and configurations. Also available on Setapp.

Azure Data Studio – SSMS is slow and cumbersome. If you’re just writing and running some SQL, ADS is your new friend.

Visual Studio Code – Do you write code in Python? VSC handles that. PHP? Sure, that too. C#? Why not? Batch scripting? Of course. JavaScript? Yeah, I mean… if you want to.

Firefox – My favourite operating system.

Notion – Note taking, knowledge management, databases, kanban, and lots of other stuff.

TickTick – A to-do list and task management system that (mostly) keeps me on track.

🐧 My Linux Server

My Linux server runs as a virtual machine in VirtualBox. The server software itself is a Debian based system called Yunohost, an operating system designed from the ground up to make running and hosting a server as easy as possible. All of the software below runs within the Yunohost OS.

VaultWarden – Secure your passwords!

Nextcloud – Better than Dropbox and OneDrive for file sync across any operating system. Also offers collaboration tools, apps, and more.

GitLab – Version control for code, text, and other projects. You can have a local instance or use the public website.