note
    description:
        "[
            Skeleton database.

            This database connects via ODBC to a data source
            called "dose2010" with user "dose" and password "1234".

        ]"
    date: "$Date: 2010-11-05 10:26:09 -0300 (Fri, 05 Nov 2010) $"
    revision: "$Revision: 968 $"

class
    S_DATABASE

inherit

    SYSTEM_ENCODINGS
        export {NONE} all end

create
    make

feature {NONE} -- Initialization

    make
            -- Initialize database.
        do
            create odbc_database
        end

feature -- Access

    change: DB_CHANGE
            -- Access to database changing functions.

    selection: DB_SELECTION
            -- Access to database selecting functions.

    session_control: DB_CONTROL
            -- Database session control.

feature -- Status report

    is_connected: BOOLEAN
            -- Is database connected?
        do
            Result := session_control /= Void and then session_control.is_connected
        end

    table_exists (a_table: STRING): BOOLEAN
            -- Does table `a_table' exist in database?
        require
            connected: is_connected
        local
            l_tuple: DB_TUPLE
        do
            selection.clear_all
            selection.set_map_name (a_table, "table_name")
            selection.query ("SELECT CASE WHEN EXISTS((SELECT * FROM information_schema.tables WHERE table_name = :table_name)) THEN 1 ELSE 0 END")
            if selection.is_ok then
                selection.load_result
                create l_tuple.copy (selection.cursor)
                Result := l_tuple.item (1).out ~ "1"
            else
                print_sql_error_and_reset
            end
            selection.terminate
        end

feature -- Basic operations

    connect
            -- Connect to database.
        do
            odbc_database.set_data_source ("store-dose")
            odbc_database.login ("group4", "dose2010")
            odbc_database.set_base

            create session_control.make
            create selection.make
            create change.make

            session_control.connect
        end

    disconnect
            -- Disconnect from database.
        do
            if is_connected then
                session_control.disconnect
            end
        ensure
            not_connected: not is_connected
        end

    execute_sql_file (a_filename: STRING)
            -- Execute SQL statements in file `a_filename'.
            -- This function assumes that there is exactly one statement per line.
        require
            connected: is_connected
            file_exists: (create {RAW_FILE}.make (a_filename)).exists
        local
            l_raw: RAW_FILE
            l_file: PLAIN_TEXT_FILE
            l_sql: STRING
        do
            from
                create l_file.make_open_read (a_filename)
            until
                l_file.end_of_file
            loop
                l_file.read_line

                l_sql := l_file.last_string

                l_sql.left_adjust
                l_sql.right_adjust
                if not l_sql.is_empty then
                    change.modify (l_sql)
                    if not change.is_ok then
                        print_sql_error_and_reset
                    end
                end
            end
        end

    insert_string_32 (a_value: STRING_32)
            -- Store `a_value' in database.
        local
            l_utf8_data: STRING_8
        do
            utf32.convert_to (utf8, a_value)
            l_utf8_data := utf32.last_converted_stream

            change.clear_all
            change.set_map_name (l_utf8_data, "data")
            change.modify ("INSERT INTO s_skeleton_table (field) VALUES (:data)")
            if not change.is_ok then
                print_sql_error_and_reset
            end
        end

    load_data
            -- Load data from database and store it in `last_data'.
        local
            l_data_container: LINKED_LIST [DB_RESULT]
            l_tuple: DB_TUPLE
            l_id: INTEGER_REF
            l_raw_data: STRING_8
        do
            create l_data_container.make
            create last_data.make

            selection.clear_all
            selection.query ("SELECT * FROM s_skeleton_table")

            if selection.is_ok then
                selection.set_container (l_data_container)
                selection.load_result
                from
                    l_data_container.start
                until
                    l_data_container.after
                loop
                    create l_tuple.copy (l_data_container.item)

                    l_id ?= l_tuple.item (1)
                    l_raw_data ?= l_tuple.item (2)
                    utf8.convert_to (utf32, l_raw_data)

                    last_data.extend ([l_id.item, utf8.last_converted_string.as_string_32])

                    l_data_container.forth
                end
            else
                print_sql_error_and_reset
            end

            selection.terminate
        end

    last_data: LINKED_LIST [TUPLE [id: INTEGER; field: STRING_32]]
            -- Last loaded data.

feature {NONE} -- Implementation

    print_sql_error_and_reset
            -- Print current SQL error and reset status.
        require
            has_error: not session_control.is_ok
        do
            print ("%N" + session_control.error_message + "%N")
            session_control.reset
        ensure
            reset: session_control.is_ok
        end

    odbc_database: DATABASE_APPL [ODBC]
            -- ODBC database handle.

invariant
    session_control_not_void: is_connected implies session_control /= Void
    change_not_void: is_connected implies change /= Void
    selection_not_void: is_connected implies selection /= Void

end