creating view within moodle database

creating view within moodle database

by Matt T -
Number of replies: 4

Firstly, sorry if this is the wrong forum

This may be a stupid question, but would creating a view in the moodle database cause any issues with, say, database checks on an upgrade? 

Is it not advisable for any other reason?

If you need context:

I need a view that returns username + a specific custom user field (that the user sets in their profile) in a single table. This won't be used in any way by moodle code but by a third party tool which will have read access to that view only. The tool doesn't let you write joins etc but expects a single table with 2 columns


CREATE VIEW usersAndACustomField

AS

SELECT

    mdl_user.username AS moodleusername,

    mdl_user_info_data.data AS profile_field_value

FROM mdl_user 

INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid 

WHERE mdl_user_info_field.id = 3

Average of ratings: -
In reply to Matt T

Re: creating view within moodle database

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

I would not have thought so as a view in effect is a stored select query and as such does not alter the database content nor affect its integrity.  You could always run a test server, create the view, perform an upgrade and see what happens.

Average of ratings: Useful (1)
In reply to Matt T

Re: creating view within moodle database

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Exactly the right forum, not even vaguely stupid and no it would not cause a problem as a view is effectively just a stored query as  Gareth sayes. However there may be slight differences between the syntax for the different databases that Moodle supports so developers for a broad audience tend not to create them. But for an individual trying to get information out of their Moodle, it is a fine idea.

Average of ratings: Useful (1)
In reply to Marcus Green

Re: creating view within moodle database

by Matt T -

Thanks both - I realise Moodle uses a database abstraction layer. I just wasn't sure if the upgrade script does any checks to see tables it doesn't recognise or have defined.

I'll create a cloned environment and see if there's any problems. 

I'm sure there's a better way to achieve this but it's the simplest way to do this data integration.

Also to anybody who finds this in Google there's an error in my last line of SQL code above as I referenced the wrong table - it should be a reference to the mdl_user_info_data table

Cheers,

Matt

Average of ratings: -
In reply to Matt T

Re: creating view within moodle database

by Gareth J Barnard -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

No worries Matt.  I've never known Moodle to moan about extra tables and have plugins that utilise their own tables too.

Average of ratings: -