caharkness.com

Identify Table Fragmentation and Create a Statement Plan for SQL Server

use MyDatabaseName
go

declare @Offenders table
(
    [Schema] varchar(100),
    [TableName] varchar(100),
    [IndexName] varchar(200),
    [PercentFragmented] float,
    [PageCount] int,
    [RecommendedAction] nvarchar(max)
);

insert @Offenders

select
    *,
    case when temp.PercentFragmented > 30
        then    ('alter index ' + temp.IndexName + ' on ' + temp.TableName + ' rebuild with (fillfactor = 90);')
        else    ('alter index ' + temp.IndexName + ' on ' + temp.TableName + ' reorganize;')
    end as RecommendedAction

from
(
    select
        s.name                              as 'Schema',
        t.name                              as 'TableName',
        i.name                              as 'IndexName',
        ddips.avg_fragmentation_in_percent  as 'PercentFragmented',
        ddips.page_count                    as 'PageCount'

    from
        sys.dm_db_index_physical_stats (db_id(), null, null, null, null) as ddips
        inner join sys.tables as t on t.object_id = ddips.object_id
        inner join sys.schemas as s on s.schema_id = t.schema_id
        inner join sys.indexes as i on
            i.object_id = ddips.object_id
        and i.index_id = ddips.index_id
    where
        ddips.database_id = db_id()
    and i.name is not null
    and ddips.avg_fragmentation_in_percent > 0
) temp

select 
    * 
from
    @Offenders
where
    [PercentFragmented] > 10
order by
    [PageCount] desc,
    [PercentFragmented] desc

Mounting Windows Shares in Debian Linux Made Easy

Here’s a quick and easy way to get Windows shares up and running in your Debian server:

#! /bin/sh
### BEGIN INIT INFO
# Provides:          cifs-share-x
# Required-Start:    $local_fs $network $named $time $syslog
# Required-Stop:     $local_fs $network $named $time $syslog
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Description:       Automatically mount Windows shares in Debian Linux
### END INIT INFO

#
#   To install this, run as root:
#   export SERVICE_NAME="cifs-share-x"; curl "https://gist.githubusercontent.com/caHarkness/22bb218d016663444b1a64023937530d/raw/cifs-share" > /etc/init.d/$SERVICE_NAME; chmod +x /etc/init.d/$SERVICE_NAME; sed -n "s/cifs-share-x/$SERVICE_NAME/gp" /etc/init.d/$SERVICE_NAME; ln -s /etc/init.d/$SERVICE_NAME /etc/rc2.d/S99$SERVICE_NAME; nano /etc/init.d/$SERVICE_NAME; sleep 1; systemctl daemon-reload
#

WINDOWS_USER="Windows User"
WINDOWS_PASSWORD="password"
WINDOWS_SHARE="//DESKTOP-NAME/Share"

LINUX_USER="root"
LINUX_GROUP="root"
LINUX_DIR="/mnt/Share"

mount_share () {
    mkdir -p "$LINUX_DIR"
    mount -t cifs -o username="$WINDOWS_USER",password="$WINDOWS_PASSWORD",uid=$LINUX_USER,gid=$LINUX_GROUP "$WINDOWS_SHARE" "$LINUX_DIR"
}

unmount_share () {
    umount "$LINUX_DIR"
}

list_share () {
    ls -lah "$LINUX_DIR"
}

edit_service () {
    SERVICE_NAME=$(basename $0)
    nano /etc/init.d/$SERVICE_NAME
    sleep 1
    systemctl daemon-reload
}

uninstall_service () {
    SERVICE_NAME=$(basename $0)
    rm /etc/init.d/$SERVICE_NAME
    rm /etc/rc2.d/S99$SERVICE_NAME
    echo "Service uninstalled."
}

PATH=/sbin:/usr/sbin:/bin:/usr/bin

. /lib/lsb/init-functions

case "$1" in
    start)
        mount_share
    ;;

    stop)
        unmount_share
    ;;

    restart)
        unmount_share
        mount_share
        ;;

    list)
        list_share
        ;;

    edit)
        edit_service
        ;;

    uninstall)
        uninstall_service
        ;;

    *)
        echo "Usage: $0 {start|stop|restart|list|edit|uninstall}"
        ;;
esac

This /etc/init.d script is available on my GitHub as a gist, you can see the most up-to-date version of it here. To make things even easier, you can run the following, single-liner to install this automatically:

export SERVICE_NAME="cifs-share-x"; curl "https://gist.githubusercontent.com/caHarkness/22bb218d016663444b1a64023937530d/raw/cifs-share" > /etc/init.d/$SERVICE_NAME; chmod +x /etc/init.d/$SERVICE_NAME; sed -n "s/cifs-share-x/$SERVICE_NAME/gp" /etc/init.d/$SERVICE_NAME; ln -s /etc/init.d/$SERVICE_NAME /etc/rc2.d/S99$SERVICE_NAME; nano /etc/init.d/$SERVICE_NAME; sleep 1; systemctl daemon-reload

(remember that this line may change, refer to the comment in the gist)

Doing so will download the most current version of the script, save it as cifs-share-x by default, and open it for editing in nano automatically. Be sure to configure the script correctly! Upon saving, systemctl daemon-reload is made aware of the new script. You can call service cifs-share-x start to start the service, or just reboot instead. If you want to do this for multiple Windows shares, just change cifs-share-x in the export SERVICE_NAME= statement and repeat the process!

This service will manage the mounting and unmounting of the share located at //DESKTOP-NAME/Share (forward slashes are used in Linux) to the /mnt/Share directory in Linux, and allowing for easy directory listing, editing the service script, and uninstalling it altogether!

Dumping MySQL (And Restoring It)

Although Microsoft SQL Server is far beyond any of the competition in regards to language flexibility and features, we are not “dumping” MySQL for another database server. No, I am going to walk you through how to back up your entire MySQL database and restore it. I do want to remind you that this is also compatible with MariaDB as MariaDB is a drop-in replacement for MySQL in complete, open source environments.

Run the following as root in a BASH shell:

cd && mysqldump --all-databases > "$(date +'%Y%m%d%H%M')-mysqldump.sql"

This will bring you to your root user’s home directory and dump the entire database to a uniquely named file with the .sql extension. This file contains all the MySQL statements, in chronological order, that recreates everything you just backed up. You can blow your current MySQL installation to bits and restore everything with this .sql file. To actually restore your new backup, run the following as root:

mysql < timestamp-mysqldump.sql

Where timestamp-mysqldump.sql is the true file name of the backup. The actual name will be prefixed with a unique timestamp, accurate down to the second, hence “uniquely named file” as stated earlier. It’s good practice to keep everything backed up, organized, and as often as possible. You’re welcome!

Make A New MySQL Admin User

This works for both MySQL and MariaDB, so if you’re sticking with Oracle or you prefer the open source alternative, this should not cause you any trouble. After you are done installing either, you should be able to get into the command line interface by running the following as root:

mysql

On either true MySQL systems or MySQL-compatible MariaDB systems, you should be accessing the new database as root. I believe it’s bad practice to continue using the root user to modify the database. From here, we want to create a new administrator. To do that, we can use the following statements, in order, at the prompt:

drop user 'www-data'@'localhost';

create user
    'www-data'@'localhost'
identified by
    'password';

grant all on *.* to 'www-data'@'localhost' with grant option;

The first statement will fail if you do not already have a user named www-data. These statements, when executed in order, removes the www-data user (if there is one) from MySQL and creates an entirely new user by the name of www-data, granting it all privileges, including the ability to grant permissions to other users.

If you prefer a one-liner, run this as root in your BASH shell:

echo "drop user 'www-data'@'localhost'; create user 'www-data'@'localhost' identified by 'password'; grant all on *.* to 'www-data'@'localhost' with grant option;" | mysql

Just don’t be an idiot and blindly run any of the statements above without fully understanding what you are doing. You’d be creating a www-user with the password “password.”

Sublime Text 3 Preferences Made Public

While I’m in a sharing mood, I figured I would make the preferences I use in Sublime Text 3 publicly available. I constantly find myself needing to reconfigure Sublime Text 3 across multiple Windows installations, and it’s easier to just copy & paste the contents of the file from a web browser than to manage the settings file wherever it may be.

{
    "color_scheme": "Packages/Color Scheme - Default/Monokai.sublime-color-scheme",
    "font_size": 11,
    "hot_exit": false,
    "ignored_packages":
    [
        "Vintage"
    ],
    "remember_open_files": false,
    "smart_indent": false,
    "tab_size": 4,
    "theme": "Adaptive.sublime-theme",
    "translate_tabs_to_spaces": true,
    "update_check": false
}

Open This Folder As A Project

