Customizing a DZ60 Keyboard using QMK

This is my 60% keyboard built on the DZ60 Rev 2.0 printed circuit board.

These are the steps I took to configure it for my use. Please note that I had configured this keyboard using Linux; Windows and Mac alternatives exist, but they are outside the scope of this article.

1. The DZ60 can be customized using Quantum Mechanical Keyboard Firmware. I downloaded a copy of it here: https://github.com/qmk/qmk_firmware/zipball/master.

2. I then unzipped the files to ~/software/qmk/

3. Run this shell script to ensure all dependencies are installed:

sudo ~/software/qmk/util/install_dependencies.sh

4. Create a folder that will hold my custom keymapping file: ~/software/qmk/keyboards/dz60/keymaps/cpc1/keymap.c

I am utilizing 4 layers. Generally speaking:

Layer 0 is the main layer containing QWERTY and other such keys that I will use most often.

Layer 1 is activated by holding the key located beneath B. This layer contains F1-F12, basic navigation (ESDF for cursor movement, page up/down), volume control, and number pad (at right hand home row region).

Layer 2 is activated by holding the key located beneath NM.. This layer contains mouse navigation keys (ESDF for mouse movement, XCV as the three mouse buttons).

Layer 3 is activated by holding the key located beneath ENTER and to the right of UP. This layer contains keyboard hardware control keys (reset, LED controls).

This is my full keymap.c file:

#include "dz60.h"
#define __      KC_TRNS
#define ___     KC_TRNS
#define _______ KC_TRNS
#define HY      KC_HYPR
#define LST     KC_LSFT
#define SP_MO1  LT(1, KC_SPC)
#define BS_MO2  LT(2, KC_BSPC)
#define PN_MO3  LT(3, KC_PSCR)
const uint16_t PROGMEM keymaps[][MATRIX_ROWS][MATRIX_COLS] = {

	// Layer 0 - Standard keyboard
	KEYMAP_2_SHIFTS(
		KC_GESC, KC_1   , KC_2   , KC_3   , KC_4   , KC_5   , KC_6   , KC_7   , KC_8   , KC_9   , KC_0   , KC_MINS, KC_EQL , KC_DEL , KC_INS, 
		KC_TAB , KC_Q   , KC_W   , KC_E   , KC_R   , KC_T   , KC_Y   , KC_U   , KC_I   , KC_O   , KC_P   , KC_LBRC, KC_RBRC, KC_BSLS, 
		KC_ESC , KC_A   , KC_S   , KC_D   , KC_F   , KC_G   , KC_H   , KC_J   , KC_K   , KC_L   , KC_SCLN, KC_QUOT, KC_ENT , 
		HY, LST, KC_Z   , KC_X   , KC_C   , KC_V   , KC_B   , KC_N   , KC_M   , KC_COMM, KC_DOT , KC_SLSH, KC_RSFT, KC_UP  , PN_MO3 ,
		KC_LCTL, KC_LGUI, KC_LALT, KC_SPC , SP_MO1 , BS_MO2 , KC_HOME, KC_END , KC_LEFT, KC_DOWN, KC_RGHT),

	// Layer 1 - Cursor Navigation, Volume, Number Pad
	KEYMAP_2_SHIFTS(
		_______, KC_F1  , KC_F2  , KC_F3  , KC_F4  , KC_F5  , KC_F6  , KC_F7  , KC_F8  , KC_F9  , KC_F10 , KC_F11 , KC_F12 , _______, _______, 
		KC_TAB , KC_HOME, KC_PGUP, KC_UP  , KC_PGDN, _______, _______, KC_7   , KC_8   , KC_9   , KC_PPLS, KC_PMNS, _______, _______, 
		_______, KC_END , KC_LEFT, KC_DOWN, KC_RGHT, _______, _______, KC_4   , KC_5   , KC_6   , KC_PAST, KC_PSLS, KC_PENT, 
		HY, LST, KC_MUTE, KC_VOLD, KC_VOLU, _______, _______, _______, KC_1   , KC_2   , KC_3   , _______, _______, _______, _______, 
		KC_LCTL, KC_LGUI, KC_LALT, _______, _______, KC_0   , KC_PDOT, _______, _______, _______, _______),

	// Layer 2 - Mouse Navigation
	KEYMAP_2_SHIFTS(
		_______, _______, KC_ACL0, KC_ACL1, KC_ACL2, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		KC_TAB , _______, _______, KC_MS_U, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, KC_MS_L, KC_MS_D, KC_MS_R, _______, _______, _______, _______, _______, _______, _______, _______, 
		HY, LST, _______, KC_BTN1, KC_BTN3, KC_BTN2, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		KC_LCTL, KC_LGUI, KC_LALT, _______, _______, _______, _______, _______, _______, _______, _______),


	// Layer 3 - Keyboard Hardware Controls
	KEYMAP_2_SHIFTS(
		RESET  , _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, RGB_TOG, RGB_MOD, RGB_HUI, RGB_HUD, RGB_SAI, RGB_SAD, RGB_VAI, RGB_VAD, _______, _______, _______, _______, 
		__, ___, BL_TOGG, BL_DEC , BL_INC , _______, _______, _______, _______, _______, _______, _______, _______, _______, _______, 
		_______, _______, _______, _______, _______, _______, _______, _______, _______, _______, _______),
};

