github.com/google/syzkaller@v0.0.0-20251211124644-a066d2bc4b02/syz-cluster/pkg/db/migrations/1_initialize.up.sql (about)

     1  CREATE TABLE Series (
     2      ID STRING(36) NOT NULL, -- UUID
     3      ExtID STRING(128) NOT NULL, -- For LKML, it's a message ID of the series.
     4      AuthorName STRING(512) NOT NULL,
     5      AuthorEmail STRING(512) NOT NULL,
     6      Title STRING(512) NOT NULL,
     7      Version INT64 NOT NULL,
     8      Link STRING(512) NOT NULL,
     9      PublishedAt TIMESTAMP NOT NULL,
    10      LatestSessionID STRING(36),
    11      Cc ARRAY<STRING(256)>,
    12  ) PRIMARY KEY (ID);
    13  
    14  CREATE INDEX SeriesByPublishedAt ON Series (PublishedAt);
    15  CREATE UNIQUE INDEX SeriesByExtID ON Series (ExtID);
    16  
    17  CREATE TABLE Patches (
    18      ID STRING(36) NOT NULL, -- UUID
    19      SeriesID STRING(36) NOT NULL,
    20      Seq INT64 NOT NULL,
    21      Title STRING(512) NOT NULL,
    22      Link STRING(512) NOT NULL,
    23      BodyURI STRING(512) NOT NULL, -- These might be too big to store directly in Spanner.
    24      CONSTRAINT FK_SeriesPatches FOREIGN KEY (SeriesID) REFERENCES Series (ID),
    25  ) PRIMARY KEY(ID);
    26  
    27  CREATE INDEX PatchesBySeriesAndSeq ON Patches (SeriesID, Seq);
    28  
    29  CREATE TABLE Builds (
    30      ID STRING(36) NOT NULL, -- UUID
    31      TreeName STRING(128) NOT NULL,
    32      CommitHash STRING(256) NOT NULL,
    33      CommitDate TIMESTAMP NOT NULL,
    34      SeriesID STRING(36), -- NULL if no series were applied to the tree.
    35      Arch STRING(128) NOT NULL,
    36      ConfigName STRING(256) NOT NULL, -- E.g. subsystem-specific build configuration names. Known to the builders.
    37      ConfigURI STRING(512) NOT NULL, -- The config actually used during the build.
    38      Status STRING(128) NOT NULL,
    39      CONSTRAINT FK_Series FOREIGN KEY (SeriesID) REFERENCES Series (ID),
    40      CONSTRAINT StatusEnum CHECK (Status IN ('build_failed', 'built', 'tests_failed', 'success')),
    41  ) PRIMARY KEY(ID);
    42  
    43  -- It does not cover all fields that will be requested, but it should be discriminative enough.
    44  CREATE INDEX LastSuccessfulBuild ON Builds (TreeName, SeriesID, CommitDate DESC);
    45  
    46  /*
    47    There may be multiple sessions per a single series, e.g. if
    48    1) We happened to re-deploy the new version when the previous was being fuzzed.
    49    2) We want to run bechmarks: some sessions will correspond solely to them.
    50  */
    51  CREATE TABLE Sessions (
    52      ID STRING(36) NOT NULL, -- UUID
    53      SeriesID STRING(36) NOT NULL,
    54      CreatedAt TIMESTAMP NOT NULL,
    55      StartedAt TIMESTAMP,
    56      FinishedAt TIMESTAMP,
    57      SkipReason STRING(1024),
    58      LogURI STRING(512) NOT NULL,
    59      TriageLogURI STRING(512) NOT NULL,
    60      Tags ARRAY<STRING(256)>,
    61      CONSTRAINT FK_SeriesSessions FOREIGN KEY (SeriesID) REFERENCES Series (ID),
    62  ) PRIMARY KEY(ID);
    63  
    64  ALTER TABLE Series ADD CONSTRAINT FK_SeriesLatestSession FOREIGN KEY (LatestSessionID) REFERENCES Sessions (ID);
    65  CREATE INDEX SessionsByFinishedAt ON Sessions (FinishedAt);
    66  
    67  -- Individual tests/steps completed within a session.
    68  CREATE TABLE SessionTests (
    69      SessionID STRING(36) NOT NULL, -- UUID
    70      TestName STRING(256) NOT NULL,
    71      UpdatedAt TIMESTAMP NOT NULL,
    72      Result STRING(36) NOT NULL,
    73      BaseBuildID STRING(36),
    74      PatchedBuildID STRING(36),
    75      LogURI STRING(256) NOT NULL,
    76      ArtifactsArchiveURI STRING(256) NOT NULL,
    77      CONSTRAINT FK_SessionResults FOREIGN KEY (SessionID) REFERENCES Sessions (ID),
    78      CONSTRAINT ResultEnum CHECK (Result IN ('passed', 'failed', 'error', 'running')),
    79      CONSTRAINT FK_BaseBuild FOREIGN KEY (BaseBuildID) REFERENCES Builds (ID),
    80      CONSTRAINT FK_PatchedBuild FOREIGN KEY (PatchedBuildID) REFERENCES Builds (ID),
    81  ) PRIMARY KEY(SessionID, TestName);
    82  
    83  /*
    84    Findings are build/boot errors or crashes found during processing the patch series.
    85    One could have used (SessionID, TestName, Title) as a key, but that becomes very inconvenient
    86    if the Finding is to be referenced from multiple places.
    87  */
    88  CREATE TABLE Findings (
    89      ID STRING(36) NOT NULL, -- UUID
    90      SessionID STRING(36) NOT NULL,
    91      TestName STRING(256) NOT NULL,
    92      Title STRING(256) NOT NULL,
    93      LogURI STRING(256) NOT NULL,
    94      ReportURI STRING(256) NOT NULL,
    95      SyzReproURI STRING(256) NOT NULL,
    96      SyzReproOptsURI STRING(256) NOT NULL,
    97      CReproURI STRING(256) NOT NULL,
    98      CONSTRAINT FK_SessionCrashes FOREIGN KEY (SessionID) REFERENCES Sessions (ID),
    99      CONSTRAINT FK_TestCrashes FOREIGN KEY (SessionID, TestName) REFERENCES SessionTests (SessionID, TestName),
   100  ) PRIMARY KEY (ID);
   101  
   102  CREATE UNIQUE INDEX NoDupFindings ON Findings(SessionID, TestName, Title);
   103  
   104  -- Session's bug reports.
   105  CREATE TABLE SessionReports (
   106      ID STRING(36) NOT NULL, -- UUID
   107      SessionID STRING(36) NOT NULL, -- UUID
   108      ReportedAt TIMESTAMP,
   109      Moderation BOOL,
   110      Reporter STRING(256),
   111      CONSTRAINT FK_SessionReports FOREIGN KEY (SessionID) REFERENCES Sessions (ID),
   112  ) PRIMARY KEY(ID);
   113  
   114  CREATE UNIQUE INDEX NoDupSessionReports ON SessionReports(SessionID, Moderation);
   115  CREATE INDEX SessionReportsByStatus ON SessionReports (Reporter, ReportedAt);
   116  
   117  -- Replies on a session report.
   118  CREATE TABLE ReportReplies (
   119      MessageID STRING(512) NOT NULL, -- Gmail sets a limit of 500 characters for Message-ID
   120      ReportID STRING(36) NOT NULL, -- UUID
   121      Time TIMESTAMP,
   122      CONSTRAINT FK_ReplyReportID FOREIGN KEY (ReportID) REFERENCES SessionReports (ID),
   123  ) PRIMARY KEY(MessageID, ReportID);