DB Size Problem

Ninox file format and Database size spiraling out of control are old threads, but as far as I know Ninox hasn't changed how data that is deleted in the database file is handled. When you download a manual backup copy, you are getting a "packed" copy. Meaning, all information flagged as deleted is deleted and any, but the last database schema are deleted.

I can !

As I have recently been through all this issue and spent many hours going through this in detail with Ninox support!

How are you deleting the images? and are these images in image cells that you have created OR on that Ninox paperclip?

Let us take the first! image cells - if you delete an image item (Ignore this code bit for now ie  image := null or image := " " etc) they are Soft deleted!

Ninox paperclip - you deleted these. They are also Soft deleted.

They should be hard deleted but they actually stay sat on the Ninox server (unless you also remove all backups (Manual and Auto type) that were associated with the Deleted images in your DB! - WOW! ie last nights backup has images in it that you may delete today. - Ninox server maintains those ref links etc incase you restored the backup. Hope that makes sense.

There are more complexities but as an example one of my DB bloated to nearly 3gig (even though I delete images every day) - once I purged these and removed all associated backsup etc the next day it was should just under 1/2 MB

However removing images manually is a pain in the backside!

Fortunately with @rosoft_steven help I now have a set of tools that can sweep through a record and delete images in bulk on one or all records etc with table (happy to post here but credit is down the Stephen)

Ninox are aware of this issue and promise to do something about Image handling!. Cos it is a nonsense to expect user to delete images by the drop down one at a time.

So quick fix is

Remove the images. and take 2 backups ! Then delete all backup for that db prior to you doing the purge.

Then depending on how you are deleted your images (ie by script) you may need one or more of the life line scripts Stephen helped me with! as these go on to absolutely flag them for deletion on the server (you still have to remove prior back ups until Ninox come up with a proper FIX!

Removing an image field image by script ie x := null or x:= " " gets rid of it from the image field but puts it back onto the paperclip !!

Mel

actual note  verbatum !

Also when you have shredded the files etc. Don't expect the available user space to suddenly record what you have. Ninox say it only updates roughly twice a day. In fact it appears only update over night ! cloud version)

When I have finished collating all the emails back from me/ninox/me that have gone back and forth I will put on a separate post up to help others.

Finally - do to some aspect of the way images are removed it seem that some (lots? becomes orphan files. Hence the need for Stephen's excellent code for purging files

I have now resorted to a dashboard (the new pages) just to deal with image removal ! (apart from top one!)

I will upload scripts during tomorrow as I now run out of play time 

Ha ha - made the wife a coffee and got let of the hook !!!!

highlighted text are my table names

Button - Delete images in job dockets - This routine is to simply to remove the Field image files (NOT paperclip) from the jobs (soft delete them) in bulk. Another script will remove them (set the server to clear them)

clears 6 image fields

let mySure := dialog("Sure?", "Are you Sure?", ["Yes", "No"]);
let myDate := today() - 150; lets me keep 150 days of records with the image still in

if mySure = "Yes" then
    for a in select JobDockets where Status = 4 or Status = 7 or Status = 6 and 'Order Date' < myDate do
        a.(SupplierQuote := null);
        a.(SupplierConfirmation := null);
        a.(SupplierVisual := null);
        a.(Image1 := null);
        a.(Image2 := null);
        a.(Image3 := null);
        a.(Image4 := null);
        alert("Images have been soft deleted from the system")
    end
else
    alert("Okay - Nothing has been deleted")
end

clear a sigle field image in bulk! (again not paperclip)

Button - Delete images from Quotes File - Again soft deletes them

let myDate := today() - 150; lets me keep 150 days of records with the image still in
let mySure := dialog("Sure?", "Are you Sure?", ["Yes", "No"]);
if mySure = "Yes" then
    for a in select Quotes where Status = 5 or Status = 7 and QteDate < myDate do
        a.(QuoteImage := null);
        alert("Records have been deleted from the system")
    end
else
    alert("Okay - Nothing has been deleted")
end

Button - Delete soft deleted image files in Jobdockets ie get rid from the paperclip & the server - Remember these do not truly go if you have backups relating to the db that have same linked data !!

USE WITH CARE !!!!

let mySure := dialog("Sure?", "Are you Sure?", ["Yes", "No"]);
if mySure = "Yes" then
    let method := "DELETE";
    let teamid := teamId();
    let dbid := databaseId();
    let tid := "B";
    let cont := "application/json";
    let bear := "Bearer " + first(select Settings).'API-Key';
    for i in select JobDockets where 'Order Date' <= today() - 400 do
        let rid := text(i);
        while cnt(files(i)) != 0 do 
            let file := urlEncode(text(i.Filename));
            let url := ---
https://api.ninox.com/v1/teams/{ teamid }/databases/{ dbid }/tables/{ tid }/records/{ rid }/files/{ file }
                ---;
            let response := do as server
                    http(method, url, {
                        Authorization: bear,
                        'Content-Type': cont
                    }, {})
                end;
            if response.error then
                alert(text(response.error))
            end
        end

    end
else
    alert("Okay - Nothing has been deleted")
end

I have other scripts but they are only variation for dealing with files from other tables etc... above should eb easy to adapt for your tables.

Without RoSoft_Steven help I would be dealing files one at a time !!!

Can't tell you how much this improved the admin of deleting file images from one of my other DB's that has over 800,000 records in it in one table !