Pure Javascript port of the open source OCR engine Tesseract

 https://github.com/naptha/tesseract.js#tesseractjs

Analyzing CSV files using SQL with Squirrel SQL client and Apache Calcite JDBC driver

 

Intro

I had written another article to work with CSV files as SQL, using the csvjdbc driver, but it does not yet support using joins, unions, etc, which is actually quite important. So in this article, i am using an alternative JDBC driver that does support working with multiple tables  : Apache Calcite CSV example.

Apache calcite is a generic framework which provides a way to work with different data sources like files using SQL. It defines interfaces like Schema, Table, Colum etc. that we can implement to enable a resource to be used in SQL. It also has the ability to execute SQL statements using these customized implementations. It also provides some implementations, e.g. one to read CSV files as relational data, and provides a JDBC driver for this purpose. This CSV JDBC driver is the one we will use in this article. It also provides a command line utility to execute SQL , called sqlline.

The JDBC driver

The way this driver works is that instead of the database name, we have to point to a JSON file that defines the database model. This file will in turn specify a directory that holds the csv files. And all the csv files under that directory will be considered as tables in that model. The CSV files need to have a header for column names.

The SQL client

Now, we could use the driver directly from a java program, perhaps passing it the sql from the command line, but why do that when a sql client written in java is available ? That's where Squirrel SQL comes in. It has been my favorite since last many years.

Btw, i have read some good stuff about DBeaver too, which we might cover later.

Installation

You need to download the jar installer from https://squirrel-sql.sourceforge.io/#installation, and then run it on the command line with java -jar <jarname-you-downloaded>. Run it as administrator, else it may throw errors about not being able to write to the installation directory. After successful install, it will create a shortcut and you can use that to launch the Squirrel SQL client.

Configuring the client

Firstly, we need to register our calcite csv jdbc driver with squirrel. There is a Drivers tab on the left side. Click on that, and the plus icon to add a new driver. The sample JDBC url looks like jdbc:calcite:model=path/to/model.json. The driver class name is org.apache.calcite.jdbc.Driver. 

We also need to specify here the JDBC driver jar itself, and the other jars that it depends on. One issue we face here is that there are a large number of dependencies. How do we collect them all ? Well its easy : Head over to the maven repo for the csv example. First download the csv example jar itself. Then download the pom.xml in the same folder. 

Now run 

mvn dependency:copy-dependencies -DoutputDirectory=.

This will download all the jars needed in the same folder. Add all these jars to the Driver's dependencies from the UI. It should say that the driver has been installed successfully, and you can see it with a green tick in the list of drivers.

See the screenshot below :

Now we need to create a data source, which in our case will point to the model json, which in turn will point to the folder containing csv files. So click now on the Aliases tab, then on the plus icon to create a new alias. Substitute the your path to model.json in the url. No user/password is needed. See the screenshot below :

But whats in the model.json ?

{

  version: '1.0',

  defaultSchema: 'Trading',

  schemas: [

    {

      name: 'Trading',

      type: 'custom',

      factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory',

      operand: {

        directory: 'C:\\projects\\trading\\db'

      }

    }

  ]

}

As you see, its a schema created using the CsvSchemaFactory, that points to the directory holding our Csv files.

Now you can connect to the data-source by double clicking the alias. Once connected, the objects tab will display the csv file names as table names, and the header names as column names. In the SQL tab, you can execute the SQL queries. Check out what table names and column names are displayed and use exactly those. You may need quotes around those with spaces, e.g. "Industry Name".

And yes, we can write SQLs on multiple tables using joins, subqueries, unions etc. But its not all happy sailing. All columns for instance, are defined as type VARCHAR, so we always need to do type conversions. Also empty string are not NULLs and need to be handled separately.

Can we change the model.json to redefine the proper column types ? This needs to be researched. A workaround is to define views with checking and conversion functions on columns to avoid the above issues.

Happy SQLing !

Analyzing CSV files with SQL using Squirrel SQL Client and csvjdbc driver

Note

I just found that the csvjdbc driver does not yet support using joins, unions, etc, which is actually quite important, so if you want that, check this article that uses Apache Calcite.

Hola. So being an SQL fan, i somehow find it much easier to work on Excel files in SQL, rather than Excel itself. SQL is so beautiful, so easy, yet so powerful !

The JDBC driver

Firstly, we need a JDBC driver that can read CSV files. I am using the one from https://github.com/simoc/csvjdbc. Its read-only, and that's okay for me, since i only wish to analyze the files. The jar can be downloaded from maven : https://mvnrepository.com/artifact/net.sourceforge.csvjdbc/csvjdbc/1.0.42

The way this driver works is that instead of the database name, we need to specify a directory that holds the csv files. And all the csv files under that directory will be considered as tables in that database. The CSV files need to have a header for column names. The separator can be mentioned in the jdbc url.

The SQL client

Now, we could use the driver directly from a java program, perhaps passing it the sql from the command line, but why do that when a sql client written in java is available ? That's where Squirrel SQL comes in. It has been my favorite since last many years.

