Forem: geraldew

Some Example SQL

geraldew — Fri, 18 Jul 2025 16:02:36 +0000

Prologue

I thought it might be interesting to share an example of the sort of code I write in my day job.

Much of the time, what I write is so inherently arcane by being specific to my work environment, that it would be way too much trouble to anonymise it for sharing in public.

However I recently wrote something that felt quite shareable

I have translated the idiom of the code into something that feels like it would have a similar-enough mathematical pattern.

By chance, outside of my work I hade been reading some description of the dynamics of authors and publishers, and that struck me as almost suitable. Frankly, it doesn't quite work, but it will do until I find another paradigm.

Ideally I would like to find some publicly available example data that fits here. Suggestions are welcome.

Base Data

So before we get to the code I wrote, here is our example base data structure. I've imagined a dataset listing per-year connections between authors and publishers, where:

each author has only one publisher for a given year
publishers can have any number of authors each year
publishers might just not be active some years - if that sounds strange, just think of it as meaning that from one year to the next, a publisher can begin to operate, or cease to operate.

So our base table is just a triple of ID numbers for the author and publisher each, and a year. Each triple is unique. More about this in the commentary after the SQL script.

The Base Table

CREATE TABLE
  Dbs.Data_AuthorYearPublisher
(
  Pblshr_Num INTEGER ,
  Acnt_Yr INTEGER ,
  Athr_Id INTEGER
  )
UNIQUE PRIMARY INDEX( 
  Pblshr_Num ,
  Acnt_Yr ,
  Athr_Id
  )
;

The Analytical SQL

Because there's a fair bit of SQL here, I have a choice of two ways to present it.

show each step as an explained piece and then put it all together
show the whole lot and then explain each part

What I will do is the second method, so I can post the whole lot, and then revisit this page to add explanations as I find time to do do.

Of course, in reality I wrote it by starting with the base table, writing a treatment of it, then writing a treatment of the treatment and so on, until I had something that gave me the kind of understanding of the data that I'd vaguely had in mind.

So while I will at this point just dump all the SQL in a code block, I can say that the general structure is:

a common table expression (CTE) named "Rollup_Publisher_Year" pulling from the base table
a CTE "Chunkify" from "Rollup_Publisher_Year"
a CTE "Rollup_Publisher" from Chunkify
a CTE "Patterns" from Rollup_Publisher
a CTE "RollingSums" from Patterns
a CTE "DoProportionCalcs" from RollingSums
a final SELECT from DoProportionCalcs that just gives a different sequence for the columns.

And generally, apart from being translated into the terminology of authors and publishers the identifier names are unchanged from original code.

Finally, to give an idea of timeliness to the writing of this SQL, it came from reaching a "next thing to do" late on a Thursday afternoon. I started tackling it at about 5pm and by 7pm had the SELECT of the "Patterns" step running. Overnight I thought of the idea for the "X0", "X1", "X2" encoding and after a morning diversion, spent the next afternoon pushing through to the code you see here.

-- Analyse Over-the-Year Patterns of Author Counts of Publishers
WITH
  Rollup_Publisher_Year AS
(
SELECT
  Pblshr_Num ,
  Acnt_Yr ,
  EXTRACT( YEAR FROM CURRENT_DATE ) AS Current_Yr ,
  ( Current_Yr - Acnt_Yr ) AS BackByYearsN ,
  COUNT( DISTINCT Athr_Id ) AS Athr_Ctd
FROM
  Dbs.Data_AuthorYearPublisher
GROUP BY
  Pblshr_Num ,
  Acnt_Yr ,
  Current_Yr ,
  BackByYearsN
) ,
  Chunkify AS
(
SELECT
  Pblshr_Num ,
  Acnt_Yr ,
  BackByYearsN ,
  Athr_Ctd ,
  CASE
    WHEN Athr_Ctd = 0 THEN '00'
    WHEN Athr_Ctd < 10 THEN 'X0'
    WHEN Athr_Ctd < 100 THEN 'X1'
    WHEN Athr_Ctd < 1000 THEN 'X2'
    WHEN Athr_Ctd < 10000 THEN 'X3'
    WHEN Athr_Ctd < 100000 THEN 'X4'
    WHEN Athr_Ctd >= 100000 THEN 'X5+'
    END AS AthrCnt_Cd
FROM
  Rollup_Publisher_Year
WHERE
  BackByYearsN > 1
) ,
  Rollup_Publisher AS
