Automation Techs For Productivity And Fun

Generate Data For Testing With PostgreSQL

When developing a software using PostgreSQL database as storage, the function "generated_series" is very useful to create data for testing.

PostgreSQL 9.5.15 Documentation - 9.24. Set Returning Functions

As explained in the document above, "generate_series" function is a function to fetch a ranged dataset.

For example, if you want a dataset with numbers 1 to 10, write like this:

select * from generate_series(1, 10);

(10 rows)

It's a simple function, but it's very useful to generate various patterns of test data.

## Creating A Test Data Table

You can create tables with test fixture data inserted by using "generate_series" with "INSERT-SELECT" command.

Let's say you have a table defined like this:

create table bulk_test_items(
  id serial not null,
  item_cd varchar(100),
  name varchar(100),
  created timestamp default current_timestamp,
  primary key(id)

And if you want to create data with item_cd 1 to 1000, run a command like this:

insert into bulk_test_items(item_cd, name, created)
  to_char(i, 'FM0000000000'),
  format('TEST ITEM%s', i), 
  generate_series(1, 1000) as i

And then, 1000 data would be inserted into table bulk_test_items.

  to_char   |   format    |        clock_timestamp        
 0000000001 | TEST ITEM1  | 2019-01-27 00:07:09.88704+09
 0000000002 | TEST ITEM2  | 2019-01-27 00:07:09.887053+09
 0000000003 | TEST ITEM3  | 2019-01-27 00:07:09.887056+09
 0000000004 | TEST ITEM4  | 2019-01-27 00:07:09.887057+09
 0000000005 | TEST ITEM5  | 2019-01-27 00:07:09.887059+09
 0000000006 | TEST ITEM6  | 2019-01-27 00:07:09.887061+09
 0000000007 | TEST ITEM7  | 2019-01-27 00:07:09.887062+09
 0000000008 | TEST ITEM8  | 2019-01-27 00:07:09.887064+09
 0000000009 | TEST ITEM9  | 2019-01-27 00:07:09.887066+09
 0000000010 | TEST ITEM10 | 2019-01-27 00:07:09.887068+09

You can easily change number of data to generate, by changing parameters of "generate_series" function.

## Generating Combination Data

You can also create combination data easily by cross joining multiple "generate_series" functions.

    generate_series(1, 3) as x
    ,generate_series(0, 1) as y
    ,generate_series(1, 2) as z

 x | y | z 
 1 | 0 | 1
 1 | 1 | 1
 2 | 0 | 1
 2 | 1 | 1
 3 | 0 | 1
 3 | 1 | 1
 1 | 0 | 2
 1 | 1 | 2
 2 | 0 | 2
 2 | 1 | 2
 3 | 0 | 2
 3 | 1 | 2
(12 rows)

And convert it to natural data using case expressions or other relations.

    row_number() over()
    ,case when y = 0 then
    end as y
    ,case when z = 1 then
    end as z
    generate_series(1, 3) as x
    ,generate_series(0, 1) as y
    ,generate_series(1, 2) as z

 row_number | x |   y    |  z   
          1 | 1 | Animal | Wine
          2 | 1 | Plant  | Wine
          3 | 2 | Animal | Wine
          4 | 2 | Plant  | Wine
          5 | 3 | Animal | Wine
          6 | 3 | Plant  | Wine
          7 | 1 | Animal | Beer
          8 | 1 | Plant  | Beer
          9 | 2 | Animal | Beer
         10 | 2 | Plant  | Beer
         11 | 3 | Animal | Beer
         12 | 3 | Plant  | Beer
(12 rows)

I've used to create these kind of test fixtures with programs. But for softwares based on PostgreSQL, using "generate_series" was much easier.


An Easy Way To Compress And Download Log Files From Remote Server Using Node.js

As I've written in the article below, there is a quite easy way to connect to and control remote Linux servers with Node.js.

How To Manage Remote Servers And Databases Through SSH Tunnel With Node.js

I've shown examples to run commands on server, and executing queries on remote database using the "node-ssh" package. These kind of remote scripts makes it easy to save time managing massive servers.

This time, I want to show you how to automate file downloading from remote servers.

Collecting and analyzing log files are common tasks for server admins. Log files are often huge, so you have to compress the file, download it to local machine, decompress the file at local, and delete the compressed file from server. It's not a difficult task, but it takes time. And this task comes up repeatedly.

There are nice packages for Node.js to automate these tasks.

"node-ssh" is a package for SSH connecting, and it also have functions to upload and download files through SSH.

"tar" package have functions to compress and decompress files, and it works also on Windows.

Using these packages, I've wrote a script that...

  1. Connect with SSH to remote server
  2. Compress log file with tar command
  3. Download compressed log file to local machine
  4. Delete compressed file from server
  5. Decompress log file on local machine
Here is the script:


Check And Remove EXIF Including GPS Data Contained In JPEG Files With Node.js

Exif is information data set to an image file when you take photos with cameras.
GPS data like latitude and longitude are often included in Exif data, so you have to be careful when uploading photos you took to public spaces on the Internet.

Popular photo sharing services like Instagram automatically remove GPS data when photos are uploaded, but if you're not sure how GPS data are treated on other services, you may want to check and remove Exif data by yourself before uploading.

There are many tools to check and edit Exif data, like ExifTool. And for programmers, most popular programming languages have some kind of libraries to treat Exif data.

For Javascript, Piexifjs would by easy to use. It works both for web client-side and Node.js.

Here is a sample script to show Exif data of JPEG images:

And Here is a sample to remove Exif data from JPEG images:


Bulk Resize Image Files Using OpenCV And Node.js

Installing OpenCV requires lot of work and disk space, so I know I should use other libraries like gm with GraphicsMagick or Jimp when I want to resize images with Node.js. Though I've already have OpenCV and opencv4nodejs installed, so I'll just write about resizing images using OpenCV.

"opencv4nodejs" is a wrapper package of OpenCV for Node.js. By using this package, you can call image processing functions included in OpenCV.

There are two functions for image resizing: "resize" and "resizeToMax". "resize" is for resizing or scaling image to specified size, and "resizeToMax" is for resizing image to height and width that fits specified max length keeping aspect ratio.

This time I wanted a script to bulk resize images in directory to fit in same size, so I used "resizeToMax".

Here's the script:

It resizes all jpeg images in SOURCE_DIRECTORY_PATH to fit 100 x 100 and saves it to DEST_DIRECTORY_PATH with same file name.


How To Manage Remote Servers And Databases Through SSH Tunnel With Node.js

I work as a web programmer, and I sometimes connect to linux servers using SSH for tasks like checking and downloading logs, checking server status, updating applications, and managing databases.

These tasks don't happen so frequently, but I noticed that I'm repeating same kind of tasks every time it happens, so for this time I wrote a Node.js script to automate these remote tasks.

There are two alternatives to choose when executing programs to control remote servers: put and run the program in the remote server, or write a program that connects to the remote server and run it locally. I chose the latter this time, because I have many different servers to manage, and I didn't want to install Node.js to each servers.

Connecting To Remote Server Through SSH

"ssh2" is a popular package for using ssh from Node.js. There seems to be everything you want to do with SSH in it.

"node-ssh" is a Promise wrapper package of ssh2.

I'm using node-ssh this time.

npm install --save node-ssh

This is a sample script that connects to remote server and executes "ls -al" command.

If you want to use "sudo", write like this:

const res2 = await ssh.execCommand('sudo ls /var/log/httpd', {stdin: sshPassword + '\n', options: {pty: true}});

Now we can execute any command and receive results from remote server.

Connect To Remote Database Through SSH Tunnel

Next, let's try connecting and executing queries on remote databases.

We'll use "tunnel-ssh" for SSH tunneling on Node.js.
This is another wrapper package of ssh2.

npm install --save tunnel-ssh

This time we'll use "knex" and "pg" to control remote PostgreSQL database.

npm install --save knex pg

Here is a template to create and connect a SSH Tunnel:

The opened tunnel closes when "tnl.close()" is called.

If you don't close it the node process keeps running, while the process is running,
you can use the SSH tunnel from other programs. This may be good when you want to create a SSH tunneling tool for other programs.

Here is a sample to select data from remote database through SSH tunnel:

You can fetch and update data from remote database just like using local database, by editing the part using knex.

This template would make your work like saving remote data to local Excel files,
bulk inserting data to remote databases, more easy.


I wrote about two ways to control remote servers through SSH using Node.js.

This should be helpful for automating tasks like managing many servers and remote databases at once.


Tutorial To Create Command Line Programs With Node.js

As I mentioned in the post below, I recommend creating command line programs with Node.js when starting to learn Javascript.

Most tutorials for Node.js starts with "How To Run A Web Application Server With Node.js", but if it's the first time for you to write a program, you should forget about Web for a while. Web Programming uses many kinds of technologies together, like HTML, CSS, HTTP, Client/Server... and it would surely make beginners confused.

So this is a tutorial about setting up Node.js and writing a simple command line program. It Node.js would be a help for you to write programs for automating tasks like converting image files, checking Excel files, and collecting data from Internet.

Setting Up Node.js Runtime Environment

Download installer from official site and install it to your PC.

After installing, "node" command and "npm" command should be added.

You can check it in command prompt by executing commands below:

node -v
npm -v

It would show you the version number of Node.js. if it's correctly installed.

"node" is the command to run Node.js programs.
If you want to run "sample.js" for example, execute a command as below:

node sample.js

"npm" (Node Package Manager) command is a command to download and install package libraries you use in Node.js programs.

You can find available packages here:

Creating A Node.js project

Let's create an example project named "HelloWorld" to check how to setup new Node.js projects.

First, open the command prompt and create a directory for your new project and move in it.

mkdir HelloWorld
cd HelloWorld

Next, execute the command below to initialize your Node.js project.

npm init

It asks you some questions about the project you are creating, but it's not important now, so just press Enter for each question.
This command creates a file named "package.json". It's a file that keeps information about your project.

Next, let's add a package to this project.

There are basic packages you can use without additional packages, but you would need to add some kind of packages for most projects.

This time, we would install "Moment.js": a popular package to control date and time.

You can install it by executing the command below:

npm install --save moment

This command downloads "Moment.js" with its depending packages to a directory named "node_modules" in your project directory.
"node" command references this directory when you call packages in your program.

The "--save" options saves the information of the package you installed to "package.json".

The name and version of the installed package would be written like this:

 "dependencies": {
  "moment": "^2.22.2"

This setting is used when you run this project on other computers.
As mentioned above, additional packages are downloaded in "node_modules" directory with dependencies,
and it uses large file size.
So when moving or copying, or commiting a project to some where else, we usually exclude "node_modules" directory from the project and reinstall packages at where we use it.

You can reinstall all the packages written in "package.json" by the command below:

npm install

It's the same when you want to run a Node.js project created by other developers.
If there's a file "package.json" in the project, it means you can prepare the project by executing "npm install" command.

Finally, if you want to uninstall packages from your project, execute the command blow:

npm uninstall --save moment

This would delete "Moment.js" and its dependent packages from "node_modules" directory and "package.json".

You can find more information about "npm" command here in the official document:

Write And Run A Node.js Program

At last your "HelloWorld" project is prepared, so let's write and run a Node.js program.

Create a file named "hello.js", with the content below:

console.log('Hello, World');

This is the most simple Node.js program.

Run the program by executing the command below in the command prompt:

node hello.js

It would show you a message "Hello, World".

This is all you have to do. Write programs with Javascript, and execute it with "node" command.

Next, let's make a program using "Moment.js" that we installed above.

Install the package again with npm command:

npm install moment

Create a file named "date_test.js" with this content:

// load Moment.js package
const moment = reqiure('moment');

const now = moment().format('MMMM Do YYYY, h:mm:ss a');


And execute it:

node date_test.js

It would show you the current date and time.

This program is using the "moment" package that you installed with npm command. The "require" function loads the package with the name specified.
So now, you can find packages in "npm" site, install it with npm command, and use it in your Node.js program.

For more

Here's a official reference of Node.js basic packages:

For more information about Javascript programming basics, check out these documents. These are documents for using Javascript on Web, but most of the part would be useful for Node.js too.


There aren't so many web sites explaining about Node.js programming for command line programs, but Node.js is really a good environment to create small programs for small tasks.

I hope this article help your start of programming with Node.js.


Why Javascript Is The Best Programming Language To Learn First

If you're a programming beginner, and you're not sure what you want to make with programs, and you don't want to waste time learning what you wouldn't use, Javascript would be the best programming language to learn first.

Javascript Is The Only Language You Can Use For Web Client-Side Scripting

The most important reason is that Javascript is the only language you can use for web client-side scripting at this time. All popular browsers support Javascript, and you need to use it if you want to create interactive web pages.

Javascript Can Run Standalone With Node.js

Javascript is not only for web. Using Node.js, you can create CLI programs to read and write files, send mails, or access databases. There are many package libraries enough for beginners.

There are libraries to create web server-side applications like Express, so you can write web backends with Node.js too.

Javascript Is Untyped

Javascript is untyped, which means you don't have to define types of values because the runtime guesses it from context. Programming without specifying types make large-scale programs difficult to debug, but for small programs for small tasks, this makes it easy to change your program dynamically to fit your situation.

If you want to use typed version of Javascript, you can use TypeScript, which is a language you can compile to Javascript.

Javascript Is Everywhere

Javascript was first a language only for web client-sides. But now, Javascript is used everywhere.

For example, using Javascript, you can ...

  • Create Windows10 applications
  • Create GUI desktop applications for Windows/Mac/Linux with Electron
  • Write automation scripts for Mac using JXA
  • Create CLI programs using Node.js
  • Write web application backends using Node.js and Express
  • Write modern web frontend UI using React.js/Vue.js
  • Customize Google Apps
  • Write programs to edit Excel files using ExcelJS
  • Create Native Mobile Applications For iOS And Android using React Native

This means that if you learn Javascript once, you can use it to create whatever you want to.


If you decided to start programming with Javascript, I recommend starting from creating CLI programs with Node.js. With Node.js, you can start without HTML, CSS, and HTTP knowledge, so it would be easier to just simply learn about Javascript programming.


How To Convert Photo Images To Fantastic Line-Drawings With Node.js

Last day I wrote an article about converting images to line-drawings using Python and OpenCV.

I like Python as a programming language and I believe OpenCV matches best with Python, though the main language for my personal projects is Javascript, I was looking for a way to do the same thing using Javascript and Node.js.

First I've tried OpenCV.js, the officially supported Javascript bindings for OpenCV,
but somehow it didn't work on my machine.

I found other image processing libraries not using OpenCV, and they looked useful for other purposes, but for the purpose this time, it seemed using OpenCV is the best.

I finally chose "opencv4nodejs", a Node.js module that works as Javascript binding for OpenCV alternative. And this worked perfectly well.

This library provides OpenCV functions as Javascript functions with more Object Oriented style to make it easy to use in Javascript programs.

Here is the source code for Node.js, which I rewrote from my Python program in the article above:

The "opencv4nodejs" module downloads OpenCV when installing from NPM, so you don't have to install Python and OpenCV.

npm install --save opencv4nodejs


Create A Block Breaker Viewer (Not A Game Yet) With Vue.js And SVG

Using Vue.js and SVG looks like the best choice for a web programmer who wants to create interactive games for kids.

I'm working on a Breakout game for practice, and it's much easier than I thought so far.

You can see it moving here. It doesn't have any event listeners, so you can just watch the balls breaking the blocks.

Source is on GitHub:


How To Convert Photo Images To Fantastic Line-Drawings Using Python And OpenCV

I've received a request for advanced coloring books from my daughter, who entered elementary school last year, but I couldn't find those kind of coloring books in book stores nearby. So I've wrote a program to create line drawings from photos.

The program works like this.



Source Code

I chose Python and OpenCV for image converting functions.

Install Python3 for your OS, and install OpenCV from pip.

pip install opencv-python

This is the script I wrote.
It reads ".JPG" files in source directory and outputs converted files to destination directory.


Here are some images I've made.

Amusement Park
Food Stands
Girl Watch Ducks


3 Ways To Run Commands Externally For Minecraft Bedrock Edition

My kids are into Minecraft lately, especially for building houses and other buildings in Creative Mode. It's like playing with LEGO blocks in a virtual world.

Minecraft provides a command system to control the world, like putting blocks to specified point and teleporting objects. And I found some ways to run these commands from outside of Minecraft, that means that I can create programs to build complex buildings automatically without clicking or tapping for each block.

These are the 3 ways I found to run Minecraft commands from external programs.

Using "function" command

The first and the easiest way is to use the "function" command.

As it's explained in the document above, create a function file with the extension ".mcfunction", write list of commands in it, and save it in function file directory.

If you create a function file named "Sample1.mcfunction", you can run in Minecraft by using the function command like this:

/function Sample1

The "function" command was only allowed in Java Edition before, but it was added to Bedrock Edition in version 1.8.

MakeCode For Minecraft

The second way is to use MakeCode For Minecraft.

This is a Scratch like graphical application provided by Microsoft. You can create a custom command by dragging and dropping Minecraft commands together. It seems custom commands are converted to Javascript programs, and you can also write custom commands directly with Javascript.

I think this is the best way for kids, not only for automation, but also for programming education.

Unfortunately I couldn't use this app because my Windows PC was too weak to run Minecraft and MakeCode together.

Creating An Original WebSocket Server

This is the third way, and the way I've adopted.

As I wrote above, MakeCode was too heavy for my Windows PC, so I needed a more lightweight tool to send commands to Minecraft. MakeCode communicates with Minecraft using WebSocket, so it should be possible to replace MakeCode with another original lightweight WebSocket server.

I found hints from these links:

It seems Minecraft uses JSON data for request and response.

You can catch events in Minecraft by subscribing it, and run commands by firing "commandRequest"s.

What you have to do is...

  1. Write a WebSocket script to subscribe events and send command requests
  2. Start your WebSocket server
  3. Connect to your WebSocket server from Minecraft by running "/connect" command
  4. Fire events from Minecraft to trigger your custom commands

For example, I wrote a WebSocket server that puts a block in front of the player when the player says "build" with Node.js.

Connect to this server from Minecraft with "/connect" command.

/connect SERVER_IPADDRESS:3000

Then just say "build" from Minecraft, and you would see a stone brick block put in front of the player.


Whichever way you choose, it would make it easy to create more complex buildings like these.


Listing Up Files Under Folder Easily On Windows

There's always a work to make list of file names and saving them to Excel files.

If you are a Windows user and  if you know how to use "dir" command and "tree" command, this work would finish in a minute.

You can run these commands using command prompt.

"dir" command - output files directly under folder

"dir" command is a command to output list of files directory under specified folder.

For example, if you want to write out files in "C:\Users\test" folder to a csv file like "C:\Users\test\Desktop\myfiles.csv", run the "dir" command like below.

dir /B C:\Users\test > C:\Users\test\Desktop\myfiles.csv

After running this command, open myfiles.csv and you'll see a list of files in it.

The command format means list this.


"/B" is a command option to output only file names.

"tree" command - output files and folders recursively

"tree" command to output every file and folder under specified folder recursively.

If you want to write output the file tree under "C:\Users\test\docs" folder to a csv file "C:\Users\test\Desktop\myfiletree.csv", run the "tree" command like below.

tree /F C:\Users\test\docs > C:\Users\test\Desktop\myfiletree.csv

The command format means list this.


"/F" is a command option to write out both folder names and file names. Tree command outputs only folder names when ran without this option.

for more commands...

These two command are very basic commands you can use on command prompt.

If you want to learn more, check out the command list by Microsoft.


Connecting To Microsoft SQL Server From Node.js Using Windows Authentication

I usually use Knex.js to connect to databases from Node.js programs.

According to the official document above, Knex.js is available for connecting to MSSQL, but it failed when I tried to connect using Windows Authentication.

It seems the package "node-mssql" which Knex.js is using, has two drivers: "Tedious" and "msnodesqlv8", and the default driver Tedious does not work with Windows Authentication.

I couldn't find out how to change the driver to use from Knex.js, so I gave up using Knex.js and decided to use "msnodesqlv8" directly from my program.


Here I found a document for msnodesqlv8.

This is a simple example program to connect with MSSQL and fetch data from a table using Promise.


4 Websites For Checking Out What's Trending In Japan

These are the websites I found when searching for information about Japan written in English.

"Tsunagu Japan" and "Live Japan" both mainly introduces best spots to visit this season. You can find unique hotels, sightseeing spots, and restaurants to visit when going on a trip to Japan.

"Moshi Moshi Japan" is good when you want to check out music trends, trending food, and festivals.

"SoraNews24" brings you casual news from Japan.


How To Repeat Node.js Tasks On Windows Without Using Task Scheduler

Using the Task Scheduler is the standard way to execute programs repeatedly on Windows.

But there is a problem that you have to save your Windows account if you want to execute the task when you're not logged in, and you have to remember to update your account when changing your password. If you forget to update, the task will try to login with your old account, and you may get locked out from Windows.

I've experienced this last day, searched for a better way to repeat my tasks.
I mainly write automation programs with Javascript for Node.js, and the best way I found, is to use "node-cron" an "PM2".

"node-cron" is a Node.js library that keeps your program process running, and executes the specified function according to schedule settings.
You can set the schedule with crontab syntax.

"PM2" is a popular process manager for Node.js.
We need it to start our program at Windows startup.

Usage Example

First you need to install node-cron and PM2.

npm install --save node-cron

npm install -g pm2

Also you need an additional PM2 plugin to execute programs on Windows startup.

npm install -g pm2-windows-startup

You can find the PM2 document for startup here.

Next, create a script for node-cron.

Let's say you created a Node.js program named "exampletask.js" with a exported function "main()", and you want to execute it on 9:00 AM every day.

A script for node-cron would be like this:

If you want to run it without using PM2, you can just run it as a Node.js program.

node exampletask_cron.js

You would see the program process keeps running, and executes your "main()" function at 9:00 AM.

The next step is to save the program as a PM2 process.
The next command adds "exampletask_cron.js" to the PM2 process list, and starts it as a PM2 process.

pm2 start exampletask_cron.js

Run the command below to check your PM2 process list.

pm2 ls

Next, save your PM2 process list.

pm2 save

Then set your process list to start on Windows startup.

pm2-startup install

For more information to custom settings, check out the PM2 document.

This is all you need to do to schedule your Node.js program.
You don't have to worry about updating your login account when changing Windows password anymore.


Dashi-Maki, The Best Egg Dish For Bento

"Bento" is a traditional and popular style to take lunch at school and at work in Japan. It's a kind of lunch box with rice, fish or meat, vegetables and other side dishes.

Watch this movie for an example:

How to Make Bento (お弁当の作り方)

A standard bento contains:

  • Rice with Furikake (a kind of seasoning) sprinkled on it
  • Dashi-Maki (Japanese style omelette roll)
  • Fried chicken or Fried fish
  • Pickles

Dashi-Maki is my favorite.
Here is a link for recipe to make Dashi-Maki, if you want to try.

What I Like

If you want to write articles with originality, writing about things or habits you love and are interested about is a good idea to start from.

Writing out why you love that target would help you think and find out what kind of things you really want.

For your readers, it would work as a table of contents of your new blog.

So, here's a list of what I'm interested about.


I work as a web application programmer, mainly using Javascript, Java, Python.
I also make games and educational applications for my kids as my personal work.

I'm interested about saving time by automating and bulk processing tasks,
and I'm thinking programming is the easiest way for it.

Pixel Art | Voxel Art

This is my new favorite I found about a year ago.
I love both watching arts other designers created, and creating arts myself.

I like pencil drawing also, but the great point of pixel (voxel) art is that
it is easier to create and transform with programs.

Books And Games

I use to read books with Kindle App in my smartphone.
I read Mangas, Novels and Technical books.

I also play lot of games, mainly smartphone games.I like single player RPGs and Adventure games rather than online multiplayer games. I think it's because I can't wait long to end the story.


These are what I like now, and this blog would mainly about these themes.

Hope you enjoy!


Search This Blog


Generate Data For Testing With PostgreSQL

When developing a software using PostgreSQL database as storage, the function "generated_series" is very useful to create data ...

Powered by Blogger.


Recent Posts