Installation

You need to download the jar installer from https://squirrel-sql.sourceforge.io/#installation, and then run it on the command line with java -jar <jarname-you-downloaded>. Run it as administrator, else it may throw errors about not being able to write to the installation directory. After successful install, it will create a shortcut and you can use that to launch the Squirrel SQL client.

Configuring the client

Firstly, we need to register our csvjdbc driver with squirrel. There is a Drivers tab on the left side. Click on that, and the plus icon to add a new driver. The sample JDBC url looks like jdbc:relique:csv:<foldername>?separator=,&fileExtension=.csv. We also need to specify here the JDBC driver jar itself, and the other jars that it depends on. See the screenshot below :

It should say that the driver has been installed successfully, and you can see it with a green tick in the list of drivers.

Now we need to create point to a data source, which in our case will be a folder containing csv files. So click now on the Aliases tab, then on the plus icon to create a new alias. Substitute your folder-name in the url. No user/password is needed. See the screenshot below :

Now you can connect to the data-source by double clicking the alias. Once connected, the objects tab will display the csv file names as table names, and the header names as column names. In the SQL tab, you can execute the SQL queries. Check out what table names and column names are displayed and use exactly those. You may need quotes around those with spaces, e.g. "Industry Name".

Happy SQLing !

IOT with the Raspberry Pi Pico

A few months ago, I got a requirement for a POC, to create a system to monitor an UPS. The inverter would provide its status, e.g. "Mains On", "Inverter On", "Battery Voltage", "Mains High", "Mains Low" etc. over a RS232 serial port. We had to read it and send it over the net to our website where the data would be stored and monitored. The readings were to be taken every 1 minute.

The Microcontroller

Reading UART serial data is not difficult for a very basic microcontroller. Even without a UART port, it is simple to read it using a program, as I did here. However, the requirement to post the data real-time to a website, called for network connectivity, and hence, a more powerful solution.

This is where the Raspberry Pi Pico comes in.

• Its a powerful microcontroller board, with many IOs including UART ports.
• It has a Pico W version, which comes with WiFi connectivity.
• Its cheap.( The base version is around Rs 350)
• It can be programmed with MicroPython, which is much easier to program in than C, and the Thonny IDE is very user friendly. Kudos to the MicroPython and Thonny devs !

While I thought I was done with the choice of microcontroller, a problem arose. There was no WIFI connection available onsite, only an Ethernet one. Now the Pico does not have a version with an Ethernet port. So i had to do some research again.

There were options where I could connect an additional Ethernet board to the Raspberry Pico, but then, I wanted it to be inbuilt, to keep it simple.

Fortunately, Wiznet provides Ethernet versions, modified versions of the Pico board, with an Ethernet port. 

Now where would I get it in India ?

Digikey seems to be a great site for electronics, it has a huge catalogue and low prices, and volume discounts. However, i just wanted one item to start with, and the shipping charges were too high. Hence i opted for Hubtronics, which was costlier for the board, but overall cheaper due to the lower shipping charges.

Development

The Pico I had ordered is a development board, so it can be programmed easily. It is to be connected using a micro-usb cable to you computer. It has a switch which can be used to put it in 2 different modes :

1. As a USB storage device. This mode will be used only once at the start, to copy the Micropython image to the Pico's internal storage. This is necessary to support the Micropython programming environment.
2. As a USB device that interacts with the Thonny IDE to develop and test the Micropython programs.

Here's a tutorial on how to go about it.

Some Gotchas

Some problems that I encountered

• Voltage compatibility - The serial port signals were 0-5V, whereas the Pico's IO, like the Pi, is 0-3.3V. There are various solutions for this, from transistors to chips, but I did it with a resistive voltage divider, since the RS232 baud rate used was low. 
• Male and Female pinout differences - This may not be immediately clear, but is obvious when you think of it. Male and Female pinouts are different, since Rx in one has to go to Tx in another and so on. So lookup the pinout as per the type of socket.
• UART parameters matching - When you initialize the Pico's UART, make sure that its parameters like baud-rate, parity, start-stop bits match that of the port you are interfacing with.
• Reads and timeouts - If you read multiple bytes at a time, and it times out, you may be get lesser data than what you asked for.
• Sleeps - Sleeps in between can make you miss data that is being sent in a stream.
• Micropython differences - Since Micropython is not the full python version, you will not have some commonly used classes, e.g. not all data structures are available.

Git for Svners

Git, the currently popular versioning system is a pain to understand for people who come from CVS/Subversion, and are used to having just a central repo and local working directory.
Since Git is a distributed repository system, each developer has his own repository, that keeps track of local changes, as well as can sync with one or multiple remote repositories. Hence the concepts of Git are rather different from SVN, and the same terms like checkout mean different things in SVN and GIT. Being an SVNer earlier, i too found Git frustrating to start with.

Atlassian has a good tutorial that explains these concepts :
https://www.atlassian.com/git/tutorials/learn-git-with-bitbucket-cloud

Some important concepts :

