Importing files/images into the database

Importing data into your application may include files such as images or pdf documents. However the standard import screens only support importing textual/numerical data into the database, not files. You will have to upload these files in a seperate process to add them to your database. Here is how to do it.

Downloading the files

Downloading obviously requires your files to be accessible via the internet. You will need an url for every file you wish to import. This can be a url to a public website or for example a Dropbox location. If this is not possible for you, you can also use the file directory of the application you want to import files into itself. Using the resource files, just upload a zip of your files into the application directory, then unzip and you are ready.

Storing the files

So now your files are nicely set up, just waiting to be stored into your database. Now you will need to relate the files to records in your database. 

The relation between the files and database records is not always apparent. In reality you will often just have a directory full of files and nothing else. The name of the file might contain some sort of key to the record or you may have an excel sheet with the filenames and some unique key to the record. For the sake of this example, let's assume you have a such list of url's and keys to the record. Now you can create a (temporary) field in the class you want to store the image in. The datatype should be url or text. Now you can use the import functionality to add the download url to the appropriate records. 

After all the urls are imported you can download and store the files by creating a velocity script that you can call from an url or use in a velocity action helper. The script will use the $session.downloadFile(String) method to download the file. In the example below we are going to import passport photo's into a person class. 

## First we create a query to get a table of of persons where the download url is known
## You may want to include an extra filter for example checking if the passport photo
## has not already been downloaded so you can rerun this script more often.

#set($query = $session.newQuery('Person'))
$query.addFilter('Download url', 'is not null')
#set($persons = $query.getTable())

## Some reporting to the screen
Number of files to download: $table.size() files
<hr>

## Iterate (loop) through to table 
#foreach($person in $persons)
    #set($url = $record.get("Download url"))
    
    ## Download the file. The method expects a string as an argument.
    ## If your datatype is an url, use quotes '$url' to turn it into a string.
    ## Here we are assuming the datatype is already text so we don't need quotes.
    ## We are also assuming the url is absolute (starts with http:// or https://)
    #set($downloadedFile = $session.downloadFile($url))

    #if($downloadedFile.exists())
        
        ## The file has been downloaded into a temporary file. 
        ## We use unconditionalUpdate to store the file because we don't 
        ## want to lock the record or run into a locking conflict. This is 
        ## also a quicker process. When the file is stored, the system creates
        ## a copy of the file and saves it in the appropriate location.
        ## By the we use $void to capture possible return values we are 
        ## not interested in. If not, they would be outputted to the screen.
        #set($void = $person.unconditionalUpdate('Passport photo', $downloadedFile))

        ## Although temporary files are cleaned up automatically, for hygiëne 
        ## and convenience we delete the temporary file immediately.
        #set($void = $downloadedFile.delete())

    #else
        ## The download did not succeed, let's report the failure.
        Could not get file $url for $person.getLabel()<br>    
    #end 
#end

Some other scenarios

Assume you don't have a list of keys and urls to the file. Instead the file names themselves contain some key for example the social security number of the person, a seperator and the postfix 'pp' (for passport photo). Off course you could create a list manually by cutting and pasting in excel or whatever you like, but you can also skip this and just loop throught the records and compose the download url using the information already available in the record. The velocity would then become this. It's shortened here for clarity.

## Some query to get the table $persons

#foreach($person in $persons)
    
    ## Compose the url using a standard formula and assuming 
    ## all images have the .jpg extension and a _pp postfix.
    #set($url = "https://mywebsite.com/passportphoto/$record.get('Social security number')_pp.jpg")
    #set($downloadedFile = $session.downloadFile($url))

## Store the file...

#end

Often you will have to do some files manually because the formula hasn't been followed exactly. Just add some reporting to pick these out or query the database afterwards.

Another common scenario is that you have to store the files into a connected record. For example the passport photos are stored in a connected table to person containing documents, images of various types. Also a person may actually have several passport photos. In this case, import the list of filenames as per usual, but instead of adding them as a text field in the person class, import them in the connected class. When that is finished, you will have a record for each filename in the import file that needs to be downloaded AND connected to the right person. Often in these cases, the file the filename does' t correspond exactly to for example the social security number of a person in the database. Say it look like this [surname of person]_[social security number]_[nr].*. In these case you will need to extract the information you want from the filename in order to relate it to the correct person. An easy way to do this is to use native JAVA string methods. Doing so may result in solving the problem like so:

## Query to get the yet to be connected records
## This allows you to run the script several times
## in case you need to do some adjustments.

#set($query = $session.newQuery('Document' ))
$query.addFilter('Person', 'is null')
$query.addFilter('Filename', 'is not null')
#set($documents = $query.getTable())

## Looping through the documents. Note that we use $doc to avoid
## a clash with the always preloaded context variable $document 

#foreach($doc in $documents)

    #set($filename = $doc.get('Filename'))
    #set($url = "http://www.mywebsite.com/$filename")
    #set($downloadedFile = $session.downloadFile($url))
    
    #if($downloadedFile.exists())
        #set($void = $doc.unconditionalUpdate('File', $downloadedFile))
        #set($void = $downloadedFile.delete())
    #end

    ## Extract key from the filename using native JAVA String methods
    #set($firstUnderscore = $filename.indexOf('_'))
    #set($lastUnderscore = $filename.lastIndexOf('_'))
    #if($firstUnderscore > -1 && $lastUnderscore > -1)
        
        #set($from = $firstUnderscore + 1)
        #set($socialSecurityNr = $filename.substring($from, $lastUnderscore))

        ## Find the person via the social security number

        #set($query = $session.newQuery('Person'))
        $query.addFilter('Social security number, $socialSecurityNr)
        #set($person = $query.getFirstRecord())
        #if($person)

            ## Connect the document with the person
            $doc.unconditionalUpdate('Person', $person.getSingleKeyValue())

        #end
    #end
#end