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);


 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              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)
select
  to_char(i, 'FM0000000000'),
  format('TEST ITEM%s', i), 
  clock_timestamp()
from
  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.


select
    x   
    ,y  
    ,z  
from
    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.


select
    row_number() over()
    ,x  
    ,case when y = 0 then
        'Animal'
    else
        'Plant'
    end as y
    ,case when z = 1 then
        'Wine'
    else
        'Beer'
    end as z
from
    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.

Share:

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.

https://github.com/steelbrain/node-ssh

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

https://www.npmjs.com/package/tar

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:

Share:

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:

Share:

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.

Share:

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.

https://github.com/mscdex/ssh2

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

https://github.com/steelbrain/node-ssh

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.

https://www.npmjs.com/package/tunnel-ssh

npm install --save tunnel-ssh

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

https://knexjs.org/

https://node-postgres.com/

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.

Conclusion

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.


Share:

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.

https://blog.pyrospect.com/2019/01/why-javascript-is-best-programming.html

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.

https://nodejs.org/en/

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:

https://www.npmjs.com/

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.

https://momentjs.com/

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:

https://docs.npmjs.com/packages-and-modules/getting-packages-from-the-registry

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');

console.log(now);


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:

https://nodejs.org/api/

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.

https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/JavaScript_basics

https://javascript.info/

Conclusion

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.


Share:

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.

https://nodejs.org/en/

https://www.npmjs.com/

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.

https://www.typescriptlang.org/index.html

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.

Conclusion

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.


Share:

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.

https://blog.pyrospect.com/2019/01/how-to-convert-photo-images-to.html

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.

https://docs.opencv.org/3.4.5/d5/d10/tutorial_js_root.html

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.

https://github.com/oliver-moran/jimp

https://github.com/lovell/sharp

http://aheckmann.github.io/gm/

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

https://www.npmjs.com/package/opencv4nodejs

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

Share:

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.

https://pirosuke.github.io/games/breakout/


Source is on GitHub:

https://github.com/pirosuke/breakout

Share:

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.

Before

After

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.


Gallery

Here are some images I've made.

Amusement Park
Food Stands
Park
Girl Watch Ducks

Share:

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.

https://minecraft.net/en-us/

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.

https://minecraft.gamepedia.com/Function_(Bedrock)

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.

https://minecraft.makecode.com/

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:

https://www.reddit.com/r/MCPE/comments/5ta719/mcpewin10_global_chat_using_websockets/

https://gist.github.com/jocopa3/5f718f4198f1ea91a37e3a9da468675c

https://gist.github.com/jocopa3/54b42fb6361952997c4a6e38945e306f

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.

Conclusion

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


https://www.instagram.com/p/BrrYC3LAf9y/


https://www.instagram.com/p/BriMXvyA6VQ/


Share:

Search This Blog

Labels

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.

Labels

Recent Posts