Is there a way to include the foreign key value in a query result?

[Fred Eisele]

Is is possible to write a query in such a way that a foreign_key appears in the result without including the entity to which it refers?

e.g.

schema S = literal : sql {
 entities
 a b
 foreign_keys
 has : b -> a
 attributes
 i : a -> Varchar
 j : a -> Varchar
 k : b -> Varchar
 m : b -> Varchar
}


query Q = simple : S {
 from
      eh:a
     attributes
      has -> eh.has
      i -> eh.i
}

This does not throw an error but it does not include `has` in result.

entity Q -> {from
 eh : a
attributes
 i -> eh.i
}

[Fred Eisele]

Of course the query I gave was wrong.

The following is what I intended.

And it does produce an error.

For section attributes, cannot find (has, bee.has).

schema S = literal : sql {
 entities
 a b
 foreign_keys
 has : b -> a
 attributes
 i : a -> Varchar
 j : a -> Varchar
 k : b -> Varchar
 m : b -> Varchar
}




query Q = simple : S {
  from

      bee:b
     attributes
      has -> bee.has
      k -> bee.k
}

[Fred Eisele]

I understand that this implies a confounding of purposes.

The id of the entity `a` found in `b.has` should have no meaning and in some sense is pointless.

Nevertheless, it might be useful to see it.

[Ryan Wisnesky]

I don’t entirely follow, but can you send me the file in question so I can check for any odd behavior?

--
You received this message because you are subscribed to the Google Groups "categoricaldata" group.
To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Fred Eisele]

The similar sql would be .

create table a {
id Integer not null primary key,
i Varchar,
j Varchar }

create table b {
id Integer not null primary key,
has Integer foreign key references a(id),
k Varchar,
m Varchar }

select bee.k, bee.has
from b as bee

Notice how the foreign key column can be included in the select.
How can this be done in all?

[Ryan Wisnesky]

Actually, you can’t include foreign key columns as attributes like that by design - AQL IDs are meaningless IDs, and they can’t be ‘observed’; AQL is invariant under change of IDs, but not change of values. When an instance gets emitted as SQL, for example, all the AQL IDs turn into fresh new SQL IDs. To capture the semantics of SQL perfectly in this case, you need attributes for the id columns as well, along with equations related them to the foreign keys. The attached AQL loads your SQL into AQL’s SQL DB, then pulls the schema out of the DB, showing all the resulting AQL schema with all the attributes and equations you need, etc.

[Ryan Wisnesky]

Sorry, here’s the promised file.

[Fred Eisele]

Here is the schema produced by exec_jdbc per the file provided.

[provided for reference]

{

entities
 A B
foreign_keys
 B__HS__A_ID : B -> A
path_equations
 
attributes
 ID : A -> Integer
 I : A -> Varchar
 J : A -> Varchar
 ID : B -> Integer
 HS : B -> Integer
 K : B -> Varchar
 M : B -> Varchar
observation_equations
 forall x:B. x.HS = x.B__HS__A_ID.ID
}

Thanks