5. Open a terminal window and navigate to ~/software/qmk/

6a. Run this command if you just want to create a hex file without actually flashing the keyboard:

sudo make dz60:cpc1

6b. Or, hold spacebar and B on the keyboard while plugging it into your computer; this will start the keyboard in bootloader mode. Now I can run the following command to compile the hex and then flash it onto the keyboard:

sudo make dz60:cpc1:dfu

6c. Or, if the keyboard is already plugged in and in use, run this command (same as 5b above). When the hex is built, it will prompt you to reset the keyboard so that the hex can be flashed onto the keyboard. In my case, my reset button is in Layer 3, as noted earlier.

sudo make dz60:cpc1:dfu

Additional info:

Full Documentation: https://docs.qmk.fm
GitHub of QMK Project: https://github.com/qmk/qmk_firmware/

Scheduling Jobs with Oracle 9i DBMS_JOB Package

Creating a new job that runs every day at 4:00am:

declare 
	l_job number; 
begin 
	dbms_job.submit(
		l_job, -- OUT; the job ID number that will be generated
		'schema_name.procedure_name;', -- IN; the name of the job you wish to run, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated
	); 
end; 

Regarding the interval, here are some examples:

-- Every 15 minutes starting from the minute/second of the previous execution
'sysdate+1/24/4'

-- Every hour, same minute/second as the previous execution
'sysdate+1/24'

-- Every hour, at the 15-minute mark
'trunc(sysdate, 'hh')+1/24+15/24/60'

-- Every hour, limited to between 9:00am and 5:00pm
'case when to_char(sysdate, ''hh24mi'') between ''0900'' and ''1700'' then sysdate+1/24 else null end'

-- Every 3 days, same hour/minute/second as the previous execution
'sysdate+3'

-- Every day at 5:00am
'trunc(sysdate)+1+5/24'

-- Every Monday at 5:00am
'next_day(trunc(sysdate), ''monday'')+5/24'

To see a list of existing jobs:

select * from dba_jobs;

Altering all properties of an existing job:

begin
	dbms_job.change(
		123, -- IN; job ID number
		'schema_name.procedure_name;', -- IN; the name of the job, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run after this change
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated
	);
end;

Altering just the “what”:

begin
	dbms_job.what(
		123, -- IN; job ID number
		'schema_name.procedure_name;' -- IN; the name of the job, aka. "what"
	);
end;

These procedures allows you to make changes in a manner very similar to dbms_job.what illustrated above:

	.next_date
	.interval

Force a job to run:

begin
	dbms_job.run(123);
	-- ... where the "123" is the job's ID number
end;

Removing an existing job:

begin
	dbms_job.remove(123);
	-- ... where the "123" is the job's ID number
end;

Ubuntu Boot Partition Full

The other day when I attempted to run some regular updates for my Linux box (running Ubuntu 14.04 LTS), I encountered the message that the update could no proceed because the boot partition was full. Here are the steps I took to clear unneeded files from the boot partition.

