Unique Constraint not working for : Field(type='upload', unique=True)

[François Delpierre]

Hi,

The unique constraint is not working in the following Field definition.

    Field('f_xlsfile', type='upload', notnull=True,
          requires=[
              IS_UPLOAD_FILENAME(extension='^xlsx?$',
                  error_message='Please post .xls or .xlsx Excel files only.'),
              IS_LENGTH(1048576*2, 1024,
                  error_message='File size must be between 1kB & 2048kB'),
              ],
          unique=True,
          label=T('Excel file')),

[Mariano Reingart]

Hi François:

Could you send us an example of two records where the unique constraint is not working with upload fields?

What are you expecting?

IIRC, there are some parameters for Field so you can handle the file name uniqueness, but generally that is not a good idea as other users could be uploading a different file but with the same filename, and there could be a name collision if you store the files in the disk.

Please see:

http://web2py.com/books/default/chapter/29/07#SQLFORM-and-uploads

If don't want that the contents to be repeated, maybe you have to use blobs:

http://web2py.com/books/default/chapter/29/06

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

--
 
 
 

[DenesL]

Hi Francois,

if I understand correctly you want to avoid duplicated file names.
In this case unique=True is useless because web2py renames the file with a random UUID, and the renamed filename is stored in the field.

But in the following example I created a validator that checks the uploading filename against previous ones.
Note that it uses internal undocumented functions to achieve its purpose so it *might* not be backward compatible.

class UPLOAD_FILENAME_IS_NOT_IN_SET(IS_IN_SET):
    def __init__(self, *a, **b):
        IS_IN_SET.__init__(self, *a, **b)

    def __call__(self, value):
        value, error = IS_IN_SET.__call__(self, value.filename.replace(' ','_') )
        if error == None:
            return (value, self.error_message)
        else:
            return (value, None)


db.define_table('xlup',

  Field('f_xlsfile', type='upload', notnull=True,
          requires=[
              IS_UPLOAD_FILENAME(extension='^xlsx?$',
                  error_message='Please post .xls or .xlsx Excel files only.'),
              IS_LENGTH(1048576*2, 1024,
                  error_message='File size must be between 1kB & 2048kB'),
              ],

#           unique=True,
          label=T('Excel file'))
)


previous_files=[ db.xlup.f_xlsfile.retrieve_file_properties(r.f_xlsfile)['filename'] for r in db().select(db.xlup.f_xlsfile) ]

db.xlup.f_xlsfile.requires.append(UPLOAD_FILENAME_IS_NOT_IN_SET(previous_files, error_message='file already exists'))


Denes

[François Delpierre]

Hi,

I got a quite similar result by working on the validation at the form level:

    if form1.process().accepted:
        session.flash = 'File uploaded'
        redirect(URL('check_xls_file', args=form1.vars.id, user_signature=True))
    elif form1.errors:
        response.flash = 'File rejected'

And in the check_xls_file function:

    # Check that a file with the same name has not been already posted.
    t = db.t_xlsfile
    xlsfilename = t.f_xlsfile.retrieve(t_xlsfile_row.f_xlsfile)[0]
    if xlsfilename in [t.f_xlsfile.retrieve(row.f_xlsfile)[0] for row in db(t.id < t_xlsfile_row.id).select(t.f_xlsfile)]:
        session.flash = T('Sorry, you already posted a file with that name : %s' % (xlsfilename,))
        db.rollback()
        redirect(URL('index'))

Thanks a lot for the quite nice solution. If I had it I would have immediately used it. I was not aware of the db.xlup.f_xlsfile.requires.append() method.