Use jq to format p4 changes output to SQLite
Tags: p4
Published: 2023-07-26 18:33:39

---
title: Use jq to format p4 changes output to SQLite
published: true
description: 
tags: p4
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2023-07-26 17:41 +0000
---

There are so many possibilities once data is structured. In my [last post](https://grepliz.netlify.app/posts/jq-p4-changes) about formating `p4 changes` out to JSON, it occurred to me it would be fairly trivial to put this same data into a [SQLite](https://www.sqlite.org/index.html) databasse.

As show in the [previous post](https://grepliz.netlify.app/posts/jq-p4-changes), `p4 changes` output can be converted to JSON data like this:
```
p4 changes | jq -R -s '[split("\n")[:-1] | map(split("\u0020")) | .[] | { "change": .[1], "author": .[5], "date": .[3], "message": .[6:length] | join(" ") }]'

[
  {
    "change": "3",
    "author": "grepliz@test-local-01",
    "date": "2023/07/20",
    "message": "'Add day1.pdf'"
  },
  {
    "change": "2",
    "author": "grepliz@test-local-01",
    "date": "2023/07/20",
    "message": "'Add crazy.psd.'"
  },
  {
    "change": "1",
    "author": "grepliz@test-local-01",
    "date": "2023/07/20",
    "message": "'Populated server with files '"
  }
]
```

For brevity's sake, I'm going to redirect that output to a file called `json.txt` and `cat` it for the rest of the examples.

```
p4 changes | jq -R -s '[split("\n")[:-1] | map(split("\u0020")) | .[] | { "change": .[1], "author": .[5], "date": .[3], "message": .[6:length] | join(" ") }]' > json.txt
```

CREATING THE INSERT STATEMENTS
------------------------------

We can now pipe the JSON data back into another `jq` filter to produce the `insert` statements needed for a SQLite database.

```
cat json.txt | jq -r '.[] | "insert into changes values (\(.change), \"\(.author)\", \"\(.date)\", \(.message));"'             

insert into changes values (3, "grepliz@test-local-01", "2023/07/20", 'Add day1.pdf');
insert into changes values (2, "grepliz@test-local-01", "2023/07/20", 'Add crazy.psd.');
insert into changes values (1, "grepliz@test-local-01", "2023/07/20", 'Populated server with files ');
```

The key to this `jq` filter is using string interpolation via `\(exp)` which is documented here:
https://jqlang.github.io/jq/manual/#Stringinterpolation:\(exp)

Because the `insert` statement contains characters that can make the filter a bit of a bear to look at (parenthesis and quotations), let's look at a simpler example.

Suppose we just wanted to create a sentence with this format:
> Changelist number X was writte by Y on Z.

What would that look like?  

Using the `\(exp)` syntax, we can do something like this:
 
```
cat json.txt | jq -r '.[] | "Changelist number \(.change) was written by \(.author) on \(.date)"'                 

Changelist number 3 was written by grepliz@test-local-01 on 2023/07/20
Changelist number 2 was written by grepliz@test-local-01 on 2023/07/20
Changelist number 1 was written by grepliz@test-local-01 on 2023/07/20
```

Now suppose we wanted to quote the author in our sentence, we would need to add quotation marks and escape them like this: `\"(.author)\"`.

```
cat json.txt | jq -r '.[] | "Changelist number \(.change) was written by \"\(.author)\" on \(.date)."'

Changelist number 3 was written by "grepliz@test-local-01" on 2023/07/20.
Changelist number 2 was written by "grepliz@test-local-01" on 2023/07/20.
Changelist number 1 was written by "grepliz@test-local-01" on 2023/07/20.
```

Putting it all together, we arrive at this:
```
cat json.txt | jq -r '.[] | "insert into changes values (\(.change), \"\(.author)\", \"\(.date)\", \(.message));"'

insert into changes values (3, "grepliz@test-local-01", "2023/07/20", 'Add day1.pdf');
insert into changes values (2, "grepliz@test-local-01", "2023/07/20", 'Add crazy.psd.');
insert into changes values (1, "grepliz@test-local-01", "2023/07/20", 'Populated server with files ');
```

Now we just need to redirect this to a file called `insert.sql` and we will import the data into a SQLite database (i.e. append `> insert.sql` to the above statement).

SETTING UP THE SQLITE DATABASE
------------------------------
Create a SQLite database like this:
```
sqlite3 changelists.db
```

This will create a file called `changelists.db` in your current directory and take you the a SQLite interactive prompt.

Create a table with a schema that matches the values in the `insert.sql` file.

```
sqlite> CREATE TABLE changes (
   ...>     change INTEGER PRIMARY KEY, 
   ...>     author TEXT NOT NULL, 
   ...>     date TEXT NOT NULL, 
   ...>     Message TEXT NOT NULL
   ...> );
```

We now have an empty table called `changes`.  If we try to `select` from this table, we see that it is empty as indicated by the fact that nothing is returned.

```
sqlite> select * from changes;
```

Let's toggle some options to make the SQL output look a little nicer when we do populate the table.

```
sqlite> .headers on
sqlite> .mode columns
```

We can now add a dummy row to see what it will look like.

```
sqlite> insert into changes values (0, "liz", "today", "this is a changelist description.");
sqlite> select * from changes;
change  author  date   Message                          
------  ------  -----  ---------------------------------
0       liz     today  this is a changelist description.
```

Great, that looks pretty good.  Now let's import the `insert.sql` file we just created.

```
sqlite> .read insert.sql
sqlite> select * from changes;
change  author                 date        Message                          
------  ---------------------  ----------  ---------------------------------
0       liz                    today       this is a changelist description.
1       grepliz@test-local-01  2023/07/20  Populated server with files      
2       grepliz@test-local-01  2023/07/20  Add crazy.psd.                   
3       grepliz@test-local-01  2023/07/20  Add day1.pdf                     
```

Next, let's delete our dummy row.

```
sqlite> delete from changes where change = 0;
sqlite> select * from changes;
change  author                 date        Message                     
------  ---------------------  ----------  ----------------------------
1       grepliz@test-local-01  2023/07/20  Populated server with files 
2       grepliz@test-local-01  2023/07/20  Add crazy.psd.              
3       grepliz@test-local-01  2023/07/20  Add day1.pdf                
```

Voila! Now we have a SQLite database with a populated `changes` table.