How to subtract number of years, months and day from dates before 1900 in Google Spreadsheets?

Apparently Google Spreadsheet in incapable of handling dates before 1900 in the DATE, YEAR, MONTH, DAY functions.
So while in LibreOffice you can do this:

=DATE(YEAR(A1)-B1, MONTH(A1)-C1, DAY(A1)-D1)

where A1 is a date before 1900 and B1, C1, D1 are integers for the years, months, days to subtract
in Google Spreadsheet this results in a #NUM! error.

A workaround would be:

YEAR: =DATEDIF(-693990,A1,"Y")
MONTH: =DATEDIF(-693990,A1,"YM")

but this does not work properly with days.
A different way to put it is:

YEAR: =YEAR(A1+693961)-1900
MONTH: =MONTH(A1+693961)
DAY: =DAY(A1+693961)

However this does not help with subtracting and reconstructing the date.
So my workaround was to add a new spreadsheet function, because that can be written in javascript and these limitations do not apply.

From Tools > Script editor create a new script. Paste the following function and from Run > Test as add-on... add it to the sheet you need it in.

function dateSubtract(originalDate, yearsToSubtract, monthsToSubtract, daysToSubtract) {
  var date = new Date(originalDate);  
  date.setDate(date.getDate() - daysToSubtract);
  date.setMonth(date.getMonth() - monthsToSubtract);
  date.setFullYear(date.getFullYear() - yearsToSubtract);
  return date;
}

Than in the spreadsheet you can use this function like this:

=DATESUBTRACT(A1, B1, C1, D1)

What did I need this for?
In genealogy research the death records might indicate the age of the person in a years, months, days format. I collected some of these records in a spreadsheet and wanted to calculate the supposed birth date of the deceased.

Maven dependencies basics

Source: https://maven.apache.org/pom.html

Always required in a POM:

• groupId
• artifactId
• version

Dependency hierarchy:

• All POMs inherit from Maven's Super POM.
  This is why some properties you specify but not use have effect. For example you can set the compiler version with these properties:
  

  <maven.compiler.source>1.7</maven.compiler.source>
  <maven.compiler.target>1.7</maven.compiler.target>

• To set up a POM hierarchy within your project, all POMs that are specified as <parent> in other POMs or that have <modules> (aggregation aka. multi-module projects) must use <packaging>pom</packaging>.
  
• Note:  A POM project may be inherited from - but does not necessarily have any modules that it aggregates. Conversely, a POM project may aggregate projects that do not inherit from it.
  

Dependency scope:

• compile - this is the default scope, used if none is specified. Compile dependencies are available in all classpaths. Furthermore, those dependencies are propagated to dependent projects.
• provided - this is much like compile, but indicates you expect the JDK or a container to provide it at runtime. It is only available on the compilation and test classpath, and is not transitive.
• runtime - this scope indicates that the dependency is not required for compilation, but is for execution. It is in the runtime and test classpaths, but not the compile classpath.
• test - this scope indicates that the dependency is not required for normal use of the application, and is only available for the test compilation and execution phases. It is not transitive.
• system - this scope is similar to provided except that you have to provide the JAR which contains it explicitly. The artifact is always available and is not looked up in a repository.

Exclusion of transitive dependencies:

• One by one: specify them one by one
• All: use the * wildcard for both the groupId and artifactId

Show README and CHANGELOG on Maven Site

It is common that the project's README contains valuable information about the project. For example on a repository's webpage in Gitlab or Github, the README is displayed by default.
Here's a way to publish the README and CHANGELOG markdown files on the project's Maven Site:

In the Site descriptor (/src/site/site.xml) reference the files you want to publish:

<?xml version="1.0" encoding="UTF-8"?>
<project>
  <body>
    <menu name="Documentation">
      <item name="README" href="docs/README.html"/>
      <item name="CHANGELOG" href="docs/CHANGELOG.html" />
    </menu>
  </body>
</project>

In the pre-site build phase copy the files to the markdown resource directory. The site plugin transforms the documentation to HTML and outputs it to the corresponding target directory.
After the site building finished, clean up the duplicate files:

