[LNT] Python 3 support: fix storage of json data as BLOB

[thopre]

Summary

Machine and Run tables store JSON as binary data in their Parameters
column. However, JSON is inserted as text data in several places,
causing problem when SQLAlchemy reads it back since it expects binary
data but receives text, which Python3 throws an exception for.

2 constructs are responsible for inserting JSON as text:

1. json.dumps() method is used in several places to convert a dictionary to JSON which returns it as text. This commit adds an encoding step to UTF-8 since it is the recommended encoding for JSON.

2. One of the insertion with a JSON Parameters column in lnt_db_create.sql is missing a cast to BLOB. This commit adds the missing cast.

[thopre]

Add more cases of missing CAST to BLOB

[PrzemekWirkus]

From where can I get schema for this database you are inserting to ?

[PrzemekWirkus]

LGTM

Ah, DB schema is just above the insert. Silly me!

[thopre]

Committed as 860a51b14a489e6037dab342b82dfc8f99d566c7

lnt/server/db/migrations/upgrade_11_to_12.py

def update_testsuite(engine, db_key_name):
     sqlalchemy.orm.mapper(Machine, machine_table)
 
     all_machines = session.query(Machine)
     for machine in all_machines:
         info = dict(json.loads(machine.Parameters))
         name = info.pop('name', None)
         if name is not None:
             info['hostname'] = name
-        machine.Parameters = json.dumps(sorted(info.items()))
+        machine.Parameters = json.dumps(sorted(info.items())).encode("utf-8")
 
     session.commit()
     session.close()
 
 
 def upgrade(engine):
     update_testsuite(engine, 'NT')
     update_testsuite(engine, 'Compile')

lnt/server/db/migrations/upgrade_1_to_2.py

for i, run in enumerate(all_runs):
                         m.groups()
                     m = re.search('clang-([0-9.]*)', cc_build_string)
                     if m:
                         run_order = m.group(1)
 
         # Update the run info.
         run_info['inferred_run_order'] = run_order
         run_info['__report_version__'] = '1'
-        run.Parameters = json.dumps(sorted(run_info.items()))
+        run.Parameters = json.dumps(sorted(run_info.items())).encode('utf-8')
 
         if run_order != orig_order:
             # Lookup the new run order.
             result = session.query(Order.ID).\
                 filter(Order.llvm_project_revision == run_order).first()
 
             # If the result exists...
             if result is not None:

lnt/server/db/testsuite.py