1. First, I found out that I am running kernel 3.19.0-65 with this command below.

me@computer:~$  uname -r

3.19.0-65-generic

2. Next, list what kernel images are present in my root partition.

me@computer:~$ dpkg --list | grep linux-image

ii  linux-image-3.19.0-61-generic
3.19.0-61.69~14.04.1                                amd64        Linux
kernel image for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-3.19.0-65-generic
3.19.0-65.73~14.04.1                                amd64        Linux
kernel image for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-extra-3.19.0-61-generic
3.19.0-61.69~14.04.1                                amd64        Linux
kernel extra modules for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-extra-3.19.0-65-generic
3.19.0-65.73~14.04.1                                amd64        Linux
kernel extra modules for version 3.19.0 on 64 bit x86 SMP
ii  linux-image-generic-lts-vivid                         3.19.0.65.47
                                       amd64        Generic Linux
kernel image

3. The above was actually a truncated example; the actual list was much longer. In summary, I had many older kernel images that I do not need anymore. In the example shown above, I decided that since I am running 3.19.0-65, I will not need the -61 image anymore. Below is the command I used to clear out -61; I ran similar commands for all the kernel images with even lower versions as well to clear up space.

me@computer:~$ sudo apt-get purge linux-image-3.19.0-61-generic

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  linux-image-3.19.0-61-generic*
0 upgraded, 0 newly installed, 1 to remove and 8 not upgraded.
After this operation, 47.8 MB disk space will be freed.
Do you want to continue? [Y/n] Y
...

4. Finally, check out the contents of the /boot/ directory. If you see any orphaned files from older kernels, consider removing them to save space.

Bonus: Useful related commands

sudo apt-get autoremove
sudo apt-get clean
sudo apt-get update
sudo apt-get dist-upgrade

“autoremove” gets rid of packages that were automatically installed previously, but are no longer needed.

“cleans” empties /var/cache/apt/archives/ and /var/cache/apt/archives/partial/.

“update” updates apt-get’s list of available software packages.

“dist-upgrade” is best explained via its man page entry:


dist-upgrade in addition to performing the function of upgrade, also intelligently handles changing dependencies with new versions of packages; apt-get has a “smart” conflict resolution system, and it will attempt to upgrade the most important packages at the expense of less important ones if necessary. The dist-upgrade command may therefore remove some packages.

Backing up Android Phone to Linux

My environment: Smart phone running Android version 6.0.1, and computer running Ubuntu 16.04 LTS

I have a few key folders on my phone that I would like to back up regularly. For simplicity sake, let us say that I am just dealing with the “Camera” folder which holds all my latest photographs. The following commands makes use of gvfs-commands (Gnome virtual file system) to copy/move files, assuming the phone has been connected via USB to the computer, and the phone has been unlocked.

The bash script I currently run looks like this:

#!/bin/bash

