Add feature to seed with automatically generated UUID values as primary key

[avinashv]

Potentially related to #17?

I am getting a different error while trying to seed rows when the table has UUID set as the primary key called 'id':

SQLSTATE[HY000]: General error: 1364 Field 'id' doesn't have a default value

Latest Laravel, MySQL, and v3.3.3 of laravel-excel-seeder.

With sqlite the error is SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: table_name.id

(I've replaced the table name myself here.)

Everything works perfectly when the migration uses the traditional $table->id(); instead of $table->uuid('id')->primary();. The Model has public $incrementing = false; and public $keyType = 'string';.

[bfinlay]

Based on those error messages, it looks like your spreadsheet may not have an 'id' column. Does your spreadsheet have an 'id' column?

If the spreadsheet doesn't have an id column, then when it inserts the row since the 'id' column is uuid (string) type and not auto-incrementing, the database doesn't know what to insert. You would need to tell the database what to insert.

When the traditional $table->id is used and it is auto-incrementing, the database automatically determines the ID number.

One way to tell the database what to insert is to create an id column in the spreadsheet and fill it with the UUIDs that you want to use.

Another way would be to use the parsers setting and have the parser function return a new UUID.

[avinashv]

@bfinlay Well diagnosed, thank you!

I have been letting all traditional integer IDs be automatically generated while seeding, and assumed that the same thing would happen for UUIDs. When manually seeding the UUIDs in the Excel file, the seed works as intended. It did not accept my formula, I had to use the actual values otherwise I saw SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed.

I didn't go the parser route: I understood that this is a global change, and I have a mix of UUIDs and integer IDs across the schema.

Might I suggest this as a feature request, then? I feel like UUIDs are a common enough use case to justify this excellent package having it as a part of core functionality.

For anybody who wants a quick and easy Excel formula for generating UUIDs:

=LOWER(CONCATENATE(
    DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",
    DEC2HEX(RANDBETWEEN(0,65535),4),"-",
    DEC2HEX(RANDBETWEEN(0,65535),4),"-",
    DEC2HEX(RANDBETWEEN(0,65535),4),"-",
    DEC2HEX(RANDBETWEEN(0,4294967295),8),
    DEC2HEX(RANDBETWEEN(0,65535),4)
))

Then of course just paste those values into your id field.

[bfinlay]

I have some ideas for how to do this. It may take a couple weeks before I have time to work on it.

[bfinlay]

The seeder can generate UUID automatically now. See UUID in README.MD.

example, assuming your worksheet does not have an ID column in it:

use bfinlay\SpreadsheetSeeder\SpreadsheetSeeder;

class UsersTableSeeder extends SpreadsheetSeeder
{    
    public function settings(SpreadsheetSeederSettings $set)
    {
        $set->uuid = ['id'];
        $set->addColumns = ['id'];
    }
}

I am closing this issue, but feel free to update. Let me know if the new version works for you.