mysql - Increment File Name Before Extension By 1 in the Database -

i have script uploads file , stores details of file name in database. when document gets uploaded want able update name of file in database proceeded incremental number such _1, _2, _3 (before file extension) if document_id exists. table structure looks this:

id   |  document_id  |  name            |  modified     |   user_id 33   |  81           |  document.docx   |  2014-03-21   |   1 34   |  82           |  doc.docx        |  2014-03-21   |   1 35   |  82           |  doc.docx        |  2014-03-21   |   1 36   |  82           |  doc.docx        |  2014-03-21   |   1 

so in case above want id 35 name doc_1.docx , id 36 name doc_2.docx.

this have got far. have retrieved last file details have been uploaded:

$result1 = mysqli_query($con,"select id, document_id, name, modified  b_bp_history order id desc limit 1");  while($row = mysqli_fetch_array($result1)) { $id = $row['id']; $documentid = $row['document_id']; $documentname = $row['name']; $documentmodified = $row['modified']; } 

so give me details need see whether document_id exists already. thought best see if exist carrying out following:

$sql = "select id, document_id  b_bp_history document_id = $documentid"; $result2 = mysqli_query($sql);  if(mysqli_num_rows($result2) >0){  /* need update */  } else {  /* don't need update in here automatically add database  table no number after it.  not sure if should add first 1  _1 after increment easy? */  } 

as can see above need update in there checks see if number exists after name , if increment one. on else statement i.e. if document_id doesn't exist add first 1 _1.docx increment easier?

if document_id exist update in first half need check last number before extension , increment +1, if it's _1 then next _2. not sure how though either. end result want is:

id   |  document_id  |  name              |  modified     |   user_id 33   |  81           |  document.docx     |  2014-03-21   |   1 34   |  82           |  doc.docx          |  2014-03-21   |   1 35   |  82           |  doc_1.docx        |  2014-03-21   |   1 36   |  82           |  doc_2.docx        |  2014-03-21   |   1 

i hope explains little, thank help.

cheers, andy

generating sequence id value in mysql represent revision id based naming convention

i used mysql 5.5.32 develop , test solution. sure review bottom section of solution few homework assignments future consideration in overall design approach.

summary of requirements , initial comments

a external script writes document history table. meta information user submitted file kept in table, including user assigned name. op requests sql update statement or procedural block of dml operations reassign original document name 1 represents concept of discrete revision id.

• the original table design contains independent primary key: id
• an implied business key exists in relationship between document_id (a numerical id possibly assigned externally script itself) , modified (a date typed value representing when latest revision of document submitted/recorded).

although other rdbms systems have useful objects , built-in features such oracle's sequence object , analytical functions, there options available mysql's sql based capabilities.

setting working schema

below ddl script used build environment discussed in solution. should match op description exception (discussed below):

 create table document_history  (  id int auto_increment primary key,   document_id int,  name varchar(100),  modified datetime,  user_id int   );   insert document_history (document_id, name, modified,    user_id)  values    (81, 'document.docx', convert('2014-03-21 05:00:00',datetime),1),    (82, 'doc.docx', convert('2014-03-21 05:30:00',datetime),1),    (82, 'doc.docx', convert('2014-03-21 05:35:00',datetime),1),    (82, 'doc.docx', convert('2014-03-21 05:50:00',datetime),1);   commit;       

the table document_history designed datetime typed column column called modified. entries document_history table otherwise have high likeliness of returning multiple records queries organized around composite business key combination of: document_id , modified.

how provide sequenced revision id assignment

a creative solution sql based, partitioned row counts in older post: row_number() in mysql @bobince.

a sql query adapted task:

 select t0.document_id, t0.modified, count(*) revision_id    document_history t0    join document_history t1      on t0.document_id = t1.document_id     , t0.modified >= t1.modified   group t0.document_id, t0.modified   order t0.document_id asc, t0.modified asc; 

the resulting output of query using supplied test data:

 | document_id |                     modified | revision_id |  |-------------|------------------------------|-------------|  |          81 | march, 21 2014 05:00:00+0000 |           1 |  |          82 | march, 21 2014 05:30:00+0000 |           1 |  |          82 | march, 21 2014 05:35:00+0000 |           2 |  |          82 | march, 21 2014 05:50:00+0000 |           3 | 

