MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Windows 10 Laptops

without comments

Teaching Oracle technology always has challenges. They’re generally large challenges because we ask students to run 4 GB Linux VM with Oracle Database 11g XE pre-configured for them. A number of the student computers aren’t up to the task of running the virtualization.

Installing VMware Workstation or Player and a 64-bit Linux operating system is the easiest way to discover a laptop that advertises itself as 64-bit when it truly isn’t. Most of the computers raise an exception that says they’re unable to run hyperthreading, and naturally two BIOS settings are disabled by the manufacturers.

As a result, I get a lot of questions from students on computers. Some of the questions are simple and driven by a desire to maximize their investment. Other questions aren’t quite as simple. The harder questions are typically driven by a need to accomplish something they can’t do with their computer.

I can’t help but feel too many students see laptops as commodities, like televisions. They purchase their laptops thinking they’ve bought the right computer because it provides features like a touch screen. Unfortunately, they don’t notice things like the operating system because many of them purchase computers that run the Microsoft Windows.

They believe Microsoft Windows is simply a single operating system. They don’t know that there are seven versions of Windows 10 with different features. More importantly, they don’t know there are two key versions of Windows 10 when they purchase a laptop – the Windows 10 Home and Windows 10 Pro. The student seem to never find a simple Windows 10 Buyers Guide.

Windows 10 Home Edition is designed for end-user computing that includes using application software, whereas Windows 10 Pro Edition is designed for computing that runs both application and server software. The choice of one over the other determines what you can or can’t do with your Windows software.

Changing between Windows 10 Home and Windows 10 Pro comes at a cost to most consumers. That’s because they purchase machines with OEM versions of the Windows operating system. Vendors provide OEM versions of Windows 10 because they customize boards and chip-sets; and sometimes they purchase and install chips that fail to meet manufacturing standards. In these cases, the OEM Windows 10 comes with modifications and custom drivers. Moving from an OEM Windows 10 Home to a Windows 10 Pro can be very complicated.

Also, it’s all too common for OEM Windows 10 to disable 64-bit operations while advertising their product as 64-bit. The reasons for this can be complex and hard to identify sometimes. When a manufacturer purchases defective CPUs, they tend to disable some of the chips features. Manufacturers often disable 64-bit features to work around a defective CPU, one or more chip-sets, or their own customizations to the Windows 10 operating system.

I wrote all this to help focus purchases for those who want to run an Oracle Database on a Windows 10 operating system. You have two choices. One uses the native Windows 10 Pro operating system to run Oracle Database 11g XE natively, and the other uses Windows 10 to run VMware or Virtual Box to support a Linux operating system and Oracle Database 11g XE instance.

Best of luck, and always check the laptop specifications. As a rule, don’t buy Windows 10 Home machines if you want to run an Oracle Database.

Written by maclochlainn

January 15th, 2018 at 9:27 pm

Apple iTunes Bug

without comments

Over the years, this bug never gets fixed. I know it must irritate more people than just me. Unlike those who live in urban communities with great download speeds and relatively inexpensive Internet providers, I live in an area held hostage by expensive CableOne Internet service. Net neutrality won’t fix my issue.

The Apple iTunes bug occurs after you download a movie and the cloud symbol disappears. At first, it may appear as designed, with only one image displayed, like Papillon and Passengers is shown below:

From time to time, Apple iTunes gets confused (polite speak for an intermittent bug) and creates a new iCloud image side-by-side with the downloaded version of the movie. You can see that with the image of The Adventures of Robin Hood:

It annoys me and it takes time to fix. The present solution is to delete the downloaded file image and then re-download it if you must have a local copy (the tedious lives of those outside of large metropolitan areas). Naturally, for those of us outside of large metropolitan areas with monthly restrictions on the size of downloads, Apple’s solution is not a viable workaround. Unfortunately, Apple appears disinclined to figure out what causes the problem or fixing it in the existing iTunes code base. When I called Apple’s iTune support it took a third level engineer to agree that the problem even exists. 😉

Written by maclochlainn

December 22nd, 2017 at 10:11 pm

Posted in Apple,Uncategorized

Oracle 12c and PHP

without comments

This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.

CDB Connection:

This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("video", "video", "localhost/orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

PDB Connection:

This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("video", "video", "localhost/videodb");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.

You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:

sqlplus video@video/video

Hope this helps those trying to sort it out.

Written by maclochlainn

December 10th, 2017 at 12:42 pm

Posted in Oracle,Oracle 12c,PHP

Type Dependency Tree

without comments

While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.

This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):

CREATE OR REPLACE TYPE base_t AS OBJECT
( base_id  NUMBER ) NOT FINAL;
/
 
CREATE OR REPLACE TYPE person_t UNDER base_t
( first_name   VARCHAR2(20)
, middle_name  VARCHAR2(20)
, last_name    VARCHAR2(20)) NOT FINAL;
/
 
CREATE OR REPLACE TYPE driver_t UNDER person_t
( license VARCHAR2(20));
/

Here’s a query to show the hierarchy of object types and attributes by object-level in the hierarchy:

