Story:
| Jira Legacy | ||||||
|---|---|---|---|---|---|---|
|
| Table of Contents |
|---|
Options that were considered:
1) Local File Storage
2) Postgres DB
2) AWS S3
3) A generic implementation that can be used community wise irrespective of hosting technology
Local File Storage:
Generated files could be stored in the local file System. This method is already planned to be used in File uploading when Instance UUIDs are uploaded.
...
- Ephemeral - The generated files will be lost if the hosting environment is restarted
- Space constraints - We can only generate a fixed sized files
Postgres DB
PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object
| Bytea | Large Object |
|---|---|
| data type is not well suited for storing very large amounts of binary data | storing binary data is better suited to storing very large values |
| can hold up to 1 GB of binary data | can store upto 4TB |
| it would require a huge amount of memory to process such a large value | have some security issues since anyone connected to the database can view and/or modify any Large Object, even if they don't have permissions to view/update the row containing the Large Object reference |
Processing: CREATE TABLE files (imgname text, img bytea);File file = new File("myTxt.txt");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO files VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close(); | Processing: CREATE TABLE fileslo (fileName text, filesoid oid);// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
// Now open the file
File file = new File("myTxt.txt");
FileInputStream fis = new FileInputStream(file);
// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0) {
obj.write(buf, 0, s);
tl += s;
}
// Close the large object
obj.close();
// Now insert the row into imageslo
PreparedStatement ps = conn.prepareStatement("INSERT INTO fileslo VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close(); |
Pros:
- As postgres is already the DB used by FOLIO, we could use that to store the files
- As it is already used DB , no special maintenance is necessary
...