class TestSuiteJSONSchema(Base):
     created with a json schema description.
     """
     __tablename__ = 'TestSuiteJSONSchemas'
     testsuite_name = Column("TestSuiteName", String(256), primary_key=True)
     jsonschema = Column("JSONSchema", Binary)
 
     def __init__(self, testsuite_name, data):
         self.testsuite_name = testsuite_name
-        self.jsonschema = json.dumps(data, encoding='utf-8', sort_keys=True)
+        self.jsonschema = json.dumps(data, sort_keys=True).encode('utf-8')
 
 
 class TestSuite(Base):
     __tablename__ = 'TestSuite'
 
     id = Column("ID", Integer, primary_key=True)
     name = Column("Name", String(256), unique=True)
 

lnt/server/db/testsuitedb.py

def __init__(self, v4db, name, test_suite):
 
             @property
             def parameters(self):
                 """dictionary access to the BLOB encoded parameters data"""
                 return dict(json.loads(self.parameters_data))
 
             @parameters.setter
             def parameters(self, data):
-                self.parameters_data = json.dumps(sorted(data.items()))
+                self.parameters_data = json.dumps(sorted(data.items())).encode("utf-8")
 
             def get_baseline_run(self, session):
                 ts = Machine.testsuite
                 user_baseline = ts.get_users_baseline(session)
                 if user_baseline:
                     return self.get_closest_previously_reported_run(
                         session, user_baseline.order)
                 else:
def __init__(self, v4db, name, test_suite):
 
             @property
             def parameters(self):
                 """dictionary access to the BLOB encoded parameters data"""
                 return dict(json.loads(self.parameters_data))
 
             @parameters.setter
             def parameters(self, data):
-                self.parameters_data = json.dumps(sorted(data.items()))
+                self.parameters_data = json.dumps(sorted(data.items())).encode("utf-8")
 
             def __json__(self, flatten_order=True):
                 result = {
                     'id': self.id,
                     'start_time': self.start_time,
                     'end_time': self.end_time,
                 }
                 # Leave out: machine_id, simple_run_id, imported_from

tests/SharedInputs/SmallInstance/data/lnt_db_create.sql

CREATE TABLE "NT_Machine" (
   "ID" INTEGER PRIMARY KEY NOT NULL, 
   "Name" VARCHAR(256), 
   "Parameters" BLOB, 
   hardware VARCHAR(256), 
   os VARCHAR(256)
 );
 INSERT INTO "NT_Machine" ("Name", "Parameters", "hardware", "os")
  VALUES('localhost__clang_DEV__x86_64',
-        '[["name", "localhost"], ["uname", "Darwin localhost 11.3.0 Darwin Kernel Version 11.3.0: Thu Jan 12 18:47:41 PST 2012; root:xnu-1699.24.23~1/RELEASE_X86_64 x86_64"]]',
+        CAST('[["name", "localhost"], ["uname", "Darwin localhost 11.3.0 Darwin Kernel Version 11.3.0: Thu Jan 12 18:47:41 PST 2012; root:xnu-1699.24.23~1/RELEASE_X86_64 x86_64"]]' AS BLOB),
         'x86_64','Darwin 11.3.0'); -- ID 1
 CREATE TABLE "NT_Test" (
   "ID" INTEGER PRIMARY KEY NOT NULL, 
   "Name" VARCHAR(256)
 );
 INSERT INTO "NT_Test" ("Name")
  VALUES('SingleSource/UnitTests/2006-12-01-float_varg'); -- ID 1
 INSERT INTO "NT_Test" ("Name")

tests/server/db/Inputs/V4Pages_extra_records.sql

 BEGIN TRANSACTION;
 INSERT INTO "NT_Test" ("Name")
  VALUES('SingleSource/UnitTests/ObjC/block-byref-aggr'); -- ID 3 (was 87)
 INSERT INTO "compile_Test" ("Name")
  VALUES('compile/403.gcc/combine.c/init/(-O0)'); -- ID 3 (was 38)
  
 -- make sure there are 3 machines - to test ?filter-machine-regex= on daily_report page
 INSERT INTO "NT_Machine" ("Name", "Parameters", "hardware", "os")
- VALUES('machine2','[]','AArch64','linux'); -- ID 2
+ VALUES('machine2',CAST('[]' AS BLOB),'AArch64','linux'); -- ID 2
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(2,NULL,'152290'); -- ID 3
 UPDATE "NT_Order" SET "NextOrder" = 3 WHERE "ID" = 2;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,3,'run3.json','2012-04-11 16:28:23.000000',
-        '2012-04-11 16:28:58.000000',NULL,'[]'); -- ID 3
+        '2012-04-11 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 3
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(3,1,NULL,NULL,0.001,0.0001,NULL,NULL); -- ID 3
 INSERT INTO "NT_Machine" ("Name", "Parameters", "hardware", "os")
- VALUES('machine3','[]','AArch64','linux'); -- ID 3
+ VALUES('machine3',CAST('[]' AS BLOB),'AArch64','linux'); -- ID 3
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(3,NULL,'152291'); -- ID 4
 UPDATE "NT_Order" SET "PreviousOrder" = 4 WHERE "ID" = 3;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters") 
  VALUES(3,4,'run4.json','2012-04-11 16:28:24.000000',
-        '2012-04-11 16:28:59.000000',NULL,'[]'); -- ID 4
+        '2012-04-11 16:28:59.000000',NULL,CAST('[]' AS BLOB)); -- ID 4
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(4,1,NULL,NULL,0.001,0.0001,NULL,NULL); -- ID 4
  
 -- check that a regression on consecutive runs more than 1 day apart can be detected:
 INSERT INTO "NT_Test" VALUES(4,'test1'); -- ID 4
 INSERT INTO "NT_Test" VALUES(5,'test2'); -- ID 5
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152292'); -- ID 5
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,5,'run5.json','2012-05-01 16:28:23.000000',
-        '2012-05-01 16:28:58.000000',NULL,'[]'); -- ID 5
+        '2012-05-01 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 5
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,4,0,0,0.001,1.0,NULL,NULL); -- ID 5: passing result
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,5,0,1,0.001,1.0,NULL,NULL); -- ID 6: failing result
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(5,NULL,'152293'); -- ID 6
 UPDATE "NT_Order" SET "PreviousOrder" = 6 WHERE "ID" = 5;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,6,'run6.json','2012-05-03 16:28:24.000000',
-        '2012-05-03 16:28:59.000000',NULL,'[]'); -- ID 6
+        '2012-05-03 16:28:59.000000',NULL,CAST('[]' AS BLOB)); -- ID 6
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(6,4,0,0,0.001,10.0,NULL,NULL); -- ID 7: passing result 10x slower
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,5,0,0,0.001,1.0,NULL,NULL); -- ID 8: passing result
 
 -- check that a failing test result does not show up in the sparkline
 INSERT INTO "NT_Test" VALUES(6,'test6'); -- ID 6
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152294'); -- ID 6
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152295'); -- ID 7
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152296'); -- ID 8
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,6,'run7.json','2012-05-10 16:28:23.000000',
-        '2012-05-10 16:28:58.000000',NULL,'[]'); -- ID 7
+        '2012-05-10 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 7
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,7,'run8.json','2012-05-11 16:28:23.000000',
-        '2012-05-11 16:28:58.000000',NULL,'[]'); -- ID 8
+        '2012-05-11 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 8
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,8,'run9.json','2012-05-12 16:28:23.000000',
-        '2012-05-12 16:28:58.000000',NULL,'[]'); -- ID 9
+        '2012-05-12 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 9
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(7,6,0,0,0.001,1.0,NULL,NULL); -- ID 9: passing result
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(8,6,0,1,0.001,1.0,NULL,NULL); -- ID 10: failing result

tests/server/ui/Inputs/V4Pages_extra_records.sql

 BEGIN TRANSACTION;
 INSERT INTO "NT_Test" ("Name")
  VALUES('SingleSource/UnitTests/ObjC/block-byref-aggr'); -- ID 3 (was 87)
 INSERT INTO "compile_Test" ("Name")
  VALUES('compile/403.gcc/combine.c/init/(-O0)'); -- ID 3 (was 38)
  
 -- make sure there are 3 machines - to test ?filter-machine-regex= on daily_report page
 INSERT INTO "NT_Machine" ("Name", "Parameters", "hardware", "os")
- VALUES('machine2','[]','AArch64','linux'); -- ID 2
+ VALUES('machine2',CAST('[]' AS BLOB),'AArch64','linux'); -- ID 2
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(2,NULL,'152290'); -- ID 3
 UPDATE "NT_Order" SET "NextOrder" = 3 WHERE "ID" = 2;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,3,'run3.json','2012-04-11 16:28:23.000000',
-        '2012-04-11 16:28:58.000000',NULL,'[]'); -- ID 3
+        '2012-04-11 16:28:58.000000',NULL,CAST('[]' AS BLOB)); -- ID 3
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(3,1,NULL,NULL,0.001,0.0001,NULL,NULL); -- ID 3
 INSERT INTO "NT_Machine" ("Name", "Parameters", "hardware", "os")
- VALUES('machine3','[]','AArch64','linux'); -- ID 3
+ VALUES('machine3',CAST('[]' AS BLOB),'AArch64','linux'); -- ID 3
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(3,NULL,'152291'); -- ID 4
 UPDATE "NT_Order" SET "PreviousOrder" = 4 WHERE "ID" = 3;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters") 
  VALUES(3,4,'run4.json','2012-04-11 16:28:24.000000',
-        '2012-04-11 16:28:59.000000',NULL,'[]'); -- ID 4
+        '2012-04-11 16:28:59.000000',NULL,CAST('[]' AS BLOB)); -- ID 4
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(4,1,NULL,NULL,0.001,0.0001,NULL,NULL); -- ID 4
  
 -- check that a regression on consecutive runs more than 1 day apart can be detected:
 INSERT INTO "NT_Test" VALUES(4,'test1'); -- ID 4
 INSERT INTO "NT_Test" VALUES(5,'test2'); -- ID 5
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152292'); -- ID 5
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,5,'run5.json','2012-05-01 16:28:23.000000',
         '2012-05-01 16:28:58.000000',NULL,
-        '[["producer", "http://buildbot.server.url/builders/some-builder/builds/987"]]'); -- ID 5
+        CAST('[["producer", "http://buildbot.server.url/builders/some-builder/builds/987"]]' AS BLOB)); -- ID 5
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,4,0,0,0.001,1.0,NULL,NULL); -- ID 5: passing result
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,5,0,1,0.001,1.0,NULL,NULL); -- ID 6: failing result
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(5,NULL,'152293'); -- ID 6
 UPDATE "NT_Order" SET "PreviousOrder" = 6 WHERE "ID" = 5;
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,6,'run6.json','2012-05-03 16:28:24.000000',
-        '2012-05-03 16:28:59.000000',NULL,'[]'); -- ID 6
+        '2012-05-03 16:28:59.000000',NULL,CAST('[]' AS BLOB)); -- ID 6
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(6,4,0,0,0.001,10.0,NULL,NULL); -- ID 7: passing result 10x slower
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(5,5,0,0,0.001,1.0,NULL,NULL); -- ID 8: passing result
 
 -- check that a failing test result does not show up in the sparkline
 INSERT INTO "NT_Test" VALUES(6,'test6'); -- ID 6
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152294'); -- ID 6
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152295'); -- ID 7
 INSERT INTO "NT_Order" ("NextOrder", "PreviousOrder", "llvm_project_revision")
  VALUES(NULL,NULL,'152296'); -- ID 8
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,6,'run7.json','2012-05-10 16:28:23.000000',
         '2012-05-10 16:28:58.000000',NULL,
-        '[["producer", "http://my.build.server/buildResult"]]'); -- ID 7
+        CAST('[["producer", "http://my.build.server/buildResult"]]' AS BLOB)); -- ID 7
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,7,'run8.json','2012-05-11 16:28:23.000000',
         '2012-05-11 16:28:58.000000',NULL,
-        '[["producer", "https://my.build.server/buildResult"]]'); -- ID 8
+        CAST('[["producer", "https://my.build.server/buildResult"]]' AS BLOB)); -- ID 8
 INSERT INTO "NT_Run" ("MachineID", "OrderID", "ImportedFrom", "StartTime",
                       "EndTime", "SimpleRunID", "Parameters")
  VALUES(2,8,'run9.json','2012-05-12 16:28:23.000000',
         '2012-05-12 16:28:58.000000',NULL,
-        '[["producer", "https://buildbot.server.url/builders/some-builder/builds/999"]]'); -- ID 9
+        CAST('[["producer", "https://buildbot.server.url/builders/some-builder/builds/999"]]' AS BLOB)); -- ID 9
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(7,6,0,0,0.001,1.0,NULL,NULL); -- ID 9: passing result
 INSERT INTO "NT_Sample" ("RunID", "TestID", "compile_status",
                          "execution_status", "compile_time", "execution_time",
                          "score", "mem_bytes")
  VALUES(8,6,0,1,0.001,1.0,NULL,NULL); -- ID 10: failing result