% DBMANAGER  Database manager.
%
%   For help, run DBMANAGER and press F1.
%
% Author: Jeremiah Smith
% Started: 7/31/07
% Last Edit: 6/2/08

function dbmanager()

w = 28;  % Listbox width
h = 1.768;  % Button height
vs = .35;  % Vertical space
hs = 2;  % Horizontal space

prefs('GUI')
Pos = gui_position(0,4*w+5*hs,10*h+4*vs-1);

%Main Window
gui.main = figure('HandleVisibility','off',...
    'IntegerHandle','off',...
    'Menubar','none',...
    'NumberTitle','off',...
    'Name','Database Manager',...
    'Color',BGColor,...
    'Units','characters',...
    'Userdata',[],...
    'Position',Pos,...
    'CloseRequestFcn',{@close_func});

gui.db_text = uicontrol(gui.main,'Style','text','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Databases','Position',[hs 9*h+2*vs w h-.5]);
gui.table_text = uicontrol(gui.main,'Style','text','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Tables','Position',[2*hs+w 9*h+2*vs w h-.5]);
gui.col_text = uicontrol(gui.main,'Style','text','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Columns','Position',[3*hs+2*w 9*h+2*vs w h-.5]);
gui.col_text = uicontrol(gui.main,'Style','text','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Workspace Data','Position',[4*hs+3*w 9*h+2*vs w h-.5]);

gui.db_listbox = uicontrol(gui.main,'Style','listbox','Units','characters','BackgroundColor','white','FontSize',10,'String','Datase List','Position',[hs h+2*vs w 8*h]);
gui.db_add = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Add','Position',[hs vs w/2 h]);
gui.db_remove = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Remove','Position',[2*hs+w/2-hs vs w/2 h]);
gui.table_listbox = uicontrol(gui.main,'Style','listbox','Units','characters','BackgroundColor','white','FontSize',10,'String','No Database Selected','Position',[2*hs+w h+2*vs w 8*h]);
gui.table_add = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Add','Position',[2*hs+w vs w/2 h]);
gui.table_remove = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Remove','Position',[2*hs+w+w/2 vs w/2 h]);
gui.col_listbox = uicontrol(gui.main,'Style','listbox','Units','characters','Max',2,'BackgroundColor','white','FontSize',10,'String','No Table Selected','Position',[3*hs+2*w h+2*vs w 8*h]);
gui.col_add = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Add','Position',[3*hs+2*w vs w/2 h]);
gui.col_remove = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Remove','Position',[3*hs+2*w+w/2 vs w/2 h]);
gui.data_listbox = uicontrol(gui.main,'Style','listbox','Units','characters','BackgroundColor','white','FontSize',10,'String','','Position',[4*hs+3*w h+2*vs w 8*h]);
gui.data_refresh = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Refresh','Position',[4*hs+3*w vs w/2 h]);
gui.data_insert = uicontrol(gui.main,'Style','push','Units','characters','BackgroundColor',BGColor,'FontSize',10,'String','Insert','Position',[4*hs+3*w+w/2 vs w/2 h]);

% Initialization Functions
DB(gui)
refresh_data(gui.data_refresh,[],gui)

% Set Callbacks
set(gui.db_listbox,'Callback',{@Tables,gui})
set(gui.table_listbox,'Callback',{@Columns,gui,[]})
set(gui.db_add,'Callback',{@add_db,gui})
set(gui.table_add,'Callback',{@add_table,gui})
set(gui.col_add,'Callback',{@add_col_setup,gui})
set(gui.col_remove,'Callback',{@rm_cols,gui})
set(gui.table_remove,'Callback',{@rm_table,gui})
set(gui.db_remove,'Callback',{@rm_db,gui})
set(gui.data_refresh,'Callback',{@refresh_data,gui})
set(gui.data_insert,'Callback',{@insert_data,gui})
set(cell2mat(struct2cell(gui)),'KeyPressFcn',{@key_commands,gui})

set(cell2mat(struct2cell(gui)),'Units','normalized')

%% Preferences
function prefs(varargin)
% Import preferences into the caller function

if nargin == 0 || nargin == 1 && ~~strcmp(varargin{1},'GUI')
% Interface Background Color Possibilities
%   BGColor = [0 0 0];  % Black
%   BGColor = [1 1 1];  % White
%   BGColor = [1 0 0];  % Red
%   BGColor = [0 1 0];  % Green
%   BGColor = [0 0 1];  % Blue
%   BGColor = [1 0 1];  % Magenta
%   BGColor = [0 1 1];  % Cyan
%   BGColor = [1 1 0];  % Yellow
%   BGColor = [0 .5 .5];  % Turquoise
%   BGColor = [.6 .6 .6];  % Grey
%   BGColor = [1 .6 0];  % Blaze Orange
%   BGColor = [1 .6824 .2745];  % Light Orange
%   BGColor = [.2745 .5098 1];  % Light Blue
%   BGColor = [.5 0 1];  % Purple
%   BGColor = [1 .6 .6];  % Peach
%   BGColor = [.745 .8353 .9765];  % Microsoft Office Blue
BGColor = get(0,'DefaultUicontrolBackgroundcolor');  % Default Color
end

if nargin == 0 || nargin == 1 && ~~strcmp(varargin{1},'JDBC Source')
    %  Leave empty unless using the 
    J_file = '';  % JDBC sources file location, include the file name
end

if nargin == 0 || nargin == 1 && ~~strcmp(varargin{1},'JDBC')
    % JDBC source information.  Keep in mind that passwords stored here are
    % highly insecure.  This is a plain text file and can be very easily
    % read.
    
    % Example
    % JDBC = {'Database'  'Driver'    'URL'   'Login'     'Password';
    %     'my_data','com.mysql.jdbc.Driver','jdbc:mysql://127.0.0.1:3306/','my_login','Pa5sWerD'};
    
    JDBC = {'Database'  'Driver'    'URL'   'Login'     'Password';
        };
