Liran Zelkha has uploaded a new change for review. Change subject: core: Improve Dynamic Query SQL generator ......................................................................
core: Improve Dynamic Query SQL generator Optimize SQL created by the dynamic query generator. Change-Id: I862873171c6753f8c8863c10c336e981e39dc8cb Bug-Url: https://bugzilla.redhat.com/?????? Signed-off-by: [email protected] <[email protected]> --- M backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java 1 file changed, 240 insertions(+), 25 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/35/28135/1 diff --git a/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java b/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java index 575ba44..e103029 100644 --- a/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java +++ b/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java @@ -10,13 +10,17 @@ import org.junit.Rule; import org.junit.Test; import org.mockito.Mockito; +import org.ovirt.engine.core.common.businessentities.Tags; import org.ovirt.engine.core.common.config.Config; import org.ovirt.engine.core.common.config.ConfigCommon; import org.ovirt.engine.core.common.config.ConfigValues; import org.ovirt.engine.core.common.config.IConfigUtilsInterface; +import org.ovirt.engine.core.common.interfaces.ITagsHandler; import org.ovirt.engine.core.utils.MockConfigRule; public class SyntaxCheckerTest { + + private final static String TAG_NAME = "tag1"; @Rule public MockConfigRule mcr = new MockConfigRule(); @@ -36,6 +40,14 @@ .thenReturn("SELECT * FROM (%2$s) %1$s) as T1 %3$s"); Mockito.when(configUtils.getValue(ConfigValues.DBPagingSyntax, ConfigCommon.defaultConfigurationVersion)) .thenReturn("OFFSET (%1$s -1) LIMIT %2$s"); + Mockito.when(configUtils.getValue(ConfigValues.PgMajorRelease, ConfigCommon.defaultConfigurationVersion)) + .thenReturn(9); + BaseConditionFieldAutoCompleter.tagsHandler = Mockito.mock(ITagsHandler.class); + Tags tags = new Tags(); + tags.settag_name(TAG_NAME); + Mockito.when(BaseConditionFieldAutoCompleter.tagsHandler.GetTagByTagName(Mockito.anyString())).thenReturn(tags); + Mockito.when(BaseConditionFieldAutoCompleter.tagsHandler.GetTagNamesAndChildrenNamesByRegExp(Mockito.anyString())) + .thenReturn("'" + TAG_NAME + "'"); Config.setConfigUtils(configUtils); } @@ -100,42 +112,245 @@ } @Test - public void testAlerts() { - SyntaxChecker chkr = new SyntaxChecker(100); + public void testHost() { + testValidSql("Host: sortby cpu_usage desc", + "SELECT * FROM ((SELECT vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: name =test1 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.vds_name LIKE nulltest1 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: CPU_USAGE > 80 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.usage_cpu_percent > 80 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: user.name = user1 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vds_with_tags.vds_id=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE nulluser1 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: STORAGE.name = pool1 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( storage_pool_id IN (SELECT storage_pool_id FROM storage_domains WHERE storage_domains.storage_name LIKE 'pool1')) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: EVENT.severity=error and CPU_USAGE > 80 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN audit_log ON vds_with_tags.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds_with_tags.usage_cpu_percent > 80 ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: EVENT.severity=error and tag=tag1 sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN audit_log ON vds_with_tags.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds_with_tags.tag_name IN ('tag1') ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: tag=tag1", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.tag_name IN ('tag1') )) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Host: vm.name=vm1", + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN vms_with_tags ON vds_with_tags.vds_id=vms_with_tags.run_on_vds WHERE vms_with_tags.vm_name LIKE nullvm1 )) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } - ISyntaxChecker curSyntaxChecker = SyntaxCheckerFactory.createBackendSyntaxChecker("foo"); + @Test + public void testHosts() { + testValidSql("Hosts: sortby cpu_usage desc", + "SELECT * FROM ((SELECT vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } - SyntaxContainer res = curSyntaxChecker.analyzeSyntaxState("Events: severity=error", true); - String query = chkr.generateQueryFromSyntaxContainer(res, true); - Assert.assertEquals("SELECT * FROM (SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '2' and (not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0", - query); + @Test + public void testVm() { + testValidSql("Vm: status=Up or status=PoweringUp or status=MigratingTo or status=WaitForLaunch or status=RebootInProgress or status=PoweringDown or status=Paused or status=Unknown sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags WHERE ( ( ( ( ( ( ( vms_with_tags.status = '1' OR vms_with_tags.status = '2' ) OR vms_with_tags.status = '6' ) OR vms_with_tags.status = '9' ) OR vms_with_tags.status = '10' ) OR vms_with_tags.status = '16' ) OR vms_with_tags.status = '4' ) OR vms_with_tags.status = '7' ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Vm:", + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Vm: user.name = user1", + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vms_with_tags.vm_guid=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE nulluser1 )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Vm: user.name = user1 and user.tag=tag1", + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vms_with_tags.vm_guid=vdc_users_with_tags.vm_guid WHERE ( vdc_users_with_tags.name LIKE nulluser1 AND vdc_users_with_tags.tag_name IN ('tag1') ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testVms() { + testValidSql("Vms: status=Up or status=PoweringUp or status=MigratingTo or status=WaitForLaunch or status=RebootInProgress or status=PoweringDown or status=Paused or status=Unknown sortby cpu_usage desc", + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags WHERE ( ( ( ( ( ( ( vms_with_tags.status = '1' OR vms_with_tags.status = '2' ) OR vms_with_tags.status = '6' ) OR vms_with_tags.status = '9' ) OR vms_with_tags.status = '10' ) OR vms_with_tags.status = '16' ) OR vms_with_tags.status = '4' ) OR vms_with_tags.status = '7' ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testEvent() { + testValidSql("Event: ", + "SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Event: severity=error ", + "SELECT * FROM (SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '2' and (not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Event: severity=alert ", + "SELECT * FROM (SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '10' and (not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Event: host.name = host1 ", + "SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and audit_log_id IN (SELECT audit_log.audit_log_id FROM audit_log LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE nullhost1 ) and not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testEvents() { + testValidSql("Events: ", + "SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Events: severity=error ", + "SELECT * FROM (SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '2' and (not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Events: severity=alert ", + "SELECT * FROM (SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '10' and (not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testTemplate() { + testValidSql("Template: ", + "SELECT * FROM ((SELECT * FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testTemplates() { - SyntaxChecker chkr = new SyntaxChecker(100); - - ISyntaxChecker curSyntaxChecker = SyntaxCheckerFactory.createBackendSyntaxChecker("foo"); - - SyntaxContainer res = curSyntaxChecker.analyzeSyntaxState("Templates: ", true); - String query = chkr.generateQueryFromSyntaxContainer(res, true); - Assert.assertEquals( - "SELECT * FROM ((SELECT * FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0", - query); + testValidSql("Templates: ", + "SELECT * FROM ((SELECT * FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test - public void testVmPools() { - SyntaxChecker chkr = new SyntaxChecker(100); + public void testUser() { + testValidSql("User:", + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("User: host.name=host1", + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags LEFT OUTER JOIN vms_with_tags ON vdc_users_with_tags.vm_guid=vms_with_tags.vm_guid LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE nullhost1 )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + @Test + public void testUsers() { + testValidSql("Users:", + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testGroup() { + testValidSql("Group:", + "SELECT * FROM (SELECT * FROM ad_groups WHERE ( id IN (SELECT ad_groups.id FROM ad_groups )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testGroups() { + testValidSql("Groups:", + "SELECT * FROM (SELECT * FROM ad_groups WHERE ( id IN (SELECT ad_groups.id FROM ad_groups )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testPool() { + testValidSql("Pool: ", + "SELECT * FROM ((SELECT * FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testPools() { + testValidSql("Pools: ", + "SELECT * FROM ((SELECT * FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testCluster() { + testValidSql("Cluster: ", + "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testClusters() { + testValidSql("Clusters: ", + "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testDatacenter() { + testValidSql("DataCenter: sortby name", + "SELECT * FROM (SELECT * FROM storage_pool WHERE ( id IN (SELECT storage_pool_with_storage_domain.id FROM storage_pool_with_storage_domain )) ORDER BY name,name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testStorage() { + testValidSql("Storage: ", + "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testStorages() { + testValidSql("Storages: ", + "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testDisk() { + testValidSql("disk: ", + "SELECT * FROM (SELECT * FROM all_disks WHERE ( disk_id IN (SELECT all_disks.disk_id FROM all_disks )) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testDisks() { + testValidSql("disks: ", + "SELECT * FROM (SELECT * FROM all_disks WHERE ( disk_id IN (SELECT all_disks.disk_id FROM all_disks )) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testVolume() { + testValidSql("volume: ", + "SELECT * FROM (SELECT * FROM gluster_volumes_view WHERE ( id IN (SELECT gluster_volumes_view.id FROM gluster_volumes_view )) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testVolumes() { + testValidSql("volumes: ", + "SELECT * FROM (SELECT * FROM gluster_volumes_view WHERE ( id IN (SELECT gluster_volumes_view.id FROM gluster_volumes_view )) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testQuota() { + testValidSql("quota: ", + "SELECT * FROM (SELECT * FROM quota_view WHERE ( quota_id IN (SELECT quota_view.quota_id FROM quota_view )) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testQuotas() { + testValidSql("quotas: ", + "SELECT * FROM (SELECT * FROM quota_view WHERE ( quota_id IN (SELECT quota_view.quota_id FROM quota_view )) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testNetwork() { + testValidSql("network: ", + "SELECT * FROM (SELECT * FROM network_view WHERE ( id IN (SELECT network_view.id FROM network_view )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testNetworks() { + testValidSql("networks: ", + "SELECT * FROM (SELECT * FROM network_view WHERE ( id IN (SELECT network_view.id FROM network_view )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testProvider() { + testValidSql("provider: ", + "SELECT * FROM (SELECT * FROM providers WHERE ( id IN (SELECT providers.id FROM providers )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testProviders() { + testValidSql("providers: ", + "SELECT * FROM (SELECT * FROM providers WHERE ( id IN (SELECT providers.id FROM providers )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testInstanceType() { + testValidSql("instancetype: ", + "SELECT * FROM ((SELECT * FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testInstanceTypes() { + testValidSql("instancetypes: ", + "SELECT * FROM ((SELECT * FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + @Test + public void testImageType() { + testValidSql("imagetype: ", + "SELECT * FROM ((SELECT * FROM image_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + + @Test + public void testImageTypes() { + // TODO: This looks like a bug... + testValidSql("imagetypes: ", + "SELECT * FROM ((SELECT * FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + } + + private void testValidSql(String dynamicQuery, String exepctedSQLResult) { + SyntaxChecker chkr = new SyntaxChecker(20); ISyntaxChecker curSyntaxChecker = SyntaxCheckerFactory.createBackendSyntaxChecker("foo"); - - SyntaxContainer res = curSyntaxChecker.analyzeSyntaxState("Pools: ", true); - + SyntaxContainer res = curSyntaxChecker.analyzeSyntaxState(dynamicQuery, true); + Assert.assertTrue("Invalid syntax: " + dynamicQuery, res.getvalid()); String query = chkr.generateQueryFromSyntaxContainer(res, true); - - Assert.assertEquals( - "SELECT * FROM ((SELECT * FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0", - query); + Assert.assertEquals(exepctedSQLResult, query); } } -- To view, visit http://gerrit.ovirt.org/28135 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I862873171c6753f8c8863c10c336e981e39dc8cb Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Liran Zelkha <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
