I have a web app that provides the user with the usual CRUD functionality for "widgets" let's say. But I also have give them the option of creating widgets in bulk via importing csv files. The logic I have right now works, but the scope of the app just grew. In addition to writing the records to the main db, I have now been asked to write widget data to 4 other databases used by a different application. (the 4 databases all belong to one business app, which doesn't have to be separate, but I cannot change it at this point in time).
The code I'm using right now looks something like this (pseudocode):
open file loop through all records in csv file for each record connect to main db insert into main db for each widget_manager app server connect insert disconnect if at any time there is a failure with any insert rollback all inserts. end end disconnect main db end
Currently, depending on the size of the csv file, while the import is running the database is locked. I can't select anything or use other pages in the app.
Question / Goals
I have three goals in mind:
- prevent locking if at all possible.
- provide real time feedback to the end user to show that something is happening... can i do this for each record as its being added? right now, i use an Ajax call but of course, the results only show up at the end of the for loop, when all records have been accounted for. I do have an animated gif that runs for the duration of the import, which is helpful. But it'd be cool if I could show the records being added somehow. I'm currently reviewing the following post: https://stackoverflow.com/questions/28172863/show-a-modal-window-with-a-progress-during-ajax-call
to see how things would work if I drove the import logic on the client side... So far, I'm not sure of what the benefits / risks would be.
- simplify the code by breaking it down into multiple functions. So for example, instead of just having one button called "import" that does it all, have two buttons. One called "import to main db" and the other "import to widget manager" or something. The later is what will update the 4 new databases that i have to write to. Note: it's OK if the record exists in the main app, but the insert to the widget manager fails. I just need to make a note of it in logs. Again, another bad business requirement, but I have no control over that.
- The other idea I had was to programmatically limit the file size to "n" number of records. But I'm not sure if this will annoy the end users or not.
If you see a problem with the approach I'm considering, please let me know. Also if you have any ideas on how I can provide real time feedback... that'd be great.
open file loop through all records in csv file for each record connect to main db START TX on main db ***** insert into main db for each widget_manager app server connect insert disconnect if at any time there is a failure with any insert rollback all inserts. end end END TX on main db ***** disconnect main db end