[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.