Fix for createViewOrReplace and its support for sqlite3

[thebrownfox]

Related issues:
Closes #4854
Closes #4855

[OlivierCavadenti]

Thanks for the report + PR ! Tests fails

[OlivierCavadenti]

@kibertoad maybe could be nice to add this in 0.95.15 ?

[kibertoad]

sure, sounds good

mistake

[OlivierCavadenti]

@kibertoad I push returns, if you can take a look.

[thebrownfox]

@OlivierCavadenti sorry, I was busy and then sick. Have you sorted it out? BTW I was thinking whether the functionality could behave the same as in sqlite3 across all dbs. In postgres, when you use the builtin functionality, it needs to have the same columns in same order and new ones at the end. I ended up using drop and create anyway.

await Model.knex().schema.dropViewIfExists(dBViewName);

dbView = await Model.knex().schema.createView(
    dBViewName,
    (view) => {
        view.as(Model.knex().raw(query));
    }
);

[OlivierCavadenti]

@OlivierCavadenti sorry, I was busy and then sick. Have you sorted it out? BTW I was thinking whether the functionality could behave the same as in sqlite3 across all dbs.

No problem ^^.

In postgres, when you use the builtin functionality, it needs to have the same columns in same order and new ones at the end.

I am afraid I dont understand, you need to create new view but with same columns in same order when you use createViewOrReplace ? I dont test that, but it throws an error ?

EDIT : I see the documentation (it seems good pratice :D) and I see the problem:

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

[OlivierCavadenti]

BTW I was thinking whether the functionality could behave the same as in sqlite3 across all dbs.

In postgres, when you use the builtin functionality, it needs to have the same columns in same order and new ones at the end.

By the way, I assume this limitation in PostgreSQL (need replace with same columns, same order) it's to dont break all existing queries that query the view you replace.

In my opinion, it's better to dont change existing queries 'create or replace view' and so have this safe behavior, and put in Knex documentation what you are saying : the difference between sqlite3 and other dbs.

@kibertoad what do think about that ?

[kibertoad]

@OlivierCavadenti I agree, handling it via documentation is a better path.

[OlivierCavadenti]

@kibertoad we will put this one on 1.0.0 (with backport in 0.95.X) or we will merge it after the 1.0.0 tag ?

[kibertoad]

@OlivierCavadenti I'm OK with merging it in 1.0.0 if you prefer it that way.

[kibertoad]

@OlivierCavadenti Any last minute changes, or I can start releasing 1.0.0 in an hour or so?

[OlivierCavadenti]

@OlivierCavadenti Any last minute changes, or I can start releasing 1.0.0 in an hour or so?

I dont see anything else. I will track carefully any issues if anything need to be fix before/after the 1.0.0 tag :D.

[kibertoad]

Released in 1.0.1