COL type_name  FORMAT A20  HEADING TYPE_NAME
COL attr_no    FORMAT 999  HEADING ATTR_NO
COL attr_name  FORMAT A20  HEADING ATTR_NAME
COL TYPE       FORMAT A12  HEADING TYPE
SELECT   DISTINCT
         LPAD(' ',2*(LEVEL-1)) || ut.type_name AS type_name
,        uta.attr_no
,        uta.attr_name
,        CASE
           WHEN uta.attr_type_name = 'NUMBER' THEN
             uta.attr_type_name
           WHEN uta.attr_type_name = 'VARCHAR2' THEN
             uta.attr_type_name || '(' || uta.LENGTH || ')'
         END AS TYPE
FROM     user_types ut
,        user_type_attrs uta
WHERE    ut.typecode = 'OBJECT'
AND      ut.type_name = uta.type_name
AND      uta.inherited = 'NO'
START
WITH     ut.type_name = 'BASE_T'
CONNECT
BY PRIOR ut.type_name = ut.supertype_name
ORDER BY uta.attr_no;

It should return the following:

TYPE_NAME	     ATTR_NO ATTR_NAME		  TYPE
-------------------- ------- -------------------- ------------
BASE_T			   1 BASE_ID		  NUMBER
  PERSON_T		   2 FIRST_NAME 	  VARCHAR2(20)
  PERSON_T		   3 MIDDLE_NAME	  VARCHAR2(20)
  PERSON_T		   4 LAST_NAME		  VARCHAR2(20)
    DRIVER_T		   5 LICENSE		  VARCHAR2(20)

As always, I hope this helps those looking to discover an Oracle object type hierarchy without examining each object type in turn.

Written by maclochlainn

December 10th, 2017 at 12:59 am

Substitutable Columns

with 2 comments

Oracle’s substitutable columns are interesting and substantially different than Oracle’s nested tables. The benefit of substitutable columns is that you can create one for an object type or any subtypes of that object type. Unfortunately, you can’t create the same behavior with nested tables because Oracle’s implementation of collection types are always final data types and you can’t extend their behaviors.

The Oracle Database has three types of collections. Two are SQL scoped collection types and the remaining one is a PL/SQL-only collection. You can only use the two SQL scoped collection types as column data types. One of the SQL-scoped collection types is an Attribute Data Type (ADT), which uses a base data type of DATA, NUMBER, or VARCHAR2.

The base data types of a UDT are scalar data types and scalar data types are data types that hold one thing. The other SQL-scoped collection type is a collection of User-Defined Types (UDTs), which are object types that you create like record structures by assembling sets of basic scalar data types. The elements of a UDT are known as members, whereas the instances of a collection are known as elements because they indexed in a set.

You can join a row with any nested table by using a cross join because they match the row with the nested table by using an ID-dependent join. An ID-dependent join is inexpensive because it relies on a structural dependency, the existence of the nested table in a column of a row. Typical joins on the other hand are joins between two tables or two copies of the same table. These non ID-dependent joins use at least matching values in one column of each table or one column of two copies of a table.

