public static void AddFile (this DBWork me, DB db, DBFile file, string filename, bool hidden)
{
using (IDbCommand cmd = db.CreateCommand ()) {
cmd.CommandText = "INSERT INTO WorkFile (work_id, file_id, hidden, filename) VALUES (@work_id, @file_id, @hidden, @filename);";
DB.CreateParameter (cmd, "work_id", me.id);
DB.CreateParameter (cmd, "file_id", file.id);
DB.CreateParameter (cmd, "hidden", hidden);
DB.CreateParameter (cmd, "filename", filename);
cmd.ExecuteNonQuery ();
}
}
public static int CompressFiles ()
{
byte [] buffer = new byte [1024];
int read;
long saved_space = 0;
using (DB db = new DB (true)) {
using (DB db_save = new DB (true)) {
using (IDbCommand cmd = db.CreateCommand ()) {
cmd.CommandText = @"
SELECT File.*
FROM File
WHERE (File.compressed_mime = '' OR File.compressed_mime IS NULL)
AND File.size <> 0
AND File.id IN
(SELECT WorkFile.file_id FROM WorkFile WHERE WorkFile.file_id = File.id)
LIMIT 10
";
using (IDataReader reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
DBFile file = new DBFile (reader);
long srclength;
long destlength = -1;
string tmpfile = Path.GetTempFileName ();
string tmpfilegz;
Console.Write ("Downloading {0} = {1} with size {2}... ", file.id, file.filename, file.size);
using (Stream stream_reader = db_save.Download (file)) {
using (FileStream stream_writer = new FileStream (tmpfile, FileMode.Create, FileAccess.Write)) {
while (0 < (read = stream_reader.Read (buffer, 0, buffer.Length))) {
stream_writer.Write (buffer, 0, read);
}
}
}
srclength = new FileInfo (tmpfile).Length;
Console.Write ("Compressing file {0} with size {1}... ", tmpfile, srclength);
tmpfilegz = FileUtilities.GZCompress (tmpfile);
if (tmpfilegz == null) {
Console.WriteLine ("Compression didn't succeed.");
} else {
destlength = new FileInfo (tmpfilegz).Length;
Console.WriteLine ("Success, compressed size: {0} ({1}%)", destlength, 100 * (double) destlength / (double) srclength);
using (IDbTransaction transaction = db_save.BeginTransaction ()) {
// Upload the compressed file.
// Npgsql doesn't seem to have a way to truncate a large object,
// so we just create a new large object and delete the old one.
int file_id = file.file_id.Value;
int gzfile_id = db_save.Manager.Create (LargeObjectManager.READWRITE);
LargeObject gzfile = db_save.Manager.Open (gzfile_id, LargeObjectManager.READWRITE);
using (FileStream st = new FileStream (tmpfilegz, FileMode.Open, FileAccess.Read, FileShare.Read)) {
while (0 < (read = st.Read (buffer, 0, buffer.Length)))
gzfile.Write (buffer, 0, read);
}
gzfile.Close ();
// Save to our File record
file.file_id = gzfile_id;
file.compressed_mime = "application/x-gzip";
file.Save (db_save);
// Delete the old large object
db_save.Manager.Delete (file_id);
transaction.Commit ();
saved_space += (srclength - destlength);
}
}
if (File.Exists (tmpfilegz)) {
try {
File.Delete (tmpfilegz);
} catch {
// Ignore
}
}
if (File.Exists (tmpfile)) {
try {
File.Delete (tmpfile);
} catch {
// Ignore
}
}
}
}
//}
}
}
}
Console.WriteLine ("Saved {0} bytes.", saved_space);
return 0;
}
public static int MoveFilesToFileSystem ()
{
long moved_bytes = 0;
Manager.log.Info ("MoveFilesToFileSystem: [START]");
using (DB db = new DB ()) {
using (DB download_db = new DB ()) {
while (true) {
using (IDbCommand cmd = db.CreateCommand ()) {
// execute this in chunks to avoid huge data transfers and slowdowns.
cmd.CommandText = "SELECT * FROM File WHERE NOT file_id IS NULL LIMIT 100";
using (IDataReader reader = cmd.ExecuteReader ()) {
if (!reader.Read ())
break;
do {
DBFile file = new DBFile (reader);
byte [] buffer = new byte [1024];
int oid = file.file_id.Value;
int read;
string fn = FileUtilities.CreateFilename (file.md5, file.compressed_mime == MimeTypes.GZ, true);
using (FileStream writer = new FileStream (fn, FileMode.Create, FileAccess.Write, FileShare.Read)) {
using (Stream str = download_db.Download (file)) {
while ((read = str.Read (buffer, 0, buffer.Length)) != 0)
writer.Write (buffer, 0, read);
}
}
IDbTransaction transaction = download_db.BeginTransaction ();
download_db.Manager.Delete (oid);
file.file_id = null;
file.Save (download_db);
transaction.Commit ();
moved_bytes += file.size;
log.InfoFormat ("MoveFilesToFileSystem: Moved oid {0} to {1} ({2} bytes, {3} total bytes moved)", oid, fn, file.size, moved_bytes);
} while (reader.Read ());
}
}
}
while (true) {
using (IDbCommand cmd = db.CreateCommand ()) {
// execute this in chunks to avoid huge data transfers and slowdowns.
cmd.CommandText = "SELECT * FROM Revision WHERE (diff_file_id IS NULL AND NOT diff = '') OR (log_file_id IS NULL AND NOT log = '') LIMIT 100";
using (IDataReader reader = cmd.ExecuteReader ()) {
if (!reader.Read ())
break;
do {
DBRevision revision = new DBRevision (reader);
string tmpfile = null;
if (!string.IsNullOrEmpty (revision.diff)) {
int length = 0;
if (revision.diff_file_id == null) {
try {
length = revision.diff.Length;
tmpfile = Path.GetTempFileName ();
File.WriteAllText (tmpfile, revision.diff);
DBFile diff = download_db.Upload (tmpfile, ".log", false, null);
revision.diff_file_id = diff.id;
revision.diff = null;
} finally {
try {
if (File.Exists (tmpfile))
File.Delete (tmpfile);
} catch (Exception ex) {
log.ErrorFormat ("error deleting temp file: {0}", ex);
}
}
moved_bytes += length;
log.InfoFormat ("MoveFilesToFileSystem: Moved revision {0}'s diff to db/filesystem ({1} bytes, {2} total bytes moved)", revision.id, length, moved_bytes);
}
}
if (!string.IsNullOrEmpty (revision.log)) {
int length = 0;
if (revision.log_file_id == null) {
try {
length = revision.log.Length;
tmpfile = Path.GetTempFileName ();
File.WriteAllText (tmpfile, revision.log);
DBFile log = download_db.Upload (tmpfile, ".log", false, null);
revision.log_file_id = log.id;
revision.log = null;
} finally {
try {
if (File.Exists (tmpfile))
File.Delete (tmpfile);
} catch (Exception ex) {
log.ErrorFormat ("error deleting temp file: {0}", ex);
}
}
moved_bytes += length;
Manager.log.InfoFormat ("MoveFilesToFileSystem: Moved revision {0}'s log to db/filesystem ({1} bytes, {2} total bytes moved)", revision.id, length, moved_bytes);
}
revision.log = null;
}
revision.Save (download_db);
} while (reader.Read ());
}
}
}
}
}
Manager.log.Info ("MoveFilesToFileSystem: [Done]");
return 0;
}
private void DownloadFile (DB db, DBFile file)
{
// access must be verified before calling this method (no verification is done here)
if (file.file_id == null) {
DownloadMd5 (file.md5);
} else {
using (Stream str = db.Download (file)) {
DownloadStream (str, file.compressed_mime);
}
}
}
public DBFile Upload (string md5, string path_to_contents, string filename, string extension, bool hidden, string compressed_mime)
{
IDbTransaction transaction = null;
LargeObjectManager manager;
LargeObject obj;
int? oid;
DBFile result;
long filesize;
string gzFilename = null;
try {
filesize = new FileInfo (path_to_contents).Length;
if (filesize > 1024 * 1024 * 500)
throw new Exception ("Max file size is 500 MB");
using (IDbCommand cmd = CreateCommand ()) {
cmd.CommandText = "SELECT * FROM File WHERE md5 = '" + md5 + "'";
using (IDataReader reader = cmd.ExecuteReader ()) {
if (reader.Read ())
return new DBFile (reader);
}
}
//Console.WriteLine ("Uploading {0} {1} with compressed mime: {2}", Filename, md5, compressed_mime);
// The file is not in the database
// Note: there is a race condition here,
// the same file might get added to the db before we do it here.
// not quite sure how to deal with that except retrying the above if the insert below fails.
if (compressed_mime == MimeTypes.GZ) {
gzFilename = path_to_contents;
} else {
gzFilename = FileUtilities.GZCompress (path_to_contents);
compressed_mime = MimeTypes.GZ;
}
transaction = BeginTransaction ();
if (Configuration.StoreFilesInDB) {
manager = new LargeObjectManager (this.dbcon);
oid = manager.Create (LargeObjectManager.READWRITE);
obj = manager.Open (oid.Value, LargeObjectManager.READWRITE);
using (FileStream st = new FileStream (gzFilename, FileMode.Open, FileAccess.Read, FileShare.Read)) {
byte [] buffer = new byte [1024];
int read = -1;
while (read != 0) {
read = st.Read (buffer, 0, buffer.Length);
obj.Write (buffer, 0, read);
}
}
obj.Close ();
} else {
oid = null;
string fn = FileUtilities.CreateFilename (md5, true, true);
File.Copy (gzFilename, fn, true);
log.DebugFormat ("Saved file to: {0}", fn);
}
result = new DBFile ();
result.file_id = oid;
result.filename = Path.GetFileName (filename);
result.md5 = md5;
result.size = (int) filesize;
result.hidden = hidden;
switch (extension.ToLower ()) {
case ".log":
case ".stdout":
case ".stderr":
result.mime = MimeTypes.LOG;
break;
case ".txt":
result.mime = MimeTypes.TXT;
break;
case ".htm":
case ".html":
result.mime = MimeTypes.HTML;
break;
case ".png":
result.mime = MimeTypes.PNG;
break;
case ".jpg":
result.mime = MimeTypes.JPG;
break;
case ".bmp":
result.mime = MimeTypes.BMP;
break;
case ".tar":
result.mime = MimeTypes.TAR;
break;
case ".bz":
result.mime = MimeTypes.BZ;
break;
case ".bz2":
result.mime = MimeTypes.BZ2;
break;
case ".zip":
result.mime = MimeTypes.ZIP; ;
break;
case ".gz":
result.mime = MimeTypes.GZ;
break;
case ".xpi":
result.mime = MimeTypes.XPI;
break;
case ".crx":
result.mime = MimeTypes.CRX;
break;
default:
result.mime = MimeTypes.OCTET_STREAM;
break;
}
result.compressed_mime = compressed_mime;
result.Save (this);
transaction.Commit ();
transaction = null;
return result;
} finally {
FileUtilities.TryDeleteFile (gzFilename);
if (transaction != null)
transaction.Rollback ();
}
}
public Stream Download (DBFile file)
{
if (file.file_id.HasValue) {
return new DBFileStream (file, this);
} else {
return new System.IO.Compression.GZipStream (new FileStream (DBFile_Extensions.GetFullPath (file.md5), FileMode.Open, FileAccess.Read), System.IO.Compression.CompressionMode.Decompress);
}
}
public DBFileStream (DBFile file, DB db)
{
try {
transaction = db.dbcon.BeginTransaction ();
obj = db.Manager.Open (file.file_id.Value);
} catch {
if (transaction != null) {
transaction.Rollback ();
transaction = null;
}
}
}
public static void RemoveFile (this DBWork me, DB db, DBFile file)
{
using (IDbCommand cmd = db.CreateCommand ()) {
cmd.CommandText = "DELETE FROM WorkFile WHERE work_id = @work_id AND file_id = @file_id;";
DB.CreateParameter (cmd, "work_id", me.id);
DB.CreateParameter (cmd, "file_id", file.id);
cmd.ExecuteNonQuery ();
}
}
public static DBFile GetFile (DB db, int work_id, string filename, bool throw_on_multiple)
{
DBFile result = null;
using (IDbCommand cmd = db.CreateCommand ()) {
cmd.CommandText =
@"
SELECT File.id, File.md5, File.file_id, File.mime, File.compressed_mime, File.size, File.file_id, File.hidden OR WorkFile.hidden AS hidden,
CASE WHEN WorkFile.filename = '' THEN File.filename ELSE WorkFile.filename END
FROM WorkFile
INNER JOIN File ON WorkFile.file_id = File.id
WHERE WorkFile.work_id = @work_id AND (WorkFile.filename = @filename OR (WorkFile.filename = '' AND File.filename = @filename));
";
DB.CreateParameter (cmd, "work_id", work_id);
DB.CreateParameter (cmd, "filename", filename);
using (IDataReader reader = cmd.ExecuteReader ()) {
if (!reader.Read ())
return null;
result = new DBFile (reader);
if (throw_on_multiple && reader.Read ())
throw new Exception (string.Format ("Found more than one file in work with id {0} whose filename is '{1}'", work_id, filename));
}
}
return result;
}
