upload .DbClean

dpanscik

This is very exciting. I almost have upload working exactly as needed.

I was going to write a script to clean up orphaned files but then realized Allan had already done work in this area. Following the examples at; https://editor.datatables.net/manual/net/upload#Deletion-of-orphaned-files

I did a program break inside of .DbClean. When I delete files, the code never gets inside of .DbClean Not sure why...

here is the upload section of the controller.

                        string localsystempath = "D:\\Users\\dp\\Dropbox (Personal)\\Code\\AppCode\\AppData";
                        string systemIOpath = "\\";
                        response = new Editor(db, "Flex_AccountsPayable", "TableID")
                            .Model<Flex_AccountsPayable3>()
                            .Field(
                                  new Field("Flex_AccountsPayable.TableID")
                                    .Set(false)
                                )
                            .Where("G_AccountIdeintifer", accountIdentifier, "=")
                            .Field(new Field("EmailReceivedDate")
                            //.Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("InvoiceDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("DueDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("AddedIntoQbDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .MJoin(new MJoin("files")
                                    .Link("Flex_AccountsPayable.TableID", "users_files.user_id")
                                    .Link("files.id", "users_files.file_id")
                                    .Field(
                                        new Field("id")
                                            //.Upload(new Upload(System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot", "uploads", "__ID____EXTN__"))
                                            .Upload(new Upload(System.IO.Path.Combine(localsystempath, "uploads", "__ID____EXTN__"))
                                                .Db("files", "id", new Dictionary<string, object>
                                                {
                                                    //{"web_path", System.IO.Path.DirectorySeparatorChar+System.IO.Path.Combine("uploads", "__ID____EXTN__")},
                                                    {"web_path", systemIOpath+System.IO.Path.Combine("uploads", "__ID____EXTN__")},
                                                    //{"system_path", Upload.DbType.SystemPath},
                                                    {"system_path", localsystempath},
                                                    {"filename", Upload.DbType.FileName},
                                                    {"filesize", Upload.DbType.FileSize}
                                                })
                                                .DbClean(data =>{
                                                    foreach (var row in data)
                                                    {
                                                        System.IO.File.Delete(row["system_path"].ToString());
                                                    }

                                                    return true;
                                                })
                                                .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif", "pdf" }, "Please upload an image."))


                                            )
                                    )
                                )
                            .Debug(true)
                            .Process(RequestFull)
                            .Data();

allan

If you delete a row which has a file, it doesn't hit that break point? I don't immediately have an explanation for that I'm afraid - what you have looks correct and it seems to be working okay here.

Allan

dpanscik

Yeah... weird. When running tests, I was noticing files that editor removed from the database were not getting deleted. So I put a breakpoint inside of .DbClean and behold, that section of code was not getting triggered.

dpanscik

Circling back around on this. dbClean does not hit a break point. I don't know why...

Here is my code

                    if (1 == 1)
                    {
                        //This is the static path for user images  
                        string localsystempath = System.Web.Hosting.HostingEnvironment.MapPath("~/uploads/");
                        string systemIOpath = "\\";
                        response = new Editor(db, "Flex_AccountsPayable", "TableID")
                            .Model<Flex_AccountsPayable3>()
                            .Field(
                                  new Field("Flex_AccountsPayable.TableID")
                                    .Set(false)
                                )
                            .Where("G_AccountIdeintifer", accountIdentifier, "=")
                            .Field(new Field("EmailReceivedDate")
                            //.Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("InvoiceDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("DueDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .Field(new Field("AddedIntoQbDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                            .MJoin(new MJoin("files")
                                    .Link("Flex_AccountsPayable.TableID", "users_files.user_id")
                                    .Link("files.id", "users_files.file_id")
                                    .Field(
                                        new Field("id")
                                            .Upload(new Upload(System.IO.Path.Combine(localsystempath, "__ID____EXTN__"))
                                                .Db("files", "id", new Dictionary<string, object>
                                                {
                                                    {"web_path", systemIOpath+System.IO.Path.Combine("uploads","__ID____EXTN__")},
                                                    {"system_path", localsystempath},
                                                    {"filename", Upload.DbType.FileName},
                                                    {"filesize", Upload.DbType.FileSize}
                                                })
                                                .DbClean(data =>{
                                                    foreach (var row in data)
                                                    {
                                                        System.IO.File.Delete(row["system_path"].ToString());
                                                        var logDetail = "AP File Delete: " + row["system_path"].ToString();
                                                        logDetail = logDetail.Substring(0, 255);

                                                        //log the file removal attempt
                                                        var dbTable = new Flex_LogGeneral
                                                        {
                                                            LogTime = DateTime.Now,
                                                            LogInfo = logDetail,
                                                        };
                                                        dbConnection.Flex_LogGeneral.Add(dbTable);
                                                        dbConnection.SaveChanges();

                                                    }

                                                    return true;
                                                })
                                                .Validator(Validation.FileSize(1000000, "Max file size is 1 Meg."))
                                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "gif", "pdf" }, "Please upload an image."))


                                            )
                                    )
                                )
                            .Debug(true)
                            .Process(RequestFull)
                            .Data();
                        
                    }

I start with choosing a file (see screen shot below).

This hits the break point at the top of the editor. But does not hit the break point at dbClean.

You can see the file name has been added to the form. I then click "Create"

Once again we hit the breakpoint at the top of the editor, however the breakpoint at dbClean does not get hit.

The file uploads the the form submits.

allan

I'll give it a try when I get into the office on Monday - I'm not sure why it wouldn't be getting triggered, since it clearly should be.

Could you confirm what version of the Editor .NET libraries you are using please?

Regards,
Allan

dpanscik

Hi Allan,

Sorry I got distracted with non-coding projects for a week or so...

I am using editor v2.1.2

-David

allan

I did as well and didn't manage to try it - sorry! I'm actually travelling this week, but I've marked this discussion so I circle back to it when I'm back. There the Editor 2.2.2 now, but there are no changed in the DbClean area - so I don't believe it would make any difference upgrading.

Allan

dpanscik

Have a good trip!

dpanscik

Hi Allan.

Just a poke to remind you to have a look at this issue.

David

allan

Gosh - yes! Sorry! I've manage to isolate the issue. It is due to how the list of used files is obtained when in an Mjoin. I'm working on a fix at the moment.

Allan

allan

In your code, could you change:

.DbClean(data =>{

to be the following please?:

.DbClean("users_files.file_id", data =>{

That will should allow it to work as expected.

The issue, as I noted above, is how the list of files that are unused is determined - the generation for the SQL query when Mjoin is used isn't smart enough to figure out the correct table and field names to use from the link table - thus it will never find any matching files, and the callback is never called.

It needs a little extra direction and the overload for DbClean to add the table / field name as the first parameter does that.

I've just been looking at the git history to see when I added that (I don't remember it!) and it has always been in the .NET libraries since I ported them from the PHP ones. My apologies for having totally forgotten about that and drawn this out much longer than it should have been.

I'm going to update the documentation to add details about this overload - it doesn't (yet) have those details.

Regards,
Allan

allan

Docs updated to note that this parameter is possible and indeed required when using Mjoin.

Allan

dpanscik

Thanks Allan. Ill implement this change in my code. Appreciate the assistance. Thank you.