Error: Macro BackLinksMenu(None) failed

compressed data is corrupt

Error: Macro TicketQuery(summary=SERVER_DATABASE_PERSISTENCE, format=table, col=summary|owner|status|type|component|priority|effort|importance, rows=description|analysis_owners|analysis_reviewers|analysis_score|design_owners|design_reviewers|design_score|implementation_owners|implementation_reviewers|implementation_score|test_owners|test_reviewers|test_score|) failed

current transaction is aborted, commands ignored until end of transaction block

Analysis

Overview

The Server have to keep it's resource data in a flexible and extend-able database. The information should be stored and loaded when the Server is restarted, the current functionality should continue to work.

Task requirements

• Choose an appropriate database for storing.
• Create a good database schema for storing the resources and the history
• Store in the database the resources and the history
  • Should be able to pick a resource data at a specified revision
  • Should be able delete and create a resource in the database
  • Should be able to store the changes of an resource in the database.
• Create an API that communicates with the database using JDBC.
  • Create a JDBC template to query easy the DB
  • Think of a way to manage the DB connection
    • Connecting to the DB should be treadsafe.
• Make the facade and the web use the database
• Preserve the current functionality - collaboration, skipping changes, web upload/download/browse/delete
• Make a mock server from the old implementation of accessing resources in the server memory (optional)

Task result

Source code and tests.

Implementation idea

• Use H2 database for the database.
• Use ThreadLocal connection variables for the ThreadSafe connections.
• Create special WebResourceAccess to replace the current mem accesses in the web, but to preserve working with helpers.
• Use the current Persistence API to store the immutables in the DB.

Related

No related tickets for now...

How to demo

• Show the working web and collaboration in Sophie but with the DB implementation
  • Web interface
    • Upload a book through the web interface
    • Browse the books in the web interface
    • Download and delete the book.
  • Collaboration
    • Save a book on the server
    • Make some changes from different Sophie clients
    • Undo the change, redo them
    • See the actions in all the clients

Design

Database and JDBC template

Database and schema

• The choosen database is H2 DB -> ​Homepage
  • The other canditates were:
    • Derby or JavaDB, it is Sun's embedded database used mainly for Java Application, but in comparison to the H2 there are no inner optimizations with the transactions and there are some problems
    • Hypersonic -> The embedded DB of Hibernate, it is simmilar ot H2, but its main purpose is to be used with Hibernate.
  • The H2 database has a JDBC implementation and comes with one jar, added as dependency to the server.core module.
  • It can be embedded in the memory (The virtual machine), in file or to be used as server and client
  • In Sophie 2 it will be used as Embedded in the memory (JVM) and stored into a file.
  • JDBC can access the DB with similar URLS "jdbc:h2:path_on_the_machine;AUTO_SERVER=TRUE", the AUTO_SERVER_TRUE here means that if another client tries to connect to the DB our clent will continue to be connected, and not rejected, also means that if another client is connected already to the DB, our client will not be rejected...
• Schema -> The schema will be constructed in sucha way that the queries to the DB could be optimal (It will be normalized and if you want for example to see the value of a given key of a given resource at a given revision, you will not need to select the change caused the revision, ie every logiical kind of data will be iiiin separate table)
  • Tables that have unique data will be named in the following convention : T_TABLENAME (T comes from table)
  • Tables that can be viewed with a series of joins on the other tables and are used mainly for ease when selecting will be names MV_TABLENAME (The V is from view, the M comes from multiple (tables))
  • TODO SCHEMA_AND_DESCRIPTION_HERE

JDBC Template

• For our purposes using JDBC is enough there is no need of ORM like Hibernate and bean model for it, because we will have small database.
• The queries for selecting/updating/inserting data can be categorized and there will be one class that gives methods for using them. This class will capsulate all the actions using JDBC and will take care with the JDBC API for the developers.
• Class : JdbcTemplate -> Responsible for releasing all the JDBC resources that are used for query like ResultSets and Statements. It is not responsible for managing JDBC Connections.
  • The JdbcTemplate class can be constructed with a ConenctionManager (used for retrieving of threadsafe connections to the DB and commit and rollback of transactions)
  • Public methods:
    • public <T> T execute(SQLCallback<T> callback) -> Used for executing SQLCallbacks (Actions that executeeeeee sql queries) to the database. It returns the result of the callback. If there is any error, the current transaction to the DB will be rollbacked and the connection closed (This method must be use by all methods executing queries, it takes care of errors in the JDBC or in the DB)
    • public <T> T execute(final ConnectionCallback<T> action) throws JdbcException -> Used to execute a ConnectionCallback (Actions which use JDBC connections). This execute method uses the mentioned above one, creating a SQLCallback that executes the ConnectionCallback in a connection retrieved by the ConnectionManager. (This method takes care of retrieving the connections, it should release the connection if there is no user made transaction and there no errors)
    • public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper, Object... parameters) -> Method for executing queries to the database which return list data, for example for selecting a list of resource names...
    • public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... parameters) -> Used for executing queries that hase a single result, for example the name of a resource with a given database ID.
    • public <T> T query(final String sql, final ResultSetMapper<T> mapper, final Object... parameters) -> Executes a query with a list of parameters, used by the two methods above.
    • public <T, P> T query(final String sql, final ResultSetMapper<T> mapper, final ParametersSetter<P> parametersSetter) -> The same as the above one, but its query parameters are managed by a given ParameterSetter.
    • public int update(final String sql, final Object... parameters) Used for a single update to the databse, it returns the update count of the query.
    • public int[] updateBatch(final String sql, final Collection<Object[]> parameters) Executes a number of updates to the database with one query string, but with different parameters, used by the above method for one update.
    • public int[] updateBatch(final String sql, final ParametersSetter<Object[]> parametersSetter) The same as the above, but using ParameterSetter to manage the parameters and used by the above.
    • public int[] updateBatch(final String sql, final ParametersSetter<Object[]> parametersSetter) Executes an insert to the database, there are analogical insertBatch method as the update ones.
    • public ConnectionManager getConnectionManager() -> Getter of the ConnectionManager of the template.
    • commit() and rollback() methods for user made transactions that can be commited or rollbacked by the user, the use the ConnectionManager.
  • As you can see there are plenty of public methods some of which should be protected or private (the batch methods, the query and execute methods), They are public because the user may not be able to change the template but wants to write qa query different from just insert/update one query or selllect one object or one list of objects, if you have oppinion on which of these methods should be private/protected, please write it in the review.

Implementation

^{(Describe and link the implementation results here (from the wiki or the repository).)}

Testing

^{(Place the testing results here.)}

Comments

^{(Write comments for this or later revisions here.)}