Joins between substitutable columns that hold UDTs are unlike joins between nested tables. The following sets up an example to demonstrate how you can join the non-substitutable columns of a row with the substitutable columns.

  1. You need a base UDT object type that you can extend, where extend means you can create a subtype of the base object type. While this is straight forward when you create an Oracle object type with methods, it isn’t necessarily straight forward when you want to simply create a base data structure as a generalized type with subtypes.

    The important clause is overriding the FINAL default by making the base type NOT FINAL. The example use BASE_T as the generalized type or data structure of a substitutable column:

    CREATE OR REPLACE TYPE base_t AS OBJECT
    ( base_id  NUMBER ) NOT FINAL;
    /
  2. After you create your base data structure, you create a specialized subtype. The following example creates a PERSON_T type and accepts the default of FINAL, which means you can’t create another subtype level.

    CREATE OR REPLACE TYPE person_t UNDER base_t
    ( first_name   VARCHAR2(20)
    , middle_name  VARCHAR2(20)
    , last_name    VARCHAR2(20));
    /
  3. With a generalized BASE_T type and a specialized PERSON_T subtype, you create a CUSTOMER table with a substitutable CUSTOMER_NAME column. The CUSTOMER_NAME column uses the generalized BASE_T data type. You should also create a CUSTOMER_S sequence that you can use as a surrogate key column for the table.

    CREATE TABLE customer
    ( customer_id    NUMBER
    , customer_name  BASE_T );
     
    CREATE SEQUENCE customer_s;
  4. You can now populate the table with instances of the BASE_T type or the PERSON_T subtype. The following inserts three rows into the CUSTOMER table. One for Hank Pym the original Ant-Man, one for Scott Lang the succeeding Ant-Man, and another for Darren Cross the original Yellowjacket.

    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Hank'
              , middle_name => NULL
              , last_name => 'Pym'));
     
    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Scott'
              , middle_name => NULL
              , last_name => 'Lang'));
     
    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Darren'
              , middle_name => NULL
              , last_name => 'Cross'));
  5. The significance or problem associated with substitutable columns is that the actual columns of the object data type are hidden, which means you can’t query them like they’re nested elements of the substitutable column. The following query demonstrates what happens when you try to access those hidden member columns:

    SELECT customer_id
    ,      customer_name.base_id
    ,      customer_name.first_name
    ,      customer_name.middle_name
    ,      customer_name.last_name
    FROM   customer;

    It returns the following error message:

    ,      customer_name.last_name
           *
    ERROR at line 5:
    ORA-00904: "CUSTOMER_NAME"."LAST_NAME": invalid identifier
  6. It only raises the last column in the SELECT-list because that’s the first place where it fails to recognize an identifier, which is a valid column name in scope of the query.

  7. This error message may lead you to call the CUSTOMER_NAME column in a subquery and use the TABLE function to convert it to a result set. However, it also fails because a UDT object type by itself is an ordinary object type not a collection of object types. The TABLE function can’t promote the single instance to collection.

    SELECT *
    FROM   TABLE(SELECT TREAT(customer_name AS person_t) FROM customer);

    It returns the following error message:

    FROM   TABLE(SELECT TREAT(customer_name AS person_t) FROM customer)
           *
    ERROR at line 2:
    ORA-22905: cannot access rows from a non-nested table item
  8. The non-nested table error message should lead you to wrap the call to the TREAT function in a call to the COLLECT function, like this:

    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT *
    FROM   TABLE(
             SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
             FROM customer);

    It returns the substitutable column’s hidden column labels and their values:

     Base First  Middle Last
     ID # Name   Name   Name
    ----- ------ ------ ------
        1 Hank	    Pym
        2 Scott	    Lang
        3 Darren	    Cross
  9. After learning how to unwrap the hidden columns of the substitutable column, you can now join the ordinary columns to the hidden columns like this:

    COL customer_id    FORMAT 9999  HEADING "Customer|ID #"
    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c INNER JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    It returns the ordinary column and substitutable column’s hidden column labels and their values:

    Customer  Base First  Middle Last
        ID #  ID # Name   Name   Name
    -------- ----- ------ ------ ------
           1     1 Hank	     Pym
           2     2 Scott	     Lang
           3     3 Darren	     Cross
  10. The preceding query only returns values when the substitutable column holds a value. It fails to return a value when the substitutable column holds a null value. You need to use a LEFT JOIN to ensure you see all ordinary columns whether or not the substitutable column holds a value.

    COL customer_id    FORMAT 9999  HEADING "Customer|ID #"
    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c LEFT JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    It returns the ordinary column and substitutable column’s hidden column labels and their values when the substitutable column holds an instance value. However, it only returns the ordinary column when the substitutable column holds a null value, as shown below:

    Customer  Base First  Middle Last
        ID #  ID # Name   Name   Name
    -------- ----- ------ ------ ------
           1     1 Hank	     Pym
           2     2 Scott	     Lang
           3     3 Darren	     Cross
           4
  11. It should be noted that queries like this have a cost, and that cost is high. So, you should only implement substitutable columns when the maintenance coding costs (or sustaining engineering) outweighs the processing cost.

    You can determine the cost like this:

    EXPLAIN PLAN
    SET STATEMENT_ID = 'Strange'
    FOR
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c LEFT JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    You can query the cost like this:

    SET LINESIZE 130
    SELECT *
    FROM   TABLE(dbms_xplan.display(NULL,'Strange'));

    It should return something like this for the sample table and solution:

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------
    Plan hash value: 2373055701
     
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation			     | Name	| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT		     |		|  8168 |   550K|	|   167   (2)| 00:00:03 |
    |   1 |  SORT ORDER BY			     |		|  8168 |   550K|   624K|   167   (2)| 00:00:03 |
    |*  2 |   HASH JOIN OUTER		     |		|  8168 |   550K|	|    32   (4)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL		     | CUSTOMER |     5 |    15 |	|     2   (0)| 00:00:01 |
    |   4 |    VIEW 			     |		|  8168 |   526K|	|    29   (0)| 00:00:01 |
    |   5 |     COLLECTION ITERATOR PICKLER FETCH|		|  8168 |	|	|    29   (0)| 00:00:01 |
    |   6 |      SORT AGGREGATE		     |		|     1 |    14 |	|	     |		|
    |   7 |       TABLE ACCESS FULL 	     | CUSTOMER |     5 |    70 |	|     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("C"."CUSTOMER_ID"="O"."SYS_NC_ROWINFO$"."BASE_ID"(+))

As always, I hope this explains how to insert and query the hidden columns of a substitutable column, and how you join ordinary columns and hidden columns of a substitutable column from a table.

Written by maclochlainn

December 8th, 2017 at 11:17 pm

How to install MongoDB

without comments

This post shows the yum command to install the MongoDB packages on Linux. More on setup and use will follow.

You install the MongoDB package with the following yum command:

yum install -y mongodb

You should see the following log file, which may also show a missing mirrored site:

Loaded plugins: langpacks, refresh-packagekit
cassandra/signature                                         |  819 B  00:00     
cassandra/signature                                         | 2.9 kB  00:00 !!! 
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
updates/20/x86_64/metalink                                  | 2.6 kB  00:00     
Resolving Dependencies
--> Running transaction check
---> Package mongodb.x86_64 0:2.4.6-1.fc20 will be installed
--> Processing Dependency: v8 for package: mongodb-2.4.6-1.fc20.x86_64
--> Processing Dependency: libv8.so.3()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64
--> Processing Dependency: libtcmalloc.so.4()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64
--> Processing Dependency: libboost_program_options.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64
--> Processing Dependency: libboost_iostreams.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64
--> Processing Dependency: libboost_filesystem.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64
--> Running transaction check
---> Package boost-filesystem.x86_64 0:1.54.0-12.fc20 will be installed
---> Package boost-iostreams.x86_64 0:1.54.0-12.fc20 will be installed
---> Package boost-program-options.x86_64 0:1.54.0-12.fc20 will be installed
---> Package gperftools-libs.x86_64 0:2.1-4.fc20 will be installed
---> Package v8.x86_64 1:3.14.5.10-18.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                   Arch       Version                 Repository   Size
================================================================================
Installing:
 mongodb                   x86_64     2.4.6-1.fc20            fedora       30 M
Installing for dependencies:
 boost-filesystem          x86_64     1.54.0-12.fc20          updates      66 k
 boost-iostreams           x86_64     1.54.0-12.fc20          updates      59 k
 boost-program-options     x86_64     1.54.0-12.fc20          updates     147 k
 gperftools-libs           x86_64     2.1-4.fc20              updates     266 k
 v8                        x86_64     1:3.14.5.10-18.fc20     updates     3.0 M
 
Transaction Summary
================================================================================
Install  1 Package (+5 Dependent packages)
 
Total download size: 34 M
Installed size: 101 M
Downloading packages:
(1/6): boost-iostreams-1.54.0-12.fc20.x86_64.rpm            |  59 kB  00:00     
(2/6): boost-filesystem-1.54.0-12.fc20.x86_64.rpm           |  66 kB  00:00     
(3/6): boost-program-options-1.54.0-12.fc20.x86_64.rpm      | 147 kB  00:00     
(4/6): gperftools-libs-2.1-4.fc20.x86_64.rpm                | 266 kB  00:00     
(5/6): v8-3.14.5.10-18.fc20.x86_64.rpm                      | 3.0 MB  00:00     
(6/6): mongodb-2.4.6-1.fc20.x86_64.rpm                      |  30 MB  00:04     
--------------------------------------------------------------------------------
Total                                              7.6 MB/s |  34 MB  00:04     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : 1:v8-3.14.5.10-18.fc20.x86_64                                1/6 
  Installing : boost-program-options-1.54.0-12.fc20.x86_64                  2/6 
  Installing : gperftools-libs-2.1-4.fc20.x86_64                            3/6 
  Installing : boost-filesystem-1.54.0-12.fc20.x86_64                       4/6 
  Installing : boost-iostreams-1.54.0-12.fc20.x86_64                        5/6 
  Installing : mongodb-2.4.6-1.fc20.x86_64                                  6/6 
  Verifying  : boost-iostreams-1.54.0-12.fc20.x86_64                        1/6 
  Verifying  : boost-filesystem-1.54.0-12.fc20.x86_64                       2/6 
  Verifying  : gperftools-libs-2.1-4.fc20.x86_64                            3/6 
  Verifying  : mongodb-2.4.6-1.fc20.x86_64                                  4/6 
  Verifying  : boost-program-options-1.54.0-12.fc20.x86_64                  5/6 
  Verifying  : 1:v8-3.14.5.10-18.fc20.x86_64                                6/6 
 
Installed:
  mongodb.x86_64 0:2.4.6-1.fc20                                                 
 
Dependency Installed:
  boost-filesystem.x86_64 0:1.54.0-12.fc20                                      
  boost-iostreams.x86_64 0:1.54.0-12.fc20                                       
  boost-program-options.x86_64 0:1.54.0-12.fc20                                 
  gperftools-libs.x86_64 0:2.1-4.fc20                                           
  v8.x86_64 1:3.14.5.10-18.fc20                                                 
 
Complete!

Hope this helps those looking for the command.

Written by maclochlainn

September 27th, 2017 at 10:16 pm

PostgreSQL Identity Columns

without comments

It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.

Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.

MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:

CREATE TABLE auto
( id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, text_field   VARCHAR(30)  NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.

If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:

CREATE TABLE auto
( auto_id     NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001)
, text_field  VARCHAR2(30)
, CONSTRAINT  auto_pk PRIMARY KEY (auto_id));

You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.

CREATE TABLE auto
( id          SERIAL      CONSTRAINT auto_pk PRIMARY KEY
, text_field  VARCHAR(30));
 
ALTER SEQUENCE auto_id_seq RESTART WITH 1001;

You can see the table and assigned sequence with the following command in PostgreSQL:

\d+ auto

It should display:

                                                      Table "public.auto"
   Column   |         Type          |                     Modifiers                     | Storage  | Stats target | Description 
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
 id         | integer               | not null default nextval('auto_id_seq'::regclass) | plain    |              | 
 text_field | character varying(30) |                                                   | extended |              | 
Indexes:
    "auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no

As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.

Written by maclochlainn

August 4th, 2017 at 12:52 am

Cassandra Query Language

without comments

