General developer forum

Using Moodle DB object for external/ non native DB table ?

 
Picture of Jeff Noel
Using Moodle DB object for external/ non native DB table ?
 

Hi,


I created an additionnal table within Moodle's database. All my default Moodle tables being with mdl_[tablename].

My new tables all being with ext_[tablename].

I'm trying to insert a new record in my table ext_policy, but whenever I'm using $DB->insert_record() or $DB->insert_record_raw I get the following error:

Table 'moodle.mdl_ext_policy' doesn't exist

Is there a way to specify the table prefix in the $DB object queries ? I'm trying not to use raw SQL queries in my code.


Thank you for your time and help !

 
Average of ratings: -
Picture of Howard Miller
Re: Using Moodle DB object for external/ non native DB table ?
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

Why did you do that? Let's back up a bit and establish what you're trying to achieve because it sounds a strange thing to do.

 
Average of ratings: -
Picture of Jeff Noel
Re: Using Moodle DB object for external/ non native DB table ?
 

Policy agreements are handled through Moodle, and need to be agreed upon every semester.

If the user did not accept the policies for the current semester, he cannot access Moodle. The database itself keeps tracks of who accepted the policies and which semester they did, along with the date. When someon agreed, I also update the policyagreed field in the mdl_user table. This is the field that I check before forcing (or not) a redirect to the policy agreement page.


I want to optimize and secure the policy agreement process and thought using Moodle's DB object could be useful, especially since the table is within the same database.

 
Average of ratings: -
Picture of Howard Miller
Re: Using Moodle DB object for external/ non native DB table ?
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

Fine... but why do you need a different prefix for your custom tables?

What you probably should be doing is creating proper plugins (e.g. a local plugin) that define their own tables to do this. It's not much more effort and it will be a proper job.

 
Average of ratings: -
Davo
Re: Using Moodle DB object for external/ non native DB table ?
Core developersParticularly helpful MoodlersPlugin developers

If you want to use the Moodle DB functions you need to be using tables with the mdl_ prefix (or the defined prefix for your site). The functions are written that way so that prefixes can be changed, without any change to the Moodle code.

If these are custom tables related to a plugin you are creating (even if an external application is going to access them as well), then you're likely to be much better off using the mdl_ prefix.

If you really need to access tables without the prefix, then you could try looking at how the enrol_database plugin handles this - https://github.com/moodle/moodle/blob/master/enrol/database/lib.php#L851 

It's not as easy as using the Moodle $DB global, but it is possible to get it to work.

One other alternative might be to mess around with the $CFG->prefix variable, but I could imagine a whole world of pain and confusion if you went down that route (I haven't even checked if it would work, it's such a bad idea ...).

 
Average of ratings: -
Picture of Darko Miletić
Re: Using Moodle DB object for external/ non native DB table ?
Core developersParticularly helpful Moodlers

You can try this:

$oldprefix = $CFG->prefix;
$CFG->prefix = 'myprefix_';
$DB->something;
$CFG->prefix = $oldprefix;

You should also place appropriate try/catch and ensure that no exception leaves your code. You must also guarantee that $CFG->prefix will be set to original state.


This is still extremely bad idea.




 
Average of ratings: -
Picture of Richard Jones
Re: Using Moodle DB object for external/ non native DB table ?
Particularly helpful MoodlersPlugin developersTesters

Is it too simplistic to label your table mdl_ext_tablename?  Then just use the db functions with "ext_tablename".

 
Average of ratings: Useful (1)
Picture of Jeff Noel
Re: Using Moodle DB object for external/ non native DB table ?
 

Sometimes, simple answers are the best move. I was about to fiddle with the DB object's prefix property, but it's way better to rename the table... especially since this one hasn't been used elsewhere for now.

Thank you very much for the idea !


For those wondering, our development team opted for "ext_" prefixes to differentiate tables used by our customs plugins, BUT also used by external APIs. This way our tests can be sorted out more easily when migrating or developing new environments for our Moodle instances.


Thanks everyone for the good suggestions !

 
Average of ratings: -