MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for May, 2020

Fedora Exlixir Install

without comments

Having played around with Erlang some twelve years ago, I felt it was time to experiment with the Elixir programming language. The install on Fedora was straightforward with Dandified YUM. Naturally, you need to be the root user or a user found in the sudoer‘s list:

sudo dnf install -y elixir

Display detailed console log →

 

The installation said to add it to the $PATH variable but on Fedora 30, a symbolic link of elixir is installed in the /usr/bin directory that points to /usr/share/elixir/1.9.0/bin/elixir shell script. Next, a version check, like this:

elixir --version

it returned

Erlang/OTP 21 [erts-10.3.5.11] [source] [64-bit] [smp:1:1] [ds:1:1:10] [async-threads:1] [hipe]
 
Elixir 1.9.0 (compiled with Erlang/OTP 21)

Next, I created a little elixir test program, naturally after I glanced through the documentation for a Hello World program, like this hello.exs file:

IO.puts "Hello, Elixir World!"

Then, I ran it as stand alone file with only read and write, read, and read privileges:

elixir hello.exs

It returns:

Hello, Elixir World!

Next, I tried to read the file from the file system in the iex interactive shell. I thought the example on the website was odd because it gave the impression that you were to call iex recursively but I discovered that’s not the case. You can only run it from the OS shell, and the file must have read, write, execute privileges for whomever calls it. Then, this syntax works from the Linux CLI interfaace:

iex -S hello.exs

Then, you exit the shell by issuing a ^Z (CTRL+Z). If you try ^C, you’ll need to follow that with ^D, which I found in some notes. The ^Z is the normal kill signal for the current process, which appears the proper way to exit the iex interactive shell. Overall, the interactive shell is virtually useless except to validate small syntax examples.

Written by maclochlainn

May 20th, 2020 at 11:47 pm

Posted in Elixir,Erlang,Linux

Tagged with

Create Student User

without comments

It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.

Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

DECLARE
 /* Control variable. */
 container  BOOLEAN := FALSE;
 
 /* Weakly structured system reference cursor. */
 container_sql  SYS_REFCURSOR;
 
 /* Constant required for pre-container databases to avoid a
    a compile time error. */
 sql_statement  CONSTANT VARCHAR2(50) :=
                  'SELECT cdb FROM v$database WHERE cdb = ''YES''';
BEGIN
 /* Check if the current user is the superuser. */
 FOR i IN (SELECT USER FROM dual) LOOP
 
   /* Perform tasks as superuser. */
   IF i.USER = 'SYSTEM' THEN
     /* Check for a container-enabled column, which enables this to
        work in both pre-container Oracle databases, like 10g and 11g. */
     FOR j IN (SELECT DISTINCT column_name
               FROM   dba_tab_columns
               WHERE  column_name = 'CDB') LOOP
 
       /* Check for a container database, set control variable
          and exit when found. */
       OPEN container_sql FOR sql_statement;
       LOOP
         container := TRUE;
         EXIT WHEN container_sql%FOUND;
       END LOOP;
     END LOOP;
 
      /* Conditionally drop existing user and role. */   
      IF container THEN
        /* Conditionally drop a container user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'C##STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER c##student CASCADE';
        END LOOP;
 
        /* Conditionally rop the container c##studentrole role. */
        FOR j IN (SELECT ROLE
                  FROM   dba_roles
                  WHERE  ROLE = 'C##STUDENTROLE') LOOP
          EXECUTE IMMEDIATE 'DROP ROLE c##studentrole';
        END LOOP;
 
        /* Create a container user with 200 MB of space. */
        EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Create a container role. */
        EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL';
 
        /* Grant privileges to a container user. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO c##studentrole';
 
        /* Grant role to user. */
        EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student';
      ELSE
        /* Conditonally drop the non-container database user. */
        FOR j IN (SELECT username
                  FROM   dba_users
                  WHERE  username = 'STUDENT') LOOP
          EXECUTE IMMEDIATE 'DROP USER student CASCADE';
        END LOOP;
 
        /* Create the student database. */
        EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10)
        ||                'IDENTIFIED BY student'||CHR(10)
        ||                'DEFAULT TABLESPACE users'||CHR(10)
        ||                'QUOTA 200M ON users'||CHR(10)
        ||                'TEMPORARY TABLESPACE temp';
 
        /* Grant necessary privileges to the student database. */
        EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10)
        ||                'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10)
        ||                'CREATE SESSION, CREATE TABLE,'||CHR(10)
        ||                'CREATE TRIGGER, CREATE TYPE,'||CHR(10)
        ||                'CREATE VIEW TO student';
       END IF;
    ELSE
      /* Print an message that the user lacks privilegs. */
      dbms_output.put_line('You must be the SYSTEM user to drop and create a user.');
    END IF;
  END LOOP;
