The dataSource method appears to be broken

[cfcoder]

In the project i'm on, we are in the midst of switching from an old database to a new database. Old is HSP and new is FPMA (for referencing below). I'm needing to create a new page based on the new database that lists the projects in the new database. The old db had projects but the table was called something else. So the projects.cfc controller simply used the following to get the list of all projects.

projects = model('Project').findAll();

The Project.cfc model config would simply set the table name using the table() function as the table was not called "projects" in the old db.

I'm now trying to use the dataSource() function to override the data source name to use the new DSN FPMA, but it's not working for my new page. I change the config contents for Project.cfc model to be:

dataSource("FPMA");

and in the new Projects.cfc controller index action, I first off call that same line above:

projects = model('Project').findAll();

But now it throws an error saying there is no projects table in the database. There is in FPMA but not in HSPTest.

I think there is an issue here.

[neokoenig]

I haven't really used datasource() much, but some thoughts:

Are you reloading the app after making these changes? I mentioned to you on Slack that the config() function is cached.

Another thought is an alternative method, which may or may not be more suitable depending on your use case... but it might be better in the long run (if potentially require more code changes):

Instead of setting the datasource() attribute on a single model (and then wanting it to change) where you'd have to reload the model classes etc, you could create a second model, and then switch between them in the finder itself depending on use case.

Model B (the new one) could potentially extend Model A (legacy) so you could take advantages of not duplicating model methods too, in principle.

/models/Project.cfc
Old user model; naturally uses the default datasource

component extends="Model" {
	function config() {
		super.config();
	}
}

/models/new/Project.cfc
New Project model (which extends the old Project model, which in turn extends the Model model)
As this model is in a subfolder, we use dot notation to extend the legacy Project model, also I think you might need to set the table name manually (as it might otherwise look for app.models.project as a table name)

You could also just have it in the root directory, but you'd need a different CFC name obviously. (Also you don't have to extend the original model if you don't want to, you could just extend app.models.Model but I don't know how much logic you've got in your project model.)

component extends="app.models.Project" {
	function config() {
		datasource("newDatasource");
                table("projects");
	}
}

Then in your controller methods, you could switch which model you're calling dependent on some condition.
i.e:

modelName = someCondition ? "new.Project" : "Project";
projects = model(modelName).findAll();

I guess you could also use a helper function in /global/functions.cfm to handle this;

function switchModel(required string modelName, boolean useNewDatasource=false) {
	return arguments.useNewDatasource ? "new.#arguments.modelName#" : arguments.modelName;
}

then:

projects = model(switchModel('Project', session.isLoggedIn)).findAll();

Lastly, you could override the core model() method via a plugin, i.e, override the entire model() call, make your changes and then return core.model(); That's a bit more involved, but it's a possible option.

By keeping the models seperate, you could then also start having additional fields/custom behaviour on the second model. All code/ideas above provided off the top of my head without testing :)

[neokoenig]

I believe what you're seeing is that wheels is caching the entire model definition, i.e, including properties (table columns) as it sees on application start. So swithing out the datasource at runtime won't run the cfdbinfo call to get the table columns on the new datasource.

This is why I thought it might be better to seperate it out into it's own model, as it would have it's own cached version of the model class, and you would be able to seperate the two.

[cfcoder]

What is really odd... I override another model in the /admin space and it works fine. It's as if the name "Project" or "Projects" (depending on if you refer to the controller or model) are reserved words. I can't believe that is the reason so I suggested maybe the hierarchy is at fault, but the example where overriding the model does work also is in /admin/lookupTypes.cfc so it too is down an extra level from the webroot while the models are all in /models. I just can't explain why this behavior is happening. So far, no one else can either. :(

[cfcoder]

@neokoenig As I added a moment ago to the top of your first response (before I realized you had already responded) this need for dual datasources is temporary while we refactor the code to use the new database. Once that is completed, then we set the default ds and move on. But I think someone with deep knowledge of the wheels code base should be able to research why this is happening for me and perhaps fix it to work for all situations.

[neokoenig]

TBH without knowing more about your setup, it's really hard to just guess.

For instance /admin/lookupTypes.cfc - what is that? part of the wheels application? A model? A controller? What does it extend?

Try and break it down into a simple reproducable test case where someone here can test it. But do try my suggestion above, if just to test. It would allow you to dump the two classes side by side and compare. It would also remove the possibility of wheels caching the model definition (including the table names).

[cfcoder]

@neokoenig Again, thanks for your suggestions and responsive posts! See answer found I posted a few minutes ago.

[cfcoder]

After changing the DSN setting to use only one (the new) databae with no overrides, I still got the same error message, table not found! With two other developers here not having this issue suggests that something was unique to my environment. So I dug into the /wheels source code with debug dumps to narrow down where the error was coming from. I narrowed it down to a tag failure which is native CFML tag so then I knew the issue was in my database connection. However, the DS setup has been working fine in CF Admin, with no errors reported there. So I started comparing with others db configuration and checking security settings in SSMS app and found the problem:

I did not have the membership for my username used in the code set to db_owner. None of the Role Members were checked. This resolved the table not found issue!

I would submit that CFWheels should change the code to try and determine the cause of the dbinfo tag error and issue a less misleading error message. Table not found was not the error.

[cfcoder]

I should add that using datasource("FPMA") in the model now works to override.

[neokoenig]

Great you found the answer in the end

[zainforbjs]

It seems to resolved, I'm gonna close this. Please review the #1411 for further details.