• The local repository is a full fledged repository, and  independently holds a history of its own branches, commits etc. One could create and work with a local repository, without ever connecting to a remote repository, say for some private work that is not shared.
• We usually start by cloning a remote repository to a local one. See clone. This creates a copy of the remote repository to the local, as well as adds a reference to the remote repository usually as "origin" to the local repo's list of remotes( See the remote command). Thus, when syncing with the original repository, we can specify it as "origin".
• The checkout command does NOT checkout files from remote. Instead it checks out the specified branch to the working directory. i.e. that branch now becomes the current one.
• GIT has a staging area where the changes to be committed are kept. We specify which files are to be staged by using the add command. Without adding, the commit command will have nothing to work on.
• When you push your changes to the remote, you are syncing changes committed in your local repo to the remote one. So you must have committed them first to your local repo. This may not be obvious to SVNers, who would expect the locally updated files to be automatically pushed !
•  TODO : See the branch command
• Unlike SVN, we do not need separate working folders for branches. We can switch to another branch in the same working folder, using the checkout command. This means that uncommitted changes can be lost, unless we stash them to a temp location.

Maven for ANTers

Coming from a build tool like ANT, it can be pretty frustrating for developers to understand maven. It seems to be doing too many unspecified things, too rigidly. This article tries to understand maven from that perspective.

So here's a quick summary :

1. Maven executes Goals, just like Ant has targets.
2. In addition, maven has Phases, each phase being a list of goals. We can also tell maven to execute one or more phases, and then each phase will execute the goals grouped under it. Phases themselves have an order, so that if we execute a given phase, the phases coming before that in the predefined order will be executed first.
3. The default phases are validate, compile, test, package, verify, install, deploy.
4. Each maven execution run happens in a lifecycle, which has phases under it. The default lifecycles are default, clean and site. 
5. We can create and define our custom goals. These are packaged in a Plugin.
6. Maven also allows us to specify and manage dependencies of the project.
7. Maven resources have a groupId, artifactId and version.
8. Maven provides for a repository to fetch/store the artifacts. A central maven repository is the default .Also, maven keeps a local repository on the system where its run, to avoid fetching from the remote repository each time.

 

See also https://www.baeldung.com/maven-goals-phases

Read these links first for a basic understanding :
https://maven.apache.org/guides/introduction/introduction-to-the-lifecycle.html

In ANT, we specify tasks and their dependencies. We can execute a specific task, and only that, along with its specified dependent tasks will be executed. But this also means that tasks like clean, compile, generate, copy, package, install, deploy etc have be specified in details for all projects, along with config like source, staging, target dirs etc. Also external dependencies like jars have to be managed, along with their versions.  These may be common amongst multiple projects. Should we check these into source control, or have a separate common location ? What if we want to add a common functionality to all builds ? How do we name and version output artifacts ?

Maven tries to answer these questions. It tries to provide a standard way to execute projects, by promoting convention over configuration :

• Standardized project directory structure. e.g. The src/main/java,  src/main/resources src/test etc
• Naming conventions for articfacts using groupid, artifactid, version
• Providing a dependency configuration, and a repository mechanism to store and access needed dependencies
• Out-of-the box implementation of standard build lifecycle, so that a project can be build with minimum configuration
• An inheritance mechanism, so that a build may be shared amongst multiple projects, each overriding only the parts needed.
• Profiles to have different builds for different case, e.g we might just need compile in dev mode, and the full jar with dependencies may have to be built in production mode.

So if we have a simple java project with no dependencies, then a minimal pom with just the groupid, artifactid and version id will be able to build the project from clean to deploy.

Maven works with lifecyles, phases and goals. It has default implementations of these. We can also create our own lifecycles, which is a list of phases, in order. Then there are goals, that execute in a particular phase which do the actual work. e.g. in the default lifecycle, the install phase has a goal install:install.

Goals are usually implemented using java classes called plugins. When creating plugins, we can specify what phase the plugin should execute, tho its not mandatory. The phase in which a plugin executes can also be specified via the build configuration, and this will override the default.

What can we execute with the maven command ?  

A list of mixture of phases and goals. Whatever phases are specified, all the phases before them in the life cycle will be implicitly executed.

However, thats not the case with goals, only the specified goals will be executed.

e.g. we can execute just the install goal using mvn instal:install, instal:install being the name of the goal in the install phase. Earlier phases will not be run. However, it can cause problems like in this case, since this goal looks for the anme of the output jar to install in the execution context, which is missing. In this case, it can be remedied by also installing the jar:jar goal before, so that it gets the jar name.

What does the maven build file consist of ?

1. The groupid, artifactid and version id of the project being built, this the minimal info needed.
2. The list of dependencies needed by the project.
3. The list of dependency repositories, if using any other than the standard maven one.
4. The list of repositories if any, to deploy/publish the final artifact( usually jar, war etc)
5. The build section to use and configure non-standard maven plugins, in which phase and with what params are they to run. Similarly to customise/change execution of the standard maven plugins.
6. A profiles

