DEV Community: Allan Simonsen

How to spice up your markdown documents

Allan Simonsen — Tue, 30 Aug 2022 05:36:36 +0000

Some find markdown documents boring because you can only work with characters and don't have much control over the look and feel of the layout of your document.
But even when you are restricted to only using characters for writing and illustrating, there are actually a lot of different tools out on the Internet for making interesting and useful documents, blogs or documentation.

Fancy ASCII heading

In some cases a fancy heading can catch your readers attention. The online tools below lets you choose between different kinds of fonts. Just write your text and copy the generated ASCII art into your document.

  ______                                 _____  _____ _____ _____   _    _                _ _             
 |  ____|                         /\    / ____|/ ____|_   _|_   _| | |  | |              | (_)            
 | |__ __ _ _ __   ___ _   _     /  \  | (___ | |      | |   | |   | |__| | ___  __ _  __| |_ _ __   __ _ 
 |  __/ _` | '_ \ / __| | | |   / /\ \  \___ \| |      | |   | |   |  __  |/ _ \/ _` |/ _` | | '_ \ / _` |
 | | | (_| | | | | (__| |_| |  / ____ \ ____) | |____ _| |_ _| |_  | |  | |  __/ (_| | (_| | | | | | (_| |
 |_|  \__,_|_| |_|\___|\__, | /_/    \_\_____/ \_____|_____|_____| |_|  |_|\___|\__,_|\__,_|_|_| |_|\__, |
                        __/ |                                                                        __/ |
                       |___/                                                                        |___/

Diagrams

Sometimes a diagram can tell more than a thousand words. You may need to illustrate flows or database table relations.
I found these online tools for drawing diagrams:

    ┌──────────────────────────────────────────────────────┐
    │                                                      │
    │  ┌───────────────────┐                               │
    │  │                   │           ┌───────────────┐   │
    │  │   Diagram box     │           │               │   │
    │  │                   ├───────────►               │   │
    │  │     One           │           │  Diagram box  │   │
    │  │                   │           │               │   │
    │  └───────────────────┘           │     two       │   │
    │                                  │               │   │
    │                                  │               │   │
    │                                  └───────────────┘   │
    │                                                      │
    └──────────────────────────────────────────────────────┘

Insert images

Images can actually be converted to ASCII art. Online you can find a lot of different image to ASCII art converters. You just have to accept that you can't have the same high resolution images in your markdown documents as you can in PDF or Word documents.

Here are a few randomly chosen online tools from a Google search

MMMMMMMMMMMMMMWKOkkkk0XWMMMMMMMMMMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMKdc:::;;;::cxKNMMMMMMMMMMMMMMMMMMMMMMM
MMMMMMMMMMMK;'coddddddo:'.lNMMMMMMMMMMMMMMMMMMMMMM
MMMMNKkkOXWx':dddddddddd;.,0WX0OOXWMMMMMMMMMMMMMMM
MMNk:;;;;:c,.:dddddddddd,..:c;;;;;cOWMMMMMMMMMMMMM
MKc':odddoc,.'lddddddddc...;lodddo;,dNMMMMMMMMMMMM
K:'lddddddddc''lddddddc.':ldddddddd:'oNMMMMMMMMMMM
o'cddddddddddl'.,;;;;,.,oddddddddddd:'kMMMMMMMMMMM
c'ldddddddddo:';oxkkxl,,cdddddddddddc.dMMMMMMMMMMM
O:,:ldoooooo:'oKXXXXXX0c',cdddddddl:,cKMMMMMMMMMMM
MXo'.,;;;;;,.;0XXXXXXXNk' .;;;;;;'.,xNMMMMMMMMMMMM
Xo;;:loooool,,kXXXXXXXXd'.;c::::cc;;:kNMMMMMMMMMMM
l'cdddddddddl,,o0KXXKOl,;loddddddddo;'xMMMMMMMMMMM
c'lddddddddddl'.,:cc:'.;odddddddddddc'xMMMMMMMMMMM
O,,oddddddddo,.:oollol,.:odddddddddl':KMMMMMMMMMWM
Wk,,odddddoc'.cdddddddo;..,lddddddl':KMWXOkdollcck
MW0c,;cc:;;'.:dddddddddo,..',:ccc;,oOklc:;;::cl,'k
MMMN0dlloxKd'cdddddddddd;..,,.:lldOd;,:lodddddo':X
MMMMMMMMMMMO,;dddddddddo,..,:;oNMXc'cddddddddd;'kM
MMMMMMMMMMMWk:;;::::::;,cdOo;;;kNl'cddddddddd:'dWM
MMMMMMMMMMMMMN0xdoooodkKWMMK:;;cx,;ddddddddl;,dNMM
MMMMMMMMMMMMMMWKxollllloxkONx;;;;''::::::;;;lOWMMM
MMMMMMMMMMMWKdc;;:clllc:;;;::';;:kkdoooodxOXWMMMMM
MMMMMMMMMW0l;;coddddddddddo,'';;:KMMMMMMMMMMMMMMMM
MMMMMMMW0l,;ldddddddddddoc,,ko;;:0MMMMMMMMMMMMMMMM
MMMMMMMXo,,;clodddddddo:,'c0Wd,;:KMMMMMMMMMMMMMMMM
MMMMMMMMWXOdlc:;;;::;;;ckXNMWo,,cNMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMWX0kxddxOXWMMMMXc,,dWMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMMMO;,;kMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMMWd,,cXMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMMKc',xWMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMWx,'cXMMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMXc.;OMMMMMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMMMMMMMMMMNl'dWMMMMMMMMMMMMMMMMMMM

Tables

Markdown natively supports tables, but if you want to make a custom ASCII table, or if you are not quite comfortable generating your markdown table by hand, then you could try one of the following online tools. Some of the tools below also support converting CSV files to markdown or ASCII tables.

.--------.--------.
| Row 1  | Row 2  |
:--------+--------:
| 42     | 43     |
| 43     | 44     |
| 44     | 45     |
'--------'--------'

Folder structure

Sometimes you need to document a folder structure in you text or Markdown file and on for this you can use the tree command. Its build in on Windows and on Linux you can install it using sudo apt install tree if you havn't already have it installed.

$ tree
Folder PATH listing for volume Data
Volume serial number is C0000100 DCB8:8751
D:.
├───subfolder1
├───subfolder2
│   ├───subsubfolder1
│   ├───subsubfolder2
│   │   └───subsubsubfolder1
│   └───subsubfolder3
└───subfolder3

MariaDd Quick-tip #10 - Select table definition

Allan Simonsen — Tue, 23 Aug 2022 04:55:56 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Select table definition

Have you ever had the task of documenting a whole database or maybe just a few tables some programmer created years ago. I have, and I quickly found that having a query to directly select the schema of a table was a great timesaver.
Or maybe you don't have access to the production database, but the bugs or performance issues you experience, indicate a missing index or a wrong column definition, so you need to send a request for the IT operations department to fetch the information about a table from the database for you.
In both cases the queries below can be very useful.

SET @DatabaseName := 'test_db';
SET @TableName := 'departments';

SELECT c.COLUMN_NAME, c.COLUMN_TYPE, c.IS_NULLABLE
  FROM INFORMATION_SCHEMA.`COLUMNS` c 
 WHERE c.TABLE_SCHEMA = @DatabaseName
   AND c.TABLE_NAME = @TableName;

SELECT s.TABLE_NAME,
       s.INDEX_NAME,
       group_concat(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX ) AS index_columns,
       s.INDEX_TYPE,
       CASE s.NON_UNIQUE
            WHEN 1 THEN 'Not Unique'
            ELSE 'Unique'
       END AS is_unique
  FROM information_schema.STATISTICS s
 WHERE s.INDEX_SCHEMA = @DatabaseName
   AND s.TABLE_NAME = @TableName
 GROUP BY s.TABLE_NAME,
          s.INDEX_NAME,
          s.INDEX_TYPE,
          s.NON_UNIQUE
 ORDER BY s.TABLE_NAME,
          s.INDEX_NAME;

SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE  
  FROM information_schema.TABLE_CONSTRAINTS tc 
 WHERE tc.CONSTRAINT_SCHEMA = @DatabaseName
   AND tc.TABLE_NAME = @TableName;

MariaDB Quick-tip #9 - Number of rows in all tables

Allan Simonsen — Tue, 16 Aug 2022 03:36:39 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Number of rows in all tables

When you start to work on a project for a new customer, a good piece of information is how many tables does the database contain and how many rows are there in each table. Or maybe you don't have access to the production database and need to send a service request to the IT operations department to get information about the production database.
Then the query below will be useful to you.

SET @DatabaseName := 'test_db';

SELECT t.TABLE_NAME, t.TABLE_ROWS 
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.TABLE_SCHEMA = @DatabaseName
   AND t.TABLE_TYPE = 'BASE TABLE'

MariaDB Quick-tip #8 - All foreign key constraints

Allan Simonsen — Tue, 09 Aug 2022 02:27:26 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

All foreign key constraints

A well designed database relates its tables to each other using foreign key relations in order to ensure data integrity. But when the database has hundreds or even thousands of tables it can be a huge challenge finding all the tables related to a specific table you may have to modify.
The query below will find all the foreign key constraints between columns with a specific column name.

SET @DatabaseName := 'test_db';
SET @ColumnName := 'dept_no';

SELECT concat(rc.unique_constraint_schema, '.', rc.referenced_table_name) AS `Primary table`,
       concat(rc.constraint_schema, '.', rc.table_name) AS `Foreign table`, 
       rc.constraint_name AS `Constraint name`
  FROM information_schema.referential_constraints rc
  JOIN information_schema.key_column_usage cu ON rc.constraint_schema = cu.table_schema 
       AND rc.table_name = cu.table_name
       AND rc.constraint_name = cu.constraint_name
 WHERE rc.constraint_schema = @DatabaseName
   AND cu.COLUMN_NAME = @ColumnName
 ORDER BY rc.constraint_schema,
       rc.table_name;

MariaDB Quick-tip #7 - Find stored procedure

Allan Simonsen — Tue, 02 Aug 2022 02:41:53 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Find stored procedure

When working on large databases there can in some projects be hundreds or even thousands of stored procedures, so if your task is to find all stored procedures that query a specific table then finding it could be quite a headache.
This query below has helped me many times and I hope it will help you too.

SELECT r.ROUTINE_SCHEMA  as "Database",
       r.ROUTINE_NAME  
  FROM information_schema.routines r
 WHERE r.ROUTINE_TYPE  = 'PROCEDURE'
   AND r.ROUTINE_DEFINITION LIKE '%departments%'
 ORDER BY r.ROUTINE_SCHEMA ASC,
       r.ROUTINE_NAME ASC;

MariaDb Quick-tip #6 - Find table or column

Allan Simonsen — Tue, 26 Jul 2022 03:39:00 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Find table by name

If you have ever worked on a big database with a huge number of tables and you are not yet familiar with the whole data model, you know how hard it is to locate that specific table you are looking for. Or maybe you just need to get a list of tables that have similar names to put into that Power Point presentation you are working on.
Then the following query may be useful to you.

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS  
  FROM information_schema.tables 
 WHERE table_name LIKE '%dep%'

Find table that have a specific column

If you have been a programmer for some time and worked on different projects for different customers then you have probably come across databases with few or entirely without foreign key constraints. There can be a lot of reasons for not having foreign keys constraint and I won't go into them here, but one consequence of not having them is that finding tables that is related to other tables becomes hard so you need another way of finding related tables. And I do hope that the person who implemented the tables named the columns relating the tables by the same column name or a similar name. And if this is the case you can query all your database tables to find all tables containing columns with a specific name. This query below does that.

 SELECT CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME), c.COLUMN_NAME 
   FROM information_schema.TABLES t
   JOIN information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME 
  WHERE c.COLUMN_NAME LIKE '%name%'
    AND t.TABLE_SCHEMA <> 'information_schema'

MariaDB Quick-tip #5 - Create a sequence of date and time

Allan Simonsen — Tue, 19 Jul 2022 03:15:00 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Create a sequence of date and time

When working with data that is somehow related to dates and times you may need to generate a sequence of date to group by or select from.
The trick here is to use a recursive CTE (Common Table Expression. It can be a bit tricky to read but what the CTE does is to make a union with one additional datetime for each recursion. More examples can be found in the MariaDb documentation. And the syntax is very similar to the SQL Server syntax as you can see here.

The code below will create a sequence of datetimes with 10 minutes interval between each datetime value.

Like recursion in general, this can take up a lot of resources and be slow so I would not recommend using it in production code with high number of execution. But for data analysis and drilling into your data this trick can be very useful.

SET @stepSizeInMinutes = 10; -- Change this line to change the time interval
SET @from = '2017-01-01 00:00:00.00000';
SET @to = '2017-01-04 23:50:00.00000';

-- Create Recursive Discrete Table
WITH RECURSIVE Recursive_CTE AS
(
       SELECT @from AS TimestampUtc
        UNION ALL
       SELECT TimestampUtc + INTERVAL @stepSizeInMinutes MINUTE
         FROM Recursive_CTE
        WHERE TimestampUtc < @to
)
SELECT *
  FROM Recursive_CTE
 ORDER BY TimestampUtc

Create the sequence using MariaDB Sequence engine

As mentioned in the comments by @darkain, thanks for the tip, the date sequence can be created by using the MariaDB Sequence engine and this solution is a lot more affective than using the recursive CTE. So here is the SQL for that:

SET @stepSizeInMinutes = 10; -- Change this line to change the time interval
SET @from = '2017-01-01 00:00:00.00000';
SET @to = '2017-01-04 23:50:00.00000';

SELECT @from + INTERVAL seq * @stepSizeInMinutes MINUTE AS `Datetime sequence`
  FROM seq_0_to_99999999
 WHERE seq BETWEEN 0 and (SELECT TIMESTAMPDIFF(MINUTE, @from, @to) / @stepSizeInMinutes);

MariaDb Quick-tip #4 - Random int for each row

Allan Simonsen — Tue, 12 Jul 2022 03:55:19 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the years, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Random int for each row

The trick to generate a random number for each row is to use the RAND() function, multiply it by the maximum number you need and then cast the number as INT.

The code below show how to create a number between 0 and 24 and another example of how to create a number between 15 and 25.

The calculation of the random int for each row is not very efficient, so you should consider using a more efficient method for generating the random numbers if you need it in production code.

You have to do something slightly different to generate a range of int on the SQL Server

CREATE OR REPLACE TEMPORARY TABLE names (Name VARCHAR(50));

INSERT INTO names (Name) 
VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane');

-- Generate random ints between 0 and 24
SELECT Name, CAST(MOD(RAND() * 100000, 25) AS INT) AS `Random Int`
  FROM names;

-- Generate random ints between 15 and 25
SELECT Name, CAST(MOD(RAND() * 100000, (25-15)) AS INT) + 15 AS `Random Int`
  FROM names;

MariaDB Quick-tip #3 - Prepending zeros

Allan Simonsen — Tue, 05 Jul 2022 02:43:49 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Prepending zeros

Some data formats require that your numbers have prepended zeros so you can get the MariaDb to format the number in your query. This is done by using the LPAD(str, len [, padstr]) function and it is alot more elegant than what you have to do to achieve the same result on the SQL Server.

CREATE OR REPLACE TEMPORARY TABLE names (Name VARCHAR(50), ImportantNumber INT);

INSERT INTO names (Name, ImportantNumber) 
VALUES ('Joe', 2), ('Bob', 4), ('Anne', 42), ('Jane', 134);

SELECT Name, LPAD(ImportantNumber, 4, 0) AS ImportantNumber
  FROM names

MariaDB Quick-tip #2 - Randomize rows

Allan Simonsen — Tue, 28 Jun 2022 03:20:02 +0000

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Randomize rows

Sometimes you want the returned rows of your query to be randomized. That could be when generating test data.
The trick is to use ORDER BY RAND(). The RAND() function will generate a new random number between 0 and 1 for each row and when sorted the rows will be randomized.
This is not something you should put into your production code unless you have no other way of randomizing you rows, because MariaDB has no way of optimizing this kind of query so if the trick is used on a large table it will take time and computing resources.
This approach to randomize the rows of your query is only slightly different from how you would achieve the same result on the SQL Server

CREATE OR REPLACE TEMPORARY TABLE names (Name VARCHAR(50));

INSERT INTO names (Name) 
VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane');

SELECT Name
  FROM names
 ORDER BY RAND() -- Ordering by RAND() will randomized the rows

MariaDB Quick-tip #1 - Range of int

Allan Simonsen — Tue, 21 Jun 2022 03:03:02 +0000

MariaDb tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Range of int

When testing your code you may need a range on integers and the trick for generating such a range is to use a local variable that you increment by one for each row.

In the code below we are using the information_schema.COLUMNS table, so be aware that this specific query will only generate a maximum of numbers that is the the same as the information_schema.COLUMNS table, but you can use any one of your table to get the same effect.
You have to do something slightly different to generate a range of int on the
SQL Server.

CREATE OR REPLACE TEMPORARY TABLE int_range (num int);
SET @range_limit = 24;

 INSERT INTO int_range
 SELECT 0
  UNION
SELECT @rownum := @rownum + 1
  FROM information_schema.COLUMNS C, (SELECT @rownum := 0) r
 WHERE @rownum < @range_limit;

SELECT num 
  FROM int_range

Google Charts visualization JavaScript API

Allan Simonsen — Tue, 14 Jun 2022 03:22:54 +0000

In this article I will show how you can use the Google charts JavaScript API to create a simple chart quickly. You can use the Google charts JavaScript API to create a lot of different charts, but this article is a quick introduction on how to get started using the API for creating a simple column chart.

The google charts visualization API can be loaded from the Google CDN URL: https://www.gstatic.com/charts/loader.js

Then we need to load the chart visualization api and set a callback function to be called when the api has been loaded. This is done by these two lines of code.

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

The drawChart(...) callback function creates a DataTable from an array of population data for each year from 1980 to 2021.
Then a ColumnChart object is created and then added to the populationChart div together with the data table.

Below is the entire example code for the html page that creates a column chart of some population data.

<html>
<head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
    <h1>Population</h1>
    <div id="populationChart"></div>
    <script type="text/javascript">
        // Load the Visualization API and the corechart package.
        google.charts.load('current', {'packages':['corechart']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {
            const populationData = google.visualization.arrayToDataTable([
                ['Year', 'Population'],
                [1980, 5122065],[1981, 5123989],[1982, 5119155],
                [1983, 5116464],[1984, 5112130],[1985, 5111108],
                [1986, 5116273],[1987, 5124794],[1988, 5129254],
                [1989, 5129778],[1990, 5135409],[1991, 5146469],
                [1992, 5162126],[1993, 5180614],[1994, 5196642],
                [1995, 5215718],[1996, 5251027],[1997, 5275121],
                [1998, 5294860],[1999, 5313577],[2000, 5330020],
                [2001, 5349212],[2002, 5368354],[2003, 5383507],
                [2004, 5397640],[2005, 5411405],[2006, 5427459],
                [2007, 5447084],[2008, 5475791],[2009, 5511451],
                [2010, 5534738],[2011, 5560628],[2012, 5580516],
                [2013, 5602628],[2014, 5627235],[2015, 5659715],
                [2016, 5707251],[2017, 5748769],[2018, 5781190],
                [2019, 5806081],[2020, 5822763],[2021, 5840045]
            ], false);
            const options = {
                width: 820,
                height: 400
            };
            var populationChart = new google.visualization.ColumnChart(document.getElementById('populationChart'));
            populationChart.draw(populationData, options);
        }
    </script>
</body>
</html>

This is how the chart looks in the browser:

The Google Charts visualization api is very well documented with lots of examples here: https://developers.google.com/chart/interactive/docs