| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| file_storage [2023/05/20 22:04] – [More Coming Soon] wtkadmin | file_storage [2025/03/30 17:56] (current) – [Private File Cleanup] wtkadmin |
|---|
| 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. | 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. |
| |
| 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**. | 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. | 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 php> | <code php> |
| function wtkFormFile($fncTable, $fncColPath, $fncFilePath, $fncFileName, $fncLabel = '', $fncColSize = 'm6 s12', $fncRefresh = '', $fncShowOneClickUpload = 'N', $fncAccept = 'accept="image/*"', $fncThumbnail = 'Y') | /** |
| </code> | * @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" |
| | */ |
| |
| * $fncTable name of data table | function wtkFormFile($fncTable, $fncColPath, $fncFilePath, $fncFileName, |
| * $fncColPath name of data column to hold path to image/file | $fncLabel = '', $fncColSize = 'm6 s12', $fncRefresh = '', |
| * $fncFilePath actual path on webserver | $fncShowOneClickUpload = 'N', $fncAccept = 'accept="image/*"', |
| * $fncFileName name of data column to hold new name of file uploaded | $fncThumbnail = 'Y', $fncFormId = '1', $fncAllowDelete = 'Y') { |
| * $fncLabel optionally passed to show as label; if not then uses $fncColName | // ... |
| * $fncColSize MaterializeCSS column sizing - defaults to 'm6 s12' | } |
| * $fncRefresh defaults to blank; set to image ID you want refreshed upon saving (by JS) | </code> |
| * $fncShowOneClickUpload defaults to 'N' but if set to 'Y' then adds button to upload using AJAX without needing a 'Save' button | |
| * $fncAccept defaults to 'accept="image/*"'; you can change this to other document filters like accept=".pdf" | |
| * $fncThumbnail defaults to 'N'; if set to 'Y' then adds an <img id="imgPreview" ...> which will show a preview of images | |
| |
| ===== Public Files ===== | ===== Public Files ===== |
| 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. | 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. |
| |
| These 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). | 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** | **File Definition** |
| `UID` int UNSIGNED NOT NULL AUTO_INCREMENT, | `UID` int UNSIGNED NOT NULL AUTO_INCREMENT, |
| `AddDate` timestamp NOT NULL default CURRENT_TIMESTAMP, | `AddDate` timestamp NOT NULL default CURRENT_TIMESTAMP, |
| | `DelDate` DATETIME, |
| `UserUID` int UNSIGNED COMMENT 'who uploaded', | `UserUID` int UNSIGNED COMMENT 'who uploaded', |
| `TableRelation` varchar(30) NOT NULL, | `TableRelation` varchar(30) NOT NULL, |
| `ParentUID` int UNSIGNED COMMENT 'associated with TableRelation', | `ParentUID` int UNSIGNED COMMENT 'associated with TableRelation', |
| `Description` varchar(120) NULL, | `Description` varchar(120) NULL, |
| `FilePath` varchar(30) NULL, | |
| `OrigFileName` varchar(110) NULL, | `OrigFileName` varchar(110) NULL, |
| | `FilePath` varchar(30) NULL, |
| `NewFileName` varchar(12) NULL, | `NewFileName` varchar(12) NULL, |
| `FileExtension` varchar(20) NOT NULL, | `FileExtension` varchar(20) NOT NULL, |
| `FileSize` int, | `FileSize` int, |
| `CurrentLocation` char(1) DEFAULT 'L' COMMENT 'L for local, A for AWS S3, C for Cloudflare R2', | `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 'Y for AWS, Cloudflare, etc.', | `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', | `TempDownload` ENUM('N','Y') DEFAULT 'N' COMMENT 'Y for private bucket requiring internal download to view', |
| | `Redacted` CHAR(1) DEFAULT 'N', |
| PRIMARY KEY (`UID`), | PRIMARY KEY (`UID`), |
| KEY `ix_wtkFiles_TableRelation` (`TableRelation`,`ParentUID`), | KEY `ix_wtkFiles_TableRelation` (`TableRelation`,`ParentUID`), |
| KEY `ix_wtkFiles_ExternalStorage` (`ExternalStorage`), | KEY `ix_wtkFiles_ExternalStorage` (`ExternalStorage`), |
| FOREIGN KEY (`UserUID`) REFERENCES wtkUsers(`UID`) | CONSTRAINT `fk_wtkFiles_UserUID` |
| ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; | FOREIGN KEY (`UserUID`) REFERENCES wtkUsers(`UID`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1; |
| </code> | </code> |
| |
| * fast uploads due to CDN | * fast uploads due to CDN |
| |
| Message Alec for more details. | [[https://wizardstoolkit.com/contact.php|Contact us]] for more details. |