After installing Cassandra and reading Cassandra The Definitive Guide, it struck me that I should learn a bit more about the Cassandra Query Language (CQL). So, after I setup a single-node environment and created a .bashcassandra environment file to connect as a student user to the Cassandra instance:

# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre
 
# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

Having started Cassandra as the cassandra user, I connected to the Cassandra Query Language Shell (cqlsh) to learn how to write CQL. You can find the basic structure of the Cassandra Query Language (CQL) on the Apache Cassandra website. I also discovered that CQL by itself can’t let you join tables without using Apache SparkSQL. Apache SparkSQL adds the ability to perform CQL joins in Cassandra, and became available in 2015.

I also learned you can’t use a CREATE OR REPLACE command when you change certain aspects of User-Defined Functions (UDFs). You actually need to drop any UDF before you change RETURNS NULL ON NULL INPUT clause to a CALLED ON NULL INPUT clause or vice versa. You can’t embed Java that connects to database without using the cassandra-java-driver-2.0.2 driver.

You connect to the cqlsh like this:

cqlsh

Here’s my script that creates Cassandra keyspace, which is more or less a database. You use the USE command to connect to the keyspace or database, like you would in MySQL. You do not have sequences in Cassandra because they’re not a good fit for a distributed architecture. Cassandra does not support a native procedural extension like relational databases. You must create User-defined functions (UDFs) by embedding the logic in Java.

This script does the following:

  • Creates a keyspace
  • Uses the keyspace
  • Conditionally drops tables and functions
  • Creates two tables
  • Inserts data into the two tables
  • Queries data from the tables

I also included a call to a UDF inside a query in two of the examples. One of the queries demonstrates how to return a JSON structure from a query. To simplify things and provide clarification of the scripts behaviors, the details are outlined below.

  • The first segment of the script creates the keyspace, changes the scope to use the keyspace, conditionally drop tables, create tables, and insert values into the tables:

    /* Create a keyspace in Cassandra, which is like a database
       in MySQL or a schema in Oracle. */
    CREATE KEYSPACE IF NOT EXISTS student
      WITH REPLICATION = {
         'class':'SimpleStrategy'
        ,'replication_factor': 1 }
      AND DURABLE_WRITES = true;
     
    /* Use the keyspace or connect to the database. */
    USE student;
     
    /* Drop the member table from the student keyspace. */
    DROP TABLE IF EXISTS member;
     
    /* Create a member table in the student keyspace. */
    CREATE TABLE member
    ( member_number       VARCHAR
    , member_type         VARCHAR
    , credit_card_number  VARCHAR
    , credit_card_type    VARCHAR
    , PRIMARY KEY ( member_number ));
     
    /* Conditionally drop the contact table from the student keyspace. */
    DROP TABLE IF EXISTS contact;
     
    /* Create a contact table in the student keyspace. */
    CREATE TABLE contact
    ( contact_number      VARCHAR
    , contact_type        VARCHAR
    , first_name          VARCHAR
    , middle_name         VARCHAR
    , last_name           VARCHAR
    , member_number       VARCHAR
    , PRIMARY KEY ( contact_number ));
     
    /* Insert a row into the member table. */
    INSERT INTO member
    ( member_number, member_type, credit_card_number, credit_card_type )
    VALUES
    ('SFO-12345','GROUP','2222-4444-5555-6666','VISA');
     
    /* Insert a row into the contact table. */
    INSERT INTO contact
    ( contact_number, contact_type, first_name, middle_name, last_name, member_number )
    VALUES
    ('CUS_00001','FAMILY','Barry', NULL,'Allen','SFO-12345');
     
    /* Insert a row into the contact table. */
    INSERT INTO contact
    ( contact_number, contact_type, first_name, middle_name, last_name, member_number )
    VALUES
    ('CUS_00002','FAMILY','Iris', NULL,'West-Allen','SFO-12345');
     
    /* Insert a row into the member table. */
    INSERT INTO member
    ( member_number, member_type, credit_card_number, credit_card_type )
    VALUES
    ('SFO-12346','GROUP','3333-8888-9999-2222','VISA');
     
    /* Insert a row into the contact table. */
    INSERT INTO contact
    ( contact_number, contact_type, first_name, middle_name, last_name, member_number )
    VALUES
    ('CUS_00003','FAMILY','Caitlin','Marie','Snow','SFO-12346');
  • The following queries the member table:

    /* Select all columns from the member table. */
    SELECT * FROM member;

    It returns the following:

     member_number | credit_card_number  | credit_card_type | member_type
    ---------------+---------------------+------------------+-------------
         SFO-12345 | 2222-4444-5555-6666 |             VISA |       GROUP
         SFO-12346 | 3333-8888-9999-2222 |             VISA |       GROUP
  • Create a concatenate User-defined function (UDF) for Cassandra. The first step requires you to edit the cassandra.yaml file, which you find in the /etc/cassandra/default.conf directory. There is a single parameter that you need to edit, and it is the enable_user_defined_functions parameter. By default the parameter is set to false, and you need to enable it to create UDFs.

    After you make the edit, the cassandra.yaml file should look like this:

    1089
    1090
    1091
    1092
    1093
    
    # If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
    # INFO level
    # UDFs (user defined functions) are disabled by default.
    # As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
    enable_user_defined_functions: true

    After you make the change, you can create your own UDF. The following UDF formats the first, middle, and last name so there’s only one whitespace between the first and last name when there middle name value is null.

    This type of function must use a CALLED ON NULL INPUT clause in lieu of a RETURNS NULL ON NULL INPUT clause. The latter would force the function to return a null value if any one of the parameters were null.

    /* Drop the concatenate function because a replace disallows changing a
       RETURNS NULL ON NULL INPUT with a CALLED ON NULL INPUT without raising
       an "89: InvalidRequest" exception. */
    DROP FUNCTION concatenate;
     
    /* Create a user-defined function to concatenate names. */
    CREATE OR REPLACE FUNCTION concatenate (first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR)
    CALLED ON NULL INPUT
    RETURNS VARCHAR
    LANGUAGE java
    AS $$
      /* Concatenate first and last names when middle name is null, and
         first, middle, and last names when middle name is not null. */
      String name;
     
      /* Check for null middle name. */
      if (middle_name == null) {
        name = first_name + " " + last_name; }
      else {
        name = first_name + " " + middle_name + " " + last_name; }
     
      return name;
    $$;
  • Query the values from the contact table with the UDF function in the SELECT-list:

    /* Query the contact information. */
    SELECT member_number
    ,      contact_number
    ,      contact_type
    ,      concatenate(first_name, middle_name, last_name) AS full_name
    FROM   contact;

    It returns the following:

     member_number | contact_number | contact_type | full_name
    ---------------+----------------+--------------+--------------------
         SFO-12345 |      CUS_00001 |       FAMILY |        Barry Allen
         SFO-12345 |      CUS_00002 |       FAMILY |    Iris West-Allen
         SFO-12346 |      CUS_00003 |       FAMILY | Caitlin Marie Snow
  • Query the values from the contact table with a JSON format:

    /* Query the contact information and return in a JSON format. */
    SELECT JSON
           contact_number
    ,      contact_type
    ,      concatenate(first_name, middle_name, last_name) AS full_name
    FROM   contact;

    It returns the following:

     [json]
    -------------------------------------------------------------------------------------------------
    {"contact_number": "CUS_00001", "contact_type": "FAMILY", "full_name": "Barry Allen"}
    {"contact_number": "CUS_00002", "contact_type": "FAMILY", "full_name": "Iris West-Allen"}
    {"contact_number": "CUS_00003", "contact_type": "FAMILY", "full_name": "Caitlin Marie Snow"}

