Example: DB-based file-system extension

Example

The source-code below implements a JSS file-system extension, where files and folders are stored in a SQL Server CE database.

Installing and using the extension

To try it, create a new JSS file-system extension, copy the code into it and give it a name such as “DB Folder”. Each folder of this type which is created, will create a database in a subfolder of the user’s home folder called “fileDBs”. The name of the database will be that of the folder that it stores files for, so each DB Folder must have a name that’s unique to each user.

Source code

function getFileInfos(path, pattern, session, node) {
	var fileInfos = [];
	getDB(session, node).readTransaction(function(transaction) {
		var res = transaction.executeSql("SELECT name, length, modifiedTime, createdTime FROM Files");
		for (var i=0; i < res.rows.length; i ++) {
			var row = res.rows[i];
			fileInfos.push({
				name: row.name,
				isFolder: false,
				length: row.length,
				modifiedTime: row.modifiedTime,
				createdTime: row.createdTime
			});
		}
	});
	return fileInfos;
}

function onWriteBegin() {
	return { type: 'binary' };
}

function write(path, data, length, session, node) {
	getDB(session, node).transaction(function(tx) {
		tx.executeSql("INSERT INTO Files(name, data, length, modifiedTime, createdTime) VALUES (?, ?, ?, ?, ?)", 
			[path, data, length, new Date(), new Date()]);
	});
}

function read(path, session, node) {
	var data = null;
	getDB(session, node).readTransaction(function(transaction) {
		var res = transaction.executeSql("SELECT * FROM Files WHERE name=?", [path]);
		if (res.rows.length > 0)
			data = res.rows[0].data;
	});
	return { binary: data };
}

function deleteFile(path, session, node) {
	getDB(session, node).transaction(function(tx) {
		tx.executeSql("DELETE FROM Files WHERE name=?", [path]);
	});
}

function moveFile(fromPath, toPath, session, fromNode, toNode) {
	var success = false;
	getDB(fromNode).transaction(function(tx) {
		var res = tx.executeSql("UPDATE Files SET name=? WHERE name=?", [toPath, fromPath]);
		success = res.rowsAffected > 0;
	});
	return success;
}


var databases = {};
function getDB(session, node) {
	var db = databases[node.path];
	if (!db) {
		var dbFolder   = system.getFile(session.fullHomePath + "/fileDBs");
		if (!dbFolder.exists())
			dbFolder.createFolder();
		var folderName = node.path.substring(node.path.lastIndexOf('/') + 1);
		db             = databases[node.path] = system.openDatabaseSync(dbFolder.fullPath + "/" + folderName + ".sdf");
	}
   
   // create the Files table if it doesn't already exist
   if (!tableExists(db, "Files"))
	  db.transaction(function(tx) {
		  tx.executeSql("CREATE TABLE [Files] ([name] NVARCHAR(1024), [data] IMAGE, [length] INT, [modifiedTime] DATETIME, [createdTime] DATETIME)");
	  });
   return db;
}

function tableExists(db, tableName) {
   var rowCount = 0;
   db.readTransaction(function(tx) {
	  var res = tx.executeSql("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ?", [tableName]);
	  rowCount = res.rows.length;
   });
   return rowCount > 0;
}

Description of source code

The code consists of eight functions. The first six, i.e. getFileInfos, onWriteBegin, write, read, deleteFile and moveFile, are called by CompleteFTP when directory listings, uploads, downloads, deletions and moves are required. The other two: getDB and tableExists, are utility functions used by the first six functions.

Main methods

Read file information from database.

function getFileInfos(path, pattern, session, node) {
	var fileInfos = [];
	getDB(session, node).readTransaction(function(transaction) {
		var res = transaction.executeSql("SELECT name, length, modifiedTime, createdTime FROM Files");
		for (var i=0; i < res.rows.length; i++) {
			var row = res.rows[i];
			fileInfos.push({
				name: row.name,
				isFoldesr: false,
				length: row.length,
				modifiedTime: row.modifiedTime,
				createdTime: row.createdTime
			});
		}
	});
	return fileInfos;
}

Write file to database.

function write(path, data, length, session, node) {
	getDB(session, node).transaction(function(tx) {
		tx.executeSql("INSERT INTO Files(name, data, length, modifiedTime, createdTime) VALUES (?, ?, ?, ?, ?)", 
			[path, data, length, new Date(), new Date()]);
	});
}

Read file from database and return data as binary.

function read(path, session, node) {
	var data = null;
	getDB(session, node).readTransaction(function(transaction) {
		var res = transaction.executeSql("SELECT * FROM Files WHERE name=?", [path]);
		if (res.rows.length > 0)
			data = res.rows[0].data;
	});
	return { binary: data };
}

Delete file out of database.

function deleteFile(path, session, node) {
	getDB(session, node).transaction(function(tx) {
		tx.executeSql("DELETE FROM Files WHERE name=?", [path]);
	});
}

Move file.

function moveFile(fromPath, toPath, session, fromNode, toNode) {
	var success = false;
	getDB(fromNode).transaction(function(tx) {
		var res = tx.executeSql("UPDATE Files SET name=? WHERE name=?", [toPath, fromPath]);
		success = res.rowsAffected > 0;
	});
	return success;
}
Database methods

Returns database object for the given node.

function getDB(session, node) {
	var db = databases[node.path];
	if (!db) {
		var dbFolder   = system.getFile(session.fullHomePath + "/fileDBs");
		if (!dbFolder.exists())
			dbFolder.createFolder();
		var folderName = node.path.substring(node.path.lastIndexOf('/') + 1);
		db             = databases[node.path] = system.openDatabaseSync(dbFolder.fullPath + "/" + folderName + ".sdf");
	}
   
   // create the Files table if it doesn't already exist
   if (!tableExists(db, "Files"))
	  db.transaction(function(tx) {
		  tx.executeSql("CREATE TABLE [Files] ([name] NVARCHAR(1024), [data] IMAGE, [length] INT, [modifiedTime] DATETIME, [createdTime] DATETIME)");
	  });
   return db;
}

Returns true, if a table with the given name exists in the database.

function tableExists(db, tableName) {
   var rowCount = 0;
   db.readTransaction(function(tx) {
	  var res = tx.executeSql("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ?", [tableName]);
	  rowCount = res.rows.length;
   });
   return rowCount > 0;
}

Common methods

The following JSS API methods are used in the source-code. Their documentation may also be found in the JSS API Reference.

1. readTransaction ( [callback] )

Run the read transaction contained in the given callback function. A SQLTransactionSync object is passed as an argument to the callback. This may be used to execute queries. The transaction is committed if the callback returns normally, and rolled back if it throws an exception.

Parameters:
2. transaction ( [callback] )

Run the non-read transaction contained in the given callback function. A SQLTransactionSync object is passed as an argument to the callback. This may be used to execute queries. The transaction is committed if the callback returns normally and rolled back if it throws an exception.

Parameters:
3. executeSql ( sqlStatement arguments ) SQLResultSet

Execute the given SQL and return the result as an SQLResultSet object.

Parameters:
Returns:
SQLResultSet
4. getFile ( path ) File

Returns a File object for the given path.
The File object may or may not represent an existing file or directory.

Parameters:
Returns:
File