What to watch on Prime Video

TODO

Movies

English

Youth 

The Unbearable Weight of Massive Talent

Dune

Kimi

The tender bar

Notting Hill

Many Tom Cruise movies, including the MI series

Many James Bond movies

Pride and Prejudice

Sense and Sensibility

The Matrix

The Jurrasic movies

The Harry Potter movies

The Lord of the rings

The tomorrow war

The Personal History of David Copperfield

The Big Wedding

Wild mountain thyme

Before I fall

2067

Pan's Labyrinth

Another round

Motherless Brooklyn

Mothers day

The kids are all right

Minari

Bliss

Gravity

Bottle Shock

Uncle Frank

A street cat named Bob

Aeronauts

Mr Holmes

Children of the bride

Gone girl

Wild oats

Mud

The secret : Dare to dream

Indian

Sharmaji Namkeen

Sherni

Missing

Bonus

Dev bhoomi

Series

Howards End

Tales from the Loop - Sci Fi

The mentalist - Detective thriller

Scorpion - Nerds rule

Person of Interest - AI and crime

Jack Ryan - CIA thriller

Jack Reacher - Private Investigator thriller

Wheel of time - High Fantasy

Picard - star trek

The good doctor

Doctor House

Sherlock Holmes

Seinfeld

Using a tplink router as wireless bridge to extend range

It's usually straight forward nowadays :

• Reset the new router
• Connect to it via WIFI using the default SSID and password given on the back, or WPA
• Login to the new router, at its default I.P, e.g. 192.1681.0.1 or http://tplinkwifi.net/
• You need to create a password at the first login
• Choose the range extender option
• Select your main network from the list
• Choose static I.P and use an I.P in the same subnet as the main router. E.g. if the main router is at 192.168.1.1, you can choose any 192.168.1.*
• Disable DHCP server
• If WPA_PSK does not work, try WPA2_PSK. This was the issue for me.
• Restart and check that the internet light is green

Docker stuff

Removing all docker containers in one go

docker container rm `docker ps -a | cut -f 1 -d " " | tr "\n" " "`

We are using command line substitution with the ``, and populating the container ids inside it. The cut gets the first field from docker ps, that is the id, and the tr replaces the newline with a space, so that we get all ids in a single line.

Btw, this will also work in the new Linux Subsystem for Windows(LSW) command line, its able to see the docker container created from windows command line.

A simple timer switch using a potentiometer

 

A draft, not yet complete...

A digital timer that uses a potentiometer and ADC to select a time interval. The input triggering circuit is isolated from the mains using an optocoupler.

BT136 triac

MOC3061 optocoupler

pic12f675 - the cheapest microcontroller i could find, with an ADC.

The potentiometer is missing in the circuit below, its connected to the ADC

For the source code, see https://github.com/manojmo/pic_micro/blob/master/pot_minutes_calc.c

Based on the potentiometer voltage read from the ADC, it calculates the ON time, turns on the triac, waits till the ON time expires, then turns OFF the triac.

Useful online sites/services

SQL Fiddles

It happens that we want to test sql on a particular db, and do not have it installed.
There are sites which provide us a testing environment for various databases. e.g.
Note that some sites may consider whatever you submit to be under commons creative license.

https://dbfiddle.uk/ ( This one has mariadb too )
http://www.sqlfiddle.com/#!3/e48975/1

Editing tips

Select Code Block

Awesome facility in Jedit, that allows us to select the text between matching braces. Not only curly, but square and round braces are also supported. The text can be cut or copied as needed. I found this useful when editing large JSON data. Most of the free online editors do not allow to cut the text of a collapsed node, and this was what i wanted. I was able to achieve this using the above facility in Jedit, tho it does not show a collapsed view. Its under Edit->Source menu. We can also just navigate to the matching braces.

Starting with Sails.js

Having decided to explore Nodejs, i wanted to start with something like Rails that would be able to generate an MVC app from the database. While i haven't worked with Rails, i did work with the Rails inspired CakePHP, and liked it. So Sails.js was the equivalent in the Nodejs world.

On a side note, while Node is noted for its performance in handling I/O bound tasks, there are async frameworks in the Python world too like gevent and asyncio and uvloop, which can do the same, and there are some comparsions : https://magic.io/blog/uvloop-blazing-fast-python-networking/
Development wise, i think i would prefer python over Javascript.
Anyways, on to sails :

So i installed node and sails 1.1 and fired the app with sails lift. Its nice to have some example models and setup running like sails has, so we can get started quickly.

Though sails does not generate CRUD from the database, there is a sails-inverse-model module that does a basic CRUD generation. Haven't tested end to end tho.

I made some changes to the User model, added another model.
Then i wanted to point the default file-based database to my local mysql. Accordingly i toddled over to the config/datastores.js and made the necessary changes. Restarted sails and got a nasty shock ! There was a big stack trace about some auto-migration and some error about being unable to insert data. But i hadn't setup any migration. Seems that sails has a facility to apply to latest moel changes to the database, to keep the database in sync with the models. This is called migration, and is set to 'alter' by default in the config/models.js. This alter mode drops and recreates tables instead of altering them ! I fell this is a very dangerous behaviour, and should be been turned off by default.
So  first thing, set migration to 'safe', or risk losing your data !