END;
/

As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.

Written by maclochlainn

May 4th, 2020 at 8:01 pm

Posted in DBA,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,pl/sql,sql

Tagged with ,

MySQL Linux to Windows

without comments

My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.

The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:

ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30

It would return something like:

192.168.147.198

After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy Secure Copy) utility. I recommend you click on the MSI (Microsoft Installer Package) file, and install it on your Windows machine. As a rule, you should accept the default location, which is C:\Program Files\PuTTy.

While you could alter your system-level %PATH% environment variable after you install the software, I recommend you only include it in the %PATH% within the scope of a Command (cmd) shell. Navigate to your Windows Start and enter cmd in the search field. It should launch the Command Prompt terminal, which is a terminal emulator.

In the terminal editor, type the following case sensitive command to add the PuTTy directory to your path (yes, it’s case sensitive):

SET PATH=%PATH%;%ProgramFiles%\PuTTy

Now, you can securely copy the files and directory structure from Linux to Windows with the following command (where you replace the traditional server name with the dynamically assigned IP address). You should also be in the target directory where you want the files and directories copied:

C:\Data\cit225>pscp -r student@192.168.147.198:/home/student/Data/cit225/mysql .

After entering the password for the student on the Linux VM, you should see the following copy over:

Raiders2.png              | 99 kB |  99.5 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings3.png       | 119 kB | 119.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter4.png          | 103 kB | 103.9 kB/s | ETA: 00:00:00 | 100%
Raiders1.png              | 92 kB |  92.4 kB/s | ETA: 00:00:00 | 100%
Raiders3.png              | 123 kB | 123.9 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings2.png       | 111 kB | 111.7 kB/s | ETA: 00:00:00 | 100%
LordOfTheRings1.png       | 103 kB | 104.0 kB/s | ETA: 00:00:00 | 100%
HarryPotter2.png          | 118 kB | 118.7 kB/s | ETA: 00:00:00 | 100%
HarryPotter7.png          | 150 kB | 150.2 kB/s | ETA: 00:00:00 | 100%
HarryPotter3.png          | 106 kB | 106.1 kB/s | ETA: 00:00:00 | 100%
HarryPotter5.png          | 82 kB |  82.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter6.png          | 129 kB | 129.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter1.png          | 118 kB | 118.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter8.png          | 150 kB | 150.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter8.txt          | 8 kB |   8.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter3.txt          | 5 kB |   5.8 kB/s | ETA: 00:00:00 | 100%
HarryPotter5.txt          | 7 kB |   7.9 kB/s | ETA: 00:00:00 | 100%
HarryPotter1.txt          | 6 kB |   6.6 kB/s | ETA: 00:00:00 | 100%
HarryPotter2.txt          | 7 kB |   7.8 kB/s | ETA: 00:00:00 | 100%
Raiders3.txt              | 5 kB |   5.6 kB/s | ETA: 00:00:00 | 100%
HarryPotter4.txt          | 7 kB |   7.5 kB/s | ETA: 00:00:00 | 100%
HarryPotter7.txt          | 5 kB |   5.4 kB/s | ETA: 00:00:00 | 100%
HarryPotter6.txt          | 7 kB |   7.4 kB/s | ETA: 00:00:00 | 100%
LOTRFellowship.txt        | 4 kB |   5.0 kB/s | ETA: 00:00:00 | 100%
apply_store_base.sql      | 1 kB |   1.6 kB/s | ETA: 00:00:00 | 100%
query_membership.sql      | 0 kB |   0.3 kB/s | ETA: 00:00:00 | 100%
apply_mysql_lab1.sql      | 1 kB |   1.9 kB/s | ETA: 00:00:00 | 100%
configure_mysql_web.sql   | 37 kB |  37.1 kB/s | ETA: 00:00:00 | 100%
seed_mysql_store_ri2.sql  | 58 kB |  58.5 kB/s | ETA: 00:00:00 | 100%
cleanup_mysql_store.sql   | 5 kB |   5.4 kB/s | ETA: 00:00:00 | 100%
create_mysql_store_ri2.sq | 21 kB |  21.1 kB/s | ETA: 00:00:00 | 100%

My students will need to repeat this step to transfer all of the sample PHP files that demonstrate web application patterns. They also need to inspect individual files to ensure any path referencing commands are manually converted to their new Windows equivalent.

They can move the physical files as the root superuser with the following pscp command provide you haven’t stored the files somewhere other than the default location:

C:\Data\temp>pscp -r root@192.168.147.198:/var/lib/mysql .

As always, I hope this helps those trying to sort things out.

Written by maclochlainn

May 3rd, 2020 at 11:58 am

Posted in Fedora,Linux,MySQL,MySQL 8,Oracle,Oracle 18c,PuTTy,Unix,Windows 10

Tagged with , , , ,