end

if nargin == 0 || nargin == 1 && ~~strcmp(varargin{1},'DB')
    % List of databases to exclude from the databases list
    ex = {'Excel Files','dBASE Files','MS Access Database'};
    
    % Meta Data suffixes
    metatrigger = 1;  % 1 = show meta data (slower), 0 = don't show meta data (faster)
    meta = {'general','_notes','_info','_units'};  % Meta data table suffixes, must be a cell array
end

% DO NOT EDIT ANYTHING BELOW THIS LINE
% Get list of variables
clear varargin
temp = whos;
list = [];
for i=1:1:length(temp)
    list{i} = temp(i).name;
end

% Save list to caller function's workspace
for i=1:1:length(temp)
    eval(['assignin(''caller'',''' list{i} ''',' list{i} ')'])
end

%% Refresh Data
function refresh_data(h,eventdata,gui)
% Refresh Data listbox
% h = handle of refresh button
% eventdata = unused
% gui = structure of handles

DataL = get(gui.data_listbox,'String');
if ~iscell(DataL)
    DataL = {DataL};
end
V = get(gui.data_listbox,'Value');
if ~isempty(DataL)
    Old = DataL(V);
else
    Old = '';
end
DataL = evalin('base','who');
f = ismember(DataL,Old);
f = find(f == 1);
if ~~isempty(f)
    f = 1;
end
set(gui.data_listbox,'String',DataL,'Value',f,'ListboxTop',1)

%% Insert Data
function insert_data(h,eventdata,gui)
% Insert data into the selected columns
% h = handle of insert button
% eventdata = unused
% gui = structure of handles

[DBx T Cols] = selected(gui);

if ~~isempty(DBx)
    errordlg('You must select a database.','Error')
    return
elseif ~~isempty(T)
    errordlg('You must select a table.','Error')
    return
elseif ~~isempty(Cols)
    errordlg('You must select a column.','Error')
    return
end

% Get Data
DataL = get(gui.data_listbox,'String');
if ~iscell(DataL)
    DataL = {DataL};
end
if ~~isempty(DataL)
    errordlg('No data to insert.','Error')
    return
end
V = get(gui.data_listbox,'Value');
DataL = DataL{V};
D = evalin('base',DataL);

% Attempt to insert data
tic
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
try
    switch DB_Type(gui)
        case 'ACCESS'
            e = insert2(DBx,Prefs.Login,Prefs.Pass,T,Cols,D);
        case 'MySQL'
            e = insert3(DBx,Prefs.Login,Prefs.Pass,T,Cols,D);
    end
catch
    time = toc/60;
    temp = lasterror;
    errordlg(temp.message,temp.identifier)
    return
end
time = toc/60;

switch e
    case 0
        msgbox(sprintf(['Data insert successful!\n\n' num2str(time) ' minutes']),'Successful')
    case 1
        errordlg('Invalid database.','Error')
    case 2
        errordlg('Database connection error.','Error')
    case 3
        errordlg('Data transfer error.','Error')
    case 4
        errordlg('Connection close error.','Error')
end

%% Populate DB Listbox
function DB(gui)
% Clean the database list of common database entries which cannot be
% accessed including 'Excel Files', 'dBASE Files', and 'MS Access Database'
prefs('DB')  % Load preferences
h = gui.db_listbox;

if exist('getdatasources') == 2 || exist('getdatasources') == 5
    list = getdatasources;
    if ~isnumeric(list)
        list = list';
    else
        list = {};
    end
else
    list = {};
end

% Add other sources listed in preferences
prefs('JDBC')
JDBC(1,:) = [];  % Remove the first row
JDBC_sources = JDBC(:,1);clear JDBC
prefs('JDBC Source')
if ~isempty(J_file)
    if exist(J_file,'file') == 2
        load(J_file)
        JDBC_sources = [JDBC_sources;srcs(:,1)];
    end
end
list = sort(unique([list;JDBC_sources]));

% Remove ODBC.INI if it was created
if ~~ispc
    Win = getenv('WINDIR');  % Get Windows directory
    CurDir = cd;  % Get Current Directory
    if ~strcmp(Win,CurDir)
        if exist('ODBC.ini','file') == 2
            delete('ODBC.INI')
        end
    end
end

f = ismember(list,ex);  % Logical array of Matching values
list(f) = [];  % Remove values that are also found in the exclusion list

if ~~ishandle(h)
    set(h,'String',sort(list))
end

%% Populate Tables Listbox
function Tables(h,eventdata,gui)
V = get(h,'Value'); % Get the values of the tables selected
S = get(h,'String');  % Get the list of databases
LoginInfo = get(h,'Userdata');
if ~~isempty(S)
    switch computer
        case 'PCWIN'
            winopen('C:\WINDOWS\system32\odbcad32.exe &')
        case 'PCWIN64'
            winopen('C:\WINDOWS\system32\odbcad32.exe &')
        otherwise
            confds % Open JDBC setup window
    end
    return
end
DBx = S{V};  % Selected DB

Prefs.Login = '';
Prefs.Pass = '';
err = 0;
while err == 0 || err == 1
    if err == 0
        conn = connect(DBx,Prefs.Login,Prefs.Pass);  % Connect to database
    elseif err == 1
        if ~isempty(LoginInfo)
            f = find(strcmp(LoginInfo(:,1),DBx) == 1);
        else
            f = [];
        end
        
        if ~isempty(f)
            Prefs.Login = LoginInfo{f,2};
            Prefs.Pass = LoginInfo{f,3};
        else
            [Prefs.Login Prefs.Pass] = logindlg('Title','Database Login');
            LoginInfo = [LoginInfo;{DBx Prefs.Login Prefs.Pass}];
            set(h,'Userdata',LoginInfo)  % Save login info
        end
        conn = connect(DBx,Prefs.Login,Prefs.Pass);  % Connect to database
    end
    err = db_error(conn.Message);  % Error check
    if err == -1
        return
    end
    
    if err == 2
        dbmeta = dmd(conn);
        try
            t = tables(dbmeta, 'cata');  % Get tables in database
        catch
            t = {};  % No tables
        end
        close(conn);
    end
end
if ~isempty(t)
    sys = ismember(t(:,2),'SYSTEM TABLE');  % Find the system tables in the DB
    t(sys,:) = [];  % Remove System Tables
    t = t(:,1);  % Remove the table type column;
end

set(gui.table_listbox,'String',t,'ListboxTop',1,'Value',1,'UserData',Prefs)
Columns(gui.table_listbox,[],gui,Prefs)

%% Populate Columns Listbox
function Columns(h,eventdata,gui,Prefs)
[DBx T Cols] = selected(gui);

if ~~isempty(Prefs)
    Prefs = get(gui.table_listbox,'UserData');
end
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end

conn = connect(DBx,Prefs.Login,Prefs.Pass);
SQL = ['SELECT * FROM `' T '`'];
curs = exec(conn,SQL);
if ~~isempty(curs.Message)
    curs = fetch(curs,1);
    colnames = columnnames(curs);    % Get column names from cursor
end
close(conn); % Close connection
if ~isempty(curs.Message)
    set(gui.col_listbox,'String','No Table Selected','Value',[],'ListboxTop',1)
    return
end
eval(['colnames = {' colnames '};']) % Reorganize column names

% Determine if DDOY exists
f = find(ismember(colnames,'DDOY'), 1);
if ~isempty(f)
    Prefs.DDOY = 1;
else
    Prefs.DDOY = 0;
end

set(gui.col_listbox,'String',colnames','Value',[],'ListboxTop',1)

%% Database Connection Error
function err = db_error(message)
if ~isempty(message)
    f = findstr(message,'password');
    if ~~isempty(f)
        errordlg(message,'Database Error')
        err = -1;
        return
    else
        err = 1;
    end
else
    err = 2;
end

%% Add Database
function add_db(h,eventdata,gui)
% Create a new database
% h = handle of add button
% eventdata = unused
% gui = structure of handles

if ~~strcmp(computer,'PCWIN')
    answer = questdlg('Select Database Type','DB Type','MySQL','MS Access','Cancel','Cancel');
    switch answer
        case 'MS Access'
            if exist('Template.mdb','file') ~= 2
                errordlg(sprintf('Template.mdb required to create a new database.\nThe file can be found at http://www.biometeorology.umn.edu/data_matlab.php.'),'File Not Found')
                return
            end
            
            file = inputdlg('Database Name','Database Name');
            if ~~isempty(findstr(file{1},'.mdb'))
                file = [file{1} '.mdb'];
            end
            DIR = uigetdir(cd,'Store new database at...');
            
            % Copy Template to new location
            [status,message,messageid] = copyfile('Template.mdb',[DIR '\' file],'f');
            if status == 0
                errordlg(message,messageid)
                return
            end
            
            % Add new database to ODBC list
            switch computer
                case 'PCWIN'
                    %         system('C:\WINDOWS\system32\odbcad32.exe');
                    %         ! C:\WINDOWS\system32\odbcad32.exe
                    winopen('C:\WINDOWS\system32\odbcad32.exe')
                case 'PCWIN64'
                    winopen('C:\WINDOWS\SysWOW64\odbcad32.exe')
                otherwise
            end
        case 'MySQL'
            DB_I = inputdlg({'Database Name','Driver','URL'},'DB Info',1,{'','com.mysql.jdbc.Driver','jdbc:mysql://134.84.160.137:3306/'});  % Prompt user for input
            if ~~isempty(DB_I)
                return
            end
            DBx = DB_I{1};
            
            [Login Pass] = logindlg('Title','DB Login');  % Get Login information
            
            conn = database('',Login,Pass,DB_I{2},DB_I{3});  % Connect to MySQL
            
            if ~isempty(conn.Message)
                errordlg(conn.Message,'Error')
                return
            end
            
            % Create the new database
            SQL = ['CREATE DATABASE `' DBx '`'];
            curs = exec(conn,SQL);
            close(conn);
            
            if ~isempty(curs.Message)
                errordlg(curs.Message,'Error')
                return
            end
            
            % Add the DB to the JDBC file
            JDBC = setdbprefs('JDBCDataSourceFile');  % Check for source
            if ~isempty(JDBC)
                if exist(JDBC,'file') == 2
                    load(JDBC)
                    srcs = [srcs;{lower(DBx) DB_I{2} DB_I{3}}];  % Add the source
                    save(JDBC,'srcs')  % Save the sources
                else
                    srcs = {lower(DBx) DB_I{2} DB_I{3}};
                    save('JDBC_temp.mat','srcs')  % Save the sources
                    setdbprefs('JDBCDataSourceFile','JDBC_temp.mat')
                end
            else
                srcs = {lower(DBx) DB_I{2} DB_I{3}};
                save('JDBC_temp.mat','srcs')  % Save the sources
                setdbprefs('JDBCDataSourceFile','JDBC_temp.mat')
            end
            
        otherwise
            return
    end
else
    
end

% Maintain Selection Order
V = get(gui.db_listbox,'Value');
VT = get(gui.db_listbox,'ListboxTop');
S = get(gui.db_listbox,'String');
S = S{V};
DB(gui);
f = ismember(get(gui.db_listbox,'String'),S);
f = find(f == 1);
set(gui.db_listbox,'Value',f,'ListboxTop',VT)

%% Add Table
function add_table(h,eventdata,gui)
% Add a table to the database selected.
% h = handle of add button
% eventdata = unused
% gui = structure of handles

[DBx T Col] = selected(gui);  % Get Selected Entries

Table = inputdlg('Table Name','Name');
if ~~isempty(Table)
    return
end
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
conn = connect(DBx,Prefs.Login,Prefs.Pass);
dbmeta = dmd(conn);  % Get Database MetaData
switch get(dbmeta,'DatabaseProductName')
    case 'ACCESS'
        curs = exec(conn,['CREATE TABLE `' Table{1} '`']);
    case 'MySQL'
        curs = exec(conn,['CREATE TABLE `' Table{1} '` (`junk` numeric(1,0)) Type=MyISAM']);
    otherwise
        curs = exec(conn,['CREATE TABLE `' Table{1} '`']);
end
close(conn);

if ~isempty(curs.Message)
    if ~strcmp(curs.Message,'Error:Commit/Rollback Problems')
        errordlg(curs.Message,'SQL Error')
        return
    else
        disp('Error:Commit/Rollback Problems')
    end
end

% Maintain Selection Order
V = get(gui.table_listbox,'Value');
VT = get(gui.table_listbox,'ListboxTop');
S = get(gui.table_listbox,'String');
if ~~isempty(S)
    S = '';
else
    S = S(V);
end
Col = get(gui.col_listbox,'String');
ColV = get(gui.col_listbox,'Value');
ColVT = get(gui.col_listbox,'ListboxTop');
Tables(gui.db_listbox,[],gui)
f = ismember(get(gui.table_listbox,'String'),S);
f = find(f == 1);
if ~~isempty(f)
    f = 1;
end
set(gui.table_listbox,'Value',f,'ListboxTop',VT)
set(gui.col_listbox,'Value',ColV,'ListboxTop',ColVT,'String',Col)

%% Add Column Setup
function add_col_setup(h,eventdata,gui)
% Get info for Add Column
% h = handle of add button
% eventdata = unused
% gui = structure of handles

w = 28;  % Listbox width
h = 1.768;  % Button height
vs = .35;  % Vertical space
hs = 2;  % Horizontal space

prefs('GUI')
Pos = [170 18 w+2*hs 5*vs+4*h+.5];

% Bring the window to focus if it alreayd exists
FData = get(gui.main,'UserData');
if ~isempty(FData)
    if ~~isstruct(FData)
        if ~~isfield(FData,'AddCol')
            if ~~ishandle(FData.AddCol)
                figure(FData.AddCol)
                return
            end
        end
    end
end

%Main Window
fig.main = figure('HandleVisibility','off',...
    'IntegerHandle','off',...
    'Menubar','none',...
    'NumberTitle','off',...
    'Name','Add Column',...
    'Tag','BDT',...
    'Color',BGColor,...
    'Units','characters',...
    'Userdata',[],...
    'Position',Pos,...
    'Visible','off');

FData.AddCol = fig.main;
set(gui.main,'UserData',FData)

Pos = gui_position(gui.main,w+2*hs,5*vs+4*h+.5);
set(fig.main,'Position',Pos,'Visible','on')

fig.type_text = uicontrol(fig.main,'Style','text','FontSize',10,'Units','characters','BackgroundColor',BGColor,'HorizontalAlignment','left','String','Type','Position',[hs 4*vs+3*h+h-.7 w h-.5]);

Type = DB_Type(gui);

% Allowable Column Data Types
switch Type
    case 'ACCESS'
        DTypes = {'numeric','memo','Real','smallint','integer','time','currency','counter','bit','longbinary'};
    case 'MySQL'
        DTypes = {'numeric(25,15)','varchar(150)'};
end

fig.type = uicontrol(fig.main,'Style','popup','FontSize',10,'Units','characters','BackgroundColor','white','String',DTypes,'Position',[hs 3*vs+2*h+h-.5 w h]);
fig.name_text = uicontrol(fig.main,'Style','text','FontSize',10,'BackgroundColor',BGColor,'HorizontalAlignment','left','Units','characters','String','Column Name','Position',[hs 2*vs+2*h w h-.5]);
fig.name = uicontrol(fig.main,'Style','edit','FontSize',10,'BackgroundColor','white','HorizontalAlignment','left','Units','characters','String','','Position',[hs 2*vs+h w h]);
fig.ok = uicontrol(fig.main,'Style','push','FontSize',10,'Units','characters','String','OK','Position',[hs vs w h],'UserData',gui);

set(fig.ok,'Callback',{@add_col,fig})
set(cell2mat(struct2cell(fig)),'Units','normalized')

%% Add Column
function add_col(h,eventdata,fig)
% Add Column to selected table
% h = OK button handle
% eventdata = unused
% fig = structure of handles

% Get Name and Type
Name = get(fig.name,'String');
if ~~isempty(Name)
    errordlg('A column name must be input first.','Error')
    return
end
gui = get(fig.ok,'UserData');  % Database Manager handles
Types = get(fig.type,'String');
V = get(fig.type,'Value');
Type = Types{V};

[DBx T Col] = selected(gui);  % Get Selected Entries
if ~~isempty(T)
    errordlg('You must first select a database.','Error')
    return
end

% Execute SQL Statement
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
switch DB_Type(gui)
    case 'ACCESS'
        SQL = ['ALTER TABLE `' T '` ADD `' Name '` ' Type];
        
        conn = connect(DBx,Prefs.Login,Prefs.Pass);
        curs = exec(conn,SQL);
        close(conn);
    case 'MySQL'
        SQL = ['ALTER TABLE `' DBx '`.`' T '` ADD (`' Name '` ' Type ')'];
        
        DB = DB_Info(DBx);  % Get DB info
        
        % Use Java to execute SQL statment due to Matlab bug
        import java.lang.Thread
        import java.lang.Class
        import java.sql.DriverManager
        current_thread = java.lang.Thread.currentThread();
        class_loader = current_thread.getContextClassLoader();
        class = java.lang.Class.forName(DB{2},true,class_loader);
        DB_URL = [DB{3} DBx];
        conn = java.sql.DriverManager.getConnection(DB_URL,Prefs.Login,Prefs.Pass);
        stmt = conn.createStatement();
        result = stmt.execute(SQL);
        stmt.close();
end

% Maintain Selection Order
V = get(gui.col_listbox,'Value');
VT = get(gui.col_listbox,'ListboxTop');
S = get(gui.col_listbox,'String');
if ~iscell(S)
    S = {S};
end
if ~~isempty(V)
    S = {};
else
    S = S{V};
end
Columns(gui.table_listbox,[],gui,get(gui.table_listbox,'UserData'))
f = ismember(get(gui.col_listbox,'String'),S);
f = find(f == 1);
set(gui.col_listbox,'Value',f,'ListboxTop',VT)

%% Remove Database
function rm_db(h,eventdata,gui)
% Remove selected database
% h = handle of remove button
% eventdata = unused
% gui = structure of handles

[DBx T Col] = selected(gui);  % Get Selected Entries

Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
switch DB_Type(gui)
    case 'ACCESS'
        conn = connect(DBx,Prefs.Login,Prefs.Pass);
        Information = get(conn);
        close(conn);
        loc = [Information.Catalog '.mdb'];  % Database location
        
        delete(loc)  % Delete the database
        if exist(loc,'file') == 2
            errordlg('Unable to delete the selected database.','Error')
            return
        end
        
        switch computer
            case 'PCWIN'
                winopen('C:\WINDOWS\system32\odbcad32.exe');  % Open ODBC setup window
            case 'PCWIN64'
                winopen('C:\WINDOWS\SyWOW64\odbcad32.exe');  % Open ODBC setup window
        end
    case 'MySQL'
        conn = connect(DBx,Prefs.Login,Prefs.Pass);  % Connect to the database
        dbmeta = dmd(conn);  % Get database meta data
        Type = get(dbmeta,'URL');  % Database connection type
        SQL = ['DROP DATABASE `' DBx '`'];
        curs = exec(conn,SQL);
        close(conn);
        if ~isempty(curs.Message)
            msgbox(curs.Message,'Error')
        end
        
        if ~isempty(findstr(Type,'odbc'))
            switch computer
                case 'PCWIN'
                    winopen('C:\WINDOWS\system32\odbcad32.exe');  % Open ODBC setup window
                case 'PCWIN64'
                    winopen('C:\WINDOWS\SyWOW64\odbcad32.exe');  % Open ODBC setup window
            end
        else
            prefs('JDBC Source')  % Location of JDBC source file saved in preferences
            L = 0;
            if exist(J_file,'file') == 2
                load(J_file)  % Load the list of sources
                L1 = size(srcs,1);  % Length
                srcs(ismember(srcs(:,1),DBx),:) = [];  % Remove the selected DB
                save(J_file,'srcs')  % Save the sources
                L = size(srcs,1) - L1;
                clear L1 srcs J_file
            end
            
            if L == 0
                J_file = setdbprefs('JDBCDataSourceFile');
                if exist(J_file,'file') == 2
                    load(J_file)  % Load the list of sources
                    L1 = size(srcs,1);  % Length
                    srcs(ismember(srcs(:,1),DBx),:) = [];  % Remove the selected DB
                    save(J_file,'srcs')  % Save the sources
                    L = size(srcs,1) - L1;
                    clear L1 srcs J_file
                end
            end
        end
end

DBs = get(gui.db_listbox,'String');
V = get(gui.db_listbox,'Value');
if ~iscell(DBs)
    DBs = {DBs};
end
DBs(V) = [];  % Remove database from the list
set(gui.db_listbox,'String',DBs,'Value',1,'ListboxTop',1)
set(gui.table_listbox,'String','No Database Selected')
set(gui.col_listbox,'String','No Table Selected','ListboxTop',1)

%% Remove Columns
function rm_cols(h,eventdata,gui)
% Remove selected columns
% h = handle of remove button
% eventdata = unused
% gui = structure of handles

[DBx T Col] = selected(gui);  % Get Selected Entries

if ~~isempty(T)
    errordlg('You must select a table.','Error')
    return
end

if ~~isempty(Col)
    errordlg('You must select a column.','Error')
    return
elseif ~~strcmp(Col{1},'No Columns')
    return
end

SQL = [];
for i=1:1:length(Col)
    SQL = [SQL '`' Col{i} '`,'];
end

SQL = ['ALTER TABLE `' T '` DROP COLUMN ' SQL(1:end-1)];

% Remove the Columns
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
switch DB_Type(gui)
    case 'ACCESS'
        conn = connect(DBx,Prefs.Login,Prefs.Pass);
        curs = exec(conn,SQL);
        close(conn);
        
        if ~isempty(curs.Message)
            if ~strcmp(curs.Message,'No ResultSet was produced')
                errordlg(curs.Message,'Error')
                return
            end
        end
    case 'MySQL'
        DB = DB_Info(DBx);  % Get DB info
        
        % Use Java to execute SQL statment due to Matlab bug
        import java.lang.Thread
        import java.lang.Class
        import java.sql.DriverManager
        current_thread = java.lang.Thread.currentThread();
        class_loader = current_thread.getContextClassLoader();
        class = java.lang.Class.forName(DB{2},true,class_loader);
        DB_URL = [DB{3} DBx];
        conn = java.sql.DriverManager.getConnection(DB_URL,Prefs.Login,Prefs.Pass);
        stmt = conn.createStatement();
        result = stmt.execute(SQL);
        stmt.close();
end

% Maintain Selection Order
V = get(gui.col_listbox,'Value');
VT = get(gui.col_listbox,'ListboxTop');
S = get(gui.col_listbox,'String');
if ~iscell(S)
    S = {S};
end
S = S{V};
Columns(gui.table_listbox,[],gui,[])
set(gui.col_listbox,'Value',[],'ListboxTop',1)

%% Remove Table
function rm_table(h,eventdata,gui)
% Remove the selected table
% h = Remove button handle
% eventdata = unused
% gui = structure of handles

[DBx T Col] = selected(gui);  % Get Selected Entries

if ~~isempty(T)
    errordlg('You must select a database.','Error')
    return
end

% Remove Table
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
conn = connect(DBx,Prefs.Login,Prefs.Pass);
SQL = ['DROP TABLE `' T '`'];
curs = exec(conn,SQL);
close(conn);
if ~isempty(curs.Message)
    if ~strcmp(curs.Message,'Error:Commit/Rollback Problems')
        errordlg(curs.Message,'Error')
        return
    else
        disp('Error:Commit/Rollback Problems')
    end
end

% Maintain Selection Order
V = get(gui.table_listbox,'Value');
VT = get(gui.table_listbox,'ListboxTop');
S = get(gui.table_listbox,'String');
S = S(V);
Col = get(gui.col_listbox,'String');
ColV = get(gui.col_listbox,'Value');
ColVT = get(gui.col_listbox,'ListboxTop');
Tables(gui.db_listbox,[],gui)
Columns(gui.table_listbox,[],gui,[])

%% GUI Position
function pos = gui_position(fig,width,height)
% Determine the position of the GUI to be created so that is centered on
% top of the previous window
% fig = handle of some child of the previous window
% width = width of current window (characters)
% height = height of current window (characters)
% pos = position of current window [x y]

Un = get(fig,'Units');
set(fig,'Units','characters')
if fig == 0
    set(0,'Units','characters')
    P = get(0,'ScreenSize');
    set(0,'Units','pixels')
else
    P = get(fig,'Position');  % Get the previous figure's position
end
set(fig,'Units',Un)
Un = get(0,'Units');
set(0,'Units','characters')
sc = get(0,'ScreenSize');  % Get the screen size
set(0,'Units',Un)
P(1) = P(1) + P(3)/2;  % Horizontal center
P(2) = P(2) + P(4)/2;  % Vertical center
P(3:4) = [];  % Remove dimensions, they aren't needed
pos(1) = P(1) - width/2;
pos(2) = P(2) - height/2;
pos(3) = width;
pos(4) = height;

% Check to ensure the window is in bounds
if pos(1)+width > sc(3)
    pos(1) = sc(3) - width;
end
if pos(2)+height > sc(4)
    pos(2) = sc(4) - height - 2;
end
if pos(1) < 0
    pos(1) = 0;
end
if pos(2) < 0
    pos(2) = 0;
end

%% Close Function
function close_func(h,eventdata)
% Close the add window

FData = get(h,'UserData');
if ~~isstruct(FData)
    if ~~isfield(FData,'AddCol')
        if ~~ishandle(FData.AddCol)
            delete(FData.AddCol)
        end
    end
    
    if ~~isfield(FData,'Help')
        if ~~ishandle(FData.Help)
            delete(FData.Help)
        end
    end
end

delete(h)

%% Help:  Listbox
function help_listbox(h,eventdata,m,l)
% Change the text listbox to the text corresponding to the selected menu entry
% m = handle of menu listbox
% l = handle of text listbox

Pos = get(l,'Position');  % Current Position
Un = get(l,'Units');  % Current Units
set(l,'Units','centimeters')
Pos2 = get(l,'Position');  % Get the position of the listbox in cm
Pos2(3) = Pos2(3)-.5;
set(l,'Position',Pos2)  % Set the listbox's size a bit smaller so the bottom scroll bar won't appear

V = get(m,'Value');  % Selected menu entry
Text = get(m,'Userdata');  % List of text for each menu entry
if ~iscell(Text{V})
    T = textwrap(l,{Text{V}});
else
    T = [];
    for i=1:1:length(Text{V})
        T = [T;textwrap(l,Text{V}(i))];
    end
end

set(l,'String',T,'Value',[],'Units',Un,'Position',Pos,'ListboxTop',1);

%% Key Commands
function key_commands(h,eventdata,gui)
% Switch yard for keyboard commands called from the BDT interface.
% h = handle of BDT figure
% eventdata = unused
% gui = structure of handles for BDT

if ~~isfield(gui,'filename_edit')
    if h == gui.filename_edit
        return
    end
end

Key = get(gui.main,'CurrentKey');
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end

switch Key
    case 'e'
        % Erase data in the selected table/columns
        
        [DBx T Col] = selected(gui);  % Get selected stuff
        
        SQL = ['DELETE * FROM `' T '`'];
        
        answer = questdlg(['Would you like to delete all the data from the columns in ' T '?'],'Delete...');
        if ~~strcmp(answer,'Yes')
            conn = connect(DBx,Prefs.Login,Prefs.Pass);
            curs = exec(conn,['DELETE * FROM `' T '`']);
            close(conn);
            
            if ~isempty(curs.Message)
                errordlg(['Error deleting all data in ' T '.'],'Error')
            end
        end
        
    case 'r'
        DB(gui);
        
    case 'f1'
        % Database Manager Help
        dbmanager_help(gui.main,[],gui)
        
    case 'f7'
        % Add a JDBC source list
        JDBC = inputdlg('File Location','JDBC Sources');  % Prompt the user for a location
        if ~~isempty(JDBC)
            return
        end
        JDBC = JDBC{1};
        if exist(JDBC,'file') == 2
            setdbprefs('JDBCDataSourceFile',JDBC)  % Set the source
            DB(gui)
        else
            errordlg('File does not exist.','Error')
        end
        
    case 'f8'
        % Open the JDBC Data Source Setup Window
        confds
    case 'f9'
        if ~~ispc
            % Open the ODBC Data Source Administrator window
            system('C:\WINDOWS\system32\odbcad32.exe');  % Open ODBC setup window
        end
    case 'f12'
        % Open the Database Manager, BDT, and other Matlab files website
        web('http://www.biometeorology.umn.edu/data_matlab.php','-browser')
end

%% Selected Info
function [DBx T Col] = selected(gui)
% Get the selected DB, Table, and Columns

% Get Database
DBs = get(gui.db_listbox,'String');
if ~iscell(DBs)
    DBs = {DBs};
end
if ~~strcmp(DBs,'No Databases')
    return
end
V = get(gui.db_listbox,'Value');
DBx = DBs{V};

% Get Table
Ts = get(gui.table_listbox,'String');
if ~iscell(Ts)
    Ts = {Ts};
end
V = get(gui.table_listbox,'Value');
if ~isempty(Ts)
    T = Ts{V};
    if ~~strcmp(T,'No Database Selected')
        T = '';
    end
else
    T = '';
end

% Get Columns
Cols = get(gui.col_listbox,'String');
if ~iscell(Cols)
    Cols = {Cols};
end
if ~~strcmp(Cols{1},'No Table Selected')
    Col = '';
else
    V = get(gui.col_listbox,'Value');
    if ~isempty(V)
        Col = Cols(V);
    else
        Col = '';
    end
end

%% Connect
% Function to decide between use of ODBC or JDBC databases.
function conn = connect(DB,Login,Password)
% DB = database to connect to
% Login = login to database
% Password = password to database
% conn = connection structure used by database functions

J = DB_Info(DB);  % Database Info

% Connect
if ~~isempty(J)
    conn = database(DB,Login,Password);  % ODBC source
else
    if length(J) == 5
        if ~~isempty(Login)  % Provided passwords take precedence
            if ~isempty(J{4})  % When there are no provided passwords use the one in the array
                Login = J{4};
                Password = J{5};
            end
        end
    end
    
    conn = database(DB,Login,Password,J{2},[J{3} DB]);  % MySQL Connection
end

%% Database Information
function J = DB_Info(DB)
% Check preferences for JDBC list
prefs('JDBC');  % List of JDBC sources saved in preferences
J = JDBC;clear JDBC
J(1,:) = [];  % Remove the first row
if ~isempty(J)
    temp = find(strcmp(J(:,1),DB) == 1);
    if ~isempty(temp)
        J = J(temp,:);  % Matching entry
    else
        J = [];  % No matching entry
    end
end

% Check preferences for JDBC source file
if ~~isempty(J)
    prefs('JDBC Source')  % Location of JDBC source file saved in preferences
    if ~isempty(J_file)
        if exist(J_file,'file') == 2
            load(J_file)  % Load the file
            temp = find(strcmp(srcs(:,1),DB) == 1);
            if ~isempty(temp)
                J = srcs(temp,:);  % Matching entry
            end
        else
            disp('BDT Preferences')
            disp(['    ' J_file ' does not exist.'])
        end
    end
end

% Check the JDBC source file in Matlab
if ~~isempty(J)
    J_file = setdbprefs('JDBCDataSourceFile');  % Location of JDBC source file from setdbprefs
    if ~isempty(J_file)
        if exist(J_file,'file') == 2
            load(J_file)  % Load the file
            temp = find(strcmp(srcs(:,1),DB) == 1);
            if ~isempty(temp)
                J = srcs(temp,:);  % Matching entry
            end
        else
            disp([J_file ' does not exist. Please update setdbprefs with a valid JDBC source file.'])
            setdbprefs('JDBCDataSourceFile','')
        end
    end
end

%% Database Type
function Type = DB_Type(gui)
% gui = structure of main GUI handles

DBx = get(gui.db_listbox,'String');  % List of databases
DBv = get(gui.db_listbox,'Value');  % Selected database number
DBx = DBx{DBv};  % Selected database
Prefs = get(gui.table_listbox,'UserData');
if ~~isempty(Prefs)
    Prefs.Login = [];
    Prefs.Pass = [];
end
conn = connect(DBx,Prefs.Login,Prefs.Pass);  % Connect to the database
dbmeta = dmd(conn);  % Get database meta data
Type = get(dbmeta,'DatabaseProductName');  % Database type
close(conn);
clear conn dbmeta DBx DBv Prefs

%% Database Manager Help
function dbmanager_help(h,eventdata,fig)
% Help menu for BDT
% h = handle of button or context menu entry
% fig = BDT GUI handle or graph figure handle

FData = get(fig.main,'Userdata');  % Get figure data
if ~~isstruct(FData)
    if ~~isfield(FData,'Help')
        if ~~ishandle(FData.Help)
            figure(FData.Help)
            return
        end
    end
else
    FData = [];
end

prefs('GUI')  % Load Preferences
Pos = gui_position(h,70,30);
gui.main = figure('HandleVisibility','off',...
    'IntegerHandle','off',...
    'Menubar','none',...
    'NumberTitle','off',...
    'Name','DB Manager:  Help',...
    'Units','characters',...
    'Color',BGColor,...
    'Userdata','BDT_Dialog',...
    'Position',Pos);

% Save figure handle
FData.Help = gui.main;
set(fig.main,'Userdata',FData)

% Text
Menu = {'1. Databases'};
Text = {{' ';'    The Database Manager lists databases from four sources.  The first list of databases the Database Manager checks is the list of sources in the preferences coded into it.  To add/edit the list edit the dbmanager.m file and look around line 107.  The list is a cell array of information consisting of the ''Database Name'', ''Driver'', ''URL'', ''Login'', and ''Password''.  The login and password are no required and not suggested since the m-file is an unprotected text file.  The Database Manager will prompt when the login and password are required.';' ';...
    '     The second list of databases come from a source file listed in the preferences on line 95.  The file is one created by the ''confds'' function.  Simply run ''confds'' and save the file to a known location.  Write the location into line 95 of dbmanager.m.';' ';...
    '     The third list of databases is from the same kind of file but the location is stored in Matlab.  ''setdbprefs(''JDBCDataSourceFile'',''C:\Some_Folder\JDBC_Sources.mat'')'' will set the location.  Put this in your Matlab startup script for it to be set every time Matlab starts.  Setting the location in this manor allows BDT, Database Manager, and db_query to see the same list.';' ';...
    '     The find list of databases (only available in Windows) is the ODBC Data Source Administrator (press F9 to access it).  '}};

Menu = [Menu;{'2. Add Database'}];
Text = [Text;{{' ';'     Please refer to the type of database you intend to add.'}}];;

Menu = [Menu;{'   2a. Microsoft Access'}];
Text = [Text;{{' ';'     Adding a new database involves three prompts.  The first of the three asks for the name of the database.  This is not the name which will show up in the Database Manager but the name of the database file.';' ';...
    '     The next prompt asks for the location where the database file will reside.  Remember the location as it will be needed in the final step.';' ';...
    '     The final step opens the ODBC Data Source Administrator. Click Add. Select "Microsoft Access Driver (*.mdb)" and click Finish. In the Data Source Name field give the database a name.  This name is the name which will be displayed in the Database Manager.  Press Select and direct it to the location of the database file.  Finally, press OK twice.  The new database should now appear in the Databases list in the database manager.'}}];

Menu = [Menu;{'   2b. MySQL'}];
Text = [Text;{{' ';'     The creation of a MySQL database requires three pieces of information along with the login and password.  The ''Name'' is the name of the database.  This can be anything you want though it is best to avoid anything non-alphanumeric with the exception of -''s and _''s.';' ';...
    '     The driver will depend on what type of database you are connecting to.  The supplied default is a driver for MySQL databases.  Examples of other database drivers can be found in the ''database'' function help in Matlab.  It is assumed that the JDBC drivers are already installed properly.';' ';...
    '     The URL will not only depend on the type of database you are connecting to but the location of the database on the internet.  Again, refer to the ''database'' function for examples.';' ';...
    '     Newly added databases will be added to the JDBC sources list in the JDBC source file specified in the setdbprefs.  If one does not exist one will be created.  Created JDBC lists will be ''in memory'' until Matlab is closed.'}}];

Menu = [Menu;{'3. Remove Database'}];
Text = [Text;{{' ';'     After selecting the database and clicking the Remove button below the Databases list the ODBC Data Source Administrator will open.  Select the same database and click Remove.'}}];

Menu = [Menu;{'4. Add/Remove Tables'}];
Text = [Text;{{' ';'     Table names cannot contain the following characters:';' ';...
    '     .';...
    '     /';...
    '     *';...
    '     :';...
    '     !';...
    '     #';...
    '     &';...
    '     -';...
    '     ?';...
    '     ;';...
    '     "';...
    '     ''';...
    '     $'}}];