The next problem i faced was that my changes to the User model were not being reflected, and i was getting and error on account of that. I thought it might be some sort of caching. But restart or deleting the temp folder did not help either. Neither could i find any such issues reported on the internet. Finally, when looking at the files, i saw that there was a User.js~ backup file created by jedit, and this had the old User model that was getting picked up. Apparently the filter to load the files is too lenient, it will even allow a *.xml extension as a model.
In order to see where the files were being loaded, i created a syntax error in the model file. Sure enough, Sails complained about the error, and i saw from the error trace that it was being done in (node_modules/sails/lib/hooks/moduleloader/index.js:304:18). Adding a [^~]$ at the end of the regex fixed the loading-backup-files issue at least.

Another issue was about having to restart sails after every change, which is really irritating. I saw options like forever, nodemon and sails-hook-autoreload. I tried the auto-reload-hook, but it seems to trigger the auto-migration  again, so i removed it. I tried forever, one issue is that the stop does not work. Also, difficult to see logs, and if there are errors, it crashes. Still to check out.

Trying to get the hang of files. Did not like the actions approach, where each action lives in a separate file, rather than all in a controller. But the controller with methods approach is also supported. Tried using the ajax-form from parasails as in the examples. However, my form was not being rendered. Read about the client asset js file, and how we need to add one and register the page there, and write form validations. What about server-side validations, these would need to be repeated there too ? There should be a way to share them. And the default layout is updated to load all of these client files at once ! Why not include the required one in the particular view instead ?
Model objects are global, tho there seems to be a setting to control this. I like to know what i am importing.

Overall, sails seems to be doing too much implicitly, and if something is not working, finding and changing the behaviour takes a lot of time. I also wonder whether how all this affects performance. The Waterline ORM has its limitations like not fetching associations data. Also, what about all those facilities like default apis, one would need to know how to turn them off. I think i would prefer to go with something minimal like express, rather than sails.

On the positive side, sails comes with responsive ui and ready-to-go facilities.
It seems that sails comes with an auto-generator, and just by defining a model, we can expose the model's api as json.

Single Page Applications, Server vs Client-side, Ej, React, Vue, Angular

Since a few years, single-page applications have become popular, along with the advent of micro-services. Why have they become popular, and what are the advantages in using them ?

Traditionally, typical web-applications followed the Model, View, Controller pattern, with all of these written on the server side. So a java based app would have the views in some java-related technology like JSP or JSF. Also the routing logic by the controller was in the same technology. Similarly, in a PHP app, it would all be in PHP.

Probably with the advent of AI/ML/Analytics, languages like python became the choice for those kind of applications, due to the extensive libraries/frameworks available.
So if I wanted to move from my Java application to python, I would need to rewrite not just the models, but the controllers and views too in python.
So people started thinking, could we not separate the UI part, and make it independent of the backend ? Enter the SPAs.

Single Page applications are so called because they keep all the UI and routing logic as one bundle( page) loaded once in the browser(might be split if its too large), and  subsequently, no UI has to be loaded from the server. Also, usually the routing, i.e controller logic is in the same bundle and not on the server side. Other than loading the UI quickly, without page refreshes apparent to the user, another advantage is re-usability. The UI has been detached from the server side code, and communicates with the server-side thru http apis for the business logic. As long as the apis produce the same output, the server-side tech-stack can be changed, without affecting the UI part.

Some of challenges for SPAs are :

1. Search engine optimization : UI pages are not really urls on the server-side, but just one javascript resource containing all the UI/routing content, and hence are not available in the traditional way to the search engines. One solution may be to render the pages that need to be indexed, on the server side, and the others on the client.
2. Initial load times :  Can be high for SPAs, since all UI pages are loaded in one go on the browser.
3. The server side APIs exposed for the use of the SPA also become available for everyone, and increase the chance of hacking or un-authorized use. Also, since javascript for the controllers and views can be hacked thru javascript, one needs to be more careful in validating the flow on server-side as well.

Angular, React, Vue are some examples of SPAs. SPAs render UI on the client-side, i.e in the browser.

EJS, Pug, Handlebar are some server-side templating engines for javascript. Like JSP, or JSP with EL and JSTL etc. They will generate UI code on the server-side, so are like the traditional web applications.

Responsive means one that responds to its environment, changes behaviour depending on where it is loaded. e.g. menus, page layout gets re-arranged depending on whether its loaded on a mobile, or a tablet, or a desktop/laptop, so that it looks good in each. See https://www.w3schools.com/html/html_responsive.asp

While a responsive app can be used via a browser on both mobiles, and computers, it will not be able to use the full native functionality of that platform, e.g. use the camera, GPS or other system devices.

