You could insert the row with a "not yet published" flag, and only create the file after the insert. Or have a separate table with just the auto-increment field and allocate ids whenever you want to use for the filenames and insertion into the main table.

++. Interesting approach. Get the database set up first, determine what the last id was, then continue processing.

Didn't think of going this route.



"Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

sulfericacid

ysth writes:

You could insert the row with a "not yet published" flag, and only create the file after the insert.

Instead of keeping the image data in a file, consider storing it in the database in the same row that describes it. Keeping a file system and database in sync can tricky. You have to be prepared to handle files which don't have database entries and database entries which don't refer to files. That's why it can be simpler just to store your file data in your database (e.g. in a BLOB column.) Just something to think about...

Also, to avoid the race condition that ysth refers to, try to perform the determination of the last number used and the updating of the new row in one SQL statement:

  -- create the new image row with blank gallery_id:
  INSERT INTO images ... VALUES (...);
  -- atomically update the gallery_id field
  UPDATE images SET gallery_id = (SELECT 1+MAX(...) FROM images) WHERE
+ ...;
[download]

Another possibility is to do it all in one INSERT statement:

  INSERT INTO images (gallery_id, title, description, fstop)
    SELECT 1+MAX(...), 'title', 'description', 'fstop' FROM images;
[download]

You'll run into a race condition if you have perl figure out the next id and then use that id in a subsequent INSERT or UPDATE statement.

Believe me, I have been bitten by it several times.