Story:
| Jira Legacy | ||||||
|---|---|---|---|---|---|---|
|
...
- Ephemeral - The generated files will be lost if the hosting environment is restarted
- Space constraints - We can only generate a fixed sized files
- stateless- If running more then one instance of the module is running, it will be difficult to keep track of where the files were generated
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(); |
...