What are native mobile apps ?
Native means written for a particular platform using its tools/SDK, like Swift for iOS, java for Android etc. There are also frameworks like React Native, that will generate a native app from code written in web like technologies i.e.html-js.
A hybrid app is a web-app that can access some but not all native functionality. It uses a sort of bridge component that can invoke native functionality.

Bootstrap is a free and open-source CSS framework directed at responsive, mobile-first front-end web development. It contains CSS- and JavaScript-based design templates for typography, forms, buttons, navigation and other interface components

What is <script type="text/template">some html</script>

It will be ignored by the browser for rendering. It is used to define an html template into which values can be substituted to create html on the fly,e.g. adding a new row to a table, by client side templating frameworks like Vue, React etc. Nothing new here, it was possible since a long time to add user-defined xml, (not just under the script tag ) into a page and use it this way.

Router Port Forwarding

Sometimes,( at the risk of exposing your computer to the big bad world), you want to allow access from the internet to an application running on your P.C.

Your P.C is usually going to be behind a router, so the internet knows only the address of the router and not your P.C. Your router is keeping your internal network separate from the internet. The router has two I.P.s, an internal one to communicate with the internal network, and an external one to communicate with the internet.

You also should have a firewall setup on your router to not allow any incoming traffic to your internal network.

But suppose you do want to expose an application on your P.C. to the internet, how to go about it ?
The internet knows only the router's external I.P. So obviously, that has to be used.

We need to configure what is called "Port Forwarding" on the router. What it allows us to do is redirect  an incoming request to the router on a particular port to the same/another port on some machine in the internal network.
So for example, i might setup forwarding such that a request to http://router-external-ip:8080 is redirected to my P.C 192.168.1.200:80, thus making my application running at 192.168.1.200:80 available to the internet.

Note that the firewall rules may have to be changed to allow the incoming traffic, preferably restricted to the external address making the request, and the port being used.

There are sites online that will allow you to test whether the port forwarding is working or not, e.g. https://www.yougetsignal.com/tools/open-ports/

If you are getting connection refused errors, check that the firewall on the P.C allows the incoming call, as well as the firewall on the internet host that is making the call. E.g if its hosted, the hosting provider may have opened default ports like 80, but blocked others like 8080 for outgoing calls.

Eye-friendly dark styling for the browser

It is easier on the eyes to have a darker color than white as the background of the websites we browse.

I can think of 2 possible approaches to achieve this :

1. Setting a web-proxy in the browser settings, and manipulating HTML thru the proxy. The problem is with https traffic which is encrypted and cannot be read/changed in between. There are proxy servers like https://www.charlesproxy.com/ which get around this by generating their own certificates.
2. Using a JS browser plugin/extension, which allows us to execute JS/inject CSS on the fly after the page loads. Stylish was one such user extension, which allowed custom styles. However, it had issues with snoooping on your data, so i decided not to go with it. Stylus seems to be another alternative. But doing it via javascript is more powerful. I had written one such extension for chrome : https://chrome.google.com/webstore/detail/onload-scripts/gddicpebdonjdnkhonkkfkibnjpaclok

So i started off with injecting css using javascript to change the background color to gray. However, there are sites like Facebook, which seem to check that their divs are not being changed. I found that this is usually true for divs containing images. So i thought, maybe i can exclude such divs from the styling, and it seems to be working. Below is the javascript used. Disclaimer : I have copied some of it from a  stackoverflow answer.

UPDATE : The mutationsObserver seems to take too much time on FB.
The evenlistener seems to work well.

    document.addEventListener("DOMNodeInserted", function(e) {
        //console.log( "DNI" + e);
        anode = e.target;
        if( anode.tagName && (anode.tagName.toLowerCase() == 'div' || anode.tagName.toLowerCase()  == 'span'  || anode.tagName.toLowerCase()  == 'td')){
            setbgcolor(anode);
        }
    },
    false);

-----------

function addCss(rule) {
  let css = document.createElement('style');
  css.type = 'text/css';
  if (css.styleSheet) css.styleSheet.cssText = rule; // Support for IE
  else css.appendChild(document.createTextNode(rule)); // Support for the rest
  document.getElementsByTagName("head")[0].appendChild(css);
}
var bgcolor = 'lightgray';

function setbgcolor( elem){
    // Don't change if first child elem is image. For sites like FB
    if(  ! elem.firstElementChild || (elem.firstElementChild && elem.firstElementChild.tagName.toLowerCase() != 'img') ){
        elem.style.backgroundColor = bgcolor;
        //console.log( "ONLS:" + elem.outerHTML);
    }

}

// divs with images
var allDivs = document.getElementsByTagName("div");
for( var i=0; i< allDivs.length; i++){
    var currDiv = allDivs[i];
    setbgcolor(currDiv);
}
// CSS rules
let rule  = 'body {background-color: ' + bgcolor + '} ';
    //rule += 'div {background-color: ' + bgcolor + '} ';
    rule += 'pre {background-color:' + bgcolor + '} ';
    rule += 'td {background-color:' + bgcolor + '} ';

addCss(rule);