note revision id sequence follows correct order each version checked in , revision sequence resets when counting new series of revisions related different document id.

edit: comment @thomasköhne consider keeping revision_id persistent attribute of version tracking table. derived assigned file name, may preferred because index optimization single-value column more work. revision id alone may useful other purposes such creating accurate sort column querying document's history.

using mysql string manipulation functions

revision identification can benefit additional convention: column name width should sized accommodate appended revision id suffix. mysql string operations help:

 -- resizing string values:   select substr('extralongfilenamexxx',1,17) dual   | substr('extralongfilenamexxx',1,17) |  |-------------------------------------|  |                   extralongfilename |         -- substituting , inserting text within existing string values:   select replace('the quick <lean> fox','<lean>','brown') dual   | replace('the quick <lean> fox','<lean>','brown') |  |--------------------------------------------------|  |                              quick brown fox |    -- combining strings using concatenation   select concat(id, '-', document_id, '-', name)     document_history   | concat(id, '-', document_id, '-', name) |  |-----------------------------------------|  |                      1-81-document.docx |  |                           2-82-doc.docx |   |                           3-82-doc.docx |  |                           4-82-doc.docx | 

pulling together: constructing new file name using revision notation

using previous query above base, inline view (or sub query), next step in generating new file name given revision log record:

sql query revised file name

 select replace(docrec.name, '.', concat('_', rev.revision_id, '.')) new_name,      rev.document_id, rev.modified    (           select t0.document_id, t0.modified, count(*) revision_id             document_history t0             join document_history t1               on t0.document_id = t1.document_id              , t0.modified >= t1.modified            group t0.document_id, t0.modified            order t0.document_id asc, t0.modified asc        ) rev    join document_history docrec     on docrec.document_id = rev.document_id    , docrec.modified = rev.modified; 

output revised file name

 |        new_name | document_id |                     modified |  |-----------------|-------------|------------------------------|  | document_1.docx |          81 | march, 21 2014 05:00:00+0000 |  |      doc_1.docx |          82 | march, 21 2014 05:30:00+0000 |  |      doc_2.docx |          82 | march, 21 2014 05:35:00+0000 |  |      doc_3.docx |          82 | march, 21 2014 05:50:00+0000 | 

these (new_name) values ones required update document_history table. inspection of modified column document_id = 82 shows check-in revisions numbered in correct order respect part of composite business key.

finding un-processed document records

if file name format consistent, sql like operator may enough identify record names have been altered. mysql offers filtering capabilities through regular expressions, offers more flexibility parsing through document name values.

what remains figuring out how update single record or set of records. appropriate place put filter criteria on outermost part of query right after join between aliased tables:

 ...    , docrec.modified = rev.modified  docrec.id = ??? ; 

there other places can optimize faster response times, such within internal sub query derives revision id value... more know specific set of records interested in, can segment beginning sql statements @ of interest.

homework: closing comments on solution

this stuff purely optional , represent side thoughts came mind on aspects of design , usability while writing up.

two-step or one-step?

with current design, there 2 discrete operations per record: insert script , update of value via sql dml call. may annoying have remember 2 sql commands. consider building second table built insert operations.

• use second table (document_list) hold identical information, except possibly 2 columns:

  1. base_file_name (i.e., doc.docx or document.docx) may apply multiple history_id values.
  2. file_name (i.e., doc_1.docx, doc_2.docx, etc.) unique each record.

• set database trigger on source table: document_history , put sql query we've developed inside of it. automatically populate correct revision file name @ same moment after script fills history table.

why bother? suggestion fits under category of scalability of database design. assignment of revision name still 2 step process, second step handled automatically within database, whereas you'd have remember include everywhere invoked dml operation on top of history table.

managing aliases

i didn't see anywhere, assume user assigns name file being tracked. in end, appears may not matter internally tracked thing end user of system never see.

for information, information isn't portrayed customer, saved in table in database version history...

reading history of given document easier if "base" name kept same once has been given:

in data sample above, unless document_id known, may not clear file names listed related. may not problem, practice semantic point of view separate user assigned file names aliases can changed , assigned @ at time.

consider setting separate table tracking "user-friendly" name given end user, , associating document id supposed represent. user may make hundreds or thousands of rename requests... while end file system uses simpler, more consistent naming approach.