(
SELECT
  Pblshr_Num ,
  ( -- derive a backward sequence string, for now, just to illustrate their cliemt pattern
    --MAX( CASE WHEN BackByYearsN = 0 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    --MAX( CASE WHEN BackByYearsN = 1 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 2 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 3 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 4 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 5 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 6 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 7 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 8 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 9 THEN CAST( Athr_Ctd AS VARCHAR( 10) ) ELSE '' END ) || '...'
    ) AS BackYears_Athr_Ctd_Str ,
  ( -- derive a backward sequence string, for now, just to illustrate their cliemt pattern
    --MAX( CASE WHEN BackByYearsN = 0 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    --MAX( CASE WHEN BackByYearsN = 1 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 2 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 3 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 4 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 5 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 6 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 7 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 8 THEN AthrCnt_Cd ELSE '' END ) || '-<-' ||
    MAX( CASE WHEN BackByYearsN = 9 THEN AthrCnt_Cd ELSE '' END ) || '...'
    ) AS BackYears_AthrCntCd_Str ,
  --
  MIN( Acnt_Yr ) AS Min_Acnt_Yr ,
  MAX( Acnt_Yr ) AS Max_Acnt_Yr ,
  --
  MIN( BackByYearsN ) AS Min_BackByYearsN ,
  AVG( BackByYearsN ) AS Avg_BackByYearsN ,
  MAX( BackByYearsN ) AS Max_BackByYearsN ,
  --
  MIN( Athr_Ctd ) AS Min_Athr_Ctd ,
  AVG( Athr_Ctd ) AS Avg_Athr_Ctd ,
  MAX( Athr_Ctd ) AS Max_Athr_Ctd ,
  --
  MAX( CASE WHEN Acnt_Yr = 2024 THEN Athr_Ctd END ) AS Athrs_2024_Ctd ,
  MAX( CASE WHEN Acnt_Yr = 2023 THEN Athr_Ctd END ) AS Athrs_2023_Ctd
FROM
  -- Rollup_Publisher_Year
  Chunkify
GROUP BY
  Pblshr_Num
) ,
  Patterns AS
(
SELECT
  BackYears_AthrCntCd_Str ,
  COUNT( Pblshr_Num ) AS Publisher_Cnt ,
  --
  -- derive a value that can later be checked for being more than zero - the actual amount doesn't itself have a meaning
  -- the idea is that this value can only be zero if both 2014 and 2023 were zero
  MAX( ZEROIFNULL( Athrs_2024_Ctd ) + ZEROIFNULL( Athrs_2023_Ctd ) ) AS MaxAthrsCtd_24plus23 ,
  --
  MAX( Max_Acnt_Yr ) AS Max_Max_Acnt_Yr ,
  MAX( Min_Acnt_Yr ) AS Max_Min_Acnt_Yr ,
  MIN( Min_Acnt_Yr ) AS Min_Min_Acnt_Yr ,
  --
  SUM( Athrs_2024_Ctd ) AS Sum_Athrs_2024_Ctd ,
  SUM( Athrs_2023_Ctd ) AS Sum_Athrs_2023_Ctd
FROM
  Rollup_Publisher
GROUP BY
  BackYears_AthrCntCd_Str
-- ORDER BY
--  Publisher_Cnt DESC
) ,
  RollingSums AS
(
SELECT
  P.* ,
  CASE WHEN MaxAthrsCtd_24plus23 > 0 THEN '24 or 23' END AS RecentlyActive ,
  CASE WHEN Sum_Athrs_2024_Ctd > 0 THEN 'Acted_2024' END AS Active2024 ,
  FIRST_VALUE( Publisher_Cnt) OVER(    
    ORDER BY
      Publisher_Cnt DESC ,
      Sum_Athrs_2024_Ctd DESC ,
      Sum_Athrs_2023_Ctd DESC
    ) AS IgnoreFirstPattern_Cnt ,
  --
  SUM( Publisher_Cnt ) OVER (
    ORDER BY
      Publisher_Cnt DESC ,
      Sum_Athrs_2024_Ctd DESC ,
      Sum_Athrs_2023_Ctd DESC
    ROWS UNBOUNDED PRECEDING
    ) AS SumPblshrsSoFar ,
  SUM( Publisher_Cnt ) OVER () AS SumPblshrsOfAll ,
  --
  SUM( CASE WHEN MaxAthrsCtd_24plus23 > 0 THEN Publisher_Cnt END ) OVER (
    ORDER BY
      Publisher_Cnt DESC ,
      Sum_Athrs_2024_Ctd DESC ,
      Sum_Athrs_2023_Ctd DESC
    ROWS UNBOUNDED PRECEDING
    ) AS SumActvPblshrsSoFar ,
  SUM( CASE WHEN MaxAthrsCtd_24plus23 > 0 THEN Publisher_Cnt END ) OVER () AS SumActvPblshrsOfAll ,
  --
  SUM( CASE WHEN Sum_Athrs_2024_Ctd > 0 THEN Publisher_Cnt END ) OVER (
    ORDER BY
      Publisher_Cnt DESC ,
      Sum_Athrs_2024_Ctd DESC ,
      Sum_Athrs_2023_Ctd DESC
    ROWS UNBOUNDED PRECEDING
    ) AS SumActv24PblshrsSoFar ,
  SUM( CASE WHEN Sum_Athrs_2024_Ctd > 0 THEN Publisher_Cnt END ) OVER () AS SumActv24PblshrsOfAll ,
  --
  SUM( Sum_Athrs_2024_Ctd ) OVER (
    ORDER BY
      Publisher_Cnt DESC ,
      Sum_Athrs_2024_Ctd DESC ,
      Sum_Athrs_2023_Ctd DESC
    ROWS UNBOUNDED PRECEDING
    ) AS SumSoFar_Athrs_2024_Ctd ,
  SUM( Sum_Athrs_2024_Ctd ) OVER () AS SumofAll_Athrs_2024_Ctd
FROM
  Patterns AS P
) ,
  DoProportionCalcs AS
(
SELECT
  R_S.* ,
  ( CAST( SumActvPblshrsSoFar AS FLOAT) / CAST( SumActvPblshrsOfAll AS FLOAT) ) AS SoFar_ActvPblshrs_Ppn ,
  ( CAST( SumActv24PblshrsSoFar AS FLOAT) / CAST( SumActv24PblshrsOfAll AS FLOAT) ) AS SoFar_Actv24Pblshrs_Ppn ,
  ( CAST( SumPblshrsSoFar AS FLOAT) / CAST( SumPblshrsOfAll AS FLOAT) ) AS SoFar_Pblshrs_Ppn ,
  R_S.SumPblshrsSoFar - IgnoreFirstPattern_Cnt AS SoFar_WrkblPblshrs ,
  ( CAST( SoFar_WrkblPblshrs AS FLOAT) / CAST( SumPblshrsOfAll - IgnoreFirstPattern_Cnt AS FLOAT) ) AS SoFar_WrkblPblshrs_Ppn ,
  --
  ( CAST( SumSoFar_Athrs_2024_Ctd AS FLOAT) / CAST( SumofAll_Athrs_2024_Ctd AS FLOAT) ) AS SoFar_Athrs2024_Ppn
FROM
  RollingSums AS R_S
)
SELECT
  -- Grouped By
  BackYears_AthrCntCd_Str ,
  -- Agggregates from here on
  Publisher_Cnt ,
  -- using the idea of Active only about 2024
  Active2024 ,
  SumActv24PblshrsSoFar ,
  SoFar_Actv24Pblshrs_Ppn ,
  SumActv24PblshrsOfAll ,
  -- and their Authrs
  Sum_Athrs_2024_Ctd ,
  SumSoFar_Athrs_2024_Ctd ,
  SoFar_Athrs2024_Ppn ,
  SumofAll_Athrs_2024_Ctd ,
  -- some year spread indicators
  Max_Max_Acnt_Yr ,
  Max_Min_Acnt_Yr ,
  Min_Min_Acnt_Yr ,
  --
  -- All Publishers, no filtering
  SumPblshrsSoFar ,
  SoFar_Pblshrs_Ppn ,
  SumPblshrsOfAll ,
  -- Ignoring the long-time inactive Publishers
  IgnoreFirstPattern_Cnt ,
  SoFar_WrkblPblshrs ,
  SoFar_WrkblPblshrs_Ppn ,
  --
  -- Idea of Active in either 2024 or 2023
  MaxAthrsCtd_24plus23 ,
  Sum_Athrs_2023_Ctd ,
  RecentlyActive ,
  SumActvPblshrsSoFar ,
  SoFar_ActvPblshrs_Ppn ,
  SumActvPblshrsOfAll
FROM
  DoProportionCalcs
ORDER BY 
  Publisher_Cnt DESC ,
  Sum_Athrs_2024_Ctd DESC ,
  Sum_Athrs_2023_Ctd DESC
;

Commentary

Note that I said this "doesn't quite work" - that's because in real life, authors probably use multiple publishers simultaneously. But here we'll imagine that doesn't happen - though we will allow authors to change publisher from one year to the next.

That's important for one important aspect of this analysis:

we cannot ever add the counts of authors across the years

To be continued...

Just some example work notes

geraldew — Sun, 22 Jun 2025 15:36:47 +0000

Note for readers at Dev.to - this posting is not intended for general reading as is. However I may later write an article that refers to it. Not that anyone reads my posts here anyway.

The context for it, is some coding in progress at mexenum which comes from ongoing work on Foldatry and its companion application Diskartulary - but frankly that information while true would be way too tedious to try to describe.

Overview

This is some general musing as I seek to build out a general purpose body of code that will handle vital matters of custom values in my applications as well as the ability to save and reload them as "settings".

See "Stages of implementation" for what has come before this. These notes are being written to clarify ideas during the transition from stage "First abstraction" into "Second abstraction".

Layers

We're going to go over this set three times:

once just to list them
then with broad descriptions
then in more detail

List

Meta Extended Enumerations
Data types of Settings
A Settings Collection
Loadable Settings Collection
Savable Settings Collection

Descriptions

Meta Extended Enumerations = each Enumeration becomes a matrix of values, with a key and attributes - these will be used for various things throughout the program - really, everywhere that the application needs a definitive finite set of optional values.
Data types of Settings = because we will restrict this "Settings ability" to a fairly small number of data types - but it will include the ability to be told how to use other enumerations that are custom to each application
A Settings Collection = because each application will create and use (at least) one of these. This is something that can be used internally by the application. In that context, them being in a collection may or may not be of some use.
Loadable Settings Collection = which will provide an ability to reliably read settings in from a JSON file, which includes handling their custom enumerations
Savable Settings Collection = which will provide an ability to reliably save settings out to a JSON file, with an inherent facility for controlling which among all the settings will be thus output

Details

Meta Extended Enumerations
- named-tuple of attributes with a type enforcement function for it
- class of meta-extended-enumeration
Data types of Settings
- an enumeration to cover five pairs of types
- a named-tuple for extra attributes, including references for functions to handle custom enumerations
A Settings Collection
- a class to hold a set of the settings
Loadable Settings Collection
Savable Settings Collection

As Seen From JSON

which application is it for
which version of the application made it
dictionary of keys and values
- name of key
- string representation of value (might be a single value, or a list of values)

When reading:

confirm it is for the application
confirm we know how to handle settings from that version
loop
- read a key - confirm we recognise it
  - read the value, if this should be an enumeration value, then convert it

Therefore, things we need at read time are:

a function to check the application code
a function to check the version code
a function to look up a key
a function to handle the value for that key

Stages of implementation

Initial stage

Implemented "manually" inside Foldatry.

All the enumerations used around the application are separately declared and used where appropriate.

For each enumeration an addtional extended-enumeration was manually crafted, which included the generation of a list of tuples for passing to a combobox widget

The Settings collection was manually implemented as an enumeration that lists all the settings, plus a custom extended-enumeration for each. There is a run-time dictionary keyed on that enumeration, holding the extended-enumerations.

During loading from JSON file, the keys were manually given direction of which value enumeration to use (to decode from text values). In non-GUi mode, when loading from a file, all keys present in it are applied as settings.

An additional GuiSettings object then added a boolean flag per setting, allowing control of which gets saved and which gets loaded. Thus there is an ability to save a partial set of settings to a file, thereby allowing for separate files for subsets of settings, which could then be progressively loaded via the non-GUI mode.

locations of those in the modules

As for where the code for the above lives, in terms of the Foldatry Python project:

the extended-enumerations were all placed in the module "applitry"
the settings class-and-object and the gui-settings class-and-object were both placed in the main "foldatry" module, which currently handles both the GUI mode and command-line mode operation. It is desired to eventually split these.

First abstraction

The section of code that did extended enumerations was abstracted to a module, which consolidated the repetitious code from across the multiple extended enumerations. This could be given a keycode for each enumeration - actually using its class as the id - and then be fed all the extended attributes.

Second abstraction

Here, the goal is the do a similar abstraction for application settings.

The catch though, is how to:

not have to specify a lot of the enumeration extensions twice
abstract the way that the initial stage settings code, knew about all the custom enumerations used throughout the program

Python variables

Named Tuples

_nmdtpl_XenumAttrbt = fi_namedtuple('nmdtpl_XenumAttrbt', 'XenumJval XenumBrief XenumShow XenumSort')

where those elements are:

XenumJval = JSON value string
XenumBrief = abbreviation
XenumShow = longer display (but not a full description, that's what documentation is for)
XenumSort = used to specify placement in GUI selection

nmdtpl_SettingAttrbt = fi_namedtuple('nmdtpl_SettingAttrbt', 'EnumMmb_DataType EnumTyp_Enum Value_Default Str_JSONkey Str_Show Fn_Val2EnumMmb Fn_EnumMmb2Val')

where those elements are:

EnumMmb_DataType = integer, string, list of string, custom enumeration
EnumTyp_Enum = the class name if a custom enumeration
Value_Default = default value
Str_JSONkey = key used in the JSON
Str_Show =
Fn_Val2EnumMmb = conversion function
Fn_EnumMmb2Val = converse conversion function

Dictionaries

a first dictionary of the extended enumerations, keyed by the enumeration class, then each value being another dictionary keyed on the enumeration members (i.e. the run-time internal Python items) with each holding a _nmdtpl_XenumAttrbt
a second dictionary of the settings, keyed by an enumeration passed in when creating the dictionary. for each key is held a nmdtpl_SettingAttrbt where the use of a value in EnumTyp_Enum must have that already present in the first dictionary
a third object, which gets passed the second dictionary and which adds a third dictionary of boolean flags for each setting

For the providing abstraction, the first and second dictionaries will be inside an object each. While an application only needs one of each of those, it could perhaps do more of them. For example, settings being read from JSON could be done with a new object and then only merged to (or perhaps replacing) the "real" one if the load has no errors. Such things can then be implemented as application features that the service module has no "awareness" about.

Running Linux purely on a USB drive

geraldew — Wed, 11 Jun 2025 13:43:35 +0000

Some time ago, I wrote an article about the method I use to run my computers.

Linux installation to a USB external drive with EFI boot

Why This Article Now

The article I wrote was really just a technical annotation for other "tech" people to read, and wasn't intended as a "how to" suitable for non-technical people.

However it seems timely to make something more general, perhaps to assist people seeking a safe way to try their hand at really using Linux, but with no modifications to their existing setup.

But that still means this is not a set of instructions for the tech-unskilled to follow.

Instead, this is something to share with such people, so they can pass it to friends who maybe do have the skills to help them out.

What My Method Achieves

Which is to say that once setup, my method:

does not require modifying the existing setup of a computer (* see pedantic footnotes);
runs from a physically small SSD USB drive;
can be used on any generally current computer - something with a 64bit AMD/Intel CPU and supporting UEFI
thereby, the little drive effectively IS your active system, the computer becomes just a commodity item
indeed it inherently means you can shutdown and unplug, then plug into another computer and boot on that instead.

I've been running my systems this way for a very long time now. I was certainly operating thus when I moved to Melbourne in early 2010. I was doing so in the days of USB2, and USB3 has merely made it even more viable.

One caveat to make up front: I have assumed that it will be possible for the end user to do run-time selection of how to boot their computer. You could argue that it would be wise to confirm that before building the Linux setup on a USB drive. I can agree, but figure it should usually be possible, but don't think it's worth trying to have an ordinary user determine this. Firmware and/or boot arrangements on computers is frustratingly non-standard and hard to talk about or describe.

Steps High Level

Buy or get USB drives - one "thumb/flash" for the process, one "SSD" as the end goal
Make an installation USB thumb/flash drive
Build Linux onto the USB SSD
Make that Linux fully portable
Make sure the end-user, on the end-computer, has a way to select a UEFI device from which to boot.

Steps Mid Level

Buy or get USB drives (showing minimum sizes)
- a 4GB thumb/flash drive
- a 200GB USB SSD
Make an installation USB thumb/flash drive
- download an installer "image" file
- install/run a USB etcher program
- "burn" the flash drive
- perhaps: test that it can be booted
Make the Linux
- Obtain/use a computer with no internal hard drive (is simpler this way)
- boot the installation USB flash drive
- plug in the target USB SSD drive
- run the installation (as the SSD is the only viable target, this will be simple)
- shutdown
Make the Linux fully portable
- with the machine still having no internal drive, boot the USB SSD
- open a terminal window
- give the command to reinstall GRUB in portable mode: sudo grub-install --removable
- shutdown
Make sure the end-user, on the end-computer, has a way to select a UEFI device from which to boot.
- Optional: explore if their computer can be told to boot the USB SSD if it is plugged, but to otherwise boot its internal drive

Steps Low Level

Thankfully, there are quite a few videos and articles around on how to install Linux by using a flash drive as the installation media.

After uploading this article, I will seek to add a series of hyperlinks from here.

Why Installing Using a Computer With No Internal Drive?

As noted in my other article, there are certainly other ways to do it. I just find this way a lot easier, there is less that can go wrong, and it guarantees that the unplugged drive has not been affected.

But also, writing instructions for the alternatives could/would get very complex and hard to cover thoroughly.

I happen to be lucky, that one of my laptops makes this very easy.

The Secret Sauce

I think it's disappointing that I need to write these articles at all, as there's no good reason the "Make the Linux fully portable" step could not be incorporated into the stock installers of the major distributions.

However, as noted above, making an installation process safely handle all the possible/likely situations is easy for me to carp about when I'm not the one trying to make that.

Sure, in ideal situations, such as forced by my "no internal drive" advice means the secret sauce is merely:
- sudo grub-install --removable
but even that needs to be done at the right point in the process.

Why bother with this? In short, because if you don't, then there is a good chance that the next time your Linux-on-USB-drive does a kernel update, that will mess with the UEFI of the computer it is running on at the time. Technically this is about there being more than one ESP partition and this "secret sauce" avoids modifying the wrong one.

Linux Distro Variations

Frankly, this is just an Ubuntu/Xubuntu user sharing a tip. I expect this will work for any distro that sets up GRUB, but I'm really not in any position to test or advise.

This is also why I don't give any advice on how to do the partitioning - with just one drive present, the distro installer defaults will probably be fine.

Pedantic Footnotes

At the top of this article, I said my method "does not require modifying the existing setup of a computer". But quite how true that is, may depend on details of the existing setup. And we'll assume here that we mean a Windows setup, probably as had been originally supplied on a computer.

It might be that:

the firmware (often called "the BIOS") may be set with something called "secure boot" turned on.
a setting may be suppressing an option during booting that lets a user select a boot option

In general, as long as you can press a key as the computer boots and thereby get an ability to tell it which way to boot, then I consider the problem solved.

So far, I have always found a way around problem firmware settings, but sorting them out does sometimes require mucking about and/or research. Just why this can be hard is a whole other topic.

It is a fair thing to want things to be more automatic - for example, on the computers I mainly use, if I plug in the USB drive before powering on, then it will boot on that. But if I don't plug it in,then it will just boot as it used to.

However, one of the computers here - a Lenovo - seems to refuse to allow such a default, and so I always have to do something manual as I power it up.

There are ways around this, but they are out of the scope of this article, as the idea here is to leave the computer itself **essentially unchanged**.

Another thing I'm considering out of scope is the issue of the booted Linux being able to read (or write?) the files on the Windows setup. There are enough details and issues about that to warrant separate coverage - and personally, is not something I do, so I'm not properly knowledgeable on that score.

Character Encoding with the Python os module and Unicode

geraldew — Sat, 05 Oct 2024 16:23:36 +0000

I realised today that I hadn't published some notes I made about how:

Python, and
its "os" module

handle unspecified character encodings.

This was something I had to tackle when getting my program Foldatry to handle old file systems.

Note that this was only about file and folder names, I've not yet checked how it goes for file contents.

My decisions were:

to let the Python os module get names from the file system and put them into a Python Unicode string - i.e. that I wouldn't write code to tell it how to interpret the encoding of the names until after it had stored them in Python variables;
to write the following function for use in displaying path-file-names (to the terminal or to screen widgets) - this was vital because otherwise the program would simply crash.

def pfn4print( p_pfn ):
    return p_pfn.encode('utf-8', 'surrogateescape').decode('cp1252', 'backslashreplace')

Note that cp1252 is the Windows-1252 superset of the ISO-8859-1 encoding standard.

The rationale for that code is:

the Python os module will by-default have put the bytes it got from the file system into a Python Unicode string;
we then want to turn that back into the original byte sequence, which is achieved by encode('utf-8', 'surrogateescape')
this works because the documentation for the os module says that's what was done to make the Python Unicode string i.e. that it used 'surrogateescape'

Then for display it is using the Windows-1252 codec for two reasons:

it was the most common for a long time: longer than the earlier MS-DOS sets and than other language sets;
because it has representations for nearly all the 256 characters of the 8-bit byte patterns, so it should usually show something even if it's not what was originally intended.

The alternative to this would have been to always handle all the path and filenames as bytestrings, and that seemed too much work.

Now if you know lots about Unicode, you may know that not all bytes sequences are valid in its standard encodings.

So what does Python the os module do when it encounters these (i.e. in byte sequences that it didn't get told how to decode)?

Well, this is where 'surrogateescape' comes in, as this effectively has Python store the bad byte sequence as:

it wasn't valid; and
what its sequence was.

Which is why encode('utf-8', 'surrogateescape') gives us back the original bytes.

Now I do grant that this seems a bit "magic". While you certainly could look deeper into how:

Python handles Unicode internally;
as well as all the different encodings it knows about;
and all the options available as it encodes and decodes;

but my guess is that you can mostly get-by without going that deep, and just trusting "surrogateescape" to do its trick.

Note that this is all for program doesn't crash handling. Ultimately, correct handling of unspecified character encoding is a matter of working out which encoding was/is valid for it. There are tools which will take a good go at doing that, but to be sure of it will require human judgement - essentially because it got there by human misadventure.

p.s. here's function that converts a string to a list of its Unicode ordinals (i.e. the sequence of code points). Handy for checking what Unicode string Python really thinks it has.

def string_as_list_of_code_points( p_str):
    return list( ord(a_char) for a_char in list(p_str) )

Example 1

This comes from a CD-ROM that I'd made back in the Windows 98 era. As a consequence, the filenames definitely were not done as Unicode.

Our example filename here is:

für where that letter in the middle is the "LATIN SMALL LETTER U WITH DIAERESIS"

Encodings

On the CD-ROM the encoding is the sequence of these three bytes:

0x66 0xFC 0x72

Read into Python3 -via the os module - it becomes this sequence of Unicode code points:

U+0066 U+FFFD!!FC U+0072

If we succeed with conversion - see below - then Python holds this sequence of Unicode "code points":

U+0066 U+00FC U+0072

If Python writes that out to UTF-8 it becomes these four bytes:

0x66 0xC3 0xBC 0x72 being: a one-byte character, a two-byte character, and a one-byte character.

My understanding of what Windows-NT uses internally, is "UCS-2" which in bytes, would be:

0x00 0x66 0x00 0xFC 0x00 0x72 being three two-byte characters.

See:

LATIN SMALL LETTER U WITH DIAERESIS
from the Unicode group: Latin-1 Supplement

Python sequence

Okay, let's see how that works in a Python interactive session. The following was clipped over from a terminal window.

$ python3
Python 3.10.12 (main, Sep 11 2024, 15:47:36) [GCC 11.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> s_0 = "für"
>>> print( list( ord(a_char) for a_char in list(s_0) ) )
[102, 252, 114]
>>> ba_1 = s_0.encode( "utf-8", "surrogateescape")
>>> print( list( ba_1 ) )
[102, 195, 188, 114]
>>> ba_2 = s_0.encode("cp1252", "backslashreplace")
>>> print( list( ba_2 ) )
[102, 252, 114]
>>> s_1 = ba_2.decode("utf-8", "surrogateescape")
>>> print( list( ord(a_char) for a_char in list(s_1) ) )
[102, 56572, 114]
>>> ba_3 = s_1.encode( "utf-8", "surrogateescape")
>>> print( list( ba_2 ) )
[102, 252, 114]
>>> s_2 = ba_3.decode("cp1252")
>>> print( list( ord(a_char) for a_char in list(s_2) ) )
[102, 252, 114]
>>> print( s_2)
für

To recap that relative to the earlier commentary:

ba_2 is the way this string would have been on the CD-ROM
s_1 is what happens when we ask Python os to read ba_2 without specifying an encoding - so it presumes UTF-8 but then has to cope with the fact that 252 is not valid as the first byte of a UTF-* character, so os stores it as a surrogate
we can see how it is properly encoded as UTF-8 in ba_1

Depending on your familiarity with code points and encoding, there might be two surprises in that:

that UTF-8 encoding of three characters required four bytes - essentially because the "ü" required two bytes
the matter of "things that wouldn't be valid in UTF-8" then being handled in a strange way, but that can be reversed

Similarly, something that might not catch your attention, is:

that this example is still simple because the archaic Windows-1252 byte encoding for "ü" matches its Unicode code point number: 252

Oh, and:

I don't actually remember where I got the notation U+FFFD!!FC from.
I might have even made it up, as a hybrid way to say "will show as the Unicode Replacement Character but knows it came from hex FC"
If you look at the Python session, you'll see code point in the middle of s_1 is decimal 56572 which is 0xDCFC hexadecimal

Example 2

So now let's look at an example, where the Windows-1252 byte encoding byte for a character does not match its Unicode code point number.

the euro symbol
€30
in Windows-1252 this is encoded as 0x80
in Unicode this is code point U+20AC (decimal 8364)
(in case you're wondering, this difference is because Microsoft decided to use places in the "C1 control codes" range, that Unicode chose to leave alone)

See:

Euro Sign

Python sequence

Here is the same sequence of actions as we did for Example 1 but this time with a short string starting with the Euro symbol.

>>> s_0 = "€30"
>>> print( list( ord(a_char) for a_char in list(s_0) ) )
[8364, 51, 48]
>>> ba_1 = s_0.encode( "utf-8", "surrogateescape")
>>> print( list( ba_1 ) )
[226, 130, 172, 51, 48]
>>> ba_2 = s_0.encode("cp1252", "backslashreplace")
>>> print( list( ba_2 ) )
[128, 51, 48]
>>> s_1 = ba_2.decode("utf-8", "surrogateescape")
>>> print( list( ord(a_char) for a_char in list(s_1) ) )
[56448, 51, 48]
>>> ba_3 = s_1.encode( "utf-8", "surrogateescape")
>>> print( list( ba_2 ) )
[128, 51, 48]
>>> s_2 = ba_3.decode("cp1252")
>>> print( list( ord(a_char) for a_char in list(s_2) ) )
[8364, 51, 48]
>>> print( s_2)
€30

Things to note this time:

ba_1 is the UTF-8 encoding, note that the Euro symbol requires three bytes
ba_2 is the way this string would have been in Windows 98 et al
s_1 is what happens when we ask Python os to read ba_2 without specifying an encoding - so it presumes UTF-8 but then has to cope with what it gets
we still have the matter of "things that wouldn't be valid in UTF-8" being handled in a strange way, but that can be reversed
you'll see this time, the code point at the front of s_1 is decimal 56448 which is 0xDC80 hexadecimal

End note

I should perhaps add that I'm not claiming the methods shown here are either complete or bulletproof. This is merely what I added to my program to cope with what it was encountering. I do plan to revisit all this at some later date but there's a lot of important other features that will come first. So, not "real soon now" at all.

Changing SQL Dialect From Teradata To SQLite

geraldew — Mon, 03 Jun 2024 14:59:38 +0000

Source of This Annotation

I recently had the occasion to convert an interesting SQL script of mine from being in Teradata SQL to running in SQLite. As I did so, I made notes of the various specific changes I had to make. From those, I have plucked out the concepts and some examples and then added annotations.It is not a master guide for the topic, rather is just some sharing of a single experience.

Environment

Some of the issues encountered are probably not about the difference in dialect, but are instead just that my place in the two environments are quite different. For Teradata the environment is mostly not in my control. For SQLite, while it is completely in my control, I've not added any elements that might make it more equivalent - largely because the intention is to easy to replicate.

The most immediate aspect of this is that of namespaces, where for Teradata I must use multiple namespaces as forced by system-wide settings. For SQLite everything happens inside a single namespace. These issues will be explicitly apparent in the text below, but it will not call out which are due to the dialect difference and which to the environment.

Details

Enough preamble - into the details we go. These are not in any specific order, as this has merely been adapted from run-and-hit-error approach to making all the required changes.

Table names

While in some senses obvious - that in changing from one platform to another, that you might not have exactly the same named tables present - another aspect is that the whole structure of name referencing can be different.
From
DatabaseName.TableName
To
TableName
However, that kind of change was going to cause problems for situations where I had actually made use of the different databases as name spaces.

For example, it happens that where I work, I don't have system permissions to create views and macros in the same databases where I can make tables. And while that is a hassle and requires me to instead make any Views and Macros in my own "user" database (because in Teradata, a user account IS a user's own database) that is also handy.

For example, I used my own space as a place to create a view
MyUser.NameOfPurpose
and then use it to make a table of the same name but different database location as in:

CREATE TABLE
    DatabaseName.NameOfPurpose
AS
SELECT
    *
FROM
    MyUser.NameOfPurpose

Which is nice and neat, but then poses a problem if we're translating everything to a single database/namespace.

I'm not saying the following is a great strategy, but it was my quick during the process choice to make use of prefixes in lieu of namespaces and it got me through this time.

So I would change from

MyUser.Something To
VEW_Something and from WorkingDatabase.MyUser_Something (because a polite convention where I work is to put our usernames as a prefix*) To TBL_Something

If I was going to do more of this, then I might come up with something more sophisticated.

(* by the way, the politeness is to not make other analysts look up the data dictionary to get the CREATORNAME and also has a nice effect that our general object names can't interfere with each other)

ANZSIC Resource changes

This section is unique to the specific purpose I had for this conversion, which was that is was using the Australian and New Zealand Standard Industrial Classification (ANZSIC) codes. Follow that link if you want to know more about these. My understanding is that while an independent standard used across Australia and New Zealand, there are similar things in other parts of the world.

And indeed I've been using these kinds of codes in my data work for over 20 years.

As the whole purpose of the SQL script I was converting is to do something interesting with data classified using these codes, necessarily I also had to ensure the handling of these codes was correctly adapted from one environment to the other.

I will leave this section here, it may still be of interest as the kind of changes that occur in this type of exercise.

Things I had to deal with included:

Reduce from 5 digit coding to just 4
Specific table and column names
Change some specific ANZSIC codes

Reduce from 5 digit coding to just 4

As it happens, in my workplace, we enhance some ANZSIC codes by adding an extra digit. In picking up both the ANZSIC definitions and some example data from public "open data" resources, these use only the four digit codes, some various changes had to be made to suit that.

Specific table and column names

Similarly, at my workplace, the reference tables for the ANZSIC codes were made for me by other people (and had the added 5th digit) so in downloading them independently from the ABS (Australian Bureau of Statistics) I don't have the same table structures on tap.

As it happened, this could have given me the chance to do something more appropriate in terms of the structures I would make but didn't want to be rewriting a lot of things while I was mainly just trying to adapt some 2,000 lines of SQL. So I chose a compromise, loading the lookup tables in a way that was convenient to construct from the downloads but then using a custom view to emulate the structure assumed by the script I was converting.

Change some specific ANZSIC codes

As the script that I was converting has a special feature of being able to isolate a single (or small set of) ANZSIC codes for treatment, I didn't have any wish to expose which specific ones my workplace had a special interest in. So I chose another one that seemed to have a similar distribution property (in totally different data, mind).

So that meant changing each place where the script had (the secret digit sequence):

'NNNN' and replacing it with
'8512' Note that those are both strings/chars because while many of the ANZSIC codes are digits, not all are.

Making Views Syntax Change

On Teradata you can make a View using either the keyword CREATE or the keyword REPLACE - with the latter working even when a view of that name already exists. As a consequence there is no reason not to always use REPLACE. But SQLite doesn't have this feature.

FWIW by contrast, Teradata SQL still doesn't have a form of DROP that is safe to use regardless of whether an item does or does not already exist. Yes, there are some work-arounds but that's a whole other topic.

Therefore, a first step is to search/replace throughout the script to find every

REPLACE VIEW and change it to
CREATE VIEW

Actually, that's not quite enough, because in part, one writes scripts so they can be run and re-run. For SQLite this is a simple matter of putting a bulletproof DROP before each CREATE

DROP VIEW NameOfView IF EXISTS ;
CREATE VIEW NameOfView AS

Making Macros Significant Change

While noting that, like with views, for its macro syntax Teradata allows a REPLACE MACRO as well as a CREATE MACRO - but that's not really the problem.

Instead, the situation is that SQLite doesn't appear to have any "macro" feature. Luckily, that's not quite true.

Because what SQLite does have, is triggers. And while a trigger has to be tied to some other database action, it provides a way of defining a statement that will execute.

Of course, a Teradata macro has quite a few other aspects, such as parameter passing, but in this case I didn't need any of that.

So for converting a Teradata Macro into SQL, what we do is create a Trigger for a view that does nothing. To execute the Trigger, we do a DELETE FROM for the view name. In SQLite the delete would fail on the view but still triggers the .. trigger to execute. Ergo, we have a kind of macro.

So, if we had a macro named MacroName, then in Teradata SQL we would do:

REPLACE MACRO MacroName AS
(
-- sql statements
;
)
;

And to get a similar effect in SQLite, we first create a dummy view:

CREATE VIEW 
  View4Trigger_MacroName
AS
SELECT
  CURRENT_TIME 
;

then we define a trigger that will do our bidding

CREATE TRIGGER
  Trigger4_MacroName
INSTEAD OF DELETE ON
  View4Trigger_MacroName
BEGIN
-- sql
END
;

and then we trigger the trigger, with a statement that does:

DELETE FROM 
  View4Trigger_MacroName
;

Whither Stored Procedures

p.s. Who said "Stored Procedure" ? Actually, you won't find "macro" mentioned anywhere - and that's because macros are a Teradata oddity. There's a lot I could say about this, but it's just Teradata history and not particularly relevant here. Teradata did eventually add Stored Procedures to its feature set - quite a long time ago now - but macros are still there and have some permission conveniences.

Remove Temporary Scaffolding Code

This point merely says something about how I write Teradata SQL, which is that:

I can be lazy about writing CREATE statements and so will use a CREATE .. AS construct to make a table and then use SHOW TABLE to get a CREATE statement ready to adapt.
but I also don't leave a script with a CREATE AS in it, and replace it with a CREATE for making an empty table and then do an INSERT to populate it.

As a consequence, I will end up with a script which included a whole bunch of CREATE AS WITH NO DATA and SHOW TABLE then DROP TABLE statement combinations lying around.

In the SQLite environment - and with the script feature complete - these were just extraneous and could be deleted.

Remove Character Set Declarations

One of the consequences of my CREATE AS and SHOW TABLE method is that the table creations I thereby get will have all the specific defaults applied and made explicit. In the Teradata environment that's quite a plus.

But they immediately caused me problems in SQLite and had to go.

So any column definitions that had the following text

CHARACTER SET LATIN NOT CASESPECIFIC had to have them removed.

Remove Set Table Declarations

Along the same line as the above, the Teradata (good) default is to have "set" tables (as opposed to "multiset") but this is not a feature of SQLite and so had to go.
Thus

CREATE SET TABLE becomes simply
CREATE TABLE

Remove More Table Declarations

And to cut a long story short, for my script the following stock clause also had to be removed from CREATE TABLE statements:

    FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1

UPI recoding

Where UPI = UNIQUE PRIMARY INDEX

In Teradata, the equivalent to a "primary key" is a "unique primary index". But as well as the keyword change, the SQLite syntax is different. In Teradata the UPI setting comes as a clause after the list of column definitions has been closed - with a ) character. In SQLite, the primary key setting is done inside the column list.

Actually in SQLite it has two forms of that but we'll only use one here.

From

  LastColumnName DataType )
UNIQUE PRIMARY INDEX ( 
  PrimaryIndexColumn )
;

  LastColumnName DataType , 
  PRIMARY KEY ( 
    PrimaryIndexColumn )
  )
;

SAMPLE and TOP

This is yet another one of those things that simply varies among SQL dialects - and frankly I have no which one, if either, is in the SQL standard.

Teradata has a SAMPLE clause, but as I was only using it for some minor data testing of view constructions, could be easily be replaced.

Ditto for the Teradata syntax of using SELECT TOP x at the beginning of a SELECT statement.

Both of those could be replaced by using the SQLite syntax of add LIMIT x at the end of a SELECT statement.

While not important anywhere, this was annoying to enact as it couldn't be done by simple search/replace actions in the editor.

No System Calendar Pseudo Table

This is another small thing that I've become used to doing on Teradata, because it supplies a built-in pseudo table that generates a full calendar.

While obviously useful for calendar related things, the fact that it has a day_of_calendar column makes it easy to construct scratch tables as if out of thin air.

In the script at hand I was using it as the base for some cross joins to generate a full set of digit combinations. As this was only a matter of ten rows - for the digits "0" to "9" - I chose to replace it with a real table and simply wrote ten insert to literally populate it with the desired digits.

DROP TABLE IF EXISTS
  TBL_1_Digits
;
CREATE TABLE 
  TBL_1_Digits
(
  DigitChar CHAR(1) ,
  PRIMARY KEY ( 
    DigitChar )
  )
;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '0' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '1' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '2' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '3' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '4' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '5' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '6' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '7' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '8' ) ;
INSERT INTO TBL_1_Digits ( DigitChar ) VALUES ( '9' ) ;

In retrospect that was probably a better solution all round - I'm used to using the Teradata sys_calendar.CALENDAR for all kinds of things in many kinds of scales, so my general reasons for doing so remain valid.

Replace FULL OUTER JOIN

As SQLite does not support FULL OUTER JOIN then something will have to be done instead.

As it happens, this is a well covered topic, with some opinions being that FULL OUTER JOIN is something that should be avoided even where it is supported. The stock advice seems to be to replace it with a combination of UNION and GROUP BY structures.

Here is what I had as Teradata SQL

-- Attempt a coalesce of the two lookup methods
SELECT
  COALESCE( AZ_ML.At_Lvl_Cd, DC_AU.DigitsN ) AS At_Lvl_Cd ,
  COALESCE( AZ_ML.Lvl_At , CHARACTER_LENGTH( DC_AU.DigitsN) ) AS Lvl_At ,
  COALESCE( AZ_ML.Up_Lvl_Cd, DC_AU.DigitsM ) AS Up_Lvl_Cd
FROM
  DbName.MyUser_ANZSIC_DigitCharsAndUps AS DC_AU
  FULL OUTER JOIN
  DbName.MyUser_ANZSIC_Multi_Level_Lookup_Base AS AZ_ML ON
    AZ_ML.At_Lvl_Cd = DC_AU.DigitsN
;

And here is the SQLite replacement

-- Attempt a coalesce of the two lookup methods
SELECT
  U.At_Lvl_Cd ,
  MAX( U.Lvl_At ) AS Lvl_At ,
  MAX( U.Up_Lvl_Cd ) AS Up_Lvl_Cd
FROM
  ( -- U
    SELECT
      AZ_ML.At_Lvl_Cd AS At_Lvl_Cd ,
      AZ_ML.Lvl_At AS Lvl_At ,
      AZ_ML.Up_Lvl_Cd AS Up_Lvl_Cd
    FROM
      TBL_ANZSIC_Multi_Level_Lookup_Base AS AZ_ML
    UNION
    SELECT
      DC_AU.DigitsN AS At_Lvl_Cd ,
      LENGTH( DC_AU.DigitsN) AS Lvl_At ,
      DC_AU.DigitsM AS Up_Lvl_Cd
    FROM
      TBL_ANZSIC_DigitCharsAndUps AS DC_AU
    ) AS U
GROUP BY
  U.At_Lvl_Cd
;

To be frank, there is much more that can be said about FULL OUTER JOIN versus UNION .. GROUP BY but this was all I needed for the few places the issue was present in this script.

There is plenty to read elsewhere on this topic.

CHARACTER_LENGTH

While simple, this one caught me by surprise because I thought this function was part of the SQL "standard" - not that that ever means very much in practice. Anyway, the solution was a simple change of function name.

I replaced
CHARACTER_LENGTH
with
LENGTH

QUALIFY

Teradata is one of the dialects that has QUALIFY and uses it to enable filtering in the same query layer as a "window" function is declared. In short, SQLite does not have this.

In my case, this is not new, as I've previously had to re-code from Teradata to HiveQL, which is similar is having window functions but not a "QUALIFY" clause.

What it requires is adding an extra layer of table abstraction - as a derived table or a CTE (common table expression) and then use a WHERE clause to do what the QUALIFY did.

For example, here is how it might look in Teradata SQL

SELECT
    T.*
FROM
    TheTableName AS T
QUALIFY
    ROW_NUMBER() OVER ( 
        PARTITION BY 
            GroupCol 
        ORDER By 
            Sortcol 
        ) = 1
;

And a first step to conversion is to make the window function a named element in the SELECT clause. Note that Teradata is happy to apply the QUALIFY to that named value.

SELECT
    T.* ,
    ROW_NUMBER() OVER ( 
        PARTITION BY 
            GroupCol 
        ORDER By 
            Sortcol 
        ) AS Rw_Num
FROM
    TheTableName AS T
QUALIFY
  Rw_Num = 1
;

Now we can abstract all of the above and remove the QUALIFY and instead do the same filtering as a WHERE clause.

SELECT
    D_T.*
FROM
    ( -- D_T
        SELECT
            T.* ,
            ROW_NUMBER() OVER ( 
                PARTITION BY 
                    GroupCol 
                ORDER By 
                    Sortcol 
                ) AS Rw_Num
        FROM
            TheTableName AS T
        ) AS D_T
WHERE
  D_T.Rw_Num = 1
;

With the QUALIFY gone, the syntax is now ready to work in SQLite.

Do note that I'm not saying this will be internally planned and executed the exact same way - or that it won't, that being a complex per-platform topic.

Change from PRIMARY INDEX

Now, you might be excused for thinking this was already covered in the text above. But no, this is another twist, because it is UNIQUE PRIMARY INDEX which is the PRIMARY KEY equivalent.

Instead, in Teradata, a non-unique "PRIMARY INDEX" merely assists with data spreading at execution. While this technically means that the clause can just be dropped without having any effect, there's a good chance that the reason it was there will imply some thinking about what should be done instead.

In the one case of this for the conversion I was attempting I had used "PRIMARY INDEX" simply because I was too lazy to work out what column combination would be a UPI (i.e. primary key). Having re-assessed that, I made a useful selection and set a new PRIMARY KEY clause.

Change Syntax for Defining a Recursive View

While this was confusing to sort out, from comparative reading of documentation and examples, the change is simple enough - albeit with a couple of twists. Indeed, there were three issues to be dealt with here. Do note: I'm not going to try to explain recursive queries in this context - if you need to gain comfort with those then you will need to seek elsewhere.

The three issues are:

Syntax
Final Select
Naming

Syntax

On the face of it, it mainly looks like a change from the Teradata SQL

CREATE RECURSIVE VIEW
  VEW_ANZSIC_Multi_Level_Recursive_Lookup
(
  At_Lvl_Cd ,
  Lvl_At ,
  Up_Lvl_Cd ,
  Up_Lvl_At ,
  DegreeOfSep )
AS
(

To the SQLite form of

CREATE VIEW 
  VEW_ANZSIC_Multi_Level_Recursive_Lookup 
AS
WITH RECURSIVE 
  Recursive_Lookup
(
  At_Lvl_Cd ,
  Lvl_At ,
  Up_Lvl_Cd ,
  Up_Lvl_At ,
  DegreeOfSep )
AS

But as we will see, there is more to it than this.

Final Select

CREATE RECURSIVE VIEW NameOfView ( ListOfColumns) AS ( 
-- Seed Select Statement
UNION ALL
-- Recursive Select Statement that uses NameOfView
);

Note how this compares to a non-view use of recursion in Teradata SQL, where a recursive with clause is followed by a final SELECT that uses it.

WITH RECURSIVE NameOfWith ( ListOfColumns) AS ( 
-- Seed Select Statement
UNION ALL
-- Recursive Select Statement that uses NameOfWith
)
SELECT
    Something
FROM
  NameOfWith
;

Now look back to the RECURSIVE VIEW syntactic structure and see that it does not have the final SELECT. In effect, that happens when you use the view in a later select. Clearly the Teradata idea of a recursive view is only a way of saving the WITH RECURSIVE clause as a named item, available outside its own definition.

By comparison, the SQLite idea seems to be that you're merely defining a view - hence CREATE VIEW rather than CREATE RECURSIVE VIEW but then allows you to put a recursive WITH inside the definition.

Does this matter? Well it might.

As it happened, the Teradata recursive view that I had written realy required being used with a WHERE clause each time - filtering WHERE Up_Lvl_Cd IS NOT NULL
In adding that final SELECT inside the view to make it work in SQLite, that WHERE clause was "burnt into" the view. But by the nature of that specific clause, having it also used in later uses of the view would be quite harmless.

I suspect that converting in the other direction - from SQLite to Teradata - it would be prudent to add another view just to add the effect of that final select.

Naming

Another quirk of the object naming difference between the two environments, is that the Teradata syntax, has the strange thing by which the view may have had to be declared as being
in a named database but then its non-database name must be used on the inside.

To adapt the syntax example given above, we add DatabaseName. to the create clause, but note that it cannot be used inside the definition.

CREATE RECURSIVE VIEW DatabaseName.NameOfView ( ListOfColumns) AS ( 
-- Seed Select Statement
UNION ALL
-- Recursive Select Statement that uses NameOfView
);

That has led to my name changing scheme from earlier steps to have confused things - as only one of the two names was replaced - which led to some fun until I twigged to what was going on.

As already noted, that problem is not present in the SQLite syntax for which the named recursive element is an alias fully inside the view definition, rather than part of its outside.

Summary

To be honest, the process of conversion proved to be both as difficult as I expected (but with more details) all while clearly always going to be possible (thankfully because my knowledge proved to be sufficient to be confident that all the issues were covered). So it was both annoying and yet satisfying to achieve.

If you've read this far I hope you found the annotations either helpful or interesting as an exercise. I wrote it because I have often been grateful for when other people openly documented their experiences.

Using SQL to discover problem dates in your data

geraldew — Mon, 08 Apr 2024 14:54:38 +0000

The Issue

When you work on a data warehouse, it's enough of an issue that there are probably very many tables to be using in your analysis.

Lots of tables, means there are lots of columns, and most likely, a lot of those column will hold date data types. Anywhere there are dates there are potentials for having absurd values showing up in them.

Where I work, a longstanding policy is to not allow vital date columns to support NULL values. Nevertheless, NULL values are need, often for an end-date field as a way to indicate that the record is still current. As a consequence, there is a practice of using the maximum possible date of 31st December 9999 for the pseudo-NULL value. Inevitably though, it seems that not all processes get written using the correct exact value, and so numerous instances of 1st January 9999 will show up as well as other misunderstandings of what should be used.

While I have a fairly stock method for handling these things when I'm analysing end-date values - as per the NULLIF expression:

SELECT
    NULLIF( Cln_Dt, ( '31/12/9999' (DATE, FORMAT 'dd/mm/yyyy') ) ) AS EnNulled_Cln_Dt

and which can be easily extend to handle a few such values:

SELECT
    NULLIF( 
        NULLIF( 
            NULLIF( 
                Cln_Dt, 
                ( '31/12/9999' (DATE, FORMAT 'dd/mm/yyyy') ) 
                ),
            ( '01/01/9999' (DATE, FORMAT 'dd/mm/yyyy') ) 
            ),
        ( '31/12/9000' (DATE, FORMAT 'dd/mm/yyyy') ) 
        )
        AS EnNulled_Cln_Dt

but ultimately the problem becomes one of knowing quite which values to explicitly write the code to handle.

Besides the specific date values, there is also the issue of knowing how often this kind of thing is occurring.

The Absent Solution

Alas, the most likely thing you've thought as you're reading this is: surely any organisation with this kind of problem would acquire some kind of Data Quality tool and tackle it head on?

Yes, you are probably right to think that, and I'm certainly not going to argue against the idea. However I can honestly say that in over 20 years of doing data analysis work I've never known any actual progress to have been made on issues like this. Or maybe it has, but new processes for messing up data keep springing up.

I could write a whole book on that topic, don't tempt me!

So what I present here is something that can be done by anyone with the ability to write SQL and sufficient access permissions to create and populate a holding table. I don't claim it is super-clever - it is just something I wrote in a half afternoon and confirmed that it works.

What you see is the SQL I wrote - slightly changed to not use names specific to my workplace - and then some annotations added to describe the code.

The Method

As I work on a Teradata, I'll be using its syntax and features, namely:

making a couple of tables
making a number of views
making a number of macros

Also, I will be using the way that Teradata provides a "data dictionary" - i.e. a way to query the system itself to find out what databases, tables and columns are on the system. In Teradata, this is done by a pseudo database called DBC and various views within that. For our purpose here we'll just use one of those, one that lists all the columns, which is unsurprisingly called DBC.ColumnsV

One other thing that I use for this, is that my SQL tool allows me to run an SQL statement - that has been written to itself generate yet more SQL as a set of return values - and then execute those return values. If you don't have that kind of feature in your tooling, that's ok - because you can usually manually clip the generated cover over from being a result to being submitted as more statements to execute.

Corresponding to that, there are some issues about how to execute very large numbers of SQL statements and how to abandon them partway through. Those issues will not be covered in this article - but the method is structured to deal with the after effects of such interruptions.

Similarly, in other system or dialects the implementation may need to use other-named features instead - e.g "stored procedures" and this article won't attempt to cover what those might be.

The SQL

Create Two Tables - Bank and Run

I will want two tables to hold findings:

one for use during a run
and one for storing the latest findings across all runs.

While I work on Teradata quite a lot, there can be slight variations among setups, so I have a stock method to use a "CREATE AS" to generate a prototype table. I then do a SHOW TABLE commend to see what the DDL looks like and then adapt it for the real table.

Here's my prototype table maker:

CREATE TABLE
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
AS
(
SELECT
  DatabaseName ,
  TableName ,
  ColumnName ,
  CAST( NULL AS DATE) AS DateValue ,
  CAST( NULL AS INTEGER ) AS PresenceCount ,
  CAST( NULL AS DATE) AS LastCheckedAtDate
FROM
  DBC.ColumnsV
WHERE
  DatabaseName IS NULL
  AND
  TableName IS NULL
  AND
  ColumnName IS NULL
)
WITH
  NO DATA
;

Next is how wee see the DDL for the prototype table.

SHOW TABLE
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
;

I clip the output of that to a text editor and make some small changes.

Next we drop the bank table - in case you're wondering, Teradata (still) does not provide a "drop if exists" statement in its SQL dialect.

DROP TABLE
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
;

Next we make the real bank table - and is my customisation from the DDL of the prototype table. In this case I just added the UPI (unique primary index) which is the Teradata equivalent of a primary key setting. Another Teradata feature is SET TABLE for which the other option is MULTISET TABLE but I'm not going to explain the distinction here. I think "set tables" are a matter of good practice.

CREATE SET TABLE 
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank ,
    FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO,
    MAP = TD_MAP1
(
  DatabaseName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
  TableName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
  ColumnName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
  DateValue DATE FORMAT 'yyyy-mm-dd',
  PresenceCount INTEGER,
  LastCheckedAtDate DATE FORMAT 'yyyy-mm-dd'
  )
UNIQUE PRIMARY INDEX ( 
  DatabaseName ,
  TableName ,
  ColumnName ,
  DateValue
)
;

Next I have a statement to drop the per-run table, which I would only need to do here if I was re-doing this sequence, because otherwise the table doesn't yet exist.

DROP TABLE
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run
;

Next we make the per-run table - this is the same as for the bank table and Teradata lets us do a simple clone of the structure.

CREATE TABLE
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run
AS
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
WITH
  NO DATA
;

Define Views and Macros

Next we define a View that exists just to give us a constant value of the number of days to wait before refreshing the check for extreme dates on a specific column. Teradata allows for a SELECT with no FROM clause, which makes this quite simple.

If you're wondering why this is a View and not merely a matter of having the value quoted as a literal where it gets used, this method allows us to independently re-write this view to change the behaviour of our little system. While this is a small example, in other situations, this approach of putting definitions in vies can allow for quite sophisticated method variations.

REPLACE VIEW
  myuid.Prefix_DQ_C_DaystoRefresh
AS
(
SELECT
  150 AS DaystoRefresh
)
;

Next we define a View for how to get a list of date columns from the system. Here we are using knowledge about the DBC database on a Teradata system and its view holding metadata for the columns in every view and table.

REPLACE VIEW
  myuid.Prefix_DQ_C_DTC_DateColumns_Potential
AS
(
SELECT
  TRIM( C_V.DatabaseName ) AS Dbn ,
  TRIM( C_V.TableName ) AS Tbn ,
  TRIM( C_V.ColumnName ) AS Cln
FROM
  DBC.ColumnsV AS C_V
WHERE
  /* -- later need to find a way to get the real data types for columns in views
  C_V.Type IN ( )
  AND
  */
  C_V.DatabaseName IN ( 'SpecificDbs' )
  /* AND
  TableName IS NULL */
  AND
  C_V.ColumnName LIKE '%_Dt'
)
;

Note that I've left myself some code in a bracketed comment in case I wanted to filter the columns by actually having a DATE data type. In my case, the specific database I wanted to run analyses on was a space of only Views, and this resource does not hold their data types. Hence I've filtered them by assuming that their names would all end with "_Dt"

Next we define a View for how to get a list of date columns to inspect this time around. While the base of this is the view we just defined for fetching the metadata, my data warehouse is so large that this would give me thousands of columns to analyse. While I do want to analyse them all, on any day when I run this, I won't want to re-analyse ones that I've done somewhat recently.

To that end, I compare to the Bank table and look at the dates a column was last analysed. Similarly, if I had to interrupt during a run then I don't want to re-analyse ones that I've just done, but which are pending my end-of-run step to merge into the Bank table.

Both of those checks are simply LEFT OUTER JOINs and testing for a match through them.

REPLACE VIEW
  myuid.Prefix_DQ_C_DTC_DateColumns_BeyondDaystoRefresh
AS
(
SELECT
  GetDateCols.Dbn ,
  GetDateCols.Tbn ,
  GetDateCols.Cln ,
  ( CURRENT_DATE - Overdue_Holdings.Max_LastCheckedAtDate ) AS DaysSinceLastCheck ,
  CASE 
    WHEN DaysSinceLastCheck IS NULL THEN 'A'
    ELSE 'B' 
    END AS LastCheckCategory
FROM
  myuid.Prefix_DQ_C_DaystoRefresh AS D_T_R
  CROSS JOIN
  myuid.Prefix_DQ_C_DTC_DateColumns_Potential AS GetDateCols
  LEFT OUTER JOIN
  ( -- Overdue_Holdings
    SELECT
      DatabaseName ,
      TableName ,
      ColumnName ,
      MAX( LastCheckedAtDate ) AS Max_LastCheckedAtDate
    FROM
      TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
    GROUP BY
      DatabaseName ,
      TableName ,
      ColumnName
    ) AS Overdue_Holdings ON
    Overdue_Holdings.DatabaseName = GetDateCols.Dbn
    AND
    Overdue_Holdings.TableName = GetDateCols.Tbn
    AND
    Overdue_Holdings.ColumnName = GetDateCols.Cln
  LEFT OUTER JOIN
  ( -- During_Run = this is here to allow for the meta-SQL generation step to be stopped and re-started
    SELECT
      DatabaseName ,
      TableName ,
      ColumnName ,
      MAX( LastCheckedAtDate ) AS Max_LastCheckedAtDate
    FROM
      TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run
    GROUP BY
      DatabaseName ,
      TableName ,
      ColumnName
    ) AS During_Run ON
    During_Run.DatabaseName = GetDateCols.Dbn
    AND
    During_Run.TableName = GetDateCols.Tbn
    AND
    During_Run.ColumnName = GetDateCols.Cln
WHERE
  Overdue_Holdings.Max_LastCheckedAtDate IS NULL
  OR 
  Overdue_Holdings.Max_LastCheckedAtDate + D_T_R.DaystoRefresh > CURRENT_DATE
  AND
  During_Run.Max_LastCheckedAtDate IS NULL
)
;

Next we define a View to provide a constant value for the number of years after the current year to treat as an extreme date. The setting here is quite arbitrary, but you should think carefully about setting it to zero or one - you might be surprised to find out how often your data includes "next year" or "next decade" values to be valid in various places.

REPLACE VIEW
  myuid.Prefix_DQ_MetaSQL_V_YearsAfterCurrent
AS
(
SELECT
  150 AS YearsToSkip_Int ,
  TRIM( CAST( YearsToSkip_Int AS VARCHAR(8) ) ) AS YearsToSkip_Str
)
;

Next we define the View that will be the SQL script builder for analysing the columns. I've been writing this kind of thing for many years. While it's always a bit messy doing this, hopefully you can read through the construction aspects and get a feel for what the SQL it generates will look like.

REPLACE VIEW
  myuid.Prefix_DQ_MetaSQL_V_ExtremeDatesInColumns
AS
(
SELECT
  ( 
    'INSERT INTO ' || 
      'TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run ' || 
      '( DatabaseName, TableName, ColumnName, DateValue, PresenceCount, LastCheckedAtDate ) ' ||
    'SELECT ' ||
      '''' || Dbn || ''' AS Db_N , ' ||
      '''' || Tbn || ''' AS Tb_N , ' ||
      '''' || Cln || ''' AS Cl_N , ' ||
      Cln || ' AS DateValue , ' ||
      'COUNT( ' || Cln || ') AS PresenceCount , ' ||
      'CURRENT_DATE AS LastCheckedAtDate ' ||
    'FROM ' ||
      Dbn || '.' || Tbn || ' ' ||
    'WHERE ' ||
      'EXTRACT( YEAR FROM ' || Cln || ') > EXTRACT( YEAR FROM CURRENT_DATE ) + ' || Y_A_C.YearsToSkip_Str || ' ' ||
    'GROUP BY ' ||
      Cln || ' ' ||
    ';'
    ) AS SqlStr ,
  ( LastCheckCategory || '#' ||
    CAST( ( DaysSinceLastCheck (FORMAT '99999') ) AS CHAR(2) ) || ':' ||
    ( Dbn || '.' || Tbn || '.' || Cln ) 
    ) AS Sorter
FROM
  myuid.Prefix_DQ_MetaSQL_V_YearsAfterCurrent AS Y_A_C
  CROSS JOIN
  myuid.Prefix_DQ_C_DTC_DateColumns_BeyondDaystoRefresh AS GetDateCols
)
;

While the sorter column is both optional and arbitrary, the way I've constructed it here will let us prioritise columns that have not been analysed yet and then those done longest ago.

Do note, that Teradata Views are not allowed to have an ORDER BY clause, so while we can provide the sorting column, actually applying it will have to wait for a Macro.

Next we have (surprise!) a Teradata Macro, that really doesn't do any other than select just the generated SQL strings and in a prudent order.

Also note that the generated SQL will all be INSERTs into the Run table.

REPLACE MACRO
  myuid.Prefix_DQ_MetaSQL_M_ExtremeDatesInColumns
AS
(
SELECT
  SqlStr
FROM
  myuid.Prefix_DQ_MetaSQL_V_ExtremeDatesInColumns
ORDER BY
  Sorter
;
)
;

Being a SELECT, that macro will return rows when it gets run.

Next we define a Macro for deleting all the rows in the Run table.

REPLACE MACRO
  myuid.Prefix_DQ_R_ExtremeDatesInColumns_ClearRun
AS
(
DELETE FROM
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run
;
)
;

Next we define a Macro to perform the merge of the findings that were inserted into the Run table.

To old people like me, this uses the "new" MERGE INTO statement, but really it's been in Teradata for quite a long time now. For any Teradata users not familiar with it, you should note that the internal execution of this was written in a way that is much more efficient than either the older UPDATE or INSERT commands (but you'd need to read Teradata doco to see why that is). The syntax and run-time logic of this command can take some getting used to - but in this situation it fits the bill perfectly, and hopefully is fairly apparent.

REPLACE MACRO
  myuid.Prefix_DQ_R_ExtremeDatesInColumns_MergeRun
AS
(
MERGE INTO
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank AS T_Bnk
USING
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Run AS T_Run ON
    T_Run.DatabaseName = T_Bnk.DatabaseName
    AND
    T_Run.TableName = T_Bnk.TableName
    AND
    T_Run.ColumnName = T_Bnk.ColumnName
    AND
    T_Run.DateValue = T_Bnk.DateValue
WHEN MATCHED THEN UPDATE SET 
  PresenceCount = T_Run.PresenceCount ,
  LastCheckedAtDate = T_Run.LastCheckedAtDate
WHEN NOT MATCHED THEN INSERT 
  (
    DatabaseName ,
    TableName ,
    ColumnName ,
    DateValue ,
    PresenceCount ,
    LastCheckedAtDate )
   VALUES (
    T_Run.DatabaseName ,
    T_Run.TableName ,
    T_Run.ColumnName ,
    T_Run.DateValue ,
    T_Run.PresenceCount ,
    T_Run.LastCheckedAtDate )
;
)
;

Perform a Run

Now, having created our tables, and defined all the views and macros, we can actually do a run.

First Clear the Run table by running the macro for that.

EXECUTE
  myuid.Prefix_DQ_R_ExtremeDatesInColumns_ClearRun
;

Next we run the macro that generates the SQL.

EXECUTE
  myuid.Prefix_DQ_MetaSQL_M_ExtremeDatesInColumns
;

Assuming you don't have a tool that can automate this, you can just copy all that output as text and re-paste it back into where you submit your SQL.
Perhaps try just the first record on its own first.

At this point, the design of our little system doesn't care how many of those rows you transplant over to being submitted. Perhaps keep doing a few until at least one indicates that it actually inserted something into the Run table.

Next, after we've run as many of those as we feel like doing, we run the macro to merge the Run discoveries into the Bank table.

EXECUTE
  myuid.Prefix_DQ_R_ExtremeDatesInColumns_MergeRun
;

With all that done, we inspect the results now in the Bank table.

SELECT
  *
FROM
  TempDbs.Prefix_DQ_ExtremeDatesInColumns_Bank
ORDER BY
  DatabaseName ,
  TableName ,
  ColumnName ,
  PresenceCount DESC ,
  DateValue DESC
;

Addendum

As it happened, I wrote all the above and ran it and it chugged along nicely, taking different amounts of time for various tables and the date columns in them.

However, when I inspected the results I realised it included something that I really wasn't interested in - in that it counted all the correct-dummy date of 31 December 9999.

Because really, my interest was to show various people my findings of unusual values - for discussion about when to treat them as erroneous data and when to just treat them as alternate "NULL" end dates.

The 31 December 9999 dates being in the results aren't themselves a problem, but if I want to avoid having them in there at all, then here is an extra couple of lines to insert to have it not bother collecting those values.

   'WHERE ' ||
      'NULLIF( ' || Cln || ' , ( ''31/12/9999'' (DATE, FORMAT ''dd/mm/yyyy'') ) ) IS NOT NULL ' ||
      'AND ' ||
      'EXTRACT( YEAR FROM ' || Cln || ') > EXTRACT( YEAR FROM CURRENT_DATE ) + ' || Y_A_C.YearsToSkip_Str || ' ' ||

Summary

In a better world, problem dates would find enthusiastic people and tools for correcting errant values and managing data in organised ways. But while you wait for that to spontaneously happen (perhaps don't hold your breath) you can at least use your own skills to get a handle on the scale of the problem, with just plain old SQL.

License approval differences of the FSF and the OSI

geraldew — Sun, 22 Oct 2023 13:13:00 +0000

Are there actually significant distinctions between the Free Software Foundation (FSF) and the Open Source Initiative (OSI) in terms of the licenses they approve?

As I’ve not seen anyone do a satisfactory page about this I felt it was time I tried to tackle the topic. At this point I won't claim it to be "comprehensive" but I hope at least it makes a good enough start.

Much as I don't like when people post here on Dev with just a link to the whole article being somewhere else, I also don't want to maintain two copies while I might still be making minor corrections.

So with that apology here is a link to the full article:

How many and of what nature are the license approval differences of the FSF and the OSI ?

There are certainly other things to cover, presumably in a later posting. At the least, an updated and thorough listing from both organisations and cross-match would seem a next step. Also I suspect there are licenses that either organisation has judged, about which the other has given no public opinion.

Oh, and to not tease, in short my finding was that: there are not significant distinctions between the FSF and OSI approvals.

Some Simple Scaling Progress Indication Log Functions

geraldew — Mon, 07 Aug 2023 14:58:40 +0000

I will prefix this piece by saying that this is just me sharing some ideas and code that I've used in a personal project. I am not claiming this is a unique creation nor that you or anyone else should take up using it.

I recently saw a Mastodon post about a text function library for Python that did a progress bar in text mode displays. That reminded me of the progress logging method I devised and prompted me to consider writing about it.

The Problem

As I was writing my program "Foldatry" I felt a need to be able to see how it was going and where it was up to. For the most part, it works by crawling around folder structures, making records of what it finds and then comparing those findings in various ways.

By its very nature, its job is to explore things not already known. As a consequence, a "progress bar" as normally conceived just isn't possible.

In particular, my program is intended to operate on potentially very large complex directory structures, so I need something that would be workable regardless of the scale of files and folders it would be applied to.

Foldatry is intended to be used either via its GUI or via the command line. In both modes, it shows what it is doing mainly by writing lines of text to logs. These logs can be shown in the GUI and/or written to text log files. Hence for showing progress, I wanted something that would write meaningful progress indicators but never blow out the size of the logs.

The Concept

When I think of handling things of unknown scale, my mind goes to logarithmic methods. In some of my professional data work I've made literal use of "log" functions (and in SQL at that) but for this I wanted something a bit clearer, and frankly, less "mathematical".

Nonetheless, I knew I wanted something that changed what it did with scaled powers of ten. As a simple idea I wanted it to show the 1, 2, 3, 4 e4tc up to 10 and then 20, 30, 40 etc up to 100, then 200, 300 etc and so on. While that may seem quite a few lines of progress log, when applied to multi-multi-thousands it would soon settle to a reasonably finite amount.

The Code

Of course, an idea is of no use unless we have code to implement it.

Here's the initially code function.

I've not bothered to revisit it and make it more Pythonic - frankly it works and was only seen as a thing to quickly put in place during development.

Oh - by the way - by accident I mistyped "progress" as "progess" when naming the first function and left it intact as it amused me and was a more unique search string than "progress" - my apologies for any discomfort that gives some readers.

def is_n_to_show_progess( n ) :
    if n < 11:
        b = True
    elif n < 101 :
        b = (n % 10) == 0
    elif n < 1001 :
        b = (n % 100) == 0
    elif n < 10001 :
        b = (n % 1000) == 0
    elif n < 100001 :
        b = (n % 10000) == 0
    elif n < 1000001 :
        b = (n % 10000) == 0
    elif n < 10000001 :
        b = (n % 100000) == 0
    else :
        b = False
    return b

To use this function, pass it a counter

i = 0
for thing in the_things:
    i += 1
    if is_n_to_show_progess( i ):
        print( "Now at " +  str(i) )

It's not rocket science to work out what this will output, but let's make that explicit anyway.

Now at 1
Now at 2
Now at 3
Now at 4
Now at 5
Now at 6
Now at 7
Now at 8
Now at 9
Now at 10
Now at 20
Now at 30

etc

For one place where I was using this, I found that I was nearly always reaching to a count near or below 20, so I felt I may as well have a variant that I could tell to show me all the count points below some specified point. The implementation was trivial.

def is_n_to_show_progess_showing_all_below_x( n, x ) :
    if n < x :
        b = True
    else :
        b = is_n_to_show_progess( n )
    return b

The above functions all work fine if the circumstances are that every counter value is reached in sequence. But sometimes, we get to increment counters in jumps - for example a sub-call may bring back a variable size amount to add to the running count.

For that case, we need a function that can use the same idea but be given a range of values to consider. Here is how that looks:

def is_n_range_to_show_progess( from_n, upto_n ) :
    def inrange_check( factor, f_n, u_n ) :
        boo = ( (upto_n % factor) == 0 ) or ( (upto_n - from_n) >= factor ) or ( (upto_n % factor) < (from_n % factor) )
        return boo
    if upto_n < 101 :
        b = ( (upto_n % 10) == 0 ) or ( (upto_n - from_n) >= 10 ) or ( (upto_n % 10) < (from_n % 10) )
    elif upto_n < 1001 :
        b = inrange_check( 100, from_n, upto_n )
    elif upto_n < 10001 :
        b = inrange_check( 1000, from_n, upto_n )
    elif upto_n < 100001 :
        b = inrange_check( 10000, from_n, upto_n )
    elif upto_n < 1000001 :
        b = inrange_check( 100000, from_n, upto_n )
    elif upto_n < 10000001 :
        b = inrange_check( 1000000, from_n, upto_n )
    else :
        b = False
    return b

And the way to use it, is to keep a variable that holds what the count was before the possible jump and another for what it has become after the jump in value.

Here is the place where it is used in Foldatry:

                was_file_Count = g_file_count
                new_file_Count = g_file_count + found_n_files_deep_match + found_n_files_deep_mismatch + found_n_files_deep_errors
                if cmntry.is_n_range_to_show_progess( was_file_Count, new_file_Count ) :
                    p_multi_log.do_logs( "dbs", "Files:" + show_at_str )

Later on, in using this method, I was becoming dissatisfied with the progress indications when the counts got up into high numbers. While the output was good for logs, that I would look at long after the whole run was finished, it was not very helpful as I was watching the program running live. I felt that what I wanted there was something that would show me progress at some general time interval rather than just those power-of-ten milestones.

Here's what I came up with. Unlike the earlier versions, this now had a dependency, but I considered it enough of a stock module to depend on.

Here's the code, then I'll describe what it does and how to use it.

import time as im_time

def is_n_to_show_progess_timed_get_mark():
    return im_time.perf_counter()

def is_n_to_show_progess_timed( p_n, min_time_diff, max_time_diff, prev_time_mark) :
    i_t = im_time.perf_counter()
    i_d = i_t - prev_time_mark
    if i_d > max_time_diff :
        r_b = True
    elif i_d > min_time_diff :
        if p_n < 11:
            r_b = True
        elif p_n < 101 :
            r_b = (p_n % 10) == 0
        elif p_n < 1001 :
            r_b = (p_n % 100) == 0
        elif p_n < 10001 :
            r_b = (p_n % 1000) == 0
        elif p_n < 100001 :
            r_b = (p_n % 10000) == 0
        elif p_n < 1000001 :
            r_b = (p_n % 10000) == 0
        elif p_n < 10000001 :
            r_b = (p_n % 100000) == 0
        else :
            r_b = False
    else :
        r_b = False
    if r_b :
        r_new_time_mark = im_time.perf_counter()
    else:
        r_new_time_mark = prev_time_mark
    return r_b, r_new_time_mark

def is_n_to_show_progess_timed_stock( p_n, p_time_mark):
    min_time_diff = 5 # so every is_n will be reported
    max_time_diff = 15 # show something every 15 seconds
    r_b, r_new_time_mark = is_n_to_show_progess_timed( p_n, min_time_diff, max_time_diff, p_time_mark)
    return r_b, r_new_time_mark

So far, in practice, I am using the latter call - is_n_to_show_progess_timed_stock which merely bakes in, a minimum time of 5 seconds and a maximum of 15 seconds - so we'll presume that for describing. Just note that the middle call can always be used for any preferred number of seconds.

Here is how we use it:

First we get a time mark value. We just do this so we can pass it in the next call.
Then, we call our progress function, giving it our counter and the timer mark - we get back a boolean, and a timer value to replace the one we passed

i_time_mark = is_n_to_show_progess_timed_get_mark()
# do things in some kind of loop
    n += 1
    b, i_time_mark = is_n_to_show_progess_timed_stock( n, i_time_mark)
    if b :
        print( "show progress " + str(n) )

Here is an example of the log entries written with this method.

File: 2023-08-06 11:29:09,447 - Matchsubtry traverse_tree_recurse: File count = 2
File: 2023-08-06 11:29:21,900 - Matchsubtry traverse_tree_recurse: File count = 3
File: 2023-08-06 11:29:57,633 - Matchsubtry traverse_tree_recurse: File count = 5
File: 2023-08-06 11:30:30,194 - Matchsubtry traverse_tree_recurse: File count = 8
File: 2023-08-06 11:30:35,686 - Matchsubtry traverse_tree_recurse: File count = 9
File: 2023-08-06 11:31:07,213 - Matchsubtry traverse_tree_recurse: File count = 11
File: 2023-08-06 11:31:48,356 - Matchsubtry traverse_tree_recurse: File count = 14
File: 2023-08-06 11:32:33,264 - Matchsubtry traverse_tree_recurse: File count = 19
File: 2023-08-06 11:32:39,236 - Matchsubtry traverse_tree_recurse: File count = 20
File: 2023-08-06 11:33:07,987 - Matchsubtry traverse_tree_recurse: File count = 22
File: 2023-08-06 11:33:46,181 - Matchsubtry traverse_tree_recurse: File count = 25
File: 2023-08-06 11:34:25,549 - Matchsubtry traverse_tree_recurse: File count = 28
File: 2023-08-06 11:35:00,026 - Matchsubtry traverse_tree_recurse: File count = 30
File: 2023-08-06 11:35:35,388 - Matchsubtry traverse_tree_recurse: File count = 33
File: 2023-08-06 11:35:55,221 - Matchsubtry traverse_tree_recurse: File count = 36

You may notice several things going on there:

not all the values between 1 and 10 were written - that's because some of those happened faster than the minimum specified time
values in between the multiples of 10 are showing up - that's because the maximum specified time had passed
values of the multiples of 10 still showed up - while these wouldn't be guaranteed to, these probably all will for some scaling degree or other.

Summary

These functions are a simple idea that I've found useful. I suspect I may yet think of some other variations on this idea, but will just let ongoing events prompt me to do so.

Coding Diary 2023-03-19 an undesired feature in filecmp.py

geraldew — Sun, 19 Mar 2023 13:26:53 +0000

I'm writing this just an exercise in open sharing. Perhaps it can show that there's value in copying a stock Python module and playing around with it.

The Problem

My program Foldatry is intended to run unattended for long periods of time as it pores over gigabytes and terabytes of files and folders. Here the focus is on a section that seeks to prove that two structures of files are identical - a useful thing to do after a big copy operation.

The function for doing this was meant to do a two part run:

first a "light touch" in which only the metadata was compared;
then if that found all looked the same, then a second "heavy touch" run would compare all the file contents as well.

But was happening was that the amount of time being spent in the first round was taking much longer than expected. Indeed it seemed to be taking as long as I would expect the heavy touch to run.

So what was going on?

As it happened, I've long had good enough logging built into the program to show me where the problem was. It seemed to be inside my use of the dircmp class in the stock Python module filecmp.py

Here is the documentation link for that module: https://docs.python.org/3.11/library/filecmp.html and from it, here is the brief for that class/object:

The dircmp class

class filecmp.dircmp(a, b, ignore=None, hide=None)

Construct a new directory comparison object, to compare the directories a and b. ignore is a list of names to ignore, and defaults to filecmp.DEFAULT_IGNORES. hide is a list of names to hide, and defaults to [os.curdir, os.pardir].

As it happens, I had looked at this module a while back (and that's another story) so I did have some idea of how it works. Enough to know that:

it is written purely in Python - although itself using some other stock Python modules
therefore, I could copy the module code into my own program and modify it as a way to see what was happening.

So that's what I did.

Being a little careful, I fetched a copy from the version of Python that I'm using (on Xubuntu 20.04) - from https://github.com/python/cpython/blob/3.7/Lib/filecmp.py and put it, slightly renamed, into my source code folder. As I'd already been using this module via an alias on import, I only had to change the reference to the now-local renamed copy.

After a quick check that this had taken effect, I started inserting print statements to see which internal functions were being called.

The Issue Identified

Note: to keep this from being tedious I'll skip some of the concepts of this module and jump to the particular.

I soon determined that the flaw lay in a section where the metadata of files was being compared.

    s1 = _sig( os.stat( f1 ) )
    s2 = _sig( os.stat( f2 ) )
    if s1[0] != stat.S_IFREG or s2[0] != stat.S_IFREG:
        return False
    if shallow and s1 == s2:
        return True
    if s1[ 1 ] != s2[ 1 ]:
        return False

In the context of use by the dircmp class, the parameter shallow will be in a state of True and this where things were failing. And by failing, the internal file comparison function was thereby continuing to compare the files byte-by-byte - which is why it was being heavy instead of being light.

Ok, so that begged the question of why that was failing.

Values made `.sig` call

If you look at the code section above, you'll see that the s1 == s2 comparison is on values that had been fetched by calling a function _sig

Here is the code for that function.

def _sig(st):
    return (stat.S_IFMT(st.st_mode),
            st.st_size,
            st.st_mtime)

There's nothing particularly obviously wrong with that, though the detail will be in the feature being used from the stock module stat but happily I didn't need to go inspect that module. Instead I just added some print statements to inspect the values that were going into the return clause. Here they are:

For file A:

32768
11
1673954681.5983226

For file B:

32768
11
1673954681.0

Clearly the difference is in the third value, which was being fed from st.st_mtime and while you can look that up, I can tell you it's just a float number representing the modification timestamp of the files. Being a float, the integer portion is the POSIX time in seconds. The fractional portion of the number is therefore the fractions of a second for the timestamp. As you can see, for one of the files the timestamp was stored with a fractional component and the other without one.

While the question of whether timestamps are a good enough thing to be comparing - in deciding generally whether the file contents should be compared - is clearly a good question, in this context it is being so picky about the fractional parts that's causing my problem.

The Fix

So, in the short term, a simple fix is for me to just add an int function on the times being collected so that only their integer components will be compared.

Thus, the very minor code change is:

def _sig(st):
    return (stat.S_IFMT(st.st_mode),
            st.st_size,
            int( st.st_mtime ) )

Of course, having a local copy of a stock Python module is not a good situation, but there is a wider arc to that story.

Addendum 1

As it happens, I had already realised some time ago that I should write my own replacement for the filecmp.py module - not so much because I think it's bad, but because my needs are both more specific than it is intended to handle as well as needing something more flexible.

Indeed the very issue that prompted this post is an example - that there could be various strategies about matching files - their names and/or their metadata and/or their content -that I'd like handled in more controllable ways.

At the moment, some parts of those ideas are implemented as things to check and/or do after using the dircmp class. While I did start writing the replacement module - it's in the code base but is unused - I don't intend tackling it until I have settled all the different things I want it to cover. These are described in part of the Foldatry documentation so I won't detail them here.

However, for the context of this article, I will say that being able to usefully compare files that have been copied from one file system to another is a definite need that I have - and this is likely to throw up differences of how the timestamps are stored and handled on them.

there are some useful comments about this at Get file timestamps (creation, modification, access date and time) in Python

Addendum 2

On how the stock module "works".

When I needed to look at this a while back it took me some reading and thinking to see why it was written the way it is. As an overview. it tries to use a "phased" approach to doing the comparisons - thus allowing a fairly simple external call to lead to quite varied amounts of operation depending on what is encountered in the folders and files.

When I realised that, I could see the sense of how it was written. It happens that for my needs in Foldatry, I - the application programmer - want to be more in control than that - so that told me I will need to write something more suited.

I do happen to think that the module is written a bit more cryptically than it needs to be - but that's not an uncommon opinion I have of Python that I see written.

Addendum 3

There's an unimportant story of why it took me so long to notice this undesired feature - and do note that I'm not calling this a "bug".

When I started Foldatry, the confirmation of perfect copies was the first part that I wrote - before any GUI and even before the primary reason I wrote anything (which was the matchsubtry feature).

That means that my testing was being done on large amounts of files from very early on. However, it was only quite a while later than I hit on the idea of doing a "light touch" run before embarking on the full file contents comparison. When I tested this, I probably didn't run it on very large file sizes and so saw no significant delays. And when I have run it - for real usage - on very large structures, knowing such a run would take hours, I'd run it overnight - so again, not noticing that the light touch run wasn't actually fast. I would have seen confirmation of the two runs in the logs but not inspected the times closely.

Of course, for the light touch to take a long time was dependent on there being minor differences in the timestamps - and in retrospect I'm guessing that may depend on which copying tools I had used and/or the sequences of copies and moves between ext4 and NTFS file systems (as most of my USB drives use the latter).

Oh well. I got there eventually. And I will at some point write the replacement module.

Musing about Programming - Abstraction vs Specifics

geraldew — Fri, 30 Dec 2022 11:35:41 +0000

This article is merely musing, I can't really advise that it will be worth your while to read it.

Sometimes I think that the major unsolved (untackled?) problem in programming is the matter of separating the abstracted concept of a program from the specifics of how to do it in the given language of the initial implementation.

Example

Take my program Foldatry for example. I wrote it because I had a particular kind of file and folder operation in mind and no program had seemed to tackle it.

But, in order to realise my concept - literally meaning to make it real - I needed to try creating it in some programming language. As I had been long intending to learn Python and because it seemed to provide the features I would need, I chose Python.

To be clear, at the point of setting out to write it, I was quite confidant that what I had in mind could be done. My concept seemed inherently logical and so should be programmable. But, I knew I was going to have to "work out" the actual logic as I progressed through the coding. Hence, while I had a hunch, I did not know how much code it was going to require.

For Foldatry. the core feature I'm referring to here, is the part that became named as "matchsubtry" (match sub tree). I've described the concept in more detail at:

matchsubtry.md

After writing it, thus proving both that the concept was sound and having an actual example implementation, I got on with writing other parts of the general tool that is Foldatry.

Whither Abstraction

But while I began with an abstract concept, now that I've made that concept real, writing out a just-sufficiently-detailed description of it feels like an exercise of documentation rather than of programming. This seems to beg a question of why I wasn't able to create the basis of the program in some abstract form.

This process of abstraction and re-implementation is not a great mystery to programmers, it has been done many times when "porting" a program from one language to another.

Note that I'm considering this kind of re-implementation to only be where the methods used in the program are understood. There are contrasting examples where people have looked at the running behaviour of a program and then independently written something to emulate that behaviour. Of course, sometimes that behaviour implies so obvious an implementation method that the line between these tropes is blurred.

But, as I've noted elsewhere, I didn't (and still don't) think Python is a suitable environment in which to write a reliable and performant program. Thus I consider Foldatry to merely be a working prototype for other people to take its ideas and implement them in a better language (e.g. Rust).

Ah, but is there a catch? That perhaps I have inadvertently buried the port-able concepts in among the idiosyncrasies of Python?

What if there was a way of writing some meta-form of Foldatry that expresses the cross-language concepts of the program? At a first level, such a meta-program could be used by other people to guide them in implementing an adaptation of Foldatry into some other language and/or environment. At a second level, could such a meta-language be usable to generate quite a lot of the other language program?

Now, of course, I'm not naive enough to imagine I'm the first to think along these lines. Indeed I'm sure I sketched thoughts like this back in the 1990s or even the 1980s. I have deliberately chosen to write this article before I go looking online to see what ever has been worked out along the arc.

I am aware that there are many web sites and/or service companies that offer to automate some parts of the language conversion process. I do not have the impression that any of those work so well as to cause programmers in general to see them as obvious paths to take.

Another example - Tk

Part of the prompt to write this piece was a morning's coffee musing about my use of the Tk GUI library (via the Python Tkinter module) in Foldatry and the extent to which that also traps the program into Python. There seems to be a static subset of languages that have well established usage of Tk - and that still leaves out most compiler based languages.

Indeed, the way that all the supported uses of Tk seem to exist is by linking to the dynamic Tcl and Tk libraries as written in C. This helps explain why there is no such thing as Tk support for JavaScript or Go (as in golang).

The reason I bring up Tk in this context is that my impression of it, is precisely that its use sits inside a Python program as a form of minimal concept expression. Tk uses its own abstractions such as Frames and allows the coder to only specify the things that are crucial to how the GUI will operate (well, operation and layout, really).

As I was writing the GUI section of Foldatry I found that there was a nice fit between me trying to find the logic of how various parts of my program's interface should relate, and how Tkinter required me to specify things. As a consequence I'm hoping that were I to re-implement it - in another language and another GUI toolkit, that I would be tasked only with how to reinstate the concepts in the new environment - and not be unpicking parts of how Tkinter works, (But that, of course it merely a guess.)

Abstraction and Re-implementation

While my title for this article is - Abstraction vs Specifics - it is perhaps better given as "Abstraction and Re-implementation" because that is the task that often confronts us in development. While I've given the example of translation from one programming language to another, it might just as well have been about keeping it in the same language and trying for a fresh implementation. Especially as that perhaps happens just as often. Yes, this is where we insert a certain megacorp's numerous claims to have "rewritten from scratch" their products - a claim that has proven to be false for every instance I've ever inspected.

We've been at this programming thing for quite a while, and yet we still don't have any "standard" (meaning: in common use) pseudo-code language. We programmers merely take that as read, but perhaps we should occasionally ask ourselves why that is.

I have seen people inadvertently suggest that Python might itself be such a language. Sadly that suggestion is farcical but I can see the good thought that prompts it.

To avoid getting too catty about Python, let's recognise that Python was intended as a multi-paradigm scripting language, which is enough to validate the suggestion. Perhaps if it had stayed simple then it might have served as one, but the numerous infiltrations from the C-Python implementation into the definition of the language surely stopped Python being a simple and clear language many, many years ago now. Much of what is written in contemporary Python now is essentially cryptic puzzles to 90% of programmers (yes, including those who code only in Python).

I'm not trying to suggest that the needed panacea is any particular pseudo-code definition, rather I'm pondering what the good-enough set of programming concepts would be, so as to support one or several pseudo-code regimes.

This might involve some heavy thinking about which paradigms are vital and which merely convenient. There are plenty of web pages out there stating the important paradigms so I won't try to list them comprehensively here. But I also don't want to play hard to get, so obviously we are talking here about such things as: expressions, iteration, recursion, procedural, object-oriented, functional etc. The wars about which of those are ideal for workaday, practical, commercial, scalable (the adjective list here is probably endless) programming can continue unaffected. I am only interested in what would make for something good enough to act as a broad way to write out the vital concepts of a given program idea.

Or perhaps, we haven't achieved such a thing because it isn't possible. As in fundamentally, inherently impossible.

Or perhaps we haven't achieved it because it is our behaviour to not come together well enough to make one work.

Python Tkinter - An Exercise in Wrapping the ComboBox

geraldew — Thu, 15 Dec 2022 12:57:47 +0000

I recently solved a small problem by wrapping a sub-class around theTkinter ComboBox definition. Having made notes as I was doing so, I figured I may as well tidy and share what I did.

There are plenty of tutorials around about doing this kind of thing - I'm not claiming this to be anything special or better.

The Problem

It so happens that the environment in which I have done most of my application coding over the years is one that provides a "ComboBox" control aka a drop-down list.

But in that environment I am very used to having the ComboBox fed by a small table, usually as rows with key and show columns. The control is bound to the key but displays the show columns - this being achieved by setting the dispay width of the first column to zero. Thus the Combobox has values just for displays but when I read what the user selected, I get the key. Most of the time I use numeric keys and string displays. When supplying these via a table, an order can be specified - that might be independent of the display values.

Having come to writing applications in Python and using Tkinter, it has been frustrating that this only supports the idea of the displayed values being exactly what the control returns as the user's selection.

While annoying, this issue just hadn't been important enough to go out of my way to solve. For some reason, I suddenly felt like tackling - hence this annotated journey.

Note: I am deliberately overlooking that we can fetch the index of the selected item, rather than the displayed value. Most of the time, I don't really care about where things are in the displayed drop-down list - and want to be free to re-order them, or leave an item out. However, as we'll later on, the index might be a useful once we've worked how to wrap one class inside another.

Enter Enumerations

As an old Pascal programmer, I quite like using defined types and enumerations in my programming. The most important reason for liking them - especially as a combination - is that a compile phase will pick up any attempt to stray from assigning across the type definitions.

However, Python as an interpreted language with dynamic typing doesn't really provide that benefit. Nonetheless I still quite like using enumerations as it lets me write symbolic values in the program code and ignore the specific values much of the time.

To quote from WikiBooks: Pascal Programming/Enumerations

type
    weekday = (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday);

var
    startOfWeek: weekday;
begin
startOfWeek := Sunday;
end.

For internal use for a compiled program, the actual (binary) values that will be used for the enumeration values simply don't matter. And being a strongly typed language, the Pascal compiler will give an error for any attempt to assign a non "weekday" value to the variable "startOfWeek".

Of course, Python is quite different, and I won't explain that in detail - see the standard Python documentation for enum — Support for enumerations - but suffice to say I find them useful enough to use them.

However this does pose a problem with comboboxes - because they need to be fed with strings to display and therefore they return those strings back as the selected value.

To get around this, I have been creating my enumerations, and then adding to them by writing functions to express each enumerated value as a display/select string and a converse function to get the enumeration that has a specific string. It all works, but seems clumsy.

What I would like is to have Comboboxes to which I can pass a list of pairs, such that the string halves get displayed to the user but I can get back the enumeration half (key) for that string.

Current Approach

Currently, what I do is to have a Tk variable that is bound to the textvariable property of the Combobox

Hence the setup portion of the code does:

create Tk variable
create Tk Combobox control with binding to the variable
set the values for the Combobox control
set the default value for the Combobox by setting the bound variable

    t06_t2_str_scheme = m_tkntr.StringVar() 
    t06_t2_cb_schemes = m_tkntr_ttk.Combobox( t06_t2_frame02, width = 50, textvariable = t06_t2_str_scheme )
    t06_t2_cb_schemes['values'] = cmntry.MatchScheme_GetTuple()
    t06_t2_str_scheme.set( cmntry.MatchScheme_Default_Value() )

In which I'm pulling from some support functions that I define along with my enumerations - namely:

MatchScheme_GetTuple()
MatchScheme_Default_Value()

Then, to later read the selection, we just get the value of the bound variable

    got_selection = t06_t2_str_scheme.get()

And for which I will then need to call another function to translate from the selected string to the corresponding enumeration.

    enum_selection = MatchScheme_EnumOfString( got_selection)

It's not that doing this is hard, it's just fiddly.

What I Want To Do

I want to be able to

have a list of tuples as what I set for the Combobox
in which the first value in each tuple is the value I want to get back
the second value in each tuple is the value I want displayed for selection

e.g. define the list of tuples

    the_ListTuplePair = [ ( 1, "One" ), ( 2, "Two"), ( 3, "Three"), ( 4, "Four") ]

e.g. create the Combobox object

    t06_t2_cb_schemes = TuplePairCombobox( t06_t2_frame02, width = 50, listTuplePair = the_ListTuplePair, defaultKey = 2 )

e.g. the collection of the selection

    the_selection = t06_t2_cb_schemes.getSelectedKey()

Caveats and Cases

While mulling over some of the potential issues, I considered I would need to deal with:

that no repeats of the keys should get processed and displayed, and hence not selected
that repeats of the display values is allowed and catered for
a default key that matches no provided key means no default value is set
that no selection being made returns as None

Implementation A - using the value bound to the control

The Air Code

Reminding myself a little bit about how class definitions work in Python (I do tend to avoid them) I sat down and typed up the following (with a fair bit of toying around):

class TuplePairCombobox( m_tkntr_ttk.Combobox ):
    def _process_listTuplePair( ip_listTuplePair ):
        # ensure the listTuplePair has no repeats of the keys
        t_list_keys = [] # used to detect repeats
        r_dict_valu2key = {} # use both to populate the combobox and reverse lookup after selection
        for tpl in ip_listTuplePair:
            if tpl[0] in t_list_keys :
                pass
            else
                if tpl[1] in i_dict_valu2key:
                    n = t_list_keys # number of space to pad the display string
                    i_dict_valu2key[ tpl[1] + ( " "*n ) ] = tpl[0]
                else
                    i_dict_valu2key[ tpl[1] ] = tpl[0]
                t_list_keys.append( tpl[0] )
        return r_dict_valu2key
    def __init__(self, container, p_listTuplePair, p_defaultKey, *args, **kwargs):
        self.i_StringVar = m_tkntr.StringVar() 
        i_dict_valu2key = _process_listTuplePair( p_listTuplePair )
        super().__init__(container, textvariable = self.i_StringVar, *args, **kwargs)
        self['values'] = tuple( list( i_dict_valu2key.keys() ) )
        if p_defaultKey in list( i_dict_valu2key.keys() ) :
            self.i_StringVar.set( self.i_defaultKey )
    def getSelectedKey():
        txt_selection = self.i_StringVar.get()
        r_key = i_dict_tpls[ txt_selection ]
        return r_key

That seemed to cover the most of how the idea would work. A short description might be:

process the supplied list of tuples to build a dictionary that will act as both the reverse lookup - to go from the selected display to the tuple keys - and as the list of things to display in the combobox
apply my logical rules about repeats, of keys and of values
to deal with any repeated values, look for clashes and add extra spaces to their ends, with different numbers of spaces as we go along

The Actual Code

Here is what I had after pasting in the air code and fixing some of its oversights.

The Class

class TuplePairCombobox( m_tkntr_ttk.Combobox ):
    def _process_listTuplePair( self, ip_listTuplePair ):
        # ensure the listTuplePair has no repeats of the keys
        t_list_keys = [] # used to detect repeats
        r_dict_valu2key = {} # use both to populate the combobox and reverse lookup after selection
        for tpl in ip_listTuplePair:
            if tpl[0] in t_list_keys :
                pass
            else :
                if tpl[1] in r_dict_valu2key:
                    n = len( t_list_keys ) # number of space to pad the display string
                    r_dict_valu2key[ tpl[1] + ( " "*n ) ] = tpl[0]
                else :
                    r_dict_valu2key[ tpl[1] ] = tpl[0]
                t_list_keys.append( tpl[0] )
        return r_dict_valu2key
    def __init__(self, container, p_listTuplePair, p_defaultKey, *args, **kwargs):
        self.i_StringVar = m_tkntr.StringVar() 
        self.i_dict_valu2key = self._process_listTuplePair( p_listTuplePair )
        super().__init__(container, textvariable = self.i_StringVar, *args, **kwargs)
        self['values'] = tuple( list( self.i_dict_valu2key.keys() ) )
        if p_defaultKey in list( self.i_dict_valu2key.keys() ) :
            self.i_StringVar.set( self.i_defaultKey )
    def getSelectedKey( self ):
        txt_selection = self.i_StringVar.get()
        if txt_selection in self.i_dict_valu2key:
            r_key = self.i_dict_valu2key[ txt_selection ]
        else : 
            r_key = None
        return r_key

The Object

Here is how the new feature gets used.

First the creation of the ComboBox object, with the usual Tkinter pair of: create and place steps.

    i_defaultKey = 3
    t02_cb_schemer = TuplePairCombobox( t02_frame50, i_listTuplePair, i_defaultKey, width = 50) 
    t02_cb_schemer.pack( side = m_tkntr.LEFT, padx=tk_padx(), pady=tk_pady() )

The Value Fetch

Here is how the value is collected from the ComboBox.

            print( t02_cb_schemer.getSelectedKey() )

The List of Tuples

Here is a mock example of a list of tuples for supplying to the new TuplePairCombobox object.

    #i_listTuplePair = [ ( 1, "One" ), ( 2, "Two"), ( 3, "Three"), ( 4, "Four") ]

The List of Tuples with a Repeated Value

Just to check that my extra edge case code is working, here is an alternate list of tuples, in which there are two identical display strings.

    i_listTuplePair = [ ( 1, "One" ), ( 2, "Two"), ( 3, "Three"), ( 4, "Two") ]

In that case, all four options are in the ComboBox and can each return a distinct key - i.e. the fact that two display strings are there is worked around.

Caveat:

the mechanism for this is very simple, and would fail for some combinations in the source tuples. I will have to decide whether to write something more robust.

The List of Tuples with a Repeated Key

And similarly here is an alternate list of tuples, in which there are two identical key values.

    i_listTuplePair = [ ( 1, "One" ), ( 2, "Two"), ( 2, "Three"), ( 4, "Four") ]

In this case, as per the logic I wrote in, the second appearance of the key 2 will be omitted.

Method B - using the index of the selection

In Method A we just worked around the issue of a Combobox "returning" one of the display values.

But with the standard Tkinter Combobox we do also have the option of pulling out the index of the selected value. Can that be used to make a better method?

Air code

For this, I copied the finished Method A code and changed the definition of getSelectedKey to use current() and then thought my way backwards about what data structure it would require to exist. I chose to build two lists in parallel, so that corresponding keys and show strings would be at the same index number.

That gave me the following code, ready to implant into my program. I reversed part of the name "TuplePair" becoming "PairTuple" so that the usage calls could be easily swapped to test it.

class PairTupleCombobox( m_tkntr_ttk.Combobox ):
    def _process_listTuplePair( self, ip_listTuplePair ):
        r_list_keys = [] 
        r_list_shows = [] 
        for tpl in ip_listTuplePair:
                r_list_keys.append( tpl[0] )
                r_list_shows.append( tpl[1] )
        return r_list_keys, r_list_shows
    def __init__(self, container, p_listTuplePair, p_defaultKey, *args, **kwargs):
        self.i_list_keys, self.i_list_shows = self._process_listTuplePair( p_listTuplePair )
        super().__init__(container, textvariable = self.i_StringVar, *args, **kwargs)
        self['values'] = tuple( self.i_list_shows )
        # still need to set the default value from the nominated key
    def getSelectedKey( self ):
        i_index = self.current()
        return self.i_list_keys[ i_index ]

Actual code

And here is the final code.

class PairTupleCombobox( m_tkntr_ttk.Combobox ):
    def _process_listPairTuple( self, ip_listPairTuple ):
        r_list_keys = [] 
        r_list_shows = [] 
        for tpl in ip_listPairTuple:
            r_list_keys.append( tpl[ 0] )
            r_list_shows.append( tpl[ 1] )
        return r_list_keys, r_list_shows
    def __init__( self, container, p_listPairTuple, p_defaultKey, *args, **kwargs):
        self.i_list_keys, self.i_list_shows = self._process_listPairTuple( p_listPairTuple )
        super().__init__(container, *args, **kwargs)
        self['values'] = tuple( self.i_list_shows )
        # still need to set the default value from the nominated key
        try:
            t_default_key_index = self.i_list_keys.index( p_defaultKey ) 
            self.current( t_default_key_index )
        except:
            pass
    def getSelectedKey( self ):
        try:
            i_index = self.current()
            return self.i_list_keys[ i_index ]
        except:
            return None

The main change is that once it had passed initial testing, I add a try clauses to:

handle finding the index of the default value and using current as a setter
handle in case no default was set and no selection was made

Summary

As an exercise, this was a nice combination of:

giving myself the ability to rework and simplify most of the places where I use a Combobox control
a rediscovery that using classes to rework other classes wasn't quite as tricky as I'd expected

I do think I should find a better name for my new classes and/or choose which one of them to keep long term.

A Beginner's Guide to Copyright, Programming and Software - Short Version

geraldew — Wed, 05 Oct 2022 13:50:33 +0000

This is intended as a bullet-point guide to the concepts of copyright that someone new to programming will need to understand. An interest of mine is Free and Open Source Software (FOSS) so this guide includes points about the copyright context of that.

This is part of a planned set of postings - starting with the essentials and then exploring more detail for those who are interested. The intended titles are:

Short Version (i.e. this post)
Medium Version
Edge Cases
Myths and Misunderstandings

As the various parts are published I will amend this posting to provide links in the list above - so if you don't seen any links, then then others are still yet to come.

The Short Version

The Law

Bear in mind that this will involve some over-simplifications - the intention is to be brief but prepare an understanding before hitting the complexities.

Here are the dot points:

Copyright by now has a fairly uniform legal approach around the world.
By default, anything created has copyright automatically applied at the point of creation.
By default, copyright means that the creator "owns" the copyright and any and nearly all re-distribution is only legal if there is authorisation from that owner.
With private individuals, the "owner" is that private individual but with employees creating things in the course of their employment, the "owner" is usually the employer.
An owner can sell their copyright to another party who then "holds" the copyright.
Copyright does not apply to titles.
Copyright does apply to translations.
Copyright laws were mainly set up long before software became a major thing, but were adapted enough to explicitly cover software too.
For software, copyright is independent of whether you get to see the source code.
It is possible for the owner to embed a license statement in the copyrighted work that can grant various rights and freedoms to others, perhaps without requiring either contact or negotiation.
There is a broad set of of such licenses that are collectively known as providing "software freedom".
In practice, there are two independent organisations who provide guidance about such software licenses - the Free Software Foundation (FSF) and the Open Source Initiative (OSI).
While there are many, many variations on the software freedom licenses, they tend to fall into two main types - most commonly referred to as "copyleft" and "permissive".
Only a copyright owner can re-issue content with a different license.
Not all countries have a concept of "public domain" in their copyright laws, and those that do are not all the same about it.
Nearly all copyright laws specify a finite period of time for which copyright applies to a work.
But, many countries have repeatedly changed their copyright laws so as to keep pushing the point where copyright ceases for a work further and further into the future.
Somewhat similar to how titles are not copyrightable, many copyright laws allow for limited unauthorised publishing (e.g. of small extracts, or for specific purposes) but the terms, concepts and conditions vary quite a lot.

Those are the core principles that you need to know in order to understand the basics of copyright as a legal concept.

However, that leaves many possible complications, from interpretations of details, international variations, practical likelihoods, application to software, etc - those will instead be tackled in the "Medium" part.

Also, while most of that list is quite stark and bluntly stated, many misunderstandings and myths are often circulated - some of those will be tackled in the "Myths" part (e.g. suggestions that people other than the copyright owner can "re-license" a work).

Other Laws

As a quick aside:

The term "intellectual property" (or "IP") is somewhat made-up, so as to cover copyright, trademark and patent laws together;
Trademark laws vary greatly across countries, both legally and in practice;
Patent laws are about ideas and incidentally requires the ideas to be openly declared. By comparison, copyright is about the particular expressions - e.g. the text, pictures, videos, sounds, instructions.

Where both of those other "intellectual property" legal concepts have their own complications and variations.

Some other legal concepts that have intersections with software are:

contract law - particularly where one party is contracted by another to create a copyrightable work.
consumer law - where a copyrightable work is sold/given from a producer to a customer/user.
censorship laws - which might inhibit or prohibit the publishing of works independent of copyright
border laws - which might inhibit or prohibit the transit of works across national borders independent of copyright
(and perhaps many more such)

Other than being mentioned above, these other laws will not be covered by these notes. For one thing, they vary enormously from country to country.

Paying for things

Generally, copyright law doesn't say much about needing to pay for things. Instead it empowers the copyright owner to include payment requirements when authorising distribution and replication, such as via contracts and licenses.

How this plays out in practice varies enormously. For example, the replication might be physical (e.g. a paper book) and the payment physical (cash) with the bookseller returning a portion of the sale. Or it might all be virtual (downloaded software) and the payment virtual (credit card transaction online) direct to the producer (perhaps to get a valid unlock code to input to the software). Sometimes the model might be to not inhibit replication but to require allowing the copyright owner to "audit" the amount of usage and exact a corresponding payment. The mechanisms of such proprietary use of copyright are many and varied. Contracts, licenses and End User License Agreements (EULA) can be very complex.

The key is that unauthorised replication and distribution is a violation of copyright, independent of the exchange of funds.

Free and Open Source Software (FOSS)

There is a set of core concepts that are generally known as "the four freedoms": A program is free open source software if the program's users have the four essential freedoms:

freedom to run the program as you wish, for any purpose (freedom 0).
freedom to study how the program works, and change it so it does your computing as you wish (freedom 1). Access to the source code is a precondition for this.
freedom to redistribute copies so you can help others (freedom 2).
freedom to distribute copies of your modified versions to others (freedom 3). By doing this you can give the whole community a chance to benefit from your changes. Access to the source code is a precondition for this.

FOSS license statements, embedded in a work, effectively provide a pre-authorised negotiation, meaning neither creator nor adopter has to contact each other about the applicable conditions.

Note here that access to the source code is a mechanism to enable freedom.

By the way, you should ignore use of the term "commercial" software, as the software freedom concepts do not impede any parties from making financial (i.e. commercial) arrangements. The opposite of FOSS is proprietary where the defaults of copyright law apply, and all arrangements require an active negotiation.

The Two Main License Types

As said earlier, the major cleft among FOSS licenses are between two approaches. The distinction comes down to quite who is granted the most freedom:

with "copyleft" licenses the emphasis is on the end-user, ensuring that they are always passed the four freedoms;
with "permissive" licenses, the emphasis is on other developers/programmers, including allowing them to not pass on the four freedoms.

Those are distinct strategies with applicability to different situations. There is much discussion among the FOSS community about when each is the more suitable choice, or even whether it matters.

The thing that everyone agrees about, is that the copyright owner gets to choose the license to apply - essentially because copyright law enforces that privilege.

The Two Philosophies

About the two organisations who are the stewards of Free Open Source Software:

The Free Software Foundation came first, they set out the core ideas and also created the General Public License (probably the most important "copyleft" license).
The Open Source Initiative came later and, using the same four freedoms, emphasised the practical benefits of software freedom and also offered a service for approving specific licenses.

The two organisations have differing purposes and activities - complementary in effect. I recommend reading their own web sites for clarity - I see many inaccurate descriptions made about them by others.

My view is that you can mostly ignore any bickering between the Free Software and Open Source camps. They agree about most of what software freedom is all about. While their independent focus means they don't agree 100% - as a qualitative guess I would say the agreement is over 95% - indeed it is quite hard to find a license that one approves that the other does not.

Note they they also both support both "copyleft" and "permissive" licenses (and always have done)
Since this piece was initially published, I have published an article listing some of the differing license approvals by the FSF and OSI - see How many and of what nature are the license approval differences of the FSF and the OSI ?

Creative Commons

The innovation of using embedded licenses for software - to provide software freedom - was so successful that an effort arose to do something similar for non-software content. This led to the creation of a set known as Creative Commons licenses. These licenses enable authors of creative works to specify which rights they reserve and which rights they waive for the benefit of recipients or other creators.

This has also become sufficiently successful to now be quite frequently used for application to the documentation of software.

The core mechanism is the same as for software freedom licenses - of embedding a quotation of the license into the body of the copyrighted work. The distinction is about clarity:

of having a software license that talks about software concepts for applying to software
of having a content license that does not talk about software concepts for applying to things that are not-software.

Short Version Coda

Life is complex, and so the above opens the door for a lot of questions about the details. Those will be tackled in the next part.

Also, to reiterate, this "short" version is deliberately simplistic - and was left so, rather than litter it with footnotes and hyperlinks. Ideally I'd publish the full set all at once, but to avoid the overall delay of that, I will publish the parts only as and when I'm happy with them.

The reason I've written this, is because while I've seen a great many attempts at explaining Open Source Software or Free Software, nearly all presume a familiarity with copyright. So I wanted to try reversing that - to explain copyright first while placing FOSS in that context.