Editor upload files with uuid not working.

naspersgasp

Hi,

Good day.

My existing file table uses uuids. When I try to set the example database table to use uuids as well, I get the following error in the upload many example(NodeJS):

Unhandled promise error: [object Promise]Error: update files set web_path = '/uploads/0.jpg', system_path = '/Users/thejj/Downloads/Editor-NodeJS-1.7.4/controllers/../public/uploads/0.jpg' where id = 0 - ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: '692745d4-8f39-11e8-add9-0242ac170002'

I've not changed anything in the upload many example. All I did was apply the upload patch you told me about, which works. Is there anything special I need to configure to use uuids? Thanks.

Regards.

allan

If a uuid is being used, the 0 in the file name looks a bit odd - I woudl have expected that to be a uuid.

Could you enable its debug mode (.pebug(true)) immediately before the .process(...) call and then show me the JSON returned from the server when that error happens? Could you also show me your Node Editor configuration?

Thanks,
Allan

naspersgasp

Hi,

Good day.

It is the upload-many example. I didn't change anything. All I did was add a trigger to insert uuid instead of int. Oh yes, and change column from INT to VARCHAR to hold uuid.

My template:

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "/api/upload-many",
        table: "#example",
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select"
            }, {
                label: "Images:",
                name: "files[].id",
                type: "uploadMany",
                display: function ( fileId, counter ) {
                    return '<img src="'+editor.file( 'files', fileId ).web_path+'"/>';
                },
                noFileText: 'No images'
            }
        ]
    } );

    var table = $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "/api/upload-many",
            type: "POST"
        },
        // ajax: "/api/upload-many",
        serverSide: true,
        columns: [
            { data: "users.first_name" },
            { data: "users.last_name" },
            { data: "users.phone" },
            { data: "sites.name" },
            {
                data: "files",
                render: function ( d ) {
                    return d.length ?
                        d.length+' image(s)' :
                        'No image';
                },
                title: "Image"
            }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

My node server js:

let editor = new Editor(db, 'users')
        .fields(
            new Field('users.first_name'),
            new Field('users.last_name'),
            new Field('users.phone'),
            new Field('users.site').options(
                new Options().table('sites').value('id').label('name')
            ),
            new Field('sites.name')
        )
        .leftJoin('sites', 'sites.id', '=', 'users.site')
        .join(
            new Mjoin('files')
                .link('users.id', 'users_files.user_id')
                .link('files.id', 'users_files.file_id')
                .fields(
                    new Field('id').upload(
                        new Upload(__dirname + '/../public/uploads/{id}.{extn}')
                            .db('files', 'id', {
                                filename: Upload.Db.FileName,
                                filesize: Upload.Db.FileSize,
                                web_path: '/uploads/{id}.{extn}',
                                system_path: Upload.Db.SystemPath
                            })
                            .validator(Validate.fileSize(500000, 'Files must be smaller than 500K'))
                            .validator(
                                Validate.fileExtensions(
                                    ['png', 'jpg', 'gif'],
                                    'Only image files can be uploaded (png, jpg and gif)'
                                )
                            )
                    )
                )
        );

    await editor.process(req.body, req.files);
        res.json(editor.data());

The .pebug(true) does not work? Get an error that the function does not exist in editor.

Regards.

naspersgasp

Assumed the pebug was a typo Changed it to debug(true). No debug JSON is returned. I get the exact same error.

To reproduce, use your upload many example and change the files id column to VARCHAR(36) and the users_files file_id column to VARCHAR(36).
Create trigger with:

CREATE TRIGGER `before_insert_file` BEFORE INSERT ON `files`
 FOR EACH ROW SET new.id = uuid()

Those are the only changes I made.

Regards.

allan

pebug - doh... A bug in my debugging!

It would appear that what is happening here is that MySQL will only return the last insert id if it was an auto increment, and it doesn't support the RETURNING command that other database engines do. The only way to get the newly inserted uuid is to do a SELECT statement to get a variable to which the uuid was inserted (and then used for the insert) - these two threads are particularly useful: 1 2.

In Postgres this isn't an issue since it does support RETURNING, but MySQL is going to require a workaround - unfortunately it isn't immediately clear what that should be!

Without inserting database specific code, the only way I can see around this is to allow the Javascript to create and specify the primary key value (uuid in this case).

Is using a number value for your pkey an option here?

Allan

naspersgasp

Hi,

Good day @allan.

Thanks for explaining the issue. If I'm following correctly, this is an issue in the editor NodeJS server side library? And it's not something that's easily fixable?

Changing the Pkeys would be some work as all the related Pkeys are uuids as well. It's a legacy application migration that I'm busy with. Thought editor would make the crud part of the migration easier.

I'll have a read through the links you posted and see if I can maybe follow the server code and come up with a solution. Or, just replace all the Pkeys.

Thanks.

Regards.

allan

So its not just the file table which uses uuid's but all tables? Yes, that's going to pose the Editor libraries some issues I'm afraid. MySQL doesn't (as far as I can see) provide an API to get the last inserted id, if it isn't an auto_increment column. The result is that you need to create the uuid and then store it in a variable, use that in the query and then select that variable to read it! That's a massive mess of code that is incompatible with other databases.

In the case of the files, I think it would be possible to modify the code to be able to generate a uuid in the Node code and then use that on the database (i.e. provide the pkey value). For more regular rows I think its going to become more problimatic though.

Allan

naspersgasp

I'm looking at converting the uuids instead. Thanks for the help. Regards.