Backticks around index names are MySQL specific

[vlucas]

The generated migrations have backticks in the index names, like this:

$table->integer('affiliate_id')->index('`affiliate_actions_affiliate_id`');

These cause errors in PostgreSQL, as the backtick character is not ANSI SQL, and is specific to MySQL only.

Error text in console from Artisan:

  [Illuminate\Database\QueryException]
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"
  LINE 1: create index `admins_user_id` on "admins" ("user_id")
                       ^ (SQL: create index `admins_user_id` on "admins" ("user_id"))

Index names without the backticks works fine, however:

$table->integer('affiliate_id')->index('affiliate_actions_affiliate_id');

[Xethron]

The reason for adding that was, if the name contained a space, it failed. I guess I should first check if it contains a space and then add standard quotes.

Will take a look at this as soon as I get a moment.

[vlucas]

I think the better approach might be to (1) detect if the connection is MySQL, and only then use backticks, and (2) replace spaces with underscores for the index name.

I ended up just manually editing all my initial migrations, so this isn't a blocker issue for me, but it was a bit surprising that the migrations that were generated were causing errors from the start. Overall though, your code still saved me a ton of work and many hours, so cheers for that! 👍

[joboscribe]

Is this MySQL specific? I don't think backticks can be used in names there either.

I would say it's probably safest/best to remove them for all indices (or names in general) and disallow or replace spaces.

[vlucas]

@joboscribe They are not being used IN names, they are being used to surround names (quote them). And yes, the backtick character is used by MySQL for this in all areas - tables names, column names, etc.

[joboscribe]

@vlucas i'm seeing the following error:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1 near "`more_than`": syntax error (SQL: create index discount_codes_type2_`more_than`_index on "discount_codes_type2" ("`more_than`"))

From the line:

$table->index('`more_than`');

Which makes sense given this from createIndexName:

$index = strtolower($this->table.'_'.implode('_', $columns).'_'.$type);

Since $columns is, in this case, a string with backticks in it. Well, it's an array and that's its only entry, but you get the point.

Very nice tool, though, saved me a lot of hours of hand-crafting my own migrations. Migrations that would have been terribly, horribly broken.

[vlucas]

@joboscribe Ah, I see. It seems that there is a larger issue here than I even first thought.

[normkatz]

After reverse engineering a mysql schema, once I remove the backticks, all
works well when I migrate to postgres with one more exception: Indexes
must be uniquely named in postgres. The xethron module uses an inferred
naming convention with column names but if the same named column such as
"user_id" has an index in more than one table, back to the same parent,
then the index name created clashes with a duplicate on another table. The
solution for me was to add the child table name as a prefix to the index
name, something we do manually in our dev environment. If you're going to
fix the backticks, you might as well also guarantee uniqueness on index
names across multiple tables (migration files) by incorporating the current
table name that needs the index into the index name.

On Wed, Jul 23, 2014 at 1:37 PM, Vance Lucas notifications@github.com
wrote:

@joboscribe https://github.com/joboscribe Ah, I see. It seems that
there is a larger issue here than I even first thought.

—
Reply to this email directly or view it on GitHub
#17 (comment)
.

[Xethron]

The idea behind this is to "recreate" the database already present. So, if the database was created in MySQL with conflicting table names, the migrations should look the same.

However, something I've been wanting to add, is a option to "ignoreIndexNames" and "ignoreForeignKeyNames". That way, they will be blank, and Laravel will use its defaults. This could be useful if you are trying to update your existing database, using Laravel's default db conventions.

However, in most cases, I assume the user would want an identical representation of their database.

[normkatz]

I think that's a reasonable assumption. I agree if you're forward
engineering back to the same db vendor, you should get the same schema that
you started with. So a --ignoreKeyNames flag would be a great workaround
for developers that are moving from one vendor to another and need a
representation that is vendor agnostic. Another possibility is to
"implicitly" code a rule, based on the vendor (postgres, oracle, etc) that
you're pushing to, whether you need to generate key names that are globally
unique across all tables.

On Sun, Jul 27, 2014 at 5:36 AM, Bernhard Breytenbach <
notifications@github.com> wrote:

The idea behind this is to "recreate" the database already present. So, if
the database was created in MySQL with conflicting table names, the
migrations should look the same.

However, something I've been wanting to add, is a option to
"ignoreIndexNames" and "ignoreForeignKeyNames". That way, they will be
blank, and Laravel will use its defaults. This could be useful if you are
trying to update your existing database, using Laravel's default db
conventions.

However, in most cases, I assume the user would want an identical
representation of their database.

—
Reply to this email directly or view it on GitHub
#17 (comment)
.

[Xethron]

Added --defaultIndexNames and --defaultFKNames