// Select the node that will be observed for mutations
var targetNode = document.getElementsByTagName('body')[0];

// Options for the observer (which mutations to observe)
var config = { attributes: true, childList: true, subtree: true };

// Callback function to execute when mutations are observed
var callback = function(mutationsList, observer) {
    for(var mutation of mutationsList) {
        if (mutation.type == 'childList') {
            for (var i = 0; i < mutation.addedNodes.length; i++) {
                var anode = mutation.addedNodes[i];
                //console.log( "ONLSMO" +  anode.tagName + anode.id );
                if( anode.tagName && anode.tagName.toLowerCase() == 'div' || anode.tagName.toLowerCase()  == 'span'  || anode.tagName.toLowerCase()  == 'td'){
                    setbgcolor(anode);
                }
            }
        }
    }
};

// Create an observer instance linked to the callback function
var observer = new MutationObserver(callback);

// Start observing the target node for configured mutations
observer.observe(targetNode, config);

// Later, you can stop observing
//observer.disconnect();

Relationships in sqlalchemy

Sqlalchemy is a powerful and flexible framework in python, to interact with relational databases.
This post will cover only a small portion of working with relationships.

What are the advantages of relationships ?

They allow us to query data of related objects along with the main object.
This can be done via a join, or separate queries, either eagerly, or lazily( when the related object is accessed)
Also, they make it easy to insert/delete/update data into related tables, especially in case of OTM/MTMs

Sample Entities

Consider the entities defined below :

----

class User(Base):
     __tablename__ = 'users'

     id = Column(Integer, primary_key=True)
     name = Column(String(50))

     addresses = relationship("Address", back_populates="user")

class Address(Base):
     __tablename__ = 'addresses'

     id = Column(Integer, primary_key=True)
     city = Column(String(50))
     street = Column(String(50))
   
     user_id = Column(Integer, ForeignKey('users.id'))
     user = relationship("User", back_populates="addresses")

----
Here, a user can have many addresses, reflected by the addresses relationship. An address on the other hand, belongs to a single user, reflected by the user relationship.

Sample Data

Consider the following data :

ed_user = User(name='Edward')
ed_user.addresses = [ Address(city='Pune'), Address(city='Mumbai')]
bob_user = User(name='Bob')
bob_user.addresses = [ Address(city='Pune'),Address(city='Delhi')]

session.add(ed_user)
session.add(bob_user)

Creating the tables

Its possible to create the tables needed for the entities using metadata.create_all() :

engine = create_engine('sqlite:///:memory:') # Memory engine

Session = sessionmaker(bind=engine)

session = Session()

User.metadata.create_all(engine) # Create the tables

Logging of sqls can be enabled with :
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

Test Scenarios

All Users, with all addresses

qry_users = session.query(User).all()

print( "All users", [ (qu.name, [add.city for add in qu.addresses]) for qu in qry_users])

This is quite straightforward. We did not explicitly query for addresses. The users will be queried. Since the relationship loading default is lazy, queries for addresses of each user will be fired when the address details are accessed. Since a separate query is fired for each user, the lazy option is not performant if there are many rows of the main object, and we need to access addresses for each.

Users with a Mumbai address,  Mumbai addresses only

Why do we specify the Mumbai condition twice ? The first part is to filter users, and fetch only those with Mumbai addresses. The second part is to filter the addresses for each user, and restrict only to Mumbai addresses. This can be a bit confusing first. To filter the main object, we could do :

qry_users = session.query(User).filter(User.addresses.any(city='Mumbai')).all()

However, this will filter user, not addresses, so we will get non-Mumbai addresses too for each user.

In this case, since both are to be filtered, an inner join will suffice.

qry_users = session.query(User).join(User.addresses).options(contains_eager(User.addresses)).filter(Address.city=='Mumbai').all()

We joined with User.addresses, this way, we do not have to repeat the join condition, it is picked up from the relationship.

What is the need for the contains_eager ? It says that the related addresses have already been loaded from this query, do not fire the relationship queries again. Without it, not only will the related addresses query fire again(poor performance), but all addresses will be fetched, which we do not want.

Lets try to query all users again. What's this ? Edward's addresses show only Mumbai ! This is a result of caching. The Edward user object was last populated only with Mumbai address, and was cached along with its related objects. A session.rollback(), or session.expire_all() or session.expire(obj) can be used to clear the cache and make sqlalchemy fetch the latest data from the db. It would be a good idea to put one of these before each test scenario, to get the expected results.

All Users,  Mumbai addresses only

Note that in this case, we are not filtering user, only addresses. So if a user does not have a Mumbai address,she should still be listed, albeit with an empty addresses collection. i.e an outer join. This is usually true for related objects. This seems to be a straightforward case, and maybe something like filterrelated( obj, condition) should have been available. But its not. We have to again perform a join, an outer one.

----

addresses = User.metadata.tables['addresses'] # reference to a table object

sel = addresses.select().where(Address.city=='Mumbai')

qry_users = session.query(User).add_entity(Address).outerjoin(('addresses',  sel)).options(contains_eager(User.addresses)).all()

