You are writing some Javascript code in the browser and you create classes, then you create some methods, which you see have some issue with the "this" keyword by default if you use the standard method declaration so you end up doing something like this:

// instead of myMethod() { ... }
myMethod=()=>{
  const myClass=this;
}

And this (pardon the pun) works here. Imagine my surprise when I did something that seemed identical:

// instead of myMethod() { ... }
myMethod=()=>{
  debugger;
}

And then I tried to see what "this" was in the Chrome Developer Tools. It was "undefined". Why?!

Long story short, I then tried this (heh!) and it worked:

// instead of myMethod() { ... }
myMethod=()=>{
  const myClass=this;
  debugger;
}

The moral of the story is that "this" is not declared unless used in code. Probably a browser optimization. Hope it saves you the ten minutes it took me to understand what was going on, after I've started doubting my entire JavaScript expertise and the entire design of my app.

As you know, LiChess Tools is my own Chromium browser extension for lichess.org, adding a lot of stuff to it. Recently, from version 2.2.0 on, I've added a new feature called Show Pawn Structures. This post explains in detail what it is, what it does and how it does it.

Pawn Structures

What is a pawn structure? The configuration of pawns on the chessboard. Because pawns are the least mobile of the chess pieces, the pawn structure is relatively static and thus plays a large role in determining the strategic character of the position. It is a powerful chess concept that is mostly ignored on amateur level and I've met 2000+ rated players who didn't know what that was. Not that I know, either, which is why I got so excited to build this feature because it would further chess understanding and learning. With different structures come medium term plans, so instead of having the opaque engine recommendations of making one move or another, you will have a general idea on where to take the game to.

The above is the chess definition of the concept, though. In order to work with it in an algorithm it has to be clearly defined. The difficulty here lies in the fact that while the pawn structure is "relatively static" its meaning is not. While you will be shown a specific pawn configuration in the context of a named structure, it would be implied that other similar configurations also belong. That similarity being not precise, but something nebulous related to the general ideas and themes that are made possible by the structure.

Feature requirements

The purpose of the feature is to determine the pawn structure of a position in either game analysis, analysis board, studies, TV games and mini-games (the things that appear when you hover on a playing user link or in the Current Games section), then display it, similar to the Show Opening feature. The reasoning here is that one can learn to classify positions and thus know the general plans that apply in the situation.

Technical details

There is a list of pawn structures that LiChess Tools supports. The list is at the end of this post. In order to the structure I created a textual representation of them, that looks something like this: 312100TX 0X0210 2020 XXLXXX XXXXXX XXXX. A bit daunting, but the most important part is the first group of characters: 312100TX.

The idea is that the first characters are the most significant, so similar pawn structures would start with the same letters and digits, even if they diverge later on. Based on the structures detailed by various books on the matter, I've considered that the position of the d-pawn is the most significant, followed by the e and c pawns, then the pawn majority on the White and Black sides, followed by the other pawns: f,b,g,a,h. The final part is doubled or triple pawns, which most of the time is irrelevant.

So let's get back to the complicated string above: 312100TX 0X0210 2020 XXLXXX XXXXXX XXXX (we will assume White board orientation)

  • 312 - my pawn position on the d,e,c files: d5 (3 squares forward), e3 (1 square), c4 (2 squares) - the possible characters for this group are X (missing pawn), 0 (unmoved pawn), 1,2,3 (squares the pawn is forward from its starting position)
  • 100 - their pawn position on the d,e,c files: d6, e7, c7
  • TX - the majority on the queenside and kingside: T (they) have majority on the queenside, and equality on the kingside - the possible characters for this group are M (me), T (them) or X (neither)
  • 0X0 - my pawn position for the f,b,g files
  • 210 - there pawn position for the f,b,g files
  • 20 - my pawn position for the a,h files
  • 20 - my pawn position for the a,h files
  • XXLXXX XXXXXX XXXX - similar to above groups, doubled or tripled pawns - X for no, L for yes

Most pawn structures are exclusively about the position of the c,d and e file pawns, therefore the first group of characters will be the most determining one. This may change in the future, perhaps, as better chess players than me will point out issues with this system, but for the moment this is how it works.

Based on this string we can compare the current pawn position to the one of the named pawn structures. There are three options in the Preferences for this feature:

  • Enabled - if this feature is on or off
  • Only named structures - will only display the named structures if enabled. If not, then the name will be displayed as the most significant group of characters in the structure representation. On mouseover, the title will show the entire thing as well as the most similar named structure found.
  • Fuzzy - if this feature is on, then a structure will be considered named if 90% similar to the standard one.

The feature will invert the structure and look for a name there if not found for your own orientation. If a name is found there, "(R)" will be added to the name. 

Note that the most named structures are represented by the most significant group only, and only several of them by the first two or three groups of characters. The rest is ignored.

Similarity

Now, how do we compute the similarity? It's a work in progress, but at the moment it works like this:

  • the number of common characters on the same position from the beginning of the text are counted as 1
  • the number of other common characters on the same position (so after any position where the characters were different) are counted as 0.8
  • the number of positions where the current structure has pawns on squares that may allow moving them on the named structure square for that position (so where the value is numerical and smaller than the numerical value of the named structure) are counted as 0.2
  • the percentage of the resulting sum from the characters counted is returned as the result

Example: Carlsbad vs Slav
21X2X0TM
21XX11TX
11100280 (8 denotes 0.8 and 2 denotes 0.2 here) = 4/8 = 50%

It may be that this is not the best way and it might be changed in the future.

List of structures

A small note on naming: different sources name these differently or don't recognize them as structures at all. I did what I could to give a short identifiable name to each position, but I think sooner or later I will have to restrict the number of names, rather than increase it. We'll see.

So here is the list of pawn structures recognized by LiChess Tools (v2.2.3):

The links above are also used in LiChess Tools and are mostly from Wikipedia, but also some approximations or just other random sites because there are no links for many of the Flores structures. I would be very happy if someone would help me clean these up.

Hope this explains everything. Enjoy!

Note: This article is about Chromium based browsers.

Remember the days when computers were configured to present the filesystem to the network by default in read/write mode? Those were the days. Today, though, everything is configured for security and browsers are no exceptions. One thing that annoyed me yesterday was CSP (Content Security Policy) which disallowed me to fetch from a web site information from another web site which was happy to provide it. The culprit was a meta tag that looks like this:
<meta http-equiv="Content-Security-Policy" content="...">

The content was configuring default-srcconnect-src, style-src, frame-src, worker-src, img-srcscript-srcfont-src! Everything. But I wasn't looking for a hack to disable CSP (well I was, but that's another story), I just wanted to test that, given a friendly CSP, I could connect to a specific web site and get the data that I wanted and do something with it. Surely in the developer tools of the browser I would find something that would allow me to temporarily disable CSP. No such luck!

Then I looked on the Internet to see what people were saying. All were complaining about "Refused to connect to [url] because it violates the following Content Security Policy directive..." and how it annoyed them, but there was no real solution. Here is what I found:

  • browser extensions to remove the CSP header
    • I assume this works, but it wasn't my case
  • browser extensions to remove content from the page from the Developer Tools
    • I tried one, but when it changed the content now the browser was crashing with an ugly Aw, snap! page with a Status_Access_Violation status
  • I tried ticking the web site's settings for Insecure content
    • How naïve to think that it would allow loading of insecure content
  • I tried browser command line flags and experimental flags
    • nothing worked

I was contemplating hacking the browser somehow when I stumbled upon this gem: Override files and HTTP response headers locally.

It is almost exactly what I was looking for, only it doesn't replace content with regular expressions but saves the entire content of a URL on the local drive and serves it from there, modified in whatever way you want. So if you want to alter a server rendered page you're out of luck.

How did I use it to remove the CSP? I went to sources, I configured the local overrides and I then edited the page (in the Sources panel) and simply deleted the annoying meta tag. Now it worked.

Hope it helps!