You can call the script from a relative directory inside cqlsh, like this:

SOURCE 'cstudent.cql'

At the end of the day, the concept of adding and removing nodes is attractive. Though, the lack of normal relational mechanics and narrowly supported set of CQL semantics leaves me with open questions. For example, is clustering without a coordinator really valuable enough to settle for eventual, or tunable, consistency with such a narrowly scoped query language?

As always, I hope this helps those looking for a quick how-to on Cassandra.

Written by maclochlainn

July 30th, 2017 at 12:33 am

DevOps Handbook Review

without comments

DevOps: Is it a mindset or process? That’s a big question for managers because while you can’t manage a mindset, you can manage a process. DevOps is actually a framework of processes.

Some actually say DevOps is actually a lot like making a patchwork quilt. You need to design the patches before you figure out how to stitch them together. I believe this is true, and base that on my experience as a release engineer at Oracle Corporation.

You need to adopt:

  • An Agile system development life cycle that ensures you value outcome over process.
  • A value stream that lets you track the efficiency and effectiveness of costs verses outcomes.
  • A measurable development pipeline that tracks costs and outcomes.
  • A management strategy that handles operational, tactical, and strategic goals.

Those are big goals to accomplish but how will you achieve them? I would suggest that you first try to understand the experiences of others who adopted a DevOps methodology.

I’d like to recommend The DevOps Handbook because it uses case studies to highlight the best practices in DevOps. They do use some engineering terms to describe things that could be simpler. For example, telemetry is a fancy word for plans but it strikes me they use the word for a purpose. The authors want to commit us to adopting a rigorous planned route before we implement DevOps approaches.

While I may not choose all the words they did to convey these ideas and case studies, they are consistent in their approach. Along the way, they introduce much of the supporting case studies one-by-one. Moreover, they demonstrate approaches taken to solve types of problems and leave the integration of ideas to us.