for D in /run/user/1000/gvfs/*
do
	gvfs-copy ${D}/Phone/DCIM/Camera/*.* /picfolder/ 
	gvfs-move ${D}/Phone/DCIM/Camera/*.* /tmpfolder/
	gvfs-move /tmpfolder/*.* ${D}/Phone/Pictures/1-ToSort/ 

	echo "${D} done" 
done

As you may have noticed, I took the safe approach of copying the photos to my computer, then moving the same photos again to a temporary directory on my computer, and finally moving the temporary files back onto the phone in an archive folder. The final steps effectively represents a move from the Camera folder on the phone to the archive folder on the phone; this is because gvfs-move does not support the move of files within the same device at this time.

Combining Oracle trigger and DBMS_UTILITY.FORMAT_CALL_STACK to track transactions

I recently encountered a situation where a small number of records in a large Oracle table contain wrong values, and naturally I need to find out exactly which program is causing this problem. I decided to use Oracle triggers to do this job, making use of the built-in DBMS_UTILITY.FORMAT_CALL_STACK function as the main ingredient.

create or replace trigger trg_stack_trace_logger
before insert or update on inventory_table
for each row
begin
	if (:old.expiration_date <> :new.expiration_date) then
		insert into stack_trace_log
		values(
		'User=' || user || '; ' ||
		'Date=' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') || '; ' ||
		'Old Value=' || :old.expiration_date || '; ' ||
		'New Value=' || :new.expiration_date || '; ' ||
		DBMS_UTILITY.FORMAT_CALL_STACK
		);
	end if;
end;

As you can see, the output contains both old/new values of the transaction as well as some metadata (ie. the stack trace) of the transaction itself. The output is inserted into a table called “stack_trace_log”, which, for simplicity sake, is just a table consisted of a single varchar2 field; if you will use this type of tracking over a longer period, it is probably best to track username, date, etc. in their own fields for better reporting capabilities.

select * from stack_trace_log;

LOGGED_INFO
---------------
User=JOE; 
Date=11/24/2015 08:33:39; 
Old Value=2015-11-15-00.00.00; 
New Value=2030-11-15-08.33.39; 
----- PL/SQL Call Stack -----    
object handle line number object name
0x91626018    1           anonymous block
0x8dcb7b30    3           ERP.TRG_STACK_TRACE_LOGGER
0x9657ec50    354         package body ERP.INVENTORY_API
0x9657ec50    1483        package body ERP.INVENTORY_API
0x8c7d2758    4254        package body ERP.INVENTORY_API
0x969315a0    650         package body ERP.RECEIVING_API
0x969315a0    3524        package body ERP.RECEIVING_API
0x969315a0    2861        package body ERP.RECEIVING_API
0x91411208    342         package body ERP.BARCODE_ARRIVAL_API
0x8bd5cca8    1           anonymous block
0x82871f48    1120        package body SYS.DBMS_SYS_SQL
0x82886f48    323         package body SYS.DBMS_SQL
0x99f8e6c0    138         package body ERP.BARCODE_INTERFACE_API
0x93980f88    1           anonymous block

1 rows selected

Insert Microsoft Word content into Oracle database

The following sample is over-simplified, but it shows how we can iterate through tables (and their columns and rows) to extract text, and in turn inserting them into an Oracle table.

Option Explicit

Public Sub InsertIntoOracle()
    Dim cn As ADODB.Connection
    Dim source, user, password, str As String
    Dim aTable As Table
    Dim tbl, row, col As Long

    source = "database"
    user = "scott"
    password = "tiger"
    tbl = 0

    Set cn = New ADODB.Connection
    cn.Open "Provider = OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
    cn.BeginTrans
    For Each aTable In ActiveDocument.Tables
        tbl = tbl + 1
        For row = 1 To aTable.Rows.Count
            For col = 1 To aTable.Columns.Count
                str = Trim(aTable.Cell(row, col).Range.Text)
                If (Len(str) > 2) Then
                    cn.Execute "insert into document_content values('" & tbl & "-" & row & "-" & col & ": " & str & "')"
                End If
            Next
        Next
    Next

    If cn.Errors.Count = 0 Then
        cn.CommitTrans
    Else
        cn.RollbackTrans
    End If

    cn.Close
End Sub

Obfuscate sensitive data in Oracle

If business needs requires you to store sensitive data such as social security numbers, bank routing/account numbers, and so on, you should ensure the data is stored in a safe way. Below are a set of two simple functions to encrypt/obfuscate such data to get your started.

To encrypt a varchar2 string with a specific encryption phrase (or “key”):

create or replace function your_schema.encrypt(clear_varchar_ varchar2, key_ varchar2) return varchar2 
is
	v_clear_varchar varchar2(2000);
	v_enc_raw		raw(2000);
	v_enc_varchar	varchar2(2000);
begin
	if (mod(length(clear_varchar_), 8) != 0) then
		v_clear_varchar := rpad(clear_varchar_, length(clear_varchar_) + 8 - mod(length(clear_varchar_), 8), chr(0));
	else
		v_clear_varchar := clear_varchar_;
	end if;
	dbms_obfuscation_toolkit.desencrypt(input => utl_raw.cast_to_raw(rpad(v_clear_varchar, 64, ' ')),
		key => utl_raw.cast_to_raw(key_), 
		encrypted_data => v_enc_raw);
		v_enc_varchar := utl_raw.cast_to_varchar2(v_enc_raw);
	return v_enc_varchar;
end;

The following function decrypts; you must use the same key that was used to encrypt it.

create or replace function your_schema.decrypt(enc_varchar_ varchar2, key_ varchar2) return varchar2 
is
	v_tmp_raw    	 raw(2048);
	v_clear_varchar	varchar2(4000);
begin
	dbms_obfuscation_toolkit.desdecrypt(input => utl_raw.cast_to_raw(enc_varchar_),
		key =>  utl_raw.cast_to_raw(key_), 
		decrypted_data => v_tmp_raw);
	v_clear_varchar := replace(trim(utl_raw.cast_to_varchar2(v_tmp_raw)),chr(0),'');
	return v_clear_varchar;
end;

Here is an example usage: The following SQL statement inserts an obfuscated password into a table that stores user data.

insert into your_schema.user_accounts (username, password)
values(
	'scott', 
	your_schema.encrypt('tiger', '_seCret!keY:3')
);

And below is how you would retrieve and decrypt the password.

select your_schema.decrypt(password, '_seCret!keY:3') from your_schema.user_accounts where username='scott';

Security is a serious matter and it warrants extensive research. This article merely offers the awareness that sensitive data should not be stored in clear text, and hopefully provides a good starting point.

Oracle role hierarchy report

The SQL below can be used to provide a list of roles that inherits the CONNECT role, and with the use of START WITH clause, it will also iterate through all the roles beneath those roles, thus providing a hierarchy report.

select level, drp.granted_role, rpad('-',6*level,'-')||drp.grantee as grantee,
case
	when u.username is not null and account_status='OPEN' then 'Ua'
	when u.username is not null and account_status<>'OPEN' then 'Ux'
	when r.role is not null then 'R'
end as grantee_type
from dba_role_privs drp, dba_users u, dba_roles r
where drp.grantee=u.username(+) and drp.grantee=r.role(+)
start with drp.granted_role='CONNECT'
connect by prior drp.grantee=drp.granted_role;

Below is a sample of what may be returned.

LEVEL GRANTED_ROLE GRANTEE GRANTEE_TYPE
1 CONNECT ——ADMINS R
2 ADMINS ————ANDER Ua
2 ADMINS ————MARY Ux
2 ADMINS ————ZOE Ua
1 CONNECT ——EMPLOYEES R
2 EMPLOYEES ————ACCOUNTING R
3 ACCOUNTING ——————DEBBIE Ua
2 EMPLOYEES ————OPERATIONS R
3 OPERATIONS ——————PETER Ua
3 OPERATIONS ——————WILLEM Ua

Running VBScript in 32-bit mode on a 64-bit Windows machine

I have a legacy VBScript program that has been recently been migrated to a new 64-bit Windows server by a system administrator due to a policy set by a higher power. By default, VBScript executed in 64-bit Windows automatically runs as a 64-bit program. Because I have reasons to have this program run in 32-bit mode, I modified my batch file to execute my program in a different way.

This is the BEFORE picture. When this batch file is run in 64-bit Windows, it will execute in 64-bit mode.

time /t
cscript ThisIsMyVbscriptProgram.vbs
time /t

Below is the AFTER picture. Note that I am passing my script into a new command prompt environment in the Windows SYSWOW64 folder; this new environment is strictly in 32-bit mode.

time /t
%WINDIR%SYSWOW64cmd.exe /c cscript ThisIsMyVbscriptProgram.vbs
time /t

As a side note: WOW stands for “Windows on Windows”.

Using DBA_SOURCE to query package source code

Recently I had the need to find out exactly what existing PL/SQL logic was touching a certain field that seems to update by itself, wiping out important data. Below was the quick query I put together, using the DBA_SOURCE view, to hunt down the culprit.

select name as package_name, line, text
from dba_source
where owner='MY_SCHEMA'
and type='PACKAGE BODY'
and (
  upper(text) like '%MY_TABLE_NAME%FIELD_NAME%'
  or
  upper(text) like '%FIELD_NAME%MY_TABLE_NAME%'
)
order by name, line;

Note that I could have searched for “and upper(text) like ‘%UPDATE%MY_TABLE_NAME%FIELD_NAME%'” rather than having two conditions, but I wanted to err on the side of caution, thus my wish to pull more data out to be safe.