Yuxu Ge
Article · 2021-01-25

Batch License Photo Processing: Watermarking, Upload, and SQL Generation

Adding Watermarks with ImageMagick

First, ensure your server has ImageMagick installed (on Ubuntu: apt-get install imagemagick). Write a simple shell script, batch_watermark.sh, to iterate through all images in a target directory and apply watermarks to each.

#!/bin/bash
WATERMARK="watermark.png"               # 预先准备好的水印图片路径
SRC_DIR="./cert_images_original"        # 原始证照图片目录
DST_DIR="./cert_images_watermarked"     # 加水印后的输出目录

mkdir -p "$DST_DIR"
for img in "$SRC_DIR"/*.jpg; do
  filename=$(basename "$img")
  # 使用ImageMagick composite命令叠加水印
  composite -gravity southeast -dissolve 80 "$WATERMARK" "$img" "$DST_DIR/$filename"
  if [ $? -eq 0 ]; then
    echo "$filename 处理完成"
  else
    echo "$filename 处理出错" >&2
  fi
done

The script invokes ImageMagick's composite command once per image to overlay the watermark. The -gravity southeast flag positions the watermark in the bottom-right corner; -dissolve 80 sets opacity to 80 (lower values make the watermark more transparent). Processed images go to a new directory, preserving originals. The script logs each file's result, so you can monitor progress.

Tip: Before processing at scale, test the script on a few sample images. Check watermark position and clarity. If you need to adjust size or position, modify the ImageMagick parameters and re-test before running the full batch.

Offline Compression and SCP Transfer

After adding watermarks, you have a batch of marked images. The next step is uploading them to a remote object storage server. Direct file-by-file transfer is inefficient, so compress everything locally first, then transfer once with SCP. Compression also reduces size, cutting upload time.

Use tar to bundle the directory and gzip to compress:

# 将加水印后的图片目录打包压缩
tar -czvf cert_images_watermarked.tar.gz ./cert_images_watermarked

This produces cert_images_watermarked.tar.gz. Next, transfer it to the target server (assume the domain is yourserver.example.com):

# 将压缩包通过scp传输到远程服务器
scp cert_images_watermarked.tar.gz [email protected]:/tmp/

Ensure network connectivity and adequate bandwidth before SCP. For large files, add the -C flag to enable compression during transfer. Once transfer completes, decompress on the server with tar -xzvf to prepare for the upload step.

Note: SCP does not support resumable transfer. Network interruption forces you to restart from the beginning. For files of dozens of gigabytes, use rsync instead—it supports resumable transfer and is more reliable on unstable networks, saving time if the connection breaks mid-way.

Uploading Images to Object Storage (Java Tool)

Once images reach the server and are decompressed, upload them in bulk to object storage (such as an internal cloud service). We wrote a simple Java utility for this task. We chose Java because the project's existing object storage SDK and access controls are implemented in Java; using the existing API ensures safe and reliable uploads.

Upload workflow: The Java tool reads all images from a target directory and uploads each to object storage via API. The storage service returns an access URL or key for each file. We associate these URLs with the corresponding image and store information, preparing to generate database SQL. Here is the pseudocode flow:

List<File> files = listFiles("/tmp/cert_images_watermarked");
for (File file : files) {
    String key = file.getName();  // 使用文件名作为对象存储中的键
    // 调用对象存储SDK上传文件并获取URL
    String url = objectStorageClient.upload(file, key);
    // 记录映射关系，后续用于生成SQL，例如保存在列表或写入文件
    recordMapping(file.getName(), url);
}

In the actual implementation, objectStorageClient.upload wraps the HTTP upload to object storage and returns the public access URL for each file. To simplify the workflow, we use the filename as the storage key. Typically, the URL can be constructed by concatenating a fixed domain with the filename. For example, if the object storage domain is https://files.example.com/ and an image is named store001_license.jpg, its access path would likely be https://files.example.com/store001_license.jpg. This naming strategy means we don't need extra lookups to construct SQL, reducing overhead.

For bulk uploads, implement retry logic. For instance, retry each file a few times on failure, or log failed files for manual or scripted re-upload later, ensuring all files eventually reach object storage.

Generating SQL in Bulk

The final task is generating SQL INSERT statements from the uploaded images to record them in the database. You need one INSERT per license photo, including fields like store ID, certificate type, and image URL. Manual SQL writing is tedious and error-prone at scale, so script this too.

Suppose the database table is store_certificates(store_id, cert_type, image_url, upload_date), with one row per store's certificate image. Write a shell script to read the filename–URL mappings, then concatenate SQL. Here is a sample generate_sql.sh snippet:

#!/bin/bash
OUTPUT_FILE="insert_pics.sql"
BASE_URL="https://files.example.com/"  # 对象存储访问域名

> $OUTPUT_FILE  # 清空输出文件
for file_path in ./cert_images_watermarked/*.jpg; do
  filename=$(basename "$file_path")
  # 假设文件名格式为 storeId_certCode.jpg，例如 "001_yingye.jpg"
  store_id=$(echo "$filename" | cut -d'_' -f1)
  cert_code=$(echo "$filename" | cut -d'_' -f2 | sed 's/\..*//')
  image_url="${BASE_URL}${filename}"
  echo "INSERT INTO store_certificates (store_id, cert_type, image_url, upload_date) VALUES ('$store_id', '$cert_code', '$image_url', NOW());" >> $OUTPUT_FILE
done
echo "生成完毕：`wc -l < $OUTPUT_FILE` 条SQL语句已写入$OUTPUT_FILE"

The script assumes filenames encode store ID and certificate type (e.g., 001_yingye.jpg = store 001's business license). It extracts the needed fields via string slicing and concatenates INSERT statements, writing all to insert_pics.sql. The last line prints the statement count for verification. After generating, execute the SQL file on the target database to insert all image records in bulk.

Note: When generating SQL, escape and quote strings properly. String values need quotes to prevent syntax errors if filenames contain special characters. Also, large INSERT operations should run during off-peak hours with backups in place.

Encountered Problems and Solutions

During actual execution of the batch workflow, we faced several typical issues:

Optimization Opportunities

Though the batch task completed successfully, reviewing the workflow reveals several areas for improvement:

Lessons Learned

This practical experience batch-processing license photos yielded several general principles:

Through these steps and lessons, the batch task succeeded: all store license photos received watermarks, uploaded to object storage, and the database correctly indexed each image location. This hands-on experience not only achieved the goal but also built a foundation for tackling similar batch work in the future.

© 2026 Yuxu Ge ·