wisnesky
commented
4 years ago Ah, good question: the answer is, you can’t. CQL will not allow you to use generator names as data, as that would break the principle that no CQL query can distinguish isomorphic databases.
Instead, anything that you want to be treated as data must be imported as an attribute. Think of the “id” column being imported as a “meaningless autogenerated row identifier” used only to connect foreign keys. If you want to get access to the value, you’ll need an attribute, which you can then populate using the id column. As your data migrates, these generators will change, but the value held in the attribute will not.
It looks like the description is a derived attribute. There’s a number of ways to approach your problem, but probably the simplest way, based on your description of only having a few input types and having conditional logic, is to define a new typeside, say sql’, that includes a function lookup : Varchar -> Varchar (or similar). Its body would say something like “if input[0] == 1 then Alice else if input[0] == 2 then bob else throw error” or similar. Anywhere that CQL accepts the sql typeside it should also accept sql’; if not, please report.
Then, you import your data as you are doing now, not onto S, but S on the sql’ typeside. Then you create a new schema S’, importing S, with an observation equation saying “forall x. x.derived = lookup(x.id_as_att)”, and perform a sigma of the imported data along the inclusion mapping from S to S’. That’s a general technique for populating derived attributes, although there are others (for example, we could write CQL ‘constraints’ instead of a typeside function). In fact, the attributes need not be derived; you can use this technique to e.g. solve simultaneous systems of equations (although this will stress the automated theorem prover, of course).
On Jul 10, 2020, at 9:45 AM, Tim Pierson notifications@github.com wrote:
Sorry if this is not the place for these questions, but I couldn't figure this out from the examples.
I have a dataset from postgres that has a number of columns defined as non-sequential, integer-valued enumerations. Elsewhere, there is documentation on each of element of these enumerations. How can I import the data from jdbc and ensure that the appropriate generators use the correct id values for the correct multi equations?
For example, say I had the following:
schema S = literal : sql { entities Foo Bar foreign_keys bar_type : Foo -> Bar attributes bar_type_description : Bar -> Varchar }
instance J = import_jdbc "jdbc:postgresql://localhost/timpierson?setSchema=govt_usa_fed_cfpb_hmda" : S { Foo -> " SELECT row_number() OVER () as id , cast(bar_type as integer) as bar_type from foo " Bar -> " SELECT cast(bar_type as integer) as id, null as bar_type_description from foo " }
instance BarTypes = literal : S { imports J generators b1 b3 b1111 : Bar multi_equations bar_type_description -> {b1 Bur, b3 Bhar, b1111 Bargh}
} And in the data bar_type is only ever one of 1, 3, 1111; I'd like to make sure that the multi equations provide the correct string for the correct id.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/CategoricalData/CQL/issues/46, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2QKNZ5TNFYKU4OR4JXTSLR25ARBANCNFSM4OWYD5EA.
o1lo01ol1o
Sorry if this is not the place for these questions, but I couldn't figure this out from the examples.
I have a dataset from postgres that has a number of columns defined as non-sequential, integer-valued enumerations. Elsewhere, there is documentation on each of element of these enumerations. How can I import the data from jdbc and ensure that the appropriate generators use the correct
idvalues for the correct multi equations?For example, say I had the following:
And in the data
bar_typeis only ever one of1, 3, 1111; I'd like to make sure that the multi equations provide the correct string for the correct id.