For tracking file metadata

bucket.cfc

component {
    function init(required string dsn, required string bucketName){
        // set the default bucket and datasource
        variables.dsn = arguments.dsn;
        variables.q = new Query(datasource=variables.dsn);
        variables.storage = ExpandPath("./storage");
        
        // set the default bucket
        setBucket(arguments.bucketName);
    }

    public numeric function createBucket(required string name){
        var r = "";
        var bucketId="";
        var ret = {"success" = false};
        
        // get the bucket id
        q.clearParams();
        q.setSql("select buckets_seq.nextval from dual");
        try {
            bucketId = q.execute().getResult().NEXTVAL;
        } catch(java.lang.Exception e){
            ret["error"] = e.message;
            return ret;
        }
        
        // add it to the db
        q.setSql("insert into buckets(id,name) values(?,?)");
        q.addParam(value=bucketId,cfsqltype="integer");
        q.addParam(value=LCase(arguments.name),cfsqltype="varchar");
        try {
            r = q.execute();
        } catch(java.lang.Exception e){
            ret["error"] = e.message;
            return ret;
        }
        
        ret["success"] = true;
        ret["id"] = bucketId;
    }
    
    public function setBucket(string name, numeric id){
        // set the bucket to use for all further operations
        var r = "";
        
        if(StructKeyExists(arguments,"id")){
            // set the id and resume
            variables.id = arguments.id;
        } else {
            // determine the id and resume or create if one doesn't exist
            if( StructKeyExists(arguments,"name") && arguments.name != "" ){
                q.clearParams();
                q.setSql("select id from buckets where name = ?");
                q.addParam(value=LCase(arguments.name),cfsqltype="varchar");
                
                try {
                    r = q.execute();
                    if( r.getPrefix().RECORDCOUNT == 1 ){
                        // get the info
                        variables.id = r.getResult().ID;
                    } else {
                        // it doesn't exist, so just go ahead and create it
                        local.bucketId = "";
                        
                        // get the bucket id
                        q.clearParams();
                        q.setSql("select buckets_seq.nextval from dual");
                        try {
                            bucketId = q.execute().getResult().NEXTVAL;
                        } catch(java.lang.Exception e){
                            throw(type="java.lang.Exception",message="Unable to retrieve a bucket id: #e.message#");
                        }
                        
                        // add it to the db
                        q.setSql("insert into buckets(id,name) values(?,?)");
                        q.addParam(value=bucketId,cfsqltype="integer");
                        q.addParam(value=LCase(arguments.name),cfsqltype="varchar");
                        try {
                            r = q.execute();
                            
                            // if there's no problem here we can set the id
                            variables.id = bucketId;
                        } catch(java.lang.Exception e){
                            throw(type="java.lang.Exception",message="Unable to add bucket: #e.message#");
                        }
                    }
                } catch(java.lang.Exception e){
                    // some sort of error exists
                }
            } else {
                throw(type="java.lang.Exception",message="You cannot specificy an empty bucket name.");
            }
        }
        
        // if we get to here, awesome!
        return true;
    }
    
    public function getBucket(required string name){
        q.clearParams();
        
    }
    
    public boolean function dropBucket(required string name){
        q.clearParams();
        
        // db will have a cascade drop to the items table
        // so we just need to drop the dependent files
        q.setSql("select");
    }
    
    public boolean function rename(required string oldName, required string newName){
        var ret = {"success" = false };
        q.clearParams();
        q.setSql("update buckets set name = :newName where name = :oldName");
        q.addParam(name="newName",value=arguments.newName,cfsqltype="varchar");
        q.addParam(name="oldName",value=arguments.oldName,cfsqltype="varchar");
        
        try {
            q.execute();
        } catch(java.lang.Exception e){
            ret["error"] = e.message;
        }
        
        ret["success"] = true;
    }
    
    public boolean function add(any files){
        if( isArray(arguments.files) ){
            // insert multiple items
            local.ret = ArrayNew(1);
            
            for(i=1; i<= arrayLen(arguments.files); i++){
                ArrayAppend(ret,createItem(arguments.files[i]));
            }
            return ret;
            //
        } else {
            // perform for a single item
            return createItem(arguments.files);
        }
        
    }
    
    function getItem(required any id, required includeFiles=false){
        var ret = [];
        var itm = "";
        var r = "";
        var isSingle = false;
        
        // retrieve multiple items or a single item
        if( !isArray(arguments.id) ){
            arguments.id = [ arguments.id ];
            isSingle = true; // this will be used later to determine how to return
        }
        q.clearParams();
        q.setSql("select id, bucket_id, filename, mime, filesize, created_date, accessed_date, display_count from bucket_files where id in(?)");
        q.addParam(value=ArrayToList(arguments.id),cfsqltype="varchar",list=true);
        
        // open the file
        try {
            // retrieve from db
            r = q.execute();
            
            // since we are returning potentially many results, we should check to see
            // that we actually got back the expected amount--if any fail, this whole op fails!
            if(r.getPrefix().RECORDCOUNT != ArrayLen(arguments.id)) throw(type="java.lang.Exception",message="Invalid items specified!");
            
            // get the results ready to process
            r = r.getResult();
            
            for(i=1; i<= ArrayLen(arguments.id); i++){
                itm = new item();
                
                // setup our item
                itm.setId(r.ID[i]);
                itm.setBucket(r.BUCKET_ID[i]);
                itm.setFilename(r.FILENAME[i]);
                itm.setMime(r.MIME[i]);
                itm.setFilesize(r.FILESIZE[i]);
                itm.setCreated(r.CREATED_DATE[i]);
                itm.setAccessed(r.ACCESSED_DATE[i]);
                itm.setDisplayed(r.DISPLAY_COUNT[i]);
                
                // should we grab the content too?
                if( includeFiles ){
                    itm.setContent(FileReadBinary("#storage#/#itm.getId()#"));
                }
                
                // and append to our return
                ArrayAppend(ret,itm);
            }
   
        } catch(java.lang.Exception e){
            throw(type="java.lang.Exception",message="Unable to retrieve item (#ArrayToList(arguments.id)#): #e.message#");
        }
        
        // and grab the content (decode from 
        if( isSingle ){
            return ret[1];
        } else {
            return ret;
        }
    }
    
    public boolean function dropItem(required string id){
        var ret = {"success" = false };
        
        q.clearParams();
        q.setSql("delete from bucket_files where id = ?");
        q.addParam(value=arguments.id,cfsqltype="varchar");
        
        try {
            q.execute();
        } catch(java.lang.Exception e){
            // if we encounter an error here then we cannot save to the db
            // so just return a failure
            ret["error"] = e.message;
            return ret;
        }
        
        try {
            FileDelete("#storage#/#arguments.id#");
        } catch(java.lang.Exception e){
            // this point just means that the file was missing or unable to be deleted
            // so we can ignore this, but we will need to run maintenance on the storage
            // area to ensure that we don't have a bunch of garbage in the storage area
        }
        
        ret["success"] = true;
        ret["id"] = arguments.id;
    }
    
    private function createItem(any f){
        var itemId = createUUID();
        var ret = {"success" = false };
        var itm = new item();
        
        // set the parameters for the item
        itm.setId(itemId);
        itm.setBucket(variables.id);
        itm.setFilename(arguments.f.FILENAME);
        itm.setMime(getPageContext().getServletContext().getMimeType(arguments.f.FILENAME));
        itm.setFilesize(arrayLen(arguments.f.CONTENT));
        itm.setCreated(now());
        itm.setAccessed(itm.getCreated());
        itm.setDisplayed(1);
        
        // flush this just incase something is there already
        q.clearParams();
        
        q.setSql("insert into bucket_files(id, bucket_id, filename, mime, filesize, created_date, accessed_date, display_count) values(:id, :bucket_id, :filename, :mime, :filzesize, :created_date, :accessed_date, :display_count)");
        q.addParam(name="id",value=itm.getId(),cfsqltype="varchar");
        q.addParam(name="bucket_id",value=itm.getBucket(),cfsqltype="varchar");
        q.addParam(name="filename",value=itm.getFilename(),cfsqltype="varchar");
        q.addParam(name="mime",value=itm.getMime(),cfsqltype="varchar");
        q.addParam(name="filesize",value=itm.getFilesize(),cfsqltype="varchar");
        q.addParam(name="created_date",value=itm.getCreated(),cfsqltype="timestamp");
        q.addParam(name="accessed_date",value=itm.getAccessed(),cfsqltype="timestamp");
        q.addParam(name="display_count",value=itm.getDisplayed(),cfsqltype="integer");
        
        try{
            // persist to the db
            q.execute().getPrefix();
        } catch(java.lang.Exception e){
            // if we encounter an error here then we cannot save to the db
            // so just return a failure
            ret["error"] = e.message;
            return ret;
        }
        
        try{
            // and write to the filesystem
            FileWrite("#storage#/#itemId#",f.CONTENT);
        } catch(java.lang.Exception e){
            // we cannot save the file, so remove the db entry
            q.clearParams();
            q.setSql("delete from bucket_files where id = ?");
            q.addParam(value=itemId, cfsqltype="varchar");
            q.execute();
            
            // graceful return
            ret["error"] = e.message;
            return ret;
        }
        
        // if we get here then return the created id
        ret["success"] = true;
        ret["item"] = itm;
        
        return ret;
    }
}

item.cfc

/**
* @accessors
*/
component {
    property name="id";
    property name="bucket";
    property name="filename";
    property name="mime";
    property name="filesize";
    property name="created";
    property name="accessed";
    property name="displayed";
    property name="content";
}

test.cfm

<cfscript>
    b = new bucket("myDSN","weezer");
    //f = FileReadBinary(ExpandPath("./235844.pdf"));
    WriteDump(b.getItem(['8ae8e1962da401a5012da62eacb20002','8ae8e1962d9efc32012d9f8f05240072']));
    //WriteDump(b.setBucket("weeZer1"));
</cfscript>