Comments (8)
I was thinking we could have a
postgrest --dump-relationships=one-to-one
command, its output could help with generating the dynamic SQL.
No need for another CLI flag, we already have everything we need:
postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql postgrest-run --dump-schema \
| jq '.dbRelationships | .[] | .[1] | .[] | select(.relCardinality.tag == "O2O")'
from postgrest.
Include the bash script on the v9.0.1 release artifacts.
Surely you mean the v10 release artifacts, because that's where the breaking changes were introduced, right?
from postgrest.
It doesn't show on which table is the foreign key defined
I was having a similar issue when working on #3226, since we need to know to which table to insert first in a o2o
relationship.
the simplest solution would be to add an attribute to Cardinality.
So I think this solution would be necessary anyways.
from postgrest.
For this issue it doesn't seem worth it changing the Relationship type. Will close with #3312.
So I think this solution would be necessary anyways.
I agree. That could be a refactor:..
PR.
from postgrest.
I don't like adding config options for this purpose. We should provide better migration paths, instead: Docs, Tools, anything that helps the actual migration.
For upgrading, adding computed relationships was recommended but this is considerable work for projects that have many tables.
Could this be (partly) automated with dynamic SQL to create the computed relationships?
from postgrest.
Yeah, I don't like it either. It also provides an easy way to have incompatible outputs between postgREST APIs, that could cause trouble.
We should provide better migration paths, instead: Docs, Tools, anything that helps the actual migration.
Could this be (partly) automated with dynamic SQL to create the computed relationships?
I was thinking we could have a postgrest --dump-relationships=one-to-one
command, its output could help with generating the dynamic SQL.
from postgrest.
Just using jq
looks like a good idea.
Removing some unnecessary keys:
postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql postgrest-run --dump-schema \
| jq '.dbRelationships | .[] | .[1] | .[] | select(.relCardinality.tag == "O2O")' \
| jq 'del(.relFTableIsView,.relTableIsView,.tag,.relIsSelf)'
We get an output like:
{
"relCardinality": {
"relColumns": [
[
"id",
"id"
],
[
"code",
"code"
]
],
"relCons": "students_info_id_code_fkey",
"tag": "O2O"
},
"relForeignTable": {
"qiName": "students",
"qiSchema": "test"
},
"relTable": {
"qiName": "students_info",
"qiSchema": "test"
}
}
{
"relCardinality": {
"relColumns": [
[
"id",
"id"
],
[
"code",
"code"
]
],
"relCons": "students_info_id_code_fkey",
"tag": "O2O"
},
"relForeignTable": {
"qiName": "students_info",
"qiSchema": "test"
},
"relTable": {
"qiName": "students",
"qiSchema": "test"
}
}
The relevant information is there though it's cumbersome with a lot of o2o. And we need to define computed rels for both ends..
Next steps:
- Create a bash script to make this easier. It should parse the json output and then generate the SQL statements.
- Include the bash script on the v9.0.1 release artifacts.
Edit: jq expression that aggregates the objects into an array
postgrest-with-postgresql-16 -f test/spec/fixtures/load.sql postgrest-run --dump-schema \
| jq '[.dbRelationships | .[] | .[1] | .[] | select(.relCardinality.tag == "O2O" and .relFTableIsView == false and .relTableIsView == false) | del(.relFTableIsView,.relTableIsView,.tag,.relIsSelf)]'
from postgrest.
We need to create two computed rels, an o2m
and a m2o
for correcting the o2o
rel. So the resulting computed rels from the students
/students_info
example shown above should be:
-- students -> students_info o2m
create function students_info(students) returns setof students_info rows 1000 as $$
select * from students_info where id = $1.id and code = $1.code
$$ language sql;
-- students_info -> students m2o
create function students(students_info) returns setof students rows 1 as $$
select * from students where id = $1.id and code = $1.code
$$ language sql;
But there's a problem with the schema cache json output. It doesn't show on which table is the foreign key defined. So we cannot know to which table the "many" end belongs to.
So the current output is insufficient for having this automated 😞.
Maybe the problem is that we add the relationship and the inverse relationship. If we only had one then this should be solvable.
Edit: the simplest solution would be to add an attribute to Cardinality
. But then users would need to use the next v12
version for the migration script..
from postgrest.
Related Issues (20)
- Wrong `503 Service Unavailable` on pg `53400` HOT 1
- Dependency Dashboard
- Clarify that domain representations need implicit casts
- Domain representations for RPC
- Static executables on other platforms: Native builds via Nix
- Static executables on other platforms: Cross compilation via Nix
- Selecting a related object modified by an `AFTER INSERT TRIGGER` does not show the inserted data HOT 4
- A "match" pattern inside an "or()" construct cannot contain parens HOT 2
- Cannot select field from inherited table HOT 2
- Channel retrying message doesn't doesn't follow log format HOT 3
- Re-Request mapping of internal variables for stored procedures HOT 2
- Responses are slow when the schema cache reloads HOT 3
- Stack Overflow error in dev enviroment when table and embed resource do not exist HOT 8
- Admin server `/ready` endpoint is incorrect on slow schema cache loads
- `pgrst.server_trace_header` in-database config not working HOT 1
- Filtering With Or Logical Operator on Embedded Resources Inconsistent Behavior
- Postgrest Server Does'nt respond for DB error SQL Error [57014]: ERROR: canceling statement due to statement timeout .
- Inserts and Updates now require an up-to-date schema cache HOT 2
- Building static executables directly via nix' `pkgsStatic.postgrest`
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from postgrest.