Menu = [Menu;{'5. Add/Remove Columns'}];
Text = [Text;{{' ';'     Column names cannot contain the following characters:';' ';...
    '     .';...
    '     /';...
    '     *';...
    '     :';...
    '     !';...
    '     #';...
    '     &';...
    '     -';...
    '     ?';...
    '     ;';...
    '     "';...
    '     ''';...
    '     $'}}];

Menu = [Menu;{'6. Inserting Data'}];
Text = [Text;{{' ';'     Data can either be a numeric array or a cell array of the same dimensions.  The data itself can be either numeric or character/string.';' ';...
    '     NOTE:  Data is not necessarily added in order.  The rows are guaranteed to be consistent but the order of them will likely not be.  For this reason, it is important to include time/index in the data you insert.  Also, if there are other columns in the table you are not inserting data into there will be NaN''s inserted so that the total number or rows in each column remains consistent.'}}];

Menu = [Menu;{'7. Key Commands'}];
Text = [Text;{{' ';...
    '     e:  Erase all data in the selected table.';' ';...
    '     r:  Refresh the databases list.';' ';...
    '     F1:  Opens this help menu';' ';...
    '     F7:  Add JDBC Source File';' ';...
    '     F8:  JDBC Data Source Setup Window';' ';...
    '     F9:  ODBC Data Source Administrator';' ';...
    '     F12:  Database Manager, BDT, and other Matlab scripts website';' ';...
    }}];

% Listboxes
gui.menu_listbox = uicontrol(gui.main,'Style','listbox','HorizontalAlignment','left','Units','normalized','String',Menu,'BackgroundColor',BGColor,'Position',[0 .8 1 .2],'Userdata',Text);
gui.text_listbox = uicontrol(gui.main,'Style','listbox','HorizontalAlignment','left','Units','normalized','String','','BackgroundColor','white','Position',[0 0 1 .8],'Max',2,'Value',[]);

set(gui.menu_listbox,'Callback',{@help_listbox,gui.menu_listbox,gui.text_listbox});
help_listbox([],[],gui.menu_listbox,gui.text_listbox)
set(gui.main,'ResizeFcn',{@help_listbox,gui.menu_listbox,gui.text_listbox})