<project>
  ...
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-resources-plugin</artifactId>
        <configuration>
          <encoding>${project.build.sourceEncoding}</encoding>
        </configuration>
        <executions>
          <execution>
            <!-- Copy the readme and such files to the site source files so that a page is generated from it. -->
            <id>copy-docs</id>
            <phase>pre-site</phase>
            <goals>
              <goal>copy-resources</goal>
            </goals>
            <configuration>
              <outputDirectory>${basedir}/src/site/markdown/docs</outputDirectory>
              <resources>
                <resource>
                  <directory>${basedir}</directory>
                  <includes>
                    <include>README.md</include>
                    <include>CHANGELOG.md</include>
                  </includes>
                </resource>
              </resources>
            </configuration>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <artifactId>maven-clean-plugin</artifactId>
        <!-- Delete the markdown/docs directory to remove the readme and such duplicate files from the site source files. -->
        <executions>
          <execution>
            <id>clear-docs</id>
            <phase>site</phase>
            <goals>
              <goal>clean</goal>
            </goals>
            <configuration>
              <filesets>
                <fileset>
                  <directory>src/site/markdown/docs</directory>
                  <followSymlinks>false</followSymlinks>
                </fileset>
              </filesets>
              <excludeDefaultDirectories>true</excludeDefaultDirectories>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>
  ...
</project>

To show the process in terms of file structure:
The normal state:

.
+-- src 
| +-- site
| | +-- site.xml

After the files were copied:

.
+-- src 
| +-- site
| | +-- markdown
| | | +-- docs
| | | | +-- README.md
| | | | +-- CHANGELOG.md
| | +-- site.xml

The output in site target:

.
+-- docs
| +-- README.html
| +-- CHANGELOG.html
+-- index.html

A bonus note regarding the deployment url: it can be changed it the distributionManagement section:

<project>
  ...
  <distributionManagement>
    <site>
      <url>${maven.reports.deployBase}/${project.groupId}</url>
    </site>
  </distributionManagement>
  ...
</project>

What you absolutely need to know about JavaScript

When it comes to making websites HTML, CSS and JS are the three core technologies you need to know.

• Hypertext Markup Language (HTML) makes up the structure of the website
• Cascading Style Sheets (CSS) is responsible for styling
• JavaScript (JS) is responsible for enhanced user interactions

Both JS and CSS rely on the tree structure of the web page. The tree structure is represented with the Document Object Model (DOM).

Browser compatibility

Websites are rendered by the web browsers thus JS is also interpreted by the browsers, and this is why there are differences between their capabilities in interpreting JS.
You have two options here:

• check for browser compatibility yourself e.g. in the MDN JS Reference
• use a compiler/polyfill e.g. Babel to take care of compatibility for you

The standardized version of JavaScript is called ECMAScript. It has several editions since 1997. The 6th edition called ES6 and ES2015 added a significant new syntax and new libraries that generally make development easier, however it is not supported by all browsers.

The main browser compatibility issue is with Internet Explorer. Currently (2018/05) the only supported Internet Explorer version is IE11 and it only gets technical support and security updates from Microsoft - instead of being developed further it is replaced by Microsoft Edge.
If you want to support IE11 you have two options here:

• Use ES5 (standardized in 2009) - 99% of features supported by IE11
• Use ES6/ES2015 with a compiler/polyfill - 11% of features supported by IE11

Why you want to use the enhanced Javascript syntax

Probably the most important new feature of ES2015 is the use of promises instead of callbacks. This was then further simplified by the introduction of the async/await keywords in ES2017. Here is a demonstration of the above, and here is a guide to picking the right asynchronous way out of the three. 

If this is not enough see what else is in ES2015.

Frameworks and Libraries

There are many frameworks and libraries to help developers implement frontend applications. If you read How it feels to learn JavaScript in 2016 you'll get a general idea of how fast the frontend technology is changing.
Here are some recent trends:

• The State of JavaScript Frameworks, 2017
• Angular vs. React vs. Vue: A 2018 Comparison

Running Javascript outside of the browser

It is possible to use JS outside of the context of a web browser, for example it can be used to write server code or desktop applications. The most well known JS runtime environment is NodeJS. It has a package manager called npm.
If you want to run tests, compile code, do style checks it is a convenient way to run these commands and maintain these dependencies through npm.

Further readings

• The Complete ECMAScript 2015-2017 Guide
• W3C's JavaScript best practices
• Pick an IDE for JavaScript development

Trainings

• W3Schools' JS Tutorial
• CodeSchool's JavaScript courses

Documentation

• MDN JavaScript Reference
• ECMAScript Compatibility Table
• ES6 Features Overview

Some random notes about Jenkins pipelines

First of all, there are two type of syntax:

• Declarative
• has a single pipeline{} block on the top level
• it is possible to use Scripted pipeline syntax within a script{} step
• Scripted
• has stage('name'){} or node{} blocks on the top level

There are subtle differences:

• Changing directories
• in Scripted you can use the dir(){} block
• in Declarative you cannot. One way to change directories is to change directory on every line of your commands.

Declarative pipeline

Workspace

Apparently the workspace is something that is shared within the pipeline. It doesn't matter how many different docker images you run your steps in, they will all work in the exact same directory and whatever they change will be inherited by the next stage too.

Example: Same and different agents across multiple stages modifying the same workspace

