Snipping in Lubuntu, the sequel

In my last post, I added screen snipping to my Lubuntu system using scrot and mtpaint.   The result is similar to using the Snipping Tool in Windows 7, where the resulting clipped image is left in the Snipping Tool viewer.    There are two drawbacks to the approach:  first, leaving the clipped image in mtpaint is not the quickest thing to do for simple copy and paste applications, and second, not every program will accept the resulting image.

In this post, I'll demonstrate adding a new meta-key combination, Windows-Shift-S to select and copy part of a screen, format the data so any program can use it, and copy the image to the system clipboard.  The end result will be the same meta-key that Windows 10 uses to capture screens, and the result will be ready to paste -- no intermediate step leaving the result in mtpaint.

Capturing the image in a format that was usable by all my programs turned out to be trickier than I thought.   At first, I thought I could send the output of scrot to xclip.   This works for LibreOffice Writer and Gmail, but it didn't work for Blogger.  Hence,it was time to take a deeper dive into this process.  The 'net being the 'net, there's often someone who's run into the same problem.   In this case, artfulrobot (their handle) on askubuntu.com provided a solution that I'm going to work with here.

In this post, I'll discuss using scrot, base64, and xclip to add Snipping Tool functionality to Lubuntu.  We'll start by making sure we have all three programs installed, then we'll test invoking them from the command line, and finally we'll map them to meta-keys in the Openbox window manager.

First, make sure these the tools are installed on your system.   Base64 should be installedd on Lubuntu, but you may need to install scrot and xclip yourself.  Open a terminal window, and try executing xclip and scrot; if they load, everything is good.   If you get a message like:

The program 'xclip' is currently not installed. You can install it by typing: 
sudo apt install xclip 

If you get that message, go ahead and install the tool using apt:

sudo apt install xclip

Now you should have all three tools installed.  There are a lot of moving parts, so let's start with the solution and work backwards.  If you haven't read the previous post, I suggest you browse it now.

First, edit the openbox configuration file using vi or your favorite text editor:

tom@dv8000:~$ cd .config/openbox/ 
tom@dv8000:~/.config/openbox$ ls -l 
total 36 
-rw-r--r-- 1 tom tom 33058 Jan 23 14:06 lubuntu-rc.xml 

tom@dv8000:~/.config/openbox$ cp lubuntu-rc.xml lubuntu-rc.xml.safe
tom@dv8000:~/.config/openbox$ vi lubuntu-rc.xml 

 In our previous post, we added the Windows-s meta-key to copy a screen shot to mtpaint in lines 384 through 389.  The command to do this on line 387 was pretty simple, and we included it right in the configuration file.

The command we need to execute this time is a bit too complicated to include directly in the openbox configuration file, so we'll wrap it in a simple shell script on line 394.  The easiest place to put the shell script is in your home directory until you're satisfied with it.  In my case, that's /home/tom; I'll name the shell script ScrotIt, a meshing of SnipIt and scrot.  Using your editor, add lines 390 through 397, substituting your home directory for /home/tom:

    384     <!--  Windows-s key to select screen and copy to mtpaint -->
    385     <keybind key="W-s">
    386       <action name="Execute">
    387         <command>  scrot -s -e 'mtpaint $f ' /tmp/ScrotSave.png </command>
    388       </action>
    389     </keybind>
    390     <!-- Windows-Shift-s to select screen and copy to xclip -->
    391     <keybind key="W-S-s">
    392       <action name="Execute">
    393         <command>
    394             /home/tom/ScrotIt
    395        </command>
    396      </action>
    397     </keybind>

After you added the new keybinding, save the file and refresh openbox with your changes:

openbox --reconfigure

Next, edit the script ScrotIt using your favorite editor, and add the following lines:

      1 #!/bin/bash
      2 scrot -s /tmp/scrotSave.png && \ 
      3    echo "<img src='data:image/png;base64,"\ 
      4         $(base64 -w0 /tmp/scrotSave.png)\
      5         "' />" | 
      6    xclip -selection clipboard -t text/html 

Let's walk through the code and see what's happening:

• Line 2 runs the scrot command, saving the output in /tmp/scrotSave

• Lines 3 thru 5 build an ASCII string with the image

• Line 3 starts an <img> tag, and the source is a data URI.   The MIME type is image/png, and it will be base64-encoded.

• Line 4 runs the base64 command, encoding the image data as an ASCII character string.  The bash $( ) construction executes base64 and returns the output as a string to the script running the command

• Line 5 closes the <img> tag we started in line 3.  The shell concatenates the string in line 3, the output of base64 in line 4, and the closing tag string in line 5 into one long string.

• Finally, on line 5 the output of the echo command that started on line 3 is piped to xclip on line 6.  xclip takes the output and leaves it in the system clipboard, ready for pasting.

The last step is to make the script executable and test it.  After typing in the ScrotIt command on the second line and pressing enter, ScrotIt will wait until you select an area of your screen.

 

tom@dv8000:~$ chmod +x ScrotIt 
tom@dv8000:~$ ./ScrotIt 

Select an area of your screen and paste it into an application that supports images, like your word processor or mail program (but not a text editor like vi). I copied and pasted the following image into blogger:



If all went well, now try it with the Windows-Shift-S meta-key combination that you defined for openbox.   After selecting an area of your screen with your mouse, you should be able to paste it into your document.

Using these tools, you can select, copy, and paste from your screen into documents as easily with Linux as you can with Windows 10.

---

References:

In addition to the Unix man pages for scrot, base64, and xclip, I found the following web pages to be useful.

https://wiki.archlinux.org/index.php/openbox#Keybinds

https://askubuntu.com/questions/759651/how-to-copy-an-image-to-the-clipboard-from-a-file-using-command-line

https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs

Snipping in Lubuntu

One feature that I find very useful in Windows 10 is the Snipping Tool.   The Snipping Tool is mapped to Win-Shift-s key combination.   After pressing that meta-key combination, the screen grays out, and you can select an area of the screen to copy to the system clipboard.    This is really useful for splatting images quickly into an email (or other document) with a minimum of effort.   Print Screen and Alt-Print Screen were useful for grabbing images of a whole screen or of a whole window.   But many times we only want a small portion of the screen, and the Snipping Tool is a useful way to grab exactly what you need.