Here’s a layout of the book since the table of contents is missing on Amazon.com’s website.

  • Part 1: How DevOps applies lean principles to technology
    • Chapter 1: Introduction
    • Chapter 2: The Principles of Flow
    • Chapter 3: The Principles of Feedback
    • Chapter 4: The Principles of Continual Learning and Experimentation
  • Part 2: Where to Start the DevOps Transformation
    • Chapter 5: Selecting which Value Stream to Start With
    • Chapter 6: Understanding the Work in Our Value Stream
    • Chapter 7: How to Design Our Organization and Architecture
    • Chapter 8: How to Get Great Outcomes by Integrating Operations into Daily Work
  • Part 3: How to Implement Technical Practices for Continuous Delivery
    • Chapter 9: Create the Foundations of the Deployment Pipeline
    • Chapter 10: Enable Fast and Reliable Automated Testing
    • Chapter 11: Enable and Practice Continuous Integration
    • Chapter 12: Automate and Enable Low-Risk Releases
    • Chapter 13: Architect for Low-Risk Releases
  • Part 4: How to Implement Technical Practices for Fast and Continuous Feedback
    • Chapter 14: Create Telemetry to Enable Seeing and Solving Problems
    • Chapter 15: Analyze Telemetry to Better Anticipate Problems and Achieve Goals
    • Chapter 16: Enable Feedback to Safely Deploy Code
    • Chapter 17: Integrate Hypothesis-Driven Development and Testing
    • Chapter 18: Create Review and Coordination Process to Increase Qualify of Work
  • Part 5: How to implement Feedback to Drive Sooner, Faster, and Cheaper Results
    • Chapter 19: Enable and Inject Learning into Daily Work
    • Chapter 20: Convert Local Discoveries into Global Improvements
    • Chapter 21: Reserve Time to Create Organizational Learning and Improvement
  • Part 6: How to Implement Feedback on Achieving Information Security Goals
    • Chapter 22: Information Security as Everyone’s Job, Every Day
    • Chapter 23: Protecting the Deployment Pipeline

I hope this helps those looking for a good reference on DevOps.

Written by maclochlainn

July 26th, 2017 at 6:50 pm

Posted in Agile

Tagged with ,

Install Cassandra on Fedora

with one comment

It was quite interesting to discover that DataStax no longer provides the DataStax Community version of Apache Cassandra or the DataStax Distribution of Apache Cassandra. Needless to say, I was quite disappointed because it means folks will get less opportunity to learn how to use Cassandra because it makes it more difficult for beginning developers.

I spent a good hour sorting through what was available and then figuring out the real requirements to install Apache Cassandra 3.11. These are the instructions.

Install Java and JRE as Prerequisites

If you don’t have the JRE installed, you should download it from Oracle’s website and install it. After you download the latest version of the JRE package (jre-8u141-linux-x64.rpm). You should use the rpm utility to install the JRE package, like the following example:

rpm -ivh /home/student/Downloads/jre-8*.rpm

It should generate the following installation report:

Preparing...                          ################################# [100%]
	package jre1.8.0_141-1.8.0_141-fcs.x86_64 is already installed
sh-4.2# rpm -qa jre
sh-4.2# rpm -qf jre
error: file /jre: No such file or directory
sh-4.2# rpm -qa | grep jre
jre1.8.0_141-1.8.0_141-fcs.x86_64
sh-4.2# rpm -qa | grep jre | rpm -qi
rpm: no arguments given for query
sh-4.2# rpm -qi `rpm -qa | grep jre`
Name        : jre1.8.0_141
Version     : 1.8.0_141
Release     : fcs
Architecture: x86_64
Install Date: Mon 24 Jul 2017 11:09:58 PM PDT
Group       : Development/Tools
Size        : 139460427
License     : http://java.com/license
Signature   : (none)
Source RPM  : jre1.8.0_141-1.8.0_141-fcs.src.rpm
Build Date  : Wed 12 Jul 2017 04:47:52 AM PDT
Build Host  : jdk7-lin2-amd64
Relocations : /usr/java 
Packager    : Java Software <jre-comments@java.sun.com>
Vendor      : Oracle Corporation
URL         : URL_REF
Summary     : Java Platform Standard Edition Runtime Environment
Description :
The Java Platform Standard Edition Runtime Environment (JRE) contains
everything necessary to run applets and applications designed for the
Java platform. This includes the Java virtual machine, plus the Java
platform classes and supporting files.
 
The JRE is freely redistributable, per the terms of the included license.

Confirm Java and JRE Installation

You can check the current installed version of Java and JRE by using the alternatives utility with the --config option and the keyword of java or jre.

sh-4.2# alternatives --config java

It should generate the following list when you check for the java library:

There are 3 programs which provide 'java'.
 
  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64/jre/bin/java
 + 2           /usr/lib/jvm/jre-1.8.0-openjdk.x86_64/bin/java
   3           /usr/java/jre1.8.0_141/bin/java
 
Enter to keep the current selection[+], or type selection number:

It should generate the following list when you check for the javac library:

There are 2 programs which provide 'javac'.
 
  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64/bin/javac
 + 2           /usr/lib/jvm/java-1.8.0-openjdk.x86_64/bin/javac
 
Enter to keep the current selection[+], or type selection number:

After installing and selecting them as the designated alternative, if you have more than one Java or JRE installed on your OS, you should create a configuration file for the root user. You should include the following to set your $PATH, $JAVA_HOME, and $JRE_HOME environment variables:

# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre
 
# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

Install Apache Cassandra

The yum utility is the best way to install Apache Cassandra. However, you will need to configure the /etc/yum.repos.d/cassandra.repo before you attempt to install Cassandra 3.11 from the Apache organization, like this:

[cassandra]
name=Apache Cassandra
baseurl=https://www.apache.org/dist/cassandra/redhat/311x/
gpgcheck=1
repo_gpgcheck=1
gpgkey=https://www.apache.org/dist/cassandra/KEYS

