Wizard's Toolkit

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== File Storage ======

Almost all websites need to allow uploading files and tracking them.  Sometimes you want to associate an image with a specific data row like a user image associated with user.  Other times you may want to allow uploading multiple documents and/or images associated with a parent row of data.

===== Public versus Secure/Private =====

Some images uploaded need to be **public** so they can be used in lists.  For example avatars or images associated with users will most likely need to be public so when showing users a list of all users they can see the images associated with everyone.

Other images or files need to be **private** or **secure**. For example if your website requires people to upload their driver's license you would not want that to be available to the public.

===== File Uploads =====

The Wizard's Toolkit library has a PHP function that works in conjunction with our JS function to make uploading a file easy.  The **/demo** folder has several example files showing exactly how this is done.  The PHP code requires pulling the data from the SQL table.

===== SQL Data =====

Storing images and documents as blobs in a SQL database is a horrible idea!  This makes backups take considerably longer, stresses the SQL DB by pulling the images out every time needed, plus several other reasons.  For example a DB backup I managed took 12 hours nightly due to images and documents stored in the DB as blobs.  Once it was changed to the Wizard's Toolkit method of storing the location of the file on the server, the nightly backups took less than an hour.

In the data table the `FilePath` is stored in a separate data column than the `NewFileName`.  The reason it is called **New**FileName is because WTK renames the file during the upload process.  That way the length of the 'FileName' column only needs to be varchar(12).  The name is generated by inserting a row into the wtkGUID data table and taking the auto-incremented GUID value.  Then a 'w' is prepended and the original file extension is added.  If the file extension is **jpeg** it will be changed to **jpg**.

