Hard coded SQL in framework block to use it with another database like PostgreSQL.

[cesarliws]

Example:

./nocms/application/core/MY_Controller.php

Line Number: 1807

$SQL   = "SELECT max(`index`)+1 AS newIndex FROM `".cms_table_name('main_navigation')."` WHERE $whereParentId";

This SQL do not works with PostgreSQL.

Ill fix it in my fork, but I want to know how approach you want me to do it, so I can search all the places it is happening and fix in the proper way.

[goFrendiAsgard]

Yeah, some legacy script that need to be changed. I'll inform you once it is repaired

[goFrendiAsgard]

Okay, I've do something with this, at least the "`" has been removed from hard-coded sql. I'll also change every hard-coded sql into active record if possible. (Some subquery cannot be approached by using active record) https://github.com/goFrendiAsgard/No-CMS/commit/ae9d319f02103f90d8b9faa3f3d004c46221ca7d

[cesarliws]

Thank you!

I was testing here and it stoped in groceryCRUD, so I found it "https://github.com/scoumbourdis/grocery-crud-databases" and saw that you have sent some patches. Will you add it to No-CMS?

[goFrendiAsgard]

It is already there, that's why No-CMS also work with postgresql & sqlite (However, tell me if it doesn't work)

[cesarliws]

Good to know. But unfortunatelly it is not working. How it loads the proper Model? I was doind some tests here, with $this->db->platform(), but it do not works for "pdo", as it returns "pdo" for all databases, so is necessary to call $this->db->subdriver, and then the subdriver is 'pgsql', instead of 'postgres' returned by $this->db->platform()

[cesarliws]

I just got your changes and now it seems to be working, thank you.

But one more problem remaining in "blog" module, somehow it still loads core/MY_controller.php, and then the datatypes like this:

protected $TYPE_DATETIME_NULL = array( 'type' => 'DATETIME', );

for PostgreSQL it must be TIMESTAMP, this should be overrided. How is the right way to override datatype for different databases, since it is declared in the Controller and not in the Model?

[cesarliws]

The same with "static_accessories" Module.

[cesarliws]

And you added a new column "bootstrap_glyph_icon" that is not in the database yet, it is missing in the in cms_main_navigation table.

[goFrendiAsgard]

Thank you for your investigation. I'll work on it. And sorry, it should not be "bootstrap_glyph_icon", but "bootstrap_glyph" instead https://github.com/goFrendiAsgard/No-CMS/blob/949898c7ffae39a4eef7e249681e1b3612f03358/modules/installer/models/install_model.php#L533

[cesarliws]

Hummm after comment it, activate Blog module, it is not working yet,

nocms/blog/manage_article

A Database Error Occurred "SHOW COLUMNS FROM cms_blog_article"

[cesarliws]

Yes, bootstrap_glyph did the trick. tnx :)

[goFrendiAsgard]

Hi, this one should work https://github.com/goFrendiAsgard/No-CMS/tree/8e3d429b9818f8e3dee1f34111dbec0f35bd8a54

I've fix every occurring bug, but there might be something I miss. So please let me know if you face another problem. (I don't close this issue since I will also do a more intensive investigation).

It is really hard to maintain peace among different databases :)