For years, I have been using Sublime Text 3 and have found close to negative one reasons to change text editors any time soon. It’s fast, Python scriptable, and highly supported by many plugins. One of the things I have learned from holding a single user license to Sublime Text 3 is how to make opening directories within it a convenient task. At work, I probably right click and “Open with Sublime Text 3” both folders and directories nearly 50 times a day! Even many of the photos below show a directory open with Sublime Text 3, as if it were an open project or solution in Visual Studio. Let’s break down what we do to the Windows registry to make a custom “Open with” entry:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Folder\shell\Open with Sublime Text 3]
@="Open with Sublime Text 3"
"Icon"=hex(2):43,00,3a,00,5c,00,50,00,72,00,6f,00,67,00,72,00,61,00,6d,00,20,\
  00,46,00,69,00,6c,00,65,00,73,00,5c,00,53,00,75,00,62,00,6c,00,69,00,6d,00,\
  65,00,20,00,54,00,65,00,78,00,74,00,20,00,33,00,5c,00,73,00,75,00,62,00,6c,\
  00,69,00,6d,00,65,00,5f,00,74,00,65,00,78,00,74,00,2e,00,65,00,78,00,65,00,\
  2c,00,30,00,00,00

[HKEY_CLASSES_ROOT\Folder\shell\Open with Sublime Text 3\command]
@="C:\\Program Files\\Sublime Text 3\\sublime_text.exe %1"

[HKEY_CLASSES_ROOT\Directory\Background\shell\Open with Sublime Text 3]
@="Open with Sublime Text 3"
"Icon"=hex(2):43,00,3a,00,5c,00,50,00,72,00,6f,00,67,00,72,00,61,00,6d,00,20,\
  00,46,00,69,00,6c,00,65,00,73,00,5c,00,53,00,75,00,62,00,6c,00,69,00,6d,00,\
  65,00,20,00,54,00,65,00,78,00,74,00,20,00,33,00,5c,00,73,00,75,00,62,00,6c,\
  00,69,00,6d,00,65,00,5f,00,74,00,65,00,78,00,74,00,2e,00,65,00,78,00,65,00,\
  2c,00,30,00,00,00

[HKEY_CLASSES_ROOT\Directory\Background\shell\Open with Sublime Text 3\command]
@="C:\\Program Files\\Sublime Text 3\\sublime_text.exe %V"

In summary, if you were to merge this with your Windows registry, right clicking any directory object, or its background, will give you the option to open it with Sublime Text 3. There are four entries in total, two per filesystem object. If we consider that a directory icon and directory backgrounds are two separate things, we need an icon and a command for each. Here’s what it looks like in Regedit:

The location to the icon is stored as a REG_EXPAND_SZ type, but it translates to the absolute path to the executable of Sublime Text 3, trailed by a comma and the number zero. This tells the menu item to take icon of the executable. The command key just stores what we want Windows to execute.

Passing %1 to the command translates to the absolute path to the filesystem object. For the “Directory Background” filesystem object, %V is needed. That expands to the path of the container, whether it’s a folder, the desktop, or a drive. Paste what’s above in a .reg file and merge it to have the following context menu item everywhere you are in Windows!

If you’re a true tinkerer, you will understand this has true power. You can try all kinds of different file types and executables. The above example is only true for containers, but if you study the Windows registry, nearly every component of the Windows Explorer can have customized context menu items.

Forget Contemporary Artists

Clearly they are being obsolesced. If you don’t believe me, create an account over at https://app.wombo.art/ and start letting artificial intelligence do the work. You will soon understand why we no longer need young socialists demanding money for their lousy art; computer software does it for free.

Thirteen original pieces of art I consider to be mine. Surely, I didn’t paint these by hand using a tablet, but I do consider software to be my paint brush. If you ask me who makes the brush, the paint, and the canvas, I will most certainly answer “WOMBO.”

Feeling Stupid

If someone shows you that you’ve been doing something incorrectly and teaches you the correct way, what you feel isn’t “feeling stupid” it’s feeling smarter. You learned something new.

Privacy Policy

This post serves as the required privacy policy I am supposed to provide when uploading Android applications to the Google Play Store. If you are reading this privacy policy because you were curious about the application and how I handle the privacy of the users who use my applications, I don’t collect any data from my users.

Long List of Best Debian Packages and Commands

apt install gpm

Installs a text mode mouse driver for using the mouse in a system that doesn’t have a display or window manager.

apt install llmnrd

Installs a package that notifies Windows machines on the local network of your host name in Debian.

apt install npm
node --version
npm install -g n
export PATH="$PATH"
n latest && n prune

Installs the Node.js package manager and Node.js version manager, updating it to the latest version and getting rid of older versions.

npm install -g pm2
pm2 startup
pm2 start script.js
pm2 save

Installs the daemon process manager for keeping scripts, like script.js, running as a system service.

apt install default-jre-headless
java --version

Installs the latest default Java Runtime Environment and confirms its installation by echoing the version.

This list is growing, so changes will be made. Bookmark this page.