pipeline {
    agent none
    stages {
        stage ('Node build') {
            agent {
                docker {
                    image 'node:8.7.0'
                }
            }
            steps {
                sh 'npm install'
                sh 'npm run build'
            }
        }
        stage ('Node test') {
            agent {
                docker {
                    image 'node:8.7.0'
                }
            }
            steps {
                sh 'npm run test'
            }
        }
        stage ('Maven build') {
            agent {
                docker {
                    image 'maven:3.3.9'
                }
            }
            steps {
                sh 'mvn clean install'
            }
        }
    }
}

Some common HSQLDB stored procedures

HSQLDB is the default database of LibreOffice Base.
Here are some of the stored procedures that might come in handy:

Numerical built-in Functions / Stored Procedures
ABS(d)	returns the absolute value of a double value
CEILING(d)	returns the smallest integer that is not less than d
FLOOR(d)	returns the largest integer that is not greater than d
MOD(a,b)	returns a modulo b
POWER(a,b)	returns a raised to the power of b
RAND()	returns a random number x bigger or equal to 0.0 and smaller than 1.0
ROUND(a,b)	rounds a to b digits after the decimal point
SQRT(d)	returns the square root
String built-in Functions / Stored Procedures
CONCAT(str1,str2)	returns str1 + str2
LENGTH(s)	returns the number of characters in s
LOWER(s)	converts s to lower case
REPEAT(s,count)	returns s repeated count times
REPLACE(s,replace,s2)	replaces all occurrences of replace in s with s2
SUBSTRING(s,start[,len])	returns the substring starting at start (1=left) with length len
TRIM( LEADING ... FROM ...)	TRIM([{LEADING | TRAILING | BOTH}] FROM <string expression>)
UPPER(s)	converts s to upper case
Date/Time built-in Functions / Stored Procedures
CURRENT_DATE	returns the current date
CURRENT_TIME	returns the current time
CURRENT_TIMESTAMP	returns the current timestamp
DATEDIFF(string, datetime1, datetime2)	returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values (both the long and short form of the strings can be used): 'ms'='millisecond',  'ss'='second', 'mi'='minute', 'hh'='hour',  'dd'='day',  'mm'='month',  'yy' = 'year'
DAYOFMONTH(date)	returns the day of the month (1-31)
DAYOFWEEK(date)	returns the day of the week (1 means Sunday)
HOUR(time)	return the hour (0-23)
MINUTE(time)	returns the minute (0-59)
MONTH(date)	returns the month (1-12)
SECOND(time)	returns the second (0-59)
YEAR(date)	returns the year
System built-in Functions / Stored Procedures
CAST(term AS type) CONVERT(term,type)	converts exp to another data type
COALESCE(expr1,expr2,expr3,...)	if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on
CASE v1 WHEN...	CASE v1 WHEN v2 THEN v3 [ELSE v4] END when v1 equals v2 return v3 [otherwise v4 or null if there is no ELSE]
CASE WHEN...	CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

Notational Conventions used above

• [A] means A is optional.
• { B | C } means either B or C must be used.
• [{ B | C }] means either B or C may optionally be used, or nothing at all.
• ( and ) are the actual characters '(' and ')' used in statements.
• UPPERCASE words are keywords

Java basics - random numbers

Math.random()

This is as simple as it gets:
Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0.

Usage:

double d = Math.random();

Random class methods

An instance of this class is used to generate a stream of pseudorandom numbers.

Here are some of the methods:

Method	Return type	Return value
Get the next value in the stream:
nextBoolean()	boolean	true or false
nextDouble()	double	between 0.0d (inclusive) and 1.0d (exclusive)
nextFloat()	float	between 0.0f (inclusive) and 1.0f (exclusive)
nextInt()	int	any int within int range
nextLong()	long	any long within long range
Generate a random integer in a given range:
nextInt(int n)	int	between 0 (inclusive) and the specified value (exclusive)

Usage:

Random r = new Random();
double d = r.nextDouble();

ThreadLocalRandom class methods

A random number generator isolated to the current thread.
It's a subclass of java.util.Random, so all methods that are in Random can be used here too.
Get the instance with ThreadLocalRandom.current()

Method	Return type	Return value
nextDouble(double n)	double	between 0 (inclusive) and the specified value (exclusive)
nextDouble(double least, double bound)	double	between the given least value (inclusive) and bound (exclusive)
nextInt(int least, int bound)	int	between the given least value (inclusive) and bound (exclusive)
nextLong(long n)	long	between 0 (inclusive) and the specified value (exclusive)
nextLong(long least, long bound)	long	between the given least value (inclusive) and bound (exclusive)

Usage:

double d = ThreadLocalRandom.current().nextDouble(0.0, 1.0);