ModeShape
  1. ModeShape
  2. MODE-1861

Database binary store schema is incompatible with MySQL 5.5

    Details

    • Type: Bug Bug
    • Status: Closed Closed (View Workflow)
    • Priority: Blocker Blocker
    • Resolution: Done
    • Affects Version/s: 3.1.3.Final
    • Fix Version/s: 3.2.0.Final
    • Component/s: Storage
    • Labels:
      None
    • Similar Issues:
      Show 10 results 

      Description

      The DatabaseBinaryStore attempts to create a table with the following statement:

      CREATE TABLE CONTENT_STORE (
        cid VARCHAR(255) NOT NULL,
        mime_type VARCHAR(255),
        ext_text VARCHAR(1000),
        usage INTEGER,
        usage_time TIMESTAMP,
        payload BLOB,
        primary key(cid)
      )
      

      However, "usage" is a reserved word in MySQL5, and therefore the table cannot be created. Additionally, no error message appears to be recorded in the log.

        Activity

        Hide
        Randall Hauch
        added a comment -

        Added a pull-request that changed how the DatabaseBinaryStore configures and uses the database, so that the various SQL statements (both DDL and DML) are read from property files rather than be dynamically created. This means that it is far easier to see and understand exactly the kinds of statements that are issued for a given DBMS. It also allows the database table and statements to be altered slightly for specific DBMSes.

        Several DBMS-specific property files (containing the SQL statements) were added to accommodate the variations in the previously-generated statements. There is a fair amount of duplication, but each file is self-contained, is very readable, and can be customized as needed (including by the user, since additional property files can be created to override those ModeShape provides out-of-the-box). IMO, these benefits outweigh the negative aspects of the duplication.

        Since several DBMSes (including MySQL, PostgreSQL, Sybase and SQLServer) consider 'usage' to be a reserved keyword, the 'usage' column was renamed to 'usage_flag' in the create-table and other statements for all of the DBMSes. (This shouldn't be a problem, since the use of reserved words should have prevented creation of the table in the first place). The statements for the other DBMSes still use 'usage', but these DBMSes don't consider 'usage' to be a reserved keyword.

        This change also reduced the amount of "general-purpose" JDBC-oriented code, such as eliminating the SQLBuilder code.

        Finally, the Database class now creates PreparedStatement objects during initialization, and these are reused throughout the lifetime of the DatabaseBinaryStore. This will increase performance compared with creating a PreparedStatement every time one is needed.

        Show
        Randall Hauch
        added a comment - Added a pull-request that changed how the DatabaseBinaryStore configures and uses the database, so that the various SQL statements (both DDL and DML) are read from property files rather than be dynamically created. This means that it is far easier to see and understand exactly the kinds of statements that are issued for a given DBMS. It also allows the database table and statements to be altered slightly for specific DBMSes. Several DBMS-specific property files (containing the SQL statements) were added to accommodate the variations in the previously-generated statements. There is a fair amount of duplication, but each file is self-contained, is very readable, and can be customized as needed (including by the user, since additional property files can be created to override those ModeShape provides out-of-the-box). IMO, these benefits outweigh the negative aspects of the duplication. Since several DBMSes (including MySQL, PostgreSQL, Sybase and SQLServer) consider 'usage' to be a reserved keyword, the 'usage' column was renamed to 'usage_flag' in the create-table and other statements for all of the DBMSes. (This shouldn't be a problem, since the use of reserved words should have prevented creation of the table in the first place). The statements for the other DBMSes still use 'usage', but these DBMSes don't consider 'usage' to be a reserved keyword. This change also reduced the amount of "general-purpose" JDBC-oriented code, such as eliminating the SQLBuilder code. Finally, the Database class now creates PreparedStatement objects during initialization, and these are reused throughout the lifetime of the DatabaseBinaryStore. This will increase performance compared with creating a PreparedStatement every time one is needed.
        Hide
        Horia Chiorean
        added a comment -

        Rebased and merged into master.

        Show
        Horia Chiorean
        added a comment - Rebased and merged into master.

          People

          • Assignee:
            Randall Hauch
            Reporter:
            Randall Hauch
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: