Add command which syncs deploy file with database

[christoph-bessei]

From #112:

May I suggest a "sync route" option for deployment? This should do the following:

• Remove routes which exist inside the database, but not inside .fusio.yaml
• Sync route options (methods, rates, actions, database connections, ...) from .fusio.yaml to database
• Maybe allow the user to set the route ID inside .fusio.yaml so it doesn't change across deployments and all relations are preserved?

Possible command name: system:sync

[christoph-bessei]

Additional requirement

The sync should respect the order of routes inside .fusio.yaml.

Example .fusio.yaml V1

/stock: !include resources/routes/stock/collection.yml
/stock/:id: !include resources/routes/stock/entity.yml

The routes are deploy with fusio deploy. After some time user decides to add some pre-filtered stock routes (inventory and purchase).

.fusio.yaml V2

/stock: !include resources/routes/stock/collection.yml
/stock/inventory: !include resources/routes/stock/inventoryCollection.yml
/stock/purchase: !include resources/routes/stock/purchaseCollection.yml
/stock/:id: !include resources/routes/stock/entity.yml

After another fusio deploy the routes are in the following order:

1. /stock
2. /stock/:id
3. /stock/inventory
4. /stock/purchase

So /stock/inventory and /stock/purchase are never called, because /stock/:id matches first.

After a call to fusio system:sync the order should be

1. /stock
2. /stock/inventory
3. /stock/purchase
4. /stock/:id

[chriskapp]

@christoph-bessei thanks for the great input and this is a valid point. In general you could fix this problem by using a more specific route i.e. /stock/$id<[0-9]+> this would then only match integer routes and the order would be not important. In general I think it is best to have only distinct routes, in your case /stock/:id could match /stock/inventory and /stock/purchase and it is not clear which route you want to call either the static or dynamic route. But we could build a sorting algorithm which simply tries to move all static routes to the top but maybe this is not always the behaviour which a user wants. So I will need to take a closer look on this topic if I start to develop this command.

[christoph-bessei]

You're right, distinct routes are preferable.

Nevertheless the sync command should respect the order of routes inside .fusio.yaml to avoid problems (maybe add a "sorting" column to fusio_routes?). IMHO fusio shouldn't "automagically" sort the routes in some way.

[chriskapp]

Yes I think also guessing the sort id is probably the wrong solution. Using a sorting column is a great idea, I have also thought about this for a while. Iam currently trying to implement this through a priority column for the routes table where the deploy command sets the priority by definition in the .fusio.yaml. If you create a route through the backend the route gets automatically the highest priority so new created routes are always at the top. Maybe we could also implement a simple field where the user can set the priority. In the future we could also implement things like sorting by drag n drop etc.