YumInstallController.php working for SQLite

[GoogleCodeExporter]

Hi,

First, thanks for your great work so far.

I've made few changes to "YumInstallController.php" to make it work with SQLite.
Changes are located in function actionInstall()

---
SQLite can only drop table one by one :
(at line 53:)
// Clean up existing Installation
$db->createCommand(sprintf('drop table if exists %s', $usersTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$profileFieldsTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$profileFieldsGroupTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$profileTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$messagesTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', $rolesTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$userRoleTable))->execute();
$db->createCommand(sprintf('drop table if exists %s', 
$userUserTable))->execute();

---
Every "auto increment" field should be created that way :
xxxx integer NOT NULL PRIMARY KEY AUTOINCREMENT

---
Indexes should be created separatly :
...
$db->createCommand($sql)->execute();
$sql = "CREATE INDEX IF NOT EXISTS `status` ON `" . $usersTable . "`(`status`)";
...

---
ENUM doesn't exist in SQLite, I've disabled it for "$profileTable".

---
Several fields are created "NOT NULL", but without default values (in 
"$profileFieldsTable" and "$profileTable").
I've added "default ''".

---
SQLite can't do "INSERT INTO" with multiple "VALUES", I've been using "SELECT 
UNION" instead :
...
$sql = "INSERT INTO `".$rolesTable."` (`title`,`description`) SELECT
    'UserCreator', 'This users can create new Users'
    UNION SELECT
    'UserRemover', 'This users can remove other Users'";
$db->createCommand($sql)->execute();
...

---
I've attached the file with my changes. I didn't know where to put the "switch" 
to execute mysql or sqlite code.

Keep up your good work ;)

Original issue reported on code.google.com by ouebfra...@gmail.com on 6 Jul 2010 at 12:26

Attachments:

• YumInstallController.php

[GoogleCodeExporter]

Well, there's another issue related to make SQLite work :

user/components/CAdvancedArBehavior.php

186 : return sprintf("delete ignore from %s where %s = '%s'",

The "ignore" option doesn't work with SQLite.

Original comment by ouebfra...@gmail.com on 6 Jul 2010 at 2:53

[GoogleCodeExporter]

Hi. I have merged some of your changes into the new 0.8 svn work i've began 
today. unfortunately there is more work to do. We need to have 4 different 
install scripts for each major database, that's mysql, sqlite, postgrseql and 
maybe MS-Database stuff. Since i am using mysql only, i need someone else who 
works with one of the other databases to provide Installation scripts. thanks 
for this first one with sqlite.

Do you -really- use Yum on an sqlite database? 

Original comment by thyseus on 30 Jul 2010 at 7:21

• Changed state: Accepted

[GoogleCodeExporter]

I don't use Yum for now as it doesn't work well with sqlite (well, that's 
because it's not quite implemented). And I really need it to work with sqlite.
By the way, Yum appears to be the better compromise I've found so far to user 
management.
If you need I could try to put some more efforts on it.

Original comment by ouebfra...@gmail.com on 30 Jul 2010 at 8:08

[GoogleCodeExporter]

Hi,

Here's an updated version of the "YumInstalController.php" that should work 
indifferently for both Mysql and Sqlite database.
The changes are located in actionInstall().

---

At the begining of the function creation of variables to manage inline 
differences between Mysql and Sqlite.

if(Yii::app()->db->getDriverName()=="sqlite")
{
    $idField = 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT';
    $sqlEnd = ' ;';
    $sqlEnum = "VARCHAR(20) NOT NULL default ''";
    //
    $sqlInsertBegin = " SELECT ";
    $sqlInsertUnion = " UNION SELECT ";
    $sqlInsertEnd = "";
} else {
    $id_field = 'int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY';
    $sqlEnd = ' ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;';
    $sqlEnum = "ENUM('protected', 'private', 'public') NOT NULL";
    //
    $sqlInsertBegin = " VALUES ( ";
    $sqlInsertUnion = ") , (";
    $sqlInsertEnd = ")";
}

---

My previous commited file didn't work for Mysql because the "create index if 
not exists" doesn't exist.
I think with can remove the "if not exists" for all.

CREATE INDEX [...]

Then create index separatly instead of direct creation within CREATE TABLE 
statement. It should work with all database (?).

---

SELECT [...]
UNION
SELECT [...]

instead of :
INSERT INTO [...] VALUES ([...]) , ([...]) , ([...])

The sql statement is made with the $sqlInsertBegin, $sqlInsertUnio and 
$sqlInsertEnd variables.

---

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity 
constraint violation: 19 profile_fields.hint may not be NULL
...
and many others
=>
[...]
`hint` text NOT NULL default '',
`match` varchar(255) NOT NULL default '',
`range` varchar(255) NOT NULL default '',
`error_message` varchar(255) NOT NULL default '',
[...]
`default` varchar(255) NOT NULL default '',
[...]

---
As ENUM doesn't exist in SQLite, I've disabled it for "$profileTable".
A dedicated variable $sqlEnum put the relevant code.

---

All theses changes should lead to a successful installation for Sqlite database 
(and Mysql too, without any modification).

Original comment by ouebfra...@gmail.com on 4 Aug 2010 at 11:40

Attachments:

• YumInstallController.php

[GoogleCodeExporter]

Here's my updated (and fixed) "YumInstallController.php" according to r111 
(settings and textsettings tables, and default values - and, I've added the 
french default value).

With the last svn changes - r111 - I've got an issue related to 'yumsettings', 
I've temporarly commented line 153 in my UserModule.php :
//$settings = YumSettings::model()->find('is_active');)
I guess this is "work in progress" svn :)

Original comment by ouebfra...@gmail.com on 4 Aug 2010 at 12:58

Attachments:

• YumInstallController.php