Use jq to format p4 changes output to SQLite

There are so many possibilities once data is structured. In my last post about formating p4 changes out to JSON, it occurred to me it would be fairly trivial to put this same data into a SQLite databasse.

As show in the previous post, 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.