text:
Kaldi GStreamer server
======================
[![GitHub license](https://img.shields.io/github/license/alumae/kaldi-gstreamer-server.svg)](https://github.com/alumae/kaldi-gstreamer-server/blob/master/LICENSE)
[![Code Climate](https://img.shields.io/codeclimate/github/alumae/kaldi-gstreamer-server.svg)](https://codeclimate.com/github/alumae/kaldi-gstreamer-server)

This is a real-time full-duplex speech recognition server, based on
the Kaldi toolkit and the GStreamer framework and implemented in Python.

Features
--------

  * Full duplex communication based on websockets: speech goes in, partial hypotheses come out (think of Android's voice typing)
  * Very scalable: the server consists of a master component and workers; one worker is needed per concurrent recognition session; workers can be
    started and stopped independently of the master on remote machines
  * Can do speech segmentation, i.e., a long speech signal is broken into shorter segments based on silences
  * Supports arbitrarily long speech input (e.g., you can stream live speech into it)
  * Supports Kaldi's GMM and "online DNN" models
  * Supports rescoring of the recognition lattice with a large language model
  * Supports persisting the acoustic model adaptation state between requests
  * Supports unlimited set of audio codecs (actually only those supported by GStreamer)
  * Supports rewriting raw recognition results using external programs (can be used for converting words to numbers, etc)
  * Python, Java, Javascript clients are available

English demo that uses the server: http://bark.phon.ioc.ee/dictate/

Estonian demo: http://bark.phon.ioc.ee/dikteeri/

Changelog
---------
  * 2017-06-28: The [sample client program](kaldigstserver/client.py) can now accept audio from stdin. This can be used to test the server with a live microphone, e.g.:
    `arecord -f S16_LE -r 16000 | python kaldigstserver/client.py -r 32000 -`. Thanks to @wkuna!

  * 2016-11-28: Server now supports serving requests using SSL. SSL is automatically turned on when the `certfile` and `keyfile` command line arguments are specified.

  * 2016-10-14: Support for nnet3 (including 'chain') models, thanks to @yifan! Not tested very carefully.
  Set the `decoder->nnet-mode` property to 3 to use nnet3 models.

  * 2016-10-06: added a sample conf for Librispeech models and the corresponding model download script (thanks to @skoocda)

  * 2015-12-04: added a link to the Dockerfile.

  * 2015-06-30: server now uses the recently added "full final results" functionality of gst-kaldi-nnet2-online.
  Full results can include things like n-best hypotheses, word and phone alignment information,
  and possibly other things in the future. You have to upgrade gst-kaldi-nnet2-online (when using this plugin instead of
  the GMM-based Kaldi GStreamer plugin) prior to using this. Also added a sample full results post-processing
  script `sample_full_post_processor.py` (see `sample_english_nnet2.yaml` on how to use it).

Installation
------------

### Docker

Building Kaldi and all the other packages required by this software can be quite complicated. Instead of building
all the prerequisites manually, one could use the Dockerfile created by José Eduardo Silva: https://github.com/jcsilva/docker-kaldi-gstreamer-server.

### Requirements

#### Python 2.7 with the following packages:

  * Tornado 4, see http://www.tornadoweb.org/en/stable/
  * ws4py (0.3.0 .. 0.3.2)
  * YAML
  * JSON

*NB!*: The server doesn't work quite correctly with ws4py 0.3.5 because of a bug I reported here: https://github.com/Lawouach/WebSocket-for-Python/issues/152.
Use ws4py 0.3.2 instead. To install ws4py 0.3.2 using `pip`, run:

    pip install ws4py==0.3.2

In addition, you need Python 2.x bindings for gobject-introspection libraries, provided by the `python-gi`
package on Debian and Ubuntu.

#### Kaldi

Download and compile Kaldi (http://kaldi.sourceforge.net). Also compile the online extensions (`make ext`)
and the Kaldi GStreamer plugin (see `README` in Kaldi's `src/gst-plugin` directory).

#### Acoustic and language models for Kaldi

You need GMM-HMM-based acoustic and n-gram language models (actually their FST cascade) for your language.

Working (but not very accurate) recognition models are available for English and Estonian in the  `test/models/` directory.
English models are based on Voxforge acoustic models and the CMU Sphinx  2013 general English trigram language model (http://cmusphinx.sourceforge.net/2013/01/a-new-english-language-model-release/).
The language models were heavily pruned so that the resulting FST cascade would be less than the
100 MB GitHub file size limit.

*Update:* the server also supports Kaldi's new "online2" online decoder that uses DNN-based acoustic models with i-vector input. See below on
how to use it. According to experiments on two Estonian online decoding setups, the DNN-based models result in about 20% (or more) relatively less
errors than GMM-based models (e.g., WER dropped from 13% to 9%).

Running the server
------------------

### Running the master server

The following starts the main server on localhost:8888

    python kaldigstserver/master_server.py --port=8888

### Running workers

The master server doesn't perform speech recognition itself, it simply delegates client recognition
requests to workers. You need one worker per recognition session. So, the number of running workers
should be at least the number of potential concurrent recognition sessions. Good thing is that
workers are fully independent and do not even have to be running on the same machine, thus
offering practically unlimited parallelness.

There are two decoders that a worker can use: based on the Kaldi `onlinegmmdecodefaster` GStreamer plugin
or based on the newer `kaldinnet2onlinedecoder` plugin. The first one supports GMM models, the latter one needs
"online2" DNN-based models with i-vector input.

To run a worker, first write a configuration file. A sample configuration that uses the English GMM-HMM
models that come with this project is available in `sample_worker.yaml`. A sample worker that uses
"online2" DNN-based models is in `sample_english_nnet2.yaml`.

#### Using the 'onlinegmmdecodefaster' based worker

Before starting a worker, make sure that the GST plugin path includes Kaldi's `src/gst-plugin` directory
(which should contain the file `libgstkaldi.so`), something like:

    export GST_PLUGIN_PATH=~/tools/kaldi-trunk/src/gst-plugin

Test if it worked:

    gst-inspect-1.0 onlinegmmdecodefaster

The latter should print out information about the Kaldi's GStreamer plugin.

Now, you can start a worker:

    python kaldigstserver/worker.py -u ws://localhost:8888/worker/ws/speech -c sample_worker.yaml

The `-u ws://localhost:8890/worker/ws/speech` argument specifies the address of the main server
that the worker should connect to. Make sure you are using the same port as in the server invocation.

You can start any number of worker processes, just use the same command to start the next workers.

It might be a good idea to use [supervisord](http://supervisord.org) to start and stop the main server and
several workers. A sample supervisord configuration file is in `etc/english-supervisord.conf`.

Server usage
------------

A sample implementation of the client is in `kaldigstserver/client.py`.

If you started the server/worker as described above, you should be able to test the installation by invoking:

    python kaldigstserver/client.py -r 32000 test/data/english_test.raw

Expected output:

    THE. ONE TWO THREE FOUR FIVE SIX SEVEN EIGHT.

Expected output when using using the DNN-based online models based on Fisher:

    one two or three you fall five six seven eight. yeah.

The `-r 32000` in the last command tells the client to send audio to the server at 32000 bytes per second. The raw
sample audio file uses a sample rate of 16k with a 16-bit encoding which results in a byterate of 32000.

You can also send ogg audio:

    python kaldigstserver/client.py -r 4800 test/data/english_test.ogg

The rate in the last command is 4800. The bit rate of the ogg file is 37.5k, which results in a byte rate of 4800.

Using the 'kaldinnet2onlinedecoder' based worker
------------------------------------------------

The DNN-based online decoder requires a newer GStreamer plugin that is not in the Kaldi codebase and has to be compiled
seperately. It's available at https://github.com/alumae/gst-kaldi-nnet2-online. Clone it, e.g., under `~/tools/gst-kaldi-nnet2-online`.
Follow the instuctions and compile it. This should result in a file `~/tools/gst-kaldi-nnet2-online/src/libgstkaldionline2.so`.

Also, download the DNN-based models for English, trained on the TEDLIUM speech corpus and combined with a generic English language model
provided by Cantab Research. Run the `download-tedlium-nnet2.sh` under `test/models` to download the models (attention, 1.5 GB):

    cd test/models
    ./test/models/download-tedlium-nnet2.sh
    cd ../../

Before starting a worker, make sure that the GST plugin path includes the path where the `libgstkaldionline2.so` library you compiled earlier
resides, something like:

    export GST_PLUGIN_PATH=~/tools/gst-kaldi-nnet2-online/src

Test if it worked:

    gst-inspect-1.0 kaldinnet2onlinedecoder

The latter should print out information about the new Kaldi's GStreamer plugin.

Now, you can start a worker:

    python kaldigstserver/worker.py -u ws://localhost:8888/worker/ws/speech -c sample_english_nnet2.yaml

As the acoustic models are trained on TED data, we also test on TED data. The file `test/data/bill_gates-TED.mp3` contains about one
minute of a TED talk by Bill Gates. It's encoded as 64 kb MP3, so let's send it to the server at 64*1024/8=8192 bytes per second:

    python kaldigstserver/client.py -r 8192 test/data/bill_gates-TED.mp3

Recognized words should start appearing at the terminal. The final result should be something like:

> when i was a kid the disaster we worry about most was a nuclear war. that's why we had a bear like this down our basement filled with cans of food and water. nuclear attack came we were supposed to go downstairs hunker down and eat out of that barrel. today the greatest risk of global catastrophe. don't look like this instead it looks like this. if anything kills over ten million people in the next few decades it's most likely to be a highly infectious virus rather than a war. not missiles that microbes now part of the reason for this is that we have invested a huge amount in nuclear deterrence we've actually invested very little in a system to stop an epidemic. we're not ready for the next epidemic.

Compare that to the original transcript in `test/data/bill_gates-TED.txt`:

> When I was a kid, the disaster we worried about most was a nuclear war. That's why we had a barrel like this down in our basement, filled with cans of food and water. When the nuclear attack came, we were supposed to go downstairs, hunker down, and eat out of that barrel.
> Today the greatest risk of global catastrophe doesn't look like this. Instead, it looks like this. If anything kills over 10 million people in the next few decades, it's most likely to be a highly infectious virus rather than a war. Not missiles, but microbes. Now, part of the reason for this is that we've invested a huge amount in nuclear deterrents. But we've actually invested very little in a system to stop an epidemic. We're not ready for the next epidemic.

#### Retrieving and sending adaptation state ####

If you use the 'kaldinnet2onlinedecoder' based worker, you can retrieve the adaptation state after the decoding session
finishes, and send the previously retrieved adaptation state when starting a new session.

The 'kaldinnet2onlinedecoder' worker always sends the adaptation state encoded in a JSON container once the session ends. Client
can store it in a file. This is functionality is implemented by the `client.py`. Assuming that you started the server and a worker as in the last
example, you can do:

    python kaldigstserver/client.py -r 32000 --save-adaptation-state adaptation-state.json test/data/english_test.wav

The `adaptation-state.json` file will contain something like this:

    {"type": "string+gzip+base64", "value": "eJxlvUuPdEmSHbavXx...", "time": "2014-11-14T11:08:49"}

As you can see, the adaptation state is not human-readable, it's actually gzipped and base64-encoded text data.

To start another decoding session using the saved adaptation state, you can do something like this:

    python kaldigstserver/client.py -r 32000 --send-adaptation-state adaptation-state.json test/data/english_test.wav

Alternative usage through a HTTP API
---------------------------------------

One can also use the server through a very simple HTTP-based API. This allows to simply send audio via a PUT or POST request
to http://server:port/client/dynamic/recognize and read the JSON ouput. Note that the JSON output is differently structured
than the output of the websocket-based API. This interface is compatible to the one implemented by http://github.com/alumae/ruby-pocketsphinx-server.

The HTTP API supports chunked transfer encoding which means that server can read and decode an audio stream before it is complete.

Example:

Send audio to server:

     curl  -T test/data/english_test.wav  "http://localhost:8888/client/dynamic/recognize"

Output:

    {"status": 0, "hypotheses": [{"utterance": "one two or three you fall five six seven eight. [noise]."}], "id": "7851281f-e187-4c24-9b58-4f3a5cba3dce"}

Send audio using chunked transfer encoding at an audio byte rate; you can see from the worker logs that decoding starts already when the first chunks
have been received:

    curl -v -T test/data/english_test.raw -H "Content-Type: audio/x-raw-int; rate=16000" --header "Transfer-Encoding: chunked" --limit-rate 32000  "http://localhost:8888/client/dynamic/recognize"

Output (like before):

    {"status": 0, "hypotheses": [{"utterance": "one two or three you fall five six seven eight. yeah."}], "id": "4e4594ee-bdb2-401f-8114-41a541d89eb8"}

Websocket-based client-server protocol
----------------------

### Opening a session

To open a session, connect to the specified server websocket address (e.g. ws://localhost:8888/client/ws/speech).
The server assumes by default that incoming audio is sent using 16 kHz, mono, 16bit little-endian format. This can be overriden
using the 'content-type' request parameter. The content type has to be specified using GStreamer 1.0 caps format,
e.g. to send 44100 Hz mono 16-bit data, use: "audio/x-raw, layout=(string)interleaved, rate=(int)44100, format=(string)S16LE, channels=(int)1".
This needs to be url-encoded of course, so the actual request is something like:

    ws://localhost:8888/client/ws/speech?content-type=audio/x-raw,+layout=(string)interleaved,+rate=(int)44100,+format=(string)S16LE,+channels=(int)1

Audio can also be encoded using any codec recognized by GStreamer (assuming the needed packages are installed on the server).
GStreamer should recognize the container and codec automatically from the stream, you don't have to specify the content type.
E.g., to send audio encoded using the Speex codec in an Ogg container, use the following URL to open the session (server should
automatically recognize the codec):

    ws://localhost:8888/client/ws/speech

### Sending audio

Speech should be sent to the server in raw blocks of data, using the encoding specified when session was opened.
It is recommended that a new block is sent at least 4 times per second (less frequent blocks would increase the recognition lag).
Blocks do not have to be of equal size.

After the last block of speech data, a special 3-byte ANSI-encoded string "EOS"  ("end-of-stream") needs to be sent to the server. This tells the
server that no more speech is coming and the recognition can be finalized.

After sending "EOS", client has to keep the websocket open to receive recognition results from the server. Server
closes the connection itself when all recognition results have been sent to the client.
No more audio can be sent via the same websocket after an "EOS" has been sent. In order to process a new
audio stream, a new websocket connection has to be created by the client.

### Reading results

Server sends recognition results and other information to the client using the JSON format.
The response can contain the following fields:

  * status -- response status (integer), see codes below
  * message -- (optional) status message
  * result -- (optional) recognition result, containing the following fields:
    - hypotheses - recognized words, a list with each item containing the following:
        + transcript -- recognized words
        + confidence -- (optional) confidence of the hypothesis (float, 0..1)
    - final -- true when the hypothesis is final, i.e., doesn't change any more

The following status codes are currently in use:

  * 0 -- Success. Usually used when recognition results are sent
  * 2 -- Aborted. Recognition was aborted for some reason.
  * 1 -- No speech. Sent when the incoming audio contains a large portion of silence or non-speech.
  * 9 -- Not available. Used when all recognizer processes are currently in use and recognition cannot be performed.

Websocket is always closed by the server after sending a non-zero status update.

Examples of server responses:

    {"status": 9}
    {"status": 0, "result": {"hypotheses": [{"transcript": "see on"}], "final": false}}
    {"status": 0, "result": {"hypotheses": [{"transcript": "see on teine lause."}], "final": true}}

Server segments incoming audio on the fly. For each segment, many non-final hypotheses, followed by one final
hypothesis are sent. Non-final hypotheses are used to present partial recognition hypotheses
to the client. A sequence of non-final hypotheses is always followed by a final hypothesis for that segment.
After sending a final hypothesis for a segment,
server starts decoding the next segment, or closes the connection, if all audio sent by the client has been processed.

Client is reponsible for presenting the results to the user in a way
suitable for the application.

Client software
---------------

Javascript client is available here: https://kaljurand.github.io/dictate.js

Citing
------

If you use this software for research, you can cite the paper where this software is
described (available here: http://ebooks.iospress.nl/volumearticle/37996):

    @inproceedigs{alumae2014,
      author={Tanel Alum\"{a}e},
      title="Full-duplex Speech-to-text System for {Estonian}",
      booktitle="Baltic HLT 2014",
      year=2014,
      address="Kaunas, Lithuania"
    }

Of course, you should also acknowledge Kaldi, which does all the hard work.


text:
package com.thoughtworks.go.server.sqlmigration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;

import org.apache.log4j.Logger;
import org.h2.api.Trigger;

/**
 * @understands
 */
public class Migration_230007 implements Trigger {

    private static final Logger LOG = Logger.getLogger(Migration_230007.class);
    private static final int STATE_TRANSITION_TIMESTAMP = 2;
    private static final int STAGE_ID = 4;
    private PreparedStatement statement;

    public void init(Connection connection, String schemaName, String triggerName, String tableName, boolean before, int type) throws SQLException {
        statement = connection.prepareStatement("UPDATE stages "
                                                + "SET lastTransitionedTime = ? "
                                                + "WHERE stages.id = ?");
    }

    public void fire(Connection connection, Object[] oldRows, Object[] newRows) throws SQLException {
        statement.setTimestamp(1, (Timestamp) newRows[STATE_TRANSITION_TIMESTAMP]);
        statement.setLong(2, (Long) newRows[STAGE_ID]);
        statement.addBatch();
        statement.execute();
    }

    public void close() throws SQLException {
    }

    public void remove() throws SQLException {
    }
}

redpajama_set_name: RedPajamaGithub


text:
package com.xiao.weather.model;

/**
 * Created by xiao on 2015/9/25.
 */
public class User {
    /**
     * 用户名字,密码
     */
    private String userName;
    private String userPwd;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPwd() {
        return userPwd;
    }

    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }
}


text:
package cloudbuild

import (
	"context"
	"fmt"
	"github.com/GoogleCloudPlatform/declarative-resource-client-library/dcl"
	dclService "github.com/GoogleCloudPlatform/declarative-resource-client-library/services/google/cloudbuild"
	"github.com/GoogleCloudPlatform/declarative-resource-client-library/unstructured"
)

type WorkerPool struct{}

func WorkerPoolToUnstructured(r *dclService.WorkerPool) *unstructured.Resource {
	u := &unstructured.Resource{
		STV: unstructured.ServiceTypeVersion{
			Service: "cloudbuild",
			Version: "ga",
			Type:    "WorkerPool",
		},
		Object: make(map[string]interface{}),
	}
	if r.Annotations != nil {
		rAnnotations := make(map[string]interface{})
		for k, v := range r.Annotations {
			rAnnotations[k] = v
		}
		u.Object["annotations"] = rAnnotations
	}
	if r.CreateTime != nil {
		u.Object["createTime"] = *r.CreateTime
	}
	if r.DeleteTime != nil {
		u.Object["deleteTime"] = *r.DeleteTime
	}
	if r.DisplayName != nil {
		u.Object["displayName"] = *r.DisplayName
	}
	if r.Etag != nil {
		u.Object["etag"] = *r.Etag
	}
	if r.Location != nil {
		u.Object["location"] = *r.Location
	}
	if r.Name != nil {
		u.Object["name"] = *r.Name
	}
	if r.NetworkConfig != nil && r.NetworkConfig != dclService.EmptyWorkerPoolNetworkConfig {
		rNetworkConfig := make(map[string]interface{})
		if r.NetworkConfig.PeeredNetwork != nil {
			rNetworkConfig["peeredNetwork"] = *r.NetworkConfig.PeeredNetwork
		}
		u.Object["networkConfig"] = rNetworkConfig
	}
	if r.Project != nil {
		u.Object["project"] = *r.Project
	}
	if r.State != nil {
		u.Object["state"] = string(*r.State)
	}
	if r.Uid != nil {
		u.Object["uid"] = *r.Uid
	}
	if r.UpdateTime != nil {
		u.Object["updateTime"] = *r.UpdateTime
	}
	if r.WorkerConfig != nil && r.WorkerConfig != dclService.EmptyWorkerPoolWorkerConfig {
		rWorkerConfig := make(map[string]interface{})
		if r.WorkerConfig.DiskSizeGb != nil {
			rWorkerConfig["diskSizeGb"] = *r.WorkerConfig.DiskSizeGb
		}
		if r.WorkerConfig.MachineType != nil {
			rWorkerConfig["machineType"] = *r.WorkerConfig.MachineType
		}
		if r.WorkerConfig.NoExternalIP != nil {
			rWorkerConfig["noExternalIP"] = *r.WorkerConfig.NoExternalIP
		}
		u.Object["workerConfig"] = rWorkerConfig
	}
	return u
}

func UnstructuredToWorkerPool(u *unstructured.Resource) (*dclService.WorkerPool, error) {
	r := &dclService.WorkerPool{}
	if _, ok := u.Object["annotations"]; ok {
		if rAnnotations, ok := u.Object["annotations"].(map[string]interface{}); ok {
			m := make(map[string]string)
			for k, v := range rAnnotations {
				if s, ok := v.(string); ok {
					m[k] = s
				}
			}
			r.Annotations = m
		} else {
			return nil, fmt.Errorf("r.Annotations: expected map[string]interface{}")
		}
	}
	if _, ok := u.Object["createTime"]; ok {
		if s, ok := u.Object["createTime"].(string); ok {
			r.CreateTime = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.CreateTime: expected string")
		}
	}
	if _, ok := u.Object["deleteTime"]; ok {
		if s, ok := u.Object["deleteTime"].(string); ok {
			r.DeleteTime = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.DeleteTime: expected string")
		}
	}
	if _, ok := u.Object["displayName"]; ok {
		if s, ok := u.Object["displayName"].(string); ok {
			r.DisplayName = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.DisplayName: expected string")
		}
	}
	if _, ok := u.Object["etag"]; ok {
		if s, ok := u.Object["etag"].(string); ok {
			r.Etag = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.Etag: expected string")
		}
	}
	if _, ok := u.Object["location"]; ok {
		if s, ok := u.Object["location"].(string); ok {
			r.Location = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.Location: expected string")
		}
	}
	if _, ok := u.Object["name"]; ok {
		if s, ok := u.Object["name"].(string); ok {
			r.Name = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.Name: expected string")
		}
	}
	if _, ok := u.Object["networkConfig"]; ok {
		if rNetworkConfig, ok := u.Object["networkConfig"].(map[string]interface{}); ok {
			r.NetworkConfig = &dclService.WorkerPoolNetworkConfig{}
			if _, ok := rNetworkConfig["peeredNetwork"]; ok {
				if s, ok := rNetworkConfig["peeredNetwork"].(string); ok {
					r.NetworkConfig.PeeredNetwork = dcl.String(s)
				} else {
					return nil, fmt.Errorf("r.NetworkConfig.PeeredNetwork: expected string")
				}
			}
		} else {
			return nil, fmt.Errorf("r.NetworkConfig: expected map[string]interface{}")
		}
	}
	if _, ok := u.Object["project"]; ok {
		if s, ok := u.Object["project"].(string); ok {
			r.Project = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.Project: expected string")
		}
	}
	if _, ok := u.Object["state"]; ok {
		if s, ok := u.Object["state"].(string); ok {
			r.State = dclService.WorkerPoolStateEnumRef(s)
		} else {
			return nil, fmt.Errorf("r.State: expected string")
		}
	}
	if _, ok := u.Object["uid"]; ok {
		if s, ok := u.Object["uid"].(string); ok {
			r.Uid = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.Uid: expected string")
		}
	}
	if _, ok := u.Object["updateTime"]; ok {
		if s, ok := u.Object["updateTime"].(string); ok {
			r.UpdateTime = dcl.String(s)
		} else {
			return nil, fmt.Errorf("r.UpdateTime: expected string")
		}
	}
	if _, ok := u.Object["workerConfig"]; ok {
		if rWorkerConfig, ok := u.Object["workerConfig"].(map[string]interface{}); ok {
			r.WorkerConfig = &dclService.WorkerPoolWorkerConfig{}
			if _, ok := rWorkerConfig["diskSizeGb"]; ok {
				if i, ok := rWorkerConfig["diskSizeGb"].(int64); ok {
					r.WorkerConfig.DiskSizeGb = dcl.Int64(i)
				} else {
					return nil, fmt.Errorf("r.WorkerConfig.DiskSizeGb: expected int64")
				}
			}
			if _, ok := rWorkerConfig["machineType"]; ok {
				if s, ok := rWorkerConfig["machineType"].(string); ok {
					r.WorkerConfig.MachineType = dcl.String(s)
				} else {
					return nil, fmt.Errorf("r.WorkerConfig.MachineType: expected string")
				}
			}
			if _, ok := rWorkerConfig["noExternalIP"]; ok {
				if b, ok := rWorkerConfig["noExternalIP"].(bool); ok {
					r.WorkerConfig.NoExternalIP = dcl.Bool(b)
				} else {
					return nil, fmt.Errorf("r.WorkerConfig.NoExternalIP: expected bool")
				}
			}
		} else {
			return nil, fmt.Errorf("r.WorkerConfig: expected map[string]interface{}")
		}
	}
	return r, nil
}

func GetWorkerPool(ctx context.Context, config *dcl.Config, u *unstructured.Resource) (*unstructured.Resource, error) {
	c := dclService.NewClient(config)
	r, err := UnstructuredToWorkerPool(u)
	if err != nil {
		return nil, err
	}
	r, err = c.GetWorkerPool(ctx, r)
	if err != nil {
		return nil, err
	}
	return WorkerPoolToUnstructured(r), nil
}

func ListWorkerPool(ctx context.Context, config *dcl.Config, project string, location string) ([]*unstructured.Resource, error) {
	c := dclService.NewClient(config)
	l, err := c.ListWorkerPool(ctx, project, location)
	if err != nil {
		return nil, err
	}
	var resources []*unstructured.Resource
	for {
		for _, r := range l.Items {
			resources = append(resources, WorkerPoolToUnstructured(r))
		}
		if !l.HasNext() {
			break
		}
		if err := l.Next(ctx, c); err != nil {
			return nil, err
		}
	}
	return resources, nil
}

func ApplyWorkerPool(ctx context.Context, config *dcl.Config, u *unstructured.Resource, opts ...dcl.ApplyOption) (*unstructured.Resource, error) {
	c := dclService.NewClient(config)
	r, err := UnstructuredToWorkerPool(u)
	if err != nil {
		return nil, err
	}
	if ush := unstructured.FetchStateHint(opts); ush != nil {
		sh, err := UnstructuredToWorkerPool(ush)
		if err != nil {
			return nil, err
		}
		opts = append(opts, dcl.WithStateHint(sh))
	}
	r, err = c.ApplyWorkerPool(ctx, r, opts...)
	if err != nil {
		return nil, err
	}
	return WorkerPoolToUnstructured(r), nil
}

func WorkerPoolHasDiff(ctx context.Context, config *dcl.Config, u *unstructured.Resource, opts ...dcl.ApplyOption) (bool, error) {
	c := dclService.NewClient(config)
	r, err := UnstructuredToWorkerPool(u)
	if err != nil {
		return false, err
	}
	if ush := unstructured.FetchStateHint(opts); ush != nil {
		sh, err := UnstructuredToWorkerPool(ush)
		if err != nil {
			return false, err
		}
		opts = append(opts, dcl.WithStateHint(sh))
	}
	opts = append(opts, dcl.WithLifecycleParam(dcl.BlockDestruction), dcl.WithLifecycleParam(dcl.BlockCreation), dcl.WithLifecycleParam(dcl.BlockModification))
	_, err = c.ApplyWorkerPool(ctx, r, opts...)
	if err != nil {
		if _, ok := err.(dcl.ApplyInfeasibleError); ok {
			return true, nil
		}
		return false, err
	}
	return false, nil
}

func DeleteWorkerPool(ctx context.Context, config *dcl.Config, u *unstructured.Resource) error {
	c := dclService.NewClient(config)
	r, err := UnstructuredToWorkerPool(u)
	if err != nil {
		return err
	}
	return c.DeleteWorkerPool(ctx, r)
}

func WorkerPoolID(u *unstructured.Resource) (string, error) {
	r, err := UnstructuredToWorkerPool(u)
	if err != nil {
		return "", err
	}
	return r.ID()
}

func (r *WorkerPool) STV() unstructured.ServiceTypeVersion {
	return unstructured.ServiceTypeVersion{
		"cloudbuild",
		"WorkerPool",
		"ga",
	}
}

func (r *WorkerPool) SetPolicyMember(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, member *unstructured.Resource) (*unstructured.Resource, error) {
	return nil, unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) GetPolicyMember(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, role, member string) (*unstructured.Resource, error) {
	return nil, unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) DeletePolicyMember(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, member *unstructured.Resource) error {
	return unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) SetPolicy(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, policy *unstructured.Resource) (*unstructured.Resource, error) {
	return nil, unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) SetPolicyWithEtag(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, policy *unstructured.Resource) (*unstructured.Resource, error) {
	return nil, unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) GetPolicy(ctx context.Context, config *dcl.Config, resource *unstructured.Resource) (*unstructured.Resource, error) {
	return nil, unstructured.ErrNoSuchMethod
}

func (r *WorkerPool) Get(ctx context.Context, config *dcl.Config, resource *unstructured.Resource) (*unstructured.Resource, error) {
	return GetWorkerPool(ctx, config, resource)
}

func (r *WorkerPool) Apply(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, opts ...dcl.ApplyOption) (*unstructured.Resource, error) {
	return ApplyWorkerPool(ctx, config, resource, opts...)
}

func (r *WorkerPool) HasDiff(ctx context.Context, config *dcl.Config, resource *unstructured.Resource, opts ...dcl.ApplyOption) (bool, error) {
	return WorkerPoolHasDiff(ctx, config, resource, opts...)
}

func (r *WorkerPool) Delete(ctx context.Context, config *dcl.Config, resource *unstructured.Resource) error {
	return DeleteWorkerPool(ctx, config, resource)
}

func (r *WorkerPool) ID(resource *unstructured.Resource) (string, error) {
	return WorkerPoolID(resource)
}

func init() {
	unstructured.Register(&WorkerPool{})
}


text:
from oslo_utils import uuidutils

from nova import context
from nova import exception
from nova.objects import cell_mapping
from nova.objects import instance_mapping
from nova import test
from nova.tests import fixtures

sample_mapping = {'instance_uuid': '',
                  'cell_id': 3,
                  'project_id': 'fake-project'}

sample_cell_mapping = {'id': 3,
                       'uuid': '',
                       'name': 'fake-cell',
                       'transport_url': 'rabbit:///',
                       'database_connection': 'mysql:///'}

def create_cell_mapping(**kwargs):
    args = sample_cell_mapping.copy()
    if 'uuid' not in kwargs:
        args['uuid'] = uuidutils.generate_uuid()
    args.update(kwargs)
    ctxt = context.RequestContext('fake-user', 'fake-project')
    return cell_mapping.CellMapping._create_in_db(ctxt, args)

def create_mapping(**kwargs):
    args = sample_mapping.copy()
    if 'instance_uuid' not in kwargs:
        args['instance_uuid'] = uuidutils.generate_uuid()
    args.update(kwargs)
    ctxt = context.RequestContext('fake-user', 'fake-project')
    return instance_mapping.InstanceMapping._create_in_db(ctxt, args)

class InstanceMappingTestCase(test.NoDBTestCase):
    USES_DB_SELF = True

    def setUp(self):
        super(InstanceMappingTestCase, self).setUp()
        self.useFixture(fixtures.Database(database='api'))
        self.context = context.RequestContext('fake-user', 'fake-project')
        self.mapping_obj = instance_mapping.InstanceMapping()

    def test_get_by_instance_uuid(self):
        cell_mapping = create_cell_mapping()
        mapping = create_mapping()
        db_mapping = self.mapping_obj._get_by_instance_uuid_from_db(
                self.context, mapping['instance_uuid'])
        for key in [key for key in self.mapping_obj.fields.keys()
                    if key != 'cell_mapping']:
            self.assertEqual(db_mapping[key], mapping[key])
        self.assertEqual(db_mapping['cell_mapping']['id'], cell_mapping['id'])

    def test_get_by_instance_uuid_not_found(self):
        self.assertRaises(exception.InstanceMappingNotFound,
                self.mapping_obj._get_by_instance_uuid_from_db, self.context,
                uuidutils.generate_uuid())

    def test_save_in_db(self):
        mapping = create_mapping()
        cell_mapping = create_cell_mapping()
        self.mapping_obj._save_in_db(self.context, mapping['instance_uuid'],
                {'cell_id': cell_mapping['id']})
        db_mapping = self.mapping_obj._get_by_instance_uuid_from_db(
                self.context, mapping['instance_uuid'])
        for key in [key for key in self.mapping_obj.fields.keys()
                    if key not in ['cell_id', 'cell_mapping', 'updated_at']]:
            self.assertEqual(db_mapping[key], mapping[key])
        self.assertEqual(db_mapping['cell_id'], cell_mapping['id'])

    def test_destroy_in_db(self):
        mapping = create_mapping()
        self.mapping_obj._get_by_instance_uuid_from_db(self.context,
                mapping['instance_uuid'])
        self.mapping_obj._destroy_in_db(self.context, mapping['instance_uuid'])
        self.assertRaises(exception.InstanceMappingNotFound,
                self.mapping_obj._get_by_instance_uuid_from_db, self.context,
                mapping['instance_uuid'])

    def test_cell_id_nullable(self):
        # Just ensure this doesn't raise
        create_mapping(cell_id=None)

class InstanceMappingListTestCase(test.NoDBTestCase):
    def setUp(self):
        super(InstanceMappingListTestCase, self).setUp()
        self.useFixture(fixtures.Database(database='api'))
        self.context = context.RequestContext('fake-user', 'fake-project')
        self.list_obj = instance_mapping.InstanceMappingList()

    def test_get_by_project_id_from_db(self):
        project_id = 'fake-project'
        mappings = {}
        mapping = create_mapping(project_id=project_id)
        mappings[mapping['instance_uuid']] = mapping
        mapping = create_mapping(project_id=project_id)
        mappings[mapping['instance_uuid']] = mapping

        db_mappings = self.list_obj._get_by_project_id_from_db(
                self.context, project_id)
        for db_mapping in db_mappings:
            mapping = mappings[db_mapping.instance_uuid]
            for key in instance_mapping.InstanceMapping.fields.keys():
                self.assertEqual(db_mapping[key], mapping[key])


text:
package org.butterfaces.component.renderkit.html_basic;

import org.butterfaces.component.html.HtmlMarkdown;
import org.butterfaces.component.partrenderer.MaxLengthPartRenderer;
import org.butterfaces.component.partrenderer.RenderUtils;
import org.butterfaces.component.renderkit.html_basic.text.AbstractHtmlTagRenderer;
import org.butterfaces.util.StringUtils;
import org.butterfaces.component.partrenderer.MaxLengthPartRenderer;
import org.butterfaces.component.partrenderer.RenderUtils;
import org.butterfaces.component.renderkit.html_basic.text.AbstractHtmlTagRenderer;
import org.butterfaces.util.StringUtils;

import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.context.ResponseWriter;
import javax.faces.render.FacesRenderer;
import java.io.IOException;

@FacesRenderer(componentFamily = HtmlMarkdown.COMPONENT_FAMILY, rendererType = HtmlMarkdown.RENDERER_TYPE)
public class MarkdownRenderer extends AbstractHtmlTagRenderer<HtmlMarkdown> {

    @Override
    public void encodeBegin(FacesContext context, UIComponent component) throws IOException {
        super.encodeBegin(context, component, "butter-component-markdown");
    }

    @Override
    protected String getHtmlTagName() {
        return "textarea";
    }

    @Override
    protected void encodeEnd(final HtmlMarkdown markdown, final ResponseWriter writer) throws IOException {
        // Render textarea counter
        new MaxLengthPartRenderer().renderMaxLength(markdown, writer);

        writer.startElement("script", markdown);
        if (!markdown.isReadonly()) {
            writer.writeText(RenderUtils.createJQueryPluginCall(markdown.getClientId(), "textarea", createJQueryMarkdownPluginCall(markdown)), null);
        } else {
            writer.writeText(RenderUtils.createJQueryPluginCall(markdown.getClientId(), null, "markdownReadonly()"), null);
        }
        writer.endElement("script");

        if (StringUtils.isNotEmpty(markdown.getPlaceholder())
                && markdown.getPlaceholder().contains("\\n")
                && !markdown.isReadonly()) {
            RenderUtils.renderJQueryPluginCall(markdown.getClientId(), "multilinePlaceholder()", writer, markdown);
        }
    }

    private String createJQueryMarkdownPluginCall(HtmlMarkdown markdown) {
        final StringBuilder jQueryPluginCall = new StringBuilder();

        jQueryPluginCall.append("markdown({");
        jQueryPluginCall.append("autofocus: false,");
        jQueryPluginCall.append("savable: false,");
        jQueryPluginCall.append("language: '" + markdown.getLanguage() + "'");
        jQueryPluginCall.append("})");

        return jQueryPluginCall.toString();
    }

}


text:
[summary](#summary) | [setup](#setup) | [how to run](#how-to-run)

[.txt](#txt) | [.tsv](#tsv) | [.tab](#tab) | [.csv](#csv) | [.xlsx](#xlsx) | [.json](#json) | [.yaml](#yaml) | [.html](#html) | [.xml](#xml)

[plain text](#plaintext) | [encodings](#encodings) | [newlines](#newlines) | [relational formats](#relational-fmt) | [joins](#joins) | [keys](#keys) | [hierarchical formats](#hierarchical-fmt)

# SUMMARY

Command line tools for data extraction, data manipulation, and file format conversion.

    check-tsv              verify rows in TSV file are same length

    convert-date           convert dates in tabular data using strftime-style formats

    counting-sort          sort a file using counting sort

    csv-to-json            convert CSV to JSON

    csv-to-postgres        import a CSV file into a PostgreSQL table

    csv-to-tab             convert CSV to tab delimited

    csv-to-xlsx            convert CSV files to XLSX worksheets

    date-fill              fill in missing rows in a TSV file with a time series column

    date-seq               create a sequence of dates

    header-sort            sort file, keeping header in place

    highlight              highlight text matching REGEX

    html-table-to-csv      extract table content from HTML file as CSV

    join-tsv               perform a relation join on two TSV files

    json-pluck             convert JSON array to JSON stream

    json-diff              show differences between two JSON documents

    normalize-utf8         write UTF-8 encoded input to standard out in normalized form

    postgres-to-csv        write a PostgreSQL table to stdout in CSV format

    reservoir-sample       select N lines from standard input randomly

    set-diff               find lines in first file which are not in the second

    set-intersect          find lines common to two files

    tab-to-csv             convert tab delimited file to CSV

    tokenize               extract words from English language text

    trim-tsv               trim whitespace from fields of TSV file

    tsv-header             show TSV header with ordinal position of each column

    tsv-to-json            convert TSV to JSON

    utf8-category          tally UTF-8 encoded characters by general category

    utf8-script            tally UTF-8 encoded characters by script

    utf8-viewer            display Unicode points and optionally names of UTF-8 encoded file

    xls-to-csv             convert XLS to CSV

    xlsx-to-csv            convert XLSX to CSV

    yaml-to-json           convert YAML to JSON

The *data tools* are for working with data at the command line.  They are meant to complement the tools you already have.  Use `help SUBCOMMAND` to see the help page for a *data tool* or browse the help pages on [GitHub](https://github.com/clarkgrubb/data-tools/tree/master/doc).

Command line tools are composable when the output of one command can be the input of another.  The output can be redirected to a file whose path is passed as an argument, or the commands can be connected by a shell pipe.  Use of a pipe is *tacit programming*: it relieves the programmer of the need to name a file.  Furthermore the byte stream is private to the commands on either side of the pipe.

Only tools which read from standard input or write to standard output can participate in a pipeline.  Tools in a pipeline must agree on the *format* of the data in the byte stream.  The *data tools* support these formats: `.txt`, `.tsv`, `.tab`, `.csv`, `.xls`, `.xlsx`, `.json`, `.yaml`, `.html`, and `.xml`.  Some of the *data tools* are *format conversion tools* to be used to convert from one format to another.

# SETUP

`python3`, `pip3` and `gcc` are required.

To install Python and Bash scripts:

    $ ./setup.py sdist
    $ pip3 install dist/data-tools-0.1.0.tar.gz

To install C tools:

    $ make install.c

To install man pages:

    $ make install.man

# HOW TO RUN

    check-tsv              [TSV_FILE]

    convert-date           [-i FMT] [-o FMT] [-c COLUMN] [-H]

    counting-sort          [FILE]

    csv-to-json            [-d DELIMITER] [-q QUOTECHAR] [CSV_FILE]

    csv-to-postgres        -f CSV_PATH -t TABLE [-d DB] [-h HOST] [-p PORT] [-U USER] [-w|-W]

    csv-to-tab             [-e|-x|-r] [CSV_FILE]

    csv-to-xlsx            -o XLSX_FILE CSV_FILE ...

    date-fill              --date-column NUM --format FORMAT

    date-seq               [--format=FMT] [--weekdays=DAY[,DAY]...] YYYY[MM[DD[HH]]] YYYY[MM[DD[HH]]]

    header-sort            [OPTIONS] FILE

    highlight              REGEX [FILE]

    highlight              (--red|--green|--yellow|--blue|--magenta|--cyan)=REGEX ... [FILE]

    html-table-to-csv      [-t TABLE_NUM] [FILE]

    join-tsv               -c NAME [-l|-r|-f] [-n VALUE] TSV_FILE1 TSV_FILE2

    json-pluck             < FILE

    json-diff              [DIFF_OPTIONS] JSON_FILE1 JSON_FILE2

    normalize-utf8         [--nfc|--nfd|--nfkc|--nfkd] [FILE]

    postgres-to-csv        -t TABLE [-d DB] [-h HOST] [-p PORT] [-U USER] [-w|-W]

    reservoir-sample       [-r SEED] -s NUM [FILE]

    set-diff               FILE1 FILE2

    set-intersect          FILE1 FILE2

    tab-to-csv             [-u] [TAB_DELIMITED_FILE]

    tokenize               [-n]

    trim-tsv               [TSV_FILE]

    tsv-header             [TSV_FILE]

    tsv-to-json            [TSV_FILE]

    utf8-category          [-l|--long-names] [-c|--count-ascii|-s|--skip-ascii]

    utf8-script            [-c|--count-ascii|-s|--skip-ascii]

    utf8-viewer            [-b|-c|-n] [-w NUM] [FILE]

    utf8-viewer            [-b|-c|-n] -a BYTE ...

    xls-to-csv             <same as xlsx-to-csv>

    xlsx-to-csv            [--date-format=DATE_FMT] XLSX_FILE DIRECTORY

    xlsx-to-csv            [--date-format=DATE_FMT] --sheet=SHEET XLSX_FILE [OUTPUT_FILE]

    xlsx-to-csv            --list XLSX_FILE

    yaml-to-json           [FILE]

# PLAIN TEXT: .TXT

*Plain text* is a sequence of bytes which use an encoding to represent printable characters.

If no other suffix is appropriate, `.txt` is a reasonable suffix for a plain text file.

In plain text, control characters other than for line endings and perhaps tabs are disallowed.  Long lines are discouraged.

# ENCODINGS

[iconv](#iconv) | [bad bytes](#bad-bytes) | [utf-8](#utf-8) | [utf-16](#utf-16) | [unicode](#unicode)

## iconv

The *data tools* expect and produce UTF-8 encoded data.  8-bit encoded ASCII is valid UTF-8.

Use `iconv` to convert a file in a different encoding:

    $ iconv -t UTF-8 -f UTF-16 foo.utf16.txt > foo.utf8.txt

To get a list of supported encodings:

    $ iconv -l

## bad bytes

Not all sequences of bytes are valid UTF-8; the *data tools* throw exceptions when invalid bytes are encountered.  A drastic way to deal with the problem is to strip the invalid bytes:

    $ iconv -c -f UTF-8 -t UTF-8 < INPUT_FILE > OUTPUT_FILE

This command strips all non-ASCII characters:

    $ iconv -c -f UTF-8 -t ASCII < INPUT_FILE > OUTPUT_FILE

One can try to transliterate the non-ASCII characters:

    $ iconv -f UTF-8 -t ASCII//TRANSLIT < INPUT_FILE > OUTPUT_FILE

Here is a way to find non-ASCII bytes:

    $ grep --color='auto' -P -n "[\x80-\xFF]+"

The `-P` option is not provided by the version of `grep` distributed with Mac OS X.  One can use the `highlight` command in this repo:

    $ highlight '[\x80-\xFF]+'

To find the first occurrence of bytes which are not valid UTF-8, use `iconv`:

    $ iconv -f utf-8 -t utf-8 < /bin/ls > /dev/null
    iconv: illegal input sequence at position 24

`utf8-viewer` will render invalid UTF-8 bytes with black squares.  The black square is itself a Unicode character (U+25A0), so there is ambiguity.  The Unicode points are displayed next to the rendered characters, however, and the point will be ---- for invalid characters.

    $ utf8-viewer /bin/ls

When a file is in an unknown encoding, one can inspect it byte-by-byte.
`od -b` displays the bytes in octal:

    $ od -b /bin/ls

`od -b` is an unequivocal way to look at the data.  It removes the confusion caused by the character encoding assumed by the display.  On the other hand it is difficult to make sense of octal bytes.

If some of the bytes in a file are ASCII, such as when the encoding is one of the many 8-bit extensions of ASCII, then `od -c` will display the file in an unequivocal yet easier-to-interpret way:

    $ ruby -e '(0..255).each { |i| print i.chr }' | iconv -f mac -t utf8 | od -c

`od -c` uses C backslash sequences or octal bytes for non-ASCII and non-printing ASCII characters.

`xxd` displays the data in rows of 16 bytes.  Each row is displayed in 3 columns.  The first column is the hex offset of the first byte in the row, the second column is the bytes in hex, and third column is the ASCII characters for the bytes, with a period `.` standing in for control characters and upper 8-bit bytes.  The `-c` flag changes the number of bytes per row:

    $ xxd /bin/ls
    $ xxd -c 32 /bin/ls

The `-i` flag will convert the data to a C source literal.  The `-r` flag will convert the output of `xxd` back to the original binary format:

    $ xxd -i /bin/ls
    $ xxd /bin/ls | xxd -r

The `-s` flag and the `-l` flag specify the start byte and the total number of bytes to display:

    $ xxd -s 10 -l 20 /bin/ls

Another way to pick out bytes from a file is `dd`:

    $ dd bs=1 iseek=10 count=20 if=/etc/passwd 2> /dev/null

`cat -te` uses a unique escape sequence for each byte, but unlike `od`, it does not display
a fixed number of bytes per line; the mapping from input to output is not injective.  Still, since it doesn't introduce line breaks at regular intervals, it may be easier to interpret.  An example:

    $ ruby -e '(0..255).each { |i| print i.chr }' | iconv -f mac -t utf8  | cat -te

`cat -t` renders printable ASCII and newlines; it uses `^` notation for other control characters.  Some versions of `cat -t`
use Emacs style `M-X` notation for upper 8-bit bytes.  In this case, `X` will be what `cat -t` would have used to render
the character if the upper bit were zero, with the exception of `^J` being used for newline.

The Ruby interpreter can be pressed into service as a tool for performing base conversion:

    $ ruby -e 'puts "316".to_i(8).to_s(16)'
    ce

The `bc` calculator can also be used:

    $ echo $'obase=16\n\nibase=8\n316' | bc
    CE

## utf-8

The `utf8-viewer` *data tool* provides an easy way to determine the Unicode points of a sequence of UTF-8 bytes.

    $ utf8-viewer foo.txt

If you want to see the character for a Unicode point, use `printf`:

    $ printf '\u03bb\n'

`zsh` and recent versions of `bash` honor the `\u` backslash escape sequence in `$' '` strings:

    $ echo $'\u03bb'

If you have access to `python` or `ruby`:

    $ python -c 'print(u"\u03bb")'

    $ ruby -e 'puts "\u03bb"'

The *data tools* provide `utf8-category` and `utf8-script`, which summarize the characters by general category and script:

    echo 'It is greater than ∞!' | utf8-category -l
    1	Control
    14	Lowercase_Letter
    1	Uppercase_Letter
    5	Other_Punctuation
    1	Math_Symbol

    $ echo 'αλφα βητα foo bar' | utf8-script
    4	Common
    8	Greek
    6	Latin

Both tools have `-c` and `-s` flags for counting ASCII characters separately or omitting them from the tally entirely.

## utf-16

Unicode points above `U+FFFF` are represented in UTF-16 by a pair of 16-bit characters called _surrogates_:

    $ echo -n 𝒷 | iconv -f utf-8 -t utf-16 | xxd
    0000000: feff d835 dcb7

The first 16-bit character is the byte order mark (BOM).  The second 16-bit character is the _high surrogate_, and the third 16-bit character is the _low surrogate_.  A high surrogate is in the range  0xD800 to 0xDBFF, and a low surrogate is in the range 0xDC00 to 0xDFFF.

In some programming languages, one must use surrogates to insert a point from the supplementary planes in a string literal:

    $ scala
    scala> "\ud835\udcb7"
    res0: String = 𝒷

Here is Python code describing the conversion from surrogates to code point and back:

    def to_high_and_low_surrogate(n):
        assert(0xFFFF < n <= 0x10FFFF)
        high = 0xD800 + (((n - 0x10000) >> 10) & 0x3FF)
        low = 0xDC00 + ((n - 0x10000) & 0x3FF)
        return high, low

    def to_code_point(high, low):
        assert(0xD800 <= high < 0xDC00)
        assert(0xDC00 <= low <= 0xDFFF)
        return (high - 0xD800) * 0x400 + (low - 0xDC00) + 0x10000

## unicode

How to look up a Unicode point:

    $ curl ftp://ftp.unicode.org/Public/UNIDATA/UnicodeData.txt > /tmp/UnicodeData.txt

    $ awk -F';' '$1 == "03BB"' /tmp/UnicodeData.txt
    03BB;GREEK SMALL LETTER LAMDA;Ll;0;L;;;;;N;GREEK SMALL LETTER LAMBDA;;039B;;039B

`UnicodeData.txt` is a useful file and possibly deserves a dedicated path on the file system.

The first three fields are "Point", "Name", and "[General Category](http://www.unicode.org/reports/tr44/#General_Category_Values)".

Unicode contains all the characters one is likely to need, but writing code which handles the entire Unicode character
set correctly is sometimes impractical.  One might opt to reject characters which are not needed instead.  A character
frequency table such as used when breaking ciphers is useful in this context:

    $ cat /etc/passwd |  ruby -ne '$_.split("").each { |ch| puts "#{ch} #{ch.ord}" }' | sort | uniq -c | sort -nr

Unicode contains different character sequences which are
rendered the same way.  An example is SMALL LETTER C WITH CEDILLA `ç`,
which can be represented as a single character: U+00E7 or as SMALL LETTER C
followed by COMBINING CEDILLA: U+0063 U+0327.

When performing a string comparison, the two sequences should often
be regarded as identifical.  The easiest way to accomplish this is to put
the strings to be compared into a normalized form.  The Unicode standard defines
[four normal forms](http://unicode.org/reports/tr15/).  The *data tool* `normalize-utf8` can be used to put a UTF-8 encoded file or stream into any of them.

# NEWLINES

[eol markers](#eol-markers) | [set operations](#set-op) | [highlighting](#highlighting) | [sequences](#seq) | [sampling](#sampling)

## eol markers

The *data tools* interpret LF, CRLF, or CR as end-of-line markers in input.  The *data tools* use LF as the end-of-line marker in output.  To convert LF line endings to CRLF or CR line endings:

    $sed 's/$'"/$(echo \\\r)/"

    $ tr '\n' '\r'

To convert CRLF or CR line endings to LF line endings:

    $ tr -d '\r'

    $ tr '\r' '\n'

For LF to CRLF conversions, another option is the following tools (which might need to be installed, see if your package manager has `dos2unix`).  These tools take paths as arguments and modify the files in place:

    dos2unix
    unix2dos

The Unicode Consortium provides a [complete list](http://www.unicode.org/standard/reports/tr13/tr13-5.html) of Unicode characters that might be treated as EOL markers.  In a line-delimited file format these characters should be escaped or removed.

The terminal wraps long lines without any indication that it has done so.  The `cut` command and the environment variable `COLUMNS` can be used to truncate long lines instead:

    $ cut -c 1-$COLUMNS FILE
    $ cut -c $(( $COLUMNS + 1 ))-$(( 2 * $COLUMNS )) FILE

## set operations

*Data tools* are provided for finding the lines which two files share in common, or which are exclusive to the first file:

    $ set-intersect FILE1 FILE2
    $ set-diff FILE1 FILE2

The `cat` command can be used to find the union of two files, with an optional `sort -u` to remove duplicate lines:

    $ cat FILE1 FILE2 | sort -u

## highlighting

When inspecting files at the command line, `grep` and `less` are invaluable.  `grep` can highlight the search pattern in red:

    $ grep --color=always root /etc/passwd

The `highlight` command does the same thing, except that it also prints lines which don't match
the pattern.  Also it supports multiple patterns, each with its own color:

    $ highlight --red root --green daemon --blue /bin/bash /etc/passwd

Both `grep` and `highlight` use [ANSI Escapes](http://www.ecma-international.org/publications/standards/Ecma-048.htm).  If you are paging through the output, use `less -R` to render the escape sequences correctly.

## sequences

The `seq` command can generate a newline delimited arithmetic sequence:

    $ seq 1 3
    1
    2
    3

Zero-padded:

    $ seq -w 08 11
    08
    09
    10
    11

Step values other than one:

    $ seq 1 .5 2
    1
    1.5
    2

The `seq` is useful in conjunction with a shell `for` loop.  This will create a hundred empty files:

    $ for i in $(seq -w 1 100); do touch foo.$i; done

It is also useful at times to be able to iterate through a sequence of dates.  The *data tools* provide `date-seq` for this.  For example, suppose that you wanted to fetch a set of URLs which contained a date:

    $ for date in $(date-seq --format='%Y/%m/%d' 20130101 20130131)
    > do mkdir -p $date
    > curl "http://blog.foo.com/${date}" > ${date}/index.html
    > done

`date-seq` can iterate though years, months, days, hours, minutes, or seconds.  When iterating through days, the `--weekdays` flag can be used to specify days of the week.  See the [man page](https://github.com/clarkgrubb/data-tools/blob/master/doc/date-seq.1.md) for details.

## sampling

It is desirable at times to take a random sample of lines from a file.  Simply taking the first *N* lines often does not yield a representative sample.  Instead one should shuffle the file first:

    $ sort -R foo.txt | head -3

On large files, randomly shuffling a file is slow.  Also, the `sort` installed on Mac OS X does not have the `-R` flag.  One can use `awk` to select a random percentage of lines from a file:

    $ awk 'rand() < 0.01' foo.txt

This is faster than shuffling the file, but does not produce a precise sample size, even if you know the number of lines in the file.

An efficient and unbiased way to select an exact number of lines from a file is to use reservoir sampling.  The *data tool* `reservoir-sample` implements it:

    $ reservoir-sample --size 3 < /etc/passwd

# TSV, TAB, CSV, and XLSX

# RELATIONAL FORMATS

[tsv](#tsv) | [csv](#csv) | [xlsx](#xlsx)

Much that can be done with a SQL SELECT statement in a database can also be done with `awk`, `sort`, and `join`.

Relational data can be stored in flat files in a variety of ways.  On Unix, the `/etc/passwd` file stores records one per line, with colons (:) separating the seven fields.  We can use `awk` to query the file.

Get the root entry from `/etc/passwd`:

    $ awk -F: '$1 == "root"' /etc/passwd

Count the number of users by their login shell:

    $ awk -F: '{cnt[$7] += 1} END {for (sh in cnt) print sh, cnt[sh]}' /etc/passwd

The `/etc/passwd` file format, though venerable, has an ad hoc flavor.  In the following sections we consider three formats which are widely used for relational data.

## tsv

The IANA, which is responsible for registering MIME types, has a [specification for TSV](http://www.iana.org/assignments/media-types/text/tab-separated-values).  Records are newline delimited and fields are tab-delimited.  There is no mechanism for escaping or quoting tabs and newlines.  Despite this limitation, we prefer to convert the other formats to TSV because `awk`, `sort`, and `join` cannot easily manipulate the other formats.  By default Hadoop uses tabs as a field separator.

Trailing spaces in fields can be hidden by tabs, causing joins to mysteriously fail.  `cat -te` can be used to expose trailing spaces.  The *data tool* `trim-tsv` can be used to clean up a TSV file.

The fact that tabs are visually identical to spaces means that in many applications they *can* be replaced by spaces.  This makes tabs available for delimiting fields.  One could use a non-printing character, but most applications do not display non-printing characters well: inspecting the data is harder.

Here is how to align the columns of a tab delimited file:

    $ tr ':' '\t' < /etc/passwd | column -t -s $'\t'

The default field separator for `awk` is whitespace.  The correct way to use `awk` on a TSV is like this:

    $ awk 'BEGIN {FS="\t"; OFS="\t"} ...'

The IANA spec says that a TSV file must have a header.  Self-describing data is a good practice.  On the other hand the header is at times inconvenient—when sorting the file, for example.  The repo provides the `header-sort` command to sort a file while keeping the header in place.  When we must remove the header, we label the file with a `.tab` suffix instead of a `.tsv` suffix.

Even if a file has a header, `awk` scripts must refer to columns by number instead of name.  The following code displays the header names with their numbers:

    $ head -1 foo.tsv | tr '\t' '\n' | nl

Python and similar languages have a `split` method which is ideal for parsing a TSV file:

    with open(path) as f:
        header = f.readline().rstrip('\r\n').split('\t')
        for line in f:
            fields = line.rstrip('\r\n').split('\t')
                ...

CSV libraries are sometimes used to read TSV files.  This works when the delimiter can be changed from a comma to a tab.  The practice is incorrect if the library does not also allow the quote character to be set to none.

The `join` method in Python and similar languages can be used to generate a TSV file.  Remember to check for prohibited characters in the data:

    import re

    RX_PROHIBITED = re.compile(u'[\f\n\r\t\v\x85\u2028\u2029]')

    def tsv_replace(field, replace_char=' '):
        return RX_PROHIBITED.sub(replace_char, field)

    with open(path, 'w') as f:
        for row in rows:
            f.write(u'\t'.join([tsv_replace(field) for field in row]))
            f.write(u'\n')

## csv

The CSV format is described in [RFC 4180](http://www.ietf.org/rfc/rfc4180.txt).

Note that CSV files do not necessarily have headers.  This is perhaps because CSV files are an export format for spreadsheets.

RFC 4180 defines the EOL marker as CRLF.  The *data tools* use LF as the EOL marker, however.  If you want to conform to the spec, run the output through `unix2dos`.  Also note that the final CRLF is optional.

CSV provides a mechanism for quoting commas and EOL markers.  Double quotes are used, and double quotes themselves are escaped by doubling them.

The *data tools* repo provides utilities for converting between TSV and CSV:

    csv-to-tab
    tab-to-csv

Converting from CSV to TSV is problematic if the fields contain tabs or newlines.  By default `csv-to-tab` will fail if it encounters any.  There are flags to tell `csv-to-tab` to strip, backslash escape, replace with space, or replace with space and squeeze.   See the [man page](https://github.com/clarkgrubb/data-tools/blob/master/doc/csv-to-tab.1.md).

## xlsx

XLSX is the default format used by Excel since 2007.  Other spreadsheet applications can read it.

XLSX is a ZIP archive of mostly XML files.  The `unzip -l` command can be used to list the contents of an XLSX file.

Excel provides the ability to export data in a CSV or TSV format.  One exports by choosing the format when saving the workbook.  The CSV formats all use 8-bit encodings and are not recommended since Excel spreadsheets can contain Unicode data.  To export as TSV, look for the "Unicode Text" or "UTF-16 Unicode Text" option.  The file suffix will be `.txt`.  The character encoding is UTF-16 and can be converted using `iconv`:

    $ iconv -f utf-16 -t utf-8 < foo.txt > foo.tsv

Using Excel to export the data requires having Excel, which is not free.  Also Excel must be run in a desktop environment and is difficult to automate.  The *data tools* include the script `xslx-to-csv` so the operation can be performed at the command line.  To extract the sheets from a workbook as CSV files, run this:

    $ xlsx-to-csv WORKBOOK.xlsx OUTPUT_DIR

The directory OUTPUT_DIR will be created and must not already exist.

One can list the sheet names and extract a single sheet to a CSV file:

    $ xlsx-to-csv --list WORKBOOK.xlsx

    $ xlsx-to-csv --sheet=SHEET WORKBOOK.xlsx SHEET.csv

By default dates are written in `%Y-%m-%dT%H:%M:%S` format.  This can be change using the `--date-format` flag.  See `man strftime` for instructions on how to specify a date format.

The tool `xls-to-csv` is available for converting the older (pre 2007) Excel spreadsheet to CSV.  It has the same interface as `xlsx-to-csv`.

The tool `csv-to-xlsx` is available for creating XLSX workbooks.  Each CSV file on the command line becomes a worksheet in the workbook.  The worksheet names are derived from the CSV file names; see the man page for details.

Importing UTF-8 encoded data into Excel is not effortless. What I have found to work is to convert the data to a tab delimited `.tab` format, but change the suffix to `.txt` since otherwise Excel will not allow the path to be selected.  Then use `File | Import...` and select `Text file`.  After the file path is selected, Excel drops the user into a wizard which allows the format of the file to be specified. The default file origin on Macintosh is `Macintosh`, which is an 8-bit encoding.  Change it to `Unicode (UTF-8)`.  Select `Delimited`.  One the second screen, set `Delimiters` to `Tab`, and `Text qualifier`, which controls to quote character, to `{none}`.  The optional third screen allows the user to set the date formats of the columns.

# JOINS

[tab](#join-tab) | [tsv](#join-tsv) | [sqlite](#sqlite) | [postgres](#postgres) | [r](#join-r) | [pandas](#join-pandas) | [hive](#hive) | [spark](#spark)

## tab

To illustrate joining at the command line we create some tab delimited files:

    $ grep -v '^#' /etc/passwd | tr ':' '\t' > /tmp/pw.tab

    $ grep -v '^#' /etc/group | tr ':' '\t' > /tmp/grp.tab

Here is an example of using `sort` and `join` to join by group id:

    $ sort -t $'\t' -k 4,4 /tmp/pw.tab > /tmp/pw.sort.tab

    $ sort -t $'\t' -k 3,3 /tmp/grp.tab > /tmp/grp.sort.tab

    $ join -t $'\t' -1 4 -2 3 /tmp/pw.sort.tab /tmp/grp.sort.tab

This is tedious because (1) each file must be sorted by the join column, (2) the field delimiter must be specified for each invocation of `sort` and `join`, and (3) the join column index must be determined and specified.

## tsv

`sort` and `join` don't handle files with headers correctly.  Since TSV files have headers, the *data tools* include a `join-tsv` command.

To illustrate using `join-tsv` let's create some TSV files:

    $ ( echo $'name\tpw\tuid\tgid\tgecos\thome\tshell';  grep -v '^#' /etc/passwd | tr ':' '\t' ) > /tmp/pw.tsv

    $ ( echo $'name\tpw\tgid\tlist';  grep -v '^#' /etc/group | tr ':' '\t' ) > /tmp/grp.tsv

If the join column has the same name in both files, it can be specified with the `-c` or `--column` flag:

    $ join-tsv --column=gid /tmp/pw.tsv /tmp/grp.tsv

The output is in TSV format, and in particular it has a header.  The order of columns is (1) join column, (2) left file columns other than the join column, (3) right file columns other than the join column.  If the join column has different names in the two files, the left name is used in the output.

`join-tsv` reads the smaller of the two files into memory.

`join-tsv` treats an empty string as the null value by default.  It can perform left, right, or full outer joins.  See the  [man page](https://github.com/clarkgrubb/data-tools/blob/master/doc/join-tsv.1.md) for details.

## sqlite

Using SQLite to perform a join:

    $ sqlite3

    > create table pw ( name text, pw text, uid int, gid int, gecos text, home text, shell text );
    > create table grp ( name text, pw text, gid int, list text );
    > .separator \t
    > .import /tmp/pw.tab pw
    > .import /tmp/grp.tab grp
    > .mode csv
    > .output /tmp/pw_grp.csv
    > select * from pw join grp on pw.gid = grp.gid;

There is no way to escape the separator when importing files into SQLite.

## postgres

    $ tab-to-csv < /tmp/pw.tab > /tmp/pw.csv
    $ tab-to-csv < /tmp/grp.tab > /tmp/grp.csv

    $ psql
    > create table pw ( name text, pw text, uid int, gid int, gecos text, home text, shell text );
    > create table grp ( name text, pw text, gid int, list text );

    $ ( echo 'copy pw from stdin with (format csv); '; cat /tmp/pw.csv ) | psql
    $ ( echo 'copy grp from stdin with (format csv); '; cat /tmp/grp.csv ) | psql

    $ psql
    > create table pw_grp as select pw.name as pw_name, grp.name as grp_name from pw join grp on pw.gid = grp.gid;

    $ echo 'copy pw_grp to stdout with (format csv);' | psql > /tmp/pw_grp.csv

## r

Using R to perform a join:

    $ /usr/bin/r

    > pw = read.delim('/tmp/pw.tsv', quote='')

    > grp = read.delim('/tmp/grp.tsv', quote='')

    > j = merge(pw, grp, by.x='gid', by.y='gid')

    > write.table(j, '/tmp/pw_grp.tsv', row.names=F, sep='\t', quote=F)

## pandas

Using the Python library *pandas* to perform a join:

    $ python

    > import pandas as pd

    > pw = pd.read_table('/tmp/pw.tsv')

    > grp = pd.read_table('/tmp/grp.tsv')

    > j = pd.merge(pw, grp, left_on='gid', right_on='gid')

    > j.to_csv('/tmp/pw_grp.tsv', sep='\t', index=False)

## hive

[Hive functions](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode)

The native format of Hive is to use `^A` (`%x01`) as field delimiters and newlines as row delimiters.

    $ hive -hiveconf mapred.job.tracker=local -hiveconf fs.default.name=file:///tmp -hiveconf hive.metastore.warehouse.dir=file:///tmp/test_hive

    > create table passwd ( user string, passwd string, uid int, gid int, gecos string, home string, shell string ) row format delimited fields terminated by '\t' stored as textfile;
    > load data local inpath '/tmp/pw.tab' overwrite into table passwd;
    > create table group ( group string, passwd string, gid int, members string ) row format delimited fields terminated by '\t' stored as textfile;
    > load data local inpath '/tmp/grp.tab' overwrite into table group;
    > insert overwrite local directory '/tmp/pw_grp' row format delimited fields terminated by '\t' select * from passwd p join group g on p.gid = g.gid;

Suppose that we have data in a file which is not in first normal form.  Here the 4th column contains the name of the children, separated by pipes:

    $ cat /tmp/families.tab
    Simpson	Homer	Marge	Bart|Lisa|Maggie
    Cleaver	Ward	June	Wally|Beaver

We can use Hive to normalize the data:

    > create table families (surname string, father string, mother string, children string) row format delimited fields terminated by '\t' stored as textfile;
    > load data local inpath '/tmp/families.tab' overwrite into table families;
    > create table families2 as select surname, father, mother, split(children, '\\|') as children from families;
    > select surname, father, mother, child from families2 lateral view explode(children) foo as child;

The result is the table:

    Simpson	Homer	Marge	Bart
    Simpson	Homer	Marge	Lisa
    Simpson	Homer	Marge	Maggie
    Cleaver	Ward	June	Wally
    Cleaver	Ward	June	Beaver

## spark

    $ spark-shell
    > val pw = sc.textFile("/etc/passwd").filter(line => line(0) != '#').map(line => line.split(":"))
    > val grp = sc.textFile("/etc/group").filter(line => line(0) != '#').map(line => line.split(":"))
    > val j = pw_gid.join(grp_gid).map(tup => List(tup._1) ++ tup._2._1 ++ tup._2._1)
    > j.map(row => row.mkString("\t")).saveAsTextFile("/tmp/pw_grp")

# KEYS

A *candidate key* is a minimal set of columns which can be used to uniquely identify rows.  A primary key is a candidate key, and other candidate keys can be declared using a uniqueness constraint.  When a candidate key is declared the database rejects inserts and updates that would violate the uniqueness constraint.

Candidate keys are a property of the data; they aren't necessarily declared in the schema.  To verify a candidate key, one checks whether the number of rows in table is the same as the number of distinct values of a column or a set of columns:

    > SELECT COUNT(*) FROM customers;
    > SELECT COUNT(DISTINCT name) FROM customers;
    > SELECT COUNT(*) FROM (SELECT DISTNCT first_name, last_name FROM customers);

Strictly speaking, one should also verify that no proper subset of the columns is also a candidate key.

Usually a join condition is a test of equality between one or more columns from the left relation and the same number of columns from the right relation.

    > SELECT c.name, sum(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

Usually, the columns should be a candidate key for either the left or the right relation.  Consider the following perverse query:

    > SELECT c.name, sum(o.amount) FROM customers c JOIN orders o ON c.name = o.customer_name GROUP BY c.name;

If there were _n_ customers with the same name, then the amount associated with their common name would be _n times_ the sum of their orders.  Incidentally, keeping the name of the customer in the orders relation is a violation of second normal form if a unique identifier for the customer is already in the orders table.

# HIERARCHICAL FORMATS

[json](#json) | [yaml](#yaml) | [html](#html) | [xml](#xml)

## json

[json.org](http://json.org/)

The MongoDB export format is a file of serialized JSON objects, one per line.  Whitespace can be added or removed anywhere to a serialized JSON object without changing the data the JSON object represents (except inside strings, and newlines must be escaped in strings).  Thus it is always possible to write a JSON object on a single line.

It is easier for most clients to process a large data set in the MongoDB export format, e.g.

    {"foo": 1}
    {"bar": 2}
    {"baz": 3}
    ...

than to process a large JSON array, e.g.

    [{"foo":1},{"bar":2},{"baz":3},...]

This is because the latter format forces most clients to read the entire data set into memory.  The *data tools* repo contains a utility for dealing with a large JSON array:

    json-pluck < big_array.json > mongo_format.json

The following *data tools* are provided to convert CSV or TSV files to the MongoDB export format.  In the case of `csv-to-json`, the CSV file must have a header:

    csv-to-json
    tsv-to-json

`python -mjson.tool` can be used to pretty print JSON and test whether the JSON is well formed.

    $ echo '{"foo": 1, "bar": 2, "baz": [1, 2, 3]}' | python -mjson.tool
    {
        "bar": 2,
        "baz": [
            1,
            2,
            3
        ],
        "foo": 1
    }

Other tools for pretty printing JSON are `jq` and `json` which can be installed using the operating system package manager and `npm`, respectively:

    $ echo '{"foo": 1, "bar": 2, "baz": [1, 2, 3]}' | jq '.'

    $ echo '{"foo": 1, "bar": 2, "baz": [1, 2, 3]}' | json

The `json-diff` script uses `python -mjson.tool` and `diff` to compare two JSON documents.

The *data tools* utility `json-ruby` can be used to convert JSON to TSV.

    $ json-ruby 'BEGIN{ puts ["foo", "bar", "baz"].join("\t")}; puts [$_["foo"], $_["bar"], $_["baz"]].join("\t")' < dump.json

The script passed to `json-ruby` is Ruby.  The JSON is parsed, and the data is stored in the `$_` variable.  If the input is a MongoDB style export with one JSON object per line, then `json-ruby` iterates over the file in an awk-like manner, setting the `$_` variable to each object in turn.

An alternative to `python -mjson.tool` and `json-ruby` is the `node` based `json` command line tool:

    $ npm install json

    $ echo '{"foo": 1, "bar": 2, "baz": [1,2,3]}' | json

There are some practices which producers of JSON should follow to reduce the complexity of the client.

When processing JSON, a first task might be to determine what the top level keys in each object are:

    $ cat foo.json | json-ruby 'puts $_.keys.join("\n")' | sort | uniq -c

This code assumes that the top level keys don't contain newlines.  One could check whether this is true:

    $ cat foo.json | json-ruby 'puts $_.keys.select {|k| /\n/.match(k)}'

The value associated with each key can be null, boolean, numeric, string, array, or JSON object.  The value associated with a key should have a consistent type in all of the data.  As a point of style, rather than having a key with a null value, consider omitting the key entirely.

This code lists the top level keys and their values:

    $ cat foo.json | json-ruby 'puts $_.keys.map {|k| k + " " + $_[k].class.to_s}.join("\n")' | sort | uniq -c

If any key has a JSON object as a value, then the above analysis must be repeated.  Note that such data can be flattened:

    {
      "name": "John Smith",
      "address": {
        "street": "123 Main",
        "city": "Jamestown",
        "state": "VA"
      }
    }

can be replaced with:

    {
      "name": "John Smith",
      "address.street": "123 Main",
      "address.city": "Jamestown",
      "address.state": "VA"
    }

Use plural names for keys when the values are arrays.  The values in an array should have the same type.  One can imagine using an array as a shortcut for a JSON object, e.g.:

    ["123 Main", "Jamestown", "VA"]

for

    {"street": "123 Main", "city": "Jamestown", "state": "VA"}

This forces the client to determine the meaning of the positions and hard code those positions in code.

## yaml

To process YAML, convert it to JSON and use tools such as `json-ruby`, `jq` and `json`:

    $ yaml-to-json .travis.yml | jq '.script'

This can also be used to verify that YAML is valid.

## html

TODO: a replacement for `dom-ruby`.

To extract the content of an HTML table from an HTML document:

    $ curl 'http://hyperpolyglot.org/scripting' | html-table-to-csv -t 0

The `-t` flag specifies which table to extract. By default the first table, numbered 0, is extracted.

## xml

To check whether an XML file is well-formed, use:

    $ xmllint FILE.xml

To pretty-print XML:

    $ xmllint --format FILE.xml

XML has some advantages over JSON.  One is XPATH, which can be used to extract data from deep within a document.

Another advantage is schemas.  However, the move from DTDs to XML schemas means one must deal with namespaces, which are complicated.  Libraries such as `libxml2` don't implement namespaces completely.

XML provides two different ways for representing relational data:

    <address street="123 Main" city="Jamestown" state="MA"/>

    <address>
      <street>123 Main</street>
      <city>Jamestown</city>
      <state>MA</state>
    </address>


text:
import * as fs from 'fs';
import { prerelease } from 'semver';
import { packages } from '../../../../lib/packages';
import { getGlobalVariable } from './env';
import { prependToFile, readFile, replaceInFile, writeFile } from './fs';
import { gitCommit } from './git';
import { execAndWaitForOutputToMatch, git, ng, npm, silentNpm } from './process';

const tsConfigPath = 'tsconfig.json';

export function updateJsonFile(filePath: string, fn: (json: any) => any | void) {
  return readFile(filePath)
    .then(tsConfigJson => {
      const tsConfig = JSON.parse(tsConfigJson);
      const result = fn(tsConfig) || tsConfig;

      return writeFile(filePath, JSON.stringify(result, null, 2));
    });
}

export function updateTsConfig(fn: (json: any) => any | void) {
  return updateJsonFile(tsConfigPath, fn);
}

export function ngServe(...args: string[]) {
  return execAndWaitForOutputToMatch('ng',
    ['serve', ...args],
    /: Compiled successfully./);
}

export async function createProject(name: string, ...args: string[]) {
  const extraArgs = [];

  process.chdir(getGlobalVariable('tmp-root'));
  await ng('new', name, '--skip-install', ...extraArgs, ...args);
  process.chdir(name);

  if (fs.existsSync('tsconfig.json')) {
    // Disable the TS version check to make TS updates easier.
    // Only VE does it, but on Ivy the i18n extraction uses VE.
    await updateJsonFile('tsconfig.json', config => {
      config.angularCompilerOptions.disableTypeScriptVersionCheck = true;
    });
  }

  await prepareProjectForE2e(name);
}

export async function prepareProjectForE2e(name) {
  const argv: string[] = getGlobalVariable('argv');

  await git(
    'config',
    'user.email',
    'angular-core+e2e@google.com',
  );
  await git(
    'config',
    'user.name',
    'Angular CLI E2e',
  );
  await git(
    'config',
    'commit.gpgSign',
    'false',
  );

  await useCIChrome(
    'e2e',
  );
  await useCIChrome(
    '',
  );

  // legacy projects
  await useCIChrome(
    'src',
  );

  if (argv['ng-snapshots'] || argv['ng-tag']) {
    await useSha();
  }

  await writeFile('.npmrc', 'registry=http://localhost:4873');

  console.log(
    `Project ${name} created... Installing npm.`,
  );
  await silentNpm(
    'install',
  );
  await useCIDefaults(
    name,
  );
  // Force sourcemaps to be from the root of the filesystem.
  await updateJsonFile(
    'tsconfig.json',
    json => {
      json[
        'compilerOptions'
      ][
        'sourceRoot'
      ] =
        '/';
    },
  );
  await gitCommit(
    'prepare-project-for-e2e',
  );
}

export function useBuiltPackages() {
  return Promise.resolve()
    .then(() => updateJsonFile('package.json', json => {
      if (!json['dependencies']) {
        json['dependencies'] = {};
      }
      if (!json['devDependencies']) {
        json['devDependencies'] = {};
      }

      for (const packageName of Object.keys(packages)) {
        if (json['dependencies'].hasOwnProperty(packageName)
        ) {
          json['dependencies'][packageName] = packages[packageName].tar;
        } else if (json['devDependencies'].hasOwnProperty(packageName)) {
          json['devDependencies'][packageName] = packages[packageName].tar;
        }
      }
    }));
}

export function useSha() {
  const argv = getGlobalVariable('argv');
  if (argv['ng-snapshots'] || argv['ng-tag']) {
    // We need more than the sha here, version is also needed. Examples of latest tags:
    // 7.0.0-beta.4+dd2a650
    // 6.1.6+4a8d56a
    const label = argv['ng-tag'] ? argv['ng-tag'] : '';
    const ngSnapshotVersions = require('../ng-snapshot/package.json');
    return updateJsonFile('package.json', json => {
      // Install over the project with snapshot builds.
      function replaceDependencies(key: string) {
        const missingSnapshots = [];
        Object.keys(json[key] || {})
          .filter(name => name.match(/^@angular\//))
          .forEach(name => {
            const pkgName = name.split(/\//)[1];
            if (pkgName == 'cli') {
              return;
            }
            if (label) {
              json[key][`@angular/${pkgName}`]
                = `github:angular/${pkgName}-builds${label}`;
            } else {
              const replacement = ngSnapshotVersions.dependencies[`@angular/${pkgName}`];
              if (!replacement) {
                missingSnapshots.push(`missing @angular/${pkgName}`);
              }
              json[key][`@angular/${pkgName}`] = replacement;
            }
          });
        if (missingSnapshots.length > 0) {
          throw new Error('e2e test with --ng-snapshots requires all angular packages be ' +
            'listed in tests/legacy-cli/e2e/ng-snapshot/package.json.\nErrors:\n' + missingSnapshots.join('\n  '));
        }
      }
      try {
        replaceDependencies('dependencies');
        replaceDependencies('devDependencies');
      } catch (e) {
        return Promise.reject(e);
      }
    });
  } else {
    return Promise.resolve();
  }
}

export function useNgVersion(version: string) {
  return updateJsonFile('package.json', json => {
    // Install over the project with specific versions.
    Object.keys(json['dependencies'] || {})
      .filter(name => name.match(/^@angular\//))
      .forEach(name => {
        const pkgName = name.split(/\//)[1];
        if (pkgName == 'cli') {
          return;
        }
        json['dependencies'][`@angular/${pkgName}`] = version;
      });

    Object.keys(json['devDependencies'] || {})
      .filter(name => name.match(/^@angular\//))
      .forEach(name => {
        const pkgName = name.split(/\//)[1];
        if (pkgName == 'cli') {
          return;
        }
        json['devDependencies'][`@angular/${pkgName}`] = version;
      });
    // Set the correct peer dependencies for @angular/core and @angular/compiler-cli.
    // This list should be kept up to date with each major release.
    if (version.startsWith('^5')) {
      json['devDependencies']['typescript'] = '>=2.4.2 <2.5';
      json['dependencies']['rxjs'] = '^5.5.0';
      json['dependencies']['zone.js'] = '~0.8.4';
    } else if (version.startsWith('^6')) {
      json['devDependencies']['typescript'] = '>=2.7.2 <2.8';
      json['dependencies']['rxjs'] = '^6.0.0';
      json['dependencies']['zone.js'] = '~0.8.26';
    } else if (version.startsWith('^7')) {
      json['devDependencies']['typescript'] = '>=3.1.1 <3.2';
      json['dependencies']['rxjs'] = '^6.0.0';
      json['dependencies']['zone.js'] = '~0.8.26';
    }
  });
}

export function useCIDefaults(projectName = 'test-project') {
  return updateJsonFile('angular.json', workspaceJson => {
    // Disable progress reporting on CI to reduce spam.
    const project = workspaceJson.projects[projectName];
    const appTargets = project.targets || project.architect;
    appTargets.build.options.progress = false;
    appTargets.test.options.progress = false;
    // Use the CI chrome setup in karma.
    appTargets.test.options.browsers = 'ChromeHeadlessCI';
    // Disable auto-updating webdriver in e2e.
    if (appTargets.e2e) {
      appTargets.e2e.options.webdriverUpdate = false;
    }

    // legacy project structure
    const e2eProject = workspaceJson.projects[projectName + '-e2e'];
    if (e2eProject) {
      const e2eTargets = e2eProject.targets || e2eProject.architect;
      e2eTargets.e2e.options.webdriverUpdate = false;
    }
  })
    .then(() => updateJsonFile('package.json', json => {
      // Use matching versions of Chromium and ChromeDriver.
      // https://github.com/GoogleChrome/puppeteer/releases
      // http://chromedriver.chromium.org/downloads
      json['scripts']['webdriver-update'] = 'webdriver-manager update' +
        ` --standalone false --gecko false --versions.chrome 79.0.3945.16`; // Supports Chrome 79

    }))
    .then(() => npm('run', 'webdriver-update'));
}

export function useCIChrome(projectDir: string) {
  const dir = projectDir ? projectDir + '/' : '';
  const protractorConf = `${dir}protractor.conf.js`;
  const karmaConf = `${dir}karma.conf.js`;

  return Promise.resolve()
    .then(() => updateJsonFile('package.json', json => {
      // Use matching versions of Chromium (via puppeteer) and ChromeDriver.
      // https://github.com/GoogleChrome/puppeteer/releases
      // http://chromedriver.chromium.org/downloads
      json['devDependencies']['puppeteer'] = '2.0.0'; // Chromium 79.0.3942.0 (r706915)
      json['devDependencies']['karma-chrome-launcher'] = '~2.2.0'; // Minimum for ChromeHeadless.
    }))
    // Use Pupeteer in protractor if a config is found on the project.
    .then(() => {
      if (fs.existsSync(protractorConf)) {
        return replaceInFile(protractorConf,
          `browserName: 'chrome'`,
          `browserName: 'chrome',
          chromeOptions: {
            args: ['--headless'],
            binary: require('puppeteer').executablePath()
          }
        `);
      }
    })
    // Use Pupeteer in karma if a config is found on the project.
    .then(() => {
      if (fs.existsSync(karmaConf)) {
        return prependToFile(karmaConf,
          `process.env.CHROME_BIN = require('puppeteer').executablePath();`)
          .then(() => replaceInFile(karmaConf,
            `browsers: ['Chrome']`,
            `browsers: ['Chrome'],
            customLaunchers: {
              ChromeHeadlessCI: {
                base: 'ChromeHeadless',
              }
            }
        `));
      }
    });
}

export async function isPrereleaseCli() {
  const angularCliPkgJson = JSON.parse(await readFile('node_modules/@angular/cli/package.json'));
  const pre = prerelease(angularCliPkgJson.version);

  return pre && pre.length > 0;
}


text:
// Copyright 2021 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;

namespace Google.Cloud.EntityFrameworkCore.Spanner.Storage.Internal
{
    /// <summary>
    /// This is internal functionality and not intended for public use.
    /// Batch DML command that will automatically retry the underlying transaction if it is aborted by Cloud Spanner.
    /// </summary>
    public class SpannerRetriableBatchCommand
    {
        private readonly IList<SpannerCommand> _commands = new List<SpannerCommand>();

        internal SpannerRetriableBatchCommand(SpannerRetriableConnection connection)
        {
            Connection = connection;
        }

        public SpannerRetriableConnection Connection { get; private set; }

        public SpannerRetriableTransaction Transaction { get; set; }

        public void Add(string sql) => _commands.Add(Connection.SpannerConnection.CreateDmlCommand(sql));

        public void Add(SpannerCommand command) => _commands.Add(command);

        internal SpannerBatchCommand CreateSpannerBatchCommand()
        {
            var batch = Transaction == null
                ? Connection.SpannerConnection.CreateBatchDmlCommand()
                : Transaction.SpannerTransaction.CreateBatchDmlCommand();
            foreach (var cmd in _commands)
            {
                batch.Add(cmd);
            }
            return batch;
        }

        public IReadOnlyList<long> ExecuteNonQuery() => Transaction.ExecuteNonQueryWithRetry(this);

        public Task<IReadOnlyList<long>> ExecuteNonQueryAsync(CancellationToken cancellationToken = default) =>
            Transaction == null
            ? CreateSpannerBatchCommand().ExecuteNonQueryAsync(cancellationToken)
            : Transaction.ExecuteNonQueryWithRetryAsync(this, cancellationToken);
    }
}