29278921934

Committed 13 Jul 2026 07:29PM UTC coverage: 79.007% (-1.5%) from 80.479%

Build # 29278921934

Build Type

Pull #142

github

Committed by

pieterlukasse

Commit Message

feat: improve code comments, security of some methods, reused parsing method

Pull Request Pull Request #142: Feat: validate source access for team

Coverage Stats

99 of 196 new or added lines in 8 files covered. (50.51%)

7 existing lines in 3 files now uncovered.

2356 of 2982 relevant lines covered (79.01%)

29.09 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

47.34

/models/source.go

package models

import (
        "github.com/uc-cdis/cohort-middleware/db"
        "github.com/uc-cdis/cohort-middleware/utils"
)

type Source struct {
        SourceId                     int    `json:"source_id"`
        SourceName                   string `json:"source_name"`
        Description                  string `json:"description,omitempty" gorm:"column:description"`
        SourceConnection             string `json:",omitempty"`
        SourceDialect                string `json:",omitempty"`
        Username                     string `json:"-"` // never included
        Password                     string `json:"-"` // never included
        CurrentTeamProjectAccessible string `json:",omitempty" gorm:"column:current_team_project_accessible"`
}

type SourceI interface {
        GetSourceById(id int) (*Source, error)
        GetSourceByName(name string) (*Source, error)
        GetAllSources() ([]*Source, error)
        GetAllSourcesWithTeamProject(teamName string) ([]*Source, error)
        GetAllRoleNamesWithSourceGeneratePermission(sourceId int) ([]string, error)
}

// Returns source name details for given id
func (h Source) GetSourceById(id int) (*Source, error) {
        db2 := db.GetAtlasDB().Db
        var dataSource *Source
        query := db2.Model(&Source{}).
                Select("source_id, source_name").
                Where("source_id = ?", id).
                Where("deleted_date is null")
        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        query.Scan(&dataSource)
        return dataSource, nil
}

func (h Source) getSourceByIdWithConnection(id int) (*Source, error) {
        db2 := db.GetAtlasDB().Db
        var dataSource *Source
        query := db2.Model(&Source{}).
                Select("source_id, source_name, source_connection, source_dialect, username, password").
                Where("source_id = ?", id).
                Where("deleted_date is null")
        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        query.Scan(&dataSource)
        return dataSource, nil
}

type SourceSchema struct {
        SchemaName string
}

func (h Source) GetSourceSchemaNameBySourceIdAndSourceType(id int, sourceType SourceType) (*SourceSchema, error) {
        // special handling of sourceType "Misc", as it is not stored in source_daimon table
        if sourceType == Misc {
                return &SourceSchema{SchemaName: "MISC"}, nil
        }

        if sourceType == Dbo {
                return &SourceSchema{SchemaName: "DBO"}, nil
        }

        // otherwise, get the schema name from source_daimon table
        atlasDb := db.GetAtlasDB()
        db2 := atlasDb.Db
        var sourceSchema *SourceSchema
        query := db2.Model(&Source{}).
                Select("source_daimon.table_qualifier as schema_name").
                Joins("INNER JOIN "+atlasDb.Schema+".source_daimon ON source.source_id = source_daimon.source_id").
                Where("source.source_id = ?", id).
                Where("source_daimon.daimon_type = ?", sourceType).
                Where("source.deleted_date is null")
        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        query.Scan(&sourceSchema)
        return sourceSchema, nil
}

type SourceType int64

const (
        Omop    SourceType = 0 //TODO - we might have to split up into OmopData and OmopVocab in future...
        Results SourceType = 2
        Temp    SourceType = 5
        Misc    SourceType = 6
        Dbo     SourceType = 7
)

// Get the data source details for given source id and source type.
// The source type can be one of the type SourceType.
func (h Source) GetDataSource(sourceId int, sourceType SourceType) *utils.DbAndSchema {
        dataSource, _ := h.getSourceByIdWithConnection(sourceId)

        dbSchema, _ := h.GetSourceSchemaNameBySourceIdAndSourceType(sourceId, sourceType)
        dbSchemaName := dbSchema.SchemaName
        sourceConnection := utils.SourceConnection{SourceConnection: dataSource.SourceConnection,
                Username: dataSource.Username,
                Password: dataSource.Password, // pragma: allowlist secret
        }
        dbAndSchema := utils.GetDataSourceDB(sourceConnection, dbSchemaName)
        return dbAndSchema
}

// Returns source id for given source name
func (h Source) GetSourceByName(name string) (*Source, error) {
        db2 := db.GetAtlasDB().Db
        var dataSource *Source
        query := db2.Model(&Source{}).
                Select("source_id, source_name").
                Where("source_name = ?", name).
                Where("deleted_date is null")
        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        query.Scan(&dataSource)
        return dataSource, nil
}

