Approaches for querying the PostgreSQL?

Technical
1

This is a question about approaching how to query the PostgreSQL database, generated by the OpenTAP resultlister.

I use the PostgreSQL resultlistener to store data.
I use ASP.NET MVC as backend technology to retrieve data and visualize it.

I used Entity Framework (EF) to generate ‘entities’ from the database schema, created by the resultlistener. Just for information: entities are basically C# model classes. EF generates a DbContext, where one can specify DbSets for entities you want to query

Then, I query the database with methods using LINQ queries, like the following:

        /// <summary>
        /// Get runid's from table testrun, given a planrunid
        /// One planrunid may return multiple runid's.
        /// </summary>
        /// <param name="planRunId"></param>
        /// <returns></returns>
        public IEnumerable<int> GetTestStepRunIds(int planRunId)
        {
            // Equivalent SQL query for planRunId = 167:
            //SELECT testrun.runid
            //FROM testrun
            //WHERE testrun.PlanRunID =  167
            var res = from t in ctx.Testrun
                      where t.Planrunid == planRunId
                      select t.Runid;
            var list = res.ToList();
            logger.LogInformation("Count: " + list.Count);
            return list;
        }

Another example:

    // Gets the plan runs that are associated to a serial ID. Serial ID is logged in a test step.
    public List<PcbPlanrunViewModel> GetAssociatedPlanRuns(string serialId)
    {
        var res = from r in ctx.Result
                  join s in ctx.Resultseries 
                  on r.Resultseriesid equals s.Resultseriesid
                  join t in ctx.Testrun 
                  on s.Runid equals t.Runid
                  where r.Dim0 == serialId
                  select new PcbPlanrunViewModel { PlanRunId = t.Runid };
        var list = res.ToList();
        logger.LogInformation("Count: " + list.Count);
        return list;
    }

However, one disadvantage with this approach is that the entities are static, while the amount of columns can be dynamic.
I think the same problem will come up with other ORM tools.

To illustrate the problem, here is one method:

    private Models.OpentapDb.Result GetResultFromResultSeriesId(int resultseriesId)
    {
        // Select the result
        var res = from r in ctx.Result
                  where r.Resultseriesid == resultseriesId
                  select new Models.OpentapDb.Result
                  {
                      Resultid = r.Resultid,
                      Resultseries = r.Resultseries,
                      Resultseriesid = r.Resultseriesid,
                      Dim0 = r.Dim0,
                      Dim1 = r.Dim1,
                      Dim2 = r.Dim2,
                      Dim3 = r.Dim3,
                      Dim4 = r.Dim4,  // What if the table has more than 5 dimensions?
                  };
        return res.First(); // For simplicity
    }

So one solution is to count the amount of columns and use SQL, not entities. However, I haven’t been able to combine this approach.

However, overall, I feel like I’m not sure if this is the approach one should take.

Are there some approaches that are recommended to query a database that has a structure like the one generated by this resultlistener?

I also see that the OpenTap project has interfaces like ResultStore and ResultColumn. Perhaps one should use these classes to retrieve data?

When displaying tables with in the frontend, I think it’s best to think in ‘adding columns’, instead of ‘adding rows’. I think it’s a good idea to work here with a column object.

1 Like
2

Hi Nick,

Honestly getting the results back from the result stores is a quite rare operation, so the API is not very documented. From the Run Explorer you can export your results into any format you like, as long as there is a ResultListener for it, so you can use that to generate CSV files.

If you want, you can also use the IResultStore API, which the Postgresql database implements.

It works like this:

// get the step and test plan runs
var entries = resultStore.GetEntries(new SearchCondition { Operation = new SearchChildrenOf(planRuns), GetParents = !isPlan, GetChildren = isPlan }, new List<string>(), true).ToList();
// now get the actual plans
var planRuns = entries.Where(ent => ent.ObjectType == "Plan Run");
foreach (var planRun in planRuns){
    resultStore.GetTestPlanString(planRun); // if you want..
   foreach(var steprun in  entries.Where(x => x.Parent == planRun)){
      // do something with the step run.
    }
}
4 Likes
3

Hi Rolf,

Thanks for your reply. I will look into this.

2 Likes
4

@nick1 , have you thought about creating your own ResultsListener? You then have control how the results are stored and thus queried and presented.

3 Likes
5

Hi
I want to use Entity Framework Core with Npgsql to access data stored in Postgres database. However using Npgsql requires all tables to have a Primary key. But tables “testrun2attachment” and “testrun2params” do not have primary key!