----

We have used a slightly different format, with the select, since i wanted to avoid duplicating the join condition with addresses. Sqlalchemy has many such options. Again, note the contains_eager,  to avoid querying for related addresses again.

----

print( "All users, Mumbai addresses only", [ (qu[0].name, [add.city for add in qu[0].addresses]) for qu in qry_users])

----

Note that with multiple entities selected in the join, the output is not a single entity, but multiple, wrapped in a Result object. Also, unlike with a single entity, the results will contain duplicates, as in a sql join. If we choose specific columns instead of the entire entity, the result will wrap the column without any entity. This is undesirable : changing the query changes the way in which results are accessed.

**Actually, with a contains_eager, one would expect to get only the User entity, with the address as a related entity. There are some inconsistencies or difficult to understand usages. Dropping the add_entity above, leads to a single User entity in the output.

qry_users = session.query(User).outerjoin(('addresses',  sel)).options(contains_eager(User.addresses)).all()

All Users with Delhi address,  all addresses

Here, we want to filter user using addresses, but fetch all addresses of the filtered users. This scenario shows how filtering and fetching related objects are separate things.

qry_users = session.query(User).filter(User.addresses.any(city='Delhi')).all()

A common mistake here might be to try User.addresses.city. Try to print type(User.addresses). Its an InstrumentedAttribute, not a list of Address. So it won't have a city member and trying to access it will throw a "AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with User.addresses has an attribute 'city'". However, the results of the query execution, will be entities, so  qu.addresses will be a list of addresses, as we have already seen above. Its important to understand the difference between Entity class and Entity instance.

Interesting reads

Using java 8 features :

https://dzone.com/articles/functional-programming-patterns-with-java-8

Understanding time complexity :

https://adrianmejia.com/blog/2018/04/05/most-popular-algorithms-time-complexity-every-programmer-should-know-free-online-tutorial-course/

Async with Lightweight threads in java

http://blog.paralleluniverse.co/2014/02/06/fibers-threads-strands/

Go vs Java concurrency example of fetching urls :

https://dev.to/napicellatwit/go-for-java-developers-or-is-the-java-concurrency-that-bad-6ff

Profiling Javscript/Node applications

https://marmelab.com/blog/2018/04/03/how-to-track-and-fix-memory-leak-with-nodejs.html

A rant against annotations

This is an old issue, ever since annotations came up. I usually dislike most use of annotations, except for those that belong to the source code, like assert or deprecated. The reason is that they break reusability by pulling into a class info about how the class is to be used by other classes, and many times, tying a class to a framework.

E.g. using spring/hibernate annotations in a class tie that class to that framework. What if i later want to change from hibernate to something else ? I will have to change the source code.

Consider the following case of a service class CustomerService using a customerDao. The dao uses something @Component(name='customerDao'),
and its injected into the service as @Resource(name='customerDao')
Lets say that this Dao uses hibernate. Later on, we decide to change the dao to use JdbcTemplate instead of hibernate. But i want to keep the hibernate implementation as well. And maybe, i need to use both of them in different places. So i now have a new customerDaoSpTpl say. But the service code now needs to be changed in order to use this new customerDaoSpTpl. So did we really achieve DI, if we needed to change the service class using the dao ?

The problem is due to annotations. We are putting stuff in a class, that does not really belong in a class definition, but is about the wiring or interaction of classes.
There are also issues when old classes from a jar are to be replaced with new ones, the annotation processor can fire annotations for both. ( There probably is some exclude facility for the annotation processor ) The names used in annotated classes from a jar can't be changed either.

Ideally the wiring should be kept separate from the class definition.
Spring does support this, thru xml as well as a java annotations format, but everyone seems to be putting annotations into source code, because thats easier to use upfront.

One disadvantage of having a single separate source for wiring maybe that it has frequent changes and always needs merging when checking in.

Distributing the database

You want to design you application to scale, so need to choose a database solution that will scale.
And it would be nice to have the querying and integrity features of an RDBMS.

Assuming that we want to shard data, and not just have a replica,
Some questions arise :

•  How easy would it be to add a new node ?Should sharding be automatic, or per some partitioning rules ? If as per rules, what happens when we add/remove nodes, the data will need to be redistributed.
• If a table is distributed across nodes, what happens to primary key ids ? How to prevent duplicates across nodes ? Id ranges ?
• One major use of ids as primary keys is for use in foreign key constraints. But will a distributed table support foreign keys ? Probably not.
• Will ACID transactions be available ?

Found some open-source solutions to scale RDBMS :

• CockroachDB (https://www.cockroachlabs.com/) : A key-value store that supports the wire-protocol of Postgresql, sql, ACID transactions etc. So it behaves as if its a distributed postgresql to the sql drivers, but internally is not.
• Citus : https://www.citusdata.com : A Postgresql extension that allows us to scale Postgresql. The underlying db is indeed postgresql. However, not all postgresql features can be supported in distributed mode.
• Posgres-XL (https://www.postgres-xl.org/overview/)