Page MenuHomePhabricator

[web-db] add function for parsing SQLite query
ClosedPublic

Authored by kamil on Mar 21 2023, 3:51 AM.
Tags
None
Referenced Files
Unknown Object (File)
Sat, Nov 23, 10:22 AM
Unknown Object (File)
Sat, Nov 23, 9:37 AM
Unknown Object (File)
Mon, Nov 18, 4:06 PM
Unknown Object (File)
Mon, Nov 18, 3:54 PM
Unknown Object (File)
Mon, Nov 18, 3:30 PM
Unknown Object (File)
Wed, Nov 6, 3:15 AM
Unknown Object (File)
Mon, Nov 4, 11:10 PM
Unknown Object (File)
Mon, Nov 4, 11:10 PM
Subscribers

Details

Summary

Function will help work with query results.
For this table:

idx|text
1|test1
2|test2

sql.js result is:

[
  {
    columns: ['idx', 'text'],
    values: [
      [1, 'test1'],
      [2, 'test2'],
    ],
  },
];

This function converts this into:

[
   { idx: 1, text: 'test1' },
   { idx: 2, text: 'test2' },
]
Test Plan

Run tests from D7119

Diff Detail

Repository
rCOMM Comm
Lint
No Lint Coverage
Unit
No Test Coverage

Event Timeline

kamil held this revision as a draft.
kamil edited the test plan for this revision. (Show Details)
kamil edited the summary of this revision. (Show Details)
kamil published this revision for review.Mar 21 2023, 4:11 AM
ashoat requested changes to this revision.Mar 21 2023, 7:21 AM
ashoat added inline comments.
web/database/utils/db-utils.js
3 ↗(On Diff #23917)

This is a rare case where we can prefer a non-readonly type. In general we want to be "flexible" with the caller. It's more flexible to have read-only types for inputs, but more flexible to allow callers to do whatever they want with the return. Since we are constructing a brand new array in this function, it should be okay to allow the caller to do whatever they want

3 ↗(On Diff #23917)

Where is this type QueryExecResult defined? Is it built into Flow? If so, can you link where it is defined in the Flow lib?

6 ↗(On Diff #23917)

Why is this any? You should always expect your reviewers to call you out on this, so you should annotate your diffs ahead of time... otherwise it is a wasted review cycle

This revision now requires changes to proceed.Mar 21 2023, 7:21 AM
web/database/utils/db-utils.js
3 ↗(On Diff #23917)

This is a rare case where we can prefer a non-readonly type. In general we want to be "flexible" with the caller. It's more flexible to have read-only types for inputs, but more flexible to allow callers to do whatever they want with the return. Since we are constructing a brand new array in this function, it should be okay to allow the caller to do whatever they want

Thanks for the explanation, makes sense

Where is this type QueryExecResult defined? Is it built into Flow? If so, can you link where it is defined in the Flow lib?

In D7101, I was in the middle of creating a stack and didn't set the parent diff before your review, my bad

6 ↗(On Diff #23917)

Why is this any?

There is no easy way to check flow types in runtime as they don't exist there. On the keyserver, there is simply an Object type returned from the database.
Here I wanted to base this on generics, but without any annotations flow will complain with object type [1] is incompatible with T. I think object is almost the same as any in this context so I think it was better to do it that way.

You should always expect your reviewers to call you out on this, so you should annotate your diffs ahead of time... otherwise, it is a wasted review cycle

I am not sure what is your point if you talking about the fact that I didn't add any explanation - that's fair, I thought it was obvious since it's runtime-related types, but that's a bad assumption from my side.

If you're implying that that's a bad approach codewise, I have two alternatives:

  1. remove any and generic T and base on Object.
  2. parse each result separately and do sth like:
type ClientDBDraftInfo = {
  +key: string,
  +text: string,
};
function parseDraftResult(result: QueryExecResult): ClientDBDraftInfo[] {
  const { columns, values } = result;
  return values.map(rowResult => {
    const keyId = columns.indexOf('key');
    const textId = columns.indexOf('text');
    if (keyId < 0 || textId < 0) {
      throw new Error('Invalid result: missing columns');
    }

    const key = rowResult[keyId];
    if (typeof key !== 'string') {
      throw new Error('Invalid key type');
    }
    const text = rowResult[textId];
    if (typeof text !== 'string') {
      throw new Error('Invalid text type');
    }
    return {
      key,
      text,
    };
  });
}

but I'm not convinced if this is really needed, I would rather leave it as it is or use 1 and add invariants on results when there will be needed.
LMK what do you think

ashoat requested changes to this revision.Mar 22 2023, 2:53 PM

We should not be polluting the global namespace in D7101. You should not be able to use QueryExecResult without importing it unless it's in the Flow lib.

I am not sure what is your point if you talking about the fact that I didn't add any explanation - that's fair, I thought it was obvious since it's runtime-related types, but that's a bad assumption from my side.

Yeah, that's what I meant. In the line you quoted, I said "you should annotate your diffs ahead of time" – the indication is that you should expect your reviewer to request changes on any diff with any, so you should give an explanation for why you're using any instead of waiting for your reviewer to request changes.

I don't think doing input validation for SQL results is necessary – the any is fine here. It would probably have been more clear how / why you're using any if these utility functions were introduced in the same diff where they are used.

This revision now requires changes to proceed.Mar 22 2023, 2:53 PM

I don't think doing input validation for SQL results is necessary – the any is fine here. It would probably have been more clear how / why you're using any if these utility functions were introduced in the same diff where they are used.

I think that introducing the validation makes sense. It is going to be rather cheap, but will prevent a really expensive debugging in a case where we had an invalid database entry, fetched it and returned annotated as a correct type - this might cause a bug in totally unrelated place and reading the code wouldn't be too efficient in finding it. So it's a lot better to detect such an issue as soon as possible.

The problem is that we would need to validate each query result manually, going through each column one-by-one and checking the result type. This isn’t something we do today for MySQL queries typically. If we feel strongly about doing input validation for SQL query results we could do with tcomb, but I’m not sure that the effort is worth it.

We should not be polluting the global namespace in D7101. You should not be able to use QueryExecResult without importing it unless it's in the Flow lib.

Fixed

Yeah, that's what I meant. In the line you quoted, I said "you should annotate your diffs ahead of time" – the indication is that you should expect your reviewer to request changes on any diff with any, so you should give an explanation for why you're using any instead of waiting for your reviewer to request changes.

Thanks for the explanation, noted!

RE: validation
I created follow-up task: ENG-3382, let's get back to this later

This revision is now accepted and ready to land.Mar 23 2023, 12:38 PM
web/database/utils/db-utils.js
19 ↗(On Diff #24019)

It might be better to name this something more specific, eg. parseMultiStatementSQLiteResult or something

web/database/utils/db-utils.js
19 ↗(On Diff #24019)

Or I guess SQLite always returns an array of statements with an array of results with an array of columns inside it?

It might be helpful to assert that T must be an Object. This will help with readability too, I think