After you’ve added the necessary yum configuration file and ensured you’re using both Java 1.8 and JRE 1.8, you can install Apache Cassandra with the following yum command as the root user or as a sudoer member with the sudo command:

yum install -y cassandra

If successful, you should see the following output:

Loaded plugins: langpacks, refresh-packagekit
cassandra/signature                                         |  819 B  00:00     
cassandra/signature                                         | 2.9 kB  00:00 !!! 
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
updates/20/x86_64/metalink                                  | 2.6 kB  00:00     
Resolving Dependencies
--> Running transaction check
---> Package cassandra.noarch 0:3.11.0-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package            Arch            Version            Repository          Size
================================================================================
Installing:
 cassandra          noarch          3.11.0-1           cassandra           28 M
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 28 M
Installed size: 37 M
Downloading packages:
warning: /var/cache/yum/x86_64/20/cassandra/packages/cassandra-3.11.0-1.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID fe4b2bda: NOKEY
Public key for cassandra-3.11.0-1.noarch.rpm is not installed
cassandra-3.11.0-1.noarch.rpm                               |  28 MB  00:07     
Retrieving key from https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0xF2833C93:
 Userid     : "Eric Evans <eevans@sym-link.com>"
 Fingerprint: cec8 6bb4 a0ba 9d0f 9039 7cae f835 8fa2 f283 3c93
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x8D77295D:
 Userid     : "Eric Evans <eevans@sym-link.com>"
 Fingerprint: c496 5ee9 e301 5d19 2ccc f2b6 f758 ce31 8d77 295d
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x2B5C1B00:
 Userid     : "Sylvain Lebresne (pcmanus) <sylvain@datastax.com>"
 Fingerprint: 5aed 1bf3 78e9 a19d ade1 bcb3 4bd7 36a8 2b5c 1b00
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x0353B12C:
 Userid     : "T Jake Luciani <jake@apache.org>"
 Fingerprint: 514a 2ad6 31a5 7a16 dd00 47ec 749d 6eec 0353 b12c
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0xFE4B2BDA:
 Userid     : "Michael Shuler <michael@pbandjelly.org>"
 Fingerprint: a26e 528b 271f 19b9 e5d8 e19e a278 b781 fe4b 2bda
 From       : https://www.apache.org/dist/cassandra/KEYS
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
Warning: RPMDB altered outside of yum.
  Installing : cassandra-3.11.0-1.noarch                                    1/1 
  Verifying  : cassandra-3.11.0-1.noarch                                    1/1 
 
Installed:
  cassandra.noarch 0:3.11.0-1                                                   
 
Complete!

Starting Cassandra

You should start Cassandra as the cassandra user. Before starting Cassandra, you need to create a .bashrc file for the cassandra user because one isn’t created by default since you can’t log on to the Linux OS as the cassandra user. The home directory for the cassandra user is /var/lib/cassandra and the owner of that directory is the root user.

As the root user, create the following .bashrc file for the cassandra user:

# Wrap sqlplus with rlwrap to edit prior lines with the
# up, down, left and right keys.
cqlsh()
{
  if [ "$RLWRAP" = "0" ]; then
    cqlsh "$@"
  else
    rlwrap cqlsh "$@"
  fi
}
 
# Set vi as a command line editor.
set -o vi
 
# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre
 
# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

You should start Cassandra in background, like this:

cassandra

Using Cassandra

As the student user in my developer Fedora instance, you should be able to connect using the following:

cqlsh

You will see the following:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh> HELP
 
Documented shell commands:
===========================
CAPTURE  CLS          COPY  DESCRIBE  EXPAND  LOGIN   SERIAL  SOURCE   UNICODE
CLEAR    CONSISTENCY  DESC  EXIT      HELP    PAGING  SHOW    TRACING
 
CQL help topics:
================
AGGREGATES               CREATE_KEYSPACE           DROP_TRIGGER      TEXT     
ALTER_KEYSPACE           CREATE_MATERIALIZED_VIEW  DROP_TYPE         TIME     
ALTER_MATERIALIZED_VIEW  CREATE_ROLE               DROP_USER         TIMESTAMP
ALTER_TABLE              CREATE_TABLE              FUNCTIONS         TRUNCATE 
ALTER_TYPE               CREATE_TRIGGER            GRANT             TYPES    
ALTER_USER               CREATE_TYPE               INSERT            UPDATE   
APPLY                    CREATE_USER               INSERT_JSON       USE      
ASCII                    DATE                      INT               UUID     
BATCH                    DELETE                    JSON            
BEGIN                    DROP_AGGREGATE            KEYWORDS        
BLOB                     DROP_COLUMNFAMILY         LIST_PERMISSIONS
BOOLEAN                  DROP_FUNCTION             LIST_ROLES      
COUNTER                  DROP_INDEX                LIST_USERS      
CREATE_AGGREGATE         DROP_KEYSPACE             PERMISSIONS     
CREATE_COLUMNFAMILY      DROP_MATERIALIZED_VIEW    REVOKE          
CREATE_FUNCTION          DROP_ROLE                 SELECT          
CREATE_INDEX             DROP_TABLE                SELECT_JSON

Written by maclochlainn

July 25th, 2017 at 9:23 pm