// Returns list of all active sources
func (h Source) GetAllSources() ([]*Source, error) {
        db2 := db.GetAtlasDB().Db
        var dataSource []*Source
        query := db2.Model(&Source{}).
                Select("source_id, source_name").
                Where("deleted_date is null")
        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        query.Scan(&dataSource)
        return dataSource, nil
}

// Returns a list of all sources, enriched with a boolean field (`current_team_project_accessible`)
// telling whether the source is accessible to the given team or not.
func (h Source) GetAllSourcesWithTeamProject(teamName string) ([]*Source, error) {
        atlasDb := db.GetAtlasDB()
        db2 := atlasDb.Db
        var dataSource []*Source
        query := db2.Table(atlasDb.Schema+".source AS s").
                Select(`
                        s.source_id AS source_id,
                        s.source_name AS source_name,
                        bool_or(sr.name = ?) AS current_team_project_accessible
                `, teamName).
                Joins(`
                        JOIN ` + atlasDb.Schema + `.sec_permission sp
                          ON s.source_key = SUBSTRING(sp.value FROM 'cohortdefinition:\*:generate:(.*?):get')
                `).
                Joins(`
                        JOIN ` + atlasDb.Schema + `.sec_role_permission srp
                          ON sp.id = srp.permission_id
                `).
                Joins(`
                        JOIN ` + atlasDb.Schema + `.sec_role sr
                          ON srp.role_id = sr.id
                `).
                Where("s.deleted_date is null").
                Group("s.source_id, s.source_name")

        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        metaResult := query.Scan(&dataSource)
        if metaResult.Error != nil {
                return nil, metaResult.Error
        }

        for _, source := range dataSource {
                var meta struct {
                        Description string `gorm:"column:description"`
                }

                omopDataSource := h.GetDataSource(source.SourceId, Omop)

                query := omopDataSource.Db.Table(omopDataSource.Schema + ".cdm_source").
                        Select("source_description as description").
                        Limit(1)

                query, cancel := utils.AddTimeoutToQuery(query)
                metaResult := query.Scan(&meta)
                cancel()

                if metaResult.Error != nil {
                        return nil, metaResult.Error
                }

                source.Description = meta.Description
        }

        return dataSource, nil
}

// Returns list of roles names for the roles that contain a permission to generate
// cohorts for a specific source.
func (h Source) GetAllRoleNamesWithSourceGeneratePermission(sourceId int) ([]string, error) {
        atlasDb := db.GetAtlasDB()
        db2 := atlasDb.Db

        type roleRow struct {
                Name string `gorm:"column:name"`
        }

        var rows []roleRow

        query := db2.Table(atlasDb.Schema+".source AS s").
                Select("sr.name AS name").
                Joins(`
                        JOIN `+atlasDb.Schema+`.sec_permission sp
                          ON s.source_key = SUBSTRING(sp.value FROM 'cohortdefinition:\*:generate:(.*?):get')
                `).
                Joins(`
                        JOIN `+atlasDb.Schema+`.sec_role_permission srp
                          ON sp.id = srp.permission_id
                `).
                Joins(`
                        JOIN `+atlasDb.Schema+`.sec_role sr
                          ON srp.role_id = sr.id
                `).
                Where("s.source_id = ?", sourceId).
                Group("sr.name")

        query, cancel := utils.AddTimeoutToQuery(query)
        defer cancel()
        metaResult := query.Scan(&rows)
        if metaResult.Error != nil {
                return nil, metaResult.Error
        }

        roleNames := make([]string, len(rows))
        for i, r := range rows {
                roleNames[i] = r.Name
        }

        return roleNames, nil
}