In my last post, my old laptop got a second lease on life with a Lubuntu installation and an upgrade to 2GB of memory.  I am spoiled  by the Snipping Tool on my Windows 10 computers, so I'm looking for a way to get that functionality with Lubuntu.

As with many things in life, "da google" is your friend.   Googling "screen capture" or "screen image" returns lots of hits showing screen images from Lubuntu, but not a lot of information describing how to grab that screen image.    Eventually, I found some information about a screen capture utility called scrot (short for SCReen shOT).  In this post, I'll discuss using scrot and mtpaint to add Snipping Tool functionality to Lubuntu.  Mtpaint is installed as part of the Lubuntu distribution, but you may need to install scrot  yourself. Finally we'll map them to meta-keys in the Openbox window manager.

First, make sure scrot and mtpaint are installed on your system.     Open a terminal window, and try executing scrot -v.  If the tool loads and displays a version number, everything is good.   If it doesn't load, you'll see a message like:

The program 'scrot' is currently not installed. You can install it by typing: 
sudo apt install scrot 

If you get that message, go ahead and install the tool using apt:

sudo apt install scrot

Now you should have both tools installed.  I'll start by running them from the command line.  First, try
scrot --select

You'll notice that your terminal window pauses, waiting for you do do something.  With your mouse, click and drag over any section of your screen, creating a rectangle.  Release the mouse button, then in your terminal window type
ls -l *png

and your output will look something like the following.   Scrot creates files with the datetime and images size in the filename:

tom@dv8000:~$ ls -l *png
-rw-rw-r-- 1 tom tom 131103 Jan 13 11:48 2018-01-13-114835_1440x875_scrot.png
-rw-rw-r-- 1 tom tom    510 Jan 13 11:48 2018-01-13-114856_184x172_scrot.png
tom@dv8000:~$

Our next step is to get the the output into mtpaint.  Scrot has a couple of options to facilitate sending the captured image to another program.   The -exec option tells scrot to run another program, and the $f will pass the name of the saved file.   Try the next command; scrot will save the output in /tmp/ScrotSave.png and then execute mtpaint with that filename as the argument.

scrot --select --exec 'mtpaint $f ' /tmp/ScrotSave.png

Now we have all the facilities of mtpaint at our disposal to edit the image.  Using mtpaint, we can save that image back to disk, or just copy it to the system clipboard  using Ctrl-C, then Edit -> Export Clipboard to System from the drop-down menu.

Finally, we'll map this command to a meta-key combination in Lubuntu's openbox window manager.   From your home directory, navigate to the .config directory, then to the openbox directory, and look for the XML configuration file.   In my system, it's lbubuntu-rc.xml:

tom@dv8000:~$ cd .config 
tom@dv8000:~/.config$ cd openbox  
tom@dv8000:~/.config/openbox$ ls -l 
total 36 
-rw-r--r-- 1 tom tom 33422 Jan 13 15:10 lubuntu-rc.xml 
tom@dv8000:~/.config/openbox$ 