For example an uploaded file may end up with a name like `w1087.png`.  Here is an example SQL statement to pull the FirstName and file-associated columns from the wtkUsers table (the FirstName is not relevant for file upload but likely to be needed on the page.

<code sql>
SELECT `FirstName`,`FilePath`,`NewFileName`
 FROM `wtkUsers`
WHERE `UID` = ?
</code>

Then the PHP code would simply be:

<code php>
$pgHtm .= wtkFormFile('wtkUsers','FilePath','/imgs/user/','NewFileName','User Photo','m6 s12');
</code>

The parameter options for **wtkFormFile** are as follows;

<code php>
/**
* @param string $fncTable name of data table
* @param string $fncColPath name of data column to hold path to image/file
* @param string $fncFilePath actual path on webserver
* @param string $fncFileName name of data column to hold new name of file uploaded
* @param string $fncLabel optionally passed to show as label; if not then uses $fncColName
* @param string $fncColSize MaterializeCSS column sizing - defaults to 'm6 s12'
* @param string $fncRefresh defaults to '' blank; set to image ID you want refreshed upon saving (by JS)
* @param string $fncShowOneClickUpload defaults to 'N' but if set to 'Y' then adds button to upload using AJAX without needing a 'Save' button
* @param string $fncAccept defaults to 'accept="image/*"'; you can change this to other document filters like accept=".pdf"
* @param string $fncThumbnail defaults to 'Y'; if set to 'Y' then adds an <img id="imgPreview" ...> which will show a preview of images
* @param number $fncFormId usually leave this with default of '1' but if you have more than one file upload on a page, each must have this parameter different
* @param string $fncAllowDelete defaults to 'Y' which shows a Delete button to delete file on server
* @return html returns surrounding HTML for input type="file"
*/

function wtkFormFile($fncTable, $fncColPath, $fncFilePath, $fncFileName,
    $fncLabel = '', $fncColSize = 'm6 s12', $fncRefresh = '',
    $fncShowOneClickUpload = 'N', $fncAccept = 'accept="image/*"',
    $fncThumbnail = 'Y', $fncFormId = '1', $fncAllowDelete = 'Y') {
// ...
}
</code>

===== Public Files =====

Files which are not sensitive and should be viewable by anyone on the website should be stored in a web folder that is available to the website.  For example a sub-folder named /imgs/ and then you can subcategorize by internal subfolders.  Like /imgs/users/ and /imgs/pets/ .  The above example PHP code shows an example of public file location.

===== wtkFiles Data Table =====

Wizard's Toolkit makes uploading files and tracking them easy with a combination of SQL data, JS and PHP functions.  Plus there is PHP integration with both AWS S3 and Cloudflare R2 (which use the same AWS SDK).  PHP pages are provided for managing uploading of files, storing their locations, managing their location of onsite or external hosting, and viewing of the files regardless of their hosting location.

The information about the uploaded files should be stored in the `wtkFiles` data table.  To make them **private** simply set the `TempDownload` column to 'Y'.  This works regardless of whether file is stored locally or externally (AWS S3 or Cloudflare R2).

**File Definition**
<code SQL>
-- This is the MySQL table structure; a PostgreSQL version is available as well of course

CREATE TABLE `wtkFiles` (
  `UID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `AddDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `DelDate` DATETIME,
  `UserUID` int UNSIGNED COMMENT 'who uploaded',
  `TableRelation` varchar(30) NOT NULL,
  `ParentUID` int UNSIGNED COMMENT 'associated with TableRelation',
  `Description` varchar(120) NULL,
  `OrigFileName` varchar(110) NULL,
  `FilePath` varchar(30) NULL,
  `NewFileName` varchar(12) NULL,
  `FileExtension` varchar(20) NOT NULL,
  `FileSize` int,
  `CurrentLocation` char(1) DEFAULT 'L' COMMENT 'L for local, A for AWS S3, C for Cloudflare R2, X for deleted',
  `ExternalStorage` char(1) DEFAULT 'N' COMMENT 'N for Local only; Y for AWS, Cloudflare, etc.',
  `TempDownload` ENUM('N','Y') DEFAULT 'N' COMMENT 'Y for private bucket requiring internal download to view',
  `Redacted` CHAR(1) DEFAULT 'N',
  PRIMARY KEY (`UID`),
  KEY `ix_wtkFiles_TableRelation` (`TableRelation`,`ParentUID`),
  KEY `ix_wtkFiles_ExternalStorage` (`ExternalStorage`),
  CONSTRAINT `fk_wtkFiles_UserUID`
    FOREIGN KEY (`UserUID`) REFERENCES wtkUsers(`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;
</code>

If you want all your files to be "Private" then you should change the file definition to have `TempDownload` default to 'Y'.

Likewise if you plan on having the majority of your files stored externally you should default `ExternalStorage` to 'Y'.  It is recommended to always keep `CurrentLocation` defaulting to 'L'.  That way your website or app will upload the file to your local server initially.  The wtk/cron.php file has code available which checks all `wtkFiles` that are both `CurrentLocation` = 'L' AND `ExternalStorage` = 'Y'.  Those files it uploads to your Cloudflare R2 or AWS S3, then it updates the `wtkFiles`.`CurrentLocation` to 'C' (or you can change it to 'A') and deletes the file from your local server.

Many websites require having file uploads associated with different purposes or parent tables.  The `wtkFiles` has `TableRelation` and `ParentUID` to track that exact need.  This way if you need file uploads tracked for your `wtkUsers` and your `pets` data tables, they can both use `wtkFiles` and just set the `TableRelation` to 'wtkUsers' or 'pets' respectively.

The `FilePath` stores the location the file is in.  When on your server this will be a relative path.  When on an external storage like S3 or R2 the default code instead uses the 'YY/Mon' of the upload date for the file.  That's so you do not exceed any folder maximums and it also makes it much easier to find in S3 or R2.  For example if the file was uploaded 2022-02-14 then the external path would be /22/Feb/{NewFileName}.

===== Private File Locations =====

When files should not be publicly accessible they should be stored in a secure location.  This can be external storage like AWS S3 or Cloudflare R2.  Or it could simply be in a folder on your server that is not accessible from the website.

When a file is marked as `wtkFiles`.`TempDownload` = 'Y', that tells Wizard's Toolkit `viewFile.php` file to copy them from their secure location into the /exports folder.  During this process it renames them and encodes the names.

==== Private File Cleanup ====

The CRON job (wtk/cron.php) which should run once every minute will delete any files in the /exports folder which are more than 15 minutes old.  You can change that of course to wait longer before deleting or to delete them faster.  The /exports folder is used for this and for exporting reports to CSV or XML.

=== AWS S3 and Cloudflare R2 ===

Generally speaking, the external storage solution by Cloudflare R2 costs dramatically less than Amazon's S3 service.  Cloudflare R2 uses the exact same Amazon S3 SDK so if you have S3 working migrating to Cloudflare is extremely easy code-wise.  If you have external files in S3 and want to migrate them to Cloudflare R2, the Wizard's Toolkit contains pages to manage that for you.  All you need to do is modify the pathing logic based on your current S3 path rules.

Cloudflare R2 advantages over AWS S3
  * less cost than AWS
  * no download costs!
  * no glacier storage so all files download fast, even very old ones
  * huge company - very reliable
  * fast uploads due to CDN

[[https://wizardstoolkit.com/contact.php|Contact us]] for more details.