[3.0]: pg install: members table not created due to "ERROR: function indexable_month_day(date) does not exist"

[sbulen]

Basic Information

Installing pg, there a syntax error on the members table, and the members table is not created.

Two related issues here:

1. The function indexable_month_day() is present in 2.1 & upon upgrades to 3.0, but it's not present on installs.
2. This error is not displayed (I can only see it via debugging utilities).

Error text is as follows:

ERROR: function indexable_month_day(date) does not exist LINE 1: ... INDEX ato_members_idx_birthdate2 ON ato_members (indexable_... HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Note: None of the pg functions are created in 3.0 upon install...

Steps to reproduce

1.Install pg, the members table won't exist

2.NOTE: DROP and re-CREATE the database before starting the test if you are reusing the db. Otherwise the functions may still be present from a prior install/upgrade. To test the install, you need a fully empty database, including no functions, sequences, etc.

Much of this stuff is still laying around, even if you drop all the TABLEs in a DB.

Expected result

All tables created & populated; SMF pg functions created

Actual result

The members table does not exist.

You will see this on the Database Population screen during installation:

Some of the queries were not executed properly. This could be caused by an unsupported (development or old) version of your database software.

Technical information about the queries:

    ERROR: relation "ato_members" does not exist

Note: None of the pg functions are created in 3.0 upon install...

Version/Git revision

3.0 Alpha 4 - current GitHub

Database Engine

PostgreSQL

Database Version

14

PHP Version

8.4.5

Logs

ERROR:  function indexable_month_day(date) does not exist
LINE 1: ... INDEX ato_members_idx_birthdate2 ON ato_members (indexable_...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Additional Information

No response

[sbulen]

FYI - I'm not going to address this one, as I was unsure how the new installer is intended to address new database prerequisites.

[jdarwood007]

This will take some modification of the installer.

Essentially, I forgot that PG needed to install functions. We need to extend support on the db API to be able to create/edit/delete functions. Then add the ones needed for pg, telling it to not do it for MySQL.

[sbulen]

I like how it installs what it finds in other areas, eg, tables. Maybe a pre-install folder, that's empty for mysql, but has the functions for pg. If we ever have similar reqs for mysql, not a lot of rework will be required.

[jdarwood007]

I was contemplating a pre-install folder idea. The problem is that the syntax is very different for the functions between MySQL and PostgreSQL if we were to support both of them. There isn't a pretty way to do it, really.

[Oldiesmann]

Extending the DB API is probably the best way to handle this in case we need/want custom functions for MySQL later.

[jdarwood007]

https://dev.mysql.com/doc/refman/8.4/en/create-procedure.html https://www.postgresql.org/docs/current/sql-createfunction.html

I am thinking we would need to do a db_create_function that takes

• function name
• Return type
• Parameters array
  • Name (optional)
  • Type
• SQL statement
• Array of arguments for SQL statement
• Options
  • Such as IMMUTABLE, STRICT

The type would be our standard types, but then we would have to do casts for anything. Such as on PostgreSQL, it says integer, while we just specify int. So we would ensure we translate that.

The SQL statement and array can be passed into db_quote

However, this still leaves operators. We create some operators for + and !=

We also create some sequences and specify a "start with". Which we don't want to implement.

@Oldiesmann and @albertlast Any thoughts here.

[albertlast]

Which function we create on MySQL side?

[jdarwood007]

None, but it would make more sense to me to extend the API to do functions rather than one-off queries.

[albertlast]

When we got none, than we never know If this API still works on MySQL side. Since the Code is never executed on MySQL side.

[albertlast]

Since we talk here about program code, like php code and not data related code like table, i would recommand to go the "pre-install" folder logic, where database sql code can be placed which is stateless.

[Oldiesmann]

Extending the db to allow us to create arbitrary functions or other things seems like the best solution, even if we don't use them in MySQL.

[albertlast]

There is solution in middle of between just stupid "pre-install sql code" and "fully managed php database code", this i would call "db version control". You can check if a function, procedure or operation exists: https://dba.stackexchange.com/questions/567/how-do-i-find-out-if-a-procedure-or-function-exists-in-a-mysql-database https://stackoverflow.com/questions/24773603/how-to-find-if-a-function-exists-in-postgresql

And both system support comment on this kind of object: https://dev.mysql.com/doc/refman/8.4/en/create-procedure.html https://www.postgresql.org/docs/current/sql-comment.html Comments could be a json with version number and other meta stuff.

When a db object didn't exists or wrong version number, than a specif sql file get execute for this kind of database.

In best case you would implement for the check just a call backfunction, where people could define that mysql always exísts and pg just need more details.

[jdarwood007]

I'm not sure how the examples you provide offer a 3rd solution. I think the 2 options are a pre-install SQL that creates all the procedures/functions that we need to support SMF. Or we build out support for them in the API.

Both will work. Both are valid solutions. I have no reason to favor one or the other. Seems like Oldies leans one way and you the other. So we are at an impasse as to what solution is best. One seems like it would be quicker to implement, but less flexible, and the other would offer more robust future options, but a lot more effort to implement.

@Sesquipedalian do you have an opinion on things?

[Sesquipedalian]

I don't see a lot of use for a Db::createFunction() method. The code inside any user-defined database function will be unique to the particular database engine in use. There is not much to abstract. So simply calling Db::query() with the appropriate CREATE FUNCTION ... code in the query string is the best approach.

Regarding the original issue, therefore, we should just take steps early in the installation process to ensure that all our user-defined database functions have been created correctly. It doesn't need to be any more complicated than that.