1	package models
2
3	import (
4	"github.com/uc-cdis/cohort-middleware/db"
5	"github.com/uc-cdis/cohort-middleware/utils"
6	)
7
8	type Source struct {
9	SourceId int `json:"source_id"`
10	SourceName string `json:"source_name"`
11	Description string `json:"description,omitempty" gorm:"column:description"`
12	SourceConnection string `json:",omitempty"`
13	SourceDialect string `json:",omitempty"`
14	Username string `json:"-"` // never included
15	Password string `json:"-"` // never included
16	CurrentTeamProjectAccessible string `json:",omitempty" gorm:"column:current_team_project_accessible"`
17	}
18
19	type SourceI interface {
20	GetSourceById(id int) (*Source, error)
21	GetSourceByName(name string) (*Source, error)
22	GetAllSources() ([]*Source, error)
23	GetAllSourcesWithTeamProject(teamName string) ([]*Source, error)
24	GetAllRoleNamesWithSourceGeneratePermission(sourceId int) ([]string, error)
25	}
26
27	// Returns source name details for given id
28	func (h Source) GetSourceById(id int) (*Source, error) {	2✔
29	db2 := db.GetAtlasDB().Db	2✔
30	var dataSource *Source	2✔
31	query := db2.Model(&Source{}).	2✔
32	Select("source_id, source_name").	2✔
33	Where("source_id = ?", id).	2✔
34	Where("deleted_date is null")	2✔
35	query, cancel := utils.AddTimeoutToQuery(query)	2✔
36	defer cancel()	2✔
37	query.Scan(&dataSource)	2✔
38	return dataSource, nil	2✔
39	}	2✔
40
41	func (h Source) getSourceByIdWithConnection(id int) (*Source, error) {	692✔
42	db2 := db.GetAtlasDB().Db	692✔
43	var dataSource *Source	692✔
44	query := db2.Model(&Source{}).	692✔
45	Select("source_id, source_name, source_connection, source_dialect, username, password").	692✔
46	Where("source_id = ?", id).	692✔
47	Where("deleted_date is null")	692✔
48	query, cancel := utils.AddTimeoutToQuery(query)	692✔
49	defer cancel()	692✔
50	query.Scan(&dataSource)	692✔
51	return dataSource, nil	692✔
52	}	692✔
53
54	type SourceSchema struct {
55	SchemaName string
56	}
57
58	func (h Source) GetSourceSchemaNameBySourceIdAndSourceType(id int, sourceType SourceType) (*SourceSchema, error) {	696✔
59	// special handling of sourceType "Misc", as it is not stored in source_daimon table	696✔
60	if sourceType == Misc {	702✔
61	return &SourceSchema{SchemaName: "MISC"}, nil	6✔
62	}	6✔
63
64	if sourceType == Dbo {	691✔
65	return &SourceSchema{SchemaName: "DBO"}, nil	1✔
66	}	1✔
67
68	// otherwise, get the schema name from source_daimon table
69	atlasDb := db.GetAtlasDB()	689✔
70	db2 := atlasDb.Db	689✔
71	var sourceSchema *SourceSchema	689✔
72	query := db2.Model(&Source{}).	689✔
73	Select("source_daimon.table_qualifier as schema_name").	689✔
74	Joins("INNER JOIN "+atlasDb.Schema+".source_daimon ON source.source_id = source_daimon.source_id").	689✔
75	Where("source.source_id = ?", id).	689✔
76	Where("source_daimon.daimon_type = ?", sourceType).	689✔
77	Where("source.deleted_date is null")	689✔
78	query, cancel := utils.AddTimeoutToQuery(query)	689✔
79	defer cancel()	689✔
80	query.Scan(&sourceSchema)	689✔
81	return sourceSchema, nil	689✔
82	}
83
84	type SourceType int64
85
86	const (
87	Omop SourceType = 0 //TODO - we might have to split up into OmopData and OmopVocab in future...
88	Results SourceType = 2
89	Temp SourceType = 5
90	Misc SourceType = 6
91	Dbo SourceType = 7
92	)
93
94	// Get the data source details for given source id and source type.
95	// The source type can be one of the type SourceType.
96	func (h Source) GetDataSource(sourceId int, sourceType SourceType) *utils.DbAndSchema {	692✔
97	dataSource, _ := h.getSourceByIdWithConnection(sourceId)	692✔
98		692✔
99	dbSchema, _ := h.GetSourceSchemaNameBySourceIdAndSourceType(sourceId, sourceType)	692✔
100	dbSchemaName := dbSchema.SchemaName	692✔
101	sourceConnection := utils.SourceConnection{SourceConnection: dataSource.SourceConnection,	692✔
102	Username: dataSource.Username,	692✔
103	Password: dataSource.Password, // pragma: allowlist secret	692✔
104	}	692✔
105	dbAndSchema := utils.GetDataSourceDB(sourceConnection, dbSchemaName)	692✔
106	return dbAndSchema	692✔
107	}	692✔
108
109	// Returns source id for given source name
110	func (h Source) GetSourceByName(name string) (*Source, error) {	1✔
111	db2 := db.GetAtlasDB().Db	1✔
112	var dataSource *Source	1✔
113	query := db2.Model(&Source{}).	1✔
114	Select("source_id, source_name").	1✔
115	Where("source_name = ?", name).	1✔
116	Where("deleted_date is null")	1✔
117	query, cancel := utils.AddTimeoutToQuery(query)	1✔
118	defer cancel()	1✔
119	query.Scan(&dataSource)	1✔
120	return dataSource, nil	1✔
121	}	1✔
122
123	// Returns list of all active sources
124	func (h Source) GetAllSources() ([]*Source, error) {	12✔
125	db2 := db.GetAtlasDB().Db	12✔
126	var dataSource []*Source	12✔
127	query := db2.Model(&Source{}).	12✔
128	Select("source_id, source_name").	12✔
129	Where("deleted_date is null")	12✔
130	query, cancel := utils.AddTimeoutToQuery(query)	12✔
131	defer cancel()	12✔
132	query.Scan(&dataSource)	12✔
133	return dataSource, nil	12✔
134	}	12✔
135
136	// Returns a list of all sources, enriched with a boolean field (`current_team_project_accessible`)
137	// telling whether the source is accessible to the given team or not.
138	func (h Source) GetAllSourcesWithTeamProject(teamName string) ([]*Source, error) {	×
139	atlasDb := db.GetAtlasDB()	×
140	db2 := atlasDb.Db	×
141	var dataSource []*Source	×
142	query := db2.Table(atlasDb.Schema+".source AS s").	×
143	Select(`	×
144	s.source_id AS source_id,	×
145	s.source_name AS source_name,	×
146	bool_or(sr.name = ?) AS current_team_project_accessible	×
147	`, teamName).	×
148	Joins(`	×
NEW 149	JOIN ` + atlasDb.Schema + `.sec_permission sp	×
150	ON s.source_key = SUBSTRING(sp.value FROM 'cohortdefinition:\:generate:(.?):get')	×
151	`).	×
152	Joins(`	×
NEW 153	JOIN ` + atlasDb.Schema + `.sec_role_permission srp	×
154	ON sp.id = srp.permission_id	×
155	`).	×
156	Joins(`	×
NEW 157	JOIN ` + atlasDb.Schema + `.sec_role sr	×
158	ON srp.role_id = sr.id	×
159	`).	×
160	Where("s.deleted_date is null").	×
161	Group("s.source_id, s.source_name")	×
162		×
163	query, cancel := utils.AddTimeoutToQuery(query)	×
164	defer cancel()	×
165	metaResult := query.Scan(&dataSource)	×
166	if metaResult.Error != nil {	×
167	return nil, metaResult.Error	×
168	}	×
169
170	for _, source := range dataSource {	×
171	var meta struct {	×
172	Description string `gorm:"column:description"`	×
173	}	×
174		×
175	omopDataSource := h.GetDataSource(source.SourceId, Omop)	×
176		×
177	query := omopDataSource.Db.Table(omopDataSource.Schema + ".cdm_source").	×
178	Select("source_description as description").	×
179	Limit(1)	×
180		×
181	query, cancel := utils.AddTimeoutToQuery(query)	×
182	metaResult := query.Scan(&meta)	×
183	cancel()	×
184		×
185	if metaResult.Error != nil {	×
186	return nil, metaResult.Error	×
187	}	×
188
189	source.Description = meta.Description	×
190	}
191
192	return dataSource, nil	×
193	}
194
195	// Returns list of roles names for the roles that contain a permission to generate
196	// cohorts for a specific source.
NEW 197	func (h Source) GetAllRoleNamesWithSourceGeneratePermission(sourceId int) ([]string, error) {	×
NEW 198	atlasDb := db.GetAtlasDB()	×
NEW 199	db2 := atlasDb.Db	×
NEW 200		×
NEW 201	type roleRow struct {	×
NEW 202	Name string `gorm:"column:name"`	×
NEW 203	}	×
NEW 204		×
NEW 205	var rows []roleRow	×
NEW 206		×
NEW 207	query := db2.Table(atlasDb.Schema+".source AS s").	×
NEW 208	Select("sr.name AS name").	×
NEW 209	Joins(`	×
NEW 210	JOIN `+atlasDb.Schema+`.sec_permission sp	×
NEW 211	ON s.source_key = SUBSTRING(sp.value FROM 'cohortdefinition:\:generate:(.?):get')	×
NEW 212	`).	×
NEW 213	Joins(`	×
NEW 214	JOIN `+atlasDb.Schema+`.sec_role_permission srp	×
NEW 215	ON sp.id = srp.permission_id	×
NEW 216	`).	×
NEW 217	Joins(`	×
NEW 218	JOIN `+atlasDb.Schema+`.sec_role sr	×
NEW 219	ON srp.role_id = sr.id	×
NEW 220	`).	×
NEW 221	Where("s.source_id = ?", sourceId).	×
NEW 222	Group("sr.name")	×
NEW 223		×
NEW 224	query, cancel := utils.AddTimeoutToQuery(query)	×
NEW 225	defer cancel()	×
NEW 226	metaResult := query.Scan(&rows)	×
NEW 227	if metaResult.Error != nil {	×
NEW 228	return nil, metaResult.Error	×
NEW 229	}	×
230
NEW 231	roleNames := make([]string, len(rows))	×
NEW 232	for i, r := range rows {	×
NEW 233	roleNames[i] = r.Name	×
NEW 234	}	×
235
NEW 236	return roleNames, nil	×
237	}

uc-cdis / cohort-middleware / 29278921934

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous