Re: [h2] JSON support
I'll try to make it short. It would be useful for a model where the database bundle (OSGi talking) doesn't know what it's plugins needs/wants to store more about each record. A way of providing this is to create a JSON field, where plugins can store their own data structure inside the same table/record as the original one. It's a kind of make it unrelational from this field and beyond. Or, we could quit H2 and go No-SQL ... But why not ? Em quinta-feira, 13 de abril de 2017 18:05:48 UTC+12, Rick Hanton escreveu: > > Daniel. I agree, it would be useful for a project I'm working on now for > our flyway database scripts for MySQL that have a few JSON type columns to > be properly interpreted by H2 (for unit test purposes) even if it is just > using JSON as an alias for VARCHAR or something. > > Thanks guys, > Rick > > On Friday, August 12, 2016 at 6:07:51 AM UTC-7, Daniel Scott wrote: >> >> Sorry to resurrect an old thread, but I'm interested to see if H2 >> supports the json datatype, found in postgres and mysql? >> >> On Monday, 22 July 2013 19:50:39 UTC+1, Thomas Mueller Graf wrote: >>> >>> Hi, >>> >>> Hm, from the description it sounds like you currently use a relational >>> database, and need features of a relational database (like H2, SQLite, or >>> similar). It doesn't sound too much like a use case for JSON to me, as you >>> don't seem to use Javascript or have a have a hierarchical or unstructured >>> data model. Where do you actually need JSON, and can't use SQL? >>> >>> Of course you could use one of those JSON projects, but then you would >>> be bound to use it forever and couldn't easily switch to another storage if >>> needed. With a relational database it's relatively easy to switch as there >>> are multiple products that support the same standard (SQL). >>> >>> Regards, >>> Thomas >>> >>> >>> >>> >>> >>> On Sat, Jul 20, 2013 at 9:32 PM, Anthony wrote: >>> Hi I do have a user case, I have a very large application that uses mdb as its backend, with a sizable customer base. Now the system is typical peer-to-peer networked of about 5 - 10 users per customer base. DB connection uses ADODB now one useful feature it has, is that each pc also has to store say one or two file(s) locally that has about 2000 to 3000 records. Now instead of having to store the data in a separate local db you can store the data to disk in an .adtg file, then you can perform standard sql queries, as well as joins, updates etc, rather than loading an json in memory and looping the data. Now I wish to port the application such that it is platform independent, either web base or desktop (Lazarus) and H2 as the backend. This adtg file is used extensively throughout the app. I am a where of xquery which is an query language for xml but json has a much lower overhead. In my search I came across a few projects in this area eg JSONiq, JaQL, JSonQuery, ObjectPath, UnQL. They are young, but maybe a temp solution for now. Thanks all for your feedback. If anyone know of a better way please reply. Regards Anthony. On Saturday, July 20, 2013 6:24:29 PM UTC+1, Thomas Mueller wrote: > Hi, > > What is your use case? > > I think the fact that MySQL can generate the "explain plan" in JSON > format isn't really a good reason why H2 needs to support JSON. > > But I do agree that JSON is important, similar than XML. Many web > applications use JSON and some NoSQL databases support it (for example > MongoDB). > > However, JSON in a relational database is something different. See > also http://stackoverflow.com/questions/3564024/storing- > data-in-mysql-as-json > > If you use a relational database, I assume you want to use a solution > that works not just in one database but in a way that is portable to many > databases. For example storing the JSON as text. Maybe you want also want > the ability to create indexes (similar to MongoDB). > > Luckily, you can do that already by using user defined functions. > Similar to the XML functions in H2, that can be ported to other > databases. > I don't think supporting a JSON data type is all that important (similar > to > an XML data type). Just use VARCHAR or CLOB. > > I view JSON as similar important than XML. But so far there were no > actual use cases or questions about XML support on the H2 group. So I > assume people don't have a problem that needs to be solved. > > So again, as for JSON, what is your use case? What problem do you need > to solve? > > Regards, > Thomas > > > > On Fri, Jul 19, 2013 at 10:49 PM, Rami Ojares > wrote: > >> I followed the first link and found a list of 4 reasons. >> >>>
[h2] Re: What is the behavior of deleting an index and adding a new one?
Hello. Old rows are indexed on creation of an index. The new index will be used for both old and new rows. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] What is the behavior of deleting an index and adding a new one?
Hi, I have a production database and due to some changes I want to delete the existing index and add a new one on a specific table. Will the new index be automatically built from the past data or will it be only effective for new data? Thanks, Imran. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Very slow execution times for SELECT-statements with lots of LEFT OUTER JOINs after updating on H2 version 1.4
Hello, I could generate a test case from our data model which can to some extent reporduce the behavior of H2 v 1.4 that I mentioned above. I attatched two SQL scripts. The first one creates the tables with part of the constraints that the original tables in our test suite have. The second one is the Select-statement on which one can show the performance differences between 1.4 and 1.3 for this statement. For creating the database and executing the SQL scripts I use Squirrel (v 3.8.1). I created two aliases using the URL 'jdbc:h2:file:path/to/my/database'. I configured the first alias to use the driver of H2v1.4 and the second one to use the driver of H2v1.3 (latest 1.3 version). Comparing the execution times of the Select-statements I observed execution times of approximately 0.25 s for H2v1.4 and 0.09 for H2v1.3. Although the differences for this test case are not significantly different, executing the same Select-statement on our original data base I could observe execution times of 1.5 s for H2v1.4 and 0.15s for H2v1.3. Of course we have test cases that use much more complex Select-statements with LEFT OUTER JOINs for which we observed execution times that were 5 times slower than those on H2 v1.3. At last, for the experiments I didn't change any of the H2 db settings listed here: https://www.h2database.com/javadoc/org/h2/engine/DbSettings.html#OPTIMIZE_OR Thank you for your help in advance. Greetings Ivaylo Am Montag, 23. April 2018 09:59:47 UTC+2 schrieb Ivaylo Dobrikov: > > Hi Noel, > > thank you for the quick response. I've executed the statetement above with > EXPLAIN ANALYZE in version 1.4 and version 1.3 and couldn't see any > differences in the plan. However, the execution time in 1.4 was > significantly greater than the execution time in 1.3. I am going now to > generate a standalone test case and will submit it as soon as I am ready. > > Am Freitag, 20. April 2018 20:36:30 UTC+2 schrieb Noel Grandin: >> >> EXPLAIN ANALYZE will show you what plan the db is executing, something in >> the planner is generating a worse plan now. >> >> Your best bet is to generate a standalone test case for us to look at. >> >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. CREATE TABLE TableA ( aId int, PRIMARY KEY (aId)); CREATE TABLE TableD ( dId int, PRIMARY KEY (dId)); CREATE TABLE TableCur ( curId int, PRIMARY KEY (curId)); CREATE TABLE TableACL ( acId int, PRIMARY KEY (acId)); CREATE TABLE TableFCV ( fcId int, PRIMARY KEY (fcId)); CREATE TABLE TableMP ( mpId int, aId int, dId int, pcId int, rcId int, acId int, fc1Id int, fc2Id int, fc3Id int, fc4Id int, fc5Id int, PRIMARY KEY (mpId), CONSTRAINT FK_TableMP1 FOREIGN KEY (aId) REFERENCES TableA(aId), CONSTRAINT FK_TableMP2 FOREIGN KEY (dId) REFERENCES TableD(dId), CONSTRAINT FK_TableMP3 FOREIGN KEY (pcId) REFERENCES TableCur(curId), CONSTRAINT FK_TableMP4 FOREIGN KEY (rcId) REFERENCES TableCur(curId), CONSTRAINT FK_TableMP5 FOREIGN KEY (acId) REFERENCES TableFCV(fcId), CONSTRAINT FK_TableMP6 FOREIGN KEY (fc1Id) REFERENCES TableFCV(fcId), CONSTRAINT FK_TableMP7 FOREIGN KEY (fc2Id) REFERENCES TableFCV(fcId), CONSTRAINT FK_TableMP8 FOREIGN KEY (fc3Id) REFERENCES TableFCV(fcId), CONSTRAINT FK_TableMP9 FOREIGN KEY (fc4Id) REFERENCES TableFCV(fcId), CONSTRAINT FK_TableMP10 FOREIGN KEY (fc5Id) REFERENCES TableFCV(fcId) ); CREATE TABLE TableP ( pId int, mpId int, PRIMARY KEY (pId), CONSTRAINT FK_TableP FOREIGN KEY (mpId) REFERENCES TableMP(mpId)); CREATE TABLE TableLT ( lId int, pId int, PRIMARY KEY (lId), CONSTRAINT FK_TableLT FOREIGN KEY (pId) REFERENCES TableP(pId)); CREATE TABLE TableAGGTYPE ( atId int, PRIMARY KEY (atId)); CREATE TABLE TableAGGTYPET ( atId int, PRIMARY KEY (atId), CONSTRAINT FK_TableAGGTYPET FOREIGN KEY (atId) REFERENCES TableAGGTYPE(atId)); CREATE TABLE TableCALCP ( cpId int); CREATE TABLE TableRV ( lId int, cpId int, pId int, atId int, rvId int, PRIMARY KEY (rvId), CONSTRAINT FK_TableRV1 FOREIGN KEY (cpId) REFERENCES TableCALCP(cpId), CONSTRAINT FK_TableRV2 FOREIGN KEY (pId) REFERENCES TableP(pId), CONSTRAINT FK_TableRV3 FOREIGN KEY (lId) REFERENCES TableLT(lId), CONSTRAINT FK_TableRV4 FOREIGN KEY (atId) REFERENCES TableAGGTYPET(atId), CONSTRAINT UC_RV UNIQUE (lId,cpId,pId,atId) ); CREATE TABLE TableDIUSERS ( uId int, PRIMARY
Re: [h2] Very slow execution times for SELECT-statements with lots of LEFT OUTER JOINs after updating on H2 version 1.4
Hi Noel, thank you for the quick response. I've executed the statetement above with EXPLAIN ANALYZE in version 1.4 and version 1.3 and couldn't see any differences in the plan. However, the execution time in 1.4 was significantly greater than the execution time in 1.3. I am going now to generate a standalone test case and will submit it as soon as I am ready. Am Freitag, 20. April 2018 20:36:30 UTC+2 schrieb Noel Grandin: > > EXPLAIN ANALYZE will show you what plan the db is executing, something in > the planner is generating a worse plan now. > > Your best bet is to generate a standalone test case for us to look at. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
