Automatically convert Rand() to Random()

[Nibogo]

I think SMF should automatically convert Rand() inside queries to Random() (and viceversa), as Rand() is only used in MySQL throwing an error for Postgresql and SQLite. This would allow developers to just use any of those and we won't have to extract database name, see what's being used and then use the proper function for each one. As you know getting a random value is quite common for a lot of mods.

[Oldiesmann]

How is this a bug? A feature request maybe, but definitely not a bug.

In any case, we simply cannot implement compatibility for every function someone might want to use. It is up to the individual mod author to ensure that their code works properly on all database systems. We will be glad to assist you with adding the compatibility yourself, but we can't go adding code to support things that SMF itself doesn't use.

[Nibogo]

Yes, it's a feature but when I was in the team the feature request board wasn't monitored at all, that's why I posted that here, and I obviously know how to do it, I just think this is something that could be really useful, it requires less than 3 minutes of implementation and almost no testing, in the other hand you'd be making developing of mods a little bit easier.

[Joshua Dickerson]

Nibogo, got a patch? Not saying we'd add it, but there is a possibility.

[Nibogo]

No, but you could add it easily and see if it worth it

[Joshua Dickerson]

RANDOM() works in all?

[NanoSector]

RAND() works only in and MySQL.

An option would be to check the engine used and then convert the RAND() to RANDOM() for PostgreSQL and SQLite, if that hasn't been said before, skipped over the post.

Though you can also use PHP for it, search for RAND() and insert a number generated with rand() in it's place. Dunno if that works in all cases.

[Nibogo]

RANDOM() works in all?

No, that's the problem, that MySQL uses Rand() while SQLite and PostgreSQL are using Random(), and no, php cannot be used here to just throw a random number.

[Joshua Dickerson]

Nibogo, submit a pull request through Github. Seems simple enough to replace RAND() with RANDOM() for non-MySQL installs to make it easier for mod authors (who probably aren't testing with anything besides MySQL anyway).

[Arantor]

Or, even use the feature already in SMF to handle this. There are perfectly good reasons not to just rewrite queries on the fly, performance, maintainability, that sort of thing.

Subs-Members.php, generateValidationCode() has this already.

Code Select Expand

$request = $smcFunc['db_query']('get_random_number', '
SELECT RAND()',
array(
)
);

That's what the first parameter to db_query is: specific indications for certain backends that things require work.

It's not elegant, but the entire way multi-architecture is implemented is not elegant. But that's the way it should be done, using what is already there for that purpose.

[Nibogo]

I think it'd be better if we do the conversion, I really don't think a lot of people is aware of that, not even developers

[live627]

It's undocumented.

[MrPhil]

So, if you write your SQL query as

Code Select Expand

SELECT {RAND}() ....
SMF would replace {RAND} by RAND or RANDOM, depending on which library is being used. That sounds easy enough to do. If an ignorant author only tests with RAND() in MySQL, it should even be possible to automatically flag that (to be fixed) during the submission process.

Is there a list of various database eccentricities beyond RAND/RANDOM? To future-proof code, you might want to go beyond the three databases that SMF handles, and be prepared to handle Oracle, DB2, Access, etc. should they ever become supported. Hopefully there aren't too many such cases, so SMF would not have to specially handle dozens of keywords or even put all function names in {}.

While improving cross-DB capabilities, how about discovering naked reserved keywords during submission testing, and suggesting that they be backticked? This might have to wait until a query is fully assembled (during test) and can be parsed in PHP before actually submitting to the database. Are there other things (triggers, stored procedures, etc.) that could be emulated if properly marked up in the query?

[emanuele]

* emanuele adds this to the list of things to document.

But since it's already there is neither a bug, nor a feature request.