Some time ago I wrote a post about the difference between temporary tables (#something) and table variables (@something) which concluded that before SQL Server 2019 table variables sucked. But I didn't give any real example. I made it very technical and clinical, perhaps creating the wrong impression that it's a matter of minute optimizations and not something somebody should know.

So gather 'round, children, and let me tell you the tale of the dreaded table variable! How a query that timed out after an hour was run in just 3 seconds with a tiny little fix. The server version was... 2017 (fade to dream sequence)

First, the setup: I needed to delete rows from a large table (30+ million rows) which did not have an associated account in a calculated list of about 20 thousand. Simple right?

DECLARE @Accounts TABLE(ACC_ID CHAR(5))

INSERT INTO @Accounts
SELECT ... whatever
      
SELECT TOP 1 *
FROM MyTable t
LEFT OUTER JOIN @Accounts a
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL

Just get me that first orphaned record please... ... ... 40 minutes later... WTF?

As I was saying in the previous post, table variables do not have statistics and the engine assumes they have only one row. So what does the execution plan want? To go through all of the 30+ million rows and then search them into the accounts table. Only the accounts table has 20 thousand rows, too. And it takes forever!

Mind that I've simplified the case here. Table variables do allow indexes and primary keys. I tried that. No effect at all!

Let's change the query then:

CREATE TABLE #Accounts(ACC_ID CHAR(5))

INSERT INTO #Accounts
SELECT ... whatever
      
SELECT TOP 1 *
FROM MyTable t
LEFT OUTER JOIN #Accounts a
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL

DROP TABLE #Accounts

Just get me... wait, what? The query ended in 3 seconds. Suddenly, knowing the number and structure of the rows in the table led to the correct execution plan.

But can't I use table variables somehow? You can, but you have to force the engine to do it your way. First of all, you use OPTION (FORCE ORDER) which will keep the tables in the order you declared them. And then you have to reverse the JOIN so that @Accounts is the first table, but the effect is the same.

DECLARE @Accounts TABLE(ACC_ID CHAR(5))

INSERT INTO @Accounts
SELECT ... whatever
      
SELECT TOP 1 *
FROM @Accounts a
RIGHT OUTER JOIN MyTable t
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL
OPTION (FORCE ORDER)

Back to three seconds. Ugh!

Now, I will probably use a temporary table, because forcing the way the SQL engine interprets your queries is almost always a bad idea and it makes the code harder to read, too.

Hope it helps!

LiChess Tools is a Chromium-based browser extension (meaning Chrome, Brave, Edge, Kiwi - which also works on mobiles - and others, as long as they have a Chromium version equal or higher to 111) that extends the functionalities of the lichess.org web site. This page is the user manual for the extension, cataloguing all of its current features and explaining how to use them.

All of the features below can be individually turned on or off from the Preferences menu, the LiChess Tools menu entry. Note that many of the options will be hidden until you toggle Advanced Preferences - these features will be marked with Advanced in the category section.

Here are the available features in LiChess Tools v2.3.27 in the order of decreasing approximate importance:

Add me to LiChess Tools team

category: Community
values: Hide forum entry - defaults to empty

This will add a new forum entry in the forum list from where you can join the LiChess Tools users team or, once joined, you can participate in the extension community.

This feature was defaulted to on in v2.3.1 and some people noted that automatically joining someone to a team, thus advertising this extension on the timeline for all your friends is unethical. I agree, so I removed that implementation. Initially I thought that you either get a mandatory "do you want to...?" popup or an optional out from the team, so less of a hassle and didn't consider the messages in the timeline. 

Sorry to all the people that felt slighted by the original design!

Options:

  • Hide forum entry - If set, this will remove the forum entry suggesting you join the LiChess Tools users team

Being part of the team will be advertised on your public profile as well as on your friend's timelines. That is great for LiChess Tools, so if you want to support the extension, please consider joining.

I don't plan to spam anybody in the team, just use it to announce various changes to the extension. Which are a lot and happen all the time and might be construed as spam... OK, I will not spam you much. And also I would like to hear from you, maybe even (gasp!) form a community that will take this further than my meager imagination could take it.

While in the team, feel free to talk to the other users, ask for features, complain, all the jazz. Use the team's forum as one would use the official Lichess Feedback forum. Only less divisive :). And don't worry, the team is called L1Chess Tools because Lichess forbids the text "lichess" in the team's name, not because I plan a L2Chess Tools team or whatever.

Although that is an idea...

Extended interactive lessons

category: Study
values: Play all variations/Show score/Study links in comments in same window/Play again from where you entered Preview mode - defaults to Play all variations, Show score, Study links in comments in same window

This feature may be the flagship of the extension, as it allows you to play an Interactive lesson chapter by going through all variations instead of just the mainline. The default lichess behavior is to only follow the mainline, considering all sidelines as bad. With the new behavior all moves in the PGN tree are good, while the ones missing are bad. You can mark the bad variations with glyphs (good move, mistake, blunder, etc.), for example, to indicate if a move is bad or good, but you can continue playing it to see why. 

There is a mechanism to choose what the computer will play next as your opponent. It uses the number of branches in 8 ply (4 chess moves for both players) to determine which is more probable. This value can be changed (see Next move probability depth feature) For example if you have something like 1. e4 (1. d4 d5 2. Nc3 (2. Nf3)) 1... e5 2. Nf3, the mechanism will see that there are two possible branches for d4 as opposed to one for e4, so it will choose d4 66% of the time. The probability can be changed manually by adding a comment in the move with the format prc:<number>, where the number is a percentage between 1 and 100. (ex: prc:90) Also, if set in the Transpositions behavior feature, the moves can be picked from moves following transposing positions. 

When hints are not provided by the study creator, one will be generated to show how many possible moves are available.

There is also a system to compute accuracy when playing an interactive lesson by counting the good and the bad moves. This will be displayed as a percentage at the end of the lesson and can be turned off by option.

The lichess UI remains the same, therefore the area in which to explain why any other move is wrong only appears on mainline moves. That is why this feature also adds a context menu item (Explain why other moves are wrong) for nodes in interactive lessons to enable editing that information. As usual, when a "bad" move is made (meaning a move not in the PGN), the text to be displayed to the user will be taken from the first next move from the current position. Therefore in order to make this work you have to right-click that node and explain why bad moves are wrong. This might be a bit counterintuitive, as you may have several moves following the current position, but the explanation has to be set to the first next move.

This feature also adds a header like Extended Interactive Lesson over the edit controls specific to interactive lessons. They usually take a lot of space, so clicking on this header will hide/show them. This allows people to edit Interactive Lesson chapters just as comfortable as normal ones.

Options:

  • Play all variations - enables the Extended mode for interactive lessons, allowing you to play all variations with the computer picking random moves from the ones available in the PGN
  • Show score - shows the score at the end of the lesson. One can disable this to reduce frustration or for other reasons.
  • Study links in comments in same window - this makes links from move list comments which lead to studies open in the same window. The reason for this is that you want to continue a variation that is split into multiple chapters or that leads to another study in the same window, especially in Extended Interactive Lesson mode. Now you can utilize this with the Bookmarks option of the Move List Options feature to jump from move to move and chapter to chapter.
  • Play again from where you entered Preview mode - this feature is disabled by default. It remembers the position where you entered Preview mode for an Interactive Lesson and, when the lesson ends, the Play again button will take you back to that position instead of the first move in the chapter. Obviously, this only works for creators or contributors to studies.

These options are also available in the action menu (hamburger button) of the Analysis board when in a study chapter that is of type Interactive Lesson. This allows to quickly enable/disable just these relevant settings.

Notes:

  • one can play all variations of an interactive lesson with multiple branches only with the LiChess Tools extension installed, so if you create such a lesson only people with the extension can enjoy it. It would be nice to let everybody know that in a comment if you share it.
  • asking to show the next move will show arrows of all possible next moves. Be warned that pressing the button is seen by the scoring system as a fail.

Extended interactive lesson flow

category: Study - Advanced
values: Sequential/Spaced Repetition - defaults to none

Work in progress!

This feature allows you to play Extended Interactive Lessons variation by variation. When any of these options are enabled, the chapters that had variations played will have a small progress indicator in the chapter item in the chapters list. When all variations have been played a reset prompt will appear. You can also manually reset the played variations from the chapter edit dialog for the studies you can modify and from the reset button on the chapter item for the ones you can't.

Options:

  • Sequential - this will force playing extended interactive lessons variation by variation, in order
    • if you make any mistakes, you will start over
    • if you don't play from the very beginning of the variation, it will not count
  • Spaced Repetition - this will force playing extended interactive lessons variation by variation, randomly, based on success or failure
    • if you finish a variation successfully, the time until you can play it again will double
    • if you finish it with mistakes, the time until you can play it again will become two days
    • if you don't play from the very beginning of the variation, it will not count  
  • Both - this will behave as Spaced Repetition, but will take variations in order
    • the distinction is small, but it matters to some players to get the first variation that can be played

Notes

  • The default replay interval is one day.
  • This is a work in progress. It might not work exactly as you expect it. Please report any issues to me.

Ctrl-right to play random next move from list

category: Analysis/Study
values: yes/no - defaults to yes

This feature will allow you to play a random move by pressing Ctrl-right. There is a mechanism to choose what the computer will play next as your opponent. It uses the number of branches in 8 ply (4 chess moves for both players) to determine which is more probable. This value can be changed (see Next move probability depth feature) For example if you have something like 1. e4 (1. d4 d5 2. Nc3 (2. Nf3)) 1... e5 2. Nf3, the mechanism will see that there are two possible branches for d4 as opposed to one for e4, so it will choose d4 66% of the time. The probability can be changed manually by adding a comment in the move with the format prc:<number>, where the number is a percentage between 1 and 100 (ex: prc:90). Also, if set in the Transpositions behavior feature, the moves can be picked from moves following transposing positions. 

Ctrl-left works almost like the left key, going to the previous position, but in this case it will go to the position you came from, not from the previous position on the line you've jumped to.

Notes:

  • if set in Transpositions behavior, Ctrl-right may choose a move from another line, but made from the same position, which means that going "back" will go to the previous position in the current line, not the original one. Use Ctrl-Left to return to the position you jumped from. 
  • the keyboard shortcuts will be shown in the keyboard help popup if this feature is enabled

Behavior of transpositions

category: Analysis/Study - Advanced
values: exclude if same line/don't add identical next moves/play moves from transpositions - defaults to none

This feature determines what a transposition is for other features that use transpositions. The options are:

  • exclude if same line - two identical positions in the same PGN will not be considered transpositions of one another if they are in the same line of moves (one came before the other) (see Highlight moves in analysis and Show next moves from transpositions)
  • don't add identical next moves - when showing next possible moves at the bottom of the move list, if adding them from transposing positions, don't add the same move twice (see Show next moves from transpositions)
  • play moves from transpositions - when picking a next move to play, use transposing positions as well (see Ctrl-right to play random next move from the list and Extended Interactive Lessons)
  • none - when none of the values above are selected, any two identical positions will be considered transpositions, the move list can show multiple identical moves if they are coming from different transpositions/lines and the extension will not pick next moves using transpositions.

Next move probability depth

category: Analysis/Study - Advanced
values: equal/one move/two moves/three moves/five moves - defaults to 4 moves (8 ply)

This configures the mechanism calculating the probability of a next move in the PGN (see Extended Interactive Lessons and Ctrl-right to play random next move from list). The default value goes 4 moves for each player to see how many branches follow. It will then weigh each next move in the list based on that. If you want to turn this off completely, just select equal, which means it will not look at following moves at all. 

Variation arrows from transpositions

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

This feature is showing variation arrows for transpositions, as defined in the Transpositions behavior feature.

PGN Editor

category: Analysis
values: yes/no - defaults to yes

This feature adds a new entry in the Tools menu called PGN Editor. Clicking on it will open a text editor where PGNs can be pasted, manipulated and copied from.

Functionalities:

  • Merge - this button will take multiple games in PGN format and merge them into as little games as possible. For example, all games starting from the same position can be merged into one. Also, games that start from a position present in another game will be merged there. Games starting from positions not present in the others cannot be merged.
    Example: 
    Original PGN content:
    1.e4 e5
    
    1.e4 d5
    
    After merge:
    1. e4 e5 ( 1... d5 ) *
    ​
  • Normalize - this button will, for each game in the PGN, take all moves made from a particular board position in any variation and group them in the first occurrence of that position.
    Example:
    1. e4 (1. d4 e5 2. e4 a5) 1... e5 2. d4 h5 
    
    (After both paths e4 d5 d4 and d4 d5 e4 the same position is reached (a transposition). In the original PGN the first path move from the common position is 2...h5 and in the second path 2...a5.)
    
    Normalizing this will result in:
    
     1. e4 ( 1. d4 e5 2. e4 ) 1... e5 2. d4 h5 ( 2... a5 ) 
    
    (both a5 and h5 moves from the mainline position, while the d4 path will stop at the common position, with no following moves.)
  • Split - this button will, for each game in the PGN, split each variation into their own games. Example: 1. e4 e5 ( 1... d5 ) will be split into 1. e4 e5 and 1. e4 d5
  • Search - this button will prompt for a string, then search the games for it.
    • The string can be part of a FEN or a PGN (wildcards * for anything and ? for one character are supported)
    • Another option is to search for PGN tags, like this: Black=totalnoob69 or Opening=Elephant Gambit.
    • Yet another option is to search for ply (half-moves), like this: ply=10, ply<30, ply>15

The games that match will have a new PGN tag added (called Found) in the text area and also will be copied to the clipboard. Searching will remove previous search Found PGN tags. 
Do not use full FEN strings, only the first four elements (board, who's turn it is to play, castling and en passant square) are supported (no clock values)

  • Examples:
    • searching for rnbqkbnr/pp2pppp/2p5/3p4/2PP4/5N2/PP2PPPP/RNBQKB1R b KQkq - 1 3 will fail (because it has half clock and ply at the end)
    • searching for rnbqkbnr/pp2pppp/2p5/3p4/2PP4/5N2/PP2PPPP/RNBQKB1R b KQkq - would work for the games that reach that position
    • same for:
      • rnbqkbnr/pp2pppp/2p5/3p4/2PP4/5N2/PP2PPPP/RNBQKB1R - FEN search
      • pp2pppp - FEN search
      • rnbqkbnr/*/2p5 - FEN search
      • 5?2 - FEN search
      • ECO=C40 - PGN tag search
      • c4 c6 - PGN search
      • ply=10 - search games with exactly 10 ply (5 moves for each side)
      • ply>30 - search games with more ply than 30
      • ply<20 - search games with less play than 20
  • Result - this button will remove all games that are not marked with a Found tag and remove the Found tag from those found. The flow is: you press Search, it finds some games and marks them with a Found tag, you press Result and only the found games remain in the text area.
    • Warning! If you have not searched for anything before (or if you press the button twice), it will clear the text area
  • Cut - this button will remove things based on the prompt you give it:
    • if it contains "tags" it will remove all tags from games
    • if it contains "comments" it will remove all comments from games
    • if it contains "annotations" it will remove all annotations from games
    • if it contains "ply <some value>" it will remove everything after the specified number of ply (half moves), on every branch, in every game
    • the above patterns can be combined
    • Example: tags, comments, annotations, ply 10
  • Count - this button will count the number of games and total number of moves in them
  • Cancel - this button will cancel the current operation (for example when trying to merge tens of thousands of games, it may take a while)
  • Copy - this button copies the content of the text area in the clipboard
  • Upload - this button will ask for a file to upload to the text area (dragging files and dropping themdirectly to the text area works as well)
  • Download - this button will download the content of the text area as a pgnEditor_<currentTime>.pgn file
  • Undo - this button will undo the changes in text area (equivalent to pressing Ctrl-z)
  • Redo - this button will redo the changes in text area (equivalent to pressing Ctrl-y)

Notes:

  • Normalize changes the flow of the moves. Don't expect the normalized PGN to have the same structure as the original. This functionality is particularly for grouping the moves from a particular position in one place.
  • Tag search will search the exact string in the tag, no wildcards supported, but will ignore whitespaces.
  • Tell me if you want better search systems implemented, like regular expressions or searching of moves anywhere in the PGN or something like that.

Enhanced PGN import

category: Analysis
values: yes/no - defaults to yes

Another important feature of LiChess Tools, this one makes changes to the import mechanism in the Analysis board page. You can now import multiple PGNs at the same time and they will get merged into a single one. It is great for creating repertoires, analyzing the games of a specific player or creating Extended Interactive Lessons. From the Analysis board you can go to options and turn it into a study.

As part of this you will also get the Escape key functionality for the FEN and PGN inputs, blurring the input and allowing key shortcuts again.

Notes:

  • the import mechanism in Analysis board ignores comments from the PGNs. This is a lichess limitation and to get around it I would have to rewrite the entire thing. I may do that, but it's not planned.
  • the import in Analysis board is different from the one in Studies. The one in Analysis board is client based, while the one in Studies is on the server. That is why I can only alter the one in Analysis board.
  • there is no logical limit to how many PGNs to import, but the Analysis board has a limit of about 5000 moves. Also, the API to turn it into a study has a size limitation, so if you try to turn a huge analysis to a study and it fails, know that it is too big. Highlighting moves on huge PGNs also is slow. So use with care.
  • From v2.1.21 the merge mechanism will check if the PGNs start from different positions and only merge what starts from the same position. Previously the merge seemed to be working, but it made no sense from the standpoint of PGNs, resulting in an invalid PGN that cannot be loaded.

Friends box

category: Friends
values: Default/Menu/Open/Hidden - defaults to Menu

This has evolved a lot. Originally it was automatically opening the friends box, but then several requests came that complained about the placement of the friends box and how distracting it was. So the current version of the feature can either:

  • Default - the default behavior of the friends list: stays in the bottom-right corner, only updates when opened, which requires a user click.
  • Button- the friends box is removed and replaced with a friends button, similar to the Notifications one. It will update in real time with the number of friends online and will change color when any one of them is playing.
  • Menu - the friends box is removed and replaced with a friends menu. It will update in real time with the number of friends online and will change color when any one of them is playing. It will also show the number of playing friends in a small blue square.
  • Open - the default behavior of the friends list, but it will open automatically when loading the page
  • Hidden - the friends list will disappear completely

Notes:

  • clicking on the main menu item directly will open the friends page, but not on mobiles
  • when the width of the page goes under 972px, menus are grouped under a hamburger menu. The Friends menu will disappear and become a button in that case.

Live friends page

category: Friends
values: yes/no - defaults to yes

This is linked to the Friends box feature. The default behavior is to load whatever list of friends you have and then leave it at that. This feature will update the friends list in real time, while also allowing filtering by who is online, who is playing and (if player voice alerts are enabled) which player is muted. 

Each player will have a TV icon as well and (if player voice alerts are enabled) a mute/unmute button to include them in the alerts. (see Sound and voice alert with friends start playing)

From version 2.3.27, a cell showing the last time the player was active will be displayed.

Sound and voice alert when friends start playing

category: Friends
values: Ultrabullet/Bullet/Blitz/Rapid/Classical/Standard only- defaults to none

This feature will beep and spell out the player name and type of game they started. The type of the game can be chosen from the values. The definition of the game type comes from lichess, which assumes a typical game will be around 40 moves.

You can also choose which specific players to have the alert for, by going to the friends page and clicking the mute buttons. (see Live friends page)

If you select Standard only, then the alerts will only be sounded for the Standard variant of the game.

Notes:

  • Chromium (and other) browsers disallow sounds from page load until the user does something like click on the page. In case an alert is attempted and audio is not allowed a red icon will appear next to the top buttons, which will disappear once the audio is allowed again.
  • The audio icon is part of lichess, which will also display it once sound is attempted but not allowed. At this time, the lichess icon will NOT disappear unless you click on it, different from how LiChess Tools uses it for alerts.
  • If you are playing a game on another tab, the sounds will not be played if the Quiet mode on all tabs feature is enabled.

Practice against moves from Opening Explorer

category: Analysis/Study
values: yes/no - defaults to yes

This is another great feature that allows you to play against whatever is selected in the Opening Explorer. Select masters to play against the best players, select Lichess and select the range of player to train against and select specific users to test your strategies against their played games. The feature can be turned on/off not only from Preferences, but also from the Explorer config screen.

In order to use this feature you need to go to the Analysis board or a study, open the Explorer and click on the icon next to the Masters tab, the one that looks like an arrow hitting a target.

One extra feature of this tool is making a random Explorer move if clicking on the total row on the bottom. It effectively makes a move as if Explorer Practice is enabled and it's the computer to move.

The feature is unavailable in the Analysis screen of a running game.

Notes:

  • you can toggle Explorer Practice by clicking on the arrow in the target button, but also with shift-L, to make it easier to switch between computer evaluation and the practice mode
    • the keyboard help popup shows this shortcut if the feature is enabled
  • the moves in the explorer window will get hidden in Analysis board, but not in studies. The motivation being that one is more like a game, with no lasting effects, the other is research.
  • if the Show emojis when out of moves feature is enabled in Explorer Practice options, then at the end of a run, when there are no more moves available in the Explorer for the feature to make, a quick computer analysis will be run to show you a smiley (happy or sad).

Explorer Practice options

Options for the Explorer Practice feature:

  • Show emojis when out of moves - set by default - will run a short computer analysis when out of moves in Explorer Practice, then show an happy/sad emoji based on the eval.
  • Click Explorer Σ to make a move - set by default - clicking on the Total row in Explorer will make a random move, weighted by the probabilities of the move (basically a one turn Practice move)
  • Show opponent name - disabled by default - this will show a name next to the move made by the computer, giving you a hint about who played the same move in the same position

Highlight moves in analysis

category: Analysis/Study - Advanced
values: Last move in each variation/Not commented last moves/Transpositions to current move/Highlight board when out of main line/Highlight variation depth - defaults to Last move in each variation, Not commented last moves, Transpositions to current move

This feature will highlight moves in the analysis move list. Each option highlights a specific thing:

  • Last move in each variation - for each branch, highlights the last move
  • Not commented last moves - for each branch, highlights the last move that has no comment or is not a mate
  • Transpositions to current move - highlights positions that are identical to the current one in the same PGN (see Behavior of transpositions to change what the extension considers a transposition)
  • Highlight board when out of main line - dims the board a little when moving out of main line (similar to chess.com)
  • Highlight variation depth - it will change the display of the moves so that they have different colors depending on the variation depth level.
    • this replaced the Move colorizer theme that existed until version 2.3.
    • Warning: this may have adverse performance effects on really large PGNs (like 1000+ moves)

Extra context menu options

category: Analysis/Study
values: Copy branch as PGN/Engine evaluation for last moves/Highlight all transpositions/Remove superfluous/Show context menu when no moves - defaults to Copy branch as PGN, Engine evaluation for last moves, Remove superfluous, Show context menu when no moves

This feature will add extra options to the Analysis board/Study move list context menu. The options are:

  • Copy branch as PGN - this works in Analysis board and Studies and turns all the previous moves of the line you are in, plus all the branches after the move, into a PGN in your clipboard.
    • If you press the Shift key when clicking on Copy branch as PGN the copied PGN will start from current position, not from the beginning of the original chapter.
    • If you press the Control key when clicking on Copy branch as PGN each variation branch will be copied as a separate PGN.
    • If you press the Alt key when clicking on Copy branch as PGN then the PGN will get copied only to the current position.
    • You can use it to split large PGNs by copying variations into their own chapters, then maybe deleting them from the original one.
  • Engine evaluation for last moves - this works only for Studies, because it involves move comments. It will go through all of the uncommented last moves and create a comment in the form eval:<evaluation> using the local computer engine. The evaluation will stop at the engine depth selected in Preferences (see Custom analysis engine depth).
  • Highlight all transpositions - this works for both Analysis board and Studies and highlights all moves that have transpositions. You can configure what a transposition is in Preferences (see Behavior of transpositions)
  • Remove superfluous - for purposes of saving space, this removes the following entries from the context menu:
    • Annotate with glyphs - there is a button for this under the board
    • Comment on this move - there is a button for this under the board
    • Copy variation PGN - LiChess Tools already provides a better version
  • Show context menu when no moves - this allows for showing the context menu when there are no moves in the tree. When the board starts from a FEN position, there is a little ... element that you can right-click. When the board is completely empty, there is a row that highlights when you hover and you can right-click for the context menu.

Move assistant

category: Analysis/Study
values: yes/no - defaults to yes

This feature will add a little eye icon button in the computer evaluation window, next to the settings. Clicking this button will enable the Move assistant, which will show, when selecting a piece, the evaluation on each possible destination square. This is not meant to replace, but be complementary to the computer analysis.

Move assistant is persistent on page reloads.

Once a piece is selected, the background engine will run on one or two CPU threads with 64-128MB for hash - depending on device capabilities - for 90 seconds and then stop. During that time the color of the squares will be updated as the evaluation changes. 90 seconds corresponds to a depth of about 20 on my older laptop in a middlegame position. Unselecting pieces will stop the processing.

Obviously, this consumes processor power, so if you are on a mobile device it will consume battery. Running both local computer analysis and Move assistant will use CPU for both processes. Opening multiple analysis browser tabs will use resources for each tab in which a piece is selected.

Sometimes destination squares will not be colorized. For example, this is the case for mate threats, where moves that do not interfere with the mate will not be evaluated. 

Custom analysis engine depth

category: Analysis/Study - Advanced
values: a number representing the depth - defaults to empty

This feature will allow you to set a depth level for the computer engine. If the current move has a smaller depth level (let's say it was cached in the cloud with that level) it will start the local computer evaluation. Once it reaches the set depth, the engine will stop. You can restart the analysis by pressing the little + button next to the computer engine depth indication.

This feature is also used as the level at which to consider evaluation done for the Engine evaluation for last moves option (see Extra context menu options)

This feature is also used in Practice, where it controls the level of play (See Custom analysis engine options)

Custom analysis engine options

category: Analysis/Study - Advanced
values: Always use local engine for evaluation/Apply in Practice mode - defaults to none

This feature controls the behavior of the analysis engine used in Analysis board and Studies.

Options:

  • Always use local engine for evaluation - Use this option to never show computer evaluations from the cloud. For example if you want to not see evaluations over a certain level or in practice mode. You can also toggle this on/off from the Analysis board hamburger menu.
  • Apply in Practice mode - Use this to apply both the custom engine depth and the no cloud option before in Practice mode. This means you can play against a computer of your own level and ignore cloud and table bases. You can also toggle this on/off from the Analysis board hamburger menu.

Show next moves from transpositions

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

When you get to a certain position in the PGN which has transpositions, the next moves from those transpositions will be displayed under the move list, just like for a fork into multiple variations. You can configure what a transposition is, as well as if you want multiple identical moves to be shown or not and if the moves in this list should be considered by Ctrl-right and Extended Interactive Lessons in Preferences (see Behavior of transpositions)

Extra key shortcuts

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

this feature adds extra key shortcuts to the Analysis board/Studies:

  • first it changes the functionality of the i, m and b keys to work in Analysis board as well as Studies and to use all variations, not just the main line as the original lichess behavior. The functionality of these keys is to jump to the next inaccuracy, mistake or blunder, respectively
  • it adds the same functionality to Alt-i, Alt-m and Alt-b, only for the opponent
  • g and Alt-g cycle through good/brilliant/interesting moves
  • it adds the ability to select any of the moves in the available moves lists:
    • press . (dot) and then a digit and it will make to the nth next move available in the PGN (see Transpositions behavior if you want to select moves from transpositions, too)
    • press Ctrl-. and then a digit and it will make to the nth next move available in the computer evaluation list
    • press Shift-. and then a digit and it will make to the nth next move available in the opening explorer list
  • ` (the key before 1) and then f will freeze/unfreeze the board - this means you can still move pieces, but the board will appear as when it was frozen. Helps with visualization.

Notes:

  • the keyboard help popup now shows all of these shortcuts if the feature is enabled

Additional glyphs

category: Study - Advanced
values: yes/no - defaults to yes

This feature displays an extra # glyph on mate moves. Also it enlarges glyphs on mobile devices.

Notes:

  • The lichess UI allows for multiple glyphs on the same move. The one shown on board will just be the first one in the list.
  • Originally this feature was created to show annotations that lichess was not showing. From September 2023 lichess started natively showing all annotations, therefore eliminating the need for much of the functionality of the feature.

Extra analysis charting

category: Analysis/Study - Advanced
values: Material/Principled/Max tension/Max potential/Find interesting moves/Chart smoothing/on Eval gauge - defaults to Material, Principled, Max tension, Find interesting moves, Chart smoothing, on Eval gauge

This feature is adding extra lines on the computer analysis chart. In the search for good/great/brilliant moves - as shown on chess.com and requested by a lot of players, the lines drawn over the normal computer eval chart are meant to give some indication of what is going on without asserting an exact significance of the values. The lines are purposefully left without explanation, in the hope that players recognize some aspects of the game in the numerical values charted and their intersection.

For example, many of the moves generally considered brilliant see a steep increase in evaluation, but a steep decrease in material and/or principled position, indicating a sacrifice or an unnatural seeming move that wins the game.

If Find interesting moves is enabled then an extra entry in the analysis summary shows the total of interesting/good/brilliant moves in the PGN. The feature will also attempt to find such moves automatically, but that's an experimental feature at the moment.

Options:

  • Material - green dashed line shows a more classical material difference between the sides
  • Principled - blue dashed line shows how principled the position is. This means stuff like square control, freedom of movement, developed centralized pieces, etc, but excepting material value.
  • Max tension - red dotted vertical line shows the point of maximum tension in the game. Tension here is defined as the total material that can be captured by both sides in just one move.
  • Max potential - green dotted vertical line shows the point of maximum potential (similar to tension, but it superficially looks at capture chains)
  • Find interesting moves - if enabled, this will show good/brilliant/interesting moves in the analysis summary as a link that cycles through them. It will also automatically find some categories of interesting moves and mark them with !? glyphs (if not already annotated). The glyphs added by this algorithm will be temporary (not persisted on the server).
  • Chart smoothing - if this is enabled, temporary spikes in the charts will be removed. Example: you take a bishop with a knight, the material jumps 3 points in your favor, but the next move the opponent takes your knight, so the material goes back to 0. The smoothing function will attempt to remove these meaningless jumps in material. 
  • on Eval gauge - if this is set, two lines (one green and one blue) will appear on the evaluation gauge when it is visible, changing with the values of material and principled values, respectively.

Notes:

  • I called it "interesting moves" because there is no objective good/brilliant move algorithm. I will continue to work on it in the future.

Learn from your mistakes in Studies

category: Study - Advanced
values: yes/no - defaults to no

This feature will add a Learn from your mistakes button for study chapters that have a server computer analysis, just like for game analysis. It's a bit hackish, but it should work fine.

Change chapter names from PGN tags

category: Study - Advanced
values: yes/no - defaults to yes

This feature will give you the option to quickly change the name of a study chapter to either the content of its Event PGN tag or the White and Black PGN tags combines when editing the chapter. Sometimes people want to import a series of PGNs into a study, but only the first chapter gets a proper naming from the PGN tags, the rest getting the default Chapter 2, Chapter 3... etc name. With this you can go to each of them, edit them by clicking the cog button next to their names, then choosing with one click the name the chapter should have.

Study chapter navigation controls

category: Study - Advanced
values: yes/no - defaults to yes

This feature will add a set of buttons at the end of the chapter list in a study. The buttons allow to navigate to the first chapter, previous chapter, random chapter, next chapter and last chapter respectively.

Sticky study Preview mode

category: Study, Interactive Lesson chapters - Advanced
values: yes/no - defaults to yes

This feature will keep you in Preview mode as you move from one Interactive lesson chapter to another. As one trains using interactive lessons, at the end of one they are presented with the option to move to the next chapter. The default lichess behavior is to show you the chapter in edit mode (assuming you are the owner or a contributor) which requires you to press the Preview button all the time to continue training, after also having seen the move list. Now you can press Preview once and play chapter after chapter with no hassle.

From version 2.1.51 on, this is preserved also between page reloads, so you will have to manually unset Preview mode to edit a study Interactive Lesson.

Show game opening names

category: General
values: yes/no - defaults to yes

This feature will load the lichess opening name for games and display it. When watching a game or analyzing it, the opening will be shown under the player names. For mini-games (the tiny ones that appear when hovering over a game link or put somewhere on the page) it will appear at the bottom. For Analysis board, if the wiki side is empty, it will show the opening there, assuming the position is in the local cache.

In the very rare cases where the same exact position is found with colors reversed, the name of the opening will appear with a little (R) at the end.

This should work for Analysis Board, Game analysis, Studies, TV games, Board Editor, Broadcasts, Swiss tournaments, etc.

Show pawn structures

category: General
values: Enabled/Only named structures/Fuzzy search - defaults to Only named structures/Fuzzy search 

This feature, similar to Show game opening names, shows the pawn structure of the current game position. Because it's all very local, with no API requirements, it also works for current game pages.

An entire blog post about it can be found here: The Pawn Structure feature in LiChess Tools

Options:

  • Enabled - enables or disables the feature - disabled by default.
  • Only named structures - show only structures with names. This is enabled by default, but of course will not work unless enabling the whole feature. If you disable this, you will see a very technical name for each and every move.
  • Fuzzy search - this will show named pawn structures within a certain proximity. It helps to determine the general structure, even if it might not be totally accurate.

Notes:

  • This feature has a lot of potential, as the pawn structure is something that informs medium to long term plans. Knowing these will help you grow a lot as a chess player.
  • If the pawn structure is named, it will show as a link which can be clicked to learn more.
  • If the structure is not found in the available list, it will be searched from the opponent's perspective, in which case it will be shown with a (R) at the end if found

Show player country flags

category: General
values: yes/no - defaults to yes

A very early feature of the extension, it has also evolved a lot. The latest version is much more efficient in terms of how it gets the data from the server and adds flags to almost every place where a user link is shown. I am aware that this might be distracting and I am planning some detailed values to choose how wide spread the flags will be. Also, did you notice the flags are lazily flapping in the wind?

In order to not use too many resources, the flags for players are cached for 10 days. If you want to clear the cache, go to Preferences and turn it off and on again.

Better exported board image

category: Analysis/Study/Board editor - Advanced
values: yes/no - defaults to yes

This feature will override the default share Board button in studies, SCREENSHOT button in the board editor and add a SCREENSHOT button to the analysis board. When clicked, the button will export not only the board position, but also the arrows and anything else drawn on the board in the current position. You retain the original behavior by right-clicking the button and opening in a new tab or window.

Autosave analysis for reload

category: Analysis - Advanced
values: yes/no - defaults to yes

This happened to me a lot of times: I am analyzing something and I accidentally swipe the touchpad or press some key that reloads the page. All my work is gone! This feature keeps track of the latest modifications of Analysis board PGN and reloads it on page load. It is your choice if you want to import it or not by going to the PGN text area and clicking the Import PGN button.

Notes:

  • The saved PGN as well as the Analysis board import text area content will be overwritten by whatever move you make, so if you fail to import the PGN and make a move, you lose the data. 

Opening explorer player features

category: Analysis/Study - Advanced
values: Me button to switch to your player - defaults to Me button to switch to your player

This feature controls the choice of the player in the Opening Explorer.

  • Me button to switch to your player - adds a tab next to Masters database, Lichess and Player named Me. If you click on it the choice of the player will toggle from your user to the last selected user.  The button can be shown/hidden from the Explorer config screen as well. 
  • Ability to remove players from the name list - adds the ability to delete players from the list of username previously chosen in the player tab
    • Lichess implemented this feature natively so it was removed from LiChess Tools in v2.1.21

Notes:

  • The Me button will appear only if you have any another players configured for the Player tab name list

Toggle snapshots of Explorer settings

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

This feature will add a new button called Snap! in the Explorer settings of the Lichess database tab. This will allow to save the current Explorer settings under a name, then quickly toggle between the various sets saved by clicking on the same tab, which will now change text to reflect the name of the selected "snap". You can easily delete existing snaps and create others.

Note:

  • This started as disabled by default, but it was so useful that I've decided to enable it.

Various TV options

category: TV
values: Link for current TV game/Bookmark for current TV game/Streamers current games/Friends current games/Previous two games in player TV - default to all

This feature has been introduced in version 2.2.0 to merge four existing tools for TV with the same functionality. Make sure you review your Preferences.

Options:

  • Link for current TV game - adds a link to the currently played game in TV on the title, just like in Analysis mode
  • Bookmark for current TV game - adds the ability to bookmark a currently playing game by hovering the mouse next to the game title
  • Streamers current games - adds a Streamers tab in the Current Games page where you can see the games of all currently playing streamers
  • Friends current games - adds a Friends tab in the Current Games page where you can see the games of all currently playing players that you follow
  • Previous two games in player TV  - In category TV (when you are watching Blitz games for example) you get a list of two previous games in that category under the main playing board. For player TV, when you go to a specific player to watch their games, this is missing. This feature will add the two previously played games by the player you are watching.

Previously viewed game menu item

category: TV - Advanced
values: yes/no - defaults to yes

This feature adds an extra menu item in the Watch menu that will take you to the previously seen chess game. Just opening any game will consider it as watched. If you go to the previously watched game, then clicking on the menu item again will take you to the game you watched before that, and so on. The total number of remembered games is 10.

Pin broadcasts and studies to home page

category: General- Advanced
values: yes/no - defaults to yes

This feature will add a red pin to the studies and broadcasts tabs on the top left, right after the search icon. Click on this pin to pin/unpin the study or broadcast to the home page. All the things you pinned will be added to the spotlights area of the main page (top left corner)

Last visited study menu

category: Study - Advanced
values: yes/no - defaults to yes

This feature adds an extra menu item in the Learn menu that will take you to the previously visited study. Just opening any study will consider it as visited.

Styling for study comments

category: Study
values: yes/no - defaults to yes

This feature adds the ability to style the comments in your PGNs. You can choose between a number of styles that you can place anywhere in a comment with cls:<styleName>. It also adds a new button next to the comment button in a study chapter (a quill in an inkwell) that with cycle through all of the classes at the very beginning of the comment to the current move. The usable classes are: red, orange, yellow, green, lightgreen, cyan, lightblue, blue, violet, magenta, pink, underline, strikethrough, italic, bold, cursive and nothing or clear to clear existing class. (ex: cls:red will make the rest of line of the comment red)

Notes:

  • the name of the class declares the intent, not the actual result of the styling. Given that there are dark and light themes on lichess, the styles have been updated to be readable in both situations, using shadows or slightly different colors.
  • the style of the comment will only be visible to you and people with LiChess Tools installed. All others will see cls:blue or whatever in the comment.
  • you can actually put anything as the class name and the comment will be put inside a span with the class lichessTools-<your class name>, which allows you to customize your style even more with the addition of custom styles from other extensions like Stylus. In this case all people seeing the comment would also need the custom styling.

Show the order of arrows and circles

category: Analysis/Study - Advanced
values: yes/no - defaults to no

This feature shows a number on each drawn shape, so that you can see their order. Sometimes in studies you want to convey the moves that could follow the current position using arrows and circles, but in certain situations the order of the moves is important. Set this to true in order to see which arrow comes first.

Ctrl-Space for best computer move

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

The default functionality of the Space bar in analysis mode is to start the computer engine if not started or play the best move in the computer move list. Because I was often using lichess with YouTube in another tab, pressing Space accidentally was always an annoyance. This feature changes the shortcut to Ctrl-Space.

Notes:

  • A previous version of the feature was using Shift-Space, but it would overlap with a lichess feature.

Clear chapter artifacts

category: Study - Advanced
values: yes/no - defaults to yes

This feature allows you to choose which artifacts to clear from only the current study chapter. To use it, click on the cog button next to the chapter name and, instead of the Clear annotations button now you get the choice of just comments, glyphs (good move, bad move, that kind of thing) and drawn shapes (circles and arrows) as well as all of the above. There is an extra option of clearing all PGN tags. This feature also adds individual delete buttons to all tags in the current study chapter.

Notes:

  • this only works for the current chapter, as the site does not have access to the moves or tags in the PGNs of other chapters
    • the feature could be modified to work on all chapters, but it would have to first select the chapter, then alter it, which I think is not something that should be done automatically.
  • this works on the client, which means that instead of sending one command to the lichess site, it creates delete requests for each item. If the PGN is large, this can take a long time.

Customize chat buttons

category: Play
values: yes/no - defaults to no

When playing the first few moves and after the game has ended, lichess gives you the option to click on some buttons for some standardized message like Hello, or Have fun! This feature will give you the ability to customize these buttons.

The interface may not be the most intuitive. To edit buttons you click on the little cog button, to exit edit mode you click on the same. This allows altering, adding and deleting buttons. Click on the small X button to enter delete mode, after which you click on buttons to delete them. Delete them all and you get the default buttons back.

In order to add or alter buttons you have to write in the chat text input something like <name>/<message>. Example: hi/Why, hello there, old chap!, which will create a button with the text HI which will send the chat text "Why, hello there, old chap!". In order to add the button, you click the + button. In order to alter an existing button, you click on that button.

Notes:

  • the name of the button can be only 2,3 or 4 characters long.
  • if you attempt to add or edit a button without a recognizably correct input text, the input box will jiggle to let you know you are doing something wrong
  • to edit the end of game buttons you have to reach the end of a game. This may be inconvenient and I am thinking of solutions
  • I am considering expanding this feature with buttons for the duration of the game as well as for spectators. Let me know what you think!

Player lag indicators

category: Play
values: none/bars/chart - defaults to none

This feature adds bars or a chart next to your and your opponent's name showing the lag and server latency. To limit use of server resources, the opponent lag indicator is updated only every 5 seconds, while yours every second or so.

Player warning alert

category: Play - Advanced
values: yes/no - defaults to no

This feature will add a small warning icon next to the opponent's name if the percentage of disconnects for the current time control is higher than 3%. Other warning reasons may be added in the future. The purpose of this is to allow you to abort a game before wasting time on a malicious opponent.

Timeline notifications

category: General - advanced
values: Forum post/Blog post/Lichess announcement/Stream start/Simul create/Simul join/Team create/Team join/Tournament join/Following/Study like/Blog post like - defaults to Forum post/Blog post

This feature will add a notification when there are unread items in your Timeline. The default values include comments on forum or blog posts you follow, forum or blog posts from people you follow. Basically posts that you wouldn't normally be notified about. 

Clicking on the notification will take you to the Timeline page. Opening it from here or anywhere else will consider the items read and so you will not see the notification until new ones turn up.

Notes:

  • there will be no notification if quiet mode is on (meaning you are playing a game or you have quiet mode manually set)

Mobile device features

category: General
values: Evaluation gauge/Hide the octopus mascot/Analysis arrows/Random move button/Scroll lock when playing - defaults to Evaluation gauge, Random move button

This feature alters some behavior specifically for mobile devices. There are Chromium-based mobile browsers, like Kiwi, which accept browser extensions. Enjoy LiChess Tools on mobile installing one and then the extension, then customize the experience with this feature. The options are:

  • Evaluation gauge - makes the evaluation gauge visible even on small screen widths (for mobile)
  • Hide the octopus mascot - hides the octopus mascot that takes a lot of space in Interactive Lessons
  • Analysis arrows - adds a button next to the Explorer and Practice ones that allows for drawing arrows and circles on mobile devices in Analysis board and Studies
  • Random move button - adds a button between the previous and next move buttons that will randomly play a move from the move list (same functionality as Ctrl-right to play random next move from list on desktop)
  • Screen lock when playing - will lock scrolling and zooming on mobiles when playing (or during puzzles). A lock icon will appear as a button on top of the screen that you can tap to temporarily lock/unlock the scrolling on that page (refreshing or going to other screens will again automatically lock the screen unless you unset the value from Preferences).

Notes:

  • Because both the shape drawing and random move buttons take up space, this feature also changes the style of the buttons so that they take two rows of space: the first for utility buttons (left) and the hamburger menu button (right) and the second row for PGN navigation buttons (first, previous, random, next, last move). If none of the two options mentioned are enabled, then the regular "one row for all buttons" style is used.
  • Screen locking when "playing" doesn't necessarily means when playing, but when lichess is in "playing mode". For example that also means when you haven't yet started the game or when you just ended the game or when you are doing puzzles.

Mobile device game features

category: General - Advanced
values: Game arrows/Standard buttons - defaults to none

This feature alters some behavior specifically for mobile devices when in-game. This means playing, or solving puzzles or even watching running TV games. There are Chromium-based mobile browsers, like Kiwi, which accept browser extensions. Enjoy LiChess Tools on mobile installing one and then the extension, then customize the experience with this feature. The options are:

  • Game arrows - adds a button next to the Analysis board one that allows for drawing arrows and circles on mobile devices in games
  • Standard buttons - the default behavior of lichess.org is to hide the move navigation buttons for games on small screens, instead showing one line of PGN with previous and next move buttons on either side. Set this in order to see all buttons (including the Game arrows one above). For normal play this is not necessary, but in puzzles, TV and such screens this is the only way to access the drawing arrows button.

Show evaluation of explorer moves

category: Analysis/Study - Advanced
values: From computer eval/From ChessDb/From Lichess /From winning stats/Rows from eval/Hidden - defaults to From computer eval/From ChessDb

Options:

  • From computer eval - will show the evaluation from the computer engine
  • From ChessDb - will show the evaluation from the chessdb.cn database
  • From Lichess - will show the evaluation from the Lichess evaluation API
  • From winning stats - will compute an evaluation based on the win/draw/loss statistics
  • Rows from eval - will add extra rows with the move and the evaluation if the explorer doesn't contain a line for that move
  • Hidden - hides any evaluation in explorer, but retains the settings if you want to show them again. This option can also be changed from the Explorer settings

This feature will show the move evaluation for Explorer moves. This information is retrieved from three different sources that can be enabled or disabled: the local computer eval, chessDb.cn, the lichess evaluation API and the Explorer stats themselves. If Hidden is set, then the settings for evaluation will remain, but the column will not be shown. The feature can be hidden not only from Preferences, but also from the Explorer config screen.

ChessDb.cn will be used first (if enabled) and then the Lichess eval API, so if you prefer the Lichess API, you should disable ChessDb. In case ChessDb fails for whatever reason, lichess eval API will be used instead. Just note that the Lichess API is limited in how much calls one can make and contains less data than ChessDb.cn.

The statistics eval will only be shown if there are 100 or more games in the lichess database for that move and not all won by one side. Its faded coloring signifies that it's not a true eval, but if enabled can compare with the other values. 

Computer evaluations will have a white color, stats evaluations will have a grey faded color and the cloud evaluations will be slightly blue. If using chessDb, the eval will show either red, green or bright green for bad, good and best moves.

This feature also adds a warning icon on the right side of moves that have large differences between evaluation and winning stats, indicating a possible trap or gambit situation. It also calculates the sharpness of a move, as defined in Evaluating sharpness with Leela’s WDL and adds a blue warning icon on the right side of the move when it's higher than 100.

The feature is unavailable in the Analysis screen of a running game.

Notes:

  • The more lines you have configured on your computer analysis, the more items in the list will be evaluated. Explorer can show 12 moves, for example, while the computer analysis is configured with Multiple lines=2, this means a maximum of 2 items in the Explorer will have an evaluation. With 5, you get 5, assuming the computer moves are in the Explorer list.
  • There is an internal cache of these evaluations, so you can run the computer eval for some moves, then stop it, but when going to those moves you will still see the evaluations as done before at whatever depth was last computed.
  • Take care with stats evals in positions with few games. It might tell you that you are absolutely winning, but that's just because no one played a winning move from that position. That is why eval from winning stats is disabled by default.
  • The evaluations from cloud differ in depth based on how many moves you want to see. For example you could ask for just one move (the best in the position) and it might show you the eval with depth 55, but if you ask for two moves it will give you depth 44, for 20 moves you would get depth 13 or even nothing. To account for this, cloud eval combines the values from two calls: for 5 moves and for 10 moves. Since the cloud data comes from local evals, server evals and different versions of Stockfish and I am combining two different depth evals, take the evaluation as an estimation, not a given.
  • Changing the options for evaluation in Preferences will reset the local cache for each move.
  • Lichess' cloud eval API will return 404 when no data is available, resulting in ugly red network lines and error messages in the browser developer tools. Those are not errors, but there is no way to hide them in Chromium browsers from JavaScript. If it annoys you, you can turn them off from DevTools (see https://stackoverflow.com/questions/4500741/suppress-chrome-failed-to-load-resource-messages-in-console)
  • Depending on how much you do analysis and how overloaded the servers are, cloud eval might start returning 429 errors, meaning the servers report you are abusing them. A warning will appear every minute while that happens. The workaround is to disable From cloud and use just computer eval (which, of course, will also use cloud eval :-P). The configuration on lichess' side, at least for the moment, is 3000 calls per IP address per day. That's a bit low, but it might be enough.
  • If computer evaluation is started and used as a data source, the cloud eval will not be accessed.

Show explorer moves leading to gambits

category: Analysis/Study - Advanced
values: yes/no - defaults to no

This feature adds a new column to the Explorer table showing the number of gambits reachable from the current position and for each move to play. The feature can be turned on/off not only from Preferences, but also from the Explorer config screen.

The feature is unavailable in the Analysis screen of a running game.

Quiet mode on all tabs

category: Play - Advanced
values: yes/no - defaults to yes

When a game starts, lichess sets a quietMode variable to true, instructing various notifications to not be displayed. Unfortunately, that only happens on the page that one plays on. This feature makes all open lichess pages get the value for quiet mode from the same place, so they don't behave inconsistently.

There is also a button added to the lichess menu which can manually enable/disable quiet mode. Warning: once manually enabled, quiet mode will remain on until manually disabled! The normal game playing quiet mode will turn on and off automatically, as before, only it will affect all open lichess pages.

Note:

Colors for shapes on mobile

category: General - Advanced
values: 1/2/3/4 - defaults to 1

This is part of the Mobile Experience feature and it selects the count of colors you can use on mobile to draw arrows and circles. If you only want to turn the drawing mode on/off with a single tap, select 1.

Insert new chapter after current one

category: Study - Advanced
values: yes/no - defaults to yes

This feature adds a button to the create study chapter form with the text Create after current. Using this button instead of the normal Create chapter will create the chapter immediately after the currently selected chapter.

Force add Stockfish 16

category: Analysis/Study - Advanced
values: yes/no - defaults to no

Some browsers have issues with the new Stockfish 16 engine, but these issues are not clearly defined. For example many users of the Brave browser complained of division by zero errors. I use Brave, I've never seen this. Therefore, if you are a user of Brave or some other browser that does not support the Stockfish 16 engine, this will force it to be available. However, the responsibility of enabling this and using SF16 are yours.

Don't worry, there is nothing bad that can happen, you just switch to Stockfish 14 and you're done in case using SF16 doesn't seem to work.

Show PGN in studies

category: Study - Advanced
values: yes/no - defaults to yes

This feature adds a textarea in the study Share tab called PGN. Similar to the one in Analysis board, it will show the PGN text of the current chapter. The PGN will be generated by the same code that generates the one from Extra context menu options and may differ from the one generated by the Copy PGN or Download PGN buttons.

Persist study settings

category: Study - Advanced
values: 'New/edit chapter settings' 'Position/move in the study' - defaults to Position/move in the study

Options

  • New/edit chapter settings - will persist the settings for studies when you create or edit them. Then it will use the same values when creating new studies.
  • Position/move in the study - will remember the position (move in the list) in a study chapter and restore it on page refresh
    • Note that if Lichess does not restore the chapter on refresh (it happens sometimes when the user get disconnected) then this will not work. This just restores the move in the current chapter, it does not change it.
    • Also note that this will not work in Interactive chapters if in Preview move (so if you play them)

Move list options

category: Analysis/Study - Advanced
values: Indented variations/Bookmarks/Expanded move list/Hide left side/Open in new window/Eval button on the right - defaults to Bookmarks

This feature will change the functionality or appearance of the move list in Analysis board. There are already such changes with the Highlight moves in analysis functionality.

Options:

  • Indented variations - this will make even variations that follow in line (when they are less than three, they are displayed one after the other in parentheses, like in a PGN text) show as tree branches, increasing readability and preparing for new features that will use this.
  • Bookmarks - Study only - this will allow for bookmarks, which are very cool (read below), but a bit experimental. Let me know how they could be more of use to you.
  • Expanded move list - Only for desktop resolutions (1260+ px), this feature is removing the left and right margins and expanding the move list to the edge of the screen and also downwards. It is meant to allow analysis on very complex PGNs.
  • Hide left side - Most of the time the left side of the analysis screen is useless: chat, notes, study participants and stuff like that. With this, you can hide that side to gain even more space for the move list. This is intended to be used together with Expanded move list, but you can use it separately as well. 
  • Open in new window - Study only - this adds a small button in the top-right corner of the page, just before the computer evaluation toggle. Clicking it opens a new window with just the analysis tools (move list, computer eval, explorer) that you can move to another monitor, for example, or resize and place wherever you want. Use the study SYNC button to keep the two windows synchronized. (the SYNC button only appears under the board when the study is set to Enable sync)
  • Eval button on the right - this will move the computer evaluation button to the right side of the eval header and also make it a little bit smaller.

How bookmarks work:

  • right-click on a move in a study move list and select Add/Remove bookmark from the context menu
  • put any text in the textbox that appears (or remove it all to delete the bookmark)
  • this will create a bookmark, which serves several purposes:
    • it will show as a named label in the move list (good for naming variations or making the PGN more readable)
    • it will allow to expand/collapse the branch from that move on
    • it will serve as an anchor for URLs to this exact variation
  • for any bookmarked move you can right-click it and get:
    • Collapse/Expand all bookmarks - collapse and expand all bookmarks in the tree
    • Get bookmark URL - will copy to clipboard a URL to that specific move in that specific chapter and study (the link will look like this: https://lichess.org/study/<studyId>/<chapterId>#<bookmark name>)
    • Split chapter here - only if the bookmarked node has children (following moves) - it will create a new chapter with the following moves, then add a comment with the URL to the new chapter. If you want to also delete the following moves, press Shift when you click on the menu item. The confirmation dialog should reflect that moves will be deleted.

Notes:

  • Bookmarks are saved in the comments as bkm:<label>
  • Only you and people with the LiChess Tools extension (and bookmarks enabled) can see and use the bookmarks, the rest will just see the bkm:<label> in the comment
  • Splitting a chapter with Shift-click DELETES THE EXISTING FOLLOWING MOVES from the initial chapter. Don't tell me you weren't warned.

Link to download all studies of a player

category: Analysis/Study - Advanced
values: yes/no - defaults to yes

This feature will add a "Download all studies" link in the studies list section whenever a user is selected in the query. This happens in two situations. Either you went to your studies or you selected the studies of another player. The necessary condition for the link to appear is that the study search query contains "owner:<user>" where <user> is any user id. The link will download one PGN with ALL studies of the selected user that you have access to. Naturally, your studies will all be downloaded. For other users, only the public studies will be downloaded.

Mirror button in Board Editor

category: Board Editor - Advanced
values: yes/no - defaults to yes

This will add a new button to the Board Editor called Mirror which will mirror the position you have currently. Unlike Flip board, it will create the exact same position for the other side, with the exact same valuation and move opportunities.

Custom mini-game size

category: General - Advanced
values: a number - defaults to unset

This feature sets a custom size to mini games, which are the boards that appear when you hover over a playing player link or in the Current Game or Broadcasts section, etc. The default is unset, which keeps the Lichess style. An equivalent numerical value for the default would be around 20. Change the number to increase or decrease the size of mini boards.

Notes:

  • the minimum value that will be taken into account for mini-games is 15. Lower values will only affect the popup board in the computer eval window.

Fix board coordinate position

category: Analysis/Study - Advanced
values: Fix outside coordinates/Larger coordinate font - defaults to Fix outside coordinates

This feature has two purposes. The first is to fix a Lichess bug that doesn't apply the preference for board coordinates on the outside in Analysis/Study. The second is to fix the CSS a little bit and also to enlarge the coordinates font.

Since most people found the larger coordinate font ugly, you have to enable the larger font manually and is disabled by default.

Show dates for Profile slider

category: General - Advanced
values: yes/no - defaults to yes

On the profile of a user there are buttons to select the range of the chart on top of the page. There is also a slider that can select custom ranges and one can also drag the selection on the chart itself. This feature will show the start/end dates selected by either of these methods.

More decimals for computer evaluation

category: Analysis/Study - Advanced
values: yes/no - defaults to no

If you enable this feature, the computer evaluation will show two decimals instead of one when computer engine evaluation is running.

Themes

category: General - Advanced
values: whatever themes are available - defaults to none

This feature will enable/disable various CSS themes. If an external theme (made by someone other than me) it will show attribution in the Preferences page. It will also be the creator's responsibility to maintain it.

Available themes:

  • Performance - performance CSS changes, mainly attempting to remove all animations, which cause slow rendering
  • Just Explorer - will hide the computer eval moves (but not the arrows, if enabled) and the move list when the Explorer is open. This will only be applied for mobile devices
  • Mobile - a lot of style improvements for mobile, especially aimed at studies and interactive lessons
  • Move colorizer - each level of PGN moves is colored slightly differently and comments are italicized
    • Warning: for large move lists this affects the performance quite a lot. The suggestion is to not use it if you have a lot of variations with more than 1000 moves in total.
    • the move colorizer theme has been removed in v2.3 and replaced with the Highlight variation depth feature.
  • Slim arrows - will make arrows less thick: color arrows, variation arrows and computer evaluation arrows.

Notes:

  • Multiple themes can be applied at the same time, but beware of conflicts. 

Wiki pages based on FEN

category: Analysis board - Advanced
values: yes/no - defaults to yes

This feature will show Wiki pages on openings even if the order of the moves changes. Basically, it looks for the Wikibooks Chess Opening page associated with the current position.

Commands

The commands feature is actually a combination of tools that register themselves with the cliCommands tool. This enables you to type / and then a command and execute various functions, besides the standard ones. Since commands are executed manually, they have no visible Preference to disable them.

Here is the list of available commands:

  • board - it will show/hide the board in Analysis mode (Analysis board and Studies). Use this with the Expanded move list and Hide left side options of the Move list options feature to have a move list filling the whole screen (good for presenting, for example).
  • trapvalue - provided the current move is in the Explorer database, this command will calculate the trap value of the current position. For more details on what that means, check out How To Calculate The Trappiest Chess Openings Using The Lichess API
  • copypgn ["fen"] ["separate"] ["tohere"] ["unicode"] - this will copy the moves in the analysis move list (Analysis board and Studies) to the clipboard as the moves reaching the current position and any branching moves from it.
    • if fen is specified, the PGNs will start from the current position with the FEN tag specified
      • same as Shift-click on the Copy PGN menu item 
    • if separate is specified, each variation will be saved as a separate linear PGN
      • same as Ctrl-click on the Copy PGN menu item
    • if tohere is specified, the PGN only copies moves to the current position
      • same as Alt-click on the Copy PGN menu item
    • if unicode is specified, the PGN will have unicode pieces instead of letters
      • note that results in a text that is meant for human readers, no longer a valid PGN
  • readgame [speed] [voice] [instrument] - this will read the game moves from the current position. It will follow only the first variation of every node, ignoring the others.
    • speed is an optional integer number, defaults to 100. Larger values makes the speech faster, lower makes it slower.
    • voice is an optional integer index number, defaults to 0. Changing it will change the voice which reads the moves, depending on your own browser setup.
    • instrument is an optional integer index number, defaults to 0. Changing it will enable a sound to be played in the background, based on the computer evaluation, if the game has been analyzed.
    • example: /readgame 80 2 1
    • this command also adds a feature to read a game in analysis if you add #readgame at the end of the URL (i.e. https://lichess.org/<game id>#readgame or https://lichess.org/<game id>/black#readgame or https://lichess.org/study/<study id>/<chapter id>#readgame etc.)
  • skipmove - this will generate 5 moves (if possible) to reach the same position, but with the other side to play.

Also save options for browser private/incognito mode

category: General - Advanced
values: yes/no - defaults to no

This was a user request to find a way to save the options outside the local browser cache, as he was playing the game from incognito/private browser mode. Since no other solution was better, this saves the options as notes in the first chess game you ever played on lichess. So be careful that this will alter those notes, although I am not aware of many people using the private notes feature.

Notes:

  • be aware that it will alter the private notes of the very first game you played on lichess
  • the extension will use whatever options you have in the browser local cache. Only if they are not there it will take them from the notes. That means that you can do weird stuff like enabling the feature, saving options, disabling it, changing the options and now you will have different settings in normal and incognito mode. So use with care.

Hide score tally crosstable

category: Play - Advanced
values: yes/no - defaults to no

Another user request was to not see the crosstable, the thing that shows how many games you played with the same opponent and what the results and games were. It was intimidating. This feature will blur it out, but allow you to click on it to unblur it. It's a silly feature that may not survive long because there is also the Zen mode lichess option which overlaps this behavior, but it's here for the moment.

  This extension adds a lot of functionalities to your lichess web site. It has so many useful and powerful features! I am very proud of it. The extension is always going to be free, ad-free, donation links free, etc. Yet the only way for it to do what YOU want is feedback. Any feedback! Praise, curses, bug reports, feature requests, use stories, anything. The more you tell me, the more I can improve on this!

  News update: I have merged several TV tools into one, as well as changed the Custom Engine Preferences in version 2.2.0. I removed the Move Colorize theme and replaced it with a feature in the Highlight tool in version 2.3.0. Please review your Preferences.

  I have also written a different page that will function as a user manual, with all the details on features, preferences and what they mean.

  If you are just interested in the list of features, in reversed chronological order, you might want to check out the history file.

  Other stuff:

  • all features have been encapsulated in "tools" in the code
  • all ideas for the future will be rewritten as issues on GitHub, where you can also put feature requests and bug reports
  • A help icon has been added to each of the Preferences to show details on each feature 
  • the extension requires Chrome version 111 or higher

  And now back to our regularly scheduled programming (programming, get it?):

  LiChess Tools (ver. 2.3.24) adds the following features to lichess:

  • play ALL variations in Interactive lesson study chapters!
    • computer is going to play a random move (configurable probability), so you don't need to create a chapter for every small variation
  • PGN editor to merge, normalize, split, count, upload, download, copy PGNs.
  • merge multiple PGNs in analysis import
    • I merged 1000 PGNs with 25000 moves and it worked!
  • automatically open/hide/convert to menu or button the Friends box at page load
    • having the friends box as a menu/button item is really neat
  • sound alert when one of your friends starts playing a game
    • also reading the type of game, so you know if you even want to look at it
    • now there is an option to mute this for individual players in the enhanced friends list
  • ability to randomly play one of the next moves (with configurable probability in comments i.e. prc:66) with Ctrl-Right and go back with Ctrl-Left
  • play against the Opening Explorer (either masters, lichess players or a specific player) in Analysis
  • evaluation of Explorer moves, as well as telling you what move leads to gambits
  • Missed Timeline posts or comments to posts you follow notification
  • screen lock on mobiles while playing (scroll and zoom)
  • find interesting moves and allowing cycling through interesting/good/brilliant moves just like with blunders
  • highlights for the last move of variations (special case for the ones that have no comment and do not end in checkmate) in the analysis/study board
    • you immediately see not only where a variation starts, but also where it ends
  • highlights for the transpositions to the current move in the analysis/study board
    • you won't ever have to worry that you are analyzing the exact same variation but in a different order
    • also you can now show all transposing positions in the PGN
  • new shortcut for playing the next best computer move from Space to Ctrl-Space
    • always annoyed me when I accidentally pressed the key
  • a custom chess engine level
    • if it is idle in a lower state, it runs until it gets to that level
    • this is also used as the required engine level by the study context menu option of commenting all last moves with a computer evaluation
  • custom chess engine options: never use cloud/tablebase, use engine in Practice mode
  • sticky Interactive lesson Preview mode
    • you can now play chapter after chapter without hassle
  • use keyboard shortcuts (i, m, b, Alt-i, Alt-m, Alt-b) for inaccuracies, mistakes and blunders in all variations
    • note that this is a native feature of lichess, but only in your game analyses and only the mainline moves
    • added g and Alt-g to cycle between "positive" moves (good, brilliant and interesting) 
  • show player country flags next to their names
    • if they have their country specified in the profile
    • now you will see flags everywhere. It might break some stuff, so let me know.
  • show the order of circles and arrows in a study/analysis.
    • this is great when you want to understand the order of moves/hints
    • option is off by default
  • a new menu item to open the last viewed TV game
  • show opening names in TV and mini games, as well as Analysis board and Studies
  • many TV options:
    • show history section in player TV (just like for category TV - the two latest games of the player)
    • friends and streamers section in the Current Games tab
    • link and bookmark the current TV game
  • quick button to switch to your player and back in personal opening explorer
  • copy to clipboard branch and continuations from a certain position in analysis/study
    • you can now just pick a variation, copy it in its own chapter, with just a few clicks
    • Shift/Ctrl/Alt change the way this item works
  • available languages: English and Romanian
    • ask for more! I will provide you with the English sentences and the context and you can tell me how it is in your language
  • the options for the extension are in the lichess Preferences section
    • complete integration. The extension popup has no functional role anymore
    • this also means that I will be able to port this to other browsers with minimal effort. Ask if you want this!
  • move options from transpositions to the current position
    • the Extended Interactive Lessons and the Ctrl-Arrow functionalities are also able to choose moves following from this list, as well as the variation arrows
  • automatically evaluate last moves in every variation and store it as a comment
    • the engine level for the evaluation is the same as the custom chess engine level in Preferences 
  • buttons in the study chapter edit form to quickly set the title to the content of the Event or of the White/Black PGN tags
  • set colors/styles to study comments
    • note that these will only be visible to people having the extension installed
  • study chapter navigation controls, including random chapter button
  • auto save and button to reload PGNs in Analysis mode (recover from accidental reloads) 
    • now it automatically copies the last PGN in the PGN box, but you have to manually import it by pressing the button
  • show all transpositions in the analysis/study move list
  • hide the score tally while playing
  • live friends page will update automatically and allow TV view, mute playing alerts and much more
  • global switch to enable/disable extension
  • ability to selectively remove artifacts (comments, shapes, glyphs and PGN tags) from the current study chapter
  • custom chat buttons at beginning and end of play
  • one button delete PGN tags
  • draw arrows and circles on mobile devices (analysis and in-game)
  • extra lines on the game analysis chart
  • menu entry to go to last opened Study
  • study options: persist settings, create chapter after current, show chapter PGN as in Analysis
  • move list options: indented variations shows all variations as tree branches, not inline, expanded move list uses all the space available for the analysis move list and hide left side hides the left side of the analysis window for even more space. Open in new window lets you see the move list in another window that you can move to another monitor. You can have the computer evaluation toggle back on the right side.
  • bookmark study moves, which allows for collapse/expand variations, linking to position, highlight in the move list, getting the bookmark URL from a context menu and split the chapter from any bookmark.
  • Option to not see cloud values in computer evaluation
  • Wiki pages will now load in Analysis regardless of move order
  • Variation arrows for transpositions
  • Show pawn structure names in TV games, mini games, Analysis board and Studies
  • Click on Explorer total row to get a random move
  • Toggle between different Explorer lichess tab settings
  • Custom mini-game size
  • Play again from same position you entered Preview mode in
  • Use Stockfish 16 on Brave browser
  • Learn from your mistakes in study chapters
  • Pin studies and broadcasts to home page
  • Community forum
  • Freeze board keyboard shortcut in Analysis/Study
  • Player lag chart next to player names during play
  • Link to download all studies of a user
  • Show profile chart time range dates in a label
  • Outside board coordinates, even in Analysis/Study, and bigger font.
  • Puzzle statistics in Profile
  • Move assistant shows the evaluation of your selected piece legal moves
  • /commands! Type /help to get a list

  I couldn't wait to share it with you guys. I will be happy for any feedback, suggestions or help.

  I've started a series of use case blog posts, they might show you how to use the extension in real life:

Here are some screenshots, but they don't really tell you the story. You just have to try it.

Good luck using my extension. I am sure I am going to be tinkering with it a bit. Let me know of any problems you have with it.

Other ideas

For readability sake, I've removed all the old ideas from here and moved all of the new ones as GitHub issues. You can go there and add your own!

Q&A

Q: Can you publish your extension code on GitHub?
A: Yes, I did. I could. Probably I will be starting with version 2, which will be a rewrite of a version 1 that has been in use for a while and that people have given me feedback for. As much as I like sharing my code, I really don't want to have to deal with all the GitHub complications right now.

Q: If your code is not on GitHub, it doesn't exist! Also, I looked at your code and it sucks balls!
A: That's not a question. And I agree. But right now I am focusing on features, not quality control. Wait for V2.

Q: How do we contact you with new ideas, bug reports and general roasting of your coding skills?
A: Use this post. This is my personal blog and my preferred method of communication. On top-right you can see a lot of links to various methods of direct communication with me, although I would prefer thoughtful feedback to remain documented here, as comments. You can now also use the GitHub project.

Q: I am addicted to LiChess Tools and I am afraid later on you will fill it with ads, premium features and EULAs that allow you to remove my kidneys. Can you address my fear?
A: Like everything on this blog, it will always remain free. And not free as in "until someone else buys it" or free as in "watch videos and it's free" or free as in "I will fill your screen with junk", but completely utterly free. Like LiChess, I guess. Also, it doesn't connect to any external services or capture any user data. For now! Muhahahaha! Later on it might need some external services for extra features that you ask for, but I hope it doesn't.

Q: How long did it take you to write this?
A: Mostly a week. Following the 80/20 rule, now I have to work at least one more month to make it good. In the end it probably took two months to start and I am still tinkering, but I can only work on it when I get the time. This has been published since the 10th of May 2023 and I am still adding or fixing or changing things. For the cause!

Q: You should write a tutorial on how to use it. Could you make a video of it?
A: I am not a video person. I hope that this post can convey the basic ways in which to use the extension and that the extension itself can be used without the need of a tutorial. Let's work together to make this clear and easy to use for everyone instead. Also, there is now the user manual page. However, I am not adverse to someone who knows how to make videos to make some about LiChess Tools.

Q: Your Extended Interactive Lesson feature is all I had ever wanted from life! But when I am editing the study, I get the same interface as normal studies. Can you fix it?
A: Some parts of LiChess are easy to change, some not so much. Anything related to rendering is a mess to hook to. Additionally, I wouldn't want to have studies that can only be edited and used with my extension. There is a move context menu that allows setting the "explain why any other move is wrong" now. Also you can collapse the controls now, so they don't bother you at least.

Q: So how do I mark the good branches from the bad variations in Extended Interactive Lessons?
A: Any move that is not in the study will be bad. As for the branches that you want to explore specifically, use the annotations (Mistake, Blunder, Brilliant Move, etc) and comments. You can even explore the bad branches in Preview mode this way and learn why they would be bad.

Q: Can you add features to show me what moves to make while playing?
A: LiChess Tools is not a cheating tool. However I try to add as many tools as possible to help you analyze your games after you've played them.

Q: But can you add some features that don't involve cheating for the games that I am playing/watching?
A: Most of the features of LiChess Tools are analysis oriented because analysis is much better exposed than the game code. Because there are a lot of private variables that are not made accessible, it's difficult to selectively change parts of the game interface and any features would have to brutally copy paste and replace some legitimate code bits. I am afraid that until that changes on LiChess, I will not touch that part, mostly because that means I would have to keep score on what they change on the web site and update my extension accordingly. Also, there are some guidelines that expect one to not change the playing interface at all.

Q: How about changing the way LiChess looks?
A: I am not a good visual designer, nor do I do a lot of work on web frontend. There are some extensions that are doing that (like Prettier Lichess, which I used myself, and perhaps you should ask those people for help instead. Also, I am avoiding as much as possible changes to the visual elements of the website specifically because it might interfere with some such extension or custom CSS tool. BTW, if you are working on something like that and find LiChess Tools is interfering with your stuff, let me know. We can figure things out. In v2.0.14 a new Themes tool has been added. I can publish CSS themes this way, but I don't intend to maintain them myself. If you want to see your theme there, contact me. 

Q: OK, you're my new hero. How can I help?
A: Contact me and let's talk. I despise doing anything UI design related, as evidenced by this blog and the extension popup, so maybe you can help there. Also, not a specialist in browser extensions, so any improvements and/or help with other browsers would be welcome.

Q: Yeah, but I can't code. How can I help?
A: Help me by making this extension known. I don't want "marketing", just spread the word. Let people know and if they like it, they will use it. Can't use it if they don't know about it, though, and I am always afraid people think I am spamming them when I try to advertise my work. Make this famous, is it too much to ask?

Q: I use LiChess in my own language and the new features are jarring in English
A: I've implemented the translation mechanism, but I need the texts for the various languages. If you provide them, I will make them available. Each tool starts with something like 

    intl={
      'en-US':{
        'options.general': 'General',
        'openingNameTitle': 'LiChess Tools - opening name'
      },
      'ro-RO':{
        'options.general': 'General',
        'openingNameTitle': 'LiChess Tools - numele deschiderii'
      }
    }

I plan to integrate Crowdin or something like this, but I don't have the time. You can look in Github and either tell me how to translate to your language or even create a Pull Request.

Q: Chrome sucks! Microsoft sold out! Can you make this work for my favorite browser?
A: Short answer: no. Long answer: I want to help people, so the more the merrier, but I also don't have a lot of resources to maintain code on a browser I don't use. Safari is a mess and extensions on it require to have a tool that only works on Macs and they ask you for money. Firefox has less than 5% of the market and refuses to implement the feature that makes LiChess Tools work. Opera already supports Chrome extensions. To be honest, it is not reasonable for me to bother with anything but Chrome. So long answer is also no :)

A: That's not my bug, it comes from LiChess. They have bugs, too.
Q: How could you possibly have answered before I asked the question?

Q: Did you actually think people were going to read this far down?
A: No.

Q: I told about this to all my friends, I came with feedback and constructive criticism and it feels like you ignored me. What gives?
A: For sure I want to take everything into consideration and act on requests as fast as possible, but it might be that I am caught up with something else. I thoroughly intend to give you and the extension as much attention as possible, so maybe make sure I got your message, first.

Hope it helps!

and has 0 comments

C# 3.0 introduced Object Initializer Syntax which was a game changer for code that created new objects or new collections. Here is a contrived example:

var obj = new ComplexObject
{
    // object initializer
    AnItem = new Item("text1"),
    AnotherItem = new Item("text2"),
    // collection initializer
    RestOfItems = new List<Item>
    {
        new Item("text3"),
        new Item("text4"),
        new Item("text5")
    },
    // indexer initializer
    [0]=new Item("text6"),
    [1]=new Item("text7")
};

Before this syntax was available, the same code would have looked like this:

var obj = new ComplexObject();
obj.AnItem = new Item("text1");
obj.AnotherItem = new Item("text2");
obj.RestOfItems = new List<Item>();
obj.RestOfItems.Add(new Item("text3"));
obj.RestOfItems.Add(new Item("text4"));
obj.RestOfItems.Add(new Item("text5"));
obj[0] = new Item("text6");
obj[2] = new Item("text7");

It's not like the number of lines has changed, but both the writability and readability of the code increase with the new syntax. At least that's why I think. However, outside these very simple scenarios, the feature feels like it's encumbering us or that it is missing something. Imagine you want to only add items to a list based on some condition. You might get a code like this:

var list = new List<Item>
{
    new Item("text1")
};
if (condition) list.Add(new Item("text2"));

We use the initializer for one item, but not for the other. We might as well use Add for both items, then, or use some cumbersome syntax that hurts more than it helps:

var list = new[]
{
    new Item("text1"),
    condition?new Item("text2"):null
}
.Where(i => i != null)
.ToList();

It's such an ugly syntax that Visual Studio doesn't know how to indent it properly. What to do? Software patterns to the rescue! 

Seriously now, people who know me know that I scoff at the general concept of software patterns, but the patterns themselves are useful and in this case, even the conceptual framework that I often deride is useful here. Because we are trying to initialize an object or a collection, which means we are attempting to build it. So why not use a Builder pattern? Here are two versions of the same code, one with extension methods (which can be used everywhere, but might pollute the member list for common objects) and another with an actual builder object created specifically for our purposes (which may simplify usage):

// extension methods
var list = new List<Item>()
    .Adding(new Item("text1"))
    .ConditionalAdding(condition, new Item("text2"));
...
public static class ItemListExtensions
{
    public static List<T> Adding<T>(this List<T> list, T item)
    {
        list.Add(item);
        return list;
    }
    public static List<T> ConditionalAdding<T>(this List<T> list, bool condition, T item)
    {
        if (condition)
        {
            list.Add(item);
        }
        return list;
    }
}

// builder object
var list = new ItemListBuilder()
    .Adding("text1")
    .ConditionalAdding(condition, "text2")
    .Build();
...
public class ItemListBuilder
{
    private readonly List<Item> list;

    public ItemListBuilder()
    {
        list = new List<Item>();
    }

    public ItemListBuilder Adding(string text)
    {
        list.Add(new Item(text));
        return this;
    }

    public ItemListBuilder ConditionalAdding(bool condition, string text)
    {
        if (condition)
        {
            list.Add(new Item(text));
        }
        return this;
    }

    public List<Item> Build()
    {
        return list.ToList();
    }
}

Of course, for just a simple collection with some conditions this might feel like overkill, but try to compare the two versions of the code: the one that uses initializer syntax and then the Add method and the one that declares what it wants to do, step by step. Also note that in the case of the builder object I've taken the liberty of creating methods that only take string parameters then build the list of Item, thus simplifying the syntax and clarifying intent.

I had this situation where I had to map an object to another object by copying some properties into collections and values of some type to other types and so on. The original code was building the output using a top-down approach:

public Output BuildOutput(Input input) {
  var output=new Output();
  BuildFirstPart(output, input);
  BuildSecondPart(output, input);
  ...
  return output;
}

public BuildFirstPart(Output output, Input input) {
  var firstSection = BuildFirstSection(input);
  output.FirstPart=new List<Part> {
    new Part(firstSection)
  };
  if (condition) {
    var secondSection=BuildSeconfSection(input);
    output.FirstPart.Add(new Part(secondSection));
  }
}

And so on and so on. I believe that in this case a fluent design makes the code a lot more readable:

var output = new Output {
  FirstPart = new List<Part>()
    .Adding(BuildFirstSection(input))
    .ConditionalAdding(condition, BuildSecondSection(input),
  SecondPart = ...
};

The "build section" methods would also be inlined and replaced with fluent design methods. In this way the structure of "the output" is clearly shown in a method that declares what it will build and populates the various members of the Output class with simple calculations, the only other methods that the builder needs. A human will understand at a glance what thing it will build, see its structure as a tree of code and be able to go to individual methods to see or change the specific calculation that provides a value.

The point of my post is that sometimes the very out-of-the-box features that help us a lot most of the time end up complicating and obfuscating our code in specific situations. If the code starts to smell, to become unreadable, to make you feel bad for writing it, then stop, think of a better solution, then implement it so that it is the best version for your particular case. Use tools when they are useful and discard them when other solutions might prove more effective.

and has 0 comments

Intro

  Some of the most visited posts on this blog relate to dependency injection in .NET. As you may know, dependency injection has been baked in in ASP.Net almost since the beginning, but it culminated with the MVC framework and the .Net Core rewrite. Dependency injection has been separated into packages from where it can be used everywhere. However, probably because they thought it was such a core concept or maybe because it is code that came along since the days of UnityContainer, the entire mechanism is sealed, internalized and without any hooks on which to add custom code. Which, in my view, is crazy, since dependency injection serves, amongst other things, the purpose of one point of change for class instantiations.

  Now, to be fair, I am not an expert in the design patterns used in dependency injection in the .NET code. There might be some weird way in which you can extend the code that I am unaware of. In that case, please illuminate me. But as far as I went in the code, this is the simplest way I found to insert my own hook into the resolution process. If you just want the code, skip to the end.

Using DI

  First of all, a recap on how to use dependency injection (from scratch) in a console application:

// you need the nuget packages Microsoft.Extensions.DependencyInjection 
// and Microsoft.Extensions.DependencyInjection.Abstractions
using Microsoft.Extensions.DependencyInjection;
...

// create a service collection
var services = new ServiceCollection();
// add the mappings between interface and implementation
services.AddSingleton<ITest, Test>();
// build the provider
var provider = services.BuildServiceProvider();

// get the instance of a service
var test = provider.GetService<ITest>();

  Note that this is a very simplified scenario. For more details, please check Creating a console app with Dependency Injection in .NET Core.

Recommended pattern for DI

  Second of all, a recap of the recommended way of using dependency injection (both from Microsoft and myself) which is... constructor injection. It serves two purposes:

  1. It declares all the dependencies of an object in the constructor. You can rest assured that all you would ever need for that thing to work is there.
  2. When the constructor starts to fill a page you get a strong hint that your class may be doing too many things and you should split it up.

  But then again, there is the "Learn the rules. Master the rules. Break the rules" concept. I've familiarized myself with it before writing this post so that now I can safely break the second part and not master anything before I break stuff. I am talking now about property injection, which is generally (for good reason) frowned upon, but which one may want to use in scenarios adjacent to the functionality of the class, like logging. One of the things that always bothered me is having to declare a logger in every constructor ever, even if in itself a logger does nothing to the functionality of the class.

  So I've had this idea that I would use constructor dependency injection EVERYWHERE, except logging. I would create an ILogger<T> property which would be automatically injected with the correct implementation at resolution time. Only there is a problem: Microsoft's dependency injection does not support property injection or resolution hooks (as far as I could find). So I thought of a solution.

How does it work?

  Third of all, a small recap on how ServiceProvider really works.

  When one does services.BuildServiceProvider() they actually call an extension method that does new ServiceProvider(services, someServiceProviderOptions). Only that constructor is internal, so you can't use it yourself. Then, inside the provider class, the GetService method is using a ConcurrentDictionary of service accessors to get your service. In case the service accessor is not there, the method from the field _createServiceAccessor is going to be used. So my solution: replace the field value with a wrapper that will also execute our own code.

The solution

  Before I show you the code, mind that this applies to .NET 7.0. I guess it will work in most .NET Core versions, but they could change the internal field name or functionality in which case this might break.

  Finally, here is the code:

public static class ServiceProviderExtensions
{
    /// <summary>
    /// Adds a custom handler to be executed after service provider resolves a service
    /// </summary>
    /// <param name="provider">The service provider</param>
    /// <param name="handler">An action receiving the service provider, 
    /// the registered type of the service 
    /// and the actual instance of the service</param>
    /// <returns>the same ServiceProvider</returns>
    public static ServiceProvider AddCustomResolveHandler(this ServiceProvider provider,
                 Action<IServiceProvider, Type, object> handler)
    {
        var field = typeof(ServiceProvider).GetField("_createServiceAccessor",
                        BindingFlags.Instance | BindingFlags.NonPublic);
        var accessor = (Delegate)field.GetValue(provider);
        var newAccessor = (Type type) =>
        {
            Func<object, object> newFunc = (object scope) =>
            {
                var resolver = (Delegate)accessor.DynamicInvoke(new[] { type });
                var resolved = resolver.DynamicInvoke(new[] { scope });
                handler(provider, type, resolved);
                return resolved;
            };
            return newFunc;
        };
        field.SetValue(provider, newAccessor);
        return provider;
    }
}

  As you can see, we take the original accessor delegate and we replace it with a version that runs our own handler immediately after the service has been instantiated.

Populating a Logger property

  And we can use it like this to do property injection now:

static void Main(string[] args)
{
    var services = new ServiceCollection();
    services.AddSingleton<ITest, Test>();
    var provider = services.BuildServiceProvider();
    provider.AddCustomResolveHandler(PopulateLogger);

    var test = (Test)provider.GetService<ITest>();
    Assert.IsNotNull(test.Logger);
}

private static void PopulateLogger(IServiceProvider provider, 
                                    Type type, object service)
{
    if (service is null) return;
    var propInfo = service.GetType().GetProperty("Logger",
                    BindingFlags.Instance|BindingFlags.Public);
    if (propInfo is null) return;
    var expectedType = typeof(ILogger<>).MakeGenericType(service.GetType());
    if (propInfo.PropertyType != expectedType) return;
    var logger = provider.GetService(expectedType);
    propInfo.SetValue(service, logger);
}

  See how I've added the PopulateLogger handler in which I am looking for a property like 

public ILogger<Test> Logger { get; private set; }

  (where the generic type of ILogger is the same as the class) and populate it.

Populating any decorated property

  Of course, this is kind of ugly. If you want to enable property injection, why not use an attribute that makes your intention clear and requires less reflection? Fine. Let's do it like this:

// Add handler
provider.AddCustomResolveHandler(InjectProperties);
...

// the handler populates all properties that are decorated with [Inject]
private static void InjectProperties(IServiceProvider provider, Type type, object service)
{
    if (service is null) return;
    var propInfos = service.GetType()
        .GetProperties(BindingFlags.Instance | BindingFlags.Public)
        .Where(p => p.GetCustomAttribute<InjectAttribute>() != null)
        .ToList();
    foreach (var propInfo in propInfos)
    {
        var instance = provider.GetService(propInfo.PropertyType);
        propInfo.SetValue(service, instance);
    }
}
...

// the attribute class
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class InjectAttribute : Attribute {}

Conclusion

I have demonstrated how to add a custom handler to be executed after any service instance is resolved by the default Microsoft ServiceProvider class, which in turn enables property injection, one point of change to all classes, etc. I once wrote code to wrap any class into a proxy that would trace all property and method calls with their parameters automatically. You can plug that in with the code above, if you so choose.

Be warned that this solution is using reflection to change the functionality of the .NET 7.0 ServiceProvider class and, if the code there changes for some reason, you might need to adapt it to the latest functionality.

If you know of a more elegant way of doing this, please let me know.

Hope it helps!

Bonus

But what about people who really, really, really hate reflection and don't want to use it? What about situations where you have a dependency injection framework running for you, but you have no access to the service provider builder code? Isn't there any solution?

Yes. And No. (sorry, couldn't help myself)

The issue is that ServiceProvider, ServiceCollection and all that jazz are pretty closed up. There is no solution I know of that solved this issue. However... there is one particular point in the dependency injection setup which can be hijacked and that is... the adding of the service descriptors themselves!

You see, when you do ServiceCollection.AddSingleton<Something,Something>, what gets called is yet another extension method, the ServiceCollection itself is nothing but a list of ServiceDescriptor. The Add* extensions methods come from ServiceCollectionServiceExtensions class, which contains a lot of methods that all defer to just three different effects:

  • adding a ServiceDescriptor on a type (so associating an type with a concrete type) with a specific lifetime (transient, scoped or singleton)
  • adding a ServiceDescriptor on an instance (so associating a type with a specific instance of a class), by default singleton
  • adding a ServiceDescriptor on a factory method (so associating a type with a constructor method)

If you think about it, the first two can be translated into the third. In order to instantiate a type using a service provider you do ActivatorUtilities.CreateInstance(provider, type) and a factory method that returns a specific instance of a class is trivial.

So, the solution: just copy paste the contents of ServiceCollectionServiceExtensions and make all of the methods end up in the Add method using a service factory method descriptor. Now instead of using the extensions from Microsoft, you use your class, with the same effect. Next step: replace the provider factory method with a wrapper that also executes stuff.

Since this is a bonus, I let you implement everything except the Add method, which I will provide here:

// original code
private static IServiceCollection Add(
    IServiceCollection collection,
    Type serviceType,
    Func<IServiceProvider, object> implementationFactory,
    ServiceLifetime lifetime)
{
    var descriptor = new ServiceDescriptor(serviceType, implementationFactory, lifetime);
    collection.Add(descriptor);
    return collection;
}

//updated code
private static IServiceCollection Add(
    IServiceCollection collection,
    Type serviceType,
    Func<IServiceProvider, object> implementationFactory,
    ServiceLifetime lifetime)
{
    Func<IServiceProvider, object> factory = (sp)=> {
        var instance = implementationFactory(sp);
        // no stack overflow, please
        if (instance is IDependencyResolver) return instance;
        // look for a registered instance of IDependencyResolver (our own interface)
        var resolver=sp.GetService<IDependencyResolver>();
        // intercept the resolution and replace it with our own 
        return resolver?.Resolve(sp, serviceType, instance) ?? instance;
    };
    var descriptor = new ServiceDescriptor(serviceType, factory, lifetime);
    collection.Add(descriptor);
    return collection;
}

All you have to do is (create the interface and then) register your own implementation of IDependencyResolver and do whatever you want to do in the Resolve method, including the logger instantiation, the inject attribute handling or the wrapping of objects, as above. All without reflection.

The kick here is that you have to make sure you don't use the default Add* methods when you register your services, or this won't work. 

There you have it, bonus content not found on dev.to ;)

and has 0 comments

  So I was happily minding my own business after a production release only for everything to go BOOM! Apparently, maybe because of something we did, but maybe not, the memory of the production servers was running out. Exception looked something like:

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
 at System.Reflection.Emit.TypeBuilder.SetMethodIL(RuntimeModulemodule, Int32tk ,BooleanisInitLocals, Byte[] body, Int32 bodyLength, 
    Byte[] LocalSig ,Int32sigLength, Int32maxStackSize, ExceptionHandler[] exceptions, Int32numExceptions ,Int32[] tokenFixups, Int32numTokenFixups)
 at System.Reflection.Emit.TypeBuilder.CreateTypeNoLock() 
 at System.Reflection.Emit.TypeBuilder.CreateType()
 at System.Xml.Serialization.XmlSerializationReaderILGen.GenerateEnd(String []methods, XmlMapping[] xmlMappings, Type[] types) 
 at System.Xml.Serialization.TempAssembly.GenerateRefEmitAssembly(XmlMapping []xmlMappings, Type[] types, StringdefaultNamespace ,Evidenceevidence)
 at System.Xml.Serialization.TempAssembly..ctor(XmlMapping []xmlMappings, Type[] types, StringdefaultNamespace ,Stringlocation, Evidenceevidence)
 at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMappingxmlMapping, Typetype ,StringdefaultNamespace, Stringlocation, Evidence evidence)
 at System.Xml.Serialization.XmlSerializer..ctor(Typetype, XmlAttributeOverrides overrides, Type[] extraTypes, 
     XmlRootAttributeroot, StringdefaultNamespace, Stringlocation, Evidence evidence)
 at System.Xml.Serialization.XmlSerializer..ctor(Typetype, XmlAttributeOverrides overrides) 

At first I thought there was something else eating away the memory, but the exception was repeatedly thrown at this specific point. And I did what every senior dev does: googled it! And I found this answer: "When an XmlSerializer is created, an assembly is dynamically generated and loaded into the AppDomain. These assemblies cannot be garbage collected until their AppDomain is unloaded, which in your case is never." It also referenced a Microsoft KB886385 from 2007 which, of course, didn't exist at that URL anymore, but I found it archived by some nice people.

What was going on? I would tell you, but Gergely Kalapos explains things much better in his article How the evil System.Xml.Serialization.XmlSerializer class can bring down a server with 32Gb ram. He also explains what commands he used to debug the issue, which is great!

But since we already know links tend to vanish over time (so much for stuff on the Internet living forever), here is the gist of it all:

  • XmlSerializer generates dynamic code (as dynamic assemblies) in its constructors
  • the most used constructors of the class have a caching mechanism in place:
    • XmlSerializer.XmlSerializer(Type)
    • XmlSerializer.XmlSerializer(Type, String)
  • but the others do not, so every time you use one of those you create, load and never unload another dynamic assembly

I know this is an old class in an old framework, but some of us still work in companies that are firmly rooted in the middle ages. Also since I plan to maintain my blog online until I die, it will live on the Internet for the duration.

Hope it helps!

  There is a common task in Excel that seems should have a very simple solution. Alas, when googling for it you get all these inexplainable crappy "tutorial" sites that either show you something completely different or something that you cannot actually do because you don't have the latest version of Office. Well, enough of this!

  The task I am talking about is just selecting a range of values and concatenating them using a specified separator, what in a programming language like C# is string.Join or in JavaScript you get the array join function. I find it very useful when, for example, I copy a result from SQL and I want to generate an INSERT or UPDATE query. And the only out of the box solution is available for Office 365 alone: TEXTJOIN.

  You use it like =TEXTJOIN(", ", FALSE, A2:A8) or =TEXTJOIN(", ", FALSE, "The", "Lazy", "Fox"), where the parameters are:

  • a delimiter
  • a boolean to determine if empty cells are ignored
  • a series or text values or a range of cells

  But, you can have this working in whatever version of Excel you want by just using a User Defined Function (UDF), one specified in this lovely and totally underrated Stack Overflow answer: MS Excel - Concat with a delimiter.

  Long story short:

  • open the Excel sheet that you want to work on 
  • press Alt-F11 which will open the VBA interface
  • insert a new module
  • paste the code from the SO answer (also copy pasted here, for good measure)
  • press Alt-Q to leave
  • if you want to save the Excel with the function in it, you need to save it as a format that supports macros, like .xlsm

And look at the code. I mean, it's ugly, but it's easy to understand. What other things could you implement that would just simplify your work and allow Excel files to be smarter, without having to code an entire Excel add-in? I mean, I could just create my own GenerateSqlInsert function that would handle column names, NULL values, etc. 

Here is the TEXTJOIN mimicking UDF to insert in a module:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Hope it helps!

  I haven't been working on the Sift string distance algorithm for a while, but then I was reminded of it because someone wanted it to use it to suggest corrections to user input. Something like Google's: "Did you mean...?" or like an autocomplete application. And it got me thinking of ways to use Sift for bulk searching. I am still thinking about it, but in the meanwhile, this can be achieved using the Sift4 algorithm, with up to 40% improvement in speed to the naïve comparison with each item in the list.

  Testing this solution, I've realized that the maxDistance parameter did not work correctly. I apologize. The code is now fixed on the algorithm's blog post, so go and get it.

  So what is this solution for mass search? We can use two pieces of knowledge about the problem space:

  • the minimum possible distance between two string of length l1 and l2 will always abs(l1-l2)
    • it's very easy to understand the intuition behind it: one cannot generate a string of size 5 from a string of size 3 without at least adding two new letters, so the minimum distance would be 2
  • as we advance through the list of strings, we have a best distance value that we keep updating
    • this molds very well on the maxDistance option of Sift4

  Thus armed, we can find the best matches for our string from a list using the following steps:

  1. set a bestDistance variable to a very large value
  2. set a matches variable to an empty list
  3. for each of the strings in the list:
    1. compare the minimum distance between the search string and the string in the list (abs(l1-l2)) to bestDistance
      1. if the minimum distance is larger than bestDistance, ignore the string and move to the next
    2. use Sift4 to get the distance between the search string and the string in the list, using bestDistance as the maxDistance parameter
      1. if the algorithm reaches a temporary distance that is larger than bestDistance, it will break early and report the temporary distance, which we will ignore
    3. if distance<bestDistance, then clear the matches list and add the string to it, updating bestDistance to distance
    4. if distance=bestDistance, then add the string to the list of matches

  When using the common Sift4 version, which doesn't compute transpositions, the list of matches is retrieved 40% faster on average than simply searching through the list of strings and updating the distance. (about 15% faster with transpositions) Considering that Sift4 is already a lot faster than Levenshtein, this method will allow searching through hundreds of thousands of strings really fast. The gained time can be used to further refine the matches list using a slower, but more precise algorithm, like Levenshtein, only on a lot smaller set of possible matches.

  Here is a sample written in JavaScript, where we search a random string in the list of English words:

search = getRandomString(); // this is the search string
let matches=[];             // the list of found matches
let bestDistance=1000000;   // the smaller distance to our search found so far
const maxOffset=5;          // a common value for searching similar strings
const l = search.length;    // the length of the search string
for (let word of english) {
    const minDist=Math.abs(l-word.length); // minimum possible distance
    if (minDist>bestDistance) continue;    // if too large, just exit
    const dist=sift4(search,word,maxOffset,bestDistance);
    if (dist<bestDistance) {
        matches = [word];                  // new array with a single item
        bestDistance=dist;
        if (bestDistance==0) break;        // if an exact match, we can exit (optional)
    } else if (dist==bestDistance) {
        matches.push(word);                // add the match to the list
    }
}

  There are further optimizations that can be added, beyond the scope of this post:

  • words can be grouped by length and the minimum distance check can be done on entire buckets of strings of the same lengths
  • words can be sorted, and when a string is rejected as a match, reject all string with the same prefix
    • this requires an update of the Sift algorithm to return the offset at which it stopped (to which the maxOffset must be added)

  I am still thinking of performance improvements. The transposition table gives more control over the precision of the search, but it's rather inefficient and resource consuming, not to mention adding code complexity, making the algorithm harder to read. If I can't find a way to simplify and improve the speed of using transpositions I might give up entirely on the concept. Also, some sort of data structure could be created - regardless of how much time and space is required, assuming that the list of strings to search is large and constant and the number of searches will be very big.

  Let me know what you think in the comments!

  Today I had a very interesting discussion with a colleague who optimized my work in Microsoft's SQL Server by replacing a table variable with a temporary table. Which is annoying, since I've done the opposite plenty of time, thinking that I am choosing the best solution. After all, temporary tables have the overhead of being stored into tempdb, on the disk. What could possibly be wrong with using a table variables? I believe this table explains it all:

First of all, the storage is the same. How? Well, table variables start off in memory, but if they go above a limit they get saved to tempdb! Another interesting bit is the indexes. While you can create primary keys on table variables, you can't use other indexes - that's OK, though, because you would hardly need very complex variable tables. But then there is the parallelism: none for table variables! As you will see, that's rather important. At least table variables don't cause recompilations. And last, but certainly not least, perhaps the most important difference: statistics! You don't have statistics on table variables.

Let's consider my scenario: I was executing a stored procedure and storing the selected values in a table variable. This SP had the single reason to filter the ids of records that I would then have to extract - joining them with a lot of other tables - and could return 200, 800 or several hundred thousand rows.

With a table variable this means :

  1. when inserting potentially hundreds of thousands of rows I would have no parallelism (slow!) and it would probably save it to tempdb anyway (slow!)
  2. when joining other tables with it, not having statistics, it would just treat it like a short list of values, which it potentially wasn't, and looping through it : Table Spool (slow!)
  3. various profiling tools would show the same or even less physical reads and the same SQL server execution time, but the CPU time would be larger than execution time (hidden slow!)

This situation has been improved considerably in SQL Server 2019, to the point that in most cases table variables and temporary tables show the same performance, but versions previous to that would show this to a larger degree.

And then there are hacks. For my example, there is reason why parallelism DOES occur:

So are temporary tables always better? No. There are several advantages of table variables:

  1. they get cleared automatically at the end of their scope
  2. result in fewer recompilations of stored procedures
  3. less locking and resources, since they don't have transaction logs

For many simple situations, like where you want to generate some small quantity of data and then work with that, table variables are best. However, as soon as the data size or scenario complexity increases, temporary tables become better.

As always, don't believe me, test! In SQL everything "depends", you can't rely on fixed rules like "X is always better" so profile your particular scenarios and see which solution is better.

Hope it helps!

  I had this situation where I was trying to optimize a query. And after some investigation I've stumbled upon something strange: querying on the primary key was generating a lot of reads. I was joining my table with a temporary table of 10 ids and there were 630 reads! How come?

  At first I thought it was because the way indexes work. The primary key was comprised of RowId and RowDate and, even if I knew theoretically searching by RowId should use the primary key, the evidence was against me: when querying by RowId and RowDate I would get the expected 10 reads.

  I created two queries, one with and one without RowDate. I then compared their execution plans. They were identical! Only one took a lot longer, specifically in the Index Seek (which used correctly the primary key). When I looked at the properties for that plan element, I saw something strange:

Actual Partitions Accessed 1..63!

I then realized that the table was partitioned on the RowDate column. In this case, RowDate takes precedence to any indexed column! You might think of partitioning a table like forcefully adding the partition columns to every index in the table, including the primary key. In fact, a partitioned table acts like a number of separate tables with the same definition (columns, indexes, etc.), just different data. The indexes work on each separate partition. When you partition a table, you also partition its indexes.

In truth, I would have expected the query execution plan to show the partition split as a separate step. I understand it's hard to conceptualize it without creating as many execution paths as there are partitions, but still, there should be an indication in the shape of the plan that makes it clear you are querying on multiple partitions.

Once RowDate was used, the SQL engine would choose the one partition of my row, then use the primary key index to seek it. Instead of 63*10 reads, just 10 reads, the number of the rows in the id table.

So be careful when you use table partitioning to ALWAYS use the partition columns in the queries for the table, else you will get as many parallel searches as there are partitions, regardless of the indexes you created, as they are also partitioned.

Hope that helps!

  This is a very basic tutorial on how to access Microsoft SQL Server data via SQL queries. Since these are generic concepts, they will be applicable in most other SQL variants out there. My hope is that it will provide the necessary tools to quickly "get into it" without having to read (or understand) too much. Where you go from there is on you.

  There are a lot of basic concepts about SQL, this post will be pretty long.

Table of contents

Connecting to a database

  Let's start with tooling. To access a database you will need SQL Server Management Studio, in my case version 2022, but I will not do anything complicated with it here, therefore any version will do just fine. I will assume you have it installed already as installation is beyond the scope of the blog post. Starting it will prompt for a connection:

  To connect to the local computer, the server will be either . or (local) or the computer name. You can of course connect to any server and you can specify the "instance" and the port number as well. An instance is a specific named installation of SQL server which allows one to have multiple installations (and even versions) of SQL Server. In fact, each instance has its own port, so specifying the port number will ignore the name of the instance. The default port is usually 1433.

  Example of connection server strings: Computer1\SQLEXPRESS, sql.corporate.com,1433, (local), .

  The image here is from a connection to the local machine using Windows Authentication (your windows user). You can connect using SQL Server Authentication, which means providing a username and a password, or using one of the more modern Azure Active Directory methods.

  I will also assume that the connection parameters are known to you, so let's go to the next step.

  Once connected, the Object Explorer window will display the connection you've opened.

  Expanding the Databases node will show the available databases.

  Expanding a database node we get the objects that are part of the database, the most important being:

  • Tables - where the actual data resides
  • Views - abstractions over more complex queries that behave like tables as much as possible, but with some restrictions
  • Stored Procedures - SQL code that can be executed with parameters and may return data results
  • Functions - SQL code that can be executed and returns a value (which can be scalar, like a number of string, or a table type, etc.) 

  In essence they are the equivalent of data stores and code that is executed to use those stores. Views, SPs and functions will not be explained in this post, but feel free to read about them afterwards.

  If one expands a table node, the child nodes will contains various things, the most important of which are:

  • Columns - the names and types of each column in the table
  • Indexes - data structures designed to increase performance to various ways of accessing the data in the table
  • Constraints and Keys - logical restrictions and relationships between tables

  Tables are kind of like Excel sheets, they have rows (data records) and columns (record properties). The power of SQL is a way to declare what you want from tabular representations of data and get the results quickly and efficiently.

  Last thing I want to show from the graphical interface is right clicking on a table node, which shows multiple options, including generating simple operations on the table, the CRUD (Create, Read, Update, Delete) operations mostly, which in SQL are called INSERT, SELECT, UPDATE and DELETE respectively.

  The keywords are traditionally written in all caps, I am not shouting at you. Depending on your preferences and of course the coding standards that apply to your project you can capitalize SQL code however you like. SQL is case insensitive.

Anyway, whatever you are going to choose to "script" it's going to open a so called query window and show you a text with the query. You then have the option of executing it. Normally no one uses the UI to generate scripts except for getting the column names in order for SELECT or INSERT operations. Most of the time you will just right click on a database and choose New Query or select a database and press Ctrl-N, with the same result.

Getting data from tables

Finally we get to doing something. The operation to read data from SQL is called SELECT. One can specify the columns to be returned or just use * to get them all. It is good practice to always specify the column names in production code, even if you intend to select all columns, as the output of the query will not change if we add more columns in the future. However, we will not be discussing software projects, just how to get or change the data using SQL server, so let's get to it.

The simplest select query is: SELECT * FROM MyTable, which will return all columns of all records of the table. Note that MyTable is the name of a table and the least specific way of accessing that table. The same query can be written as: SELECT * FROM [MyDatabase].[dbo].[MyTable], specifying the database name, the schema name (default one is dbo, but your database can use multiple ones) and only then the table name.

The square bracket syntax is usually not required, but might be needed in special cases, like when a column has the same name as a keyword or if an object has spaces or commas in it (never a good idea, but a distinct possibility), for example: SELECT [Stupid,column] FROM [Stupid table name with spaces]. Here we are selecting a badly named column from a badly named table. Removing the square brackets would result in a syntax error.

In the example above we selected stuff from table CasesSince100 and we got tabular results for every record and the columns defined in the table. But that is not really useful. What we want to do when getting data is:

  • getting data from specific columns
  • formatting the data for our purposes
  • filtering the data on conditions
  • grouping the data
  • ordering the results

So here is a more complex query:

-- everything after two dashes in a line is a comment, ignored by the engine
/* there is also
   a multiline comment syntax */
SELECT TOP 10                            -- just the first 10 records
    c.Entity as Country,                 -- Entity will be returned with the name Country
    CAST(c.[Date] as Date) as [Date],    -- Unfortunate naming, as Date is also a type
    c.cases as Cases                     -- capitalized alias
FROM CasesSince100 c                     -- source for the data, aliased as 'c'
WHERE c.Code='ROU'                       -- conditions to filter by
    AND c.[Date]>'2020-03-01'
ORDER BY c.[Date] DESC                   -- ordering in descending order

  The query above will return at most 10 rows, only for Romania, for dates larger than March 2020, but ordered from the newest to oldest. Data returned will be the country name, the date (which was originally a DATETIME and now is cast to a timeless DATE type) and the number of cases.

  Note that I have aliased all columns, so the resulting table has columns named as the aliases. I've also aliased the table name as 'c', which helps in several ways. First of all, Intellisense works better and faster when specifying the table name. All you have to do is type c. and the list of columns will pop up and be filtered as you type. The second reason will become apparent when I am talking about updating and deleting. For the moment just remember that it's a good idea to alias your tables.

  You can alias a table by specifying a name to call it by next to its own name and optionally using 'as', like SELECT ltn.* FROM Schema.LongTableName as ltn. It helps differentiating between ambiguous names (like if two joined tables have columns with the same name), simplifying the code for long named tables and helping with code completion. Even when aliased, the table name can be used and one can specify or ignore the name of the table if the column names are unambiguous.

Of course these are trivial examples. The power of SQL is that you can get information from multiple sources, aggregate them and structure your database for quick access. More advanced concepts are JOINs and indexes, and I hope you will read until I get there, but for now let's just go through the very basics.

Here is another query that groups and aggregates data:

SELECT TOP 10                            -- top 10 results
    c.Entity as Country,                 -- country name
    SUM(CAST(c.cases as INT)) as Cases   -- cases is text, so we transform it to int
FROM CasesSince100 c
WHERE YEAR([Date])=2020                  -- condition applies a function to the date
GROUP BY c.Entity                        -- groups by country
HAVING SUM(CAST(c.cases as INT))<1000000 -- this is filtering on grouped values
ORDER BY SUM(CAST(c.cases as INT)) DESC  -- order on sum of cases

This query will show us the top 10 countries and the total sum of cases in year 2020, but only for countries where that total is less than a million. There is a lot to unpack here:

  • cases column is declared as NVARCHAR(150) meaning Unicode strings of varied length, but at most 150 characters, so we need to cast it to INT (integer) to be able to apply summing to it
  • there are two different ways of filtering: WHERE, which applies to the data before grouping, then HAVING, which applies to data after grouping
  • filtering, grouping, ordering all work with unaliased columns, so even if Entity is returned as Country, I cannot do WHERE Country='Romania'
  • grouping allows to get a row for each combination of the columns the grouping is done and compute some sort of aggregation (in the case above, a sum of cases per country)

Here are the results:

Let me rewrite this in a way that is more readable using what is called a subquery, in other words a query from which I will query once again:

SELECT TOP 10
    Country,
	SUM(Cases) as Cases
FROM (
    SELECT
        c.Entity as Country,
        CAST(c.cases as INT) as Cases,
	    YEAR([Date]) as [Year]
FROM CasesSince100 c
) x
WHERE [Year]=2020
GROUP BY Country
HAVING SUM(Cases)<1000000
ORDER BY Cases DESC

Note that I still have to use SUM(Cases) in the HAVING clause. I could have grouped it in another subquery and selected again and so on. In order to select from a subquery, you need to name it (in our case, we named it x). Also I selected Country from x, which I could have also written as x.Country. As I said before, table names (aliased or not) are optional if the column name if unambiguous. Also you may notice that I've given a name to the summed column. I could have skipped that, but that would mean the resulting columns would have had no name and the query itself would have been difficult to use in code (extracted column values would have had to be retrieved by index and not by name, which is never recommended).

If you think about it, the order of the clauses in a SELECT operation has a major flaw: you are supposed to write SELECT, then specify what columns you want and only then specify where you want the columns to be read from. This makes code completion problematic, which is why the in code query language for .NET (LInQ) puts the selection at the end. But even so there is a trick:

  • SELECT * and then complete the query
  • go back and replace the * with the column names you want to extract (you will now have Intellisense code completion)
  • the alias of the tables will now come in handy, but even without aliases one can press Ctrl-Space and get a list of possible values to select

Defining tables and inserting data

Before we start inserting information, let's create a table:

CREATE TABLE Food(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FoodName NVARCHAR(100),
    Quantity INT
)

One important concept in SQL is the primary key. It is a good idea in most cases that your tables have a primary key which identifies each record uniquely and also makes them easy to reference. Let me give you an example. Let's assume that we would put no Id column in our Food table and then we would accidentally add cheese twice. How would you reference the first record as opposed to the second? How would you delete the second one?

A primary key is actually just a special case of a unique index, clustered by default. We will get to indexes later, so don't worry about that yet. Enough to remember that it is fastest (most efficient) to find records by the primary key than any other column combination and the way records are uniquely identified. 

The IDENTITY(1,1) notation tells SQL Server that we will not insert values in that column and instead let it put values starting with 1, then increasing with 1 each time. That functionality will become clear when we INSERT data in the table:

INSERT INTO Food(FoodName,Quantity)
VALUES('Bread',1),('Cheese',1),('Pork',2),('Chilly',10)

Selecting from our Food table now gets us these results:

As you can see, we've inserted four records, by only specifying two out of three columns - we skipped Id. Yet SQL has filled the column with values from 1 to 4, starting with 1 and incrementing each time with 1.

The VALUES syntax is specifying inline data, but we could, in fact, insert into a table the results of a query, something like this:

INSERT INTO Food(FoodName,Quantity)
SELECT [Name],Quantity
FROM Store
WHERE [Type]='Food'

There is another syntax for insert that is useful with what are called temporary tables, tables created for the purpose of your session (lifetime of the query window) and that will automatically disappear once the session is over. It looks like this:

SELECT FoodName,Quantity
INTO #temp
FROM Food

This will create a table (temporary because of the # sign in front of it) that will have just FoodName and Quantity as columns, then proceed on saving the data there. This table will not have a primary key nor any types of indexes and it will work as a simple dump of the data selected. You can add indexes later or alter the table in any way you want, it works just like a regular table. While a convenient syntax (you don't have to write a CREATE TABLE query or think of the type of columns) it has a limited usefulness and I recommend not using it in application code.

Just as one creates a table, there are DROP TABLE and ALTER TABLE statements that delete or change the structure of the table, but we won't go into that.

Changing existing data

So now we have some data in a table that we have defined. We will see how the alias syntax I discussed in the SELECT section will come in handy. In short, I propose you use just two basic syntax forms for all CRUD operations: one for INSERT and one for SELECT, UPDATE and DELETE.

But how can you use the same syntax for statements that are so different, I hear you ask? Let me give you some example of similar code doing just that before I dive in what each operation does.

SELECT *
FROM Food f
WHERE f.Id=4

UPDATE f
SET f.Quantity=9
FROM Food f
WHERE f.Id=4

DELETE FROM f
FROM Food f
WHERE f.Id=4

The last two lines of all operations are exactly the same. These are simple queries, but imagine you have a complex one to craft. The first thing you want to see is that you are updating or deleting the right thing, therefore it makes sense to start with a SELECT query instead, then change it to a DELETE or UPDATE when satisfied. You see I UPDATE and DELETE using the alias I gave the table.

When first learning UPDATE and DELETE statements, one usually gets to this syntax:

UPDATE Food     -- using the table name is cumbersome if in a complex query
SET Quantity=9  -- unless using Food.Quantity and Food.Id
WHERE Id=4      -- you don't get easy Intellisense

DELETE          -- this seems a lot easier to remember
FROM Food       -- but it only works with one table in a simple query
WHERE Id=4

I've outlined some of the reasons I don't use this syntax in the comments, but the most important reason why one shouldn't use them except for very simplistic cases is that you are trying to create a query to destructively change the data in the database and there is no fool proof way to duplicate the same logic in a SELECT query to verify what you are going to change. I've seen people (read that as: I was dumb enough to do it myself) who created an entire different SELECT statement to verify what they would do, then realize to their horror the statements were not equivalent and they had updated or deleted the wrong thing!

OK, let's look at UPDATE and DELETE a little closer.

One of the useful clauses for these statements is, just like with SELECT, the TOP clause, which instructs SQL to affect just a finite number of rows. However, because TOP has been added later for write operations, you need to encase the value (or variable) in parentheses. For SELECT you can skip the parentheses for constant values (you still need them for variables)

DELETE TOP (10) FROM MyTable

Another interesting clause, that frankly I have not used a lot, but is essential in some specific cases, is OUTPUT. One can delete or update some rows and at the same time get the rows they have changed. The reason being that first of all in a DELETE statement the rows will be gone, so you won't be able to SELECT them again. But even in an UPDATE operation, the rows chosen to be updated by a query may not be the same if you execute them again. 

SQL does not guarantee the order of rows unless specifically using ORDER BY. So if you execute SELECT TOP 10 * FROM MyTable twice, you may get two different results. Moreover, between the time you UPDATE some rows and you SELECT them in another query, things may change because of other processes running at the same time on the same data.

So let's say we have some for of Invoices and Items tables that reference each other. You want to delete one invoice and all the items associated with it. There is no way of telling SQL to DELETE from multiple tables at the same time, so you DELETE the invoice, OUTPUT its Id, then delete the items for that Id.

CREATE TABLE #deleted(Id INT) -- temporary table, but explicitly created

DELETE FROM Invoice 
OUTPUT Deleted.Id    -- here Deleted is a keyword
INTO #deleted        -- the Id from the deleted rows will be stored here
WHERE Id=2           -- and can be even be restored from there

DELETE 
FROM Item
WHERE Id IN (
  SELECT Id FROM #deleted
)  -- a subquery used in a DELETE statement

-- same thing can be written as:
DELETE FROM i
FROM Item i
INNER JOIN #deleted d  -- I will get to JOINs soon
ON i.Id=d.Id

I have been informed that the INTO syntax is confusing and indeed it is:

  • SELECTing INTO will create a new table with results and throw an exception if the table already exists. The table will have the names and types of the selected values, which may be what one wants for a quick data dump, but it may also cause issues. For example the following query would throw an exception:
    SELECT 'Blog' as [Name]
    INTO #temp
    
    INSERT INTO #temp([Name]) -- String or binary data would be truncated error
    VALUES('Siderite')
    ​

    because the Name column of the new temporary table would be VARCHAR(4), just like 'Blog' and 'Siderite' would be too long

  • UPDATEing or DELETEing with OUTPUT INTO will require an existing table with the same number and types of columns as the columns specified in the OUTPUT clause and will throw an exception if it doesn't exist

One can use derived values in UPDATE statements, not just constants. One can reference the columns already existing or use any type of function that would be allowed in a similar SELECT statement. For example, here is a query to get the tax value of each row and the equivalent update to store it into a separate column:

SELECT
    i.Price, 
    i.TaxPercent, 
    i.Price*(i.TaxPercent/100) as Tax  -- best practice: SELECT first
FROM Item i

UPDATE i
SET Tax = i.Price*(i.TaxPercent/100)   -- UPDATE next
FROM Item i

So here we first do a SELECT, to see if the values we have and calculate are correct and, if satisfied, we UPDATE using the same logic. Always SELECT before you change data, so you know you are changing the right thing.

There is another trick to help you work safely, one that works on small volumes of data, which involves transactions. Transactions are atomic operations (all or nothing) which are defined by starting them with BEGIN TRANSACTION and are finalized with either COMMIT TRANSACTION (save the changes to the database) or ROLLBACK TRANSACTION (revert changes to the database). Transactions are an advanced concept also, so read about it yourself, but remember one can do the following:

  • open a new query window
  • execute BEGIN TRANSACTION
  • do almost anything in the query window
  • if satisfied with the result execute COMMIT TRANSACTION
  • if any issue with what you've done execute ROLLBACK TRANSACTION to undo the changes

Note that this only applies for stuff you do in that query window. Also, all of these operations are being saved in the log of the database, so this works only with small amounts of data. Attempting to do this with large amounts of data will practically duplicate it on disk and take a long time to execute and revert.

The NULL value

We need a quick primer on what NULL is. NULL is a placeholder for a value that was not set or is considered unknown. It's a non-value. It is similar to null in C# or JavaScript, but with some significant differences applicable to SQL only. For example, a NULL value (an oxymoron for sure) will never be equal to (or not equal to) or less than or greater than anything. One might expect to get all the values in a table in these two queries: SELECT * FROM MyTable WHERE Value>5 and SELECT * FROM MyTable WHERE Value<=5. But if any rows will have NULL for a Value, then they will not appear in any of the query results. That applies to the negation operator NOT as well: SELECT * FROM MyTable WHERE NOT (Value>5).

This behavior can be changed by using SET ANSI_NULLS OFF, but I am yet to see a database that has ever been set up like this.

To check if a value is or is not NULL, one uses the IS and IS NOT syntax :)

SELECT *
FROM MyTable
WHERE MyValue IS NOT NULL

The NULL concept will be used a lot in the next chapter.

Combining data from multiple sources

We finally go to JOIN operations. In most scenarios, you have a database containing multiple table, with intricate connections between them. Invoices that have items, customers, the employee that processed it, dates, departments, store quantities, etc., all referencing something. Integrating data from multiple tables is a complex subject, but I will touch just the most common and important parts:

  • INNER JOIN
  • OUTER JOIN
  • EXISTS
  • UNION / UNION ALL

Let's write a query that displays the name of employees and their department. I will show the CREATE TABLE statements, too, in order to see where we get the data from:

CREATE TABLE Employee (
  EmployeeId INT,          -- Best practice: descriptive column names
  FirstName NVARCHAR(100),
  LastName NVARCHAR(100),
  DepartmentId INT)        -- Best practice: use same name for the same thing

CREATE TABLE Department (
  DepartmentId INT,        -- same thing here
  DepartmentName NVARCHAR(100)
)

SELECT
    CONCAT(FirstName,' ',LastName) as Employee,
    DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId=d.DepartmentId

Here it is: INNER JOIN, a clause that combines the data from two tables based ON a condition or series of conditions. For each row of Employee we are looking for the corresponding row of Department. In this example, one employee belongs to only one department, but a department can hold multiple employees. It's what we call a "one to many relationship". One can have "one to one" or "many to many" relationships as well. That is very important when trying to gauge performance (and number of returned rows).

Our query will only find at most one department for each employee, so for 10 employees we will get at most 10 rows of data. Why do I say "at most"? Because the DepartmentId for some employees might not have a corresponding department row in the Department table. INNER JOIN will not generate records if there is no match. But what if I want to see all employees, regardless if their department exists or not? Then we use an OUTER JOIN:

SELECT
    CONCAT(FirstName,' ',LastName) as Employee,
    DepartmentName
FROM Employee e
LEFT OUTER JOIN Department d
ON e.DepartmentId=d.DepartmentId

This will generate results for each Employee and their Department, but show a NULL (without value) result if the department does not exist. In this case LEFT is used to define that there will be rows for each record in the left table (Employee). We could have used RIGHT, in which case we would have rows for each department and NULL values for departments that have no employees. There is also the FULL OUTER JOIN option, in which case we will get both departments with NULL employees if none are attached and employees with NULL departments in case the department does not exist (or the employee is not assigned - DepartmentId is NULL)

Note that the keywords INNER and OUTER are completely optional. JOIN is the same thing as INNER JOIN and LEFT JOIN is the same as LEFT OUTER JOIN. I find that specifying them makes the code more readable, but that's a personal choice.

The OUTER JOINs are sometimes used in a non intuitive way to find records that have no match in another table. Here is a query that shows employees that are not assigned to a department:

SELECT
    CONCAT(FirstName,' ',LastName) as Employee
FROM Employee e
LEFT OUTER JOIN Department d
ON e.DepartmentId=d.DepartmentId
WHERE d.DepartmentId IS NULL

Until now, we talked about the WHERE clause as a filter that is applied first (before grouping) so one might intuitively have assumed that the WHERE clauses are applied immediately on the tables we get the data from. If that were the case, then this query would never return anything, because every Department will have a DepartmentId. Instead, what happens here is the tables are LEFT JOINed, then the WHERE clause applies next. In the case of unassigned employees, the department id or name will be NULL, so that is what we are filtering on.

So what happens above is:

  • the Employee table is LEFT JOINed with the Department table
  • for each employee (left) there will be rows that contain the values of the Employee table rows and the values of any matched Department table rows
  • in the case there is no match, NULL values will be returned for the Department table for all columns
  • when we filter by Department.DepartmentId being NULL we don't mean any Department that doesn't have an Id (which is impossible) but any Employee row with no matching Department row, which will have a NULL value where the Department.DepartmentId value would have been in case of a match.
  • not matching can happen for two reasons: Employee.DepartmentId is NULL (meaning the employee has not been assigned to a department) or the value stored there has no associated Department (the department may have been removed for some reason)

Also, note that if we are joining tables on some condition we have to be extra careful with NULL values. Here is how one would join two tables on VARCHAR columns being equal even when NULL:

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON (t1.Value IS NULL AND t2.Value IS NULL) OR t1.Value=t2.Value

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON ISNULL(t1.Value,'')=ISNULL(t2.Value,'')

The second syntax seems promising, doesn't it? It is more readable for sure. Unfortunately, it introduces some assumptions and also decreases the performance of the query (we will talk about performance later on). The assumption is that if Value is an empty string, then it's the same as having no value (being NULL). One could use something like ISNULL(Value,'--NULL--') but now it starts looking worse.

There are other ways of joining two tables (or queries, or table variables, or table functions, etc.), for example by using the IN or the EXISTS/NOT EXISTS clauses or subqueries. Here are some examples:

SELECT *
FROM Table1
WHERE MyValue IN (SELECT MyValue FROM Table2)

SELECT *
FROM Table1
WHERE MyValue = (SELECT TOP 1 MyValue FROM Table2 WHERE Table1.MyValue=Table2.MyValue)

SELECT *
FROM Table1
WHERE NOT EXISTS(SELECT * FROM Table2 WHERE Table1.MyValue=Table2.MyValue)

These are less readable, usually have terrible performance and may not return what you expect them to return.

When I was learning SQL, I thought using a JOIN would be optimal on all cases and subqueries in the WHERE clause were all bad, no exception. That is, in fact, false. There is a specific case where it is better to use a subquery in WHERE instead of JOIN, and that is when trying to find records that have at least one match. It is better to use EXISTS because it is short-circuiting logic which leads to better performance.

Here is an example with different syntax for achieving the same goal:

SELECT DISTINCT d.DepartmentId
FROM Department d
INNER JOIN Employee e
ON e.DepartmentId=d.DepartmentId

SELECT d.DepartmentId
FROM Department d
WHERE EXISTS(SELECT * FROM Employee e WHERE e.DepartmentId=d.DepartmentId)

Here, the search for departments with employees will return the same thing, but in the first situation it will get all employees for all departments, then list the department ids that had employees, while in the second query the department will be returned the moment just one employee that matches is found.

There is another way of combining data from two sources and that is to UNION two or multiple result sets. It is the equivalent of taking rows from multiple sources of the same type and showing them together in the same result set.

Here is a dummy example:

SELECT 1 as Id
UNION
SELECT 2
UNION
SELECT 2

And we execute it and...

What happened? Shouldn't there have been three values? Somehow, when copy pasting the silly example, you added two identical values. UNION will add only distinct values to the result set. using UNION ALL will show all three values.

SELECT 1 as Id
UNION ALL
SELECT 2
UNION ALL
SELECT 2

SELECT DISTINCT Id FROM (
  SELECT 1 as Id
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 2
) x

The first query will return 1,2,2 and the second will be the equivalent of the UNION one, returning 1 and 2. Note the DISTINCT keyword.

My recommendation is to never use UNION and instead use UNION ALL everywhere, unless it makes some kind of sense for a very specific scenario, because the operation to DISTINCT values is expensive, especially for many and/or large columns. When results are supposed to be different anyway, UNION and UNION ALL will return the same output, but UNION is going to perform one more pointless distinct operation.

After learning about JOIN, my request to start with SELECT queries and only them modify them to be UPDATE or DELETE begins to make more sense. Take a look at this query:

UPDATE d
SET ToFindManager=1
--SELECT *
FROM Department d
LEFT OUTER JOIN Employee e
ON d.DepartmentId=e.DepartmentId
AND e.[Role]='Manager'
WHERE e.EmployeeId IS NULL

This will set ToFindManager in departments that have no corresponding manager. But if you select the text from SELECT * on and then execute, you will get the results that you are going to update. Same query, executing by selecting different sections of it will either verify or perform the operation.

Indexes and relationships. Performance.

We have seen how to define tables, how to insert, select, update and delete records from them. We've also seen how to integrate data from multiple sources to get what we want. The SQL engine will take our queries, try to understand what we meant, optimize the execution, then give us the results. However, with large enough data, no amount of query optimization will help if the relationships between tables are not properly defined and tables are not prepared for the kind of queries we will execute.

This requires an introduction to indexes, which is a rather advanced idea, both in terms of how to create, use, debug and profile, but also as a computer science concept. I will try to stick to the basics here, and you go and get more in depth from here.

What is an index? It's a separate data structure that will allow quick access to specific parts of the original data. A table of contents in a blog post is an index. It allows you to quickly jump to the section of the post without having to read it all. There are many types of indexes and they are used in different ways.

We've talked about the primary key: (unless specified differently) it's a CLUSTERED, UNIQUE index. It can be on a single column or a combination of columns. Normally, the primary key will be the preferred way to find or join records on, as it physically rearranges the table records in order and insures only one record has a particular primary key.

The difference between CLUSTERED and NONCLUSTERED indexes is that a table can have only one clustered index, which will determine the physical order of record data on the disk. As an example, let's consider a simple table with a single integer column called X. If there is a clustered index on X, then when inserting new values, data will be moved around on the disk to account for this:

CREATE TABLE Test(X INT PRIMARY KEY)

INSERT INTO Test VALUES (10),(1),(20)

INSERT INTO Test VALUES (2),(3)

DELETE FROM Test WHERE X=1

After inserting 10,1 and 20, data on the disk will be in the order of X: a 1, followed by a 10, then a 20. When we insert values 2 and 3, 10 and 20 will have to be moved so that 2 and 3 are inserted. Then, after deleting 1, all data will be moved so that the final physical order of the data (the actual file on the disk holding the database data) will be 2,3,10,20. This will help optimize not only finding the rows, but also efficiently reading them from disk (disk access is the most expensive operation for a database). 

Note: deletion is working a little differently in reality, but in theory this is how it would work.

Nonclustered indexes, on the other hand, keep their own order and reference the records from the original data. For such a simple example as above, the result would be almost identical, but imagine you have the Employee table and you create a nonclustered index on LastName. This means that behind the scenes, a data structure that looks like a table is created, which is ordered by LastName and contains another column for EmployeeId (which is the primary key, the identifier of an employee). When you do SELECT * FROM Employee ORDER BY LastName, the index will be used to first get a list of ids, then select the values from them.

A UNIQUE index also insures that no two records will have the same combination of values as defined therein. In the case of the primary key, there cannot be two records with the same id. But one can imagine something like:

CREATE UNIQUE INDEX IX_Employee_Name ON Employee(FirstName,LastName)

INSERT INTO Employee (FirstName,LastName)
VALUES('Siderite','Blog')

IX_Employee_Name is a nonclustered unique index on FirstName and LastName. If you execute the insert, it will work the first time, but fail the second time:

There is another type of index-like structure called a foreign key. It should be used to define logical relationships between tables. For the Department table, DepartmentId should be a primary key, but in the Employee table, DepartmentId should be defined as a foreign key connecting to the column in the Department table.

Important note: a foreign key defines the relationship, but doesn't index the column. A separate index should be added on the Employee.DepartmentId column for performance reasons.

I don't want to get into foreign keys here. Suffice to say that once this relationship is defined, some things can be achieved automatically, like deleting corresponding Item records by the engine when deleting Invoices. Also the performance of JOIN queries increases.

Indexes can be used not only on equality, but also other more complex cases: numerical ranges, prefixes, etc. It is important to understand how they are structured, so you know when to use them.

Let's consider the IX_Employee_Name index. The index is practically creating a tree structure on the concatenation of the first and last name of the employee and stores the primary key columns for the table for reference. It will work great for increasing performance of a query like SELECT * FROM Employee ORDER BY FirstName or SELECT * FROM Employee WHERE FirstName LIKE 'Sid%'. However it will not work for LastName queries or contains queries like SELECT * FROM Employee ORDER BY LastName or SELECT * FROM Employee WHERE FirstName LIKE '%derit%'.

That's important because sometimes simpler queries will take more resources than more complicated ones. Here is a dumb example:

CREATE INDEX IX_Employee_Dumb ON Employee(
    FirstName,
    DepartmentId,
    LastName
)

SELECT *
FROM Employee e
WHERE e.FirstName='Siderite'
  AND e.LastName='Blog'

SELECT *
FROM Employee e
WHERE e.FirstName='Siderite'
  AND e.LastName='Blog'
  AND e.DepartmentId=1

The index we create is called IX_Employee_Dumb and it creates a data structure to help find rows by FirstName, DepartmentId and LastName in that order. 

For some reason, in our employee table there are a lot of people called Siderite, but with different departments and last names. The first query will use the index to find all Siderite employees (fast), then look into each and check if LastName is 'Blog' (slow). The second query will directly find the Siderite Blog employee from department with id 1 (fast), because it uses all columns in the index. As you can see, the order of columns in the index is important, because without the DepartmentId in the WHERE clause, only the first part of the index, for FirstName, can be used. In the last query, because we specify all columns, the entire index can be used to efficiently locate the matching rows. 

Note 2022-09-06: Partitioning a table (advanced concept) takes precedence to indexes. I had a situation where a table was partitioned on column RowDate into 63 partitions. The primary key was RowId, but when you SELECTed on RowId, there were 63 index seeks performed. If queried on RowId AND RowDate, it went to the containing partition and did only one index seek inside it. So careful with partitioning. It only provides a benefit if you query on the columns you use to partition on.

One more way of optimizing queries is using the INCLUDE clause. Imagine that Employee is a table with a lot of columns. On the disk, each record is taking a lot of space. Now, we want to optimize the way we get just FirstName and LastName when searching in a department:

SELECT FirstName,LastName
FROM Employee
WHERE DepartmentId=@departmentId

That @ syntax is used for variables and parameters. As a general rule, any values you send to an SQL query should be parameterized. So don't do in C# var sql = "SELECT * FROM MyTable WHERE Id="+id, instead do var sql="SELECT * FROM MyTable WHERE Id=@id" and add an @id parameter when running the query.

So, in the query above SQL will do the following:

  • use an index for DepartmentId if any (fast)
  • find the EmployeeId
  • read the (large) records of each employee from the table (slow)
  • extract and return the first and last name for each

But add this index and there is no need to even go to the table:

CREATE INDEX IX_Employee_DepWithNames
  ON Employee(DepartmentId)
  INCLUDE(FirstName,LastName)

What this will do is add the values of FirstName and LastName to the data inside the index and, if only selecting values from the include list, return them from the index directly, without having to read records from the initial table.

Note that DepartmentId is used to locate rows (in WHERE and JOIN ON clauses) while FirstName and LastName are the columns one SELECTs.

Indexes are a very complex concept and I invite you to examine it at length. It might even be fun.

When indexes are bad

Before I close, let me tell you where indexes are NOT recommended.

One might think that adding an index for each type of query would be a good thing and in some scenarios it might, but as usual in database work, it depends. What performance you gain for finding records in SELECT, UPDATE and DELETE statements, you lose with INSERT, UPDATE and DELETE data changes.

As I explained before, indexes are basically hidden tables themselves. Slight differences, but the data they contain is similar, organized in columns. Whenever you change or add data, these indexes will have to be updated, too. It's like writing in multiple tables at the same time and it affects not only the execution time, but also the disk space.

In my opinion, the index and table structure of a database depends the most on if you intend to read a lot from it or write a lot to it. And of course, everybody will scowl and say: "I want both! High performance read and write". My recommendation is to separate the two cases as much as possible.

  • You want to insert a lot of data and often? Use large tables with many columns and no indexes, not even primary keys sometimes.
  • You want to update a lot of data and often? Use the same tables to insert the modifications you want to perform.
  • You want to read a lot of data and often? Use small read only tables, well defined, normalized data, clear relationships between tables, a lot of indexes
  • Have a background process to get inserts and updates and translate them into read only records

Writing data and reading data, from the SQL engine perspective, are very very different things. They might as well be different software and indeed some companies use one technology to insert data (like NoSQL databases) and another to read it.

Conclusion

I hope the post hasn't been too long and that it will help you when beginning with SQL. Please leave any feedback that you might have, the purpose of this blog is to help people and every perspective helps.

SQL is a very interesting idea and has changed the way people think of data access. However, it has become so complex that most people are still confused even after years of working with it. Every year new features are being added and new ideas are put forward. Yet there are a few concepts, a foundation if you will, that will get you most of the way there. This is what I have tried to distil here. Hope I succeeded.

  I was attempting to optimize an SQL process that was cleaning records from a big table. There are a multitude of ways of doing this, but the pattern that I had adopted for the last similar tasks were to delete rows in batches using the TOP (@rowCount) syntax. And it had all worked fine until then, but now my "optimization" increased the run time from 6 minutes to 2 hours! Humbled (or more like humiliated) I started to analyze what was going on.

  First thing I did was to SET STATISTICS IO ON. Then I ran the cleaning task again. And lo and behold, there was a row reporting accessing an object that was not part of the query itself. What was going on? At first I thought that I was using a VIEW somewhere, one that I had thought was a table, but no, there was no reference to that object anywhere. But when I looked for that object is was a view!

  The VIEW in question was a view with SCHEMABINDING, to which several indexes were then created. That explained it all. If you ever attempted to create an index on a view you probably got the error "Cannot create index on view, because the view is not schema bound" and then you investigated what that entailed (and probably gave up because of all the restrictions) but in that first moment when you thought "all I have to do is add WITH SCHEMABINDING and I can index my views!" it seemed like a good idea. It might even be a good idea for several scenarios, but what it also does is create a reverse dependency on the object you are using. Moreover, if you look more carefully at the Microsoft documentation it says: "The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution." So you may find yourself querying a table and instead the engine queries a view instead!

  You see, what happens is that every time when you delete 4900 rows from a table that is used by a view that has indexes on it is those indexes are being recreated, so not only your table is affected, but potentially everything that is being called in the view as well. If it's a complicated view that integrates data from multiple sources, it will be run after every batch delete and indexed. Again. And again. And again again. It also prohibits you from some operations, like TRUNCATE TABLE, where you get a funny message saying it's referenced by a view and that is why you can't truncate it. What?!

  Now, I deleted the VIEW and ran the same code. It was faster, but it still took ages because finding the records to delete was a much longer operation than the deletion itself. This post is about this reverse dependency that an indexed view introduces.

  So what is the solution? What if you have the view, you need the view and you also need it indexed? You can disable the indexes before your operation, then enable them again. I believe this will solve most issues, even if it's not a trivial operation. Just remember that in cleaning operations, you need some indexes to find the records to delete as well.

  That's it. I hope it helps. Get out of here!