Linux Must Have Applications

Must Have for My Need

1. TimeShift
   System backup with snapshots for easy rollback
   Other backup solution Grsync and Back in Time
2. Graphics Software
   Inkscape, Gimp, Krita, 
3. Web Browser
   Google Chrome, Mozilla Firefox
4. Productivity Suite
   LibreOffice, Google Apps
5. Audio & Video Editing
   Audacity, KdenLive
6. VLC Media Player
   Obviously VLC has dominated the market with the codec compatibility, but more importantly for me was the ease of creating your own theme.  I have a killer theme customized for me so I love using it and provide me with some pride and accomplishment.  "Self pad on the back so to speak"
7. Veracrypt
   Disk encryption software
8. KeepassXC
   Password manager to help manage your hundreds if not thousands of passwords
9. FontBase
   There are literally thousands of fonts in the world and will be in the millions soon so it's important to have a font manager to help in the graphics designing.  The most important is that you dont need to install the font onto your system which in turn will help with performance overall since the system does not need to preload all those fonts.

Nice to Have

1. PlayOnLinux
   Install Windows application using Wine with ease 
2. Windows Games
   Lutris.net has tweeked Wine and uses things like forStarcraft II  uses DX9 to OpenGL - 64 bit - Wine Staging + Esync.  Other games will have other dependencies to optimize that particular game.
3. Thunar Bulk Rename
   Just like the name suggested, it gives you the ability to rename filenames in bulk based on patterns 
4. Konsole
   Terminal with the special ability to clone your commands from one session to another.
5. BeyondCompare
   Find and merge differences in files

This list is not complete.  I will be updating throughout the next month as I see the regular use of some of these tools.

Hierarchy Tree in MySQL

Extracted from : https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

Create the table to store the tree

CREATE TABLE t_hierarchy (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        parent int(10) unsigned NOT NULL,
        PRIMARY KEY (id),
        KEY ix_hierarchy_parent (parent, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT)
BEGIN
        DECLARE _level INT;
        DECLARE _fill INT;
        INSERT
        INTO    t_hierarchy (id, parent)
        VALUES  (1, 0);
        SET _fill = 0;
        WHILE _fill < fill DO
                INSERT
                INTO    t_hierarchy (parent)
                VALUES  (1);
                SET _fill = _fill + 1;
        END WHILE;
        SET _fill = 1;
        SET _level = 0;
        WHILE _level < level DO
                INSERT
                INTO    t_hierarchy (parent)
                SELECT  hn.id
                FROM    t_hierarchy ho, t_hierarchy hn
                WHERE   ho.parent = 1
                        AND hn.id > _fill;
                SET _level = _level + 1;
                SET _fill = _fill + POWER(fill, _level);
        END WHILE;
END
$$
DELIMITER ;

DROP FUNCTION IF EXISTS hierarchy_connect_by_parent_eq_prior_id;

DELIMITER $$

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    t_hierarchy
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    t_hierarchy
                WHERE   id = _parent;
        END LOOP;       
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_fill_hierarchy(6, 5);
COMMIT;

Select statement to retrieve tree

SELECT  CONCAT(REPEAT('    ', level - 1), CAST(id AS CHAR)),
        parent,
        level
FROM    (
        SELECT  id, parent, IF(ancestry, @cl := @cl + 1, level + @cl) AS level
        FROM    (
                SELECT  TRUE AS ancestry, _id AS id, parent, level
                FROM    (
                        SELECT  @r AS _id,
                                (
                                SELECT  @r := parent
                                FROM    t_hierarchy
                                WHERE   id = _id
                                ) AS parent,
                                @l := @l + 1 AS level
                        FROM    (
                                SELECT  @r := 1218,
                                        @l := 0,
                                        @cl := 0
                                ) vars,
                                t_hierarchy h
                        WHERE   @r <> 0
                        ORDER BY
                                level DESC
                        ) qi
                UNION ALL
                SELECT  FALSE, hi.id, parent, level
                FROM    (
                        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
                        FROM    (
                                SELECT  @start_with := 1218,
                                        @id := @start_with,
                                        @level := 0
                                ) vars, t_hierarchy
                        WHERE   @id IS NOT NULL
                        ) ho
                JOIN    t_hierarchy hi
                ON      hi.id = ho.id
                ) q
        ) q2

Multi Column Bootstrap Navbar

https://codepen.io/maheshbhagat/pen/wKGzMW

Oracle SQL Convert Seconds to TIme Format

SELECT TO_CHAR(TRUNC(x/3600),'FM9900')|| chr(58) || TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00')|| chr(58) || TO_CHAR(MOD(x,60),'FM00') FROM DUAL Note: where x is the time field such as departuretime

Camel Case in SQL

Below is a query to help make the Camel Case in the LAST_NAME. The first name has already been Camel Cased so I left it alone. SELECT concat(first_name,' ', concat(UPPER(LEFT(LAST_NAME,1)),'',LOWER(SUBSTRING(LAST_NAME,2,60)))) name FROM table;

Below is just a query of all the fields I needed then export the resultset into a file name email3.txt SELECT concat(first_name,' ', concat(UPPER(LEFT(LAST_NAME,1)),'',LOWER(SUBSTRING(LAST_NAME,2,60)))) name, email, 'Saigon South International School' school, job_title FROM cardinfo_cardinfo where email <> '' and is_leaving_teacher = 0 and job_title <> '' into outfile '/tmp/email3.txt';