PostgreSQL support?

[ricpelo]

Hi! Great project!

Is PostgreSQL support in the short-term roadmap?

Thanks!

[nadar]

Hi @ricpelo, as luya is build upon the Yii2 Framework you could just use the http://www.yiiframework.com/doc-2.0/yii-db-pgsql-schema.html Schema:

I googled the setup very quickly:
http://stackoverflow.com/questions/26436024/how-to-set-default-schema-in-yii2

I hope this helps? Let me know if you have any further questions.

[nadar]

(maybe we would have to change the migration files to work with postgresql, but unsure about that. maybe you could just try or take a look at the migrations files so we would know what needs to be changed. thanks)

[ricpelo]

Hi, @nadar, thank you very much for your answer. By "support" I mean that the current installation procedure works only with MySQL, because the migrations applied with ./vendor/bin/luya migrate during setup includes MySQL-specific definitions like TINYINT and so. For example:

https://github.com/luyadev/luya-module-admin/blob/master/src/migrations/m141104_104622_admin_group.php

[nadar]

True! We should change this and use the http://www.yiiframework.com/doc-2.0/yii-db-schema.html instead, then it would be applied based on your database schema settings. Maybe you could send a pull request?

We would have to do it anyway, so it should be part of our short-term roadmap.

[ricpelo]

Sorry, @nadar, I'd love to contribute, but unfortunately I don't have the free time needed to work on this now :(.

[nadar]

Ok, i will take care of this.

[nadar]

@ricpelo i made the changes for the cms and admin modules, which are by default used in the kickstarter application, maybe you can give it a try and see if it works? I am going to make the changes also for all other modules.

[ricpelo]

Hi, @nadar, I just tried the installation procedure starting with:

$ composer create-project luyadev/luya-kickstarter:@dev

and I got the following error message:

./vendor/bin/luya migrate
Yii Migration Tool (based on Yii v2.0.10)

Creating migration history table "migration"...Done.
Total 34 new migrations to be applied:
        m141104_104622_admin_group
        m141104_104631_admin_user_group
        m141104_114809_admin_user
        m141203_121042_admin_lang
        m141203_143052_cms_cat
        m141203_143059_cms_nav
        m141203_143111_cms_nav_item
        m141208_134038_cms_nav_item_page
        m150106_095003_cms_layout
        m150108_154017_cms_block
        m150108_155009_cms_nav_item_page_block_item
        m150122_125429_cms_nav_item_module
        m150205_141350_block_group
        m150304_152220_admin_storage_folder
        m150304_152238_admin_storage_file
        m150304_152244_admin_storage_filter
        m150304_152250_admin_storage_effect
        m150304_152256_admin_storage_image
        m150309_142652_admin_storage_filter_chain
        m150323_125407_admin_auth
        m150323_132625_admin_group_auth
        m150331_125022_admin_ngrest_log
        m150615_094744_admin_user_login
        m150617_200836_admin_user_online
        m150626_084948_admin_search_data
        m150915_081559_admin_config
        m150924_112309_cms_nav_prop
        m150924_120914_admin_prop
        m151012_072207_cms_log
        m151022_143429_cms_nav_item_redirect
        m151026_161841_admin_tag
        m160629_092417_cmspermissiontable
        m160915_081618_create_admin_logger_table
        m161219_150240_admin_lang_soft_delete

Apply the above migrations? (yes|no) [no]:yes
*** applying m141104_104622_admin_group
    > create table admin_group ...Exception: SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  la columna «is_deleted» es de tipo b
oolean pero la expresión default es de tipo integer
HINT:  Necesitará reescribir la expresión o aplicarle una conversión de tipo.
The SQL being executed was: CREATE TABLE "admin_group" (
        "id" serial NOT NULL PRIMARY KEY,
        "name" varchar(255) NOT NULL,
        "text" text,
        "is_deleted" boolean DEFAULT 0
) (/home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Schema.php:631)
#0 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Command.php(854): yii\db\Schema->convertException(Object(PDOExcept
ion), 'CREATE TABLE "a...')
#1 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Migration.php(261): yii\db\Command->execute()
#2 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-module-admin/src/migrations/m141104_104622_admin_group.php(14): yii\d
b\Migration->createTable('admin_group', Array)
#3 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/db/Migration.php(94): m141104_104622_admin_group->safeUp()
#4 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(651): yii\db\Migration-
>up()
#5 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(163): yii\console\contr
ollers\BaseMigrateController->migrateUp('m141104_104622_...')
#6 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#7 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
#8 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
#9 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/console/Controller.php(128): yii\base\Controller->runAction('', Array
)
#10 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Module.php(454): yii\console\Controller->runAction('', Array)
#11 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate', 
Array)
#12 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/console/Application.php(104): yii\console\Application->runActio
n('migrate', Array)
#13 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/console/Application.php(147): luya\console\Application->runAction('m
igrate', Array)
#14 /home/ricardo/web/luya-kickstarter/vendor/yiisoft/yii2/base/Application.php(375): yii\console\Application->handleRequest(Ob
ject(yii\console\Request))
#15 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/base/Boot.php(153): yii\base\Application->run()
#16 /home/ricardo/web/luya-kickstarter/vendor/luyadev/luya-core/bin/luya(21): luya\base\Boot->applicationConsole()
#17 {main}
*** failed to apply m141104_104622_admin_group (time: 0.010s)


0 from 34 migrations were applied.

Migration failed. The rest of the migrations are canceled.

The "is_deleted" boolean DEFAULT 0 expression is wrong because 0 is an integer value, not boolean.

Hope this helps! Thank you very much for all your work!

[ricpelo]

Maybe all the $this->boolean()->defaultValue(0) expressions must be changed to $this->boolean()->defaultValue(false)?

[nadar]

True!

What confused me:
in postgresql boolean stands for structure type: boolean: https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/QueryBuilder.php#L68

in mysql boolean stands for structure type: tinyint(1):
https://github.com/yiisoft/yii2/blob/master/framework/db/mysql/QueryBuilder.php#L44

So i assumed the defaultValue for the sql field should be either an integer value.

(maybe the schema builder of Yii2 should typecast the defaultValue for type boolean, whould make both options possible)

[nadar]

@ricpelo Could you please try again? Thank you!

[ricpelo]

@nadar, perfect! Now works perfectly in PostgreSQL :) ./vendor/bin/luya migrate|import|setup works without errors. Thank you very much!

Interestingly, when I go to http://luya.local/admin I get a Response Error: 500 Internal Server Error message. Looking deeply, I can see that when I visit http://luya.local/admin/api-admin-menu I get the following XML response:

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<response>
<name>Unauthorized</name>
<message>Your request was made with invalid credentials.</message>
<code>0</code>
<status>401</status>
<type>yii\web\UnauthorizedHttpException</type>
</response>

Is the above issue related to this one? Do you know if it's triaged?

[nadar]

I don't think this is related to each other, this is maybe more the problem of your webserver related to auhtentication with bearer auth, maybe this helps: yiisoft/yii2#6631 (comment)
Are you using apache? Can you login and then the error appears? (so in your database you will find an access token in the admin_user table...)

Maybe close this issue and open another one, so other people can profit from your problem ;-) ok?

[ricpelo]

Yes, I'm using Apache, the error appears when I login, and I find an access token in the admin_user table :).

OK for me to close this issue now. I'll try opening a new one. Thank you very much!