% DB_QUERY  Import data from a database.
%   DB_QUERY  lists the available databases along with their respective
%   tables and columns.  Note, this may take a minute to list them all.
%
%   INFO = DB_QUERY  returns the available databases along with their
%   respective tables and columns in an organized cell array.
%
%   DATA = DB_QUERY(DATABASE,TABLE,COLUMNS,LOGIN,PASSWORD)
%   Returns the columns specified in COLUMNS where COLUMNS is a string or
%   cell array of strings.  The columns are returned from the TABLE in the
%   specified DATABASE using the LOGIN and PASSWORD to connect.  DATABASE,
%   TABLE, LOGIN, and PASSWORD are all strings.  If there is no login or
%   password for a database simply input double apostrophes (empty string).
%
%   DATA = DB_QUERY('G21-2005-Raw','MainG21','Temperature','','','type','struct');
%   Returns the Temperature column from the MainG21 table in the
%   G21-2005-Raw database given no login and password (both parameters are
%   empty strings).  The returned data is in a structure with the field
%   names based on the provided column names.  The available return data
%   types are STRUCT, NUMERIC, and CELL.  The default return type is
%   NUMERIC.
%
%   [x1 x2 x3] = DB_QUERY(DATABASE,TABLE,{'Column1,'Column2','Column3'},
%   LOGIN,PASSWORD)
%   DB_QUERY can return either one array or the number of arrays equal to
%   the number of columns.  If a structure output is specified it can only
%   return a single structure.  
%
%   DATA =
%   DB_QUERY('G21-2005-Raw','ECCO2G21',{'u_star','Temperature'},'','','type
%   ','cell','condition','DDOY','>=',100,'AND','condition','air_pressure','
%   >',95)
%   The preceeding example returns the values from the u_star and
%   Temperature columns in the ECCO2G21 table in the G21-2005-Raw database
%   where the DDOY is greater than or equal to 100 and the air_pressure is
%   greater than 95.  There is no upper limit to the number of conditions.
%   The only requirements are that all the conditions have a column name
%   followed by an operator and a value.  All but the last condition must
%   also have an AND or OR operator.  The available condition operators are
%   >, >=, <, <=, =, ==, ~=, and !=.  This example also returns the data in
%   a cell array with each cell being an entire column of data.
%
%   NOTE:  DB_QUERY will try to order the data by DDOY.  If DDOY is not
%   present DB_QUERY will not attempt any ordering.
%
%   Performance Benchmarks: (Results will vary with from computer to
%   computer)
%       2.29 seconds - 17,210 values copied from DB
%       2.50 seconds - 103,260 values copied from DB
%       30.75 seconds - 863,130 values copied from DB
%       35.84 seconds - 1,294,696 values copied from DB
%       41.14 seconds - 1,475,939 values copied from DB
%
%   Java exceptions should only occur if too much data is being copied from
%   the database.  Use the conditional statements to limit how much you are
%   importing.  If the Java exception does not mention OutOfMemoryError or
%   Java heap space email me the complete message and what you were doing
%   to get it.  smit1729@umn.edu
%
% Jeremiah Smith
% 10/6/06
% Last Edit: 10/30/06

function varargout = db_query(varargin)
Pref = setdbprefs;  % Get the old DBprefs
setdbprefs('DataReturnFormat','numeric')  % Set import type preference

%% Error Check - No values, display info
if nargin == 0
    DBs = getDBs;  % Get the MS Access Databases

    if nargout == 1
        Info = DBs;  % Initialize info cell array

        disp('This may take a few minutes.')
        for i=1:1:length(DBs)
            % Get Table Names
            conn = database(DBs{i},'','');  % Create a connection
            if ~isempty(conn.Message)
                error(conn.Message)
            end
            dbmeta = dmd(conn);
            Tables = tables(dbmeta,'cata');  % Get tables in database
            close(conn); % Close connection
            f = ismember(Tables(:,2),'SYSTEM TABLE');  % Find System Tables
            Tables(f,:) = [];  % Remove System Tables

            Info{i,2} = Tables(:,1);  % Add the tables to the Info cell array

            for j=1:1:length(Tables)
                % Load columns for the current database
                conn = database(DBs{i},'','');  % Create a connection
                if ~isempty(conn.Message)
                    error(conn.Message)
                end
                SQL = ['SELECT * FROM "' Tables{j} '"'];
                curs = exec(conn,SQL);  % Execute query
                curs = fetch(curs,1);  % Grab the data
                if ~isempty(curs.Message)
                    close(conn);  % Close the connection
                    error(curs.Message)
                    return
                end
                colnames = columnnames(curs);
                close(conn);  % Close connection
                eval(['colnames = {' colnames '};'])  % Reformat data
                colnames = colnames';

                Info{i,2}{j,2} = colnames;
            end
        end

        varargout{1} = Info;
    elseif nargout == 0 && nargin == 0
        for i=1:1:length(DBs)
            disp(DBs{i})

            % Get Table Names
            conn = database(DBs{i},'','');  % Create a connection
            if ~isempty(conn.Message)
                error(conn.Message)
            end
            dbmeta = dmd(conn);
            Tables = tables(dbmeta,'cata');  % Get tables in database
            close(conn); % Close connection
            f = ismember(Tables(:,2),'SYSTEM TABLE');  % Find System Tables
            Tables(f,:) = [];  % Remove System Tables

            for j=1:1:size(Tables,1)
                % Load columns for the current database
                conn = database(DBs{i},'','');  % Create a connection
                if ~isempty(conn.Message)
                    error(conn.Message)
                end
                SQL = ['SELECT * FROM "' Tables{j,1} '"'];
                curs = exec(conn,SQL);  % Execute query
                curs = fetch(curs,1);  % Grab the data
                if ~isempty(curs.Message)
                    close(conn);  % Close the connection
                    error(curs.Message)
                    return
                end
                colnames = columnnames(curs);
                close(conn);  % Close connection
                eval(['colnames = {' colnames '};'])  % Reformat data
                colnames = colnames';

                disp(sprintf('\t%s',Tables{j}))
                for k=1:1:length(colnames)
                    disp(sprintf('\t\t%s',colnames{k}))
                end
            end
        end
    end
    
    return
    
end

%% Parse input arguments
% Database Check
if ~ischar(varargin{1})  % Type check
    error('Database must be a string.')
    return
end
DBs = getdatasources';  % Get list of databases
if max(strcmp(DBs,varargin{1})) ~= 1  % Ensure it exists
    error('Invalid Database.')
    return
end

% Table Check
if ~ischar(varargin{2})  % Type check
    error('Table must be a string.')
end
% Get Table Names
conn = database(varargin{1},varargin{4},varargin{5});  % Create a connection
if ~isempty(conn.Message)
    error(conn.Message,'SQL Error')
    close(conn);  % Close the connection
end
dbmeta = dmd(conn);
Tables = tables(dbmeta,'cata');  % Get tables in specified database
close(conn); % Close connection
if min(ismember(varargin{2},Tables(:,1))) == 0
    error(['Invalid tables: ' varargin{2}])
    return
end

% Column Check
%Type
if ~~ischar(varargin{3})
    % Good
elseif ~~iscell(varargin{3})
    for i=1:1:length(varargin{3})
        if ~ischar(varargin{3}{i})
            error('Columns must be strings.')
            return
        end
    end
else
    error('Column input must be a string or cell array of strings.')
    return
end
DDOY = col_check(varargin{1},varargin{2},varargin{3},varargin{4},varargin{5});

% 'Type' Check
t = []; % Initialize
for i=1:1:length(varargin)
    if ~~ischar(varargin{i})
        if ~~strcmpi(varargin{i},'type')
            t = i;
            break
        end
    end
end
if isempty(t) == 1
    type = 'numeric';
else
    if t < length(varargin)
        if ~isempty(intersect(varargin{t+1},{'struct','numeric','cell'}))
            type = varargin{t+1};
        else
            error('Invalid entry for ''type'' parameter.  Must be ''struct'', ''numeric'', or ''cell''.')
        end
    else
        error('Must have a value after ''type'' parameter.')
    end
end
% Output check based on 'type' and number of columns
if ~~strcmp(type,'struct')
    if nargout > 1
        error('Too many output arguments for structure output.')
    end
else
    if ~~ischar(varargin{3})
        if nargout ~= 1
            error('Too many output arguments.')
        end
    elseif ~~iscell(varargin{3})
        if (nargout > 1 && nargout < length(varargin{3})) || nargout > length(varargin{3})
            error('Too many output arguments.')
        end
    end
end

% Conditions Check
conditions = [];  % Initialize
% Condition Locations
for i=1:1:length(varargin)
    if ~~ischar(varargin{i})
        if ~~strcmpi(varargin{i},'condition')
            conditions = [conditions;i];
        end
    end
end
if ~~isempty(conditions)
    conditions = 0;
end
if length(varargin) < max(conditions)+3
    error('Missing condition parameters.')
    return
end
if conditions ~= 0
    col_check(varargin{1},varargin{2},varargin(conditions+1),varargin{4},varargin{5});  % Check the condition columns
    
    % Operator Check
    if ~isempty(find(ismember(varargin(conditions+2),{'>','>=','<','<=','==','=','~=','!='}) == 0))
        error('Invalid condition operator.  Only >, >=, <, <=, =, ==, ~=, and != are allowed.')
        return
    end
    % Operator Correct
    [temp1 e temp2] = intersect(varargin(conditions+2),'=');
    if ~isempty(e)
        varargin{conditions(e)+2} = '=';
    end
    [temp1 e temp2] = intersect(varargin(conditions+2),{'~=','!='});
    if ~isempty(e)
        varargin{conditions(e)+2} = '<>';
    end
    if ~isempty(find(ismember(lower(varargin(conditions(1:end-1)+4)),{'and','or'}) == 0))
        error('Invalid multi-condition operator.  Only AND and OR allowed.')
        return
    end
end

%% Form the SQL Statement
if ~~ischar(varargin{3})
    SQL = ['SELECT "' varargin{3} '" FROM "' varargin{2} '"'];
else
    Columns = [];  % Initialize
    for i=1:1:length(varargin{3})
        Columns = [Columns ',"' varargin{3}{i} '"'];
    end

    SQL = ['SELECT ' Columns(2:end) ' FROM "' varargin{2} '"'];
end
if conditions ~= 0
    SQL = [SQL ' WHERE'];
    for i=1:1:size(conditions,1)
        if ~~isnumeric(varargin{conditions(i)+3})
            SQL = [SQL ' "' varargin{conditions(i)+1} '"' varargin{conditions(i)+2} num2str(varargin{conditions(i)+3})];
        else
            SQL = [SQL ' "' varargin{conditions(i)+1} '"' varargin{conditions(i)+2} '''' varargin{conditions(i)+3} ''''];
        end
        
        % Add multi-condition operator
        if i ~= size(conditions,1)
            SQL = [SQL ' ' varargin{conditions(i)+4}];
        end
    end
end
% Correction to the SQL statement so it pulls the data in in the correct order
if DDOY == 1
    SQL = [SQL ' ORDER BY "DDOY"'];
end

%% Import the Data
conn = database(varargin{1},varargin{4},varargin{5});  % Create a connection
if ~isempty(conn.Message)
    close(conn);  % Close the connection
    error(conn.Message)
    return
end
curs = exec(conn,SQL);  % Execute query
curs = fetch(curs);  % Grab the data
if ~isempty(curs.Message)
    close(conn);  % Close the connection
    error(curs.Message)
    return
end
data = curs.Data;
close(conn);  % Close the connection

%% Format Data
switch type
    case 'numeric'
        if nargout == 1  % One return array
            varargout(1) = {data};
        else  % Multiple return arrays
            for i=1:1:nargout
                varargout(i) = {data(:,i)};
            end
        end
    case 'struct'
        if ~~ischar(varargin{3})
            data = struct(clean_name(varargin{3}),data);
        else
            D = struct(varargin{3}{1},data(:,1));
            if length(varargin{3}) >= 2
                for i=2:1:length(varargin{3})
                    D.(clean_name(varargin{3}{i})) = data(:,i);
                end
            end
            
            varargout(1) = {D};
        end
    case 'cell'
        if nargout == 1  % One return array
            D = [];
            for i=1:1:size(data,2)
                D{i} = data(:,i);
            end
            
            varargout(1) = {D};
        else  % Multiple return arrays
            for i=1:1:size(data,2)
                varargout(i) = {{data(:,i)}};
            end
        end
end

setdbprefs('DataReturnFormat',Pref.DataReturnFormat)  % Restore the previous return format

%% Column Check Function
function DDOY = col_check(DB,Table,Columns,Login,Password)

% Load columns for the current database
conn = database(DB,Login,Password);  % Create a connection
SQL = ['SELECT * FROM "' Table '"'];
curs = exec(conn,SQL);  % Execute query
curs = fetch(curs,1);  % Grab the data
if ~isempty(curs.Message)
    close(conn);  % Close the connection
    error(curs.Message)
    return
end
colnames = columnnames(curs);
close(conn);  % Close connection
eval(['colnames = {' colnames '};'])  % Reformat data
colnames = colnames';

% Find all columns which do not exist
f = find(ismember(Columns,colnames) == 0);
if ~isempty(f)
    % Prepare erroneous columns for display
    bad_cols = [];
    for i=1:1:length(f)
        bad_cols = [bad_cols ',' Columns{f(i)}];
    end
    error(['Invalid Columns: ' bad_cols(2:end)])
    return
end

% Check for DDOY
if ~isempty(intersect(colnames,'DDOY'))
    DDOY = 1;  % DDOY is present in the table in question
else
    DDOY = 0;
end

%% Clean Name
% Structures cannot have field names with anything other than letters,
% numbers, and underscores.  This function removes or replaces the bad
% characters.
function name2 = clean_name(name)
N = double(name);  % Convert to numeric equivilant
f1 = find(N == 32 | N == 45);  % Find all spaces and dashes

f2 = find((N >= 48 & N <= 57) | (N >= 65 & N <= 90) | (N >= 97 & N <= 122) | N == 95 | N == 32 | N == 45);
name2 = name(f2);  % Name equals just the good characters
name2(f1) = '_';  % Replace all spaces and dashes with an underscore

if ~strcmp(name,name2)
    disp(sprintf('%s = %s',name,name2))
end

%% Get Databases
function x = getDBs()

DIR = getenv('WINDIR');  % Windows directory
File = 'ODBC.ini';  % ODBC file name

fid = fopen([DIR '\' File]);  % Open ODBC file
if fid == -1
    x = [];
    disp('Cannot open the ODBC file.')
    return
end
i = 2;  % Initialize
x = {fgetl(fid)};  % Initialize
while x{i-1} ~= -1  % Get all the lines in the ODBC file
    x{i} = fgetl(fid);
    i = i+1;
end
fclose(fid);  % Close ODBC file
x(end) = [];  % Remove the last value, it's -1
x = x';

f1 = strfind(x,'(*.mdb)');  % Find all lines with .mdb
f2 = [];  % Initialize
for i=1:1:length(x)  % Save the index of each line with .mdb
    if ~isempty(f1{i})
        f2 = [f2 i];
    end
end

x = x(f2); %  Save just the lines with .mdb
x(1) = [];  % Erase the first line, it's MS Access Database and can't be accessed

f = strfind(x,'=');  % Find the location of all the ='s
for i=1:1:length(x)  % Crop everything after and including the =
    x{i} = x{i}(1:f{i}-1);
end