Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-21794

Add origin component to all autoincrement data IDs in Registry

    XMLWordPrintable

    Details

      Description

      Make all autoincrement primary keys into a compound key containing an autoincrement key and a literal taken from config.

      Compound primary keys that include autoincrement fields are not natively supported in SQLite.  Work around this by separating each table with such a key into a "local" table with an autoincrement, non-compound primary key and a "remote" with a non-autoincrement compound primary key, joined by a union view when needed.

        Attachments

          Issue Links

            Activity

            Hide
            jbosch Jim Bosch added a comment -

            Michelle Gower has proposed "origin" instead of "site" for the name of these fields; I like that better, too.

            Show
            jbosch Jim Bosch added a comment - Michelle Gower has proposed "origin" instead of "site" for the name of these fields; I like that better, too.
            Hide
            tjenness Tim Jenness added a comment -

            I worry a bit that we may want to do this registry change fairly soon as well. Especially if we really do think it would be only 3 SPs.

            I assume this would be relevant for the shared-nothing processing where we are passing sqlite files between workflow jobs and merging them.

            Show
            tjenness Tim Jenness added a comment - I worry a bit that we may want to do this registry change fairly soon as well. Especially if we really do think it would be only 3 SPs. I assume this would be relevant for the shared-nothing processing where we are passing sqlite files between workflow jobs and merging them.
            Hide
            jbosch Jim Bosch added a comment -

            I don't know how I ever thought this was 3 story points, but the disruption downstream alone is at least that much.

            I was thinking the faster way to get up and running for shared-nothing processing was export-import via yaml, not merging SQLite files directly. That may well be slower than merging SQLite files, but the functionality is either already present or very close. And I think we probably to start mapping multiple quanta (i.e. subgraphs) to single workflow jobs (as we've always talked about) for shared-nothing to be at all efficient anyway, and enough of that will make export-import time negligible (unless it's pathologically slow in ways we need to fix anyway).

            Show
            jbosch Jim Bosch added a comment - I don't know how I ever thought this was 3 story points, but the disruption downstream alone is at least that much. I was thinking the faster way to get up and running for shared-nothing processing was export-import via yaml, not merging SQLite files directly. That may well be slower than merging SQLite files, but the functionality is either already present or very close. And I think we probably to start mapping multiple quanta (i.e. subgraphs) to single workflow jobs (as we've always talked about) for shared-nothing to be at all efficient anyway, and enough of that will make export-import time negligible (unless it's pathologically slow in ways we need to fix anyway).
            Hide
            tjenness Tim Jenness added a comment -

            Do we know enough to be able to adjust the schema now but without trying to use any of the machinery for it? So using an origin that is a constant and never used. That would help with downstream implementation if we were right about the schema tweaks.

            Show
            tjenness Tim Jenness added a comment - Do we know enough to be able to adjust the schema now but without trying to use any of the machinery for it? So using an origin that is a constant and never used. That would help with downstream implementation if we were right about the schema tweaks.
            Hide
            jbosch Jim Bosch added a comment -

            That would certainly make it somewhat easier. I'd have to think harder to have a sense for whether it would be a lot or a little easier.

            Show
            jbosch Jim Bosch added a comment - That would certainly make it somewhat easier. I'd have to think harder to have a sense for whether it would be a lot or a little easier.
            Hide
            tjenness Tim Jenness added a comment -

            We talked about this ticket at today's standup and came to the conclusion that it's going to be a very invasive change that we can't get done before schema freeze. The handling of the compound primary keys is going to be difficult and even if we got it right now we'd still have to patch existing repos to add the correct origin values later. This means we'd like to put more thought into the implementation.

            In the interim for sqlite merging we should pursue import/export of YAML files and see if workflow jobs can pass export files around that can be imported sequentially at the start of a job and have a single export at the end of each job.

            Show
            tjenness Tim Jenness added a comment - We talked about this ticket at today's standup and came to the conclusion that it's going to be a very invasive change that we can't get done before schema freeze. The handling of the compound primary keys is going to be difficult and even if we got it right now we'd still have to patch existing repos to add the correct origin values later. This means we'd like to put more thought into the implementation. In the interim for sqlite merging we should pursue import/export of YAML files and see if workflow jobs can pass export files around that can be imported sequentially at the start of a job and have a single export at the end of each job.
            Hide
            jbosch Jim Bosch added a comment -

            Closing this as invalid, since we've switched to UUIDs instead.

             

            Show
            jbosch Jim Bosch added a comment - Closing this as invalid, since we've switched to UUIDs instead.  

              People

              Assignee:
              jbosch Jim Bosch
              Reporter:
              jbosch Jim Bosch
              Watchers:
              Andy Salnikov, Jim Bosch, Tim Jenness
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Jenkins

                  No builds found.