Index unique column is not being enforced outside of editor.

Hi Jeff, 

Yea, the CMSB "unique" value validation runs at the application level. 

I think we initially did it that way many years ago because virtual hosts didn't always grant INDEX privileges.  You can actually see under: Admin > General > Server Info > Database Server > Privileges we're displaying the INDEX status to indicate if it's available on the server.  That said, it's on our list to implement this feature as well.  

You can either manually add a unique index yourself through the Developer Console plugin, or here's some code you can add to ensure it's always present (such as before an import operation):

// Ensure accounts.username has a UNIQUE INDEX on it
$hasUniqueIndex = DB::query("SHOW INDEX FROM ::accounts WHERE Non_unique = ?", 0)->pluck('Column_name')->contains('username');
if (!$hasUniqueIndex) {
    DB::query("ALTER TABLE ::accounts ADD UNIQUE INDEX (username)");
}

You'll then be able to see that index under: CMS Setup > Database > User Accounts] > Source
Or: admin.php?menu=database&action=editTable&tableName=accounts#tab_mysqlSource

ZenDB does not appear to support the REPLACE command, which can be used as an alternative to INSERT. Right now, we need to do a get or query, then insert for each record. 

Try using the DB::query() method as I've done above for executing any custom queries.

Also, would views and stored procedures be possible?

Yes, but we don't have the CMSB logic to store and recreate them in the CMS schema files.  So when we need to do things like that we often add application code to detect and recreate them, or maybe a plugin to do it once, as we often need to move code between servers and need everything to be portable.  I usually use the Developer Console Plugin for quick queries.

Let me know any questions.

Thanks, I can implement that. While on the subject the other think that would be nice is adjusting the default value. When I added a column using quick add, ithe editor creates a medium text field by default with null as the default value. I realized I wanted this as a int with default 0. The column editor changed the data type Ok but ignored the default value leaving it at null. Not a big deal to change it via rhe developer console or any database tool, it would be great if when default value is set, that it was refected in the mysql source. 

That was slick - it worked like a charm. Wish I had realized that 10 years ago.