NuoSQL Options for Displaying Data

The NuoSQL tool, nuosql, is the command line utility for accessing data in NuoDB.  There is one new option as of NuoDB 1.2 that is very useful for displaying data with very wide columns using nuosql.  Then there's another new option (but you'll have to wait for NuoDB 2.0) that is useful for displaying many rows of data.

Displaying your data is a challenge when you have very long character strings in a column or new lines in a character string column.  To demonstrate this, I created a table with the following schema:

SQL> show table nuodb_friends

    Found table NUODB_FRIENDS in schema USER

        NAME string
        HOME_ADDRESS string
        WHAT_THEY_DO_AT_NUODB string
        WHY_THEY_ARE_MY_FRIEND string

SQL> 

The data in the HOME_ADDRESS column has newlines and data in the other columns might be particularly long.    Simply doing a SELECT in nuosql presents some pretty messy, unreadable output.

SQL> select * from nuodb_friends;

   NAME                HOME_ADDRESS                                    WHAT_THEY_DO_AT_NUODB                                                                                               WHY_THEY_ARE_MY_FRIEND                                                                        
 --------- ------------------------------------ ------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

 Paul      10 Main St.
Sunnytown, MA
01010
USA  Product Management which pretty much means everything, Makes coffee He tells funny stories about his family and kids.  He is always available to listen to my stories about life with my teenage children.                               
 John      11 Elm St.
Sunnyville, NH
02020      SQL engine, wicked smaaahht, knows a lot about Hibernate and Java   This is the third company we have worked at together.  He is very, very nice and I can ask him all my stupid questions.  And he has an enjoyable level of pessimism. 
 Elisabete 100 LongCommute St.
Springfield, USA Customer support, generally friendly and helpful resource           She is super friendly and always smiling.                                                                                                                            

SQL> 

Yuck!   Sometimes it's just easier to display the data vertically instead of horizontally, so nuosql lets you do this.  All you have to do is say "SET OUTPUT VERTICAL".

SQL> set output vertical;
SQL> select * from nuodb_friends;
==================================== Row #1 ====================================
NAME: Paul
HOME_ADDRESS: 10 Main St.
Sunnytown, MA
01010
USA
WHAT_THEY_DO_AT_NUODB: Product Management which pretty much means everything, Makes coffee
WHY_THEY_ARE_MY_FRIEND: He tells funny stories about his family and kids.  He is always available to listen to my stories about life with my teenage children.
==================================== Row #2 ====================================
NAME: John
HOME_ADDRESS: 11 Elm St.
Sunnyville, NH
02020
WHAT_THEY_DO_AT_NUODB: SQL engine, wicked smaaahht, knows a lot about Hibernate and Java
WHY_THEY_ARE_MY_FRIEND: This is the third company we have worked at together.  He is very, very nice and I can ask him all my stupid questions.  And he has an enjoyable level of pessimism.
==================================== Row #3 ====================================
NAME: Elisabete
HOME_ADDRESS: 100 LongCommute St.
Springfield, USA
WHAT_THEY_DO_AT_NUODB: Customer support, generally friendly and helpful resource
WHY_THEY_ARE_MY_FRIEND: She is super friendly and always smiling.

SQL> 

Pretty cool, right?  

Spoiler alert... Now suppose you have rows and rows and rows of data and when you do your SELECT it streams by in a flash.  Well you can always scroll back.  Or, with NuoDB 2.0, you will be able to set a PAGER.  Setting a PAGER will cause the results of your SQL query to be piped to that command.  You can specify a one word pager, for example,

SQL> SET PAGER more;

which will set the pager to the specified command and the command must be on the user's PATH.  Or you can specify a full command string surrounded in quotes, for example,

SQL> SET PAGER "less -X -E"

A colleague asked if you could set the pager to anything, like an editor or something.  Yep, you can really hang yourself with this, but you can also do a lot of useful things.   Check this out:

SQL> create table names(who string);
SQL> insert into names values('Mary'), ('Abe'), ('John'), ('Zeb'), ('Laurie'), ('Oleg'), ('Zoey'), ('Jakob');
SQL> select * from names;

  WHO   
 ------ 

 Mary   
 Abe    
 John   
 Zeb    
 Laurie 
 Oleg   
 Zoey   
 Jakob  

SQL> set pager sort;
SQL> select * from names;


  WHO   
 ------ 
 Abe    
 Jakob  
 John   
 Laurie 
 Mary   
 Oleg   
 Zeb    
 Zoey   
SQL> 

It's really fun to add useful features and make this tool more user-friendly.  I'm curious what useful options you would like to see implemented in nuosql.   Respond in comments here!