[Qa-jenkins-scm] [Git][qa/jenkins.debian.net][master] 2 commits: reproducible: db_maintenance: flake8-fy

Mattia Rizzolo gitlab at salsa.debian.org
Tue Dec 11 21:26:59 GMT 2018


Mattia Rizzolo pushed to branch master at Debian QA / jenkins.debian.net


Commits:
076898e4 by Mattia Rizzolo at 2018-12-11T21:19:20Z
reproducible: db_maintenance: flake8-fy

Signed-off-by: Mattia Rizzolo <mattia at debian.org>

- - - - -
2976bc69 by Mattia Rizzolo at 2018-12-11T21:25:00Z
reproducible: db_maintenance: change the data type used to store timestamp

using text to store a timestamp is not a good idea as comparisons are
awful.

There are many other tables needing similar actions, but I'm starting
with this one as this particular table is affected by slow queries.

Signed-off-by: Mattia Rizzolo <mattia at debian.org>

- - - - -


1 changed file:

- bin/reproducible_db_maintenance.py


Changes:

=====================================
bin/reproducible_db_maintenance.py
=====================================
@@ -222,11 +222,11 @@ schema_updates = {
             build_duration TEXT NOT NULL,
             UNIQUE (name, version, suite, architecture, build_date))''',
     ],
-    3: [ # add columns to stats_bugs for new usertag umask
+    3: [  # add columns to stats_bugs for new usertag umask
         '''ALTER TABLE stats_bugs ADD COLUMN open_umask INTEGER''',
         '''ALTER TABLE stats_bugs ADD COLUMN done_umask INTEGER''',
     ],
-    4: [ # stats_pkg_state needs (datum, suite) as primary key
+    4: [  # stats_pkg_state needs (datum, suite) as primary key
         '''CREATE TABLE stats_pkg_state_tmp
            (datum TEXT NOT NULL,
             suite TEXT NOT NULL,
@@ -243,7 +243,7 @@ schema_updates = {
         '''DROP TABLE stats_pkg_state;''',
         '''ALTER TABLE stats_pkg_state_tmp RENAME TO stats_pkg_state;''',
     ],
-    5: [ # stats_builds_per_day needs (datum, suite) as primary key
+    5: [  # stats_builds_per_day needs (datum, suite) as primary key
         '''CREATE TABLE stats_builds_per_day_tmp
                      (datum TEXT NOT NULL,
                       suite TEXT NOT NULL,
@@ -259,7 +259,7 @@ schema_updates = {
         '''DROP TABLE stats_builds_per_day;''',
         '''ALTER TABLE stats_builds_per_day_tmp RENAME TO stats_builds_per_day;''',
     ],
-    6: [ # stats_builds_age needs (datum, suite) as primary key
+    6: [  # stats_builds_age needs (datum, suite) as primary key
         '''CREATE TABLE stats_builds_age_tmp
                      (datum TEXT NOT NULL,
                       suite TEXT NOT NULL,
@@ -274,7 +274,7 @@ schema_updates = {
         '''DROP TABLE stats_builds_age;''',
         '''ALTER TABLE stats_builds_age_tmp RENAME TO stats_builds_age;''',
     ],
-    7: [ # change build_duration field in results and stats_build from str to int
+    7: [  # change build_duration field in results and stats_build from str to int
         '''CREATE TABLE stats_build_tmp
            (id INTEGER PRIMARY KEY,
             name TEXT NOT NULL,
@@ -305,7 +305,7 @@ schema_updates = {
         'DROP TABLE results',
         'ALTER TABLE results_tmp RENAME TO results',
     ],
-    8: [ # add default value to stats_bugs to get a full 'done vs open bugs' graph
+    8: [  # add default value to stats_bugs to get a full 'done vs open bugs' graph
         '''CREATE TABLE stats_bugs_tmp
            (datum TEXT NOT NULL,
             open_toolchain INTEGER DEFAULT '0',
@@ -341,7 +341,7 @@ schema_updates = {
         'DROP TABLE stats_bugs',
         'ALTER TABLE stats_bugs_tmp RENAME TO stats_bugs',
     ],
-    9: [ # rename 'sid' to 'unstable'
+    9: [  # rename 'sid' to 'unstable'
         "UPDATE sources SET suite = 'unstable' WHERE suite = 'sid'",
         "UPDATE stats_build SET suite = 'unstable' WHERE suite = 'sid'",
         "UPDATE stats_pkg_state SET suite = 'unstable' WHERE suite = 'sid'",
@@ -349,7 +349,7 @@ schema_updates = {
         "UPDATE stats_builds_age SET suite = 'unstable' WHERE suite = 'sid'",
         "UPDATE stats_meta_pkg_state SET suite = 'unstable' WHERE suite = 'sid'",
     ],
-    10: [ # add the notes and issues tables
+    10: [  # add the notes and issues tables
         '''CREATE TABLE notes (
             package_id INTEGER,
             version TEXT NOT NULL,
@@ -364,18 +364,18 @@ schema_updates = {
             url TEXT,
             PRIMARY KEY (name))''',
     ],
-    11: [ # table with removed packages, to enable the maintenance job to do clean up
+    11: [  # table with removed packages, to enable the maintenance job to do clean up
         '''CREATE TABLE removed_packages (
             name TEXT NOT NULL,
             suite TEXT NOT NULL,
             architecture TEXT NOT NULL,
             PRIMARY KEY (name, suite, architecture))''',
     ],
-    12: [ # refactor the artifacts handling, splitting artifacts saving from
-          # IRC notification
+    12: [  # refactor the artifacts handling, splitting artifacts saving from
+           # IRC notification
         'ALTER TABLE schedule ADD COLUMN notify TEXT',
     ],
-    13: [ # record manual scheduling done, to be able to limit people
+    13: [  # record manual scheduling done, to be able to limit people
         '''CREATE TABLE manual_scheduler (
             id INTEGER PRIMARY KEY,
             package_id INTEGER NOT NULL,
@@ -383,14 +383,14 @@ schema_updates = {
             date_request INTEGER NOT NULL)''',
         'ALTER TABLE schedule ADD COLUMN scheduler TEXT',
     ],
-    14: [ # column to enable mail notification to maintainers
+    14: [  # column to enable mail notification to maintainers
         'ALTER TABLE sources ADD COLUMN notify_maintainer INTEGER NOT NULL DEFAULT 0',
     ],
-    15: [ # add columns to stats_bugs for new usertag ftbfs
+    15: [  # add columns to stats_bugs for new usertag ftbfs
         '''ALTER TABLE stats_bugs ADD COLUMN open_ftbfs INTEGER''',
         '''ALTER TABLE stats_bugs ADD COLUMN done_ftbfs INTEGER''',
     ],
-    16: [ # add default value to stats_bugs.(open|done)_ftbfs to get a full 'done vs open bugs' graph
+    16: [  # add default value to stats_bugs.(open|done)_ftbfs to get a full 'done vs open bugs' graph
         '''CREATE TABLE stats_bugs_tmp
            (datum TEXT NOT NULL,
             open_toolchain INTEGER DEFAULT '0',
@@ -428,21 +428,22 @@ schema_updates = {
         'DROP TABLE stats_bugs',
         'ALTER TABLE stats_bugs_tmp RENAME TO stats_bugs',
     ],
-    17: [ # add column to save which builders builds what
+    17: [  # add column to save which builders builds what
         "ALTER TABLE schedule ADD COLUMN builder TEXT",
         "ALTER TABLE results ADD COLUMN builder TEXT NOT NULL DEFAULT ''",
         "ALTER TABLE stats_build ADD COLUMN builder TEXT NOT NULL DEFAULT ''",
     ],
-    18: [ # add columns to stats_bugs for new usertag locale
+    18: [  # add columns to stats_bugs for new usertag locale
         '''ALTER TABLE stats_bugs ADD COLUMN open_locale INTEGER DEFAULT 0''',
         '''ALTER TABLE stats_bugs ADD COLUMN done_locale INTEGER DEFAULT 0''',
     ],
-    19: [ # add column architecture to stats_pkg_state, stats_builds_per_day and stats_builds_age tables and set previous values to amd64
+    19: [  # add column architecture to stats_pkg_state, stats_builds_per_day
+           # and stats_builds_age tables and set previous values to amd64
         "ALTER TABLE stats_pkg_state ADD COLUMN architecture TEXT NOT NULL DEFAULT 'amd64'",
         "ALTER TABLE stats_builds_per_day ADD COLUMN architecture TEXT NOT NULL DEFAULT 'amd64'",
         "ALTER TABLE stats_builds_age ADD COLUMN architecture TEXT NOT NULL DEFAULT 'amd64'",
     ],
-    20: [ # use (datum, suite, architecture) as primary key for stats_pkg_state
+    20: [  # use (datum, suite, architecture) as primary key for stats_pkg_state
         '''CREATE TABLE stats_pkg_state_tmp
            (datum TEXT NOT NULL,
             suite TEXT NOT NULL,
@@ -460,7 +461,7 @@ schema_updates = {
         '''DROP TABLE stats_pkg_state;''',
         '''ALTER TABLE stats_pkg_state_tmp RENAME TO stats_pkg_state;''',
     ],
-    21: [ # use (datum, suite, architecture) as primary key for stats_builds_per_day
+    21: [  # use (datum, suite, architecture) as primary key for stats_builds_per_day
         '''CREATE TABLE stats_builds_per_day_tmp
                      (datum TEXT NOT NULL,
                       suite TEXT NOT NULL,
@@ -477,7 +478,7 @@ schema_updates = {
         '''DROP TABLE stats_builds_per_day;''',
         '''ALTER TABLE stats_builds_per_day_tmp RENAME TO stats_builds_per_day;''',
     ],
-    22: [ # use (datum, suite, architecture) as primary key for stats_builds_age
+    22: [  # use (datum, suite, architecture) as primary key for stats_builds_age
         '''CREATE TABLE stats_builds_age_tmp
                      (datum TEXT NOT NULL,
                       suite TEXT NOT NULL,
@@ -493,8 +494,8 @@ schema_updates = {
         '''DROP TABLE stats_builds_age;''',
         '''ALTER TABLE stats_builds_age_tmp RENAME TO stats_builds_age;''',
     ],
-    23: [ # save which builders built a package and change the name of the
-          # field keeping the job name
+    23: [  # save which builders built a package and change the name of the
+           # field keeping the job name
         '''CREATE TABLE stats_build_tmp
             (id INTEGER PRIMARY KEY,
              name TEXT NOT NULL,
@@ -515,7 +516,7 @@ schema_updates = {
         'DROP TABLE stats_build',
         'ALTER TABLE stats_build_tmp RENAME TO stats_build',
     ],
-    24: [ # the same as #23 but for the results table
+    24: [  # the same as #23 but for the results table
         '''CREATE TABLE results_tmp
            (id INTEGER PRIMARY KEY,
             package_id INTEGER NOT NULL,
@@ -535,7 +536,7 @@ schema_updates = {
         'DROP TABLE results',
         'ALTER TABLE results_tmp RENAME TO results',
     ],
-    25: [ # rename the builder column also in the schedule table.
+    25: [  # rename the builder column also in the schedule table.
         '''CREATE TABLE schedule_tmp
            (id INTEGER PRIMARY KEY,
             package_id INTEGER NOT NULL,
@@ -556,14 +557,14 @@ schema_updates = {
         'DROP TABLE schedule',
         'ALTER TABLE schedule_tmp RENAME TO schedule',
     ],
-    26: [ # add a column to the schedule table to save the schedule message
+    26: [  # add a column to the schedule table to save the schedule message
         "ALTER TABLE schedule ADD COLUMN message TEXT",
         "ALTER TABLE stats_build ADD COLUMN schedule_message TEXT NOT NULL DEFAULT ''",
     ],
-    27: [ # add column architecture to stats_meta_pkg_state and set previous values to amd64
+    27: [  # add column architecture to stats_meta_pkg_state and set previous values to amd64
         "ALTER TABLE stats_meta_pkg_state ADD COLUMN architecture TEXT NOT NULL DEFAULT 'amd64'",
     ],
-    28: [ # use (datum, suite, architecture, meta_pkg) as primary key for stats_meta_pkg_state
+    28: [  # use (datum, suite, architecture, meta_pkg) as primary key for stats_meta_pkg_state
         '''CREATE TABLE stats_meta_pkg_state_tmp
            (datum TEXT NOT NULL,
             suite TEXT NOT NULL,
@@ -584,7 +585,7 @@ schema_updates = {
 
     # THE FOLLOWING UPDATES CAN ONLY BE PREFORMED ON POSTGRES DATABASE
 
-    29: [ # Add auto incrementing to the id columns of some tables
+    29: [  # Add auto incrementing to the id columns of some tables
         "CREATE SEQUENCE schedule_id_seq",
         "ALTER TABLE schedule ALTER id SET DEFAULT NEXTVAL('schedule_id_seq')",
         "CREATE SEQUENCE manual_scheduler_id_seq",
@@ -598,19 +599,19 @@ schema_updates = {
         "CREATE SEQUENCE results_id_seq",
         "ALTER TABLE results ALTER id SET DEFAULT NEXTVAL('results_id_seq')",
     ],
-    30: [ # Add new table to track diffoscope breake
+    30: [  # Add new table to track diffoscope breake
         '''CREATE TABLE stats_breakages
                      (datum TEXT,
                       diffoscope_timeouts INTEGER,
                       diffoscope_crashes INTEGER,
                       PRIMARY KEY (datum))''',
     ],
-    31: [ # rename the 'testing' suite into 'stretch'
+    31: [  # rename the 'testing' suite into 'stretch'
         "UPDATE {} SET suite='stretch' WHERE suite='testing'".format(t)
-            for t in ("sources", "stats_pkg_state", "stats_builds_per_day",
-                "stats_builds_age", "stats_meta_pkg_state", "stats_build")
+        for t in ("sources", "stats_pkg_state", "stats_builds_per_day",
+                  "stats_builds_age", "stats_meta_pkg_state", "stats_build")
     ],
-    32: [ # copy stretch packages (includng results) in buster
+    32: [  # copy stretch packages (includng results) in buster
         """INSERT INTO sources (name, version, suite, architecture, notify_maintainer)
             SELECT name, version, 'buster', architecture, notify_maintainer
             FROM sources
@@ -630,27 +631,31 @@ schema_updates = {
                 FROM buster AS b JOIN sr ON b.name=sr.name
                     AND b.architecture=sr.architecture""",
     ],
-    33: [ # the message columns.  They are not actually needed.
+    33: [  # the message columns.  They are not actually needed.
         "ALTER TABLE schedule DROP COLUMN message",
         "ALTER TABLE stats_build DROP COLUMN schedule_message",
     ],
-    34: [ # rename status "not for us" to "NFU"
+    34: [  # rename status "not for us" to "NFU"
         "UPDATE results SET status='NFU' WHERE status='not for us'",
         "UPDATE stats_build SET status='NFU' WHERE status='not for us'",
     ],
-    35: [ # rename status "unreproducible" to "FTBR"
+    35: [  # rename status "unreproducible" to "FTBR"
         "UPDATE results SET status='FTBR' WHERE status='unreproducible'",
         "UPDATE stats_build SET status='FTBR' WHERE status='unreproducible'",
         "ALTER TABLE stats_pkg_state RENAME COLUMN unreproducible to FTBR",
         "ALTER TABLE stats_meta_pkg_state RENAME COLUMN unreproducible to FTBR",
         "ALTER TABLE stats_builds_per_day RENAME COLUMN unreproducible to FTBR",
         "ALTER TABLE stats_builds_age " + \
-         "RENAME COLUMN oldest_unreproducible to oldest_FTBR",
+        "RENAME COLUMN oldest_unreproducible to oldest_FTBR",
     ],
-    36: [ # rename status "404" to "E404"
+    36: [  # rename status "404" to "E404"
         "UPDATE results SET status='E404' WHERE status='404'",
         "UPDATE stats_build SET status='E404' WHERE status='404'",
     ],
+    37: [  # change the data type in the stats_build.build_date column
+        "ALTER TABLE stats_build ALTER COLUMN build_date SET DATA TYPE timestamp"
+        " USING build_date::timestamp"
+    ]
 }
 
 



View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/compare/7ee41f26ee054a0738280079568c68eb230fc2f3...2976bc69b744bb7b8317a4beb75fba6e05406394

-- 
View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/compare/7ee41f26ee054a0738280079568c68eb230fc2f3...2976bc69b744bb7b8317a4beb75fba6e05406394
You're receiving this email because of your account on salsa.debian.org.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/qa-jenkins-scm/attachments/20181211/9fc251cd/attachment-0001.html>


More information about the Qa-jenkins-scm mailing list