1"use strict";(self.webpackChunkpackit_dev=self.webpackChunkpackit_dev||[]).push([[9355],{15680:(e,t,n)=>{n.d(t,{xA:()=>u,yg:()=>g});var i=n(96540);function r(e,t,n){return t in e?Object.defineProperty(e,t,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[t]=n,e}function o(e,t){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);t&&(i=i.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),n.push.apply(n,i)}return n}function a(e){for(var t=1;t<arguments.length;t++){var n=null!=arguments[t]?arguments[t]:{};t%2?o(Object(n),!0).forEach((function(t){r(e,t,n[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):o(Object(n)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(n,t))}))}return e}function l(e,t){if(null==e)return{};var n,i,r=function(e,t){if(null==e)return{};var n,i,r={},o=Object.keys(e);for(i=0;i<o.length;i++)n=o[i],t.indexOf(n)>=0||(r[n]=e[n]);return r}(e,t);if(Object.getOwnPropertySymbols){var o=Object.getOwnPropertySymbols(e);for(i=0;i<o.length;i++)n=o[i],t.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(r[n]=e[n])}return r}var s=i.createContext({}),p=function(e){var t=i.useContext(s),n=t;return e&&(n="function"==typeof e?e(t):a(a({},t),e)),n},u=function(e){var t=p(e.components);return i.createElement(s.Provider,{value:t},e.children)},c="mdxType",m={inlineCode:"code",wrapper:function(e){var t=e.children;return i.createElement(i.Fragment,{},t)}},d=i.forwardRef((function(e,t){var n=e.components,r=e.mdxType,o=e.originalType,s=e.parentName,u=l(e,["components","mdxType","originalType","parentName"]),c=p(n),d=r,g=c["".concat(s,".").concat(d)]||c[d]||m[d]||o;return n?i.createElement(g,a(a({ref:t},u),{},{components:n})):i.createElement(g,a({ref:t},u))}));function g(e,t){var n=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var o=n.length,a=new Array(o);a[0]=d;var l={};for(var s in t)hasOwnProperty.call(t,s)&&(l[s]=t[s]);l.originalType=e,l[c]="string"==typeof e?e:r,a[1]=l;for(var p=2;p<o;p++)a[p]=n[p];return i.createElement.apply(null,a)}return i.createElement.apply(null,n)}d.displayName="MDXCreateElement"},72036:(e,t,n)=>{n.r(t),n.d(t,{assets:()=>s,contentTitle:()=>a,default:()=>m,frontMatter:()=>o,metadata:()=>l,toc:()=>p});var i=n(58168),r=(n(96540),n(15680));const o={title:"Usage of composite type to store the pipelines",authors:"lbarczio"},a=void 0,l={unversionedId:"database/composite-types",id:"database/composite-types",title:"Usage of composite type to store the pipelines",description:"Composite column types:",source:"@site/research/database/composite-types.md",sourceDirName:"database",slug:"/database/composite-types",permalink:"/research/database/composite-types",draft:!1,editUrl:"https://github.com/packit/research/tree/main/research/database/composite-types.md",tags:[],version:"current",frontMatter:{title:"Usage of composite type to store the pipelines",authors:"lbarczio"},sidebar:"autogenerated",previous:{title:"Database",permalink:"/research/category/database"},next:{title:"Data stores",permalink:"/research/database/data-stores"}},s={},p=[{value:"Current pipeline model",id:"current-pipeline-model",level:2},{value:"1.option",id:"1option",level:2},{value:"Issues",id:"issues",level:3},{value:"1. querying concrete steps of the pipeline",id:"1-querying-concrete-steps-of-the-pipeline",level:4},{value:"2. data duplication",id:"2-data-duplication",level:4},{value:"Average number of Copr builds using the same SRPM build",id:"average-number-of-copr-builds-using-the-same-srpm-build",level:5},{value:"Average number of tests using the same Copr build",id:"average-number-of-tests-using-the-same-copr-build",level:5},{value:"2.option",id:"2option",level:2},{value:"Issues",id:"issues-1",level:3},{value:"1. querying concrete steps of the pipeline",id:"1-querying-concrete-steps-of-the-pipeline-1",level:4},{value:"2. more complicated manipulation with the data in general",id:"2-more-complicated-manipulation-with-the-data-in-general",level:4},{value:"3. race conditions",id:"3-race-conditions",level:4},{value:"4. more complicated implementation",id:"4-more-complicated-implementation",level:4}],u={toc:p},c="wrapper";function m({components:e,...t}){return(0,r.yg)(c,(0,i.A)({},u,t,{components:e,mdxType:"MDXLayout"}),(0,r.yg)("p",null,(0,r.yg)("a",{parentName:"p",href:"https://docs.sqlalchemy.org/en/14/orm/composites.html"},"Composite column types"),":\nSets of columns can be associated with a single user-defined datatype, which in modern use is normally a Python dataclass. The ORM provides a single attribute which represents the group of columns using the class you provide."),(0,r.yg)("p",null,"Examples how can this be done:"),(0,r.yg)("ol",null,(0,r.yg)("li",{parentName:"ol"},"sqlalchemy_utils ",(0,r.yg)("a",{parentName:"li",href:"https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html?highlight=CompositeType#module-sqlalchemy_utils.types.pg_composite"},"CompositeType"),":\na custom SQLAlchemy type designed to work with PostgreSQL's composite types:")),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-python"},"class MyCompositeType(CompositeType):\n attribute1 = Column(Integer)\n attribute2 = Column(String)\n\nclass MyModel(Base):\n __tablename__ = 'my_table'\n\n id = Column(Integer, primary_key=True)\n my_composite = Column(MyCompositeType)\n\n")),(0,r.yg)("ol",{start:2},(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("a",{parentName:"li",href:"https://docs.sqlalchemy.org/en/14/orm/composites.html#sqlalchemy.orm.composite"},(0,r.yg)("inlineCode",{parentName:"a"},"sqlalchemy.orm.composite()")),": allows to define composite types as a Python class:")),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-python"},"\nclass MyCompositeType(object):\n def __init__(self, attribute1, attribute2):\n self.attribute1 = attribute1\n self.attribute2 = attribute2\n\nclass MyModel(Base):\n __tablename__ = 'my_table'\n\n id = Column(Integer, primary_key=True)\n composite = composite(MyCompositeType, Column('attribute1', Integer), Column('attribute2', Integer))\n")),(0,r.yg)("ol",{start:3},(0,r.yg)("li",{parentName:"ol"},"subclassing custom class by ",(0,r.yg)("a",{parentName:"li",href:"https://docs.sqlalchemy.org/en/14/core/custom_types.html#sqlalchemy.types.TypeDecorator"},(0,r.yg)("inlineCode",{parentName:"a"},"sqlalchemy.types.TypeDecorator"))," and implementing the necessary conversion methods")),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-python"},'\n@dataclass\nclass MyCompositeType:\n attribute1: int\n attribute2: int\n\nclass MyCompositeTypeDecorator(TypeDecorator):\n impl = SQLInteger\n\n def process_bind_param(self, value, dialect):\n if value is not None:\n return f"{value.attribute1},{value.attribute2}"\n\n def process_result_value(self, value, dialect):\n if value is not None:\n attribute1, attribute2 = map(int, value.split(","))\n return MyCompositeType(attribute1, attribute2)\n\nclass MyModel(Base):\n __tablename__ = \'my_table\'\n\n id = Column(Integer, primary_key=True)\n composite = Column(MyCompositeTypeDecorator())\n\n')),(0,r.yg)("p",null,"For simplification, let's work with a pipeline model that has only SRPM, Copr build and test."),(0,r.yg)("h2",{id:"current-pipeline-model"},"Current pipeline model"),(0,r.yg)("p",null,"Pipeline model in current fashion:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-python"},'class PipelineModel(Base):\n __tablename__ = "pipelines"\n id = Column(Integer, primary_key=True)\n datetime = Column(DateTime, default=datetime.utcnow)\n\n job_trigger_id = Column(Integer, ForeignKey("job_triggers.id"))\n job_trigger = relationship("JobTriggerModel", back_populates="runs")\n\n srpm_build_id = Column(Integer, ForeignKey("srpm_builds.id"), index=True)\n srpm_build = relationship("SRPMBuildModel", back_populates="runs")\n copr_build_group_id = Column(\n Integer, ForeignKey("copr_build_groups.id"), index=True\n )\n copr_build_group = relationship("CoprBuildGroupModel", back_populates="runs")\n test_run_group_id = Column(\n Integer, ForeignKey("tft_test_run_groups.id"), index=True\n )\n test_run_group = relationship("TFTTestRunGroupModel", back_populates="runs")\n\n')),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"has the foreign keys to other tables to reference steps of the pipeline (groups), the group then references particular\ntargets, therefore when getting the whole pipeline, join on multiple tables has to happen"),(0,r.yg)("li",{parentName:"ul"},"with the composite types we would like to solve the current need of doing multiple joins to get the data about\none pipeline")),(0,r.yg)("h2",{id:"1option"},"1.option"),(0,r.yg)("p",null,"How could the model look like when using composite type on target level:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"storing the data for the particular target directly in pipeline"),(0,r.yg)("li",{parentName:"ul"},"this would be a step back since we already did the grouping refactoring")),(0,r.yg)("h3",{id:"issues"},"Issues"),(0,r.yg)("h4",{id:"1-querying-concrete-steps-of-the-pipeline"},"1. querying concrete steps of the pipeline"),(0,r.yg)("p",null,"Examples of when this happens:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"when updating Copr build status in DB, we get the corresponding builds via the build ID from C
1opr (index)"),(0,r.yg)("li",{parentName:"ul"},"when updating TF run status in DB, we get the corresponding test run via the TF pipeline ID (index)"),(0,r.yg)("li",{parentName:"ul"},"babysit tasks - they get all the pending Copr builds / Test runs"),(0,r.yg)("li",{parentName:"ul"},"when triggering ",(0,r.yg)("inlineCode",{parentName:"li"},"/packit test")," - we get the latest Copr build model with corresponding commit SHA")),(0,r.yg)("h4",{id:"2-data-duplication"},"2. data duplication"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"updating SRPM build would require getting all pipelines and updating the data everywhere")),(0,r.yg)("h5",{id:"average-number-of-copr-builds-using-the-same-srpm-build"},"Average number of Copr builds using the same SRPM build"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre"},"packit=# SELECT AVG(copr_build_target_count) FROM (\n SELECT COUNT(DISTINCT copr_build_targets.id) AS copr_build_target_count\n FROM pipelines\n JOIN copr_build_targets ON pipelines.copr_build_group_id = copr_build_targets.copr_build_group_id\n GROUP BY pipelines.srpm_build_id\n) AS copr_targets_count;\n avg\n--------------------\n 6.4208079446515274\n(1 row)\n")),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"similar situation for Copr builds \u22c4 TF runs, since the relationship can be 1:n")),(0,r.yg)("h5",{id:"average-number-of-tests-using-the-same-copr-build"},"Average number of tests using the same Copr build"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre"},"packit=# SELECT AVG(test_run_count) AS average_test_runs FROM (\n SELECT copr_id, COUNT(DISTINCT tft_id) AS test_run_count\n FROM tf_copr_build_association_table\n GROUP BY copr_id\n) AS tf_counts;\n average_test_runs\n--------------------\n 1.2618628164375168\n(1 row)\n")),(0,r.yg)("h2",{id:"2option"},"2.option"),(0,r.yg)("p",null,"How could the model look like when using composite type on group level:"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},'"groups" stored together in one pipeline'),(0,r.yg)("li",{parentName:"ul"},"mapping build -> TF needed")),(0,r.yg)("h3",{id:"issues-1"},"Issues"),(0,r.yg)("h4",{id:"1-querying-concrete-steps-of-the-pipeline-1"},"1. querying concrete steps of the pipeline"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"same as in the previous case.")),(0,r.yg)("h4",{id:"2-more-complicated-manipulation-with-the-data-in-general"},"2. more complicated manipulation with the data in general"),(0,r.yg)("h4",{id:"3-race-conditions"},"3. race conditions"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"e.g. updating 2 Copr builds of the same pipeline at the same time")),(0,r.yg)("h4",{id:"4-more-complicated-implementation"},"4. more complicated implementation"))}m.isMDXComponent=!0}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.