diff --git a/sql/upgrades/137.sql b/sql/upgrades/137.sql new file mode 100644 --- /dev/null +++ b/sql/upgrades/137.sql @@ -0,0 +1,44 @@ +-- SWH DB schema upgrade +-- from_version: 136 +-- to_version: 137 +-- description: Add comment columns to all tables + +insert into dbversion(version, release, description) + values(137, now(), 'Work In Progress'); + +-- comment for columns of dbversion table +comment on column dbversion.version is 'Current version for sql schema'; +comment on column dbversion.release is 'Release date and time for current version'; +comment on column dbversion.description is 'Description for current release'; + +-- comment for columns of content table +comment on column content.sha1 is 'Sha1 checksum of file content'; +comment on column content.sha1_git is 'Git id of sha1 checksum'; +comment on column content.sha256 is 'Sha256 checksum of file content'; +comment on column content.blake2s256 is 'Blake2s checksum of file content'; +comment on column content.length is 'Length of file content'; +comment on column content.ctime is 'Time of file creation'; +comment on column content.status is 'Status of content (absent, visible, hidden)'; +comment on column content.object_id is 'File content identifier'; + +-- comment for columns of skipped_content +comment on column skipped_content.sha1 is 'Sha1 checksum of skipped content'; +comment on column skipped_content.sha1_git is 'Git id of sha1 checksum'; +comment on column skipped_content.sha256 is 'Sha256 checksum of skipped content'; +comment on column skipped_content.blake2s256 is 'Blake2s checksum of skipped content'; +comment on column skipped_content.length is 'Length of skipped content'; +comment on column skipped_content.ctime is 'Time of file creation'; +comment on column skipped_content.status is 'Status of skipped content (absent, visible, hidden)'; +comment on column skipped_content.reason is 'Reason for skipping content'; +comment on column skipped_content.origin is 'Foreign key for origin table'; +comment on column skipped_content.object_id is 'Skipped content identifier'; + +-- comment for columns of fetch_history +comment on column fetch_history.id IS 'Identifier for fetch history'; +comment on column fetch_history.origin IS 'Foreign key for origin table'; +comment on column fetch_history."date" IS 'Date of entry'; +comment on column fetch_history.status IS 'True indicates successful fetch'; +comment on column fetch_history."result" IS 'Detailed return values, times etc'; +comment on column fetch_history.stdout IS 'Console output of fetch'; +comment on column fetch_history.stderr IS 'Error message for fetch'; +comment on column fetch_history.duration IS 'Time taken to complete fetch, NULL if ongoing'; diff --git a/swh/storage/sql/30-swh-schema.sql b/swh/storage/sql/30-swh-schema.sql --- a/swh/storage/sql/30-swh-schema.sql +++ b/swh/storage/sql/30-swh-schema.sql @@ -10,9 +10,13 @@ description text ); +comment on column dbversion.version is 'Current version for sql schema'; +comment on column dbversion.release is 'Release date and time for current version'; +comment on column dbversion.description is 'Description for current release'; + -- latest schema version insert into dbversion(version, release, description) - values(133, now(), 'Work In Progress'); + values(137, now(), 'Work In Progress'); -- a SHA1 checksum create domain sha1 as bytea check (length(value) = 20); @@ -50,6 +54,15 @@ object_id bigserial ); +comment on column content.sha1 is 'Sha1 checksum of file content'; +comment on column content.sha1_git is 'Git id of sha1 checksum'; +comment on column content.sha256 is 'Sha256 checksum of file content'; +comment on column content.blake2s256 is 'Blake2s checksum of file content'; +comment on column content.length is 'Length of file content'; +comment on column content.ctime is 'Time of file creation'; +comment on column content.status is 'Status of content (absent, visible, hidden)'; +comment on column content.object_id is 'File content identifier'; + -- An origin is a place, identified by an URL, where software source code -- artifacts can be found. We support different kinds of origins, e.g., git and @@ -65,6 +78,10 @@ url text not null ); +comment on column origin.id is 'Artifact origin id'; +comment on column origin.type is 'Type of origin'; +comment on column origin.url is 'Url of origin'; + -- Content blobs observed somewhere, but not ingested into the archive for -- whatever reason. This table is separate from the content table as we might @@ -87,6 +104,17 @@ object_id bigserial ); +comment on column skipped_content.sha1 is 'Sha1 checksum of skipped content'; +comment on column skipped_content.sha1_git is 'Git id of sha1 checksum'; +comment on column skipped_content.sha256 is 'Sha256 checksum of skipped content'; +comment on column skipped_content.blake2s256 is 'Blake2s checksum of skipped content'; +comment on column skipped_content.length is 'Length of skipped content'; +comment on column skipped_content.ctime is 'Time of file creation'; +comment on column skipped_content.status is 'Status of skipped content (absent, visible, hidden)'; +comment on column skipped_content.reason is 'Reason for skipping content'; +comment on column skipped_content.origin is 'Foreign key for origin table'; +comment on column skipped_content.object_id is 'Skipped content identifier'; + -- Log of all origin fetches (i.e., origin crawling) that have been done in the -- past, or are still ongoing. Similar to list_history, but for origins. @@ -102,6 +130,15 @@ duration interval -- fetch duration of NULL if still ongoing ); +comment on column fetch_history.id IS 'Identifier for fetch history'; +comment on column fetch_history.origin IS 'Foreign key for origin table'; +comment on column fetch_history."date" IS 'Date of entry'; +comment on column fetch_history.status IS 'True indicates successful fetch'; +comment on column fetch_history."result" IS 'Detailed return values, times etc'; +comment on column fetch_history.stdout IS 'Console output of fetch'; +comment on column fetch_history.stderr IS 'Error message for fetch'; +comment on column fetch_history.duration IS 'Time taken to complete fetch, NULL if ongoing'; + -- A file-system directory. A directory is a list of directory entries (see -- tables: directory_entry_{dir,file}).