There are lots of things that we can configure here, but we're only interested in the keybindings. Before you begin, copy the xml file to a safe place! If you make an error, you don't want to leave the configuration file a mess, and you can restore it using your saved copy.  After creating a backup copy, edit lubuntu-rc.xml with your favorite text editor (leafpad and vi are both installed on Lubuntu, don't use a document editor like Abiword, OpenOffice, or LibreOffice). 

Find the line where the Lubuntu specific keybinding start, and add the XML starting with the <keybind> tag through the closing </keybind> tag:

    382     <!--  Lubuntu specific : Keybindings -->
    383     <!--  Lubuntu specific : Keybindings -->
    384     <!--  Windows-s key to select screen and copy to mtpaint -->
    385     <keybind key="W-s">
    386       <action name="Execute">
    387         <command>  scrot -s -e 'mtpaint $f ' /tmp/ScrotSave.png 
    388      </action>
    389     </keybind>
 

Let's look at what we've done:   Line 384 is a comment describing our change. In line 385, we start a new keybinding, capturing the meta-character combination Windows and the "s" key.  The action will be to Execute the command within the command tags on line 387, which is the scrot command that we executed in the terminal prompt above.

Next, save the file, and refresh the window manager with the new configuration information:

openbox --reconfigure

If all went well, openbox will not display any output.   If there is an error, openbox pops up a message describing the error and its location in the XML file.   After openbox has reloaded the XML file, test your keymap by pressing the Windows and S, then select part of your screen using the mouse.   After you release the mouse key, mtpaint will open with a copy of the image you selected. 

Now you have a quick and easy way to grab a selected part of your screen and manipulate it for further use.  Save the image as a file, or copy it to the system clipboard in mtpaint.

---

References:

In addition to the Unix man pages for scrot and mtpaint,  the following web pages should be useful for readers desiring a deeper look into the keybinding and mtpaint.

https://wiki.archlinux.org/index.php/openbox#Keybinds

http://mtpaint.sourceforge.net/handbook/en_GB/chap_04.html#SEC6

He's dead, Jim

Except for Scotty's warning that "the dilithium crystals are going to blow", Dr. McCoy's pronouncement that "He's dead, Jim" may be the most famous Star Trek quote in popular lexicon.

And so was my nine-year old laptop:  it's dead, Tom. With a hard disk that had just failed,  the laptop was "really and truly dead".  Dual-booting into Windows XP or Ubuntu,  the laptop was a top-of-line beast when it was new, and it still performed well enough for most daily tasks nine years later.

A search on eBay and ten dollars later, my laptop was ready to go... except for an operating system.   Restoring Windows XP was not an option; I'm the second owner, and I don't have the XP install disks.   I was running Ubuntu 14.04 LTS at the time the old hard disk failed, so Ubuntu seemed like a good choice this time.

With a single-core AMD 64-bit processor running just shy of 2GHz and  1GB of memory, I noticed that the laptop was finding the latest browsers a handful to run.   This was especially true when Mozilla dropped support for flash video.   HTML 5 video requires much more resources than flash did.  So, I decided to look for a lightweight Linux distribution that required less computing power than the full-blown Ubuntu distribution I was using.  Anything the operating system wasn't using would be available for applications to use.

Google "lightweight Linux distributions", and you'll get dozens and dozens of results to browse through.   My advice:  don't over-think it.   A lightweight distribution is a lightweight distribution, and the 600 pound gorilla is not the Linux distribution you choose (nor is it Windows nor macOS), it's the applications.  Applications are stuffed features that you'll never use, and they get bigger and badder every release.  Browsers like Firefox and Chrome are often victims of massive amounts of JavaScript and advertisements running video automatically.  So, the distribution is the least of your problems, just pick a distribution and install it.

I choose Lubuntu.   It's reasonably lightweight, it has access to the Ubuntu software libraries, it includes enough applications to get you started, and it's well-supported by the Ubuntu community.   I installed Lubuntu 17.04 LTS, which is supported by security updates for the next several years. 

I used Mozilla's Firefox browser until the most recent update that dropped support for flash.  The latest Firefox browser consumed so much memory that the laptop took a deep dive into the swap space and performance really suffered.  I found Opera to work much better.  As a bonus, Opera includes an ad blocker that tames most ads without crippling a website's functionality.

Lubuntu includes Abiword for word processing and gnumeric for a spreadsheet.  They're both lightweight applications, but if you need compatibility with Microsoft's office suite, I recommend installing either LibreOffice or OpenOffice.  LibreOffice and OpenOffice share a common ancestry; either one is a good choice, and compatibility with Microsoft's file types is very good.  Again, don't over-think it! Lubuntu's software catalog includes LibreOffice, so that's the one I choose.     From the Lubuntu start menu, choose System Tools -> Software.   If your computer is old (like mine), be patient while the software catalog loads.   Then, click the Productivity button and scroll down to find LibreOffice.  Installing LibreOffice loads the base product and the word processor; choose Base, Calc, Draw, or Impress to load the other features.

Finally, I found 1GB of memory to be just barely enough to satisfy the memory requirements of the most recent browser upgrades.  If your system has enough memory that it is not using much swap space, you'll find computing to be much more enjoyable.   If your computer's hard disk is buzzing away while it swaps programs and data in and out of memory, it's time for a memory upgrade.   I found 1GB of memory on eBay for $14 that lets my system hum along nicely while barely touching the swap space.    Memory upgrades can be tricky, I had good luck with a-techcomponents, a vendor I found on eBay.

Happy Computing, and Happy New Year!

SQL Server Listagg and SQL Recursion, Part Three

I've had many occasions to import and export data from one database to another, and I've found SQL recursion to be very useful when I need to string a set of values into one column.   Recently, I had a little hiccup:  a query that I expected to take a few seconds was still running after five minutes.    Clearly, something was wrong.  After convincing myself that the query was correct, I began to examine the data and some of the intermediate query results.

Most of the keys had perhaps five or six values concatenated into a column.  However there were a few keys that had 30 to 40 values to concatenate.    I've reviewed some of the intermediate results before, and I knew the algorithm expanded out about half the possible combinations and then selected the one row with the complete answer.  When there were only three or four values to concatenate to a key, this was not a problem.  But with 30 to 40 values to concatenate to one key, I had a very long running query.

The solution to the performance problem is to reduce the amount of work the query does.   And the key to that is in SQL Server Listagg, Part Two.  In part two, I discussed the problem of concatenating values together when one of the values repeats.  I introduced the use of a surrogate key to control the recursion. In part one, I used the values to control recursion, starting with the smallest value, and repeatedly concatenating larger values to the result.   In part two, I assigned surrogate keys to the values, started the recursion with the smallest surrogate key, and repeatedly concatenated values with larger surrogate keys.   The queries in part one and part two are very similar, and they create a lot of intermediate results that are later discarded.

The new query is much more efficient, and it gains efficiency in two places.  Say we have a set of values that we want to concatenate,  {A, B, G, L}. Now suppose we have a set of surrogates {1, 2, 3, 4} assigned to {A, B, G, L}.   In the first two posts, we use a correlated subquery to find the smallest value to start.  But if we have assigned surrogate values to each of the target values, we do not need the correlated subquery; the starting surrogate value will always be 1.  This makes the query simpler and faster.

In the algorithm in part one, we start with "A", then look for a larger value to concatenate.  That value could be B, G, or L.  Our part one algorithm will iterate thru and create rows with all those combinations: {A},  {A, B}, {A,G}, and {A,L}.   And the iterations continue: {A,B,G}, {A,B,L}, {A,G,L}.  And, finally, {A,B,G,L}.   But we really only want {A,B,G,L}.     Now, if we start with surrogate 1, the next surrogate must be 2, the one after that 3, and  finally 4.    Our intermediate results are {A}, {A,B}, {A,B,G}, and finally {A,B,G,L}.  In part one, we had an algorithm where the work increased exponentially with the number of values to concatenate.  Now, we have an algorithm where the work increases linearly with the number of values to concatenate.   This algorithm is no more complicated that the one we saw in part two, but it's far more efficient.  This is a bigger improvement than eliminating the correlated subquery above.

Here's the code, notice the comments after "from add_surrogate" and "inner join rq":

-- Start with a new common table expression and create a unique 
-- surrogate value for each value in the VALUE column.  The OLAP  
--  row_number() function will do that easily.
with add_surogate (id, value, surogate_value) as (
    select  id, 
            value,       
            row_number() over (partition by id order by value)     
    from sample
    )

-- Add the surrogate_value to the rq CTE
,rq ( id, value, surogate_value,  concat_value, depth) as (

   select a.id,          
          a.value, 
          surrogate_value,
          cast( a.value as varchar(256)), 
          1
   from add_surrogate a
   -- Using surrogate values, we do not need the correlated sub-query
   -- to find the smallest initial value.  The smallest surrogate 
   -- value will always be 1
   where surrogate_value = 1  

   union all 

   select c.id,     
          c.value, 
          surrogate_value, 
          -- No changes, concatenate values as before 
          cast( rq.concat_value + ',' + c.value as varchar(256)), 
          rq.depth + 1
   from add_surrogate c
   inner join rq 
      on c.id = rq.id
         -- Now control the progress of the recursion using the
         -- surrogate value. In part two, the condition was   
         -- the new surrogate > the old surrogate.  That works,
         -- but here's where we can do better:  The next surrogate
         -- will always be the previous surrogate value plus 1.  
        and surrogate_value = surrogate_value + 1
     
)

,all_rows as (
    select id, value, concat_value, depth,
           max(depth) over (partition by id) max_depth
    from rq
)

select id, concat_value
from all_rows
where depth = max_depth -- Select final answers

Moore's Law often works in our favor:  if performance doubles every two years, then we can solve bigger and more difficult problems than we could before. But sometimes the problem we're trying to solve outstrips Moore's Law,  and then we need a better way of solving the problem. In this case, a small tweak in the code delivers a huge performance boost.

Maple Syrup - Liquid Gold

Today, I became a New Englander.

I could not claim that before.  I am, as the Maine expression says, "from away", a transplanted Marylander rooting for the Baltimore Orioles in the spring and the New England Patriots in the fall.  But today, I made my first batch of maple syrup.  Starting with two quarts of fresh maple sap from our friends' sugar maple trees and some advice on sugar content, I boiled two quarts of sap down to about two ounces of maple syrup.  I feel like I belong.

Maple syruping has a long tradition pre-dating European settlers.  It is a pretty simple process that has become more mechanized over the ages, with long lines of tubing replacing traditional taps and buckets and horses and sleds.  Anyone living in New England owes themselves a visit to one of the many sugar houses dotting the New England countryside to watch the pros in action. In this post, I'll describe a simple way of making a few ounces of home-made maple syrup.

Four quart pot to boil two quarts of sap.

I started with two quarts of maple sap in a four-quart pan. Leave plenty of room to prevent the sap from boiling over into the stove.

Calculating reduction

The syrup is usually boiled to a 30:1 to 40:1 reduction.   Our friends and a nearby sugar house both said the sap was very heavy with sugar this year, so I guessed that a 30:1 reduction would work. Professionals use the syrup's boiling point to indicate the syrup is ready.  I started with 60mm of syrup in the four-quart pan.  A 30:1 reduction leaves should leave about 2mm of syrup in the pan.

Maple sap boiling in a smaller container

Boiling down to a depth of 2mm didn't seem practical, so at 10mm in the four-quart pan I transferred the sap to a smaller container.  I had boiled to a 6:1 reduction, now I just needed a 5:1 reduction in the smaller pan.   There was 49mm of syrup in the small pot, so a 5:1 reduction should give me about 10mm of syrup.

Maple syrup jug with tin foil funnel

After boiling down to the 10mm mark, it's time to bottle.  I have a small syrup container complete with a hand-made aluminum foil funnel and bowl to catch any stray syrup. 

Liquid Gold!

Finally, the finished product: a little over two ounces of maple syrup.

 

Today, I am a New Englander.  Tomorrow, I eat pancakes and maple syrup!

SQL Server Listagg, Part Two

Last week's post examined using common table expressions and recursive SQL to concatenate strings grouped by an id value in SQL Server.  This is an easy operation to perform in DB2 and Oracle; both databases support the LISTAGG function.   But SQL Server does not support the LISTAGG function, so we needed to take a different approach to getting the same results.

Our query works well, but sometimes our data may have values that repeat within an id.   Because our query controls the recursion with the values that we're concatenating, we might not get the result back that we wanted.

Let's start with last week's sample table, and add a row to it:

    insert into sample values(3,'G');

Run the last week's final query, and our results look this:

Id	Concat_value
1	A,B
2	C,D
3	E,F,G
3	E,F,G
4	H

This is probably not the result we hoped for.  So, first question:  what did we hope to see?   If the correct result is four rows, with the value of "E,F,G" for Id = 3 , then we just need to add the distinct function to the final query.

But maybe the correct answer is four rows, and the concat_value for Id = 3 is "E,F,G,G".  In that case, we need to do a little work to our query.   As originally written, the query uses the VALUE column to control the recursion.   If the values repeat, then we need to introduce a surrogate value to the query.  Let's examine the query and let the code comments do the talking:

-- Start with a new common table expression(CTE) that creates a unique surrogate
-- value for each value in the VALUE column.  The OLAP row_number() function will 
-- do that easily.
with add_surogate (id, value, surogate_value) as (
    select  id, 
     value,       
            row_number() over (partition by id order by value)     
    from sample
    )

-- Add the surrogate_value to the rq CTE
,rq ( id, value, surogate_value,  concat_value, depth) as (

   select a.id,          
          a.value, 
   a.surogate_value,
          cast( a.value as varchar(256)), 
          1
   from add_surogate a
   where a.surogate_value = (
         select min(surogate_value)   -- Start the recursion using the surrogate value
         from add_surogate b
         where a.id = b.id
         )

   union all 

   select c.id,     
          c.value, 
          c.surogate_value, 
          -- No changes, concatenate values as before 
          cast( rq.concat_value + ',' + c.value as varchar(256)), 
          rq.depth + 1
   from add_surogate c
   inner join rq 
      on c.id = rq.id
         -- And control the progress of the recursion using the
         -- surrogate value
        and c.surogate_value > rq.surogate_value
        and rq.depth < 10
)

,all_rows as (
    select id, value, concat_value, depth,
           max(depth) over (partition by id) max_depth
    from rq
)

select id, concat_value
from all_rows
where depth = max_depth

The changes from last week's query to this week's query are pretty simple:

• Add a new common table expression to create a surrogate value
• Add the surrogate value to the recursive common table expression
• Use the surrogate value to control the recursion
• Continue to build the result string as before

With these simple changes, we get the desired results:

ID	Concat_value
1	A,B
2	C,D
3	E,F,G,G
4	H

More Reading:

The vendors' online documentation have good examples:

• SQL Server 2008 Recursive Queries Using Common Table Expressions
• DB2 Info Center
• Oracle 11gR2 Don't be put off by the term "subquery factoring". It's just recursion.

SQL Server ListAgg Function, Part One

Lately, I've been straddling the Oracle database world and the Microsoft SQL Server world, moving data from Oracle tables to SQL Server tables.  One of the things I needed to do was concatenate string values grouped by an id value.

This is a pretty easy thing to do in the Oracle world.   Let's create a table and add some data:

create table sample (
    id number,
    value varchar2(16)
);
insert into sample values (1, 'A');
insert into sample values (1, 'B');
insert into sample values (2, 'C');
insert into sample values (2, 'D');
insert into sample values (3, 'E');
insert into sample values (3, 'F');
insert into sample values (3, 'G');
insert into sample values (4, 'H');
commit; 

Next, using the LISTAGG function available in DB2 and Oracle, our query will return an id and the concatenation of the values for each id:

select id, 
       listagg(value,',') within group (order by value) concat_value
from sample
group by id

ID	CONCAT_VALUE
1	A,B
2	C,D
3	E,F,G
4	H

That was easy on an Oracle database!  Now, search the SQL Server documentation, and you won't find a LISTAGG function. Next, google "SQL Server listagg", and you'll find lots of discussion. Stackoverflow contributors offer many ways of solving this problem, usually using stored procedures or functions. In this post, I will suggest a SQL solution to the problem using SQL recursion and common table expressions.

First, grab the create table and the insert SQL above.  Change the varchar2 datatype to varchar (or nvarchar) and create the sample table on your SQL Server database.  Next, construct the recursive query.

with rq ( id, value, concat_value, depth) as (

   select a.id, 
          a.value, 
          cast( a.value as varchar(256)), 
          1
   from sample a
   where a.value = (
         select min(value) 
         from sample b
         where a.id = b.id
         )

   union all 

   select c.id, 
          c.value,  
          cast( rq.concat_value + ',' + c.value as varchar(256)), 
          rq.depth + 1
   from sample c
   inner join rq 
      on c.id = rq.id
        and c.value > rq.value
        and rq.depth < 10
)

select rq.* 
from rq
order by id, depth

Before we execute the query, let's examine it:

• the recursion returns 4 columns:  the id, the current value, the result of concatenating the values, and the recursive depth
• we CAST the concatenated value to a varchar large enough to hold the result
• we use a correlated query to return the smallest value for each id.  This gives us a starting point for the recursion. 
• Tracking the depth is useful while developing a query.  The test for rq.depth < 10 will keep the query from spinning away if there's an error.
• The condition c.value > rq.value performs the same function as "order by value" in the Oracle query.   

Execute the query, and we get the following result table:

ID	Value	Concat_value	depth
1	A	A	1
1	B	A,B	2
2	C	C	1
2	D	C,D	2
3	E	E	1
3	F	E,F	2
3	G	E,G	2
3	G	E,F,G	3
4	H	H	1

The answer we want is in the result table above, we just need to filter out the intermediate rows.   The rows we want have have the maximum depth value by ID.  We will use the max function by id to find the maximum depth in a second common table expression, and then filter the results in our final query:

with rq ( id, value, concat_value, depth) as (

   select a.id, 
          a.value, 
          cast( a.value as varchar(256)), 
          1
   from sample a
   where a.value = (
         select min(value) 
         from sample b
         where a.id = b.id
         )

   union all 

   select c.id, 
          c.value,  
          cast( rq.concat_value + ',' + c.value as varchar(256)), 
          rq.depth + 1
   from sample c
   inner join rq 
      on c.id = rq.id
        and c.value > rq.value
        and rq.depth < 10
)

,all_rows as (
    select id, value, concat_value, depth,
  max(depth) over (partition by id) max_depth
    from rq
)

select id, concat_value
from all_rows
where depth = max_depth

When we execute this query, we get the same results as our original query using the LISTAGG function in Oracle:

ID	Concat_value
1	A,B
2	C,D
3	E,F,G
4	H

Using common table expressions and recursion, we now have a way to aggregate strings together in SQL Server.  But this is just the start; using the method for string aggregation as a model, we can develop other aggregations as well.  Next week, we talk about handling cases where values are repeated within an id.

Directory Lookups

Every organization has an employee directory, and building search functions to find someone in the directory is a common problem.  How do you look someone up?  By their last name? By their first name?  And what do we mean by last and first names?  In some countries (China, Hungary), the family name precedes the given name. Or by their full name, including a middle initial?

If an organization is small, it's easy to load all the names into a select list and let the user pick one.  For large organizations, searching the company directory is more challenging.   Is Marguerite in the directory as Marguerite, or Margaret, or maybe even Gretchen, or maybe just Marge?  And how many John Smiths and Jane Does do we have in the directory?

In fact, searching text is a challenging problem.  Consider product searches.   Go to www.ebay.com, enter a product, and you will likely get tens, perhaps hundreds of good matches.  In my experience, the eBay search engine works very well.  Other vendors do not do as well -- a search that turns up hundreds of poor matches is not very useful. 

In general, we want a search strategy that returns lots of good matches and very few poor matches.   In this post, we'll use the company directory as an example of how to solve this problem.

So, suppose someone sits at a web page, types in a name, and presses Enter.  How do we select a list of likely names to present?   Most organizations have tried a few approaches.  Soundex looks promising, but in practice tends to return many false positives.  Many organizations home-grow a solution:  only look at the first few characters of each name, perhaps drop all the vowels and squish everything together, or maybe swap first and last names.

Most home-grown solutions suffer from not returning enough likely matches and returning too many bad matches  --- the worst of all situations.  Worse, many organizations invest much time and effort home-growing the poor-performing solution. There are a lot of solutions out there, but rather than re-invent the wheel, let's examine a solution using Oracle's text indexes and fuzzy matching.  We can get good results by using the out-of-box tools provided in the Oracle database.

Let's start with a simple table and some data.  First we'll create a table to hold the names, split into a first name, last name, nickname, and finally all the names concatenated together. More about the all_names column later.

create table names (
    first_name varchar2(50),
    last_name varchar2(50),
    nick_name varchar2(50),
    all_names varchar2(160)
);

The first_name (John), last_name (Smith), and nick_name (Johnny) are entered as part of normal table maintenance.  The all_names columns is a concatenation of the three names, and we'll use a trigger to maintain the all_names column:

create or replace trigger names_before_iu
before insert or update on names
for each row
begin
   :new.all_names := :new.first_name || ' ' ||
                     :new.last_name || ' ' ||
                     :new.nick_name ;
end;

Next, let's load some test data. We'll use the system catalog to get some "names", with the object's name acting as a first_name and the object's type acting as a last_name. In addition, we'll use the Apex demo_customers table as a source of names:

insert into names (first_name, last_name)
select object_name, object_type
from user_objects;

insert into names (first_name, last_name)
select cust_first_name, cust_last_name
from demo_customers;

Our next step is creating an index on the table. We will index the all_names column using a ctxsys.context type of index:

create index names_ctx
    on names ( all_names)
    indextype is ctxsys.context
    parameters('sync(on commit)');

We will use this context index to do fuzzy matching. Our queries will use the contains function in the where clause, and we need to construct a fuzzy argument to pass to the contains function.  The fuzzy function expands the search to include similarly spelled words.  The fuzzy argument takes three parameters:

• a search string that is at least 3 characters long
• a number between 1 and  80 specifying the lowest similarity score we will accept.  A lower number returns more results, a higher number returns better matches.
• a number specifying how many variations of the search string to use. The more variations we request, the more results we will see.
• a string specifying whether to weight or noweight the results

These numbers will become clear after a few examples. Sometimes things become clear when you poke them with a stick and see what happens, and this is one of those times.  The fuzzy match argument is a bit tricky to type, so let's create a function to make our typing easier.

create or replace
function mk_fuzzy(p_name in varchar2,
                  p_similarity in number default 50,
                  p_n_variations in number default 10)
    return varchar2 is
    
begin

   return 'fuzzy(' || p_name ||  ',' ||
           p_similarity || ',' ||
           p_n_variations || ',W)';

end mk_fuzzy;

Now for our query.

select score(1), a.first_name, a.last_name  
from names a
where contains(all_names, mk_fuzzy('viw',1,3), 1)>= 0
order by 1 desc;

Let's start at the top.  First, the score function takes one argument, in this case the number 1.   The argument refers to the third argument in the contains function, the number 1.  These two numbers should match, and the score function returns the score from the contains function for each row returned by the query.  Below we will use the contains function twice, and the score index ties the score to a specific contains query.

Next, the contains function takes three parameters:

• the column to search on 
• a fuzzy argument.  In this case, we're looking for the word "view" misspelled as "viw".  The similarity score argument of 1 specifies to return any match, and the variation argument specifies to use three variations of the "viw" argument.
• an index for the score function

 Let's run the query and examine our results:

SCORE(1)	FIRST_NAME	LAST_NAME
48	V1	VIEW
48	V2	VIEW
48	V3	VIEW
48	V4	VIEW
48	V_EMP	VIEW
8	DEMO_ORDERS_BIU	TRIGGER
8	DEMO_ORDER_ITEMS_BI	TRIGGER
8	DEMO_ORDER_ITEMS_BIU_GET_PRICE	TRIGGER
8	DEMO_PRODUCT_INFO_BIU	TRIGGER
8	DEMO_CUSTOMERS_BIU	TRIGGER
8	DEMO_TAGS_BIU	TRIGGER
0	WEB_CLIENT2	PROCEDURE
0	WEB_CLIENT	PROCEDURE
0	TESTTABLE_SEQ	SEQUENCE

The scores range from 0 (no match) to 48 (poor match). 48 is not a great score, but the query does find all of our views.

The all_names column includes both first and last names.  After some trial and error against a real-life table of several thousand names, the following query produces a good combination of  relevant results:

select score(1), a.first_name,       
       score(2), a.last_name,       
       score(1) + score(2)
from names a
where contains(all_names, mk_fuzzy('viw',50,50), 1) >  0
  and contains(all_names, mk_fuzzy('emp',50,50),2) > 0 
order by 5 desc;

This query returns the each name, the score for the each name, and the sum of both scores.  Notice the score indexes:  score(1) returns the score from the first use of contains, and score(2) returns the score from the second use of contains.  Ordering the results by the sum of the scores gives us the best matches first, and it's agnostic as to whether the family names follow given names or not. And because our all_names column includes nick names, our query works well with an argument consisting of a nick name and a family name, too.

Against our sample table, the above query returns the following results:

SCORE(1)	FIRST_NAME	SCORE(2)	LAST_NAME	TOTAL_SCORE
52	V_EMP	76	VIEW	128

With both our name arguments misspelled ( "emp" instead of V_EMP, "viw" instead of VIEW), the query still returns the correct answer, and the query doesn't return any bad matches.   This is exactly what we want!

The Oracle fuzzy matching tools deliver high quality search results with a minimum of new development and much less future maintenance.  In addition, tweaking the similarity score and the term expansion in the fuzzy matching function makes the tool very flexible.   If we don't get enough results, or if the results are poor, tweaking the arguments will solve the problem, and it's much easier to tweak the two arguments than re-writing home-grown code.

Replace Or Repair?

Welcome to the new look!  It's fall in New England, the trees are turning many colors, and we have some of the prettiest scenery of the year.   It seems fitting to share this view of Mount Kearsarge from Kezar Lake.  In a few months, the lake will be iced over and the hills will be bare.

Today's topic is a common problem: we're surrounded by appliances (everything from a mobile phone to a refrigerator to an automobile is just an appliance), and sooner or later, the appliance isn't working or isn't working well enough. Do we replace it, or do we repair it?

Case 1: HTC Eris droid phone. This is a nice little android phone, now about 5 years old and still working, using a better set of widget than most newer phones, but only capable of running Android version 2.2. Unfortunately many of my favorite apps no longer work, and app vendors are not releasing updated versions for Android 2.2 phones.   My wife just bought a new Android phone, and I will replace my HTC Eris with her Razr Maxx.

Case 2:  Kodak C875 camera.  The C875 was Kodak's top-of-the-line point and shoot digital camera seven years ago.   It suffered a drop on the driveway when it was two years old, but it continued to work for another five years.  But the camera quit working two days before vacation, and there was no time to get it repaired.  I purchased a replacement camera with better zoom, a faster processor, and image stabilization for about half the cost of the Kodak.

What to do with the old camera?  On one hand, it was a top-of-the-line camera.  On the other hand, cameras have gotten much better and less expensive. So, the camera sat forgotten in a desk drawer for several months, until I figured it was time to repair it or get rid of it.  I quickly learned that the large service centers were no longer repairing this particular model.  But I found one on ebay,  Gerald's Camera Service, that would repair the camera for $39 with a 30-day warrantee.  For $39, I will have a better camera if I get the Kodak fixed than if I buy a new camera.

$39 later, I have my old camera back, working perfectly, just in time to take the panoramic view of Mount Kearsarge.   The new camera doesn't take panoramic shots, a nice feature of the old Kodak.

In fact, there are many things to admire about old appliances.  Our other camera is an old film Pentax K-1000.  The K-1000 is a beautifully simple camera.  There are only four controls:  set the film speed, set the shutter speed, set the f-stop, and focus on the subject.   The controls are easy to learn, and the photographer can set them more quickly than the PASM/C controls on a digital camera.

Simplicity is a big advantage of older appliances.  Last week I had my Austin-Healey out late.  Fans of old British cars know the Lucas moniker "A gentleman does not drive after dark".  But I was out late attending a Linux group meeting, and I found that my headlight switch would not work.  In a modern car, the stranded motorist would call AAA for a tow to the dealership.  In my 50-year old car,  I connected the headlights through the front-panel switch, and I was on the road in 20 minutes.   In a modern car, the rescued motorist would face a thousand-dollar repair bill to replace the combination switch.  In my 50-year old car, I will disassemble the switch and fix it for no cost.  If I can't fix the switch, I will replace it for $40.

Sometimes it's time to say good-bye to the old appliance.  The HTC Eris will sit in desk drawer, like a spare tire, in case I drop or lose the Razr Maxx.  Sometimes, the repair cost is reasonable and the appliance is worth repairing.  I expect to get another 5 years of use from the Kodak camera. And sometimes the simplicity of an older appliance makes them easier to use, and easier and less expensive to repair.  

Update

I spent half an hour removing the headlight switch, disassembling it, lubricating it, re-assembling it, and re-installing it in my car. I expect the switch will function for another 50 years.

National Donut Day

The best programmers are not fueled by intelligence, nor inquisitiveness, nor a desire to learn new skills.   The best programmers are fueled by two things:  coffee and donuts.

Today, June 6, is National Donut Day.  With me today are two things that have fueled my career: a cup of coffee and a hot donut.  The coffee is a cup of Green Mountain's Double Diamond, an intensely-flavored and somewhat bitter roast that's regrettably only available in a K-cup.  The donut is fresh from Lou's in Hanover, NH. Still warm from the oven, it's dripping with that most quintessential of New England toppings, a maple sugar glaze.

Are you drooling yet?

As warm and tasty as this donut is, my best recommendation is to take a trip up Route 1 in Maine, and visit Frosty's on Main Street in Brunswick, Maine.  I believe these donuts to be the best on the planet.  Reviewers on yelp agree; the only complaint is the demand is high, and they run out of donuts early. 

There's lots to see and do on the Maine coast, and a good donut just adds to the pleasure.  Bon appétit!

Windows XP: The Sky Is Not Falling

On April 8, 2014, Microsoft officially brings the Windows XP era to a close.  After that date, Microsoft will no longer offer support and security fixes for its 14 year-old operating system.

Reactions to Microsoft's announcement have been mixed.  Predictably, there are lots of Chicken Little reactions:  Windows XP is not secure, security updates won't be available, Windows XP is a risk to the whole organization, the sky is falling!

Well, the good news is that the sky is not falling.   Many users already bought new computers with Windows 7 or Windows 8.  But, if you want to continue to use Windows XP, there are a few things that you should do.

Let's start with Windows security model.  The problem is not the Windows XP security model, the problem is the way we choose to use Windows XP.    Following on the heels of Windows 95/98/ME, many of us use Windows XP as if it was Windows 95.  We take advantage of the convenient features, and we choose convenience over security.  

Windows 95 and its successors were really just graphical user interfaces sitting on top of a single-user operating system.  Microsoft built Windows XP on top of the Windows NT, so the Windows XP security model shares similarities with secure mainframe and workstation operating systems.   If we use Windows XP more like a workstation and less like Windows 95, then we can use Windows XP securely.  Our computer may not be as convenient to use, but it will be more secure.

Use passwords:  Passwords and log in screens were available in Windows 95, but most users didn't bother using a password:  you turned on the computer, and you were automatically logged on.  Windows XP offered the same convenience of automatic log in.  Don't do it!   Always use a password, and pick a secure password.

Use the NTFS file system: Windows XP inherited both the FAT32 file system from Windows 95 and the NTFS file system from Windows NT.  Early Windows XP computers were usually shipped with the FAT32 file system; conversion to NTFS was an option.  The  FAT32 file system has no security built in; any user can read or write or delete files anywhere on the disk.  The NTFS file system can be secured, and a user must have authorization to read, write, or delete files.   In the FAT32 world, it's easy to corrupt the /Windows directory (or any other directory); in the NTFS world, an unauthorized user can not corrupt the /windows directory.  If you're still running a FAT32 file system, convert it to NTFS.

Use the Limited User and Computer Administrator account types. This is another very important security feature that Windows XP inherited  from Windows NT.  Mainframe and Unix workstation users are familiar with the idea of granting certain users permission to update the operating system and restricting access to other users.  Windows XP has this capability, too.  Windows XP Home users are either Computer Administrators or Limited Users.   An Administrator can install programs, and an administrator has permission to read/write to any directory in an NTFS file system.  A Limited user can not install software, and the NTFS file systems limits the disk access.

This is a secure way to manage a computer,  yet most XP users do not take advantage of it.  Instead, we always log in as an Administrator, and we use our XP system as if it was Windows 95.  Do not do this!   The only good reason to log in as an Administrator is to install software, run the defragmenter, or perform other system administration tasks. For browsing the web, word-processing, working with spreadsheets,  etc, create a Limited user account, and use the Limited user account for daily work.  Never do your day to day work as a Computer Administrator.

Unfortunately, some software vendors insisted on writing applications that saved data to a sub-directory of the \Windows directory.  This was a bad practice, but it was not uncommon.  If you have an application that does this, you may be able to use the application's options to change where it saves files.  If that does not work, you can run the application as an Administrator.  That does defeat the security, but sometimes it's the only way to run an older application.

Disable the guest account.  The Unix workstations we used back in the 1980s and 1990s usually had a "guest" account.  Guest accounts were intended for use by friendly guests who could log in with out a password.  It was a convenience feature that quickly caused us more work than it saved us.  Guest accounts became back doors to our systems for unfriendly hackers.  Windows XP ships with a guest account; it's an unlocked back door to your computer, so be sure this feature is disabled.

Update Windows: As of this writing, there are a few days left to run Windows update.  Microsoft tightened up the XP security in XP releases SP2 and SP3.  Be sure you are running SP3 with the latest updates.  Run Windows update, upgrade to SP3 if you need to, and get the last few updates.  Although Microsoft won't be updating Windows after April 8th, the good news is that after fourteen years, the hackers are unlikely to discover a serious bug.

To the cloud and beyond: Early XP computers were usually shipped with 40GB or perhaps 60GB disks.  After years of use, many of us are running out of disk space.  Or the applications that came with our old computer have become dated.  This is a good opportunity to investigate cloud services. With a Microsoft live.com account or a google account, we have access to many GB of free storage and a suite of applications for word processing, email, spreadsheets,  blogging, etc, all delivered via a web browser.  We get up-to-date applications delivered via the web, we get free disk storage, and the cloud service does all the maintenance.  What's not to like? Let's be honest:  when was the last time you backed up your computer?

Use a modern browser:  That brings us to our next recommendation:  upgrade your browser.   Install the latest FireFox or Chrome or Internet Explorer browser, and let these applications keep themselves updated.  And remember to install the software using your Computer Administrator account.

Security Suite: Another good practise is to run some security software.  In addition to free security suites such as AVG, some Internet service providers offer free subscriptions to popular security suites. Comcast offers Norton anti-virus free to its customers.  Also, check the security software license at your place of business.  With many office workers telecommuting from home, businesses have invested in licenses that allow employees to install the same security suite on their home computers that they use at work.  Another option is Microsoft Security Essentials, available through Windows Update.  Run Windows update manually, click the Custom button (not the Express button), scroll through the list of optional updates, check the Microsoft Security Essentials, and finally click on Review and install updates.

Summary

Windows XP is not an insecure operating system; however, many of us use XP insecurely.  By taking advantage of XP security features, we can continue to safely use our XP computer.  

1. Create passwords for your users.
2. Convert to NTFS.  FAT32 is not secure.
3. Use the Computer Administrator account only for computer administration tasks.
4. Use a Limited user account for everything else. 
5. Disable the guest account.
6. If you share a computer at home, everyone using it should have a unique Limited user account
7. You have a few days left; be sure you have all the available XP updates applied.
8. Upgrade to a modern browser. Chrome and FireFox will update themselves.
9. Add a security suite. 

SQL Server 2014 Install Experience

I recently starting working on a new project using Microsoft SQL Server.  I'm not new to relational databases, but I am new to SQL Server.   So, I'm going to do what any geek would do:  install it myself, poke with it a stick, and see what it does.  Let's get started.

First, google "microsoft download sql server".  I suppose I should use bing, but "bing" is not a verb, at least not yet.  From the google search results, select the SQL Server Express Edition - Microsoft link.

The Microsoft Express Edition page includes a download link for 2012 Express and a link to try SQL Server 2014.  There's a lot of experience on the Internet installing SQL Server 2012 or 2008, so let's try the 2104 version.  Click on Try SQL Server 2014 Express .  (If you're still running Windows XP, you will need to upgrade your Windows operating system or install SQL Server 2008.)

The next page describes five downloads in detail:

• LocalDB (SqlLocalDB)
• Express (SQLEXPR)
• Express with Tools (SQLEXPRWT)
• SQL Server Management Studio Express (SQLManagementStudio)
• Express with Advanced Services (SQLEXPRADV)

We will use the last one -- it includes the database, the tools, and SQL Server Management Studio.

Click the Get Started Now button.

Sign in with you live.com or outlook.com id.  Complete the registration page, select Express with Advanced Services radio button, select 32/64 bit,  and select your language.

The download starts automagically.  If you don't have the Akamai Netsession installer, you will be prompted to install it. Click download to install it; after the installer is done, the download continues.

After the download is complete, find the file and execute it.  In my case, I'm running SQLEXPRADV_x64_ENU.exe.  The file is over 1GB in size, so it will take a moment to load.  Accept the default directory to load files, and get a coffee.  After a few minutes, the SS Installation Center opens.  Select the first option (assuming you don't have an older version), then accept the license agreement.




The setup continues, finally stopping at the Feature Selection screen.  Accept the defaults, which includes everything but the LocalDB.   Click Next. 




At the Instance Configuration, again take the defaults, noting the names: SQLExpress is the Named instance and SQLEXPRESS is the Instance ID.  Notice that the SQL Server directory and Reporting Services directory are both versioned 12, not 14.  These cannot be changed, but what's in a name?  Just click Next to continue.




At the Server Configuration Screen, set up the accounts.   Again, note and take the defaults.  Click next:




The Database Engine has four tabs: Server Configuration, Data Directories, User Instances, and FILESTREAM.   My server is just for learning purposes, so I'll use the default Windows authentication mode. 

The Reporting Services Configuration window has radio buttons Install and configure, or Install only.  Again, take the default Install and configure, and click next.

Now the Installation Progress window displays while the install completes.  This will take a while, grab a coffee and a donut. 


Finally, installation is complete.  On my older computer, installation took about half an hour.  But, the installer does all the work and the installation is easy. The Complete install screen lists some additional resources.  Note that unlike earlier SQL Server 2005 and 2008, the Microsoft SQL Server books are now online:

• Use Microsoft Books Online for SQL Server
• SQL Server Sample Databases
• Release Notes
• Microsoft Update

Now I'm ready to take a dive into SQL Server with even more resources at the SQL Server 2014 forums on MSDN, the training courses at www.lynda.com, or perhaps even a paper book.