NoSQL Tutorial

A comprehensive look at the NoSQL database.
More Insertion: The Lists

NoSQL can handle data in different way, called list format. A sample table may be:

CODE    ACM003
NAME    Bart Simpson
PHONE   1-555-5432-321
CODE    ACM004
NAME    Wiley The Coyote"

Yes, you're right! This is the same way the edit command displays data. Although list tables aren't performing at all, in my opinion they are a good way to insert new data into tables. It's handy creating a program that can output this fashion. An example is shown in Listing 1.

Okay, this is not a “state of the art” shell program, but this example may show that the complete operation is easy with every language, even the shell.

There are a couple of things I would like to emphasize about the above code. How can a list be merged with a real table? The command listtotable does the job for you, as you probably guessed looking at the pipe, converting the list format to the table one. A reverse command, tabletolist, exists as well.

Please notice the beginning and ending newlines, as well as the tabs between field name and value, which appear in the print statement: these are required in order to create the correct list structure.

The list structure, as well as table structure, are well documented in the Chapter 2 of the NoSQL reference you will find in /usr/local/doc/nosql/doc.

Getting More from NoSQL

Now, let's have a business example? A complete catalog was created using a NoSQL table (see the catalog.rdb file below) and published on the Web dynamically. Every two days, we receive orders from our customers that they have usually created with Excel and exported, at our request, in a coma-separated file.

PRID    DESC                             PRICE
------  -------------------------       ------
PRD001  Acme glue for RoadRunners        30.00
PRD002  Acme TNT                        150.00
PRD003  Carrots                           5.00
PRD004  Acme toolbox                     75.00
The file (sample_order.txt below) we receive has the following
format: requester's unique code, Product ID, Quantity.

Now from a shell or command line we run:

export TMPFILE=`mktemp -q /tmp/$0.XXXXXX` ; cat sample_order.txt | \
perl -e 'print "CODE\tPROD\tQTY\n"; print "----\t----\t---\n"; \
catalog.rdb | \
nosql addcol SUBTOTAL | nosql compute 'SUBTOTAL = QTY*PRICE' > $TMPFILE ; \
echo "Please bill to:" ; echo "---------------" ; echo ""; cat
nosql join -j CODE - customer.rdb | nosql column NAME PHONE EMAIL | \
nosql body | head -1 ; echo "";echo "" ; cat $TMPFILE | nosql rmcol CODE | \
nosql print -w; echo ""; echo -n "Total due: "; cat $TMPFILE | \
nosql subtotal -T SUBTOTAL | nosql body ; rm $TMPFILE
and our output is:
Please bill to:
Wiley The Coyote        1-800-ILLGETIT
PROD   QTY DESC                       PRICE SUBTOTAL
------ --- ------------------------- ------ --------
PRD001   5 Acme glue for RoadRunners  30.00      150
PRD002  30 Acme TNT                  150.00     4500
PRD004   1 Acme toolbox               75.00       75
Total due: 4725
This result may be sent via e-mail to our logistic people. Not so bad for a five-minute single shell command, is it?

I know it's a bit hermetic, so let's have a closer look: the explanation is divided in four sections to be easily read. I will keep out the echo commands which are quite obvious.

Section 1: Extracting Useful Data From the Received File
  • export TMPFILE=`mktemp -q /tmp/$0.XXXXXX`: exporting environment variable TMPFILE that contains a runtime generated tempfile.

  • cat sample_order.txt: get as input the file we received.

  • perl -e 'print "CODE\tPROD\tQTY\n"; print "-----\t----\t---\n"; while(<STDIN>) { s/,/\t/g; print };`: prints a NoSQL compliant header, composed of CODE, PROD and QTY. Then, it prints the table received from STDIN, substituting a coma (,) with a tab (\t), in order to get the correct table structure.

  • nosql join -j PROD - catalog.rdb: joins the table read from STDIN (the - character) and catalog.rdb using the PROD (product ID) column.

  • nosql addcol SUBTOTAL: now a column SUBTOTAL is added to the resulting table.

  • nosql compute 'SUBTOTAL = QTY*PRICE' > $TMPFILE: calculates the SUBTOTAL column by multiplying quantity and price columns. It then redirects the resulting table to the previous calculated temporary file.



Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.


srikanth.v's picture

my query is i want the thing as price * qty should give the amount and the total should be calculated for that what is the query in oracle sql(structured query language).