{"action":"created","issue":{"url":"https://api.github.com/repos/openhab/openhab/issues/3147","labels_url":"https://api.github.com/repos/openhab/openhab/issues/3147/labels{/name}","comments_url":"https://api.github.com/repos/openhab/openhab/issues/3147/comments","events_url":"https://api.github.com/repos/openhab/openhab/issues/3147/events","html_url":"https://github.com/openhab/openhab/issues/3147","id":105744744,"number":3147,"title":"New DBMS Persistence Service for derby, h2, hsqldb, mariadb, mysql, postgresql, Sqlite and others","user":{"login":"lewie","id":5707426,"avatar_url":"https://avatars.githubusercontent.com/u/5707426?v=3","gravatar_id":"","url":"https://api.github.com/users/lewie","html_url":"https://github.com/lewie","followers_url":"https://api.github.com/users/lewie/followers","following_url":"https://api.github.com/users/lewie/following{/other_user}","gists_url":"https://api.github.com/users/lewie/gists{/gist_id}","starred_url":"https://api.github.com/users/lewie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lewie/subscriptions","organizations_url":"https://api.github.com/users/lewie/orgs","repos_url":"https://api.github.com/users/lewie/repos","events_url":"https://api.github.com/users/lewie/events{/privacy}","received_events_url":"https://api.github.com/users/lewie/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":20,"created_at":"2015-09-10T07:11:11Z","updated_at":"2015-10-01T13:03:19Z","closed_at":null,"body":"### This is a new DBMS Persistence Service \r\nUses MyBatis and JDBC.\r\n\r\n#### Keys:\r\n- Tablenames are more readable\r\n- Tablenames can be reconfigured\r\n- Can work with original Databasemodel of MySQL- Service\r\n- Currently supports the following databases:\r\nderby, h2, hsqldb, mariadb, mysql, postgresql, Sqlite\r\n- DBMS clearly separates the database-specific part of openhab logic.\r\nTo integrate a new database is fairly simple.\r\n-  The JDBC drivers are not compiled with in the bundle, there is one \"Driver Bundle\" per Database. Driver files do not increase the main DBMS Service Bundle.\r\n\r\n#### What is the benefit of a 'per Item' table layout?\r\nWhat is the benefit to manage 500,000 Integer, Numbers or Strings per item, instead of 10,000,000 Strings in one table? An attempt to explain:\r\n 1. Data Mass searching: 500,000 to 10,000,000.\r\n 2. It is expensive to search for average, min, max, etc., especially in a bunch of strings.\r\n 3. There are Items which generates only some Data and other millions of datasets per Year, and others are often queried per Minute. On Item level, these different requirements are easier to handle.\r\n 4. Clarity\r\n 5. Delete an Items Table and start over in a second.\r\n\r\nIn short, with a 'per Item' layout, the scalability and easy maintenance of the database is ensured, even if large amounts of data must be managed.\r\n\r\n#### For Testing:\r\n[Pre-Build] (https://github.com/lewie/openhab/tree/dbms-new-service/bundles/persistence/org.openhab.persistence.dbms/build)\r\n[Drivers and Configuration](https://github.com/lewie/openhab/tree/dbms-new-service/bundles/persistence/org.openhab.persistence.dbms/drivers)\r\n\r\n#### Older Discussions:\r\nhttps://github.com/openhab/openhab/pull/2805\r\nhttps://github.com/openhab/openhab/pull/2670\r\n\r\n#### Configuration:\r\n```\r\n######################## DBMS Persistence Service #############################\r\n# I N S T A L L   D B M S   P E R S I S T E N C E   S E R V I C E \r\n# To use this DBMS-service-bundle (org.openhab.persistence.dbms-X.X.X.jar),\r\n# a appropriate JDBC database-driver is needed in OpenHab addons Folder.\r\n# Prepared matching Bundles with JDBC database-driver embedded can be found in DBMS project. \r\n# Use: '/drivers/org.openhab.persistence.dbms.jdbc.[databaseName]-X.X.X.jar'.\r\n# Copy both (DBMS-service-bundle and a JDBC database-driver) to your OpenHab '[OpenHab]/addons' Folder to make it work. \r\n#\r\n# A special case is the driver for H2, it ONLY works by copying \"h2-X.X.XXX.jar\" to your '[OpenHab]/addons' folder directly.\r\n# Alternatively copying the driver directly may work for some other JDBC database-driver Files too. \r\n#\r\n# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite\r\n# If using a not known url like jdbc:XXX, configuration is falling back to the default mysql profile and SQL.\r\n# \r\n# derby, h2, hsqldb, sqlite can be embedded, \r\n# If no database is available, for Example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder. \r\n\r\n# D A T A B A S E  C O N F I G\r\n# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.\r\n# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'\r\n# dbms:url=jdbc:derby:./testDerby;create=true\r\n# dbms:url=jdbc:h2:./testH2\r\n# dbms:url=jdbc:hsqldb:./testHsqlDb\r\n# dbms:url=jdbc:mariadb://192.168.0.1:3306/testMariadb\r\n# dbms:url=jdbc:mysql://192.168.0.1:3306/testMysql\r\n# dbms:url=jdbc:postgresql://192.168.0.1:5432/testPostgresql\r\n# dbms:url=jdbc:sqlite:./testSqlite.db\r\n\r\n# required database user\r\n#dbms:user=\r\ndbms:user=test\r\n\r\n# required database password\r\n#dbms:password=\r\ndbms:password=test\r\n\r\n# E R R O R   H A N D L I N G\r\n# optional when Service is deactivated (optional, default: 0 -> ignore) \r\n#dbms:errReconnectThreshold=\r\n\r\n# I T E M   O P E R A T I O N S\r\n# optional tweaking SQL datatypes\r\n# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html        \r\n# see: http://www.h2database.com/html/datatypes.html\r\n# see: http://www.postgresql.org/docs/9.3/static/datatype.html\r\n# defaults:\r\n#dbms:sqltype.CALL\t\t\t=\tVARCHAR(200)\r\n#dbms:sqltype.COLOR\t\t\t=\tVARCHAR(70)\r\n#dbms:sqltype.CONTACT\t\t=\tVARCHAR(6)\r\n#dbms:sqltype.DATETIME\t\t=\tDATETIME\r\n#dbms:sqltype.DIMMER\t\t=\tTINYINT\r\n#dbms:sqltype.LOCATION\t\t=\tVARCHAR(30)\r\n#dbms:sqltype.NUMBER\t\t=\tDOUBLE\r\n#dbms:sqltype.ROLLERSHUTTER\t=\tTINYINT\r\n#dbms:sqltype.STRING\t\t=\tVARCHAR(65500)\r\n#dbms:sqltype.SWITCH\t\t=\tVARCHAR(6)\r\n\r\n# For Itemtype \"Number\" default decimal digit count (optional, default: 3) \r\n#dbms:numberDecimalcount=\r\n\r\n# T A B L E   O P E R A T I O N S\r\n# Tablename Prefix generation, using Item real names or \"item\" (optional, default: false -> \"item\") \r\n#dbms:tableUseRealItemNames=\r\ndbms:tableUseRealItemNames=true\r\n\r\n# Tablename Suffix length (optional, default: 4 -> 0001-9999) \r\n#dbms:tableIdDigitCount=\r\n\r\n# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false) \r\n# USE WITH CARE! Deactivate after Renaming is done!\r\n#dbms:rebuildTableNames=\r\n#dbms:rebuildTableNames=true\r\n\r\n# D A T A B A S E / M Y B A T I S   C O N N E C T I O N S\r\n# Some embeded Databases can handle only one Connection (optional, default: 1) \r\n# dbms.maxActiveConnections = 1\r\n# dbms.maxIdleConnections = 1\r\n```\r\nPlease test the bundle of functionality, stability, robustness and Java specialists for possible improvements (thread safety, synchronization problems ...).\r\n\r\n...and of course the openhab2 Compatibility.\r\n\r\nThanks and Have Fun!\r\n\r\n[Go to Pullrequest](https://github.com/openhab/openhab/pull/3146)\r\n\r\nRgds\r\nHelmut\r\n"},"comment":{"url":"https://api.github.com/repos/openhab/openhab/issues/comments/144720706","html_url":"https://github.com/openhab/openhab/issues/3147#issuecomment-144720706","issue_url":"https://api.github.com/repos/openhab/openhab/issues/3147","id":144720706,"user":{"login":"lewie","id":5707426,"avatar_url":"https://avatars.githubusercontent.com/u/5707426?v=3","gravatar_id":"","url":"https://api.github.com/users/lewie","html_url":"https://github.com/lewie","followers_url":"https://api.github.com/users/lewie/followers","following_url":"https://api.github.com/users/lewie/following{/other_user}","gists_url":"https://api.github.com/users/lewie/gists{/gist_id}","starred_url":"https://api.github.com/users/lewie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lewie/subscriptions","organizations_url":"https://api.github.com/users/lewie/orgs","repos_url":"https://api.github.com/users/lewie/repos","events_url":"https://api.github.com/users/lewie/events{/privacy}","received_events_url":"https://api.github.com/users/lewie/received_events","type":"User","site_admin":false},"created_at":"2015-10-01T13:03:19Z","updated_at":"2015-10-01T13:03:19Z","body":"Hi @grzech1983 ,\r\nI work hard on the JDBC Binding, while some time-consuming changes have occurred.\r\n\r\nPlease give me some time yet!\r\nThe JDBC Binding is then the basis for further tests.\r\n\r\nA short Test shows, no Errors with JDBC-Binding:\r\nUsed code:\r\n````\r\nFrame {\r\n   Webview url=\"http://localhost:8080/chart?items=Dimmer_Item&period=h&random=1&service=jdbc\" height=6\r\n   Chart item=Dimmer_Item period=h refresh=30000\r\n}\r\n````\r\n\r\nRgds\r\nHelmut"}}